|
--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;
|
If executed with MyISAM, the second ADD PARTITION fails with an error:
|
10.3 375ae890
|
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
|
If executed with InnoDB, this ADD PARTITION ends with warnings and errors in the error log:
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.
|
Apparently as an aftermath, following ALTERs may cause different assertion failures (depending on the engine, ALTER and server version):
|
10.4 74b3d425
|
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
|
|
10.3 375ae890
|
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
|
|
10.7 979b23d5
|
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
|
The failure appeared on 10.3 after this commit:
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
|
|