MySQL Bugs: #27380: Problems Deleting Data from InnoDB

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:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.22 and 5.0.37	OS:	Linux (RedHat Enterprise 4)
Assigned to:		CPU Architecture:	Any

Description:
I am unable to delete records from large InnoDB tables unless I restrict the amount rows being deleted.  The oddity is that the number of rows being restricted seems to not be a factor in the deletion!  Example:  I have 18885 rows in a table and I attempt to delete them, the delete will fail with "ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction", however if I add "limit 75000" (notice that 75000 > 18885) then the query will process and delete the rows!

I am currently unable to determine a cause for this........

mysql> select count(*) from CGLOBAL where lvisit < "2007-03-02";
+----------+
| count(*) |
+----------+
|    18885 | 
+----------+
1 row in set (16.91 sec)

mysql> delete from CGLOBAL where lvisit < "2007-03-02";
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> delete from CGLOBAL where lvisit < "2007-03-02";
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> delete from CGLOBAL where lvisit < "2007-03-02" limit 75000;
Query OK, 18885 rows affected (9.54 sec)

How to repeat:
In my case, just attempt to delete any number of rows from any InnoDB table without using the limit command!

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.

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?

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

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