Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Is there a way to check and see if a query finished? I ran 3 very long running update queries (+/- 25 hours each) as I went out the door for vacation last week. Unfortunately, somewhere during the week the power went out and the machine running MYSQL shut down. Is there a way to check and see which of the 3 (or all three) queries were completed?

I know I could check to see if the data had been updated, but NULLs are to be expected with a correct and complete execution and there's 48 million rows of data to peruse. Any thoughts?

share|improve this question

migrated from stackoverflow.com Aug 5 '12 at 20:18

This question came from our site for professional and enthusiast programmers.

6  
The logs... if you log things. If you don't I'd recommend starting. –  Ben Aug 4 '12 at 21:09
1  
@Ben - too late to recommend a UPS and transactional processing too, I guess. –  Spudley Aug 4 '12 at 21:11
1  
If you are using InnoDB you can do your code in transactions. dev.mysql.com/doc/refman/5.0/en/commit.html –  Zefiryn Aug 4 '12 at 21:12
1  
If you had slow query logging turned on then queries that slow would probably have ended up in the slow query log. –  Frederick Cheung Aug 4 '12 at 21:15
3  
And, in case it didn't complete properly, do you have a backup? –  Spudley Aug 4 '12 at 21:18

1 Answer 1

If you are running with binary logs enabled this can be checked with relatively high reliability.

First, to see whether binary logs are indeed enabled, run:

SHOW BINARY LOGS;

If they're enabled, you should get an output like this:

+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000244 |  15462544 |
| mysql-bin.000245 | 102622775 |
+------------------+-----------+

Otherwise you would get an error message.

Now, if binary logs are enabled, then any successful commit is also written to binary logs. I say "commit", but the truth is any successful operation, even on non transactional tables such as MyISAM, is written there. But, to be honest, to have any certainty about the outcome of your queries, I do hope for your sake you're using a transactional engine such as InnoDB, or else you cannot be sure of anything.

OK, so asssuming now that you have binary logs turned on, and that your tables are transactional (hopefully InnoDB), then a successful completion of your queries is expected to be written to the binary logs.

You must now hunt down the relevant binary log and look for the query there. If you find the query - good! If not - it's most probably not there. I'll explain shortly.

Which binary log contains your query? Look at the binary log files themselves, typically in your data directory. Look for their timestamps. When the power came up, a new binary log was created. Find it. Your queries are most probably in the binary log one before that one. This is a guess. It could also be one before that, etc. But it's a good guess.

Now, using the mysqlbinlog utility, execute, from command line, something like this:

mysqlbinlog mysql-bin.000245

Replace file name with the one you suspect to contain the query.

This will output all queries in this binary log file into standard output. On Unix, use grep to find your query:

mysqlbinlog mysql-bin.000245 | grep "something which identifies the query"

On Windows, good luck. Open with notepad++ or something and search manually.

Is the query there? Great - you know it has been committed.

Is the query not there? Need to check on sync_binlog param. Is it 1? Then query not in binary log ==> query not committed. But if sync_binlog is not 1, there could still be a chance that the query has been committed yet not in the binary log, as the crash may have happened just after the commit and just before the binary log was flushed to disk. You then need to revert to other means.

Those being: (and hopefully, again, you're using InnoDB): look for a single row that can identify outcome of the query. With InnoDB you get "all or nothing". If you can be certain of a single row affected by the query - you can be sure that the query has completed.

edit: of course, if slow log is enabled, you can expect such long query to be logged there after completion as well...

Good luck!

share|improve this answer
2  
I hope you stick around. –  jcolebrand Aug 5 '12 at 21:00
2  
@jcolebrand Rolando could do with some competition on here :) –  Phil Aug 6 '12 at 1:04

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.