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
Triage: Needs Triage: D3 (Medium)

[26 Mar 2012 12:31] Marco Tusa
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
[26 Mar 2012 12:36] Marco Tusa
Forgot to mention all platforms where 32bit.
Still not tested on 64.

Regards
[2 Apr 2012 19:19] Marco Tusa
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
[2 Apr 2012 22:54] Sveta Smirnova
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.