MySQL Bugs: #64763: MySQL with InnoDB 1.1 always increment History list values in case of DDL

Bug #64763	MySQL with InnoDB 1.1 always increment History list values in case of DDL
Submitted:	26 Mar 2012 12:31	Modified:	2 Apr 2012 22:54
Reporter:	Marco Tusa	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: InnoDB Plugin storage engine	Severity:	S3 (Non-critical)
Version:	1.1	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	history list, innodb

Description:
MySQL with InnoDB 1.1, always increment History list values, and never reduce it back to 0 when executing DDL statements.

How to repeat:
Install MySQL 5 with InnoDB 1.1 (we have try several versions, also present in the latest 5.5.22.)
use basic set-up and define InnoDB BP to a value more then 10M;
in create a table, and run in loop several statements to insert, truncate values.

IE:
use world;
begin;
insert into City2 select * from City;
commit;
truncate table City2;

We have test it on:
Debian
Fedora
CentOS
OS/X
Windows

same behaviour
Also forcing the full purge in InnoDB will not fix the history list.

we also saw:
https://bugs.launchpad.net/percona-server/+bug/734956
https://bugs.launchpad.net/percona-server/+bug/756387

Forgot to mention all platforms where 32bit.
Still not tested on 64.

Regards

Sveta,
forgot to update the bug report.
we did a lot of test and we have seen that ones reaching high number of history list then the numbers start to went down.

Still convince that the purge thread is not perfectly working on 32, but at list not blocked.

What we have noticed anyhow is that Documentation is not correctly stating the different behaviour after the changes happened in the purge log and the increased number of possible undolog in InnoDB 1.1.

So now more then a possible bug on the code, I would say we have a bad/missed documentation on how the history list needs to be seen from previous MySQL version and the current one.

Regards
a

Thank you for the feedback.

Verified as described.

To repeat:

1. Start MTR as ./mtr --start[-dirty] innodb --mysqld=--innodb_buffer_pool_size=8388608000 &
2. Download employees database from http://dev.mysql.com/doc/employee/en/employee.html
3. Run:

while true; do ../client/mysql -uroot -S ./var/tmp/mysqld.1.sock employees -e "begin;insert into sal select * from salaries;commit; truncate sal; show engine innodb status;" >>bug_hl.txt; done

In 5.1 bug_hl.txt contains values of History list length as follows:

4,5,6,7,8,9,10,4,6,7,.... 

Values go up, then down.

In 5.5 it only grows. At least I MTR died because timeout before it started decreasing.

Another problem: I run SHOW ENGINE INNODB STATUS in second terminal while this loop were running. In 5.1 it returns immediately while in 5.5 I had to wait up to 30 seconds.