Bug #15550 mysqld crashes in printing a FOREIGN KEY error in InnoDB
Submitted: 7 Dec 2005 12:23 Modified: 18 Jun 2010 2:02
Reporter: Kai Ruhnau Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S1 (Critical)
Version:5.0.3 and later OS:Linux (Gentoo Linux)
Assigned to: CPU Architecture:Any

[7 Dec 2005 12:23] Kai Ruhnau
Description:
When I execute the following query, the MySQL server dies:

mysql> insert into beta_parts_numbers SET ID_parts=0;
ERROR 2013 (HY000): Lost connection to MySQL server during query

How to repeat:
I was not able to produce a simple testcase. All I know is, that my beta and my production systems  both suffer from this. Please see attached file for CREATE TABLE, backtrace and logfile.
[7 Dec 2005 12:23] Kai Ruhnau
CREATE TABLE, stack trace, logfile

Attachment: mysql_bug.txt (text/plain), 5.69 KiB.

[7 Dec 2005 12:30] Aleksey Kishkin
Thank you for this bug report. To properly diagnose the problem, we
need a backtrace to see what is happening behind the scenes. To
find out how to generate a backtrace, please read
http://www.mysql.com/doc/en/Making_trace_files.html

Once you have generated a backtrace, please submit it to this bug
report and change the status back to 'Open'. Thank you for helping
us make our products better.
[7 Dec 2005 12:30] Aleksey Kishkin
Thank you for this bug report. To properly diagnose the problem, we
need a backtrace to see what is happening behind the scenes. To
find out how to generate a backtrace, please read
http://www.mysql.com/doc/en/Making_trace_files.html

Once you have generated a backtrace, please submit it to this bug
report and change the status back to 'Open'. Thank you for helping
us make our products better.
[7 Dec 2005 12:31] Aleksey Kishkin
Yes. already see the backtrace
[7 Dec 2005 12:33] Aleksey Kishkin
Kai, how did you get mysql? from emerge or downloaded binary distribution from mysqlc.om?
[7 Dec 2005 12:44] Kai Ruhnau
From ebuild (mysql-5.0.16-r3.ebuild)

The same error occured in 5.0.15. I upgraded this morning.
[7 Dec 2005 17:23] Kai Ruhnau
I have tested this issue against mysql-debug-5.0.16-linux-i686-glibc23.tar.gz from dev.mysql.com
This version works with a fresh datadir and an imported dump (I did not import the ~2million entry clicklog table):

mysql> insert into target_parts_numbers SET ID_parts=0;
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`torgs/target_parts_numbers`, CONSTRAINT `target_parts_numbers_ID_parts` FOREIGN KEY (`ID_parts`) REFERENCES `target_parts` (`ID`) ON DELETE CASCADE)

However, if start this server with my current datadir, mysql dies, too:

mysql> insert into target_parts_numbers set ID_parts=0;
ERROR 2013 (HY000): Lost connection to MySQL server during query

Here is the result of the stacktrace against the official debug binary:

0x8154f16 handle_segfault + 368
0xb7f876c7 _end + -1348474697
0x83213b9 rec_get_offsets_func + 125
0x8322e8d rec_print + 101
0x82907fa row_ins_foreign_report_add_err + 524
0x8291611 row_ins_check_foreign_constraint + 731
0x8291d8a row_ins_check_foreign_constraints + 340
0x8292d1b row_ins_index_entry + 36
0x8292e16 row_ins_index_entry_step + 45
0x8292fd9 row_ins + 446
0x829310c row_ins_step + 255
0x8294e01 row_insert_for_mysql + 411
0x820afe5 _ZN11ha_innobase9write_rowEPc + 879
0x81b6db2 _Z12write_recordP3THDP8st_tableP12st_copy_info + 1208
0x81b5d53 _Z12mysql_insertP3THDP13st_table_listR4ListI4ItemERS3_IS5_ES6_S6_15enum_duplicatesb + 1771
0x816bac1 _Z21mysql_execute_commandP3THD + 7929
0x81707a6 _Z11mysql_parseP3THDPcj + 312
0x8168984 _Z16dispatch_command19enum_server_commandP3THDPcj + 1554
0x8168367 _Z10do_commandP3THD + 437
0x816767c handle_one_connection + 758
0xb7f81ae4 _end + -1348498220
0xb7eb23da _end + -1349347894
[7 Dec 2005 19:42] Kai Ruhnau
It seems, that only some tables, that are referenced, are broken:

CREATE TABLE `beta_test` (
  `ID` int(10) unsigned NOT NULL auto_increment,
  `ID_parts` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`ID`),
  KEY `ID_parts` (`ID_parts`),
  CONSTRAINT `beta_test_ID_parts` FOREIGN KEY (`ID_parts`) REFERENCES `beta_parts` (`ID`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `beta_test2` (
  `ID` int(10) unsigned NOT NULL auto_increment,
  `ID_customer` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`ID`),
  KEY `ID_customer` (`ID_customer`),
  CONSTRAINT `beta_test2_ID_customer` FOREIGN KEY (`ID_customer`) REFERENCES `beta_customer` (`ID`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

mysql> insert into beta_test2 SET ID_customer=0;
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`torgs_beta/beta_test2`, CONSTRAINT `beta_test2_ID_customer` FOREIGN KEY (`ID_customer`) REFERENCES `beta_customer` (`ID`) ON DELETE CASCADE)

mysql> insert into beta_test SET ID_parts=0;
ERROR 2013 (HY000): Lost connection to MySQL server during query
[7 Dec 2005 22:57] Heikki Tuuri
Marko,

please study this.

Regards,

Heikki
[7 Dec 2005 23:10] Heikki Tuuri
Kai,

have you updated an empty string '' to NULL or vice versa?

Have you used ON UPDATE CASCADE?

With what version of MySQL you originally created your database?

Please post:

SHOW CREATE TABLE beta_parts;

Regards,

Heikki
[8 Dec 2005 9:40] Kai Ruhnau
> have you updated an empty string '' to NULL or vice versa?

On Monday (that seems to be the start point of the trouble) I used the following statement on beta_sub_orders:

ALTER TABLE beta_sub_orders CHANGE purchase_price purchase_price DECIMAL(10,4) NULL DEFAULT NULL;

Before that, purchase_price was DECIMAL(10,4) NOT NULL DEFAULT '0.0000'

mysql> INSERT INTO beta_sub_orders SET ID_orders=0;
ERROR 2013 (HY000): Lost connection to MySQL server during query

> Have you used ON UPDATE CASCADE?

I don't have any manual ON UPDATE setting.

> With what version of MySQL you originally created your database?

The first time it was created and further developed with 4.0er series, than switched to 4.1.~12 (there I ex- and imported a complete dump). Recently I switched to MySQL 5.0.15 (without ex- and importing a dump).

> Please post:
>
> SHOW CREATE TABLE beta_parts;

CREATE TABLE `beta_parts` (
  `ID` int(10) unsigned NOT NULL auto_increment,
  `room` int(10) unsigned NOT NULL default '0',
  `container` int(10) unsigned NOT NULL default '0',
  `box` int(10) unsigned NOT NULL default '0',
  `subbox` int(10) unsigned NOT NULL default '0',
  `quantity` int(11) NOT NULL default '0',
  `min_quantity` int(11) NOT NULL default '0',
  `force_sernum` enum('0','1') NOT NULL default '0',
  `unit` char(10) NOT NULL default '',
  `delivery_time` int(10) unsigned NOT NULL default '0',
  `inventory` date default NULL,
  `quantity_before_inventory` int(11) NOT NULL default '0',
  `purchase_price` decimal(10,4) default NULL,
  `process_price` decimal(10,4) default NULL,
  `sales_price` decimal(10,4) NOT NULL default '0.0000',
  `quantity_factor` int(10) unsigned NOT NULL default '1',
  `part_type` enum('article','services','consumables') NOT NULL default 'article',
  `visible` enum('yes','no') NOT NULL default 'yes',
  `last_change` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `ID_staff_change` int(10) unsigned default NULL,
  PRIMARY KEY  (`ID`),
  KEY `ID_staff_change` (`ID_staff_change`),
  CONSTRAINT `beta_parts_ID_staff_change` FOREIGN KEY (`ID_staff_change`) REFERENCES `beta_staff` (`ID`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Thank you for your support.

Greetings
Kai
[8 Dec 2005 10:23] Kai Ruhnau
beta_sub_orders has also an ID_parts:

mysql> insert into beta_sub_orders SET ID_parts=0;
ERROR 2013 (HY000): Lost connection to MySQL server during query

Greetings
Kai
[8 Dec 2005 11:20] Marko Mäkelä
Kai,
can you run mysqld inside gdb and post the output of "bt full" when the crash occurs?

I can't repeat the crash. The INSERT to beta_test just fails:

insert into beta_test SET ID_parts=0;
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test/beta_test`, CONSTRAINT `beta_test_ID_parts` FOREIGN KEY (`ID_parts`) REFERENCES `beta_parts` (`ID`) ON DELETE CASCADE)

I created the tables beta_parts, beta_test, and beta_test2, after removing foreign key references to other tables.

Marko
[8 Dec 2005 12:38] Kai Ruhnau
gdb backtrace

Attachment: gdb back trace.txt (text/plain), 10.70 KiB.

[8 Dec 2005 12:40] Kai Ruhnau
I hope I did it right.

Regarding reproducability:

As I mentioned: importing a dump in a fresh database does not trigger this bug.

Thanks and greetings
Kai
[8 Dec 2005 12:56] Marko Mäkelä
Thanks for the backtrace. The assertion fails because InnoDB writes an unused bit for ROW_FORMAT=COMPACT records (comp=TRUE) to be zero and expects it to be zero. Apparently the record is corrupted or the "compact" flag (0=old format, 1=compact) is wrong in the data dictionary.

Could you re-run it, executing the gdb command
p/x rec[-10]@200
when it crashes?

Was the table created with MySQL/InnoDB 5.0.3 or later, i.e., which format is it in: the new default ROW_FORMAT=COMPACT or the old ROW_FORMAT=REDUNDANT? What does SHOW TABLE STATUS report as the row format? Does CHECK TABLE succeed on all the tables involved?

Marko
[8 Dec 2005 13:17] Heikki Tuuri
Kai,

please print:

SELECT * FROM beta_parts ORDER BY id LIMIT 1;

Regards,

Heikki
[8 Dec 2005 13:21] Heikki Tuuri
I meant do the SELECT in the crashing database instance.

The crash happens when InnoDB tries to print that row to the foreign key error message.
[8 Dec 2005 17:39] Kai Ruhnau
Thank you so far!

> Could you re-run it, executing the gdb command

(gdb) p/x rec[-10]@200
$1 = {0x15, 0x11, 0xa, 0x4, 0x0, 0x0, 0x10, 0x2d, 0x1, 0x22, 0x0, 0x0, 0x0, 0x7, 0x0, 0x0, 0x0, 0x1e, 0xd6, 0xd9, 0x0, 0x0, 0x0, 0x52, 0xcf, 0x13, 0x16,
  0x0, 0x0, 0x3, 0x26, 0x0, 0x0, 0x0, 0x6, 0x0, 0x0, 0x0, 0x2, 0x0, 0x0, 0x0, 0x0, 0x80, 0x0, 0x0, 0x1, 0x80, 0x0, 0x0, 0x0, 0x2,
  0x20 <repeats 30 times>, 0x0, 0x0, 0x0, 0x0, 0x8f, 0xab, 0x5f, 0x80, 0x0, 0x0, 0x0, 0x20, 0x20, 0x20, 0x20, 0x20, 0x20, 0x30, 0x2e, 0x30, 0x30, 0x30,
  0x30, 0x0, 0x0, 0x0, 0x1, 0x1, 0x1, 0x43, 0x65, 0x58, 0xc7, 0x0, 0x0, 0x0, 0x1, 0x6d, 0x69, 0x65, 0x64, 0x63, 0x5f, 0xd3, 0xd3, 0x53, 0x4f, 0x4c, 0x48,
  0x2a, 0x29, 0x25, 0x21, 0x1d, 0x19, 0x15, 0x11, 0xa, 0x4, 0x0, 0x0, 0x18, 0x2d, 0x1, 0xab, 0x0, 0x0, 0x0, 0x8, 0x0, 0x0, 0x0, 0x1d, 0xc7, 0xe1, 0x80,
  0x0, 0x0, 0xc, 0x82, 0x0, 0x91, 0x0, 0x0, 0x2, 0xbf, 0x0 <repeats 12 times>, 0x80, 0x0, 0x0, 0x2, 0x80, 0x0, 0x0, 0x0, 0x2, 0x20 <repeats 11 times>}

> Was the table created with MySQL/InnoDB 5.0.3 or later

beta_parts was recreated after updating to MySQL 4.1. It was definitly not created with MySQL 5.0.
beta_parts_numbers was created with MySQL 5.0.15.

| beta_parts                                  | InnoDB |       9 | Redundant  |    3507 |            453 |     1589248 |               0 |        98304 |         0 |           3466 | 2005-10-08 13:01:43 | NULL        | NULL       | utf8_general_ci |     NULL |                | InnoDB free: 23552 kB; (`ID_staff_change`) REFER  |
| beta_parts_numbers                          | InnoDB |      10 | Compact    |    3610 |             54 |      196608 |               0 |       147456 |         0 |           3344 | 2005-12-07 20:26:15 | NULL        | NULL       | utf8_general_ci |     NULL |                | InnoDB free: 23552 kB; (`ID_parts`) REFER `torgs_beta/beta_parts`(`ID`) ON DELET |

> Does CHECK TABLE succeed on all the tables involved?

mysql> CHECK TABLE beta_parts;
+-----------------------+-------+----------+----------+
| Table                 | Op    | Msg_type | Msg_text |
+-----------------------+-------+----------+----------+
| torgs_beta.beta_parts | check | status   | OK       |
+-----------------------+-------+----------+----------+
1 row in set (0.15 sec)

mysql> CHECK TABLE beta_parts_numbers;
+-------------------------------+-------+----------+----------+
| Table                         | Op    | Msg_type | Msg_text |
+-------------------------------+-------+----------+----------+
| torgs_beta.beta_parts_numbers | check | status   | OK       |
+-------------------------------+-------+----------+----------+
1 row in set (0.06 sec)

> please print:

(This type of query is often used in my application)

mysql> SELECT * FROM beta_parts ORDER BY id LIMIT 1;
+----+------+-----------+-----+--------+----------+--------------+--------------+------+---------------+------------+---------------------------+----------------+---------------+-------------+-----------------+-----------+---------+---------------------+-----------------+
| ID | room | container | box | subbox | quantity | min_quantity | force_sernum | unit | delivery_time | inventory  | quantity_before_inventory | purchase_price | process_price | sales_price | quantity_factor | part_type | visible | last_change         | ID_staff_change |
+----+------+-----------+-----+--------+----------+--------------+--------------+------+---------------+------------+---------------------------+----------------+---------------+-------------+-----------------+-----------+---------+---------------------+-----------------+
|  7 |  806 |         6 |   2 |      0 |        1 |            0 | 1            |      |             0 | 2005-10-31 |                         0 |           NULL |          NULL |      0.0000 |               1 | article   | yes     | 2005-10-31 00:35:35 |               1 |
+----+------+-----------+-----+--------+----------+--------------+--------------+------+---------------+------------+---------------------------+----------------+---------------+-------------+-----------------+-----------+---------+---------------------+-----------------+
1 row in set (0.02 sec)

Greetings
Kai
[8 Dec 2005 21:28] Marko Mäkelä
Most probably we've mixed the record and the index in the error message output. This bug has probably gone unnoticed so far, because typically both the parent and the child table are in the same ROW_FORMAT. I will have a closer look at the code tomorrow.

Background: In ROW_FORMAT=COMPACT, InnoDB cannot interpret records without having the data dictionary information. In ROW_FORMAT=REDUNDANT (the only InnoDB format before version 5.0.3), offsets to the columns are stored on the disk for every record.
[8 Dec 2005 21:58] Kai Ruhnau
Is there a quick way to bring all tables (at least in the production system) to the new "Compact" format. This should, if I understand you right, work around this bug?
I'd prefer if I did not have to reimport a dump (time consuming), although this is far better than losing the MySQL servers.
[8 Dec 2005 22:20] Marko Mäkelä
ALTER TABLE <name> ROW_FORMAT=COMPACT;

should do the trick. I'm afraid it will not be much faster than a dump and reimport, but I think it's more comfortable to use.

I hope that my upcoming fix will be included in 5.0.18 (which is probably some weeks away).

Marko
[8 Dec 2005 23:19] Heikki Tuuri
Marko, Kai,

yes:

row0ins.c:
...
        ut_print_name(ef, trx, foreign->referenced_table_name);
        fputs(", in index ", ef);
        ut_print_name(ef, trx, foreign->referenced_index->name);
        fputs(",\nthe closest match we can find is record:\n", ef);
        if (rec && page_rec_is_supremum(rec)) {
                /* If the cursor ended on a supremum record, it is better
                to report the previous record in the error message, so that
                the user gets a more descriptive error message. */
                rec = page_rec_get_prev(rec);
        }

        if (rec) {
                rec_print(ef, rec, foreign->foreign_index);
        }
...

rec is really in foreign->referenced_index!

Fix: on the last line, replace foreign->foreign_index with foreign->referenced_index.

The bug has gone unnoticed because usually foreign->referenced_index and foreign->foreign_index have identical structure of columns. This can cause crashes if they are not identical.

Marko, please check that foreign_index and referenced_index are not mixed in some other place in row0ins.c. It is easy to mix them. I should have named them child_index and parent_index to be more intuitive.

Thank you,

Heikki
[9 Dec 2005 7:52] Marko Mäkelä
Preliminary patch to fix Bug #15550 in MySQL 5.0

Attachment: bug15550.patch (text/x-patch), 3.03 KiB.

[9 Dec 2005 7:57] Marko Mäkelä
Heikki, Kai,

it seems to me that row_ins_foreign_report_add_err() can be called on either the parent rec or the child rec, depending on the value of the check_ref flag passed to row_ins_check_foreign_constraint().

I attached a preliminary patch to this bug, and I will continue testing to see if other occurrences of rec_print() are correct. Kai, please let us know if the patch works for you.

Marko
[9 Dec 2005 11:27] Heikki Tuuri
Marko,

from the 'if' conditions you see that it only calls row_ins_foreign_report_add_err() if check_ref == TRUE.

Thus,  foreign->referenced_index is the correct index.

Regards,

Heikki
[9 Dec 2005 12:17] Marko Mäkelä
Heikki,
you're right.

I also checked all invocations of rec_print(). The ones in row_ins_foreign_report_err() raised some concerns, but then I noticed that the function is only called if check_ref==FALSE in row_ins_check_foreign_constraint().

I will shortly post a simpler patch, which includes more test cases.
[9 Dec 2005 12:23] Marko Mäkelä
One-line patch to fix Bug #15550, and more test cases

Attachment: bug15550-2.patch (text/x-patch), 3.72 KiB.

[9 Dec 2005 19:21] Kai Ruhnau
I just patched a MySQL-5.0.16 source.

All tests succeed.

On my beta system (with mixed ROW_FORMAT):

mysql> insert into beta_parts_numbers SET ID_parts=0;
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`torgs_beta/beta_parts_numbers`, CONSTRAINT `beta_parts_numbers_ID_parts` FOREIGN KEY (`ID_parts`) REFERENCES `beta_parts` (`ID`) ON DELETE CASCADE)

My production system uses ROW_FORMAT=Compact consistenly and runs stable now.

Thanks a lot for your support.
[9 Dec 2005 22:41] Marko Mäkelä
Kai,
thank you for reporting this bug and helping us to trace it down. I wish all bug reporters knew how to use gdb. :-)

I have just sent a snapshot of the InnoDB 5.0 source tree to Elliot Murphy, who will hopefully soon apply it to the MySQL/InnoDB source code repository.
[15 Dec 2005 11:30] Alexander Ivanov
Fixed in 5.0.18.
[21 Dec 2005 9:27] Jon Stephens
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Documented in 5.0.18 changelog. Closed.
[5 May 2010 15:15] Bugs System
Pushed into 5.1.47 (revid:joro@sun.com-20100505145753-ivlt4hclbrjy8eye) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[6 May 2010 1:44] Paul DuBois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug. Re-closing.
[28 May 2010 5:56] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)
[28 May 2010 6:24] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100524190941-nuudpx60if25wsvx) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[28 May 2010 6:52] Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[29 May 2010 2:43] Paul DuBois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.
[17 Jun 2010 11:57] Bugs System
Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 12:36] Bugs System
Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 13:23] Bugs System
Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)