Bug #16516 using mysqldump occasionally hangs mysql v4.1.15/16
Submitted: 15 Jan 2006 17:28 Modified: 21 Feb 2006 11:42
Reporter: Kyle Beckham Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:v4.1.16 OS:Windows (windows 2003 sp1)
Assigned to: CPU Architecture:Any

[15 Jan 2006 17:28] Kyle Beckham
Description:
We have used mysqldump for over 2 years now to dump our customers mysql databases.

We use this format:  
mysqldump.exe  -Q -q --opt -uxxxx -pxxxx -rD:\Bak\catalog.sql catalog
The script kicks off a cmd prompt to run each mysqldump one after the other.
The first one has to complete before the next one it run.

Not until we upgraded to v4.1.15 did we start have a problem. Occasionally the mysqldump script will hang and the mysql server will hang during a dump.  All users websites hitting the mysql database will timeout.  this happened here and there since v4.1.15 but it has happened almost every night the past two weeks.
We did upgrade to v4.1.16 last weds, but still the same issue happens.  

We can not seam to get this resolved.  Here is some of the outout we have at the time of a hang:

How to repeat:
not sure, we can run the backup script 10 times in a row and nothing hangs. Then at night here and there it hangs.
[15 Jan 2006 17:29] Kyle Beckham
+----------------------------+------------+
| Variable_name              | Value      |
+----------------------------+------------+
| Aborted_clients            | 373        |
| Aborted_connects           | 719        |
| Binlog_cache_disk_use      | 0          |
| Binlog_cache_use           | 0          |
| Bytes_received             | 438327192  |
| Bytes_sent                 | 2320935611 |
| Com_admin_commands         | 0          |
| Com_alter_db               | 0          |
| Com_alter_table            | 0          |
| Com_analyze                | 0          |
| Com_backup_table           | 0          |
| Com_begin                  | 0          |
| Com_change_db              | 38254      |
| Com_change_master          | 0          |
| Com_check                  | 0          |
| Com_checksum               | 0          |
| Com_commit                 | 0          |
| Com_create_db              | 0          |
| Com_create_function        | 0          |
| Com_create_index           | 0          |
| Com_create_table           | 0          |
| Com_dealloc_sql            | 0          |
| Com_delete                 | 19639      |
| 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                 | 57756      |
| Com_insert_select          | 0          |
| Com_kill                   | 0          |
| Com_load                   | 0          |
| Com_load_master_data       | 0          |
| Com_load_master_table      | 0          |
| Com_lock_tables            | 33         |
| Com_optimize               | 10         |
| 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               | 0          |
| Com_savepoint              | 0          |
| Com_select                 | 2072977    |
| Com_set_option             | 1638       |
| 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         | 14         |
| Com_show_create_table      | 1612       |
| Com_show_databases         | 7          |
| Com_show_errors            | 0          |
| Com_show_fields            | 1675       |
| Com_show_grants            | 0          |
| Com_show_innodb_status     | 0          |
| Com_show_keys              | 0          |
| Com_show_logs              | 0          |
| Com_show_master_status     | 0          |
| Com_show_ndb_status        | 0          |
| Com_show_new_master        | 0          |
| Com_show_open_tables       | 0          |
| Com_show_privileges        | 0          |
| Com_show_processlist       | 0          |
| Com_show_slave_hosts       | 0          |
| Com_show_slave_status      | 0          |
| Com_show_status            | 19         |
| Com_show_storage_engines   | 0          |
| Com_show_tables            | 14814      |
| Com_show_variables         | 2          |
| 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          | 34         |
| Com_update                 | 62683      |
| Com_update_multi           | 0          |
| Connections                | 23793      |
| Created_tmp_disk_tables    | 3240       |
| Created_tmp_files          | 978        |
| Created_tmp_tables         | 58543      |
| Delayed_errors             | 0          |
| Delayed_insert_threads     | 0          |
| Delayed_writes             | 0          |
| Flush_commands             | 1          |
| Handler_commit             | 23         |
| Handler_delete             | 13171      |
| Handler_discover           | 0          |
| Handler_read_first         | 109593     |
| Handler_read_key           | 5127350    |
| Handler_read_next          | 9850828    |
| Handler_read_prev          | 0          |
| Handler_read_rnd           | 2033649    |
| Handler_read_rnd_next      | 795625301  |
| Handler_rollback           | 2232       |
| Handler_update             | 203034     |
| Handler_write              | 298792     |
| Key_blocks_not_flushed     | 0          |
| Key_blocks_unused          | 35927      |
| Key_blocks_used            | 542        |
| Key_read_requests          | 6848991    |
| Key_reads                  | 25190      |
| Key_write_requests         | 183448     |
| Key_writes                 | 124749     |
| Max_used_connections       | 827        |
| Not_flushed_delayed_rows   | 0          |
| Open_files                 | 37         |
| Open_streams               | 0          |
| Open_tables                | 64         |
| Opened_tables              | 6647       |
| Qcache_free_blocks         | 0          |
| Qcache_free_memory         | 0          |
| Qcache_hits                | 0          |
| Qcache_inserts             | 0          |
| Qcache_lowmem_prunes       | 0          |
| Qcache_not_cached          | 0          |
| Qcache_queries_in_cache    | 0          |
| Qcache_total_blocks        | 0          |
| Questions                  | 2293217    |
| Rpl_status                 | NULL       |
| Select_full_join           | 2690       |
| Select_full_range_join     | 300        |
| Select_range               | 36218      |
| Select_range_check         | 206        |
| Select_scan                | 1032476    |
| Slave_open_temp_tables     | 0          |
| Slave_retried_transactions | 0          |
| Slave_running              | OFF        |
| Slow_launch_threads        | 0          |
| Slow_queries               | 0          |
| Sort_merge_passes          | 0          |
| Sort_range                 | 124490     |
| Sort_rows                  | 2381870    |
| Sort_scan                  | 636130     |
| Table_locks_immediate      | 3104631    |
| Table_locks_waited         | 18         |
| Threads_cached             | 0          |
| Threads_connected          | 827        |
| Threads_created            | 23792      |
| Threads_running            | 826        |
| Uptime                     | 211864     |
+----------------------------+------------+
163 rows in set (0.00 sec)
[15 Jan 2006 17:30] Kyle Beckham
mysql> show variables;
+---------------------------------+-----------------------------+
| Variable_name                   | Value                       |
+---------------------------------+-----------------------------+
| back_log                        | 50                          |
| basedir                         | D:\MySQL\                   |
| binlog_cache_size               | 32768                       |
| bulk_insert_buffer_size         | 8388608                     |
| character_set_client            | latin1                      |
| character_set_connection        | latin1                      |
| character_set_database          | latin1                      |
| character_set_results           | latin1                      |
| character_set_server            | latin1                      |
| character_set_system            | utf8                        |
| character_sets_dir              | D:\MySQL\share\charsets/    |
| collation_connection            | latin1_swedish_ci           |
| collation_database              | latin1_swedish_ci           |
| collation_server                | latin1_swedish_ci           |
| concurrent_insert               | ON                          |
| connect_timeout                 | 5                           |
| datadir                         | D:\MySQL\Data\              |
| date_format                     | %Y-%m-%d                    |
| datetime_format                 | %Y-%m-%d %H:%i:%s           |
| default_week_format             | 0                           |
| delay_key_write                 | ON                          |
| delayed_insert_limit            | 100                         |
| delayed_insert_timeout          | 300                         |
| delayed_queue_size              | 1000                        |
| expire_logs_days                | 0                           |
| flush                           | OFF                         |
| flush_time                      | 1800                        |
| ft_boolean_syntax               | + -><()~*:""&|              |
| ft_max_word_len                 | 84                          |
| ft_min_word_len                 | 4                           |
| ft_query_expansion_limit        | 20                          |
| ft_stopword_file                | (built-in)                  |
| group_concat_max_len            | 1024                        |
| have_archive                    | YES                         |
| have_bdb                        | NO                          |
| have_blackhole_engine           | NO                          |
| have_compress                   | YES                         |
| have_crypt                      | NO                          |
| have_csv                        | NO                          |
| have_example_engine             | NO                          |
| have_geometry                   | YES                         |
| have_innodb                     | YES                         |
| have_isam                       | NO                          |
| have_ndbcluster                 | NO                          |
| have_openssl                    | NO                          |
| have_query_cache                | YES                         |
| have_raid                       | NO                          |
| have_rtree_keys                 | YES                         |
| have_symlink                    | NO                          |
| init_connect                    |                             |
| init_file                       |                             |
| init_slave                      |                             |
| innodb_additional_mem_pool_size | 2097152                     |
| innodb_autoextend_increment     | 8                           |
| innodb_buffer_pool_awe_mem_mb   | 0                           |
| innodb_buffer_pool_size         | 80740352                    |
| innodb_data_file_path           | ibdata1:10M:autoextend      |
| innodb_data_home_dir            |                             |
| innodb_fast_shutdown            | ON                          |
| innodb_file_io_threads          | 4                           |
| innodb_file_per_table           | OFF                         |
[15 Jan 2006 17:30] Kyle Beckham
| innodb_flush_log_at_trx_commit  | 1                           |
| innodb_flush_method             |                             |
| innodb_force_recovery           | 0                           |
| innodb_lock_wait_timeout        | 50                          |
| innodb_locks_unsafe_for_binlog  | OFF                         |
| innodb_log_arch_dir             |                             |
| innodb_log_archive              | OFF                         |
| innodb_log_buffer_size          | 1048576                     |
| innodb_log_file_size            | 40894464                    |
| innodb_log_files_in_group       | 2                           |
| innodb_log_group_home_dir       | .\                          |
| innodb_max_dirty_pages_pct      | 90                          |
| innodb_max_purge_lag            | 0                           |
| innodb_mirrored_log_groups      | 1                           |
| innodb_open_files               | 300                         |
| innodb_table_locks              | ON                          |
| innodb_thread_concurrency       | 8                           |
| interactive_timeout             | 28800                       |
| join_buffer_size                | 131072                      |
| key_buffer_size                 | 41943040                    |
| key_cache_age_threshold         | 300                         |
| key_cache_block_size            | 1024                        |
| key_cache_division_limit        | 100                         |
| language                        | D:\MySQL\share\english\     |
| large_files_support             | ON                          |
| license                         | GPL                         |
| local_infile                    | ON                          |
| log                             | OFF                         |
| log_bin                         | OFF                         |
| log_error                       | .\esvr-sql9.err             |
| log_slave_updates               | OFF                         |
| log_slow_queries                | OFF                         |
| log_update                      | OFF                         |
| log_warnings                    | 1                           |
| long_query_time                 | 10                          |
| low_priority_updates            | OFF                         |
| lower_case_file_system          | OFF                         |
| lower_case_table_names          | 1                           |
| max_allowed_packet              | 1048576                     |
| max_binlog_cache_size           | 4294967295                  |
| max_binlog_size                 | 1073741824                  |
| max_connect_errors              | 10                          |
| max_connections                 | 2038                        |
| max_delayed_threads             | 20                          |
| max_error_count                 | 64                          |
[15 Jan 2006 17:30] Kyle Beckham
| max_heap_table_size             | 16777216                    |
| max_insert_delayed_threads      | 20                          |
| max_join_size                   | 4294967295                  |
| max_length_for_sort_data        | 1024                        |
| max_relay_log_size              | 0                           |
| max_seeks_for_key               | 4294967295                  |
| max_sort_length                 | 1024                        |
| max_tmp_tables                  | 32                          |
| max_user_connections            | 0                           |
| max_write_lock_count            | 4294967295                  |
| myisam_data_pointer_size        | 4                           |
| myisam_max_extra_sort_file_size | 107374182400                |
| myisam_max_sort_file_size       | 107374182400                |
| myisam_recover_options          | OFF                         |
| myisam_repair_threads           | 1                           |
| myisam_sort_buffer_size         | 54525952                    |
| myisam_stats_method             | nulls_unequal               |
| net_buffer_length               | 16384                       |
| net_read_timeout                | 30                          |
| net_retry_count                 | 10                          |
| net_write_timeout               | 60                          |
| new                             | OFF                         |
| old_passwords                   | ON                          |
| open_files_limit                | 2048                        |
| pid_file                        | D:\MySQL\Data\esvr-sql9.pid |
| port                            | 3306                        |
| preload_buffer_size             | 32768                       |
| protocol_version                | 10                          |
| query_alloc_block_size          | 8192                        |
| query_cache_limit               | 1048576                     |
| query_cache_min_res_unit        | 4096                        |
| query_cache_size                | 0                           |
| query_cache_type                | ON                          |
| query_cache_wlock_invalidate    | OFF                         |
| query_prealloc_size             | 8192                        |
| range_alloc_block_size          | 2048                        |
| read_buffer_size                | 61440                       |
| read_only                       | OFF                         |
| read_rnd_buffer_size            | 258048                      |
| relay_log_purge                 | ON                          |
| relay_log_space_limit           | 0                           |
| rpl_recovery_rank               | 0                           |
| secure_auth                     | OFF                         |
| shared_memory                   | OFF                         |
| shared_memory_base_name         | MYSQL                       |
| server_id                       | 0                           |
| skip_external_locking           | ON                          |
| skip_networking                 | OFF                         |
| skip_show_database              | OFF                         |
| slave_net_timeout               | 3600                        |
| slave_transaction_retries       | 0                           |
| slow_launch_time                | 2                           |
| sort_buffer_size                | 262136                      |
| sql_mode                        |                             |
| sql_notes                       | ON                          |
| sql_warnings                    | ON                          |
| storage_engine                  | InnoDB                      |
| sync_binlog                     | 0                           |
| sync_frm                        | ON                          |
| sync_replication                | 0                           |
| sync_replication_slave_id       | 0                           |
| sync_replication_timeout        | 0                           |
| system_time_zone                | Eastern Standard Time       |
| table_cache                     | 64                          |
| table_type                      | InnoDB                      |
| thread_cache_size               | 0                           |
| thread_stack                    | 196608                      |
| time_format                     | %H:%i:%s                    |
| time_zone                       | SYSTEM                      |
| tmp_table_size                  | 27262976                    |
| tmpdir                          |                             |
| transaction_alloc_block_size    | 8192                        |
| transaction_prealloc_size       | 4096                        |
| tx_isolation                    | REPEATABLE-READ             |
| version                         | 4.1.16                      |
| version_comment                 | Official MySQL binary       |
| version_compile_machine         | ia32                        |
| version_compile_os              | Win32                       |
| wait_timeout                    | 28800                       |
+---------------------------------+-----------------------------+
185 rows in set (0.00 sec)
[17 Jan 2006 10:52] Valeriy Kravchuk
Thank you for a problem report. Is there anything unusual in your error log during these hangs? Can you, please, try to perfrom SHOW PROCESSLIST command from a separate session during the mysqldump and send the last results before it hangs?
[19 Jan 2006 4:24] Kyle Beckham
the process list

Attachment: mysql_issue_b.txt (text/plain), 29.08 KiB.

[19 Jan 2006 4:27] Kyle Beckham
We just do not understand why this hanging is happening. Is there a better way that we should be running the dumps?
[19 Jan 2006 4:33] Kyle Beckham
I forgot to add that we have not see anything unusual in the logs,
[19 Jan 2006 7:28] Valeriy Kravchuk
Are there any other active sessions while you are trying to dump? Please, do what I asked you to: Try to perfrom SHOW PROCESSLIST command from a separate session during the mysqldump and send the last results before it hangs. 

Reread the appropriate page in the manual also, http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html - you may find some hints there.
[19 Jan 2006 12:07] Kyle Beckham
We had done that and there was nothing:

mysql>
mysql>
mysql>
mysql> show processlist
    -> ;
+-------+------+----------------+------+---------+------+-------+---------------
---+
| Id    | User | Host           | db   | Command | Time | State | Info
   |
+-------+------+----------------+------+---------+------+-------+---------------
---+
| 19327 | root | localhost:2580 | NULL | Query   |    0 | NULL  | show processli
st |
+-------+------+----------------+------+---------+------+-------+---------------
---+
1 row in set (0.00 sec)
 
mysql>
 
----------------------------------------------

then last night when it ran it hung and we provided the attached show processlist. I will resubmit again for you.
[19 Jan 2006 12:08] Kyle Beckham
please see my response from [19 Jan 5:24] Kyle Beckham - that has the attached process list.
[30 Jan 2006 14:17] Valeriy Kravchuk
Please, try to add --lock-tables option to each of your individual database dump command line and check, does it help to prevent this hang or not. Inform about the results. 

A lot of "Opening tables" in your SHOW PROCESSLIST results, and your usage of Windows, make me think that your problem may be also related to Bug #12071, not fixed yet.
[21 Feb 2006 2:55] Kyle Beckham
once v4.1.18 was released all of these issues have seemed to go away!
[21 Feb 2006 7:12] Valeriy Kravchuk
There is no such problem in 4.1.18, according to the bug reporter.
[21 Feb 2006 11:42] Kyle Beckham
I am saying that we had v4.1.15 upgraded to v1.1.16 - still had problems.
Never installed v4.1.17, as i am not sure it was even released. Then noticed v4.1.18 was released. Since we installed v4.1.18 the mysql dump issues when away I thought there was a related bug with open file locks or something that was fixed for v4.1.17/18.