How can I Export Warnings generated by HeidiSQL

Question

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?

Answer 1

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$