Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-19250

Alter table results in foreign key error and further Alter causes crash

    Details

      Description

      I've tried to play with test case from upstream https://bugs.mysql.com/bug.php?id=94816 on MariaDB 10.3.x:

      MariaDB [test]> select version();
      +-----------------+
      | version()       |
      +-----------------+
      | 10.3.14-MariaDB |
      +-----------------+
      1 row in set (0,000 sec)
       
      MariaDB [test]> create table a (
          -> id int unsigned not null,
          ->
          -> constraint PK_a primary key (id)
          -> ) engine=InnoDB;
      Query OK, 0 rows affected (0,244 sec)
       
      MariaDB [test]> create table b (
          -> id int unsigned not null,
          ->
          -> constraint PK_b primary key (id)
          -> ) engine=InnoDB;
      Query OK, 0 rows affected (0,254 sec)
       
      MariaDB [test]> create table src (
          -> a_id int unsigned not null,
          -> b_id int unsigned not null,
          ->
          -> constraint PK_src primary key (a_id),
          -> constraint FK_src_a foreign key (a_id) references a (id),
          -> constraint FK_src_b foreign key (b_id) references b (id)
          -> ) engine=InnoDB;
      Query OK, 0 rows affected (0,229 sec)
       
      MariaDB [test]> create table ref (
          -> a_id int unsigned not null,
          -> b_id int unsigned not null,
          ->
          -> constraint FK_ref_a_b foreign key (b_id,a_id) references src (b_id,a_id)
          -> ) engine=InnoDB;
      Query OK, 0 rows affected (0,252 sec)
       
      MariaDB [test]> alter table src
          -> add constraint UQ_src_a unique (a_id),
          -> drop primary key,
          -> add column id int unsigned not null,
          -> add constraint PK_src primary key (id)
          -> ;
      Query OK, 0 rows affected, 1 warning (0,851 sec)
      Records: 0  Duplicates: 0  Warnings: 1
       
      MariaDB [test]> show warnings\G
      *************************** 1. row ***************************
        Level: Warning
         Code: 1088
      Message: failed to load FOREIGN KEY constraints
      1 row in set (0,000 sec)
      

      Here we have a different behavior both comparing to MySQL 8.0 (that gives error upon creation of table ref already) and buggy MySQL 5.7.x (that gives warning, error and makes src table disappear on ALTER). In MariaDB 10.3.x we have both tables created:

      MariaDB [test]> show engine innodb status\G
      *************************** 1. row ***************************
        Type: InnoDB
        Name:
      Status:
      =====================================
      2019-04-14 16:47:33 0x7f877c363700 INNODB MONITOR OUTPUT
      =====================================
      Per second averages calculated from the last 40 seconds
      -----------------
      BACKGROUND THREAD
      -----------------
      srv_master_thread loops: 10 srv_active, 0 srv_shutdown, 678 srv_idle
      srv_master_thread log flush and writes: 688
      ----------
      SEMAPHORES
      ----------
      OS WAIT ARRAY INFO: reservation count 155
      OS WAIT ARRAY INFO: signal count 101
      RW-shared spins 0, rounds 119, OS waits 50
      RW-excl spins 0, rounds 126, OS waits 5
      RW-sx spins 0, rounds 0, OS waits 0
      Spin rounds per wait: 119.00 RW-shared, 126.00 RW-excl, 0.00 RW-sx
      ------------------------
      LATEST FOREIGN KEY ERROR
      ------------------------
      2019-04-14 16:43:03 0x7f877c363700 Error in foreign key constraint of table test/ref:
      there is no index in referenced table which would contain
      the columns as the first columns, or the data types in the
      referenced table do not match the ones in table. Constraint:
      ,
        CONSTRAINT `FK_ref_a_b` FOREIGN KEY (`b_id`, `a_id`) REFERENCES `src` (`b_id`, `a_id`)
      The index in the foreign key in table is FK_ref_a_b
      Please refer to https://mariadb.com/kb/en/library/foreign-keys/ for correct foreign key definition.
      ...
      MariaDB [test]> show create table src\G
      *************************** 1. row ***************************
             Table: src
      Create Table: CREATE TABLE `src` (
        `a_id` int(10) unsigned NOT NULL,
        `b_id` int(10) unsigned NOT NULL,
        `id` int(10) unsigned NOT NULL,
        PRIMARY KEY (`id`),
        UNIQUE KEY `UQ_src_a` (`a_id`),
        KEY `FK_src_b` (`b_id`),
        CONSTRAINT `FK_src_a` FOREIGN KEY (`a_id`) REFERENCES `a` (`id`),
        CONSTRAINT `FK_src_b` FOREIGN KEY (`b_id`) REFERENCES `b` (`id`)
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1
      1 row in set (0,000 sec)
       
      MariaDB [test]> show create table ref\G
      *************************** 1. row ***************************
             Table: ref
      Create Table: CREATE TABLE `ref` (
        `a_id` int(10) unsigned NOT NULL,
        `b_id` int(10) unsigned NOT NULL,
        KEY `FK_ref_a_b` (`b_id`,`a_id`),
        CONSTRAINT `FK_ref_a_b` FOREIGN KEY (`b_id`, `a_id`) REFERENCES `src` (`b_id`, `a_id`)
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1
      1 row in set (0,001 sec)
      

      But as soon as I try next ALTER to add generated column I get a crash:

      MariaDB [test]> alter table src add column other int unsigned generated always as (1);
      ERROR 2013 (HY000): Lost connection to MySQL server during query
      

      with the following stack trace:

      openxs@ao756:~/dbs/maria10.3$ tail -80 data/ao756.err
      stack_bottom = 0x7f877c362e98 thread_stack 0x49000
      /home/openxs/dbs/maria10.3/bin/mysqld(my_print_stacktrace+0x29)[0x7f878e0cc539]
      mysys/stacktrace.c:270(my_print_stacktrace)[0x7f878dc05b4f]
      /lib/x86_64-linux-gnu/libpthread.so.0(+0x10330)[0x7f878b9b3330]
      /home/openxs/dbs/maria10.3/bin/mysqld(+0x8eadae)[0x7f878dd97dae]
      handler/handler0alter.cc:9975(ha_innobase::commit_inplace_alter_table(TABLE*, Alter_inplace_info*, bool))[0x7f878d96585b]
      sql/sql_table.cc:7593(mysql_inplace_alter_table)[0x7f878daca6c4]
      sql/sql_table.cc:9760(mysql_alter_table(THD*, st_mysql_const_lex_string const*, st_mysql_const_lex_string const*, HA_CREATE_INFO*, TABLE_LIST*, Alter_info*, unsigned int, st_order*, bool))[0x7f878db16a8f]
      sql/sql_alter.cc:494(Sql_cmd_alter_table::execute(THD*))[0x7f878da431e8]
      sql/sql_parse.cc:6283(mysql_execute_command(THD*))[0x7f878da4a761]
      sql/sql_parse.cc:8089(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x7f878da4d22f]
      sql/sql_parse.cc:1857(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x7f878da4e07e]
      sql/sql_parse.cc:1405(do_command(THD*))[0x7f878db1410c]
      sql/sql_connect.cc:1402(do_handle_one_connection(CONNECT*))[0x7f878db14224]
      nptl/pthread_create.c:312(start_thread)[0x7f878b9ab184]
      /lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7f878aeb803d]
       
      Trying to get some variables.
      Some pointers may be invalid and cause the dump to abort.
      Query (0x7f8734013750): alter table src add column other int unsigned generated always as (1)
      Connection ID (thread ID): 8
      Status: NOT_KILLED
      ...
      2019-04-14 16:52:59 8 [Warning] InnoDB: Load table `test`.`ref` failed, the table has missing foreign key indexes. Turn off 'foreign_key_checks' and try again.
      2019-04-14 16:52:59 8 [Warning] InnoDB: Table test/src contains 4 user defined columns in InnoDB, but 3 columns in MariaDB. Please check INFORMATION_SCHEMA.INNODB_SYS_COLUMNS and http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
      

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              valerii Valerii Kravchuk
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated: