MySQL Bugs: #69623: since 5.5.32 & 5.6.12, innodb cant start with own multi-file tablespace

Bug #69623	since 5.5.32 & 5.6.12, innodb cant start with own multi-file tablespace
Submitted:	30 Jun 2013 11:25	Modified:	5 Jul 2013 16:24
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	5.5.32	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	regression

Description:
5.5.32 fails to open the second tablespace:
InnoDB: Error: checksum mismatch in data file .\ib2
InnoDB: Could not open or create data files.

5.6.12 fails like this:

[ERROR] InnoDB: Space id in fsp header 65592,but in the page header 0
[ERROR] InnoDB: checksum mismatch in data file .\ib2
[ERROR] InnoDB: Could not open or create the system tablespace. If you tried to add new data files to the system tablespace, and it failed here, you should now edit innodb_data_file_path in my.cnf back to what it was, and remove the new ibdata files InnoDB created in this failed attempt. InnoDB only wrote those files full of zeros, but did not yet use them in any way. But
be careful: do not remove old data files which contain your precious data!

This is a major problem, many folks will be unable to upgrade at all.

How to repeat:
start 5.5.32 or 5.6.12 with new tablespace, e.g.

--innodb-data-file-path="ib1:10M;ib2:20M:autoextend"

insert data and restart instance:

------
drop table if exists t1;
create table t1(a int primary key,b blob,c date,key(a,b(10),c))engine=innodb;
replace into t1 values (1,'test',now()),(2,'test',now()),
(3,'test',now()),(4,'test',now());
set @a:=5;
replace into t1(a,b,c) select @a:=@a+1,rand(),now() 
from t1 a,t1 b,t1 c,t1 d,t1 e,t1 f,t1 g,t1 h,t1 i, t1 j;
-----

also: http://bugs.mysql.com/bug.php?id=69573

Added a changelog entry for 5.5.33, 5.6.13, 5.7.2:

"InnoDB would fail to open a tablespace that has multiple data files."

Thank you for the bug report.

bug#69573 was marked as a duplicate.

Note that you can receive this error message:

[ERROR] InnoDB: Space id in fsp header 65592(or any other number),but in the page header 0

even for 5.6.11 and perhaps earlier versions if you have more than one file for the InnoDB shared tablespace.

If this is reported for a second or later file in the shared tablespace it is a false alarm. These files are expected to have a 0 for the space id in their header. The problem is an insufficiently selective check for damage.

If you do receive that error message in an earlier version you should not upgrade to 5.6.12 or 5.5.32, because the extra error checking will prevent the server from starting after the upgrade. Instead, wait for 5.6.13 or 5.5.33 and upgrade to one of those.

If you do upgrade to the affected versions and the server no longer starts, either upgrade to a later version or downgrade to 5.6.11 or 5.5.31. The unnecessary error message causes no damage and no additional corrective action is required.

James Day, MySQL Senior Principal Support Engineer, Oracle

The following limitation has been documented in the 5.5.32 and 5.6.12 release notes:

""InnoDB" may fail to open a tablespace that has multiple data files due to
newly introduced corruption checking functionality. It is recommended that
you do not upgrade to this version if you have more than one file for your
shared "InnoDB" tablespace. If you have upgraded to an affected version
and the server no longer starts, you can upgrade to a later version when
it becomes available or downgrade to an earlier version."

I've got below error with 5.6.10 MySQL Community Server (GPL)

160504 12:15:33 mysqld_safe Starting mysqld daemon with databases from /data/mysqldata
2016-05-04 12:15:33 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2016-05-04 12:15:33 2271 [Note] Plugin 'FEDERATED' is disabled.
2016-05-04 12:15:33 2271 [Note] InnoDB: The InnoDB memory heap is disabled
2016-05-04 12:15:33 2271 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2016-05-04 12:15:33 2271 [Note] InnoDB: Compressed tables use zlib 1.2.3
2016-05-04 12:15:33 2271 [Note] InnoDB: CPU does not support crc32 instructions
2016-05-04 12:15:33 2271 [Note] InnoDB: Using Linux native AIO
2016-05-04 12:15:33 2271 [Note] InnoDB: Initializing buffer pool, size = 10.7G
2016-05-04 12:15:35 2271 [Note] InnoDB: Completed initialization of buffer pool
2016-05-04 12:15:35 2271 [ERROR] InnoDB: Space id in fsp header 6110973,but in the page header 0
2016-05-04 12:15:35 2271 [ERROR] InnoDB: Space id in fsp header 8011517,but in the page header 0
2016-05-04 12:15:35 2271 [ERROR] InnoDB: Space id in fsp header 5516166,but in the page header 0
2016-05-04 12:15:35 2271 [ERROR] InnoDB: Space id in fsp header 3750772,but in the page header 0
2016-05-04 12:15:35 2271 [ERROR] InnoDB: Space id in fsp header 5187663,but in the page header 0
2016-05-04 12:15:35 2271 [ERROR] InnoDB: Space id in fsp header 276634,but in the page header 0
2016-05-04 12:15:35 2271 [ERROR] InnoDB: Space id in fsp header 2375421,but in the page header 0
2016-05-04 12:15:35 2271 [ERROR] InnoDB: Space id in fsp header 276634,but in the page header 0
2016-05-04 12:15:35 2271 [ERROR] InnoDB: Space id in fsp header 3291995,but in the page header 0
2016-05-04 12:15:35 2271 [ERROR] InnoDB: Space id in fsp header 4995906,but in the page header 0
2016-05-04 12:15:35 2271 [ERROR] InnoDB: Space id in fsp header 1129337,but in the page header 0
2016-05-04 12:15:35 2271 [ERROR] InnoDB: Space id in fsp header 1064536,but in the page header 0
2016-05-04 12:15:35 2271 [ERROR] InnoDB: Space id in fsp header 9256536,but in the page header 0
2016-05-04 12:15:35 2271 [ERROR] InnoDB: Space id in fsp header 4864829,but in the page header 0
2016-05-04 12:15:35 2271 [ERROR] InnoDB: Space id in fsp header 4864829,but in the page header 0
2016-05-04 12:15:35 2271 [ERROR] InnoDB: Space id in fsp header 2505608,but in the page header 0
2016-05-04 12:15:35 2271 [ERROR] InnoDB: Space id in fsp header 8992402,but in the page header 0
2016-05-04 12:15:35 2271 [ERROR] InnoDB: Space id in fsp header 5258134,but in the page header 0
2016-05-04 12:15:35 2271 [ERROR] InnoDB: Space id in fsp header 342984,but in the page header 0
2016-05-04 12:15:35 2271 [ERROR] InnoDB: Space id in fsp header 3678452,but in the page header 0
2016-05-04 12:15:35 2271 [ERROR] InnoDB: Space id in fsp header 8072479,but in the page header 0
2016-05-04 12:15:35 2271 [ERROR] InnoDB: Space id in fsp header 342984,but in the page header 0
2016-05-04 12:15:35 2271 [ERROR] InnoDB: Space id in fsp header 6307469,but in the page header 0
2016-05-04 12:15:35 2271 [ERROR] InnoDB: Space id in fsp header 342984,but in the page header 0
2016-05-04 12:15:35 2271 [ERROR] InnoDB: Space id in fsp header 3937731,but in the page header 0
2016-05-04 12:15:35 2271 [ERROR] InnoDB: Space id in fsp header 5783342,but in the page header 0
2016-05-04 12:15:35 2271 [ERROR] InnoDB: Space id in fsp header 1981282,but in the page header 0
2016-05-04 12:15:35 2271 [ERROR] InnoDB: Space id in fsp header 3160937,but in the page header 0
2016-05-04 12:15:35 2271 [ERROR] InnoDB: Space id in fsp header 1456957,but in the page header 0
2016-05-04 12:15:35 2271 [ERROR] InnoDB: Space id in fsp header 342984,but in the page header 0
2016-05-04 12:15:35 2271 [ERROR] InnoDB: Space id in fsp header 7356172,but in the page header 0
2016-05-04 12:15:35 2271 [ERROR] InnoDB: Space id in fsp header 2898821,but in the page header 0
2016-05-04 12:15:35 2271 [ERROR] InnoDB: Space id in fsp header 3742840,but in the page header 0
2016-05-04 12:15:35 2271 [ERROR] InnoDB: Space id in fsp header 2243464,but in the page header 0
2016-05-04 12:15:35 2271 [Note] InnoDB: Highest supported file format is Barracuda.
2016-05-04 12:15:36 2271 [Note] InnoDB: 128 rollback segment(s) are active.