MySQL Bugs: #69274: MySQL 5.6 crashes during startup with assertion failure "sym

Bug #69274	MySQL 5.6 crashes during startup with assertion failure "sym_node->table != NUL"
Submitted:	17 May 2013 23:16	Modified:	9 Nov 2015 13:21
Reporter:	PJ Cardenas	Email Updates:
Status:	Unsupported	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S1 (Critical)
Version:	5.6.10	OS:	Linux (CentOS 6.2 64-bit)
Assigned to:		CPU Architecture:	Any
Tags:	assertion failure, innodb, mysql 5.6

Description:
I am trying to migrate from Percona 5.5.30 to Percona 5.6.10. However, whenever I try to start the 5.6 server, it crashes with the following error:

------
InnoDB: Assertion failure in thread 140263797983008 in file pars0pars.cc line 865
InnoDB: Failing assertion: sym_node->table != NULL
------

Right above this message are the following lines which I think are related to the crash:

------
2013-05-17 15:59:10 12504 [Warning] Invalid (old?) table or database name '#sql5307_9777_c'
2013-05-17 15:59:10 7f91b5da8720 InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: If you are installing InnoDB, remember that you must create
InnoDB: directories yourself, InnoDB does not create them.
2013-05-17 15:59:10 12504 [ERROR] InnoDB: Could not find a valid tablespace file for 'tmp/#sql5307_9777_c'. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html for how to resolve the issue.
2013-05-17 15:59:10 12504 [ERROR] InnoDB: Tablespace open failed for '"tmp".<result 2 when explaining filename '#sql5307_9777_c'>', ignored.
2013-05-17 15:59:10 12504 [Warning] Invalid (old?) table or database name '#sql5307_9777_c'
------

The interesting part is that in Percona 5.5.30, the server starts and operates normally. In Percona 5.6.10, I can't even start the server. The crash is reproducible in stock versions of MySQL (5.6.10 and 5.6.11) so I don't think that this is specific to Percona.

How to repeat:
Not sure how the tablespace file got lost. It seems that an operation that requires a temporary table did not finish normally (e.g. server crashed or killed improperly).

Suggested fix:
If the assertion test in pars0pars.cc line 865 is not critical, then let MySQL 5.6 mimic the behavior of MySQL 5.5 in which I can still start and use the server even if the tablespace file of a temporary table is missing.

I would also like to know how to deal with orphaned temporary table. The suggested workaround in http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html does not work because:
1. Temporary tables are session-specific. If I try to re-create it in any database and copy the .frm file, innodb would simply think that it is a new temporary table. There is no way to link the copied .frm file to the session that created the temporary table.
2. If I understand if correctly, the tmpdir location is shared across all databases in the server. Similar to reason #1, I don't know how to link the .frm file to the correct database.

Log file from Percona 5.5.30 (startup successful)

Attachment: percona5.5.30.log (application/octet-stream, text), 3.46 KiB.

Log file from Percona 5.6.10 (crash on startup)

Attachment: percona5.6.10.log (application/octet-stream, text), 5.23 KiB.

Log file from MySQL 5.6.11 (crash on startup)

Attachment: mysql5.6.11.log (application/octet-stream, text), 4.49 KiB.

Table monitor error from Percona 5.5.30

Attachment: table_monitor.log (application/octet-stream, text), 176 bytes.

Thank you for the bug report. You are reporting a bug with Percona server please report the bug here if you are able to repeat with our binaries would be nice if you report the bug on Percona bug track too. Thanks.

I was able to reproduce the bug with your pre-compiled binaries (http://dev.mysql.com/get/Downloads/MySQL-5.6/mysql-5.6.11-linux-glibc2.5-x86_64.tar.gz/fro...). I mentioned it in the ticket description. I also attached the error log that I got from MySQL 5.6.11. You'd see that the error message and stack trace are very similar to what I got from Percona 5.6.10

we need to establish some guideline about how to upgrade.  in my opinion, upgrades should be done after a slow shutdown (innodb_fast_shutdown=0) for best results, a normal shutdown.

Using MEB to perform an upgrade is risky, since it involves a crash recovery and upgrade at the same time.  Can anybody from innodb confirm, comment, deny this?

I am seeing this exact same assertion and call stack issue when preparing a series of incremental backups taken with Percona XtraBackup against Percona Server PS 5.1 with an RQG load running against the server during backup. The assertion appears during the crash recovery/prepare phase. It is easily reproducible. I will switch to using pure MySQL and try to reproduce.

it is not supported to do upgrade + crash recovery in one step.
You need to restore hot backups to the same version server from which they originated.

[27 May 2013 15:19] Shane Bester
> we need to establish some guideline about how to upgrade.  in my opinion, upgrades should be done after a slow shutdown (innodb_fast_shutdown=0) for best results, a normal shutdown.

We had this error when doing a snapshot, syncing the snapshot and doing an updade. Stopping mysql cleanly, doing the snapshot and then start the original mysql bypasses the whole innodb recovery and is a good way to work around this issue.

Perhaps not usefull for the poster regarding the XtraDB backup though. More usefull for those who want to upgrade to 5.6

Bug #87966 marked as duplicate of this one