MySQL Bugs: #17436: ALTER TABLE causes pending INSERT DELAYED updates to fail.

Bug #17436	ALTER TABLE causes pending INSERT DELAYED updates to fail.
Submitted:	15 Feb 2006 21:18	Modified:	28 Jul 2006 11:53
Reporter:	Gregert Johnson	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S2 (Serious)
Version:	4.0.24 and up	OS:	Linux (Linux)
Assigned to:	Ingo Strüwing	CPU Architecture:	Any

Description:
When an INSERT DELAYED statement is issued against a locked table, the DELAYED thread waits in the "upgrading lock" state.  If the connection that has the lock then issues an ALTER TABLE statement, the DELAYED thread is killed, without executing the pending insert.

How to repeat:
Start the mysql utility on two connections.

Connection 1:
CREATE TABLE x (k int);
LOCK TABLES x WRITE;

Connection 2:
INSERT DELAYED x VALUES(123);

[At this point SHOW PROCESSLIST shows the DELAYED thread in "upgrading lock" state]

Connection 1:
ALTER TABLE x MODIFY k bigint;

[SHOW PROCESSLIST shows that the DELAYED thread has disappeared]

Connection 2:
SELECT * FROM x;
(stalls because of lock)

Connection 1:
UNLOCK TABLES;

Connection 2:
Query returns empty set - ERROR!

***********

When the above is repeated with INSERT DELAYED replaced by a simple INSERT, no error occurs - the row is successfully inserted.

Suggested fix:
Make INSERT DELAYED behave like INSERT!

I should add that I believe this problem to be more serious than the lost insert illustrated in the example above.  The actual situation that led to this report was one where table corruption resulted.  The application in question involves tables that are implemented as MySQL merge tables, in order to support very large datasets.  As a data collection application inserts rows into each of these merge tables at a fairly high rate (up to about 1000 rows per second), the rows that are written into the table (via multiple-value INSERT statements) are directed automatically by mysqld into the last "base table" of the union list defined for the table.  A daemon process runs every few minutes to check the size of the active base table; when it becomes larger than a given threshhold, a new base table is created, and it is added to the merge table by means of an ALTER TABLE command that simply updates the union list.  This mechanism had been working flawlessly;  however, when the INSERT was changed to INSERT DELAYED, corruption of the active base tables resulted.  Reverting to ordinary INSERT fixed the problem.

My speculation is that an ALTER TABLE statement not only kills the DELAYED connection thread, but does so in a manner that terminates it ungracefully in the middle of update operations, resulting in corruption of memory and disk data structures.

Thank you for a bug report. Verified just as described on 5.0.19-BK. I see no clear explanation of what should happen in this case in the manual (http://dev.mysql.com/doc/refman/5.0/en/insert-delayed.html), so it is at least a documentation request, even if it is intended behaviour.

When ALTER TABLE starts, it aquires a TL_WRITE_ALLOW_READ lock. This allows readers to use the table until the last moment. Then the table is copied. Finally the tables are renamed and the old table is closed. The lock prevents the delayed insert thread to get at the table and the close kills the thread. So it has no chance to get the queued inserts in.

I intend to change it so that the delayed insert thread is stopped before ALTER TABLE starts copying the table. Stopping shall include flushing the queue. The only problem is to acquire a lock that prevents other threads from queueing more inserts or modifying the table before ALTER TABLE acquires the final lock.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/6676

Back to "In progress". The changeset contains cosmetic changes only.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/6736

Sorry, but we decided to declare this as being not a bug.

In my patch I proposed a way how to accomplish that queued inserts could be incorported in the table before an ALTER TABLE is done.

But this violates the write lock of the first thread. When it retrieves the write lock, it wants to be sure that no changes are done to the table. Whatever it wants to do during the lock, there must be no changes to the table, not even inserts, until it releases the lock.

And it is pretty legitimate to ALTER a write locked table. Since this might change the record structure, the queued inserts cannot be applied to the table any more. The problem here is that the insert values have been parsed already. The resulting records are stored in the queue.

In the current implementation of delayed insert we cannot please all three requirements: 1. Accepted delayed inserts must not go lost. 2. A write lock excludes inserts. 3. ALTER TABLE is allowed in a write lock.

We will try to explain this in the manual.

For the future I see two hypothetical solutions to get around this problem:

1.) Have a new lock type that blocks the acceptance of delayed inserts. It could perhaps be requested with LOCK TABLE t1 WRITE_BLOCK or similar. This could be used by the application if it wants to use ALTER TABLE in a write lock.

2.) Rewrite the INSERT DELAYED feature so that the statement text is queued and the delayed thread parses the commands again. The downside is that we lose paralelism in parsing and creating records and need a lot more memory. After all this would be nothing else as having an extra thread in the application that does the inserts. For most users this would be nothing but a loss of performance. So it is very unlikely that this rewrite will even be taken into account.

If you want one of the changes implemented, or have another idea, please file a feature request for it (a bug report with severity "Feature request").

Regards
Ingo

I've added the following note to the INSERT DELAYED and ALTER TABLE sections of the manual: 

Pending <literal>INSERT DELAYED</literal> statements are lost if a table is write locked and <literal>ALTER TABLE</literal> is used to modify the table structure.

See also http://bugs.mysql.com/bug.php?id=38692 (which really isn't about Query cache at all)