Bug #1396 Lost Data with delayed insert / mysqlhotcopy or lock and flush
Submitted: 25 Sep 2003 3:43 Modified: 22 Jun 2015 15:26
Reporter: Martin Friebe (Gold Quality Contributor) (OCA) Email Updates:
Status: Won't fix Impact on me:
Category:MySQL Server: General Severity:S3 (Non-critical)
Version:4.0.15, 5.1.34 OS:FreeBSD (freebsd (propably others))
Assigned to: CPU Architecture:Any
Tags: Obsolete_201506, qc
Triage: Triaged: D5 (Feature request)

[25 Sep 2003 3:43] Martin Friebe
   mysqlhotcopy (but possible any other software) can cause delayed 
handlers to terminate with "Delayed insert thread couldn't get requested 
lock for "

   I havent worked out the exact conditions under wich mysqlhotcopy 
fails, but it seems that if mysqlhotcopy tries to get a lock (waiting 
for a query to finish) and you start another delayed insert (maybe 
involving several tables?), then it will fail the server

How to repeat:
   The description below, extracts the fault without mysqlhotcopy

   you need two mysql client connection: (assuming a table tb with a 
column a int)
  client 1> LOCK TABLES  tb READ;
  client 2> insert delayed into tb  values(1);
  client 1> flush tables;
   # the delayed handler dies here, the insert is not in the table
  client 1> unlock tables
[25 Sep 2003 11:55] Indrek Siitan

Just a note - the whole procedure to repeat took less time than specified
by the "delayed_insert_timeout" variable (which is how long the DELAYED
insert thread should wait for a lock).
[19 Nov 2003 9:14] Sergei Golubchik
It all happens because FLUSH TABLES kills all delayed threads - as documented in the manual. Of course it is not expected to kill threads until they have inserted all the pending data, but if there is a lock that block inserts there is not that much we can do :(

There is no way to fix this deficiency of delayed inserts that won't be too complex and too intrusive for the stable version 4.0 (and even for 4.1)

We can fix this particular bug of mysqlhotcopy, though by adding
FLUSH TABLES t1,t2,... WITH READ LOCK command.

I'll put this bug into "deferred" state until this new command will be implemented.
[18 Sep 2009 13:58] Konstantin Osipov
When WL#5000 is complete, mysqlhotcopy will do FLUSH TABLE <> WITH READ LOCK, 
and thus FLUSH will come before the table is locked for read, which will close the window for a race/start of insert delayed thread.
That should resolve this problem.
[12 Oct 2012 8:42] proscene diffusion
yo men excuse moi de avoir prit ta conection mais a 3h00 il avait persone pie gros gere tes comme loin dans le palmaraise moi je suis en 98 position .
[22 Apr 2014 15:27] Marcos Gonzalez
[22 Jun 2015 15:26] Morgan Tocker
Mysqlhotcopy was deprecated in MySQL 5.6, and removed from MySQL 5.7.5.  The rationale behind this decision is explained here:  http://www.tocker.ca/2014/04/17/proposal-to-deprecate-mysqlhotcopy.html

I am closing this bug as Won't fix.