Bug #27380 | Problems Deleting Data from InnoDB | ||
---|---|---|---|
Submitted: | 22 Mar 2007 17:25 | Modified: | 26 Mar 2007 15:22 |
Reporter: | Morgan Miskell | Email Updates: | |
Status: | Not a Bug | Impact on me: | |
Category: | MySQL Server | Severity: | S2 (Serious) |
Version: | 5.0.22 and 5.0.37 | OS: | Linux (RedHat Enterprise 4) |
Assigned to: | CPU Architecture: | Any |
[22 Mar 2007 17:25]
Morgan Miskell
[23 Mar 2007 9:53]
Sveta Smirnova
Thank you for the report. But version 5.0.22 is quite old. Please upgrade to current 5.0.37 and if you can repeat provide output of SHOW CREATE TABLE, SHOW STATUS for table from which you can not delete rows. Also provide your configuration file.
[23 Mar 2007 14:56]
Morgan Miskell
Same issue with 5.0.37! Your MySQL connection id is 106 Server version: 5.0.37-community-log MySQL Community Edition (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> use Clientvariablesdb; Database changed mysql> delete from CGLOBAL where lvisit < date_sub(CURRENT_TIMESTAMP,interval 20 day); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> select count(*) from CGLOBAL where lvisit < date_sub(CURRENT_TIMESTAMP,interval 20 day); +----------+ | count(*) | +----------+ | 158063 | +----------+ 1 row in set (17.77 sec) mysql> delete from CGLOBAL where lvisit < date_sub(CURRENT_TIMESTAMP,interval 20 day) limit 75000; Query OK, 75000 rows affected (14.50 sec) mysql> select count(*) from CGLOBAL where lvisit < date_sub(CURRENT_TIMESTAMP,interval 20 day); +----------+ | count(*) | +----------+ | 84032 | +----------+ 1 row in set (26.71 sec) mysql> delete from CGLOBAL where lvisit < date_sub(CURRENT_TIMESTAMP,interval 20 day); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> delete from CGLOBAL where lvisit < date_sub(CURRENT_TIMESTAMP,interval 20 day) limit 85000; Query OK, 84238 rows affected (16.32 sec) I do have another note that may or may not be relevant. The server itself is an Intel Quad XEON 64-bit, but we are running the 32-bit O/S with the 32-bit version of MySQL. It will take me 5-10 days to get another server to test 64-bit O/S with 64-bit MySQL. Do you think that could be a contributing factor?
[23 Mar 2007 15:15]
Morgan Miskell
Sorry forgot to post requested data: mysql> show create table CGLOBAL; +---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | CGLOBAL | CREATE TABLE `CGLOBAL` ( `cfid` varchar(64) NOT NULL default '', `data` text, `lvisit` datetime NOT NULL default '0000-00-00 00:00:00', KEY `id2` (`cfid`), KEY `id3` (`lvisit`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ show table status like "CGLOBAL"; +---------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+--------------------------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +---------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+--------------------------+ | CGLOBAL | InnoDB | 9 | Redundant | 4037709 | 730 | 2947563520 | 0 | 2001764352 | 0 | NULL | 2007-03-19 19:13:26 | NULL | NULL | latin1_swedish_ci | NULL | | InnoDB free: 12943360 kB | +---------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+--------------------------+ /etc/my.cnf [client] port = 3306 socket = /var/lib/mysql/mysql.sock # Here follows entries for some specific programs # The MySQL server [mysqld] port = 3306 socket = /var/lib/mysql/mysql.sock skip-locking key_buffer = 768M max_allowed_packet = 16M table_cache = 512 sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 8M myisam_sort_buffer_size = 64M thread_cache = 8 query_cache_size = 32M tmp_table_size=96M # Try number of CPU's*2 for thread_concurrency thread_concurrency = 8 max_connections=2500 server-id = 1 log-long-format #log-queries-not-using-indexes long_query_time=3 #Log any queries that take longer than 3 seconds log_slow_queries=/var/log/mysql-slow-queries.log relay-log=sql-relay-bin relay-log-index=sql-relay-bin master-info-file=master.info relay-log-info=sql-relay-log.info log-bin=sql-bin # Point the following paths to different dedicated disks tmpdir = /tmp/ #log-update = /path-to-dedicated-directory/hostname # Uncomment the following if you are using BDB tables #set-variable = bdb_cache_size=4M #set-variable = bdb_max_lock=10000 # Uncomment the following if you are using InnoDB tables innodb_file_per_table innodb_data_home_dir = /var/lib/mysql/ innodb_data_file_path = ibdata1:10G;ibdata2:2G:autoextend innodb_log_group_home_dir = /var/lib/mysql/ innodb_log_arch_dir = /var/lib/mysql/ ## You can set .._buffer_pool_size up to 50 - 80 % ## of RAM but beware of setting memory usage too high innodb_buffer_pool_size = 256M innodb_additional_mem_pool_size = 20M ## Set .._log_file_size to 25 % of buffer pool size innodb_log_file_size = 128M innodb_log_buffer_size = 56M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 [mysqldump] quick set-variable = max_allowed_packet=16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout
[26 Mar 2007 7:15]
Sveta Smirnova
Thank you for the additional information. But this seems to be not result of a bug. Read about innodb_lock_wait_timeout at http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html