[MDEV-11548] Reproducible server crash after the 2nd ALTER TABLE ADD FOREIGN KEY IF NOT EXISTS Created: 2016-12-12  Updated: 2017-01-09  Resolved: 2017-01-09

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Alter Table
Affects Version/s: 10.0, 10.1, 10.0.28, 10.2
Fix Version/s: 10.0.29

Type: Bug Priority: Major
Reporter: Attila Styevko Assignee: Alexey Botchkov
Resolution: Fixed Votes: 0
Labels: None
Environment:

Gentoo Linux hardened no-multilib, kernel 4.7.10-hardened, 16 GiB RAM, Intel Xeon 64-bit CPU

Attachments: File mysqld.err.bz2    
Issue Links:
Relates
relates to MDEV-318 IF (NOT) EXIST clauses for ALTER TABL... Closed
Sprint: 10.0.29

 Description   

Version: '10.0.28-MariaDB' Source distribution crashes after the following SQL command sequence (mysqld.err attached):

CREATE DATABASE IF NOT EXISTS forgedImei;
 
USE forgedImei;
 
 
 
CREATE TABLE IF NOT EXISTS imei (
 
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 
  imei VARCHAR(16) NOT NULL UNIQUE KEY,
 
  problem ENUM( 'D', 'I' ) NOT NULL,
 
  provInEir ENUM( 'no', 'yes' ) NOT NULL
 
    COMMENT "'yes' means IMEI can be provisioned in EIR."
 
);
 
 
 
CREATE TABLE IF NOT EXISTS imsi (
 
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 
  imsi VARCHAR(16) NOT NULL UNIQUE KEY
 
);
 
 
 
CREATE TABLE IF NOT EXISTS pair (
 
  imei INT UNSIGNED NOT NULL,
 
  imsi INT UNSIGNED NOT NULL
 
);


ALTER TABLE pair
 
ADD FOREIGN KEY IF NOT EXISTS (imei)
 
REFERENCES imei (id)
 
ON DELETE CASCADE
 
ON UPDATE CASCADE;
 
 
 
ALTER TABLE pair
 
ADD FOREIGN KEY IF NOT EXISTS (imsi)
 
REFERENCES imsi (id)
 
ON DELETE CASCADE
 
ON UPDATE CASCADE;



 Comments   
Comment by Elena Stepanova [ 2016-12-13 ]

attila.styevko,
Thanks for the report and the great test case.

10.0 eb4f2e063c341d9f3644339c68cb01679e782001

 
#3  <signal handler called>
 
#4  0x0000000000e5b5e8 in my_strcasecmp_utf8 (cs=0x1782740 <my_charset_utf8_general_ci>, s=0x7f7c9a1a4db8 "pair_ibfk_1", t=0x0) at /data/src/10.0/strings/ctype-utf8.c:5361
 
#5  0x00000000006f8805 in handle_if_exists_options (thd=0x7f7ca3bf3070, table=0x7f7c9a09e470, alter_info=0x7f7caf6e9460) at /data/src/10.0/sql/sql_table.cc:5933
 
#6  0x00000000006fe94f in mysql_alter_table (thd=0x7f7ca3bf3070, new_db=0x7f7c9a1a47f8 "forgedImei", new_name=0x0, create_info=0x7f7caf6e94f0, table_list=0x7f7c9a1a4218, alter_info=0x7f7caf6e9460, order_num=0, order=0x0, ignore=false) at /data/src/10.0/sql/sql_table.cc:8498
 
#7  0x0000000000766f85 in Sql_cmd_alter_table::execute (this=0x7f7c9a1a4a00, thd=0x7f7ca3bf3070) at /data/src/10.0/sql/sql_alter.cc:312
 
#8  0x000000000064f0c8 in mysql_execute_command (thd=0x7f7ca3bf3070) at /data/src/10.0/sql/sql_parse.cc:5109
 
#9  0x00000000006524fe in mysql_parse (thd=0x7f7ca3bf3070, rawbuf=0x7f7c9a1a4088 "ALTER TABLE pair\nADD FOREIGN KEY IF NOT EXISTS (imsi)\nREFERENCES imsi (id)\nON DELETE CASCADE\nON UPDATE CASCADE", length=110, parser_state=0x7f7caf6ea650) at /data/src/10.0/sql/sql_parse.cc:6570
 
#10 0x0000000000645014 in dispatch_command (command=COM_QUERY, thd=0x7f7ca3bf3070, packet=0x7f7c9c108071 "ALTER TABLE pair\nADD FOREIGN KEY IF NOT EXISTS (imsi)\nREFERENCES imsi (id)\nON DELETE CASCADE\nON UPDATE CASCADE", packet_length=110) at /data/src/10.0/sql/sql_parse.cc:1309
 
#11 0x00000000006442d7 in do_command (thd=0x7f7ca3bf3070) at /data/src/10.0/sql/sql_parse.cc:999
 
#12 0x00000000007626d8 in do_handle_one_connection (thd_arg=0x7f7ca3bf3070) at /data/src/10.0/sql/sql_connect.cc:1378
 
#13 0x000000000076244a in handle_one_connection (arg=0x7f7ca3bf3070) at /data/src/10.0/sql/sql_connect.cc:1293
 
#14 0x00000000009fbd80 in pfs_spawn_thread (arg=0x7f7ca71c06f0) at /data/src/10.0/storage/perfschema/pfs.cc:1860
 
#15 0x00007f7caf3310a4 in start_thread (arg=0x7f7caf6eb700) at pthread_create.c:309
 
#16 0x00007f7cad4e987d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:111

Comment by Alexander Barkov [ 2016-12-13 ]

A smaller script demonstrating the same problem:

DROP TABLE IF EXISTS t1;
 
DROP TABLE IF EXISTS t2; 
DROP TABLE IF EXISTS t3;
 
 
 
CREATE TABLE t1 (id INT UNSIGNED NOT NULL PRIMARY KEY);
 
CREATE TABLE t2 (id INT UNSIGNED NOT NULL PRIMARY KEY);
 
CREATE TABLE t3 (
 
  id1 INT UNSIGNED NOT NULL,
 
  id2 INT UNSIGNED NOT NULL
 
);
 
 
 
ALTER TABLE t3
 
ADD FOREIGN KEY IF NOT EXISTS (id1)
 
REFERENCES t1 (id);
 
 
 
ALTER TABLE t3
 
ADD FOREIGN KEY IF NOT EXISTS (id2)
 
REFERENCES t2 (id);

An even smaller script:

DROP TABLE IF EXISTS t1;
 
DROP TABLE IF EXISTS t2; 
 
 
CREATE TABLE t1 (id INT UNSIGNED NOT NULL PRIMARY KEY);
 
CREATE TABLE t2 (id1 INT UNSIGNED NOT NULL);
 
 
 
ALTER TABLE t2
 
ADD FOREIGN KEY IF NOT EXISTS (id1)
 
REFERENCES t1 (id);
 
 
 
ALTER TABLE t2
 
ADD FOREIGN KEY IF NOT EXISTS (id1)
 
REFERENCES t1 (id);

Comment by Alexey Botchkov [ 2017-01-09 ]

http://lists.askmonty.org/pipermail/commits/2017-January/010406.html

Generated at Thu Feb 08 07:50:48 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.