InnoDB: unable to purge a record

[Jenni Snyder]

We have a single production replica that has been upgraded to Percona Server 5.5.21-55-log. It is replicating from our production Percona Server 5.0.22, but serving no queries.

Over the weekend, this appeared in the error log:

120527 18:32:03 InnoDB: unable to purge a record
InnoDB: tuple DATA TUPLE: 4 fields;
 0: len 4; hex 80000006; asc ;;
 1: SQL NULL;
 2: len 8; hex 0000000000107740; asc w@;;
 3: len 4; hex 80b6864b; asc K;;

InnoDB: record PHYSICAL RECORD: n_fields 4; compact format; info bits 32
 0: len 4; hex 80000006; asc ;;
 1: SQL NULL;
 2: len 8; hex 0000000000107740; asc w@;;
 3: len 4; hex 80b6864b; asc K;;

space 1608 offset 5134 (1 records, index id 4390)
InnoDB: Submit a detailed bug report to http://bugs.mysql.com

Because this machine is just replicating, we observed no query performance issues or other log messages/issues on the machine, and I have found no log messages on the server indicating there was a hardware issue.

We went through the normal upgrade process from 5.0 to 5.5 on this machine, but have not done much tuning yet. I have attached the my.cnf file, slightly modified from our Percona Server 5.0 instances. This blocks us from upgrading the rest of our servers to 5.5, please advise if this is an upgrade/tuning issue or something we can avoid.

thank you!

[Bart Verwilst (verwilst)]

We've been seeing similar errors on our database ( 500GB table ). Using Percona 5.5.23-55-log, the trigger was doing an "ALTER TABLE t ROW_FORMAT=COMPRESSED".

In a screen, i started it, and when i came back later that day, this is what i saw:

mysql> alter table t ROW_FORMAT=COMPRESSED;
ERROR 2013 (HY000): Lost connection to MySQL server during query

[Raghavendra D Prabhu (raghavendra-prabhu)]

@Jsynder, Purge is failing becuase you may have a corrupted secondary index,

you can get its name from

select IST.SCHEMA, IST.NAME, ISI.NAME from INNODB_SYS_TABLES IST INNER JOIN INNODB_SYS_INDEXES ISI ON IST.TABLE_ID = ISI.TABLE_ID and ISI.INDEX_ID=4390;

The message is because of this change http://sprunge.us/DggK?c from bug -- http://bugs.mysql.com/bug.php?id=61104

That is -- an assertion failure was replaced by an if condition so that the index can be fixed and the the vicious loop of crash -> start -> purge -> crash be avoided. (you can see that in the last message in that bug)

The solution proposed there is to DROP/CREATE that particular INDEX or mysqldump and recreate that particular table if corruption is more severe.

Until you do that you can prevent purge buffering by setting http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_change_buffering variable to inserts.

"InnoDB: record PHYSICAL RECORD: n_fields 4; compact format; info bits 32" -- the info bits == 32 suggests that the record is delete marked, so you are hitting "page_get_n_recs(page) <= 1"

[Jenni Snyder (jsnyder)]

Thank you for your response, Raghavendra!

I have rebuilt the index you identified, and we will keep an eye on it from there.

[Stewart Smith (stewart)]

Awesome that there's a solution. I'll mark the bug as "Invalid" as it's a data corruption issue rather than software bug (we hope). feel free to reopen here if it turns out to not be corruption.

[Jenni Snyder (jsnyder)]

Thanks again, Raghavendra and Stewart, we have not seen the issue since rebuilding the index.

Raghavendra, can you tell me if this error message always indicates index corruption? If so, is it worth it to request that the error message be updated to be more actionable?

[Alexey Kopytov (akopytov)]

It's essentially a followup to bug #841621 and the upstream http://bugs.mysql.com/bug.php?id=61104

Under some unknown circumstances the change buffer may get corrupted, which led to an assertion failures reported in the aforementioned bugs. The "fix" for http://bugs.mysql.com/bug.php?id=61104 introduced in MySQL 5.5.21 simply replaced the assertion with a diagnostic printout reported in this bug in the hope to investigate the source of corruption. To my knowledge, no progress has been made so far with further investigations.

To fix corruption, start with innodb_force_recovery=4 and innodb_purge_threads=0 and rebuild

The workaround to prevent this corruption in future is to set innodb_change_buffering=inserts.

[Jenni Snyder (jsnyder)]

Thank you Alexey, I'm sorry for not understanding that correctly from the comment above!

[Raghavendra D Prabhu (raghavendra-prabhu)]

Marking this as a duplicate of https://bugs.launchpad.net/percona-server/+bug/841621 based on https://bugs.launchpad.net/percona-server/+bug/1006095/comments/7