| Bug #13034 | Threads persisting in 'Waiting for tables' state | ||
|---|---|---|---|
| Submitted: | 7 Sep 2005 11:58 | Modified: | 26 Dec 2005 9:21 |
| Reporter: | Adam Newby | Email Updates: | |
| Status: | No Feedback | Impact on me: | |
| Category: | MySQL Server | Severity: | S1 (Critical) |
| Version: | mysql-4.1.11-Debian_4 (Source distributi | OS: | Linux (Linux 2.4.24) |
| Assigned to: | CPU Architecture: | Any | |
[24 Sep 2005 13:36]
Valeriy Kravchuk
Thank you for a bug report. Please, send your my.cnf file content and the results of ulimit -a command executed by the user who runs mysqld.
[26 Sep 2005 9:43]
Adam Newby
my.cnf file
Attachment: my.cnf-2005-09-06 (application/octet-stream, text), 2.79 KiB.
[26 Sep 2005 12:08]
Valeriy Kravchuk
OK, you have in the ulimit -a results: open files (-n) 1024 and because all InnoDB-related options are commented out in your my.cnf, I assume you use MyISAM tables. Am I right? So, how many tables do you have in all your databases? How many simultaneous connections you usually have while observing threads in this 'Waiting for tables' state? Can you, please, try to increase the number of files allowed to opened simultaneously (to, say, 2048, using ulimit) and inform about the results - are there any such messages after this change, and if they still happen, how often.
[5 Oct 2005 9:50]
Gabriel Huber
We can also reproduce this bug. We first saw it selecting two databases in two phpmyadmin windows. But it can also be reproduced with two open mysql command line clients, each submitting a "SHOW TABLE STATUS FROM ´foo´" at the same time. After that no querys that refer to tables are answered, but the server is not completly locked up - I can still view the running processes. One of the two processes and all new processes remain int 'Opening table' state. The server is running on windows 2003 SERVER, SP 1, MySQL 4.1.14. Tables in first database (server) had 68 tables - all tables are InnoDB except one MyISAM. Same for the second database (old_server) with 38 tables. SHOW VARIABLES, SHOW STATUS, PROCESSLIST in Attached File. Also Attached my.cnf .
[5 Oct 2005 9:55]
Gabriel Huber
Could not attach file: here it is: mysql> SHOW STATUS; +----------------------------+-----------+ | Variable_name | Value | +----------------------------+-----------+ | Aborted_clients | 4 | | Aborted_connects | 2 | | Binlog_cache_disk_use | 1 | | Binlog_cache_use | 72 | | Bytes_received | 9716482 | | Bytes_sent | 223258893 | | Com_admin_commands | 19055 | | Com_alter_db | 0 | | Com_alter_table | 0 | | Com_analyze | 0 | | Com_backup_table | 0 | | Com_begin | 184 | | Com_change_db | 13550 | | Com_change_master | 0 | | Com_check | 0 | | Com_checksum | 0 | | Com_commit | 181 | | Com_create_db | 0 | | Com_create_function | 0 | | Com_create_index | 0 | | Com_create_table | 0 | | Com_dealloc_sql | 0 | | Com_delete | 1644 | | Com_delete_multi | 0 | | Com_do | 0 | | Com_drop_db | 0 | | Com_drop_function | 0 | | Com_drop_index | 0 | | Com_drop_table | 0 | | Com_drop_user | 0 | | Com_execute_sql | 0 | | Com_flush | 0 | | Com_grant | 0 | | Com_ha_close | 0 | | Com_ha_open | 0 | | Com_ha_read | 0 | | Com_help | 0 | | Com_insert | 1334 | | Com_insert_select | 12 | | Com_kill | 0 | | Com_load | 0 | | Com_load_master_data | 0 | | Com_load_master_table | 0 | | Com_lock_tables | 0 | | Com_optimize | 0 | | Com_preload_keys | 0 | | Com_prepare_sql | 0 | | Com_purge | 0 | | Com_purge_before_date | 0 | | Com_rename_table | 0 | | Com_repair | 0 | | Com_replace | 382 | | Com_replace_select | 0 | | Com_reset | 0 | | Com_restore_table | 0 | | Com_revoke | 0 | | Com_revoke_all | 0 | | Com_rollback | 0 | | Com_savepoint | 0 | | Com_select | 21315 | | Com_set_option | 9538 | | Com_show_binlog_events | 0 | | Com_show_binlogs | 0 | | Com_show_charsets | 0 | | Com_show_collations | 9529 | | Com_show_column_types | 0 | | Com_show_create_db | 0 | | Com_show_create_table | 0 | | Com_show_databases | 0 | | Com_show_errors | 0 | | Com_show_fields | 958 | | Com_show_grants | 0 | | Com_show_innodb_status | 0 | | Com_show_keys | 0 | | Com_show_logs | 0 | | Com_show_master_status | 0 | | Com_show_new_master | 0 | | Com_show_open_tables | 0 | | Com_show_privileges | 0 | | Com_show_processlist | 115 | | Com_show_slave_hosts | 0 | | Com_show_slave_status | 0 | | Com_show_status | 89 | | Com_show_storage_engines | 0 | | Com_show_tables | 2 | | Com_show_variables | 9529 | | Com_show_warnings | 2 | | Com_slave_start | 0 | | Com_slave_stop | 0 | | Com_stmt_prepare | 0 | | Com_stmt_execute | 0 | | Com_stmt_send_long_data | 0 | | Com_stmt_reset | 0 | | Com_stmt_close | 0 | | Com_truncate | 0 | | Com_unlock_tables | 0 | | Com_update | 5009 | | Com_update_multi | 0 | | Connections | 3162 | | Created_tmp_disk_tables | 6 | | Created_tmp_files | 41 | | Created_tmp_tables | 1495 | | Delayed_errors | 0 | | Delayed_insert_threads | 1 | | Delayed_writes | 190 | | Flush_commands | 1 | | Handler_commit | 184 | | Handler_delete | 1183 | | Handler_discover | 0 | | Handler_read_first | 11511 | | Handler_read_key | 277582 | | Handler_read_next | 199245 | | Handler_read_prev | 9148 | | Handler_read_rnd | 65473 | | Handler_read_rnd_next | 1885084 | | Handler_rollback | 10916 | | Handler_update | 5918 | | Handler_write | 22444 | | Key_blocks_not_flushed | 0 | | Key_blocks_unused | 68631 | | Key_blocks_used | 1136 | | Key_read_requests | 479008 | | Key_reads | 12975 | | Key_write_requests | 3377 | | Key_writes | 2770 | | Max_used_connections | 10 | | Not_flushed_delayed_rows | 0 | | Open_files | 55 | | Open_streams | 0 | | Open_tables | 68 | | Opened_tables | 1018 | | Qcache_free_blocks | 182 | | Qcache_free_memory | 49316824 | | Qcache_hits | 16957 | | Qcache_inserts | 11617 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 9696 | | Qcache_queries_in_cache | 770 | | Qcache_total_blocks | 1800 | | Questions | 102931 | | Rpl_status | NULL | | Select_full_join | 9 | | Select_full_range_join | 0 | | Select_range | 2143 | | Select_range_check | 0 | | Select_scan | 11639 | | Slave_open_temp_tables | 0 | | Slave_running | OFF | | Slave_retried_transactions | 0 | | Slow_launch_threads | 0 | | Slow_queries | 13520 | | Sort_merge_passes | 0 | | Sort_range | 1384 | | Sort_rows | 121961 | | Sort_scan | 2572 | | Table_locks_immediate | 47037 | | Table_locks_waited | 1 | | Threads_cached | 5 | | Threads_connected | 6 | | Threads_created | 10 | | Threads_running | 4 | | Uptime | 47271 | +----------------------------+-----------+ 162 rows in set (0.00 sec)
[5 Oct 2005 9:59]
Gabriel Huber
http://www.uo-mittelerde.ch/mysqlbug.txt
[5 Oct 2005 19:12]
Guilherme Magalhaes
You got it Gabriel. As soon as we noticed that the server where crashing by SHOW TABLE STATUS FROM sent by phpMyAdmin, we shut it down. It's been this way for two months now. We simply lost all credibility on MySQL due to simple, easy to find, bugs like this. But we need to offer reliable database to our users. Any sugestions? It also happens on Windows 2003 SP1 servers.
[26 Oct 2005 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".
[26 Nov 2005 9:21]
Valeriy Kravchuk
I want to inform the original reporter that similar bug http://bugs.mysql.com/bug.php?id=12071 is finally verified on Windows 2003. Others may be interested in this information too. As that bug was affecting a lot of our customers, it will be fixed in the nearest future, I believe. I hope the fix will solve other similar problems like described here.
[27 Dec 2005 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".
Description: We periodically see threads blocking in the 'Waiting for tables' state, despite no other thread having issued a FLUSH TABLES or ALTER TABLE statement. These threads can block for anything up to a number of hours. While they are blocked, updates and selects continue to be processed on the same table. Sometimes a thread in this state will switch to 'Reopen tables' and then back to 'Waiting for tables'. I've seen this problem on and off, with different versions of MySQL, since February 2002, when I was corresponding with Monty Widenius about it. I've also seen an existing MySQL bug report that sounds identical http//bugs.mysql.com/bug.php?id=12071 I will attach an example process list after submitting the bug. How to repeat: It's not clear how to repeat this problem. Sometimes we can go for weeks without it happening, sometimes it keeps cropping up. Suggested fix: No known fix atm.