MySQL Bugs: #1396: Lost Data with delayed insert / mysqlhotcopy or lock and flush

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:	None
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

Description:
   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

Confirmed. 

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).

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.

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.

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 .

THERE IS NO PROBLEM

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.