Table corruption upon adding serial data type

--source include/have_partition.inc

CREATE TABLE t (f INT, KEY(f));

ALTER TABLE t ADD x SERIAL;

ALTER TABLE t ADD SYSTEM VERSIONING;

ALTER TABLE t PARTITION BY SYSTEM_TIME LIMIT 10 (PARTITION p0 HISTORY, PARTITION pn CURRENT);

ALTER TABLE t ADD PARTITION (PARTITION p1 HISTORY);

ALTER TABLE t ADD PARTITION (PARTITION p2 HISTORY);

# At this point we already have a problem

SET system_versioning_alter_history=KEEP;

ALTER TABLE t DROP f;

# Cleanup

DROP TABLE t;

mysqltest: At line 9: query 'ALTER TABLE t ADD PARTITION (PARTITION p2 HISTORY)' failed: 1030: Got error 190 "Incompatible key or row definition between the MariaDB .frm file and the information in the storage engine. You have to dump an" from storage engine MyISAM

ALTER TABLE t ADD PARTITION (PARTITION p2 HISTORY);

Warnings:

Warning	1082	InnoDB: Table test/t#P#p0 has no primary key in InnoDB data dictionary, but has one in MariaDB!

Warning	1082	InnoDB: Table test/t#P#p1 has no primary key in InnoDB data dictionary, but has one in MariaDB!

Warning	1082	InnoDB: Table test/t#P#pn has no primary key in InnoDB data dictionary, but has one in MariaDB!

2021-12-13  3:21:15 9 [ERROR] Table test/t#P#p0 has no primary key in InnoDB data dictionary, but has one in MariaDB! If you created the table with a MariaDB version < 3.23.54 and did not define a primary key, but defined a unique key with all non-NULL columns, then MariaDB internally treats that key as the primary key. You can fix this error by dump + DROP + CREATE + reimport of the table.

2021-12-13  3:21:15 9 [Warning] Table ./test/t#P#p0 key_used_on_scan is 0 even though there is no primary key inside InnoDB.

2021-12-13  3:21:15 9 [ERROR] Table test/t#P#p1 has no primary key in InnoDB data dictionary, but has one in MariaDB! If you created the table with a MariaDB version < 3.23.54 and did not define a primary key, but defined a unique key with all non-NULL columns, then MariaDB internally treats that key as the primary key. You can fix this error by dump + DROP + CREATE + reimport of the table.

2021-12-13  3:21:15 9 [Warning] Table ./test/t#P#p1 key_used_on_scan is 0 even though there is no primary key inside InnoDB.

2021-12-13  3:21:15 9 [ERROR] Table test/t#P#pn has no primary key in InnoDB data dictionary, but has one in MariaDB! If you created the table with a MariaDB version < 3.23.54 and did not define a primary key, but defined a unique key with all non-NULL columns, then MariaDB internally treats that key as the primary key. You can fix this error by dump + DROP + CREATE + reimport of the table.

2021-12-13  3:21:15 9 [Warning] Table ./test/t#P#pn key_used_on_scan is 0 even though there is no primary key inside InnoDB.

2021-12-13  3:21:15 9 [ERROR] Table test/t#P#p0 has no primary key in InnoDB data dictionary, but has one in MariaDB! If you created the table with a MariaDB version < 3.23.54 and did not define a primary key, but defined a unique key with all non-NULL columns, then MariaDB internally treats that key as the primary key. You can fix this error by dump + DROP + CREATE + reimport of the table.

2021-12-13  3:21:15 9 [Warning] Table ./test/t#P#p0 key_used_on_scan is 0 even though there is no primary key inside InnoDB.

2021-12-13  3:21:15 9 [ERROR] Table test/t#P#p1 has no primary key in InnoDB data dictionary, but has one in MariaDB! If you created the table with a MariaDB version < 3.23.54 and did not define a primary key, but defined a unique key with all non-NULL columns, then MariaDB internally treats that key as the primary key. You can fix this error by dump + DROP + CREATE + reimport of the table.

2021-12-13  3:21:15 9 [Warning] Table ./test/t#P#p1 key_used_on_scan is 0 even though there is no primary key inside InnoDB.

2021-12-13  3:21:15 9 [ERROR] Table test/t#P#pn has no primary key in InnoDB data dictionary, but has one in MariaDB! If you created the table with a MariaDB version < 3.23.54 and did not define a primary key, but defined a unique key with all non-NULL columns, then MariaDB internally treats that key as the primary key. You can fix this error by dump + DROP + CREATE + reimport of the table.

2021-12-13  3:21:15 9 [Warning] Table ./test/t#P#pn key_used_on_scan is 0 even though there is no primary key inside InnoDB.

2021-12-13  3:21:15 9 [ERROR] Table test/t#P#p0 has no primary key in InnoDB data dictionary, but has one in MariaDB! If you created the table with a MariaDB version < 3.23.54 and did not define a primary key, but defined a unique key with all non-NULL columns, then MariaDB internally treats that key as the primary key. You can fix this error by dump + DROP + CREATE + reimport of the table.

2021-12-13  3:21:15 9 [ERROR] Table test/t#P#p1 has no primary key in InnoDB data dictionary, but has one in MariaDB! If you created the table with a MariaDB version < 3.23.54 and did not define a primary key, but defined a unique key with all non-NULL columns, then MariaDB internally treats that key as the primary key. You can fix this error by dump + DROP + CREATE + reimport of the table.

2021-12-13  3:21:15 9 [ERROR] Table test/t#P#pn has no primary key in InnoDB data dictionary, but has one in MariaDB! If you created the table with a MariaDB version < 3.23.54 and did not define a primary key, but defined a unique key with all non-NULL columns, then MariaDB internally treats that key as the primary key. You can fix this error by dump + DROP + CREATE + reimport of the table.

2021-12-13  3:21:15 9 [ERROR] Table test/t#P#p0 has no primary key in InnoDB data dictionary, but has one in MariaDB! If you created the table with a MariaDB version < 3.23.54 and did not define a primary key, but defined a unique key with all non-NULL columns, then MariaDB internally treats that key as the primary key. You can fix this error by dump + DROP + CREATE + reimport of the table.

2021-12-13  3:21:15 9 [ERROR] Table test/t#P#p1 has no primary key in InnoDB data dictionary, but has one in MariaDB! If you created the table with a MariaDB version < 3.23.54 and did not define a primary key, but defined a unique key with all non-NULL columns, then MariaDB internally treats that key as the primary key. You can fix this error by dump + DROP + CREATE + reimport of the table.

2021-12-13  3:21:15 9 [ERROR] Table test/t#P#pn has no primary key in InnoDB data dictionary, but has one in MariaDB! If you created the table with a MariaDB version < 3.23.54 and did not define a primary key, but defined a unique key with all non-NULL columns, then MariaDB internally treats that key as the primary key. You can fix this error by dump + DROP + CREATE + reimport of the table.

mysqld: /data/src/10.4/storage/innobase/handler/handler0alter.cc:331: void dict_table_t::prepare_instant(const dict_table_t&, const ulint*, unsigned int&): Assertion `n_fields >= oindex.n_fields' failed.

211213  3:24:12 [ERROR] mysqld got signal 6 ;

#7  0x00007faf3107ef36 in __GI___assert_fail (assertion=0x559372e92b29 "n_fields >= oindex.n_fields", file=0x559372e92538 "/data/src/10.4/storage/innobase/handler/handler0alter.cc", line=331, function=0x559372e92858 "void dict_table_t::prepare_instant(const dict_table_t&, const ulint*, unsigned int&)") at assert.c:101

#8  0x00005593726996f5 in dict_table_t::prepare_instant (this=0x7faed4072d90, old=..., col_map=0x7faed4072c10, first_alter_pos=@0x7faed40192b8: 1) at /data/src/10.4/storage/innobase/handler/handler0alter.cc:331

#9  0x000055937269fe4a in ha_innobase_inplace_ctx::prepare_instant (this=0x7faed4019150) at /data/src/10.4/storage/innobase/handler/handler0alter.cc:1081

#10 0x000055937268247d in prepare_inplace_alter_table_dict (ha_alter_info=0x7faf260d6d00, altered_table=0x7faf260d6da0, old_table=0x7faed41a5fa0, table_name=0x7faed40a0985 "t", flags=33, flags2=80, fts_doc_id_col=18446744073709551615, add_fts_doc_id=false, add_fts_doc_id_idx=false) at /data/src/10.4/storage/innobase/handler/handler0alter.cc:6856

#11 0x0000559372688cf8 in ha_innobase::prepare_inplace_alter_table (this=0x7faed41a7698, altered_table=0x7faf260d6da0, ha_alter_info=0x7faf260d6d00) at /data/src/10.4/storage/innobase/handler/handler0alter.cc:8382

#12 0x000055937225a2ce in handler::ha_prepare_inplace_alter_table (this=0x7faed41a7698, altered_table=0x7faf260d6da0, ha_alter_info=0x7faf260d6d00) at /data/src/10.4/sql/handler.cc:4622

#13 0x0000559372587f15 in ha_partition::prepare_inplace_alter_table (this=0x7faed41a6e08, altered_table=0x7faf260d6da0, ha_alter_info=0x7faf260d6d00) at /data/src/10.4/sql/ha_partition.cc:10248

#14 0x000055937225a2ce in handler::ha_prepare_inplace_alter_table (this=0x7faed41a6e08, altered_table=0x7faf260d6da0, ha_alter_info=0x7faf260d6d00) at /data/src/10.4/sql/handler.cc:4622

#15 0x0000559371ffe5f7 in mysql_inplace_alter_table (thd=0x7faed4000d90, table_list=0x7faed4014868, table=0x7faed41a5fa0, altered_table=0x7faf260d6da0, ha_alter_info=0x7faf260d6d00, target_mdl_request=0x7faf260d7b70, alter_ctx=0x7faf260d86b0) at /data/src/10.4/sql/sql_table.cc:7809

#16 0x0000559372005d9b in mysql_alter_table (thd=0x7faed4000d90, new_db=0x7faed4005568, new_name=0x7faed4005990, create_info=0x7faf260d92b0, table_list=0x7faed4014868, alter_info=0x7faf260d91f0, order_num=0, order=0x0, ignore=false) at /data/src/10.4/sql/sql_table.cc:10288

#17 0x00005593720a0d0c in Sql_cmd_alter_table::execute (this=0x7faed4014f60, thd=0x7faed4000d90) at /data/src/10.4/sql/sql_alter.cc:520

#18 0x0000559371f147ee in mysql_execute_command (thd=0x7faed4000d90) at /data/src/10.4/sql/sql_parse.cc:6192

#19 0x0000559371f19bdb in mysql_parse (thd=0x7faed4000d90, rawbuf=0x7faed4014798 "ALTER TABLE t DROP f", length=20, parser_state=0x7faf260da4d0, is_com_multi=false, is_next_command=false) at /data/src/10.4/sql/sql_parse.cc:7995

#20 0x0000559371f05edf in dispatch_command (command=COM_QUERY, thd=0x7faed4000d90, packet=0x7faed400abf1 "ALTER TABLE t DROP f", packet_length=20, is_com_multi=false, is_next_command=false) at /data/src/10.4/sql/sql_parse.cc:1857

#21 0x0000559371f04745 in do_command (thd=0x7faed4000d90) at /data/src/10.4/sql/sql_parse.cc:1373

#22 0x000055937209749e in do_handle_one_connection (connect=0x55937458fff0) at /data/src/10.4/sql/sql_connect.cc:1420

#23 0x0000559372097137 in handle_one_connection (arg=0x55937458fff0) at /data/src/10.4/sql/sql_connect.cc:1316

#24 0x00005593725cb552 in pfs_spawn_thread (arg=0x559374582650) at /data/src/10.4/storage/perfschema/pfs.cc:1869

#25 0x00007faf31597609 in start_thread (arg=<optimized out>) at pthread_create.c:477

#26 0x00007faf3116a293 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

mysqld: /data/src/10.3/storage/innobase/dict/dict0mem.cc:1222: void dict_index_t::instant_add_field(const dict_index_t&): Assertion `type == instant.type' failed.

211213  3:27:03 [ERROR] mysqld got signal 6 ;

#7  0x00007f34fa6f6f36 in __GI___assert_fail (assertion=0x5642758f3d1a "type == instant.type", file=0x5642758f34c0 "/data/src/10.3/storage/innobase/dict/dict0mem.cc", line=1222, function=0x5642758f3c80 "void dict_index_t::instant_add_field(const dict_index_t&)") at assert.c:101

#8  0x00005642752afacc in dict_index_t::instant_add_field (this=0x7f34980b7d80, instant=...) at /data/src/10.3/storage/innobase/dict/dict0mem.cc:1222

#9  0x00005642752ad8c9 in dict_table_t::instant_add_column (this=0x7f3498006da0, table=...) at /data/src/10.3/storage/innobase/dict/dict0mem.cc:1319

#10 0x0000564274f5d11f in innobase_add_instant_try (ctx=0x7f3498017848, altered_table=0x7f349804cc00, table=0x7f3498168180, trx=0x7f34f4268178) at /data/src/10.3/storage/innobase/handler/handler0alter.cc:4374

#11 0x0000564274f784ab in commit_try_norebuild (ha_alter_info=0x7f34ef759170, ctx=0x7f3498017848, altered_table=0x7f349804cc00, old_table=0x7f3498168180, trx=0x7f34f4268178, table_name=0x7f34980bcce5 "t") at /data/src/10.3/storage/innobase/handler/handler0alter.cc:9047

#12 0x0000564274f6f67c in ha_innobase::commit_inplace_alter_table (this=0x7f3498172bb8, altered_table=0x7f349804cc00, ha_alter_info=0x7f34ef759170, commit=true) at /data/src/10.3/storage/innobase/handler/handler0alter.cc:9719

#13 0x0000564274ce69bc in handler::ha_commit_inplace_alter_table (this=0x7f3498172bb8, altered_table=0x7f349804cc00, ha_alter_info=0x7f34ef759170, commit=true) at /data/src/10.3/sql/handler.cc:4604

#14 0x00005642755313f2 in ha_partition::commit_inplace_alter_table (this=0x7f3498168dc8, altered_table=0x7f349804cc00, ha_alter_info=0x7f34ef759170, commit=true) at /data/src/10.3/sql/ha_partition.cc:10257

#15 0x0000564274ce69bc in handler::ha_commit_inplace_alter_table (this=0x7f3498168dc8, altered_table=0x7f349804cc00, ha_alter_info=0x7f34ef759170, commit=true) at /data/src/10.3/sql/handler.cc:4604

#16 0x0000564274a95d35 in mysql_inplace_alter_table (thd=0x7f3498000d90, table_list=0x7f3498012bb8, table=0x7f3498168180, altered_table=0x7f349804cc00, ha_alter_info=0x7f34ef759170, target_mdl_request=0x7f34ef7592f0, alter_ctx=0x7f34ef7598a0) at /data/src/10.3/sql/sql_table.cc:7812

#17 0x0000564274a9ca2d in mysql_alter_table (thd=0x7f3498000d90, new_db=0x7f3498005470, new_name=0x7f3498005858, create_info=0x7f34ef75a490, table_list=0x7f3498012bb8, alter_info=0x7f34ef75a3d0, order_num=0, order=0x0, ignore=false) at /data/src/10.3/sql/sql_table.cc:10086

#18 0x0000564274b2dccc in Sql_cmd_alter_table::execute (this=0x7f3498013338, thd=0x7f3498000d90) at /data/src/10.3/sql/sql_alter.cc:512

#19 0x00005642749b6327 in mysql_execute_command (thd=0x7f3498000d90) at /data/src/10.3/sql/sql_parse.cc:6075

#20 0x00005642749bb9e6 in mysql_parse (thd=0x7f3498000d90, rawbuf=0x7f3498012ad8 "ALTER TABLE t ADD t TIMESTAMP", length=29, parser_state=0x7f34ef75b530, is_com_multi=false, is_next_command=false) at /data/src/10.3/sql/sql_parse.cc:7870

#21 0x00005642749a80a7 in dispatch_command (command=COM_QUERY, thd=0x7f3498000d90, packet=0x7f3498008f31 "ALTER TABLE t ADD t TIMESTAMP", packet_length=29, is_com_multi=false, is_next_command=false) at /data/src/10.3/sql/sql_parse.cc:1852

#22 0x00005642749a6a47 in do_command (thd=0x7f3498000d90) at /data/src/10.3/sql/sql_parse.cc:1398

#23 0x0000564274b27b4e in do_handle_one_connection (connect=0x5642778cc900) at /data/src/10.3/sql/sql_connect.cc:1403

#24 0x0000564274b278aa in handle_one_connection (arg=0x5642778cc900) at /data/src/10.3/sql/sql_connect.cc:1308

#25 0x00005642754fc071 in pfs_spawn_thread (arg=0x5642778af770) at /data/src/10.3/storage/perfschema/pfs.cc:1869

#26 0x00007f34fa8bd609 in start_thread (arg=<optimized out>) at pthread_create.c:477

#27 0x00007f34fa7e2293 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

mariadbd: /data/src/10.7/storage/innobase/handler/handler0alter.cc:246: void dict_table_t::prepare_instant(const dict_table_t&, const ulint*, unsigned int&): Assertion `index.n_fields > oindex.n_fields || !not_redundant()' failed.

211213  3:29:37 [ERROR] mysqld got signal 6 ;

#7  0x00007f64013c7f36 in __GI___assert_fail (assertion=0x55fe5c8d7df8 "index.n_fields > oindex.n_fields || !not_redundant()", file=0x55fe5c8d7a50 "/data/src/10.7/storage/innobase/handler/handler0alter.cc", line=246, function=0x55fe5c8d7c10 "void dict_table_t::prepare_instant(const dict_table_t&, const ulint*, unsigned int&)") at assert.c:101

#8  0x000055fe5c046a6b in dict_table_t::prepare_instant (this=0x7f63bcb1b508, old=..., col_map=0x7f63bcb1c010, first_alter_pos=@0x7f63bc07c248: 0) at /data/src/10.7/storage/innobase/handler/handler0alter.cc:246

#9  0x000055fe5c04d1b4 in ha_innobase_inplace_ctx::prepare_instant (this=0x7f63bc07c0e8) at /data/src/10.7/storage/innobase/handler/handler0alter.cc:1046

#10 0x000055fe5c030e85 in prepare_inplace_alter_table_dict (ha_alter_info=0x7f63f65ae220, altered_table=0x7f63f65ae2e0, old_table=0x7f63bc2381c8, table_name=0x7f63bc042ed5 "t", flags=33, flags2=80, fts_doc_id_col=18446744073709551615, add_fts_doc_id=false, add_fts_doc_id_idx=false) at /data/src/10.7/storage/innobase/handler/handler0alter.cc:6757

#11 0x000055fe5c0383bb in ha_innobase::prepare_inplace_alter_table (this=0x7f63bc103ac0, altered_table=0x7f63f65ae2e0, ha_alter_info=0x7f63f65ae220) at /data/src/10.7/storage/innobase/handler/handler0alter.cc:8276

#12 0x000055fe5bbeef98 in handler::ha_prepare_inplace_alter_table (this=0x7f63bc103ac0, altered_table=0x7f63f65ae2e0, ha_alter_info=0x7f63f65ae220) at /data/src/10.7/sql/handler.cc:5171

#13 0x000055fe5bef177f in ha_partition::prepare_inplace_alter_table (this=0x7f63bc226250, altered_table=0x7f63f65ae2e0, ha_alter_info=0x7f63f65ae220) at /data/src/10.7/sql/ha_partition.cc:10396

#14 0x000055fe5bbeef98 in handler::ha_prepare_inplace_alter_table (this=0x7f63bc226250, altered_table=0x7f63f65ae2e0, ha_alter_info=0x7f63f65ae220) at /data/src/10.7/sql/handler.cc:5171

#15 0x000055fe5b948656 in mysql_inplace_alter_table (thd=0x7f63bc000db8, table_list=0x7f63bc0154e0, table=0x7f63bc2381c8, altered_table=0x7f63f65ae2e0, ha_alter_info=0x7f63f65ae220, target_mdl_request=0x7f63f65aea70, ddl_log_state=0x7f63f65ae1c0, trigger_param=0x7f63f65ae680, alter_ctx=0x7f63f65af5d0) at /data/src/10.7/sql/sql_table.cc:7446

#16 0x000055fe5b951534 in mysql_alter_table (thd=0x7f63bc000db8, new_db=0x7f63bc005a58, new_name=0x7f63bc005e70, create_info=0x7f63f65b03e0, table_list=0x7f63bc0154e0, alter_info=0x7f63f65b02f0, order_num=0, order=0x0, ignore=false, if_exists=false) at /data/src/10.7/sql/sql_table.cc:10342

#17 0x000055fe5ba06af9 in Sql_cmd_alter_table::execute (this=0x7f63bc015cd8, thd=0x7f63bc000db8) at /data/src/10.7/sql/sql_alter.cc:540

#18 0x000055fe5b834b2d in mysql_execute_command (thd=0x7f63bc000db8, is_called_from_prepared_stmt=false) at /data/src/10.7/sql/sql_parse.cc:5989

#19 0x000055fe5b83ad2a in mysql_parse (thd=0x7f63bc000db8, rawbuf=0x7f63bc015400 "ALTER TABLE t ADD t TIMESTAMP", length=29, parser_state=0x7f63f65b1480) at /data/src/10.7/sql/sql_parse.cc:8028

#20 0x000055fe5b82717e in dispatch_command (command=COM_QUERY, thd=0x7f63bc000db8, packet=0x7f63bc00b989 "ALTER TABLE t ADD t TIMESTAMP", packet_length=29, blocking=true) at /data/src/10.7/sql/sql_parse.cc:1894

#21 0x000055fe5b825b1a in do_command (thd=0x7f63bc000db8, blocking=true) at /data/src/10.7/sql/sql_parse.cc:1402

#22 0x000055fe5b9fb641 in do_handle_one_connection (connect=0x55fe5e78f158, put_in_cache=true) at /data/src/10.7/sql/sql_connect.cc:1418

#23 0x000055fe5b9fb2d1 in handle_one_connection (arg=0x55fe5e78da88) at /data/src/10.7/sql/sql_connect.cc:1312

#24 0x000055fe5befbcf7 in pfs_spawn_thread (arg=0x55fe5e78eca8) at /data/src/10.7/storage/perfschema/pfs.cc:2201

#25 0x00007f64018e0609 in start_thread (arg=<optimized out>) at pthread_create.c:477

#26 0x00007f64014b3293 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

commit 63c922ae0c9e5896505f3843eeb0524ae97fe779

Author: Aleksey Midenkov

Date:   Tue Nov 2 04:52:03 2021 +0300

    MDEV-25803 Inplace ALTER breaks MyISAM/Aria table when order of keys is changed