Mysql – Will the query pick up where it stopped due to connection timeout


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.

Best Answer

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