Sherlock Sherlock - 1 year ago 67
SQL Question

Is there any way to log queries and output to a file?

My case is the following: an external party delivers a huge SQL file with thousands of queries. These include updates, inserts, subqueries, etc. Those queries are assembled in third-party software (I think Excel, but I might be mistaken).

As a result, many of these queries tend to fail and the whole batch needs to succeed without one single error. I'm using

mysql source file.sql
to execute the queries, and I'm using
mysql tee log.txt
to log the output to a txt-file.

However, this has proven to be insufficient, as this is the output I'm getting:

Query OK, 1 row affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

ERROR 1242 (21000): Subquery returns more than 1 row
Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

It's kinda looking for a needle in a haystack. I'd have to count all the preceding logging messages to determine which exact query failed.

Is there any way to get a log file including the queries? Or is there another way of doing this efficiently?

  • Executing in small batches is not an option. (It would take ages.)

  • Executing them all on the commandline isn't either. (The messages fly by so fast it's impossible to read or capture them all.)

Answer Source

If you open the mysql client with to -v or --verbose flag, it will echo the statement as well as the output.

$ mysql -v
mysql> tee output.txt
mysql> source myscript.sql


Note in the documentation for the --tee option (or the tee command):

Tee functionality works only in interactive mode.

This means it doesn't work when you run a script, and this limitation is documented. In other words, it works if you use source as you are doing, but it doesn't work if you do this:

$ mysql -v --tee output.txt < myscript.sql
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download