Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.3(EOL), 10.4(EOL), 10.5, 10.6, 10.11, 11.4
Description
The following change to our regression test suite demonstrates several problems:
diff --git a/mysql-test/suite/sql_sequence/alter.opt b/mysql-test/suite/sql_sequence/alter.opt
|
new file mode 100644
|
index 00000000000..c5eebd75ce5
|
--- /dev/null
|
+++ b/mysql-test/suite/sql_sequence/alter.opt
|
@@ -0,0 +1 @@
|
+--innodb-sys-tables
|
diff --git a/mysql-test/suite/sql_sequence/alter.result b/mysql-test/suite/sql_sequence/alter.result
|
index 15b3ed72af6..8f9035f19d0 100644
|
--- a/mysql-test/suite/sql_sequence/alter.result
|
+++ b/mysql-test/suite/sql_sequence/alter.result
|
@@ -166,6 +166,25 @@ next_not_cached_value minimum_value maximum_value start_value increment cache_si
|
select next value for t1;
|
next value for t1
|
11
|
+$check_innodb_flags;
|
+is_sequence
|
+12288
|
+alter table t1 sequence=0;
|
+$check_innodb_flags;
|
+is_sequence
|
+0
|
+alter table t1 sequence=1;
|
+$check_innodb_flags;
|
+is_sequence
|
+12288
|
+alter table t1 sequence=0, algorithm=copy;
|
+$check_innodb_flags;
|
+is_sequence
|
+0
|
+alter table t1 sequence=1, algorithm=copy;
|
+$check_innodb_flags;
|
+is_sequence
|
+12288
|
drop sequence t1;
|
#
|
# ALTER TABLE
|
diff --git a/mysql-test/suite/sql_sequence/alter.test b/mysql-test/suite/sql_sequence/alter.test
|
index 9ee2f222312..f68d7d5456f 100644
|
--- a/mysql-test/suite/sql_sequence/alter.test
|
+++ b/mysql-test/suite/sql_sequence/alter.test
|
@@ -80,6 +80,18 @@ alter sequence t1 start=100;
|
show create sequence t1;
|
select * from t1;
|
select next value for t1;
|
+let $check_innodb_flags =
|
+select flag & 12288 is_sequence from information_schema.innodb_sys_tables
|
+where name='test/t1';
|
+evalp $check_innodb_flags;
|
+alter table t1 sequence=0;
|
+evalp $check_innodb_flags;
|
+alter table t1 sequence=1;
|
+evalp $check_innodb_flags;
|
+alter table t1 sequence=0, algorithm=copy;
|
+evalp $check_innodb_flags;
|
+alter table t1 sequence=1, algorithm=copy;
|
+evalp $check_innodb_flags;
|
drop sequence t1;
|
|
--echo # |
This would crash as follows:
|
10.6 6e6fcf4d43e9f5812e1870821968a77c9f826b62 |
CURRENT_TEST: sql_sequence.alter
|
mysqltest: At line 91: query 'alter table t1 sequence=0, algorithm=copy' failed: <Unknown> (2013): Lost connection to server during query
|
|
|
The result from queries just before the failure was:
|
< snip >
|
Table Create Table
|
t1 CREATE SEQUENCE `t1` start with 100 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 10 nocycle ENGINE=InnoDB
|
select * from t1;
|
next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
|
11 1 9223372036854775806 100 1 10 0 0
|
select next value for t1;
|
next value for t1
|
11
|
$check_innodb_flags;
|
is_sequence
|
12288
|
alter table t1 sequence=0;
|
$check_innodb_flags;
|
is_sequence
|
12288
|
alter table t1 sequence=1;
|
$check_innodb_flags;
|
is_sequence
|
12288
|
alter table t1 sequence=0, algorithm=copy;
|
…
|
mariadbd: /mariadb/10.6/storage/innobase/row/row0mysql.cc:2685: dberr_t row_rename_table_for_mysql(const char*, const char*, trx_t*, rename_fk): Assertion `err != DB_DUPLICATE_KEY' failed.
|
The reason for the assertion failure is that mysql_alter_table() wrongly omitted a call to ha_innobase::rename_table() and therefore the InnoDB data dictionary would get out of sync with the TABLE_SHARE and the .frm file. That would be fixed by the following:
diff --git a/sql/sql_table.cc b/sql/sql_table.cc
|
index c20fb8d9bc4..455a771361b 100644
|
--- a/sql/sql_table.cc
|
+++ b/sql/sql_table.cc
|
@@ -11111,7 +11111,8 @@ do_continue:;
|
- Neither old or new engine uses files from another engine
|
The above is mainly true for the sequence and the partition engine.
|
*/
|
- engine_changed= ((new_table->file->ht != table->file->ht) &&
|
+ engine_changed= ((new_table->file->storage_ht() !=
|
+ table->file->storage_ht()) &&
|
((!(new_table->file->ha_table_flags() & HA_FILE_BASED) ||
|
!(table->file->ha_table_flags() & HA_FILE_BASED))) &&
|
!(table->file->ha_table_flags() & HA_REUSES_FILE_NAMES) && |
The rest (failure to change the InnoDB dict_table_t::no_rollback() related flags between 0 and 12288) is due to some omissions in handler::check_if_supported_inplace_alter().
Attachments
Issue Links
- relates to
-
-
- Stalled
-
-
-
- Closed
-
Activity
I do not think that any 'normal' user would ever do
alter table t1 sequence=0;
In my opinion this is better to leave for 10.11
It also likely duplicates MDEV-35692, please close that one too if it gets fixed.
With the PR https://github.com/MariaDB/server/pull/3821 6affdc4176df336ca680f2d5b9acd0bb1a7579b7 applied to the current main d7abccff5e55ea7fc3cb3f8d023896dc53c58152, the mandatory COPY for ALTER TABLE ... SEQUENCE=... causes an regressive assertion failure at the following case:
--source include/have_innodb.inc
|
CREATE TABLE `s1` ( |
`next_not_cached_value` bigint(21) NOT NULL, |
`minimum_value` bigint(21) NOT NULL, |
`maximum_value` bigint(21) NOT NULL, |
`start_value` bigint(21) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used', |
`increment` bigint(21) NOT NULL COMMENT 'increment value', |
`cache_size` bigint(21) unsigned NOT NULL, |
`cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed', |
`cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done' |
) ENGINE=innodb;
|
|
|
insert into s1 values (1,1,9223372036854775806,1,1,1000,0,0), (2,1,9223372036854775806,1,1,1000,0,0); |
alter table s1 sequence=1; |
The assertion is ut_ad(!node->table->skip_alter_undo); in row_upd_clust_rec().
Stack:
6 in row_upd_clust_rec of /home/ycp/source/mariadb-server/main/src/storage/innobase/row/row0upd.cc:2354
|
7 in row_upd_clust_step of /home/ycp/source/mariadb-server/main/src/storage/innobase/row/row0upd.cc:2655
|
8 in row_upd of /home/ycp/source/mariadb-server/main/src/storage/innobase/row/row0upd.cc:2756
|
9 in row_upd_step of /home/ycp/source/mariadb-server/main/src/storage/innobase/row/row0upd.cc:2898
|
10 in row_update_for_mysql of /home/ycp/source/mariadb-server/main/src/storage/innobase/row/row0mysql.cc:1654
|
11 in ha_innobase::update_row of /home/ycp/source/mariadb-server/main/src/storage/innobase/handler/ha_innodb.cc:8580
|
12 in handler::update_first_row of /home/ycp/source/mariadb-server/main/src/sql/handler.cc:8289
|
13 in ha_sequence::write_row of /home/ycp/source/mariadb-server/main/src/sql/ha_sequence.cc:296
|
14 in handler::ha_write_row of /home/ycp/source/mariadb-server/main/src/sql/handler.cc:8188
|
15 in copy_data_between_tables of /home/ycp/source/mariadb-server/main/src/sql/sql_table.cc:12587
|
16 in mysql_alter_table of /home/ycp/source/mariadb-server/main/src/sql/sql_table.cc:11680
|
The divergence in code paths happens in mysql_alter, when checking whether the "native" alter table is supported:
if (ha_alter_info.inplace_supported == HA_ALTER_INPLACE_NOT_SUPPORTED) |
use_inplace= false; |
|
|
if (use_inplace) |
This needs to be fixed before I can implement marko's proposal for MDEV-35866.
ycp, a variation of your test would fail also if the MDEV-36038 fix were not applied:
--source include/have_innodb.inc
|
|
|
CREATE TABLE s1 ( |
next_not_cached_value bigint(21) NOT NULL, |
minimum_value bigint(21) NOT NULL, |
maximum_value bigint(21) NOT NULL, |
start_value bigint(21) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used', |
increment bigint(21) NOT NULL COMMENT 'increment value', |
cache_size bigint(21) unsigned NOT NULL, |
cycle_option tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed', |
cycle_count bigint(21) NOT NULL COMMENT 'How many cycles have been done' |
) ENGINE=myisam;
|
|
|
insert into s1 values (1,1,9223372036854775806,1,1,1000,0,0), (2,1,9223372036854775806,1,1,1000,0,0); |
alter table s1 sequence=1; |
alter table s1 engine=innodb; |
|
10.11 43c5d1303f5c7c726db276815c459436110f342f |
#2 0x0000559f33a9f75b in handle_fatal_signal (sig=11) at /mariadb/10.11/sql/signal_handler.cc:298
|
#3 <signal handler called>
|
#4 0x0000559f33e18f2e in calc_row_difference (uvect=0x7fa79018dce8, old_row=0x7fa79018bc30 "\377\001", new_row=0x7fa79018bbf0 "\377\002", table=0x7fa79018aa68, upd_buff=0x7fa7900932f8 "\200", buff_len=154,
|
prebuilt=0x7fa79018cef0, auto_inc=@0x7fa7b5056f20: 0) at /mariadb/10.11/storage/innobase/handler/ha_innodb.cc:8418
|
#5 0x0000559f33e1968b in ha_innobase::update_row (this=0x7fa79018aea8, old_row=0x7fa79018bc30 "\377\001", new_row=0x7fa79018bbf0 "\377\002") at /mariadb/10.11/storage/innobase/handler/ha_innodb.cc:8595
|
#6 0x0000559f33ab6552 in handler::update_first_row (this=0x7fa79018aea8, new_data=0x7fa79018bbf0 "\377\002") at /mariadb/10.11/sql/handler.cc:7866
|
#7 0x0000559f33da1bdf in ha_sequence::write_row (this=0x7fa79018b6a8, buf=0x7fa79018bbf0 "\377\002") at /mariadb/10.11/sql/ha_sequence.cc:296
|
#8 0x0000559f33ab5daa in handler::ha_write_row (this=0x7fa79018b6a8, buf=0x7fa79018bbf0 "\377\002") at /mariadb/10.11/sql/handler.cc:7750
|
#9 0x0000559f337fde7d in copy_data_between_tables (thd=0x7fa790000d58, from=0x7fa79003fc38, to=0x7fa79018aa68, ignore=false, order_num=0, order=0x0, copied=0x7fa7b5057dd8, deleted=0x7fa7b5057de0,
|
alter_info=0x7fa7b505a100, alter_ctx=0x7fa7b50593c0) at /mariadb/10.11/sql/sql_table.cc:12351
|
#10 0x0000559f337fb1d4 in mysql_alter_table (thd=0x7fa790000d58, new_db=0x7fa7900058e8, new_name=0x7fa790005d50, create_info=0x7fa7b505a270, table_list=0x7fa790014898, recreate_info=0x7fa7b505a0c0,
|
alter_info=0x7fa7b505a100, order_num=0, order=0x0, ignore=false, if_exists=false) at /mariadb/10.11/sql/sql_table.cc:11541
|
#11 0x0000559f338aa043 in Sql_cmd_alter_table::execute (this=0x7fa790014fb8, thd=0x7fa790000d58) at /mariadb/10.11/sql/sql_alter.cc:688
|
#12 0x0000559f336d4fdd in mysql_execute_command (thd=0x7fa790000d58, is_called_from_prepared_stmt=false) at /mariadb/10.11/sql/sql_parse.cc:6167
|
#13 0x0000559f336dadc0 in mysql_parse (thd=0x7fa790000d58, rawbuf=0x7fa7900147b0 "alter table s1 engine=innodb", length=28, parser_state=0x7fa7b505b3e0) at /mariadb/10.11/sql/sql_parse.cc:8188
|
#14 0x0000559f336c6523 in dispatch_command (command=COM_QUERY, thd=0x7fa790000d58, packet=0x7fa7900a0c09 "alter table s1 engine=innodb", packet_length=28, blocking=true) at /mariadb/10.11/sql/sql_parse.cc:1905
|
I believe that this failure is the topic of MDEV-36032.
After an ALTER TABLE t1 SEQUENCE=0, a DELETE would crash because InnoDB would still think that it is a sequence. For sequences, the intended supported operations are INSERT (of a single row) and UPDATE (of that single row):
10.5 fbb6b50499a258b0e015e5b5c5b1e4effededb25
mysqltest: At line 89: query 'delete from t1' failed: 2013: Lost connection to MySQL server during query…2025-02-07 01:21:18 0x7a7c3acb46c0 InnoDB: Assertion failure in file /home/marko/10.6/storage/innobase/include/trx0trx.inl line 67InnoDB: Failing assertion: state == TRX_STATE_NOT_STARTED || (relaxed && thd_get_error_number(trx->mysql_thd))