Hello Every Body , I have been facing a strange problem, that i assume its a mysql bug of some 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- update
and will freeze like this forever , and consequently all inserts ot this
table stays in the que, untill mysql stops responding.
If i drop the INDEX on those tables INSERTS works fine, once i create
ANY 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 ram
using 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 rest
of enumerate due to restricton on email size**,'ZW','Zimbabwe') NOT NULL
default '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.sock
port = 3306
pid-file = /opt/lams/pid-lock/mysql/gnr31.pid
basedir = /opt/lams/servers/mysql
datadir = /opt/lams/database/gnr31
user = mysql
#flush
#innodb_force_recovery = 6
#-----------------------#
# config #
#-----------------------#
open-files-limit = 65536
max_connections = 400
#log-long-format
#myisam-recover = QUICK,FORCE
# -----------------------#
#Tunning and optimization#
#------------------------#
skip-innodb
#no innoDB
skip-bdb
#no berkely database
skip-external-locking
skip-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 = 600M
max_allowed_packet = 1M
table_cache = 512
sort_buffer = 2M
record_buffer = 2M
thread_cache = 8
query_cache_type = 1
query_cache_size = 20000000
#query_cache_min_res_unit = 1000
log-warnings
# -----------------------#
#Temp , and Logs #
#------------------------#
tmpdir = /opt/lams/temp/mysql
log-update = /opt/lams/logs/mysql/gnr31/log-update
log = /opt/lams/logs/mysql/gnr31/log
log-error = /opt/lams/logs/mysql/gnr31/log-error
log-slow-queries = /opt/lams/logs/mysql/gnr31/slow-queries
show 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 where
they 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 gbk
latin1_de sjis tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7
cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek
win1250 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-standard
binary
|
| wait_timeout |
28800
|
+---------------------------------+----------------------------------------------------------
PLEASEEEE ADVICE, my site is down for 2 days now.
Thanks In Advance,
Derek J
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
