Description:
Below Is the conversation happened between me and Sergei Golubchik <serg@mysql.com> MySQL AB, Senior Software Developer on lists.mysql.com who sugggessted reporting this as a bug to you.
Hello Every Body , I have been facing a strange problem, that i assume its a mysql bug ofsome sort.In my datadabase there are 2 tables, if i tried to add any index to them,any INSERT INTO request status will be - in show processlist- updateand will freeze like this forever , and consequently all inserts ot thistable stays in the que, untill mysql stops responding.If i drop the INDEX on those tables INSERTS works fine, once i createANY index except PRIMARY index, INSERTS fails.ALL kind of tricks, like myisamchk, drop table and creating a new one,even moving it to a new machine seems to cause the same problem.Running Mysqld 4.0.16 / 4.0.17 ( tried both) on AMD opteron 2 gig ramusing MySQL Binaries on REDHAT AS3.one of The table structures:CREATE TABLE `keywordlog` ( `username` char(40) default NULL, `country` enum('Unknown','Unknown','AD','Andorra','AE',**removed restof enumerate due to restricton on email size**,'ZW','Zimbabwe') NOT NULLdefault 'Unknown', `ip` char(15) NOT NULL default '', `time_date` datetime NOT NULL default '0000-00-00 00:00:00', `keyword` char(50) NOT NULL default '', `refer_url` char(70) default NULL, `request_url` char(70) default NULL, `xmlstatus` enum('HTML','XML') NOT NULL default 'HTML', `bid` char(6) NOT NULL default '', `toolbar` int(1) default '0') TYPE=MyISAM;/etc/my.conf-----------------socket = /opt/lams/pid-lock/mysql/gnr31.sockport = 3306pid-file = /opt/lams/pid-lock/mysql/gnr31.pidbasedir = /opt/lams/servers/mysqldatadir = /opt/lams/database/gnr31user = mysql#flush#innodb_force_recovery = 6 #-----------------------## config ##-----------------------# open-files-limit = 65536max_connections = 400#log-long-format#myisam-recover = QUICK,FORCE # -----------------------##Tunning and optimization##------------------------# skip-innodb#no innoDBskip-bdb#no berkely databaseskip-external-lockingskip-thread-priority#Disable using thread priorities for faster response time.#delay-key-write = ON#delay-key-write-for-all-tables#low-priority-updates#Table-modifying operations (INSERT/DELETE/UPDATE)#will have lower priority than selects. key_buffer = 600Mmax_allowed_packet = 1Mtable_cache = 512sort_buffer = 2Mrecord_buffer = 2Mthread_cache = 8 query_cache_type = 1query_cache_size = 20000000#query_cache_min_res_unit = 1000log-warnings # -----------------------##Temp , and Logs ##------------------------# tmpdir = /opt/lams/temp/mysqllog-update = /opt/lams/logs/mysql/gnr31/log-updatelog = /opt/lams/logs/mysql/gnr31/loglog-error = /opt/lams/logs/mysql/gnr31/log-errorlog-slow-queries = /opt/lams/logs/mysql/gnr31/slow-queriesshow status:---------------------------+--------------------------+------------+| Variable_name | Value |+--------------------------+------------+| Aborted_clients | 0 || Aborted_connects | 4 || Bytes_received | 4636115 || Bytes_sent | 7951527 || Com_admin_commands | 0 || Com_alter_table | 2 || Com_analyze | 0 || Com_backup_table | 0 || Com_begin | 0 || Com_change_db | 13680 || Com_change_master | 0 || Com_check | 0 || Com_commit | 0 || Com_create_db | 0 || Com_create_function | 0 || Com_create_index | 0 || Com_create_table | 0 || Com_delete | 1615 || Com_delete_multi | 0 || Com_drop_db | 0 || Com_drop_function | 0 || Com_drop_index | 0 || Com_drop_table | 0 || Com_flush | 0 || Com_grant | 0 || Com_ha_close | 0 || Com_ha_open | 0 || Com_ha_read | 0 || Com_insert | 2886 || Com_insert_select | 0 || Com_kill | 0 || Com_load | 0 || Com_load_master_data | 0 || Com_load_master_table | 0 || Com_lock_tables | 0 || Com_optimize | 0 || Com_purge | 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_rollback | 0 || Com_savepoint | 0 || Com_select | 16321 || Com_set_option | 1 || Com_show_binlog_events | 0 || Com_show_binlogs | 0 || Com_show_create | 1 || Com_show_databases | 20 || Com_show_fields | 13 || Com_show_grants | 0 || Com_show_keys | 5 || Com_show_logs | 0 || Com_show_master_status | 0 || Com_show_new_master | 0 || Com_show_open_tables | 0 || Com_show_processlist | 48 || Com_show_slave_hosts | 0 || Com_show_slave_status | 0 || Com_show_status | 2 || Com_show_innodb_status | 0 || Com_show_tables | 42 || Com_show_variables | 2 || Com_slave_start | 0 || Com_slave_stop | 0 || Com_truncate | 0 || Com_unlock_tables | 0 || Com_update | 4115 || Connections | 3045 || Created_tmp_disk_tables | 0 || Created_tmp_tables | 0 || Created_tmp_files | 0 || Delayed_insert_threads | 0 || Delayed_writes | 0 || Delayed_errors | 0 || Flush_commands | 1 || Handler_commit | 0 || Handler_delete | 9654 || Handler_read_first | 1014 || Handler_read_key | 17492 || Handler_read_next | 56977 || Handler_read_prev | 0 || Handler_read_rnd | 1271 || Handler_read_rnd_next | 1155961730 || Handler_rollback | 0 || Handler_update | 4026 || Handler_write | 155816 || Key_blocks_used | 6192 || Key_read_requests | 270565 || Key_reads | 6087 || Key_write_requests | 14633 || Key_writes | 7484 || Max_used_connections | 108 || Not_flushed_key_blocks | 0 || Not_flushed_delayed_rows | 0 || Open_tables | 229 || Open_files | 265 || Open_streams | 0 || Opened_tables | 240 || Questions | 56767 || Qcache_queries_in_cache | 2895 || Qcache_inserts | 15937 || Qcache_hits | 14941 || Qcache_lowmem_prunes | 0 || Qcache_not_cached | 227 || Qcache_free_memory | 16704832 || Qcache_free_blocks | 196 || Qcache_total_blocks | 5973 || Rpl_status | NULL || Select_full_join | 0 || Select_full_range_join | 0 || Select_range | 0 || Select_range_check | 0 || Select_scan | 3357 || Slave_open_temp_tables | 0 || Slave_running | OFF || Slow_launch_threads | 4 || Slow_queries | 1117 || Sort_merge_passes | 0 || Sort_range | 1068 || Sort_rows | 1271 || Sort_scan | 0 || Table_locks_immediate | 21150 || Table_locks_waited | 3713 || Threads_cached | 8 || Threads_created | 1229 || Threads_connected | 44 || Threads_running | 33 || Uptime | 2889 |+--------------------------+------------+show variables: ----------------------IN SHOW VARIABLES i have shose 2 very big numbers i don't know wherethey came from| myisam_max_extra_sort_file_size |268435456 || myisam_max_sort_file_size |9223372036854775807 -----------------------------------------------------------------------------------------+| Variable_name |Value |+---------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| back_log |50 || basedir |/opt/lams/servers/mysql/ || binlog_cache_size |32768 || bulk_insert_buffer_size |8388608 || character_set |latin1 || character_sets | latin1 big5 czech euc_kr gb2312 gbklatin1_de sjis tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greekwin1250 croat cp1257 latin5 || concurrent_insert |ON || connect_timeout |5 || convert_character_set | || datadir |/opt/lams/database/gnr31/ || default_week_format |0 || delay_key_write |ON || delayed_insert_limit |100 || delayed_insert_timeout |300 || delayed_queue_size |1000 || flush |OFF || flush_time |0 || ft_boolean_syntax | +-><()~*:""&| || ft_min_word_len |4 || ft_max_word_len |254 || ft_max_word_len_for_sort |20 || ft_stopword_file |(built-in) || have_bdb |NO || have_crypt |YES || have_innodb |DISABLED || have_isam |YES || have_raid |NO || have_symlink |YES || have_openssl |NO || have_query_cache |YES || init_file | || innodb_additional_mem_pool_size |1048576 || innodb_buffer_pool_size |8388608 || innodb_data_file_path | || innodb_data_home_dir | || innodb_file_io_threads |4 || innodb_force_recovery |0 || innodb_thread_concurrency |8 || innodb_flush_log_at_trx_commit |1 || innodb_fast_shutdown |ON || innodb_flush_method | || innodb_lock_wait_timeout |50 || innodb_log_arch_dir | || innodb_log_archive |OFF || innodb_log_buffer_size |1048576 || innodb_log_file_size |5242880 || innodb_log_files_in_group |2 || innodb_log_group_home_dir | || innodb_mirrored_log_groups |1 || innodb_max_dirty_pages_pct |90 || interactive_timeout |28800 || join_buffer_size |131072 || key_buffer_size |629145600 || language |/opt/lams/servers/mysql/share/mysql/english/ || large_files_support |ON || local_infile |ON || locked_in_memory |OFF || log |ON || log_update |ON || log_bin |OFF || log_slave_updates |OFF || log_slow_queries |ON || log_warnings |ON || long_query_time |10 || low_priority_updates |OFF || lower_case_table_names |OFF || max_allowed_packet |1047552 || max_binlog_cache_size |4294967295 || max_binlog_size |1073741824 || max_connections |400 || max_connect_errors |10 || max_delayed_threads |20 || max_heap_table_size |16777216 || max_join_size |18446744073709551615 || max_relay_log_size |0 || max_seeks_for_key |4294967295 || max_sort_length |1024 || max_user_connections |0 || max_tmp_tables |32 || max_write_lock_count |4294967295 || myisam_max_extra_sort_file_size |268435456 || myisam_max_sort_file_size |9223372036854775807 || myisam_repair_threads |1 || myisam_recover_options |OFF || myisam_sort_buffer_size |67108864 || net_buffer_length |16384 || net_read_timeout |30 || net_retry_count |10 || net_write_timeout |60 || new |OFF || open_files_limit |65536 || pid_file |/opt/lams/pid-lock/mysql/gnr31.pid || log_error |/opt/lams/logs/mysql/gnr31/log-error.err || port |3306 || protocol_version |10 || query_alloc_block_size |8192 || query_cache_limit |1048576 || query_cache_size |19999744 || query_cache_type |ON || query_prealloc_size |8192 || range_alloc_block_size |2048 || read_buffer_size |2093056 || read_only |OFF || read_rnd_buffer_size |262144 || rpl_recovery_rank |0 || server_id |31 || slave_net_timeout |3600 || skip_external_locking |ON || skip_networking |OFF || skip_show_database |OFF || slow_launch_time |2 || socket |/opt/lams/pid-lock/mysql/gnr31.sock || sort_buffer_size |2097144 || sql_mode |0 || table_cache |512 || table_type |MYISAM || thread_cache_size |8 || thread_stack |196608 || tx_isolation |REPEATABLE-READ || timezone |EST || tmp_table_size |33554432 || tmpdir |/opt/lams/temp/mysql/ || transaction_alloc_block_size |8192 || transaction_prealloc_size |4096 || version |4.0.17-standard-log || version_comment | Official MySQL-standardbinary || wait_timeout |28800 |+---------------------------------+---------------------------------------------------------- PLEASEEEE ADVICE, my site is down for 2 days now.Thanks In Advance,Derek J
---------------------
What columns are you trying to index ? What is the index definition ?What does SHOW PROCESSLIST show when there are "freezed" inserts ?How big is your table ?Can you create a repeatable test case ?Regards,Sergei-- __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik <serg@mysql.com> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany <___/ www.mysql.com
------------
Hello, First, Yes I can create repeatable test case, I still have liveserver with the same problem. and another Dual opteron server, which icreated the same database there as a test to make sure there are nohardware faliures casuing this probles, but i still had the same problemthere too. I can give full access to any of MySQL team to check thisproblem. I had aleaddy 2 DBA's fail to Identify the problem in there.About the kind of index. any index except primary creates this problem.I have tried with index on column only , and on multiple, one indexinstead of the the multiple i had on the table ( shown below ) . butstill same problem. The indexes i have there :mysql> show index from keywordlog;+------------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+| Table | Non_unique | Key_name | Seq_in_index | Column_name| Collation | Cardinality | Sub_part | Packed | Null | Index_type |Comment |+------------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+| keywordlog | 1 | time_date_index | 1 | time_date | A | NULL | NULL | NULL | | BTREE | || keywordlog | 1 | time_date_index | 2 | username | A | NULL | NULL | NULL | YES | BTREE | || keywordlog | 1 | time_date_index | 3 | keyword | A | NULL | NULL | NULL | | BTREE | || keywordlog | 1 | keyword_index | 1 | keyword | A | NULL | 10 | NULL | | BTREE | || keywordlog | 1 | keyword_index | 2 | time_date | A | NULL | NULL | NULL | | BTREE | || keywordlog | 1 | ip_index | 1 | ip | A | NULL | NULL | NULL | | BTREE | || keywordlog | 1 | ip_index | 2 | username | A | NULL | NULL | NULL | YES | BTREE | || keywordlog | 1 | username | 1 | username | A | NULL | NULL | NULL | YES | BTREE | || keywordlog | 1 | username | 2 | bid | A | NULL | NULL | NULL | | BTREE | || keywordlog | 1 | username | 3 | time_date | A | NULL | NULL | NULL | | BTREE | || keywordlog | 1 | bid | 1 | bid | A | NULL | NULL | NULL | | BTREE | || keywordlog | 1 | bid | 2 | time_date | A | NULL | NULL | NULL | | BTREE | |+------------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+12 rows in set (0.00 sec)Here is the result of show processlist on the second server where icreated the test case:mysql> show processlist;+----+------+-----------+------------------+---------+------+--------+------------------------------------------------------------------------------------------------------+| Id | User | Host | db | Command | Time | State |Info |+----+------+-----------+------------------+---------+------+--------+------------------------------------------------------------------------------------------------------+| 2 | root | localhost | donds87_smartppc | Query | 263 | update |INSERT INTO keywordlog (keyword, username, ip, time_date, country,refer_url, request_url, xmlstatus || 3 | root | localhost | donds87_smartppc | Query | 0 | NULL |show processlist || 4 | root | localhost | donds87_smartppc | Query | 191 | Locked |INSERT INTO keywordlog (keyword, username, ip, time_date, country,refer_url, request_url, xmlstatus || 5 | root | localhost | donds87_smartppc | Query | 184 | Locked |INSERT INTO keywordlog (keyword, username, ip, time_date, country,refer_url, request_url, xmlstatus || 6 | root | localhost | donds87_smartppc | Query | 182 | Locked |INSERT INTO keywordlog (keyword, username, ip, time_date, country,refer_url, request_url, xmlstatus || 7 | root | localhost | donds87_smartppc | Query | 180 | Locked |INSERT INTO keywordlog (keyword, username, ip, time_date, country,refer_url, request_url, xmlstatus || 8 | root | localhost | donds87_smartppc | Query | 180 | Locked |INSERT INTO keywordlog (keyword, username, ip, time_date, country,refer_url, request_url, xmlstatus || 9 | root | localhost | donds87_smartppc | Query | 179 | Locked |INSERT INTO keywordlog (keyword, username, ip, time_date, country,refer_url, request_url, xmlstatus || 10 | root | localhost | donds87_smartppc | Query | 178 | Locked |INSERT INTO keywordlog (keyword, username, ip, time_date, country,refer_url, request_url, xmlstatus || 11 | root | localhost | donds87_smartppc | Query | 177 | Locked |INSERT INTO keywordlog (keyword, username, ip, time_date, country,refer_url, request_url, xmlstatus || 12 | root | localhost | donds87_smartppc | Query | 176 | Locked |INSERT INTO keywordlog (keyword, username, ip, time_date, country,refer_url, request_url, xmlstatus || 13 | root | localhost | donds87_smartppc | Query | 175 | Locked |INSERT INTO keywordlog (keyword, username, ip, time_date, country,refer_url, request_url, xmlstatus |+----+------+-----------+------------------+---------+------+--------+------------------------------------------------------------------------------------------------------+12 rows in set (0.00 sec)As you see the first INSERT stays in UPDATE status like this forever.causing the rest to be LOCKEDHere is the information about this table.mysql> show table status like 'keywordlog';+---------------------+---------------------+----------------+---------+| Name | Type | Row_format | Rows | Avg_row_length | Data_length| Max_data_length | Index_length | Data_free | Auto_increment |Create_time | Update_time | Check_time | Create_options| Comment |+------------+--------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+----------------+---------+| keywordlog | MyISAM | Fixed | 518 | 267 | 138306| 1146756268031 | 77824 | 0 | NULL |2003-12-27 19:21:28 | 2003-12-28 02:40:00 | 2003-12-27 21:40:36| | |+------------+--------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+----------------+---------+HTH,Derek J
Great, table is 138306 bytes only - relatively small, that is.Then, the fastest way to get this fixed is to create a bugreport athttp://bugs.mysql.com (just copy-paste your first mail and informationfrom the your last reply) and to attach a (compressed) table dumpto a bugreport using [Files] tab (mark it "private" if you want it to bevisible to MySQL AB staff only).As far as I understand, the table in the dump should be created withoutindexes (this is your point, isn't it?), and if one adds to the end ofthe dump "ALTER TABLE keywordlog ADD INDEX ...; INSERT keywordlog ..."then "mysql < keywordlog.dump" will hang. Did I understand you correctly ?Then, as I advised above, just create a bugreport and attach a dump(that ends with "ALTER TABLE ... ; INSERT ...") to it - with arepeatable test case the bug will be fixed very quickly.No need to waste the time sending emails back and forth and speculating,when your test case is small enough :)Regards,Sergei
How to repeat:
I have a test case ready , if needed you can login to my servers to check that youser selves.
Suggested fix:
Inquiring for a fix
Description: Below Is the conversation happened between me and Sergei Golubchik <serg@mysql.com> MySQL AB, Senior Software Developer on lists.mysql.com who sugggessted reporting this as a bug to you. Hello Every Body , I have been facing a strange problem, that i assume its a mysql bug ofsome sort.In my datadabase there are 2 tables, if i tried to add any index to them,any INSERT INTO request status will be - in show processlist- updateand will freeze like this forever , and consequently all inserts ot thistable stays in the que, untill mysql stops responding.If i drop the INDEX on those tables INSERTS works fine, once i createANY index except PRIMARY index, INSERTS fails.ALL kind of tricks, like myisamchk, drop table and creating a new one,even moving it to a new machine seems to cause the same problem.Running Mysqld 4.0.16 / 4.0.17 ( tried both) on AMD opteron 2 gig ramusing MySQL Binaries on REDHAT AS3.one of The table structures:CREATE TABLE `keywordlog` ( `username` char(40) default NULL, `country` enum('Unknown','Unknown','AD','Andorra','AE',**removed restof enumerate due to restricton on email size**,'ZW','Zimbabwe') NOT NULLdefault 'Unknown', `ip` char(15) NOT NULL default '', `time_date` datetime NOT NULL default '0000-00-00 00:00:00', `keyword` char(50) NOT NULL default '', `refer_url` char(70) default NULL, `request_url` char(70) default NULL, `xmlstatus` enum('HTML','XML') NOT NULL default 'HTML', `bid` char(6) NOT NULL default '', `toolbar` int(1) default '0') TYPE=MyISAM;/etc/my.conf-----------------socket = /opt/lams/pid-lock/mysql/gnr31.sockport = 3306pid-file = /opt/lams/pid-lock/mysql/gnr31.pidbasedir = /opt/lams/servers/mysqldatadir = /opt/lams/database/gnr31user = mysql#flush#innodb_force_recovery = 6 #-----------------------## config ##-----------------------# open-files-limit = 65536max_connections = 400#log-long-format#myisam-recover = QUICK,FORCE # -----------------------##Tunning and optimization##------------------------# skip-innodb#no innoDBskip-bdb#no berkely databaseskip-external-lockingskip-thread-priority#Disable using thread priorities for faster response time.#delay-key-write = ON#delay-key-write-for-all-tables#low-priority-updates#Table-modifying operations (INSERT/DELETE/UPDATE)#will have lower priority than selects. key_buffer = 600Mmax_allowed_packet = 1Mtable_cache = 512sort_buffer = 2Mrecord_buffer = 2Mthread_cache = 8 query_cache_type = 1query_cache_size = 20000000#query_cache_min_res_unit = 1000log-warnings # -----------------------##Temp , and Logs ##------------------------# tmpdir = /opt/lams/temp/mysqllog-update = /opt/lams/logs/mysql/gnr31/log-updatelog = /opt/lams/logs/mysql/gnr31/loglog-error = /opt/lams/logs/mysql/gnr31/log-errorlog-slow-queries = /opt/lams/logs/mysql/gnr31/slow-queriesshow status:---------------------------+--------------------------+------------+| Variable_name | Value |+--------------------------+------------+| Aborted_clients | 0 || Aborted_connects | 4 || Bytes_received | 4636115 || Bytes_sent | 7951527 || Com_admin_commands | 0 || Com_alter_table | 2 || Com_analyze | 0 || Com_backup_table | 0 || Com_begin | 0 || Com_change_db | 13680 || Com_change_master | 0 || Com_check | 0 || Com_commit | 0 || Com_create_db | 0 || Com_create_function | 0 || Com_create_index | 0 || Com_create_table | 0 || Com_delete | 1615 || Com_delete_multi | 0 || Com_drop_db | 0 || Com_drop_function | 0 || Com_drop_index | 0 || Com_drop_table | 0 || Com_flush | 0 || Com_grant | 0 || Com_ha_close | 0 || Com_ha_open | 0 || Com_ha_read | 0 || Com_insert | 2886 || Com_insert_select | 0 || Com_kill | 0 || Com_load | 0 || Com_load_master_data | 0 || Com_load_master_table | 0 || Com_lock_tables | 0 || Com_optimize | 0 || Com_purge | 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_rollback | 0 || Com_savepoint | 0 || Com_select | 16321 || Com_set_option | 1 || Com_show_binlog_events | 0 || Com_show_binlogs | 0 || Com_show_create | 1 || Com_show_databases | 20 || Com_show_fields | 13 || Com_show_grants | 0 || Com_show_keys | 5 || Com_show_logs | 0 || Com_show_master_status | 0 || Com_show_new_master | 0 || Com_show_open_tables | 0 || Com_show_processlist | 48 || Com_show_slave_hosts | 0 || Com_show_slave_status | 0 || Com_show_status | 2 || Com_show_innodb_status | 0 || Com_show_tables | 42 || Com_show_variables | 2 || Com_slave_start | 0 || Com_slave_stop | 0 || Com_truncate | 0 || Com_unlock_tables | 0 || Com_update | 4115 || Connections | 3045 || Created_tmp_disk_tables | 0 || Created_tmp_tables | 0 || Created_tmp_files | 0 || Delayed_insert_threads | 0 || Delayed_writes | 0 || Delayed_errors | 0 || Flush_commands | 1 || Handler_commit | 0 || Handler_delete | 9654 || Handler_read_first | 1014 || Handler_read_key | 17492 || Handler_read_next | 56977 || Handler_read_prev | 0 || Handler_read_rnd | 1271 || Handler_read_rnd_next | 1155961730 || Handler_rollback | 0 || Handler_update | 4026 || Handler_write | 155816 || Key_blocks_used | 6192 || Key_read_requests | 270565 || Key_reads | 6087 || Key_write_requests | 14633 || Key_writes | 7484 || Max_used_connections | 108 || Not_flushed_key_blocks | 0 || Not_flushed_delayed_rows | 0 || Open_tables | 229 || Open_files | 265 || Open_streams | 0 || Opened_tables | 240 || Questions | 56767 || Qcache_queries_in_cache | 2895 || Qcache_inserts | 15937 || Qcache_hits | 14941 || Qcache_lowmem_prunes | 0 || Qcache_not_cached | 227 || Qcache_free_memory | 16704832 || Qcache_free_blocks | 196 || Qcache_total_blocks | 5973 || Rpl_status | NULL || Select_full_join | 0 || Select_full_range_join | 0 || Select_range | 0 || Select_range_check | 0 || Select_scan | 3357 || Slave_open_temp_tables | 0 || Slave_running | OFF || Slow_launch_threads | 4 || Slow_queries | 1117 || Sort_merge_passes | 0 || Sort_range | 1068 || Sort_rows | 1271 || Sort_scan | 0 || Table_locks_immediate | 21150 || Table_locks_waited | 3713 || Threads_cached | 8 || Threads_created | 1229 || Threads_connected | 44 || Threads_running | 33 || Uptime | 2889 |+--------------------------+------------+show variables: ----------------------IN SHOW VARIABLES i have shose 2 very big numbers i don't know wherethey came from| myisam_max_extra_sort_file_size |268435456 || myisam_max_sort_file_size |9223372036854775807 -----------------------------------------------------------------------------------------+| Variable_name |Value |+---------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| back_log |50 || basedir |/opt/lams/servers/mysql/ || binlog_cache_size |32768 || bulk_insert_buffer_size |8388608 || character_set |latin1 || character_sets | latin1 big5 czech euc_kr gb2312 gbklatin1_de sjis tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greekwin1250 croat cp1257 latin5 || concurrent_insert |ON || connect_timeout |5 || convert_character_set | || datadir |/opt/lams/database/gnr31/ || default_week_format |0 || delay_key_write |ON || delayed_insert_limit |100 || delayed_insert_timeout |300 || delayed_queue_size |1000 || flush |OFF || flush_time |0 || ft_boolean_syntax | +-><()~*:""&| || ft_min_word_len |4 || ft_max_word_len |254 || ft_max_word_len_for_sort |20 || ft_stopword_file |(built-in) || have_bdb |NO || have_crypt |YES || have_innodb |DISABLED || have_isam |YES || have_raid |NO || have_symlink |YES || have_openssl |NO || have_query_cache |YES || init_file | || innodb_additional_mem_pool_size |1048576 || innodb_buffer_pool_size |8388608 || innodb_data_file_path | || innodb_data_home_dir | || innodb_file_io_threads |4 || innodb_force_recovery |0 || innodb_thread_concurrency |8 || innodb_flush_log_at_trx_commit |1 || innodb_fast_shutdown |ON || innodb_flush_method | || innodb_lock_wait_timeout |50 || innodb_log_arch_dir | || innodb_log_archive |OFF || innodb_log_buffer_size |1048576 || innodb_log_file_size |5242880 || innodb_log_files_in_group |2 || innodb_log_group_home_dir | || innodb_mirrored_log_groups |1 || innodb_max_dirty_pages_pct |90 || interactive_timeout |28800 || join_buffer_size |131072 || key_buffer_size |629145600 || language |/opt/lams/servers/mysql/share/mysql/english/ || large_files_support |ON || local_infile |ON || locked_in_memory |OFF || log |ON || log_update |ON || log_bin |OFF || log_slave_updates |OFF || log_slow_queries |ON || log_warnings |ON || long_query_time |10 || low_priority_updates |OFF || lower_case_table_names |OFF || max_allowed_packet |1047552 || max_binlog_cache_size |4294967295 || max_binlog_size |1073741824 || max_connections |400 || max_connect_errors |10 || max_delayed_threads |20 || max_heap_table_size |16777216 || max_join_size |18446744073709551615 || max_relay_log_size |0 || max_seeks_for_key |4294967295 || max_sort_length |1024 || max_user_connections |0 || max_tmp_tables |32 || max_write_lock_count |4294967295 || myisam_max_extra_sort_file_size |268435456 || myisam_max_sort_file_size |9223372036854775807 || myisam_repair_threads |1 || myisam_recover_options |OFF || myisam_sort_buffer_size |67108864 || net_buffer_length |16384 || net_read_timeout |30 || net_retry_count |10 || net_write_timeout |60 || new |OFF || open_files_limit |65536 || pid_file |/opt/lams/pid-lock/mysql/gnr31.pid || log_error |/opt/lams/logs/mysql/gnr31/log-error.err || port |3306 || protocol_version |10 || query_alloc_block_size |8192 || query_cache_limit |1048576 || query_cache_size |19999744 || query_cache_type |ON || query_prealloc_size |8192 || range_alloc_block_size |2048 || read_buffer_size |2093056 || read_only |OFF || read_rnd_buffer_size |262144 || rpl_recovery_rank |0 || server_id |31 || slave_net_timeout |3600 || skip_external_locking |ON || skip_networking |OFF || skip_show_database |OFF || slow_launch_time |2 || socket |/opt/lams/pid-lock/mysql/gnr31.sock || sort_buffer_size |2097144 || sql_mode |0 || table_cache |512 || table_type |MYISAM || thread_cache_size |8 || thread_stack |196608 || tx_isolation |REPEATABLE-READ || timezone |EST || tmp_table_size |33554432 || tmpdir |/opt/lams/temp/mysql/ || transaction_alloc_block_size |8192 || transaction_prealloc_size |4096 || version |4.0.17-standard-log || version_comment | Official MySQL-standardbinary || wait_timeout |28800 |+---------------------------------+---------------------------------------------------------- PLEASEEEE ADVICE, my site is down for 2 days now.Thanks In Advance,Derek J --------------------- What columns are you trying to index ? What is the index definition ?What does SHOW PROCESSLIST show when there are "freezed" inserts ?How big is your table ?Can you create a repeatable test case ?Regards,Sergei-- __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik <serg@mysql.com> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany <___/ www.mysql.com ------------ Hello, First, Yes I can create repeatable test case, I still have liveserver with the same problem. and another Dual opteron server, which icreated the same database there as a test to make sure there are nohardware faliures casuing this probles, but i still had the same problemthere too. I can give full access to any of MySQL team to check thisproblem. I had aleaddy 2 DBA's fail to Identify the problem in there.About the kind of index. any index except primary creates this problem.I have tried with index on column only , and on multiple, one indexinstead of the the multiple i had on the table ( shown below ) . butstill same problem. The indexes i have there :mysql> show index from keywordlog;+------------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+| Table | Non_unique | Key_name | Seq_in_index | Column_name| Collation | Cardinality | Sub_part | Packed | Null | Index_type |Comment |+------------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+| keywordlog | 1 | time_date_index | 1 | time_date | A | NULL | NULL | NULL | | BTREE | || keywordlog | 1 | time_date_index | 2 | username | A | NULL | NULL | NULL | YES | BTREE | || keywordlog | 1 | time_date_index | 3 | keyword | A | NULL | NULL | NULL | | BTREE | || keywordlog | 1 | keyword_index | 1 | keyword | A | NULL | 10 | NULL | | BTREE | || keywordlog | 1 | keyword_index | 2 | time_date | A | NULL | NULL | NULL | | BTREE | || keywordlog | 1 | ip_index | 1 | ip | A | NULL | NULL | NULL | | BTREE | || keywordlog | 1 | ip_index | 2 | username | A | NULL | NULL | NULL | YES | BTREE | || keywordlog | 1 | username | 1 | username | A | NULL | NULL | NULL | YES | BTREE | || keywordlog | 1 | username | 2 | bid | A | NULL | NULL | NULL | | BTREE | || keywordlog | 1 | username | 3 | time_date | A | NULL | NULL | NULL | | BTREE | || keywordlog | 1 | bid | 1 | bid | A | NULL | NULL | NULL | | BTREE | || keywordlog | 1 | bid | 2 | time_date | A | NULL | NULL | NULL | | BTREE | |+------------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+12 rows in set (0.00 sec)Here is the result of show processlist on the second server where icreated the test case:mysql> show processlist;+----+------+-----------+------------------+---------+------+--------+------------------------------------------------------------------------------------------------------+| Id | User | Host | db | Command | Time | State |Info |+----+------+-----------+------------------+---------+------+--------+------------------------------------------------------------------------------------------------------+| 2 | root | localhost | donds87_smartppc | Query | 263 | update |INSERT INTO keywordlog (keyword, username, ip, time_date, country,refer_url, request_url, xmlstatus || 3 | root | localhost | donds87_smartppc | Query | 0 | NULL |show processlist || 4 | root | localhost | donds87_smartppc | Query | 191 | Locked |INSERT INTO keywordlog (keyword, username, ip, time_date, country,refer_url, request_url, xmlstatus || 5 | root | localhost | donds87_smartppc | Query | 184 | Locked |INSERT INTO keywordlog (keyword, username, ip, time_date, country,refer_url, request_url, xmlstatus || 6 | root | localhost | donds87_smartppc | Query | 182 | Locked |INSERT INTO keywordlog (keyword, username, ip, time_date, country,refer_url, request_url, xmlstatus || 7 | root | localhost | donds87_smartppc | Query | 180 | Locked |INSERT INTO keywordlog (keyword, username, ip, time_date, country,refer_url, request_url, xmlstatus || 8 | root | localhost | donds87_smartppc | Query | 180 | Locked |INSERT INTO keywordlog (keyword, username, ip, time_date, country,refer_url, request_url, xmlstatus || 9 | root | localhost | donds87_smartppc | Query | 179 | Locked |INSERT INTO keywordlog (keyword, username, ip, time_date, country,refer_url, request_url, xmlstatus || 10 | root | localhost | donds87_smartppc | Query | 178 | Locked |INSERT INTO keywordlog (keyword, username, ip, time_date, country,refer_url, request_url, xmlstatus || 11 | root | localhost | donds87_smartppc | Query | 177 | Locked |INSERT INTO keywordlog (keyword, username, ip, time_date, country,refer_url, request_url, xmlstatus || 12 | root | localhost | donds87_smartppc | Query | 176 | Locked |INSERT INTO keywordlog (keyword, username, ip, time_date, country,refer_url, request_url, xmlstatus || 13 | root | localhost | donds87_smartppc | Query | 175 | Locked |INSERT INTO keywordlog (keyword, username, ip, time_date, country,refer_url, request_url, xmlstatus |+----+------+-----------+------------------+---------+------+--------+------------------------------------------------------------------------------------------------------+12 rows in set (0.00 sec)As you see the first INSERT stays in UPDATE status like this forever.causing the rest to be LOCKEDHere is the information about this table.mysql> show table status like 'keywordlog';+---------------------+---------------------+----------------+---------+| Name | Type | Row_format | Rows | Avg_row_length | Data_length| Max_data_length | Index_length | Data_free | Auto_increment |Create_time | Update_time | Check_time | Create_options| Comment |+------------+--------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+----------------+---------+| keywordlog | MyISAM | Fixed | 518 | 267 | 138306| 1146756268031 | 77824 | 0 | NULL |2003-12-27 19:21:28 | 2003-12-28 02:40:00 | 2003-12-27 21:40:36| | |+------------+--------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+----------------+---------+HTH,Derek J Great, table is 138306 bytes only - relatively small, that is.Then, the fastest way to get this fixed is to create a bugreport athttp://bugs.mysql.com (just copy-paste your first mail and informationfrom the your last reply) and to attach a (compressed) table dumpto a bugreport using [Files] tab (mark it "private" if you want it to bevisible to MySQL AB staff only).As far as I understand, the table in the dump should be created withoutindexes (this is your point, isn't it?), and if one adds to the end ofthe dump "ALTER TABLE keywordlog ADD INDEX ...; INSERT keywordlog ..."then "mysql < keywordlog.dump" will hang. Did I understand you correctly ?Then, as I advised above, just create a bugreport and attach a dump(that ends with "ALTER TABLE ... ; INSERT ...") to it - with arepeatable test case the bug will be fixed very quickly.No need to waste the time sending emails back and forth and speculating,when your test case is small enough :)Regards,Sergei How to repeat: I have a test case ready , if needed you can login to my servers to check that youser selves. Suggested fix: Inquiring for a fix