0

I am doing a rather large query that hits MySQL connection timeout. I know how to change the timeout parameter, but I'm also query from a laptop that can't be left running for long.

The database admin told me that I could send the results to a table instead. After searching around, it seems like INSERT INTO dst_tbl SELECT * FROM src_tbl; is basically the idea (source article).

But even if my results are saved, will the query picked up from where it stopped when I reconnected? Is there a way to check that I'm not missing / duplicating records?

More generally, I'd appreciate any recommendation about how to do large query under timeout constraint.

Improve this question

1 Answer 1

Reset to default
1

MySQL is an ACID database. A in this term stands for Atomicity. This basically mean:

Atomicity requires that each transaction is "all or nothing": if one part of the transaction fails, the entire transaction fails, and the database state is left unchanged. An atomic system must guarantee atomicity in each and every situation, including power failures, errors, and crashes. To the outside world, a committed transaction appears (by its effects on the database) to be indivisible ("atomic"), and an aborted transaction does not happen.

So, you should not worry at all. If your transaction timed out nothing will be written to database. Re-run your transaction

Improve this answer
4
  • The fact that everything is rolled back is a problem actually... I was hoping that whatever I queried before timing out could be saved in a separate table (not the source one), and I could pick up from there by initiation a new connection. Would that not work?
    – Heisenberg
    Jul 3, 2014 at 6:53
  • Just wanted to add this. If you have a few sql queries running and one of them timed out it really depends how you run the queries. If they all were part of a big transaction then they all will be rolled back. If however each of them run in their own separate transaction then only the last query will be rolled back.
    – cha
    Jul 3, 2014 at 10:53
  • I just have one big SELECT query. It keeps hitting the time out connection limit. What should I do in this case then?
    – Heisenberg
    Jul 3, 2014 at 13:40
  • Ok, I see. So, you are not making any database changes, but trying to select data using very complex conditions. You need to analyze the query execution plan and understand if it can be improved by introducing indexes. You can also retrieve chunk of data e.g. If it is a sales transactions table you can retrieve data for last quarter, then for the previous quarter, etc.
    – cha
    Jul 3, 2014 at 20:13

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.