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 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)"

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.041 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.