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 22:18	Modified:	28 Jul 2006 13:53
Reporter:	Gregert Johnson
Status:	Closed
Category:	Server: MyISAM	Severity:	S2 (Serious)
Version:	4.0.24 and up	OS:	Linux (Linux)
Assigned to:	Ingo Strüwing	Target Version:

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)