[MDEV-26127] Assertion `err != DB_DUPLICATE_KEY' failed or InnoDB: Failing assertion: id != 0 on ALTER ... REBUILD PARTITION Created: 2021-07-12  Updated: 2022-06-13  Resolved: 2022-06-13

Status: Closed
Project: MariaDB Server
Component/s: Partitioning, Storage Engine - InnoDB
Affects Version/s: 10.2, 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9
Fix Version/s: 10.3.36, 10.4.26, 10.5.17, 10.6.9, 10.7.5, 10.8.4, 10.9.2

Type: Bug Priority: Critical
Reporter: Roel Van de Paar Assignee: Nayuta Yanagisawa (Inactive)
Resolution: Fixed Votes: 0
Labels: regression

Issue Links:
Relates
relates to MDEV-25506 Atomic DDL: .frm file is removed and ... Closed
relates to MDEV-25119 Test case for former ER_TABLE_EXISTS_... Open
relates to MDEV-25620 InnoDB: Failing assertion: id != 0 or... Closed
relates to MDEV-25642 Assertion `err != DB_DUPLICATE_KEY' f... Closed

 Description    

SET GLOBAL innodb_file_per_table=OFF;
 
CREATE TABLE t (c INT) ENGINE=InnoDB PARTITION BY LINEAR KEY(c) PARTITIONS 4;
 
LOCK TABLES t WRITE,t AS a READ;
 
ALTER TABLE t REBUILD PARTITION p0;

Leads to:

10.7.0 57f14eab20ae2733eb341f3d293515a10a40bc48 (Optimized)

 
InnoDB: Failing assertion: id != 0

10.7.0 57f14eab20ae2733eb341f3d293515a10a40bc48 (Optimized)

 
Core was generated by `/test/MD090721-mariadb-10.7.0-linux-x86_64-opt/bin/mysqld --no-defaults --core-'.
 
Program terminated with signal SIGABRT, Aborted.
 
#0  __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
 
[Current thread is 1 (Thread 0x1506f8575700 (LWP 804858))]
 
(gdb) bt
 
#0  __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
 
#1  0x000015070d3ef859 in __GI_abort () at abort.c:79
 
#2  0x000055d744b05b5d in ut_dbg_assertion_failed (expr=expr@entry=0x55d74572509c "id != 0", file=file@entry=0x55d745724770 "/test/10.7_opt/storage/innobase/fil/fil0fil.cc", line=line@entry=1874) at /test/10.7_opt/storage/innobase/ut/ut0dbg.cc:60
 
#3  0x000055d744b17312 in fil_rename_tablespace (new_path_in=0x1506bc02d3f8 "./test/t#P#p0#TMP#.ibd", old_path=0x55d74769ff68 "./ibdata1", id=0) at /test/10.7_opt/storage/innobase/fil/fil0fil.cc:1874
 
#4  fil_space_t::rename (this=<optimized out>, path=path@entry=0x1506bc02d3f8 "./test/t#P#p0#TMP#.ibd", log=log@entry=true, replace=replace@entry=true) at /test/10.7_opt/storage/innobase/fil/fil0fil.cc:1856
 
#5  0x000055d7452e91f4 in dict_table_t::rename_tablespace (this=this@entry=0x1506bc034480, new_name=new_name@entry=0x1506bc01f665 "test/t#P#p0#TMP#", replace=replace@entry=true) at /test/10.7_opt/storage/innobase/dict/dict0dict.cc:1507
 
#6  0x000055d74535cfce in row_undo_ins_parse_undo_rec (node=0x1506bc027410, dict_locked=<optimized out>) at /test/10.7_opt/storage/innobase/row/row0uins.cc:473
 
#7  0x000055d74535db7c in row_undo_ins (node=node@entry=0x1506bc027410, thr=thr@entry=0x1506bc035038) at /test/10.7_opt/storage/innobase/row/row0uins.cc:616
 
#8  0x000055d74525155b in row_undo (thr=0x1506bc035038, node=0x1506bc027410) at /test/10.7_opt/storage/innobase/row/row0undo.cc:422
 
#9  row_undo_step (thr=thr@entry=0x1506bc035038) at /test/10.7_opt/storage/innobase/row/row0undo.cc:478
 
#10 0x000055d745206038 in que_thr_step (thr=0x1506bc035038) at /test/10.7_opt/storage/innobase/que/que0que.cc:651
 
#11 que_run_threads_low (thr=<optimized out>) at /test/10.7_opt/storage/innobase/que/que0que.cc:709
 
#12 que_run_threads (thr=0x1506bc035038) at /test/10.7_opt/storage/innobase/que/que0que.cc:729
 
#13 0x000055d745277e8d in trx_t::rollback_low (this=this@entry=0x15070c280230, savept=savept@entry=0x0) at /test/10.7_opt/storage/innobase/trx/trx0roll.cc:125
 
#14 0x000055d745276d48 in trx_t::rollback (savept=0x0, this=0x15070c280230) at /test/10.7_opt/storage/innobase/trx/trx0roll.cc:170
 
#15 trx_t::rollback (this=this@entry=0x15070c280230, savept=savept@entry=0x0) at /test/10.7_opt/storage/innobase/trx/trx0roll.cc:157
 
#16 0x000055d745235d2a in row_rename_table_for_mysql (old_name=old_name@entry=0x1506f8570160 "test/t#P#p0#TMP#", new_name=<optimized out>, new_name@entry=0x1506f856ff60 "test/t#P#p0", trx=trx@entry=0x15070c280230, use_fk=use_fk@entry=true) at /test/10.7_opt/storage/innobase/row/row0mysql.cc:3107
 
#17 0x000055d74517a24b in innobase_rename_table (use_fk=true, to=0x1506f8570940 "./test/t#P#p0", from=0x1506f8570730 "./test/t#P#p0#TMP#", trx=0x15070c280230) at /test/10.7_opt/storage/innobase/handler/ha_innodb.cc:13534
 
#18 ha_innobase::rename_table (this=<optimized out>, from=0x1506f8570730 "./test/t#P#p0#TMP#", to=0x1506f8570940 "./test/t#P#p0") at /test/10.7_opt/storage/innobase/handler/ha_innodb.cc:13785
 
#19 0x000055d7450bee2b in ha_partition::rename_partitions (this=0x1506bc01c990, path=0x1506f8570f40 "./test/t") at /test/10.7_opt/sql/ha_partition.cc:1170
 
#20 0x000055d744feecd1 in mysql_rename_partitions (lpt=0x1506f8570be0) at /test/10.7_opt/sql/sql_partition.cc:6074
 
#21 fast_alter_partition_table (thd=thd@entry=0x1506bc000c58, table=table@entry=0x1506bc01c588, alter_info=alter_info@entry=0x1506f85734a0, create_info=create_info@entry=0x1506f8573590, table_list=0x1506bc010940, db=db@entry=0x1506f85727a0, table_name=0x1506f85727b0) at /test/10.7_opt/sql/sql_partition.cc:7438
 
#22 0x000055d744ce2edd in mysql_alter_table (thd=thd@entry=0x1506bc000c58, new_db=new_db@entry=0x1506bc0056a8, new_name=new_name@entry=0x1506bc005ac0, create_info=create_info@entry=0x1506f8573590, table_list=<optimized out>, table_list@entry=0x1506bc010940, alter_info=alter_info@entry=0x1506f85734a0, order_num=0, order=0x0, ignore=false, if_exists=false) at /test/10.7_opt/sql/sql_table.cc:9901
 
#23 0x000055d744d4dccd in Sql_cmd_alter_table::execute (this=<optimized out>, thd=0x1506bc000c58) at /test/10.7_opt/sql/structs.h:568
 
#24 0x000055d744c3c5be in mysql_execute_command (thd=0x1506bc000c58, is_called_from_prepared_stmt=<optimized out>) at /test/10.7_opt/sql/sql_parse.cc:5993
 
#25 0x000055d744c2c9c6 in mysql_parse (thd=0x1506bc000c58, rawbuf=<optimized out>, length=<optimized out>, parser_state=<optimized out>) at /test/10.7_opt/sql/sql_parse.cc:8026
 
#26 0x000055d744c388c5 in dispatch_command (command=COM_QUERY, thd=0x1506bc000c58, packet=<optimized out>, packet_length=<optimized out>, blocking=<optimized out>) at /test/10.7_opt/sql/sql_class.h:1340
 
#27 0x000055d744c3a7e7 in do_command (thd=0x1506bc000c58, blocking=blocking@entry=true) at /test/10.7_opt/sql/sql_parse.cc:1404
 
#28 0x000055d744d48e67 in do_handle_one_connection (connect=<optimized out>, put_in_cache=true) at /test/10.7_opt/sql/sql_connect.cc:1410
 
#29 0x000055d744d491cd in handle_one_connection (arg=arg@entry=0x55d74788fee8) at /test/10.7_opt/sql/sql_connect.cc:1312
 
#30 0x000055d7450d3628 in pfs_spawn_thread (arg=0x55d74782c758) at /test/10.7_opt/storage/perfschema/pfs.cc:2201
 
#31 0x000015070d8fe609 in start_thread (arg=<optimized out>) at pthread_create.c:477
 
#32 0x000015070d4ec293 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

10.7.0 57f14eab20ae2733eb341f3d293515a10a40bc48 (Debug)

 
mysqld: /test/10.7_dbg/storage/innobase/row/row0mysql.cc:2955: dberr_t row_rename_table_for_mysql(const char*, const char*, trx_t*, bool): Assertion `err != DB_DUPLICATE_KEY' failed.

10.7.0 57f14eab20ae2733eb341f3d293515a10a40bc48 (Debug)

 
Core was generated by `/test/MD090721-mariadb-10.7.0-linux-x86_64-dbg/bin/mysqld --no-defaults --core-'.
 
Program terminated with signal SIGABRT, Aborted.
 
#0  __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
 
[Current thread is 1 (Thread 0x1488580bb700 (LWP 512257))]
 
(gdb) bt
 
#0  __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
 
#1  0x000014885911e859 in __GI_abort () at abort.c:79
 
#2  0x000014885911e729 in __assert_fail_base (fmt=0x1488592b4588 "%s%s%s:%u: %s%sAssertion `%s' failed.\n%n", assertion=0x55da8061db35 "err != DB_DUPLICATE_KEY", file=0x55da8061b7c0 "/test/10.7_dbg/storage/innobase/row/row0mysql.cc", line=2955, function=<optimized out>) at assert.c:92
 
#3  0x000014885912ff36 in __GI___assert_fail (assertion=assertion@entry=0x55da8061db35 "err != DB_DUPLICATE_KEY", file=file@entry=0x55da8061b7c0 "/test/10.7_dbg/storage/innobase/row/row0mysql.cc", line=line@entry=2955, function=function@entry=0x55da8061cd00 "dberr_t row_rename_table_for_mysql(const char*, const char*, trx_t*, bool)") at assert.c:101
 
#4  0x000055da7ff2e5c3 in row_rename_table_for_mysql (old_name=<optimized out>, old_name@entry=0x1488580b5cf0 "test/t#P#p0#TMP#", new_name=<optimized out>, new_name@entry=0x1488580b5af0 "test/t#P#p0", trx=trx@entry=0x1488441fc390, use_fk=use_fk@entry=true) at /test/10.7_dbg/storage/innobase/row/row0mysql.cc:2955
 
#5  0x000055da7fda01d7 in innobase_rename_table (use_fk=true, to=0x1488580b6510 "./test/t#P#p0", from=0x1488580b6300 "./test/t#P#p0#TMP#", trx=0x1488441fc390) at /test/10.7_dbg/storage/innobase/handler/ha_innodb.cc:13534
 
#6  ha_innobase::rename_table (this=<optimized out>, from=0x1488580b6300 "./test/t#P#p0#TMP#", to=0x1488580b6510 "./test/t#P#p0") at /test/10.7_dbg/storage/innobase/handler/ha_innodb.cc:13785
 
#7  0x000055da7f9ca416 in handler::ha_rename_table (this=this@entry=0x148814014a78, from=from@entry=0x1488580b6300 "./test/t#P#p0#TMP#", to=to@entry=0x1488580b6510 "./test/t#P#p0") at /test/10.7_dbg/sql/handler.cc:5284
 
#8  0x000055da7fcc570e in ha_partition::rename_partitions (this=0x148814030900, path=0x1488580b6aa0 "./test/t") at /test/10.7_dbg/sql/ha_partition.cc:1170
 
#9  0x000055da7f9caa60 in handler::ha_rename_partitions (this=0x148814030900, path=path@entry=0x1488580b6aa0 "./test/t") at /test/10.7_dbg/sql/handler.cc:5496
 
#10 0x000055da7fbc8c29 in mysql_rename_partitions (lpt=0x1488580b6800) at /test/10.7_dbg/sql/sql_partition.cc:6074
 
#11 fast_alter_partition_table (thd=thd@entry=0x148814000db8, table=table@entry=0x148814024848, alter_info=alter_info@entry=0x1488580b93e0, create_info=create_info@entry=0x1488580b94d0, table_list=0x1488140140a0, db=db@entry=0x1488580b86c0, table_name=0x1488580b86d0) at /test/10.7_dbg/sql/sql_partition.cc:7438
 
#12 0x000055da7f7b248f in mysql_alter_table (thd=thd@entry=0x148814000db8, new_db=new_db@entry=0x1488140059c8, new_name=new_name@entry=0x148814005de0, create_info=create_info@entry=0x1488580b94d0, table_list=<optimized out>, table_list@entry=0x1488140140a0, alter_info=alter_info@entry=0x1488580b93e0, order_num=<optimized out>, order=<optimized out>, ignore=<optimized out>, if_exists=<optimized out>) at /test/10.7_dbg/sql/sql_table.cc:9901
 
#13 0x000055da7f84208d in Sql_cmd_alter_table::execute (this=<optimized out>, thd=0x148814000db8) at /test/10.7_dbg/sql/structs.h:568
 
#14 0x000055da7f6dce67 in mysql_execute_command (thd=thd@entry=0x148814000db8, is_called_from_prepared_stmt=is_called_from_prepared_stmt@entry=false) at /test/10.7_dbg/sql/sql_parse.cc:5993
 
#15 0x000055da7f6c3b27 in mysql_parse (thd=thd@entry=0x148814000db8, rawbuf=<optimized out>, length=<optimized out>, parser_state=parser_state@entry=0x1488580ba400) at /test/10.7_dbg/sql/sql_parse.cc:8026
 
#16 0x000055da7f6d2692 in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x148814000db8, packet=packet@entry=0x14881400b769 "ALTER TABLE t REBUILD PARTITION p0", packet_length=packet_length@entry=34, blocking=blocking@entry=true) at /test/10.7_dbg/sql/sql_class.h:1340
 
#17 0x000055da7f6d5aaa in do_command (thd=0x148814000db8, blocking=blocking@entry=true) at /test/10.7_dbg/sql/sql_parse.cc:1404
 
#18 0x000055da7f83a09a in do_handle_one_connection (connect=<optimized out>, connect@entry=0x55da82e1aa78, put_in_cache=put_in_cache@entry=true) at /test/10.7_dbg/sql/sql_connect.cc:1410
 
#19 0x000055da7f83a69f in handle_one_connection (arg=arg@entry=0x55da82e1aa78) at /test/10.7_dbg/sql/sql_connect.cc:1312
 
#20 0x000055da7fce9400 in pfs_spawn_thread (arg=0x55da82d02fd8) at /test/10.7_dbg/storage/perfschema/pfs.cc:2201
 
#21 0x000014885962d609 in start_thread (arg=<optimized out>) at pthread_create.c:477
 
#22 0x000014885921b293 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

Bug confirmed present in:
MariaDB: 10.6.3 (dbg), 10.6.3 (opt), 10.7.0 (dbg), 10.7.0 (opt)

Bug (or feature/syntax) confirmed not present in:
MariaDB: 10.2.39 (dbg), 10.2.39 (opt), 10.3.30 (dbg), 10.3.30 (opt), 10.4.20 (dbg), 10.4.20 (opt), 10.5.11 (dbg), 10.5.11 (opt)
MySQL: 5.5.62 (dbg), 5.5.62 (opt), 5.6.51 (dbg), 5.6.51 (opt), 5.7.34 (dbg), 5.7.34 (opt), 8.0.24 (dbg), 8.0.24 (opt)

 Comments   
Comment by Thirunarayanan Balathandayuthapani [ 2021-07-12 ]

InnoDB fails to drop the table during rebuild partitions.

Thread 13 "mariadbd" hit Breakpoint 1, ha_innobase::delete_table (
 
    this=0x7fffb8144420, name=0x7ffff0318a90 "./test/t#P#p0")
 
    at /home/thiru/mariarepo/10.6/10.6-sample/storage/innobase/handler/ha_innodb.cc:13322
 
13322   {
 
(gdb) where
 
#0  ha_innobase::delete_table (this=0x7fffb8144420, name=0x7ffff0318a90 "./test/t#P#p0")
 
    at /home/thiru/mariarepo/10.6/10.6-sample/storage/innobase/handler/ha_innodb.cc:13430
 
#1  0x000055555657233b in ha_partition::rename_partitions (this=0x7fffb8143b30, path=0x7ffff0318d20 "./test/t")
 
    at /home/thiru/mariarepo/10.6/10.6-sample/sql/ha_partition.cc:1160
 
#2  0x000055555622d16c in handler::ha_rename_partitions (this=0x7fffb8143b30, path=0x7ffff0318d20 "./test/t") at /home/thiru/mariarepo/10.6/10.6-sample/sql/handler.cc:5496
 
#3  0x000055555641adae in mysql_rename_partitions (lpt=0x7ffff0318fd0) at /home/thiru/mariarepo/10.6/10.6-sample/sql/sql_partition.cc:6074
 
#4  0x000055555641e7ff in fast_alter_partition_table (thd=0x7fffb8000d78, table=0x7fffb8238888, alter_info=0x7ffff031b370, create_info=0x7ffff031b460, 
    table_list=0x7fffb8015670, db=0x7ffff031a660, table_name=0x7ffff031a670) at /home/thiru/mariarepo/10.6/10.6-sample/sql/sql_partition.cc:7438
 
#5  0x0000555555f9f00d in mysql_alter_table (thd=0x7fffb8000d78, new_db=0x7fffb8005988, new_name=0x7fffb8005da0, create_info=0x7ffff031b460, table_list=0x7fffb8015670, 
    alter_info=0x7ffff031b370, order_num=0, order=0x0, ignore=false, if_exists=false) at /home/thiru/mariarepo/10.6/10.6-sample/sql/sql_table.cc:9901
 
#6  0x000055555605230f in Sql_cmd_alter_table::execute (this=0x7fffb8015d70, thd=0x7fffb8000d78) at /home/thiru/mariarepo/10.6/10.6-sample/sql/sql_alter.cc:550
 
#7  0x0000555555e9fd0c in mysql_execute_command (thd=0x7fffb8000d78, is_called_from_prepared_stmt=false) at /home/thiru/mariarepo/10.6/10.6-sample/sql/sql_parse.cc:5993
 
#8  0x0000555555ea5dec in mysql_parse (thd=0x7fffb8000d78, rawbuf=0x7fffb8015580 "ALTER TABLE t REBUILD PARTITION p0", length=34, parser_state=0x7ffff031c500)
 
    at /home/thiru/mariarepo/10.6/10.6-sample/sql/sql_parse.cc:8026
 
#9  0x0000555555e92355 in dispatch_command (command=COM_QUERY, thd=0x7fffb8000d78, packet=0x7fffb800b839 "ALTER TABLE t REBUILD PARTITION p0", packet_length=34, 
    blocking=true) at /home/thiru/mariarepo/10.6/10.6-sample/sql/sql_parse.cc:1896
 
#10 0x0000555555e90c22 in do_command (thd=0x7fffb8000d78, blocking=true) at /home/thiru/mariarepo/10.6/10.6-sample/sql/sql_parse.cc:1404
 
#11 0x00005555560473b7 in do_handle_one_connection (connect=0x5555586147e8, put_in_cache=true) at /home/thiru/mariarepo/10.6/10.6-sample/sql/sql_connect.cc:1410
 
#12 0x000055555604711e in handle_one_connection (arg=0x555558b654a8) at /home/thiru/mariarepo/10.6/10.6-sample/sql/sql_connect.cc:1312
 
#13 0x00005555565958ab in pfs_spawn_thread (arg=0x555558aa3fb8) at /home/thiru/mariarepo/10.6/10.6-sample/storage/perfschema/pfs.cc:2201
 
#14 0x00007ffff6cf06db in start_thread (arg=0x7ffff031d700) at pthread_create.c:463
 
#15 0x00007ffff5e8b71f in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95
 
(gdb) p err
 
$6 = DB_LOCK_WAIT_TIMEOUT

I think this error happens due to LOCK TABLES t WRITE, t AS a read

Comment by Thirunarayanan Balathandayuthapani [ 2021-07-14 ]

Alter copy algorithm does close_all_tables_for_name() to close all
instances of the table before deleting the tablespace.
But during rebuild of partition, Server does alter_close_table() fails
to remove all instance of the table.

In 10.2 also, same problem exist. But InnoDB defer the dropping of table.
Below is the warning from error log for 10.2:

InnoDB: Deferring DROP TABLE `test`.`t` /* Partition `p0` */; renaming to test/#sql-ib20

I think server should close all the table instance belongs to thread before deleting
the tablespace.

Comment by Marko Mäkelä [ 2021-10-19 ]

This still fails after MDEV-26772 was fixed in 10.6.

Comment by Nayuta Yanagisawa (Inactive) [ 2021-10-19 ]

Let me pick the issue up.

Comment by Nayuta Yanagisawa (Inactive) [ 2021-10-29 ]

The partitioning engine ignores errors from the base storage engine in some places. This seems to be the cause of the present bug. https://github.com/MariaDB/server/blob/mariadb-10.6.4/sql/ha_partition.cc#L1160

Comment by Nayuta Yanagisawa (Inactive) [ 2021-10-29 ]

Here are fixes for 10.2 and 10.6, but there are still some problems. 10.2 and 10.6 behave differently (please see the test cases). I have not yet checked why such a difference occurs.

https://github.com/MariaDB/server/commit/71326717c28d4235bad7a926b8ec5a2e81a52152
https://github.com/MariaDB/server/commit/d3cc7f5d09c8bf89cdd0f02fcf4bffcb66ef1a8f

Comment by Marko Mäkelä [ 2021-10-29 ]

I think that it is fine to improve the quality of our partitioning in small steps.

Comment by Nayuta Yanagisawa (Inactive) [ 2021-10-31 ]

True. However, the current fix introduces some inconsistency with the error handling in ha_partition::rename_partitions() (the other part of the function still suppress errors). So, I'm going to improve it a bit more.

Comment by Nayuta Yanagisawa (Inactive) [ 2022-03-24 ]

Patch for 10.2-10.4: https://github.com/MariaDB/server/commit/fe75fbd68e861eada772eff05f20945d824e18ce
Patch for 10.5+: https://github.com/MariaDB/server/commit/6efb8e8fa91499583f0b5a4401b51f75de5c754c

Only a small diff between the two patches. I marked the difference by comments.

Comment by Nayuta Yanagisawa (Inactive) [ 2022-05-11 ]

I updated patches:

Comment by Alexey Botchkov [ 2022-06-13 ]

ok to push.

Generated at Thu Feb 08 09:42:57 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.