MySQL Bugs: #31417: purge reached the head of the history list & Assertion failure

Bug #31417	purge reached the head of the history list & Assertion failure
Submitted:	5 Oct 2007 6:50	Modified:	13 Oct 2008 23:34
Reporter:	Manas Garg	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: General	Severity:	S2 (Serious)
Version:	5.0.46	OS:	Linux (FC4)
Assigned to:		CPU Architecture:	Any

Description:
Hello,

We have a two node replication setup wherein each node replicates to the other. One of the node is designated as an active node and all the writes are performed on that node.

We got an error wherein, mysql was continuously restarting. I have attached the .err and my.cnf files separately.

[root@fltwd-intdep02 mydata]# uname -a
Linux fltwd-intdep02.nevisnetworks.com 2.6.17-1.2142_FC4smp #1 SMP Sat Aug 12 08:16:08 EDT 2006 i686 i686 i386 GNU/Linux

Please note that this DB was getting this problem since yesterday night. Today morning it was recreated from scratch and it worked only for 1 hour. After that, it started restarting again.

How to repeat:
Not sure.

.err file for the mysql.

Attachment: slave.err (application/octet-stream, text), 20.54 KiB.

my.cnf

Attachment: my.cnf (application/octet-stream, text), 5.26 KiB.

I just recreated the standby db from scratch and got the same purge related warning again. I am monitoring the system to see if the assertion failure also reappears.

Thank you for the report.

Please indicate how much phyical RAM do you have  and provide output of SHOW CREATE TABLE QRTZ_FIRED_TRIGGERS and SHOW TABLE STATUS QRTZ_FIRED_TRIGGERS

Hello,

The system has 4Gb of physical RAM. The output of commands follows.

mysql> mysql> SHOW CREATE TABLE QRTZ_FIRED_TRIGGERS;
+---------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table               | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
+---------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| QRTZ_FIRED_TRIGGERS | CREATE TABLE `QRTZ_FIRED_TRIGGERS` (
  `ENTRY_ID` varchar(95) collate utf8_bin NOT NULL,
  `TRIGGER_NAME` varchar(80) collate utf8_bin NOT NULL,
  `TRIGGER_GROUP` varchar(80) collate utf8_bin NOT NULL,
  `IS_VOLATILE` varchar(1) collate utf8_bin NOT NULL,
  `INSTANCE_NAME` varchar(80) collate utf8_bin NOT NULL,
  `FIRED_TIME` bigint(13) NOT NULL,
  `STATE` varchar(16) collate utf8_bin NOT NULL,
  `JOB_NAME` varchar(80) collate utf8_bin default NULL,
  `JOB_GROUP` varchar(80) collate utf8_bin default NULL,
  `IS_STATEFUL` varchar(1) collate utf8_bin default NULL,
  `REQUESTS_RECOVERY` varchar(1) collate utf8_bin default NULL,
  PRIMARY KEY  (`ENTRY_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin |
+---------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW TABLE STATUS FROM impact LIKE 'QRTZ_FIRED_TRIGGERS';
+---------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------+----------+----------------+-------------------------+
| Name                | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation | Checksum | Create_options | Comment                 |
+---------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------+----------+----------------+-------------------------+
| QRTZ_FIRED_TRIGGERS | InnoDB |      10 | Compact    |   38 |            431 |       16384 |               0 |            0 |         0 |           NULL | 2007-10-08 13:50:26 | NULL        | NULL       | utf8_bin  |     NULL |                | InnoDB free: 3394560 kB |
+---------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------+----------+----------------+-------------------------+
1 row in set (0.00 sec)

Thank you for the feedback.

Error "purge reached the head of the history list" is printed if InnoDB tablespace is corrupted. Additionally you have in the error log: "071005  9:36:34  InnoDB: Database was not shut down normally!"

Also you said "I just recreated the standby db from scratch and got the same purge related warning again.". Did you recreate InnoDB tablespace? (I mean dump database, move ib* files to save place, start mysqld, load dump) If so and you still get repeatable errors please try to find why database crashes. In general we need larger error log file with records before first "purge reached the head of the history list" occurs. Also please try to upgrade to current version 5.0.67.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".