Partitioning features

[midenok]

Jira tasks

MDEV-19903 Setup default partitions for system versioning MDEV-17554 Auto-create new partition for system versioned tables with history partitioned by INTERVAL

MDEV-19903 Setup default partitions ...

Description

Currently such syntax fails:

create table t1 (i int) with system versioning partition by system_time;

with error message:

ERROR 1492 (HY000): For LIST partitions each partition must be defined

which is by itself incorrect.

Auto-create 1 history partition and 1 working partition for the above syntax. Specify the number of history partitions by the partitions keyword:

create table t1 (i int) with system versioning partition by system_time partitions 2;

This should create 1 history and 1 working partition. The working partition is mandatory, so partitions 1 should fail with error.

Info

create or replace table t1 (x int)
with system versioning
partition by system_time (
    partition p0 history,
    partition pn current);

[midenok]

Each partition must be defined error

ERROR 1492 (HY000): For LIST partitions each partition must be defined

#0  my_error (nr=1492, MyFlags=0) at /home/midenok/src/mariadb/trunk/src/mysys/my_error.c:113
#1  0x000000000098e8b9 in partition_info::set_up_default_partitions (this=0x7fff84013c18, thd=0x7fff84000cf8, file=0x7fff840142c8, info=0x7ffff461d268, start_no=0) at /home/midenok/src/mariadb/trunk/src/sql/partition_info.cc:461
#2  0x000000000098ef96 in partition_info::set_up_defaults_for_partitioning (this=0x7fff84013c18, thd=0x7fff84000cf8, file=0x7fff840142c8, info=0x7ffff461d268, start_no=0) at /home/midenok/src/mariadb/trunk/src/sql/partition_info.cc:596
#3  0x0000000000991229 in partition_info::check_partition_info (this=0x7fff84013c18, thd=0x7fff84000cf8, eng_type=0x7ffff461a038, file=0x7fff840142c8, info=0x7ffff461d268, add_or_reorg_part=0x0) at /home/midenok/src/mariadb/trunk/src/sql/partition_info.cc:1202
#4  0x0000000000906d8c in mysql_create_frm_image (thd=0x7fff84000cf8, db=..., table_name=..., create_info=0x7ffff461d268, alter_info=0x7ffff461d1b0, create_table_mode=0, key_info=0x7ffff461b220, key_count=0x7ffff461b21c, frm=0x7ffff461b000) at /home/midenok/src/mariadb/trunk/src/sql/sql_table.cc:4680
#5  0x0000000000908895 in create_table_impl (thd=0x7fff84000cf8, orig_db=..., orig_table_name=..., db=..., table_name=..., path=0x7ffff461b010 "./test/t1", options=..., create_info=0x7ffff461d268, alter_info=0x7ffff461d1b0, create_table_mode=0, is_trans=0x7ffff461b347, key_info=0x7ffff461b220, key_count=0x7ffff461b21c, frm=0x7ffff461b000) at /home/midenok/src/mariadb/trunk/src/sql/sql_table.cc:5030
#6  0x0000000000907bf8 in mysql_create_table_no_lock (thd=0x7fff84000cf8, db=0x7fff84013090, table_name=0x7fff840130a0, create_info=0x7ffff461d268, alter_info=0x7ffff461d1b0, is_trans=0x7ffff461b347, create_table_mode=0, table_list=0x7fff84013078) at /home/midenok/src/mariadb/trunk/src/sql/sql_table.cc:5133
#7  0x0000000000908eea in mysql_create_table (thd=0x7fff84000cf8, create_table=0x7fff84013078, create_info=0x7ffff461d268, alter_info=0x7ffff461d1b0) at /home/midenok/src/mariadb/trunk/src/sql/sql_table.cc:5225
#8  0x000000000080e11e in mysql_execute_command (thd=0x7fff84000cf8) at /home/midenok/src/mariadb/trunk/src/sql/sql_parse.cc:4391
#9  0x0000000000805d90 in mysql_parse (thd=0x7fff84000cf8, rawbuf=0x7fff84012f20 "create or replace table t1 (i int) with system versioning partition by system_time", length=82, parser_state=0x7ffff461e640, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/trunk/src/sql/sql_parse.cc:8157

595         if (use_default_partitions)
596           DBUG_RETURN(set_up_default_partitions(thd, file, info, start_no));

No now_part error

ERROR 4128 (HY000): Wrong partitions for `t1`: must have at least one HISTORY and exactly one last CURRENT

#0  my_error (nr=4128, MyFlags=0) at /home/midenok/src/mariadb/trunk/src/mysys/my_error.c:113
#1  0x00000000009917c7 in partition_info::check_partition_info (this=0x7fff78013c18, thd=0x7fff78000cf8, eng_type=0x7ffff461a038, file=0x7fff780142c8, info=0x7ffff461d268, add_or_reorg_part=0x0) at /home/midenok/src/mariadb/trunk/src/sql/partition_info.cc:1266
#2  0x0000000000906d8c in mysql_create_frm_image (thd=0x7fff78000cf8, db=..., table_name=..., create_info=0x7ffff461d268, alter_info=0x7ffff461d1b0, create_table_mode=0, key_info=0x7ffff461b220, key_count=0x7ffff461b21c, frm=0x7ffff461b000) at /home/midenok/src/mariadb/trunk/src/sql/sql_table.cc:4680
#3  0x0000000000908895 in create_table_impl (thd=0x7fff78000cf8, orig_db=..., orig_table_name=..., db=..., table_name=..., path=0x7ffff461b010 "./test/t1", options=..., create_info=0x7ffff461d268, alter_info=0x7ffff461d1b0, create_table_mode=0, is_trans=0x7ffff461b347, key_info=0x7ffff461b220, key_count=0x7ffff461b21c, frm=0x7ffff461b000) at /home/midenok/src/mariadb/trunk/src/sql/sql_table.cc:5030
#4  0x0000000000907bf8 in mysql_create_table_no_lock (thd=0x7fff78000cf8, db=0x7fff78013090, table_name=0x7fff780130a0, create_info=0x7ffff461d268, alter_info=0x7ffff461d1b0, is_trans=0x7ffff461b347, create_table_mode=0, table_list=0x7fff78013078) at /home/midenok/src/mariadb/trunk/src/sql/sql_table.cc:5133
#5  0x0000000000908eea in mysql_create_table (thd=0x7fff78000cf8, create_table=0x7fff78013078, create_info=0x7ffff461d268, alter_info=0x7ffff461d1b0) at /home/midenok/src/mariadb/trunk/src/sql/sql_table.cc:5225
#6  0x000000000080e11e in mysql_execute_command (thd=0x7fff78000cf8) at /home/midenok/src/mariadb/trunk/src/sql/sql_parse.cc:4391
#7  0x0000000000805d90 in mysql_parse (thd=0x7fff78000cf8, rawbuf=0x7fff78012f20 "create or replace table t1 (i int) with system versioning partition by system_time", length=82, parser_state=0x7ffff461e640, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/trunk/src/sql/sql_parse.cc:8157

check_vers_constants()

#0  check_vers_constants (thd=0x7fff84000cf8, part_info=0x7fff84013ea0) at /home/midenok/src/mariadb/trunk/src/sql/sql_partition.cc:1553
#1  0x0000000000d904b8 in fix_partition_func (thd=0x7fff84000cf8, table=0x7ffff46192d8, is_create_table_ind=true) at /home/midenok/src/mariadb/trunk/src/sql/sql_partition.cc:2021
#2  0x000000000095a433 in open_table_from_share (thd=0x7fff84000cf8, share=0x7ffff4618b48, alias=0x1b78bc8 <empty_clex_str>, db_stat=0, prgflag=1, ha_open_flags=0, outparam=0x7ffff46192d8, is_create_table=true, partitions_to_open=0x0) at /home/midenok/src/mariadb/trunk/src/sql/table.cc:3814
#3  0x0000000000b89681 in ha_create_table (thd=0x7fff84000cf8, path=0x7ffff461b010 "./test/t1", db=0x7fff84013798 "test", table_name=0x7fff840130a8 "t1", create_info=0x7ffff461d268, frm=0x7ffff461b000) at /home/midenok/src/mariadb/trunk/src/sql/handler.cc:5165
#4  0x0000000000908966 in create_table_impl (thd=0x7fff84000cf8, orig_db=..., orig_table_name=..., db=..., table_name=..., path=0x7ffff461b010 "./test/t1", options=..., create_info=0x7ffff461d268, alter_info=0x7ffff461d1b0, create_table_mode=0, is_trans=0x7ffff461b347, key_info=0x7ffff461b220, key_count=0x7ffff461b21c, frm=0x7ffff461b000) at /home/midenok/src/mariadb/trunk/src/sql/sql_table.cc:5049
#5  0x0000000000907bf8 in mysql_create_table_no_lock (thd=0x7fff84000cf8, db=0x7fff840130f8, table_name=0x7fff84013108, create_info=0x7ffff461d268, alter_info=0x7ffff461d1b0, is_trans=0x7ffff461b347, create_table_mode=0, table_list=0x7fff840130e0) at /home/midenok/src/mariadb/trunk/src/sql/sql_table.cc:5133
#6  0x0000000000908eea in mysql_create_table (thd=0x7fff84000cf8, create_table=0x7fff840130e0, create_info=0x7ffff461d268, alter_info=0x7ffff461d1b0) at /home/midenok/src/mariadb/trunk/src/sql/sql_table.cc:5225
#7  0x000000000080e11e in mysql_execute_command (thd=0x7fff84000cf8) at /home/midenok/src/mariadb/trunk/src/sql/sql_parse.cc:4391
#8  0x0000000000805d90 in mysql_parse (thd=0x7fff84000cf8, rawbuf=0x7fff84012f20 "create or replace table t1 (x int) with system versioning partition by system_time (     partition p0 history,     partition pn current)", length=136, parser_state=0x7ffff461e640, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/trunk/src/sql/sql_parse.cc:8157

Divergence is on create_table_impl() (sql_table.cc:5030 vs sql_table.cc:5049)

[midenok]

Bug 1: subpartitions segfault

Reproduce

create or replace table t1 (x int)
with system versioning
partition by system_time limit 2 partitions 3
subpartition by key (x)
subpartitions 2;

Result

#0  0x0000000000991b31 in partition_info::check_partition_info (this=0x7fffa4014de8, thd=0x7fffa4000cf8, eng_type=0x7ffff0532fd8, file=0x7fffa4016b48, info=0x7ffff0536208, add_or_reorg_part=0x0) at /home/midenok/src/mariadb/trunk/src/sql/partition_info.cc:1319
#1  0x0000000000906d8c in mysql_create_frm_image (thd=0x7fffa4000cf8, db=..., table_name=..., create_info=0x7ffff0536208, alter_info=0x7ffff0536150, create_table_mode=0, key_info=0x7ffff05341c0, key_count=0x7ffff05341bc, frm=0x7ffff0533fa0) at /home/midenok/src/mariadb/trunk/src/sql/sql_table.cc:4680
#2  0x0000000000908895 in create_table_impl (thd=0x7fffa4000cf8, orig_db=..., orig_table_name=..., db=..., table_name=..., path=0x7ffff0533fb0 "./test/t1", options=..., create_info=0x7ffff0536208, alter_info=0x7ffff0536150, create_table_mode=0, is_trans=0x7ffff05342e7, key_info=0x7ffff05341c0, key_count=0x7ffff05341bc, frm=0x7ffff0533fa0) at /home/midenok/src/mariadb/trunk/src/sql/sql_table.cc:5030
#3  0x0000000000907bf8 in mysql_create_table_no_lock (thd=0x7fffa4000cf8, db=0x7fffa4014230, table_name=0x7fffa4014240, create_info=0x7ffff0536208, alter_info=0x7ffff0536150, is_trans=0x7ffff05342e7, create_table_mode=0, table_list=0x7fffa4014218) at /home/midenok/src/mariadb/trunk/src/sql/sql_table.cc:5133
#4  0x0000000000908eea in mysql_create_table (thd=0x7fffa4000cf8, create_table=0x7fffa4014218, create_info=0x7ffff0536208, alter_info=0x7ffff0536150) at /home/midenok/src/mariadb/trunk/src/sql/sql_table.cc:5225
#5  0x000000000080e11e in mysql_execute_command (thd=0x7fffa4000cf8) at /home/midenok/src/mariadb/trunk/src/sql/sql_parse.cc:4391
#6  0x0000000000805d90 in mysql_parse (thd=0x7fffa4000cf8, rawbuf=0x7fffa4014050 "create or replace table t1 (x int)\nwith system versioning\npartition by system_time limit 2 partitions 3\nsubpartition by key (x)\nsubpartitions 2", length=143, parser_state=0x7ffff05375e0, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/trunk/src/sql/sql_parse.cc:8157

1311            uint j= 0;
1312            uint num_subparts_not_set= 0;
1313            List_iterator<partition_element> sub_it(part_elem->subpartitions);
1314            partition_element *sub_elem;
1315            do
1316            {
1317              sub_elem= sub_it++;
1318              warn_if_dir_in_part_elem(thd, sub_elem);
1319              if (check_table_name(sub_elem->partition_name,
1320                                   strlen(sub_elem->partition_name), FALSE))

(gdb) p sub_elem
$4 = (partition_element *) 0x0
(gdb) p part_elem->subpartitions
$5 = {
  <base_list> = {
    <Sql_alloc> = {<No data fields>}, 
    members of base_list: 
    first = 0x1e9e1e8 <end_of_list>, 
    last = 0x7fffa4017288, 
    elements = 0
  }, <No data fields>}

Cause

subpartitions list is empty.

[midenok]

Good: ER_SUBPARTITION_ERROR thrown

create or replace table n1 (x int)
partition by hash (x) partitions 3
subpartition by key (x)
subpartitions 2;

#0  my_error (nr=1500, MyFlags=0) at /home/midenok/src/mariadb/trunk/src/mysys/my_error.c:113
#1  0x00000000009912d4 in partition_info::check_partition_info (this=0x7fff84013d30, thd=0x7fff84000cf8, eng_type=0x7ffff461a038, file=0x7fff84014148, info=0x7ffff461d268, add_or_reorg_part=0x0) at /home/midenok/src/mariadb/trunk/src/sql/partition_info.cc:1212
#2  0x0000000000906d8c in mysql_create_frm_image (thd=0x7fff84000cf8, db=..., table_name=..., create_info=0x7ffff461d268, alter_info=0x7ffff461d1b0, create_table_mode=0, key_info=0x7ffff461b220, key_count=0x7ffff461b21c, frm=0x7ffff461b000) at /home/midenok/src/mariadb/trunk/src/sql/sql_table.cc:4680
#3  0x0000000000908895 in create_table_impl (thd=0x7fff84000cf8, orig_db=..., orig_table_name=..., db=..., table_name=..., path=0x7ffff461b010 "./test/n1", options=..., create_info=0x7ffff461d268, alter_info=0x7ffff461d1b0, create_table_mode=0, is_trans=0x7ffff461b347, key_info=0x7ffff461b220, key_count=0x7ffff461b21c, frm=0x7ffff461b000) at /home/midenok/src/mariadb/trunk/src/sql/sql_table.cc:5030
#4  0x0000000000907bf8 in mysql_create_table_no_lock (thd=0x7fff84000cf8, db=0x7fff840130c0, table_name=0x7fff840130d0, create_info=0x7ffff461d268, alter_info=0x7ffff461d1b0, is_trans=0x7ffff461b347, create_table_mode=0, table_list=0x7fff840130a8) at /home/midenok/src/mariadb/trunk/src/sql/sql_table.cc:5133
#5  0x0000000000908eea in mysql_create_table (thd=0x7fff84000cf8, create_table=0x7fff840130a8, create_info=0x7ffff461d268, alter_info=0x7ffff461d1b0) at /home/midenok/src/mariadb/trunk/src/sql/sql_table.cc:5225
#6  0x000000000080e11e in mysql_execute_command (thd=0x7fff84000cf8) at /home/midenok/src/mariadb/trunk/src/sql/sql_parse.cc:4391
#7  0x0000000000805d90 in mysql_parse (thd=0x7fff84000cf8, rawbuf=0x7fff84012f20 "create or replace table n1 (x int)\npartition by hash (x) partitions 3\nsubpartition by key (x)\nsubpartitions 2", length=109, parser_state=0x7ffff461e640, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/trunk/src/sql/sql_parse.cc:8157

1206      if (unlikely(is_sub_partitioned() &&
1207                  (!(part_type == RANGE_PARTITION || 
1208                     part_type == LIST_PARTITION ||
1209                     part_type == VERSIONING_PARTITION))))
1210      {
1211        /* Only RANGE and LIST partitioning can be subpartitioned */
1212        my_error(ER_SUBPARTITION_ERROR, MYF(0));
1213        goto end;
1214      }
1

Currently it is not possible to make default partition subpartitioned since RANGE and LIST do not support default.

[midenok]

Good: default subpartitions setup

create or replace table n1 (x int)
partition by range(x)
subpartition by hash(x) subpartitions 2
(partition part1 values less than (1000));

#0  partition_info::set_up_default_subpartitions (this=0x7fff84014020, thd=0x7fff84000cf8, file=0x7fff84014540, info=0x7ffff461d268) at /home/midenok/src/mariadb/trunk/src/sql/partition_info.cc:541
#1  0x000000000098f0ec in partition_info::set_up_defaults_for_partitioning (this=0x7fff84014020, thd=0x7fff84000cf8, file=0x7fff84014540, info=0x7ffff461d268, start_no=0) at /home/midenok/src/mariadb/trunk/src/sql/partition_info.cc:612
#2  0x00000000009912f9 in partition_info::check_partition_info (this=0x7fff84014020, thd=0x7fff84000cf8, eng_type=0x7ffff461a038, file=0x7fff84014540, info=0x7ffff461d268, add_or_reorg_part=0x0) at /home/midenok/src/mariadb/trunk/src/sql/partition_info.cc:1215
#3  0x0000000000906d8c in mysql_create_frm_image (thd=0x7fff84000cf8, db=..., table_name=..., create_info=0x7ffff461d268, alter_info=0x7ffff461d1b0, create_table_mode=0, key_info=0x7ffff461b220, key_count=0x7ffff461b21c, frm=0x7ffff461b000) at /home/midenok/src/mariadb/trunk/src/sql/sql_table.cc:4680
#4  0x0000000000908895 in create_table_impl (thd=0x7fff84000cf8, orig_db=..., orig_table_name=..., db=..., table_name=..., path=0x7ffff461b010 "./test/n1", options=..., create_info=0x7ffff461d268, alter_info=0x7ffff461d1b0, create_table_mode=0, is_trans=0x7ffff461b347, key_info=0x7ffff461b220, key_count=0x7ffff461b21c, frm=0x7ffff461b000) at /home/midenok/src/mariadb/trunk/src/sql/sql_table.cc:5030
#5  0x0000000000907bf8 in mysql_create_table_no_lock (thd=0x7fff84000cf8, db=0x7fff84013100, table_name=0x7fff84013110, create_info=0x7ffff461d268, alter_info=0x7ffff461d1b0, is_trans=0x7ffff461b347, create_table_mode=0, table_list=0x7fff840130e8) at /home/midenok/src/mariadb/trunk/src/sql/sql_table.cc:5133
#6  0x0000000000908eea in mysql_create_table (thd=0x7fff84000cf8, create_table=0x7fff840130e8, create_info=0x7ffff461d268, alter_info=0x7ffff461d1b0) at /home/midenok/src/mariadb/trunk/src/sql/sql_table.cc:5225
#7  0x000000000080e11e in mysql_execute_command (thd=0x7fff84000cf8) at /home/midenok/src/mariadb/trunk/src/sql/sql_parse.cc:4391
#8  0x0000000000805d90 in mysql_parse (thd=0x7fff84000cf8, rawbuf=0x7fff84012f20 "create or replace table n1 (x int)\npartition by range(x)\nsubpartition by hash(x) subpartitions 2\n(partition part1 values less than (1000))", length=138, parser_state=0x7ffff461e640, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/trunk/src/sql/sql_parse.cc:8157

Fix

--- a/sql/partition_info.cc
+++ b/sql/partition_info.cc
@@ -599,8 +605,9 @@ bool partition_info::set_up_defaults_for_partitioning(THD *thd, handler *file,
   if (!default_partitions_setup)
   {
     default_partitions_setup= TRUE;
-    if (use_default_partitions)
-      DBUG_RETURN(set_up_default_partitions(thd, file, info, start_no));
+    if (use_default_partitions &&
+        set_up_default_partitions(thd, file, info, start_no))
+      DBUG_RETURN(TRUE);
     if (is_sub_partitioned() && 
         use_default_subpartitions)
       DBUG_RETURN(set_up_default_subpartitions(thd, file, info));

[midenok]

MDEV-17554 Auto-create new partition ...

Description

When there are E empty partitions left, auto-create N new empty partitions.

This scheme must not allow partition overflow. I.e. E-fill time must not exceed N-creation time. This means that low values for INTERVAL and LIMIT must not be allowed. In case when overflow is detected there is no need to do anything special: a warning will be issued and the user will run manual rebuild to redistribute records correctly. This is important because automatic ADD must be done fast, without forced rebuild, by the obvious reason.

E and N should be configurable, but initial version may implement hardcoded value of 1 for both E and N.

Approaches:

• Run fast_alter_partition_table() in local thread;
• Run mysql_parse() in local thread;
• Connect to worker thread and supply ALTER command;
• Spawn new thread and run mysql_parse() there;
• Run mysql_parse() in manager thread or slave background thread (requires queue).

Points of interest:

• High-level approach easier to implement and (more important) to maintain;
• No sense in serializing DDL and DML, because there are another DML threads;
• Therefore no sense in tampering local thread context;
• Parser overhead is not critical since this is low-load operation;
• Scheduling requires queue;
• DDL execution lag is not critical since partition is created in advance;
• Spawning may lead to unexpected CPU oversubscription;
• Connecting may lead to 1. unexpected connection oversubscription; 2. authentication issues; and it's not parallel after all;
• Working on local thread is bug-prone and is coupled with locking issues.

To consider:

Multiple threads may lead to multiple partition creation. But the arbitration is done at partition-name level: i.e. if multiple ADD will start with same name, one of them will succeed and the others will fail.

Decision

First implementation is done in standalone thread. The drawback of this is that ALTER may be theoretically executed on unwanted table:

1. Table t1 is locked with LOCK TABLES;
2. ALTER TABLE t1 ADD PARTITION is initiated in parallel and blocked on lock;
3. t1 is unlocked, dropped and new t1 is created.
4. ALTER continues and adds partition to "unwanted" table.

Current implementation does not have proper solution for this. The ALTER will succeed only if new t1 is partitioned by SYSTEM_TIME and there is no partition by the name it tries to add. Though this behavior is generally unwanted, it has minor impact of one extra history partition in new table. In most cases ALTER will fail after old t1 is dropped, so this is low probability.

Discuss

• wlad_chat.txt

Links

• PARTITION Maintenance in MySQL (some strange observations, but interesting opinion)
• https://dev.mysql.com/doc/refman/8.0/en/partitioning-maintenance.html
• https://dev.mysql.com/doc/refman/8.0/en/alter-table-partition-operations.html
• https://dev.mysql.com/doc/refman/8.0/en/partitioning-management-range-list.html

Info: Out of INTERVAL error thrown

#0  my_error (nr=4114, MyFlags=2112) at /home/midenok/src/mariadb/trunk/src/mysys/my_error.c:113
#1  0x00000000009900e7 in partition_info::vers_set_hist_part (this=0x7fff84034b60, thd=0x7fff84000cf8) at /home/midenok/src/mariadb/trunk/src/sql/partition_info.cc:900
#2  0x00000000014597bc in ha_partition::external_lock (this=0x7fff84032d00, thd=0x7fff84000cf8, lock_type=1) at /home/midenok/src/mariadb/trunk/src/sql/ha_partition.cc:3957
#3  0x0000000000b8cb24 in handler::ha_external_lock (this=0x7fff84032d00, thd=0x7fff84000cf8, lock_type=1) at /home/midenok/src/mariadb/trunk/src/sql/handler.cc:6390
#4  0x0000000000ceadb1 in lock_external (thd=0x7fff84000cf8, tables=0x7fff840145e8, count=1) at /home/midenok/src/mariadb/trunk/src/sql/lock.cc:392
#5  0x0000000000cea913 in mysql_lock_tables (thd=0x7fff84000cf8, sql_lock=0x7fff840145c8, flags=0) at /home/midenok/src/mariadb/trunk/src/sql/lock.cc:337
#6  0x0000000000ce9b22 in mysql_lock_tables (thd=0x7fff84000cf8, tables=0x7fff840145c0, count=1, flags=0) at /home/midenok/src/mariadb/trunk/src/sql/lock.cc:300
#7  0x00000000007595f7 in lock_tables (thd=0x7fff84000cf8, tables=0x7fff84013000, count=1, flags=0) at /home/midenok/src/mariadb/trunk/src/sql/sql_base.cc:5452
#8  0x000000000075ab49 in open_and_lock_tables (thd=0x7fff84000cf8, options=..., tables=0x7fff84013000, derived=true, flags=0, prelocking_strategy=0x7ffff461adf0) at /home/midenok/src/mariadb/trunk/src/sql/sql_base.cc:5198
#9  0x000000000070837c in open_and_lock_tables (thd=0x7fff84000cf8, tables=0x7fff84013000, derived=true, flags=0) at /home/midenok/src/mariadb/trunk/src/sql/sql_base.h:511
#10 0x00000000007b6b38 in mysql_insert (thd=0x7fff84000cf8, table_list=0x7fff84013000, fields=..., values_list=..., update_fields=..., update_values=..., duplic=DUP_ERROR, ignore=false) at /home/midenok/src/mariadb/trunk/src/sql/sql_insert.cc:764
#11 0x000000000080f4d4 in mysql_execute_command (thd=0x7fff84000cf8) at /home/midenok/src/mariadb/trunk/src/sql/sql_parse.cc:4790
#12 0x0000000000805d90 in mysql_parse (thd=0x7fff84000cf8, rawbuf=0x7fff84012f20 "insert into t1 values (1)", length=25, parser_state=0x7ffff461e640, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/trunk/src/sql/sql_parse.cc:8157

Online ALTER TABLE is not supported

create or replace table t1 (x int) with system versioning partition by system_time interval 1 day;
alter online table t1 add partition (partition p1 history);

Result

ERROR 1846 (0A000): LOCK=NONE/SHARED/EXCLUSIVE is not supported. Reason: Partition specific operations do not yet support LOCK/ALGORITHM. Try LOCK=DEFAULT

[midenok]

Info: add partition all locks

#0  MDL_context::acquire_lock (this=0x7fff84000e18, mdl_request=0x7fff84013ab8, lock_wait_timeout=86400) at /home/midenok/src/mariadb/trunk/src/sql/mdl.cc:2241
#1  0x00000000009c66cb in MDL_context::acquire_locks (this=0x7fff84000e18, mdl_requests=0x7ffff4617948, lock_wait_timeout=86400) at /home/midenok/src/mariadb/trunk/src/sql/mdl.cc:2447
#2  0x0000000000755623 in lock_table_names (thd=0x7fff84000cf8, options=..., tables_start=0x7fff84013038, tables_end=0x0, lock_wait_timeout=86400, flags=0) at /home/midenok/src/mariadb/trunk/src/sql/sql_base.cc:4068
#3  0x00000000007562ad in open_tables (thd=0x7fff84000cf8, options=..., start=0x7ffff461a8e8, counter=0x7ffff461a894, sroutine_to_open_list=0x7fff84004be8, flags=0, prelocking_strategy=0x7ffff461a898) at /home/midenok/src/mariadb/trunk/src/sql/sql_base.cc:4274
#4  0x000000000091edb3 in open_tables (thd=0x7fff84000cf8, options=..., tables=0x7ffff461a8e8, counter=0x7ffff461a894, flags=0, prelocking_strategy=0x7ffff461a898) at /home/midenok/src/mariadb/trunk/src/sql/sql_base.h:258
#5  0x0000000000913405 in open_tables (thd=0x7fff84000cf8, tables=0x7ffff461a8e8, counter=0x7ffff461a894, flags=0, prelocking_strategy=0x7ffff461a898) at /home/midenok/src/mariadb/trunk/src/sql/sql_base.h:267
#6  0x000000000090fb0c in mysql_alter_table (thd=0x7fff84000cf8, new_db=0x7fff840054a8, new_name=0x7fff840058b0, create_info=0x7ffff461b150, table_list=0x7fff84013038, alter_info=0x7ffff461b098, order_num=0, order=0x0, ignore=false) at /home/midenok/src/mariadb/trunk/src/sql/sql_table.cc:9369

#0  MDL_context::acquire_lock (this=0x7fff84000e18, mdl_request=0x7fff84013478, lock_wait_timeout=86400) at /home/midenok/src/mariadb/trunk/src/sql/mdl.cc:2241
#1  0x00000000009c66cb in MDL_context::acquire_locks (this=0x7fff84000e18, mdl_requests=0x7ffff4617948, lock_wait_timeout=86400) at /home/midenok/src/mariadb/trunk/src/sql/mdl.cc:2447
#2  0x0000000000755623 in lock_table_names (thd=0x7fff84000cf8, options=..., tables_start=0x7fff84013038, tables_end=0x0, lock_wait_timeout=86400, flags=0) at /home/midenok/src/mariadb/trunk/src/sql/sql_base.cc:4068
#3  0x00000000007562ad in open_tables (thd=0x7fff84000cf8, options=..., start=0x7ffff461a8e8, counter=0x7ffff461a894, sroutine_to_open_list=0x7fff84004be8, flags=0, prelocking_strategy=0x7ffff461a898) at /home/midenok/src/mariadb/trunk/src/sql/sql_base.cc:4274
#4  0x000000000091edb3 in open_tables (thd=0x7fff84000cf8, options=..., tables=0x7ffff461a8e8, counter=0x7ffff461a894, flags=0, prelocking_strategy=0x7ffff461a898) at /home/midenok/src/mariadb/trunk/src/sql/sql_base.h:258
#5  0x0000000000913405 in open_tables (thd=0x7fff84000cf8, tables=0x7ffff461a8e8, counter=0x7ffff461a894, flags=0, prelocking_strategy=0x7ffff461a898) at /home/midenok/src/mariadb/trunk/src/sql/sql_base.h:267
#6  0x000000000090fb0c in mysql_alter_table (thd=0x7fff84000cf8, new_db=0x7fff840054a8, new_name=0x7fff840058b0, create_info=0x7ffff461b150, table_list=0x7fff84013038, alter_info=0x7ffff461b098, order_num=0, order=0x0, ignore=false) at /home/midenok/src/mariadb/trunk/src/sql/sql_table.cc:9369

#0  MDL_context::acquire_lock (this=0x7fff84000e18, mdl_request=0x7fff84013478, lock_wait_timeout=86400) at /home/midenok/src/mariadb/trunk/src/sql/mdl.cc:2241
#1  0x000000000075390c in open_table_get_mdl_lock (thd=0x7fff84000cf8, ot_ctx=0x7ffff4617b28, mdl_request=0x7fff84013478, flags=0, mdl_ticket=0x7ffff4617790) at /home/midenok/src/mariadb/trunk/src/sql/sql_base.cc:1608
#2  0x0000000000752612 in open_table (thd=0x7fff84000cf8, table_list=0x7fff84013038, ot_ctx=0x7ffff4617b28) at /home/midenok/src/mariadb/trunk/src/sql/sql_base.cc:1868
#3  0x00000000007578c5 in open_and_process_table (thd=0x7fff84000cf8, tables=0x7fff84013038, counter=0x7ffff461a894, flags=0, prelocking_strategy=0x7ffff461a898, has_prelocking_list=false, ot_ctx=0x7ffff4617b28) at /home/midenok/src/mariadb/trunk/src/sql/sql_base.cc:3806
#4  0x0000000000756452 in open_tables (thd=0x7fff84000cf8, options=..., start=0x7ffff461a8e8, counter=0x7ffff461a894, sroutine_to_open_list=0x7fff84004be8, flags=0, prelocking_strategy=0x7ffff461a898) at /home/midenok/src/mariadb/trunk/src/sql/sql_base.cc:4305
#5  0x000000000091edb3 in open_tables (thd=0x7fff84000cf8, options=..., tables=0x7ffff461a8e8, counter=0x7ffff461a894, flags=0, prelocking_strategy=0x7ffff461a898) at /home/midenok/src/mariadb/trunk/src/sql/sql_base.h:258
#6  0x0000000000913405 in open_tables (thd=0x7fff84000cf8, tables=0x7ffff461a8e8, counter=0x7ffff461a894, flags=0, prelocking_strategy=0x7ffff461a898) at /home/midenok/src/mariadb/trunk/src/sql/sql_base.h:267
#7  0x000000000090fb0c in mysql_alter_table (thd=0x7fff84000cf8, new_db=0x7fff840054a8, new_name=0x7fff840058b0, create_info=0x7ffff461b150, table_list=0x7fff84013038, alter_info=0x7ffff461b098, order_num=0, order=0x0, ignore=false) at /home/midenok/src/mariadb/trunk/src/sql/sql_table.cc:9369

#0  MDL_context::acquire_lock (this=0x7fff84000e18, mdl_request=0x7ffff4617558, lock_wait_timeout=86400) at /home/midenok/src/mariadb/trunk/src/sql/mdl.cc:2241
#1  0x0000000000753269 in open_table (thd=0x7fff84000cf8, table_list=0x7fff84013038, ot_ctx=0x7ffff4617b28) at /home/midenok/src/mariadb/trunk/src/sql/sql_base.cc:2134
#2  0x00000000007578c5 in open_and_process_table (thd=0x7fff84000cf8, tables=0x7fff84013038, counter=0x7ffff461a894, flags=0, prelocking_strategy=0x7ffff461a898, has_prelocking_list=false, ot_ctx=0x7ffff4617b28) at /home/midenok/src/mariadb/trunk/src/sql/sql_base.cc:3806
#3  0x0000000000756452 in open_tables (thd=0x7fff84000cf8, options=..., start=0x7ffff461a8e8, counter=0x7ffff461a894, sroutine_to_open_list=0x7fff84004be8, flags=0, prelocking_strategy=0x7ffff461a898) at /home/midenok/src/mariadb/trunk/src/sql/sql_base.cc:4305
#4  0x000000000091edb3 in open_tables (thd=0x7fff84000cf8, options=..., tables=0x7ffff461a8e8, counter=0x7ffff461a894, flags=0, prelocking_strategy=0x7ffff461a898) at /home/midenok/src/mariadb/trunk/src/sql/sql_base.h:258
#5  0x0000000000913405 in open_tables (thd=0x7fff84000cf8, tables=0x7ffff461a8e8, counter=0x7ffff461a894, flags=0, prelocking_strategy=0x7ffff461a898) at /home/midenok/src/mariadb/trunk/src/sql/sql_base.h:267
#6  0x000000000090fb0c in mysql_alter_table (thd=0x7fff84000cf8, new_db=0x7fff840054a8, new_name=0x7fff840058b0, create_info=0x7ffff461b150, table_list=0x7fff84013038, alter_info=0x7ffff461b098, order_num=0, order=0x0, ignore=false) at /home/midenok/src/mariadb/trunk/src/sql/sql_table.cc:9369

#0  MDL_context::acquire_lock (this=0x7fff84000e18, mdl_request=0x7ffff4617a68, lock_wait_timeout=86400) at /home/midenok/src/mariadb/trunk/src/sql/mdl.cc:2241
#1  0x00000000009c6b49 in MDL_context::upgrade_shared_lock (this=0x7fff84000e18, mdl_ticket=0x7fff84041830, new_type=MDL_SHARED_NO_WRITE, lock_wait_timeout=86400) at /home/midenok/src/mariadb/trunk/src/sql/mdl.cc:2523
#2  0x0000000000911116 in mysql_alter_table (thd=0x7fff84000cf8, new_db=0x7fff840054a8, new_name=0x7fff840058b0, create_info=0x7ffff461b150, table_list=0x7fff84013038, alter_info=0x7ffff461b098, order_num=0, order=0x0, ignore=false) at /home/midenok/src/mariadb/trunk/src/sql/sql_table.cc:9769

#0  MDL_context::acquire_lock (this=0x7fff84000e18, mdl_request=0x7ffff4617638, lock_wait_timeout=86400) at /home/midenok/src/mariadb/trunk/src/sql/mdl.cc:2241
#1  0x00000000009c6b49 in MDL_context::upgrade_shared_lock (this=0x7fff84000e18, mdl_ticket=0x7fff84041830, new_type=MDL_EXCLUSIVE, lock_wait_timeout=86400) at /home/midenok/src/mariadb/trunk/src/sql/mdl.cc:2523
#2  0x0000000000751675 in wait_while_table_is_used (thd=0x7fff84000cf8, table=0x7fff840a8208, function=HA_EXTRA_NOT_USED) at /home/midenok/src/mariadb/trunk/src/sql/sql_base.cc:1373
#3  0x0000000000d9d3df in fast_alter_partition_table (thd=0x7fff84000cf8, table=0x7fff840a8208, alter_info=0x7ffff461b098, create_info=0x7ffff461b150, table_list=0x7fff84013038, db=0x7ffff4619e08, table_name=0x7ffff4619e18) at /home/midenok/src/mariadb/trunk/src/sql/sql_partition.cc:7423
#4  0x00000000009111bf in mysql_alter_table (thd=0x7fff84000cf8, new_db=0x7fff840054a8, new_name=0x7fff840058b0, create_info=0x7ffff461b150, table_list=0x7fff84013038, alter_info=0x7ffff461b098, order_num=0, order=0x0, ignore=false) at /home/midenok/src/mariadb/trunk/src/sql/sql_table.cc:9777

[midenok]

Bug 2: partition_rotation failure

Reproduce

Apply this patch:

--- a/sql/sql_partition.cc
+++ b/sql/sql_partition.cc
@@ -7277,7 +7277,8 @@ uint fast_alter_partition_table(THD *thd, TABLE *table,
   }
   else if ((alter_info->partition_flags & ALTER_PARTITION_ADD) &&
            (part_info->part_type == RANGE_PARTITION ||
-            part_info->part_type == LIST_PARTITION))
+            part_info->part_type == LIST_PARTITION ||
+            part_info->part_type == VERSIONING_PARTITION))
   {
     /*
       ADD RANGE/LIST PARTITIONS

Result

--- /home/midenok/src/mariadb/trunk/src/mysql-test/suite/versioning/r/partition_rotation.result 2019-07-02 11:33:23.990282711 +0300
+++ /home/midenok/src/mariadb/trunk/src/mysql-test/suite/versioning/r/partition_rotation.reject 2019-07-02 19:31:31.861498467 +0300
@@ -30,7 +30,7 @@
 select subpartition_name,partition_description,table_rows from information_schema.partitions where table_schema='test' and table_name='t1';
 subpartition_name      partition_description   table_rows
 p1sp0  2001-02-04 00:00:00     1
-p1sp1  2001-02-04 00:00:00     0
+p1sp1  2001-02-04 00:00:00     1
 p0sp0  2001-02-05 00:00:00     1
 p0sp1  2001-02-05 00:00:00     1
 p2sp0  2001-02-06 00:00:00     0
@@ -281,6 +281,8 @@
 select *, row_end from t1 partition (p1);
 i      row_end
 1      2000-01-02 00:00:01.000000
+2      2000-01-03 00:00:01.000000
+3      2000-01-04 00:00:01.000000
 select *, row_end from t1 partition (p2);
 i      row_end
 2      2000-01-03 00:00:01.000000

Cause

When partition is overfilled it requires copy mode to redistribute history records.

Notes

This is not applicable to auto-create since it will auto-create partitions in advance.

[midenok]

Notes on REORGANIZE

Renaming works

create or replace table t1 (x int) with system versioning
partition by system_time;
alter table t1 reorganize partition p0 into (partition custom_name history);

Splitting doesn't work

create or replace table t1 (x int) with system versioning
partition by system_time;
alter table t1 reorganize partition p0 into (partition p0 history, partition p1 history);

ERROR 1510 (HY000): REORGANIZE PARTITION can only be used to reorganize partitions not to change their numbers

Merging doesn't work

create or replace table t1 (x int) with system versioning
partition by system_time partitions 3;
alter table t1 reorganize partition p0, p1 into (partition p00 history);

ERROR 1510 (HY000): REORGANIZE PARTITION can only be used to reorganize partitions not to change their numbers

Error thrown

#0  my_error (nr=1510, MyFlags=0) at /home/midenok/src/mariadb/trunk/src/mysys/my_error.c:113
#1  0x0000000000d9937a in prep_alter_part_table (thd=0x7fff7c000cf8, table=0x7fff7c09ed58, alter_info=0x7ffff461b098, create_info=0x7ffff461b150, alter_ctx=0x7ffff4619df8, partition_changed=0x7ffff4619a1a, fast_alter_table=0x7ffff4619a19) at /home/midenok/src/mariadb/trunk/src/sql/sql_partition.cc:5607
#2  0x0000000000910e7b in mysql_alter_table (thd=0x7fff7c000cf8, new_db=0x7fff7c0054a8, new_name=0x7fff7c0058b0, create_info=0x7ffff461b150, table_list=0x7fff7c013058, alter_info=0x7ffff461b098, order_num=0, order=0x0, ignore=false) at /home/midenok/src/mariadb/trunk/src/sql/sql_table.cc:9713
#3  0x00000000009c023e in Sql_cmd_alter_table::execute (this=0x7fff7c013b00, thd=0x7fff7c000cf8) at /home/midenok/src/mariadb/trunk/src/sql/sql_alter.cc:490
#4  0x00000000008155af in mysql_execute_command (thd=0x7fff7c000cf8) at /home/midenok/src/mariadb/trunk/src/sql/sql_parse.cc:6347
#5  0x0000000000805d90 in mysql_parse (thd=0x7fff7c000cf8, rawbuf=0x7fff7c012f20 "alter table t1 reorganize partition p0, p1 into (partition p00 history)", length=71, parser_state=0x7ffff461e640, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/trunk/src/sql/sql_parse.cc:8157

5603          if (!(tab_part_info->part_type == RANGE_PARTITION ||
5604                tab_part_info->part_type == LIST_PARTITION) &&
5605               (num_parts_new != num_parts_reorged))
5606          {
5607            my_error(ER_REORG_HASH_ONLY_ON_SAME_NO, MYF(0));
5608            goto err;
5609          }

REORGANIZE example for LIST

create or replace table n2 (x int)
partition by list(x) (
    partition p0 values in (5, 10, 15),
    partition p1 values in (6, 12, 18));

alter table n2 reorganize partition p0, p1 into (
    partition p0 values in (5, 10, 15, 12),
    partition p1 values in (6, 18));

RANGE partitioning has limitations:

ERROR 1520 (HY000): Reorganize of range partitions cannot change total ranges except for last partition where it can extend the range

TODO

• [x] Add test cases for REORGANIZE

[midenok]

Info: handle manager

/* 
 * sql_manager.cc
 * This thread manages various maintenance tasks.
 *
 *   o Flushing the tables every flush_time seconds.
 *   o Berkeley DB: removing unneeded log files.
 */
...
/* Start handle manager thread */
void start_handle_manager()
{
  DBUG_ENTER("start_handle_manager");
  abort_manager = false;
  if (flush_time && flush_time != ~(ulong) 0L)
  {
    pthread_t hThread;
    int error;
    if ((error= mysql_thread_create(key_thread_handle_manager,
                                    &hThread, &connection_attrib,
                                    handle_manager, 0)))
      sql_print_warning("Can't create handle_manager thread (errno= %d)",
                        error);
  }
  DBUG_VOID_RETURN;
}

Manager thread is currently depends on flush_time so now it its main sole task (what about 2nd task from comment?).

Info: slave background

/*
  Start the slave background thread.

  This thread is currently used for two purposes:

  1. To load the GTID state from mysql.gtid_slave_pos at server start; reading
     from table requires valid THD, which is otherwise not available during
     server init.

  2. To kill worker thread transactions during parallel replication, when a
     storage engine attempts to take an errorneous conflicting lock that would
     cause a deadlock. Killing is done asynchroneously, as the kill may not
     be safe within the context of a callback from inside storage engine
     locking code.
*/
static int
start_slave_background_thread()
{
  pthread_t th;

  slave_background_thread_running= true;
  slave_background_thread_stop= false;
  slave_background_thread_gtid_loaded= false;
  if (mysql_thread_create(key_thread_slave_background,
                          &th, &connection_attrib, handle_slave_background,
                          NULL))
  {
    sql_print_error("Failed to create thread while initialising slave");
    return 1;
  }
  mysql_mutex_lock(&LOCK_slave_background);
  while (!slave_background_thread_gtid_loaded)
    mysql_cond_wait(&COND_slave_background, &LOCK_slave_background);
  mysql_mutex_unlock(&LOCK_slave_background);

  return 0;
}

All threads

extern PSI_thread_key key_thread_delayed_insert,
  key_thread_handle_manager, key_thread_kill_server, key_thread_main,
  key_thread_one_connection, key_thread_signal_hand,
  key_thread_slave_background, key_rpl_parallel_thread;

Calls of interest

• mysql_thread_create()
• parse_sql()
• mysql_thread_create()
• start_handle_manager()
• delayed_get_table()
• Delayed_insert::Delayed_insert()
• handle_slave_background()
• Prepared_statement::execute()
• mysql_execute_command()
• mysql_parse()
• Execute_sql_statement::execute_server_code()

Info: thread system

/* Flags for the THD::system_thread variable */
enum enum_thread_type
{
  NON_SYSTEM_THREAD= 0,
  SYSTEM_THREAD_DELAYED_INSERT= 1,
  SYSTEM_THREAD_SLAVE_IO= 2,
  SYSTEM_THREAD_SLAVE_SQL= 4,
  SYSTEM_THREAD_EVENT_SCHEDULER= 8,
  SYSTEM_THREAD_EVENT_WORKER= 16,
  SYSTEM_THREAD_BINLOG_BACKGROUND= 32,
  SYSTEM_THREAD_SLAVE_BACKGROUND= 64,
  SYSTEM_THREAD_GENERIC= 128,
  SYSTEM_THREAD_SEMISYNC_MASTER_BACKGROUND= 256
};

MYSQL_THD create_thd()
{
  THD *thd= new THD(next_thread_id());
  thd->thread_stack= (char*) &thd;
  thd->store_globals();
  thd->set_command(COM_DAEMON);
  thd->system_thread= SYSTEM_THREAD_GENERIC;
  thd->security_ctx->host_or_ip="";
  server_threads.insert(thd);
  return thd;
}

extern THD_list server_threads;

/** THD registry */
class THD_list
{
  I_List<THD> threads;

[midenok]

Bug 3: thread name is not defined

Segfault on accessing uninitialized my_thread_var.

Result

#0  0x00000000014ae3a8 in my_thread_name () at /home/midenok/src/mariadb/trunk/src/mysys/my_thr_init.c:426
#1  0x00000000014c1525 in wt_thd_lazy_init (thd=0x7fff7c004190, ds=0x7fff7c001590, ts=0x7fff7c001588, dl=0x7fff7c0015a0, tl=0x7fff7c001598) at /home/midenok/src/mariadb/trunk/src/mysys/waiting_threads.c:512
#2  0x0000000000782636 in THD::THD (this=0x7fff7c000b28, id=10, is_wsrep_applier=false) at /home/midenok/src/mariadb/trunk/src/sql/sql_class.cc:785
#3  0x0000000000990290 in query_thread (arg=0x0) at /home/midenok/src/mariadb/trunk/src/sql/partition_info.cc:895
#4  0x00007ffff7f82182 in start_thread (arg=<optimized out>) at pthread_create.c:486
#5  0x00007ffff7433b1f in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

(gdb) p my_thread_var
$5 = (struct st_my_thread_var *) 0x0

Fix

Cured by my_thread_init().

Bug 4: uninitialized thd->lex

#0  __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
#1  0x00007ffff733b535 in __GI_abort () at abort.c:79
#2  0x00007ffff733b40f in __assert_fail_base (fmt=0x7ffff74c9588 "%s%s%s:%u: %s%sAssertion `%s' failed.\n%n", assertion=0x1563958 "thd->lex->m_sql_cmd == __null", file=0x15625b4 "/home/midenok/src/mariadb/trunk/src/sql/sql_parse.cc", line=10144, function=<optimized out>) at assert.c:92
#3  0x00007ffff734b012 in __GI___assert_fail (assertion=0x1563958 "thd->lex->m_sql_cmd == __null", file=0x15625b4 "/home/midenok/src/mariadb/trunk/src/sql/sql_parse.cc", line=10144, function=0x1563915 "bool parse_sql(THD *, Parser_state *, Object_creation_ctx *, bool)") at assert.c:101
#4  0x000000000081b8ab in parse_sql (thd=0x7fff7c000cf8, parser_state=0x7ffff45ecd88, creation_ctx=0x0, do_pfs_digest=false) at /home/midenok/src/mariadb/trunk/src/sql/sql_parse.cc:10144
#5  0x0000000000990469 in query_thread (arg=0x7fff780b6a38) at /home/midenok/src/mariadb/trunk/src/sql/partition_info.cc:910
#6  0x00007ffff7f82182 in start_thread (arg=<optimized out>) at pthread_create.c:486
#7  0x00007ffff7433b1f in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

(gdb) p thd->lex->m_sql_cmd
$10 = (Sql_cmd *) 0xa5a5a5a5a5a5a5a5

Fix

Call lex_start(thd)

TODO

• [ ] Handle ALTER failure and fix it;
• [x] Test replication
• [x] Test SHOW PROCESSLIST (and KILL?)

[midenok]

Bug 5: binlog needs thd->query()

#0  __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
#1  0x00007ffff733b535 in __GI_abort () at abort.c:79
#2  0x00007ffff733b40f in __assert_fail_base (fmt=0x7ffff74c9588 "%s%s%s:%u: %s%sAssertion `%s' failed.\n%n", assertion=0x15594bc "query_arg", file=0x1557a89 "/home/midenok/src/mariadb/trunk/src/sql/sql_class.cc", line=7038, function=<optimized out>) at assert.c:92
#3  0x00007ffff734b012 in __GI___assert_fail (assertion=0x15594bc "query_arg", file=0x1557a89 "/home/midenok/src/mariadb/trunk/src/sql/sql_class.cc", line=7038, function=0x15594c6 "int THD::binlog_query(THD::enum_binlog_query_type, const char *, ulong, bool, bool, bool, int)") at assert.c:101
#4  0x0000000000798152 in THD::binlog_query (this=0x7fff78000cf8, qtype=THD::STMT_QUERY_TYPE, query_arg=0x0, query_len=0, is_trans=false, direct=false, suppress_use=false, errcode=0) at /home/midenok/src/mariadb/trunk/src/sql/sql_class.cc:7038
#5  0x00000000009019e4 in write_bin_log (thd=0x7fff78000cf8, clear_error=false, query=0x0, query_length=0, is_trans=false) at /home/midenok/src/mariadb/trunk/src/sql/sql_table.cc:1985
#6  0x0000000000d9dc10 in fast_alter_partition_table (thd=0x7fff78000cf8, table=0x7fff78008e28, alter_info=0x7ffff45ea5b8, create_info=0x7ffff45ea670, table_list=0x7fff78007ca0, db=0x7ffff45e9328, table_name=0x7ffff45e9338) at /home/midenok/src/mariadb/trunk/src/sql/sql_partition.cc:7435
#7  0x00000000009111bf in mysql_alter_table (thd=0x7fff78000cf8, new_db=0x7fff780054a8, new_name=0x7fff780058b0, create_info=0x7ffff45ea670, table_list=0x7fff78007ca0, alter_info=0x7ffff45ea5b8, order_num=0, order=0x0, ignore=false) at /home/midenok/src/mariadb/trunk/src/sql/sql_table.cc:9777
#8  0x00000000009c0a7e in Sql_cmd_alter_table::execute (this=0x7fff780088a0, thd=0x7fff78000cf8) at /home/midenok/src/mariadb/trunk/src/sql/sql_alter.cc:490
#9  0x00000000008155af in mysql_execute_command (thd=0x7fff78000cf8) at /home/midenok/src/mariadb/trunk/src/sql/sql_parse.cc:6347
#10 0x00000000009904c3 in query_thread (arg=0x7fff84047ef8) at /home/midenok/src/mariadb/trunk/src/sql/partition_info.cc:924

Fix

Call thd->set_query_and_id().

[midenok]

Error handling

There are 2 types of error: A. Before table is opened; B. After table is opened

Errors of type A must be thrown always. Errors of type B should be thrown with periodicity of T per specific table.

When multiple threads try ADD simultaneously one of them succeeds and the others fail with ER_SAME_NAME_PARTITION. In such case no error should be printed at all.

Log query

#0  general_log_write (thd=0x7fff84000cf8, command=COM_QUERY, query=0x7fff84014020 "insert into t1 values (2)", query_length=25) at /home/midenok/src/mariadb/trunk/src/sql/log.cc:6665
#1  0x00000000008019ca in dispatch_command (command=COM_QUERY, thd=0x7fff84000cf8, packet=0x7fff84007a89 "insert into t1 values (2)", packet_length=25, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/trunk/src/sql/sql_parse.cc:1798
#2  0x00000000008057df in do_command (thd=0x7fff84000cf8) at /home/midenok/src/mariadb/trunk/src/sql/sql_parse.cc:1361
#3  0x00000000009b3858 in do_handle_one_connection (connect=0x34b1ac8) at /home/midenok/src/mariadb/trunk/src/sql/sql_connect.cc:1403
#4  0x00000000009b362a in handle_one_connection (arg=0x34b1ac8) at /home/midenok/src/mariadb/trunk/src/sql/sql_connect.cc:1306
#5  0x00007ffff7f82182 in start_thread (arg=<optimized out>) at pthread_create.c:486
#6  0x00007ffff7433b1f in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

frame 1

1785      case COM_QUERY:
1786      {
1787        DBUG_ASSERT(thd->m_digest == NULL);
1788        thd->m_digest= & thd->m_digest_state;
1789        thd->m_digest->reset(thd->m_token_array, max_digest_length);
1790
1791        if (unlikely(alloc_query(thd, packet, packet_length)))
1792          break;                                    // fatal error is set
1793        MYSQL_QUERY_START(thd->query(), thd->thread_id,
1794                          thd->get_db(),
1795                          &thd->security_ctx->priv_user[0],
1796                          (char *) thd->security_ctx->host_or_ip);
1797        char *packet_end= thd->query() + thd->query_length();
1798        general_log_write(thd, command, thd->query(), thd->query_length());
1799        DBUG_PRINT("query",("%-.4096s",thd->query()));

Log error

Warning

#0  error_log_print (level=WARNING_LEVEL, format=0x1543e32 "%s: %s", args=0x7ffff4619e50) at /home/midenok/src/mariadb/trunk/src/sql/log.cc:6607
#1  0x0000000000cc20f1 in sql_print_warning (format=0x1543e32 "%s: %s") at /home/midenok/src/mariadb/trunk/src/sql/log.cc:8893
#2  0x000000000069ae8f in my_message_sql (error=4114, str=0x7ffff461a0f0 "Versioned table `test`.`t1`: last HISTORY partition (`p0`) is out of LIMIT, need more HISTORY partitions", MyFlags=2112) at /home/midenok/src/mariadb/trunk/src/sql/mysqld.cc:3382
#3  0x000000000149bec0 in my_error (nr=4114, MyFlags=2112) at /home/midenok/src/mariadb/trunk/src/mysys/my_error.c:125

Error

#0  sql_print_error (format=0x1543e32 "%s: %s") at /home/midenok/src/mariadb/trunk/src/sql/log.cc:8877
#1  0x000000000069ae8f in my_message_sql (error=1041, str=0x7ffff45ecab0 "Out of memory.", MyFlags=64) at /home/midenok/src/mariadb/trunk/src/sql/mysqld.cc:3382
#2  0x000000000149be30 in my_error (nr=1041, MyFlags=64) at /home/midenok/src/mariadb/trunk/src/mysys/my_error.c:125
#3  0x000000000098d766 in query_thread (arg=0x7fff78052328) at /home/midenok/src/mariadb/trunk/src/sql/partition_info.cc:939
#4  0x00007ffff7f82182 in start_thread (arg=<optimized out>) at pthread_create.c:486
#5  0x00007ffff7433b1f in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

frame 1

3381      if (unlikely(!thd) || thd->log_all_errors || (MyFlags & ME_ERROR_LOG))
3382        (*func)("%s: %s", my_progname_short, str); /* purecov: inspected */

[midenok]

1. ALTER is locked after incrementing tdc->ref_count

#4  0x0000000000a8d42e in inline_mysql_cond_wait (that=0x7fff8004a8a8, mutex=0x7fff8004a7f8, src_file=0x15aade4 "/home/midenok/src/mariadb/trunk/src/sql/table_cache.cc", src_line=1178) at /home/midenok/src/mariadb/trunk/src/include/mysql/psi/mysql_thread.h:1178
#5  0x0000000000a90656 in tdc_remove_table (thd=0x7fff74000cf8, remove_type=TDC_RT_REMOVE_NOT_OWN, db=0x7fff8004be08 "test", table_name=0x7fff8004be0d "t1", kill_delayed_threads=false) at /home/midenok/src/mariadb/trunk/src/sql/table_cache.cc:1178
#6  0x00000000007506cf in wait_while_table_is_used (thd=0x7fff74000cf8, table=0x7fff74009c68, function=HA_EXTRA_NOT_USED) at /home/midenok/src/mariadb/trunk/src/sql/sql_base.cc:1378
#7  0x0000000000d9b53f in fast_alter_partition_table (thd=0x7fff74000cf8, table=0x7fff74009c68, alter_info=0x7ffff45ea558, create_info=0x7ffff45ea610, table_list=0x7fff74007ca0, db=0x7ffff45e92c8, table_name=0x7ffff45e92d8) at /home/midenok/src/mariadb/trunk/src/sql/sql_partition.cc:7423
#8  0x000000000090e37f in mysql_alter_table (thd=0x7fff74000cf8, new_db=0x7fff740054a8, new_name=0x7fff740058b0, create_info=0x7ffff45ea610, table_list=0x7fff74007ca0, alter_info=0x7ffff45ea558, order_num=0, order=0x0, ignore=false) at /home/midenok/src/mariadb/trunk/src/sql/sql_table.cc:9777
#9  0x00000000009be74e in Sql_cmd_alter_table::execute (this=0x7fff740088d0, thd=0x7fff74000cf8) at /home/midenok/src/mariadb/trunk/src/sql/sql_alter.cc:490
#10 0x00000000008145af in mysql_execute_command (thd=0x7fff74000cf8) at /home/midenok/src/mariadb/trunk/src/sql/sql_parse.cc:6347
#11 0x000000000098dc8f in vers_add_hist_part_thread (arg=0x7fff8004be38) at /home/midenok/src/mariadb/trunk/src/sql/partition_info.cc:946
#12 0x00007ffff7f82182 in start_thread (arg=<optimized out>) at pthread_create.c:486

1153      if (remove_type != TDC_RT_REMOVE_UNUSED)
1154      {
1155        /*
1156          Even though current thread holds exclusive metadata lock on this share
1157          (asserted above), concurrent FLUSH TABLES threads may be in process of
1158          closing unused table instances belonging to this share. E.g.:
1159          thr1 (FLUSH TABLES): table= share->tdc.free_tables.pop_front();
1160          thr1 (FLUSH TABLES): share->tdc.all_tables.remove(table);
1161          thr2 (ALTER TABLE): tdc_remove_table();
1162          thr1 (FLUSH TABLES): intern_close_table(table);
1163
1164          Current remove type assumes that all table instances (except for those
1165          that are owned by current thread) must be closed before
1166          thd_remove_table() returns. Wait for such tables now.
1167
1168          intern_close_table() decrements ref_count and signals COND_release. When
1169          ref_count drops down to number of references owned by current thread
1170          waiting is completed.
1171
1172          Unfortunately TABLE_SHARE::wait_for_old_version() cannot be used here
1173          because it waits for all table instances, whereas we have to wait only
1174          for those that are not owned by current thread.
1175        */
1176        mysql_mutex_lock(&element->LOCK_table_share);
1177        while (element->ref_count > my_refs)
1178          mysql_cond_wait(&element->COND_release, &element->LOCK_table_share);

Cause

Manually incremented ref_count doesn't get into my_refs. ALTER invalidates current TABLE_SHARE, so no big sense in passing it to thread.

2. another lock

#4  0x0000000001393125 in inline_mysql_cond_wait (that=0x61a0001418f8, mutex=0x61a000141848, src_file=0x3370720 <.str> "/home/midenok/src/mariadb/trunk/src/sql/table_cache.cc", src_line=1178) at /home/midenok/src/mariadb/trunk/src/include/mysql/psi/mysql_thread.h:1178
#5  0x000000000139b7aa in tdc_remove_table (thd=0x62b0000bd208, remove_type=TDC_RT_REMOVE_ALL, db=0x7fffd0379580 "test", table_name=0x7fffd0379585 "t1", kill_delayed_threads=false) at /home/midenok/src/mariadb/trunk/src/sql/table_cache.cc:1178
#6  0x00000000009e222a in close_all_tables_for_name (thd=0x62b0000bd208, share=0x61b000020da0, extra=HA_EXTRA_NOT_USED, skip_table=0x0) at /home/midenok/src/mariadb/trunk/src/sql/sql_base.cc:814
#7  0x0000000001c24602 in alter_partition_lock_handling (lpt=0x7fffd0379e60) at /home/midenok/src/mariadb/trunk/src/sql/sql_partition.cc:6825
#8  0x0000000001c21481 in fast_alter_partition_table (thd=0x62b0000bd208, table=0x620000038088, alter_info=0x7fffd037e9b0, create_info=0x7fffd037e780, table_list=0x61c0000900a0, db=0x7fffd037a5c0, table_name=0x7fffd037a5d0) at /home/midenok/src/mariadb/trunk/src/sql/sql_partition.cc:7453
#9  0x0000000000f1fe36 in mysql_alter_table (thd=0x62b0000bd208, new_db=0x62b0000c19b8, new_name=0x62b0000c1dc0, create_info=0x7fffd037e780, table_list=0x61c0000900a0, alter_info=0x7fffd037e9b0, order_num=0, order=0x0, ignore=false) at /home/midenok/src/mariadb/trunk/src/sql/sql_table.cc:9777
#10 0x000000000114731c in Sql_cmd_alter_table::execute (this=0x60400000e2b0, thd=0x62b0000bd208) at /home/midenok/src/mariadb/trunk/src/sql/sql_alter.cc:490
#11 0x0000000000c0f01d in mysql_execute_command (thd=0x62b0000bd208) at /home/midenok/src/mariadb/trunk/src/sql/sql_parse.cc:6347
#12 0x00000000010b3abc in vers_add_hist_part_thread (arg=0x60c000016188) at /home/midenok/src/mariadb/trunk/src/sql/partition_info.cc:971
#13 0x00007ffff7f82182 in start_thread (arg=<optimized out>) at pthread_create.c:486

While TDC_RT_REMOVE_NOT_OWN can be cured by creating TABLE; TDC_RT_REMOVE_ALL doesn't increment my_refs.

Decision

Reopen table and ensure TABLE_SHARE pointer is the same.

[midenok]

Access denied error thrown

#0  my_error (nr=1142, MyFlags=0) at /home/midenok/src/mariadb/trunk/src/mysys/my_error.c:113
#1  0x0000000000728d18 in check_grant (thd=0x7fff90000d28, want_access=8192, tables=0x7fff90007cd0, any_combination_will_do=false, number=0, no_errors=false) at /home/midenok/src/mariadb/trunk/src/sql/sql_acl.cc:8072
#2  0x00000000009be1ff in Sql_cmd_alter_table::execute (this=0x7fff90008900, thd=0x7fff90000d28) at /home/midenok/src/mariadb/trunk/src/sql/sql_alter.cc:447
#3  0x00000000008145af in mysql_execute_command (thd=0x7fff90000d28) at /home/midenok/src/mariadb/trunk/src/sql/sql_parse.cc:6347
#4  0x000000000098dbdf in vers_add_hist_part_thread (arg=0x7fffa406c4e8) at /home/midenok/src/mariadb/trunk/src/sql/partition_info.cc:948
#5  0x000000000142f054 in pfs_spawn_thread (arg=0x2cd9298) at /home/midenok/src/mariadb/trunk/src/storage/perfschema/pfs.cc:1862
#6  0x00007ffff7f82182 in start_thread (arg=<optimized out>) at pthread_create.c:486
#7  0x00007ffff7433b1f in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

[midenok]

MDEV-20068 History partition rotation is not done under LOCK TABLES

Old vers_get_partition_id()

Reference

#0  ha_innobase::records_new (this=0x7fffcabcc088) at /home/midenok/src/mariadb/midenok/src/storage/innobase/handler/ha_innodb.cc:15604
#1  0x0000555555fa26a7 in ha_innopart::part_recs_slow (this=0x7fffcabcc088, _part_elem=0x7fffebbf8548) at /home/midenok/src/mariadb/midenok/src/storage/innobase/handler/ha_innopart.cc:3116
#2  0x0000555555c33edb in partition_info::vers_limit_exceed (this=0x7fffebbf82c8, part=0x7fffebbf8548) at /home/midenok/src/mariadb/midenok/src/sql/partition_info.h:479
#3  0x0000555555f0f901 in vers_get_partition_id (part_info=0x7fffebbf82c8, part_id=0x7ffff7f51f4c, func_value=0x7ffff7f51f68) at /home/midenok/src/mariadb/midenok/src/sql/sql_partition.cc:3465
#4  0x0000555555f08b3d in get_parts_for_update (old_data=0x7fffebbf7da0 "\361\001", new_data=0x7fffebbf7d88 "\361\001", rec0=0x7fffebbf7d88 "\361\001", part_info=0x7fffebbf82c8, old_part_id=0x7ffff7f51f50, new_part_id=0x7ffff7f51f4c, new_func_value=0x7ffff7f51f68) at /home/midenok/src/mariadb/midenok/src/sql/sql_partition.cc:312
#5  0x000055555650bdfe in Partition_helper::ph_update_row (this=0x7fffcabcc7e0, old_data=0x7fffebbf7da0 "\361\001", new_data=0x7fffebbf7d88 "\361\001") at /home/midenok/src/mariadb/midenok/src/sql/partitioning/partition_handler.cc:518
#6  0x0000555555fa686b in ha_innopart::delete_row (this=0x7fffcabcc088, record=0x7fffebbf7d88 "\361\001") at /home/midenok/src/mariadb/midenok/src/storage/innobase/handler/ha_innopart.h:1149
#7  0x0000555555d6e8f7 in handler::ha_delete_row (this=0x7fffcabcc088, buf=0x7fffebbf7d88 "\361\001") at /home/midenok/src/mariadb/midenok/src/sql/handler.cc:6055
#8  0x0000555555efb7c8 in TABLE::delete_row (this=0x7fffcaaacc70) at /home/midenok/src/mariadb/midenok/src/sql/sql_delete.cc:219
#9  0x0000555555ef8e50 in mysql_delete (thd=0x7fffec3e6070, table_list=0x7fffec7e6150, conds=0x0, order_list=0x7fffec3ea4d8, limit=18446744073709551615, options=0, result=0x0) at /home/midenok/src/mariadb/midenok/src/sql/sql_delete.cc:641
#10 0x0000555555b0375c in mysql_execute_command (thd=0x7fffec3e6070) at /home/midenok/src/mariadb/midenok/src/sql/sql_parse.cc:4572
#11 0x0000555555b0c19e in mysql_parse (thd=0x7fffec3e6070, rawbuf=0x7fffec7e6088 "delete from t1", length=14, parser_state=0x7ffff7f53670, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/midenok/src/sql/sql_parse.cc:7848

vers_get_partition_id() in SELECT

#0  vers_get_partition_id (part_info=0x359c860 <.str.19>, part_id=0x10002, func_value=0x621000000001) at /home/midenok/src/mariadb/trunk/src/sql/sql_partition.cc:3505
#1  0x0000000001aefbf8 in find_used_partitions (ppar=0x7fffe0d72a80, key_tree=0x6210000ab630) at /home/midenok/src/mariadb/trunk/src/sql/opt_range.cc:4472
#2  0x0000000001aea537 in prune_partitions (thd=0x62b00009a208, table=0x620000037088, pprune_cond=0x62b0000a37c0) at /home/midenok/src/mariadb/trunk/src/sql/opt_range.cc:3887
#3  0x0000000000cf864b in JOIN::optimize_inner (this=0x62b0000a2d70) at /home/midenok/src/mariadb/trunk/src/sql/sql_select.cc:2147
#4  0x0000000000ce6e17 in JOIN::optimize (this=0x62b0000a2d70) at /home/midenok/src/mariadb/trunk/src/sql/sql_select.cc:1627
#5  0x0000000000ccb712 in mysql_select (thd=0x62b00009a208, tables=0x62b0000a18c0, wild_num=1, fields=..., conds=0x62b0000a21b0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x62b0000a2d48, unit=0x62b00009e128, select_lex=0x62b0000a12f8) at /home/midenok/src/mariadb/trunk/src/sql/sql_select.cc:4655
#6  0x0000000000cca362 in handle_select (thd=0x62b00009a208, lex=0x62b00009e060, result=0x62b0000a2d48, setup_tables_done_option=0) at /home/midenok/src/mariadb/trunk/src/sql/sql_select.cc:412
#7  0x0000000000c13f78 in execute_sqlcom_select (thd=0x62b00009a208, all_tables=0x62b0000a18c0) at /home/midenok/src/mariadb/trunk/src/sql/sql_parse.cc:6605
#8  0x0000000000bfb42e in mysql_execute_command (thd=0x62b00009a208) at /home/midenok/src/mariadb/trunk/src/sql/sql_parse.cc:3884
#9  0x0000000000be8095 in mysql_parse (thd=0x62b00009a208, rawbuf=0x62b0000a1220 "select * from t1 for system_time all where row_end = @ts", length=56, parser_state=0x7fffe0d7ba80, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/trunk/src/sql/sql_parse.cc:8157

[midenok]

1. vers_get_partition_id()

#0  vers_get_partition_id (part_info=0xec9040e7d08, part_id=0x7fffe0d73400, func_value=0x39ab74e) at /home/midenok/src/mariadb/trunk/src/sql/sql_partition.cc:3505
#1  0x0000000001c299ee in get_partition_id_with_sub (part_info=0x61f000013cb8, part_id=0x7fffe0d73600, func_value=0x7fffe0d73610) at /home/midenok/src/mariadb/trunk/src/sql/sql_partition.cc:3787
#2  0x0000000002ecf0d8 in ha_partition::write_row (this=0x61d0001a54a0, buf=0x6190000acdf0 "\375\005") at /home/midenok/src/mariadb/trunk/src/sql/ha_partition.cc:4299
#3  0x000000000162fb99 in handler::ha_write_row (this=0x61d0001a54a0, buf=0x6190000acdf0 "\375\005") at /home/midenok/src/mariadb/trunk/src/sql/handler.cc:6654
#4  0x0000000000b1b684 in write_record (thd=0x62b00009a208, table=0x620000037088, info=0x7fffe0d74b20) at /home/midenok/src/mariadb/trunk/src/sql/sql_insert.cc:2050
#5  0x0000000000b0feba in mysql_insert (thd=0x62b00009a208, table_list=0x62b0000a1300, fields=..., values_list=..., update_fields=..., update_values=..., duplic=DUP_ERROR, ignore=false) at /home/midenok/src/mariadb/trunk/src/sql/sql_insert.cc:1075
#6  0x0000000000c0145d in mysql_execute_command (thd=0x62b00009a208) at /home/midenok/src/mariadb/trunk/src/sql/sql_parse.cc:4790
#7  0x0000000000be8095 in mysql_parse (thd=0x62b00009a208, rawbuf=0x62b0000a1220 "insert t1 values (4),(5)", length=24, parser_state=0x7fffe0d7ba80, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/trunk/src/sql/sql_parse.cc:8157

2. Assertion

#0  __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
#1  0x00007ffff732e535 in __GI_abort () at abort.c:79
#2  0x00007ffff732e40f in __assert_fail_base (fmt=0x7ffff74bc588 "%s%s%s:%u: %s%sAssertion `%s' failed.\n%n", assertion=0x322a640 <.str.20> "!is_set() || (m_status == DA_OK_BULK && is_bulk_op())", file=0x322a300 <.str.13> "/home/midenok/src/mariadb/trunk/src/sql/sql_error.cc", line=335, function=<optimized out>) at assert.c:92
#3  0x00007ffff733e012 in __GI___assert_fail (assertion=0x322a640 <.str.20> "!is_set() || (m_status == DA_OK_BULK && is_bulk_op())", file=0x322a300 <.str.13> "/home/midenok/src/mariadb/trunk/src/sql/sql_error.cc", line=335, function=0x322a6a0 <__PRETTY_FUNCTION__._ZN16Diagnostics_area13set_ok_statusEyyPKc> "void Diagnostics_area::set_ok_status(ulonglong, ulonglong, const char *)") at assert.c:101
#4  0x0000000000af6c1f in Diagnostics_area::set_ok_status (this=0x62b000060bd8, affected_rows=1, last_insert_id=0, message=0x0) at /home/midenok/src/mariadb/trunk/src/sql/sql_error.cc:335
#5  0x00000000009c811f in my_ok (thd=0x62b00005b208, affected_rows_arg=1, id=0, message=0x0) at /home/midenok/src/mariadb/trunk/src/sql/sql_class.h:5037
#6  0x0000000000b1198f in mysql_insert (thd=0x62b00005b208, table_list=0x62b0000622f8, fields=..., values_list=..., update_fields=..., update_values=..., duplic=DUP_ERROR, ignore=false) at /home/midenok/src/mariadb/trunk/src/sql/sql_insert.cc:1239

frame 4

35       DBUG_ASSERT(!is_set() || (m_status == DA_OK_BULK && is_bulk_op()));

bool is_set() const { return m_status != DA_EMPTY; }

(gdb) p is_set()
$3 = true
(gdb) p m_status
$1 = Diagnostics_area::DA_ERROR
(gdb) p m_message
$2 = "Versioned table `test`.`t1`: last HISTORY partition (`p1`) is out of INTERVAL, need more HISTORY partitions", '\000' <repeats 404 times>

1048      if ((level == Sql_condition::WARN_LEVEL_WARN) &&
1049          really_abort_on_warning())
1050      {
1051        /*
1052          FIXME:
1053          push_warning and strict SQL_MODE case.
1054        */
1055        level= Sql_condition::WARN_LEVEL_ERROR;
1056      }

Fix

Add vers_set_hist_part() to ha_partition::start_stmt().

[midenok]

Bug 4

These results still fail:

@@ -360,8 +380,8 @@
 Warning        4114    Versioned table `test`.`t1`: last HISTORY partition (`p1`) is out of INTERVAL, need more HISTORY partitions
 select subpartition_name,partition_description,table_rows from information_schema.partitions where table_schema='test' and table_name='t1';
 subpartition_name      partition_description   table_rows
-p1sp0  2001-02-04 00:00:00     1
-p1sp1  2001-02-04 00:00:00     1
+p1sp0  2001-02-04 00:00:00     0
+p1sp1  2001-02-04 00:00:00     0
 pnsp0  CURRENT 0
 pnsp1  CURRENT 0
 set timestamp=unix_timestamp('2001-02-04 10:20:55');
@@ -374,12 +394,12 @@
 subpartition_name      partition_description   table_rows
 p1sp0  2001-02-04 00:00:00     1
 p1sp1  2001-02-04 00:00:00     0
-p0sp0  2001-02-05 00:00:00     1
+p0sp0  2001-02-05 00:00:00     0
 p0sp1  2001-02-05 00:00:00     1
 p2sp0  2001-02-06 00:00:00     0
 p2sp1  2001-02-06 00:00:00     0
 pnsp0  CURRENT 0
-pnsp1  CURRENT 2
+pnsp1  CURRENT 0
 ## pruning check
 set @ts=(select partition_description from information_schema.partitions
 where table_schema='test' and table_name='t1' and partition_name='p0' limit 1);

Cause

For MyISAM LOCK TABLES holds table_rows from being updated.

[midenok]

Bug 5: leftover tmp files on fast ALTER ADD

Reproduce

-- source include/have_partition.inc
-- source suite/versioning/common.inc
-- source suite/versioning/engines.inc

set time_zone= "+00:00";
call mtr.add_suppression("need more HISTORY partitions");
call mtr.add_suppression("Duplicate partition name");
let $wait_no_alter= select count(*) = 0 from information_schema.processlist where info like 'ALTER TABLE %';
let $wait_alter= select count(*) > 0 from information_schema.processlist where info like 'ALTER TABLE %';

set system_versioning_alter_history=keep;

--echo # and this is how it usually goes:
set timestamp= unix_timestamp('2000-01-01 00:00:00');
create or replace table t1 (i int) with system versioning
partition by system_time interval 1 day;
lock tables t1 write; # prevent partition auto-creation

insert into t1 values (0);
set timestamp= unix_timestamp('2000-01-01 00:00:01');
update t1 set i= i + 1;
set timestamp= unix_timestamp('2000-01-02 00:00:01');
update t1 set i= i + 1;

alter table t1 add partition (partition p9 history);

unlock tables;
--let $wait_condition= $wait_no_alter
--source include/wait_condition.inc
drop table t1;

--echo # Test cleanup
drop database test;
create database test;

Result

Files not deleted:

t1#P#p0#TMP#.MYD
t1#P#p0#TMP#.MYI

[midenok]

Tmp file created

#0  __libc_open64 (file=0x7fffe0d6c6a0 "./test/t1#P#p0#TMP#.MYD", oflag=524866) at ../sysdeps/unix/sysv/linux/open64.c:37
#1  0x0000000002fc6d1d in my_create (FileName=0x7fffe0d6c6a0 "./test/t1#P#p0#TMP#.MYD", CreateFlags=0, access_flags=514, MyFlags=272) at /home/midenok/src/mariadb/trunk/src/mysys/my_create.c:46
#2  0x0000000002ff5e3b in my_create_with_symlink (linkname=0x0, filename=0x7fffe0d6c6a0 "./test/t1#P#p0#TMP#.MYD", createflags=0, access_flags=514, MyFlags=272) at /home/midenok/src/mariadb/trunk/src/mysys/my_symlink2.c:72
#3  0x0000000002c58f19 in inline_mysql_file_create_with_symlink (key=35, src_file=0x3935e40 <.str.1> "/home/midenok/src/mariadb/trunk/src/storage/myisam/mi_create.c", src_line=696, linkname=0x0, filename=0x7fffe0d6c6a0 "./test/t1#P#p0#TMP#.MYD", create_flags=0, access_flags=514, flags=272) at /home/midenok/src/mariadb/trunk/src/include/mysql/psi/mysql_file.h:1386
#4  0x0000000002c568a3 in mi_create (name=0x7fffe0d6f010 "./test/t1#P#p0#TMP#", keys=0, keydefs=0x614000030bc8, columns=4, recinfo=0x614000030a48, uniques=0, uniquedefs=0x0, ci=0x7fffe0d6f290, flags=0) at /home/midenok/src/mariadb/trunk/src/storage/myisam/mi_create.c:693
#5  0x0000000002c06b95 in ha_myisam::create (this=0x62b0000a2cd0, name=0x7fffe0d6fae0 "./test/t1#P#p0#TMP#", table_arg=0x620000038088, ha_create_info=0x7fffe0d755a0) at /home/midenok/src/mariadb/trunk/src/storage/myisam/ha_myisam.cc:2236
#6  0x0000000001628a62 in handler::ha_create (this=0x62b0000a2cd0, name=0x7fffe0d6fae0 "./test/t1#P#p0#TMP#", form=0x620000038088, info_arg=0x7fffe0d755a0) at /home/midenok/src/mariadb/trunk/src/sql/handler.cc:4736
#7  0x0000000002ec127b in ha_partition::prepare_new_partition (this=0x61d0001c8ea8, tbl=0x620000038088, create_info=0x7fffe0d755a0, file=0x62b0000a2cd0, part_name=0x7fffe0d6fae0 "./test/t1#P#p0#TMP#", p_elem=0x61d0001ca670, disable_non_uniq_indexes=0) at /home/midenok/src/mariadb/trunk/src/sql/ha_partition.cc:1593
#8  0x0000000002ec4a7e in ha_partition::change_partitions (this=0x61d0001c8ea8, create_info=0x7fffe0d755a0, path=0x7fffe0d707a0 "./test/t1", copied=0x7fffe0d70c68, deleted=0x7fffe0d70c70, pack_frm_data=0x0, pack_frm_len=0) at /home/midenok/src/mariadb/trunk/src/sql/ha_partition.cc:1990
#9  0x0000000001629955 in handler::ha_change_partitions (this=0x61d0001c8ea8, create_info=0x7fffe0d755a0, path=0x7fffe0d707a0 "./test/t1", copied=0x7fffe0d70c68, deleted=0x7fffe0d70c70, pack_frm_data=0x0, pack_frm_len=0) at /home/midenok/src/mariadb/trunk/src/sql/handler.cc:4792
#10 0x0000000001c2c18a in mysql_change_partitions (lpt=0x7fffe0d70c60) at /home/midenok/src/mariadb/trunk/src/sql/sql_partition.cc:6055
#11 0x0000000001c2a365 in fast_alter_partition_table (thd=0x62b00009a208, table=0x620000038088, alter_info=0x7fffe0d757d0, create_info=0x7fffe0d755a0, table_list=0x62b0000a1370, db=0x7fffe0d713e0, table_name=0x7fffe0d713f0) at /home/midenok/src/mariadb/trunk/src/sql/sql_partition.cc:7325
#12 0x0000000000f228ff in mysql_alter_table (thd=0x62b00009a208, new_db=0x62b00009e9c0, new_name=0x62b00009edc8, create_info=0x7fffe0d755a0, table_list=0x62b0000a1370, alter_info=0x7fffe0d757d0, order_num=0, order=0x0, ignore=false) at /home/midenok/src/mariadb/trunk/src/sql/sql_table.cc:9785
#13 0x000000000114dd7c in Sql_cmd_alter_table::execute (this=0x62b0000a1e38, thd=0x62b00009a208) at /home/midenok/src/mariadb/trunk/src/sql/sql_alter.cc:502
#14 0x0000000000c0f56f in mysql_execute_command (thd=0x62b00009a208) at /home/midenok/src/mariadb/trunk/src/sql/sql_parse.cc:6082
#15 0x0000000000bea225 in mysql_parse (thd=0x62b00009a208, rawbuf=0x62b0000a1228 "alter table t1 add partition (partition p9 history)", length=51, parser_state=0x7fffe0d7ba80, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/trunk/src/sql/sql_parse.cc:7892
#16 0x0000000000be2f26 in dispatch_command (command=COM_QUERY, thd=0x62b00009a208, packet=0x62900011d209 "alter table t1 add partition (partition p9 history)", packet_length=51, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/trunk/src/sql/sql_parse.cc:1826
#17 0x0000000000bebdd3 in do_command (thd=0x62b00009a208) at /home/midenok/src/mariadb/trunk/src/sql/sql_parse.cc:1359
#18 0x000000000112cf91 in do_handle_one_connection (connect=0x6080000017a8) at /home/midenok/src/mariadb/trunk/src/sql/sql_connect.cc:1404
#19 0x000000000112c672 in handle_one_connection (arg=0x6080000017a8) at /home/midenok/src/mariadb/trunk/src/sql/sql_connect.cc:1306
#20 0x0000000002e62975 in pfs_spawn_thread (arg=0x615000004188) at /home/midenok/src/mariadb/trunk/src/storage/perfschema/pfs.cc:1862
#21 0x00007ffff7f82182 in start_thread (arg=<optimized out>) at pthread_create.c:486
#22 0x00007ffff7426b1f in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

frame 8

(gdb) p part_elem->part_state
$4 = PART_CHANGED

Cause

Partition name is p0 though p9 was requested. PART_CHANGED for p0 is not legal here.

Fix

Remove this code from prep_alter_part_table():

          if (*fast_alter_table && tab_part_info->vers_info->interval.is_set())
          {
            partition_element *hist_part= tab_part_info->vers_info->hist_part;
            if (hist_part->range_value <= thd->query_start())
              hist_part->part_state= PART_CHANGED;
          }

It is not legal now to rebuild any partitions on fast ADD.

[midenok]

Bug 5: versioning.partition is unstable on kvm-bintar-trusty-x86

versioning.partition 'innodb,traditional' w4 [ fail ]
        Test ended at 2019-08-08 15:13:24

CURRENT_TEST: versioning.partition
--- /usr/local/mariadb-10.4.7-linux-i686/mysql-test/suite/versioning/r/partition.result 2019-08-08 13:59:56.000000000 +0000
+++ /usr/local/mariadb-10.4.7-linux-i686/mysql-test/suite/versioning/r/partition.reject 2019-08-08 15:13:24.121897201 +0000
@@ -726,8 +726,6 @@
 delete from t1;
 ### warn about full partition
 delete from t1;
-Warnings:
-Warning    4114    Versioned table `test`.`t1`: last HISTORY partition (`p1`) is out of LIMIT, need more HISTORY partitions
 select * from t1 partition (p0sp0);
 x
 1

on kvm-deb-xenial-x86

versioning.partition 'innodb,trx_id'     w4 [ fail ]  Found warnings/errors in server log file!
        Test ended at 2019-08-08 16:42:50
line
2019-08-08 16:42:49 27 [ERROR] mysqld: Table 'test.t1' doesn't exist
2019-08-08 16:42:49 27 [ERROR] mysqld: Table 'test.t1' doesn't exist

[midenok]

MDEV-19191 FK support for CURRENT partition of versioned tables

Reproduce

--source include/have_innodb.inc
--source include/have_partition.inc
set default_storage_engine= innodb;

create or replace table parent (id int primary key)
with system versioning
partition by system_time interval 1 day;

create or replace table child (
  parent_id int,
  foreign key(parent_id) references parent(id));

create or replace database test;

Good

--source include/have_innodb.inc
--source include/have_partition.inc
set default_storage_engine= innodb;

create or replace table parent (id int primary key)
with system versioning;

create or replace table child (
  parent_id int,
  foreign key(parent_id) references parent(id));

create or replace database test;

Good 2

--source include/have_innodb.inc
--source include/have_partition.inc
set default_storage_engine= innodb;

create or replace table t1 (id int primary key)
with system versioning
partition by system_time interval 1 day;

select * from t1;

create or replace database test;

error returned

#0  dict_create_foreign_constraints_low (trx=0x7f472fc01248, heap=0x7f46c8035c40, cs=0x1d94520 <my_charset_latin1>, sql_string=0x7f46c80071f0 "create or replace table child (\nparent_id int,\nforeign key(parent_id) references parent(id))", name=0x7f473404c650 "test/child", reject_fks=0) at /home/midenok/src/mariadb/trunk/src/storage/innobase/dict/dict0dict.cc:4420
#1  0x00000000011a3428 in dict_create_foreign_constraints (trx=0x7f472fc01248, sql_string=0x7f46c8014060 "create or replace table child (\nparent_id int,\nforeign key(parent_id) references parent(id))", sql_length=92, name=0x7f473404c650 "test/child", reject_fks=0) at /home/midenok/src/mariadb/trunk/src/storage/innobase/dict/dict0dict.cc:4795
#2  0x0000000000e1d0aa in create_table_info_t::create_table (this=0x7f473404c3c0, create_fk=true) at /home/midenok/src/mariadb/trunk/src/storage/innobase/handler/ha_innodb.cc:12470
#3  0x0000000000e3afa4 in ha_innobase::create (this=0x7f46c80339c0, name=0x7f473404eea0 "./test/child", form=0x7f473404d158, create_info=0x7f473404f450, file_per_table=true, trx=0x7f472fc01248) at /home/midenok/src/mariadb/trunk/src/storage/innobase/handler/ha_innodb.cc:12684
#4  0x0000000000e1dc1f in ha_innobase::create (this=0x7f46c80339c0, name=0x7f473404eea0 "./test/child", form=0x7f473404d158, create_info=0x7f473404f450) at /home/midenok/src/mariadb/trunk/src/storage/innobase/handler/ha_innodb.cc:12737
#5  0x0000000000b901c7 in handler::ha_create (this=0x7f46c80339c0, name=0x7f473404eea0 "./test/child", form=0x7f473404d158, info_arg=0x7f473404f450) at /home/midenok/src/mariadb/trunk/src/sql/handler.cc:4736
#6  0x0000000000b91b67 in ha_create_table (thd=0x7f46c8000cf8, path=0x7f473404eea0 "./test/child", db=0x7f46c80148a0 "test", table_name=0x7f46c80141a8 "child", create_info=0x7f473404f450, frm=0x7f473404ee90) at /home/midenok/src/mariadb/trunk/src/sql/handler.cc:5200
#7  0x000000000090c456 in create_table_impl (thd=0x7f46c8000cf8, orig_db=..., orig_table_name=..., db=..., table_name=..., path=0x7f473404eea0 "./test/child", options=..., create_info=0x7f473404f450, alter_info=0x7f473404f398, create_table_mode=0, is_trans=0x7f473404f1d7, key_info=0x7f473404f0b0, key_count=0x7f473404f0ac, frm=0x7f473404ee90) at /home/midenok/src/mariadb/trunk/src/sql/sql_table.cc:5062
#8  0x000000000090b6e8 in mysql_create_table_no_lock (thd=0x7f46c8000cf8, db=0x7f46c80141f8, table_name=0x7f46c8014208, create_info=0x7f473404f450, alter_info=0x7f473404f398, is_trans=0x7f473404f1d7, create_table_mode=0, table_list=0x7f46c80141e0) at /home/midenok/src/mariadb/trunk/src/sql/sql_table.cc:5146
#9  0x000000000090c9da in mysql_create_table (thd=0x7f46c8000cf8, create_table=0x7f46c80141e0, create_info=0x7f473404f450, alter_info=0x7f473404f398) at /home/midenok/src/mariadb/trunk/src/sql/sql_table.cc:5238
#10 0x00000000009213c4 in Sql_cmd_create_table_like::execute (this=0x7f46c8014180, thd=0x7f46c8000cf8) at /home/midenok/src/mariadb/trunk/src/sql/sql_table.cc:11383
#11 0x0000000000817375 in mysql_execute_command (thd=0x7f46c8000cf8) at /home/midenok/src/mariadb/trunk/src/sql/sql_parse.cc:6098
#12 0x00000000008086f0 in mysql_parse (thd=0x7f46c8000cf8, rawbuf=0x7f46c8014060 "create or replace table child (\nparent_id int,\nforeign key(parent_id) references parent(id))", length=92, parser_state=0x7f47340525e0, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/trunk/src/sql/sql_parse.cc:7908

4398            if (!success || (!referenced_table && trx->check_foreigns)) {
4399                    char    buf[MAX_TABLE_NAME_LEN + 1] = "";
4400                    char*   bufend;
4401
4402                    bufend = innobase_convert_name(buf, MAX_TABLE_NAME_LEN,
4403                                    referenced_table_name, strlen(referenced_table_name),
4404                                    trx->mysql_thd);
4405                    buf[bufend - buf] = '\0';
4406
4407                    ib_push_warning(trx, DB_CANNOT_ADD_CONSTRAINT,
4408                            "%s table %s with foreign key constraint failed. Referenced table %s not found in the data dictionary "
4409                            "near '%s'.",
4410                            operation, create_name, buf, start_of_latest_foreign);
4411                    mutex_enter(&dict_foreign_err_mutex);
4412                    dict_foreign_error_report_low(ef, create_name);
4413                    fprintf(ef,
4414                            "%s table %s with foreign key constraint failed. Referenced table %s not found in the data dictionary "
4415                            "near '%s'.\n",
4416                            operation, create_name, buf, start_of_latest_foreign);
4417
4418                    mutex_exit(&dict_foreign_err_mutex);
4419
4420                    return(DB_CANNOT_ADD_CONSTRAINT);
4421            }

(rr) p success
$20 = 1
(rr) p referenced_table
$18 = (dict_table_t *) 0x0
(rr) p trx->check_foreigns
$19 = true

dict_table_t object must be returned

#0  dict_get_referenced_table (name=0x7f473404c650 "test/child", database_name=0x0, database_name_len=0, table_name=0x7f46c8035d38 "parent", table_name_len=6, table=0x7f473404c108, heap=0x7f46c8035c40) at /home/midenok/src/mariadb/trunk/src/storage/innobase/dict/dict0dict.cc:3447
#1  0x00000000011af76c in dict_scan_table_name (cs=0x1d94520 <my_charset_latin1>, ptr=0x7f46c8007247 "(id))", table=0x7f473404c108, name=0x7f473404c650 "test/child", success=0x7f473404c090, heap=0x7f46c8035c40, ref_name=0x7f4734049168) at /home/midenok/src/mariadb/trunk/src/storage/innobase/dict/dict0dict.cc:3554
#2  0x00000000011a561a in dict_create_foreign_constraints_low (trx=0x7f472fc01248, heap=0x7f46c8035c40, cs=0x1d94520 <my_charset_latin1>, sql_string=0x7f46c80071f0 "create or replace table child (\nparent_id int,\nforeign key(parent_id) references parent(id))", name=0x7f473404c650 "test/child", reject_fks=0) at /home/midenok/src/mariadb/trunk/src/storage/innobase/dict/dict0dict.cc:4392
#3  0x00000000011a3428 in dict_create_foreign_constraints (trx=0x7f472fc01248, sql_string=0x7f46c8014060 "create or replace table child (\nparent_id int,\nforeign key(parent_id) references parent(id))", sql_length=92, name=0x7f473404c650 "test/child", reject_fks=0) at /home/midenok/src/mariadb/trunk/src/storage/innobase/dict/dict0dict.cc:4795
#4  0x0000000000e1d0aa in create_table_info_t::create_table (this=0x7f473404c3c0, create_fk=true) at /home/midenok/src/mariadb/trunk/src/storage/innobase/handler/ha_innodb.cc:12470

Breakpoints

break my_error
  commands
    btc
  end
disable $bpnum
break /home/midenok/src/mariadb/trunk/src/sql/handler.cc:5200
  commands
    btc
  end
disable $bpnum
break /home/midenok/src/mariadb/trunk/src/storage/innobase/handler/ha_innodb.cc:12469
  commands
    btc
  end
break /home/midenok/src/mariadb/trunk/src/storage/innobase/dict/dict0dict.cc:4398
  commands
    btc
  end
break dict_load_table_one
  commands
    btc
  end
break dict_get_referenced_table
  commands
    btc
  end

[midenok]

Table not found

#0  dict_load_table_one (name=..., cached=true, ignore_err=DICT_ERR_IGNORE_NONE, fk_tables=std::deque with 0 elements) at /home/midenok/src/mariadb/trunk/src/storage/innobase/dict/dict0load.cc:2934
#1  0x00000000011ba68f in dict_load_table (name=0x7f46c8035d58 "test/parent", cached=true, ignore_err=DICT_ERR_IGNORE_NONE) at /home/midenok/src/mariadb/trunk/src/storage/innobase/dict/dict0load.cc:2755
#2  0x00000000011a2e7c in dict_table_get_low (table_name=0x7f46c8035d58 "test/parent") at /home/midenok/src/mariadb/trunk/src/storage/innobase/include/dict0priv.ic:57
#3  0x00000000011a2c29 in dict_get_referenced_table (name=0x7f473404c650 "test/child", database_name=0x0, database_name_len=4, table_name=0x7f46c8035d38 "parent", table_name_len=6, table=0x7f473404c108, heap=0x7f46c8035c40) at /home/midenok/src/mariadb/trunk/src/storage/innobase/dict/dict0dict.cc:3482
#4  0x00000000011af76c in dict_scan_table_name (cs=0x1d94520 <my_charset_latin1>, ptr=0x7f46c8007247 "(id))", table=0x7f473404c108, name=0x7f473404c650 "test/child", success=0x7f473404c090, heap=0x7f46c8035c40, ref_name=0x7f4734049168) at /home/midenok/src/mariadb/trunk/src/storage/innobase/dict/dict0dict.cc:3554
#5  0x00000000011a561a in dict_create_foreign_constraints_low (trx=0x7f472fc01248, heap=0x7f46c8035c40, cs=0x1d94520 <my_charset_latin1>, sql_string=0x7f46c80071f0 "create or replace table child (\nparent_id int,\nforeign key(parent_id) references parent(id))", name=0x7f473404c650 "test/child", reject_fks=0) at /home/midenok/src/mariadb/trunk/src/storage/innobase/dict/dict0dict.cc:4392
#6  0x00000000011a3428 in dict_create_foreign_constraints (trx=0x7f472fc01248, sql_string=0x7f46c8014060 "create or replace table child (\nparent_id int,\nforeign key(parent_id) references parent(id))", sql_length=92, name=0x7f473404c650 "test/child", reject_fks=0) at /home/midenok/src/mariadb/trunk/src/storage/innobase/dict/dict0dict.cc:4795
#7  0x0000000000e1d0aa in create_table_info_t::create_table (this=0x7f473404c3c0, create_fk=true) at /home/midenok/src/mariadb/trunk/src/storage/innobase/handler/ha_innodb.cc:12470
#8  0x0000000000e3afa4 in ha_innobase::create (this=0x7f46c80339c0, name=0x7f473404eea0 "./test/child", form=0x7f473404d158, create_info=0x7f473404f450, file_per_table=true, trx=0x7f472fc01248) at /home/midenok/src/mariadb/trunk/src/storage/innobase/handler/ha_innodb.cc:12684
#9  0x0000000000e1dc1f in ha_innobase::create (this=0x7f46c80339c0, name=0x7f473404eea0 "./test/child", form=0x7f473404d158, create_info=0x7f473404f450) at /home/midenok/src/mariadb/trunk/src/storage/innobase/handler/ha_innodb.cc:12737
#10 0x0000000000b901c7 in handler::ha_create (this=0x7f46c80339c0, name=0x7f473404eea0 "./test/child", form=0x7f473404d158, info_arg=0x7f473404f450) at /home/midenok/src/mariadb/trunk/src/sql/handler.cc:4736
#11 0x0000000000b91b67 in ha_create_table (thd=0x7f46c8000cf8, path=0x7f473404eea0 "./test/child", db=0x7f46c80148a0 "test", table_name=0x7f46c80141a8 "child", create_info=0x7f473404f450, frm=0x7f473404ee90) at /home/midenok/src/mariadb/trunk/src/sql/handler.cc:5200
#12 0x000000000090c456 in create_table_impl (thd=0x7f46c8000cf8, orig_db=..., orig_table_name=..., db=..., table_name=..., path=0x7f473404eea0 "./test/child", options=..., create_info=0x7f473404f450, alter_info=0x7f473404f398, create_table_mode=0, is_trans=0x7f473404f1d7, key_info=0x7f473404f0b0, key_count=0x7f473404f0ac, frm=0x7f473404ee90) at /home/midenok/src/mariadb/trunk/src/sql/sql_table.cc:5062
#13 0x000000000090b6e8 in mysql_create_table_no_lock (thd=0x7f46c8000cf8, db=0x7f46c80141f8, table_name=0x7f46c8014208, create_info=0x7f473404f450, alter_info=0x7f473404f398, is_trans=0x7f473404f1d7, create_table_mode=0, table_list=0x7f46c80141e0) at /home/midenok/src/mariadb/trunk/src/sql/sql_table.cc:5146
#14 0x000000000090c9da in mysql_create_table (thd=0x7f46c8000cf8, create_table=0x7f46c80141e0, create_info=0x7f473404f450, alter_info=0x7f473404f398) at /home/midenok/src/mariadb/trunk/src/sql/sql_table.cc:5238
#15 0x00000000009213c4 in Sql_cmd_create_table_like::execute (this=0x7f46c8014180, thd=0x7f46c8000cf8) at /home/midenok/src/mariadb/trunk/src/sql/sql_table.cc:11383
#16 0x0000000000817375 in mysql_execute_command (thd=0x7f46c8000cf8) at /home/midenok/src/mariadb/trunk/src/sql/sql_parse.cc:6098
#17 0x00000000008086f0 in mysql_parse (thd=0x7f46c8000cf8, rawbuf=0x7f46c8014060 "create or replace table child (\nparent_id int,\nforeign key(parent_id) references parent(id))", length=92, parser_state=0x7f47340525e0, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/trunk/src/sql/sql_parse.cc:7908

2930            /* Check if the table name in record is the searched one */
2931            if (len != ut_strlen(name.m_name)
2932                || 0 != ut_memcmp(name.m_name, field, len)) {
2933
2934                    goto err_exit;
2935            }

(rr) p name
$36 = (const table_name_t &) @0x7f47340484c0: {
  m_name = 0x7f46c8035d58 "test/parent", 
  static part_suffix = "#P#"
}

Expected

m_name value "test/partent#P#p0"

Cause

Table name is supplied from FK reference and the table is not found, because we have partitions, so real tables are suffixed by #P#... strings. Since there is no partition support at IB level, there is no standard way of obtaining partition name without referring to higher layer.

[midenok]

FK added to dictionary

#0  dict_create_add_foreign_to_dictionary (name=0x7fff8401be50 "test/child", foreign=0x7fff84082498, trx=0x7ffff488c360) at /home/midenok/src/mariadb/trunk/src/storage/innobase/dict/dict0crea.cc:1940
#1  0x00000000011c0ab9 in dict_create_add_foreigns_to_dictionary (local_fk_set=std::set with 1 element = {...}, table=0x7fff8407b578, trx=0x7ffff488c360) at /home/midenok/src/mariadb/trunk/src/storage/innobase/dict/dict0crea.cc:2120
#2  0x00000000011d4077 in dict_create_foreign_constraints_low (trx=0x7ffff488c360, heap=0x7fff84084570, cs=0x1ed0d40 <my_charset_utf8_general_ci>, sql_string=0x7fff84061280 "create or replace table child (\n  parent_id int,\n  foreign key(parent_id) references parent(id))", name=0x7ffff47cb6b0 "test/child", reject_fks=0) at /home/midenok/src/mariadb/trunk/src/storage/innobase/dict/dict0dict.cc:4123
#3  0x00000000011d2e08 in dict_create_foreign_constraints (trx=0x7ffff488c360, sql_string=0x7fff84014020 "create or replace table child (\n  parent_id int,\n  foreign key(parent_id) references parent(id))", sql_length=96, name=0x7ffff47cb6b0 "test/child", reject_fks=0) at /home/midenok/src/mariadb/trunk/src/storage/innobase/dict/dict0dict.cc:4795
#4  0x0000000000e4caba in create_table_info_t::create_table (this=0x7ffff47cb420, create_fk=true) at /home/midenok/src/mariadb/trunk/src/storage/innobase/handler/ha_innodb.cc:12470
#5  0x0000000000e6a994 in ha_innobase::create (this=0x7fff84077b80, name=0x7ffff47cdf00 "./test/child", form=0x7ffff47cc1b8, create_info=0x7ffff47ce4b0, file_per_table=true, trx=0x7ffff488c360) at /home/midenok/src/mariadb/trunk/src/storage/innobase/handler/ha_innodb.cc:12684
#6  0x0000000000e4d62f in ha_innobase::create (this=0x7fff84077b80, name=0x7ffff47cdf00 "./test/child", form=0x7ffff47cc1b8, create_info=0x7ffff47ce4b0) at /home/midenok/src/mariadb/trunk/src/storage/innobase/handler/ha_innodb.cc:12737

Multi-column FK

create or replace table t1 (id int(11) not null, id2 int(11) not null, unique (id, id2));
create or replace table t2 (id int(11) not null, id2 int(11) not null, unique (id, id2), constraint t1_id_fk foreign key (id2,id) references t1 (id,id2));

Get referenced table

#0  dict_get_referenced_table (name=0x7ffff47cb6a0 "test/t2", database_name=0x0, database_name_len=0, table_name=0x7fff84076128 "t1", table_name_len=2, table=0x7ffff47cb0e8, heap=0x7fff84076020) at /home/midenok/src/mariadb/trunk/src/storage/innobase/dict/dict0dict.cc:3447
#1  0x00000000011e04fc in dict_scan_table_name (cs=0x1ed1d40 <my_charset_utf8_general_ci>, ptr=0x7fff84074c2f " (id,id2))", table=0x7ffff47cb0e8, name=0x7ffff47cb6a0 "test/t2", success=0x7ffff47cb070, heap=0x7fff84076020, ref_name=0x7ffff47c8148) at /home/midenok/src/mariadb/trunk/src/storage/innobase/dict/dict0dict.cc:3554
#2  0x00000000011d65ba in dict_create_foreign_constraints_low (trx=0x7ffff488c360, heap=0x7fff84076020, cs=0x1ed1d40 <my_charset_utf8_general_ci>, sql_string=0x7fff84074ba0 "create or replace table t2 (id int(11) not null, id2 int(11) not null, unique (id, id2), constraint t1_id_fk foreign key (id2,id) references t1 (id,id2))", name=0x7ffff47cb6a0 "test/t2", reject_fks=0, local_fk_set=std::set with 1 element = {...}) at /home/midenok/src/mariadb/trunk/src/storage/innobase/dict/dict0dict.cc:4391
#3  0x00000000011d476f in dict_create_foreign_constraints (trx=0x7ffff488c360, sql_string=0x7fff84014020 "create or replace table t2 (id int(11) not null, id2 int(11) not null, unique (id, id2), constraint t1_id_fk foreign key (id2,id) references t1 (id,id2))", sql_length=153, name=0x7ffff47cb6a0 "test/t2", reject_fks=0, local_fk_set=std::set with 1 element = {...}) at /home/midenok/src/mariadb/trunk/src/storage/innobase/dict/dict0dict.cc:4795
#4  0x0000000000e4d61a in create_table_info_t::create_table (this=0x7ffff47cb410, create_fk=true) at /home/midenok/src/mariadb/trunk/src/storage/innobase/handler/ha_innodb.cc:12527
#5  0x0000000000e6c054 in ha_innobase::create (this=0x7fff84051c30, name=0x7ffff47cdef0 "./test/t2", form=0x7ffff47cc1a8, create_info=0x7ffff47ce498, file_per_table=true, trx=0x7ffff488c360) at /home/midenok/src/mariadb/trunk/src/storage/innobase/handler/ha_innodb.cc:12744
#6  0x0000000000e4e22f in ha_innobase::create (this=0x7fff84051c30, name=0x7ffff47cdef0 "./test/t2", form=0x7ffff47cc1a8, create_info=0x7ffff47ce498) at /home/midenok/src/mariadb/trunk/src/storage/innobase/handler/ha_innodb.cc:12797
#7  0x0000000000bbfbf7 in handler::ha_create (this=0x7fff84051c30, name=0x7ffff47cdef0 "./test/t2", form=0x7ffff47cc1a8, info_arg=0x7ffff47ce498) at /home/midenok/src/mariadb/trunk/src/sql/handler.cc:4736
#8  0x0000000000bc1597 in ha_create_table (thd=0x7fff84000cf8, path=0x7ffff47cdef0 "./test/t2", db=0x7fff840148d8 "test", table_name=0x7fff840141e0 "t2", create_info=0x7ffff47ce498, frm=0x7ffff47cdee0) at /home/midenok/src/mariadb/trunk/src/sql/handler.cc:5200
#9  0x000000000093c0f6 in create_table_impl (thd=0x7fff84000cf8, orig_db=..., orig_table_name=..., db=..., table_name=..., path=0x7ffff47cdef0 "./test/t2", options=..., create_info=0x7ffff47ce498, alter_info=0x7ffff47ce3e0, create_table_mode=0, is_trans=0x7ffff47ce227, key_info=0x7ffff47ce100, key_count=0x7ffff47ce0fc, frm=0x7ffff47cdee0) at /home/midenok/src/mariadb/trunk/src/sql/sql_table.cc:5062
#10 0x000000000093b388 in mysql_create_table_no_lock (thd=0x7fff84000cf8, db=0x7fff84014230, table_name=0x7fff84014240, create_info=0x7ffff47ce498, alter_info=0x7ffff47ce3e0, is_trans=0x7ffff47ce227, create_table_mode=0, table_list=0x7fff84014218) at /home/midenok/src/mariadb/trunk/src/sql/sql_table.cc:5146
#11 0x000000000093c67a in mysql_create_table (thd=0x7fff84000cf8, create_table=0x7fff84014218, create_info=0x7ffff47ce498, alter_info=0x7ffff47ce3e0) at /home/midenok/src/mariadb/trunk/src/sql/sql_table.cc:5238
#12 0x0000000000951072 in Sql_cmd_create_table_like::execute (this=0x7fff840141b8, thd=0x7fff84000cf8) at /home/midenok/src/mariadb/trunk/src/sql/sql_table.cc:11389
#13 0x0000000000847015 in mysql_execute_command (thd=0x7fff84000cf8) at /home/midenok/src/mariadb/trunk/src/sql/sql_parse.cc:6098
#14 0x0000000000838390 in mysql_parse (thd=0x7fff84000cf8, rawbuf=0x7fff84014020 "create or replace table t2 (id int(11) not null, id2 int(11) not null, unique (id, id2), constraint t1_id_fk foreign key (id2,id) references t1 (id,id2))", length=153, parser_state=0x7ffff47d1640, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/trunk/src/sql/sql_parse.cc:7908

Example of Foreign_key

(gdb) p *fk
$3 = {
  <Key> = {
    <Sql_alloc> = {<No data fields>},
    <DDL_options> = {
      <DDL_options_st> = {
        m_options = DDL_options_st::OPT_NONE
      }, <No data fields>},
    members of Key:
    _vptr$Key = 0x1bb98c0 <vtable for Foreign_key+16>,
    type = Key::FOREIGN_KEY,
    key_create_info = {
      algorithm = HA_KEY_ALG_UNDEF,
      block_size = 0,
      flags = 0,
      parser_name = {
        str = 0x0,
        length = 0
      },
      comment = {
        str = 0x0,
        length = 0
      },
      check_for_duplicate_indexes = false
    },
    columns = {
      <base_list> = {
        <Sql_alloc> = {<No data fields>},
        members of base_list:
        first = 0x7fff7c015098,
        last = 0x7fff7c0150a8,
        elements = 2
      }, <No data fields>},
    name = {
      str = 0x7fff7c014c28 "t1_id_fk",
      length = 8
    },
    option_list = 0x0,
    generated = false,
    invisible = false
  },
  members of Foreign_key:
  ref_db = {
    str = 0x0,
    length = 0
  },
  ref_table = {
    str = 0x7fff7c014d20 "t1",
    length = 2
  },
  ref_columns = {
    <base_list> = {
      <Sql_alloc> = {<No data fields>},
      members of base_list:
      first = 0x7fff7c0150e8,
      last = 0x7fff7c0150f8,
      elements = 2
    }, <No data fields>},
  delete_opt = FK_OPTION_UNDEF,
  update_opt = FK_OPTION_UNDEF,
  match_opt = Foreign_key::FK_MATCH_UNDEF

[midenok]

Bug 6: add foreign key to correct data fails

Reproduce

--source include/have_innodb.inc
set default_storage_engine=innodb;

create or replace table t2 (a int);
create or replace table t1 (a int);
insert into t2 values (1);
insert into t1 values (1);
alter table t1 add foreign key (a) references t2(a);

drop tables t1, t2;

Result

#5  0x0000000000bbd3a2 in handler::print_error (this=0x7f2ad0081eb0, error=151, errflag=0) at /home/midenok/src/mariadb/trunk/src/sql/handler.cc:3900
#6  0x000000000094ebc6 in copy_data_between_tables (thd=0x7f2ad0000cf8, from=0x7f2ad0076228, to=0x7f2ad00810b8, create=..., ignore=false, order_num=0, order=0x0, copied=0x7f2b2c0f4d18, deleted=0x7f2b2c0f4d10, keys_onoff=Alter_info::LEAVE_AS_IS, alter_ctx=0x7f2b2c0f5118) at /home/midenok/src/mariadb/trunk/src/sql/sql_table.cc:10741
#7  0x0000000000945a7d in mysql_alter_table (thd=0x7f2ad0000cf8, new_db=0x7f2ad00054b8, new_name=0x7f2ad00058c0, create_info=0x7f2b2c0f64a0, table_list=0x7f2ad0014178, alter_info=0x7f2b2c0f63e8, order_num=0, order=0x0, ignore=false) at /home/midenok/src/mariadb/trunk/src/sql/sql_table.cc:10152
#8  0x00000000009f6546 in Sql_cmd_alter_table::execute (this=0x7f2ad0014a48, thd=0x7f2ad0000cf8) at /home/midenok/src/mariadb/trunk/src/sql/sql_alter.cc:503
#9  0x0000000000847015 in mysql_execute_command (thd=0x7f2ad0000cf8) at /home/midenok/src/mariadb/trunk/src/sql/sql_parse.cc:6098
#10 0x0000000000838390 in mysql_parse (thd=0x7f2ad0000cf8, rawbuf=0x7f2ad0014060 "alter table t1 add foreign key (a) references t2(a)", length=51, parser_state=0x7f2b2c0f95e0, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/trunk/src/sql/sql_parse.cc:7908

DB_NO_REFERENCED_ROW returned

#0  row_ins_check_foreign_constraint (check_ref=1, foreign=0x7f2ad00805b8, table=0x7f2ad007b8d8, entry=0x7f2ad002e250, thr=0x7f2ad0084080) at /home/midenok/src/mariadb/trunk/src/storage/innobase/row/row0ins.cc:1684
#1  0x0000000000fd8e38 in row_ins_check_foreign_constraints (table=0x7f2ad007b8d8, index=0x7f2ad007ecf8, entry=0x7f2ad002e250, thr=0x7f2ad0084080) at /home/midenok/src/mariadb/trunk/src/storage/innobase/row/row0ins.cc:1998
#2  0x0000000000fd9095 in row_ins_sec_index_entry (index=0x7f2ad007ecf8, entry=0x7f2ad002e250, thr=0x7f2ad0084080, dup_chk_only=false) at /home/midenok/src/mariadb/trunk/src/storage/innobase/row/row0ins.cc:3278
#3  0x0000000000fe0ac7 in row_ins_index_entry (index=0x7f2ad007ecf8, entry=0x7f2ad002e250, thr=0x7f2ad0084080) at /home/midenok/src/mariadb/trunk/src/storage/innobase/row/row0ins.cc:3355
#4  0x0000000000fdfe3f in row_ins_index_entry_step (node=0x7f2ad0083e30, thr=0x7f2ad0084080) at /home/midenok/src/mariadb/trunk/src/storage/innobase/row/row0ins.cc:3522
#5  0x0000000000fd9b70 in row_ins (node=0x7f2ad0083e30, thr=0x7f2ad0084080) at /home/midenok/src/mariadb/trunk/src/storage/innobase/row/row0ins.cc:3658
#6  0x0000000000fd97ee in row_ins_step (thr=0x7f2ad0084080) at /home/midenok/src/mariadb/trunk/src/storage/innobase/row/row0ins.cc:3801
#7  0x0000000001002e68 in row_insert_for_mysql (mysql_rec=0x7f2ad007bd48 "\375\001", prebuilt=0x7f2ad0083908, ins_mode=ROW_INS_NORMAL) at /home/midenok/src/mariadb/trunk/src/storage/innobase/row/row0mysql.cc:1459
#8  0x0000000000e4323d in ha_innobase::write_row (this=0x7f2ad0081eb0, record=0x7f2ad007bd48 "\375\001") at /home/midenok/src/mariadb/trunk/src/storage/innobase/handler/ha_innodb.cc:8057
#9  0x0000000000bc52b7 in handler::ha_write_row (this=0x7f2ad0081eb0, buf=0x7f2ad007bd48 "\375\001") at /home/midenok/src/mariadb/trunk/src/sql/handler.cc:6682
#10 0x000000000094eb47 in copy_data_between_tables (thd=0x7f2ad0000cf8, from=0x7f2ad0076228, to=0x7f2ad00810b8, create=..., ignore=false, order_num=0, order=0x0, copied=0x7f2b2c0f4d18, deleted=0x7f2b2c0f4d10, keys_onoff=Alter_info::LEAVE_AS_IS, alter_ctx=0x7f2b2c0f5118) at /home/midenok/src/mariadb/trunk/src/sql/sql_table.cc:10734
#11 0x0000000000945a7d in mysql_alter_table (thd=0x7f2ad0000cf8, new_db=0x7f2ad00054b8, new_name=0x7f2ad00058c0, create_info=0x7f2b2c0f64a0, table_list=0x7f2ad0014178, alter_info=0x7f2b2c0f63e8, order_num=0, order=0x0, ignore=false) at /home/midenok/src/mariadb/trunk/src/sql/sql_table.cc:10152
#12 0x00000000009f6546 in Sql_cmd_alter_table::execute (this=0x7f2ad0014a48, thd=0x7f2ad0000cf8) at /home/midenok/src/mariadb/trunk/src/sql/sql_alter.cc:503
#13 0x0000000000847015 in mysql_execute_command (thd=0x7f2ad0000cf8) at /home/midenok/src/mariadb/trunk/src/sql/sql_parse.cc:6098
#14 0x0000000000838390 in mysql_parse (thd=0x7f2ad0000cf8, rawbuf=0x7f2ad0014060 "alter table t1 add foreign key (a) references t2(a)", length=51, parser_state=0x7f2b2c0f95e0, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/trunk/src/sql/sql_parse.cc:7908

1647            if (check_ref) {
1648                    check_table = foreign->referenced_table;
1649                    check_index = foreign->referenced_index;
1650            } else {
1651                    check_table = foreign->foreign_table;
1652                    check_index = foreign->foreign_index;
1653            }
1654
1655            if (check_table == NULL
1656                || !check_table->is_readable()
1657                || check_index == NULL) {
1658
1659                    if (!srv_read_only_mode && check_ref) {
1660                            FILE*   ef = dict_foreign_err_file;
1661                            std::string fk_str;
1662
1663                            row_ins_set_detailed(trx, foreign);
1664
1665                            row_ins_foreign_trx_print(trx);
1666
1667                            fputs("Foreign key constraint fails for table ", ef);
1668                            ut_print_name(ef, trx,
1669                                          foreign->foreign_table_name);
1670                            fputs(":\n", ef);
1671                            fk_str = dict_print_info_on_foreign_key_in_create_format(
1672                                    trx, foreign, TRUE);
1673                            fputs(fk_str.c_str(), ef);
1674                            fprintf(ef, "\nTrying to add to index %s tuple:\n",
1675                                    foreign->foreign_index->name());
1676                            dtuple_print(ef, entry);
1677                            fputs("\nBut the parent table ", ef);
1678                            ut_print_name(ef, trx,
1679                                          foreign->referenced_table_name);
1680                            fputs("\nor its .ibd file does"
1681                                  " not currently exist!\n", ef);
1682                            mutex_exit(&dict_foreign_err_mutex);
1683
1684                            err = DB_NO_REFERENCED_ROW;
1685                    }
1686
1687                    goto exit_func;
1688            }

(rr) p check_index
$13 = (dict_index_t *) 0x0
(rr) p check_table
$14 = (dict_table_t *) 0x7f2ad0026828
(rr) p *foreign
$15 = {
  heap = 0x7f2ad007d8e0,
  id = 0x7f2ad0080638 "test/#sql-554e_8_ibfk_1",
  n_fields = 1,
  type = 0,
  foreign_table_name = 0x7f2ad0080660 "test/#sql-554e_8",
  foreign_table_name_lookup = 0x7f2ad0080660 "test/#sql-554e_8",
  foreign_table = 0x7f2ad007b8d8,
  foreign_col_names = 0x7f2ad0080678,
  referenced_table_name = 0x7f2ad0080680 "test/t2",
  referenced_table_name_lookup = 0x7f2ad0080680 "test/t2",
  referenced_table = 0x7f2ad0026828,
  referenced_col_names = 0x7f2ad0080688,
  foreign_index = 0x7f2ad007ecf8,
  referenced_index = 0x0,
  v_cols = 0x0
}

Cause

referenced_index is NULL.

Fix

Fill foreign->referenced_index as it is done in dict_create_foreign_constraints_low().

[midenok]

Tests failed

binlog.binlog_database \
gcol.innodb_virtual_fk_restart \
innodb.foreign_key \
innodb.foreign-keys \
innodb_fts.innodb_fts_misc_1 \
innodb_gis.point_basic \
innodb.innodb \
innodb.innodb-alter \
innodb.innodb-alter-debug \
innodb.innodb_bug47621 \
innodb.innodb_bug51378 \
innodb.innodb_bug53046 \
innodb.innodb_bug53592 \
innodb.innodb_bug57904 \
innodb.innodb-fk \
innodb.innodb-fk-warnings \
innodb.innodb-system-table-view \
innodb.innodb-truncate \
innodb.instant_alter \
main.ctype_utf8mb4_innodb \
main.information_schema_inno \
main.innodb_mysql_sync \
main.insert_innodb \
main.mysqldump \
main.parser_bug21114_innodb \
main.partition_innodb \
main.query_cache_innodb \
main.subselect_sj2 \
main.subselect_sj2_jcl6 \
main.subselect_sj2_mat \
main.trigger-trans \
rpl.rpl_foreign_key_innodb \
rpl.rpl_insert_id_pk \
rpl.rpl_mdev382 \
versioning.foreign

Failed so far

rpl.rpl_mdev382 \
innodb.innodb-alter-debug \
innodb.foreign-keys \
innodb.foreign_key \
innodb.innodb-fk \
innodb.innodb-fk-warnings \
innodb_gis.point_basic \
main.information_schema_inno \
main.query_cache_innodb

[midenok]

Bug 7

Reproduce

--source include/have_innodb.inc

set default_storage_engine=innodb;

create or replace table t1 (f1 int, f2 int, f3 int, key(f1)) engine=innodb;
create or replace table t2 (f int, key(f)) engine=innodb;
alter table t1 add foreign key (f2) references t2 (f);
alter ignore table t1 add foreign key (f3) references t1 (f1);
drop table t1, t2;

Bad

1. Parse

#0  create_table_info_t::tmp_forge_fk_set (this=0x7ffff47c9400, local_fk_set0=std::set with 0 elements, name=0x7ffff47c9690 "test/#sql-71f3_9") at /home/midenok/src/mariadb/trunk/src/storage/innobase/handler/ha_innodb.cc:12703
#1  0x0000000000e4f17d in create_table_info_t::create_table (this=0x7ffff47c9400, create_fk=true) at /home/midenok/src/mariadb/trunk/src/storage/innobase/handler/ha_innodb.cc:12905
#2  0x0000000000e6dab4 in ha_innobase::create (this=0x7fff8405a6e0, name=0x7ffff47cd9d5 "./test/#sql-71f3_9", form=0x7ffff47ca198, create_info=0x7ffff47ce4f0, file_per_table=true, trx=0x7ffff488c360) at /home/midenok/src/mariadb/trunk/src/storage/innobase/handler/ha_innodb.cc:13117
#3  0x0000000000e4fd4f in ha_innobase::create (this=0x7fff8405a6e0, name=0x7ffff47cd9d5 "./test/#sql-71f3_9", form=0x7ffff47ca198, create_info=0x7ffff47ce4f0) at /home/midenok/src/mariadb/trunk/src/storage/innobase/handler/ha_innodb.cc:13170
#4  0x0000000000bbfc47 in handler::ha_create (this=0x7fff8405a6e0, name=0x7ffff47cd9d5 "./test/#sql-71f3_9", form=0x7ffff47ca198, info_arg=0x7ffff47ce4f0) at /home/midenok/src/mariadb/trunk/src/sql/handler.cc:4736
#5  0x0000000000bc15e7 in ha_create_table (thd=0x7fff84000cf8, path=0x7ffff47cd9d5 "./test/#sql-71f3_9", db=0x7fff84014808 "test", table_name=0x7fff84014110 "t1", create_info=0x7ffff47ce4f0, frm=0x7ffff47cc940) at /home/midenok/src/mariadb/trunk/src/sql/handler.cc:5200
#6  0x00000000009457ff 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:10107
#7  0x00000000009f6566 in Sql_cmd_alter_table::execute (this=0x7fff84014a18, thd=0x7fff84000cf8) at /home/midenok/src/mariadb/trunk/src/sql/sql_alter.cc:503
#8  0x000000000084702a in mysql_execute_command (thd=0x7fff84000cf8) at /home/midenok/src/mariadb/trunk/src/sql/sql_parse.cc:6099
#9  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

2. Rename

#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

3. Failing rename

#0  ha_innobase::rename_table (this=0x7fff840169a0, from=0x7ffff47cad50 "./test/#sql-71f3_9", to=0x7ffff47cab40 "./test/t1") at /home/midenok/src/mariadb/trunk/src/storage/innobase/handler/ha_innodb.cc:13831
#1  0x0000000000bbfb0f in handler::ha_rename_table (this=0x7fff840169a0, from=0x7ffff47cad50 "./test/#sql-71f3_9", to=0x7ffff47cab40 "./test/t1") at /home/midenok/src/mariadb/trunk/src/sql/handler.cc:4688
#2  0x000000000093d150 in mysql_rename_table (base=0x28b05f8, old_db=0x7ffff47cd1a8, old_name=0x7ffff47cd1d8, new_db=0x7ffff47cd1a8, new_name=0x7ffff47cd1c8, flags=1) at /home/midenok/src/mariadb/trunk/src/sql/sql_table.cc:5533
#3  0x0000000000946080 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:10273
#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

Value returned: 168

4. Wrong rename

#0  ha_innobase::rename_table (this=0x7fff840177e0, from=0x7ffff47cad50 "./test/#sql2-71f3-9", to=0x7ffff47cab40 "./test/t1") at /home/midenok/src/mariadb/trunk/src/storage/innobase/handler/ha_innodb.cc:13831
#1  0x0000000000bbfb0f in handler::ha_rename_table (this=0x7fff840177e0, from=0x7ffff47cad50 "./test/#sql2-71f3-9", to=0x7ffff47cab40 "./test/t1") at /home/midenok/src/mariadb/trunk/src/sql/handler.cc:4688
#2  0x000000000093d150 in mysql_rename_table (base=0x28b05f8, old_db=0x7ffff47cd178, old_name=0x7ffff47cb410, new_db=0x7ffff47cd178, new_name=0x7ffff47cd198, flags=65) at /home/midenok/src/mariadb/trunk/src/sql/sql_table.cc:5533
#3  0x00000000009460ff 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:10282
#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

Good

1. Parse

#0  dict_create_foreign_constraints_low (trx=0x7ffff488c360, heap=0x7fff7c0970d0, cs=0x2026d40 <my_charset_utf8_general_ci>, sql_string=0x7fff7c083270 "alter ignore table t1 add foreign key (f3) references t1 (f1)", name=0x7ffff47c9560 "test/#sql-2ad0_9", reject_fks=0) at /home/midenok/src/mariadb/10.4/src/storage/innobase/dict/dict0dict.cc:4710
#1  0x00000000012d5818 in dict_create_foreign_constraints (trx=0x7ffff488c360, sql_string=0x7fff7c013ff0 "alter ignore table t1 add foreign key (f3) references t1 (f1)", sql_length=61, name=0x7ffff47c9560 "test/#sql-2ad0_9", reject_fks=0) at /home/midenok/src/mariadb/10.4/src/storage/innobase/dict/dict0dict.cc:4751
#2  0x0000000000ea9de0 in create_table_info_t::create_table (this=0x7ffff47c92d0, create_fk=true) at /home/midenok/src/mariadb/10.4/src/storage/innobase/handler/ha_innodb.cc:12451
#3  0x0000000000ecaad6 in ha_innobase::create (this=0x7fff7c0911a0, name=0x7ffff47cd8b5 "./test/#sql-2ad0_9", form=0x7ffff47ca068, create_info=0x7ffff47ce3c8, file_per_table=true, trx=0x7ffff488c360) at /home/midenok/src/mariadb/10.4/src/storage/innobase/handler/ha_innodb.cc:12665
#4  0x0000000000eaab0f in ha_innobase::create (this=0x7fff7c0911a0, name=0x7ffff47cd8b5 "./test/#sql-2ad0_9", form=0x7ffff47ca068, create_info=0x7ffff47ce3c8) at /home/midenok/src/mariadb/10.4/src/storage/innobase/handler/ha_innodb.cc:12718
#5  0x0000000000bf99fa in handler::ha_create (this=0x7fff7c0911a0, name=0x7ffff47cd8b5 "./test/#sql-2ad0_9", form=0x7ffff47ca068, info_arg=0x7ffff47ce3c8) at /home/midenok/src/mariadb/10.4/src/sql/handler.cc:4728
#6  0x0000000000bfb6d7 in ha_create_table (thd=0x7fff7c000cf8, path=0x7ffff47cd8b5 "./test/#sql-2ad0_9", db=0x7fff7c0147c8 "test", table_name=0x7fff7c0140e0 "t1", create_info=0x7ffff47ce3c8, frm=0x7ffff47cc820) at /home/midenok/src/mariadb/10.4/src/sql/handler.cc:5192
#7  0x0000000000960c24 in mysql_alter_table (thd=0x7fff7c000cf8, new_db=0x7fff7c0054a0, new_name=0x7fff7c0058a8, create_info=0x7ffff47ce3c8, table_list=0x7fff7c014118, alter_info=0x7ffff47ce310, order_num=0, order=0x0, ignore=true) at /home/midenok/src/mariadb/10.4/src/sql/sql_table.cc:10153
#8  0x0000000000a17d1c in Sql_cmd_alter_table::execute (this=0x7fff7c0149d8, thd=0x7fff7c000cf8) at /home/midenok/src/mariadb/10.4/src/sql/sql_alter.cc:502
#9  0x0000000000858e19 in mysql_execute_command (thd=0x7fff7c000cf8) at /home/midenok/src/mariadb/10.4/src/sql/sql_parse.cc:6099
#10 0x0000000000848f40 in mysql_parse (thd=0x7fff7c000cf8, rawbuf=0x7fff7c013ff0 "alter ignore table t1 add foreign key (f3) references t1 (f1)", length=61, parser_state=0x7ffff47d1620, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/10.4/src/sql/sql_parse.cc:7909

2. Rename

#0  ha_innobase::rename_table (this=0x7fff7c016b58, from=0x7ffff47cac20 "./test/t1", to=0x7ffff47caa10 "./test/#sql2-2ad0-9") at /home/midenok/src/mariadb/10.4/src/storage/innobase/handler/ha_innodb.cc:13379
#1  0x0000000000bf9842 in handler::ha_rename_table (this=0x7fff7c016b58, from=0x7ffff47cac20 "./test/t1", to=0x7ffff47caa10 "./test/#sql2-2ad0-9") at /home/midenok/src/mariadb/10.4/src/sql/handler.cc:4680
#2  0x0000000000958286 in mysql_rename_table (base=0x2a04558, old_db=0x7ffff47cd058, old_name=0x7ffff47cd068, new_db=0x7ffff47cd058, new_name=0x7ffff47cb300, flags=2) at /home/midenok/src/mariadb/10.4/src/sql/sql_table.cc:5533
#3  0x0000000000961421 in mysql_alter_table (thd=0x7fff7c000cf8, new_db=0x7fff7c0054a0, new_name=0x7fff7c0058a8, create_info=0x7ffff47ce3c8, table_list=0x7fff7c014118, alter_info=0x7ffff47ce310, order_num=0, order=0x0, ignore=true) at /home/midenok/src/mariadb/10.4/src/sql/sql_table.cc:10309
#4  0x0000000000a17d1c in Sql_cmd_alter_table::execute (this=0x7fff7c0149d8, thd=0x7fff7c000cf8) at /home/midenok/src/mariadb/10.4/src/sql/sql_alter.cc:502
#5  0x0000000000858e19 in mysql_execute_command (thd=0x7fff7c000cf8) at /home/midenok/src/mariadb/10.4/src/sql/sql_parse.cc:6099
#6  0x0000000000848f40 in mysql_parse (thd=0x7fff7c000cf8, rawbuf=0x7fff7c013ff0 "alter ignore table t1 add foreign key (f3) references t1 (f1)", length=61, parser_state=0x7ffff47d1620, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/10.4/src/sql/sql_parse.cc:7909

3. Rename

#0  ha_innobase::rename_table (this=0x7fff7c017278, from=0x7ffff47cac20 "./test/#sql-2ad0_9", to=0x7ffff47caa10 "./test/t1") at /home/midenok/src/mariadb/10.4/src/storage/innobase/handler/ha_innodb.cc:13379
#1  0x0000000000bf9842 in handler::ha_rename_table (this=0x7fff7c017278, from=0x7ffff47cac20 "./test/#sql-2ad0_9", to=0x7ffff47caa10 "./test/t1") at /home/midenok/src/mariadb/10.4/src/sql/handler.cc:4680
#2  0x0000000000958286 in mysql_rename_table (base=0x2a04558, old_db=0x7ffff47cd088, old_name=0x7ffff47cd0b8, new_db=0x7ffff47cd088, new_name=0x7ffff47cd0a8, flags=1) at /home/midenok/src/mariadb/10.4/src/sql/sql_table.cc:5533
#3  0x00000000009614a5 in mysql_alter_table (thd=0x7fff7c000cf8, new_db=0x7fff7c0054a0, new_name=0x7fff7c0058a8, create_info=0x7ffff47ce3c8, table_list=0x7fff7c014118, alter_info=0x7ffff47ce310, order_num=0, order=0x0, ignore=true) at /home/midenok/src/mariadb/10.4/src/sql/sql_table.cc:10319
#4  0x0000000000a17d1c in Sql_cmd_alter_table::execute (this=0x7fff7c0149d8, thd=0x7fff7c000cf8) at /home/midenok/src/mariadb/10.4/src/sql/sql_alter.cc:502
#5  0x0000000000858e19 in mysql_execute_command (thd=0x7fff7c000cf8) at /home/midenok/src/mariadb/10.4/src/sql/sql_parse.cc:6099
#6  0x0000000000848f40 in mysql_parse (thd=0x7fff7c000cf8, rawbuf=0x7fff7c013ff0 "alter ignore table t1 add foreign key (f3) references t1 (f1)", length=61, parser_state=0x7ffff47d1620, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/10.4/src/sql/sql_parse.cc:7909

[midenok]

Bad: error generated

#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);

(rr) p err
$14 = DB_DUPLICATE_KEY

Fix

Correct number and create_name for ALTER case:

--- a/storage/innobase/handler/ha_innodb.cc
+++ b/storage/innobase/handler/ha_innodb.cc
@@ -12404,9 +12404,45 @@ create_table_info_t::tmp_forge_fk_set(
        const char * operation = "FIXME";
        const bool reject_fks = m_flags2 & DICT_TF2_TEMPORARY;

-       {
+       enum_sql_command sqlcom = enum_sql_command(thd_sql_command(m_thd));
+
+       if (sqlcom == SQLCOM_ALTER_TABLE) {
+               dict_table_t *table_to_alter;
+               mem_heap_t*     heap = mem_heap_create(10000);
+               ulint highest_id_so_far;
+               char *n =
+                       dict_get_referenced_table(name,
+                                               LEX_STRING_WITH_LEN(m_form->s->db),
+                                               LEX_STRING_WITH_LEN(m_form->s->table_name),
+                                               &table_to_alter,
+                                               heap);
+
+               /* Starting from 4.0.18 and 4.1.2, we generate foreign key id's in the
+               format databasename/tablename_ibfk_[number], where [number] is local
+               to the table; look for the highest [number] for table_to_alter, so
+               that we can assign to new constraints higher numbers. */
+
+               /* If we are altering a temporary table, the table name after ALTER
+               TABLE does not correspond to the internal table name, and
+               table_to_alter is NULL. TODO: should we fix this somehow? */
+
+               if (table_to_alter) {
+                       n = table_to_alter->name.m_name;
+                       highest_id_so_far = dict_table_get_highest_foreign_id(
+                               table_to_alter);
+               } else {
+                       highest_id_so_far = 0;
+               }
+
+               char* bufend = innobase_convert_name(
+                       create_name, MAX_TABLE_NAME_LEN, n, strlen(n),
+                       m_thd);
+               create_name[bufend-create_name]='\0';
+               number = highest_id_so_far + 1;
+               mem_heap_free(heap);
+       } else {
                char *bufend = innobase_convert_name(create_name, MAX_TABLE_NAME_LEN,
-                                               name, strlen(name), m_trx->mysql_thd);
+                                               name, strlen(name), m_thd);
                create_name[bufend-create_name] = '\0';
        }

Run arguments

rund --debug=d,que_eval_sql,dict_create_add_foreign_to_dictionary

[midenok]

Bug 8: referenced table with #-symbol

Test: innodb-fk

Reproduce

create or replace table `#departaments` (
  `id_depart` int(10) unsigned not null auto_increment,
  primary key (`id_depart`)
);

create or replace table `#departaments_tree` (
  `id_depart` int(10) unsigned not null,
  `id_depart_in` int(10) unsigned not null,
  primary key (`id_depart`,`id_depart_in`),
  constraint `#departaments_tree_ibfk_1` foreign key (`id_depart`) references `#departaments` (`id_depart`)
);

Cause

innobase_convert_from_table_id() is not used (it was used in dict_scan_id()).

[midenok]

TODO

• [x] Merge with innobase_get_foreign_key_info()? No!
• [x] Fix tests

[midenok]

Tests failed previously (9)

rpl.rpl_mdev382 \
innodb.innodb-alter-debug \
innodb.foreign-keys \
innodb.foreign_key \
innodb.innodb-fk \
innodb.innodb-fk-warnings \
innodb_gis.point_basic \
main.information_schema_inno \
main.query_cache_innodb

Tests failed so far (7)

innodb.innodb_mysql \
main.stat_tables_par \
innodb.foreign_key \
innodb.innodb-fk \
innodb.innodb-fk-warnings \
innodb_gis.point_basic \
main.query_cache_innodb

Important fails

CURRENT_TEST: innodb.innodb_mysql
mysqltest: In included file "./include/mix1.inc": 
included from /home/midenok/src/mariadb/trunk/src/mysql-test/suite/innodb/t/innodb_mysql.test at line 19:
At line 1536: query 'CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d),
CONSTRAINT c1 FOREIGN KEY c2 (c) REFERENCES t1 (a) ON DELETE NO ACTION,
CONSTRAINT c2 FOREIGN KEY (c) REFERENCES t1 (a) ON UPDATE NO ACTION) engine=innodb' failed: 1005: Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed")

CURRENT_TEST: main.query_cache_innodb
mysqltest: At line 59: query 'create table `#mysql50#t-2` ( t2id int unsigned, id int unsigned, primary key(t2id, id), foreign key (`id`) references `#mysql50#t-1`(`id`) on delete cascade ) engine=innodb' failed: 1005: Can't create table `#mysql50#-`.`#mysql50#t-2` (errno: 150 "Foreign key constraint is incorrectly formed")

Bug 9: foreign key name and constraint name conflict

Test: innodb_mysql

Reproduce

drop table if exists t2;
create or replace table t1 (a int primary key);
create or replace table t2 (c int,
    constraint c1 foreign key c2 (c) references t1 (a),
    constraint c2 foreign key (c) references t1 (a));

Cause

Misused Foreign_key::name as constraint name, but they in fact may be different.

Fix

Add new member Foreign_key::constraint_name and assign it in sql_yacc.

[midenok]

Tests failed so far

innodb.innodb-fk-warnings main.query_cache_innodb

Bug 10: main.query_cache_innodb fails

Reproduce 1

create or replace table `#mysql50#t-1` ( id int unsigned auto_increment, primary key(id) );
create or replace table `#mysql50#t-2` ( t2id int unsigned, id int unsigned, primary key(t2id, id), foreign key (`id`) references `#mysql50#t-1`(`id`) on delete cascade );

Reproduce 2

create or replace table `t1$ї` ( id int unsigned auto_increment, primary key(id) );
create or replace table `t2$ї` ( t2id int unsigned, id int unsigned, primary key(t2id, id), foreign key (`id`) references `t1$ї`(`id`) on delete cascade )

Bad

#0  dict_get_referenced_table (name=0x7ffff47c2690 "test/t-3", database_name=0x0, database_name_len=0, table_name=0x7fff84013c30 "#mysql50#t-1", table_name_len=12, table=0x7fff84079d40, heap=0x7fff84076b20, cs=0x1ea3c40 <my_charset_utf8_general_ci>) at /home/midenok/src/mariadb/trunk/src/storage/innobase/dict/dict0dict.cc:3430

Good

#0  dict_get_referenced_table (name=0x7ffff545b6c0 "test/t-3", database_name=0x0, database_name_len=0, table_name=0x7fff8408cea8 "t-3", table_name_len=3, table=0x7ffff545b158, heap=0x7fff8408ce10) at /home/midenok/src/mariadb/10.3/src/storage/innobase/dict/dict0dict.cc:3770

Cause

table_name didn't converted.

Fix

Trim #mysql50# prefix, convert with innobase_convert_from_id():

3656            if (!table_id) {
3657    convert_id:
3658                    /* Convert the identifier from connection character set
3659                    to UTF-8. */
3660                    len = 3 * len + 1;
3661                    *id = dst = static_cast<char*>(mem_heap_alloc(heap, len));
3662
3663                    innobase_convert_from_id(cs, dst, str, len);
3664            }

[midenok]

Bug 11: innodb.innodb-fk-warnings fails

Reproduce

create or replace temporary table t1(a int not null primary key, b int, key(b));
create or replace temporary table t2(a int, foreign key(a) references t1(a));
show warnings

Result

Create  table `tmp`.`#sql7f6b_9_1` with foreign key constraint failed. Referenced table `tmp`.`t1` not found in the data dictionary.

Expected

Create  table `tmp`.`t2` with foreign key constraint failed. Referenced table `tmp`.`t1` not found in the data dictionary

Cause

m_table_name contains temporary name. While originally create_name was scanned from SQL string now it is assigned from m_table_name.

[midenok]

Create FRM

#0  open64 () at ../sysdeps/unix/syscall-template.S:84
#1  0x00005555565aa89e in my_create (FileName=0x7fffe5748900 "./test/d2.frm", CreateFlags=0, access_flags=514, MyFlags=0) at /home/midenok/src/mariadb/midenok/src/mysys/my_create.c:46
#2  0x0000555555dbc1f2 in inline_mysql_file_create (key=0, src_file=0x5555567c60e8 "/home/midenok/src/mariadb/midenok/src/sql/discover.cc", src_line=129, filename=0x7fffe5748900 "./test/d2.frm", create_flags=0, access_flags=514, myFlags=0) at /home/midenok/src/mariadb/midenok/src/include/mysql/psi/mysql_file.h:1007
#3  0x0000555555dbcbaa in writefrm (path=0x7fffe574ba10 "./test/d2", db=0x7fff8c011740 "test", table=0x7fff8c0110d8 "d2", tmp_table=false, frmdata=0x7fff8c098db0 "\376\001\n\f\035", len=519) at /home/midenok/src/mariadb/midenok/src/sql/discover.cc:128
#4  0x0000555555c5c5c4 in TABLE_SHARE::write_frm_image (this=0x7fffe5748fe0, frm=0x7fff8c098db0 "\376\001\n\f\035", len=519) at /home/midenok/src/mariadb/midenok/src/sql/table.cc:2727
#5  0x0000555555c56cc6 in TABLE_SHARE::init_from_binary_frm_image (this=0x7fffe5748fe0, thd=0x7fff8c000b00, write=true, frm_image=0x7fff8c098db0 "\376\001\n\f\035", frm_length=519) at /home/midenok/src/mariadb/midenok/src/sql/table.cc:1215
#6  0x0000555555dfcd64 in ha_create_table (thd=0x7fff8c000b00, path=0x7fffe574ba10 "./test/d2", db=0x7fff8c011740 "test", table_name=0x7fff8c0110d8 "d2", create_info=0x7fffe574bff0, frm=0x7fffe574ba00) at /home/midenok/src/mariadb/midenok/src/sql/handler.cc:4761
#7  0x0000555555c76d5d in rea_create_table (thd=0x7fff8c000b00, frm=0x7fffe574ba00, path=0x7fffe574ba10 "./test/d2", db=0x7fff8c011740 "test", table_name=0x7fff8c0110d8 "d2", create_info=0x7fffe574bff0, file=0x7fff8c011d48, no_ha_create_table=false) at /home/midenok/src/mariadb/midenok/src/sql/unireg.cc:500
#8  0x0000555555c211a1 in create_table_impl (thd=0x7fff8c000b00, orig_db=0x7fff8c011740 "test", orig_table_name=0x7fff8c0110d8 "d2", db=0x7fff8c011740 "test", table_name=0x7fff8c0110d8 "d2", path=0x7fffe574ba10 "./test/d2", options=..., create_info=0x7fffe574bff0, alter_info=0x7fffe574bf40, create_table_mode=0, is_trans=0x7fffe574bc6e, key_info=0x7fffe574b9f0, key_count=0x7fffe574b9e4, frm=0x7fffe574ba00) at /home/midenok/src/mariadb/midenok/src/sql/sql_table.cc:4833
#9  0x0000555555c217b0 in mysql_create_table_no_lock (thd=0x7fff8c000b00, db=0x7fff8c011740 "test", table_name=0x7fff8c0110d8 "d2", create_info=0x7fffe574bff0, alter_info=0x7fffe574bf40, is_trans=0x7fffe574bc6e, create_table_mode=0, table_list=0x7fff8c011110) at /home/midenok/src/mariadb/midenok/src/sql/sql_table.cc:4946
#10 0x0000555555c21afc in mysql_create_table (thd=0x7fff8c000b00, create_table=0x7fff8c011110, create_info=0x7fffe574bff0, alter_info=0x7fffe574bf40) at /home/midenok/src/mariadb/midenok/src/sql/sql_table.cc:5019
#11 0x0000555555b567f9 in mysql_execute_command (thd=0x7fff8c000b00) at /home/midenok/src/mariadb/midenok/src/sql/sql_parse.cc:4084
#12 0x0000555555b60abc in mysql_parse (thd=0x7fff8c000b00, rawbuf=0x7fff8c010fd8 "create or replace table d2 (x int) engine innodb with system versioning", length=71, parser_state=0x7fffe574c6b0, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/midenok/src/sql/sql_parse.cc:7902

Open FRM

#0  open64 () at ../sysdeps/unix/syscall-template.S:84
#1  0x00005555565b5379 in my_open (FileName=0x7fffe574ba20 "./test/d2.frm", Flags=0, MyFlags=0) at /home/midenok/src/mariadb/midenok/src/mysys/my_open.c:57
#2  0x0000555555c53603 in inline_mysql_file_open (key=0, src_file=0x5555566c0d08 "/home/midenok/src/mariadb/midenok/src/sql/table.cc", src_line=603, filename=0x7fffe574ba20 "./test/d2.frm", flags=0, myFlags=0) at /home/midenok/src/mariadb/midenok/src/include/mysql/psi/mysql_file.h:1053
#3  0x0000555555c5508a in open_table_def (thd=0x7fff8c000b00, share=0x7fff8c01c718, flags=11) at /home/midenok/src/mariadb/midenok/src/sql/table.cc:603
#4  0x0000555555d38017 in tdc_acquire_share (thd=0x7fff8c000b00, tl=0x7fffe574c6b0, flags=3, out_table=0x7fffe574bd48) at /home/midenok/src/mariadb/midenok/src/sql/table_cache.cc:825
#5  0x0000555555ad6bf7 in open_table (thd=0x7fff8c000b00, table_list=0x7fffe574c6b0, ot_ctx=0x7fffe574c350) at /home/midenok/src/mariadb/midenok/src/sql/sql_base.cc:1742
#6  0x0000555555ad9d03 in open_and_process_table (thd=0x7fff8c000b00, lex=0x7fff8c004618, tables=0x7fffe574c6b0, counter=0x7fffe574c41c, flags=1024, prelocking_strategy=0x7fffe574c420, has_prelocking_list=false, ot_ctx=0x7fffe574c350) at /home/midenok/src/mariadb/midenok/src/sql/sql_base.cc:3414
#7  0x0000555555adaefc in open_tables (thd=0x7fff8c000b00, options=..., start=0x7fffe574c400, counter=0x7fffe574c41c, flags=1024, prelocking_strategy=0x7fffe574c420) at /home/midenok/src/mariadb/midenok/src/sql/sql_base.cc:3932
#8  0x0000555555ad395a in open_tables (thd=0x7fff8c000b00, tables=0x7fffe574c400, counter=0x7fffe574c41c, flags=1024, prelocking_strategy=0x7fffe574c420) at /home/midenok/src/mariadb/midenok/src/sql/sql_base.h:236
#9  0x0000555555adc7de in open_normal_and_derived_tables (thd=0x7fff8c000b00, tables=0x7fffe574c6b0, flags=1024, dt_phases=34) at /home/midenok/src/mariadb/midenok/src/sql/sql_base.cc:4750
#10 0x0000555555be87f7 in mysqld_list_fields (thd=0x7fff8c000b00, table_list=0x7fffe574c6b0, wild=0x7fff8c010ff8 "") at /home/midenok/src/mariadb/midenok/src/sql/sql_show.cc:1428
#11 0x0000555555b5153c in dispatch_command (command=COM_FIELD_LIST, thd=0x7fff8c000b00, packet=0x7fff8c0087f4 "", packet_length=3, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/midenok/src/sql/sql_parse.cc:2008

[midenok]

main.query_cache_innodb fails

--- /home/midenok/src/mariadb/10.5/src/mysql-test/main/query_cache_innodb.result        2019-09-30 14:04:
25.953281536 +0300
+++ /home/midenok/src/mariadb/10.5/src/mysql-test/main/query_cache_innodb.reject        2019-10-18 14:52:
22.329040421 +0300
@@ -85,7 +85,7 @@
 use test;
 drop database `#mysql50#-`;
 SET NAMES default;
-FOUND 8 /\[ERROR\] Invalid \(old\?\) table or database name/ in mysqld.1.err
+FOUND 12 /\[ERROR\] Invalid \(old\?\) table or database name/ in mysqld.1.err
 set global query_cache_type=DEFAULT;
 set global query_cache_size=@save_query_cache_size;
 End of 10.2 tests

Good

2019-10-18 14:57:41 9 [ERROR] Invalid (old?) table or database name '-'
2019-10-18 14:57:41 9 [ERROR] Invalid (old?) table or database name '-'
2019-10-18 14:57:41 9 [ERROR] Invalid (old?) table or database name '-'
2019-10-18 14:57:41 9 [ERROR] Invalid (old?) table or database name '-'
2019-10-18 14:57:41 9 [ERROR] Invalid (old?) table or database name '-'
2019-10-18 14:57:41 9 [ERROR] Invalid (old?) table or database name 't-2'
2019-10-18 14:57:41 9 [ERROR] Invalid (old?) table or database name 't-1'
2019-10-18 14:57:41 9 [ERROR] Invalid (old?) table or database name 't-2'

Bad

2019-10-18 14:52:22 9 [ERROR] Invalid (old?) table or database name '-'
2019-10-18 14:52:22 9 [ERROR] Invalid (old?) table or database name '-'
2019-10-18 14:52:22 9 [ERROR] Invalid (old?) table or database name '-'
2019-10-18 14:52:22 9 [ERROR] Invalid (old?) table or database name '-'
2019-10-18 14:52:22 9 [ERROR] Invalid (old?) table or database name '-'
2019-10-18 14:52:22 9 [ERROR] Invalid (old?) table or database name 't-2'
2019-10-18 14:52:22 9 [ERROR] Invalid (old?) table or database name '-'
2019-10-18 14:52:22 9 [ERROR] Invalid (old?) table or database name '-'
2019-10-18 14:52:22 9 [ERROR] Invalid (old?) table or database name '-'
2019-10-18 14:52:22 9 [ERROR] Invalid (old?) table or database name '-'
2019-10-18 14:52:22 9 [ERROR] Invalid (old?) table or database name 't-1'
2019-10-18 14:52:22 9 [ERROR] Invalid (old?) table or database name 't-2'

Error printed

#0  filename_to_tablename (from=0x7fffe0fb92e0 "-", to=0x7fffe0fb93f0 "#mysql50#-", to_length=193, stay_quiet=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:395
#1  0x0000000002352c0f in get_foreign_key_info (thd=0x62b00009a208, s=0x61b00007fca8, foreign=0x616000056510) at /home/midenok/src/mariadb/10.5/src/storage/innobase/handler/ha_innodb.cc:15317
#2  0x00000000023522c2 in ha_innobase::build_foreign_list (this=0x61c0000340a8, thd=0x62b00009a208, foreign_set=std::set with 1 element = {...}, err=@0x7fffe0fba300: false) at /home/midenok/src/mariadb/10.5/src/storage/innobase/handler/ha_innodb.cc:5957
#3  0x0000000002358341 in ha_innobase::open (this=0x61c0000340a8, name=0x61b000080250 "./-/t-1") at /home/midenok/src/mariadb/10.5/src/storage/innobase/handler/ha_innodb.cc:6264
#4  0x00000000016ba87e in handler::ha_open (this=0x61c0000340a8, table_arg=0x620000047088, name=0x61b000080250 "./-/t-1", mode=2, test_if_locked=18, mem_root=0x0, partitions_to_open=0x0) at /home/midenok/src/mariadb/10.5/src/sql/handler.cc:2709
#5  0x00000000010954d4 in open_table_from_share (thd=0x62b00009a208, share=0x61b00007fca8, alias=0x62b0000a13e0, db_stat=33, prgflag=8, ha_open_flags=18, outparam=0x620000047088, is_create_table=false, partitions_to_open=0x0) at /home/midenok/src/mariadb/10.5/src/sql/table.cc:4017
#6  0x0000000000a5478d in open_table (thd=0x62b00009a208, table_list=0x62b0000a1398, ot_ctx=0x7fffe0fbd880) at /home/midenok/src/mariadb/10.5/src/sql/sql_base.cc:2081
#7  0x0000000000a62ad6 in open_and_process_table (thd=0x62b00009a208, tables=0x62b0000a1398, counter=0x7fffe0fbdf60, flags=0, prelocking_strategy=0x7fffe0fbe200, has_prelocking_list=false, ot_ctx=0x7fffe0fbd880) at /home/midenok/src/mariadb/10.5/src/sql/sql_base.cc:3848
#8  0x0000000000a5ef29 in open_tables (thd=0x62b00009a208, options=..., start=0x7fffe0fbdf40, counter=0x7fffe0fbdf60, flags=0, prelocking_strategy=0x7fffe0fbe200) at /home/midenok/src/mariadb/10.5/src/sql/sql_base.cc:4346
#9  0x0000000000a6c03c in open_and_lock_tables (thd=0x62b00009a208, options=..., tables=0x62b0000a1398, derived=true, flags=0, prelocking_strategy=0x7fffe0fbe200) at /home/midenok/src/mariadb/10.5/src/sql/sql_base.cc:5222
#10 0x0000000000982f27 in open_and_lock_tables (thd=0x62b00009a208, tables=0x62b0000a1398, derived=true, flags=0) at /home/midenok/src/mariadb/10.5/src/sql/sql_base.h:503
#11 0x0000000000b7666c in mysql_insert (thd=0x62b00009a208, table_list=0x62b0000a1398, fields=..., values_list=..., update_fields=..., update_values=..., duplic=DUP_ERROR, ignore=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_insert.cc:764
#12 0x0000000000c6f0a8 in mysql_execute_command (thd=0x62b00009a208) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:4474
#13 0x0000000000c578c5 in mysql_parse (thd=0x62b00009a208, rawbuf=0x62b0000a1228 "insert into `#mysql50#t-1` values (1)", length=37, parser_state=0x7fffe0fc39c0, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:7938

#0  filename_to_tablename (from=0x7fffe0fb9410 "-", to=0x7fffe0fb9240 "#mysql50#-", to_length=386, stay_quiet=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:395
#1  0x000000000234a34c in innobase_invalidate_query_cache (trx=0x7fffedad1920, full_name=0x60200000eeb0 "-/t-2") at /home/midenok/src/mariadb/10.5/src/storage/innobase/handler/ha_innodb.cc:3136
#2  0x000000000277f6e5 in row_ins_invalidate_query_cache (thr=0x6240001e0190, name=0x60200000eeb0 "-/t-2") at /home/midenok/src/mariadb/10.5/src/storage/innobase/row/row0ins.cc:917
#3  0x0000000002769643 in row_ins_foreign_check_on_constraint (thr=0x6240001e0190, foreign=0x616000056510, pcur=0x7fffe0fba2c0, entry=0x616000058630, mtr=0x7fffe0fba400) at /home/midenok/src/mariadb/10.5/src/storage/innobase/row/row0ins.cc:1099
#4  0x00000000027669a1 in row_ins_check_foreign_constraint (check_ref=0, foreign=0x616000056510, table=0x6190000cf910, entry=0x616000058630, thr=0x6240001e0190) at /home/midenok/src/mariadb/10.5/src/storage/innobase/row/row0ins.cc:1848
#5  0x00000000028eff00 in row_upd_check_references_constraints (node=0x620000048c18, pcur=0x61100006c100, table=0x6190000cf910, index=0x6190000d1210, offsets=0x7fffe0fbbe60, thr=0x6240001e0190, mtr=0x7fffe0fbb8c0) at /home/midenok/src/mariadb/10.5/src/storage/innobase/row/row0upd.cc:296
#6  0x00000000028eaa20 in row_upd_del_mark_clust_rec (node=0x620000048c18, index=0x6190000d1210, offsets=0x7fffe0fbbe60, thr=0x6240001e0190, referenced=1, mtr=0x7fffe0fbb8c0) at /home/midenok/src/mariadb/10.5/src/storage/innobase/row/row0upd.cc:2989
#7  0x00000000028e8d1e in row_upd_clust_step (node=0x620000048c18, thr=0x6240001e0190) at /home/midenok/src/mariadb/10.5/src/storage/innobase/row/row0upd.cc:3159
#8  0x00000000028e1d5b in row_upd (node=0x620000048c18, thr=0x6240001e0190) at /home/midenok/src/mariadb/10.5/src/storage/innobase/row/row0upd.cc:3286
#9  0x00000000028e0f92 in row_upd_step (thr=0x6240001e0190) at /home/midenok/src/mariadb/10.5/src/storage/innobase/row/row0upd.cc:3430
#10 0x00000000027e6327 in row_update_for_mysql (prebuilt=0x620000048110) at /home/midenok/src/mariadb/10.5/src/storage/innobase/row/row0mysql.cc:1889
#11 0x000000000236becc in ha_innobase::delete_row (this=0x61c0000340a8, record=0x6190000d66c0 "\377\001") at /home/midenok/src/mariadb/10.5/src/storage/innobase/handler/ha_innodb.cc:8905
#12 0x00000000016eb20f in handler::ha_delete_row (this=0x61c0000340a8, buf=0x6190000d66c0 "\377\001") at /home/midenok/src/mariadb/10.5/src/sql/handler.cc:6813
#13 0x0000000001ce075a in TABLE::delete_row (this=0x620000047088) at /home/midenok/src/mariadb/10.5/src/sql/sql_delete.cc:297
#14 0x0000000001cd36a9 in mysql_delete (thd=0x62b00009a208, table_list=0x62b0000a1380, conds=0x0, order_list=0x62b00009eba8, limit=18446744073709551615, options=0, result=0x0) at /home/midenok/src/mariadb/10.5/src/sql/sql_delete.cc:834
#15 0x0000000000c711c2 in mysql_execute_command (thd=0x62b00009a208) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:4673
#16 0x0000000000c578c5 in mysql_parse (thd=0x62b00009a208, rawbuf=0x62b0000a1228 "delete from `#mysql50#t-1`", length=26, parser_state=0x7fffe0fc39c0, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:7938

Cause

Foreign key list is built on table open, hence different counter for filename_to_tablename() call.

[midenok]

Info: tests

mtrz main.stat_tables_par \
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]

Bug

Reproduce

drop tables if exists t3, t2, t1;
create or replace table t1(fld1 int not null primary key);
create or replace table t2(fld1 int not null,
    fld2 int as (fld1) virtual,
    key(fld2, fld1),
    foreign key(fld1) references t1(fld1)
    on delete cascade);
insert into t1 values(1), (2);
insert into t2 values(1, default), (2, default);
rename table t2 to t3;
delete from t1 where fld1= 1;

Result

#3  0x00007ffff74f1012 in __GI___assert_fail (assertion=0x38b0020 <str> "mysql_table", file=0x38a8180 <str> "/home/midenok/src/mariadb/10.5/src/storage/innobase/handler/ha_innodb.cc", line=20943, function=0x38b0100 <__PRETTY_FUNCTION__._Z27innobase_get_computed_valueP8dtuple_tPK12dict_v_col_tPK12dict_index_tPP16mem_block_info_tS8_PK12dict_field_tP3THDP5TABLEPhPK12dict_table_tP5upd_tP14dict_foreign_t> "dfield_t *innobase_get_computed_value(dtuple_t *, const dict_v_col_t *, const dict_index_t *, mem_heap_t **, mem_heap_t *, const dict_field_t *, THD *, TABLE *, byte *, const dict_table_t *, upd_t *, dict_foreign_t *)") at assert.c:101
#4  0x00000000023a6d7b in innobase_get_computed_value (row=0x617000095090, col=0x61d000293798, index=0x619000143810, local_heap=0x7fffc866d940, heap=0x612000089a40, ifield=0x0, thd=0x62b00009a208, mysql_table=0x0, mysql_rec=0x0, old_table=0x0, parent_update=0x0, foreign=0x0) at /home/midenok/src/mariadb/10.5/src/storage/innobase/handler/ha_innodb.cc:20943
#5  0x00000000028e2c8a in row_upd_store_v_row (node=0x617000094d10, update=0x0, thd=0x62b00009a208, mysql_table=0x0) at /home/midenok/src/mariadb/10.5/src/storage/innobase/row/row0upd.cc:2174
#6  0x00000000028e1cbc in row_upd_store_row (node=0x617000094d10, thd=0x62b00009a208, mysql_table=0x0) at /home/midenok/src/mariadb/10.5/src/storage/innobase/row/row0upd.cc:2241
#7  0x00000000028ed3f5 in row_upd_del_mark_clust_rec (node=0x617000094d10, index=0x619000143810, offsets=0x7fffc866e900, thr=0x62400022c190, referenced=0, mtr=0x7fffc866e360) at /home/midenok/src/mariadb/10.5/src/storage/innobase/row/row0upd.cc:2973
#8  0x00000000028eb67e in row_upd_clust_step (node=0x617000094d10, thr=0x62400022c190) at /home/midenok/src/mariadb/10.5/src/storage/innobase/row/row0upd.cc:3160
#9  0x00000000028e46bb in row_upd (node=0x617000094d10, thr=0x62400022c190) at /home/midenok/src/mariadb/10.5/src/storage/innobase/row/row0upd.cc:3289
#10 0x00000000028e38f2 in row_upd_step (thr=0x62400022c190) at /home/midenok/src/mariadb/10.5/src/storage/innobase/row/row0upd.cc:3433
#11 0x00000000027ea506 in row_update_cascade_for_mysql (thr=0x62400022c190, node=0x617000094d10, table=0x619000141f10) at /home/midenok/src/mariadb/10.5/src/storage/innobase/row/row0mysql.cc:2255
#12 0x000000000276ccf6 in row_ins_foreign_check_on_constraint (thr=0x62400022c190, foreign=0x616000084d10, pcur=0x7fffc86704c0, entry=0x616000085f30, mtr=0x7fffc8670600) at /home/midenok/src/mariadb/10.5/src/storage/innobase/row/row0ins.cc:1441
#13 0x0000000002766d31 in row_ins_check_foreign_constraint (check_ref=0, foreign=0x616000084d10, table=0x61900013e810, entry=0x616000085f30, thr=0x62400022c190) at /home/midenok/src/mariadb/10.5/src/storage/innobase/row/row0ins.cc:1848
#14 0x00000000028f2a40 in row_upd_check_references_constraints (node=0x620000052c28, pcur=0x611000099ec0, table=0x61900013e810, index=0x619000140610, offsets=0x7fffc8672060, thr=0x62400022c190, mtr=0x7fffc8671ac0) at /home/midenok/src/mariadb/10.5/src/storage/innobase/row/row0upd.cc:296
#15 0x00000000028ed560 in row_upd_del_mark_clust_rec (node=0x620000052c28, index=0x619000140610, offsets=0x7fffc8672060, thr=0x62400022c190, referenced=1, mtr=0x7fffc8671ac0) at /home/midenok/src/mariadb/10.5/src/storage/innobase/row/row0upd.cc:2990
#16 0x00000000028eb67e in row_upd_clust_step (node=0x620000052c28, thr=0x62400022c190) at /home/midenok/src/mariadb/10.5/src/storage/innobase/row/row0upd.cc:3160
#17 0x00000000028e46bb in row_upd (node=0x620000052c28, thr=0x62400022c190) at /home/midenok/src/mariadb/10.5/src/storage/innobase/row/row0upd.cc:3289
#18 0x00000000028e38f2 in row_upd_step (thr=0x62400022c190) at /home/midenok/src/mariadb/10.5/src/storage/innobase/row/row0upd.cc:3433
#19 0x00000000027e69c7 in row_update_for_mysql (prebuilt=0x620000052110) at /home/midenok/src/mariadb/10.5/src/storage/innobase/row/row0mysql.cc:1889
#20 0x00000000023681dc in ha_innobase::delete_row (this=0x61c0000460a8, record=0x6190001469b8 "\377\001") at /home/midenok/src/mariadb/10.5/src/storage/innobase/handler/ha_innodb.cc:8903
#21 0x00000000016ec455 in handler::ha_delete_row (this=0x61c0000460a8, buf=0x6190001469b8 "\377\001") at /home/midenok/src/mariadb/10.5/src/sql/handler.cc:6813
#22 0x0000000001ce2bda in TABLE::delete_row (this=0x620000051088) at /home/midenok/src/mariadb/10.5/src/sql/sql_delete.cc:286
#23 0x0000000001cd5d72 in mysql_delete (thd=0x62b00009a208, table_list=0x62b0000a1340, conds=0x62b0000a1c08, order_list=0x62b00009ebb8, limit=18446744073709551615, options=0, result=0x0) at /home/midenok/src/mariadb/10.5/src/sql/sql_delete.cc:819
#24 0x0000000000c6d5a7 in mysql_execute_command (thd=0x62b00009a208) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:4733
#25 0x0000000000c52e65 in mysql_parse (thd=0x62b00009a208, rawbuf=0x62b0000a1228 "delete from t1 where fld1= 1", length=28, parser_state=0x7fffc8679ea0, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:7998

frame 4

20943           ut_ad(mysql_table);

frame 7

2973            row_upd_store_row(node, trx->mysql_thd,
2974                              thr->prebuilt  && thr->prebuilt->table == node->table
2975                              ? thr->prebuilt->m_mysql_table : NULL);

(gdb) p thr->prebuilt->table == node->table
$36 = false
(gdb) p thr->prebuilt->table->table_name
There is no member or method named table_name.
(gdb) p thr->prebuilt->table->name
$37 = {
  m_name = 0x60200001a250 "test/t1",
  static part_suffix = "#P#"
}
(gdb) p node->table->name
$38 = {
  m_name = 0x60200001a590 "test/t3",
  static part_suffix = "#P#"
}

[midenok]

Good: table t3 found

#0  find_fk_open_table (thd=0x7f25e0000d08, db=0x7f263c0f3150 "test", db_len=4, table=0x7f263c0f3080 "t3", table_len=2) at /home/midenok/src/mariadb/10.5/src/sql/sql_class.cc:4770
#1  0x0000000001027bf1 in innodb_find_table_for_vc (thd=0x7f25e0000d08, table=0x7f25e002d2b8) at /home/midenok/src/mariadb/10.5/src/storage/innobase/handler/ha_innodb.cc:20150
#2  0x0000000001027e4f in innobase_allocate_row_for_vcol (thd=0x7f25e0000d08, index=0x7f25e002f958, heap=0x7f263c0f3350, table=0x7f263c0f3358, record=0x7f263c0f3340, storage=0x7f263c0f3338) at /home/midenok/src/mariadb/10.5/src/storage/innobase/handler/ha_innodb.cc:20291
#3  0x000000000126fe6d in row_upd_store_v_row (node=0x7f25e00295b8, update=0x0, thd=0x7f25e0000d08, mysql_table=0x0) at /home/midenok/src/mariadb/10.5/src/storage/innobase/row/row0upd.cc:2121
#4  0x000000000126fbb8 in row_upd_store_row (node=0x7f25e00295b8, thd=0x7f25e0000d08, mysql_table=0x0) at /home/midenok/src/mariadb/10.5/src/storage/innobase/row/row0upd.cc:2241
#5  0x0000000001275446 in row_upd_del_mark_clust_rec (node=0x7f25e00295b8, index=0x7f25e002f958, offsets=0x7f263c0f3950, thr=0x7f25e0035528, referenced=0, mtr=0x7f263c0f3c80) at /home/midenok/src/mariadb/10.5/src/storage/innobase/row/row0upd.cc:2973
#6  0x00000000012747c5 in row_upd_clust_step (node=0x7f25e00295b8, thr=0x7f25e0035528) at /home/midenok/src/mariadb/10.5/src/storage/innobase/row/row0upd.cc:3160
#7  0x0000000001270ab2 in row_upd (node=0x7f25e00295b8, thr=0x7f25e0035528) at /home/midenok/src/mariadb/10.5/src/storage/innobase/row/row0upd.cc:3289
#8  0x00000000012705c5 in row_upd_step (thr=0x7f25e0035528) at /home/midenok/src/mariadb/10.5/src/storage/innobase/row/row0upd.cc:3433
#9  0x00000000011fff8f in row_update_cascade_for_mysql (thr=0x7f25e0035528, node=0x7f25e00295b8, table=0x7f25e002d2b8) at /home/midenok/src/mariadb/10.5/src/storage/innobase/row/row0mysql.cc:2255
#10 0x00000000011c63f1 in row_ins_foreign_check_on_constraint (thr=0x7f25e0035528, foreign=0x7f25e0030698, pcur=0x7f263c0f5410, entry=0x7f25e00280a0, mtr=0x7f263c0f4f10) at /home/midenok/src/mariadb/10.5/src/storage/innobase/row/row0ins.cc:1441
#11 0x00000000011c3b6e in row_ins_check_foreign_constraint (check_ref=0, foreign=0x7f25e0030698, table=0x7f25e0026eb8, entry=0x7f25e00280a0, thr=0x7f25e0035528) at /home/midenok/src/mariadb/10.5/src/storage/innobase/row/row0ins.cc:1848
#12 0x00000000012779d4 in row_upd_check_references_constraints (node=0x7f25e0035250, pcur=0x7f25e0026c30, table=0x7f25e0026eb8, index=0x7f25e002ae78, offsets=0x7f263c0f58c0, thr=0x7f25e0035528, mtr=0x7f263c0f5bf0) at /home/midenok/src/mariadb/10.5/src/storage/innobase/row/row0upd.cc:296
#13 0x00000000012754d5 in row_upd_del_mark_clust_rec (node=0x7f25e0035250, index=0x7f25e002ae78, offsets=0x7f263c0f58c0, thr=0x7f25e0035528, referenced=1, mtr=0x7f263c0f5bf0) at /home/midenok/src/mariadb/10.5/src/storage/innobase/row/row0upd.cc:2990
#14 0x00000000012747c5 in row_upd_clust_step (node=0x7f25e0035250, thr=0x7f25e0035528) at /home/midenok/src/mariadb/10.5/src/storage/innobase/row/row0upd.cc:3160
#15 0x0000000001270ab2 in row_upd (node=0x7f25e0035250, thr=0x7f25e0035528) at /home/midenok/src/mariadb/10.5/src/storage/innobase/row/row0upd.cc:3289
#16 0x00000000012705c5 in row_upd_step (thr=0x7f25e0035528) at /home/midenok/src/mariadb/10.5/src/storage/innobase/row/row0upd.cc:3433
#17 0x00000000011fe871 in row_update_for_mysql (prebuilt=0x7f25e00347a8) at /home/midenok/src/mariadb/10.5/src/storage/innobase/row/row0mysql.cc:1889
#18 0x00000000010147b7 in ha_innobase::delete_row (this=0x7f25e002c030, record=0x7f25e00255d8 "\377\001") at /home/midenok/src/mariadb/10.5/src/storage/innobase/handler/ha_innodb.cc:8825
#19 0x0000000000bf5efe in handler::ha_delete_row (this=0x7f25e002c030, buf=0x7f25e00255d8 "\377\001") at /home/midenok/src/mariadb/10.5/src/sql/handler.cc:6738
#20 0x0000000000e1094e in TABLE::delete_row (this=0x7f25e0033938) at /home/midenok/src/mariadb/10.5/src/sql/sql_delete.cc:286
#21 0x0000000000e0caee in mysql_delete (thd=0x7f25e0000d08, table_list=0x7f25e00145d8, conds=0x7f25e0014e70, order_list=0x7f25e00056b8, limit=18446744073709551615, options=0, result=0x0) at /home/midenok/src/mariadb/10.5/src/sql/sql_delete.cc:819
#22 0x000000000084516d in mysql_execute_command (thd=0x7f25e0000d08) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:4733
#23 0x000000000083ab90 in mysql_parse (thd=0x7f25e0000d08, rawbuf=0x7f25e00144f0 "delete from t1 where fld1= 1", length=28, parser_state=0x7f263c0f95c0, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:7998

Bad

No t3 in thd->open_tables.

Cause

At TABLE_LIST::init_one_table_for_prelocking() t1 has old referenced_keys in TABLE_SHARE.

[midenok]

diff --git a/sql/table.h b/sql/table.h
index d0a1b9ab5a1..e2f920ad4a7 100644
--- a/sql/table.h
+++ b/sql/table.h
@@ -651,6 +651,35 @@ struct TABLE_SHARE
   {
     return referenced_keys && !referenced_keys->is_empty();
   }
+  void update_foreign_tables(THD *thd)
+  {
+    DBUG_ASSERT(foreign_keys);
+    List_iterator_fast<FOREIGN_KEY_INFO> it(*foreign_keys);
+    List_iterator_fast<FOREIGN_KEY_INFO> ref_it;
+    while (FOREIGN_KEY_INFO *fk= it++)
+    {
+      if (!cmp(fk->foreign_db, db) && !cmp(fk->foreign_table, table_name))
+        continue;
+      TDC_element *el= tdc_lock_share(thd, fk->foreign_db.str, fk->foreign_table.str);
+      if (!el)
+        continue;
+      if (el->share->referenced_by_foreign_key())
+      {
+        FOREIGN_KEY_INFO *ref;
+        ref_it.init(*el->share->referenced_keys);
+        while ((ref= ref_it++))
+        {
+          if (!cmp(ref->foreign_db, el->share->db) && !cmp(ref->foreign_table, el->share->table_name))
+            break;
+        }
+        if (!ref)
+      }
+      else
+      {
+      }
+      tdc_unlock_share(el);
+    }
+  }

   Virtual_column_info **check_constraints;
   uint *blob_field;                    /* Index to blobs in Field arrray*/

[midenok]

Bug

Reproduce

--source include/have_innodb.inc

set default_storage_engine= innodb;

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

drop table t2, t1;

Result

#0  __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
#1  0x00007f610fcb2535 in __GI_abort () at abort.c:79
#2  0x000000000131649a in ib::fatal::~fatal (this=0x7f61000a9b18) at /home/midenok/src/mariadb/10.5b/src/storage/innobase/ut/ut0ut.cc:597
#3  0x0000000001200172 in row_mysql_handle_errors (new_err=0x7f61000aa574, trx=0x7f610927e130, thr=0x7f609c033cd8, savept=0x7f61000aa578) at /home/midenok/src/mariadb/10.5b/src/storage/innobase/row/row0mysql.cc:790
#4  0x00000000012049a0 in row_update_for_mysql (prebuilt=0x7f609c032f58) at /home/midenok/src/mariadb/10.5b/src/storage/innobase/row/row0mysql.cc:1908
#5  0x000000000101474e in ha_innobase::update_row (this=0x7f609c02c000, old_row=0x7f609c026150 "\377\001", new_row=0x7f609c026148 "\377\002") at /home/midenok/src/mariadb/10.5b/src/storage/innobase/handler/ha_innodb.cc:8777
#6  0x0000000000bf62aa in handler::ha_update_row (this=0x7f609c02c000, old_data=0x7f609c026150 "\377\001", new_data=0x7f609c026148 "\377\002") at /home/midenok/src/mariadb/10.5b/src/sql/handler.cc:6750
#7  0x000000000097da11 in mysql_update (thd=0x7f609c000d08, table_list=0x7f609c0145f8, fields=..., values=..., conds=0x0, order_num=0, order=0x0, limit=18446744073709551615, ignore=false, found_return=0x7f61000ac118, updated_return=0x7f61000ac110) at /home/midenok/src/mariadb/10.5b/src/sql/sql_update.cc:1046
#8  0x0000000000843e38 in mysql_execute_command (thd=0x7f609c000d08) at /home/midenok/src/mariadb/10.5b/src/sql/sql_parse.cc:4347
#9  0x000000000083b410 in mysql_parse (thd=0x7f609c000d08, rawbuf=0x7f609c014520 "update t1 set fld1= 2", length=21, parser_state=0x7f61000ad5c0, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/10.5b/src/sql/sql_parse.cc:7998

frame 3

777             case DB_FOREIGN_EXCEED_MAX_CASCADE:
778                     ib::error() << "Cannot delete/update rows with cascading"
779                             " foreign key constraints that exceed max depth of "
780                             << FK_MAX_CASCADE_DEL << ". Please drop excessive"
781                             " foreign constraints and try again";
782                     goto rollback_to_savept;
783             case DB_UNSUPPORTED:
784                     ib::error() << "Cannot delete/update rows with cascading"
785                             " foreign key constraints in timestamp-based temporal"
786                             " table. Please drop excessive"
787                             " foreign constraints and try again";
788                     goto rollback_to_savept;
789             default:
790                     ib::fatal() << "Unknown error code " << err << ": "
791                             << ut_strerr(err);
792             }

(rr) p err
$11 = DB_OUT_OF_MEMORY

Error returned

#0  innobase_allocate_row_for_vcol (thd=0x7f609c000d08, index=0x7f609c0829a8, heap=0x7f61000a8170, table=0x7f61000a8168, record=0x7f61000a8158, storage=0x7f61000a8160) at /home/midenok/src/mariadb/10.5b/src/storage/innobase/handler/ha_innodb.cc:20860
#1  0x00000000011d688a in row_ins_foreign_fill_virtual (cascade=0x7f609c02eba8, rec=0x7f6108fc407d "", index=0x7f609c0829a8, node=0x7f609c033a00, foreign=0x7f609c083608, err=0x7f61000a89dc) at /home/midenok/src/mariadb/10.5b/src/storage/innobase/row/row0ins.cc:968
#2  0x00000000011cc02e in row_ins_foreign_check_on_constraint (thr=0x7f609c033cd8, foreign=0x7f609c083608, pcur=0x7f61000a9550, entry=0x7f609c0280d0, mtr=0x7f61000a9050) at /home/midenok/src/mariadb/10.5b/src/storage/innobase/row/row0ins.cc:1366
#3  0x00000000011c9bde in row_ins_check_foreign_constraint (check_ref=0, foreign=0x7f609c083608, table=0x7f609c026ee8, entry=0x7f609c0280d0, thr=0x7f609c033cd8) at /home/midenok/src/mariadb/10.5b/src/storage/innobase/row/row0ins.cc:1848
#4  0x000000000127da44 in row_upd_check_references_constraints (node=0x7f609c033a00, pcur=0x7f609c026c60, table=0x7f609c026ee8, index=0x7f609c02aec8, offsets=0x7f609c080328, thr=0x7f609c033cd8, mtr=0x7f61000a9da0) at /home/midenok/src/mariadb/10.5b/src/storage/innobase/row/row0upd.cc:296
#5  0x000000000127c794 in row_upd_clust_rec_by_insert (node=0x7f609c033a00, index=0x7f609c02aec8, thr=0x7f609c033cd8, referenced=1, mtr=0x7f61000a9da0) at /home/midenok/src/mariadb/10.5b/src/storage/innobase/row/row0upd.cc:2759
#6  0x000000000127aa41 in row_upd_clust_step (node=0x7f609c033a00, thr=0x7f609c033cd8) at /home/midenok/src/mariadb/10.5b/src/storage/innobase/row/row0upd.cc:3210
#7  0x0000000001276b22 in row_upd (node=0x7f609c033a00, thr=0x7f609c033cd8) at /home/midenok/src/mariadb/10.5b/src/storage/innobase/row/row0upd.cc:3289
#8  0x0000000001276635 in row_upd_step (thr=0x7f609c033cd8) at /home/midenok/src/mariadb/10.5b/src/storage/innobase/row/row0upd.cc:3433
#9  0x00000000012048e1 in row_update_for_mysql (prebuilt=0x7f609c032f58) at /home/midenok/src/mariadb/10.5b/src/storage/innobase/row/row0mysql.cc:1889
#10 0x000000000101474e in ha_innobase::update_row (this=0x7f609c02c000, old_row=0x7f609c026150 "\377\001", new_row=0x7f609c026148 "\377\002") at /home/midenok/src/mariadb/10.5b/src/storage/innobase/handler/ha_innodb.cc:8777
#11 0x0000000000bf62aa in handler::ha_update_row (this=0x7f609c02c000, old_data=0x7f609c026150 "\377\001", new_data=0x7f609c026148 "\377\002") at /home/midenok/src/mariadb/10.5b/src/sql/handler.cc:6750
#12 0x000000000097da11 in mysql_update (thd=0x7f609c000d08, table_list=0x7f609c0145f8, fields=..., values=..., conds=0x0, order_num=0, order=0x0, limit=18446744073709551615, ignore=false, found_return=0x7f61000ac118, updated_return=0x7f61000ac110) at /home/midenok/src/mariadb/10.5b/src/sql/sql_update.cc:1046
#13 0x0000000000843e38 in mysql_execute_command (thd=0x7f609c000d08) at /home/midenok/src/mariadb/10.5b/src/sql/sql_parse.cc:4347
#14 0x000000000083b410 in mysql_parse (thd=0x7f609c000d08, rawbuf=0x7f609c014520 "update t1 set fld1= 2", length=21, parser_state=0x7f61000ad5c0, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/10.5b/src/sql/sql_parse.cc:7998

20854           if (!*table)
20855                   *table= innodb_find_table_for_vc(thd, index->table);
20856   
20857           /* For purge thread, there is a possiblity that table could have
20858           dropped, corrupted or unaccessible. */
20859           if (!*table)
20860                   return true;

Side bug

This error is wrongly handled as DB_OUT_OF_MEMORY:

968             if (innobase_allocate_row_for_vcol(thd, index, &v_heap,
969                                                &mysql_table,
970                                                &record, &vcol_storage)) {
971                     if (v_heap) mem_heap_free(v_heap);
972                     *err = DB_OUT_OF_MEMORY;
973                     goto func_exit;
974             }

[midenok]

Table t2 not present in thd->open_tables

#0  innodb_find_table_for_vc (thd=0x7f609c000d08, table=0x7f609c080978) at /home/midenok/src/mariadb/10.5b/src/storage/innobase/handler/ha_innodb.cc:20677
#1  0x000000000102b7ff in innobase_allocate_row_for_vcol (thd=0x7f609c000d08, index=0x7f609c0829a8, heap=0x7f61000a8170, table=0x7f61000a8168, record=0x7f61000a8158, storage=0x7f61000a8160) at /home/midenok/src/mariadb/10.5b/src/storage/innobase/handler/ha_innodb.cc:20855
#2  0x00000000011d688a in row_ins_foreign_fill_virtual (cascade=0x7f609c02eba8, rec=0x7f6108fc407d "", index=0x7f609c0829a8, node=0x7f609c033a00, foreign=0x7f609c083608, err=0x7f61000a89dc) at /home/midenok/src/mariadb/10.5b/src/storage/innobase/row/row0ins.cc:968
#3  0x00000000011cc02e in row_ins_foreign_check_on_constraint (thr=0x7f609c033cd8, foreign=0x7f609c083608, pcur=0x7f61000a9550, entry=0x7f609c0280d0, mtr=0x7f61000a9050) at /home/midenok/src/mariadb/10.5b/src/storage/innobase/row/row0ins.cc:1366
#4  0x00000000011c9bde in row_ins_check_foreign_constraint (check_ref=0, foreign=0x7f609c083608, table=0x7f609c026ee8, entry=0x7f609c0280d0, thr=0x7f609c033cd8) at /home/midenok/src/mariadb/10.5b/src/storage/innobase/row/row0ins.cc:1848
#5  0x000000000127da44 in row_upd_check_references_constraints (node=0x7f609c033a00, pcur=0x7f609c026c60, table=0x7f609c026ee8, index=0x7f609c02aec8, offsets=0x7f609c080328, thr=0x7f609c033cd8, mtr=0x7f61000a9da0) at /home/midenok/src/mariadb/10.5b/src/storage/innobase/row/row0upd.cc:296
#6  0x000000000127c794 in row_upd_clust_rec_by_insert (node=0x7f609c033a00, index=0x7f609c02aec8, thr=0x7f609c033cd8, referenced=1, mtr=0x7f61000a9da0) at /home/midenok/src/mariadb/10.5b/src/storage/innobase/row/row0upd.cc:2759
#7  0x000000000127aa41 in row_upd_clust_step (node=0x7f609c033a00, thr=0x7f609c033cd8) at /home/midenok/src/mariadb/10.5b/src/storage/innobase/row/row0upd.cc:3210
#8  0x0000000001276b22 in row_upd (node=0x7f609c033a00, thr=0x7f609c033cd8) at /home/midenok/src/mariadb/10.5b/src/storage/innobase/row/row0upd.cc:3289
#9  0x0000000001276635 in row_upd_step (thr=0x7f609c033cd8) at /home/midenok/src/mariadb/10.5b/src/storage/innobase/row/row0upd.cc:3433
#10 0x00000000012048e1 in row_update_for_mysql (prebuilt=0x7f609c032f58) at /home/midenok/src/mariadb/10.5b/src/storage/innobase/row/row0mysql.cc:1889
#11 0x000000000101474e in ha_innobase::update_row (this=0x7f609c02c000, old_row=0x7f609c026150 "\377\001", new_row=0x7f609c026148 "\377\002") at /home/midenok/src/mariadb/10.5b/src/storage/innobase/handler/ha_innodb.cc:8777
#12 0x0000000000bf62aa in handler::ha_update_row (this=0x7f609c02c000, old_data=0x7f609c026150 "\377\001", new_data=0x7f609c026148 "\377\002") at /home/midenok/src/mariadb/10.5b/src/sql/handler.cc:6750
#13 0x000000000097da11 in mysql_update (thd=0x7f609c000d08, table_list=0x7f609c0145f8, fields=..., values=..., conds=0x0, order_num=0, order=0x0, limit=18446744073709551615, ignore=false, found_return=0x7f61000ac118, updated_return=0x7f61000ac110) at /home/midenok/src/mariadb/10.5b/src/sql/sql_update.cc:1046
#14 0x0000000000843e38 in mysql_execute_command (thd=0x7f609c000d08) at /home/midenok/src/mariadb/10.5b/src/sql/sql_parse.cc:4347
#15 0x000000000083b410 in mysql_parse (thd=0x7f609c000d08, rawbuf=0x7f609c014520 "update t1 set fld1= 2", length=21, parser_state=0x7f61000ad5c0, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/10.5b/src/sql/sql_parse.cc:7998

4763    TABLE *find_fk_open_table(THD *thd, const char *db, size_t db_len,
4764                           const char *table, size_t table_len)
4765    {
4766      for (TABLE *t= thd->open_tables; t; t= t->next)
4767      {
4768        if (t->s->db.length == db_len && t->s->table_name.length == table_len &&
4769            !strcmp(t->s->db.str, db) && !strcmp(t->s->table_name.str, table) &&
4770            t->pos_in_table_list->prelocking_placeholder == TABLE_LIST::PRELOCK_FK)
4771          return t;
4772      }
4773      return NULL;
4774    }

[midenok]

Good: foreign table added to list on open

#0  TABLE_LIST::init_one_table_for_prelocking (this=0x7f5e98015160, db_arg=0x7f5e98015050, table_name_arg=0x7f5e98015068, 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=0x7f5e98004b88) at /home/midenok/src/mariadb/10.5/src/sql/table.h:2006
#1  0x00000000007877e8 in DML_prelocking_strategy::handle_table (this=0x7f5f00054768, thd=0x7f5e98000d08, prelocking_ctx=0x7f5e98004b78, table_list=0x7f5e980145c8, need_prelocking=0x7f5f00054366) at /home/midenok/src/mariadb/10.5/src/sql/sql_base.cc:4716
#2  0x0000000000784841 in extend_table_list (thd=0x7f5e98000d08, tables=0x7f5e980145c8, prelocking_strategy=0x7f5f00054768, has_prelocking_list=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_base.cc:3587
#3  0x000000000078676c in open_and_process_table (thd=0x7f5e98000d08, tables=0x7f5e980145c8, counter=0x7f5f000551a4, flags=0, prelocking_strategy=0x7f5f00054768, has_prelocking_list=false, ot_ctx=0x7f5f000546a8) at /home/midenok/src/mariadb/10.5/src/sql/sql_base.cc:3902
#4  0x0000000000784f9b in open_tables (thd=0x7f5e98000d08, options=..., start=0x7f5f000551e8, counter=0x7f5f000551a4, flags=0, prelocking_strategy=0x7f5f00054768) at /home/midenok/src/mariadb/10.5/src/sql/sql_base.cc:4320
#5  0x0000000000778416 in open_tables (thd=0x7f5e98000d08, tables=0x7f5f000551e8, counter=0x7f5f000551a4, flags=0) at /home/midenok/src/mariadb/10.5/src/sql/sql_base.h:474
#6  0x000000000097a74b in mysql_update (thd=0x7f5e98000d08, table_list=0x7f5e980145c8, fields=..., values=..., conds=0x0, order_num=0, order=0x0, limit=18446744073709551615, ignore=false, found_return=0x7f5f00056118, updated_return=0x7f5f00056110) at /home/midenok/src/mariadb/10.5/src/sql/sql_update.cc:399
#7  0x00000000008435b8 in mysql_execute_command (thd=0x7f5e98000d08) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:4347
#8  0x000000000083ab90 in mysql_parse (thd=0x7f5e98000d08, rawbuf=0x7f5e980144f0 "update t1 set fld1= 2", length=21, parser_state=0x7f5f000575c0, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:7998

[midenok]

MDEV-17554: store AUTO parameter

1.
It should be possible to enable/disable auto-creation.
For example, CREATE TABLE ... PARTITION BY SYSTEM_TIME ... PARTITIONS AUTO;
this solves few problems at once:
* a user explicitly tells when auto-creation should work
* you don't need to worry "if this name is already occupied"
* you can define that these partitions can never overflow (for INTERVAL)
* if you know that AUTO partitions never overflow, you can keep the old
  behavior for ALTER TABLE ADD PARTITION.

2.
Separate thread is an interesting solution. Nicely avoids lots of
problems. Still:
* it's asynchronous, all tests have to take it into account
* it's racy. "low probability" or not, with our number of users they'll
  hit it and rightfully will report it as a bug
* if LOCK TABLE prevents it, partitions can overflow

But I think these problems are easier to solve than those we'll face if
auto-creation will happen in the main connection thread.

So, let's keep your approach with a thread.

But instead of going through the parser and mysql_execute_command,
create a function that takes a TABLE or a TABLE_SHARE. And adds a
partition there. It'll fix the "racy" part. This function can be called
from a new thread.

As for the LOCK TABLES - if you're under LOCK TABLES, you can simply
call that function (to add a partition) directly at the end of the main
INSERT/UPDATE/DELETE statement. It'll solve the last problem, LOCK
TABLES won't prevent auto-creation.

A couple of style comments:
1. create a one-liner make_partition_name (or something) with:

    sprintf(move_ptr, "p%u", i)

and use it both in create_default_partition_names() and in your new
code. Just to make sure partition names are always generated uniformly
by the same function and if we'll want to change it to "pp%u" it should
be done only in one place.

2. don't overload operators, use, say,

    bool greater_than(size_t seconds)

But overall it's pretty good. A nice idea with a separate thread.

.par file written on CREATE

#0  inline_mysql_file_write (src_file=0x195012a "/home/midenok/src/mariadb/10.5/src/sql/ha_partition.cc", src_line=2812, file=42, buffer=0x7f8a2c0a5578 "\a", count=28, flags=20) at /home/midenok/src/mariadb/10.5/src/include/mysql/psi/mysql_file.h:1171
#1  0x000000000103b36f in ha_partition::create_handler_file (this=0x7f8a2c0a48b0, name=0x7f8a60117930 "./test/t1") at /home/midenok/src/mariadb/10.5/src/sql/ha_partition.cc:2811
#2  0x000000000103a7df in ha_partition::create_partitioning_metadata (this=0x7f8a2c0a48b0, path=0x7f8a60117930 "./test/t1", old_path=0x0, action_flag=0) at /home/midenok/src/mariadb/10.5/src/sql/ha_partition.cc:661
#3  0x0000000000c6ef5f in handler::ha_create_partitioning_metadata (this=0x7f8a2c0a48b0, name=0x7f8a60117930 "./test/t1", old_name=0x0, action_flag=0) at /home/midenok/src/mariadb/10.5/src/sql/handler.cc:4747
#4  0x00000000009bf071 in create_table_impl (thd=0x7f8a2c000d38, orig_db=..., orig_table_name=..., db=..., table_name=..., path=0x7f8a60117930 "./test/t1", options=..., create_info=0x7f8a60117f68, alter_info=0x7f8a60117e80, create_table_mode=0, is_trans=0x7f8a60117c77, key_info=0x7f8a60117b40, key_count=0x7f8a60117b3c, frm=0x7f8a60117920) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:5079
#5  0x00000000009be268 in mysql_create_table_no_lock (thd=0x7f8a2c000d38, db=0x7f8a2c0a8248, table_name=0x7f8a2c0a8258, create_info=0x7f8a60117f68, alter_info=0x7f8a60117e80, is_trans=0x7f8a60117c77, create_table_mode=0, table_list=0x7f8a2c0a8230) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:5168
#6  0x00000000009bf680 in mysql_create_table (thd=0x7f8a2c000d38, create_table=0x7f8a2c0a8230, create_info=0x7f8a60117f68, alter_info=0x7f8a60117e80) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:5260
#7  0x00000000009d506d in Sql_cmd_create_table_like::execute (this=0x7f8a2c0a7ee0, thd=0x7f8a2c000d38) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:11531
#8  0x00000000008c017a in mysql_execute_command (thd=0x7f8a2c000d38) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:5878
#9  0x00000000008b1176 in mysql_parse (thd=0x7f8a2c000d38, rawbuf=0x7f8a2c013520 "create table t1(id int, d date not null, b bool not null default 0, primary key(id,d))\nengine=innodb\npartition by range columns (d) (\npartition p1 values less than ('2016-10-18'),\npartition p2 values less than ('2020-10-19'))", length=225, parser_state=0x7f8a6011a5c0, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:7918

[midenok]

Bug: assertion on replication slave

Reproduce

mtrv rpl,heap,stmt

Does not reproduce on RBR.

--echo #
--echo # MDEV-17554 Auto-create new partition for system versioned tables with history partitioned by INTERVAL/LIMIT
--echo #
set timestamp= unix_timestamp('2000-01-01 00:00:00');
create or replace table t1 (x int) with system versioning
partition by system_time interval 1 hour auto_increment;
insert t1 values ();
set timestamp= unix_timestamp('2000-01-01 01:00:00');
delete from t1;
--replace_result InnoDB ENGINE MyISAM ENGINE MEMORY ENGINE
show create table t1;
--sync_slave_with_master
--replace_result InnoDB ENGINE MyISAM ENGINE MEMORY ENGINE
show create table t1;
--connection master

Result

#3  0x00007f6df99bd006 in __GI___assert_fail (assertion=0x17bbb2c "thd->m_transaction_psi == __null", file=0x17bb64d "/home/midenok/src/mariadb/10.5/src/sql/transaction.cc", line=324, function=0x17bb964 "bool trans_commit_implicit(THD *)") at assert.c:101
#4  0x0000000000ab59b8 in trans_commit_implicit (thd=0x7f6d68001848) at /home/midenok/src/mariadb/10.5/src/sql/transaction.cc:324
#5  0x0000000000c93e55 in ha_enable_transaction (thd=0x7f6d68001848, on=true) at /home/midenok/src/mariadb/10.5/src/sql/handler.cc:4977
#6  0x00000000009eb146 in mysql_trans_commit_alter_copy_data (thd=0x7f6d68001848) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:10669
#7  0x0000000000eceadd in mysql_change_partitions (lpt=0x7f6dec1a5df0) at /home/midenok/src/mariadb/10.5/src/sql/sql_partition.cc:6010
#8  0x0000000000ecd37f in fast_alter_partition_table (thd=0x7f6d68001848, table=0x7f6d68976ba8, alter_info=0x7f6dec1a6c08, create_info=0x7f6dec1a6cd8, table_list=0x7f6d6800e0a8, db=0x7f6d6891eaa8, table_name=0x7f6d6891eab8) at /home/midenok/src/mariadb/10.5/src/sql/sql_partition.cc:7272
#9  0x0000000000a6a2c7 in vers_add_auto_parts (thd=0x7f6d68001848) at /home/midenok/src/mariadb/10.5/src/sql/partition_info.cc:1068
#10 0x00000000008d8128 in mysql_execute_command (thd=0x7f6d68001848) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:5962
#11 0x00000000008c8c16 in mysql_parse (thd=0x7f6d68001848, rawbuf=0x7f6d6802e743 "insert t1 values ()", length=19, parser_state=0x7f6dec1a8828, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:7937

324       DBUG_ASSERT(thd->m_transaction_psi == NULL);

1. thd->m_transaction_psi set not-null

#0  0x0000000000ab4c89 in trans_begin (thd=0x7f6d68001848, flags=0) at /home/midenok/src/mariadb/10.5/src/sql/transaction.cc:225
#1  0x0000000000e2c561 in Gtid_log_event::do_apply_event (this=0x7f6d6802bd28, rgi=0x7f6d68000d30) at /home/midenok/src/mariadb/10.5/src/sql/log_event_server.cc:3437
#2  0x00000000007a1bf0 in Log_event::apply_event (this=0x7f6d6802bd28, rgi=0x7f6d68000d30) at /home/midenok/src/mariadb/10.5/src/sql/log_event.h:1490
#3  0x0000000000790281 in apply_event_and_update_pos_apply (ev=0x7f6d6802bd28, thd=0x7f6d68001848, rgi=0x7f6d68000d30, reason=0) at /home/midenok/src/mariadb/10.5/src/sql/slave.cc:3997
#4  0x000000000078fe5d in apply_event_and_update_pos (ev=0x7f6d6802bd28, thd=0x7f6d68001848, rgi=0x7f6d68000d30) at /home/midenok/src/mariadb/10.5/src/sql/slave.cc:4158
#5  0x000000000079a39b in exec_relay_log_event (thd=0x7f6d68001848, rli=0x403f5f8, serial_rgi=0x7f6d68000d30) at /home/midenok/src/mariadb/10.5/src/sql/slave.cc:4479
#6  0x0000000000786e2a in handle_slave_sql (arg=0x403d930) at /home/midenok/src/mariadb/10.5/src/sql/slave.cc:5651

frame 1

(rr) p *this
$2 = {
  <Log_event> = {
    _vptr$Log_event = 0x1f48b50 <vtable for Gtid_log_event+16>,
    log_pos = 6003,
    ...
  },
  members of Gtid_log_event:
  seq_no = 30,
  ...

binlog master-bin.000001

# at 5961
#000101  0:00:00 server id 1  end_log_pos 6003 CRC32 0x80669974         GTID 0-1-30
/*!100001 SET @@session.gtid_seq_no=30*//*!*/;
BEGIN
/*!*/;
# at 6003
#000101  0:00:00 server id 1  end_log_pos 6093 CRC32 0xc715f331         Query   thread_id=8     exec_time=638536320      error_code=0
SET TIMESTAMP=946674000/*!*/;
insert t1 values ()
/*!*/;

2. fast_alter_partition_table() does commit

#4  0x0000000000ab59b8 in trans_commit_implicit (thd=0x7f6d68001848) at /home/midenok/src/mariadb/10.5/src/sql/transaction.cc:324
#5  0x0000000000c93e55 in ha_enable_transaction (thd=0x7f6d68001848, on=true) at /home/midenok/src/mariadb/10.5/src/sql/handler.cc:4977
#6  0x00000000009eb146 in mysql_trans_commit_alter_copy_data (thd=0x7f6d68001848) at /home/midenok/src/mariadb/10.5/src/sql/sql_table.cc:10669
#7  0x0000000000eceadd in mysql_change_partitions (lpt=0x7f6dec1a5df0) at /home/midenok/src/mariadb/10.5/src/sql/sql_partition.cc:6010
#8  0x0000000000ecd37f in fast_alter_partition_table (thd=0x7f6d68001848, table=0x7f6d68976ba8, alter_info=0x7f6dec1a6c08, create_info=0x7f6dec1a6cd8, table_list=0x7f6d6800e0a8, db=0x7f6d6891eaa8, table_name=0x7f6d6891eab8) at /home/midenok/src/mariadb/10.5/src/sql/sql_partition.cc:7272
#9  0x0000000000a6a2c7 in vers_add_auto_parts (thd=0x7f6d68001848) at /home/midenok/src/mariadb/10.5/src/sql/partition_info.cc:1068
#10 0x00000000008d8128 in mysql_execute_command (thd=0x7f6d68001848) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:5962
#11 0x00000000008c8c16 in mysql_parse (thd=0x7f6d68001848, rawbuf=0x7f6d6802e743 "insert t1 values ()", length=19, parser_state=0x7f6dec1a8828, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:7937

frame 6

10654   /**
10655     Commit the copy phase of the alter table.
10656   */
10657
10658   bool mysql_trans_commit_alter_copy_data(THD *thd)
10659   {
10660     bool error= FALSE;
10661     uint save_unsafe_rollback_flags;
10662     DBUG_ENTER("mysql_trans_commit_alter_copy_data");
10663
10664     /* Save flags as trans_commit_implicit are deleting them */
10665     save_unsafe_rollback_flags= thd->transaction.stmt.m_unsafe_rollback_flags;
10666
10667     DEBUG_SYNC(thd, "alter_table_copy_trans_commit");
10668
10669     if (ha_enable_transaction(thd, TRUE))
10670       DBUG_RETURN(TRUE);
10671
10672     /*
10673       Ensure that the new table is saved properly to disk before installing
10674       the new .frm.
10675       And that InnoDB's internal latches are released, to avoid deadlock
10676       when waiting on other instances of the table before rename (Bug#54747).
10677     */
10678     if (trans_commit_stmt(thd))
10679       error= TRUE;
10680     if (trans_commit_implicit(thd))
10681       error= TRUE;
10682
10683     thd->transaction.stmt.m_unsafe_rollback_flags= save_unsafe_rollback_flags;
10684     DBUG_RETURN(error);
10685   }

Cause

Slave implicit BEGIN starts transaction, but mysql_trans_commit_alter_copy_data() doesn't expect it.

[midenok]

Good: thd->m_transaction_psi set NULL

#0  ha_commit_trans (thd=0x7f5f78000d48, all=true) at /home/midenok/src/mariadb/10.5/src/sql/handler.cc:1553
#1  0x0000000000ab58da in trans_commit_implicit (thd=0x7f5f78000d48) at /home/midenok/src/mariadb/10.5/src/sql/transaction.cc:317
#2  0x00000000008cf99f in mysql_execute_command (thd=0x7f5f78000d48) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:3710
#3  0x00000000008c8c16 in mysql_parse (thd=0x7f5f78000d48, rawbuf=0x7f5f78014880 "create or replace table t1 (x int) with system versioning\npartition by system_time interval 1 hour auto_increment", length=113, parser_state=0x7f5fa41f3580, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/10.5/src/sql/sql_parse.cc:7937

frame 2

3707        if (!(thd->variables.option_bits & OPTION_GTID_BEGIN))
3708        {
3709          /* Commit the normal transaction if one is active. */
3710          bool commit_failed= trans_commit_implicit(thd);

Related to 5426facdcbfba2d78dab3c709cbf278073383b7c:

commit 5426facdcbfba2d78dab3c709cbf278073383b7c
Author: Michael Widenius <monty@askmonty.org>
Date:   Wed Feb 5 19:01:59 2014 +0200

    Replication changes for CREATE OR REPLACE TABLE
    - CREATE TABLE is by default executed on the slave as CREATE OR REPLACE
    - DROP TABLE is by default executed on the slave as DROP TABLE IF NOT EXISTS

    This means that a slave will by default continue even if we try to create
    a table that existed on the slave (the table will be deleted and re-created) or
    if we try to drop a table that didn't exist on the slave.
    This should be safe as instead of having the slave stop because of an inconsistency between
    master and slave, it will fix the inconsistency.
    Those that would prefer to get a stopped slave instead for the above cases can set slave_ddl_exec_mode to STRICT.

    - Ensure that a CREATE OR REPLACE TABLE which dropped a table is replicated
    - DROP TABLE that generated an error on master is handled as an identical DROP TABLE on the slave (IF NOT EXISTS is not added in this case)
    - Added slave_ddl_exec_mode variable to decide how DDL's are replicated

    New logic for handling BEGIN GTID ... COMMIT from the binary log:

    - When we find a BEGIN GTID, we start a transaction and set OPTION_GTID_BEGIN
    - When we find COMMIT, we reset OPTION_GTID_BEGIN and execute the normal COMMIT code.
    - While OPTION_GTID_BEGIN is set:
      - We don't generate implict commits before or after statements
      - All tables are regarded as transactional tables in the binary log (to ensure things are executed exactly as on the master)
    - We reset OPTION_GTID_BEGIN also on rollback

    This will help ensuring that we don't get any sporadic commits (and thus new GTID's) on the slave and will help keep the GTID's between master and slave in sync.

Fix

copy_data switch for mysql_change_partitions() which turns off mysql_trans_prepare_alter_copy_data()/mysql_trans_commit_alter_copy_data() when no copy data expected.

[midenok]

Bug: wrong info (affected rows) on DML

Reproduce

--source include/have_partition.inc

--enable_info
create or replace table t1 (a int) with system versioning partition by system_time limit 1000;
insert into t1 select 1;
update t1 set a= a + 1;

create or replace table t1 (a int) with system versioning partition by system_time limit 1000 auto;
insert into t1 select 1;
update t1 set a= a + 1;

drop tables t1;
--disable_info

Result

create or replace table t1 (a int) with system versioning partition by system_time limit 1000;
affected rows: 0
insert into t1 select 1;
affected rows: 1
info: Records: 1  Duplicates: 0  Warnings: 0
update t1 set a= a + 1;
affected rows: 1
info: Rows matched: 1  Changed: 1  Inserted: 1  Warnings: 0
create or replace table t1 (a int) with system versioning partition by system_time limit 1000 auto;
affected rows: 0
insert into t1 select 1;
affected rows: 1
info: Records: 1  Duplicates: 0  Warnings: 0
update t1 set a= a + 1;
affected rows: 0
info: Records: 0  Duplicates: 0  Warnings: 0
drop tables t1;
affected rows: 0

Expected

Identical info with and without auto keyword.

Fix

DML stats is stored in statement DA. Use backup DA instead of main one.

[midenok]

Rework via table discovery

Info: https://mariadb.com/kb/en/table-discovery/

This allows to do DDL before statment.

[midenok]

Pruning case

set timestamp= unix_timestamp('2000-01-01 00:00:00');
create or replace table t1 (x int) with system versioning
partition by system_time interval 1 hour partitions 5;

insert into t1 values (0); delete from t1;

set timestamp= unix_timestamp('2000-01-01 01:00:00');
insert into t1 values (1); delete from t1;

set timestamp= unix_timestamp('2000-01-01 02:00:00');
insert into t1 values (2); delete from t1;

set timestamp= unix_timestamp('2000-01-01 03:00:00');
insert into t1 values (3); delete from t1;

select * from t1 for system_time as of '2000-01-01 02:00:00';
select * from t1 for system_time from '2000-01-01 01:00:00' to '2000-01-01 02:00:00';
--replace_column 10 #
explain partitions select * from t1 for system_time as of '2000-01-01 02:00:00';
--replace_column 10 #
explain partitions select * from t1 for system_time from '2000-01-01 01:00:00' to '2000-01-01 02:00:00';

History partition overflow leads to wrong SELECT result

Reproduce

-- source include/have_partition.inc

set timestamp= unix_timestamp('2000-01-01 00:00:00');
create or replace table t1 (x int) with system versioning
partition by system_time interval 1 hour;

insert into t1 values (0);
update t1 set x= x + 1;

set timestamp= unix_timestamp('2000-01-01 01:00:01');
update t1 set x= x + 1;

select *, row_start, row_end from t1 for system_time as of '2000-01-01 01:00:00';
--replace_column 10 #
explain partitions select * from t1 for system_time as of '2000-01-01 01:00:00';

drop tables t1;

Result

set timestamp= unix_timestamp('2000-01-01 00:00:00');
create or replace table t1 (x int) with system versioning
partition by system_time interval 1 hour;
insert into t1 values (0);
update t1 set x= x + 1;
set timestamp= unix_timestamp('2000-01-01 01:00:01');
update t1 set x= x + 1;
Warnings:
Warning 4114    Versioned table `test`.`t1`: last HISTORY partition (`p0`) is out of INTERVAL, need more HISTORY partitions
select *, row_start, row_end from t1 for system_time as of '2000-01-01 01:00:00';
x       row_start       row_end
explain partitions select * from t1 for system_time as of '2000-01-01 01:00:00';
id      select_type     table   partitions      type    possible_keys   key     key_len ref     rows    Extra
1       SIMPLE  NULL    NULL    NULL    NULL    NULL    NULL    NULL    #       Impossible WHERE noticed after reading const tables
drop tables t1;

Expected

SELECT displays "1" record. Compare with result from 3 partitions:

set timestamp= unix_timestamp('2000-01-01 00:00:00');
create or replace table t1 (x int) with system versioning
partition by system_time interval 1 hour partitions 3;
insert into t1 values (0);
update t1 set x= x + 1;
set timestamp= unix_timestamp('2000-01-01 01:00:01');
update t1 set x= x + 1;
select *, row_start, row_end from t1 for system_time as of '2000-01-01 01:00:00';
x       row_start       row_end
1       2000-01-01 00:00:00.000000      2000-01-01 01:00:01.000000
explain partitions select * from t1 for system_time as of '2000-01-01 01:00:00';
id      select_type     table   partitions      type    possible_keys   key     key_len ref     rows    Extra
1       SIMPLE  t1      p1,pn   ALL     NULL    NULL    NULL    NULL    #       Using where
drop tables t1;

Proposed fix

When overflow warning occurs touch file t1.no_pruning. When share open sees that file it sets no_pruning property to TABLE_SHARE. Pruning sees that property it turns off and issues warning that pruning is off. After partitioning rebuild this file is removed.

[midenok]

Bug: MENT-686 Partitioning auto-creation does not work if DML is executed from trigger

Related to 3104af49cdd1a3c041948b0da17558c49c3edb49, d9227f159fccfc5167920f48297d5a7ac24a7a1b (WL#3726)

Reproduce

--source include/have_partition.inc
--source include/have_sequence.inc

create table t1 (a int) with system versioning partition by system_time limit 1000 auto;
insert into t1 select seq from seq_1_to_3000;

create table t2 (b int);
create trigger tr after insert on t2 for each row delete from t1 limit 1000;

insert into t2 values (1);
select partition_name, table_rows from information_schema.partitions where table_name = 't1';

insert into t2 values (2);
select partition_name, table_rows from information_schema.partitions where table_name = 't1';

# cleanup
drop tables t1, t2;

Cause

For locked_tables_mode we can't open new TABLE instance:

  /*
    If we're in pre-locked or LOCK TABLES mode, let's try to find the
    requested table in the list of pre-opened and locked tables. If the
    table is not there, return an error - we can't open not pre-opened
    tables in pre-locked/LOCK TABLES mode.
    TODO: move this block into a separate function.
  */
  if (thd->locked_tables_mode && 
      ! (flags & MYSQL_OPEN_GET_NEW_TABLE))
  {                     // Using table locks

F.ex. if we allow this see what happens (main.lock_sync):

#3  0x00007f135d232006 in __GI___assert_fail (assertion=0x190f9eb "table_share->tmp_table != NO_TMP_TABLE || m_lock_type != 2", file=0x190e92c "/home/midenok/src/mariadb/10.5b/src/sql/handler.cc", line=3059, function=0x190fb0b "int handler::ha_index_read_idx_map(uchar *, uint, const uchar *, key_part_map, enum ha_rkey_function)") at assert.c:101
#4  0x0000000000c90b32 in handler::ha_index_read_idx_map (this=0x7f1318099aa0, buf=0x7f1318095568 "\377", index=0, key=0x7f131c142e28 "\001", keypart_map=1, find_flag=HA_READ_KEY_EXACT) at /home/midenok/src/mariadb/10.5b/src/sql/handler.cc:3058
#5  0x0000000000978736 in join_read_const (tab=0x7f131c141bc0) at /home/midenok/src/mariadb/10.5b/src/sql/sql_select.cc:21183
#6  0x0000000000977862 in join_read_const_table (thd=0x7f131c000d88, tab=0x7f131c141bc0, pos=0x7f131c142170) at /home/midenok/src/mariadb/10.5b/src/sql/sql_select.cc:21061
#7  0x0000000000931266 in make_join_statistics (join=0x7f131c1412d0, tables_list=..., keyuse_array=0x7f131c1415c0) at /home/midenok/src/mariadb/10.5b/src/sql/sql_select.cc:5253
#8  0x000000000092b989 in JOIN::optimize_inner (this=0x7f131c1412d0) at /home/midenok/src/mariadb/10.5b/src/sql/sql_select.cc:2260
#9  0x00000000009261d6 in JOIN::optimize (this=0x7f131c1412d0) at /home/midenok/src/mariadb/10.5b/src/sql/sql_select.cc:1606

ha_index_read_idx_map() expects table locked.

We also can't limit this for read-locks only. Because table might be prelocked with write-locking and reopened with read lock which fails further queries:

CURRENT_TEST: main.lock_sync
mysqltest: In included file "./include/check_no_concurrent_insert.inc":
included from /home/midenok/src/mariadb/10.5b/src/mysql-test/main/lock_sync.test at line 555:
At line 55: query 'reap' failed: 1099: Table 't2' was locked with a READ lock and can't be updated

[midenok]

Tests affected by recover_from_failed_open()

main.mdl_sync main.mysql_upgrade-20228 main.myisam_recover main.tls_version1 main.tls_version main.multi_update_debug main.delayed main.sp_sync

mdl_sync

--echo #
--echo # Test that in situation when MDL subsystem detects a deadlock
--echo # but it turns out that it can be resolved by backing-off locks
--echo # acquired by one of participating transactions (which is
--echo # possible when one of transactions consists only of currently
--echo # executed statement, e.g. in autocommit mode) no error is
--echo # reported.
--echo #
create table t1 (i int);
create table t2 (j int);
--echo # Ensure that the below SELECT stops once it has acquired metadata
--echo # lock on table 't2'.
set debug_sync= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish';
--echo # Sending:
--send select * from t2, t1

--echo #
connection deadlock_con1;
--echo # Wait till SELECT acquires MDL on 't2' and starts waiting for signal.
set debug_sync= 'now WAIT_FOR locked';
--echo # Sending:
--send lock tables t1 write, t2 write

--echo #
connection deadlock_con2;
--echo # Wait until LOCK TABLES acquires SNRW lock on 't1' and is blocked
--echo # while trying to acquire SNRW lock on 't1'.
let $wait_condition=
  select count(*) = 1 from information_schema.processlist
  where state = "Waiting for table metadata lock" and
        info = "lock tables t1 write, t2 write";
--source include/wait_condition.inc
--echo # Resume SELECT execution, this should eventually unblock LOCK TABLES.
set debug_sync= 'now SIGNAL finish';

--echo #
connection deadlock_con1;
--echo # Reaping LOCK TABLES.
--reap
unlock tables;

--echo #
connection default;
--echo # Reaping SELECT. It succeed and not report ER_LOCK_DEADLOCK error.
--reap

#8  0x000000000080abb6 in Open_table_context::recover_from_failed_open (this=0x7fe73407dab8) at /home/midenok/src/mariadb/10.5b/src/sql/sql_base.cc:3148
#9  0x000000000080d0ee in open_tables (thd=0x7fe704000d48, options=..., start=0x7fe73407dbd0, counter=0x7fe73407dbbc, flags=0, prelocking_strategy=0x7fe73407dc30) at /home/midenok/src/mariadb/10.5b/src/sql/sql_base.cc:4317
#10 0x0000000000811115 in open_and_lock_tables (thd=0x7fe704000d48, options=..., tables=0x7fe704012ac8, derived=true, flags=0, prelocking_strategy=0x7fe73407dc30) at /home/midenok/src/mariadb/10.5b/src/sql/sql_base.cc:5183
#11 0x00000000007b8cec in open_and_lock_tables (thd=0x7fe704000d48, tables=0x7fe704012ac8, derived=true, flags=0) at /home/midenok/src/mariadb/10.5b/src/sql/sql_base.h:508
#12 0x00000000008da471 in execute_sqlcom_select (thd=0x7fe704000d48, all_tables=0x7fe704012ac8) at /home/midenok/src/mariadb/10.5b/src/sql/sql_parse.cc:6089
#13 0x00000000008d08d5 in mysql_execute_command (thd=0x7fe704000d48) at /home/midenok/src/mariadb/10.5b/src/sql/sql_parse.cc:3902
#14 0x00000000008c9546 in mysql_parse (thd=0x7fe704000d48, rawbuf=0x7fe704012440 "select * from t2, t1", length=20, parser_state=0x7fe734080580, is_com_multi=false, is_next_command=false) at /home/midenok/src/mariadb/10.5b/src/sql/sql_parse.cc:7957

Test affected by recovery

mtrz main.myisam_recover main.delayed

main.myisam_recover

mysqltest: At line 99: query 'select * from t1_mrg' failed: 2013: Lost connection to MySQL server during query

#3  0x00007f09274bd006 in __GI___assert_fail (assertion=0x192e58c "0", file=0x17857b8 "/home/midenok/src/mariadb/10.5b/src/sql/sql_base.cc", line=3203, function=0x1786363 "bool Open_table_context::recover_from_failed_open()") at assert.c:101
#4  0x000000000080aea5 in Open_table_context::recover_from_failed_open (this=0x7f0921608ab8) at /home/midenok/src/mariadb/10.5b/src/sql/sql_base.cc:3203
#5  0x000000000080d0fe in open_tables (thd=0x7f0910000d48, options=..., start=0x7f0921608bd0, counter=0x7f0921608bbc, flags=0, prelocking_strategy=0x7f0921608c30) at /home/midenok/src/mariadb/10.5b/src/sql/sql_base.cc:4317
#6  0x0000000000811125 in open_and_lock_tables (thd=0x7f0910000d48, options=..., tables=0x7f0910014f18, derived=true, flags=0, prelocking_strategy=0x7f0921608c30) at /home/midenok/src/mariadb/10.5b/src/sql/sql_base.cc:5183
#7  0x00000000007b8cec in open_and_lock_tables (thd=0x7f0910000d48, tables=0x7f0910014f18, derived=true, flags=0) at /home/midenok/src/mariadb/10.5b/src/sql/sql_base.h:508
#8  0x00000000008da481 in execute_sqlcom_select (thd=0x7f0910000d48, all_tables=0x7f0910014f18) at /home/midenok/src/mariadb/10.5b/src/sql/sql_parse.cc:6089
#9  0x00000000008d08e5 in mysql_execute_command (thd=0x7f0910000d48) at /home/midenok/src/mariadb/10.5b/src/sql/sql_parse.cc:3902
#10 0x00000000008c9556 in mysql_parse (thd=0x7f0910000d48, rawbuf=0x7f0910014890 "select * from t1_mrg", length=20, parser_state=0x7f092160b580, is_com_multi=false, is_next_command=false) at /home/midenok/src/m

[midenok]

Bug: multi-update locks all tables for WRITE

Info: Table locking changed to READ for multi-update

#0  0x0000000000a44ea4 in TABLE_LIST::set_lock_type (this=0x7fe76c9f8ef8, thd=0x7fe76c000d48, lock=TL_READ) at /home/midenok/src/mariadb/10.5b/src/sql/table.cc:9225
#1  0x0000000000a14560 in Multiupdate_prelocking_strategy::handle_end (this=0x7fe7a410d9f8, thd=0x7fe76c000d48) at /home/midenok/src/mariadb/10.5b/src/sql/sql_update.cc:1771
#2  0x000000000080d4c9 in open_tables (thd=0x7fe76c000d48, options=..., start=0x7fe7a410da10, counter=0x7fe7a410d9f4, flags=512, prelocking_strategy=0x7fe7a410d9f8) at /home/midenok/src/mariadb/10.5b/src/sql/sql_base.cc:4399
#3  0x0000000000a15475 in open_tables (thd=0x7fe76c000d48, tables=0x7fe7a410da10, counter=0x7fe7a410d9f4, flags=512, prelocking_strategy=0x7fe7a410d9f8) at /home/midenok/src/mariadb/10.5b/src/sql/sql_base.h:263
#4  0x0000000000a14fc9 in mysql_multi_update_prepare (thd=0x7fe76c000d48) at /home/midenok/src/mariadb/10.5b/src/sql/sql_update.cc:1837
#5  0x00000000009061b5 in select_like_stmt_test (stmt=0x7fe7a410e150, specific_prepare=0xa14f10 <mysql_multi_update_prepare(THD*)>, setup_tables_done_option=1073741824) at /home/midenok/src/mariadb/10.5b/src/sql/sql_prepare.cc:1712
#6  0x0000000000903ba2 in mysql_test_multiupdate (stmt=0x7fe7a410e150, tables=0x7fe76c9f87d8, converted=false) at /home/midenok/src/mariadb/10.5b/src/sql/sql_prepare.cc:2089
#7  0x00000000008fe70e in check_prepared_statement (stmt=0x7fe7a410e150) at /home/midenok/src/mariadb/10.5b/src/sql/sql_prepare.cc:2338
#8  0x00000000008f8575 in Prepared_statement::prepare (this=0x7fe7a410e150, packet=0x7fe76c9ebda0 "update t1, t2 set t1.x= 0 where t1.x< t2.y", packet_len=42) at /home/midenok/src/mariadb/10.5b/src/sql/sql_prepare.cc:4075
#9  0x0000000000900150 in Prepared_statement::reprepare (this=0x7fe76c01ea68) at /home/midenok/src/mariadb/10.5b/src/sql/sql_prepare.cc:4534
#10 0x00000000008fb403 in Prepared_statement::execute_loop (this=0x7fe76c01ea68, expanded_query=0x7fe7a410e740, open_cursor=false, packet=0x7fe76c007d42 "", packet_end=0x7fe76c007d42 "") at /home/midenok/src/mariadb/10.5b/src/sql/sql_prepare.cc:4288
#11 0x00000000008fa739 in mysql_stmt_execute_common (thd=0x7fe76c000d48, stmt_id=12, packet=0x7fe76c007d42 "", packet_end=0x7fe76c007d42 "", cursor_flags=0, bulk_op=false, read_types=false) at /home/midenok/src/mariadb/10.5b/src/sql/sql_prepare.cc:3277
#12 0x00000000008fa2ca in mysqld_stmt_execute (thd=0x7fe76c000d48, packet_arg=0x7fe76c007d39 "\f", packet_length=9) at /home/midenok/src/mariadb/10.5b/src/sql/sql_prepare.cc:3172