0

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?

2
  • SHOW WARNINGS must be executed before any other command. Possibly HeidiSQL inserted something that would normally be harmless.
    – Rick James
    Dec 7, 2017 at 4:19
  • It actually prompts you when there are warnings and HeidiSQL will show them in a separate tab in the GUI. It's just that when the warnings are displayed, only a small number of the messages can be seen and they're not in a format that allows it to be exported. That's the problem.
    – Big_Al_Tx
    Dec 7, 2017 at 17:13

1 Answer 1

0

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$ 
6
  • Even in the CLI tool, when you tell it to SHOW WARNINGS, there's no way to direct the output to a file interactively. The only way you could do it is if you know IN ADVANCE that you will get errors that you want to capture that you can direct ALL output to a file. But that also means you can't do anything interactively (it all has to be written in an SQL script) and you can't see what's happening while the script is executing.
    – Big_Al_Tx
    Dec 7, 2017 at 17:18
  • @Big_Al_Tx - OK, you forced me to figure out the details. See my edit.
    – Rick James
    Dec 7, 2017 at 22:09
  • Ok, this will allow me to capture warnings in an external file, but only if I'm using the CLI tool. But I'm wondering if HeidiSQL would recognize the tee command ... I'll have to check that. I do have to admit that I misunderstood how to invoke the tee command; I thought it had to be used within the SQL statement. Thanks for that.
    – Big_Al_Tx
    Dec 7, 2017 at 22:21
  • From the CLI, type help; you will get a bunch of commands that talk to the CLI, not the server. tee is one of them. And, since tee does not talk to the server, it won't lose the WARNINGs.
    – Rick James
    Dec 7, 2017 at 22:26
  • <rant>So, is HeidiSQL a 3rd-party package that gets in the way of using MySQL?</rant>
    – Rick James
    Dec 7, 2017 at 22:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.