[MDEV-16830] ALTER TABLE DROP FOREIGN KEY - unexpected end of stream error Created: 2018-07-26  Updated: 2019-02-28  Resolved: 2018-08-03

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Alter Table, Storage Engine - InnoDB
Affects Version/s: 10.3.7, 10.3.8
Fix Version/s: 10.3.9

Type: Bug Priority: Blocker
Reporter: Robert Dyas Assignee: Marko Mäkelä
Resolution: Fixed Votes: 0
Labels: crash, ddl, instant
Environment:

CentOS7

Issue Links:
Duplicate
duplicates MDEV-18771 crash when modifying column name and ... Closed
Relates
relates to MDEV-11369 Instant add column for InnoDB Closed
relates to MDEV-13134 Introduce ALTER TABLE attributes ALGO... Closed

 Description   

On 10.3.8 when you issue the following command:

ALTER TABLE `p1b` COMMENT 'drop fk test', DROP FOREIGN KEY p1b_ibfk_1

it generates the following error:

(conn=41) unexpected end of stream, read 0 bytes from 4 (socket was closed by server)

Note that this has worked without issue in all prior 10.1 and 10.2 versions we have used. Also, note that the following does NOT generate an error and does work correctly:

ALTER TABLE `p1b` DROP FOREIGN KEY p1b_ibfk_1

Also the following works correctly:

ALTER TABLE `p1b` COMMENT 'add fk test', ADD  FOREIGN KEY (`p1`) REFERENCES `p1` (`p1_ID`) ON DELETE RESTRICT ON UPDATE CASCADE



 Comments   
Comment by Robert Dyas [ 2018-07-27 ]

Would it be possible to get this assigned? This little bug is actually a blocking bug for use to use the 10.3 series.

Comment by Robert Dyas [ 2018-08-02 ]

This seems like an important bug to resolve. How do I get it assigned to be looked at?

Comment by Elena Stepanova [ 2018-08-03 ]

Thanks for the report. Sorry for the delay.

--source include/have_innodb.inc
 
 
 
create table t1 (a int, key(a)) engine=InnoDB;
 
create table t2 (b int, foreign key(b) references t1(a)) engine=InnoDB;
 
 
 
alter table t2 COMMENT 'drop fk test', drop foreign key t2_ibfk_1;
 
 
 
# Cleanup
 
drop table t2, t1;

10.3 debug 89b6ce026

 
mysqld: /data/src/10.3/storage/innobase/handler/handler0alter.cc:7121: virtual bool ha_innobase::inplace_alter_table(TABLE*, Alter_inplace_info*): Assertion `ctx->trx' failed.
 
180803 14:50:54 [ERROR] mysqld got signal 6 ;
 
 
 
#7  0x00007f61b74bbee2 in __assert_fail () from /lib/x86_64-linux-gnu/libc.so.6
 
#8  0x0000559121e92ceb in ha_innobase::inplace_alter_table (this=0x7f61680757a8, altered_table=0x7f6168172ce0, ha_alter_info=0x7f61b07fe420) at /data/src/10.3/storage/innobase/handler/handler0alter.cc:7121
 
#9  0x000055912192527b in handler::ha_inplace_alter_table (this=0x7f61680757a8, altered_table=0x7f6168172ce0, ha_alter_info=0x7f61b07fe420) at /data/src/10.3/sql/handler.h:4155
 
#10 0x000055912191afe0 in mysql_inplace_alter_table (thd=0x7f6168000b00, table_list=0x7f6168014e38, table=0x7f6168074b60, altered_table=0x7f6168172ce0, ha_alter_info=0x7f61b07fe420, inplace_supported=HA_ALTER_INPLACE_NOCOPY_NO_LOCK, target_mdl_request=0x7f61b07fe490, alter_ctx=0x7f61b07ff090) at /data/src/10.3/sql/sql_table.cc:7570
 
#11 0x000055912192106c in mysql_alter_table (thd=0x7f6168000b00, new_db=0x7f61680051b0, new_name=0x7f6168005568, create_info=0x7f61b07ffc80, table_list=0x7f6168014e38, alter_info=0x7f61b07ffbc0, order_num=0, order=0x0, ignore=false) at /data/src/10.3/sql/sql_table.cc:9712
 
#12 0x00005591219a8011 in Sql_cmd_alter_table::execute (this=0x7f61680154f0, thd=0x7f6168000b00) at /data/src/10.3/sql/sql_alter.cc:495
 
#13 0x000055912184b377 in mysql_execute_command (thd=0x7f6168000b00) at /data/src/10.3/sql/sql_parse.cc:6281
 
#14 0x0000559121850371 in mysql_parse (thd=0x7f6168000b00, rawbuf=0x7f6168014d08 "alter table t2 COMMENT 'drop fk test', drop foreign key t2_ibfk_1", length=65, parser_state=0x7f61b08015f0, is_com_multi=false, is_next_command=false) at /data/src/10.3/sql/sql_parse.cc:8074
 
#15 0x000055912183d6d0 in dispatch_command (command=COM_QUERY, thd=0x7f6168000b00, packet=0x7f6168146841 "alter table t2 COMMENT 'drop fk test', drop foreign key t2_ibfk_1", packet_length=65, is_com_multi=false, is_next_command=false) at /data/src/10.3/sql/sql_parse.cc:1847
 
#16 0x000055912183c0f4 in do_command (thd=0x7f6168000b00) at /data/src/10.3/sql/sql_parse.cc:1392
 
#17 0x00005591219a25f1 in do_handle_one_connection (connect=0x5591249db9b0) at /data/src/10.3/sql/sql_connect.cc:1402
 
#18 0x00005591219a2375 in handle_one_connection (arg=0x5591249db9b0) at /data/src/10.3/sql/sql_connect.cc:1308
 
#19 0x0000559121e33c2d in pfs_spawn_thread (arg=0x559124a7cee0) at /data/src/10.3/storage/perfschema/pfs.cc:1862
 
#20 0x00007f61b9192494 in start_thread (arg=0x7f61b0802700) at pthread_create.c:333
 
#21 0x00007f61b757893f in clone () from /lib/x86_64-linux-gnu/libc.so.6

10.3 non-debug 89b6ce026

 
#2  <signal handler called>
 
#3  row_merge_read_clustered_index (trx=trx@entry=0x7f5a0c373348, table=table@entry=0x7f59b00681e8, old_table=old_table@entry=0x7f59b00800b0, new_table=new_table@entry=0x7f59b00800b0, online=online@entry=true, index=index@entry=0x0, fts_sort_idx=0x0, psort_info=0x0, files=0x7f59b0065050, key_numbers=0x0, n_index=0, defaults=0x0, add_v=0x0, col_map=0x0, add_autoinc=18446744073709551615, sequence=..., block=0x7f59fc4f9000 <error: Cannot access memory at address 0x7f59fc4f9000>, skip_pk_sort=false, tmpfd=0x7f5a0c0d9260, stage=0x7f59b0081170, pct_cost=pct_cost@entry=100, crypt_block=0x0, eval_table=0x7f59b00681e8, allow_not_null=false) at /data/src/10.3/storage/innobase/row/row0merge.cc:1785
 
#4  0x000055cb2d39c273 in row_merge_build_indexes (trx=0x7f5a0c373348, old_table=0x7f59b00800b0, new_table=0x7f59b00800b0, online=true, indexes=<optimized out>, key_numbers=0x0, n_indexes=0, table=0x7f59b00681e8, defaults=0x0, col_map=0x0, add_autoinc=18446744073709551615, sequence=..., skip_pk_sort=false, stage=0x7f59b0081170, add_v=0x0, eval_table=0x7f59b00681e8, allow_not_null=false) at /data/src/10.3/storage/innobase/row/row0merge.cc:4771
 
#5  0x000055cb2d2fec79 in ha_innobase::inplace_alter_table (this=0x7f59b0075580, altered_table=0x7f59b00681e8, ha_alter_info=0x7f5a0c0d9e80) at /data/src/10.3/storage/innobase/handler/handler0alter.cc:7206
 
#6  0x000055cb2ce36fa3 in ha_inplace_alter_table (ha_alter_info=0x7f5a0c0d9e80, altered_table=0x7f59b00681e8, this=<optimized out>) at /data/src/10.3/sql/handler.h:4155
 
#7  mysql_inplace_alter_table (thd=thd@entry=0x7f59b00009a8, table_list=0x7f59b00117e0, table=table@entry=0x7f59b0082808, altered_table=altered_table@entry=0x7f59b00681e8, ha_alter_info=ha_alter_info@entry=0x7f5a0c0d9e80, inplace_supported=inplace_supported@entry=HA_ALTER_INPLACE_NOCOPY_NO_LOCK, alter_ctx=0x7f5a0c0dab20, target_mdl_request=0x7f5a0c0d9f20) at /data/src/10.3/sql/sql_table.cc:7570
 
#8  0x000055cb2cfa39eb in mysql_alter_table (thd=0x7f59b00009a8, new_db=<optimized out>, new_name=<optimized out>, create_info=0x7f5a0c0db6d0, table_list=0x7f59b00117e0, alter_info=0x7f5a0c0db610, order_num=0, order=0x0, ignore=false) at /data/src/10.3/sql/sql_table.cc:9712
 
#9  0x000055cb2cfef6f5 in Sql_cmd_alter_table::execute (this=0x0, thd=0x7f59b00009a8) at /data/src/10.3/sql/sql_alter.cc:495
 
#10 0x000055cb2cf12372 in mysql_execute_command (thd=0x7f59b00009a8) at /data/src/10.3/sql/sql_parse.cc:6281
 
#11 0x000055cb2cf18ce1 in mysql_parse (thd=0x7f59b00009a8, rawbuf=<optimized out>, length=65, parser_state=0x7f5a0c0dd630, is_com_multi=<optimized out>, is_next_command=<optimized out>) at /data/src/10.3/sql/sql_parse.cc:8074
 
#12 0x000055cb2cf1b312 in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x7f59b00009a8, packet=packet@entry=0x7f59b0009349 "alter table t2 COMMENT 'drop fk test', drop foreign key t2_ibfk_1", packet_length=packet_length@entry=65, is_com_multi=is_com_multi@entry=false, is_next_command=is_next_command@entry=false) at /data/src/10.3/sql/sql_parse.cc:1847
 
#13 0x000055cb2cf1bcc0 in do_command (thd=0x7f59b00009a8) at /data/src/10.3/sql/sql_parse.cc:1392
 
#14 0x000055cb2cfec974 in do_handle_one_connection (connect=connect@entry=0x55cb2f6443f8) at /data/src/10.3/sql/sql_connect.cc:1402
 
#15 0x000055cb2cfecb14 in handle_one_connection (arg=arg@entry=0x55cb2f6443f8) at /data/src/10.3/sql/sql_connect.cc:1308
 
#16 0x000055cb2d2cfef4 in pfs_spawn_thread (arg=0x55cb2f6b5238) at /data/src/10.3/storage/perfschema/pfs.cc:1862
 
#17 0x00007f5a1393f494 in start_thread (arg=0x7f5a0c0de700) at pthread_create.c:333
 
#18 0x00007f5a11d2593f in clone () from /lib/x86_64-linux-gnu/libc.so.6

Comment by Marko Mäkelä [ 2018-08-03 ]

After the refactoring of ALTER TABLE flags, setting a table comment will set the ALTER_OPTIONS flag, which is aliased by ALTER_CHANGE_CREATE_OPTION. I think that we should use the short flag name in InnoDB.

It looks like that this is a regression caused by MDEV-11369 or some follow-up work, such as MDEV-13134. The following should fix this test case:

@@ -7107,8 +7103,9 @@ ha_innobase::inplace_alter_table(
 
 		DBUG_RETURN(false);
 
 	}
 
 
-	if ((ha_alter_info->handler_flags & ~INNOBASE_INPLACE_IGNORE)
 
-	    == ALTER_CHANGE_CREATE_OPTION
 
+	if ((ha_alter_info->handler_flags & ~(INNOBASE_INPLACE_IGNORE
 
+					      | INNOBASE_ALTER_INSTANT))
 
+	    == ALTER_OPTIONS
 
 	    && !create_option_need_rebuild(ha_alter_info, table)) {
 
 		goto ok_exit;
 
 	}

Also, it looks like ADD COLUMN will unnecessarily require a table rebuild when combined with DROP FOREIGN KEY (or ADD FOREIGN KEY when foreign_key_cheks=0). I will add a test for that too.

Comment by Marko Mäkelä [ 2018-08-03 ]

I was suspecting a similar problem in ADD COLUMN in combination with other instantaneous changes, such as DROP FOREIGN KEY, but that code was fine. I added a test case for it.

In MariaDB 10.3 before 10.3.9, this server crash can be worked around by changing the table comment in a separate ALTER TABLE statement.

Comment by Robert Dyas [ 2018-08-05 ]

I just realized the same error is also caused by:

ALTER TABLE `Vendor` COMMENT 'tbl comment', CHANGE COLUMN `Main_Fax` `Fax` varchar(30) COMMENT '' AFTER `Main_Phone`

which produces this error:

(conn=1383) unexpected end of stream, read 0 bytes from 4 (socket was closed by server)

Will the bug fix above also fix this problem or should it be submitted as a new bug?

Generated at Thu Feb 08 08:31:52 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.