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 500000 warnings? Certainly not sounds good if you are serious about your data.

Query OK, 3000000000 rows affected, 500000 warnings (xxxxx.xxx xxxx)
Records: 3000000000 Duplicates: 0 Warnings: 500000


So how do you grab those warnings?
You can use the --show-warnings parameter with the “mysql” command line utility which causes warnings to be shown after each statement if there are any. This option applies to interactive and batch mode.

/path/to/mysql --show-warnings –uUserName –pPassword DBNAME < /path/to/dump_file.sql >> /path/to/load_warnings.log 2>&1
The 2>&1 will write standard output and standard errors to a file (/path/to/load_warnings.log)

You may not see warnings
(i) If max_error_count is set to 0. In this case, warning_count still indicates how many warnings have occurred, but none of the messages are stored.
(ii) if sql_notes session variable is set to 0 to cause Note-level warnings not to be recorded.


##On EE
ttp://www.experts-exchange.com/Database/MySQL/Q_26835461.html