MySQL Bugs: #3478: rename table ... to ... crashed.

Bug #3478	rename table ... to ... crashed.
Submitted:	15 Apr 2004 14:59	Modified:	21 Dec 2004 18:22
Reporter:	Dick Stins	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	4.0.18-standard-log	OS:	Linux (redhat linux)
Assigned to:	Heikki Tuuri	CPU Architecture:	Any

Description:
Mysql server crashed when trying to rename a table.
With the command line or phpmyadmin. Both reproduce the bug.

How to repeat:
install the create script.
try the rename script.

Suggested fix:
fix it with a good error message, because the real cause is probably a circular foreign key problem or ... of innodb. 

Probably is the datamodel not correct.

create table script

Attachment: erpzionitcom_backup.sql (application/octet-stream, text), 141.38 KiB.

It is not to be able to reproduce with mysql  4.0.16-nt.

Also noticed that the linux version handles case sensitive tables. The nt version is creation all tables in lower case.

This caused some problems and needed to be corrected with the rename script.

Heikki, the files to study the problem are attached to the bug report, in the "files" tab.
Testing it I got some errors during the CREATE stataments, and in RENAME I did not get a crash, but at least mysqld never returned from one of the RENAME TABLE, so it's true there is at least this problem. Apparently I have an endless loop in trx_t*
row_vers_impl_x_locked_off_kernel() in:
	for (;;) {
		mutex_exit(&kernel_mutex);
(it iterates the FOR forever); backtrace is:
(gdb) bt
#0  row_vers_impl_x_locked_off_kernel (
    rec=0x42300085 "test/ChartMastertest/0_1303", index=0x4119c068)
    at row0vers.c:137
#1  0x082b4fe2 in lock_sec_rec_some_has_impl_off_kernel (
    rec=0x42300085 "test/ChartMastertest/0_1303", index=0x4119c068)
    at lock0lock.c:1496
#2  0x082b8cfc in lock_rec_convert_impl_to_expl (
    rec=0x42300085 "test/ChartMastertest/0_1303", index=0x4119c068)
    at lock0lock.c:4467
#3  0x082b8f0f in lock_sec_rec_read_check_and_lock (flags=0, 
    rec=0x42300085 "test/ChartMastertest/0_1303", index=0x4119c068, mode=4, 
    gap_mode=1093699948, thr=0x4130896c) at lock0lock.c:4630
#4  0x08267118 in sel_set_rec_lock (
    rec=0x42300085 "test/ChartMastertest/0_1303", index=0x4130896c, mode=4, 
    type=22405, thr=0x0) at row0sel.c:718
#5  0x082676f4 in row_sel (node=0x4130b1e8, thr=0x4130e698) at row0sel.c:1214
#6  0x08267d66 in row_sel_step (thr=0x4130e698) at row0sel.c:1743
#7  0x082581e2 in que_thr_step (thr=0x4130e698) at que0que.c:1163
#8  0x0825839a in que_run_threads (thr=0x4130e698) at que0que.c:1244
#9  0x08263e16 in row_rename_table_for_mysql (
    old_name=0x433c6a3c "test/chartmaster", 
    new_name=0x433c664c "test/ChartMaster", trx=0x411a0468) at row0mysql.c:2473
#10 0x08194f78 in ha_innobase::rename_table(char const*, char const*) (
---Type <return> to continue, or q <return> to quit---
    this=0x8a4e780, from=0x433c6a3c "test/chartmaster", 
    to=0x433c6f1c "./test/ChartMaster") at ha_innodb.cc:3756
#11 0x0819c85b in mysql_rename_table(db_type, char const*, char const*, char const*, char const*) (base=1093699948, old_db=0x4130896c "test/0_1303", 
    old_name=0x433c6f1c "./test/ChartMaster", new_db=0x4130896c "test/0_1303", 
    new_name=0x8a4fb60 "ChartMaster") at sql_table.cc:928
#12 0x081a0f25 in rename_tables (thd=0x8a29820, table_list=0x8a4d4a0, 
    skip_error=false) at sql_rename.cc:146
#13 0x081a0b86 in mysql_rename_tables(THD*, st_table_list*) (thd=0x8a29820, 
    table_list=0x8a4d4a0) at sql_rename.cc:54
#14 0x08121bda in mysql_execute_command() () at sql_parse.cc:1825
#15 0x08124c54 in mysql_parse(THD*, char*, unsigned) (thd=0x8a29820, 
    inBuf=0x8a29950 "\001", length=40) at sql_parse.cc:2998
#16 0x0811fc3f in dispatch_command(enum_server_command, THD*, char*, unsigned)
    (command=COM_QUERY, thd=0x8a29820, 
    packet=0x8a3e799 "rename table  chartmaster to ChartMaster", 
    packet_length=40) at sql_parse.cc:1082
#17 0x0811f615 in do_command(THD*) (thd=0x8a29820) at sql_parse.cc:953

The create script is returning errors, because I removed not only the passwords but also the:
SET FOREIGN_KEY_CHECKS = 0; -- at the start of the file.

and 

SET FOREIGN_KEY_CHECKS = 1;

at the end of the file.

My provider was very annoyed, because the mysql server process crashed. 
This is ofcourse very critical for a production webserver.

The rename statement does not return.

Hi!

This bug report was forgotten during the MySQL Users Conference 2004. I am able to repeat this in the 4.1 tree, too.

The thread is apparently hung in an infinite loop. The bug is probably in InnoDB's internal stored procedure code that processes a search in the table when updating the FOREIGN KEY system table of InnoDB.

The fix will probably be in 4.0.23.

Regards,

Heikki

(gdb) bt
#0  0x40066f0d in __pthread_lock () from /lib/i686/libpthread.so.0
#1  0x40063fc3 in pthread_mutex_lock () from /lib/i686/libpthread.so.0
#2  0x08356975 in safe_mutex_lock (mp=0x8690f50,
    file=0x840475c "../include/os0sync.ic", line=141102912) at thr_mutex.c:116
#3  0x082f389d in os_fast_mutex_trylock (fast_mutex=0x0) at os0sync.ic:43
#4  0x082e6a32 in mutex_test_and_set (mutex=0x8690f24) at sync0sync.ic:106
#5  0x082e6bbe in mutex_enter_func (mutex=0x8690f24, file_name=0x0, line=0)
    at sync0sync.ic:253
#6  0x082f0f50 in mem_area_free (ptr=0x402d6028, pool=0x8690f18)
    at mem0pool.c:513
#7  0x082f012d in mem_heap_block_free (heap=0x402d6028, block=0x402d6028)
    at mem0mem.c:326
#8  0x082ef066 in mem_heap_free_func (heap=0x402d6028,
    file_name=0x83eb933 "row0vers.c", line=154) at mem0mem.ic:473
#9  0x0827e7ae in row_vers_impl_x_locked_off_kernel (
    rec=0x48380085 "test/ChartMastertest/0_1303\035\022", index=0x402c8768)
    at row0vers.c:154
#10 0x082d34d9 in lock_sec_rec_some_has_impl_off_kernel (
    rec=0x48380085 "test/ChartMastertest/0_1303\035\022", index=0x402c8768)
    at lock0lock.c:1541
#11 0x082d98d4 in lock_rec_convert_impl_to_expl (
    rec=0x48380085 "test/ChartMastertest/0_1303\035\022", index=0x402c8768)
    at lock0lock.c:4625
#12 0x082d9ae7 in lock_sec_rec_read_check_and_lock (flags=0,
    rec=0x48380085 "test/ChartMastertest/0_1303\035\022", index=0x402c8768,
    mode=4, gap_mode=0, thr=0x0) at lock0lock.c:4788
#13 0x08277ccf in sel_set_rec_lock (
    rec=0x48380085 "test/ChartMastertest/0_1303\035\022", index=0x0, mode=4,
    type=1, thr=0x1) at row0sel.c:738
#14 0x082782ed in row_sel (node=0x402d0210, thr=0x402d3728) at row0sel.c:1260
#15 0x0827894f in row_sel_step (thr=0x402d3728) at row0sel.c:1791
#16 0x08264b0e in que_thr_step (thr=0x402d3728) at que0que.c:1166
#17 0x08264cc6 in que_run_threads (thr=0x402d3728) at que0que.c:1247
#18 0x08274648 in row_rename_table_for_mysql (
    old_name=0x4e7e9a1c "test/chartmaster",
    new_name=0x4e7e962c "test/ChartMaster", trx=0x402c0068) at row0mysql.c:3008
#19 0x0820297a in ha_innobase::rename_table(char const*, char const*) (
    this=0x8b44698, from=0x4e7e9a1c "test/chartmaster",
    to=0x4e7e9efc "./test/ChartMaster") at ha_innodb.cc:4108
---Type <return> to continue, or q <return> to quit---
#20 0x0820a5d7 in mysql_rename_table(db_type, char const*, char const*, char con
st*, char const*) (base=DB_TYPE_UNKNOWN, old_db=0x0,
    old_name=0x4e7e9efc "./test/ChartMaster", new_db=0x0,
    new_name=0x8b44590 "ChartMaster") at sql_table.cc:1460
#21 0x0821041f in rename_tables (thd=0x8b46668, table_list=0x8b445c8,
    skip_error=false) at sql_rename.cc:173
#22 0x08210017 in mysql_rename_tables(THD*, st_table_list*) (thd=0x8b46668,
    table_list=0x8b445c8) at sql_rename.cc:58
#23 0x0817b142 in mysql_execute_command(THD*) (thd=0x8b46668)
    at sql_parse.cc:2553
#24 0x0817ee5d in mysql_parse(THD*, char*, unsigned) (thd=0x8b46668,
    inBuf=0x8b44510 "rename table  chartmaster to ChartMaster",
    length=146040480) at sql_parse.cc:4051
#25 0x081783a3 in dispatch_command(enum_server_command, THD*, char*, unsigned)
    (command=COM_QUERY, thd=0x8b46668,
    packet=0x8b404d9 "rename table  chartmaster to ChartMaster",
    packet_length=41) at sql_parse.cc:1463
#26 0x08177cb7 in do_command(THD*) (thd=0x8b46668) at sql_parse.cc:1278
#27 0x0817719f in handle_one_connection (arg=0x0) at sql_parse.cc:1022
#28 0x40062f60 in pthread_start_thread () from /lib/i686/libpthread.so.0
#29 0x400630fe in pthread_start_thread_event () from /lib/i686/libpthread.so.0
#30 0x401f5327 in clone () from /lib/i686/libc.so.6
(gdb)

Hi!

I have now fixed this bug. It happened because InnoDB's FOREIGN KEY system tables store table names, database names, and constraint names using the case-insensitive latin1_swedish_ci collation order.

After adding binary comparison checks to table names and database names, I was able to run your script successfully.

A small glitch still remains: if one creates two tables whose name only differs in case, and one does not explicitly give different names to the foreign key constraints in them, then the constraint names collide, because they are compared in a case-insensitive manner. I do not want to fix this glitch by making the comparison binary, because then the FOREIGN KEY system tables should be rebuilt in an upgrade 4.0.22 -> 4.0.23.

I also fixed a hang when a RENAME or ALTER failed because of colliding constraint names.

Regards,

Heikki

Thanks.

Please tell me the exact mysql release number when it is (partly) fixed and released.

With best regards,

Dick Stins

Dick,

it is (partly) fixed in upcoming 4.0.23. I guess 4.0.23 will be released around Dec 31st, 2004.

Thank you,

Heikki

I'm confused by this statement:
" I do not want to fix this glitch by
making the comparison binary, because then the FOREIGN KEY system tables should
be rebuilt in an upgrade 4.0.22 -> 4.0.23."

Does this mean that 4.0.23 will be *case sensitive* when comparing FOREIGN KEY names/ids?

We are currently having problems with replication between our 4.0.14 and 4.0.21 systems. The 4.0.21 systems are not comparing the FOREIGN KEY names/IDs in a case-sensitive manner. (And the 4.0.14 system isn't even using  the FOREIGN KEY ID's, but that isn't a problem yet..)

thanx.

Dick,

CONSTRAINT names are stored in InnoDB system tables in a case insensitive manner. That means that you cannot create constraints 'ABC' and 'abc' on the same table. But when referring to a constraint when you want to drop it, you have to use the correct case:

mysql> show create table child;
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------------------------
---------------------------------+
| Table | Create Table

                                 |
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------------------------
---------------------------------+
| child | CREATE TABLE `child` (
  `a` int(11) NOT NULL default '0',
  PRIMARY KEY  (`a`),
  CONSTRAINT `aBc` FOREIGN KEY (`a`) REFERENCES `parent` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------------------------
---------------------------------+
1 row in set (0.00 sec)

mysql> alter table child drop foreign key ABC;
ERROR 1025 (HY000): Error on rename of './test/child' to './test/#sql2-ff-1' (er
rno: 152)
mysql> alter table child drop foreign key aBc;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

Regards,

Heikki