Bug #5851 "ALTER TABLE x DISCARD TABLESPACE" causes server crash
Submitted: 1 Oct 2004 17:03 Modified: 5 Oct 2004 16:34
Reporter: John David Duncan Email Updates:
Status: Closed Impact on me:
Category:MySQL Server: InnoDB storage engine Severity:S1 (Critical)
Version:4.1.4/4.1.6 BK source OS:Linux (Linux 2.4.21-4)
Assigned to: Marko Mäkelä CPU Architecture:Any

[1 Oct 2004 17:03] John David Duncan
Under Intel/Linux, with official 4.1.4 binary, an "ALTER TABLE x DISCARD TABLESPACE" 
statement caused the following crash, with a failed assertion in pars0pars.c line 397

040929 14:06:54  mysqld started
040929 14:06:54  [ERROR] Warning: Asked for 196608 thread stack, but got 126976
040929 14:06:54  InnoDB: Started; log sequence number 0 641958
040929 14:06:54  [ERROR] Slave SQL thread initialized, starting replication in log 'breakout-
bin.000004' at position 9564265, relay log './stargate-relay-bin.000005' position: 220
/usr/local/mysql/bin/mysqld: ready for connections.
Version: '4.1.4-gamma-standard-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Official 
MySQL-standard binary
040929 14:06:54  [ERROR] Slave I/O thread: connected to master 
'rep@breakout.bandalong.com:3306',  replication started in log 'breakout-bin.000004' at position 
040929 14:07:42  [ERROR] Slave I/O thread killed while reading event
040929 14:07:42  [ERROR] Slave I/O thread exiting, read up to log 'breakout-bin.000004', 
position 9564265
040929 14:07:42  [ERROR] Error reading relay log event: slave SQL thread was killed
PARSER ERROR: Unresolved identifier imvenicebeach
040929 14:26:19InnoDB: Assertion failure in thread 45068 in file pars0pars.c line 397
InnoDB: Failing assertion: node
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. See section 6.1 of
InnoDB: http://www.innodb.com/ibman.php about forcing recovery.
mysqld got signal 11;
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.

It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 225791 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

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...
Cannot determine thread, fp=0xbfe3dd58, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://www.mysql.com/doc/en/Using_stack_trace.html and follow instructions on how 
to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do 
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x8679210 = alter table inventoryitem discard tablespace
The manual page at http://www.mysql.com/doc/en/Crashing.html contains
information that should help you find out what is causing the crash.

Number of processes running now: 0

How to repeat:
First try this:
 start server with innodb_file_per_table = 1;
 create database imvenicebeach;
 create table x()...
 insert into x...
 alter table x discard tablespace. 

If that doesn't cause the bug, maybe it is related to this:
  start server with innodb_file_per_table = 0;
  create table and load data
  restart server with innodb_file_per_table = 1;
  use "ALTER TABLE x TYPE=innodb" to move tablespace into .ibd file

(The actual table structure and data are customer information that I cannot disclose
in the bug report, but please contact me if they are needed.  I also have not resolved
the symbols in the stack trace but I might be able to do so if needed. -- jdd@mysql.com.)
[1 Oct 2004 17:05] John David Duncan
effects a customer.
[2 Oct 2004 17:57] John David Duncan
I can also reproduce the problem running the 4.1.5 release on Mac OS X 10.3.
[2 Oct 2004 22:32] MySQL Verification Team
Thank you for the bug report and I was able to repeat on Linux and
Windows with latest BK source.

Below back trace on Windows.

c:\mysql\bin>mysqld --standalone --console --innodb-file-per-table=1
041002 19:23:13  InnoDB: Started; log sequence number 0 43634
mysqld: ready for connections.
Version: '4.1.6-gamma-debug'  socket: ''  port: 3306  Source distribution
PARSER ERROR: Unresolved identifier test
041002 19:24:59InnoDB: Assertion failure in thread 2176 in file C:\mysql-4.1.6-gamma\innobase\pars\pars0pars.c line 397
InnoDB: Failing assertion: node
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. See section 6.1 of
InnoDB: http://www.innodb.com/ibman.php about forcing recovery.
InnoDB: Thread 3924 stopped in file C:\mysql-4.1.6-gamma\innobase\os\os0sync.c line 487
InnoDB: Thread 2432 stopped in file C:\mysql-4.1.6-gamma\innobase\sync\sync0arr.c line 129

c:\mysql\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.6-gamma-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table x (id int) engine=innodb;
Query OK, 0 rows affected (0.59 sec)

mysql> insert into x values (1), (2);
Query OK, 2 rows affected (0.04 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> alter table x discard tablespace;
ERROR 2013 (HY000): Lost connection to MySQL server during query

>	mysqld.exe!pars_resolve_exp_variables_and_types(sel_node_struct * select_node=0x00000000, void * exp_node=0x01c5bc58)  Line 397 + 0x7c	C
 	mysqld.exe!pars_resolve_exp_variables_and_types(sel_node_struct * select_node=0x00000000, void * exp_node=0x01c5bd58)  Line 350 + 0xd	C
 	mysqld.exe!pars_assignment_statement(sym_node_struct * var=0x01c5bbd0, void * val=0x01c5bd58)  Line 1335 + 0xb	C
 	mysqld.exe!yyparse()  Line 387 + 0x18	C
 	mysqld.exe!pars_sql(const char * str=0x01c5a4a0)  Line 1755	C
 	mysqld.exe!row_discard_tablespace_for_mysql(const char * name=0x01c507a0, trx_struct * trx=0x01c594a0)  Line 2055 + 0x9	C
 	mysqld.exe!ha_innobase::discard_or_import_tablespace(char discard='')  Line 3798 + 0x10	C++
 	mysqld.exe!mysql_discard_or_import_tablespace(THD * thd=0x00e58ea8, st_table_list * table_list=0x00e6c6f8, tablespace_op_type tablespace_op=DISCARD_TABLESPACE)  Line 2298 + 0x18	C++
 	mysqld.exe!mysql_alter_table(THD * thd=0x00e58ea8, char * new_db=0x00e6c618, char * new_name=0x00000000, st_ha_create_information * create_info=0x00e59298, st_table_list * table_list=0x00e6c6f8, List<create_field> & fields={...}, List<Key> & keys={...}, unsigned int order_num=0, st_order * order=0x00000000, enum_duplicates handle_duplicates=DUP_ERROR, st_alter_info * alter_info=0x00e593d8, int do_send_ok=1)  Line 2574 + 0x36	C++
 	mysqld.exe!mysql_execute_command(THD * thd=0x00e58ea8)  Line 2510 + 0x64	C++
 	mysqld.exe!mysql_parse(THD * thd=0x00e58ea8, char * inBuf=0x00e6c680, unsigned int length=32)  Line 4046 + 0x9	C++
 	mysqld.exe!dispatch_command(enum_server_command command=COM_QUERY, THD * thd=0x00e58ea8, char * packet=0x00e65111, unsigned int packet_length=33)  Line 1457 + 0x1d	C++
 	mysqld.exe!do_command(THD * thd=0x00e58ea8)  Line 1272 + 0x31	C++
 	mysqld.exe!handle_one_connection(void * arg=0x00e58ea8)  Line 1016 + 0x9	C++
 	mysqld.exe!pthread_start(void * param=0x00e5a7f0)  Line 63 + 0x7	C
 	mysqld.exe!_threadstart(void * ptd=0x00e6c4b0)  Line 173 + 0xd	C
[4 Oct 2004 13:25] Heikki Tuuri

Someone has removed the quote chars (') from around the table name in the stored procedure below. Therefore, the InnoDB parser does not know that this is a string literal, and the parser is confused.

I also found that row_drop_table_for_mysql() leaks the memory allocated for quoted_name. I have assigned Marko to fix these bugs.

Thank you for the bug report,


                                /* out: error code or DB_SUCCESS */
        const char*     name,   /* in: table name */
        trx_t*          trx)    /* in: transaction handle */
        dulint          new_id;
        dict_table_t*   table;
        que_thr_t*      thr;
        que_t*          graph                   = NULL;
        ibool           success;
        ulint           err;
        char*           buf;

        static const char discard_tablespace_proc1[] =
        "old_id CHAR;\n"
        "new_id CHAR;\n"
        "new_id_low INT;\n"
        "new_id_high INT;\n"
        "table_name CHAR;\n"
        "table_name := ";
        static const char discard_tablespace_proc2[] =
[4 Oct 2004 18:51] Marko Mäkelä
ChangeSet@1.2051, 2004-10-04 20:38:23+03:00, marko@hundin.mysql.fi

I added the quotation marks and made sure that there are no similar errors elsewhere in the code. Thanks for the bug report!
[5 Oct 2004 12:37] Marko Mäkelä
The fix was pushed to the 4.1 source tree and should appear in 4.1.6
[5 Oct 2004 16:34] John David Duncan
Thanks for fixing this. 

The point of the exercise for the customer was to be able to fix any master/slave integrity 
questions by moving a .ibd file from one server to another.  Unfortunately I see now from the 
"TODO" comment in row_import_tablespace_for_mysql() that you will have to doctor the 
tablespace & trx numbers in the imported .ibd file before that will work.  

Should I file that feature request as a new bug report?
[5 Oct 2004 17:09] Heikki Tuuri

feel free to file a feature request for that.

And keep in mind that the .ibd to move must be 'clean' as explained in the manual.