|
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.
|
|