MySQL Bugs: #13737: Mysql hangs after insert-query

Bug #13737	Mysql hangs after insert-query
Submitted:	4 Oct 2005 12:07	Modified:	6 Oct 2005 6:14
Reporter:	Gerwout van der Veen	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	4.1.14	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
I've got the same problems as listed in the bug http://bugs.mysql.com/bug.php?id=3483, except that the problem is accuring in every database (myisam for sure, no innodb to test on this server) on the server. Also in non-fulltext tables. I've had this behaviour in both standard binary versions -(Linux (AMD64 / Intel EM64T, glibc-2.3, dynamic, gcc) and Linux (Intel EM64T, glibc-2.3, dynamic, Intel C++ Compiler 8.1, requires the Intel icc shared libraries)- from your website. Show processlist shows the processes, but after killing them, the status will change in killed, but they never are disappearing. The option  --skip-concurrent-insert solves the problem, but shouldn't be necessary. The logfile doesn't show any information and after a while mysql will slow down and will give a too many connections error. After killing (-9 normal shutdown isn't working) the server and starting again the table is corrupted. 

How to repeat:
Difficult to give an exact situation. The problem isn't occuring always. On some databases they are, but on other databases (with exactly the same table structure) the problem isn't occuring. I think it occurs when there are 2 insert-queries in the same database in a very short period after each other. Maybe a locking-issue?

Please, try to give as much information about the problem situation, as possible. The repeatable sequence of actions to reproduce it is the best way.

In any case, your my.cnf settings, content of the error log for the period when you observe the problem, structure of tables and SQL statements used is really needed. 

We'll not be able to fix the bug, if any, without the repeatable test case.

Hello,

my.cnf:
[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock
[mysqld]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock
enable-locking
key_buffer = 1024M
max_allowed_packet = 32M
table_cache = 1000
sort_buffer_size = 2048K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 16M
long_query_time = 2
query_cache_limit = 32M
query_cache_min_res_unit = 4K
query_cache_size = 1024M
query_cache_type = 1
interactive_timeout = 30
wait_timeout = 30
server-id       = 1
[mysqldump]
quick
max_allowed_packet = 32M
[mysql]
no-auto-rehash
[isamchk]
key_buffer = 512M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
set-variable=default-character-set=latin1
set-variable=default-collation=latin1_swedish_ci
set-variable=collation_server=latin1_swedish_ci

Table layout:
CREATE TABLE `blobobjects` (
  `id` int(20) NOT NULL auto_increment,
  `mimetype` varchar(80) default NULL,
  `orgname` varchar(255) default NULL,
  `filesize` int(20) default NULL,
  `data` mediumblob,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `objects` (
  `id` int(20) NOT NULL auto_increment,
  `parentid` int(20) default NULL,
  `objecttype` int(20) default NULL,
  `type` int(11) default NULL,
  `title` varchar(255) NOT NULL default '',
  `moduser` varchar(40) default NULL,
  `modtime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `creuser` varchar(40) default NULL,
  `cretime` timestamp NOT NULL default '0000-00-00 00:00:00',
  `objectvalues` text,
  `perms` varchar(255) NOT NULL default '',
  `objecttypecode` int(20) default NULL,
  `orderid` int(20) default NULL,
  PRIMARY KEY  (`id`),
  KEY `objindex` (`parentid`,`objecttype`),
  KEY `objecttype` (`objecttype`),
  KEY `orderid` (`orderid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `possobjects` (
  `id` int(20) NOT NULL default '0',
  `typeid` int(20) NOT NULL default '0',
  PRIMARY KEY  (`id`,`typeid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `roles` (
  `id` int(20) NOT NULL auto_increment,
  `name` varchar(40) NOT NULL default '',
  `description` varchar(255) default NULL,
  `moduser` varchar(40) default NULL,
  `modtime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `rights` int(20) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `sessions` (
  `sessionkey` varchar(32) NOT NULL default '',
  `expires` int(11) unsigned NOT NULL default '0',
  `value` text NOT NULL,
  PRIMARY KEY  (`sessionkey`),
  KEY `expires` (`expires`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `userroles` (
  `userid` int(20) NOT NULL default '0',
  `roleid` int(20) NOT NULL default '0',
  PRIMARY KEY  (`userid`,`roleid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `users` (
  `id` int(20) NOT NULL auto_increment,
  `username` varchar(30) NOT NULL default '',
  `password` varchar(100) NOT NULL default '',
  `fullname` varchar(100) NOT NULL default '',
  `moduser` varchar(40) default NULL,
  `modtime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

But it's just like i've said, there isn't a specific query or table which is causing the problem. I've got the same tables in about 50 other databases. Some of them are working correct, others aren't. There are also a bunch of other databases (completely different structure) which are behaving in a similair way. The mysql-error log isn't complaining at all... It is logging, because it logs other things. I would like to give you better feedback, but I don't know what to send at this time. I think, it occurs when multiple queries in a short time to the same database are fired to mysql, but I'm not 100% sure of that.

Please, look at the bug report http://bugs.mysql.com/bug.php?id=8555. I think, the problem you encountered have the same reason, really, because all the machines you are reported about are 64bit and use Debian...

I'd like to mark this report as duplicate of bug #8555, but to be sure, please, run the test suit on you machine (make test from the sources directory will be enough) and inform me about the results. 

You may try to use one of the static builds for Linux (http://dev.mysql.com/get/Downloads/MySQL-4.1/mysql-standard-4.1.14-pc-linux-gnu-i686.tar.g..., for example).

Hello,
I've updated glibc and restarted mysql without the --skip-concurrent-insert and it's handling queries like it should. In my opinion this (non MYSQL)bug should be mentioned in the online documentation. It isn't just Debian-related, because it affects glibs on other systems as well. It's difficult to track down and I think there are many configurations which are suffering from the same problem.

I had sent a request to our documentation team to describe the problem and it's solution in the prominent place.

I am closing this report as the duplicate of http://bugs.mysql.com/bug.php?id=8555 now.

I need some more information from you:

- kernel version used
- glibc version used (after upgrade)
- NPTL version used (the results of getconf GNU_LIBPTHREAD_VERSION command)

All these for the system working properly now. Please, inform me about any new problems with it after upgrading glibc.

Kernel: Debian 2.6.8-11-em64t-p4-smp
Glibc version after upgrade: 2.3.5-6
NPTL 2.3.5

I know this bugs occurs in several Linux-distributions.
Debian, Ubuntu and Redhat Advanced Server.
Several sources:
https://bugzilla.redhat.com/bugzilla/show_bug.cgi?id=113968
http://trainedmonkey.com/2005/6/10/pthread_rwlock_wrlock_bug_on_amd64_with_hoary_hedgehog
http://hashmysql.org/index.php?title=Opteron_HOWTO#pthread_rwlock_wrlock_hang_with_nptl
https://bugzilla.ubuntu.com/show_bug.cgi?id=11730
http://bugs.debian.org/cgi-bin/bugreport.cgi?bug=314408

I think it's likely that this bug is occuring in other distributions as well.
It's not AMD specific, because I am using a dual XEON configuration.

Thank you for the additional information. I am closing this report as a dublicate of http://bugs.mysql.com/bug.php?id=8555. It is not a MySQL bug, but a problem with glibc. As a solution, it is proposed to use newer versions of glibc (>= 2.3.5-3 should be enough) or do not use NPTL at all :)