MySQL Bugs: #21685: Old bug (purge reached the head of the history list) present

Bug #21685	Old bug (purge reached the head of the history list) present
Submitted:	17 Aug 2006 2:20	Modified:	6 Sep 2006 4:54
Reporter:	Carlos C Soto	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.0.22	OS:	Linux (Linux (2.6.17.8))
Assigned to:	Heikki Tuuri	CPU Architecture:	Any

Description:
I'm getting this information InnoDB:

Warning: purge reached the head of the history list,
but its length is still reported as XXXXXX! Make a detailed bug
report, and post it to bugs.mysql.com

I hope I can do a detailed bugreport, but there are not much to send.
(XXXXXX is variable, for me 324232 is a god example)

There are not many information in the web to workarround, so I'm lost.

The warning appears in syslog every ~10secs (I assume on every commit)

The variables on MYSQL are:
have_innodb                     YES
innodb_additional_mem_pool_size 1048576               
innodb_autoextend_increment     8                     
innodb_buffer_pool_awe_mem_mb   0                     
innodb_buffer_pool_size         8388608               
innodb_checksums                ON                    
innodb_commit_concurrency       0                     
innodb_concurrency_tickets      500                   
innodb_data_file_path           ibdata1:10M:autoextend
innodb_data_home_dir                                  
innodb_doublewrite              ON                    
innodb_fast_shutdown            1                     
innodb_file_io_threads          4                     
innodb_file_per_table           OFF                   
innodb_flush_log_at_trx_commit  1                     
innodb_flush_method
innodb_force_recovery           0                     
innodb_lock_wait_timeout        50                    
innodb_locks_unsafe_for_binlog  OFF                   
innodb_log_arch_dir                                   
innodb_log_archive              OFF                   
innodb_log_buffer_size          1048576               
innodb_log_file_size            5242880               
innodb_log_files_in_group       2                     
innodb_log_group_home_dir       ./                    
innodb_max_dirty_pages_pct      90                    
innodb_max_purge_lag            0                     
innodb_mirrored_log_groups      1                     
innodb_open_files               300                   
innodb_support_xa               ON                    
innodb_sync_spin_loops          20                    
innodb_table_locks              ON                    
innodb_thread_concurrency       8                     
innodb_thread_sleep_delay       10000

The InnoDB files are:
-rw-rw---- 1 mysql mysql  25K 2006-07-01 10:15 /var/lib/mysql/ib_arch_log_0000000000
-rw-rw---- 1 mysql mysql 786M 2006-08-16 21:05 /var/lib/mysql/ibdata1
-rw-rw---- 1 mysql mysql 5.0M 2006-08-16 21:05 /var/lib/mysql/ib_logfile0
-rw-rw---- 1 mysql mysql 5.0M 2006-08-16 21:05 /var/lib/mysql/ib_logfile1

How to repeat:
I don't know

Thank you for the report.

Please, try current 5.0.24 version and, if problem will occur again, send entire .err log

The system tablespace is probably corrupt. Please attach the entire .err log.

The problem is corrected, just for the history, I upgrade the mysql server and the problem persist.
I was not able to correct the problem, I made a copy of all the database files to another mysql server and the same problem happend. Finally I backup everything with mysqldump, delete all the mysql server, reinstall and restore the databases. Then the error disapears. I know this is not solved, but in my case this works.

Hello,

This bug was triggered a while ago on one of our mysql servers. I hadn't noticed it until now, when another problem (caused by this issue?) started causing mysql to crash at some intervals. I think mysql now crashes everytime i access a certain InnoDB table, all other tables in innodb are fine. Thi "purge reached the head of the history list" messages have been going on in the log file for several weeks, but today I also got the following error:

070321  8:39:22  InnoDB: Warning: purge reached the head of the history list,
InnoDB: but its length is still reported as 59945! Make a detailed bug
InnoDB: report, and post it to bugs.mysql.com
070321  8:46:27  InnoDB: Warning: purge reached the head of the history list,
InnoDB: but its length is still reported as 59947! Make a detailed bug
InnoDB: report, and post it to bugs.mysql.com
InnoDB: Next record offset is nonsensical 44864 in record at offset 0
InnoDB: rec address 099E8000, first buffer frame 067A4000
InnoDB: buffer pool high end 167A4000, buf fix count 1
070321  9:12:57  InnoDB: Page dump in ascii and hex (16384 bytes):
 len 16384; hex 0000000000000000000000000000000000000000000000000000000. (a load of zeroes here)...000;asc and then a load of spaces at the end, followed by:

070321  9:13:20  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
070321  9:13:20  InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 6 2726346712.
InnoDB: Doing recovery: scanned up to log sequence number 6 2726346722
InnoDB: Last MySQL binlog file position 0 32645118, file name C:\logs\mysql\attan-bin.000210
070321  9:13:20  InnoDB: Started; log sequence number 6 2726346722
070321  9:13:20 [Note] Recovering after a crash using C:/logs/mysql/attan-bin
070321  9:13:21  InnoDB: Warning: purge reached the head of the history list,
InnoDB: but its length is still reported as 59947! Make a detailed bug
InnoDB: report, and post it to bugs.mysql.com
070321  9:13:23 [Note] Starting crash recovery...
070321  9:13:23 [Note] Crash recovery finished.
070321  9:13:24 [Note] C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt: ready for connections.
Version: '5.0.18-nt-log'  socket: ''  port: 3306  MySQL Community Edition (GPL)
InnoDB: a nonsensical page number 0 in a node ptr record at offset 101
070321  9:13:51  InnoDB: Page dump in ascii and hex (16384 bytes):
 len 16384; hex 00000... and then the zeroes again.

I have also seen:

070321  9:13:51  InnoDB: Page checksum 1575996416, prior-to-4.0.14-form checksum 1371122432
InnoDB: stored checksum 0, prior-to-4.0.14-form stored checksum 0
InnoDB: Page lsn 0 0, low 4 bytes of lsn at page end 0
InnoDB: Page number (if stored to page already) 0,
InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 0
070321  9:14:07  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
070321  9:14:08  InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 6 2726346798.
InnoDB: Doing recovery: scanned up to log sequence number 6 2726348396
070321  9:14:08  InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 
InnoDB: Apply batch completed
InnoDB: Last MySQL binlog file position 0 32645118, file name C:\logs\mysql\attan-bin.000210
070321  9:14:09  InnoDB: Started; log sequence number 6 2726348396
070321  9:14:09 [Note] Recovering after a crash using C:/logs/mysql/attan-bin
070321  9:14:09 [Note] Starting crash recovery...
070321  9:14:09 [Note] Crash recovery finished.
070321  9:14:09 [Note] C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt: ready for connections.
Version: '5.0.18-nt-log'  socket: ''  port: 3306  MySQL Community Edition (GPL)
070321  9:14:19  InnoDB: Warning: purge reached the head of the history list,
InnoDB: but its length is still reported as 59949! Make a detailed bug

I think this innodb tablespace was created with mysql 4.0 or 4.1. Is this the source of the problem?

I assume the tablespace is corrupt in some way - can i repair the data in some way? Do i need to convert the tablespace to a newer format suitable for mysql 5.0? The tablespace is about 40Gb large, so mysqldumping and recreating the whole database is not a good idea since it would cause serious downtime to a production system. 

I am using mysql-5.0.18-nt on windows. Would an upgrade to the latest version solve this issue?

Related Bug #82213