Importing a text file containing a list of SQL commands into MySQL is a straightforward task. All you need to do is simply feed the file contents through pipe into MySQL command line client. For example: mysql app_production < dump.sql.
The reasons for doing such imports can be very different - restoring MySQL backups created with mysqldump, manually replaying binary log events or performing database migrations during software roll-outs.
While the task is simple, the import may not end successfully and when this happens, how to tell what the problem was?
MySQL errors
Whenever database hits an error, MySQL produces an error message that describes the problem and the import process stops immediately. If the message is not clear enough, you can always refer to the reported line number, which is the line number inside the source SQL file. This way you can locate the precise command or query that failed.
maciek@psce ~ $ cat dump.sql | mysql test
ERROR 1064 (42000) at line 12: You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'NULL, 'aa')' at line 1
The information will also let you resume the import from the place where it died once you manage to correct the problem:
maciek@psce ~ $ tail -n+12 dump.sql | head -1
insert into a (NULL, 'aa'); <-- it's a syntax error, it can be easily corrected
...
maciek@psce ~ $ tail -n+12 dump.sql | mysql test
MySQL warnings
Another category of possible problems are warnings. From the database point of view they are not critical enough to prevent something from running, however when they occur, the results a statement produces may differ from you would expect - for example data could be malformed or truncated. It means that you should at least know what these problems were, even if in the end you simply decide to ignore them.
When you run a query from MySQL prompt, the client always notifies about warnings right after the query completes. Then it's only a matter of running SHOW WARNINGS command to get the details:
mysql> insert into a values (NULL, 'aaaaaaa');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> show warnings;
+---------+------+----------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------+
| Warning | 1265 | Data truncated for column 'v' at row 1 |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)
As it turns out, it's not so simple when you run an import. In such cases MySQL client silently ignores warnings, so you can't even tell if anything bad happened:
maciek@psce ~ $ cat dump.sql | mysql test maciek@psce ~ $
In order to enable printing warnings you need to use --show-warnings:
maciek@psce ~ $ cat dump.sql | mysql --show-warnings test Warning (Code 1265): Data truncated for column 'v' at row 1 Warning (Code 1265): Data truncated for column 'v' at row 1 Warning (Code 1265): Data truncated for column 'v' at row 1 Warning (Code 1265): Data truncated for column 'v' at row 1
The same import now shows that some data was truncated in the process. The previously hidden information looks rather important and is definitely something we would like to investigate. But there are still some details missing.
Unlike in case of errors, warning messages do not contain line numbers, so often the reported problems can't be traced back to a particular statement. But MySQL client can be configured to be even more verbose. --verbose tells it to print every statement it runs. That is a lot of noise, but having both queries and warnings in the same place, we can process the output to filter out anything that completed successfully and only leave queries that had issues:
maciek@psce ~ $ cat dump.sql | stdbuf -i0 -o0 -e0 mysql --show-warnings --verbose test 2>&1 |
sed -n -e '/^----/{h;:a;n;/^----/{H;b b};H;b a};:b;/^(Warning|ERROR)/{H;x;p}'
--------------
insert into a
values (NULL, 'aaaaaa')
--------------
Warning (Code 1265): Data truncated for column 'v' at row 1
--------------
insert into a values (NULL, 'aaaaaa')
--------------
Warning (Code 1265): Data truncated for column 'v' at row 1
--------------
insert into a values (NULL, 'aaaaaa')
--------------
Warning (Code 1265): Data truncated for column 'v' at row 1
This does not provide all the information such as line numbers, but seeing the actual query body is typically enough to identify specific problems.
My solution is of course only as good as it is. It is definitely more useful for smaller batch jobs than complete database restore operations, which should not be suffering from these kinds of problems anyway. Ultimately it would be much better if MySQL client had an option to show the complete problem reports whenever --show-warnings is used.