MySQL Bugs: #15550: mysqld crashes in printing a FOREIGN KEY error in InnoDB

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

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.

CREATE TABLE, stack trace, logfile

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

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.

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.

Yes. already see the backtrace

Kai, how did you get mysql? from emerge or downloaded binary distribution from mysqlc.om?

From ebuild (mysql-5.0.16-r3.ebuild)

The same error occured in 5.0.15. I upgraded this morning.

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

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

Marko,

please study this.

Regards,

Heikki

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

> 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

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

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

gdb backtrace

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

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

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

Kai,

please print:

SELECT * FROM beta_parts ORDER BY id LIMIT 1;

Regards,

Heikki

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.

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

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.

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.

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

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

Preliminary patch to fix Bug #15550 in MySQL 5.0

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

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

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

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.

One-line patch to fix Bug #15550, and more test cases

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

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.

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.

Fixed in 5.0.18.

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.

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)

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug. Re-closing.

Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)

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)

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)

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.

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)

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)

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)