MySQL Bugs: #45052: ALTER TABLE ADD COLUMN crashes server with multiple foreign key columns

Bug #45052	ALTER TABLE ADD COLUMN crashes server with multiple foreign key columns
Submitted:	24 May 2009 7:46	Modified:	17 Jun 2010 1:02
Reporter:	norbert van Nobelen	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	6.0.10-alpha,5.4	OS:	Linux (OpenSuse 10.3)
Assigned to:	Alexey Botchkov	CPU Architecture:	Any
Tags:	add, alter, column, crash, foreign, key, regression, table

Description:
The following statement repeatedly crashes mysql 6.0.10-alpha (no other versions tested):

alter table package add column (currentlocationID INTEGER NOT NULL,  requiredlocationID INTEGER NOT NULL, FOREIGN KEY (currentlocationID) REFERENCES location (locationID) ON DELETE CASCADE, FOREIGN KEY (requiredlocationID) REFERENCES location (locationID) ON DELETE CASCADE);

Crash information:
090524  9:25:53 - mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=33554432
read_buffer_size=262144
max_used_connections=1
max_threads=80
thread_count=1
connection_count=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 217943 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd: 0x2e6bce0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0x4b0970f0 thread_stack 0x40000
mysqld(my_print_stacktrace+0x29) [0xac3839]
mysqld(handle_segfault+0x33b) [0x6518cb]
/lib64/libpthread.so.0 [0x2ae389d3ffb0]
mysqld(mysql_alter_table(THD*, char*, char*, st_ha_create_information*, TABLE_LIST*, Alter_info*, unsigned int, st_order*, bool)+0x1df2) [0x771ab2]
mysqld(mysql_execute_command(THD*)+0x469e) [0x66658e]
mysqld(mysql_parse(THD*, char const*, unsigned int, char const**)+0x36e) [0x66735e]
mysqld(dispatch_command(enum_server_command, THD*, char*, unsigned int)+0x859) [0x668009]
mysqld(handle_one_connection+0x1a9) [0x65afb9]
/lib64/libpthread.so.0 [0x2ae389d38020]
/lib64/libc.so.6(clone+0x6d) [0x2ae38abbff8d]
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x2ec2578 = alter table package add column (currentlocationID INTEGER NOT NULL,  requiredlocationID INTEGER NOT NULL, FOREIGN KEY (currentlocationID) REFERENCES location (locationID) ON DELETE CASCADE, FOREIGN KEY (requiredlocationID) REFERENCES location (locationID) ON DELETE CASCADE)
thd->thread_id=1
thd->killed=NOT_KILLED
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.

Starting the mysqld reveals:

linux:/home/logicore # mysqld --user=mysql &
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
090524  9:26:05  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...
InnoDB: Last MySQL binlog file position 0 186259, file name ./mysql-bin.000037
090524  9:26:05  InnoDB: Started; log sequence number 222 1843487013
090524  9:26:05 [ERROR] mysqld: Incorrect information in file: './mysql/time_zone_name.frm'
090524  9:26:05 [Warning] Can't open and lock time zone table: Incorrect information in file: './mysql/time_zone_name.frm' trying to live without them
090524  9:26:05 [Note] Event Scheduler: Loaded 0 events
090524  9:26:05 [Note] mysqld: ready for connections.
Version: '6.0.10-alpha'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)

P.S. The time_zone error is from a failure in mysql-update. Did not report that one.

Problem analysis:
Adding 1 foreign key column reveals a completely different error: An user error: There is data in the table package, so the NOT NULL constraint can not be applied:
alter table package add column (currentlocationID INTEGER NOT NULL, FOREIGN KEY (currentlocationID) REFERENCES location (locationID) ON DELETE CASCADE);

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`logicore`.`#sql-12e1_1`, CONSTRAINT `#sql-12e1_1_ibfk_3` FOREIGN KEY (`currentlocationID`) REFERENCES `location` (`locationID`) ON DELETE CASCADE)

Altering the statement to remove the NOT NULL condition results in a correct update of the table.

How to repeat:
Create two tables:
create table package (
packageID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
value varchar (200)
) Engine=INNODB;

create table location (
locationID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
value VARCHAR (200)) Engine=INNODB;

insert into location (value) values ("test");

insert into package (value) values ("test");
# There has to be data in the package table to let the NOT NULL constraint fail. One row should do it.
commit;
alter table package add column (currentlocationID INTEGER NOT NULL,  requiredlocationID INTEGER NOT NULL, FOREIGN KEY (currentlocationID) REFERENCES location (locationID) ON DELETE CASCADE, FOREIGN KEY (requiredlocationID) REFERENCES location (locationID) ON DELETE CASCADE);

Suggested fix:
Expected behaviour:
Option 1: The underlying error is an user error (constraint can not be applied) in a single add column. The expected behavious is to get the error.
Option 2: Syntax error?

In Windows 6.0 bzr source tree:

c:\dbs>c:\dbs\6.0\bin\mysqld --defaults-file=c:\dbs\6.0\my.ini --standalone --console
090524 10:36:46  InnoDB: Started; log sequence number 0 46409
090524 10:36:47 [Note] Event Scheduler: Loaded 0 events
090524 10:36:47 [Note] c:\dbs\6.0\bin\mysqld: ready for connections.
Version: '6.0.12-alpha-Win X64-log'  socket: ''  port: 3600  Source distribution
090524 10:38:51 - mysqld got exception 0xc0000005 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=8384512
read_buffer_size=131072
max_used_connections=1
max_threads=151
thread_count=1
connection_count=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 338193 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd: 0x6a9ea40
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
0000000140119A52    mysqld.exe!compare_tables()[sql_table.cc:5711]
000000014011B847    mysqld.exe!mysql_alter_table()[sql_table.cc:7090]
000000014009CC15    mysqld.exe!mysql_execute_command()[sql_parse.cc:2914]
00000001400A0B05    mysqld.exe!mysql_parse()[sql_parse.cc:5984]
00000001400A162C    mysqld.exe!dispatch_command()[sql_parse.cc:1066]
00000001400A262C    mysqld.exe!do_command()[sql_parse.cc:746]
00000001400C4B81    mysqld.exe!handle_one_connection()[sql_connect.cc:1146]
000000014050B93E    mysqld.exe!pthread_start()[my_winthread.c:63]
00000001405B6FA7    mysqld.exe!_callthreadstartex()[threadex.c:348]
00000001405B707F    mysqld.exe!_threadstartex()[threadex.c:326]
00000000772DC3BD    kernel32.dll!BaseThreadInitThunk()
0000000077544581    ntdll.dll!RtlUserThreadStart()
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0000000006AF62B8=alter table package add column (currentlocationID INTEGER NOT NULL,  requiredlocationID
INTEGER NOT NULL, FOREIGN KEY (currentlocationID) REFERENCES location (locationID) ON
DELETE CASCADE, FOREIGN KEY (requiredlocationID) REFERENCES location (locationID) ON
DELETE CASCADE)
thd->thread_id=2
thd->killed=NOT_KILLED
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.

Thank you for the bug report. Repeatable since 6.0.4 release:

c:\temp\mysql-6.0.4-alpha-winx64>bin\mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 6.0.4-alpha-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table package (
    -> packageID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> value varchar (200)
    -> ) Engine=INNODB;
Query OK, 0 rows affected (0.70 sec)

mysql>
mysql> create table location (
    -> locationID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> value VARCHAR (200)) Engine=INNODB;
Query OK, 0 rows affected (0.25 sec)

mysql>
mysql> insert into location (value) values ("test");
Query OK, 1 row affected (0.12 sec)

mysql>
mysql> insert into package (value) values ("test");
Query OK, 1 row affected (0.10 sec)

mysql> alter table package add column (currentlocationID INTEGER NOT NULL,  requiredlocationID
    -> INTEGER NOT NULL, FOREIGN KEY (currentlocationID) REFERENCES location (locationID) ON
    -> DELETE CASCADE, FOREIGN KEY (requiredlocationID) REFERENCES location (locationID) ON
    -> DELETE CASCADE);
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> exit
Bye

c:\temp\mysql-6.0.4-alpha-winx64>cd ..

c:\temp>cd mysql-6.0.2-alpha-winx64

c:\temp\mysql-6.0.2-alpha-winx64>bin\mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 6.0.2-alpha-community-nt-debug MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table package (
    -> packageID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> value varchar (200)
    -> ) Engine=INNODB;
Query OK, 0 rows affected (0.26 sec)

mysql>
mysql> create table location (
    -> locationID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> value VARCHAR (200)) Engine=INNODB;
Query OK, 0 rows affected (0.21 sec)

mysql>
mysql> insert into location (value) values ("test");
Query OK, 1 row affected (0.21 sec)

mysql>
mysql> insert into package (value) values ("test");
Query OK, 1 row affected (0.24 sec)

mysql> alter table package add column (currentlocationID INTEGER NOT NULL,  requiredlocationID
    -> INTEGER NOT NULL, FOREIGN KEY (currentlocationID) REFERENCES location (locationID) ON
    -> DELETE CASCADE, FOREIGN KEY (requiredlocationID) REFERENCES location (locationID) ON
    -> DELETE CASCADE);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`#sql-b98_1`, CONSTRAINT `#sql-b98_1_ibfk_1` FOREIGN KEY (`cu
rrentlocationID`) REFERENCES `location` (`locationID`) ON DELETE CASCADE)
mysql>

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/76438

3360 Alexey Botchkov	2009-06-17
      Bug#45052      ALTER TABLE ADD COLUMN crashes server with multiple foreign key columns
          when compare_tables() called handling the ALTER TABLE,
          table->field array only has entries for the old table fields, so
          trying to get table->field[new_field_number] value and access the
          resulting 'field' we crash the server. When we only add one field
          with the ALTER, crash doesn't happen as table->field[old_field_num+1]
          contains NULL and it's checked. But if we add a couple of fields,
          table->field[old_field_num+2] contains rubbish.
          Bug can be reproduced without INNODB or FOREIGN KEY things.
      
      per-file comments:
        mysql-test/r/alter_table.result
      Bug#45052      ALTER TABLE ADD COLUMN crashes server with multiple foreign key columns
          test result updated
      
        mysql-test/t/alter_table.test
      Bug#45052      ALTER TABLE ADD COLUMN crashes server with multiple foreign key columns
          bug testcase added
      
        sql/sql_table.cc
      Bug#45052      ALTER TABLE ADD COLUMN crashes server with multiple foreign key columns
         don't get the table->field[field_num] if the field_num > old_field_num

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/76439

3360 Alexey Botchkov	2009-06-16
      Bug#45052      ALTER TABLE ADD COLUMN crashes server with multiple foreign key columns
          when compare_tables() called handling the ALTER TABLE,
          table->field array only has entries for the old table fields, so
          trying to get table->field[new_field_number] value and access the
          resulting 'field' we crash the server. When we only add one field
          with the ALTER, crash doesn't happen as table->field[old_field_num+1]
          contains NULL and it's checked. But if we add a couple of fields,
          table->field[old_field_num+2] contains rubbish.
          Bug can be reproduced without INNODB or FOREIGN KEY things.
      
      per-file comments:
        mysql-test/r/alter_table.result
      Bug#45052      ALTER TABLE ADD COLUMN crashes server with multiple foreign key columns
          test result updated
      
        mysql-test/t/alter_table.test
      Bug#45052      ALTER TABLE ADD COLUMN crashes server with multiple foreign key columns
          bug testcase added
      
        sql/sql_table.cc
      Bug#45052      ALTER TABLE ADD COLUMN crashes server with multiple foreign key columns
         don't get the table->field[field_num] if the field_num > old_field_num

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/77025

2805 Alexey Botchkov	2009-06-24
      Bug#45052      ALTER TABLE ADD COLUMN crashes server with multiple foreign key columns
          when compare_tables() called handling the ALTER TABLE,
          table->field array only has entries for the old table fields, so
          trying to get table->field[new_field_number] value and access the
          resulting 'field' we crash the server. When we only add one field
          with the ALTER, crash doesn't happen as table->field[old_field_num+1]
          contains NULL and it's checked. But if we add a couple of fields,
          table->field[old_field_num+2] contains rubbish.
          Bug can be reproduced without INNODB or FOREIGN KEY things.
      
      per-file comments:
        mysql-test/r/alter_table.result
      Bug#45052      ALTER TABLE ADD COLUMN crashes server with multiple foreign key columns
          test result updated
      
        mysql-test/t/alter_table.test
      Bug#45052      ALTER TABLE ADD COLUMN crashes server with multiple foreign key columns
          bug testcase added
      
        sql/sql_table.cc
      Bug#45052      ALTER TABLE ADD COLUMN crashes server with multiple foreign key columns
         don't get the table->field[field_num] if the field_num > old_field_num

pushed into mysql-azalea-bugfixing tree

Pushed into 5.4.4-alpha (revid:alik@sun.com-20090702084644-k95gd2asolvz2zpu) (version source revid:jon.hauglid@sun.com-20090625092953-xiur7w0mz78g6nmo) (merge vers: 5.4.4-alpha) (pib:11)

Pushed into 5.4.4-alpha (revid:alik@sun.com-20090702084644-k95gd2asolvz2zpu) (version source revid:jon.hauglid@sun.com-20090625092953-xiur7w0mz78g6nmo) (merge vers: 5.4.4-alpha) (pib:11)

Noted in 5.4.4 changelogs.

ALTER TABLE ... ADD COLUMN for a table with multiple foreign keys
caused a server crash.

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/109700

3085 Alexey Botchkov	2010-05-31
      test for the bug #45052 backporting.

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/109783

3086 Alexey Botchkov	2010-05-31
      test added for the bug #45052

Pushed into 5.5.5-m3 (revid:alik@sun.com-20100615080459-smuswd9ooeywcxuc) (version source revid:marko.makela@oracle.com-20100601134335-ccthwwru23kn09qw) (merge vers: 5.1.48) (pib:16)

Pushed into mysql-next-mr (revid:alik@sun.com-20100615080558-cw01bzdqr1bdmmec) (version source revid:marko.makela@oracle.com-20100601134335-ccthwwru23kn09qw) (pib:16)

Noted in 5.5.5 changelog.