MySQL Bugs: #25927: Foreign key with ON DELETE SET NULL on NOT NULL can crash server

Bug #25927	Foreign key with ON DELETE SET NULL on NOT NULL can crash server
Submitted:	29 Jan 2007 20:44	Modified:	20 Jun 2010 1:14
Reporter:	Tobias Asplund	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.0.33/5.0BK	OS:	MacOS (Mac OS X/Linux)
Assigned to:	Marko Mäkelä	CPU Architecture:	Any

Description:
Setting a column to NOT NULL with a ON DELETE SET NULL clause foreign key crashes the server.

(Thanks to Pavel Pushkarev for finding this)

How to repeat:
-- Create the tables and populate them with data
CREATE TABLE a (a INT, INDEX(a)) ENGINE=InnoDB;
CREATE TABLE b (a INT, INDEX(a)) ENGINE=InnoDB;
INSERT INTO a VALUES (1);
INSERT INTO b VALUES (1);

-- Create the foreign key
ALTER TABLE b ADD FOREIGN KEY (a) REFERENCES a (a) ON DELETE SET NULL;

-- This one should fail, but it doesn't
ALTER TABLE b MODIFY a INT NOT NULL;

-- And now this one makes the server crash
DELETE FROM a;

Suggested fix:
5.1.14 works fine with the message:
mysql> DELETE FROM a;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`b`, CONSTRAINT `b_ibfk_1` FOREIGN KEY (`a`) REFERENCES `a` (`a`))

Somehow I think this error should occur with the ALTER TABLE, rather than the DELETE.

Back trace on Ubuntu 6.10 32-bit

Attachment: bt-25927.txt (text/plain), 12.67 KiB.

Thank you for the bug report. Verified as describes only with 5.0BK (back
trace attached).

This bug becomes on Win32 platform as well.

InnoDB may get confused when it tries to set a column NULL though it is declared as NOT NULL.

Assigning this to Marko. Maybe we can detect this already in the ALTER TABLE?

(gdb) run
Starting program: /home/heikki/mysql-5.0/sql/mysqld
[Thread debugging using libthread_db enabled]
[New Thread 1075718752 (LWP 13192)]
070213 19:17:51 [Warning] Changed limits: max_open_files: 1024  max_connections: 886  table_cache: 64
[New Thread 1196497840 (LWP 13195)]
[New Thread 1204886448 (LWP 13196)]
[New Thread 1213275056 (LWP 13197)]
[New Thread 1221663664 (LWP 13198)]
070213 19:17:51  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
070213 19:17:51  InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 14920664.
InnoDB: Doing recovery: scanned up to log sequence number 0 14920664
InnoDB: Last MySQL binlog file position 0 10490428, file name ./1.000001
[New Thread 1230052272 (LWP 13199)]
[New Thread 1242168240 (LWP 13200)]
[New Thread 1250556848 (LWP 13201)]
[New Thread 1258945456 (LWP 13202)]
070213 19:17:51  InnoDB: Started; log sequence number 0 14920664
[Thread 1230052272 (zombie) exited]
070213 19:17:52 [Note] Recovering after a crash using 1
070213 19:17:52 [Note] Starting crash recovery...
070213 19:17:52 [Note] Crash recovery finished.
[New Thread 1282788272 (LWP 13203)]
[New Thread 1282984880 (LWP 13204)]
070213 19:17:52 [Note] /home/heikki/mysql-5.0/sql/mysqld: ready for connections.
Version: '5.0.32-debug-log'  socket: '/home/heikki/bugsocket'  port: 3307  Source distribution
[New Thread 1283181488 (LWP 13218)]

Program received signal SIGSEGV, Segmentation fault.
[Switching to Thread 1283181488 (LWP 13218)]
0x4012707f in memcpy () from /lib/tls/libc.so.6
(gdb) bt
#0  0x4012707f in memcpy () from /lib/tls/libc.so.6
#1  0x084f53c2 in rec_convert_dtuple_to_rec_new (buf=0x404d8078 "",
    index=0x40258868, dtuple=0x4025b8a8) at rem0rec.c:974
#2  0x084f540a in rec_convert_dtuple_to_rec (buf=0x404d8078 "",
    index=0x40258868, dtuple=0x4025b8a8) at rem0rec.c:1002
#3  0x084f24b0 in page_cur_insert_rec_low (cursor=0x4025956c,
    tuple=0x4025b8a8, index=0x40258868, rec=0x0, offsets=0x0, mtr=0x4c7ba870)
    at page0cur.c:920
#4  0x084f1210 in page_cur_tuple_insert (cursor=0x4025956c, tuple=0x4025b8a8,
    index=0x40258868, mtr=0x4c7ba870) at page0cur.ic:188
#5  0x084ab208 in btr_cur_insert_if_possible (cursor=0x40259568,
    tuple=0x4025b8a8, reorg=0x4c7ba460, mtr=0x4c7ba870) at btr0cur.c:812
#6  0x084acccc in btr_cur_pessimistic_update (flags=2, cursor=0x40259568,
    big_rec=0x4c7ba67c, update=0x40259f00, cmpl_info=0, thr=0x40254480,
    mtr=0x4c7ba870) at btr0cur.c:1914
#7  0x0849a65f in row_upd_clust_rec (node=0x40259e68, index=0x40258868,
    thr=0x40254480, mtr=0x4c7ba870) at row0upd.c:1547
#8  0x0849accd in row_upd_clust_step (node=0x40259e68, thr=0x40254480)
    at row0upd.c:1787
#9  0x0849ad8a in row_upd (node=0x40259e68, thr=0x40254480) at row0upd.c:1836
#10 0x0849af7b in row_upd_step (thr=0x40254480) at row0upd.c:1965
#11 0x0848ca36 in row_update_cascade_for_mysql (thr=0x40254480,
    node=0x40259e68, table=0x40259c68) at row0mysql.c:1558
#12 0x08488a26 in row_ins_foreign_check_on_constraint (thr=0x40254480,
    foreign=0x40259468, pcur=0x4c7bb420, entry=0x40253c78, mtr=0x4c7bafb0)
    at row0ins.c:1070
#13 0x0848927f in row_ins_check_foreign_constraint (check_ref=0,
    foreign=0x40259468, table=0x4024fe68, entry=0x40253c78, thr=0x40254480)
    at row0ins.c:1398
#14 0x084987e5 in row_upd_check_references_constraints (node=0x40254268,
    pcur=0x4c7bb9a0, table=0x4024fe68, index=0x40255868, thr=0x40254480,
    mtr=0x4c7bb530) at row0upd.c:222
#15 0x0849a081 in row_upd_sec_index_entry (node=0x40254268, thr=0x40254480)
    at row0upd.c:1311
#16 0x0849a1b1 in row_upd_sec_step (node=0x40254268, thr=0x40254480)
    at row0upd.c:1366
#17 0x0849adcb in row_upd (node=0x40254268, thr=0x40254480) at row0upd.c:1850
#18 0x0849af7b in row_upd_step (thr=0x40254480) at row0upd.c:1965
#19 0x0848c6c1 in row_update_for_mysql (mysql_rec=0x908a9d0 "ý\001",
    prebuilt=0x40252a68) at row0mysql.c:1386
#20 0x083120f1 in ha_innobase::delete_row (this=0x908a8b0,
    record=0x908a9d0 "ý\001") at ha_innodb.cc:3570
#21 0x082a84bd in mysql_delete (thd=0x90a1ee0, table_list=0x90c23a0,
    conds=0x0, order=0x90a22ac, limit=18446744073709551615, options=0,
    reset_auto_increment=false) at sql_delete.cc:229
#22 0x08231e02 in mysql_execute_command (thd=0x90a1ee0) at sql_parse.cc:3512
#23 0x082383e4 in mysql_parse (thd=0x90a1ee0, inBuf=0x90c2328 "DELETE FROM a",
    length=13) at sql_parse.cc:5825
#24 0x0822d74d in dispatch_command (command=COM_QUERY, thd=0x90a1ee0,
    packet=0x90ba2c9 "DELETE FROM a", packet_length=14) at sql_parse.cc:1773
#25 0x0822ce9d in do_command (thd=0x90a1ee0) at sql_parse.cc:1557
#26 0x0822bf87 in handle_one_connection (arg=0x90a1ee0) at sql_parse.cc:1188
#27 0x4003fb63 in start_thread () from /lib/tls/libpthread.so.0
#28 0x4018518a in clone () from /lib/tls/libc.so.6
(gdb)

ON ... SET NULL is prevented when creating a foreign key constraint on a NOT NULL column, but not when making a contrained column NOT NULL. This can be catched in dict_load_foreign() or its callees. I am working on a fix.

Fix: add a parameter to dict_foreign_find_index() for rejecting columns that are NOT NULL, and in dict_foreign_add_to_cache() set the parameter on the referencing table if the constraint specifies ON DELETE SET NULL or ON UPDATE SET NULL.

Pushed into 5.0.40

Pushed into 5.1.18-beta

Noted in 5.0.40, 5.1.18 changelogs.

Setting a column to NOT NULL with an ON DELETE SET NULL clause 
foreign key crashes the server.

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)