Showing entries 1 to 4
Displaying posts with tag: show warnings (reset)
Added a Table of Contents

Not a big deal, but I just added a “Table of Contents” page to my blog to make finding older articles much easier.

I noticed most of my posts are quite lengthy, and it can take a bit of searching/clicking to find an older entry. So unless you happen to recall the ‘month/year’ it was published, which I don’t even remember that, then hopefully this will help.

Really simple, and looks just like this:

[Read more]
MySQL: Bulk import and logging the warnings to a file

Platform: Any UNIX*

If you are working on a small set of data or with fewer tables and if you notice that the DML statements such as INSERT, UPDATE, and LOAD DATA INFILE as well as DDL statements such as CREATE TABLE and ALTER TABLE operation has generated few warnings then you can just execute the “SHOW WARNINGS” SQL command to display the exact warning messages generated during the last DML operation.
But “SHOW WARINGS” shows nothing if the last statement used a table and generated no messages.
SHOW WARNINGS shows the error, warning, and note messages that resulted from the last statement that generated messages in the current session.
“SHOW WARINGS” can certainly help you in grabbing the warnings but what if you are loading a huge dump file or performing a batch operation and at the middle of load operation MySQL reports that the last DML completed with …

[Read more]
Using EXPLAIN EXTENDED / SHOW WARNINGS to Help Troubleshoot Inefficient Queries in MySQL

When examining the execution plan of troublesome queries in MySQL, most users are aware of using EXPLAIN. However, an often overlooked, yet very helpful extension of EXPLAIN, is EXPLAIN EXTENDED coupled with the SHOW WARNINGS command.

The reason being is because it provides a little more information about how the optimizer processes the query, and thus it could help to quickly identify a problem that you might not otherwise recognize with just EXPLAIN.

For instance, here is a common query which could be inefficient:

SELECT id FROM t WHERE id='1';

And here is the CREATE TABLE output:

mysql> show create table tG
*************************** 1. row …
[Read more]
Show warnings with MANY MANY warnings

What to do when you have too many warnings and would like to check them out.

Say you were trying to run a query which returned a heap of warnings and you want to go through the list ….

mysql> load data infile '/aaa/bbb/ccc' into table xyz LINES TERMINATED BY '\r\n';
Query OK, 0 rows affected, 65535 warnings (4 min 25.12 sec)

mysql> show warnings;
| Level   | Code | Message                                        |
| Warning | 1366 | Incorrect value: '' for column 'xx' at row 1   |
| Warning | 1366 | Incorrect value: '' for column 'xx' at row 9   |
| Warning | 1366 | Incorrect value: '' for …

[Read more]
Showing entries 1 to 4