Bug #48515 | Mysqldump hangs with ** DEAD ** processes in the processlist with kernel Oops | ||
---|---|---|---|
Submitted: | 3 Nov 2009 20:20 | Modified: | 14 Jan 2013 18:32 |
Reporter: | Lisa Williams | Email Updates: | |
Status: | Can't repeat | Impact on me: | |
Category: | MySQL Server: mysqldump Command-line Client | Severity: | S3 (Non-critical) |
Version: | 5.0.67, 5.1.40 | OS: | Linux (openSUSE 11.1 x86_64) |
Assigned to: | Matthew Lord | CPU Architecture: | Any |
Tags: | *** DEAD ***, checking permissions, mysqldump, process hang |
[3 Nov 2009 20:20]
Lisa Williams
[3 Nov 2009 20:25]
Lisa Williams
my.cnf we used prior to any of this happening
Attachment: my.cnf_after.txt (text/plain), 5.19 KiB.
[3 Nov 2009 20:26]
Lisa Williams
my.cnf with some changes afterwards - it still occurs
Attachment: my.cnf_after.txt (text/plain), 5.19 KiB.
[3 Nov 2009 20:27]
Lisa Williams
current my.cnf based on performance model found on web with some edits
Attachment: my.cnf.now (text/plain), 17.73 KiB.
[3 Nov 2009 20:30]
Lisa Williams
output of show variables
Attachment: mysql_vars.txt (text/plain), 14.94 KiB.
[3 Nov 2009 20:35]
Lisa Williams
* Hardware this system is running on PowerEdge 2950 III Two Dual Core Intel® Xeon® E5205, 6MB Cache, 1.86GHz,1066MHz FSB 8GB 667MHz memory 4 250GB 250GB 7.2K RPM Serial ATA 3Gbps 3.5-in hard drives reproduced on a machine with similar hardware but machine with only 2GB memory.
[3 Nov 2009 20:41]
Lisa Williams
I put the wrong info about when this started happening. It started 9/27/09.
[3 Nov 2009 21:21]
MySQL Verification Team
Thank you for the bug report. Are you tried with 5.0.87 released version?. Thanks in advance.
[3 Nov 2009 23:18]
Lisa Williams
[3 Nov 22:21] Miguel Solorzano Thank you for the bug report. Are you tried with 5.0.87 released version?. Thanks in advance. Answer - No - I haven't - but I did try with 5.1.40 5.1.40-log Source distribution
[21 Nov 2009 10:47]
Valeriy Kravchuk
I would be nice to know if recent versions, 5.0.88 and/or 5.1.41, are affected.
[22 Dec 2009 0: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".
[24 Jan 2010 13:30]
Jasper E
I am experiencing the same problem. I have seen this error occurring while using phpmyadmin on php/apache. The page would take a long time to load, when checking "SHOW PROCESSLIST" from a shell it first showed: 191381 | root | localhost | gebruiker | Query | 35 | checking permissions | SELECT *, `TABLE_SCHEMA` AS `Db`, `TABLE_NAME` | This changed to the following a few seconds later: | 191381 | root | localhost | gebruiker | Query | 38 | *** DEAD *** | SELECT *, `TABLE_SCHEMA` AS `Db`, `TABLE_NAME` | Shutting mysqld down in the traditional way doesnt work. I had to "kill -9" it after getting this error. I have also repeatedly experienced this error using a tcl/mysqltcl script, the error occured after running the query "SHOW DATABASES;" In my search to solve this error I compiled mysql-5.1.40 from tar.gz. On this mysqld version the status wouldn't change to *** DEAD ***, the process would just hang forever. Processor: AMD Athlon(tm) 64 X2 Dual Core Processor 3600+ OS: Ubuntu 8.04 LTS 32bits Other steps I took in an attempt to solve the problem: - Rebooted OS - myisamchk'd all tables in all databases - mysqldumped all tables and recreated them from scratch - mv /var/lib/mysql /var/lib/mysql_old; /usr/bin/mysql_install_db; #and then imported the mysql dumps for the other db's to newly created db's
[24 Jan 2010 13:38]
Jasper E
Additional note: I have -not- been using mysqldump at all before running the "show databases;" query or before using phpmyadmin.
[24 Jan 2010 13:40]
Jasper E
Additional note (sorry): The mysql installation from ubuntu repository on which I also receive this error on is: 5.0.51a-3ubuntu5.4-log
[24 Jan 2010 13:44]
Jasper E
my.cnf
Attachment: my.cnf.txt (text/plain), 3.92 KiB.
[3 Feb 2010 10:03]
Sveta Smirnova
Thank you for the feedback. Could you please run SHOW FULL PROCESSLIST to see full query what hangs, then run SELECT COUNT(*) FROM the table affected?
[4 Feb 2010 21:06]
Jasper E
I'd rather avoid making this happen again as it's a production server. It's quite simple to not make it happen, just don't use phpMyAdmin and let things be. If it is crucial information in order to solve this bug I am however willing to try and recreate the error to give you the information needed, at a 'quiet' time (night). I can however tell you this error also occurred with the quite simple query "SHOW DATABASES;" that showed up in the processlist after running the "mysql::info $handle databases" command (mysqltcl library), that one would get the *** DEAD *** message next to it in "show processlist". One of my scripts used to run that query over and over again from many different threads for a purpose the PING query should've been used instead; checking if the server connection was still up. Then at a seemingly random day the encountered error started popping up. I hadn't changed anything to the scripts in weeks, and the "SHOW DATABASES;" bit had been in there for years. I then changed to the mysql::ping method and the problem disappeared. (But still occurs when using phpMyAdmin) Maybe I can run the COUNT query on whichever table "SHOW DATABASES;" consults? You'd have to tell me which table that is.
[4 Feb 2010 21:15]
Jasper E
Update: I have managed to recreate the error on mysqld 5.1.40-log running on the same server from a normal user account. (I copied the datadir /var/lib/mysql and started a second mysqld on the same box with different paths in my.cnf) I then spawned multiple clients repeatedly running the query "SHOW DATABASES;" from a simple shell script. At first this would immediately return a list of databases. After letting it run for a while, I noticed the first script hanging. Mind that as this is 5.1.40-log it doesn't show *** DEAD *** in the processlist, it just keeps running the query forever. mysql> show full processlist; +------+------+-----------+--------+---------+------+-----------+-----------------------+ | Id | User | Host | db | Command | Time | State | Info | +------+------+-----------+--------+---------+------+-----------+-----------------------+ | 1350 | root | localhost | db2 | Query | 996 | executing | SHOW DATABASES | | 3454 | root | localhost | db2 | Query | 279 | executing | SHOW DATABASES | | 3540 | root | localhost | db2 | Query | 0 | NULL | show full processlist | +------+------+-----------+--------+---------+------+-----------+-----------------------+
[4 Feb 2010 21:19]
Sveta Smirnova
Thank you for the feedback. Yes, SELECT COUNT(*) FROM INFORMATION_SCHEMA.SCHEMATA; should be good in this case.
[4 Feb 2010 21:35]
Jasper E
Some more info I forgot to mention: If for instance phpMyadmin got stuck, this would not just affect the phpMyadmin client, but also other clients. Those other client's wouldn't get *** DEAD *** next to their queries in processlist, as far as I can remember their SELECT queries just returned results, but not the right results they would get with normal behavior. (I'm running a mysql myisam database to which rows get inserted, clients then usually fetch the latest added rows. The biggest table currently has about 4 million rows in it) I can't really get into detail about what did, and what didnt work when there was a dead client in the processlist. I was always in too big of a hurry to get things going again.
[4 Feb 2010 21:36]
Jasper E
mysql> SELECT COUNT(*) FROM INFORMATION_SCHEMA.SCHEMATA; +----------+ | COUNT(*) | +----------+ | 9 | +----------+ 1 row in set (0.00 sec)
[4 Feb 2010 23:21]
Sveta Smirnova
Jasper, thank you for the feedback. Could you please run shell test script with version 5.1.43? I can not repeat described behavior with current development sources.
[5 Feb 2010 18:30]
Jasper E
I have succesfully recreated this error on mysqld-5.1.43. These are the steps I took (slightly modded): Downloaded: Generic Linux (Architecture Independent), Compressed TAR Archive 5.1.43 (mysql-5.1.43.tar.gz) ### Installation $ cd ~/downloads $ wget http://dev.mysql.com/get/Downloads/MySQL-5.1/mysql-5.1.43.tar.gz/from/http://mirror.leasew... $ tar -xzf *gz $ cd *43 $ ./configure --prefix=/home/foo/mysqld-5.1.43 --with-tcp-port=3600 --with-named-curses-libs=/lib/libncurses.so.5.6 $ make $ make install ### Configuration $ cd /home/foo/mysqld-5.1.43/ $ mkdir etc && mkdir data-dir && mkdir var $ cp ~/my.cnf ./etc/ # I will post this my.cnf as attachment below $ cd bin $ ./mysql_install_db --defaults-file=/home/foo/mysqld-5.1.43/etc/my.cnf # So I have been using a *fresh* database without adding any other databases myself! $ cd .. $ ./bin/mysqld_safe --defaults-file=/home/foo/mysqld-5.1.43/etc/my.cnf & Created file cmd.sh: #!/bin/bash echo "SHOW DATABASES;" | /home/foo/mysqld-5.1.43/bin/mysql --defaults-file=/home/foo/mysqld-5.1.43/etc/my.cnf -u root #EOF Created run.sh file: #!/bin/bash screen -dmS s01 watch --interval=1 sh cmd.sh screen -dmS s02 watch --interval=1 sh cmd.sh screen -dmS s03 watch --interval=1 sh cmd.sh screen -dmS s04 watch --interval=1 sh cmd.sh screen -dmS s05 watch --interval=1 sh cmd.sh screen -dmS s06 watch --interval=1 sh cmd.sh screen -dmS s07 watch --interval=1 sh cmd.sh screen -dmS s08 watch --interval=1 sh cmd.sh screen -dmS s09 watch --interval=1 sh cmd.sh screen -dmS s10 watch --interval=1 sh cmd.sh #EOF $ ./run.sh Waited a while: $ echo "show processlist;" | /home/foo/mysqld-5.1.43/bin/mysql --defaults-file=/home/foo/mysqld-5.1.43/etc/my.cnf -u root Id User Host db Command Time State Info 4 root localhost NULL Query 366 executing SHOW DATABASES 10 root localhost NULL Query 366 executing SHOW DATABASES 11 root localhost NULL Query 366 executing SHOW DATABASES 58 root localhost NULL Query 359 executing SHOW DATABASES 59 root localhost NULL Query 359 executing SHOW DATABASES 163 root localhost NULL Query 337 executing SHOW DATABASES 231 root localhost NULL Query 322 executing SHOW DATABASES 309 root localhost NULL Query 299 executing SHOW DATABASES 620 root localhost NULL Query 0 NULL show processlist ^ processes hanging
[5 Feb 2010 18:34]
Jasper E
my.cnf used on error recreation on mysqld-5.1.43
Attachment: my.cnf.txt (text/plain), 4.40 KiB.
[5 Feb 2010 18:38]
Jasper E
Output from "SHOW VARIABLES;" while threads are stuck.
Attachment: variables.txt (text/plain), 5.47 KiB.
[13 Feb 2010 11:53]
Sveta Smirnova
Jasper, thank you for the feedback. I still can not repeat described behavior. Could you please try if this is repeatable if you compile without option --with-named-cources-lib and if it is still repeatable attach gdb to the running process and issue command `thread apply all bt` it time when it hangs
[4 Mar 2010 21:34]
Jasper E
I have recompiled it without the ncurses option and I can confirm the error still occurs. $ gdb /home/foo/mysqld-5.1.43/libexec/mysqld 26273 GNU gdb 6.8-debian Copyright (C) 2008 Free Software Foundation, Inc. License GPLv3+: GNU GPL version 3 or later <http://gnu.org/licenses/gpl.html> This is free software: you are free to change and redistribute it. There is NO WARRANTY, to the extent permitted by law. Type "show copying" and "show warranty" for details. This GDB was configured as "i486-linux-gnu"... Attaching to program: /home/foo/mysqld-5.1.43/libexec/mysqld, process 26273 Reading symbols from /lib/tls/i686/cmov/libpthread.so.0...done. [Thread debugging using libthread_db enabled] [New Thread 0xb7c4e8d0 (LWP 26273)] [New Thread 0xa5c9db90 (LWP 26273)] [New Thread 0xa5cceb90 (LWP 26273)] [New Thread 0xa5cffb90 (LWP 26448)] [New Thread 0xa6358b90 (LWP 26273)] [New Thread 0xa6389b90 (LWP 26446)] [New Thread 0xa63bab90 (LWP 26443)] [New Thread 0xa63ebb90 (LWP 26273)] [New Thread 0xa641cb90 (LWP 26273)] [New Thread 0xa644db90 (LWP 26275)] Loaded symbols for /lib/tls/i686/cmov/libpthread.so.0 Reading symbols from /usr/lib/libz.so.1...done. Loaded symbols for /usr/lib/libz.so.1 Reading symbols from /lib/tls/i686/cmov/libdl.so.2...done. Loaded symbols for /lib/tls/i686/cmov/libdl.so.2 Reading symbols from /lib/tls/i686/cmov/libcrypt.so.1...done. Loaded symbols for /lib/tls/i686/cmov/libcrypt.so.1 Reading symbols from /lib/tls/i686/cmov/libnsl.so.1...done. Loaded symbols for /lib/tls/i686/cmov/libnsl.so.1 Reading symbols from /usr/lib/libstdc++.so.6...done. Loaded symbols for /usr/lib/libstdc++.so.6 Reading symbols from /lib/tls/i686/cmov/libm.so.6...done. Loaded symbols for /lib/tls/i686/cmov/libm.so.6 Reading symbols from /lib/libgcc_s.so.1...done. Loaded symbols for /lib/libgcc_s.so.1 Reading symbols from /lib/tls/i686/cmov/libc.so.6...done. Loaded symbols for /lib/tls/i686/cmov/libc.so.6 Reading symbols from /lib/ld-linux.so.2...done. Loaded symbols for /lib/ld-linux.so.2 0xb7f47410 in __kernel_vsyscall () (gdb) thread apply all bt Thread 10 (Thread 0xa644db90 (LWP 26275)): #0 0xb7f47410 in __kernel_vsyscall () #1 0xb7f33b1a in do_sigwait () from /lib/tls/i686/cmov/libpthread.so.0 #2 0xb7f33bbf in sigwait () from /lib/tls/i686/cmov/libpthread.so.0 #3 0x081cc4fc in signal_hand () #4 0xb7f2b4fb in start_thread () from /lib/tls/i686/cmov/libpthread.so.0 #5 0xb7d25e5e in clone () from /lib/tls/i686/cmov/libc.so.6 Thread 9 (Thread 0xa641cb90 (LWP 26273)): #0 0xb7f47410 in __kernel_vsyscall () #1 0xb7d1e881 in select () from /lib/tls/i686/cmov/libc.so.6 #2 0x081d1190 in handle_connections_sockets () #3 0x081d1ef3 in main () Thread 8 (Thread 0xa63ebb90 (LWP 26273)): #0 0xb7f47410 in __kernel_vsyscall () #1 0xb7d1e881 in select () from /lib/tls/i686/cmov/libc.so.6 #2 0x081d1190 in handle_connections_sockets () #3 0x081d1ef3 in main () Thread 7 (Thread 0xa63bab90 (LWP 26443)): #0 0xb7f47410 in __kernel_vsyscall () #1 0xb7f2faa5 in pthread_cond_wait@@GLIBC_2.3.2 () from /lib/tls/i686/cmov/libpthread.so.0 #2 0x081cc83d in one_thread_per_connection_end () #3 0x081d5cf0 in handle_one_connection () #4 0xb7f2b4fb in start_thread () from /lib/tls/i686/cmov/libpthread.so.0 #5 0xb7d25e5e in clone () from /lib/tls/i686/cmov/libc.so.6 Thread 6 (Thread 0xa6389b90 (LWP 26446)): #0 0xb7f47410 in __kernel_vsyscall () #1 0xb7f2faa5 in pthread_cond_wait@@GLIBC_2.3.2 () from /lib/tls/i686/cmov/libpthread.so.0 #2 0x081cc83d in one_thread_per_connection_end () #3 0x081d5cf0 in handle_one_connection () #4 0xb7f2b4fb in start_thread () from /lib/tls/i686/cmov/libpthread.so.0 #5 0xb7d25e5e in clone () from /lib/tls/i686/cmov/libc.so.6 Thread 5 (Thread 0xa6358b90 (LWP 26273)): #0 0xb7f47410 in __kernel_vsyscall () #1 0xb7d1e881 in select () from /lib/tls/i686/cmov/libc.so.6 #2 0x081d1190 in handle_connections_sockets () #3 0x081d1ef3 in main () Thread 4 (Thread 0xa5cffb90 (LWP 26448)): #0 0xb7f47410 in __kernel_vsyscall () #1 0xb7f2faa5 in pthread_cond_wait@@GLIBC_2.3.2 () from /lib/tls/i686/cmov/libpthread.so.0 #2 0x081cc83d in one_thread_per_connection_end () #3 0x081d5cf0 in handle_one_connection () #4 0xb7f2b4fb in start_thread () from /lib/tls/i686/cmov/libpthread.so.0 #5 0xb7d25e5e in clone () from /lib/tls/i686/cmov/libc.so.6 Thread 3 (Thread 0xa5cceb90 (LWP 26273)): #0 0xb7f47410 in __kernel_vsyscall () #1 0xb7d1e881 in select () from /lib/tls/i686/cmov/libc.so.6 #2 0x081d1190 in handle_connections_sockets () #3 0x081d1ef3 in main () Thread 2 (Thread 0xa5c9db90 (LWP 26273)): #0 0xb7f47410 in __kernel_vsyscall () #1 0xb7d1e881 in select () from /lib/tls/i686/cmov/libc.so.6 #2 0x081d1190 in handle_connections_sockets () #3 0x081d1ef3 in main () Thread 1 (Thread 0xb7c4e8d0 (LWP 26273)): #0 0xb7f47410 in __kernel_vsyscall () #1 0xb7d1e881 in select () from /lib/tls/i686/cmov/libc.so.6 #2 0x081d1190 in handle_connections_sockets () #3 0x081d1ef3 in main () #0 0xb7f47410 in __kernel_vsyscall ()
[4 Mar 2010 21:41]
Jasper E
Additional note: At the time of the gdb commands pasted above there were no active mysql client connections. I first killed the mysql clients and then issued the gdb commands.
[2 May 2010 11:05]
Jasper E
Sveta, Did I issue the gdb commands correctly? Is there anything else I can do to help fixing this issue?
[2 May 2010 13:35]
Sveta Smirnova
Jasper, yes, you issue commands correctly. Probably would be good if you attach gdb to client process, then see what happens in client. Also this can be duplicate of bug #52528 with repeatable test case, but we can not be sure until that bug is fixed and you check in your environment.
[2 May 2010 17:48]
Jasper E
Dear Sveta, I have attached gdb to a hanging mysql client, the result: $ gdb /home/foo/mysqld-5.1.43/bin/mysql 6722 GNU gdb 6.8-debian Copyright (C) 2008 Free Software Foundation, Inc. License GPLv3+: GNU GPL version 3 or later <http://gnu.org/licenses/gpl.html> This is free software: you are free to change and redistribute it. There is NO WARRANTY, to the extent permitted by law. Type "show copying" and "show warranty" for details. This GDB was configured as "i486-linux-gnu"... Attaching to program: /home/foo/mysqld-5.1.43/bin/mysql, process 6722 Reading symbols from /lib/libncurses.so.5...done. Loaded symbols for /lib/libncurses.so.5 Reading symbols from /lib/tls/i686/cmov/libpthread.so.0...done. [Thread debugging using libthread_db enabled] [New Thread 0xb7c456c0 (LWP 6722)] Loaded symbols for /lib/tls/i686/cmov/libpthread.so.0 Reading symbols from /home/foo/mysqld-5.1.43/lib/mysql/libmysqlclient.so.16...done. Loaded symbols for /home/foo/mysqld-5.1.43/lib/mysql/libmysqlclient.so.16 Reading symbols from /lib/tls/i686/cmov/libcrypt.so.1...done. Loaded symbols for /lib/tls/i686/cmov/libcrypt.so.1 Reading symbols from /lib/tls/i686/cmov/libnsl.so.1...done. Loaded symbols for /lib/tls/i686/cmov/libnsl.so.1 Reading symbols from /usr/lib/libz.so.1...done. Loaded symbols for /usr/lib/libz.so.1 Reading symbols from /usr/lib/libstdc++.so.6...done. Loaded symbols for /usr/lib/libstdc++.so.6 Reading symbols from /lib/tls/i686/cmov/libm.so.6...done. Loaded symbols for /lib/tls/i686/cmov/libm.so.6 Reading symbols from /lib/libgcc_s.so.1...done. Loaded symbols for /lib/libgcc_s.so.1 Reading symbols from /lib/tls/i686/cmov/libc.so.6...done. Loaded symbols for /lib/tls/i686/cmov/libc.so.6 Reading symbols from /lib/tls/i686/cmov/libdl.so.2...done. Loaded symbols for /lib/tls/i686/cmov/libdl.so.2 Reading symbols from /lib/ld-linux.so.2...done. Loaded symbols for /lib/ld-linux.so.2 0xb7fdf410 in __kernel_vsyscall () (gdb) thread apply all bt Thread 1 (Thread 0xb7c456c0 (LWP 6722)): #0 0xb7fdf410 in __kernel_vsyscall () #1 0xb7f9a973 in __read_nocancel () from /lib/tls/i686/cmov/libpthread.so.0 #2 0xb7f57c38 in vio_read () from /home/foo/mysqld-5.1.43/lib/mysql/libmysqlclient.so.16 #3 0xb7f57c8b in vio_read_buff () from /home/foo/mysqld-5.1.43/lib/mysql/libmysqlclient.so.16 #4 0xb7f5873f in my_real_read () from /home/foo/mysqld-5.1.43/lib/mysql/libmysqlclient.so.16 #5 0xb7f58a36 in my_net_read () from /home/foo/mysqld-5.1.43/lib/mysql/libmysqlclient.so.16 #6 0xb7f52598 in cli_safe_read () from /home/foo/mysqld-5.1.43/lib/mysql/libmysqlclient.so.16 #7 0xb7f54b45 in cli_read_query_result () from /home/foo/mysqld-5.1.43/lib/mysql/libmysqlclient.so.16 #8 0xb7f51b94 in mysql_real_query () from /home/foo/mysqld-5.1.43/lib/mysql/libmysqlclient.so.16 #9 0x080558c3 in mysql_real_query_for_lazy () #10 0x08057b0d in com_go () #11 0x08059709 in read_and_execute () #12 0x0805a745 in main () #0 0xb7fdf410 in __kernel_vsyscall () (gdb)
[2 May 2010 18:01]
Sveta Smirnova
Jasper, thank you for the update. Still looks similar to bug #52528. I suggest to wait when it is fixed.
[14 Jan 2013 18:32]
Matthew Lord
I am unable to repeat the issue using 5.5.29-Enterprise on Ubuntu 12.10 x86_64. I need a clear test case in order to try something else.