Bug #78067 InnoDB: Failing assertion: zip_size <= (1 << 14) with import tablespace
Submitted: 14 Aug 2015 11:25 Modified: 12 May 2018 9:07
Reporter: Shahriyar Rzayev (OCA) Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: DDL Severity:S1 (Critical)
Version:5.6.19,5.6.19-debug OS:Linux (CentOS 6, Ubuntu 14.04)
Assigned to: CPU Architecture:Any

[14 Aug 2015 11:25] Shahriyar Rzayev
Description:
Install MySQL 5.6.19 from official Oracle repo.
In my Master server, because of unknown reason i got an error:

2015-08-04 14:26:58 8164 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_1fe4_0.MYI'; try to repair it
2015-08-04 14:26:58 8164 [ERROR] Got an error from unknown thread, /pb2/build/sb_0-12116699-1399373469.23/rpm/BUILD/mysql-5.6.19/mysql-5.6.19/s
torage/myisam/mi_write.c:223
2015-08-04 14:27:02 8164 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_1fe4_0.MYI'; try to repair it
2015-08-04 14:27:02 8164 [ERROR] Got an error from unknown thread, /pb2/build/sb_0-12116699-1399373469.23/rpm/BUILD/mysql-5.6.19/mysql-5.6.19/s
torage/myisam/mi_write.c:223
2015-08-04 14:27:06 8164 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_1fe4_0.MYI'; try to repair it
2015-08-04 14:27:06 8164 [ERROR] Got an error from unknown thread, /pb2/build/sb_0-12116699-1399373469.23/rpm/BUILD/mysql-5.6.19/mysql-5.6.19/s
torage/myisam/mi_write.c:223
2015-08-04 14:27:12 8164 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_1fe4_0.MYI'; try to repair it
2015-08-04 14:27:12 8164 [ERROR] Got an error from unknown thread, /pb2/build/sb_0-12116699-1399373469.23/rpm/BUILD/mysql-5.6.19/mysql-5.6.19/s
torage/myisam/mi_write.c:223
2015-08-04 14:27:25 8164 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_1fe4_0.MYI'; try to repair it
2015-08-04 14:27:25 8164 [ERROR] Got an error from unknown thread, /pb2/build/sb_0-12116699-1399373469.23/rpm/BUILD/mysql-5.6.19/mysql-5.6.19/s
torage/myisam/mi_write.c:223
/usr/sbin/mysqld(my_print_stacktrace+0x35)[0x8d6885]
/usr/sbin/mysqld(handle_fatal_signal+0x4a4)[0x663234]
/lib64/libpthread.so.0[0x3d65e0f710]
/usr/sbin/mysqld(handler::ha_statistic_increment(unsigned long long system_status_var::*) const+0x8)[0x5a3cb8]
/usr/sbin/mysqld(ha_myisam::write_row(unsigned char*)+0x20)[0x8edf30]
/usr/sbin/mysqld(handler::ha_write_row(unsigned char*)+0xff)[0x5ac29f]
/usr/sbin/mysqld[0x6ba0c9]
/usr/sbin/mysqld[0x6bdfc1]
/usr/sbin/mysqld(sub_select(JOIN*, st_join_table*, bool)+0x1c1)[0x6be4f1]
/usr/sbin/mysqld[0x6bdfc1]
/usr/sbin/mysqld(sub_select(JOIN*, st_join_table*, bool)+0x1c1)[0x6be4f1]
/usr/sbin/mysqld(_150804 14:27:58 mysqld_safe Number of processes running now: 0
150804 14:27:58 mysqld_safe mysqld restarted

After this, MySQL restarted, then i got another problem:

2015-08-04 14:28:00 31831 [Note] InnoDB: Reading tablespace information from the .ibd files...
2015-08-04 14:28:02 31831 [ERROR] InnoDB: Space id in fsp header 1416128883,but in the page header 824195850
09:28:02 UTC - mysqld got signal 11 ;

So it is equal to missing tablespace file.

Decide to copy datadir to local Ubuntu 14.04 desktop, here i have installed MySQL 5.6.19 with debug:

cmake -DCMAKE_INSTALL_PREFIX=/opt/mysql-5.6.19 -DMYSQL_DATADIR=/var/lib/mysql -DSYSCONFDIR=/opt/mysql-5.6.19 -DWITH_SSL=system -DMYSQL_TCP_PORT=3307 -DMYSQL_UNIX_ADDR=/opt/mysql-5.6.19/mysqld.sock -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_DEBUG=1 -DCOMPILATION_COMMENT="Shahriyar Rzayev's Ubuntu MySQL-5.6.19" -DOPTIMIZER_TRACE=1 -DWITH_ZLIB=system -DWITH_VALGRIND=1 -DCMAKE_C_FLAGS=-DHAVE_purify -DCMAKE_CXX_FLAGS=-DHAVE_purify

Attached to GDB using:

run --defaults-file=/opt/mysql-5.6.19/my.cnf --basedir=/opt/mysql-5.6.19 --datadir=/var/lib/mysql --plugin-dir=/opt/mysql-5.6.19/lib/plugin --user=root --log-error=/var/lib/mysql/ubuntu_vm.err --pid-file=/opt/mysql-5.6.19/mysqld-new.pid --socket=/var/lib/mysql/mysql.sock --port=3306

Started MySQL with innodb_force_recovery=1, created same structure table and want to import tablespace of original table:

mysql> select count(*) from news;
ERROR 1814 (HY000): Tablespace has been discarded for table 'news'
mysql> alter table news import tablespace;

It hanged and got SIGABRT:

Program received signal SIGABRT, Aborted.
[Switching to Thread 0x7ffff40a1700 (LWP 29019)]
0x00007ffff637dcc9 in __GI_raise (sig=sig@entry=6) at ../nptl/sysdeps/unix/sysv/linux/raise.c:56
56	../nptl/sysdeps/unix/sysv/linux/raise.c: No such file or directory.

From Error Log:

2015-08-14 15:31:33 28990 [Warning] InnoDB: Table '"tst1"."news"' tablespace is set as discarded.
2015-08-14 15:31:33 7ffff40a1700 InnoDB: cannot calculate statistics for table "tst1"."news" because the .ibd file is missing. For help, please refer to http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html
2015-08-14 15:31:41 28990 [ERROR] Got error 155 when reading table './tst1/news'
2015-08-14 15:38:47 7ffff40a1700  InnoDB: Assertion failure in thread 140737287689984 in file fsp0fsp.ic line 148
InnoDB: Failing assertion: zip_size <= (1 << 14)

zip_size;   /*!< compressed page size of space in bytes, or 0 for uncompressed */

I have not enabled any compression with this table before. Even did not change page size from default 16K.

I wonder if this problem related to "zlib" package differences in Centos and Ubuntu?

How to repeat:
Because of production data, i can't share full datadir or .ibd file.
But maybe reproducible in such manner:

1. Install MySQL 5.6.19 on Centos 6, try to corrupt some tablespace for a table.
2. copy datadir to Ubuntu, install MySQL 5.6.19-debug on Ubuntu.
3. Create same structure table, discard this new table's tablespace, try to import corrupted tablespace file.

Suggested fix:
Trying to understand core problem. That's why no idea for fix.
[14 Aug 2015 11:28] Shahriyar Rzayev
Added bt and bt full output

Attachment: bug_78067.txt (text/plain), 19.82 KiB.

[18 Aug 2015 12:41] Shahriyar Rzayev
No, issue is not related to OS.
Same result with installed CentOS 7 VM too:

2015-08-18 08:38:59 7fffe0bb2700  InnoDB: Assertion failure in thread 140736963749632 in file fsp0fsp.ic line 148
InnoDB: Failing assertion: zip_size <= (1 << 14)
[28 Mar 2017 5:24] Daniel Black
Space id in fsp header 1416128883,but in the page header 824195850

 824195850 -> 0x31203B0A  -> "1 ;\n"
1416128883 -> 0x54686973  -> "This"

Stdout/error going to a innodb file descriptor perhaps?

(tip from Marko)
[12 May 2018 9:07] Shane Bester
two crashes here.  first one is a result of out of disk space in tmpdir
aka crash around "handler::ha_statistic_increment"

second one is due to header of .ibd overwritten with error log outputs.
we've seen this many times.  crash handling is unsafe, and I have not found out why exactly.   Seemed to occur more when plugins/audit are enabled and signal handler is invoked in multiple threads.

solve (1) by upgrade & make mroe tmpdir available, and restore from backup for (2).

Server Exits Unexpectedly Upon Failure to Create Implicit Temporary Tables (Doc ID 1957955.1)

From 5.6.23 change history:
"For failure to create a temporary table due to being out of file descriptors, the server exited rather than returning an error. (Bug #18948649)"