Bug #1396 Lost Data with delayed insert / mysqlhotcopy or lock and flush
Submitted: 25 Sep 2003 5:43 Modified: 20 May 8:43
Reporter: Martin Friebe (Gold Quality Contributor) (SCA)
Status: Verified
Category:Server: General Severity:S3 (Non-critical)
Version:4.0.15, 5.1.34 OS:FreeBSD (freebsd (propably others))
Assigned to: Target Version:
Tags: qc
Triage: Triaged: D5 (Feature request)

[25 Sep 2003 5:43] Martin Friebe 
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
[25 Sep 2003 13:55] Indrek Siitan 
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).
[19 Nov 2003 10: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 15: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.