| Bug #15648 | MySQL freeze | ||
|---|---|---|---|
| Submitted: | 10 Dec 2005 5:28 | Modified: | 7 Mar 2006 19:03 |
| Reporter: | Mathieu Tremblay | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server | Severity: | S1 (Critical) |
| Version: | 5.0.18-max Via TCP/IP | OS: | Windows (Windows 2003 server) |
| Assigned to: | CPU Architecture: | Any | |
[10 Dec 2005 5:28]
Mathieu Tremblay
[10 Dec 2005 5:46]
Mathieu Tremblay
[mysqld] port=3307 basedir="C:/Program Files/MySQL/MySQL Server 4.1/" datadir="C:/Program Files/MySQL/MySQL Server 4.1/Data/" innodb_data_file_path=ibdata1:200M;ibdata2:100M:autoextend default-character-set=latin1 default-storage-engine=INNODB max_connections=100 query_cache_size=182M table_cache=256 tmp_table_size=184M thread_cache_size=8 key_buffer_size=285M read_buffer_size=2M read_rnd_buffer_size=256K sort_buffer_size=4M innodb_additional_mem_pool_size=12M innodb_flush_log_at_trx_commit=1 innodb_log_buffer_size=2M innodb_buffer_pool_size=800M innodb_log_file_size=50M innodb_thread_concurrency=8 skip-concurrent-insert language=french long_query_time=1 query_cache_limit=2M join_buffer_size=512k innodb_file_io_threads=6 interactive_timeout=28800 wait_timeout=28800 skip-host-cache skip-name-resolve
[10 Dec 2005 10:51]
MySQL Verification Team
You need to do some checks and monitoring your environment regarding the behavior of the TCP/IP ports. Please read the below Manual's section: http://dev.mysql.com/doc/refman/4.1/en/can-not-connect-to-server-on-windows.html Also about Winsock2 corruption: http://support.microsoft.com/kb/811259 then frequently take a look issuing from a command prompt: netstat -a and using the mysql client: show processlist; if you are using an application through the ISS, then verify the behavior of the persisten connections and compare with the processlist command. Try also to reduce the interactive_timeout and wait_timeout on server side. If you have a lot of tables using the InnoDB engine, avoid the use of command like show table status: http://bugs.mysql.com/bug.php?id=12071. Thanks in advance.
[10 Dec 2005 16:08]
Mathieu Tremblay
Its not that cannot connect etc. Its that when trying to connect or execute some query its done nothing and the client application or MySQL administrator freeze....
[10 Dec 2005 18:39]
Mathieu Tremblay
My Application is Delphi using ADO et MyODBC 3.51.12... KeepAlive Connection. The problem is suddently all the client application freeze because the MySQL Server freeze. The server look like to work fine when verifying but all application connected to the server are frooze and connot reconnect or connect new application also MySQL Administrator... Aborted_clients 28 Aborted_connects 5 Binlog_cache_disk_use 0 Binlog_cache_use 0 Bytes_received 21019304 Bytes_sent 811863602 Com_admin_commands 16 Com_alter_db 0 Com_alter_table 575 Com_analyze 0 Com_backup_table 0 Com_begin 1 Com_change_db 16 Com_change_master 0 Com_check 0 Com_checksum 0 Com_commit 8407 Com_create_db 0 Com_create_function 0 Com_create_index 0 Com_create_table 0 Com_dealloc_sql 0 Com_delete 1186 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 4613 Com_insert_select 13573 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 0 Com_replace_select 0 Com_reset 0 Com_restore_table 0 Com_revoke 0 Com_revoke_all 0 Com_rollback 4 Com_savepoint 0 Com_select 75634 Com_set_option 16965 Com_show_binlog_events 0 Com_show_binlogs 0 Com_show_charsets 0 Com_show_collations 0 Com_show_column_types 0 Com_show_create_db 0 Com_show_create_table 50 Com_show_databases 5 Com_show_errors 0 Com_show_fields 250 Com_show_grants 0 Com_show_innodb_status 1926 Com_show_keys 620 Com_show_logs 0 Com_show_master_status 1 Com_show_ndb_status 0 Com_show_new_master 0 Com_show_open_tables 0 Com_show_privileges 0 Com_show_processlist 21 Com_show_slave_hosts 0 Com_show_slave_status 0 Com_show_status 1928 Com_show_storage_engines 0 Com_show_tables 60 Com_show_variables 8 Com_show_warnings 0 Com_slave_start 0 Com_slave_stop 0 Com_stmt_close 0 Com_stmt_execute 0 Com_stmt_prepare 0 Com_stmt_reset 0 Com_stmt_send_long_data 0 Com_truncate 0 Com_unlock_tables 0 Com_update 10841 Com_update_multi 247 Connections 82 Created_tmp_disk_tables 1568 Created_tmp_files 553 Created_tmp_tables 18206 Delayed_errors 0 Delayed_insert_threads 0 Delayed_writes 0 Flush_commands 1 Handler_commit 17966 Handler_delete 0 Handler_discover 0 Handler_read_first 24564 Handler_read_key 24805185 Handler_read_next 51514007 Handler_read_prev 0 Handler_read_rnd 12067395 Handler_read_rnd_next 115994478 Handler_rollback 44 Handler_update 45922 Handler_write 24143935 Key_blocks_not_flushed 0 Key_blocks_unused 255774 Key_blocks_used 1 Key_read_requests 4 Key_reads 2 Key_write_requests 0 Key_writes 0 Max_used_connections 13 Not_flushed_delayed_rows 0 Open_files 2 Open_streams 0 Open_tables 61 Opened_tables 3505 Qcache_free_blocks 481 Qcache_free_memory 190090752 Qcache_hits 21807 Qcache_inserts 50888 Qcache_lowmem_prunes 0 Qcache_not_cached 24746 Qcache_queries_in_cache 655 Qcache_total_blocks 1838 Questions 158796 Rpl_status NULL Select_full_join 288 Select_full_range_join 0 Select_range 472 Select_range_check 0 Select_scan 23861 Slave_open_temp_tables 0 Slave_retried_transactions 0 Slave_running OFF Slow_launch_threads 0 Slow_queries 36 Sort_merge_passes 0 Sort_range 4803 Sort_rows 12197043 Sort_scan 12667 Table_locks_immediate 253835 Table_locks_waited 0 Threads_cached 0 Threads_connected 13 Threads_created 16 Threads_running 1 Uptime 67631
[10 Dec 2005 22:51]
Mathieu Tremblay
The error occurs always between 5 PM and 7 PM. The server still running, we can connect but cannot execute any query or fetching... Impossible the shutdown the MySQL server, i have to kill the process, and then I can restart de MySQL Server.
[11 Dec 2005 12:00]
Heikki Tuuri
Mathieu, please post: SHOW PROCESSLIST; during the hang. Regards, Heikki
[13 Dec 2005 5:10]
Mathieu Tremblay
6 client_restomat 192.168.0.102:4236 bd_restomat Sleep 109 [NULL] 7 client_restomat 192.168.0.103:4766 bd_restomat Sleep 1185 [NULL] 8 client_restomat 192.168.0.110:3558 bd_restomat Sleep 235 [NULL] 9 client_restomat 192.168.0.101:2640 bd_restomat Sleep 829 [NULL] 10 client_restomat 192.168.0.107:1109 bd_restomat Sleep 315 [NULL] 28 client_restomat 192.168.0.104:3061 bd_restomat Sleep 3843 [NULL] 29 client_restomat 192.168.0.106:1656 bd_restomat Sleep 7120 [NULL] 41 client_restomat 192.168.0.109:1221 bd_restomat Sleep 1697 [NULL] 59 admin 132.208.27.211:2161 bd_restomat Sleep 7031 [NULL] 61 admin 132.208.27.211:2163 bd_restomat Sleep 5125 [NULL] 63 admin 127.0.0.1:1606 [NULL] Sleep 9 [NULL] 65 admin 127.0.0.1:1608 [NULL] Query 0 [NULL] SHOW PROCESSLIST
[13 Dec 2005 13:43]
MySQL Verification Team
Can you provide an executable like your application which can reproduce the issue reported. I will test it against the 4.1. from source with the fix applied for the bug #12071? Thanks in advance.
[14 Dec 2005 3:56]
Mathieu Tremblay
I'm not sure I understand what are you expect from me... There no application the can produce this issue... The SHOW PROCESSLIST was the the process list before hang because whan MySQL server hang, there no way to get the acual process list.... And My application has no SHOW DATABASES SHOW TABLES query... Then problem did not occur with that kind of query. The Application are restrict to SELECT, UPDATE, INSERT, ALTER, LOCK_TABLES Privileges only on one specific shema.
[21 Dec 2005 14:27]
MySQL Verification Team
The problem here is I need a test case for repeat the behavior reported, there are however bugs already reported with the same behavior like you are reporting. If you can't provide a repeatable test case then I will ask you for to try 4.1.17 when this will be released.
[22 Jan 2006 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".
[3 Feb 2006 22:09]
Mathieu Tremblay
The problem occurs again with the version 5.0.18. The database server freeze when trying to shutdown instance I get an error "Unable to shutdown the MySQL server" I have to kill the process, and then I can restart de MySQL Server. Hope that problem will be corrected very very very soon !!!!
[4 Feb 2006 1:24]
Mathieu Tremblay
Precision: I can connect on the MySQL Server but cannot doing any query, its freeze !!
[4 Feb 2006 17:53]
Mathieu Tremblay
This is the SHOW PROCESSLIST; Its look like two ALTER TABLE at the same time become the MySQL Server in DEAD LOCK. 23 client_restomat 192.168.0.101:1734 bd_restomat query 547 waiting for table SELECT DISTINCT 0 AS Numero,RefTable,COUNT(Numero) AS NBClients,SUM(Total) AS Total,Date FROM Commande WHERE Commande.RefServeur=222 GROUP BY RefTable ORDER BY Date DESC 24 client_restomat 192.168.0.102:1056 bd_restomat query 595 rename result table ALTER TABLE Commande AUTO_INCREMENT=472 25 client_restomat 192.168.0.103:3917 bd_restomat query 243 waiting for table SELECT DISTINCT 0 AS Numero,RefTable,COUNT(Numero) AS NBClients,SUM(Total) AS Total,Date FROM Commande WHERE Commande.RefServeur=111 GROUP BY RefTable ORDER BY Date DESC 26 client_restomat 192.168.0.104:1506 bd_restomat query 243 waiting for table SELECT DISTINCT 0 AS Numero,RefTable,COUNT(Numero) AS NBClients,SUM(Total) AS Total,Date FROM Commande WHERE Commande.RefServeur=333 GROUP BY RefTable ORDER BY Date DESC 27 client_restomat 192.168.0.105:3087 bd_restomat query 519 waiting for table SELECT DISTINCT Numero,RefClientLivraison AS RefTable,COUNT(Numero) AS NBClients,SUM(Total) AS Total,Date FROM Commande WHERE Commande.RefServeur=999 GROUP BY RefTable ORDER BY Date DESC 28 client_restomat 192.168.0.106:2047 bd_restomat query 370 waiting for table SELECT DISTINCT Numero,RefClientLivraison AS RefTable,COUNT(Numero) AS NBClients,SUM(Total) AS Total,Date FROM Commande WHERE Commande.RefServeur=555 GROUP BY RefTable ORDER BY Date DESC 29 client_restomat 192.168.0.110:1057 bd_restomat query 595 rename result table ALTER TABLE Commande AUTO_INCREMENT=536 30 client_restomat 192.168.0.109:3287 bd_restomat query 164 waiting for table SELECT DISTINCT Numero,RefClientLivraison AS RefTable,COUNT(Numero) AS NBClients,SUM(Total) AS Total,Date FROM Commande WHERE Commande.RefServeur=666 GROUP BY RefTable ORDER BY Date DESC 31 client_restomat 192.168.0.108:1270 bd_restomat query 117 waiting for table SELECT DISTINCT Numero,RefClientLivraison AS RefTable,COUNT(Numero) AS NBClients,SUM(Total) AS Total,Date FROM Commande WHERE Commande.RefServeur=123 GROUP BY RefTable ORDER BY Date DESC 32 client_restomat 192.168.0.107:2234 bd_restomat query 370 waiting for table SELECT DISTINCT Numero,RefClientLivraison AS RefTable,COUNT(Numero) AS NBClients,SUM(Total) AS Total,Date FROM Commande WHERE Commande.RefServeur=321 GROUP BY RefTable ORDER BY Date DESC
[4 Feb 2006 23:12]
Mathieu Tremblay
I tought that the bug 12071 was corrected like its said on the link: http://dev.mysql.com/doc/refman/5.0/en/news-5-0-18.html I think you should test again with simultaneous ALTER TABLE query on the same table. Thanks
[7 Mar 2006 19:03]
MySQL Verification Team
Thank you for the bug report. Related to bug: http://bugs.mysql.com/bug.php?id=17264 according discussion with Shane.
