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.
