MariaDB – How to Export Warnings Generated by HeidiSQL

mariadb

I am trying to do a bulk data upload into a MariaDB database. The data file has over 3M records. When the SQL runs the LOAD DATA INFILE statement, I get over 9600 warnings where there were problems when the data was inserted. In order to chase down why there were problems, I need to get the row numbers of each record where an error was encountered so I can filter the source file and examine the problem columns.

I'm using HeidiSQL as my front-end, and there doesn't appear to be a way to export the list of warnings. I tried to direct the output of the SHOW WARNINGS statement to an external file like you would a query, but that doesn't work (probably because the warnings are not persistent data like data in a table would be).

Does anyone know how I can save the list of Warnings to an external file?

Best Answer

Learn how to use the commandline "mysql" tool. Then perform the LOAD and SHOW there.

Example

tee filename can be invoked from the CLI without losing the WARNINGs:

CREATE TABLE dtts (
    ... just_date DATE ... );

mysql> INSERT INTO dtts (just_date) VALUES (NOW());
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> tee /tmp/mtee
Logging to file '/tmp/mtee'
mysql> SHOW WARNINGS;
+-------+------+------------------------------------------------+
| Level | Code | Message                                        |
+-------+------+------------------------------------------------+
| Note  | 1265 | Data truncated for column 'just_date' at row 1 |
+-------+------+------------------------------------------------+
1 row in set (0.00 sec)

mysql> exit
Bye
rj$ ls -l /tmp/mtee
-rw-rw-r-- 1 rj rj 389 Dec  7 14:05 /tmp/mtee
rj$ cat /tmp/mtee
mysql> SHOW WARNINGS;
+-------+------+------------------------------------------------+
| Level | Code | Message                                        |
+-------+------+------------------------------------------------+
| Note  | 1265 | Data truncated for column 'just_date' at row 1 |
+-------+------+------------------------------------------------+
1 row in set (0.00 sec)

mysql> exit
rj$
Related Question