create function get_index_id(tbl_id int, index_name char(100))
returns int
begin
declare res int;
select index_id into res from information_schema.innodb_sys_indexes where
name=index_name and table_id = tbl_id;
return res;
end|
create table t (
pk int primary key,
a int,
b int,
c int,
unique index a_key (a),
key c_key (c)
) engine=innodb stats_persistent=1;
insert into t values (1, 1, 1, 1);
set @table_id = (select table_id from information_schema.innodb_sys_tables where name='test/t');
set @a_key_id = get_index_id(@table_id, 'a_key');
set @c_key_id = get_index_id(@table_id, 'c_key');
set @primary_id = get_index_id(@table_id, 'primary');
select distinct(index_name) from mysql.innodb_index_stats where table_name = 't';
index_name
PRIMARY
a_key
c_key
alter table t
drop index a_key,
add unique index a_key_strikes_back (a);
select distinct(index_name) from mysql.innodb_index_stats where table_name = 't';
index_name
PRIMARY
a_key_strikes_back
c_key
check table t;
Table	Op	Msg_type	Msg_text
test.t	check	status	OK
select @a_key_id = get_index_id(@table_id, 'a_key_strikes_back'),
@c_key_id = get_index_id(@table_id, 'c_key'),
@primary_id = get_index_id(@table_id, 'primary');
@a_key_id = get_index_id(@table_id, 'a_key_strikes_back')	@c_key_id = get_index_id(@table_id, 'c_key')	@primary_id = get_index_id(@table_id, 'primary')
1	1	1
set @a_key_strikes_back_id = get_index_id(@table_id, 'a_key_strikes_back');
set @c_key_id = get_index_id(@table_id, 'c_key');
set @primary_id = get_index_id(@table_id, 'primary');
alter table t
drop index a_key_strikes_back,
add unique index a_key_returns (a),
drop primary key,
add primary key (pk),
add unique index b_key (b);
check table t;
Table	Op	Msg_type	Msg_text
test.t	check	status	OK
select @a_key_strikes_back_id = get_index_id(@table_id, 'a_key_returns'),
@c_key_id = get_index_id(@table_id, 'c_key'),
@primary_id = get_index_id(@table_id, 'primary');
@a_key_strikes_back_id = get_index_id(@table_id, 'a_key_returns')	@c_key_id = get_index_id(@table_id, 'c_key')	@primary_id = get_index_id(@table_id, 'primary')
1	1	1
set @a_key_returns_id = get_index_id(@table_id, 'a_key_returns');
set @b_key_id = get_index_id(@table_id, 'b_key');
set @c_key_id = get_index_id(@table_id, 'c_key');
set @primary_id = get_index_id(@table_id, 'primary');
alter table t
drop key c_key,
add key c_key2 (c);
check table t;
Table	Op	Msg_type	Msg_text
test.t	check	status	OK
select @a_key_returns_id = get_index_id(@table_id, 'a_key_returns'),
@b_key_id = get_index_id(@table_id, 'b_key'),
@c_key_id = get_index_id(@table_id, 'c_key2'),
@primary_id = get_index_id(@table_id, 'primary');
@a_key_returns_id = get_index_id(@table_id, 'a_key_returns')	@b_key_id = get_index_id(@table_id, 'b_key')	@c_key_id = get_index_id(@table_id, 'c_key2')	@primary_id = get_index_id(@table_id, 'primary')
1	1	1	1
drop table t;
create table errors (
a int,
unique key a_key (a),
b int
) engine=innodb;
alter table errors
drop key a_key,
drop key a_key,
add unique key a_key2 (a);
ERROR 42000: Can't DROP INDEX `a_key`; check that it exists
alter table errors
drop key a_key,
drop key a_key2,
add unique key a_key2 (a);
ERROR 42000: Can't DROP INDEX `a_key2`; check that it exists
alter table errors
add key b_key (b),
drop key b_key,
add key bb_key (b);
ERROR 42000: Can't DROP INDEX `b_key`; check that it exists
alter table errors
drop key a_key,
add key a_key2 (a),
drop key a_key,
add key a_key2 (a);
ERROR 42000: Can't DROP INDEX `a_key`; check that it exists
drop table errors;
create table corrupted (
a int,
key a_key (a)
) engine=innodb;
insert into corrupted values (1);
select * from corrupted;
a
1
SET @save_dbug = @@SESSION.debug_dbug;
SET debug_dbug = '+d,dict_set_index_corrupted';
check table corrupted;
Table	Op	Msg_type	Msg_text
test.corrupted	check	Warning	InnoDB: Index a_key is marked as corrupted
test.corrupted	check	error	Corrupt
SET debug_dbug = @save_dbug;
select * from corrupted;
ERROR HY000: Index corrupted is corrupted
alter table corrupted
drop key a_key,
add key a_key2 (a);
ERROR HY000: Index a_key is corrupted
alter table corrupted
drop key a_key;
select * from corrupted;
a
1
check table corrupted;
Table	Op	Msg_type	Msg_text
test.corrupted	check	status	OK
drop table corrupted;
create table t (
a int,
unique key a_key (a)
) engine=innodb stats_persistent=1;
SET @save_dbug = @@SESSION.debug_dbug;
SET debug_dbug = '+d,ib_rename_index_fail1';
alter table t
drop key a_key,
add unique key a_key2 (a),
algorithm=instant;
ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
SET debug_dbug = @save_dbug;
alter table t
drop key a_key,
add unique key `GEN_CLUST_INDEX` (a),
algorithm=instant;
ERROR 42000: Incorrect index name 'GEN_CLUST_INDEX'
show create table t;
Table	Create Table
t	CREATE TABLE `t` (
  `a` int(11) DEFAULT NULL,
  UNIQUE KEY `a_key` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 STATS_PERSISTENT=1
drop table t;
create table rename_column_and_index (
a int,
unique index a_key(a)
) engine=innodb;
insert into rename_column_and_index values (1), (3);
alter table rename_column_and_index
change a aa int,
drop key a_key,
add unique key aa_key(aa),
algorithm=instant;
show create table rename_column_and_index;
Table	Create Table
rename_column_and_index	CREATE TABLE `rename_column_and_index` (
  `aa` int(11) DEFAULT NULL,
  UNIQUE KEY `aa_key` (`aa`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
check table rename_column_and_index;
Table	Op	Msg_type	Msg_text
test.rename_column_and_index	check	status	OK
drop table rename_column_and_index;
#
# MDEV-19189: ASAN memcpy-param-overlap in fill_alter_inplace_info upon adding indexes
#
CREATE TABLE t1 (f1 INT, f2 INT, f3 INT);
ALTER TABLE t1 ADD FOREIGN KEY f (f2) REFERENCES xx(f2);
ALTER TABLE t1 ADD FOREIGN KEY (f2) REFERENCES t1(f2), ADD KEY (f3), ADD KEY (f1);
DROP TABLE t1;
#
# MDEV-21669 InnoDB: Table ... contains <n> indexes inside InnoDB, which is different from the number of indexes <n> defined in the MariaDB
#
CREATE TABLE t1 (col_int INTEGER, col_char CHAR(20), col_varchar VARCHAR(500)) ENGINE=InnoDB;
SET @table_id = (SELECT table_id FROM information_schema.innodb_sys_tables WHERE name='test/t1');
ALTER TABLE t1 ADD KEY idx3 (col_varchar(9)), ADD KEY idX2 (col_char(9));
SET @idx3_key_id = get_index_id(@table_id, 'iDx3');
ALTER TABLE t1 DROP KEY iDx3, ADD KEY Idx3 (col_varchar(9));
SELECT @idx3_key_id = get_index_id(@table_id, 'Idx3');
@idx3_key_id = get_index_id(@table_id, 'Idx3')
1
CHECK TABLE t1 EXTENDED ;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
DROP TABLE t1;
DROP FUNCTION get_index_id;
#
# MDEV-23356 InnoDB: Failing assertion: field->col->mtype == type, crash or ASAN failures in row_sel_convert_mysql_key_to_innobase, InnoDB indexes are inconsistent after INDEX changes
#
CREATE TABLE t1 (a INT, b INT, c CHAR(8),
KEY ind1(c), KEY ind2(b)) ENGINE=InnoDB STATS_PERSISTENT=1;
INSERT INTO t1 SELECT 1, 1, 'a' FROM seq_1_to_100;
SELECT table_name, index_name, stat_name FROM mysql.innodb_index_stats;
table_name	index_name	stat_name
t1	GEN_CLUST_INDEX	n_diff_pfx01
t1	GEN_CLUST_INDEX	n_leaf_pages
t1	GEN_CLUST_INDEX	size
t1	ind1	n_diff_pfx01
t1	ind1	n_diff_pfx02
t1	ind1	n_leaf_pages
t1	ind1	size
t1	ind2	n_diff_pfx01
t1	ind2	n_diff_pfx02
t1	ind2	n_leaf_pages
t1	ind2	size
ALTER TABLE t1 DROP INDEX ind2, ADD INDEX ind3(b),
DROP INDEX ind1, ADD INDEX ind2(c);
SELECT table_name, index_name, stat_name FROM mysql.innodb_index_stats;
table_name	index_name	stat_name
t1	GEN_CLUST_INDEX	n_diff_pfx01
t1	GEN_CLUST_INDEX	n_leaf_pages
t1	GEN_CLUST_INDEX	size
t1	ind2	n_diff_pfx01
t1	ind2	n_diff_pfx02
t1	ind2	n_leaf_pages
t1	ind2	size
t1	ind3	n_diff_pfx01
t1	ind3	n_diff_pfx02
t1	ind3	n_leaf_pages
t1	ind3	size
UPDATE t1 SET a = 1 WHERE c = 'foo';
DROP TABLE t1;