MySQL Bugs: #69939: ALTER TABLE for Adding Foreign Key crashes MySql Server after disabling FK Check

Bug #69939	ALTER TABLE for Adding Foreign Key crashes MySql Server after disabling FK Check
Submitted:	6 Aug 2013 12:49	Modified:	12 Aug 2013 3:02
Reporter:	Unnikrishnan K N	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	MySQL-server-5.6.10	OS:	Any (RHEL6 64-bit , kernel 2.6.32-71.el6.x86_64)
Assigned to:		CPU Architecture:	Any

Description:
After executing the script below the server restarts after getting signal 11

>>> Script Starts here 
SET FOREIGN_KEY_CHECKS=0;
CREATE TABLE SBI_DOMAINS (
       VALUE_ID             INTEGER NOT NULL ,
       VALUE_CD             VARCHAR(100) NULL,
       VALUE_NM             VARCHAR(40) NULL,
       DOMAIN_CD            VARCHAR(20) NULL,
       DOMAIN_NM            VARCHAR(40) NULL,
       VALUE_DS             VARCHAR(160) NULL,
       USER_IN              VARCHAR(100) NOT NULL,
       USER_UP              VARCHAR(100),
       USER_DE              VARCHAR(100),
       TIME_IN              TIMESTAMP NOT NULL,
       TIME_UP              TIMESTAMP NULL DEFAULT NULL,
       TIME_DE              TIMESTAMP NULL DEFAULT NULL,
       SBI_VERSION_IN       VARCHAR(10),
       SBI_VERSION_UP       VARCHAR(10),
       SBI_VERSION_DE       VARCHAR(10),
       META_VERSION         VARCHAR(100),
       ORGANIZATION         VARCHAR(20),    
       UNIQUE XAK1SBI_DOMAINS (VALUE_CD,DOMAIN_CD),
       PRIMARY KEY (VALUE_ID)
) ENGINE=INNODB;

CREATE TABLE  SBI_THRESHOLD  (
	   THRESHOLD_ID  		INT NOT NULL ,
	   THRESHOLD_TYPE_ID  	INT NOT NULL,
	   NAME  				VARCHAR(400),
	   DESCRIPTION  		VARCHAR(1000),
	   CODE  				VARCHAR(45) NOT NULL,
       USER_IN              VARCHAR(100) NOT NULL,
       USER_UP              VARCHAR(100),
       USER_DE              VARCHAR(100),
       TIME_IN              TIMESTAMP NOT NULL,
       TIME_UP              TIMESTAMP NULL DEFAULT NULL,
       TIME_DE              TIMESTAMP NULL DEFAULT NULL,
       SBI_VERSION_IN       VARCHAR(10),
       SBI_VERSION_UP       VARCHAR(10),
       SBI_VERSION_DE       VARCHAR(10),
       META_VERSION         VARCHAR(100),
       ORGANIZATION         VARCHAR(20),     	
	   UNIQUE XIF1SBI_THRESHOLD ( CODE, ORGANIZATION ),
 	   PRIMARY KEY ( THRESHOLD_ID )
) ENGINE = INNODB;

CREATE TABLE  SBI_THRESHOLD_VALUE  (
	   ID_THRESHOLD_VALUE  	INT NOT NULL ,
	   THRESHOLD_ID  		INT NOT NULL,
	   SEVERITY_ID  		INT,
	   POSITION  			INT,
	   MIN_VALUE  			DOUBLE,
	   MAX_VALUE  			DOUBLE,
	   LABEL  				VARCHAR(20) NOT NULL,
	   COLOUR  				VARCHAR(20),
	   MIN_CLOSED  			BOOLEAN,
	   MAX_CLOSED  			BOOLEAN,
	   TH_VALUE  			DOUBLE,
       USER_IN              VARCHAR(100) NOT NULL,
       USER_UP              VARCHAR(100),
       USER_DE              VARCHAR(100),
       TIME_IN              TIMESTAMP NOT NULL,
       TIME_UP              TIMESTAMP NULL DEFAULT NULL,
       TIME_DE              TIMESTAMP NULL DEFAULT NULL,
       SBI_VERSION_IN       VARCHAR(10),
       SBI_VERSION_UP       VARCHAR(10),
       SBI_VERSION_DE       VARCHAR(10),
       META_VERSION         VARCHAR(100),
       ORGANIZATION         VARCHAR(20),     	
	   UNIQUE XIF1SBI_THRESHOLD_VALUE ( LABEL ,THRESHOLD_ID ),
 	   PRIMARY KEY ( ID_THRESHOLD_VALUE )
) ENGINE = INNODB;
ALTER TABLE  SBI_THRESHOLD  ADD FOREIGN KEY ( THRESHOLD_TYPE_ID ) REFERENCES  SBI_DOMAINS  ( VALUE_ID ) ON DELETE  RESTRICT ON UPDATE  RESTRICT;
ALTER TABLE  SBI_THRESHOLD_VALUE  ADD FOREIGN KEY ( SEVERITY_ID ) REFERENCES  SBI_DOMAINS  ( VALUE_ID ) ON DELETE  RESTRICT ON UPDATE  RESTRICT;
ALTER TABLE  SBI_THRESHOLD_VALUE  ADD FOREIGN KEY ( THRESHOLD_ID ) REFERENCES  SBI_THRESHOLD  ( THRESHOLD_ID ) ON DELETE CASCADE ON UPDATE NO ACTION;
SET FOREIGN_KEY_CHECKS=1;
<<< Script , Ends here 

How to repeat:
Just execute the above mentioned script 

Suggested fix:
Server should not crash

MySql Server Error

Attachment: MySql_Server_Error (application/octet-stream, text), 4.95 KiB.

Thank you for the bug report. Please try version 5.6.13. I couldn't repeat with 5.6.12 and current source server. Thanks.

I can reproduce it with 5.6.10 and the given SQL script, but not with 5.6.13 anymore, so seems to be fixed indeed?

Would still be nice to have a regression test case for this though?

5.6.10 initial valgrind error:

Invalid read of size 1
at: __GI_strcmp (mc_replace_strmem.c:731)
by: ha_innobase::prepare_inplace_alter_table (ut0mem.ic:98)
by: handler::ha_prepare_inplace_alter_table (handler.cc:4249)
by: mysql_inplace_alter_table (sql_table.cc:6388)
by: mysql_alter_table (sql_table.cc:8098)
by: Sql_cmd_alter_table::execute (sql_alter.cc:313)
by: mysql_execute_command (sql_parse.cc:4871)
by: mysql_parse (sql_parse.cc:6149)
by: dispatch_command (sql_parse.cc:1334)
by: do_command (sql_parse.cc:1036)
by: do_handle_one_connection (sql_connect.cc:969)
by: handle_one_connection (sql_connect.cc:885)
by: start_thread (pthread_create.c:309)
by: clone (clone.S:115)
 Address 0x0 is not stack'd, malloc'd or (recently) free'd

This bug is fixed in 5.6.12.

Thanks Shane for clarification , 
I had tried the same script in "MySQL-server-5.6.13" which worked fine . So I think this got fixed , but I am not able find any relevant entries in the change pages of mysql release 5.6.12 and 5.6.13.Can you please give the fixed issue details so that we can re-confirm that we are in right track.

We conclude it was a duplicate of Bug #16413976 (noted in change history).