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

MySQLquery-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

Related Solutions

Mysql – How to get non repeat results

Try

SELECT DISTINCT dtp.ci, 
            dtp.nombre_apellido, 
            dtp.telefono, 
            dtp.direccion, 
            dtp.correo, 
            dte.codigo 
FROM   default_tb_persona dtp
   LEFT JOIN default_tb_envio dte
          ON dtp.ci = dte.id_persona_envia;

Max.

MySQL Replication – Does Interrupting a Query on Master Replicate to Slave?

This is a local phenomenon within the MySQL Replication Architecture. Here is a sample message that can sometimes appear when you interrupt a query on a Master

Query partially completed on the master (error on master: 1053) and was aborted. There is a chance that your master is inconsistent at this point. If you are sure that your master is ok, run this query manually on the slave and then restart the slave with SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE; . Query: (INSERT vhc (v_id, vc_id) SELECT 7421594, vc_id FROM vc WHERE label='st' AND value='re')

Error 1053 is a Server Shutdown. Obviously, the intent was not to shutdown mysql and not tell connected Slaves. Hitting Ctrl-C on a Master would kill the DB Connection. However, such disconnects are simply interpreted as a shutdown. In my old post Does MySQL support replicating all databases?, I described how MySQL replicates from Master to Slave:

Master has Binary Logging enabled
Master records completed SQL Statements
Master will inject 'USE dbname;' among the SQL Statements it records
Slave connects to Master (IO Thread)
Slave IO Thread requests next SQL statement from the Binary Logs
Slave IO Thread copies it to its Relay Logs
Slave SQL Thread Processes its Relay Log Entries FIFO (Queue)

Looking back at the error message I described, the Slave detected that a binlog event in its relay logs that came from the Master. It was anticipating another event with the actual SQL saying everything went well on the Master. Error 1053 (the phantom shutdown caused by Ctrl-C) came up instead. This will give you the chance to say OH MASTER HAD THAT QUERY INTERRUPTED. I"LL GO LOOK AT THE DATA TO SEE IF THEY MATCH.

When it comes to your actual question, I would go with #1

Nothing happened on the slave, because master waits until the query is finished executing before it sends the query to the slave.

but with a caveat

Since Ctrl-C was done on the Master, the Master recorded the SQL in the binlog along with error 1053. The Slave intercepted this scenario.

Best Answer

Related Solutions

Mysql – How to get non repeat results

MySQL Replication – Does Interrupting a Query on Master Replicate to Slave?

Related Question