MySQL Bugs: #65857: coredump after discard/import tablespace

Bug #65857	coredump after discard/import tablespace
Submitted:	10 Jul 2012 3:01	Modified:	10 Jan 2013 12:22
Reporter:	xiaobin lin (OCA)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S1 (Critical)
Version:	5.5.25, 5.5.27	OS:	Linux (x86_64)
Assigned to:		CPU Architecture:	Any
Tags:	coredump.import, Tablespace

Description:
I do not know whether there is a duplicate bug report.But this can be repeated simplely. In the shell bellow, the server coredump when run the last "select".

How to repeat:

mysql  -Srun/mysql.sock -uroot   test -e "drop table if exists tb; create table tb (c int); insert into tb values(1);"
echo 1
cp data/test/tb.ibd ~/
echo 2
mysql  -Srun/mysql.sock -uroot   test -e "alter table tb discard tablespace;"
echo 3
cp ~/tb.ibd data/test/
echo 4
mysql  -Srun/mysql.sock -uroot   test -e "alter table tb import tablespace;"
echo 5
mysql  -Srun/mysql.sock -uroot   test -e "select * from tb;"
echo 6

Suggested fix:
did not look into it till now

I can not repeat this with 5.5.26:

macbook-pro:5.5 openxs$ bin/mysql -uroot test -e "drop table if exists tb; create table tb (c int); insert into tb values(1);"
macbook-pro:5.5 openxs$ cp data/test/tb.ibd /tmp
macbook-pro:5.5 openxs$ bin/mysql -uroot test -e "alter table tb discard tablespace;"
macbook-pro:5.5 openxs$ cp /tmp/tb.ibd data/testmacbook-pro:5.5 openxs$ bin/mysql -uroot test -e "alter table tb import tablespace;"
macbook-pro:5.5 openxs$ bin/mysql -uroot testReading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.5.26-debug Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like 'innodb_file%';
+--------------------------+----------+
| Variable_name            | Value    |
+--------------------------+----------+
| innodb_file_format       | Antelope |
| innodb_file_format_check | ON       |
| innodb_file_format_max   | Antelope |
| innodb_file_per_table    | ON       |
+--------------------------+----------+
4 rows in set (0.00 sec)

mysql> select * from tb;
+------+
| c    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

What exact server version, 5.5.x, do you use?

Hi, my environment:

mysql> show variables like 'innodb_file%';
+--------------------------+-----------+
| Variable_name            | Value     |
+--------------------------+-----------+
| innodb_file_format       | Antelope  |
| innodb_file_format_check | ON        |
| innodb_file_format_max   | Barracuda |
| innodb_file_per_table    | ON        |
+--------------------------+-----------+
4 rows in set (0.00 sec)

mysql> select version();
+------------+
| version()  |
+------------+
| 5.5.25-log |
+------------+
1 row in set (0.02 sec)

Please, send your entire my.cnf file content. Please, send also the output of

uname -a

OS command. My test was on Mac OS X 10.5.6.

uname -a 
Linux my031090.sqa.cm4 2.6.18-164.el5 #1 SMP Tue Aug 18 15:51:48 EDT 2009 x86_64 x86_64 x86_64 GNU/Linux

Verified on 64-bit FC14:

[openxs@chief 5.5]$ bin/mysql -uroot test -e "drop table if exists tb; create table tb (c int); insert into tb values(1);"
[openxs@chief 5.5]$ cp data/test/tb.ibd ~/
[openxs@chief 5.5]$ bin/mysql -uroot test -e "alter table tb discard tablespace;"
[openxs@chief 5.5]$ cp ~/tb.ibd data/test/
[openxs@chief 5.5]$ bin/mysql -uroot test -e "alter table tb import tablespace;"
[openxs@chief 5.5]$ bin/mysql -uroot test -e "select * from tb"                 ERROR 2013 (HY000) at line 1: Lost connection to MySQL server during query
[openxs@chief 5.5]$ 120711 10:53:11 mysqld_safe Number of processes running now: 0
120711 10:53:11 mysqld_safe mysqld restarted

[openxs@chief 5.5]$ tail -120 data/chief.err
120711 10:50:30 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
120711 10:50:30 [Note] Server socket created on IP: '0.0.0.0'.
120711 10:50:30 [Note] Event Scheduler: Loaded 0 events
120711 10:50:30 [Note] /home/openxs/dbs/5.5/bin/mysqld: ready for connections.
Version: '5.5.27-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70120711 10:53:11  InnoDB: Assertion failure in thread 140356254992128 in file buf0buf.c line 2080
InnoDB: Failing assertion: block->page.space == page_get_space_id(page_align(ptr))
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
07:53:11 UTC - mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.

key_buffer_size=8388608
read_buffer_size=131072
max_used_connections=1
max_threads=151
thread_count=1
connection_count=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 338524 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x2806090
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 7fa73cb87e68 thread_stack 0x40000
/home/openxs/dbs/5.5/bin/mysqld(my_print_stacktrace+0x35)[0x8cda29]
/home/openxs/dbs/5.5/bin/mysqld(handle_fatal_signal+0x408)[0x75adbc]
/lib64/libpthread.so.0[0x3ffac0eeb0]
/lib64/libc.so.6(gsignal+0x35)[0x3ffa8330c5]
/lib64/libc.so.6(abort+0x186)[0x3ffa834a76]
/home/openxs/dbs/5.5/bin/mysqld[0x9ef7fd]
/home/openxs/dbs/5.5/bin/mysqld[0x9ef8bb]
/home/openxs/dbs/5.5/bin/mysqld[0xa632be]
/home/openxs/dbs/5.5/bin/mysqld[0xa3744a]
/home/openxs/dbs/5.5/bin/mysqld[0xa3e7b0]
/home/openxs/dbs/5.5/bin/mysqld[0x9f3780]
/home/openxs/dbs/5.5/bin/mysqld[0xa026ce]
/home/openxs/dbs/5.5/bin/mysqld[0xa02c26]
/home/openxs/dbs/5.5/bin/mysqld[0x9eff4f]
/home/openxs/dbs/5.5/bin/mysqld[0x9da4ab]
/home/openxs/dbs/5.5/bin/mysqld[0x9e4261]
/home/openxs/dbs/5.5/bin/mysqld[0x9a0ca7]
/home/openxs/dbs/5.5/bin/mysqld[0x97536c]
/home/openxs/dbs/5.5/bin/mysqld[0x975e8a]
/home/openxs/dbs/5.5/bin/mysqld[0x976083]
/home/openxs/dbs/5.5/bin/mysqld(_Z13rr_sequentialP11READ_RECORD+0x7b)[0x856280]
/home/openxs/dbs/5.5/bin/mysqld[0x6410c9]
/home/openxs/dbs/5.5/bin/mysqld(_Z10sub_selectP4JOINP13st_join_tableb+0xc5)[0x63f4b1]
/home/openxs/dbs/5.5/bin/mysqld[0x63f099]
/home/openxs/dbs/5.5/bin/mysqld(_ZN4JOIN4execEv+0x2408)[0x628534]
/home/openxs/dbs/5.5/bin/mysqld(_Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_ES2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_select_lex+0x378)[0x628ce4]
/home/openxs/dbs/5.5/bin/mysqld(_Z13handle_selectP3THDP3LEXP13select_resultm+0x19c)[0x620dfc]
/home/openxs/dbs/5.5/bin/mysqld[0x5fd23f]
/home/openxs/dbs/5.5/bin/mysqld(_Z21mysql_execute_commandP3THD+0x94d)[0x5f5fc5]
/home/openxs/dbs/5.5/bin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x213)[0x5ff3a9]
/home/openxs/dbs/5.5/bin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0xabc)[0x5f3696]
/home/openxs/dbs/5.5/bin/mysqld(_Z10do_commandP3THD+0x297)[0x5f29c7]
/home/openxs/dbs/5.5/bin/mysqld(_Z24do_handle_one_connectionP3THD+0x199)[0x6d47c9]
/home/openxs/dbs/5.5/bin/mysqld(handle_one_connection+0x33)[0x6d42d5]
/lib64/libpthread.so.0[0x3ffac06ccb]
/lib64/libc.so.6(clone+0x6d)[0x3ffa8e0c2d]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (7fa730004c10): select * from tb
Connection ID (thread ID): 5
Status: NOT_KILLED

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
120711 10:53:11 mysqld_safe Number of processes running now: 0
120711 10:53:11 mysqld_safe mysqld restarted
120711 10:53:11 [Note] Plugin 'FEDERATED' is disabled.
120711 10:53:11 InnoDB: !!!!!!!! UNIV_DEBUG switched on !!!!!!!!!
120711 10:53:11 InnoDB: The InnoDB memory heap is disabled
120711 10:53:11 InnoDB: Mutexes and rw_locks use GCC atomic builtins
120711 10:53:11 InnoDB: Compressed tables use zlib 1.2.5
120711 10:53:11 InnoDB: Using Linux native AIO
120711 10:53:11 InnoDB: Initializing buffer pool, size = 128.0M
120711 10:53:11 InnoDB: Completed initialization of buffer pool
120711 10:53:11 InnoDB: highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 304555867
120711 10:53:11  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Doing recovery: scanned up to log sequence number 307141798
InnoDB: 1 transaction(s) which must be rolled back or cleaned up
InnoDB: in total 442362 row operations to undo
InnoDB: Trx id counter is 1E00
120711 10:53:12  InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
InnoDB: Starting in background the rollback of uncommitted transactions
120711 10:53:12  InnoDB: Rolling back trx with id 1A07, 442362 rows to undo

InnoDB: Progress in percents: 1120711 10:53:12  InnoDB: Waiting for the background threads to start
 2120711 10:53:13 InnoDB: 1.1.8 started; log sequence number 307141798
120711 10:53:13 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
120711 10:53:13 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
120711 10:53:13 [Note] Server socket created on IP: '0.0.0.0'.
120711 10:53:13 [Note] Event Scheduler: Loaded 0 events
120711 10:53:13 [Note] /home/openxs/dbs/5.5/bin/mysqld: ready for connections.
Version: '5.5.27-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100
InnoDB: Rolling back of trx id 1A07 completed
120711 10:54:17  InnoDB: Rollback of non-prepared transactions completed

Duplicate of bug #65429?

Internal analysis: it is not correct to copy an ibd file out of the data directory when the server is running.  This will leave the ibd file in an inconsistent state. Since the steps to reproduce this bug involves this "wrong procedure", we will close as "not a bug".