MySQL Bugs: #18484: Bogus "Server shutdown in progress" error on INSERT DELAYED + ALTER

Bug #18484	Bogus "Server shutdown in progress" error on INSERT DELAYED + ALTER
Submitted:	24 Mar 2006 14:07	Modified:	22 Aug 2009 16:44
Reporter:	Chris Slominski	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.1, 5.4	OS:	Any
Assigned to:	Jon Olav Hauglid	CPU Architecture:	Any

Description:
Clints occasionally have an insertion failure with the message "Server shutdown in progress" returned from the server. There are several clients rapidly doing "INSERT DELAYED ..." into various tables in the database. The error only occurs when some online table maintenance is happening. A separate client will rarely step in a perfrom some deletions of old data from a number of tables. The sequence is to lock a table, delete old rows, optimize table to reclaim disk space, unlock table, and proceed to the next table. This goes on for a long time since there are 2000 tables. The only time my insertion clients get this error is during one of these maintenance sessions. It doesn't happen every maintenance session however. When it does occur, it is usually just once per maintenance session.

Note that all of my clients are localhost connections. There are about 1000 insertions made per second.

The "shutdown" appears to be bogus, as the uptime on the server is quite old. Every client proceeds ahead normally with there insertions after one of these has occurred, however the data corresponding to the insert that failed is not in the database.

How to repeat:
My data collection runs continually and logs exceptions, so I am bound to see some of these over a 12 hour span. The heavier the insertion load, the more likely I am to get these. Otherwise they seem somewhat random and can't be recreated on demand.

Suggested fix:
Accept the insertion as it does all of the thousands of other ones.

Thank you for a problem report. 

Please, send your my.cnf file content and describe your insert and maintenance processes in more details. SHOW CREATE TABLE for the table you inserted into may be useful. 

Can you execute SHOW PROCESSLIST\G next time you'll see this message and send results here?

Here is the content of my.cnf:

[mysqld]
#datadir=/var/lib/mysql
datadir=/czar_store/test_db/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package).
old_passwords=1
#thread_stack=256K
#key_buffer_size=6G
read_rnd_buffer_size=1M
port=56791
open_files_limit=65536
table_cache=30000

[mysql.server]
user=mysql
basedir=/var/lib

[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

mysql> show create table table_1325;
+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                                                      |
+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| table_1325 | CREATE TABLE `table_1325` (
  `time` bigint(20) NOT NULL default '0',
  `val1` float default NULL,
  PRIMARY KEY  (`time`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

The error I get is as follows:

 Server shutdown in progress [INSERT DELAYED INTO table_1325 VALUES (4909857968738778968,-1)]

mysql> show processlist;
+------+---------+-----------------------------+---------+----------------+------+--------------------+------------------+
| Id   | User    | Host                        | db      | Command        | Time | State              | Info             |
+------+---------+-----------------------------+---------+----------------+------+--------------------+------------------+
| 8647 | cjs     | localhost                   | archive | Sleep          |    0 |                    | NULL             |
| 8648 | cjs     | localhost                   | archive | Sleep          |    0 |                    | NULL             |
| 8649 | cjs     | localhost                   | archive | Sleep          |    0 |                    | NULL             |
| 8650 | cjs     | localhost                   | archive | Sleep          |    0 |                    | NULL             |
| 8652 | cjs     | oparsrv1.acc.jlab.org:41302 | archive | Sleep          |   26 |                    | NULL             |
| 8883 | cjs     | localhost                   | archive | Sleep          |    0 |                    | NULL             |
| 9390 | DELAYED |                             | archive | Delayed insert |    1 | Waiting for INSERT |                  |
| 9399 | DELAYED |                             | archive | Delayed insert |    1 | Waiting for INSERT |                  |
| 9400 | DELAYED |                             | archive | Delayed insert |    1 | Waiting for INSERT |                  |
| 9401 | DELAYED |                             | archive | Delayed insert |    1 | Waiting for INSERT |                  |
| 9402 | DELAYED |                             | archive | Delayed insert |    1 | Waiting for INSERT |                  |
| 9405 | DELAYED |                             | archive | Delayed insert |    1 | Waiting for INSERT |                  |
| 9407 | DELAYED |                             | archive | Delayed insert |    1 | Waiting for INSERT |                  |
| 9408 | DELAYED |                             | archive | Delayed insert |    1 | Waiting for INSERT |                  |
| 9410 | DELAYED |                             | archive | Delayed insert |    1 | Waiting for INSERT |                  |
| 9411 | DELAYED |                             | archive | Delayed insert |    1 | Waiting for INSERT |                  |
| 9412 | DELAYED |                             | archive | Delayed insert |    1 | Waiting for INSERT |                  |
| 9413 | DELAYED |                             | archive | Delayed insert |    2 | Waiting for INSERT |                  |
| 9414 | DELAYED |                             | archive | Delayed insert |    1 | Waiting for INSERT |                  |
| 9415 | DELAYED |                             | archive | Delayed insert |    1 | Waiting for INSERT |                  |
| 9416 | DELAYED |                             | archive | Delayed insert |    0 | Waiting for INSERT |                  |
| 9417 | DELAYED |                             | archive | Delayed insert |    0 | Waiting for INSERT |                  |
| 9418 | DELAYED |                             | archive | Delayed insert |    0 | Waiting for INSERT |                  |
| 9419 | DELAYED |                             | archive | Delayed insert |    0 | Waiting for INSERT |                  |
| 9420 | DELAYED |                             | archive | Delayed insert |    0 | Waiting for INSERT |                  |
| 9421 | DELAYED |                             | archive | Delayed insert |    1 | Waiting for INSERT |                  |
| 9423 | cjs     | devlnx05.acc.jlab.org:34148 | archive | Query          |    0 | NULL               | show processlist |
+------+---------+-----------------------------+---------+----------------+------+--------------------+------------------+
27 rows in set (0.00 sec)

Please, try to install newer version, 4.1.19, and check if the same error message ever appears.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

This looks like a duplicate of http://bugs.mysql.com/bug.php?id=18256

Grammar file for bug 18484

Attachment: bug18484.yy (application/octet-stream, text), 103 bytes.

To reproduce, please branch the mysql-test-extra-6.0 tree and cd to mysql-test/gentest. Then run:

perl runall.pl --basedir=/build/bzr/mysql-5.1/ --grammar=/tmp/bug18484.yy

Bug #18256 is about a separate cause for the same error message, so they are not duplicates.

This bug is also reproducible with 2 threads only. It appears that for all practical purposes all INSERT DELAYEDs that happen during an ALTER TABLE can get the bogus error. It is not certain whether the row was actually inserted.

Fixed in the patch for Bug#45949 in 5.4.4

Noted in 5.4.4 changelog.

For INSERT DELAYED statements issued for a table while an ALTER TABLE
operation on the table was in progress, the server could return a
spurious "Server shutdown in progress" error.

Noted in 5.5.3 changelog. (Because Bug#45949 fixes this bug and is in 5.5.3)