| Bug #5180 | Server crashes when using primary keys prefix indexes and foreign keys | ||
|---|---|---|---|
| Submitted: | 24 Aug 2004 12:05 | Modified: | 30 Oct 2004 8:56 |
| Reporter: | Mauro Molinari | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S2 (Serious) |
| Version: | 4.0.20d (as stated in mysql.com) | OS: | Windows (Windows XP Professional) |
| Assigned to: | Heikki Tuuri | CPU Architecture: | Any |
[24 Aug 2004 16:22]
MySQL Verification Team
Tested with latest 4.0.21. Call stack
> mysqld.exe!row_build_row_ref(unsigned long type=0, dict_index_struct * index=0x01abd4a0, unsigned char * rec=0x02348084, mem_block_info_struct * heap=0x01abf2a0) Line 364 + 0x7a C
mysqld.exe!row_ins_foreign_check_on_constraint(que_thr_struct * thr=0x01abb368, dict_foreign_struct * foreign=0x01abc6a0, btr_pcur_struct * pcur=0x03a6ec04, dtuple_struct * entry=0x01abe8c0, mtr_struct * mtr=0x03a6ec68) Line 835 C
mysqld.exe!row_ins_check_foreign_constraint(unsigned long check_ref=0, dict_foreign_struct * foreign=0x01abc6a0, dict_table_struct * table=0x01abaaa0, dtuple_struct * entry=0x01abe8c0, que_thr_struct * thr=0x01abb368) Line 1241 + 0x20 C
mysqld.exe!row_upd_check_references_constraints(upd_node_struct * node=0x01abb188, btr_pcur_struct * pcur=0x01abe8c0, dict_table_struct * table=0x01abaaa0, dict_index_struct * index=0x01abbba0, que_thr_struct * thr=0x01abb368, mtr_struct * mtr=0x01a945a0) Line 222 + 0x16 C
mysqld.exe!row_upd_clust_rec_by_insert(upd_node_struct * node=0x0000000a, dict_index_struct * index=0x01abbba0, que_thr_struct * thr=0x01abb368, unsigned long check_ref=1, mtr_struct * mtr=0x03a6f170) Line 1369 + 0x13 C
mysqld.exe!row_upd_clust_step(upd_node_struct * node=0x01abb188, que_thr_struct * thr=0x01abb368) Line 1686 + 0x1b C
mysqld.exe!row_upd(upd_node_struct * node=0x01abb188, que_thr_struct * thr=0x01abb368) Line 1743 + 0x7 C
mysqld.exe!row_upd_step(que_thr_struct * thr=0x01abb368) Line 1872 + 0x7 C
mysqld.exe!row_update_for_mysql(unsigned char * mysql_rec=0x02559608, row_prebuilt_struct * prebuilt=0x01aba2a0) Line 1111 C
mysqld.exe!ha_innobase::update_row(const unsigned char * old_row=0x02559608, unsigned char * new_row=0x025595a0) Line 2525 + 0xd C++
mysqld.exe!mysql_update(THD * thd=0x02557040, st_table_list * table_list=0x0255e320, List<Item> & fields={...}, List<Item> & values={...}, Item * conds=0x0255e488, st_order * order=0x00000000, unsigned long limit=4294967295, enum_duplicates handle_duplicates=DUP_ERROR) Line 293 + 0x1f C++
mysqld.exe!mysql_execute_command() Line 1929 + 0x3d C++
mysqld.exe!mysql_parse(THD * thd=0x02557040, char * inBuf=0x0255e298, unsigned int length=69) Line 3002 C++
mysqld.exe!dispatch_command(enum_server_command command=COM_QUERY, THD * thd=0x02557040, char * packet=0x0254f111, unsigned int packet_length=69) Line 1083 + 0x17 C++
mysqld.exe!do_command(THD * thd=0x02557040) Line 953 + 0x31 C++
mysqld.exe!handle_one_connection(void * arg=0x02557040) Line 737 + 0x9 C++
mysqld.exe!pthread_start(void * param=0x00cdcca8) Line 63 + 0x7 C
mysqld.exe!_threadstart(void * ptd=0x00cdcef0) Line 173 + 0xd C
kernel32.dll!77e6d33b()
[24 Aug 2004 16:36]
MySQL Verification Team
/row0row.c
--359--
for (i = 0; i < ref_len; i++) {
dfield = dtuple_get_nth_field(ref, i);
pos = dict_index_get_nth_field_pos(index, clust_index, i);
ut_a(pos != ULINT_UNDEFINED);
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
field = rec_get_nth_field(rec, pos, &len);
dfield_set_data(dfield, field, len);
}
[16 Sep 2004 18:15]
Heikki Tuuri
Hi! The following patch fixes this. ------------------------------------------------- dict0dict.h, dict0dict.c, row0row.c, pars0opt.c: Fix bug #5180: having a column prefix index in the primary key, and the same c olumn fully in a secondary key could cause an assertion failure in row_build_row _ref() ------------------------------------------------- Now it works: heikki@hundin:~/mysql-4.0/client> ./mysql test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 24 to server version: 4.0.22-debug-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> CREATE TABLE CrashTable1 -> ( -> Name VARCHAR(100) PRIMARY KEY -> ) ENGINE = InnoDB; Query OK, 0 rows affected (0.02 sec) mysql> mysql> CREATE TABLE CrashTable2 -> ( -> CrashTable1 VARCHAR(100), -> PRIMARY KEY (CrashTable1(20)), -> INDEX (CrashTable1), -> FOREIGN KEY (CrashTable1) REFERENCES CrashTable1 (Name) ON UPDATE CASCA DE -> ) ENGINE = InnoDB; Query OK, 0 rows affected (0.02 sec) mysql> mysql> INSERT INTO CrashTable1 -> VALUES ('Hi there!'); Query OK, 1 row affected (0.01 sec) mysql> mysql> INSERT INTO CrashTable2 -> SET CrashTable1 = 'Hi there!'; Query OK, 1 row affected (0.01 sec) mysql> mysql> -- next statement makes the server crash! mysql> UPDATE CrashTable1 -> SET Name = 'Hello there!' -> WHERE Name = 'Hi there!'; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> check table CrashTable1; +------------------+-------+----------+----------+ | Table | Op | Msg_type | Msg_text | +------------------+-------+----------+----------+ | test.CrashTable1 | check | status | OK | +------------------+-------+----------+----------+ 1 row in set (0.01 sec) mysql> check table CrashTable2; +------------------+-------+----------+----------+ | Table | Op | Msg_type | Msg_text | +------------------+-------+----------+----------+ | test.CrashTable2 | check | status | OK | +------------------+-------+----------+----------+ 1 row in set (0.17 sec) mysql> Thank you, Heikki
[30 Oct 2004 8:56]
Heikki Tuuri
Fixed in 4.0.22.
[30 Oct 2004 10:45]
Mauro Molinari
Glad to see I've been of help! :-) Mauro Molinari.
Description: Hello, I am an Italian student who was developing a database project for University and I found something I think is a serious bug. Trying to optimize my database as much as possible, I deeply read MySQL documentation and find an interesting feature: prefix indexes. So I decided to use them. If you create a table with a varchar attribute, then a second table with a varchar attribute that is (part of) a primary key and also references the varchar attribute of the first table (with ON UPDATE CASCADE), you can define a structure that is accepted by MySQL (and correctly created), but that will cause the server to crash if you then update the first table changing the value of an instance of a primary key that is referenced by the value of an instance of the second table. It's hard to well explain in words (expecially because I'm not English), but the examples I attach in "How to repeat" should crearly show what I mean. You'll see the server crash! A brief note about the server version: when I go to www.mysql.com, Downloads section, the webpage says that the latest production release for Windows is 4.0.20d, and this is the version I downloaded (and still using). But, after installing it and running mysql command in a DOS prompt, it tells me that the server version is 4.0.20a, not 4.0.20d... I don't know if this is a mistake or something else. In doubt, I also tried to download again and reinstall the supposed 4.0.20d version from www.mysql.com, but it actually seems to be 4.0.20a version when running mysql command. Mauro Molinari How to repeat: -- ##### FIRST EXAMPLE ##### CREATE DATABASE CrashTest1; USE CrashTest1; CREATE TABLE CrashTable1 ( Name VARCHAR(100) PRIMARY KEY ) ENGINE = InnoDB; CREATE TABLE CrashTable2 ( CrashTable1 VARCHAR(100), PRIMARY KEY (CrashTable1(20)), INDEX (CrashTable1), FOREIGN KEY (CrashTable1) REFERENCES CrashTable1 (Name) ON UPDATE CASCADE ) ENGINE = InnoDB; INSERT INTO CrashTable1 VALUES ('Hi there!'); INSERT INTO CrashTable2 SET CrashTable1 = 'Hi there!'; -- next statement makes the server crash! UPDATE CrashTable1 SET Name = 'Hello there!' WHERE Name = 'Hi there!'; -- ##### SECOND EXAMPLE ##### CREATE DATABASE CrashTest2; USE CrashTest2; CREATE TABLE CrashTable1 ( Name VARCHAR(100) PRIMARY KEY ) ENGINE = InnoDB; CREATE TABLE CrashTable2 ( ID MEDIUMINT UNSIGNED AUTO_INCREMENT, CrashTable1 VARCHAR(100), PRIMARY KEY (ID, CrashTable1(20)), INDEX (CrashTable1), FOREIGN KEY (CrashTable1) REFERENCES CrashTable1 (Name) ON UPDATE CASCADE ) ENGINE = InnoDB; INSERT INTO CrashTable1 VALUES ('Hi there!'); INSERT INTO CrashTable2 SET CrashTable1 = 'Hi there!'; -- next statement makes the server crash! UPDATE CrashTable1 SET Name = 'Hello there!' WHERE Name = 'Hi there!';