1

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.)
Aacini
  • 65,180
  • 12
  • 72
  • 108
Sherlock
  • 7,525
  • 6
  • 38
  • 79

2 Answers2

2

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

Update:

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
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Thanks @Sherlock, I added the extra detail here because [another question](http://stackoverflow.com/questions/23327501) described trouble doing tee in non-interactive mode. I wanted to supplement my answer here to keep the info together. – Bill Karwin Apr 28 '14 at 17:39
1

You can prepare your query script as per below for each statement:

echo "update mytable set col1='value' where id='id'" >> C:\Backup\error.txt
update mytable set col1='value' where id='id' >> C:\Backup\error.txt 2>&1

Note: If your are executing on linux then change path etc. accordingly.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Zafar Malik
  • 6,734
  • 2
  • 19
  • 30
  • Oh, that's very nice. I assume that there's no way to log it to a variable log file? Like `>> the tee file` or something? But this is good as-is, thanks. – Sherlock Apr 25 '14 at 07:05