Bug #107542 Same alter statement executed twice on dd table, sdi record to delete not found
Submitted: 12 Jun 2022 8:27 Modified: 14 Jun 2022 5:27
Reporter: chang li Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DDL Severity:S6 (Debug Builds)
Version:8.0.18 OS:Any
Assigned to: CPU Architecture:Any

[12 Jun 2022 8:27] chang li
Description:
Executing sql statements in section **How to repeat** cause assertion 
**(!sdi_delete_record_not_found)** failed. I found that when executing
the **alter** statement at the first time, the sdi key for acquiring 
**mysql.columns** is {type = 1, id = 12}. After finishing the first 
**alter** statement, old sdi record for **mysql.columns** is deleted, 
and new one inserted with id=339. However, when executing the same 
**alter** statement again, the sdi key is still {type = 1, id = 12}, 
which leads to the assertion failure, since sdi record for **mysql.columns**
already has new id.

**GDB Info**

#6  0x000000000481620c in ut_dbg_assertion_failed (expr=0x635e9d9 "!sdi_delete_record_not_found", 
    file=0x635e148 "/u01/mysql-server/storage/innobase/api/api0api.cc", line=3160)
    at /u01/mysql-server/storage/innobase/ut/ut0dbg.cc:98
#7  0x000000000483aa98 in ib_sdi_delete (tablespace_id=4294967294, ib_sdi_key=0x7f8b580a7b78, trx=0x7f8b591d4c90)
    at /u01/mysql-server/storage/innobase/api/api0api.cc:3160
#8  0x000000000498ca11 in dict_sdi_delete (tablespace=..., table=0x7f8a540556b0, sdi_key=0x7f8b580a8060)
    at /u01/mysql-server/storage/innobase/dict/dict0sdi.cc:467
#9  0x00000000043c625e in dd::sdi_tablespace::drop_tbl_sdi (thd=0x7f8a54000cf0, hton=..., table=..., schema=...)
    at /u01/mysql-server/sql/dd/impl/sdi_tablespace.cc:181
#10 0x00000000043bfc3f in dd::sdi::<lambda(const dd::Schema&)>::operator()(const dd::Schema &) const (
    __closure=0x7f8b580a81a0, s=...) at /u01/mysql-server/sql/dd/impl/sdi.cc:590
#11 0x00000000043c022c in dd::(anonymous namespace)::with_schema<long long unsigned int, dd::sdi::drop(THD*, const dd::Table*)::<lambda(const dd::Schema&)> >(THD *, const unsigned long long &, <unknown type in /u01/mysql-server/runtime_output_directory/mysqld, CU 0xc0d2e0e, DIE 0xc18d9f1>) (thd=0x7f8a54000cf0, key=@0x7f8b580a81b8: 1, 
    clos=<unknown type in /u01/mysql-server/runtime_output_directory/mysqld, CU 0xc0d2e0e, DIE 0xc18d9f1>)
    at /u01/mysql-server/sql/dd/impl/sdi.cc:493
#12 0x00000000043bfd1c in dd::sdi::drop (thd=0x7f8a54000cf0, tp=0x7f8a540556b0)
    at /u01/mysql-server/sql/dd/impl/sdi.cc:592
#13 0x00000000042c2326 in dd::cache::Storage_adapter::drop<dd::Table> (thd=0x7f8a54000cf0, object=0x7f8a540556b0)
    at /u01/mysql-server/sql/dd/impl/cache/storage_adapter.cc:255
#14 0x00000000041e01f5 in dd::cache::Dictionary_client::drop<dd::Table> (this=0x7f8a540044d0, object=0x7f8a540556b0)
    at /u01/mysql-server/sql/dd/impl/cache/dictionary_client.cc:2423
#15 0x000000000301423e in mysql_inplace_alter_table (thd=0x7f8a54000cf0, schema=..., new_schema=..., 
    table_def=0x7f8a540556b0, altered_table_def=0x7f8a54143730, table_list=0x7f8a5400d398, table=0x0, 
    altered_table=0x7f8a5402e670, ha_alter_info=0x7f8b580a8c10, inplace_supported=HA_ALTER_INPLACE_INSTANT, 
    alter_ctx=0x7f8b580a9a30, columns=std::set with 0 elements, fk_key_info=0x7f8a540f8cf0, fk_key_count=3, 
    fk_invalidator=0x7f8b580a9960) at /u01/mysql-server/sql/sql_table.cc:12790
#16 0x000000000301ee96 in mysql_alter_table (thd=0x7f8a54000cf0, new_db=0x7f8a5400cc80 "mysql", new_name=0x0, 
    create_info=0x7f8b580ab450, table_list=0x7f8a5400d398, alter_info=0x7f8b580ab300)
    at /u01/mysql-server/sql/sql_table.cc:16434
#17 0x000000000356d50e in Sql_cmd_alter_table::execute (this=0x7f8a5400dba8, thd=0x7f8a54000cf0)
    at /u01/mysql-server/sql/sql_alter.cc:348
#18 0x0000000002f64016 in mysql_execute_command (thd=0x7f8a54000cf0, first_level=true)
    at /u01/mysql-server/sql/sql_parse.cc:4453
#19 0x0000000002f6691b in mysql_parse (thd=0x7f8a54000cf0, parser_state=0x7f8b580acb30)
    at /u01/mysql-server/sql/sql_parse.cc:5257
#20 0x0000000002f5bde0 in dispatch_command (thd=0x7f8a54000cf0, com_data=0x7f8b580adbd0, command=COM_QUERY)
    at /u01/mysql-server/sql/sql_parse.cc:1765
#21 0x0000000002f5a345 in do_command (thd=0x7f8a54000cf0) at /u01/mysql-server/sql/sql_parse.cc:1273
#22 0x000000000311a6fd in handle_connection (arg=0x7c99ac0)
    at /u01/mysql-server/sql/conn_handler/connection_handler_per_thread.cc:302

How to repeat:
mysql> set session debug="+d, skip_dd_table_access_check";
Query OK, 0 rows affected (0.00 sec)

mysql> alter table mysql.columns add column vc int as (srs_id + 1) virtual;
Query OK, 0 rows affected (0.18 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table mysql.columns add column vc int as (srs_id + 1) virtual;
ERROR 2013 (HY000): Lost connection to MySQL server during query
[13 Jun 2022 7:32] MySQL Verification Team
Hello chang li,

Thank you for the report and feedback.
I quickly attempted to reproduce on latest build(debug) but not seeing any assertion.  Please note that we don't fix bugs in old versions, don't backport bug fixes, so you need to check with latest MySQL Server version. So, please, upgrade to 8.0.29 and inform us if problem still exists.

-
mysql> set session debug="+d, skip_dd_table_access_check";
Query OK, 0 rows affected (0.00 sec)

mysql> alter table mysql.columns add column vc int as (srs_id + 1) virtual;
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table mysql.columns add column vc int as (srs_id + 1) virtual;
ERROR 3958 (HY000): Failed to delete sdi for mysql.columns in mysql due to missing record.
mysql>
mysql> alter table mysql.columns add column vc int as (srs_id + 1) virtual;
ERROR 3958 (HY000): Failed to delete sdi for mysql.columns in mysql due to missing record.

regards,
Umesh
[13 Jun 2022 15:12] chang li
Hi, I checked it with latest version, now the problem not exists.
Sorry for that, I will check with latest codes before reporting bugs hence.
[14 Jun 2022 5:27] MySQL Verification Team
No problems and thank you for confirming.

regards,
Umesh