MySQL Bugs: #14727: hangs randomly at INSERT statements

Bug #14727	hangs randomly at INSERT statements
Submitted:	7 Nov 2005 23:30	Modified:	11 Nov 2005 7:54
Reporter:	[ name withheld ]	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.15-standard-log	OS:	Linux (Debian GNU/Linux 3.1)
Assigned to:		CPU Architecture:	Any

Description:
Sometimes and randomly INSERT statemens simply hang, the mysql server still works but the INSERT never finishes. When I kill the INSERT command it is still shown in the process list as "killed" and the table is still locked. (look at processlist, ids: #11 and #89)

kernel: Linux 2.6.12, SMP (4 CPUs, Intel Xenon), x86_64, Debian GNU/Linux 3.1

This problem, but in different version of mysql was posted in mailing list:
http://forums.mysql.com/read.php?20,53276,53276

mysql> show processlist;
+-----+----------+----------------------+---------------+---------+------+--------+------------------------------------------------------------------------------------------------------+
| Id  | User     | Host                 | db            | Command | Time | State  | Info                              |
+-----+----------+----------------------+---------------+---------+------+--------+------------------------------------------------------------------------------------------------------+
|  11 | skript   | xxxx | db2       | Query   |  653 | update | INSERT INTO mos_session ( `session_id`,`time`,`username`,`gid`,`guest` ) VALUES ( 'ab4c72f1fb083f010 |
|  12 | skript   | xxxx | db2       | Query   |  649 | Locked | DELETE FROM mos_session
WHERE (time < 1131400668)                                                    |
|  16 | skript   | xxxx | db2       | Query   |  620 | Locked | DELETE FROM mos_session
WHERE (time < 1131400698)                                                    |
|  20 | skript   | xxxx | db2       | Query   |  605 | Locked | DELETE FROM mos_session
WHERE (time < 1131400713)                                                    |
|  23 | skript   | xxxx | db2       | Query   |  593 | Locked | DELETE FROM mos_session
WHERE (time < 1131400725)                                                    |
|  37 | skript   | xxxx | db2       | Query   |  540 | Locked | DELETE FROM mos_session
WHERE (time < 1131400777)                                                    |
|  42 | skript   | xxxx | db2       | Query   |  520 | Locked | DELETE FROM mos_session
WHERE (time < 1131400797)                                                    |
|  76 | skript   | xxxx | db2       | Query   |  314 | Locked | DELETE FROM mos_session
WHERE (time < 1131401004)                                                    |
|  82 | skript   | xxxx | db2       | Query   |  301 | Locked | DELETE FROM mos_session
WHERE (time < 1131401017)                                                    |
|  83 | skript   | xxxx | db2       | Query   |  300 | Locked | DELETE FROM mos_session
WHERE (time < 1131401017)                                                    |
|  89 | skript   | xxxx | rs_cestovanie | Query   |  278 | update | INSERT INTO mos_session ( `session_id`,`time`,`username`,`gid`,`guest` ) VALUES ( 'bc8f853fa6262fa0c |
|  91 | skript   | xxxx | db1 | Query   |  274 | Locked | DELETE FROM mos_session
WHERE (time < 1131401043)                                                    |
|  94 | skript   | xxxx | db1 | Query   |  269 | Locked | DELETE FROM mos_session
WHERE (time < 1131401049)                                                    |
| 114 | skript   | xxxx | db1 | Query   |  210 | Locked | DELETE FROM mos_session
WHERE (time < 1131401108)                                                    |
| 134 | skript   | xxxxx | db1 | Query   |  127 | Locked | DELETE FROM mos_session
WHERE (time < 1131401191)                                                    |
| 159 | skript   | xxxx | db1 | Query   |   55 | Locked | DELETE FROM mos_session
WHERE (time < 1131401263)                                                    |
| 165 | skript   | 62.168.117.203:59328 | db2      | Query   |   25 | Locked | DELETE FROM mos_session
WHERE (time < 1131401293)                                                    |
| 166 | skript   | xxxx | db2       | Query   |   18 | Locked | DELETE FROM mos_session
WHERE (time < 1131401300)                                                    |
+-----+----------+----------------------+---------------+---------+------+--------+------------------------------------------------------------------------------------------------------+
19 rows in set (0.00 sec)

repair/chek of tables was sucessfull, with no errors. Table/database was diffrent in each time of hang

How to repeat:
I can't reporoduce this in a test environment. It's very random

As you can see in the linked forum message above, I have this problem, too (random hangs during insert).

Server version: 5.0.15 (AMD64 precompiled)
Distribution: Debian 3.1 (glibc 2.3.2)
Databases: MyISAM/InnoDB
(I am mainly connecting to the mysql server with PHP5.1RC4)

Hi!

If you are using InnoDB tables, please print

SHOW INNODB STATUS\G

during a hang and post it here.

Regards,

Heikki

workaround: downgrade to older version.
downgrade to 4.1.11-Debian_4sarge2-log (version in Debian distro) was solved this issue at this moment.

Please, answer the question asked by Heikki.

I am trying to reprocude this problem (5.0.15) with InnoDB and MyISAM tables. At the moment, the hang doesn't occur (as I said it happens quite randomly).

Last hangs were two days before and with myISAM tables...

Sorry, I don't read Heikki Tuuri's comment. He was posted it, when I'm writting comment :)

I don't use InnoDB tables on server. Everything runs on MyISAM. I still trying to reproduce this issue, but I have too much diffrent setup (no SMP and different version of kernel, glibc).

Hi,
could it be the same as http://bugs.mysql.com/bug.php?id=8555 ?
I see you're using glib 2.3.2, Debian 3.1, AMD64, just like in that bug report (which is in fact a bug in glibc's NPTL).

I've patched the glibc which is shipped with debian and have no problems any more, so I think Guilhem Bichot is right.

Unfortunately debian hasn't patched their glibc in the AMD64 stable distribution, but  after a small discussion they have patched it in "stinkypete" (unofficial contributions tree for AMD64) yesterday.

So either adding stinkypete to sources.list or downloading the new glibc packages from ftp://ftp.de.debian.org/debian-amd64/debian-amd64/pool/main/g/glibc/ resolves this problem.

I was runs stress script mentioned in bug #8555 on few x84_64 server and I have only problems with MySQL (4.1.15) server from mysql.com and debian 3.1. I think MySQL server from Debian distro (4.1.11-Debian_4sarge2-log) is not affected by this issue, so I think Guilhem Bichot is right.

So, can we mark this bug report as a duplicate of http://bugs.mysql.com/bug.php?id=8555?

Yes, I think

Looks like a duplicate of http://bugs.mysql.com/bug.php?id=8555. Same platform, same solution.