MySQL Bugs: #35527: Foreign keys: crash if self-reference in create ... as select

Bug #35527	Foreign keys: crash if self-reference in create ... as select
Submitted:	24 Mar 2008 17:42	Modified:	27 Jun 2008 5:13
Reporter:	Peter Gulutzan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	6.1.0-fk-debug	OS:	Linux (SUSE 10 \| 32-bit)
Assigned to:	Dmitry Lenev	CPU Architecture:	Any

Description:
I'm using mysql-6.1-fk.
I start mysqld with --foreign-key-all-engines=1.

I create a table which contains a foreign-key reference
to 'k5', and also contains "select ... from k5".

Crash.

How to repeat:
mysql> create table k5 (s1 int primary key);
Query OK, 0 rows affected (0.10 sec)

mysql> create table k6 (s1 int references k5(s1)) as select s1 from k5;
ERROR 2013 (HY000): Lost connection to MySQL server during query

Thank you for the bug report.

[New Thread 1273313616 (LWP 26691)]
080324 18:48:05 [Note] Event Scheduler: Loaded 0 events
080324 18:48:05 [Note] /home/miguel/dbs/6.1fk/libexec/mysqld: ready for connections.
Version: '6.1.0-fk-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
[New Thread 1273579856 (LWP 26693)]
[Thread 1273579856 (LWP 26693) exited]
[New Thread 1273579856 (LWP 26695)]
mysqld: lock.cc:387: int lock_external(THD*, TABLE**, uint): Assertion `(*tables)->reginfo.lock_type >= TL_READ' failed.

Program received signal SIGABRT, Aborted.
[Switching to Thread 1273579856 (LWP 26695)]
0x0000003ef5830ec5 in raise () from /lib64/libc.so.6
(gdb) bt full
#0  0x0000003ef5830ec5 in raise () from /lib64/libc.so.6
No symbol table info available.
#1  0x0000003ef5832970 in abort () from /lib64/libc.so.6
No symbol table info available.
#2  0x0000003ef582a11f in __assert_fail () from /lib64/libc.so.6
No symbol table info available.
#3  0x00000000006e6cc5 in lock_external (thd=0x2066108, tables=0x20bcbf0, count=2) at lock.cc:387
<cut>

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/48601

2678 Dmitry Lenev	2008-06-27
      Fix for bug #35527 "Foreign keys: crash if self-reference in
      create ... as select"
      
      Execution of CREATE TABLE ... SELECT which contained foreign key
      definitions led to server crash due to failing debug assertion
      or errors about illegal constraint names for production builds.
      
      The first problem was caused by the fact that table list
      elements corresponding to the parent tables of foreign
      key constraints were added to both global table list and
      outer select of the statement but were not ready/intended
      for opening tables (didn't have lock_type properly set). 
      The second problem occured because code implementing CREATE 
      TABLE ... SELECT statement didn't call code generating names 
      for constraints without explicit names.
      
      Full solution for both this problems will be implemented on one
      of the the later milestones of WL#148 "Foreign keys". This patch 
      is a simple workaround which prevents server from asserting and
      emitting confusing errors.

Patch implementing simple workaround for this problem was pushed into mysql-6.1-fk
tree. Full blown solution for it will be implemented on one of later milestones of  WL#148 "Foreign keys". Since this issue was reported against tree which is not
publicly available yet I am simply closing this bug report.