MDEV-16417 Store Foreign Key metadata outside of InnoDB

[midenok]

Subtasks

• Data organization
• Change operations
• InnoDB refactoring
• Crash safety

Links

MDEV-16417 Store Foreign Key metadata outside of InnoDB

Data organization

Each column contains foreign info (foreign_info) and referenced info (ref_list). Foreign info contains database, table name, column name. Referenced info is a list of database and table names.

TODO

• [ ] test db/table names in different locales
• [x] test self-references: adding/dropping of them, dropping of self-referenced column, dropping self-referenced table, etc.

These 2 ^^^ can be done in existing test cases.

• [ ] check corner cases of mysql_prepare_alter_table(): mysql_create_like_table() and `compare_table_with_partition()
• [x] try storage without referenced_key_name
• [x] 64k extra2 limit error test case
• [x] check for cmp() correctly by fs locale for table names (and system locale for columns)
• [x] where fk duplicates are checked? FK duplicates are allowed!
• [x] revert in ALTER

https://dev.mysql.com/worklog/task/?id=148 (see Checks during CREATE TABLE or ALTER TABLE )

Current limitations

extra2 section is limited to 65534 bytes. FRM total size is limited to 1048576 bytes.

Pull off 64k size limit of Extra2 segment in FRM file

Extra2 segment size is stored as 2-byte in 3 places:

1. by offset 4 in FRM header;
2. by offset 6 in FRM header as position to key information (disk_buff);
3. in the beginning of Extra2 segment.

disk_buff is at 2 bytes address stored in 2. forminfo is at 4 bytes address stored after extra2 strpos is at forminfo + FRM_FORMINFO_SIZE + "length of all screens" recpos is at 3 bytes address stored in strpos + 5

So after "disk_buff" there can be Extra3 segment as next segment "forminfo" is addressed by 4 bytes.

Change operations

• RENAME COLUMN
• RENAME TABLE
• DROP TABLE
• DROP DATABASE

DROP COLUMN

When dropping the column of table A, if there is foreign_info containing table B then A is removed from B ref_list. If the column contains ref_list, then drop is failed.

DROP TABLE

For each column of dropped table A do instructions from DROP COLUMN.

InnoDB refactoring

There about 35 occurrences of SYS_FOREIGN in InnoDB SQL code and 27 occurrences of SYS_FOREIGN_COLS.

The following 16 functions work with SYS_FOREIGN and/or SYS_FOREIGN_COLS and must be dropped or refactored:

• btr_node_ptr_max_size()
• dict_create_or_check_foreign_constraint_tables()
• dict_create_add_foreign_to_dictionary()
• dict_create_add_foreigns_to_dictionary()
• dict_create_add_foreign_field_to_dictionary()
• dict_process_sys_foreign_rec()
• dict_process_sys_foreign_col_rec()
• dict_load_foreign()
• dict_load_foreigns()
• dict_load_foreign_cols()
• innobase_drop_foreign_try()
• innobase_rename_column_try()
• row_drop_table_for_mysql()
• drop_all_foreign_keys_in_db()
• row_delete_constraint_low()
• row_rename_table_for_mysql()
• dict_foreign_parse_drop_constraints()
• dict_table_get_highest_foreign_id()

The following functions work with FOREIGN_KEY_INFO:

• get_foreign_key_info()
• ha_innobase::get_foreign_key_list()
• ha_innobase::get_parent_foreign_key_list()

Test suite fixes

Crash safety

Since RENAME operations are non-business logic: they are used for application upgrade, but not for application usual operation we should not decide how to store data based on requirements for them. RENAME operations can have simple crash safety mechanism which corresponds to their minor role.

1. create new version for each FRM;
2. move each old FRM to backup FRM;
3. move each new FRM to working FRM;
4. create timestamp file with list of backup FRM names;
5. delete backup FRM files;
6. delete timestamp file.

On startup:

1. if timestamp file exists, delete all files listed there (there can be multiple timestamp files from parallel non-conflicting ALTER commands);
2. if new FRM files exist, delete them;
3. if backup FRM files exist, move them to working files (replacing the existing ones).

If crash occurs before 4. it may have some list of backup FRM which must be restored. New version FRM files must be thrown off. If crash occurs after 4. the backups are not important now as all new FRM files are in places. Backups can be thrown off. 4. must be atomic. It is not acceptable if timestamp file contains not full list of backup FRM files. So either timestamp must exist and be complete or not exist at all. It is also acceptable if timestamp file will be empty as it will be skipped from the processing.

Note

execute_ddl_log_action() can rename and delete .frm files.

[midenok]

Info

1. FRM created

#0  my_create (FileName=0x7ffff476c800 "./test/t1.frm", CreateFlags=0, access_flags=514, MyFlags=0) at /home/midenok/src/mariadb/10.5/src/mysys/my_create.c:40
#1  0x0000000000be3c84 in inline_mysql_file_create (key=0, src_file=0x181297d "/home/midenok/src/mariadb/10.5/src/sql/discover.cc", src_line=128, filename=0x7ffff476c800 "./test/t1.frm", create_flags=0, access_flags=514, myFlags=0) at /home/midenok/src/mariadb/10.5/src/include/mysql/psi/mysql_file.h:1041
#2  0x0000000000be3934 in writefrm (path=0x7ffff476fbb0 "./test/t1", db=0x7fff88013ce0 "test", table=0x7fff880135f8 "t1", tmp_table=false, frmdata=0x3642688 "\376\001\n\f\022", len=432) at /home/midenok/src/mariadb/10.5/src/sql/discover.cc:127
#3  0x00000000009df73c in TABLE_SHARE::write_frm_image (this=0x7ffff476d6e0, frm=0x3642688 "\376\001\n\f\022", len=432) at /home/midenok/src/mariadb/10.5/src/sql/table.cc:3266
#4  0x00000000009d6822 in TABLE_SHARE::init_from_binary_frm_image (this=0x7ffff476d6e0, thd=0x7fff88000d08, write=true, frm_image=0x3642688 "\376\001\n\f\022", frm_length=432) at /home/midenok/src/mariadb/10.5/src/sql/table.cc:1681
#5  0x0000000000c33923 in ha_create_table (thd=0x7fff88000d08, path=0x7ffff476fbb0 "./test/t1", db=0x7fff88013ce0 "test", table_name=0x7fff880135f8 "t1", create_info=0x7ffff47701a8, frm=0x7ffff476fba0) at /home/midenok/src/mariadb/10.5/src/sql/handler.cc:5132
#6  0x000000000098bdad in create_table_impl (thd=0x7fff88000d08, orig_db=..., orig_table_name=..., db=..., table_name=..., path=0x7ffff476fbb0 "./test/t1", options=..., create_info=0x7ffff47701a8, alter_info=0x7ffff47700f0, create_table_mode=0, is_trans=0x7ffff476fee7, key_info=0x7ffff476fdc0, key_count=0x7ffff476fdbc, frm=0x7ffff476fba0) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:5078
#7  0x000000000098af58 in mysql_create_table_no_lock (thd=0x7fff88000d08, db=0x7fff88013648, table_name=0x7fff88013658, create_info=0x7ffff47701a8, alter_info=0x7ffff47700f0, is_trans=0x7ffff476fee7, create_table_mode=0, table_list=0x7fff88013630) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:5162
#8  0x000000000098c370 in mysql_create_table (thd=0x7fff88000d08, create_table=0x7fff88013630, create_info=0x7ffff47701a8, alter_info=0x7ffff47700f0) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:5254
#9  0x00000000009a19fb in Sql_cmd_create_table_like::execute (this=0x7fff880135d0, thd=0x7fff88000d08) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:11484
#10 0x000000000088f7ac in mysql_execute_command (thd=0x7fff88000d08) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:5892
#11 0x0000000000880d30 in mysql_parse (thd=0x7fff88000d08, rawbuf=0x7fff88013520 "create or replace table t1 (x int)", length=34, parser_state=0x7ffff4772620, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:7939

2. IB table created

#0  create_table_info_t::create_table (this=0x7ffff476d0f0, create_fk=true) at /home/midenok/src/mariadb/10.5/src/storage/innobase/handler/ha_innodb.cc:12751
#1  0x0000000001083e8c in ha_innobase::create (this=0x7fff8803e690, name=0x7ffff476fbb0 "./test/t1", form=0x7ffff476de70, create_info=0x7ffff47701a8, file_per_table=true, trx=0x7ffff47fb360) at /home/midenok/src/mariadb/10.5/src/storage/innobase/handler/ha_innodb.cc:13073
#2  0x000000000106463f in ha_innobase::create (this=0x7fff8803e690, name=0x7ffff476fbb0 "./test/t1", form=0x7ffff476de70, create_info=0x7ffff47701a8) at /home/midenok/src/mariadb/10.5/src/storage/innobase/handler/ha_innodb.cc:13126
#3  0x0000000000c31f21 in handler::ha_create (this=0x7fff8803e690, name=0x7ffff476fbb0 "./test/t1", form=0x7ffff476de70, info_arg=0x7ffff47701a8) at /home/midenok/src/mariadb/10.5/src/sql/handler.cc:4691
#4  0x0000000000c33ae7 in ha_create_table (thd=0x7fff88000d08, path=0x7ffff476fbb0 "./test/t1", db=0x7fff88013ce0 "test", table_name=0x7fff880135f8 "t1", create_info=0x7ffff47701a8, frm=0x7ffff476fba0) at /home/midenok/src/mariadb/10.5/src/sql/handler.cc:5155
#5  0x000000000098bdad in create_table_impl (thd=0x7fff88000d08, orig_db=..., orig_table_name=..., db=..., table_name=..., path=0x7ffff476fbb0 "./test/t1", options=..., create_info=0x7ffff47701a8, alter_info=0x7ffff47700f0, create_table_mode=0, is_trans=0x7ffff476fee7, key_info=0x7ffff476fdc0, key_count=0x7ffff476fdbc, frm=0x7ffff476fba0) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:5078
#6  0x000000000098af58 in mysql_create_table_no_lock (thd=0x7fff88000d08, db=0x7fff88013648, table_name=0x7fff88013658, create_info=0x7ffff47701a8, alter_info=0x7ffff47700f0, is_trans=0x7ffff476fee7, create_table_mode=0, table_list=0x7fff88013630) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:5162
#7  0x000000000098c370 in mysql_create_table (thd=0x7fff88000d08, create_table=0x7fff88013630, create_info=0x7ffff47701a8, alter_info=0x7ffff47700f0) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:5254
#8  0x00000000009a19fb in Sql_cmd_create_table_like::execute (this=0x7fff880135d0, thd=0x7fff88000d08) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:11484
#9  0x000000000088f7ac in mysql_execute_command (thd=0x7fff88000d08) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:5892
#10 0x0000000000880d30 in mysql_parse (thd=0x7fff88000d08, rawbuf=0x7fff88013520 "create or replace table t1 (x int)", length=34, parser_state=0x7ffff4772620, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:7939

Divergence is on ha_create_table() (5132 vs 5155).

Info: tests

mtrz main.stat_tables_par \
main.foreign_key \
innodb.foreign_key \
innodb.innodb-fk \
innodb.innodb-fk-warnings \
innodb_gis.point_basic \
main.query_cache_innodb \
main.information_schema_inno \
mroonga/storage.foreign_key_alter_add \
mroonga/storage.foreign_key_rename

[midenok]

Change operations

TODO

• [ ] Lock tables in correct order

Test cases

--source include/have_innodb.inc

set default_storage_engine= innodb;

--echo # Check rename column, lock tables
create or replace table t1 (id int primary key);
create or replace table t2 (id int primary key);
create or replace table t3 (id int primary key);
create or replace table ch1 (
  id int, id2 int,
  foreign key (id) references t1 (id),
  foreign key (id2) references t2 (id),
  foreign key (id) references t3 (id));
select * from t1, t2, t3;
--connect con1, localhost, root
lock tables t3 read;
--connection default
set @saved_lock_wait_timeout= @@lock_wait_timeout;
set lock_wait_timeout= 1;
alter table ch1 change id2 xid2 int;
--error ER_LOCK_WAIT_TIMEOUT
alter table ch1 change id xid int;
set lock_wait_timeout= @saved_lock_wait_timeout;
--connection con1
unlock tables;
--disconnect con1
--connection default
alter table ch1 change id xid int;
select * from ch1;
flush tables t1, t2, t3;
drop tables ch1, t2, t1, t3;

--echo # Check rename table
create or replace table t1 (id int primary key);
create or replace table t2 (id int primary key);
create or replace table t3 (id int primary key);
select * from t1, t2, t3;
create or replace table ch1 (
  id int, id2 int,
  foreign key (id) references t1 (id),
  foreign key (id2) references t2 (id),
  foreign key (id) references t3 (id));
select * from ch1;
rename table ch1 to ch2;
flush tables t1, t2, t3;
drop tables ch2, t2, t1, t3;

--echo # Check drop table
create or replace table t1 (id int primary key);
create or replace table ch1 (id int, foreign key (id) references t1 (id));
select * from t1;
select * from ch1;
drop tables ch1, t1;

RENAME TABLE

#0  ha_innobase::rename_table (this=0x7fff84016280, from=0x7ffff47cad50 "./test/t1", to=0x7ffff47cab40 "./test/#sql2-71f3-9") at /home/midenok/src/mariadb/trunk/src/storage/innobase/handler/ha_innodb.cc:13831
#1  0x0000000000bbfb0f in handler::ha_rename_table (this=0x7fff84016280, from=0x7ffff47cad50 "./test/t1", to=0x7ffff47cab40 "./test/#sql2-71f3-9") at /home/midenok/src/mariadb/trunk/src/sql/handler.cc:4688
#2  0x000000000093d150 in mysql_rename_table (base=0x28b05f8, old_db=0x7ffff47cd178, old_name=0x7ffff47cd188, new_db=0x7ffff47cd178, new_name=0x7ffff47cb410, flags=2) at /home/midenok/src/mariadb/trunk/src/sql/sql_table.cc:5533
#3  0x0000000000945ffc in mysql_alter_table (thd=0x7fff84000cf8, new_db=0x7fff840054b8, new_name=0x7fff840058c0, create_info=0x7ffff47ce4f0, table_list=0x7fff84014148, alter_info=0x7ffff47ce438, order_num=0, order=0x0, ignore=true) at /home/midenok/src/mariadb/trunk/src/sql/sql_table.cc:10263
#4  0x00000000009f6566 in Sql_cmd_alter_table::execute (this=0x7fff84014a18, thd=0x7fff84000cf8) at /home/midenok/src/mariadb/trunk/src/sql/sql_alter.cc:503
#5  0x000000000084702a in mysql_execute_command (thd=0x7fff84000cf8) at /home/midenok/src/mariadb/trunk/src/sql/sql_parse.cc:6099
#6  0x0000000000838390 in mysql_parse (thd=0x7fff84000cf8, rawbuf=0x7fff84014020 "alter ignore table t1 add foreign key (f3) references t1 (f1)", length=61, parser_state=0x7ffff47d1640, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/trunk/src/sql/sql_parse.cc:7909

DROP TABLE

create or replace table t1 (id int primary key);
create or replace table t2 (id int references t1);
--error ER_ROW_IS_REFERENCED_2
drop table t1;
drop tables t2, t1;

#5  0x0000000000c206e4 in handler::print_error (this=0x7f8fbc014e90, error=152, errflag=0) at /home/midenok/src/mariadb/10.5/src/sql/handler.cc:3846
#6  0x0000000000c1adc6 in ha_delete_table (thd=0x7f8fbc000d08, table_type=0x4634ea8, path=0x7f90200abee0 "./test/t1", db=0x7f90200abd40, alias=0x7f8fbc0145d0, generate_warning=true) at /home/midenok/src/mariadb/10.5/src/sql/handler.cc:2569
#7  0x0000000000972125 in mysql_rm_table_no_locks (thd=0x7f8fbc000d08, tables=0x7f8fbc0145a8, if_exists=false, drop_temporary=false, drop_view=false, drop_sequence=false, dont_log_query=false, dont_free_locks=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:2483
#8  0x0000000000970e72 in mysql_rm_table (thd=0x7f8fbc000d08, tables=0x7f8fbc0145a8, if_exists=false, drop_temporary=false, drop_sequence=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:2121
#9  0x0000000000877519 in mysql_execute_command (thd=0x7f8fbc000d08) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:4867
#10 0x000000000086c580 in mysql_parse (thd=0x7f8fbc000d08, rawbuf=0x7f8fbc0144f0 "drop table t1", length=13, parser_state=0x7f90200ae5c0, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:7998

2550      if (unlikely((error= file->ha_delete_table(path))))
2551      {
....
2559        if ((!intercept || generate_warning) && ! thd->is_error())
2560        {
....
2569          file->print_error(error, MYF(intercept ? ME_WARNING : 0));
2570        }

FK error comes from storage engine. Need to open table.

[midenok]

Bug: multiple locks on same table

Result

#5  0x00007f610b5ca535 in __GI_abort () at abort.c:79
#6  0x000000000160be19 in safe_mutex_lock (mp=0x7f60b0119498, my_flags=0, file=0x17258bc "/home/midenok/src/mariadb/10.5/src/sql/table_cache.cc", line=759) at /home/midenok/src/mariadb/10.5/src/mysys/thr_mutex.c:265
#7  0x0000000000b23cb4 in inline_mysql_mutex_lock (that=0x7f60b0119498, src_file=0x17258bc "/home/midenok/src/mariadb/10.5/src/sql/table_cache.cc", src_line=759) at /home/midenok/src/mariadb/10.5/src/include/mysql/psi/mysql_thread.h:695
#8  0x0000000000b25ff5 in tdc_lock_share (thd=0x7f60b0000d08, db=0x7f60b01300c8 "test", table_name=0x7f60b01300e0 "ch1") at /home/midenok/src/mariadb/10.5/src/sql/table_cache.cc:759
#9  0x00000000009c1ff2 in TABLE_SHARE::check_foreign_keys (this=0x7f60b0120820, thd=0x7f60b0000d08) at /home/midenok/src/mariadb/10.5/src/sql/table.cc:9370
#10 0x00000000009c1b7b in TABLE_SHARE::destroy (this=0x7f60b0120820) at /home/midenok/src/mariadb/10.5/src/sql/table.cc:454
#11 0x00000000009c2551 in free_table_share (share=0x7f60b0120820) at /home/midenok/src/mariadb/10.5/src/sql/table.cc:519
#12 0x0000000000b25e50 in tdc_delete_share_from_hash (element=0x7f60b01202c8) at /home/midenok/src/mariadb/10.5/src/sql/table_cache.cc:549
#13 0x0000000000b2735c in tdc_release_share (share=0x7f60b0120820) at /home/midenok/src/mariadb/10.5/src/sql/table_cache.cc:996
#14 0x0000000000b27ea6 in tdc_remove_table (thd=0x7f60b0000d08, remove_type=TDC_RT_REMOVE_ALL, db=0x7f60b011aec0 "test", table_name=0x7f60b011aed8 "t1", kill_delayed_threads=false) at /home/midenok/src/mariadb/10.5/src/sql/table_cache.cc:1204
#15 0x00000000009e2d63 in TABLE_SHARE::check_and_close_ref_tables (this=0x7f60b0123830, thd=0x7f60b0000d08, remove=false) at /home/midenok/src/mariadb/10.5/src/sql/table.cc:9324
#16 0x00000000009e2ed4 in check_and_close_ref_tables (thd=0x7f60b0000d08, t=0x7f60b00121b8, remove=false) at /home/midenok/src/mariadb/10.5/src/sql/table.cc:9353
#17 0x00000000008a9eca in do_rename (thd=0x7f60b0000d08, ren_table=0x7f60b00121b8, new_db=0x7f60b0012890, new_table_name=0x7f60b00128a0, new_table_alias=0x7f60b00128c0, skip_error=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_rename.cc:289
#18 0x00000000008a996e in rename_tables (thd=0x7f60b0000d08, table_list=0x7f60b00121b8, skip_error=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_rename.cc:381
#19 0x00000000008a95bf in mysql_rename_tables (thd=0x7f60b0000d08, table_list=0x7f60b00121b8, silent=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_rename.cc:154
#20 0x00000000008744cf in mysql_execute_command (thd=0x7f60b0000d08) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:4267
#21 0x000000000086c100 in mysql_parse (thd=0x7f60b0000d08, rawbuf=0x7f60b00120c0 "rename table ch1 to ch2", length=23, parser_state=0x7f61047e35c0, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:7998

Fix

Don't do check_foreign_keys() except for FLUSH TABLES.

[midenok]

Info: locks sorted

create or replace table m1(x int) engine myisam;
create or replace table m2(x int) engine myisam;
create or replace table m3(x int) engine myisam;
lock tables m2 read, m3 read, m1 read; unlock tables;

Check

select * from information_schema.metadata_lock_info;
+-----------+-----------------+---------------+---------------------+--------------+------------+
| THREAD_ID | LOCK_MODE       | LOCK_DURATION | LOCK_TYPE           | TABLE_SCHEMA | TABLE_NAME |
+-----------+-----------------+---------------+---------------------+--------------+------------+
|         8 | MDL_SHARED_READ | NULL          | Table metadata lock | test         | m1         |
|         8 | MDL_SHARED_READ | NULL          | Table metadata lock | test         | m3         |
|         8 | MDL_SHARED_READ | NULL          | Table metadata lock | test         | m2         |
+-----------+-----------------+---------------+---------------------+--------------+------------+

1-3. MDL lock acquired per each table

#0  MDL_context::acquire_lock (this=0x7fff80000e28, mdl_request=0x7fff800115a0, lock_wait_timeout=86400) at /home/midenok/src/mariadb/10.5/src/sql/mdl.cc:2241
#1  0x00000000007b332b in open_table_get_mdl_lock (thd=0x7fff80000d08, ot_ctx=0x7ffff47700e8, mdl_request=0x7fff800115a0, flags=0, mdl_ticket=0x7ffff476fd80) at /home/midenok/src/mariadb/10.5/src/sql/sql_base.cc:1646
#2  0x00000000007b1f7b in open_table (thd=0x7fff80000d08, table_list=0x7fff80011158, ot_ctx=0x7ffff47700e8) at /home/midenok/src/mariadb/10.5/src/sql/sql_base.cc:1906
#3  0x00000000007b76e5 in open_and_process_table (thd=0x7fff80000d08, tables=0x7fff80011158, counter=0x7ffff4770214, flags=0, prelocking_strategy=0x7ffff4770250, has_prelocking_list=false, ot_ctx=0x7ffff47700e8) at /home/midenok/src/mariadb/10.5/src/sql/sql_base.cc:3848
#4  0x00000000007b60bb in open_tables (thd=0x7fff80000d08, options=..., start=0x7ffff4770258, counter=0x7ffff4770214, flags=0, prelocking_strategy=0x7ffff4770250) at /home/midenok/src/mariadb/10.5/src/sql/sql_base.cc:4320
#5  0x0000000000887715 in open_tables (thd=0x7fff80000d08, tables=0x7ffff4770258, counter=0x7ffff4770214, flags=0, prelocking_strategy=0x7ffff4770250) at /home/midenok/src/mariadb/10.5/src/sql/sql_base.h:258
#6  0x000000000087ef11 in lock_tables_open_and_lock_tables (thd=0x7fff80000d08, tables=0x7fff80011158) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:2888
#7  0x0000000000877d8e in mysql_execute_command (thd=0x7fff80000d08) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:5027
#8  0x000000000086c580 in mysql_parse (thd=0x7fff80000d08, rawbuf=0x7fff80011070 "lock tables m2 read, m3 read, m1 read", length=37, parser_state=0x7ffff4772620, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:7998

Note: MDL are acquired one-by-one with open_table_get_mdl_lock() in order of listed tables:

(gdb) p mdl_request.key.name()
$26 = 0x7fff800115ce "m2"
(gdb) p mdl_request.key.name()
$27 = 0x7fff80011cbe "m3"
(gdb) p mdl_request.key.name()
$28 = 0x7fff800123ae "m1"

NB

This may be the subject for refactoring as locks should be acquired in sorted batch to avoid possibility of deadlocks?

4. lock_count increased

#0  get_lock_data (thd=0x7fff80000d08, table_ptr=0x7fff80014aa0, count=3, flags=1) at /home/midenok/src/mariadb/10.5/src/sql/lock.cc:751
#1  0x0000000000d971c7 in mysql_lock_tables (thd=0x7fff80000d08, tables=0x7fff80014aa0, count=3, flags=0) at /home/midenok/src/mariadb/10.5/src/sql/lock.cc:297
#2  0x00000000007b8d7d in lock_tables (thd=0x7fff80000d08, tables=0x7fff80013608, count=3, flags=0) at /home/midenok/src/mariadb/10.5/src/sql/sql_base.cc:5459
#3  0x000000000087ed3b in lock_tables_open_and_lock_tables (thd=0x7fff80000d08, tables=0x7fff80013608) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:2954
#4  0x000000000087790e in mysql_execute_command (thd=0x7fff80000d08) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:5027
#5  0x000000000086c100 in mysql_parse (thd=0x7fff80000d08, rawbuf=0x7fff80013520 "lock tables m2 read, m3 read, m1 read", length=37, parser_state=0x7ffff4772620, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:7998

747         if (t->s->tmp_table != NON_TRANSACTIONAL_TMP_TABLE &&
748             t->s->tmp_table != INTERNAL_TMP_TABLE &&
749             (!(flags & GET_LOCK_SKIP_SEQUENCES) || t->s->sequence == 0))
750         {
751           lock_count+= t->file->lock_count();

Note

InnoDB returns 0 with lock_count() so no locking on SQL.

5. Locks sorted

#0  sort_locks (data=0x7fff80059480, count=3) at /home/midenok/src/mariadb/10.5/src/mysys/thr_lock.c:1263
#1  0x0000000001609762 in thr_multi_lock (data=0x7fff80059480, count=3, owner=0x7fff80002890, lock_wait_timeout=86400) at /home/midenok/src/mariadb/10.5/src/mysys/thr_lock.c:1287
#2  0x0000000000d9819b in mysql_lock_tables (thd=0x7fff80000d08, sql_lock=0x7fff80059448, flags=0) at /home/midenok/src/mariadb/10.5/src/sql/lock.cc:348
#3  0x0000000000d97212 in mysql_lock_tables (thd=0x7fff80000d08, tables=0x7fff80014aa0, count=3, flags=0) at /home/midenok/src/mariadb/10.5/src/sql/lock.cc:300
#4  0x00000000007b8d7d in lock_tables (thd=0x7fff80000d08, tables=0x7fff80013608, count=3, flags=0) at /home/midenok/src/mariadb/10.5/src/sql/sql_base.cc:5459
#5  0x000000000087ed3b in lock_tables_open_and_lock_tables (thd=0x7fff80000d08, tables=0x7fff80013608) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:2954
#6  0x000000000087790e in mysql_execute_command (thd=0x7fff80000d08) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:5027
#7  0x000000000086c100 in mysql_parse (thd=0x7fff80000d08, rawbuf=0x7fff80013520 "lock tables m2 read, m3 read, m1 read", length=37, parser_state=0x7ffff4772620, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:7998

6. mutex acquired

#0  pthread_mutex_lock (mutex=0x7fd46c04dee8) at ./src/preload/overrides.c:48
#1  0x000000000160c14b in safe_mutex_lock (mp=0x7fd46c04dee8, my_flags=0, file=0x194f03c "/home/midenok/src/mariadb/10.5/src/mysys/thr_lock.c", line=763) at /home/midenok/src/mariadb/10.5/src/mysys/thr_mutex.c:241
#2  0x0000000001609133 in inline_mysql_mutex_lock (that=0x7fd46c04dee8, src_file=0x194f03c "/home/midenok/src/mariadb/10.5/src/mysys/thr_lock.c", src_line=763) at /home/midenok/src/mariadb/10.5/src/include/mysql/psi/mysql_thread.h:695
#3  0x0000000001609edd in thr_lock (data=0x7fd46c054f18, owner=0x7fd46c002890, lock_wait_timeout=31536000) at /home/midenok/src/mariadb/10.5/src/mysys/thr_lock.c:763
#4  0x0000000001609b5b in thr_multi_lock (data=0x7fd46c015b48, count=3, owner=0x7fd46c002890, lock_wait_timeout=31536000) at /home/midenok/src/mariadb/10.5/src/mysys/thr_lock.c:1295

Info: in case of write locks

lock tables m2 write, m3 write, m1 write;

Check

A bit more MDL locks (all from LOCK TABLES command):

select * from information_schema.metadata_lock_info;
+-----------+--------------------------+---------------+----------------------+--------------+------------+
| THREAD_ID | LOCK_MODE                | LOCK_DURATION | LOCK_TYPE            | TABLE_SCHEMA | TABLE_NAME |
+-----------+--------------------------+---------------+----------------------+--------------+------------+
|         8 | MDL_BACKUP_DDL           | NULL          | Backup lock          |              |            |
|         8 | MDL_BACKUP_DML           | NULL          | Backup lock          |              |            |
|         8 | MDL_SHARED_NO_READ_WRITE | NULL          | Table metadata lock  | test         | m1         |
|         8 | MDL_SHARED_NO_READ_WRITE | NULL          | Table metadata lock  | test         | m3         |
|         8 | MDL_INTENTION_EXCLUSIVE  | NULL          | Schema metadata lock | test         |            |
|         8 | MDL_SHARED_NO_READ_WRITE | NULL          | Table metadata lock  | test         | m2         |
+-----------+--------------------------+---------------+----------------------+--------------+------------+

[midenok]

MDL waits while table is locked by LOCK TABLES

#4  0x0000000000a460b1 in inline_mysql_cond_timedwait (that=0x7fff84000ed8, mutex=0x7fff84000e28, abstime=0x7ffff473a648, src_file=0x17011be "/home/midenok/src/mariadb/10.5/src/sql/mdl.cc", src_line=1148) at /home/midenok/src/mariadb/10.5/src/include/mysql/psi/mysql_thread.h:1215
#5  0x0000000000a45e64 in MDL_wait::timed_wait (this=0x7fff84000e28, owner=0x7fff84000dd8, abs_timeout=0x7ffff473a648, set_status_on_timeout=false, wait_state_name=0x1e6d030 <MDL_key::m_namespace_to_wait_state_name+48>) at /home/midenok/src/mariadb/10.5/src/sql/mdl.cc:1147
#6  0x0000000000a47a27 in MDL_context::acquire_lock (this=0x7fff84000e28, mdl_request=0x7ffff473a788, lock_wait_timeout=86400) at /home/midenok/src/mariadb/10.5/src/sql/mdl.cc:2325
#7  0x0000000000a48759 in MDL_context::upgrade_shared_lock (this=0x7fff84000e28, mdl_ticket=0x7fff840198e0, new_type=MDL_EXCLUSIVE, lock_wait_timeout=86400) at /home/midenok/src/mariadb/10.5/src/sql/mdl.cc:2523
#8  0x00000000007b0a58 in wait_while_table_is_used (thd=0x7fff84000d08, table=0x7fff84022ee8, function=HA_EXTRA_PREPARE_FOR_RENAME) at /home/midenok/src/mariadb/10.5/src/sql/sql_base.cc:1411
#9  0x0000000000981d80 in mysql_alter_table (thd=0x7fff84000d08, new_db=0x7fff840054c0, new_name=0x7fff840058c8, create_info=0x7ffff473e040, table_list=0x7fff84011180, alter_info=0x7ffff473df88, order_num=0, order=0x0, ignore=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:10336
#10 0x0000000000a41e36 in Sql_cmd_alter_table::execute (this=0x7fff84011948, thd=0x7fff84000d08) at /home/midenok/src/mariadb/10.5/src/sql/sql_alter.cc:516
#11 0x000000000087b338 in mysql_execute_command (thd=0x7fff84000d08) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:5951
#12 0x000000000086c100 in mysql_parse (thd=0x7fff84000d08, rawbuf=0x7fff840110a0 "alter table m2 add y int", length=24, parser_state=0x7ffff4740620, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:7998

frame 5

1147        wait_result= mysql_cond_timedwait(&m_COND_wait_status, &m_LOCK_wait_status,
1148                                          abs_timeout);

frame 6

2322      while (cmp_timespec(abs_shortwait, abs_timeout) <= 0)
2323      {
2324        /* abs_timeout is far away. Wait a short while and notify locks. */
2325        wait_status= m_wait.timed_wait(m_owner, &abs_shortwait, FALSE,
2326                                       mdl_request->key.get_wait_state_name());

m_wait is member of MDL_context. FYI: this is how we can get "process ID" (SHOW PROCESSLIST):

(gdb) p get_lock_owner(&mdl_request->key)
$19 = 9

frame 7

2497      MDL_request mdl_xlock_request;
2498      MDL_savepoint mdl_svp= mdl_savepoint();
2499      bool is_new_ticket;
2500      DBUG_ENTER("MDL_context::upgrade_shared_lock");
2501      DBUG_PRINT("enter",("old_type: %s  new_type: %s  lock_wait_timeout: %f",
2502                          mdl_ticket->get_type_name()->str,
2503                          mdl_ticket->get_type_name(new_type)->str,
2504                          lock_wait_timeout));
2505      DEBUG_SYNC(get_thd(), "mdl_upgrade_lock");
2506
2507      /*
2508        Do nothing if already upgraded. Used when we FLUSH TABLE under
2509        LOCK TABLES and a table is listed twice in LOCK TABLES list.
2510
2511        In BACKUP namespace upgrade must always happen. Even though
2512        MDL_BACKUP_START is not stronger than MDL_BACKUP_FLUSH from
2513        has_stronger_or_equal_type(), the latter effectively blocks
2514        new MDL_BACKUP_DML while the former doesn't.
2515      */
2516      if (mdl_ticket->has_stronger_or_equal_type(new_type) &&
2517          mdl_ticket->get_key()->mdl_namespace() != MDL_key::BACKUP)
2518        DBUG_RETURN(FALSE);
2519
2520      mdl_xlock_request.init(&mdl_ticket->m_lock->key, new_type,
2521                             MDL_TRANSACTION);

frame 8

1411      if (thd->mdl_context.upgrade_shared_lock(
1412                 table->mdl_ticket, MDL_EXCLUSIVE,
1413                 thd->variables.lock_wait_timeout))
1414        DBUG_RETURN(TRUE);

mdl_context is member of THD.

[midenok]

Bug: wrong usage of mutex 'LOCK_unused_shares'

safe_mutex: Found wrong usage of mutex 'LOCK_unused_shares' and 'element->LOCK_table_share'

1. LOCK_unused_shares added to LOCK_table_share->locked_mutex

#0  0x00000000015cf589 in my_hash_insert (info=0x7fa61802a058, record=0x7fa618028958 "Lir\001") at /home/midenok/src/mariadb/10.5/src/mysys/hash.c:522
#1  0x000000000160d00d in add_to_locked_mutex (locked_mutex=0x7fa618028958, current_mutex=0x7fa6180350e8) at /home/midenok/src/mariadb/10.5/src/mysys/thr_mutex.c:739
#2  0x000000000160d153 in add_used_to_locked_mutex (used_mutex=0x7fa6180350e8, locked_mutex=0x7fa618028958) at /home/midenok/src/mariadb/10.5/src/mysys/thr_mutex.c:722
#3  0x000000000160c955 in safe_mutex_lock (mp=0x2079c48 <LOCK_unused_shares>, my_flags=0, file=0x172694c "/home/midenok/src/mariadb/10.5/src/sql/table_cache.cc", line=1105) at /home/midenok/src/mariadb/10.5/src/mysys/thr_mutex.c:387
#4  0x0000000000b24564 in inline_mysql_mutex_lock (that=0x2079c48 <LOCK_unused_shares>, src_file=0x172694c "/home/midenok/src/mariadb/10.5/src/sql/table_cache.cc", src_line=1105) at /home/midenok/src/mariadb/10.5/src/include/mysql/psi/mysql_thread.h:695
#5  0x0000000000b27f0b in tdc_remove_table (thd=0x7fa618000d08, remove_type=TDC_RT_REMOVE_ALL, db=0x7fa618047799 "test", table_name=0x7fa61804779e "t1", kill_delayed_threads=false) at /home/midenok/src/mariadb/10.5/src/sql/table_cache.cc:1105
#6  0x00000000009e35f9 in TABLE_SHARE::check_and_close_ref_tables (this=0x7fa618033de0, thd=0x7fa618000d08, remove=false) at /home/midenok/src/mariadb/10.5/src/sql/table.cc:9356
#7  0x00000000009e3704 in check_and_close_ref_tables (thd=0x7fa618000d08, t=0x7fa6180145e8, remove=false) at /home/midenok/src/mariadb/10.5/src/sql/table.cc:9374
#8  0x00000000008aa34a in do_rename (thd=0x7fa618000d08, ren_table=0x7fa6180145e8, new_db=0x7fa618014cc0, new_table_name=0x7fa618014cd0, new_table_alias=0x7fa618014cf0, skip_error=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_rename.cc:289
#9  0x00000000008a9dee in rename_tables (thd=0x7fa618000d08, table_list=0x7fa6180145e8, skip_error=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_rename.cc:381
#10 0x00000000008a9a3f in mysql_rename_tables (thd=0x7fa618000d08, table_list=0x7fa6180145e8, silent=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_rename.cc:154
#11 0x000000000087494f in mysql_execute_command (thd=0x7fa618000d08) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:4267
#12 0x000000000086c580 in mysql_parse (thd=0x7fa618000d08, rawbuf=0x7fa6180144f0 "rename table t2 to t3", length=21, parser_state=0x7fa67c0a95c0, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:7998

frame 7

9363    bool check_and_close_ref_tables(THD *thd, TABLE_LIST *t, bool remove)
9364    {
9365      TDC_element *el= tdc_lock_share(thd, t->db.str, t->table_name.str);
9366      if (el == MY_ERRPTR)
9367      {
9368        my_error(ER_OUT_OF_RESOURCES, MYF(0));
9369        return true;
9370      }
9371      if (el)
9372      {
9373        if (el->share->foreign_keys &&
9374            el->share->check_and_close_ref_tables(thd, remove))

(rr) p el->share->table_name
$8 = {
  str = 0x7fa618034375 "t2",
  length = 2
}

2. warning printed

#0  print_deadlock_warning (new_mutex=0x7f7194035068, parent_mutex=0x2079c48 <LOCK_unused_shares>) at /home/midenok/src/mariadb/10.5/src/mysys/thr_mutex.c:810
#1  0x000000000160c9b4 in safe_mutex_lock (mp=0x7f7194035068, my_flags=0, file=0x172694c "/home/midenok/src/mariadb/10.5/src/sql/table_cache.cc", line=759) at /home/midenok/src/mariadb/10.5/src/mysys/thr_mutex.c:360
#2  0x0000000000b24564 in inline_mysql_mutex_lock (that=0x7f7194035068, src_file=0x172694c "/home/midenok/src/mariadb/10.5/src/sql/table_cache.cc", src_line=759) at /home/midenok/src/mariadb/10.5/src/include/mysql/psi/mysql_thread.h:695
#3  0x0000000000b268a5 in tdc_lock_share (thd=0x7f7194000d08, db=0x7f7194014c98 "test", table_name=0x7f7194014580 "t2") at /home/midenok/src/mariadb/10.5/src/sql/table_cache.cc:759
#4  0x0000000000b28087 in tdc_remove_table (thd=0x7f7194000d08, remove_type=TDC_RT_REMOVE_ALL, db=0x7f7194014c98 "test", table_name=0x7f7194014580 "t2", kill_delayed_threads=false) at /home/midenok/src/mariadb/10.5/src/sql/table_cache.cc:1106
#5  0x00000000008aa3ab in do_rename (thd=0x7f7194000d08, ren_table=0x7f71940145e8, new_db=0x7f7194014cc0, new_table_name=0x7f7194014cd0, new_table_alias=0x7f7194014cf0, skip_error=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_rename.cc:291
#6  0x00000000008a9dee in rename_tables (thd=0x7f7194000d08, table_list=0x7f71940145e8, skip_error=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_rename.cc:381
#7  0x00000000008a9a3f in mysql_rename_tables (thd=0x7f7194000d08, table_list=0x7f71940145e8, silent=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_rename.cc:154
#8  0x000000000087494f in mysql_execute_command (thd=0x7f7194000d08) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:4267
#9  0x000000000086c580 in mysql_parse (thd=0x7f7194000d08, rawbuf=0x7f71940144f0 "rename table t2 to t3", length=21, parser_state=0x7f71fc0635c0, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:7998

frame 4

1105      mysql_mutex_lock(&LOCK_unused_shares);
1106      if (!(element= tdc_lock_share(thd, db, table_name)))

Cause

p.1: in frame 7 share of t2 was already locked then in frame 5 LOCK_unused_shares was locked. p.2: order of locking is different, first LOCK_unused_shares is locked and then share is locked.

[midenok]

Open tables in DROP

--- a/sql/sql_table.cc
+++ b/sql/sql_table.cc
@@ -2058,8 +2058,7 @@ bool mysql_rm_table(THD *thd,TABLE_LIST *tables, bool if_exists,
           }
         }
       }
-      if (lock_table_names(thd, tables, NULL,
-                           thd->variables.lock_wait_timeout, 0))
+      if (open_and_lock_tables(thd, tables, false, 0))
         DBUG_RETURN(true);
     }
     else
@@ -2455,22 +2454,15 @@ int mysql_rm_table_no_locks(THD *thd, TABLE_LIST *tables, bool if_exists,
       if (table_type && table_type != view_pseudo_hton)
         ha_lock_engine(thd, table_type);

-      if (thd->locked_tables_mode == LTM_LOCK_TABLES ||
-          thd->locked_tables_mode == LTM_PRELOCKED_UNDER_LOCK_TABLES)
+      if (wait_while_table_is_used(thd, table->table, HA_EXTRA_NOT_USED))
       {
-        if (wait_while_table_is_used(thd, table->table, HA_EXTRA_NOT_USED))
-        {
-          error= -1;
-          goto err;
-        }
-        /* the following internally does TDC_RT_REMOVE_ALL */
-        close_all_tables_for_name(thd, table->table->s,
-                                  HA_EXTRA_PREPARE_FOR_DROP, NULL);
-        table->table= 0;
+        error= -1;
+        goto err;
       }
-      else
-        tdc_remove_table(thd, TDC_RT_REMOVE_ALL, table->db.str, table->table_name.str,
-                         false);
+      /* the following internally does TDC_RT_REMOVE_ALL */
+      close_all_tables_for_name(thd, table->table->s,
+                                HA_EXTRA_PREPARE_FOR_DROP, NULL);
+      table->table= 0;

       /* Check that we have an exclusive lock on the table to be dropped. */
       DBUG_ASSERT(thd->mdl_context.is_lock_owner(MDL_key::TABLE, table->db.str,

Causes this error:

ERROR: 1146  Table 'mysql.ndb_binlog_index' doesn't exist

Cause

Non-existent table fails to open:

drop table if exists mysql.ndb_binlog_index;

[midenok]

Bug: gcol.innodb_virtual_fk fails

Reproduce

--source include/have_innodb.inc

set default_storage_engine= innodb;

create table t1(fld1 int not null primary key);
create table t2(fld1 int not null,
                fld2 int as (fld1) virtual);
insert into t1 values(1);
insert into t2 values(1, default);
set foreign_key_checks = 0;
alter table t2 add index(fld2), add foreign key (fld1) references t1(fld1)
                on update cascade, algorithm=inplace;
set foreign_key_checks = 1;
update t1 set fld1= 2;

drop table t2, t1;

[midenok]

Bad

1. t1 opened

#0  0x00000000009cfa1b in open_table_from_share (thd=0x7ff3ec000d08, share=0x7ff3ec02bfb0, alias=0x7ff3ec014618, db_stat=33, prgflag=8, ha_open_flags=16, outparam=0x7ff3ec030fb8, is_create_table=false, partitions_to_open=0x0) at /home/midenok/src/mariadb/10.5/src/sql/table.cc:3744
#1  0x00000000007b2964 in open_table (thd=0x7ff3ec000d08, table_list=0x7ff3ec0145d0, ot_ctx=0x7ff4480a5ac8) at /home/midenok/src/mariadb/10.5/src/sql/sql_base.cc:2081
#2  0x00000000007b7775 in open_and_process_table (thd=0x7ff3ec000d08, tables=0x7ff3ec0145d0, counter=0x7ff4480a5bcc, flags=0, prelocking_strategy=0x7ff4480a5c40, has_prelocking_list=false, ot_ctx=0x7ff4480a5ac8) at /home/midenok/src/mariadb/10.5/src/sql/sql_base.cc:3848
#3  0x00000000007b614b in open_tables (thd=0x7ff3ec000d08, options=..., start=0x7ff4480a5be0, counter=0x7ff4480a5bcc, flags=0, prelocking_strategy=0x7ff4480a5c40) at /home/midenok/src/mariadb/10.5/src/sql/sql_base.cc:4320
#4  0x00000000007ba855 in open_and_lock_tables (thd=0x7ff3ec000d08, options=..., tables=0x7ff3ec0145d0, derived=true, flags=0, prelocking_strategy=0x7ff4480a5c40) at /home/midenok/src/mariadb/10.5/src/sql/sql_base.cc:5196
#5  0x00000000007652bc in open_and_lock_tables (thd=0x7ff3ec000d08, tables=0x7ff3ec0145d0, derived=true, flags=0) at /home/midenok/src/mariadb/10.5/src/sql/sql_base.h:504
#6  0x00000000008199f1 in mysql_insert (thd=0x7ff3ec000d08, table_list=0x7ff3ec0145d0, fields=..., values_list=..., update_fields=..., update_values=..., duplic=DUP_ERROR, ignore=false, result=0x0) at /home/midenok/src/mariadb/10.5/src/sql/sql_insert.cc:754
#7  0x000000000087592f in mysql_execute_command (thd=0x7ff3ec000d08) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:4499
#8  0x000000000086c650 in mysql_parse (thd=0x7ff3ec000d08, rawbuf=0x7ff3ec0144f0 "insert into t1 values(1)", length=24, parser_state=0x7ff4480a85c0, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:7998

2. t2 closed

#0  closefrm (table=0x7ff4480a2ff0) at /home/midenok/src/mariadb/10.5/src/sql/table.cc:4186
#1  0x0000000000989670 in cleanup_table_after_inplace_alter_keep_files (table=0x7ff4480a2ff0) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:9325
#2  0x0000000000981a1b in mysql_alter_table (thd=0x7ff3ec000d08, new_db=0x7ff3ec0054c0, new_name=0x7ff3ec0058c8, create_info=0x7ff4480a5fe0, table_list=0x7ff3ec014678, alter_info=0x7ff4480a5f28, order_num=0, order=0x0, ignore=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:10144
#3  0x0000000000a43a56 in Sql_cmd_alter_table::execute (this=0x7ff3ec015018, thd=0x7ff3ec000d08) at /home/midenok/src/mariadb/10.5/src/sql/sql_alter.cc:516
#4  0x000000000087b888 in mysql_execute_command (thd=0x7ff3ec000d08) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:5951
#5  0x000000000086c650 in mysql_parse (thd=0x7ff3ec000d08, rawbuf=0x7ff3ec0144f0 "alter table t2 add index(fld2), add foreign key (fld1) references t1(fld1)\non update cascade, algorithm=inplace", length=111, parser_state=0x7ff4480a85c0, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:7998

3. t2 not found

#0  innodb_find_table_for_vc (thd=0x7ff3ec000d08, table=0x7ff3ec02c778) at /home/midenok/src/mariadb/10.5/src/storage/innobase/handler/ha_innodb.cc:20715
#1  0x000000000105f7df in innobase_allocate_row_for_vcol (thd=0x7ff3ec000d08, index=0x7ff3ec02f058, heap=@0x7ff4480a3160: 0x0, table=0x7ff4480a3158, record=0x7ff4480a3148, storage=0x7ff4480a3150) at /home/midenok/src/mariadb/10.5/src/storage/innobase/handler/ha_innodb.cc:20853
#2  0x000000000120a56a in row_ins_foreign_fill_virtual (cascade=0x7ff3ec0295b8, rec=0x7ff451aac07d "", index=0x7ff3ec02f058, node=0x7ff3ec033020, foreign=0x7ff3ec0873c8, err=0x7ff4480a39cc) at /home/midenok/src/mariadb/10.5/src/storage/innobase/row/row0ins.cc:968
#3  0x00000000011ffd0e in row_ins_foreign_check_on_constraint (thr=0x7ff3ec0332f8, foreign=0x7ff3ec0873c8, pcur=0x7ff4480a4540, entry=0x7ff3ec0280a0, mtr=0x7ff4480a4040) at /home/midenok/src/mariadb/10.5/src/storage/innobase/row/row0ins.cc:1364
#4  0x00000000011fd8be in row_ins_check_foreign_constraint (check_ref=0, foreign=0x7ff3ec0873c8, table=0x7ff3ec026eb8, entry=0x7ff3ec0280a0, thr=0x7ff3ec0332f8) at /home/midenok/src/mariadb/10.5/src/storage/innobase/row/row0ins.cc:1846
#5  0x00000000012b1874 in row_upd_check_references_constraints (node=0x7ff3ec033020, pcur=0x7ff3ec02ac60, table=0x7ff3ec026eb8, index=0x7ff3ec02ae78, offsets=0x7ff3ec086fc8, thr=0x7ff3ec0332f8, mtr=0x7ff4480a4da0) at /home/midenok/src/mariadb/10.5/src/storage/innobase/row/row0upd.cc:296
#6  0x00000000012b05c4 in row_upd_clust_rec_by_insert (node=0x7ff3ec033020, index=0x7ff3ec02ae78, thr=0x7ff3ec0332f8, referenced=1, mtr=0x7ff4480a4da0) at /home/midenok/src/mariadb/10.5/src/storage/innobase/row/row0upd.cc:2770
#7  0x00000000012ae877 in row_upd_clust_step (node=0x7ff3ec033020, thr=0x7ff3ec0332f8) at /home/midenok/src/mariadb/10.5/src/storage/innobase/row/row0upd.cc:3221
#8  0x00000000012aa952 in row_upd (node=0x7ff3ec033020, thr=0x7ff3ec0332f8) at /home/midenok/src/mariadb/10.5/src/storage/innobase/row/row0upd.cc:3300
#9  0x00000000012aa465 in row_upd_step (thr=0x7ff3ec0332f8) at /home/midenok/src/mariadb/10.5/src/storage/innobase/row/row0upd.cc:3444
#10 0x0000000001238621 in row_update_for_mysql (prebuilt=0x7ff3ec032578) at /home/midenok/src/mariadb/10.5/src/storage/innobase/row/row0mysql.cc:1892
#11 0x000000000104872e in ha_innobase::update_row (this=0x7ff3ec031dc0, old_row=0x7ff3ec02b9e0 "\377\001", new_row=0x7ff3ec02b9d8 "\377\002") at /home/midenok/src/mariadb/10.5/src/storage/innobase/handler/ha_innodb.cc:8775
#12 0x0000000000c29f9a in handler::ha_update_row (this=0x7ff3ec031dc0, old_data=0x7ff3ec02b9e0 "\377\001", new_data=0x7ff3ec02b9d8 "\377\002") at /home/midenok/src/mariadb/10.5/src/sql/handler.cc:6684
#13 0x00000000009af9a1 in mysql_update (thd=0x7ff3ec000d08, table_list=0x7ff3ec0145c8, fields=..., values=..., conds=0x0, order_num=0, order=0x0, limit=18446744073709551615, ignore=false, found_return=0x7ff4480a7118, updated_return=0x7ff4480a7110) at /home/midenok/src/mariadb/10.5/src/sql/sql_update.cc:1046
#14 0x0000000000875078 in mysql_execute_command (thd=0x7ff3ec000d08) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:4347
#15 0x000000000086c650 in mysql_parse (thd=0x7ff3ec000d08, rawbuf=0x7ff3ec0144f0 "update t1 set fld1= 2", length=21, parser_state=0x7ff4480a85c0, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:7998

Good

1. t2 closed

#0  closefrm (table=0x7f9ebc0f3ff0) at /home/midenok/src/mariadb/10.5b/src/sql/table.cc:4182
#1  0x00000000009862b0 in cleanup_table_after_inplace_alter_keep_files (table=0x7f9ebc0f3ff0) at /home/midenok/src/mariadb/10.5b/src/sql/sql_table.cc:9282
#2  0x000000000097e6fd in mysql_alter_table (thd=0x7f9e54000d08, new_db=0x7f9e540054c0, new_name=0x7f9e540058c8, create_info=0x7f9ebc0f6fe0, table_list=0x7f9e54014678, alter_info=0x7f9ebc0f6f28, order_num=0, order=0x0, ignore=false) at /home/midenok/src/mariadb/10.5b/src/sql/sql_table.cc:10101
#3  0x0000000000a3e246 in Sql_cmd_alter_table::execute (this=0x7f9e54015008, thd=0x7f9e54000d08) at /home/midenok/src/mariadb/10.5b/src/sql/sql_alter.cc:516
#4  0x0000000000878b38 in mysql_execute_command (thd=0x7f9e54000d08) at /home/midenok/src/mariadb/10.5b/src/sql/sql_parse.cc:5951
#5  0x0000000000869900 in mysql_parse (thd=0x7f9e54000d08, rawbuf=0x7f9e540144f0 "alter table t2 add index(fld2), add foreign key (fld1) references t1(fld1)\non update cascade, algorithm=inplace", length=111, parser_state=0x7f9ebc0f95c0, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/10.5b/src/sql/sql_parse.cc:7998

2. t2 added to list

#0  0x00000000007638e3 in TABLE_LIST::init_one_table_for_prelocking (this=0x7f9e54015160, db_arg=0x7f9e54015050, table_name_arg=0x7f9e54015068, alias_arg=0x0, lock_type_arg=TL_WRITE_ALLOW_WRITE, prelocking_type=TABLE_LIST::PRELOCK_FK, belong_to_view_arg=0x0, trg_event_map_arg=2 '\002', last_ptr=0x7f9e54004b88) at /home/midenok/src/mariadb/10.5b/src/sql/table.h:2004
#1  0x00000000007b6478 in DML_prelocking_strategy::handle_table (this=0x7f9ebc0f6768, thd=0x7f9e54000d08, prelocking_ctx=0x7f9e54004b78, table_list=0x7f9e540145c8, need_prelocking=0x7f9ebc0f6366) at /home/midenok/src/mariadb/10.5b/src/sql/sql_base.cc:4716
#2  0x00000000007b34d1 in extend_table_list (thd=0x7f9e54000d08, tables=0x7f9e540145c8, prelocking_strategy=0x7f9ebc0f6768, has_prelocking_list=false) at /home/midenok/src/mariadb/10.5b/src/sql/sql_base.cc:3587
#3  0x00000000007b53fc in open_and_process_table (thd=0x7f9e54000d08, tables=0x7f9e540145c8, counter=0x7f9ebc0f71a4, flags=0, prelocking_strategy=0x7f9ebc0f6768, has_prelocking_list=false, ot_ctx=0x7f9ebc0f66a8) at /home/midenok/src/mariadb/10.5b/src/sql/sql_base.cc:3902
#4  0x00000000007b3c2b in open_tables (thd=0x7f9e54000d08, options=..., start=0x7f9ebc0f71e8, counter=0x7f9ebc0f71a4, flags=0, prelocking_strategy=0x7f9ebc0f6768) at /home/midenok/src/mariadb/10.5b/src/sql/sql_base.cc:4320
#5  0x00000000007a70a6 in open_tables (thd=0x7f9e54000d08, tables=0x7f9ebc0f71e8, counter=0x7f9ebc0f71a4, flags=0) at /home/midenok/src/mariadb/10.5b/src/sql/sql_base.h:474
#6  0x00000000009a94bb in mysql_update (thd=0x7f9e54000d08, table_list=0x7f9e540145c8, fields=..., values=..., conds=0x0, order_num=0, order=0x0, limit=18446744073709551615, ignore=false, found_return=0x7f9ebc0f8118, updated_return=0x7f9ebc0f8110) at /home/midenok/src/mariadb/10.5b/src/sql/sql_update.cc:399
#7  0x0000000000872328 in mysql_execute_command (thd=0x7f9e54000d08) at /home/midenok/src/mariadb/10.5b/src/sql/sql_parse.cc:4347
#8  0x0000000000869900 in mysql_parse (thd=0x7f9e54000d08, rawbuf=0x7f9e540144f0 "update t1 set fld1= 2", length=21, parser_state=0x7f9ebc0f95c0, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/10.5b/src/sql/sql_parse.cc:7998

3. t2 opened

#0  open_table (thd=0x7f9e54000d08, table_list=0x7f9e54015160, ot_ctx=0x7f9ebc0f66a8) at /home/midenok/src/mariadb/10.5b/src/sql/sql_base.cc:1765
#1  0x00000000007b505b in open_and_process_table (thd=0x7f9e54000d08, tables=0x7f9e54015160, counter=0x7f9ebc0f71a4, flags=0, prelocking_strategy=0x7f9ebc0f6768, has_prelocking_list=false, ot_ctx=0x7f9ebc0f66a8) at /home/midenok/src/mariadb/10.5b/src/sql/sql_base.cc:3811
#2  0x00000000007b3c2b in open_tables (thd=0x7f9e54000d08, options=..., start=0x7f9ebc0f71e8, counter=0x7f9ebc0f71a4, flags=0, prelocking_strategy=0x7f9ebc0f6768) at /home/midenok/src/mariadb/10.5b/src/sql/sql_base.cc:4320
#3  0x00000000007a70a6 in open_tables (thd=0x7f9e54000d08, tables=0x7f9ebc0f71e8, counter=0x7f9ebc0f71a4, flags=0) at /home/midenok/src/mariadb/10.5b/src/sql/sql_base.h:474
#4  0x00000000009a94bb in mysql_update (thd=0x7f9e54000d08, table_list=0x7f9e540145c8, fields=..., values=..., conds=0x0, order_num=0, order=0x0, limit=18446744073709551615, ignore=false, found_return=0x7f9ebc0f8118, updated_return=0x7f9ebc0f8110) at /home/midenok/src/mariadb/10.5b/src/sql/sql_update.cc:399
#5  0x0000000000872328 in mysql_execute_command (thd=0x7f9e54000d08) at /home/midenok/src/mariadb/10.5b/src/sql/sql_parse.cc:4347
#6  0x0000000000869900 in mysql_parse (thd=0x7f9e54000d08, rawbuf=0x7f9e540144f0 "update t1 set fld1= 2", length=21, parser_state=0x7f9ebc0f95c0, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/10.5b/src/sql/sql_parse.cc:7998

(rr) p table_list->table_name
$6 = {
  str = 0x7f9e54015078 "t2",
  length = 2
}

frame 2

4679        if (table->file->referenced_by_foreign_key())
4680        {
4681          List <FOREIGN_KEY_INFO> fk_list;
4682          List_iterator<FOREIGN_KEY_INFO> fk_list_it(fk_list);
4683          FOREIGN_KEY_INFO *fk;
....
4688          table->file->get_parent_foreign_key_list(thd, &fk_list);
....
4698          while ((fk= fk_list_it++))
4699          {
....
4714
4715            TABLE_LIST *tl= (TABLE_LIST *) thd->alloc(sizeof(TABLE_LIST));
4716            tl->init_one_table_for_prelocking(fk->foreign_db,
4717                                              fk->foreign_table,
4718                                              NULL, lock_type,
4719                                              TABLE_LIST::PRELOCK_FK,
4720                                              table_list->belong_to_view, op,
4721                                              &prelocking_ctx->query_tables_last);
4722          }

Cause

t1 doesn't have referenced_keys because it was opened before ALTER and was not closed on ALTER.

[midenok]

Converge Foreign_key and supplemental generated Key together

mysql_prepare_create_table() does data validation and such utilities as automatic name generation. But it does that only for indexes and ignores Foreign_key objects. Now as Foreign_key data needs to be stored in FRM files as well this processing must be done for them like for any other Key objects.

Replace Key::FOREIGN_KEY type with Key::foreign flag of type Key::MULTIPLE and Key::generated set to true. Construct one object with Key::foreign == true instead of two objects of type Key::FOREIGN_KEY and Key::MULTIPLE.

Reproduce

create or replace table parent (a int unique, b int unique);
create or replace table child (
    a int not null,
    b int unique,
    primary key (a,b),
    foreign key (a) references parent (a) match full,
    foreign key (a) references parent (a) match partial);

1. Primary index created

#0  row_create_index_for_mysql (index=0x7fff7c092378, trx=0x7ffff488c360, field_lengths=0x7fff7c0235e8) at /home/midenok/src/mariadb/10.4/src/storage/innobase/row/row0mysql.cc:2553
#1  0x0000000000ead47f in create_index (trx=0x7ffff488c360, form=0x7ffff47cc118, table=0x7fff7c082a38, key_num=0) at /home/midenok/src/mariadb/10.4/src/storage/innobase/handler/ha_innodb.cc:11308
#2  0x0000000000e8d0da in create_table_info_t::create_table (this=0x7ffff47cb390, create_fk=true) at /home/midenok/src/mariadb/10.4/src/storage/innobase/handler/ha_innodb.cc:12378
#3  0x0000000000eae5ac in ha_innobase::create (this=0x7fff7c092e50, name=0x7ffff47cde50 "./test/child", form=0x7ffff47cc118, create_info=0x7ffff47ce468, file_per_table=true, trx=0x7ffff488c360) at /home/midenok/src/mariadb/10.4/src/storage/innobase/handler/ha_innodb.cc:12903
#4  0x0000000000e8e9af in ha_innobase::create (this=0x7fff7c092e50, name=0x7ffff47cde50 "./test/child", form=0x7ffff47cc118, create_info=0x7ffff47ce468) at /home/midenok/src/mariadb/10.4/src/storage/innobase/handler/ha_innodb.cc:12956
#5  0x0000000000be95c1 in handler::ha_create (this=0x7fff7c092e50, name=0x7ffff47cde50 "./test/child", form=0x7ffff47cc118, info_arg=0x7ffff47ce468) at /home/midenok/src/mariadb/10.4/src/sql/handler.cc:4728
#6  0x0000000000beb187 in ha_create_table (thd=0x7fff7c000ce8, path=0x7ffff47cde50 "./test/child", db=0x7fff7c014930 "test", table_name=0x7fff7c014238 "child", create_info=0x7ffff47ce468, frm=0x7ffff47cde40) at /home/midenok/src/mariadb/10.4/src/sql/handler.cc:5192
#7  0x000000000094ff0d in create_table_impl (thd=0x7fff7c000ce8, orig_db=..., orig_table_name=..., db=..., table_name=..., path=0x7ffff47cde50 "./test/child", options=..., create_info=0x7ffff47ce468, alter_info=0x7ffff47ce3b0, create_table_mode=0, is_trans=0x7ffff47ce197, key_info=0x7ffff47ce060, key_count=0x7ffff47ce05c, frm=0x7ffff47cde40) at /home/midenok/src/mariadb/10.4/src/sql/sql_table.cc:5042
#8  0x000000000094f0b8 in mysql_create_table_no_lock (thd=0x7fff7c000ce8, db=0x7fff7c014288, table_name=0x7fff7c014298, create_info=0x7ffff47ce468, alter_info=0x7ffff47ce3b0, is_trans=0x7ffff47ce197, create_table_mode=0, table_list=0x7fff7c014270) at /home/midenok/src/mariadb/10.4/src/sql/sql_table.cc:5126
#9  0x00000000009504d0 in mysql_create_table (thd=0x7fff7c000ce8, create_table=0x7fff7c014270, create_info=0x7ffff47ce468, alter_info=0x7ffff47ce3b0) at /home/midenok/src/mariadb/10.4/src/sql/sql_table.cc:5218
#10 0x00000000009657ef in Sql_cmd_create_table_like::execute (this=0x7fff7c014210, thd=0x7fff7c000ce8) at /home/midenok/src/mariadb/10.4/src/sql/sql_table.cc:11406
#11 0x0000000000854262 in mysql_execute_command (thd=0x7fff7c000ce8) at /home/midenok/src/mariadb/10.4/src/sql/sql_parse.cc:6102
#12 0x0000000000844790 in mysql_parse (thd=0x7fff7c000ce8, rawbuf=0x7fff7c014010 "create or replace table child (     a int not null,     b int not null,     primary key (a,b),     foreign key (a) references parent (a) match full,     foreign key (a) references parent (a) match partial)", length=205, parser_state=0x7ffff47d1640, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/10.4/src/sql/sql_parse.cc:7901

$5 = { m_name = 0x7fff7c095318 "PRIMARY" }

2. Secondary index created

#0  row_create_index_for_mysql (index=0x7fff7c0786f8, trx=0x7ffff488c360, field_lengths=0x7fff7c0222e8) at /home/midenok/src/mariadb/10.4/src/storage/innobase/row/row0mysql.cc:2553
#1  0x0000000000ead47f in create_index (trx=0x7ffff488c360, form=0x7ffff47cc118, table=0x7fff7c082a38, key_num=1) at /home/midenok/src/mariadb/10.4/src/storage/innobase/handler/ha_innodb.cc:11308
#2  0x0000000000e8d330 in create_table_info_t::create_table (this=0x7ffff47cb390, create_fk=true) at /home/midenok/src/mariadb/10.4/src/storage/innobase/handler/ha_innodb.cc:12434
#3  0x0000000000eae5ac in ha_innobase::create (this=0x7fff7c093400, name=0x7ffff47cde50 "./test/child", form=0x7ffff47cc118, create_info=0x7ffff47ce468, file_per_table=true, trx=0x7ffff488c360) at /home/midenok/src/mariadb/10.4/src/storage/innobase/handler/ha_innodb.cc:12903
#4  0x0000000000e8e9af in ha_innobase::create (this=0x7fff7c093400, name=0x7ffff47cde50 "./test/child", form=0x7ffff47cc118, create_info=0x7ffff47ce468) at /home/midenok/src/mariadb/10.4/src/storage/innobase/handler/ha_innodb.cc:12956
#5  0x0000000000be95c1 in handler::ha_create (this=0x7fff7c093400, name=0x7ffff47cde50 "./test/child", form=0x7ffff47cc118, info_arg=0x7ffff47ce468) at /home/midenok/src/mariadb/10.4/src/sql/handler.cc:4728
#6  0x0000000000beb187 in ha_create_table (thd=0x7fff7c000ce8, path=0x7ffff47cde50 "./test/child", db=0x7fff7c014930 "test", table_name=0x7fff7c014238 "child", create_info=0x7ffff47ce468, frm=0x7ffff47cde40) at /home/midenok/src/mariadb/10.4/src/sql/handler.cc:5192
#7  0x000000000094ff0d in create_table_impl (thd=0x7fff7c000ce8, orig_db=..., orig_table_name=..., db=..., table_name=..., path=0x7ffff47cde50 "./test/child", options=..., create_info=0x7ffff47ce468, alter_info=0x7ffff47ce3b0, create_table_mode=0, is_trans=0x7ffff47ce197, key_info=0x7ffff47ce060, key_count=0x7ffff47ce05c, frm=0x7ffff47cde40) at /home/midenok/src/mariadb/10.4/src/sql/sql_table.cc:5042
#8  0x000000000094f0b8 in mysql_create_table_no_lock (thd=0x7fff7c000ce8, db=0x7fff7c014288, table_name=0x7fff7c014298, create_info=0x7ffff47ce468, alter_info=0x7ffff47ce3b0, is_trans=0x7ffff47ce197, create_table_mode=0, table_list=0x7fff7c014270) at /home/midenok/src/mariadb/10.4/src/sql/sql_table.cc:5126
#9  0x00000000009504d0 in mysql_create_table (thd=0x7fff7c000ce8, create_table=0x7fff7c014270, create_info=0x7ffff47ce468, alter_info=0x7ffff47ce3b0) at /home/midenok/src/mariadb/10.4/src/sql/sql_table.cc:5218
#10 0x00000000009657ef in Sql_cmd_create_table_like::execute (this=0x7fff7c014210, thd=0x7fff7c000ce8) at /home/midenok/src/mariadb/10.4/src/sql/sql_table.cc:11406
#11 0x0000000000854262 in mysql_execute_command (thd=0x7fff7c000ce8) at /home/midenok/src/mariadb/10.4/src/sql/sql_parse.cc:6102
#12 0x0000000000844790 in mysql_parse (thd=0x7fff7c000ce8, rawbuf=0x7fff7c014010 "create or replace table child (\n    a int not null,\n    b int unique,\n    primary key (a,b),\n    foreign key (a) references parent (a) match full,\n    foreign key (a) references parent (a) match partial)", length=203, parser_state=0x7ffff47d1640, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/10.4/src/sql/sql_parse.cc:7901

$7 = { m_name = 0x7fff7c094618 "b" }

12432           for (i = 0; i < m_form->s->keys; i++) {
12433                   if (i != uint(primary_key_no)
12434                       && (error = create_index(m_trx, m_form, m_table, i))) {
12435                           DBUG_RETURN(error);
12436                   }
12437           }

[midenok]

m_form->s->keys written

#0  0x00000000009a3284 in create_key_infos (strpos=0x7f0534044c1b "", frm_image_end=0x7f0534045121 "", keys=2, keyinfo=0x7f05340452d8, new_frm_ver=4, ext_key_parts=0x7f05bc04b690, share=0x7f05bc04b988, len=12, first_keyinfo=0x7f05bc04b698, keynames=0x7f05bc04b820) at /home/midenok/src/mariadb/10.4/src/sql/table.cc:903
#1  0x000000000099a332 in TABLE_SHARE::init_from_binary_frm_image (this=0x7f05bc04b988, thd=0x7f0534000ce8, write=true, frm_image=0x7f0534044b88 "\376\001\n\f\022", frm_length=1433) at /home/midenok/src/mariadb/10.4/src/sql/table.cc:1848
#2  0x0000000000beafc3 in ha_create_table (thd=0x7f0534000ce8, path=0x7f05bc04de50 "./test/child", db=0x7f0534014930 "test", table_name=0x7f0534014238 "child", create_info=0x7f05bc04e468, frm=0x7f05bc04de40) at /home/midenok/src/mariadb/10.4/src/sql/handler.cc:5169
#3  0x000000000094ff0d in create_table_impl (thd=0x7f0534000ce8, orig_db=..., orig_table_name=..., db=..., table_name=..., path=0x7f05bc04de50 "./test/child", options=..., create_info=0x7f05bc04e468, alter_info=0x7f05bc04e3b0, create_table_mode=0, is_trans=0x7f05bc04e197, key_info=0x7f05bc04e060, key_count=0x7f05bc04e05c, frm=0x7f05bc04de40) at /home/midenok/src/mariadb/10.4/src/sql/sql_table.cc:5042
#4  0x000000000094f0b8 in mysql_create_table_no_lock (thd=0x7f0534000ce8, db=0x7f0534014288, table_name=0x7f0534014298, create_info=0x7f05bc04e468, alter_info=0x7f05bc04e3b0, is_trans=0x7f05bc04e197, create_table_mode=0, table_list=0x7f0534014270) at /home/midenok/src/mariadb/10.4/src/sql/sql_table.cc:5126
#5  0x00000000009504d0 in mysql_create_table (thd=0x7f0534000ce8, create_table=0x7f0534014270, create_info=0x7f05bc04e468, alter_info=0x7f05bc04e3b0) at /home/midenok/src/mariadb/10.4/src/sql/sql_table.cc:5218
#6  0x00000000009657ef in Sql_cmd_create_table_like::execute (this=0x7f0534014210, thd=0x7f0534000ce8) at /home/midenok/src/mariadb/10.4/src/sql/sql_table.cc:11406
#7  0x0000000000854262 in mysql_execute_command (thd=0x7f0534000ce8) at /home/midenok/src/mariadb/10.4/src/sql/sql_parse.cc:6102
#8  0x0000000000844790 in mysql_parse (thd=0x7f0534000ce8, rawbuf=0x7f0534014010 "create or replace table child (     a int not null,     b int unique,     primary key (a,b),     foreign key (a) references parent (a) match full,     foreign key (a) references parent (a) match partial)", length=203, parser_state=0x7f05bc051640, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/10.4/src/sql/sql_parse.cc:7901

[midenok]

Info: CONSTRAINT vs FOREIGN KEY argument

From 13.1.20.6:

Foreign key constraint naming is governed by the following rules:

• The CONSTRAINT symbol value is used, if defined.

• If the CONSTRAINT symbol clause is not defined, or a symbol is not included following the CONSTRAINT keyword, a constraint name name is generated automatically.

Prior to MySQL 8.0.16, if the CONSTRAINT symbol clause was not defined, or a symbol was not included following the CONSTRAINT keyword, both InnoDB and NDB storage engines would use the FOREIGN_KEY index_name if defined. In MySQL 8.0.16 and higher, the FOREIGN_KEY index_name is ignored.

• The CONSTRAINT symbol value, if defined, must be unique in the database. A duplicate symbol results in an error similar to: ERROR 1005 (HY000): Can't create table 'test.fk1' (errno: 121).

Questions

1. Do we need to ignore FOREIGN_KEY index_name like in 8.0.16?
2. Do we need uniqueness of CONSTRAINT symbol value across the database?

Test cases

innodb.foreign_key

Case from MDEV-20480 Obsolete internal parser for FK in InnoDB:

create table t2 (x int references t1(x), y int constraint fk references t1(y));
show create table t2;
Table   Create Table
t2  CREATE TABLE `t2` (
  `x` int(11) DEFAULT NULL,
  `y` int(11) DEFAULT NULL,
  KEY `x` (`x`),
  KEY `fk` (`y`),
  CONSTRAINT `fk` FOREIGN KEY (`y`) REFERENCES `t1` (`y`),
  CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`x`) REFERENCES `t1` (`x`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

KEY (y) is named fk by CONSTRAINT clause.

main.information_schema_inno

Case from Bug#25026information_schema.KEY_COLUMN_USAGE.REFERENCED_TABLE_NAMEreturns garbage:

create table `t-1` (
  id int(10) unsigned not null auto_increment,
  idtype int(10) unsigned not null,
  primary key (id),
  key fk_t1_1 (idtype),
  constraint fk_t1_1 foreign key (idtype) references `t-2` (id)
) engine=innodb;

Table is successfully created with same key name and constraint name fk_t1_1 from different clauses.

[midenok]

Bug: innodb.foreign_key fails

mysqltest: At line 70: query 'create table `t-1` (
id int(10) unsigned not null auto_increment,
idtype int(10) unsigned not null,
primary key (id),
key fk_t1_1 (idtype),
constraint fk_t1_1 foreign key (idtype) references `t-2` (id)
) engine=innodb' failed: 1061: Duplicate key name 'fk_t1_1'

#0  mysql_prepare_create_table (thd=0x7f2ee0000d08, create_info=0x7f2f300f0f48, alter_info=0x7f2f300f0e90, db_options=0x7f2f300ef9e8, file=0x7f2ee0015aa8, key_info_buffer=0x7f2f300f0b60, key_count=0x7f2f300f0b5c, create_table_mode=0) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:3825
#1  0x0000000000979d8d in mysql_create_frm_image (thd=0x7f2ee0000d08, db=..., table_name=..., create_info=0x7f2f300f0f48, alter_info=0x7f2f300f0e90, create_table_mode=0, key_info=0x7f2f300f0b60, key_count=0x7f2f300f0b5c, frm=0x7f2f300f0940) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:4835
#2  0x000000000097b2cc in create_table_impl (thd=0x7f2ee0000d08, orig_db=..., orig_table_name=..., db=..., table_name=..., path=0x7f2f300f0950 "./db@002d1/t@002d1", options=..., create_info=0x7f2f300f0f48, alter_info=0x7f2f300f0e90, create_table_mode=0, is_trans=0x7f2f300f0c87, key_info=0x7f2f300f0b60, key_count=0x7f2f300f0b5c, frm=0x7f2f300f0940) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:5079
#3  0x000000000097a548 in mysql_create_table_no_lock (thd=0x7f2ee0000d08, db=0x7f2ee00147b8, table_name=0x7f2ee00147c8, create_info=0x7f2f300f0f48, alter_info=0x7f2f300f0e90, is_trans=0x7f2f300f0c87, create_table_mode=0, table_list=0x7f2ee00147a0) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:5183
#4  0x000000000097b970 in mysql_create_table (thd=0x7f2ee0000d08, create_table=0x7f2ee00147a0, create_info=0x7f2f300f0f48, alter_info=0x7f2f300f0e90) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:5275
#5  0x000000000099170d in Sql_cmd_create_table_like::execute (this=0x7f2ee0014728, thd=0x7f2ee0000d08) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:11544
#6  0x000000000087e8ad in mysql_execute_command (thd=0x7f2ee0000d08) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:5952
#7  0x000000000086f660 in mysql_parse (thd=0x7f2ee0000d08, rawbuf=0x7f2ee0014510 "create table `t-1` (\nid int(10) unsigned not null auto_increment,\nidtype int(10) unsigned not null,\nprimary key (id),\nkey fk_t1_1 (idtype),\nconstraint fk_t1_1 foreign key (idtype) references `t-2` (id)\n) engine=innodb", length=217, parser_state=0x7f2f300f35c0, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:7999

3805        if (key->ignore)
3806        {
3807          /* ignore redundant keys */
3808          do
3809          {
3810            if (key->foreign)
3811            {
3812              List_iterator_fast<Key_part_spec> cols(key->columns);
3813              column= cols++;
3814              it.rewind();
3815              while ((sql_field=it++) &&
3816                    lex_string_cmp(system_charset_info, &column->field_name,
3817                                    &sql_field->field_name));
3818              if (!sql_field)
3819              {
3820                my_error(ER_KEY_COLUMN_DOES_NOT_EXITS, MYF(0), column->field_name.str);
3821                DBUG_RETURN(TRUE);
3822              }
3823              if (!(key_name= key->name).str)
3824                key_name= make_unique_key_name(thd, sql_field->field_name, key_names);
3825              if (key_names.find(key_name) != key_names.end())
3826              {
3827                my_error(ER_DUP_KEYNAME, MYF(0), key_name.str);
3828                DBUG_RETURN(TRUE);
3829              }
3830              key->name= key_name;
3831              key_names.insert(key_name);
3832            }

Fix

Since this is for already ignored keys (they were superseded by prior defined keys), don't check FK name for uniqueness.

[midenok]

ALTER accesses key->name

#0  0x0000000000980559 in mysql_prepare_alter_table (thd=0x7fb260000d08, table=0x7fb2600d5108, create_info=0x7fb2bc0f5fa8, alter_info=0x7fb2bc0f5ef0, alter_ctx=0x7fb2bc0f4c28, mdl_ref_tables=0x7fb2bc0f4850) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:8637
#1  0x00000000009841f1 in mysql_alter_table (thd=0x7fb260000d08, new_db=0x7fb2600054c0, new_name=0x7fb2600058c8, create_info=0x7fb2bc0f5fa8, table_list=0x7fb260014630, alter_info=0x7fb2bc0f5ef0, order_num=0, order=0x0, ignore=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:9864
#2  0x0000000000a4a658 in Sql_cmd_alter_table::execute (this=0x7fb260014e78, thd=0x7fb260000d08) at /home/midenok/src/mariadb/10.5/src/sql/sql_alter.cc:517
#3  0x000000000087e8ad in mysql_execute_command (thd=0x7fb260000d08) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:5952
#4  0x000000000086f660 in mysql_parse (thd=0x7fb260000d08, rawbuf=0x7fb260014510 "ALTER TABLE t1 ADD FOREIGN KEY (a) REFERENCES t1 (a)", length=52, parser_state=0x7fb2bc0f85c0, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:7999

8637          if (key->name.str &&
8638              !my_strcasecmp(system_charset_info, key->name.str, primary_key_name))
8639          {
8640            my_error(ER_WRONG_NAME_FOR_INDEX, MYF(0), key->name.str);
8641            goto err;
8642          }

[midenok]

Bug: system table prelocking fails with ER_WRONG_LOCK_OF_SYSTEM_TABLE

1. Table added to list with TL_WRITE_DEFAULT

#0  0x00000000008b0190 in st_select_lex::add_table_to_list (this=0x4801348, thd=0x47fcc18, table=0x5479ed0, alias=0x0, table_options=1, lock_type=TL_WRITE_DEFAULT, mdl_type=MDL_SHARED_WRITE, index_hints_arg=0x0, partition_names=0x0, option=0x0) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:8199
#1  0x0000000000baf303 in MYSQLparse (thd=0x47fcc18) at /home/midenok/src/mariadb/10.5/src/sql/sql_yacc.yy:13096
#2  0x00000000008c2fad in parse_sql (thd=0x47fcc18, parser_state=0x7ffcc9fbe758, creation_ctx=0x0, do_pfs_digest=true) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:10294
#3  0x00000000008ac996 in mysql_parse (thd=0x47fcc18, rawbuf=0x5479e20 "delete from help_topic;", length=23, parser_state=0x7ffcc9fbe758, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:7941
#4  0x00000000008ac54d in bootstrap (file=0x2975130 <instrumented_stdin>) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:1097
#5  0x0000000000731fd0 in mysqld_main (argc=61, argv=0x470be88) at /home/midenok/src/mariadb/10.5/src/sql/mysqld.cc:5640
#6  0x000000000072d502 in main (argc=60, argv=0x7ffcc9fc3a38) at /home/midenok/src/mariadb/10.5/src/sql/main.cc:25

2. tbl->reginfo.lock_type set to TL_WRITE

#0  0x00000000007f1ca8 in open_tables (thd=0x47fcc18, options=..., start=0x7ffcc9fbc280, counter=0x7ffcc9fbc26c, flags=0, prelocking_strategy=0x7ffcc9fbc2e0) at /home/midenok/src/mariadb/10.5/src/sql/sql_base.cc:4459
#1  0x00000000007f5fc5 in open_and_lock_tables (thd=0x47fcc18, options=..., tables=0x5479f10, derived=true, flags=0, prelocking_strategy=0x7ffcc9fbc2e0) at /home/midenok/src/mariadb/10.5/src/sql/sql_base.cc:5194
#2  0x00000000007a084c in open_and_lock_tables (thd=0x47fcc18, tables=0x5479f10, derived=true, flags=0) at /home/midenok/src/mariadb/10.5/src/sql/sql_base.h:504
#3  0x0000000000e851f6 in mysql_delete (thd=0x47fcc18, table_list=0x5479f10, conds=0x0, order_list=0x48015e8, limit=18446744073709551615, options=0, result=0x0) at /home/midenok/src/mariadb/10.5/src/sql/sql_delete.cc:330
#4  0x00000000008b7334 in mysql_execute_command (thd=0x47fcc18) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:4738
#5  0x00000000008acbc6 in mysql_parse (thd=0x47fcc18, rawbuf=0x5479e20 "delete from help_topic;", length=23, parser_state=0x7ffcc9fbe758, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:7988

4455        /* Set appropriate TABLE::lock_type. */
4456        if (tbl && tables->lock_type != TL_UNLOCK && !thd->locked_tables_mode)
4457        {
4458          if (tables->lock_type == TL_WRITE_DEFAULT)
4459            tbl->reginfo.lock_type= thd->update_lock_default;

3. system_count incremented

#0  lock_tables_check (thd=0x47fcc18, tables=0x547ae08, count=2, flags=0) at /home/midenok/src/mariadb/10.5/src/sql/lock.cc:155
#1  0x0000000000ddefe4 in mysql_lock_tables (thd=0x47fcc18, tables=0x547ae08, count=2, flags=0) at /home/midenok/src/mariadb/10.5/src/sql/lock.cc:290
#2  0x00000000007f49fd in lock_tables (thd=0x47fcc18, tables=0x5479f10, count=2, flags=0) at /home/midenok/src/mariadb/10.5/src/sql/sql_base.cc:5457
#3  0x00000000007f6079 in open_and_lock_tables (thd=0x47fcc18, options=..., tables=0x5479f10, derived=true, flags=0, prelocking_strategy=0x7ffcc9fbc2e0) at /home/midenok/src/mariadb/10.5/src/sql/sql_base.cc:5203
#4  0x00000000007a084c in open_and_lock_tables (thd=0x47fcc18, tables=0x5479f10, derived=true, flags=0) at /home/midenok/src/mariadb/10.5/src/sql/sql_base.h:504
#5  0x0000000000e851f6 in mysql_delete (thd=0x47fcc18, table_list=0x5479f10, conds=0x0, order_list=0x48015e8, limit=18446744073709551615, options=0, result=0x0) at /home/midenok/src/mariadb/10.5/src/sql/sql_delete.cc:330
#6  0x00000000008b7334 in mysql_execute_command (thd=0x47fcc18) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:4738
#7  0x00000000008acbc6 in mysql_parse (thd=0x47fcc18, rawbuf=0x5479e20 "delete from help_topic;", length=23, parser_state=0x7ffcc9fbe758, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:7988

150         if (t->reginfo.lock_type >= TL_WRITE_ALLOW_WRITE)
151         {
152           if (t->s->table_category == TABLE_CATEGORY_SYSTEM)
153             system_count++;
154
155           if (t->db_stat & HA_READ_ONLY)
156           {
157             my_error(ER_OPEN_AS_READONLY, MYF(0), t->alias.c_ptr_safe());
158             DBUG_RETURN(1);
159           }
160         }

4. error thrown

#4  0x0000000001643550 in my_error (nr=1428, MyFlags=0) at /home/midenok/src/mariadb/10.5/src/mysys/my_error.c:125
#5  0x0000000000ddf6bc in lock_tables_check (thd=0x47fcc18, tables=0x547ae08, count=2, flags=0) at /home/midenok/src/mariadb/10.5/src/sql/lock.cc:197
#6  0x0000000000ddefe4 in mysql_lock_tables (thd=0x47fcc18, tables=0x547ae08, count=2, flags=0) at /home/midenok/src/mariadb/10.5/src/sql/lock.cc:290
#7  0x00000000007f49fd in lock_tables (thd=0x47fcc18, tables=0x5479f10, count=2, flags=0) at /home/midenok/src/mariadb/10.5/src/sql/sql_base.cc:5457
#8  0x00000000007f6079 in open_and_lock_tables (thd=0x47fcc18, options=..., tables=0x5479f10, derived=true, flags=0, prelocking_strategy=0x7ffcc9fbc2e0) at /home/midenok/src/mariadb/10.5/src/sql/sql_base.cc:5203
#9  0x00000000007a084c in open_and_lock_tables (thd=0x47fcc18, tables=0x5479f10, derived=true, flags=0) at /home/midenok/src/mariadb/10.5/src/sql/sql_base.h:504
#10 0x0000000000e851f6 in mysql_delete (thd=0x47fcc18, table_list=0x5479f10, conds=0x0, order_list=0x48015e8, limit=18446744073709551615, options=0, result=0x0) at /home/midenok/src/mariadb/10.5/src/sql/sql_delete.cc:330
#11 0x00000000008b7334 in mysql_execute_command (thd=0x47fcc18) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:4738
#12 0x00000000008acbc6 in mysql_parse (thd=0x47fcc18, rawbuf=0x5479e20 "delete from help_topic;", length=23, parser_state=0x7ffcc9fbe758, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:7988

190       /*
191         Locking of system tables is restricted:
192         locking a mix of system and non-system tables in the same lock
193         is prohibited, to prevent contention.
194       */
195       if ((system_count > 0) && (system_count < count))
196       {
197         my_error(ER_WRONG_LOCK_OF_SYSTEM_TABLE, MYF(0));
198         DBUG_RETURN(1);
199       }

frame 7

5457        if (! (thd->lock= mysql_lock_tables(thd, start, (uint) (ptr - start),
5458                                            flags)))
5459          DBUG_RETURN(TRUE);

[midenok]

Foreign table prelocked for read

#0  0x00000000007a51ce in TABLE_LIST::init_one_table (this=0x5087958, db_arg=0x50e9f20, table_name_arg=0x50e9f30, alias_arg=0x0, lock_type_arg=TL_READ) at /home/midenok/src/mariadb/10.5/src/sql/table.h:2031
#1  0x00000000007a12dc in TABLE_LIST::init_one_table_for_prelocking (this=0x5087958, db_arg=0x50e9f20, table_name_arg=0x50e9f30, alias_arg=0x0, lock_type_arg=TL_READ, prelocking_type=TABLE_LIST::PRELOCK_FK, belong_to_view_arg=0x0, trg_event_map_arg=4 '\004', last_ptr=0x440daa8, insert_data=0 '\000') at /home/midenok/src/mariadb/10.5/src/sql/table.h:2057
#2  0x00000000007f3e81 in DML_prelocking_strategy::handle_table (this=0x7ffd27c41380, thd=0x4409c18, prelocking_ctx=0x440da98, table_list=0x5087120, need_prelocking=0x7ffd27c40ec6) at /home/midenok/src/mariadb/10.5/src/sql/sql_base.cc:4700
#3  0x00000000007f0ee1 in extend_table_list (thd=0x4409c18, tables=0x5087120, prelocking_strategy=0x7ffd27c41380, has_prelocking_list=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_base.cc:3576
#4  0x00000000007f2e0c in open_and_process_table (thd=0x4409c18, tables=0x5087120, counter=0x7ffd27c4130c, flags=0, prelocking_strategy=0x7ffd27c41380, has_prelocking_list=false, ot_ctx=0x7ffd27c41208) at /home/midenok/src/mariadb/10.5/src/sql/sql_base.cc:3891
#5  0x00000000007f163b in open_tables (thd=0x4409c18, options=..., start=0x7ffd27c41320, counter=0x7ffd27c4130c, flags=0, prelocking_strategy=0x7ffd27c41380) at /home/midenok/src/mariadb/10.5/src/sql/sql_base.cc:4309
#6  0x00000000007f5fc5 in open_and_lock_tables (thd=0x4409c18, options=..., tables=0x5087120, derived=true, flags=0, prelocking_strategy=0x7ffd27c41380) at /home/midenok/src/mariadb/10.5/src/sql/sql_base.cc:5194
#7  0x00000000007a084c in open_and_lock_tables (thd=0x4409c18, tables=0x5087120, derived=true, flags=0) at /home/midenok/src/mariadb/10.5/src/sql/sql_base.h:504
#8  0x0000000000e851f6 in mysql_delete (thd=0x4409c18, table_list=0x5087120, conds=0x0, order_list=0x440e5e8, limit=18446744073709551615, options=0, result=0x0) at /home/midenok/src/mariadb/10.5/src/sql/sql_delete.cc:330
#9  0x00000000008b7334 in mysql_execute_command (thd=0x4409c18) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:4738
#10 0x00000000008acbc6 in mysql_parse (thd=0x4409c18, rawbuf=0x5087030 "delete from help_topic;", length=23, parser_state=0x7ffd27c437f8, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:7988

4662      if (table_list->trg_event_map)
4663      {
4664        if (table->triggers)
4665        {
4666          *need_prelocking= TRUE;
4667
4668          if (table->triggers->
4669              add_tables_and_routines_for_triggers(thd, prelocking_ctx, table_list))
4670            return TRUE;
4671        }
4672
4673        if (table->s->referenced_by_foreign_key())
4674        {
4675          List_iterator<FK_info> fk_list_it(*table->s->referenced_keys);
4676          FK_info *fk;
4677          Query_arena *arena, backup;
4678
4679          arena= thd->activate_stmt_arena_if_needed(&backup);
4680          *need_prelocking= TRUE;
4681
4682          while ((fk= fk_list_it++))
4683          {
4684            // FK_OPTION_RESTRICT and FK_OPTION_NO_ACTION only need read access
4685            uint8 op= table_list->trg_event_map;
4686            thr_lock_type lock_type;

[midenok]

Crash safety vv

Test: parts.partition_debug_innodb

1. DDL log inited (global_ddl_log)

#0  init_ddl_log () at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:975
#1  0x00000000009375ce in write_ddl_log_entry (ddl_log_entry=0x7ffff0ead348, active_entry=0x7ffff0ead340) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:1401
#2  0x0000000000e2da0b in write_log_replace_delete_frm (lpt=0x7ffff0ead930, next_entry=0, from_path=0x0, to_path=0x7ffff0ead410 "./test/#sql-t1", replace_flag=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_partition.cc:6159
#3  0x0000000000e26f35 in write_log_drop_shadow_frm (lpt=0x7ffff0ead930) at /home/midenok/src/mariadb/10.5/src/sql/sql_partition.cc:6416
#4  0x0000000000e2525a in fast_alter_partition_table (thd=0x7fff8c000d08, table=0x7fff8c0255b8, alter_info=0x7ffff0eb0f28, create_info=0x7ffff0eb0fe0, table_list=0x7fff8c014648, db=0x7ffff0eafc78, table_name=0x7ffff0eafc88) at /home/midenok/src/mariadb/10.5/src/sql/sql_partition.cc:7232
#5  0x000000000094ef8d in mysql_alter_table (thd=0x7fff8c000d08, new_db=0x7fff8c0054c0, new_name=0x7fff8c0058c8, create_info=0x7ffff0eb0fe0, table_list=0x7fff8c014648, alter_info=0x7ffff0eb0f28, order_num=0, order=0x0, ignore=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:9835
#6  0x0000000000a0f5b6 in Sql_cmd_alter_table::execute (this=0x7fff8c015cf8, thd=0x7fff8c000d08) at /home/midenok/src/mariadb/10.5/src/sql/sql_alter.cc:516
#7  0x0000000000849ea8 in mysql_execute_command (thd=0x7fff8c000d08) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:5951
#8  0x000000000083ac70 in mysql_parse (thd=0x7fff8c000d08, rawbuf=0x7fff8c0144f0 "ALTER TABLE t1 ADD PARTITION\n(PARTITION p20 VALUES IN (20,21,22,23,24,25,26,27,28,29))", length=86, parser_state=0x7ffff0eb35c0, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:7998

2. Log entry written

#0  write_ddl_log_entry (ddl_log_entry=0x7f98180f50c8, active_entry=0x7f98180f50c0) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:1400
#1  0x0000000000eada5b in write_log_replace_delete_frm (lpt=0x7f98180f56b0, next_entry=0, from_path=0x0, to_path=0x7f98180f5190 "./test/#sql-t1", replace_flag=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_partition.cc:6159
#2  0x0000000000ea6f35 in write_log_drop_shadow_frm (lpt=0x7f98180f56b0) at /home/midenok/src/mariadb/10.5/src/sql/sql_partition.cc:6416
#3  0x0000000000ea524d in fast_alter_partition_table (thd=0x7f97a0000cf8, table=0x7f97a013b0f8, alter_info=0x7f98180f8da0, create_info=0x7f98180f8e88, table_list=0x7f97a012f500, db=0x7f98180f7ab8, table_name=0x7f98180f7ac8) at /home/midenok/src/mariadb/10.5/src/sql/sql_partition.cc:7232
#4  0x00000000009c525f in mysql_alter_table (thd=0x7f97a0000cf8, new_db=0x7f97a0005520, new_name=0x7f97a00059b0, create_info=0x7f98180f8e88, table_list=0x7f97a012f500, alter_info=0x7f98180f8da0, order_num=0, order=0x0, ignore=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:9950
#5  0x0000000000a8d238 in Sql_cmd_alter_table::execute (this=0x7f97a0023930, thd=0x7f97a0000cf8) at /home/midenok/src/mariadb/10.5/src/sql/sql_alter.cc:517
#6  0x00000000008bd483 in mysql_execute_command (thd=0x7f97a0000cf8) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:5959
#7  0x00000000008adef6 in mysql_parse (thd=0x7f97a0000cf8, rawbuf=0x7f97a0083930 "ALTER TABLE t1 ADD PARTITION\n(PARTITION p20 VALUES IN (20,21,22,23,24,25,26,27,28,29))", length=86, parser_state=0x7f98180fb5c0, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:7988

3. Log entry executed

#0  execute_ddl_log_action (thd=0x30c6628, ddl_log_entry=0x7fffffffab08) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:1101
#1  0x0000000000939663 in execute_ddl_log_entry_no_lock (thd=0x30c6628, first_entry=1) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:1362
#2  0x000000000093998c in execute_ddl_log_recovery () at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:1682
#3  0x00000000006c55f8 in mysqld_main (argc=158, argv=0x28d8628) at /home/midenok/src/mariadb/10.5/src/sql/mysqld.cc:5607

Info: order of ALTER_PARTITION_ADD exection

1. write_log_drop_shadow_frm()
2. mysql_write_frm(WFRM_WRITE_SHADOW)
3. write_log_add_change_partition()
4. mysql_change_partitions()
5. write_log_rename_frm()
6. mysql_write_frm(WFRM_INSTALL_SHADOW)
7. write_log_completed()

Other tests

main.partition_exchange \
parts.partition_debug_sync_innodb \
parts.partition_special_innodb \
parts.partition_mgm_lc1_innodb \
parts.partition_debug_innodb \
parts.alter_data_directory_innodb \
parts.partition_mgm_lc0_innodb \
parts.partition_alter3_innodb \
parts.partition_alter_innodb \
parts.reorganize_partition_innodb \
parts.partition_mgm_lc1_memory \
parts.partition_mgm_lc1_myisam \
parts.partition_debug_myisam \
parts.part_supported_sql_func_myisam \
parts.partition_alter3_myisam \
parts.partition_alter_maria \
parts.partition_exch_qa_15 \
parts.partition_mgm_lc0_memory \
parts.partition_mgm_lc0_myisam \
parts.partition_special_myisam \
parts.show_create

Info

Phases of exection are changed via deactivate_ddl_log_entry_no_lock(), first it sets phase to 1:

1059          else if (file_entry_buf[DDL_LOG_ACTION_TYPE_POS] == DDL_LOG_REPLACE_ACTION)
1060          {
1061            DBUG_ASSERT(file_entry_buf[DDL_LOG_PHASE_POS] == 0);
1062            file_entry_buf[DDL_LOG_PHASE_POS]= 1;
1063          }

then it deactivates log entry:

1052          if (... ||
1054              (file_entry_buf[DDL_LOG_ACTION_TYPE_POS] == DDL_LOG_REPLACE_ACTION &&
1055               file_entry_buf[DDL_LOG_PHASE_POS] == 1) || ...)
1058            file_entry_buf[DDL_LOG_ENTRY_TYPE_POS]= DDL_IGNORE_LOG_ENTRY_CODE;

Different DDL_LOG_ACTION_TYPE_POS behave differently (see comment for deactivate_ddl_log_entry_no_lock()).

[midenok]

Test: parts.partition_alter_innodb

1. write_log_drop_shadow_frm(): DDL_LOG_DELETE_ACTION (for frm) written

#0  write_ddl_log_entry (ddl_log_entry=0x7fd8d005c0c8, active_entry=0x7fd8d005c0c0) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:1400
#1  0x0000000000eada5b in write_log_replace_delete_frm (lpt=0x7fd8d005c6b0, next_entry=0, from_path=0x0, to_path=0x7fd8d005c190 "./test/#sql-t1", replace_flag=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_partition.cc:6159
#2  0x0000000000ea6f35 in write_log_drop_shadow_frm (lpt=0x7fd8d005c6b0) at /home/midenok/src/mariadb/10.5/src/sql/sql_partition.cc:6416
#3  0x0000000000ea4795 in fast_alter_partition_table (thd=0x7fd86c000cf8, table=0x7fd86c01d3b8, alter_info=0x7fd8d005fda0, create_info=0x7fd8d005fe88, table_list=0x7fd86c02aea0, db=0x7fd8d005eab8, table_name=0x7fd8d005eac8) at /home/midenok/src/mariadb/10.5/src/sql/sql_partition.cc:7158
#4  0x00000000009c525f in mysql_alter_table (thd=0x7fd86c000cf8, new_db=0x7fd86c005520, new_name=0x7fd86c0059b0, create_info=0x7fd8d005fe88, table_list=0x7fd86c02aea0, alter_info=0x7fd8d005fda0, order_num=0, order=0x0, ignore=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:9950
#5  0x0000000000a8d238 in Sql_cmd_alter_table::execute (this=0x7fd86c013880, thd=0x7fd86c000cf8) at /home/midenok/src/mariadb/10.5/src/sql/sql_alter.cc:517
#6  0x00000000008bd483 in mysql_execute_command (thd=0x7fd86c000cf8) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:5959
#7  0x00000000008adef6 in mysql_parse (thd=0x7fd86c000cf8, rawbuf=0x7fd86c010a60 "ALTER TABLE t1 DROP PARTITION p1", length=32, parser_state=0x7fd8d00625c0, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:7988

(rr) p *ddl_log_entry
$17 = {
  name = 0x7fd8d005c190 "./test/#sql-t1",
  from_name = 0x7fd8d005c130 "\300\303\005\320\330\177",
  handler_name = 0x20e7fc0 <reg_ext> ".frm",
  tmp_name = 0x7fd8de834b40 "\006",
  next_entry = 0,
  entry_pos = 32728,
  entry_type = 2932520157,
  action_type = DDL_LOG_DELETE_ACTION,
  phase = -96 '\240'
}

2. write_log_drop_shadow_frm(): DDL_LOG_EXECUTE_CODE written

#0  write_execute_ddl_log_entry (first_entry=1, complete=false, active_entry=0x7fd8d005c398) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:1473
#1  0x0000000000ea6f98 in write_log_drop_shadow_frm (lpt=0x7fd8d005c6b0) at /home/midenok/src/mariadb/10.5/src/sql/sql_partition.cc:6420
#2  0x0000000000ea4795 in fast_alter_partition_table (thd=0x7fd86c000cf8, table=0x7fd86c01d3b8, alter_info=0x7fd8d005fda0, create_info=0x7fd8d005fe88, table_list=0x7fd86c02aea0, db=0x7fd8d005eab8, table_name=0x7fd8d005eac8) at /home/midenok/src/mariadb/10.5/src/sql/sql_partition.cc:7158
#3  0x00000000009c525f in mysql_alter_table (thd=0x7fd86c000cf8, new_db=0x7fd86c005520, new_name=0x7fd86c0059b0, create_info=0x7fd8d005fe88, table_list=0x7fd86c02aea0, alter_info=0x7fd8d005fda0, order_num=0, order=0x0, ignore=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:9950
#4  0x0000000000a8d238 in Sql_cmd_alter_table::execute (this=0x7fd86c013880, thd=0x7fd86c000cf8) at /home/midenok/src/mariadb/10.5/src/sql/sql_alter.cc:517
#5  0x00000000008bd483 in mysql_execute_command (thd=0x7fd86c000cf8) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:5959
#6  0x00000000008adef6 in mysql_parse (thd=0x7fd86c000cf8, rawbuf=0x7fd86c010a60 "ALTER TABLE t1 DROP PARTITION p1", length=32, parser_state=0x7fd8d00625c0, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:7988

3. shadow frm written (WFRM_WRITE_SHADOW)

#0  writefrm (path=0x7fd8d005bf90 "./test/#sql-t1", db=0x7fd86c022c60 "test", table=0x7fd86c013f10 "t1", tmp_table=false, frmdata=0x7fd86c023fd8 "\376\001\n\024\032", len=514) at /home/midenok/src/mariadb/10.5/src/sql/discover.cc:122
#1  0x00000000009af7fe in mysql_write_frm (lpt=0x7fd8d005c6b0, flags=1) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:1861
#2  0x0000000000ea48a0 in fast_alter_partition_table (thd=0x7fd86c000cf8, table=0x7fd86c01d3b8, alter_info=0x7fd8d005fda0, create_info=0x7fd8d005fe88, table_list=0x7fd86c02aea0, db=0x7fd8d005eab8, table_name=0x7fd8d005eac8) at /home/midenok/src/mariadb/10.5/src/sql/sql_partition.cc:7161
#3  0x00000000009c525f in mysql_alter_table (thd=0x7fd86c000cf8, new_db=0x7fd86c005520, new_name=0x7fd86c0059b0, create_info=0x7fd8d005fe88, table_list=0x7fd86c02aea0, alter_info=0x7fd8d005fda0, order_num=0, order=0x0, ignore=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:9950
#4  0x0000000000a8d238 in Sql_cmd_alter_table::execute (this=0x7fd86c013880, thd=0x7fd86c000cf8) at /home/midenok/src/mariadb/10.5/src/sql/sql_alter.cc:517
#5  0x00000000008bd483 in mysql_execute_command (thd=0x7fd86c000cf8) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:5959
#6  0x00000000008adef6 in mysql_parse (thd=0x7fd86c000cf8, rawbuf=0x7fd86c010a60 "ALTER TABLE t1 DROP PARTITION p1", length=32, parser_state=0x7fd8d00625c0, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:7988

4. write_log_drop_partition(): DDL_LOG_DELETE_ACTION (for partition) written

#0  write_ddl_log_entry (ddl_log_entry=0x7fd8d005beb0, active_entry=0x7fd8d005bea0) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:1400
#1  0x0000000000eadfd9 in write_log_dropped_partitions (lpt=0x7fd8d005c6b0, next_entry=0x7fd8d005bf7c, path=0x7fd8d005bf80 "./test/t1", temp_list=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_partition.cc:6358
#2  0x0000000000ea71d9 in write_log_drop_partition (lpt=0x7fd8d005c6b0) at /home/midenok/src/mariadb/10.5/src/sql/sql_partition.cc:6514
#3  0x0000000000ea4ac6 in fast_alter_partition_table (thd=0x7fd86c000cf8, table=0x7fd86c01d3b8, alter_info=0x7fd8d005fda0, create_info=0x7fd8d005fe88, table_list=0x7fd86c02aea0, db=0x7fd8d005eab8, table_name=0x7fd8d005eac8) at /home/midenok/src/mariadb/10.5/src/sql/sql_partition.cc:7168
#4  0x00000000009c525f in mysql_alter_table (thd=0x7fd86c000cf8, new_db=0x7fd86c005520, new_name=0x7fd86c0059b0, create_info=0x7fd8d005fe88, table_list=0x7fd86c02aea0, alter_info=0x7fd8d005fda0, order_num=0, order=0x0, ignore=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:9950
#5  0x0000000000a8d238 in Sql_cmd_alter_table::execute (this=0x7fd86c013880, thd=0x7fd86c000cf8) at /home/midenok/src/mariadb/10.5/src/sql/sql_alter.cc:517
#6  0x00000000008bd483 in mysql_execute_command (thd=0x7fd86c000cf8) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:5959
#7  0x00000000008adef6 in mysql_parse (thd=0x7fd86c000cf8, rawbuf=0x7fd86c010a60 "ALTER TABLE t1 DROP PARTITION p1", length=32, parser_state=0x7fd8d00625c0, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:7988

(rr) p *ddl_log_entry
$18 = {
  name = 0x7fd8d005bc90 "./test/t1#P#p1",
  from_name = 0x7 <error: Cannot access memory at address 0x7>,
  handler_name = 0x1923f34 <innobase_hton_name> "InnoDB",
  tmp_name = 0x7fd8dd6931c0 "",
  next_entry = 0,
  entry_pos = 72259,
  entry_type = 17015968,
  action_type = DDL_LOG_DELETE_ACTION,
  phase = -8 '\370'
}

5. write_log_drop_partition(): DDL_LOG_REPLACE_ACTION (for frm) written

#0  write_ddl_log_entry (ddl_log_entry=0x7fd8d005bea8, active_entry=0x7fd8d005bea0) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:1400
#1  0x0000000000eada5b in write_log_replace_delete_frm (lpt=0x7fd8d005c6b0, next_entry=3, from_path=0x7fd8d005c190 "./test/#sql-t1", to_path=0x7fd8d005bf80 "./test/t1", replace_flag=true) at /home/midenok/src/mariadb/10.5/src/sql/sql_partition.cc:6159
#2  0x0000000000ea723f in write_log_drop_partition (lpt=0x7fd8d005c6b0) at /home/midenok/src/mariadb/10.5/src/sql/sql_partition.cc:6517
#3  0x0000000000ea4ac6 in fast_alter_partition_table (thd=0x7fd86c000cf8, table=0x7fd86c01d3b8, alter_info=0x7fd8d005fda0, create_info=0x7fd8d005fe88, table_list=0x7fd86c02aea0, db=0x7fd8d005eab8, table_name=0x7fd8d005eac8) at /home/midenok/src/mariadb/10.5/src/sql/sql_partition.cc:7168
#4  0x00000000009c525f in mysql_alter_table (thd=0x7fd86c000cf8, new_db=0x7fd86c005520, new_name=0x7fd86c0059b0, create_info=0x7fd8d005fe88, table_list=0x7fd86c02aea0, alter_info=0x7fd8d005fda0, order_num=0, order=0x0, ignore=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:9950
#5  0x0000000000a8d238 in Sql_cmd_alter_table::execute (this=0x7fd86c013880, thd=0x7fd86c000cf8) at /home/midenok/src/mariadb/10.5/src/sql/sql_alter.cc:517
#6  0x00000000008bd483 in mysql_execute_command (thd=0x7fd86c000cf8) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:5959
#7  0x00000000008adef6 in mysql_parse (thd=0x7fd86c000cf8, rawbuf=0x7fd86c010a60 "ALTER TABLE t1 DROP PARTITION p1", length=32, parser_state=0x7fd8d00625c0, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:7988

(rr) p *ddl_log_entry
$14 = {
  name = 0x7fd8d005bf80 "./test/t1",
  from_name = 0x7fd8d005c190 "./test/#sql-t1",
  handler_name = 0x20e7fc0 <reg_ext> ".frm",
  tmp_name = 0x1923f34 <innobase_hton_name> "InnoDB",
  next_entry = 3,
  entry_pos = 32728,
  entry_type = 0,
  action_type = DDL_LOG_REPLACE_ACTION,
  phase = -96 '\240'
}

6. write_log_drop_partition(): DDL_LOG_EXECUTE_CODE written

#0  write_execute_ddl_log_entry (first_entry=4, complete=false, active_entry=0x7fd8d005c398) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:1490
#1  0x0000000000ea7291 in write_log_drop_partition (lpt=0x7fd8d005c6b0) at /home/midenok/src/mariadb/10.5/src/sql/sql_partition.cc:6522
#2  0x0000000000ea4ac6 in fast_alter_partition_table (thd=0x7fd86c000cf8, table=0x7fd86c01d3b8, alter_info=0x7fd8d005fda0, create_info=0x7fd8d005fe88, table_list=0x7fd86c02aea0, db=0x7fd8d005eab8, table_name=0x7fd8d005eac8) at /home/midenok/src/mariadb/10.5/src/sql/sql_partition.cc:7168
#3  0x00000000009c525f in mysql_alter_table (thd=0x7fd86c000cf8, new_db=0x7fd86c005520, new_name=0x7fd86c0059b0, create_info=0x7fd8d005fe88, table_list=0x7fd86c02aea0, alter_info=0x7fd8d005fda0, order_num=0, order=0x0, ignore=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:9950
#4  0x0000000000a8d238 in Sql_cmd_alter_table::execute (this=0x7fd86c013880, thd=0x7fd86c000cf8) at /home/midenok/src/mariadb/10.5/src/sql/sql_alter.cc:517
#5  0x00000000008bd483 in mysql_execute_command (thd=0x7fd86c000cf8) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:5959
#6  0x00000000008adef6 in mysql_parse (thd=0x7fd86c000cf8, rawbuf=0x7fd86c010a60 "ALTER TABLE t1 DROP PARTITION p1", length=32, parser_state=0x7fd8d00625c0, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:7988

7. ./test/t1.frm deleted

#0  inline_mysql_file_delete (key=12, src_file=0x1752ebd "/home/midenok/src/mariadb/10.5/src/sql/sql_table.cc", src_line=1896, name=0x7fd8d005bb70 "./test/t1.frm", flags=16) at /home/midenok/src/mariadb/10.5/src/include/mysql/psi/mysql_file.h:1329
#1  0x00000000009af96a in mysql_write_frm (lpt=0x7fd8d005c6b0, flags=2) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:1896
#2  0x0000000000ea4e7f in fast_alter_partition_table (thd=0x7fd86c000cf8, table=0x7fd86c01d3b8, alter_info=0x7fd8d005fda0, create_info=0x7fd8d005fe88, table_list=0x7fd86c02aea0, db=0x7fd8d005eab8, table_name=0x7fd8d005eac8) at /home/midenok/src/mariadb/10.5/src/sql/sql_partition.cc:7182
#3  0x00000000009c525f in mysql_alter_table (thd=0x7fd86c000cf8, new_db=0x7fd86c005520, new_name=0x7fd86c0059b0, create_info=0x7fd8d005fe88, table_list=0x7fd86c02aea0, alter_info=0x7fd8d005fda0, order_num=0, order=0x0, ignore=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:9950
#4  0x0000000000a8d238 in Sql_cmd_alter_table::execute (this=0x7fd86c013880, thd=0x7fd86c000cf8) at /home/midenok/src/mariadb/10.5/src/sql/sql_alter.cc:517
#5  0x00000000008bd483 in mysql_execute_command (thd=0x7fd86c000cf8) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:5959
#6  0x00000000008adef6 in mysql_parse (thd=0x7fd86c000cf8, rawbuf=0x7fd86c010a60 "ALTER TABLE t1 DROP PARTITION p1", length=32, parser_state=0x7fd8d00625c0, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:7988

8. phase increased to 1

#0  deactivate_ddl_log_entry_no_lock (entry_no=4) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:1062
#1  0x00000000009adc64 in deactivate_ddl_log_entry (entry_no=4) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:1546
#2  0x00000000009af9d7 in mysql_write_frm (lpt=0x7fd8d005c6b0, flags=2) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:1900
#3  0x0000000000ea4e7f in fast_alter_partition_table (thd=0x7fd86c000cf8, table=0x7fd86c01d3b8, alter_info=0x7fd8d005fda0, create_info=0x7fd8d005fe88, table_list=0x7fd86c02aea0, db=0x7fd8d005eab8, table_name=0x7fd8d005eac8) at /home/midenok/src/mariadb/10.5/src/sql/sql_partition.cc:7182
#4  0x00000000009c525f in mysql_alter_table (thd=0x7fd86c000cf8, new_db=0x7fd86c005520, new_name=0x7fd86c0059b0, create_info=0x7fd8d005fe88, table_list=0x7fd86c02aea0, alter_info=0x7fd8d005fda0, order_num=0, order=0x0, ignore=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:9950
#5  0x0000000000a8d238 in Sql_cmd_alter_table::execute (this=0x7fd86c013880, thd=0x7fd86c000cf8) at /home/midenok/src/mariadb/10.5/src/sql/sql_alter.cc:517
#6  0x00000000008bd483 in mysql_execute_command (thd=0x7fd86c000cf8) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:5959
#7  0x00000000008adef6 in mysql_parse (thd=0x7fd86c000cf8, rawbuf=0x7fd86c010a60 "ALTER TABLE t1 DROP PARTITION p1", length=32, parser_state=0x7fd8d00625c0, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:7988

9. ./test/#sql-t1.frm renamed to ./test/t1.frm

#0  inline_mysql_file_rename (key=12, src_file=0x1752ebd "/home/midenok/src/mariadb/10.5/src/sql/sql_table.cc", src_line=1903, from=0x7fd8d005bd80 "./test/#sql-t1.frm", to=0x7fd8d005bb70 "./test/t1.frm", flags=16) at /home/midenok/src/mariadb/10.5/src/include/mysql/psi/mysql_file.h:1352
#1  0x00000000009afa47 in mysql_write_frm (lpt=0x7fd8d005c6b0, flags=2) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:1902
#2  0x0000000000ea4e7f in fast_alter_partition_table (thd=0x7fd86c000cf8, table=0x7fd86c01d3b8, alter_info=0x7fd8d005fda0, create_info=0x7fd8d005fe88, table_list=0x7fd86c02aea0, db=0x7fd8d005eab8, table_name=0x7fd8d005eac8) at /home/midenok/src/mariadb/10.5/src/sql/sql_partition.cc:7182
#3  0x00000000009c525f in mysql_alter_table (thd=0x7fd86c000cf8, new_db=0x7fd86c005520, new_name=0x7fd86c0059b0, create_info=0x7fd8d005fe88, table_list=0x7fd86c02aea0, alter_info=0x7fd8d005fda0, order_num=0, order=0x0, ignore=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:9950
#4  0x0000000000a8d238 in Sql_cmd_alter_table::execute (this=0x7fd86c013880, thd=0x7fd86c000cf8) at /home/midenok/src/mariadb/10.5/src/sql/sql_alter.cc:517
#5  0x00000000008bd483 in mysql_execute_command (thd=0x7fd86c000cf8) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:5959
#6  0x00000000008adef6 in mysql_parse (thd=0x7fd86c000cf8, rawbuf=0x7fd86c010a60 "ALTER TABLE t1 DROP PARTITION p1", length=32, parser_state=0x7fd8d00625c0, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:7988

10. phase deactivated

#0  deactivate_ddl_log_entry_no_lock (entry_no=4) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:1058
#1  0x00000000009adc64 in deactivate_ddl_log_entry (entry_no=4) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:1546
#2  0x00000000009afc47 in mysql_write_frm (lpt=0x7fd8d005c6b0, flags=2) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:1948
#3  0x0000000000ea4e7f in fast_alter_partition_table (thd=0x7fd86c000cf8, table=0x7fd86c01d3b8, alter_info=0x7fd8d005fda0, create_info=0x7fd8d005fe88, table_list=0x7fd86c02aea0, db=0x7fd8d005eab8, table_name=0x7fd8d005eac8) at /home/midenok/src/mariadb/10.5/src/sql/sql_partition.cc:7182
#4  0x00000000009c525f in mysql_alter_table (thd=0x7fd86c000cf8, new_db=0x7fd86c005520, new_name=0x7fd86c0059b0, create_info=0x7fd8d005fe88, table_list=0x7fd86c02aea0, alter_info=0x7fd8d005fda0, order_num=0, order=0x0, ignore=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:9950
#5  0x0000000000a8d238 in Sql_cmd_alter_table::execute (this=0x7fd86c013880, thd=0x7fd86c000cf8) at /home/midenok/src/mariadb/10.5/src/sql/sql_alter.cc:517
#6  0x00000000008bd483 in mysql_execute_command (thd=0x7fd86c000cf8) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:5959
#7  0x00000000008adef6 in mysql_parse (thd=0x7fd86c000cf8, rawbuf=0x7fd86c010a60 "ALTER TABLE t1 DROP PARTITION p1", length=32, parser_state=0x7fd8d00625c0, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:7988

[midenok]

What we do

1. for each FRM: 1.1. Write DDL_LOG_DELETE_ACTION for shadow version; 1.2. Write shadow version.
2. for each FRM: 2.1. Write DDL_LOG_REPLACE_ACTION from backup to normal; 2.2. Rename normal to backup.
3. for each FRM: 3.1. move shadow to normal; 3.2. deactivate DDL_LOG_DELETE_ACTION for shadow.
4. deactivate all DDL_LOG_REPLACE_ACTION (this must be atomic!)
5. for each FRM write DDL_LOG_DELETE_ACTION for backup
6. for each FRM: 6.1 delete backup; 6.2. deactivate DDL_LOG_DELETE_ACTION.

FIXME: In case of error all FRMs must be reverted back!

[midenok]

MDEV-21051

Store Foreign Key metadata outside of InnoDB Store and read foreign key info into/from FRM files

Info

mysql_prepare_alter_table() converts table->s->keys into alter_info->key_list for new FRM, but it doesn't do so for foreign keys:

8441      /*
8442        Collect all keys which isn't in drop list. Add only those
8443        for which some fields exists.
8444      */
8445      for (uint i=0 ; i < table->s->keys ; i++,key_info++)
8446      {
....
8489        for (uint j=0 ; j < key_info->user_defined_key_parts ; j++,key_part++)
8490        {
....
8558          key_parts.push_back(new Key_part_spec(&cfield->field_name,
8559                                                key_part_length),
8560                              thd->mem_root);
8561        }
....
8571        if (key_parts.elements)
8572        {
....
8625          key= new Key(key_type, &tmp_name, &key_create_info,
8626                       MY_TEST(key_info->flags & HA_GENERATED_KEY),
8627                       &key_parts, key_info->option_list, DDL_options());
8628          new_key_list.push_back(key, thd->mem_root);
8629        }

Then it adds new keys including foreign keys:

8041      List_iterator<Key> key_it(alter_info->key_list);
....
8636      {
8637        Key *key;
8638        while ((key=key_it++))                      // Add new keys
8639        {
8640          if (key->foreign)
8641          {
....
8655          }
8656          new_key_list.push_back(key, thd->mem_root);
....
8663        }
8664      }

And that's it for foreign keys!

Subtask

Make same procedure for table->s->foreign_keys like it does for table->s->keys above.

[midenok]

CREATE TABLE

1. mysql_prepare_create_table()

#0  mysql_prepare_create_table (thd=0x7f122c000cf8, create_info=0x7f128c0a5e08, alter_info=0x7f128c0a5d00, db_options=0x7f128c0a47a8, file=0x7f122c09a750, key_info_buffer=0x7f128c0a59a0, foreign_keys=..., key_count=0x7f128c0a599c, create_table_mode=0, table_name=...) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:3461
#1  0x00000000009c37fd in mysql_create_frm_image (thd=0x7f122c000cf8, db=..., table_name=..., create_info=0x7f128c0a5e08, alter_info=0x7f128c0a5d00, create_table_mode=0, key_info=0x7f128c0a59a0, key_count=0x7f128c0a599c, frm=0x7f128c0a5780) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:4844
#2  0x00000000009c4d54 in create_table_impl (thd=0x7f122c000cf8, orig_db=..., orig_table_name=..., db=..., table_name=..., path=0x7f128c0a5790 "./test/ch1", options=..., create_info=0x7f128c0a5e08, alter_info=0x7f128c0a5d00, create_table_mode=0, is_trans=0x7f128c0a5ad7, key_info=0x7f128c0a59a0, key_count=0x7f128c0a599c, frm=0x7f128c0a5780) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:5089
#3  0x00000000009c3fc8 in mysql_create_table_no_lock (thd=0x7f122c000cf8, db=0x7f122c050778, table_name=0x7f122c050788, create_info=0x7f128c0a5e08, alter_info=0x7f128c0a5d00, is_trans=0x7f128c0a5ad7, create_table_mode=0, table_list=0x7f122c050760) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:5193
#4  0x00000000009c5403 in mysql_create_table (thd=0x7f122c000cf8, create_table=0x7f122c050760, create_info=0x7f128c0a5e08, alter_info=0x7f128c0a5d00) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:5285
#5  0x00000000009dcefd in Sql_cmd_create_table_like::execute (this=0x7f122c02d9a0, thd=0x7f122c000cf8) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:11692
#6  0x00000000008c5603 in mysql_execute_command (thd=0x7f122c000cf8) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:5959
#7  0x00000000008b6076 in mysql_parse (thd=0x7f122c000cf8, rawbuf=0x7f122c010dd0 "create or replace table ch1 (\nid int, id2 int,\nforeign key (id) references t1 (id),\nforeign key (id2) references t2 (id),\nforeign key (id) references t3 (id))", length=158, parser_state=0x7f128c0a85c0, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:7988

2. Update referenced shares

#3  0x00000000009dda70 in TABLE_SHARE::fk_process_create (this=0x7f128c0a31d8, thd=0x7f122c000cf8, alter_info=0x7f128c0a5d00, ref_tables=...) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:11800
#4  0x0000000000c82566 in ha_create_table (thd=0x7f122c000cf8, path=0x7f128c0a5790 "./test/ch1", db=0x7f122c027d30 "test", table_name=0x7f122c013640 "ch1", create_info=0x7f128c0a5e08, alter_info=0x7f128c0a5d00, frm=0x7f128c0a5780, fk_update_refs=true) at /home/midenok/src/mariadb/10.5/src/sql/handler.cc:5174
#5  0x00000000009c4e37 in create_table_impl (thd=0x7f122c000cf8, orig_db=..., orig_table_name=..., db=..., table_name=..., path=0x7f128c0a5790 "./test/ch1", options=..., create_info=0x7f128c0a5e08, alter_info=0x7f128c0a5d00, create_table_mode=0, is_trans=0x7f128c0a5ad7, key_info=0x7f128c0a59a0, key_count=0x7f128c0a599c, frm=0x7f128c0a5780) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:5108
#6  0x00000000009c3fc8 in mysql_create_table_no_lock (thd=0x7f122c000cf8, db=0x7f122c050778, table_name=0x7f122c050788, create_info=0x7f128c0a5e08, alter_info=0x7f128c0a5d00, is_trans=0x7f128c0a5ad7, create_table_mode=0, table_list=0x7f122c050760) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:5193
#7  0x00000000009c5403 in mysql_create_table (thd=0x7f122c000cf8, create_table=0x7f122c050760, create_info=0x7f128c0a5e08, alter_info=0x7f128c0a5d00) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:5285
#8  0x00000000009dcefd in Sql_cmd_create_table_like::execute (this=0x7f122c02d9a0, thd=0x7f122c000cf8) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:11692
#9  0x00000000008c5603 in mysql_execute_command (thd=0x7f122c000cf8) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:5959
#10 0x00000000008b6076 in mysql_parse (thd=0x7f122c000cf8, rawbuf=0x7f122c010dd0 "create or replace table ch1 (\nid int, id2 int,\nforeign key (id) references t1 (id),\nforeign key (id2) references t2 (id),\nforeign key (id) references t3 (id))", length=158, parser_state=0x7f128c0a85c0, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:7988

ALTER TABLE

1. mysql_prepare_create_table()

#0  mysql_prepare_create_table (thd=0x7f33e4000cf8, create_info=0x7f3450054e68, alter_info=0x7f3450054d60, db_options=0x7f34500507a8, file=0x7f33e4129e30, key_info_buffer=0x7f3450053168, foreign_keys=..., key_count=0x7f3450053164, create_table_mode=-2, table_name=...) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:3461
#1  0x00000000009c5ffd in mysql_create_frm_image (thd=0x7f33e4000cf8, db=..., table_name=..., create_info=0x7f3450054e68, alter_info=0x7f3450054d60, create_table_mode=-2, key_info=0x7f3450053168, key_count=0x7f3450053164, frm=0x7f3450053150) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:4844
#2  0x00000000009c7554 in create_table_impl (thd=0x7f33e4000cf8, orig_db=..., orig_table_name=..., db=..., table_name=..., path=0x7f34500542d5 "./test/#sql-7bab_3", options=..., create_info=0x7f3450054e68, alter_info=0x7f3450054d60, create_table_mode=-2, is_trans=0x0, key_info=0x7f3450053168, key_count=0x7f3450053164, frm=0x7f3450053150) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:5089
#3  0x00000000009d1da1 in mysql_alter_table (thd=0x7f33e4000cf8, new_db=0x7f33e4005520, new_name=0x7f33e40059b0, create_info=0x7f3450054e68, table_list=0x7f33e40cd3d0, alter_info=0x7f3450054d60, order_num=0, order=0x0, ignore=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:10177
#4  0x0000000000aa3748 in Sql_cmd_alter_table::execute (this=0x7f33e40b0ce0, thd=0x7f33e4000cf8) at /home/midenok/src/mariadb/10.5/src/sql/sql_alter.cc:517
#5  0x00000000008c7643 in mysql_execute_command (thd=0x7f33e4000cf8) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:5959
#6  0x00000000008b80b6 in mysql_parse (thd=0x7f33e4000cf8, rawbuf=0x7f33e40b06a0 "alter table t2 add index(fld2), add foreign key (fld1) references t1(fld1)\non update cascade, algorithm=inplace", length=111, parser_state=0x7f34500575c0, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:7988

2. update shares

#0  Alter_table_ctx::fk_update_shares_and_frms (this=0x7f34500539c8, thd=0x7f33e4000cf8) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:11825
#1  0x00000000009d9774 in mysql_inplace_alter_table (thd=0x7f33e4000cf8, table_list=0x7f33e40cd3d0, table=0x7f33e4130678, altered_table=0x7f3450051ce8, ha_alter_info=0x7f34500530a8, inplace_supported=HA_ALTER_INPLACE_NOCOPY_NO_LOCK, target_mdl_request=0x7f3450053818, alter_ctx=0x7f34500539c8) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:7791
#2  0x00000000009d2349 in mysql_alter_table (thd=0x7f33e4000cf8, new_db=0x7f33e4005520, new_name=0x7f33e40059b0, create_info=0x7f3450054e68, table_list=0x7f33e40cd3d0, alter_info=0x7f3450054d60, order_num=0, order=0x0, ignore=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:10301
#3  0x0000000000aa3748 in Sql_cmd_alter_table::execute (this=0x7f33e40b0ce0, thd=0x7f33e4000cf8) at /home/midenok/src/mariadb/10.5/src/sql/sql_alter.cc:517
#4  0x00000000008c7643 in mysql_execute_command (thd=0x7f33e4000cf8) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:5959
#5  0x00000000008b80b6 in mysql_parse (thd=0x7f33e4000cf8, rawbuf=0x7f33e40b06a0 "alter table t2 add index(fld2), add foreign key (fld1) references t1(fld1)\non update cascade, algorithm=inplace", length=111, parser_state=0x7f34500575c0, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:7988

[midenok]

Bug 1: innodb_gis.point_basic

Reproduce

--source include/have_geometry.inc
--source include/have_innodb.inc

CREATE TABLE t1(f1 GEOMETRY, f2 POINT, f3 GEOMETRY) ENGINE=InnoDB;
SELECT f1 FROM t1 UNION SELECT f1 FROM t1;
INSERT INTO t1 (f2,f3) VALUES (ST_GeomFromText('POINT(1 1)'),
  ST_GeomFromText('POINT(2 2)'));
SELECT ST_AsText(f2),ST_AsText(f3) FROM t1;
SELECT ST_AsText(a) FROM (SELECT f2 AS a FROM t1 UNION SELECT f3 FROM t1) t;
CREATE TABLE t2 AS SELECT f2 AS a FROM t1 UNION SELECT f3 FROM t1;
DESC t2;
SELECT ST_AsText(a) FROM t2;
DROP TABLE t1, t2;

Result

#3  0x00007f764bc74012 in __GI___assert_fail (assertion=0x18d949c "tmp_field->table_name.str != 0", file=0x18d78e7 "/home/midenok/src/mariadb/10.5/src/sql/item.cc", line=6349, function=0x18d94bb "virtual void Item_field::make_send_field(THD *, Send_field *)") at assert.c:101
#4  0x0000000000cb566c in Item_field::make_send_field (this=0x7f75c807a530, thd=0x7f75c8000cf8, tmp_field=0x7f763405f050) at /home/midenok/src/mariadb/10.5/src/sql/item.cc:6349
#5  0x0000000000c68814 in Send_field::Send_field (this=0x7f763405f050, thd=0x7f75c8000cf8, item=0x7f75c807a530) at /home/midenok/src/mariadb/10.5/src/sql/field.cc:10721
#6  0x000000000075ee1c in Protocol_text::store_field_metadata (this=0x7f763405f1b0, thd=0x7f75c8000cf8, item=0x7f75c807a530, pos=0) at /home/midenok/src/mariadb/10.5/src/sql/protocol.cc:993
#7  0x000000000075eada in Protocol::send_result_set_metadata (this=0x7f75c80012c8, list=0x7f75c80053b0, flags=3) at /home/midenok/src/mariadb/10.5/src/sql/protocol.cc:911
#8  0x0000000000837936 in select_send::send_result_set_metadata (this=0x7f75c806f810, list=..., flags=3) at /home/midenok/src/mariadb/10.5/src/sql/sql_class.cc:2992
#9  0x000000000093a0ce in JOIN::exec_inner (this=0x7f75c807e270) at /home/midenok/src/mariadb/10.5/src/sql/sql_select.cc:4447
#10 0x0000000000938f05 in JOIN::exec (this=0x7f75c807e270) at /home/midenok/src/mariadb/10.5/src/sql/sql_select.cc:4232
#11 0x00000000009108b7 in mysql_select (thd=0x7f75c8000cf8, tables=0x7f75c8004c98, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=268435456, result=0x7f75c806f810, unit=0x7f75c8004c50, select_lex=0x7f75c80188d0) at /home/midenok/src/mariadb/10.5/src/sql/sql_select.cc:4656
#12 0x0000000000a08965 in st_select_lex_unit::exec (this=0x7f75c8004c50) at /home/midenok/src/mariadb/10.5/src/sql/sql_union.cc:2319
#13 0x0000000000a04d43 in mysql_union (thd=0x7f75c8000cf8, lex=0x7f75c8004b88, result=0x7f75c806f810, unit=0x7f75c8004c50, setup_tables_done_option=0) at /home/midenok/src/mariadb/10.5/src/sql/sql_union.cc:41
#14 0x000000000090fe5d in handle_select (thd=0x7f75c8000cf8, lex=0x7f75c8004b88, result=0x7f75c806f810, setup_tables_done_option=0) at /home/midenok/src/mariadb/10.5/src/sql/sql_select.cc:406
#15 0x00000000008cd71a in execute_sqlcom_select (thd=0x7f75c8000cf8, all_tables=0x7f75c8012840) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:6219
#16 0x00000000008c32fd in mysql_execute_command (thd=0x7f75c8000cf8) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:3904
#17 0x00000000008bc0b6 in mysql_parse (thd=0x7f75c8000cf8, rawbuf=0x7f75c8010c80 "SELECT f1 FROM t1 UNION SELECT f1 FROM t1", length=41, parser_state=0x7f76340625c0, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:7988

Good

(rr) p *tmp_field
$2 = {
  <Sql_alloc> = {<No data fields>},
  <Type_handler_hybrid_field_type> = {
    m_type_handler = 0x20f0988 <type_handler_double>
  },
  members of Send_field:
  db_name = {
    str = 0x16f64cf "/home/midenok/src/mariadb/10.5b/src/sql/protocol.cc",
    length = 1204738326539
  },
  table_name = {
    str = 0x7fb9300a62a0 ",\005r\001",
    length = 8
  },
  org_table_name = {
    str = 0x7fb8bc0066c0 "\310\325\a\274\270\177",
    length = 140431405340104
  },
  col_name = {
    str = 0x7fb8bc0804f0 "\245\245\245\245\245\245\245\245", '\217' <repeats 4264 times>,
    length = 1911268107864
  },
  org_col_name = {
    str = 0x7fb9300a61f0 "@c\n0\271\177",
    length = 8
  },
  length = 0,
  flags = 3154121640,
  decimals = 32696
}

table_name value looks strange anyway.

Cause

--- a/sql/sql_string.h
+++ b/sql/sql_string.h
@@ -213,13 +213,18 @@ class Static_binary_string : public Sql_alloc

   LEX_STRING lex_string() const
   {
-    LEX_STRING str = { (char*) ptr(), length() };
+    LEX_STRING str = { (char*) (length() ? ptr() : NULL), length() };
     return str;
   }
   LEX_CSTRING lex_cstring() const
   {
-    LEX_CSTRING skr = { ptr(), length() };
-    return skr;
+    LEX_CSTRING str = { (length() ? ptr() : NULL), length() };
+    return str;
+  }
+  LEX_CUSTRING lex_custring() const
+  {
+    LEX_CUSTRING str= { (const uchar *) (length() ? ptr() : NULL), length() };
+    return str;
   }

   bool has_8bit_bytes() const

Some code (probably wrongly) depends on non-null preallocated ptr() with zero length.

Fix

Revert back this change and make workaround.

[midenok]

Bug 2: foreign key check on removing backup table

#0  row_drop_table_for_mysql (name=0x7fe3040a0a30 "test/#sql2-52e1-3", trx=0x7fe307400248, sqlcom=SQLCOM_ALTER_TABLE, create_failed=false, nonatomic=true) at /home/midenok/src/mariadb/10.5/src/storage/innobase/row/row0mysql.cc:3477
#1  0x00000000010a5ff1 in ha_innobase::delete_table (this=0x7fe2c4133530, name=0x7fe3040a2410 "./test/#sql2-52e1-3", sqlcom=SQLCOM_ALTER_TABLE) at /home/midenok/src/mariadb/10.5/src/storage/innobase/handler/ha_innodb.cc:13551
#2  0x00000000010870f6 in ha_innobase::delete_table (this=0x7fe2c4133530, name=0x7fe3040a2410 "./test/#sql2-52e1-3") at /home/midenok/src/mariadb/10.5/src/storage/innobase/handler/ha_innodb.cc:13676
#3  0x0000000000c49411 in handler::ha_delete_table (this=0x7fe2c4133530, name=0x7fe3040a2410 "./test/#sql2-52e1-3") at /home/midenok/src/mariadb/10.5/src/sql/handler.cc:4684
#4  0x0000000000c49194 in ha_delete_table (thd=0x7fe2c4000cf8, table_type=0x48637a8, path=0x7fe3040a2410 "./test/#sql2-52e1-3", db=0x7fe3040a4958, alias=0x7fe3040a2b40, generate_warning=false) at /home/midenok/src/mariadb/10.5/src/sql/handler.cc:2577
#5  0x0000000000989686 in quick_rm_table (thd=0x7fe2c4000cf8, base=0x48637a8, db=0x7fe3040a4958, table_name=0x7fe3040a2b40, flags=3, table_path=0x0) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:2788
#6  0x000000000099a184 in mysql_alter_table (thd=0x7fe2c4000cf8, new_db=0x7fe2c4005520, new_name=0x7fe2c40059b0, create_info=0x7fe3040a5e68, table_list=0x7fe2c40e8670, alter_info=0x7fe3040a5d60, order_num=0, order=0x0, ignore=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:10648
#7  0x0000000000a6f8f8 in Sql_cmd_alter_table::execute (this=0x7fe2c40fb4c0, thd=0x7fe2c4000cf8) at /home/midenok/src/mariadb/10.5/src/sql/sql_alter.cc:517
#8  0x000000000088d963 in mysql_execute_command (thd=0x7fe2c4000cf8) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:5959
#9  0x000000000087e3d6 in mysql_parse (thd=0x7fe2c4000cf8, rawbuf=0x7fe2c40df210 "alter table t1 add foreign key (id2) references t1 (id)", length=55, parser_state=0x7fe3040a85c0, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:7988
#10 0x000000000087b028 in dispatch_command (command=COM_QUERY, thd=0x7fe2c4000cf8, packet=0x7fe2c4007b29 "", packet_length=55, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:1845

Tests failed

mtr.log.gz

Typical tests

mtrz main.stat_tables_par \
main.foreign_key \
innodb.foreign_key \
innodb.foreign-keys \
innodb.innodb-fk \
innodb.innodb-fk-warnings \
innodb.innodb \
innodb.innodb-alter \
innodb_gis.point_basic \
main.query_cache_innodb \
main.information_schema_inno \
main.alter_table \
mroonga/storage.foreign_key_alter_add \
mroonga/storage.foreign_key_rename

Bug 3

element->share set to NULL

#0  0x0000000000ba225c in tdc_delete_share_from_hash (element=0x7f5d5008be48) at /home/midenok/src/mariadb/10.5b/src/sql/table_cache.cc:516
#1  0x0000000000ba39e5 in tdc_release_share (share=0x7f5d50099330) at /home/midenok/src/mariadb/10.5b/src/sql/table_cache.cc:993
#2  0x0000000000ba4546 in tdc_remove_table (thd=0x7f5d50000d38, remove_type=TDC_RT_REMOVE_ALL, db=0x7f5d50014cf0 "test", table_name=0x7f5d500145f0 "t1") at /home/midenok/src/mariadb/10.5b/src/sql/table_cache.cc:1150
#3  0x00000000009cb914 in mysql_rm_table_no_locks (thd=0x7f5d50000d38, tables=0x7f5d50014630, if_exists=false, drop_temporary=false, drop_view=false, drop_sequence=false, drop_db=false, dont_log_query=false, dont_free_locks=false) at /home/midenok/src/mariadb/10.5b/src/sql/sql_table.cc:2495
#4  0x00000000009ca63a in mysql_rm_table (thd=0x7f5d50000d38, tables=0x7f5d50014630, if_exists=false, drop_temporary=false, drop_sequence=false) at /home/midenok/src/mariadb/10.5b/src/sql/sql_table.cc:2133
#5  0x00000000008ce718 in mysql_execute_command (thd=0x7f5d50000d38) at /home/midenok/src/mariadb/10.5b/src/sql/sql_parse.cc:4875
#6  0x00000000008c35b6 in mysql_parse (thd=0x7f5d50000d38, rawbuf=0x7f5d50014570 "DROP TABLE t1", length=13, parser_state=0x7f5d6c1185c0, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/10.5b/src/sql/sql_parse.cc:7988

[midenok]

ER_DROP_INDEX_FK

--source include/have_innodb.inc

create table t1 (id int(11) not null, id2 int(11) not null, unique (id,id2)) engine=innodb;
create table t2 (id int(11) not null, constraint t1_id_fk foreign key ( id ) references t1 (id)) engine = innodb;
show create table t1;
show create table t2;
create index id on t2 (id);
show create table t2;
create index id2 on t2 (id);
show create table t2;
drop index id2 on t2;
--error ER_DROP_INDEX_FK
drop index id on t2;
show create table t2;
drop table t2, t1;

#0  innobase_find_equiv_index (col_names=0x7fd55402a1f0, n_cols=1, keys=0x7fd55405e700, add=...) at /home/midenok/src/mariadb/10.5/src/storage/innobase/handler/handler0alter.cc:2683
#1  0x0000000001103442 in innobase_check_foreign_key_index (ha_alter_info=0x7fd5c00f4b38, index=0x7fd554019040, indexed_table=0x7fd554028430, col_names=0x0, trx=0x7fd5d19b2130, drop_fk=0x0, n_drop_fk=0) at /home/midenok/src/mariadb/10.5/src/storage/innobase/handler/handler0alter.cc:7110
#2  0x0000000001100409 in ha_innobase::prepare_inplace_alter_table (this=0x7fd554041b40, altered_table=0x7fd5c00f3778, ha_alter_info=0x7fd5c00f4b38) at /home/midenok/src/mariadb/10.5/src/storage/innobase/handler/handler0alter.cc:7778
#3  0x0000000000c941d1 in handler::ha_prepare_inplace_alter_table (this=0x7fd554041b40, altered_table=0x7fd5c00f3778, ha_alter_info=0x7fd5c00f4b38) at /home/midenok/src/mariadb/10.5/src/sql/handler.cc:4537
#4  0x00000000009e2a26 in mysql_inplace_alter_table (thd=0x7fd554000cf8, table_list=0x7fd5540145b0, table=0x7fd554031268, altered_table=0x7fd5c00f3778, ha_alter_info=0x7fd5c00f4b38, inplace_supported=HA_ALTER_INPLACE_NOCOPY_NO_LOCK, target_mdl_request=0x7fd5c00f52a8, alter_ctx=0x7fd5c00f5458) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:7807
#5  0x00000000009db306 in mysql_alter_table (thd=0x7fd554000cf8, new_db=0x7fd5540145c8, new_name=0x7fd5540145d8, create_info=0x7fd5c00f7180, table_list=0x7fd5540145b0, alter_info=0x7fd5c00f7078, order_num=0, order=0x0, ignore=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:10457
#6  0x00000000008c707b in mysql_execute_command (thd=0x7fd554000cf8) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:4158
#7  0x00000000008bef46 in mysql_parse (thd=0x7fd554000cf8, rawbuf=0x7fd55401ae10 "drop index id on t2", length=19, parser_state=0x7fd5c00f85c0, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:7988

Current tests: innodb innodb-alter

ID added on instant ALTER

#0  dict_create_add_foreign_id (id_nr=0x7f14740efdd8, name=0x7f1410013390 "test/t1", foreign=0x7f1410081ed0) at /home/midenok/src/mariadb/10.5/src/storage/innobase/include/dict0crea.ic:45
#1  0x000000000111fbed in innobase_update_foreign_try (ctx=0x7f14100820d0, trx=0x7f147e8e2248, table_name=0x7f1410016a65 "t1") at /home/midenok/src/mariadb/10.5/src/storage/innobase/handler/handler0alter.cc:9374
#2  0x000000000112e299 in commit_try_norebuild (ha_alter_info=0x7f14740f3fc8, ctx=0x7f14100820d0, altered_table=0x7f14740f2c08, old_table=0x7f1410029ac8, trx=0x7f147e8e2248, table_name=0x7f1410016a65 "t1") at /home/midenok/src/mariadb/10.5/src/storage/innobase/handler/handler0alter.cc:10011
#3  0x00000000011116d5 in ha_innobase::commit_inplace_alter_table (this=0x7f141002a8d0, altered_table=0x7f14740f2c08, ha_alter_info=0x7f14740f3fc8, commit=true) at /home/midenok/src/mariadb/10.5/src/storage/innobase/handler/handler0alter.cc:10799
#4  0x0000000000c95c7b in handler::ha_commit_inplace_alter_table (this=0x7f141002a8d0, altered_table=0x7f14740f2c08, ha_alter_info=0x7f14740f3fc8, commit=true) at /home/midenok/src/mariadb/10.5/src/sql/handler.cc:4557
#5  0x00000000009e4415 in mysql_inplace_alter_table (thd=0x7f1410000cf8, table_list=0x7f1410075cf0, table=0x7f1410029ac8, altered_table=0x7f14740f2c08, ha_alter_info=0x7f14740f3fc8, inplace_supported=HA_ALTER_INPLACE_INSTANT, target_mdl_request=0x7f14740f4738, alter_ctx=0x7f14740f48e8) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:7899
#6  0x00000000009dc6d8 in mysql_alter_table (thd=0x7f1410000cf8, new_db=0x7f1410005520, new_name=0x7f14100059b0, create_info=0x7f14740f5e58, table_list=0x7f1410075cf0, alter_info=0x7f14740f5d48, order_num=0, order=0x0, ignore=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:10470
#7  0x0000000000ab3d18 in Sql_cmd_alter_table::execute (this=0x7f141001f680, thd=0x7f1410000cf8) at /home/midenok/src/mariadb/10.5/src/sql/sql_alter.cc:520
#8  0x00000000008cf4d3 in mysql_execute_command (thd=0x7f1410000cf8) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:5959
#9  0x00000000008bff46 in mysql_parse (thd=0x7f1410000cf8, rawbuf=0x7f1410015b10 "alter table t1 add column b int, add foreign key(a) references t2(a),\nalgorithm=instant", length=87, parser_state=0x7f14740f85c0, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:7988

Info: why create_foreign_keys() is remade via table->s->foreign_keys

Because ID is now generated in prepare_create_table().

Info: 64k extra2 limit tests

innodb.innodb_bug56143 innodb.innodb_bug57904

Important results

• alter_foreign_crash.result
• foreign_key.test
• foreign_key.result
• innodb/foreign_key.result (? see NULL)
• innodb_bug56143.result, .test

Notes

Create table workflow:

1. Foreign_key is constructed in parser, placed into alter_info->key_list;
2. mysql_prepare_create_table() translates them to FK_info, assigns foreign_id if needed;
3. build_frm_image() writes two FK_info lists into FRM's extra2 section, for referenced keys it stores only table names (hints);
4. init_from_binary_frm_image() parses extra2 section and fills foreign_keys and referenced_keys of TABLE_SHARE.

It restores referenced_keys by reading hint list of table names, opening corresponding shares and restoring FK_info from their foreign_keys. Hints resolution is done only when initializing non-temporary shares. Usually temporary share has different (temporary) name and it is impossible to resolve foreign keys by that name (as we identify them by both foreign and referenced table names). Another not unimportant reason is performance: this saves spare share acquisitions.

Alter table workflow:

1. Foreign_key is constructed in parser, placed into alter_info->key_list;
2. mysql_prepare_alter_table() prepares action lists and share list of foreigns/references;
3. mysql_prepare_alter_table() locks list of foreigns/references by MDL_INTENTION_EXCLUSIVE, acquires shares;
4. prepare_create_table() converts key_list into FK_list, assigns foreign_id;
5. shadow FRM of altered table is created;
6. data is copied;
7. altered table is locked by MDL_EXCLUSIVE;
8. fk_handle_alter() processes action lists, creates FK backups, modifies shares, writes shadow FRMs;
9. altered table is closed;
10. shadow FRMs are installed;
11. altered table is renamed, FRM backup deleted;
12. (TBD in MDEV-21053) shadow FRMs installation log closed, backups deleted;

On FK backup system:

In case of failed DDL operation all shares that was modified must be restored into original state. This is done by FK_ddl_backup (CREATE, DROP), FK_rename_backup (RENAME), FK_alter_backup (ALTER).

On STL usage:

STL is used for utility not performance-critical algorithms, core structures hold native List. A wrapper was made to convert STL exception into bool error status or NULL value.

[midenok]

Crash safety ^^ vv

Info: how partition alter works

DDL_LOG_ENTRY vs DDL_LOG_MEMORY_ENTRY

DDL_LOG_MEMORY_ENTRY is container of indexes into log file. I.e. entry_pos is entry number in file! The actual data which is stored is in global_ddl_log.file_entry_buf which is serialization of DDL_LOG_ENTRY. IO_SIZE is serialized record size.

DDL_LOG_MEMORY_ENTRY is doubly linked list. It is also a singly linked list of "active entries". Doubly linked list is global and used in global_ddl_log.first_used, global_ddl_log.first_free. "active entries" list is thread-local and used to release entries (put them from first_used list to first_free list).

Log write algorithm

Cycle of:

1. write_ddl_log_entry(); 1.1. Fill DDL_LOG_ENTRY, action_type is DDL_LOG_DELETE_ACTION f.ex.; 1.2. set_global_from_ddl_log_entry(): global_ddl_log.file_entry_buf is set to 1.1; 1.3. get_free_ddl_log_entry(): get free entry in file, this is returned as active_entry; 1.4. write to that active_entry in file; 1.5. 1. can be cycled too (i.e. multiple entries can be written before 2.), remember first entry returned by 1.3 and pass it to 2.
2. write_execute_ddl_log_entry(complete= false) 2.1 fill global_ddl_log.file_entry_buf with DDL_LOG_EXECUTE_CODE entry, set DDL_LOG_NEXT_ENTRY_POS to entry from 1.5 (first entry); 2.2. get_free_ddl_log_entry() if needed (or it overrides old entry), this is returned as active_entry; 2.3. write_ddl_log_file_entry() to active_entry from 2.2.; 2.4. sync_ddl_log_no_lock().
3. set_part_info_exec_log_entry(exec_log_entry) 3.1 just store entry from 2.2. to part_info->exec_log_entry, it is then overridden by 2. from next cycle and by 4. on finish; 3.2. next_active_log_entry of exec entry is always NULL, it is freed separately.

On finish:

4. write_execute_ddl_log_entry(complete= true); Same as 2., but overrides with DDL_IGNORE_LOG_ENTRY_CODE.
5. write_log_completed() 5.1. release_part_info_log_entries(): cycle through "active entries" and add them to the beginning of global_ddl_log.first_free list. 5.2. Do the same for part_info->exec_log_entry.

[midenok]

Tests with DROP TABLE failures

main.partition_not_blackhole main.partition_myisam

[midenok]

High level design

Dictionary

• Old state: state before DDL command;
• New state: state expected after DDL command;
• Current table: table being modified by DDL command;
• Ref table: parent or child table in relation to current table.
• Normal frm: old FRM of ref table corresponding to its old TABLE_SHARE
• Backup frm: renamed normal frm;
• Shadow frm: new FRM for ref table with updated FK info.

Algorithm

0. Get info, lock ref tables;
1. for each ref table: modify TABLE_SHARE;
2. for each ref table: 2.1. Write DDL_LOG_DELETE_ACTION for shadow frm; 2.2. Write shadow frm.
3. Modify current table and its frm by ongoing DDL;
4. for each ref table: 4.1. Write DDL_LOG_REPLACE_ACTION from backup to normal frm; 4.2. Rename normal to backup frm.
5. for each ref table: 5.1. move shadow to normal frm; 5.2. deactivate DDL_LOG_DELETE_ACTION for shadow.
6. for each ref table: deactivate DDL_LOG_REPLACE_ACTION
7. for each ref table: write DDL_LOG_DELETE_ACTION for backup
8. for each ref table: 8.1. delete backup frm; 8.2. deactivate DDL_LOG_DELETE_ACTION for backup frm.

Failure analysis

If failure happens at 0-3: the DDL fails, current table and ref tables restored to old state. If failure happens at 4: operation continues, 5-8 are executed for non-failed ref tables. If failure happens at 5: revert 4 for failed ref and continue (excluding failed tables). If failure happens at 6: drop normal, revert 4 and continue (excluding failed tables). If failure happens at 7-8: just ignore (possibly print warning).

Failure at 0-3 results with error message. Current and ref tables are at old state; Failures at 4-6 result with success and warning info about failed ref tables. Failed ref tables contain old data about foreign keys and can be fixed by REPAIR TABLE. Failures at 7-8 result with success and possibly some stale files and warnings about them

Crash analysis

If crash happens before 3: everything is at its old state; If crash happens before 6: current table is new, but ref tables are old; If crash happens at 6: current table is new, some ref tables are new, some ref tables are old; If crash happens after 6: current table is new, ref tables are new.

Low level design

p.2 is realized by fk_write_shadow_frm() which is done by:

• TABLE_SHARE::fk_handle_create()
• Alter_table_ctx::fk_handle_alter()
• fk_handle_drop()
• fk_handle_rename()

pp.4-8 are realized by fk_safe_install_shadows().

TABLE_SHARE::fk_handle_create()

Is done by ha_create_table() after share is inited. It does:

1. Collect and locks parent tables;
2. Acquire and updates parent shares;
3. Write shadow frms.

fk_safe_install_shadows() is done by ha_create_table() after closefrm().

Alter_table_ctx::fk_handle_alter()

Is done by:

• mysql_alter_table() after wait_while_table_is_used() (TDC is cleared);
• mysql_inplace_alter_table() after wait_while_table_is_used()

It does:

1. Upgrade ref locks to MDL_EXCLUSIVE (they are locked earlier by mysql_prepare_alter_table());
2. Update ref shares according to collected operations;
3. Write shadow frms.

fk_safe_install_shadows() is done by:

• mysql_alter_table() before deleting the backup of the old table;
• mysql_inplace_alter_table() after close_all_tables_for_name().

fk_handle_rename()

Is done by:

• do_rename() before tdc_remove_table();
• simple_rename_or_index_change() after wait_while_table_is_used() (TDC is cleared).

The difference in order here is because of different roles of TDC clear in these cases. For do_rename() it is only and final TDC clear, but fk_handle_rename() acquires share again.

It does:

1. Collect ref tables;
2. Upgrade old table to MDL_EXCLUSIVE;
3. Lock ref tables;
4. Acquire and update ref shares;
5. Write shadow frms.

fk_safe_install_shadows() is done by:

• mysql_rename_tables() after succesful rename_tables();
• simple_rename_or_index_change() after succesful mysql_rename_table() and rename_table_in_stat_tables().

fk_handle_drop()

Is done by mysql_rm_table_no_locks() before tdc_remove_table() (by the same reason as for do_rename()).

It does:

1. Collect and locks ref tables;
2. Acquire and updates ref shares;
3. Write shadow frms.

fk_safe_install_shadows() is done by mysql_rm_table_no_locks() after succesful drop of triggers.

[midenok]

Typical tests

mtrz main.stat_tables_par \
main.foreign_key \
innodb.foreign_key \
innodb.foreign-keys \
innodb.innodb-fk \
innodb.innodb-fk-warnings \
innodb.innodb \
innodb.innodb-alter \
innodb.alter_algorithm \
innodb.foreign_key_debug \
innodb.innodb_bug57255 \
innodb.instant_alter_index_rename \
innodb_gis.point_basic \
main.query_cache_innodb \
main.information_schema_inno \
main.alter_table \
mroonga/storage.foreign_key_alter_add \
mroonga/storage.foreign_key_rename

Bug: circular reference self-lock

Affected test: innodb.alter_algorithm

Reproduce

--source include/have_innodb.inc

create table t1(f1 int primary key, f2 int not null,
        f3 int as (f2 * f2) virtual,
        f4 int not null unique,
        f5 int not null,
        index idx(f2))engine=innodb;

create table t2(f1 int not null, f2 int not null,
        index(f1),
        foreign key fidx(f1) references t1(f1))engine=innodb;

insert into t1(f1, f2, f4, f5) values(1, 2, 3, 4);

--enable_info
alter table t1 add index idx1(f4), page_compressed=1;

alter table t1 drop index idx, page_compression_level=5;

alter table t1 add unique index u1(f2);

alter table t1 drop index f4, page_compression_level=9;

set foreign_key_checks = 0;
alter table t1 add foreign key(f5) references t2(f1);

drop table t2, t1;
--disable_info

create or replace database test;

Result

#2  0x0000000000b97c16 in inline_mysql_mutex_lock (that=0x7fffb89a6fe0, src_file=0x1800c90 "/home/midenok/src/mariadb/10.5/src/sql/table_cache.cc", src_line=875) at /home/midenok/src/mariadb/10.5/src/include/mysql/psi/mysql_thread.h:748
#3  0x0000000000b9abff in tdc_acquire_share (thd=0x7fffb8000d48, tl=0x7ffff0886df0, flags=1, out_table=0x0) at /home/midenok/src/mariadb/10.5/src/sql/table_cache.cc:875
#4  0x0000000000b9c4b2 in Share_acquire::Share_acquire (this=0x7ffff0886de8, thd=0x7fffb8000d48, tl=...) at /home/midenok/src/mariadb/10.5/src/sql/table_cache.cc:1302
#5  0x0000000000a5b807 in Foreign_key_io::parse (this=0x7ffff0888190, thd=0x7fffb8000d48, s=0x7fffb89a10e0, image=...) at /home/midenok/src/mariadb/10.5/src/sql/unireg.cc:1370
#6  0x0000000000a2ac14 in TABLE_SHARE::init_from_binary_frm_image (this=0x7fffb89a10e0, thd=0x7fffb8000d48, write=false, frm_image=0x7fffb899ce48 "\376\001\v\fk", frm_length=2609, par_image=0x0, par_length=0) at /home/midenok/src/mariadb/10.5/src/sql/table.cc:3083
#7  0x0000000000a24311 in open_table_def (thd=0x7fffb8000d48, share=0x7fffb89a10e0, flags=9) at /home/midenok/src/mariadb/10.5/src/sql/table.cc:696
#8  0x0000000000b9a63e in tdc_acquire_share (thd=0x7fffb8000d48, tl=0x7ffff0888e40, flags=1, out_table=0x0) at /home/midenok/src/mariadb/10.5/src/sql/table_cache.cc:831
#9  0x0000000000b9c4b2 in Share_acquire::Share_acquire (this=0x7ffff0888e38, thd=0x7fffb8000d48, tl=...) at /home/midenok/src/mariadb/10.5/src/sql/table_cache.cc:1302
#10 0x0000000000a5b807 in Foreign_key_io::parse (this=0x7ffff088a1e0, thd=0x7fffb8000d48, s=0x7fffb89a73b0, image=...) at /home/midenok/src/mariadb/10.5/src/sql/unireg.cc:1370
#11 0x0000000000a2ac14 in TABLE_SHARE::init_from_binary_frm_image (this=0x7fffb89a73b0, thd=0x7fffb8000d48, write=false, frm_image=0x7fffb89a5dc8 "\376\001\n\f5", frm_length=981, par_image=0x0, par_length=0) at /home/midenok/src/mariadb/10.5/src/sql/table.cc:3083
#12 0x0000000000a24311 in open_table_def (thd=0x7fffb8000d48, share=0x7fffb89a73b0, flags=9) at /home/midenok/src/mariadb/10.5/src/sql/table.cc:696
#13 0x0000000000b9a63e in tdc_acquire_share (thd=0x7fffb8000d48, tl=0x7fffb8014b38, flags=1, out_table=0x0) at /home/midenok/src/mariadb/10.5/src/sql/table_cache.cc:831
#14 0x0000000000b9c4b2 in Share_acquire::Share_acquire (this=0x7ffff088b710, thd=0x7fffb8000d48, tl=...) at /home/midenok/src/mariadb/10.5/src/sql/table_cache.cc:1302
#15 0x00000000009b7e85 in fk_handle_drop (thd=0x7fffb8000d48, table=0x7fffb8014b38, shares=..., drop_db=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:12952
#16 0x00000000009b60d7 in mysql_rm_table_no_locks (thd=0x7fffb8000d48, tables=0x7fffb8014b38, if_exists=false, drop_temporary=false, drop_view=false, drop_sequence=false, drop_db=false, dont_log_query=false, dont_free_locks=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:2468
#17 0x00000000009b52df in mysql_rm_table (thd=0x7fffb8000d48, tables=0x7fffb8014b38, if_exists=false, drop_temporary=false, drop_sequence=false, dont_log_query=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:2155
#18 0x00000000008b5d26 in mysql_execute_command (thd=0x7fffb8000d48) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:4929
#19 0x00000000008aa7a6 in mysql_parse (thd=0x7fffb8000d48, rawbuf=0x7fffb8014a70 "DROP TABLE t2, t1", length=17, parser_state=0x7ffff088e590, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:7995

frame 15

(gdb) p table->table_name.str
$9 = 0x7fffb8014af8 "t2"

frame 10

(gdb) p tl.table_name.str
$10 = 0x7fffb899a178 "t1"

frame 5

(gdb) p tl.table_name.str
$11 = 0x7fffb899e508 "t2"

Fix

Store info into thd->fk_circular_check.

[midenok]

Bug: innodb-wl5522-1 fails

Result

--- /home/midenok/src/mariadb/10.5/src/mysql-test/suite/innodb/r/innodb-wl5522-1.result 2020-06-25 06:06:03.114373051 +0300
+++ /home/midenok/src/mariadb/10.5/src/mysql-test/suite/innodb/r/innodb-wl5522-1.reject 2020-07-01 18:22:48.812917660 +0300
@@ -266,6 +266,8 @@
 restore: t1_fk .ibd and .cfg files
 ALTER TABLE testdb_wl5522.t1 IMPORT TABLESPACE;
 ALTER TABLE testdb_wl5522.t1_fk IMPORT TABLESPACE;
+Warnings:
+Warning        1814    Tablespace has been discarded for table `t1_fk`
 SELECT * FROM testdb_wl5522.t1;
 col_1_int      col_2_varchar
 1      a1

Cause

t1_fk table closed

#0  intern_close_table (table=0x7ff1c8a700e8) at /home/midenok/src/mariadb/10.5/src/sql/table_cache.cc:218
#1  0x0000000000b9be1f in TDC_element::flush_unused (this=0x7ff1c8a57988, mark_flushed=true) at /home/midenok/src/mariadb/10.5/src/sql/table_cache.cc:1292
#2  0x0000000000b9c9c6 in Share_acquire::~Share_acquire (this=0x7ff2180f3ac0) at /home/midenok/src/mariadb/10.5/src/sql/table_cache.cc:1300
#3  0x0000000000a5be74 in Foreign_key_io::parse (this=0x7ff2180f4f10, thd=0x7ff1c8000d48, s=0x7ff1c8b94050, image=...) at /home/midenok/src/mariadb/10.5/src/sql/unireg.cc:1429
#4  0x0000000000a2ac34 in TABLE_SHARE::init_from_binary_frm_image (this=0x7ff1c8b94050, thd=0x7ff1c8000d48, write=false, frm_image=0x7ff1c8a39e18 "\376\001\n\f+", frm_length=1006, par_image=0x0, par_length=0) at /home/midenok/src/mariadb/10.5/src/sql/table.cc:3084
#5  0x0000000000a2432e in open_table_def (thd=0x7ff1c8000d48, share=0x7ff1c8b94050, flags=11) at /home/midenok/src/mariadb/10.5/src/sql/table.cc:697
#6  0x0000000000b9ab9e in tdc_acquire_share (thd=0x7ff1c8000d48, tl=0x7ff1c8014bb0, flags=3, out_table=0x7ff2180f5c48) at /home/midenok/src/mariadb/10.5/src/sql/table_cache.cc:831
#7  0x00000000007e8d80 in open_table (thd=0x7ff1c8000d48, table_list=0x7ff1c8014bb0, ot_ctx=0x7ff2180f5f88) at /home/midenok/src/mariadb/10.5/src/sql/sql_base.cc:1840
#8  0x00000000007ee5d7 in open_and_process_table (thd=0x7ff1c8000d48, tables=0x7ff1c8014bb0, counter=0x7ff2180f608c, flags=0, prelocking_strategy=0x7ff2180f6168, has_prelocking_list=false, ot_ctx=0x7ff2180f5f88) at /home/midenok/src/mariadb/10.5/src/sql/sql_base.cc:3784
#9  0x00000000007ecf20 in open_tables (thd=0x7ff1c8000d48, options=..., start=0x7ff2180f60a0, counter=0x7ff2180f608c, flags=0, prelocking_strategy=0x7ff2180f6168) at /home/midenok/src/mariadb/10.5/src/sql/sql_base.cc:4256
#10 0x00000000007f19c5 in open_and_lock_tables (thd=0x7ff1c8000d48, options=..., tables=0x7ff1c8014bb0, derived=false, flags=0, prelocking_strategy=0x7ff2180f6168) at /home/midenok/src/mariadb/10.5/src/sql/sql_base.cc:5152
#11 0x00000000009c5bf1 in open_and_lock_tables (thd=0x7ff1c8000d48, tables=0x7ff1c8014bb0, derived=false, flags=0, prelocking_strategy=0x7ff2180f6168) at /home/midenok/src/mariadb/10.5/src/sql/sql_base.h:276
#12 0x00000000009c57c7 in mysql_discard_or_import_tablespace (thd=0x7ff1c8000d48, table_list=0x7ff1c8014bb0, discard=true) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:6323
#13 0x0000000000aa936c in Sql_cmd_discard_import_tablespace::execute (this=0x7ff1c8015280, thd=0x7ff1c8000d48) at /home/midenok/src/mariadb/10.5/src/sql/sql_alter.cc:577
#14 0x00000000008b9b9c in mysql_execute_command (thd=0x7ff1c8000d48) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:5953
#15 0x00000000008aa7d6 in mysql_parse (thd=0x7ff1c8000d48, rawbuf=0x7ff1c8014a90 "ALTER TABLE testdb_wl5522.t1 DISCARD TABLESPACE", length=47, parser_state=0x7ff2180f8590, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:7995

Fix

Flush unused only for RENAME.

[midenok]

InnoDB refactoring

row_rename_table_for_mysql()

Tests affected:

mtrz main.alter_table \
innodb.innodb \
innodb.foreign_key \
innodb.innodb-fk

#0  row_rename_table_for_mysql (old_name=0x7f3f4005d670 "test/#sql-6f24_8", new_name=0x7f3f4005d870 "test/t1", trx=0x7f3f3bc01248, commit=true, use_fk=true) at /home/midenok/src/mariadb/trunk/src/storage/innobase/row/row0mysql.cc:4461
#1  0x0000000000e6ebd5 in innobase_rename_table (trx=0x7f3f3bc01248, from=0x7f3f4005ecf0 "./test/#sql-6f24_8", to=0x7f3f4005eae0 "./test/t1", commit=true, use_fk=true) at /home/midenok/src/mariadb/trunk/src/storage/innobase/handler/ha_innodb.cc:13654
#2  0x0000000000e51391 in ha_innobase::rename_table (this=0x7f3ed40169e0, from=0x7f3f4005ecf0 "./test/#sql-6f24_8", to=0x7f3f4005eae0 "./test/t1") at /home/midenok/src/mariadb/trunk/src/storage/innobase/handler/ha_innodb.cc:13846
#3  0x0000000000bbfb0f in handler::ha_rename_table (this=0x7f3ed40169e0, from=0x7f3f4005ecf0 "./test/#sql-6f24_8", to=0x7f3f4005eae0 "./test/t1") at /home/midenok/src/mariadb/trunk/src/sql/handler.cc:4688
#4  0x000000000093d150 in mysql_rename_table (base=0x45943c8, old_db=0x7f3f40061148, old_name=0x7f3f40061178, new_db=0x7f3f40061148, new_name=0x7f3f40061168, flags=1) at /home/midenok/src/mariadb/trunk/src/sql/sql_table.cc:5533
#5  0x0000000000946080 in mysql_alter_table (thd=0x7f3ed4000cf8, new_db=0x7f3ed40054b8, new_name=0x7f3ed40058c0, create_info=0x7f3f40062490, table_list=0x7f3ed4014188, alter_info=0x7f3f400623d8, order_num=0, order=0x0, ignore=true) at /home/midenok/src/mariadb/trunk/src/sql/sql_table.cc:10273
#6  0x00000000009f6566 in Sql_cmd_alter_table::execute (this=0x7f3ed4014a58, thd=0x7f3ed4000cf8) at /home/midenok/src/mariadb/trunk/src/sql/sql_alter.cc:503
#7  0x000000000084702a in mysql_execute_command (thd=0x7f3ed4000cf8) at /home/midenok/src/mariadb/trunk/src/sql/sql_parse.cc:6099
#8  0x0000000000838390 in mysql_parse (thd=0x7f3ed4000cf8, rawbuf=0x7f3ed4014060 "alter ignore table t1 add foreign key (f3) references t1 (f1)", length=61, parser_state=0x7f3f400655e0, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/trunk/src/sql/sql_parse.cc:7909

4393                    err = que_eval_sql(
4394                            info,
4395                            "PROCEDURE RENAME_CONSTRAINT_IDS () IS\n"
4396                            "gen_constr_prefix CHAR;\n"
4397                            "new_db_name CHAR;\n"
4398                            "foreign_id CHAR;\n"
4399                            "new_foreign_id CHAR;\n"
4400                            "old_db_name_len INT;\n"
4401                            "old_t_name_len INT;\n"
4402                            "new_db_name_len INT;\n"
4403                            "id_len INT;\n"
4404                            "offset INT;\n"
4405                            "found INT;\n"
4406                            "BEGIN\n"
4407                            "found := 1;\n"
4408                            "old_db_name_len := INSTR(:old_table_name, '/')-1;\n"
4409                            "new_db_name_len := INSTR(:new_table_name, '/')-1;\n"
4410                            "new_db_name := SUBSTR(:new_table_name, 0,\n"
4411                            "                      new_db_name_len);\n"
4412                            "old_t_name_len := LENGTH(:old_table_name);\n"
4413                            "gen_constr_prefix := CONCAT(:old_table_name_utf8,\n"
4414                            "                            '_ibfk_');\n"
4415                            "WHILE found = 1 LOOP\n"
4416                            "       SELECT ID INTO foreign_id\n"
4417                            "        FROM SYS_FOREIGN\n"
4418                            "        WHERE FOR_NAME = :old_table_name\n"
4419                            "         AND TO_BINARY(FOR_NAME)\n"
4420                            "           = TO_BINARY(:old_table_name)\n"
4421                            "         LOCK IN SHARE MODE;\n"
4422                            "       IF (SQL % NOTFOUND) THEN\n"
4423                            "        found := 0;\n"
4424                            "       ELSE\n"
4425                            "        UPDATE SYS_FOREIGN\n"
4426                            "        SET FOR_NAME = :new_table_name\n"
4427                            "         WHERE ID = foreign_id;\n"
4428                            "        id_len := LENGTH(foreign_id);\n"
4429                            "        IF (INSTR(foreign_id, '/') > 0) THEN\n"
4430                            "               IF (INSTR(foreign_id,\n"
4431                            "                         gen_constr_prefix) > 0)\n"
4432                            "               THEN\n"
4433                            "                offset := INSTR(foreign_id, '_ibfk_') - 1;\n"
4434                            "                new_foreign_id :=\n"
4435                            "                CONCAT(:new_table_utf8,\n"
4436                            "                SUBSTR(foreign_id, offset,\n"
4437                            "                       id_len - offset));\n"
4438                            "               ELSE\n"
4439                            "                new_foreign_id :=\n"
4440                            "                CONCAT(new_db_name,\n"
4441                            "                SUBSTR(foreign_id,\n"
4442                            "                       old_db_name_len,\n"
4443                            "                       id_len - old_db_name_len));\n"
4444                            "               END IF;\n"
4445                            "               UPDATE SYS_FOREIGN\n"
4446                            "                SET ID = new_foreign_id\n"
4447                            "                WHERE ID = foreign_id;\n"
4448                            "               UPDATE SYS_FOREIGN_COLS\n"
4449                            "                SET ID = new_foreign_id\n"
4450                            "                WHERE ID = foreign_id;\n"
4451                            "        END IF;\n"
4452                            "       END IF;\n"
4453                            "END LOOP;\n"
4454                            "UPDATE SYS_FOREIGN SET REF_NAME = :new_table_name\n"
4455                            "WHERE REF_NAME = :old_table_name\n"
4456                            "  AND TO_BINARY(REF_NAME)\n"
4457                            "    = TO_BINARY(:old_table_name);\n"
4458                            "END;\n"
4459                            , FALSE, trx);

Info

/** Find an open table in the list of prelocked tabled

  Used for foreign key actions, for example, in UPDATE t1 SET a=1;
  where a child table t2 has a KB on t1.a.

  But only when virtual columns are involved, otherwise InnoDB
  does not need an open TABLE.
*/
TABLE *find_fk_open_table(THD *thd, const char *db, size_t db_len,
                       const char *table, size_t table_len)
{
  for (TABLE *t= thd->open_tables; t; t= t->next)
  {
    if (t->s->db.length == db_len && t->s->table_name.length == table_len &&
        !strcmp(t->s->db.str, db) && !strcmp(t->s->table_name.str, table) &&
        t->pos_in_table_list->prelocking_placeholder == TABLE_LIST::PRELOCK_FK)
      return t;
  }
  return NULL;
}

/** Find or open a table handle for the virtual column template
@param[in]  thd thread handle
@param[in,out]  table   InnoDB table whose virtual column template
            is to be updated
@return table handle
@retval NULL if the table is dropped, unaccessible or corrupted
for purge thread */
static TABLE* innodb_find_table_for_vc(THD* thd, dict_table_t* table)
{
...
    mysql_table = find_fk_open_table(thd, db_buf, db_buf_len,
                     tbl_buf, tbl_buf_len);
    table->vc_templ->mysql_table = mysql_table;
    table->vc_templ->mysql_table_query_id = thd_get_query_id(thd);
    return mysql_table;
}

[midenok]

Bug: fail create parent if child has different key

Reproduce

--source include/have_innodb.inc

set foreign_key_checks=0;
create table t2 (a int primary key, b int, foreign key (b) references t1(a)) engine = innodb;
# Embedded server doesn't chdir to data directory
--replace_result $MYSQLTEST_VARDIR . master-data/ ''
--error ER_CANT_CREATE_TABLE
create table t1(a char(10) primary key, b varchar(20)) engine = innodb;
set foreign_key_checks=1;
drop table t2;

Result

Creating t1 succeeds. Expected: ER_CANT_CREATE_TABLE

Notes

Currently InnoDB code checks this by dict_load_foreigns() in create_table_info_t::create_table(). dict_foreign_add_to_cache() fails because it checks indexes of foreign table. Now dict_load_foreigns() gets data from TABLE_SHARE which has no referenced keys yet.

Fix

This check can't be done now as there is no centralized database of foreign keys from where we can figure out references. Fail instead on INSERT into t2 (when foreign_key_checks is on). This already works, nothing to fix.

[midenok]

Bug: parent table without referenced keys

When parent table is created after child table (with help of foreign_key_checks) it does not contain referenced keys. Due that inserting into child table fails.

Related tests: innodb.innodb

Reproduce

--source include/have_innodb.inc

set foreign_key_checks=0;
create table t2 (a int primary key, b int, foreign key (b) references t1(a)) engine = innodb;
set foreign_key_checks=1;
create table t1 (a int primary key) engine = innodb;
insert into t1 values (1);
insert into t2 values (1, 1);
drop tables t2, t1;

Result

mysqltest: At line 8: query 'insert into t2 values (1, 1)' failed: 1452: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fk_t2` FOREIGN KEY (`b`) REFERENCES `t1` (`a`))

Fix

On table open load shares of referenced tables and update them. Issue warning. Table repair should fix its FRM.

Info

static bool auto_repair_table(THD *thd, TABLE_LIST *table_list)
{
  TABLE_SHARE *share;
  TABLE entry;
  bool result= TRUE;

  thd->clear_error();

  if (!(share= tdc_acquire_share(thd, table_list, GTS_TABLE)))
    return result;

  DBUG_ASSERT(! share->is_view);

  if (open_table_from_share(thd, share, &table_list->alias,
                            HA_OPEN_KEYFILE | HA_TRY_READ_ONLY,
                            EXTRA_RECORD,
                            ha_open_options | HA_OPEN_FOR_REPAIR,
                            &entry, FALSE) || ! entry.file ||
      (entry.file->is_crashed() && entry.file->ha_check_and_repair(thd)))
  {
    /* Give right error message */
    thd->clear_error();
    my_error(ER_NOT_KEYFILE, MYF(0), share->table_name.str);
    sql_print_error("Couldn't repair table: %s.%s", share->db.str,
                    share->table_name.str);
    if (entry.file)
      closefrm(&entry);
  }
  else
  {
    thd->clear_error();         // Clear error message
    closefrm(&entry);
    result= FALSE;
  }

  tdc_remove_referenced_share(thd, share);
  return result;
}

bool open_table(THD *thd, TABLE_LIST *table_list, Open_table_context *ot_ctx)
{
...
    error= open_table_from_share(thd, share, &table_list->alias,
                                 HA_OPEN_KEYFILE | HA_TRY_READ_ONLY,
                                 EXTRA_RECORD,
                                 thd->open_options, table, FALSE,
                                 IF_PARTITIONING(table_list->partition_names,0));

    if (unlikely(error))
    {
      my_free(table);

      if (error == OPEN_FRM_DISCOVER)
        (void) ot_ctx->request_backoff_action(Open_table_context::OT_DISCOVER,
                                              table_list);
      else if (share->crashed)
      {
        if (!(flags & MYSQL_OPEN_IGNORE_REPAIR))
          (void) ot_ctx->request_backoff_action(Open_table_context::OT_REPAIR,
                                                table_list);
        else
          table_list->crashed= 1;  /* Mark that table was crashed */
      }
      goto err_lock;
    }

Related info: https://github.com/midenok/mariadb/issues/53#issuecomment-615442367

[midenok]

Info: fk prelocking

bool DML_prelocking_strategy::
handle_table(THD *thd, Query_tables_list *prelocking_ctx,
             TABLE_LIST *table_list, bool *need_prelocking)
{
...
    if (table->s->referenced_by_foreign_key())
    {
      List_iterator<FK_info> fk_list_it(table->s->referenced_keys);
      FK_info *fk;
      Query_arena *arena, backup;

      arena= thd->activate_stmt_arena_if_needed(&backup);
      *need_prelocking= TRUE;

      while ((fk= fk_list_it++))
      {
        // FK_OPTION_RESTRICT and FK_OPTION_NO_ACTION only need read access
        uint8 op= table_list->trg_event_map;
        thr_lock_type lock_type;

        if ((op & (1 << TRG_EVENT_DELETE) && fk_modifies_child(fk->delete_method))
         || (op & (1 << TRG_EVENT_UPDATE) && fk_modifies_child(fk->update_method))
         || (table->s->table_category == TABLE_CATEGORY_SYSTEM &&
             table_list->lock_type >= TL_WRITE_ALLOW_WRITE))
          lock_type= TL_WRITE_ALLOW_WRITE;
        else
          lock_type= TL_READ;

        if (table_already_fk_prelocked(prelocking_ctx->query_tables,
                                       &fk->foreign_db, &fk->foreign_table,
                                       lock_type))
          continue;

        TABLE_LIST *tl= (TABLE_LIST *) thd->alloc(sizeof(TABLE_LIST));
        tl->init_one_table_for_prelocking(&fk->foreign_db,
                                          &fk->foreign_table,
                                          NULL, lock_type,
                                          TABLE_LIST::PRELOCK_FK,
                                          table_list->belong_to_view, op,
                                          &prelocking_ctx->query_tables_last,
                                          table_list->for_insert_data);
      }
      if (arena)
        thd->restore_active_arena(arena, &backup);
    }

Currently only parent table prelocks children. We have to make child table prelock parent to initiate its open.

[midenok]

Good: same foreign added to foreing and referenced table

Reproduce

--source include/have_innodb.inc

create table t1 (id int primary key) engine=innodb;
create table t2 (v int, constraint c1 foreign key (v) references t1(id)) engine=innodb;
insert into t1 values(1);
insert into t2 values(1);

set foreign_key_checks=0;
drop table t1;
set foreign_key_checks=1;
drop table t2;

t2 foreign_set increased

#0  create_table_info_t::create_foreign_keys (this=0x7fd6800edc80) at /home/midenok/src/mariadb/10.5b/src/storage/innobase/handler/ha_innodb.cc:12570
#1  0x0000000001159aa8 in create_table_info_t::create_table (this=0x7fd6800edc80, create_fk=true) at /home/midenok/src/mariadb/10.5b/src/storage/innobase/handler/ha_innodb.cc:12701
#2  0x00000000011791dc in ha_innobase::create (this=0x7fd63802d1d0, name=0x7fd6800f08d0 "./test/t2", form=0x7fd6800eead0, create_info=0x7fd6800f0f28, file_per_table=true, trx=0x7fd683400380) at /home/midenok/src/mariadb/10.5b/src/storage/innobase/handler/ha_innodb.cc:13149
#3  0x000000000115afef in ha_innobase::create (this=0x7fd63802d1d0, name=0x7fd6800f08d0 "./test/t2", form=0x7fd6800eead0, create_info=0x7fd6800f0f28) at /home/midenok/src/mariadb/10.5b/src/storage/innobase/handler/ha_innodb.cc:13202
#4  0x0000000000c9e451 in handler::ha_create (this=0x7fd63802d1d0, name=0x7fd6800f08d0 "./test/t2", form=0x7fd6800eead0, info_arg=0x7fd6800f0f28) at /home/midenok/src/mariadb/10.5b/src/sql/handler.cc:5083
#5  0x0000000000ca00b7 in ha_create_table (thd=0x7fd638000d48, path=0x7fd6800f08d0 "./test/t2", db=0x7fd6380152d8 "test", table_name=0x7fd638014bc8 "t2", create_info=0x7fd6800f0f28, alter_info=0x7fd6800f0e38, frm=0x7fd6800f08c0, fk_update_refs=true) at /home/midenok/src/mariadb/10.5b/src/sql/handler.cc:5554
#6  0x00000000009bd938 in create_table_impl (thd=0x7fd638000d48, orig_db=..., orig_table_name=..., db=..., table_name=..., new_name=..., path=0x7fd6800f08d0 "./test/t2", options=..., create_info=0x7fd6800f0f28, alter_info=0x7fd6800f0e38, create_table_mode=0, is_trans=0x7fd6800f0c27, key_info=0x7fd6800f0ae8, key_count=0x7fd6800f0ae4, foreign_keys=..., referenced_keys=..., frm=0x7fd6800f08c0) at /home/midenok/src/mariadb/10.5b/src/sql/sql_table.cc:5395
#7  0x00000000009bca23 in mysql_create_table_no_lock (thd=0x7fd638000d48, db=0x7fd638014c20, table_name=0x7fd638014c30, create_info=0x7fd6800f0f28, alter_info=0x7fd6800f0e38, is_trans=0x7fd6800f0c27, create_table_mode=0, table_list=0x7fd638014c08) at /home/midenok/src/mariadb/10.5b/src/sql/sql_table.cc:5481
#8  0x00000000009bdf03 in mysql_create_table (thd=0x7fd638000d48, create_table=0x7fd638014c08, create_info=0x7fd6800f0f28, alter_info=0x7fd6800f0e38) at /home/midenok/src/mariadb/10.5b/src/sql/sql_table.cc:5575
#9  0x00000000009db173 in Sql_cmd_create_table_like::execute (this=0x7fd638014ba0, thd=0x7fd638000d48) at /home/midenok/src/mariadb/10.5b/src/sql/sql_table.cc:12374
#10 0x00000000008b9b9c in mysql_execute_command (thd=0x7fd638000d48) at /home/midenok/src/mariadb/10.5b/src/sql/sql_parse.cc:5953
#11 0x00000000008aa7d6 in mysql_parse (thd=0x7fd638000d48, rawbuf=0x7fd638014a90 "create table t2 (v int, constraint c1 foreign key (v) references t1(id)) engine=innodb", length=86, parser_state=0x7fd6800f3590, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/10.5b/src/sql/sql_parse.cc:7995

12570                   table->foreign_set.insert(local_fk_set.begin(),
12571                                             local_fk_set.end());

(rr) p table
$28 = (dict_table_t *) 0x7fd63802da28
(rr) p table->name.m_name
$29 = 0x7fd638014568 "test/t2"

t1 referenced_set increased

#0  dict_foreign_add_to_referenced_table::operator() (this=0x7fd6800eb070, foreign=0x7fd63802ee38) at /home/midenok/src/mariadb/10.5b/src/storage/innobase/include/dict0mem.h:2455
#1  0x0000000001175c2f in std::for_each<std::_Rb_tree_const_iterator<dict_foreign_t*>, dict_foreign_add_to_referenced_table> (__first=0x7fd63802ee38, __last=0x1, __f=...) at /usr/bin/../lib/gcc/x86_64-linux-gnu/8/../../../../include/c++/8/bits/stl_algo.h:3882
#2  0x0000000001158110 in create_table_info_t::create_foreign_keys (this=0x7fd6800edc80) at /home/midenok/src/mariadb/10.5b/src/storage/innobase/handler/ha_innodb.cc:12572

2450    struct dict_foreign_add_to_referenced_table {
2451            void operator()(dict_foreign_t* foreign) const
2452            {
2453                    if (dict_table_t* table = foreign->referenced_table) {
2454                            std::pair<dict_foreign_set::iterator, bool>     ret
2455                                    = table->referenced_set.insert(foreign);
2456                            ut_a(ret.second);
2457                    }
2458            }
2459    };

(rr) p foreign
$26 = (dict_foreign_t *) 0x7fd63802ee38
(rr) p table
$24 = (dict_table_t *) 0x7fd63801cd58
(rr) p table->name.m_name
$25 = 0x7fd6380072e8 "test/t1"

frame 3

12570                   table->foreign_set.insert(local_fk_set.begin(),
12571                                             local_fk_set.end());
12572                   std::for_each(local_fk_set.begin(), local_fk_set.end(),
12573                                 dict_foreign_add_to_referenced_table());
12574                   local_fk_set.clear();

(rr) p table
$22 = (dict_table_t *) 0x7fd63802da28
(rr) p table->name.m_name
$23 = 0x7fd638014568 "test/t2"

Referenced table dropped

#0  0x000000000151ae90 in dict_sys_t::remove (this=0x2013250 <dict_sys>, table=0x7fd63801cd58, lru=false, keep=false) at /home/midenok/src/mariadb/10.5b/src/storage/innobase/dict/dict0dict.cc:1934
#1  0x0000000001348400 in row_drop_table_from_cache (tablename=0x7fd63801b8b8 "test/t1", table=0x7fd63801cd58, trx=0x7fd683400380) at /home/midenok/src/mariadb/10.5b/src/storage/innobase/row/row0mysql.cc:3300
#2  0x0000000001344b7f in row_drop_table_for_mysql (name=0x7fd6800eee30 "test/t1", trx=0x7fd683400380, sqlcom=SQLCOM_DROP_TABLE, create_failed=false, nonatomic=true) at /home/midenok/src/mariadb/10.5b/src/storage/innobase/row/row0mysql.cc:3745
#3  0x00000000011798b1 in ha_innobase::delete_table (this=0x7fd638015468, name=0x7fd6800f0e90 "./test/t1", sqlcom=SQLCOM_DROP_TABLE) at /home/midenok/src/mariadb/10.5b/src/storage/innobase/handler/ha_innodb.cc:13431
#4  0x000000000115bd16 in ha_innobase::delete_table (this=0x7fd638015468, name=0x7fd6800f0e90 "./test/t1") at /home/midenok/src/mariadb/10.5b/src/storage/innobase/handler/ha_innodb.cc:13558
#5  0x0000000000c93a9a in handler::ha_delete_table (this=0x7fd638015468, name=0x7fd6800f0e90 "./test/t1") at /home/midenok/src/mariadb/10.5b/src/sql/handler.cc:4965
#6  0x0000000000c93776 in ha_delete_table (thd=0x7fd638000d48, table_type=0x2f91b58, path=0x7fd6800f0e90 "./test/t1", db=0x7fd6800f0cf0, alias=0x7fd638014b78, generate_warning=true) at /home/midenok/src/mariadb/10.5b/src/sql/handler.cc:2750
#7  0x00000000009b6444 in mysql_rm_table_no_locks (thd=0x7fd638000d48, tables=0x7fd638014b50, if_exists=false, drop_temporary=false, drop_view=false, drop_sequence=false, drop_db=false, dont_log_query=false, dont_free_locks=false) at /home/midenok/src/mariadb/10.5b/src/sql/sql_table.cc:2515
#8  0x00000000009b529f in mysql_rm_table (thd=0x7fd638000d48, tables=0x7fd638014b50, if_exists=false, drop_temporary=false, drop_sequence=false, dont_log_query=false) at /home/midenok/src/mariadb/10.5b/src/sql/sql_table.cc:2155
#9  0x00000000008b5d56 in mysql_execute_command (thd=0x7fd638000d48) at /home/midenok/src/mariadb/10.5b/src/sql/sql_parse.cc:4929
#10 0x00000000008aa7d6 in mysql_parse (thd=0x7fd638000d48, rawbuf=0x7fd638014a90 "drop table t1", length=13, parser_state=0x7fd6800f3590, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/10.5b/src/sql/sql_parse.cc:7995

1912    void dict_sys_t::remove(dict_table_t* table, bool lru, bool keep)
1913    {
....
1922
1923            /* Remove the foreign constraints from the cache */
1924            std::for_each(table->foreign_set.begin(), table->foreign_set.end(),
1925                          dict_foreign_remove_partial());
1926            table->foreign_set.clear();
1927
1928            /* Reset table field in referencing constraints */
1929            for (dict_foreign_set::iterator it = table->referenced_set.begin();
1930                 it != table->referenced_set.end();
1931                 ++it) {
1932
1933                    foreign = *it;
1934                    foreign->referenced_table = NULL;
1935                    foreign->referenced_index = NULL;
1936            }

(rr) p table
$13 = (dict_table_t *) 0x7fd63801cd58
(rr) p table->name
$14 = {
  m_name = 0x7fd6380072e8 "test/t1",
  static part_suffix = "#P#"
}
(rr) p foreign
$15 = (dict_foreign_t *) 0x7fd63802ee38

Foreign table dropped

#0  dict_foreign_remove_partial::operator() (this=0x7fd6800ed7d0, foreign=0x7fd63802ee38) at /home/midenok/src/mariadb/10.5b/src/storage/innobase/dict/dict0dict.cc:1481
#1  0x000000000152f1cf in std::for_each<std::_Rb_tree_const_iterator<dict_foreign_t*>, dict_foreign_remove_partial> (__first=0x7fd63802ee38, __last=0x1, __f=...) at /usr/bin/../lib/gcc/x86_64-linux-gnu/8/../../../../include/c++/8/bits/stl_algo.h:3882
#2  0x000000000151ae24 in dict_sys_t::remove (this=0x2013250 <dict_sys>, table=0x7fd63802da28, lru=false, keep=false) at /home/midenok/src/mariadb/10.5b/src/storage/innobase/dict/dict0dict.cc:1924
#3  0x0000000001348400 in row_drop_table_from_cache (tablename=0x7fd63801b8b8 "test/t2", table=0x7fd63802da28, trx=0x7fd683400380) at /home/midenok/src/mariadb/10.5b/src/storage/innobase/row/row0mysql.cc:3300
#4  0x0000000001344b7f in row_drop_table_for_mysql (name=0x7fd6800eee30 "test/t2", trx=0x7fd683400380, sqlcom=SQLCOM_DROP_TABLE, create_failed=false, nonatomic=true) at /home/midenok/src/mariadb/10.5b/src/storage/innobase/row/row0mysql.cc:3745
#5  0x00000000011798b1 in ha_innobase::delete_table (this=0x7fd638015628, name=0x7fd6800f0e90 "./test/t2", sqlcom=SQLCOM_DROP_TABLE) at /home/midenok/src/mariadb/10.5b/src/storage/innobase/handler/ha_innodb.cc:13431
#6  0x000000000115bd16 in ha_innobase::delete_table (this=0x7fd638015628, name=0x7fd6800f0e90 "./test/t2") at /home/midenok/src/mariadb/10.5b/src/storage/innobase/handler/ha_innodb.cc:13558
#7  0x0000000000c93a9a in handler::ha_delete_table (this=0x7fd638015628, name=0x7fd6800f0e90 "./test/t2") at /home/midenok/src/mariadb/10.5b/src/sql/handler.cc:4965
#8  0x0000000000c93776 in ha_delete_table (thd=0x7fd638000d48, table_type=0x2f91b58, path=0x7fd6800f0e90 "./test/t2", db=0x7fd6800f0cf0, alias=0x7fd638014b78, generate_warning=true) at /home/midenok/src/mariadb/10.5b/src/sql/handler.cc:2750
#9  0x00000000009b6444 in mysql_rm_table_no_locks (thd=0x7fd638000d48, tables=0x7fd638014b50, if_exists=false, drop_temporary=false, drop_view=false, drop_sequence=false, drop_db=false, dont_log_query=false, dont_free_locks=false) at /home/midenok/src/mariadb/10.5b/src/sql/sql_table.cc:2515
#10 0x00000000009b529f in mysql_rm_table (thd=0x7fd638000d48, tables=0x7fd638014b50, if_exists=false, drop_temporary=false, drop_sequence=false, dont_log_query=false) at /home/midenok/src/mariadb/10.5b/src/sql/sql_table.cc:2155
#11 0x00000000008b5d56 in mysql_execute_command (thd=0x7fd638000d48) at /home/midenok/src/mariadb/10.5b/src/sql/sql_parse.cc:4929
#12 0x00000000008aa7d6 in mysql_parse (thd=0x7fd638000d48, rawbuf=0x7fd638014a90 "drop table t2", length=13, parser_state=0x7fd6800f3590, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/10.5b/src/sql/sql_parse.cc:7995

1478    struct dict_foreign_remove_partial
1479    {
1480            void operator()(dict_foreign_t* foreign) {
1481                    dict_table_t*   table = foreign->referenced_table;
1482                    if (table != NULL) {
1483                            table->referenced_set.erase(foreign);
1484                    }
1485                    dict_foreign_free(foreign);
1486            }
1487    };

(rr) p foreign
$12 = (dict_foreign_t *) 0x7fd63802ee38
(rr) p foreign->foreign_table
$18 = (dict_table_t *) 0x7fd63802da28
(rr) p foreign->referenced_table
$19 = (dict_table_t *) 0x0

frame 2

(rr) p table
$7 = (dict_table_t *) 0x7fd63802da28
(rr) p table->name
$8 = {
  m_name = 0x7fd638014568 "test/t2",
  static part_suffix = "#P#"
}

[midenok]

Info: foreign added on inplace alter

1. foreign_key_t inited

#0  innobase_get_foreign_key_info (ha_alter_info=0x7f06b00a5060, table_share=0x7f06649bc1a0, table=0x7f06649b4f78, col_names=0x0, drop_index=0x0, n_drop_index=0, add_fk=0x7f06649d6c68, n_add_fk=0x7f06b00a2828, trx=0x7f06b8a7b1d8, s_cols=0x0) at /home/midenok/src/mariadb/10.5b/src/storage/innobase/handler/handler0alter.cc:2992
#1  0x0000000001193c3c in ha_innobase::prepare_inplace_alter_table (this=0x7f06649c6b60, altered_table=0x7f06b00a3cd8, ha_alter_info=0x7f06b00a5060) at /home/midenok/src/mariadb/10.5b/src/storage/innobase/handler/handler0alter.cc:7959
#2  0x0000000000c9d921 in handler::ha_prepare_inplace_alter_table (this=0x7f06649c6b60, altered_table=0x7f06b00a3cd8, ha_alter_info=0x7f06b00a5060) at /home/midenok/src/mariadb/10.5b/src/sql/handler.cc:4812
#3  0x00000000009d2c36 in mysql_inplace_alter_table (thd=0x7f0664000d48, table_list=0x7f0664014bb8, table=0x7f06649cc048, altered_table=0x7f06b00a3cd8, ha_alter_info=0x7f06b00a5060, inplace_supported=HA_ALTER_INPLACE_INSTANT, target_mdl_request=0x7f06b00a5fd0, alter_ctx=0x7f06b00a5380) at /home/midenok/src/mariadb/10.5b/src/sql/sql_table.cc:8112
#4  0x00000000009cadcb in mysql_alter_table (thd=0x7f0664000d48, new_db=0x7f06640055f0, new_name=0x7f06640059f8, create_info=0x7f06b00a6f88, table_list=0x7f0664014bb8, alter_info=0x7f06b00a6e98, order_num=0, order=0x0, ignore=false, if_exists=false) at /home/midenok/src/mariadb/10.5b/src/sql/sql_table.cc:10919
#5  0x0000000000aa9170 in Sql_cmd_alter_table::execute (this=0x7f0664015440, thd=0x7f0664000d48) at /home/midenok/src/mariadb/10.5b/src/sql/sql_alter.cc:535
#6  0x00000000008b9b9c in mysql_execute_command (thd=0x7f0664000d48) at /home/midenok/src/mariadb/10.5b/src/sql/sql_parse.cc:5953
#7  0x00000000008aa7d6 in mysql_parse (thd=0x7f0664000d48, rawbuf=0x7f0664014a90 "ALTER TABLE t3 ADD FOREIGN KEY fidx(f2) REFERENCES t2(f1)", length=57, parser_state=0x7f06b00a9590, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/10.5b/src/sql/sql_parse.cc:7995

2. table->foreign_set increased

#3  0x0000000001521965 in dict_foreign_add_to_cache (foreign=0x7f06649a65d8, col_names=0x0, check_charsets=true, ignore_err=DICT_ERR_IGNORE_NONE) at /home/midenok/src/mariadb/10.5b/src/storage/innobase/dict/dict0dict.cc:3109
#4  0x000000000153cc5a in dict_load_foreign (id=0x7f06b00a0bb0 "test/fidx", col_names=0x0, check_recursive=false, check_charsets=true, ignore_err=DICT_ERR_IGNORE_NONE, fk_tables=std::deque with 0 elements) at /home/midenok/src/mariadb/10.5b/src/storage/innobase/dict/dict0load.cc:3516
#5  0x000000000153bd28 in dict_load_foreigns (table_name=0x7f066402a2d8 "test/t3", col_names=0x0, check_recursive=false, check_charsets=true, ignore_err=DICT_ERR_IGNORE_NONE, fk_tables=std::deque with 0 elements) at /home/midenok/src/mariadb/10.5b/src/storage/innobase/dict/dict0load.cc:3656
#6  0x00000000011a734e in innobase_update_foreign_cache (ctx=0x7f0664016c98, user_thd=0x7f0664000d48) at /home/midenok/src/mariadb/10.5b/src/storage/innobase/handler/handler0alter.cc:9601
#7  0x00000000011a3b3d in ha_innobase::commit_inplace_alter_table (this=0x7f06649c6b60, altered_table=0x7f06b00a3cd8, ha_alter_info=0x7f06b00a5060, commit=true) at /home/midenok/src/mariadb/10.5b/src/storage/innobase/handler/handler0alter.cc:11040
#8  0x0000000000c9da9b in handler::ha_commit_inplace_alter_table (this=0x7f06649c6b60, altered_table=0x7f06b00a3cd8, ha_alter_info=0x7f06b00a5060, commit=true) at /home/midenok/src/mariadb/10.5b/src/sql/handler.cc:4832
#9  0x00000000009d3235 in mysql_inplace_alter_table (thd=0x7f0664000d48, table_list=0x7f0664014bb8, table=0x7f06649cc048, altered_table=0x7f06b00a3cd8, ha_alter_info=0x7f06b00a5060, inplace_supported=HA_ALTER_INPLACE_INSTANT, target_mdl_request=0x7f06b00a5fd0, alter_ctx=0x7f06b00a5380) at /home/midenok/src/mariadb/10.5b/src/sql/sql_table.cc:8201
#10 0x00000000009cadcb in mysql_alter_table (thd=0x7f0664000d48, new_db=0x7f06640055f0, new_name=0x7f06640059f8, create_info=0x7f06b00a6f88, table_list=0x7f0664014bb8, alter_info=0x7f06b00a6e98, order_num=0, order=0x0, ignore=false, if_exists=false) at /home/midenok/src/mariadb/10.5b/src/sql/sql_table.cc:10919
#11 0x0000000000aa9170 in Sql_cmd_alter_table::execute (this=0x7f0664015440, thd=0x7f0664000d48) at /home/midenok/src/mariadb/10.5b/src/sql/sql_alter.cc:535
#12 0x00000000008b9b9c in mysql_execute_command (thd=0x7f0664000d48) at /home/midenok/src/mariadb/10.5b/src/sql/sql_parse.cc:5953
#13 0x00000000008aa7d6 in mysql_parse (thd=0x7f0664000d48, rawbuf=0x7f0664014a90 "ALTER TABLE t3 ADD FOREIGN KEY fidx(f2) REFERENCES t2(f1)", length=57, parser_state=0x7f06b00a9590, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/10.5b/src/sql/sql_parse.cc:7995

Inplace alter loads updated foreign keys from altered_table share into foreign key cache by passing it to dict_load_foreigns().

[midenok]

Bug: FK column rename loses FK info

Reproduce

--source include/have_innodb.inc

create table t1 (a int not null, b int not null, index idx(a)) engine=innodb;
create table t2 (a int key, b int, index ind(b), foreign key (b) references t1(a) on delete cascade on update cascade) engine=innodb;

--error ER_NO_REFERENCED_ROW_2
insert into t2 values (32, 2);

alter table t1 change a id int;

--error ER_NO_REFERENCED_ROW_2
insert into t2 values (56, 6);

Result

mysqltest: At line 12: query 'insert into t2 values (56, 6)' succeeded - should have failed with errno 1452...

1. Inplace share t1 inited

#0  Foreign_key_io::parse (this=0x7f5cb80f20f0, thd=0x7f5c68000d48, s=0x7f5cb80f3a98, image=...) at /home/midenok/src/mariadb/10.5/src/sql/unireg.cc:1361
#1  0x0000000000a2a384 in TABLE_SHARE::init_from_binary_frm_image (this=0x7f5cb80f3a98, thd=0x7f5c68000d48, write=true, frm_image=0x7f5c68024a48 "\376\001\n\f\037", frm_length=958, par_image=0x0, par_length=0) at /home/midenok/src/mariadb/10.5/src/sql/table.cc:3084
#2  0x00000000009d1988 in create_table_for_inplace_alter (thd=0x7f5c68000d48, alter_ctx=..., frm=0x7f5cb80f5590, share=0x7f5cb80f3a98, table=0x7f5cb80f2cd8) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:10049
#3  0x00000000009c9f61 in mysql_alter_table (thd=0x7f5c68000d48, new_db=0x7f5c680055f0, new_name=0x7f5c680059f8, create_info=0x7f5cb80f5f88, table_list=0x7f5c68014b80, alter_info=0x7f5cb80f5e98, order_num=0, order=0x0, ignore=false, if_exists=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:10851
#4  0x0000000000aa88c0 in Sql_cmd_alter_table::execute (this=0x7f5c68015370, thd=0x7f5c68000d48) at /home/midenok/src/mariadb/10.5/src/sql/sql_alter.cc:535
#5  0x00000000008b923c in mysql_execute_command (thd=0x7f5c68000d48) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:5953
#6  0x00000000008a9e76 in mysql_parse (thd=0x7f5c68000d48, rawbuf=0x7f5c68014a90 "alter table t1 change a id int", length=30, parser_state=0x7f5cb80f8590, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:7995

(rr) p s->table_name.str
$46 = 0x7f5c68014b40 "t1"
(rr) p s->tmp_table
$47 = INTERNAL_TMP_TABLE

Share doesn't resolve referenced_keys because it is tmp_table (we cannot resolve by renamed columns).

2. t2 foreign_set cleared

#0  innobase_rename_column_try (ctx=..., trx=0x7f5cc277c380, table_name=0x7f5c68023e1d "t1", from=0x7f5c68024001 "a", to=0x7f5c68015260 "id") at /home/midenok/src/mariadb/10.5/src/storage/innobase/handler/handler0alter.cc:8932
#1  0x00000000011b24b9 in innobase_rename_columns_try (ha_alter_info=0x7f5cb80f4060, ctx=0x7f5c68016520, table=0x7f5c68910c98, trx=0x7f5cc277c380, table_name=0x7f5c68023e1d "t1") at /home/midenok/src/mariadb/10.5/src/storage/innobase/handler/handler0alter.cc:8974
#2  0x00000000011bd956 in commit_try_rebuild (ha_alter_info=0x7f5cb80f4060, ctx=0x7f5c68016520, altered_table=0x7f5cb80f2cd8, old_table=0x7f5c68910c98, trx=0x7f5cc277c380, table_name=0x7f5c68023e1d "t1") at /home/midenok/src/mariadb/10.5/src/storage/innobase/handler/handler0alter.cc:9659
#3  0x00000000011a322b in ha_innobase::commit_inplace_alter_table (this=0x7f5c68911e90, altered_table=0x7f5cb80f2cd8, ha_alter_info=0x7f5cb80f4060, commit=true) at /home/midenok/src/mariadb/10.5/src/storage/innobase/handler/handler0alter.cc:10689
#4  0x0000000000c9d1eb in handler::ha_commit_inplace_alter_table (this=0x7f5c68911e90, altered_table=0x7f5cb80f2cd8, ha_alter_info=0x7f5cb80f4060, commit=true) at /home/midenok/src/mariadb/10.5/src/sql/handler.cc:4832
#5  0x00000000009d2755 in mysql_inplace_alter_table (thd=0x7f5c68000d48, table_list=0x7f5c68014b80, table=0x7f5c68910c98, altered_table=0x7f5cb80f2cd8, ha_alter_info=0x7f5cb80f4060, inplace_supported=HA_ALTER_INPLACE_COPY_NO_LOCK, target_mdl_request=0x7f5cb80f4fd0, alter_ctx=0x7f5cb80f4380) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:8202
#6  0x00000000009ca2eb in mysql_alter_table (thd=0x7f5c68000d48, new_db=0x7f5c680055f0, new_name=0x7f5c680059f8, create_info=0x7f5cb80f5f88, table_list=0x7f5c68014b80, alter_info=0x7f5cb80f5e98, order_num=0, order=0x0, ignore=false, if_exists=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:10920
#7  0x0000000000aa88c0 in Sql_cmd_alter_table::execute (this=0x7f5c68015370, thd=0x7f5c68000d48) at /home/midenok/src/mariadb/10.5/src/sql/sql_alter.cc:535
#8  0x00000000008b923c in mysql_execute_command (thd=0x7f5c68000d48) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:5953
#9  0x00000000008a9e76 in mysql_parse (thd=0x7f5c68000d48, rawbuf=0x7f5c68014a90 "alter table t1 change a id int", length=30, parser_state=0x7f5cb80f8590, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:7995

8930            /* Reload the foreign key info for instant table too. */
8931            if (ctx.need_rebuild() || ctx.is_instant()) {
8932                    std::for_each(fk_evict.begin(), fk_evict.end(),
8933                                  dict_foreign_remove_from_cache);
8934            }

3. Foreign keys reloaded on inplace commit

#0  dict_load_foreigns (table=0x7f5c68963578, share=0x7f5cb80f3a98, col_names=0x0, check_charsets=true, ignore_err=DICT_ERR_IGNORE_NONE) at /home/midenok/src/mariadb/10.5/src/storage/innobase/handler/ha_innodb.cc:21602
#1  0x00000000011a75e0 in innobase_update_foreign_cache (ctx=0x7f5c68016520, user_thd=0x7f5c68000d48, altered_table=0x7f5cb80f2cd8) at /home/midenok/src/mariadb/10.5/src/storage/innobase/handler/handler0alter.cc:9427
#2  0x00000000011a3d5e in ha_innobase::commit_inplace_alter_table (this=0x7f5c68911e90, altered_table=0x7f5cb80f2cd8, ha_alter_info=0x7f5cb80f4060, commit=true) at /home/midenok/src/mariadb/10.5/src/storage/innobase/handler/handler0alter.cc:10823
#3  0x0000000000c9d1eb in handler::ha_commit_inplace_alter_table (this=0x7f5c68911e90, altered_table=0x7f5cb80f2cd8, ha_alter_info=0x7f5cb80f4060, commit=true) at /home/midenok/src/mariadb/10.5/src/sql/handler.cc:4832
#4  0x00000000009d2755 in mysql_inplace_alter_table (thd=0x7f5c68000d48, table_list=0x7f5c68014b80, table=0x7f5c68910c98, altered_table=0x7f5cb80f2cd8, ha_alter_info=0x7f5cb80f4060, inplace_supported=HA_ALTER_INPLACE_COPY_NO_LOCK, target_mdl_request=0x7f5cb80f4fd0, alter_ctx=0x7f5cb80f4380) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:8202
#5  0x00000000009ca2eb in mysql_alter_table (thd=0x7f5c68000d48, new_db=0x7f5c680055f0, new_name=0x7f5c680059f8, create_info=0x7f5cb80f5f88, table_list=0x7f5c68014b80, alter_info=0x7f5cb80f5e98, order_num=0, order=0x0, ignore=false, if_exists=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:10920
#6  0x0000000000aa88c0 in Sql_cmd_alter_table::execute (this=0x7f5c68015370, thd=0x7f5c68000d48) at /home/midenok/src/mariadb/10.5/src/sql/sql_alter.cc:535
#7  0x00000000008b923c in mysql_execute_command (thd=0x7f5c68000d48) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:5953
#8  0x00000000008a9e76 in mysql_parse (thd=0x7f5c68000d48, rawbuf=0x7f5c68014a90 "alter table t1 change a id int", length=30, parser_state=0x7f5cb80f8590, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:7995

(rr) p share->referenced_keys.elements
$49 = 0

Notes

  // Resolve hints to referenced keys for non-temporary shares
  if (s->tmp_table)
    return false;
  for (uint i= 0; i < rk_count; ++i)
  {
    if (read_string(hint_db, &s->mem_root, p))
      return true;
    if (read_string(hint_table, &s->mem_root, p))
      return true;
    if (s->open_flags & GTS_FK_SHALLOW_HINTS)
    {
      /* For DROP TABLE we don't need full reference resolution. We just need
         to know if anything from the outside references the dropped table. */
      FK_info *dst= new (&s->mem_root) FK_info;
      dst->foreign_db= hint_db;
      dst->foreign_table= hint_table;
      dst->referenced_db= s->db;
      dst->referenced_table= s->table_name;
      if (s->referenced_keys.push_back(dst, &s->mem_root))
      {
        my_error(ER_OUT_OF_RESOURCES, MYF(0));
        return true;
      }
      continue;
    }

Instead of not loading referenced_keys completely we can use GTS_FK_SHALLOW_HINTS branch. Then in dict_load_foreigns() we'll enter here:

    if (share->referenced_keys.elements > table->referenced_set.size())
    {
...
        for (const Table_name &t: tables_missing)
        {
            len= build_normalized_name(buf, sizeof(buf), LEX_STRING_WITH_LEN(t.db), LEX_STRING_WITH_LEN(t.name), 0, false);
            dict_table_t *for_table = dict_table_check_if_in_cache_low(buf);
            if (!for_table)
            {
                // not possible for DML (foreign table is written)
                continue;
            }
            for (dict_foreign_t *fk: for_table->foreign_set)
            {
                // Actually we have to exclude keys not matching current table
                err = dict_foreign_add_to_cache(fk, NULL, false, ignore_err);
                if (err != DB_SUCCESS)
                    return err;
            }
        }
...

But foreign tables cache is cleared, so no luck yet...

[midenok]

MDEV-22180 Planner opens unnecessary tables when updated table is referenced by foreign keys

Reproduce

#!/bin/bash
echo Dropping fktest...
time ( \
mysql -uroot -e "DROP DATABASE IF EXISTS fktest; CREATE DATABASE fktest"
)

mysql -uroot fktest -e "
  DROP TABLE IF EXISTS fkParent; 
  CREATE TABLE fkParent (
      id int(10) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
      textField varchar(10)
  );"

echo $'\n'Spawning children:
time ( \
for n in $(seq 1000); do
    [ $(($n % 100)) -eq 0 ] && echo -n $'\r'$n
    mysql -uroot fktest -e " 
    CREATE TABLE fkChild$n (
      id int(10) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY, 
      fkField$n int(10) unsigned,
      CONSTRAINT FK_$n FOREIGN KEY (fkField$n) REFERENCES fkParent(id)
    );"
done
)

mysql -uroot fktest << EOF
    set profiling= 1;
    explain UPDATE fkParent SET textField="test";
    show status like "opened_table%";
    show profile for query 1;
EOF

Result

Dropping fktest...

real    0m8.810s
user    0m0.006s
sys     0m0.005s

Spawning children:
1000
real    0m24.983s
user    0m4.562s
sys     0m2.147s
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
1       SIMPLE  fkParent        index   NULL    PRIMARY 4       NULL    1
Variable_name   Value
Opened_table_definitions        0
Opened_tables   1
Status  Duration
Starting        0.001831
checking permissions    0.001148
Opening tables  0.021698
After opening tables    0.000107
System lock     0.000980
table lock      0.000487
Opening tables  0.000121
After opening tables    0.000007
System lock     0.000012
table lock      0.000552
Unlocking tables        0.000012
closing tables  0.000033
init for update 0.005155
Query end       0.000007
Commit  0.000009
closing tables  0.000005
Unlocking tables        0.000004
closing tables  0.000735
Starting cleanup        0.000005
Freeing items   0.000012
Updating status 0.000020
Reset for next command  0.000117

Good

Dropping fktest...

real    0m10.289s
user    0m0.011s
sys     0m0.003s

Spawning children:
1000
real    0m27.759s
user    0m8.029s
sys     0m3.106s
id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
1       SIMPLE  fkParent        index   NULL    PRIMARY 4       NULL    1
Variable_name   Value
Opened_table_definitions        1
Opened_tables   1
Status  Duration
Starting        0.000243
checking permissions    0.000056
Opening tables  0.029148
After opening tables    0.000065
System lock     0.000007
table lock      0.000061
Opening tables  0.000067
After opening tables    0.000006
System lock     0.000006
table lock      0.000047
Unlocking tables        0.000008
closing tables  0.000017
init for update 0.000084
Query end       0.000004
Commit  0.000007
closing tables  0.000004
Unlocking tables        0.000003
closing tables  0.000513
Starting cleanup        0.000003
Freeing items   0.000012
Updating status 0.000016
Reset for next command  0.000097

Fix

Nothing to fix. Works good.

[midenok]

Bug

Reproduce

--source include/have_innodb.inc

create table t1 (f1 int primary key) engine=innodb;

let $fk_tables = 30;

--disable_query_log
let $i = $fk_tables;
while ($i)
{
  eval create table fk_$i (f1 int primary key,
          constraint pc$i foreign key (f1) references t1(f1)
          on delete restrict on update restrict) engine=innodb;
  eval insert into t1 values ($i);
  eval insert into fk_$i values ($i);
  dec $i;
}
--enable_query_log

--source include/restart_mysqld.inc

--error ER_ROW_IS_REFERENCED_2
delete from t1 where f1 = 29;
select * from fk_29;
create or replace database test;

Result

mysqltest: At line 23: query 'delete from t1 where f1 = 29' succeeded - should have failed with errno 1451...

Good: error returned

#0  row_ins_foreign_check_on_constraint (thr=0x7f14e013ae58, foreign=0x7f15000143d8, pcur=0x7f152c05e4d0, entry=0x7f14e0078cd0, mtr=0x7f152c05e018) at /home/midenok/src/mariadb/10.5/src/storage/innobase/row/row0ins.cc:1064
#1  0x0000000001303514 in row_ins_check_foreign_constraint (check_ref=0, foreign=0x7f15000143d8, table=0x7f1510010e78, entry=0x7f14e0078cd0, thr=0x7f14e013ae58) at /home/midenok/src/mariadb/10.5/src/storage/innobase/row/row0ins.cc:1802
#2  0x00000000013aae9d in row_upd_check_references_constraints (node=0x7f14e013ab80, pcur=0x7f14e00788e8, table=0x7f1510010e78, index=0x7f1510012ac8, offsets=0x7f152c05e970, thr=0x7f14e013ae58, mtr=0x7f152c05ebd8) at /home/midenok/src/mariadb/10.5/src/storage/innobase/row/row0upd.cc:295
#3  0x00000000013a87d5 in row_upd_del_mark_clust_rec (node=0x7f14e013ab80, index=0x7f1510012ac8, offsets=0x7f152c05e970, thr=0x7f14e013ae58, referenced=1, mtr=0x7f152c05ebd8) at /home/midenok/src/mariadb/10.5/src/storage/innobase/row/row0upd.cc:2697
#4  0x00000000013a79db in row_upd_clust_step (node=0x7f14e013ab80, thr=0x7f14e013ae58) at /home/midenok/src/mariadb/10.5/src/storage/innobase/row/row0upd.cc:2866
#5  0x00000000013a5983 in row_upd (node=0x7f14e013ab80, thr=0x7f14e013ae58) at /home/midenok/src/mariadb/10.5/src/storage/innobase/row/row0upd.cc:2995
#6  0x00000000013a54a3 in row_upd_step (thr=0x7f14e013ae58) at /home/midenok/src/mariadb/10.5/src/storage/innobase/row/row0upd.cc:3139
#7  0x000000000133d78d in row_update_for_mysql (prebuilt=0x7f14e013a428) at /home/midenok/src/mariadb/10.5/src/storage/innobase/row/row0mysql.cc:1888
#8  0x000000000114fb37 in ha_innobase::delete_row (this=0x7f14e013d3f0, record=0x7f14e0139468 "\377\035") at /home/midenok/src/mariadb/10.5/src/storage/innobase/handler/ha_innodb.cc:8655
#9  0x0000000000ca5e0f in handler::ha_delete_row (this=0x7f14e013d3f0, buf=0x7f14e0139468 "\377\035") at /home/midenok/src/mariadb/10.5/src/sql/handler.cc:7285
#10 0x0000000000ec8dbe in TABLE::delete_row (this=0x7f14e013c0b8) at /home/midenok/src/mariadb/10.5/src/sql/sql_delete.cc:277
#11 0x0000000000ec4d71 in mysql_delete (thd=0x7f14e0000d48, table_list=0x7f14e0014b80, conds=0x7f14e0015440, order_list=0x7f14e00057e8, limit=18446744073709551615, options=0, result=0x0) at /home/midenok/src/mariadb/10.5/src/sql/sql_delete.cc:794
#12 0x00000000008b49b1 in mysql_execute_command (thd=0x7f14e0000d48) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:4787
#13 0x00000000008a9e86 in mysql_parse (thd=0x7f14e0000d48, rawbuf=0x7f14e0014a90 "delete from t1 where f1 = 29", length=28, parser_state=0x7f152c062590, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:7995

1056            if (node->is_delete && 0 == (foreign->type
1057                                         & (DICT_FOREIGN_ON_DELETE_CASCADE
1058                                            | DICT_FOREIGN_ON_DELETE_SET_NULL))) {
1059
1060                    row_ins_foreign_report_err("Trying to delete",
1061                                               thr, foreign,
1062                                               btr_pcur_get_rec(pcur), entry);
1063
1064                    DBUG_RETURN(DB_ROW_IS_REFERENCED);
1065            }

(rr) p *foreign
$3 = {
  heap = 0x7f1500014248,
  id = 0x7f1500014458 "test/pc29",
  n_fields = 1,
  type = 48,
  foreign_table_name = 0x7f1500014468 "test/fk_29",
  foreign_table_name_lookup = 0x7f1500014468 "test/fk_29",
  foreign_table = 0x7f1500012428,
  foreign_col_names = 0x7f1500014480,
  referenced_table_name = 0x7f1500014478 "test/t1",
  referenced_table_name_lookup = 0x7f1500014478 "test/t1",
  referenced_table = 0x7f1510010e78,
  referenced_col_names = 0x7f1500014488,
  foreign_index = 0x7f1500014bc8,
  referenced_index = 0x7f1510012ac8,
  v_cols = 0x0
}
(rr) p DICT_FOREIGN_ON_DELETE_CASCADE
$4 = 1
(rr) p DICT_FOREIGN_ON_UPDATE_SET_NULL
$5 = 8
(rr) p DICT_FOREIGN_ON_DELETE_NO_ACTION|DICT_FOREIGN_ON_UPDATE_NO_ACTION
$6 = 48

Fix

diff --git a/sql/table.h b/sql/table.h
index 3e4d47d5701..e10f4675da4 100644
--- a/sql/table.h
+++ b/sql/table.h
@@ -2208,9 +2208,6 @@ struct TABLE_LIST
                 OT_BASE_ONLY);
     belong_to_view= belong_to_view_arg;
     trg_event_map= trg_event_map_arg;
-    /* MDL is enough for read-only FK checks, we don't need the table */
-    if (prelocking_type == PRELOCK_FK && lock_type < TL_WRITE_ALLOW_WRITE)
-      open_strategy= OPEN_STUB;

     **last_ptr= this;
     prev_global= *last_ptr;

We have to load dict_table_t cache for all foreign tables. Loading them trigger update of referenced_set of referenced table.

[midenok]

Tests failed so far

main.tc_heuristic_recover innodb.innodb innodb.innodb-system-table-view innodb.group_commit_crash_no_optimize_thread innodb.table_flags innodb.information_schema_grants innodb.undo_truncate innodb.innodb_bug60049 main.mysql_upgrade

Info: IB worker acquires THD

#0  0x000000000081a155 in THD::THD (this=0x7fa1d40011b8, id=0, is_wsrep_applier=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_class.cc:781
#1  0x000000000082ab08 in create_background_thd () at /home/midenok/src/mariadb/10.5/src/sql/sql_class.cc:4830
#2  0x0000000001140621 in innobase_create_background_thd (name=0x1a22186 "InnoDB purge worker") at /home/midenok/src/mariadb/10.5/src/storage/innobase/handler/ha_innodb.cc:1497
#3  0x00000000013bfed2 in acquire_thd (ctx=0x7fa1dfffeca0) at /home/midenok/src/mariadb/10.5/src/storage/innobase/srv/srv0srv.cc:2145
#4  0x00000000013be335 in purge_worker_callback () at /home/midenok/src/mariadb/10.5/src/storage/innobase/srv/srv0srv.cc:2194
#5  0x0000000001620fab in tpool::task_group::execute (this=0x2a90ae8 <purge_task_group>, t=0x2a90b78 <purge_worker_task>) at /home/midenok/src/mariadb/10.5/src/tpool/task_group.cc:55
#6  0x00000000016214ef in tpool::task::execute (this=0x2a90b78 <purge_worker_task>) at /home/midenok/src/mariadb/10.5/src/tpool/task.cc:47
#7  0x00000000016194a9 in tpool::thread_pool_generic::worker_main (this=0x49c4180, thread_var=0x4a01340) at /home/midenok/src/mariadb/10.5/src/tpool/tpool_generic.cc:518
#8  0x000000000161eea9 in std::__invoke_impl<void, void (tpool::thread_pool_generic::*)(tpool::worker_data*), tpool::thread_pool_generic*, tpool::worker_data*> (__f=@0x7fa1e80034e8: (void (tpool::thread_pool_generic::*)(tpool::thread_pool_generic * const, tpool::worker_data *)) 0x1619410 <tpool::thread_pool_generic::worker_main(tpool::worker_data*)>, __t=@0x7fa1e80034e0: 0x49c4180, __args=@0x7fa1e80034d8: 0x4a01340) at /usr/bin/../lib/gcc/x86_64-linux-gnu/8/../../../../include/c++/8/bits/invoke.h:73

frame 4

2188    static void purge_worker_callback(void*)
2189    {
2190      ut_ad(!current_thd);
2191      ut_ad(!srv_read_only_mode);
2192      ut_ad(srv_force_recovery < SRV_FORCE_NO_BACKGROUND);
2193      void *ctx;
2194      THD *thd= acquire_thd(&ctx);
2195      while (srv_task_execute())
2196        ut_ad(purge_sys.running());
2197      release_thd(thd,ctx);
2198    }

[midenok]

Bug

Reproduce

--source include/have_innodb.inc
--source include/have_debug.inc
--source include/have_log_bin.inc

CREATE TABLE t1(a CHAR(255),
                b CHAR(255),
                c CHAR(255),
                d CHAR(255),
                id INT,
                PRIMARY KEY(id)) ENGINE=InnoDB;
create table t2 like t1;

let $numinserts = 10;
while ($numinserts)
{
  dec $numinserts;
  eval INSERT INTO t2(a, b, c, d, id) VALUES ('a', 'b', 'c', 'd', 1+$numinserts);
}

--enable_reconnect

FLUSH TABLES;

SET binlog_format= mixed;
RESET MASTER;

START TRANSACTION;
insert into t1 select * from t2;

# Write file to make mysql-test-run.pl expect crash
--exec echo "restart" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect

SET SESSION debug_dbug="d,crash_commit_before";

--error 2006,2013
COMMIT;

# Poll the server waiting for it to be back online again.
--source include/wait_until_connected_again.inc

# table and binlog should be in sync.
SELECT * FROM t1 ORDER BY id;
SHOW BINLOG EVENTS LIMIT 4,1;

delete from t1;

DROP TABLE t1;
DROP TABLE t2;

Result

#0  __pthread_kill (threadid=<optimized out>, signo=11) at ../sysdeps/unix/sysv/linux/pthread_kill.c:56
#1  0x00000000016d423b in my_write_core (sig=11) at /home/midenok/src/mariadb/10.5/src/mysys/stacktrace.c:518
#2  0x0000000000c8b84c in handle_fatal_signal (sig=11) at /home/midenok/src/mariadb/10.5/src/sql/signal_handler.cc:330
#3  <signal handler called>
#4  THD::get_stmt_da (this=0x0) at /home/midenok/src/mariadb/10.5/src/sql/sql_class.h:4271
#5  0x0000000000b9c2ce in Share_acquire::acquire (this=0x7ffd9b595d38, thd=0x0, tl=..., flags=0) at /home/midenok/src/mariadb/10.5/src/sql/table_cache.cc:1309
#6  0x0000000001148493 in dict_load_foreigns (table=0x43fb768, share=0x0, col_names=0x0, check_charsets=true, ignore_err=DICT_ERR_IGNORE_RECOVER_LOCK) at /home/midenok/src/mariadb/10.5/src/storage/innobase/handler/ha_innodb.cc:21631
#7  0x0000000001533f20 in dict_load_table_one (name=..., ignore_err=DICT_ERR_IGNORE_RECOVER_LOCK) at /home/midenok/src/mariadb/10.5/src/storage/innobase/dict/dict0load.cc:2863
#8  0x000000000153293f in dict_load_table (name=0x440d7d8 "test/t1", ignore_err=DICT_ERR_IGNORE_RECOVER_LOCK) at /home/midenok/src/mariadb/10.5/src/storage/innobase/dict/dict0load.cc:2588
#9  0x0000000001534ca4 in dict_load_table_on_id (table_id=20, ignore_err=DICT_ERR_IGNORE_RECOVER_LOCK) at /home/midenok/src/mariadb/10.5/src/storage/innobase/dict/dict0load.cc:3015
#10 0x00000000015142cb in dict_table_open_on_id_low (table_id=20, ignore_err=DICT_ERR_IGNORE_RECOVER_LOCK, cached_only=false) at /home/midenok/src/mariadb/10.5/src/storage/innobase/dict/dict0dict.cc:222
#11 0x00000000015141ad in dict_table_open_on_id (table_id=20, dict_locked=false, table_op=DICT_TABLE_OP_LOAD_TABLESPACE, thd=0x0, mdl=0x0) at /home/midenok/src/mariadb/10.5/src/storage/innobase/dict/dict0dict.cc:948
#12 0x0000000001428661 in trx_resurrect_table_locks (trx=0x7f4ef999f1d8, undo=0x441c568) at /home/midenok/src/mariadb/10.5/src/storage/innobase/trx/trx0trx.cc:629
#13 0x0000000001428140 in trx_resurrect (undo=0x441c568, rseg=0x441c258, start_time=1595614279, start_time_micro=175685075589, rows_to_undo=0x7ffd9b598038, is_old_insert=false) at /home/midenok/src/mariadb/10.5/src/storage/innobase/trx/trx0trx.cc:725
#14 0x000000000142780b in trx_lists_init_at_db_start () at /home/midenok/src/mariadb/10.5/src/storage/innobase/trx/trx0trx.cc:786
#15 0x00000000013c60e6 in srv_start (create_new_db=false) at /home/midenok/src/mariadb/10.5/src/storage/innobase/srv/srv0start.cc:1556
#16 0x0000000001165057 in innodb_init (p=0x4211738) at /home/midenok/src/mariadb/10.5/src/storage/innobase/handler/ha_innodb.cc:4000
#17 0x0000000000c8d109 in ha_initialize_handlerton (plugin=0x4149580) at /home/midenok/src/mariadb/10.5/src/sql/handler.cc:624
#18 0x00000000008cad99 in plugin_initialize (tmp_root=0x7ffd9b59b100, plugin=0x4149580, argc=0x21c92d8 <remaining_argc>, argv=0x4024390, options_only=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_plugin.cc:1459
#19 0x00000000008ca594 in plugin_init (argc=0x21c92d8 <remaining_argc>, argv=0x4024390, flags=0) at /home/midenok/src/mariadb/10.5/src/sql/sql_plugin.cc:1752
#20 0x000000000072aaef in init_server_components () at /home/midenok/src/mariadb/10.5/src/sql/mysqld.cc:4909
#21 0x0000000000726ec3 in mysqld_main (argc=162, argv=0x4024390) at /home/midenok/src/mariadb/10.5/src/sql/mysqld.cc:5492
#22 0x0000000000722e32 in main (argc=23, argv=0x7ffd9b59ca68) at /home/midenok/src/mariadb/10.5/src/sql/main.cc:25

frame 5

(gdb) p thd
$1 = (THD *) 0x0

Cause

No current_thd in this context.

Fix

Skip obtaining foreigns?

[midenok]

Info: dict_load_table_one()

Related tests for dict_load_foreings() in dict_load_table_one():

CURRENT_TEST: innodb.innodb-fk
mysqltest: At line 73: query 'delete from t1 where f1 = 29' succeeded - should have failed with errno 1451...

Tests failed so far

mtrz main.tc_heuristic_recover \
innodb.table_flags \
main.mysql_upgrade \
innodb.undo_truncate

main.mysql_upgrade doesn't fail in bb-10.5-midenok-MDEV-16417.

innodb.undo_truncate

--- /home/midenok/src/mariadb/10.5/src/mysql-test/suite/innodb/r/undo_truncate.result   2019-10-19 12:14:06.352591661 +0300
+++ /home/midenok/src/mariadb/10.5/src/mysql-test/suite/innodb/r/undo_truncate.reject   2020-07-26 16:14:38.917467990 +0300
@@ -53,5 +53,11 @@
 drop PROCEDURE populate_t1;
 drop PROCEDURE populate_t2;
 InnoDB         0 transactions not purged
+Timeout in wait_condition.inc for (SELECT variable_value!=@trunc_start
+FROM information_schema.global_status
+WHERE variable_name = 'innodb_undo_truncations')
+Id     User    Host    db      Command Time    State   Info    Progress
+4      root    localhost       test    Query   0       starting        show full processlist   0.000
+Truncation did not happen: '10485760,10485760'
 SET GLOBAL innodb_purge_rseg_truncate_frequency = @save_frequency;
 SET GLOBAL innodb_undo_log_truncate = @save_truncate;

[midenok]

Bug: duplicate id not checked

Reproduce

--source include/have_innodb.inc

create or replace table t1 (id int primary key) engine innodb;
create or replace table t2 (id2 int, constraint c foreign key (id2) references t1 (id)) engine innodb;
--error ER_CANT_CREATE_TABLE
create or replace table t3 (id2 int, constraint c foreign key (id2) references t1 (id)) engine innodb;
drop tables t3, t2, t1;

Result

#2  0x00000000014401d0 in ut_dbg_assertion_failed (expr=0x19d0f89 "ret.second", file=0x19c5e5a "/home/midenok/src/mariadb/10.5/src/storage/innobase/include/dict0mem.h", line=2456) at /home/midenok/src/mariadb/10.5/src/storage/innobase/ut/ut0dbg.cc:60
#3  0x0000000001187f6e in dict_foreign_add_to_referenced_table::operator() (this=0x7f04a8053e20, foreign=0x7f0460a07238) at /home/midenok/src/mariadb/10.5/src/storage/innobase/include/dict0mem.h:2456
#4  0x000000000117656f in std::for_each<std::_Rb_tree_const_iterator<dict_foreign_t*>, dict_foreign_add_to_referenced_table> (_) at /usr/bin/../lib/gcc/x86_64-linux-gnu/8/../../../../include/c++/8/bits/stl_algo.h:3882
#5  0x0000000001158ad9 in create_table_info_t::create_foreign_keys (this=0x7f04a80569c0) at /home/midenok/src/mariadb/10.5/src/storage/innobase/handler/ha_innodb.cc:12566
#6  0x000000000115a2e9 in create_table_info_t::create_table (this=0x7f04a80569c0, create_fk=true) at /home/midenok/src/mariadb/10.5/src/storage/innobase/handler/ha_innodb.cc:12695
#7  0x000000000117997c in ha_innobase::create (this=0x7f04609e6ec0, name=0x7f04a805ad75 "./test/#sql-alter-2989-3", form=0x7f04a8057810, create_info=0x7f04a805bf88, file_per_table=true, trx=0x7f04abc01380) at /home/midenok/src/mariadb/10.5/src/storage/innobase/handler/ha_innodb.cc:13139
#8  0x000000000115b78f in ha_innobase::create (this=0x7f04609e6ec0, name=0x7f04a805ad75 "./test/#sql-alter-2989-3", form=0x7f04a8057810, create_info=0x7f04a805bf88) at /home/midenok/src/mariadb/10.5/src/storage/innobase/handler/ha_innodb.cc:13192
#9  0x0000000000c9db51 in handler::ha_create (this=0x7f04609e6ec0, name=0x7f04a805ad75 "./test/#sql-alter-2989-3", form=0x7f04a8057810, info_arg=0x7f04a805bf88) at /home/midenok/src/mariadb/10.5/src/sql/handler.cc:5083
#10 0x0000000000c9f7b7 in ha_create_table (thd=0x7f0460000d48, path=0x7f04a805ad75 "./test/#sql-alter-2989-3", db=0x7f0460015298 "test", table_name=0x7f0460014b88 "t3", create_info=0x7f04a805bf88, alter_info=0x7f04a805be98, frm=0x7f04a805b590, fk_update_refs=false) at /home/midenok/src/mariadb/10.5/src/sql/handler.cc:5554
#11 0x00000000009ca67d in mysql_alter_table (thd=0x7f0460000d48, new_db=0x7f04600055f0, new_name=0x7f04600059f8, create_info=0x7f04a805bf88, table_list=0x7f0460014bc8, alter_info=0x7f04a805be98, order_num=0, order=0x0, ignore=false, if_exists=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:10982
#12 0x0000000000aa8870 in Sql_cmd_alter_table::execute (this=0x7f0460015450, thd=0x7f0460000d48) at /home/midenok/src/mariadb/10.5/src/sql/sql_alter.cc:535
#13 0x00000000008b921c in mysql_execute_command (thd=0x7f0460000d48) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:5953
#14 0x00000000008a9e56 in mysql_parse (thd=0x7f0460000d48, rawbuf=0x7f0460014a90 "alter table t3 add constraint dc foreign key (a) references t1(a)", length=65, parser_state=0x7f04a805e590, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:7995

Good: DB_DUPLICATE_KEY returned

#0  dict_create_add_foreign_to_dictionary (name=0x7f840c007398 "test/t2", foreign=0x7f840c02ee38, trx=0x7f8458103380) at /home/midenok/src/mariadb/10.5b/src/storage/innobase/dict/dict0crea.cc:1870
#1  0x0000000001510146 in dict_create_add_foreigns_to_dictionary (local_fk_set=std::set with 1 element = {...}, table=0x7f840c02da28, trx=0x7f8458103380) at /home/midenok/src/mariadb/10.5b/src/storage/innobase/dict/dict0crea.cc:2042
#2  0x0000000001158032 in create_table_info_t::create_foreign_keys (this=0x7f8450058c80) at /home/midenok/src/mariadb/10.5b/src/storage/innobase/handler/ha_innodb.cc:12566
#3  0x0000000001159a88 in create_table_info_t::create_table (this=0x7f8450058c80, create_fk=true) at /home/midenok/src/mariadb/10.5b/src/storage/innobase/handler/ha_innodb.cc:12701
#4  0x00000000011791bc in ha_innobase::create (this=0x7f840c02d1d0, name=0x7f845005b8d0 "./test/t2", form=0x7f8450059ad0, create_info=0x7f845005bf28, file_per_table=true, trx=0x7f8458103380) at /home/midenok/src/mariadb/10.5b/src/storage/innobase/handler/ha_innodb.cc:13149
#5  0x000000000115afcf in ha_innobase::create (this=0x7f840c02d1d0, name=0x7f845005b8d0 "./test/t2", form=0x7f8450059ad0, create_info=0x7f845005bf28) at /home/midenok/src/mariadb/10.5b/src/storage/innobase/handler/ha_innodb.cc:13202
#6  0x0000000000c9e431 in handler::ha_create (this=0x7f840c02d1d0, name=0x7f845005b8d0 "./test/t2", form=0x7f8450059ad0, info_arg=0x7f845005bf28) at /home/midenok/src/mariadb/10.5b/src/sql/handler.cc:5083
#7  0x0000000000ca0097 in ha_create_table (thd=0x7f840c000d48, path=0x7f845005b8d0 "./test/t2", db=0x7f840c0152f8 "test", table_name=0x7f840c014be8 "t2", create_info=0x7f845005bf28, alter_info=0x7f845005be38, frm=0x7f845005b8c0, fk_update_refs=true) at /home/midenok/src/mariadb/10.5b/src/sql/handler.cc:5554
#8  0x00000000009bd938 in create_table_impl (thd=0x7f840c000d48, orig_db=..., orig_table_name=..., db=..., table_name=..., new_name=..., path=0x7f845005b8d0 "./test/t2", options=..., create_info=0x7f845005bf28, alter_info=0x7f845005be38, create_table_mode=0, is_trans=0x7f845005bc27, key_info=0x7f845005bae8, key_count=0x7f845005bae4, foreign_keys=..., referenced_keys=..., frm=0x7f845005b8c0) at /home/midenok/src/mariadb/10.5b/src/sql/sql_table.cc:5395
#9  0x00000000009bca23 in mysql_create_table_no_lock (thd=0x7f840c000d48, db=0x7f840c014c40, table_name=0x7f840c014c50, create_info=0x7f845005bf28, alter_info=0x7f845005be38, is_trans=0x7f845005bc27, create_table_mode=0, table_list=0x7f840c014c28) at /home/midenok/src/mariadb/10.5b/src/sql/sql_table.cc:5481
#10 0x00000000009bdf03 in mysql_create_table (thd=0x7f840c000d48, create_table=0x7f840c014c28, create_info=0x7f845005bf28, alter_info=0x7f845005be38) at /home/midenok/src/mariadb/10.5b/src/sql/sql_table.cc:5575
#11 0x00000000009db153 in Sql_cmd_create_table_like::execute (this=0x7f840c014bc0, thd=0x7f840c000d48) at /home/midenok/src/mariadb/10.5b/src/sql/sql_table.cc:12374
#12 0x00000000008b9b9c in mysql_execute_command (thd=0x7f840c000d48) at /home/midenok/src/mariadb/10.5b/src/sql/sql_parse.cc:5953
#13 0x00000000008aa7d6 in mysql_parse (thd=0x7f840c000d48, rawbuf=0x7f840c014a90 "create or replace table t2 (id2 int, constraint c foreign key (id2) references t1 (id)) engine innodb", length=101, parser_state=0x7f845005e590, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/10.5b/src/sql/sql_parse.cc:7995

1862            error = dict_foreign_eval_sql(info,
1863                                          "PROCEDURE P () IS\n"
1864                                          "BEGIN\n"
1865                                          "INSERT INTO SYS_FOREIGN VALUES"
1866                                          "(:id, :for_name, :ref_name, :n_cols);\n"
1867                                          "END;\n"
1868                                          , name, foreign->id, trx);

Fix

Handle this error at sql layer (CREATE TABLE, ALTER TABLE): 8899aa1001f9163a80e27cf88297867fbda2b288

[midenok]

Tests failed so far

innodb.table_flags innodb.undo_truncate main.mysql_upgrade

Description

Refactor dict_load_foreigns() for synchronising TABLE_SHARE foreign data with dict_table_t cache.

Remove a number of routines working with SYS_FOREIGNS and SYS_FOREIGN_COLS. innobase_update_foreign_try() is now used solely for ER_FK_INCORRECT_OPTION check.

Prelock parent tables as well as child tables. This is done for the case when parent table doesn't know about its children when they created before parent with foreign_key_checks=0. Opening the parent table initiates fk_resolve_referenced_keys() which updates its referenced_keys. Due to CREATE TABLE doesn't not know about "illegal" children it can not check for foreign consistency. F.ex. this would succeed:

set foreign_key_checks= 0;
create table child (fk int references parent (id)) engine=innodb;
set foreign_key_checks= 1;

create table parent (id bigint primary key) engine=innodb;

In the above case dict_load_foreigns() deduces which tables are unknown to opened parent (tables_missing) and reloads their foreign data via recursion. Infinite recursion is not possible via test case: a table cannot be "parent after child" and "child before parent" simultaneously. Though infinite recursion is possible via malicously crafted FRM file, there is no protection from that at InnoDB level but there is protection at SQL level: thd->fk_circular_check.

Later though it would not allow DML on child as well as on parent (see innodb.foreign_key MDEV-10083). So this is pretty acceptable: foreign_key_checks is unnormal setting, checking parent on CREATE TABLE would impose all frms scanning which is not acceptable.

ha_innobase::open() then synchronizes these referenced_keys with its referenced_set cache by calling dict_load_foreigns().

Disable self-references on same column. The Bug 12902967 restricted them on some condition of "same column/index" (see innodb_bug12902967.test), though such self-references were not completely disabled (see other self-ref cases changed in this patch). It is not clear why they worked if they are "self-refs on same column/index".

Old description

There about 35 occurrences of {{SYS_FOREIGN}} in InnoDB SQL code and 27 occurrences of {{SYS_FOREIGN_COLS}}.

1. The following 16 functions work with {{SYS_FOREIGN}} and/or {{SYS_FOREIGN_COLS}} and must be dropped or refactored:

• btr_node_ptr_max_size()
• dict_create_or_check_foreign_constraint_tables()
• dict_create_add_foreign_to_dictionary()
• dict_create_add_foreigns_to_dictionary()
• dict_create_add_foreign_field_to_dictionary()
• dict_process_sys_foreign_rec()
• dict_process_sys_foreign_col_rec()
• dict_load_foreign()
• dict_load_foreigns()
• dict_load_foreign_cols()
• innobase_drop_foreign_try()
• innobase_rename_column_try()
• row_drop_table_for_mysql()
• drop_all_foreign_keys_in_db()
• row_delete_constraint_low()
• row_rename_table_for_mysql()

The following functions work with {{FOREIGN_KEY_INFO}}:

• get_foreign_key_info()
• ha_innobase::get_foreign_key_list()
• ha_innobase::get_parent_foreign_key_list()

2. SHOW CREATE InnoDB interface get_foreign_key_create_info() is refactored into SQL layer.

3. Inplace ALTER functions are refactored, such as:

• innobase_get_foreign_key_info()
• innobase_check_foreign_key_index()

[midenok]

main.mysql_upgrade

CURRENT_TEST: main.mysql_upgrade
mysqldump: Got error: 1428: "You can't combine write-locking of system tables with other tables or lock types" when using LOCK TABLES
mysqltest: In included file "./include/load_dump_and_upgrade.inc":
included from /home/midenok/src/mariadb/10.5/src/mysql-test/main/mysql_upgrade.test at line 313:
At line 15: exec of '/home/midenok/src/mariadb/10.5/build/client/mysqldump --defaults-file=/home/midenok/src/mariadb/10.5/build/mysql-test/var/my.cnf --defaults-group-suffix=.1 mysql > /home/midenok/src/mariadb/10.5/build/mysql-test/var/tmp/mysql_database_backup' failed, error: 512, status: 2, errno: 2
Output from before failure:
call mtr.add_suppression("innodb_table_stats has length mismatch in the column name table_name");

Windows failures

main.alter_table

mysqltest: At line 1660: query 'ALTER TABLE ti1 CHANGE COLUMN g h VARCHAR(20)' failed: 1452: Cannot add or update a child row: a foreign key constraint fails (`test`.`#sql-alter-d340-4`, CONSTRAINT `fi1` FOREIGN KEY (`b`) REFERENCES `test\ti2` (`a`))

Reproduce

--source include/have_innodb.inc

create table t1(a int primary key) engine innodb;
create table t2(a int, f int, foreign key (a) references t1(a)) engine innodb;
insert into t1 values (1);
insert into t2 values (1, 1);
alter table t2 change column f f varchar(20);
drop tables t2, t1;

Result

CURRENT_TEST: main.y
mysqltest: At line 7: query 'alter table t2 change column f f varchar(20)' failed: 1452: Cannot add or update a child row: a foreign key constraint fails (`test`.`#sql-alter-ceb4-4`, CONSTRAINT `fk_t2` FOREIGN KEY (`a`) REFERENCES `test\t1` (`a`))

Cause

Wrong usage of build_normalized_name() which used wrong slash for dict_name_t.

Fix

Split dict_get_referenced_table() into dict_table_t::build_name() and dict_get_referenced_table().

[midenok]

innodb.bug_57255

Reproduce

--source include/have_innodb.inc

create table T1(id int primary key) engine=innodb;
create table t2(id int primary key, f1 int references T1(id)) engine=innodb;
select * from T1;
drop table t2;
drop table T1;

Result

mysqltest: At line 10: query 'DROP table T1' failed: 1451: Cannot delete or update a parent row: a foreign key constraint fails

Cause

Wrong char case in fk_install_shadow_frm().

Notes

TABLE_SHARE::write_frm_image() uses normalized_path which is already lowercase.

Info

    const LEX_CSTRING *alias= table_case_name(create_info, table_name);
    length= build_table_filename(path, sizeof(path) - 1, db->str, alias->str, "", 0);

inline const LEX_CSTRING *table_case_name(HA_CREATE_INFO *info, const LEX_CSTRING *name)
{
  return ((lower_case_table_names == 2 && info->alias.str) ? &info->alias : name);
}

But table_name is already lowered case!

inline void table_case_convert(char * name, uint length)
{
  if (lower_case_table_names)
    files_charset_info->casedn(name, length, name, length);
}

Not used.

class Table_ident :public Sql_alloc
{
...
  bool lowercase(MEM_ROOT *mem_root)
  {
    if (db.length)
    {
      db.str= (const char *) memdup_root(mem_root, db.str, db.length + 1);
      if (unlikely(!db.str))
        return true;
      my_casedn_str(system_charset_info, (char *)db.str);
    }
    if (table.length)
    {
      table.str= (const char *) memdup_root(mem_root, table.str, table.length + 1);
      if (unlikely(!table.str))
        return true;
      my_casedn_str(system_charset_info, (char *)table.str);
    }
    return false;
  }

Sidefix: lowercase file names

--- a/sql/sql_table.cc
+++ b/sql/sql_table.cc
@@ -578,9 +578,11 @@ uint build_table_filename(char *buff, size_t bufflen, const char *db,
   }
 #endif
   pos= strxnmov(pos, end - pos, tbbuff, ext, NullS);
+  uint len= (uint)(pos - buff);
+  table_case_convert(buff, len);

   DBUG_PRINT("exit", ("buff: '%s'", buff));
-  DBUG_RETURN((uint)(pos - buff));
+  DBUG_RETURN(len);
 }

Tests failed:

CURRENT_TEST: main.lowercase_table
mysqltest: At line 130: query 'drop database db1' failed: 1010: Error dropping database (can't rmdir './db1', errno: 39 "Directory not empty")

Tip

Reproduce lowercase problem:

mtr --mysqld --lower_case_table_names=1 innodb.bug_57255

[midenok]

create database D;
use D;
create table T1(id int primary key) engine=innodb;
use test;
create table t2(id int primary key, f1 int references D.T1(id)) engine=innodb;
select * from D.T1;
drop table t2;
flush tables;
drop table D.T1;
drop database D;

Fix

Lowercase table names before locking/acquiring. Before Table_name is placed into container call lowercase() if needed.

TODO

Lowercase file names on fk_write_shadow_frm(), fk_install_shadow_frm().

[midenok]

innodb.truncate_foreign

mysqltest: At line 55: query 'TRUNCATE TABLE parent' failed: 1205: Lock wait timeout exceeded; try restarting transaction

Fix

No fix needed, test corrected.

--- a/mysql-test/suite/innodb/t/truncate_foreign.test
+++ b/mysql-test/suite/innodb/t/truncate_foreign.test
@@ -52,11 +52,12 @@ send INSERT INTO child SET a=5;
 connection default;
 SET DEBUG_SYNC='now WAIT_FOR fk';
 SET foreign_key_checks=0;
+# NB: child now prelocks parent
+--error ER_LOCK_WAIT_TIMEOUT
 TRUNCATE TABLE parent;
 SET DEBUG_SYNC='now SIGNAL go';

 connection dml;
---error ER_NO_REFERENCED_ROW_2
 reap;
 SELECT * FROM parent;
 SELECT * FROM child;

[midenok]

Bug: ASAN on innodb.innodb

Reproduce

--source include/have_innodb.inc

create temporary table t1 (a int not null auto_increment, primary key(a)) engine=innodb;
insert into t1 values (NULL),(NULL),(NULL);
delete from t1 where a=3;
insert into t1 values (NULL);
select * from t1;
alter table t1 add b int;
select * from t1;
drop table t1;

Result

mysqltest: At line 8: query 'alter table t1 add b int' failed: 2013: Lost connection to MySQL server during query

ERROR: AddressSanitizer: heap-use-after-free on address 0x61b000039f48 at pc 0x0000009d4555 bp 0x7efd1deff950 sp 0x7efd1deff948
WRITE of size 4 at 0x61b000039f48 thread T12
    #0 0x9d4554 in base_list::operator=(base_list const&) /home/midenok/src/mariadb/10.5/src/sql/sql_list.h:153:13
    #1 0xf07256 in List<FK_info>::operator=(List<FK_info> const&) /home/midenok/src/mariadb/10.5/src/sql/sql_list.h:497:26
    #2 0xf07186 in FK_list::operator=(FK_list const&) /home/midenok/src/mariadb/10.5/src/sql/handler.h:1066:7
    #3 0x106b1f2 in FK_table_backup::rollback() /home/midenok/src/mariadb/10.5/src/sql/sql_alter.h:51:24
    #4 0x10720f9 in FK_table_backup::~FK_table_backup() /home/midenok/src/mariadb/10.5/src/sql/sql_alter.h:41:7
    #5 0x1062a36 in Alter_table_ctx::~Alter_table_ctx() /home/midenok/src/mariadb/10.5/src/sql/sql_alter.h:308:7
    #6 0x1024e26 in mysql_alter_table(THD*, st_mysql_const_lex_string const*, st_mysql_const_lex_string const*, HA_CREATE_INFO*, TABLE_LIST*, Alter_info*, unsigned int, st_order*, bool, bool) /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:11409:1
    #7 0x12b8f43 in Sql_cmd_alter_table::execute(THD*) /home/midenok/src/mariadb/10.5/src/sql/sql_alter.cc:535:11
    #8 0xcdddd9 in mysql_execute_command(THD*) /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:5953:26
    #9 0xcb94d0 in mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool) /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:7995:18
    #10 0xcb1f43 in dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool) /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:1874:7
    #11 0xcbb18c in do_command(THD*) /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:1355:17
    #12 0x12951fc in do_handle_one_connection(CONNECT*, bool) /home/midenok/src/mariadb/10.5/src/sql/sql_connect.cc:1411:11
    #13 0x1294941 in handle_one_connection /home/midenok/src/mariadb/10.5/src/sql/sql_connect.cc:1313:5
    #14 0x24781c5 in pfs_spawn_thread /home/midenok/src/mariadb/10.5/src/storage/perfschema/pfs.cc:2201:3
    #15 0x7efd2d044668 in start_thread (/lib/x86_64-linux-gnu/libpthread.so.0+0x9668)
    #16 0x7efd2c4ef322 in clone /build/glibc-t7JzpG/glibc-2.30/misc/../sysdeps/unix/sysv/linux/x86_64/clone.S:95

0x61b000039f48 is located 712 bytes inside of 1640-byte region [0x61b000039c80,0x61b00003a2e8)
freed by thread T12 here:
    #0 0x85a418 in __interceptor_free (/home/midenok/src/mariadb/10.5/build/sql/mariadbd+0x85a418)
    #1 0x34332b3 in my_free /home/midenok/src/mariadb/10.5/src/mysys/my_malloc.c:209:3
    #2 0x155b241 in THD::free_tmp_table_share(TMP_TABLE_SHARE*, bool) /home/midenok/src/mariadb/10.5/src/sql/temporary_tables.cc:1467:3
    #3 0x155bc7d in THD::drop_temporary_table(TABLE*, bool*, bool) /home/midenok/src/mariadb/10.5/src/sql/temporary_tables.cc:669:3
    #4 0x1022d40 in mysql_alter_table(THD*, st_mysql_const_lex_string const*, st_mysql_const_lex_string const*, HA_CREATE_INFO*, TABLE_LIST*, Alter_info*, unsigned int, st_order*, bool, bool) /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:11119:10
    #5 0x12b8f43 in Sql_cmd_alter_table::execute(THD*) /home/midenok/src/mariadb/10.5/src/sql/sql_alter.cc:535:11
    #6 0xcdddd9 in mysql_execute_command(THD*) /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:5953:26
    #7 0xcb94d0 in mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool) /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:7995:18
    #8 0xcb1f43 in dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool) /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:1874:7
    #9 0xcbb18c in do_command(THD*) /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:1355:17
    #10 0x12951fc in do_handle_one_connection(CONNECT*, bool) /home/midenok/src/mariadb/10.5/src/sql/sql_connect.cc:1411:11
    #11 0x1294941 in handle_one_connection /home/midenok/src/mariadb/10.5/src/sql/sql_connect.cc:1313:5
    #12 0x24781c5 in pfs_spawn_thread /home/midenok/src/mariadb/10.5/src/storage/perfschema/pfs.cc:2201:3
    #13 0x7efd2d044668 in start_thread (/lib/x86_64-linux-gnu/libpthread.so.0+0x9668)

previously allocated by thread T12 here:
    #0 0x85a7f7 in __interceptor_malloc (/home/midenok/src/mariadb/10.5/build/sql/mariadbd+0x85a7f7)
    #1 0x3431ebe in my_malloc /home/midenok/src/mariadb/10.5/src/mysys/my_malloc.c:88:29
    #2 0x1559b4c in THD::create_temporary_table(st_mysql_const_unsigned_lex_string*, char const*, char const*, char const*) /home/midenok/src/mariadb/10.5/src/sql/temporary_tables.cc:959:36
    #3 0x1559176 in THD::create_and_open_tmp_table(st_mysql_const_unsigned_lex_string*, char const*, char const*, char const*, bool) /home/midenok/src/mariadb/10.5/src/sql/temporary_tables.cc:71:15
    #4 0xff8042 in create_table_impl(THD*, st_mysql_const_lex_string const&, st_mysql_const_lex_string const&, st_mysql_const_lex_string const&, st_mysql_const_lex_string const&, st_mysql_const_lex_string const&, char const*, DDL_options_st, HA_CREATE_INFO*, Alter_info*, int, bool*, st_key**, unsigned int*, FK_list&, FK_list&, st_mysql_const_unsigned_lex_string*) /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:5408:24
    #5 0xff582e in mysql_create_table_no_lock(THD*, st_mysql_const_lex_string const*, st_mysql_const_lex_string const*, Table_specification_st*, Alter_info*, bool*, int, TABLE_LIST*) /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:5481:8
    #6 0xff9174 in mysql_create_table(THD*, TABLE_LIST*, Table_specification_st*, Alter_info*) /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:5575:7
    #7 0x104e986 in Sql_cmd_create_table_like::execute(THD*) /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:12380:12
    #8 0xcdddd9 in mysql_execute_command(THD*) /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:5953:26
    #9 0xcb94d0 in mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool) /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:7995:18
    #10 0xcb1f43 in dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool) /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:1874:7
    #11 0xcbb18c in do_command(THD*) /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:1355:17
    #12 0x12951fc in do_handle_one_connection(CONNECT*, bool) /home/midenok/src/mariadb/10.5/src/sql/sql_connect.cc:1411:11
    #13 0x1294941 in handle_one_connection /home/midenok/src/mariadb/10.5/src/sql/sql_connect.cc:1313:5
    #14 0x24781c5 in pfs_spawn_thread /home/midenok/src/mariadb/10.5/src/storage/perfschema/pfs.cc:2201:3
    #15 0x7efd2d044668 in start_thread (/lib/x86_64-linux-gnu/libpthread.so.0+0x9668)

Fix

--- a/sql/sql_table.cc
+++ b/sql/sql_table.cc
@@ -8438,7 +8438,8 @@ mysql_prepare_alter_table(THD *thd, TABLE *table,
     period_start_name= table->s->period_start_field()->field_name;
     period_end_name= table->s->period_end_field()->field_name;
   }
-  alter_ctx->fk_table_backup.init(table->s);
+  if (!table->s->tmp_table)
+    alter_ctx->fk_table_backup.init(table->s);
   DBUG_ENTER("mysql_prepare_alter_table");

   /*

[midenok]

Bug: main.drop_table_force fails

Reproduce

--source include/have_innodb.inc
--let $DATADIR= `select @@datadir`

create table parent (id int not null, primary key (id)) engine=innodb;
create table child (id int, parent_id int, index par_ind (parent_id), foreign key (parent_id) references parent(id) on delete cascade) engine=innodb;
--remove_file $datadir/test/parent.frm

# parent can be dropped when there are no foreign key references
drop table child;
drop table parent;

create table parent (id int not null, primary key (id)) engine=innodb;
drop table parent;

Result

mysqltest: At line 12: query 'create table parent (id int not null, primary key (id)) engine=innodb' failed: 1050: Table 'parent' already exists

Cause

fk_handle_drop() is not called.

Related to 7c2ba9e9: ha_table_exists() is replaced by dd_frm_type(). The former checked the existence of share while the latter checks the existence of frm.

ha_table_exists() returns true

#0  ha_table_exists (thd=0x7fed98000d48, db=0x7fed98014e20, table_name=0x7fed98014e30, hton=0x7feddc0f1428, is_sequence=0x7feddc0f1427) at /home/midenok/src/mariadb/10.5/src/sql/handler.cc:5859
#1  0x00000000007ee151 in upgrade_lock_if_not_exists (thd=0x7fed98000d48, create_info=..., create_table=0x7fed98014e08, lock_wait_timeout=86400) at /home/midenok/src/mariadb/10.5/src/sql/sql_base.cc:3952
#2  0x00000000007ed862 in lock_table_names (thd=0x7fed98000d48, options=..., tables_start=0x7fed98014e08, tables_end=0x0, lock_wait_timeout=86400, flags=0) at /home/midenok/src/mariadb/10.5/src/sql/sql_base.cc:4070
#3  0x00000000007ee5ab in open_tables (thd=0x7fed98000d48, options=..., start=0x7feddc0f1ac0, counter=0x7feddc0f1aac, flags=0, prelocking_strategy=0x7feddc0f1b18) at /home/midenok/src/mariadb/10.5/src/sql/sql_base.cc:4275
#4  0x00000000007f34e5 in open_and_lock_tables (thd=0x7fed98000d48, options=..., tables=0x7fed98014e08, derived=false, flags=0, prelocking_strategy=0x7feddc0f1b18) at /home/midenok/src/mariadb/10.5/src/sql/sql_base.cc:5253
#5  0x00000000009e5c9f in open_and_lock_tables (thd=0x7fed98000d48, options=..., tables=0x7fed98014e08, derived=false, flags=0) at /home/midenok/src/mariadb/10.5/src/sql/sql_base.h:499
#6  0x00000000009c21e5 in mysql_create_table (thd=0x7fed98000d48, create_table=0x7fed98014e08, create_info=0x7feddc0f1f28, alter_info=0x7feddc0f1e38) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:5512
#7  0x00000000009df713 in Sql_cmd_create_table_like::execute (this=0x7fed98014da0, thd=0x7fed98000d48) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:12358
#8  0x00000000008bc7dc in mysql_execute_command (thd=0x7fed98000d48) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:5952
#9  0x00000000008ad4d6 in mysql_parse (thd=0x7fed98000d48, rawbuf=0x7fed98014cb0 "CREATE TABLE parent (id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB", length=69, parser_state=0x7feddc0f4590, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:7994

5852      TDC_element *element= tdc_lock_share(thd, db->str, table_name->str);
5853      if (element && element != MY_ERRPTR)
5854      {
5855        if (hton)
5856          *hton= element->share->db_type();
5857        *is_sequence= element->share->table_type == TABLE_TYPE_SEQUENCE;
5858        tdc_unlock_share(element);
5859        DBUG_RETURN(TRUE);
5860      }

[midenok]

Good: share deleted from hash

#0  tdc_delete_share_from_hash (element=0x7f368802ac78) at /home/midenok/src/mariadb/10.5b/src/sql/table_cache.cc:484
#1  0x0000000000b9c1a4 in tdc_remove_table (thd=0x7f3688000d48, db=0x7f3688015228 "test", table_name=0x7f3688014b18 "parent") at /home/midenok/src/mariadb/10.5b/src/sql/table_cache.cc:1052
#2  0x00000000009b5b4e in mysql_rm_table_no_locks (thd=0x7f3688000d48, tables=0x7f3688014b58, if_exists=false, drop_temporary=false, drop_view=false, drop_sequence=false, drop_db=false, dont_log_query=false, dont_free_locks=false) at /home/midenok/src/mariadb/10.5b/src/sql/sql_table.cc:2500
#3  0x00000000009b4b4f in mysql_rm_table (thd=0x7f3688000d48, tables=0x7f3688014b58, if_exists=false, drop_temporary=false, drop_sequence=false, dont_log_query=false) at /home/midenok/src/mariadb/10.5b/src/sql/sql_table.cc:2155
#4  0x00000000008b5816 in mysql_execute_command (thd=0x7f3688000d48) at /home/midenok/src/mariadb/10.5b/src/sql/sql_parse.cc:4929
#5  0x00000000008aa296 in mysql_parse (thd=0x7f3688000d48, rawbuf=0x7f3688014a90 "drop table parent", length=17, parser_state=0x7f36d40a9590, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/10.5b/src/sql/sql_parse.cc:7995