MySQL Bugs: #45934: alter_table test with InnoDB requires more than innodb_buffer_pool

Bug #45934	alter_table test with InnoDB requires more than innodb_buffer_pool_size=2G
Submitted:	3 Jul 2009 9:08	Modified:	20 Mar 2014 16:20
Reporter:	Elena Stepanova	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.1.34sp1, 5.0.85	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
If alter_table test from large_tests suite is run on 5.1 with an InnoDB table, it eventually fails with Error:1206: The total number of locks exceeds the lock table size even if innodb_buffer_pool_size is set to 2G, while on 5.0 it seems to work with default parameter value.

How to repeat:
large_tests suite disappeared from the release packages some time ago (see bug#43629). Below is the complete scenario, although due to the lock table size error it does not reach alter table enable keys step.

drop table if exists t1;
create table `t1` (`c1` tinyint unsigned not null, key `c1` (`c1`)) engine=innodb default charset=utf8;
alter table `t1` disable keys;
insert into t1 values (1),(2),(3),(4);
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1 limit 2147483647; #Insert 4294967295 rows.
alter table t1 enable keys;
drop table t1;

With 5.0,
--------------
Though it doesn't fail with an error, it gives warning msg in the server log as below with innodb_buffer_pool_size=2G
090819 7:27:22 InnoDB: WARNING: over 67 percent of the buffer pool is occupied by
InnoDB: lock heaps or the adaptive hash index! Check that your
InnoDB: transactions do not set too many row locks.
InnoDB: Your buffer pool size is 2048 MB. Maybe you should make
InnoDB: the buffer pool bigger?
InnoDB: Starting the InnoDB Monitor to print diagnostics, including
InnoDB: lock heap and hash index sizes.

and doesn't increase the data after that
rwxr-xr-x 2 qauser wheel 4096 Aug 18 12:00 mysql
drwxr-xr-x 2 qauser wheel 4096 Aug 18 12:00 test
-rw-rw---- 1 qauser wheel 5242880 Aug 19 07:28 ib_logfile1
-rw-rw---- 1 qauser wheel 53261369344 Aug 19 07:29 ibdata1
-rw-rw---- 1 qauser wheel 5242880 Aug 19 07:29 ib_logfile0

Even With innodb_buffer_pool_size=4G, it gave the same warning -

090819 20:09:26 InnoDB: WARNING: over 67 percent of the buffer pool is occupied by
InnoDB: lock heaps or the adaptive hash index! Check that your
InnoDB: transactions do not set too many row locks.
InnoDB: Your buffer pool size is 4096 MB. Maybe you should make
InnoDB: the buffer pool bigger?
InnoDB: Starting the InnoDB Monitor to print diagnostics, including
InnoDB: lock heap and hash index sizes.

The test didn't end till it exceeded the disk space -

090819 22:20:43 InnoDB: Error: Write to file ./ibdata1 failed at offset 25 3682598912.
InnoDB: 1048576 bytes should have been written, only 512000 were written.
InnoDB: Operating system error number 0.
InnoDB: Check that your OS and file system support files of this size.
InnoDB: Check also that the disk is not full or a disk quota exceeded.
InnoDB: Error number 0 means 'Success'.
InnoDB: Some operating system error numbers are described at
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/operating-sy090820 1:43:53 [Note] /data1/qauser/nidhi/mysql_ent_5085/bin/mysqld: Normal shutdown

Verified as described: this error happens in the next select after table has 1073741824 rows.