Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: 10.0.16
-
Fix Version/s: 10.0.17
-
Component/s: Data Definition - Alter Table, Storage Engine - InnoDB
-
Labels:None
Description
ALTER enters into an infinite loop on reserving extents until system runs out of disk mysqld aborts.
#!/bin/bash mysql="mysql -uroot -S /var/run/mysql/mysql.sock " $mysql test < ./createTable.sql #for i in $( seq 22 1 122 ); do for i in $( seq 22 1 2722 ); do echo $i $mysql test -e "INSERT INTO \`mdl_course_modules\` VALUES ($i,4,5,5,24,NULL,1141569781,'',0,0,1,0,'',0,'locked',1,1,0,0,0,0,NULL,0,0,0,0,0,0);" done $mysql test -e "ALTER TABLE test.mdl_course_modules ADD stefantest LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci AFTER showdescription;"
sles12-x86-64:/var/lib/mysql/test # ls -ahl total 9.0G -rw-rw---- 1 mysql mysql 6.2K Jun 2 12:52 #sql-ba5_b99.frm -rw-rw---- 1 mysql mysql 9.0G Jun 2 13:08 #sql-ib18-3361842814.ibd drwx------ 1 mysql root 168 Jun 2 12:52 . drwx------ 1 mysql root 248 Jun 2 13:08 .. -rw-rw---- 1 mysql mysql 6.2K Jun 2 12:46 mdl_course_modules.frm -rw-rw---- 1 mysql mysql 1008K Jun 2 12:52 mdl_course_modules.ibd
Gliffy Diagrams
Attachments
Issue Links
- relates to
-
-
- Closed
-
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Innodb keeps filling the temporary table ibd file seemingly in an infinite loop on ALTER INPLACE.
The bug report has a test case, with which I could reproduce the issue only on SLES12 and with
the given rpm packages. With a custom build the issue could not be reproduced.
At the end mysqld aborts with the following stack trace :
Program received signal SIGABRT, Aborted. [Switching to Thread 0x7fdf48268700 (LWP 12374)] 0x00007fdf6c2e4187 in raise () from /lib64/libc.so.6 (gdb) bt #0 0x00007fdf6c2e4187 in raise () from /lib64/libc.so.6 #1 0x00007fdf6c2e5538 in abort () from /lib64/libc.so.6 #2 0x0000000000b1d4bb in sync_thread_add_level (latch=0x1dfd940 <dict_operation_lock>, level=1010, relock=0) at /usr/src/debug/mariadb-10.0.16/storage/xtradb/sync/sync0sync.cc:1287 #3 0x0000000000b1a315 in rw_lock_add_debug_info (lock=lock@entry=0x1dfd940 <dict_operation_lock>, pass=pass@entry=0, lock_type=lock_type@entry=351, file_name=file_name@entry=0xe4d1b0 "/home/abuild/rpmbuild/BUILD/mariadb-10.0.16/storage/xtradb/handler/handler0alter.cc", line=line@entry=4181) at /usr/src/debug/mariadb-10.0.16/storage/xtradb/sync/sync0rw.cc:985 #4 0x0000000000b1b00a in rw_lock_x_lock_low (line=4181, file_name=0xe4d1b0 "/home/abuild/rpmbuild/BUILD/mariadb-10.0.16/storage/xtradb/handler/handler0alter.cc", pass=0, high_priority=false, lock=0x1dfd940 <dict_operation_lock>) at /usr/src/debug/mariadb-10.0.16/storage/xtradb/sync/sync0rw.cc:738 #5 rw_lock_x_lock_func (lock=lock@entry=0x1dfd940 <dict_operation_lock>, pass=pass@entry=0, file_name=file_name@entry=0xe4d1b0 "/home/abuild/rpmbuild/BUILD/mariadb-10.0.16/storage/xtradb/handler/handler0alter.cc", line=line@entry=4181, priority_lock=priority_lock@entry=false, high_priority=high_priority@entry=false) at /usr/src/debug/mariadb-10.0.16/storage/xtradb/sync/sync0rw.cc:796 #6 0x0000000000accb96 in pfs_rw_lock_x_lock_func (lock=<optimized out>, pass=0, line=4181, file_name=0xe4d1b0 "/home/abuild/rpmbuild/BUILD/mariadb-10.0.16/storage/xtradb/handler/handler0alter.cc") at /usr/src/debug/mariadb-10.0.16/storage/xtradb/include/sync0rw.ic:890 #7 row_mysql_lock_data_dictionary_func (trx=0x7fdf08017688, file=file@entry=0xe4d1b0 "/home/abuild/rpmbuild/BUILD/mariadb-10.0.16/storage/xtradb/handler/handler0alter.cc", line=line@entry=4181) at /usr/src/debug/mariadb-10.0.16/storage/xtradb/row/row0mysql.cc:2184 #8 0x0000000000a06923 in rollback_inplace_alter_table (prebuilt=0x7fdf1003a8e8, table=0x7fdf10031470, ha_alter_info=0x7fdf48264080) at /usr/src/debug/mariadb-10.0.16/storage/xtradb/handler/handler0alter.cc:4181 #9 ha_innobase::commit_inplace_alter_table (this=0x7fdf10036d68, altered_table=0x7fdf0800ed50, ha_alter_info=0x7fdf48264080, commit=<optimized out>) at /usr/src/debug/mariadb-10.0.16/storage/xtradb/handler/handler0alter.cc:5431 #10 0x00000000006a07da in mysql_inplace_alter_table (target_mdl_request=0x7fdf482641b0, alter_ctx=0x7fdf48264d40, inplace_supported=HA_ALTER_INPLACE_NO_LOCK_AFTER_PREPARE, ha_alter_info=0x7fdf48264080, altered_table=0x7fdf0800ed50, table=0x7fdf10031470, table_list=0x7fdf08005408, thd=0x7fdf10031470) at /usr/src/debug/mariadb-10.0.16/sql/sql_table.cc:7140 #11 mysql_alter_table (thd=thd@entry=0x2f7aa00, new_db=<optimized out>, new_name=<optimized out>, create_info=create_info@entry=0x7fdf48265920, table_list=table_list@entry=0x7fdf08005408, alter_info=alter_info@entry=0x7fdf48265890, order_num=0, order=0x0, ignore=false) at /usr/src/debug/mariadb-10.0.16/sql/sql_table.cc:8815 #12 0x00000000006efdd2 in Sql_cmd_alter_table::execute (this=<optimized out>, thd=0x2f7aa00) at /usr/src/debug/mariadb-10.0.16/sql/sql_alter.cc:312 #13 0x000000000060a12b in mysql_execute_command (thd=thd@entry=0x2f7aa00) at /usr/src/debug/mariadb-10.0.16/sql/sql_parse.cc:5079 #14 0x0000000000613107 in mysql_parse (thd=thd@entry=0x2f7aa00, rawbuf=<optimized out>, length=124, parser_state=parser_state@entry=0x7fdf482676b0) at /usr/src/debug/mariadb-10.0.16/sql/sql_parse.cc:6513 #15 0x0000000000614d33 in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x2f7aa00, packet=packet@entry=0x2f814b1 "ALTER TABLE test.mdl_course_modules ADD stefantest LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci AFTER showdescription", packet_length=packet_length@entry=124) at /usr/src/debug/mariadb-10.0.16/sql/sql_parse.cc:1300 #16 0x0000000000615f45 in do_command (thd=0x2f7aa00) at /usr/src/debug/mariadb-10.0.16/sql/sql_parse.cc:996 #17 0x00000000006ec7f4 in do_handle_one_connection (thd_arg=thd_arg@entry=0x2f7aa00) at /usr/src/debug/mariadb-10.0.16/sql/sql_connect.cc:1375 #18 0x00000000006ec887 in handle_one_connection (arg=0x2f7aa00) at /usr/src/debug/mariadb-10.0.16/sql/sql_connect.cc:1289 #19 0x00007fdf6d4be0a4 in start_thread () from /lib64/libpthread.so.0 #20 0x00007fdf6c3947fd in clone () from /lib64/libc.so.6#0 0x00007fdf6c2e4187 in raise () from /lib64/libc.so.6 #1 0x00007fdf6c2e5538 in abort () from /lib64/libc.so.6 #2 0x0000000000b1d4bb in sync_thread_add_level (latch=0x1dfd940 <dict_operation_lock>, level=1010, relock=0) at /usr/src/debug/mariadb-10.0.16/storage/xtradb/sync/sync0sync.cc:1287 #3 0x0000000000b1a315 in rw_lock_add_debug_info (lock=lock@entry=0x1dfd940 <dict_operation_lock>, pass=pass@entry=0, lock_type=lock_type@entry=351, file_name=file_name@entry=0xe4d1b0 "/home/abuild/rpmbuild/BUILD/mariadb-10.0.16/storage/xtradb/handler/handler0alter.cc", line=line@entry=4181) at /usr/src/debug/mariadb-10.0.16/storage/xtradb/sync/sync0rw.cc:985 #4 0x0000000000b1b00a in rw_lock_x_lock_low (line=4181, file_name=0xe4d1b0 "/home/abuild/rpmbuild/BUILD/mariadb-10.0.16/storage/xtradb/handler/handler0alter.cc", pass=0, high_priority=false, lock=0x1dfd940 <dict_operation_lock>) at /usr/src/debug/mariadb-10.0.16/storage/xtradb/sync/sync0rw.cc:738 #5 rw_lock_x_lock_func (lock=lock@entry=0x1dfd940 <dict_operation_lock>, pass=pass@entry=0, file_name=file_name@entry=0xe4d1b0 "/home/abuild/rpmbuild/BUILD/mariadb-10.0.16/storage/xtradb/handler/handler0alter.cc", line=line@entry=4181, priority_lock=priority_lock@entry=false, high_priority=high_priority@entry=false) at /usr/src/debug/mariadb-10.0.16/storage/xtradb/sync/sync0rw.cc:796 #6 0x0000000000accb96 in pfs_rw_lock_x_lock_func (lock=<optimized out>, pass=0, line=4181, file_name=0xe4d1b0 "/home/abuild/rpmbuild/BUILD/mariadb-10.0.16/storage/xtradb/handler/handler0alter.cc") at /usr/src/debug/mariadb-10.0.16/storage/xtradb/include/sync0rw.ic:890 #7 row_mysql_lock_data_dictionary_func (trx=0x7fdf08017688, file=file@entry=0xe4d1b0 "/home/abuild/rpmbuild/BUILD/mariadb-10.0.16/storage/xtradb/handler/handler0alter.cc", line=line@entry=4181) at /usr/src/debug/mariadb-10.0.16/storage/xtradb/row/row0mysql.cc:2184 #8 0x0000000000a06923 in rollback_inplace_alter_table (prebuilt=0x7fdf1003a8e8, table=0x7fdf10031470, ha_alter_info=0x7fdf48264080) at /usr/src/debug/mariadb-10.0.16/storage/xtradb/handler/handler0alter.cc:4181 #9 ha_innobase::commit_inplace_alter_table (this=0x7fdf10036d68, altered_table=0x7fdf0800ed50, ha_alter_info=0x7fdf48264080, commit=<optimized out>) at /usr/src/debug/mariadb-10.0.16/storage/xtradb/handler/handler0alter.cc:5431 #10 0x00000000006a07da in mysql_inplace_alter_table (target_mdl_request=0x7fdf482641b0, alter_ctx=0x7fdf48264d40, inplace_supported=HA_ALTER_INPLACE_NO_LOCK_AFTER_PREPARE, ha_alter_info=0x7fdf48264080, altered_table=0x7fdf0800ed50, table=0x7fdf10031470, table_list=0x7fdf08005408, thd=0x7fdf10031470) at /usr/src/debug/mariadb-10.0.16/sql/sql_table.cc:7140 #11 mysql_alter_table (thd=thd@entry=0x2f7aa00, new_db=<optimized out>, new_name=<optimized out>, create_info=create_info@entry=0x7fdf48265920, table_list=table_list@entry=0x7fdf08005408, alter_info=alter_info@entry=0x7fdf48265890, order_num=0, order=0x0, ignore=false) at /usr/src/debug/mariadb-10.0.16/sql/sql_table.cc:8815 #12 0x00000000006efdd2 in Sql_cmd_alter_table::execute (this=<optimized out>, thd=0x2f7aa00) at /usr/src/debug/mariadb-10.0.16/sql/sql_alter.cc:312 #13 0x000000000060a12b in mysql_execute_command (thd=thd@entry=0x2f7aa00) at /usr/src/debug/mariadb-10.0.16/sql/sql_parse.cc:5079 #14 0x0000000000613107 in mysql_parse (thd=thd@entry=0x2f7aa00, rawbuf=<optimized out>, length=124, parser_state=parser_state@entry=0x7fdf482676b0) at /usr/src/debug/mariadb-10.0.16/sql/sql_parse.cc:6513 #15 0x0000000000614d33 in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x2f7aa00, packet=packet@entry=0x2f814b1 "ALTER TABLE test.mdl_course_modules ADD stefantest LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci AFTER showdescription", packet_length=packet_length@entry=124) at /usr/src/debug/mariadb-10.0.16/sql/sql_parse.cc:1300 #16 0x0000000000615f45 in do_command (thd=0x2f7aa00) at /usr/src/debug/mariadb-10.0.16/sql/sql_parse.cc:996 #17 0x00000000006ec7f4 in do_handle_one_connection (thd_arg=thd_arg@entry=0x2f7aa00) at /usr/src/debug/mariadb-10.0.16/sql/sql_connect.cc:1375 #18 0x00000000006ec887 in handle_one_connection (arg=0x2f7aa00) at /usr/src/debug/mariadb-10.0.16/sql/sql_connect.cc:1289 #19 0x00007fdf6d4be0a4 in start_thread () from /lib64/libpthread.so.0 #20 0x00007fdf6c3947fd in clone () from /lib64/libc.so.6
I did dig even further and found that Innodb apparently gets into an infinite loop while
reserving free extents. (fsp_reserve_free_extents()) until mysqld aborts.
MDEV-8142 (SUSE bug#930498) could be related to this issue.
Remarkably, it only happens with XtraDB; InnoDB plugin works fine.
The MTR variation of the test case is obvious:
--source include/have_innodb.inc
drop database if exists moodle19;
create database moodle19;
use moodle19;
CREATE TABLE `mdl_course_modules` (
`id` bigint(10) NOT NULL AUTO_INCREMENT,
`course` bigint(10) NOT NULL DEFAULT '0',
`module` bigint(10) NOT NULL DEFAULT '0',
`instance` bigint(10) NOT NULL DEFAULT '0',
`section` bigint(10) NOT NULL DEFAULT '0',
`idnumber` varchar(100) DEFAULT NULL,
`added` bigint(10) NOT NULL DEFAULT '0',
`delay` varchar(10) NOT NULL DEFAULT '0',
`score` smallint(4) NOT NULL DEFAULT '0',
`indent` mediumint(5) NOT NULL DEFAULT '0',
`visible` tinyint(1) NOT NULL DEFAULT '1',
`checkboxesforprereqs` tinyint(1) NOT NULL DEFAULT '0',
`stylewhencomplete` varchar(200) DEFAULT '',
`checkboxforcomplete` tinyint(1) NOT NULL DEFAULT '0',
`stylewhenlocked` varchar(200) DEFAULT 'locked',
`visiblewhenlocked` tinyint(1) NOT NULL DEFAULT '1',
`visibleold` tinyint(1) NOT NULL DEFAULT '1',
`groupmode` smallint(4) NOT NULL DEFAULT '0',
`groupingid` bigint(10) NOT NULL DEFAULT '0',
`groupmembersonly` smallint(4) NOT NULL DEFAULT '0',
`completion` tinyint(1) NOT NULL DEFAULT '0',
`completiongradeitemnumber` bigint(10) DEFAULT NULL,
`completionview` tinyint(1) NOT NULL DEFAULT '0',
`completionexpected` bigint(10) NOT NULL DEFAULT '0',
`availablefrom` bigint(10) NOT NULL DEFAULT '0',
`availableuntil` bigint(10) NOT NULL DEFAULT '0',
`showavailability` tinyint(1) NOT NULL DEFAULT '0',
`showdescription` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `mdl_courmodu_vis_ix` (`visible`),
KEY `mdl_courmodu_cou_ix` (`course`),
KEY `mdl_courmodu_mod_ix` (`module`),
KEY `mdl_courmodu_ins_ix` (`instance`),
KEY `mdl_courmodu_idncou_ix` (`idnumber`,`course`),
KEY `mdl_courmodu_gro_ix` (`groupingid`)
) ENGINE=InnoDB AUTO_INCREMENT=447023 DEFAULT CHARSET=utf8 COMMENT='course_modules table retrofitted from MySQL';
let $num= 2701;
--disable_query_log
--echo # Inserting $num rows into the table...
while ($num)
{
eval INSERT INTO mdl_course_modules VALUES ($num,4,5,5,24,NULL,1141569781,'',0,0,1,0,'',0,'locked',1,1,0,0,0,0,NULL,0,0,0,0,0,0);
dec $num;
}
--enable_query_log
ALTER TABLE moodle19.mdl_course_modules ADD stefantest LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci AFTER showdescription;
drop database moodle19;
It also passes for innodb_plugin and hangs for xtradb.
Looks like the following 2 patches for MDEV-6288 may have triggered it.
https://github.com/MariaDB/server/commit/6bd2f900b2681539a542d3639df3017f04f4279d.patch
https://github.com/MariaDB/server/commit/43c851435fb7da4c707a21797860232889702a4a.patch
The 3rd one for MDEV-6288 (pushed in mariadb-10.0.17), seemingly has fixed the issue.
https://github.com/MariaDB/server/commit/454beee5fbd9da8e87a54b46ea716679693361bd.patch
The issue, so far, could be reproduced only on SLES12 with --no-defaults.