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

[24 Mar 2006 14:07] Chris Slominski
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.
[24 Mar 2006 17:18] Valeriy Kravchuk
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?
[24 Mar 2006 17:36] Chris Slominski
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
[24 Mar 2006 17:40] Chris Slominski
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)]
[24 Mar 2006 17:44] Chris Slominski
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)
[12 May 2006 8:39] Valeriy Kravchuk
Please, try to install newer version, 4.1.19, and check if the same error message ever appears.
[12 Jun 2006 23:00] Bugs System
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".
[30 Jan 2009 22:37] Roel Van de Paar
This looks like a duplicate of http://bugs.mysql.com/bug.php?id=18256
[4 Jul 2009 19:28] Philip Stoev
Grammar file for bug 18484

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

[4 Jul 2009 19:31] Philip Stoev
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
[4 Jul 2009 19:34] Philip Stoev
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.
[22 Aug 2009 9:18] Konstantin Osipov
Fixed in the patch for Bug#45949 in 5.4.4
[22 Aug 2009 16:44] Paul Dubois
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.
[11 Mar 2010 18:10] Paul Dubois
Noted in 5.5.3 changelog. (Because Bug#45949 fixes this bug and is in 5.5.3)