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

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)

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)

MariaDB [test]> alter table src add column other int unsigned generated always as (1);

ERROR 2013 (HY000): Lost connection to MySQL server during query

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.