mirror/mariadb
1
0
Fork 0
mirror of https://github.com/MariaDB/server.git synced 2025-12-18 10:15:43 +01:00
Code Issues Projects Releases Packages Wiki Activity
mariadb/mysql-test/suite/innodb/t/innodb-index.test
Aleksey Midenkov a518e1dd42 MDEV-20865 Store foreign key info in TABLE_SHARE 
1. Access foreign keys via TABLE_SHARE::foreign_keys and
   TABLE_SHARE::referenced_keys;

   foreign_keys and referenced_keys are lists in TABLE_SHARE.

2. Remove handler FK interface:

   - get_foreign_key_list()
   - get_parent_foreign_key_list()
   - referenced_by_foreign_key()

3. Invalidate referenced shares on:

   - RENAME TABLE
   - DROP TABLE
   - RENAME COLUMN
   - ADD FOREIGN KEY

   When foreign table is created or altered by the above operations
   all referenced shares are closed. This blocks the operation while
   any referenced shares are used (when at least one its TABLE
   instance is locked).

4. Update referenced shares on:

   - CREATE TABLE

   On CREATE TABLE add items to referenced_keys of referenced
   shares. States of referenced shares are restored in case of errors.

5. Invalidate foreign shares on:

   - RENAME TABLE
   - RENAME COLUMN

   The above-mentioned blocking takes effect.

6. Check foreign/referenced shares consistency on:

   - CHECK TABLE

7. Temporary change until MDEV-21051:

   InnoDB fill foreign key info at handler open().

FOREIGN_KEY_INFO is refactored to FK_info holding Lex_cstring.

On first TABLE open FK_info is loaded from storage engine into
TABLE_SHARE. All referenced shares (if any exists) are closed. This
leads to blocking of first time foreign table open while referenced
tables are used.

MDEV-21311 Converge Foreign_key and supplemental generated Key together

mysql_prepare_create_table() does data validation and such utilities
as automatic name generation. But it does that only for indexes and
ignores Foreign_key objects. Now as Foreign_key data needs to be
stored in FRM files as well this processing must be done for it like
for any other Key objects.

Replace Key::FOREIGN_KEY type with Key::foreign flag of type
Key::MULTIPLE and Key::generated set to true. Construct one object
with Key::foreign == true instead of two objects of type
Key::FOREIGN_KEY and Key::MULTIPLE.

MDEV-21051 datadict refactorings

- Move read_extra2() to datadict.cc
- Refactored extra2_fields to Extra2_info
- build_frm_image() readability

MDEV-21051 build_table_shadow_filename() refactoring

mysql_prepare_alter_table() leaks fixes

MDEV-21051 amend system tables locking restriction

Table mysql.help_relation has foreign key to mysql.help_keyword. On
bootstrap when help_relation is opened, it preopens help_keyword for
READ and fails in lock_tables_check().

If system table is opened for write then fk references are opened for
write.

Related to: Bug#25422, WL#3984
Tests: main.lock

MDEV-21051 Store and read foreign key info into/from FRM files

1. Introduce Foreign_key_io class which creates/parses binary stream
containing foreign key structures. Referenced tables store there only
hints about foreign tables (their db and name), they restore full info
from the corresponding tables.

Foreign_key_io is stored under new EXTRA2_FOREIGN_KEY_INFO field in
extra2 section of FRM file.

2. Modify mysql_prepare_create_table() to generate names for foreign
keys. Until InnoDB storage of foreign keys is removed, FK names must
be unique across the database: the FK name must be based on table
name.

3. Keep stored data in sync on DDL changes. Referenced tables update
their foreign hints after following operations on foreign tables:

  - RENAME TABLE
  - DROP TABLE
  - CREATE TABLE
  - ADD FOREIGN KEY
  - DROP FOREIGN KEY

Foreign tables update their foreign info after following operations on
referenced tables:

  - RENAME TABLE
  - RENAME COLUMN

4. To achieve 3. there must be ability to rewrite extra2 section of
FRM file without full reparse. FRM binary is built from primary
structures like HA_CREATE_INFO and cannot be built from TABLE_SHARE.

Use shadow write and rename like fast_alter_partition_table()
does. Shadow FRM is new FRM file that replaces the old one.

CREATE TABLE workflow:

  1. Foreign_key is constructed in parser, placed into
     alter_info->key_list;

  2. mysql_prepare_create_table() translates them to FK_info, assigns
     foreign_id if needed;

  3. build_frm_image() writes two FK_info lists into FRM's extra2
     section, for referenced keys it stores only table names (hints);

  4. init_from_binary_frm_image() parses extra2 section and fills
     foreign_keys and referenced_keys of TABLE_SHARE.

     It restores referenced_keys by reading hint list of table names,
     opening corresponding shares and restoring FK_info from their
     foreign_keys. Hints resolution is done only when initializing
     non-temporary shares. Usually temporary share has different
     (temporary) name and it is impossible to resolve foreign keys by
     that name (as we identify them by both foreign and referenced
     table names). Another not unimportant reason is performance: this
     saves spare share acquisitions.

ALTER TABLE workflow:

  1. Foreign_key is constructed in parser, placed into
     alter_info->key_list;

  2. mysql_prepare_alter_table() prepares action lists and share list
     of foreigns/references;

  3. mysql_prepare_alter_table() locks list of foreigns/references by
     MDL_INTENTION_EXCLUSIVE, acquires shares;

  4. prepare_create_table() converts key_list into FK_list, assigns
     foreign_id;

  5. shadow FRM of altered table is created;

  6. data is copied;

  7. altered table is locked by MDL_EXCLUSIVE;

  8. fk_handle_alter() processes action lists, creates FK backups,
     modifies shares, writes shadow FRMs;

  9. altered table is closed;

  10. shadow FRMs are installed;

  11. altered table is renamed, FRM backup deleted;

  12. (TBD in MDEV-21053) shadow FRMs installation log closed, backups
      deleted;

On FK backup system:

In case of failed DDL operation all shares that was modified must be
restored into original state. This is done by FK_ddl_backup (CREATE,
DROP), FK_rename_backup (RENAME), FK_alter_backup (ALTER).

On STL usage:

STL is used for utility not performance-critical algorithms, core
structures hold native List. A wrapper was made to convert STL
exception into bool error status or NULL value.

MDEV-20865 fk_check_consistency() in CHECK TABLE

Self-refs fix

Test table_flags fix: "debug" deviation is now gone.

FIXMEs: +16 -1
2025-09-02 13:24:36 +03:00

1247 lines
40 KiB
Text
Raw Permalink Blame History

-- source include/have_innodb.inc
# Embedded server tests do not support restarting.
-- source include/not_embedded.inc
--source include/test_db_charset_latin1.inc
let $MYSQLD_DATADIR= `select @@datadir`;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS;
# Bug#13654923 BOGUS DEBUG ASSERTION IN INDEX CREATION FOR ZERO-LENGTH RECORD
create table t1(a varchar(2) primary key) engine=innodb;
insert into t1 values('');
--enable_info
create index t1a1 on t1(a(1));
--disable_info
drop table t1;
create table t1(a int not null, b int, c char(10) not null, d varchar(20)) engine = innodb;
insert into t1 values (5,5,'oo','oo'),(4,4,'tr','tr'),(3,4,'ad','ad'),(2,3,'ak','ak');
commit;
--error ER_DUP_KEYNAME
alter table t1 add index b (b), add index b (b);
--error ER_DUP_FIELDNAME
alter table t1 add index (b,b);
--enable_info
alter table t1 add index d2 (d);
--disable_info
show create table t1;
-- disable_result_log
analyze table t1;
-- enable_result_log
explain select * from t1 force index(d2) order by d;
select * from t1 force index (d2) order by d;
--error ER_DUP_ENTRY
alter table t1 add unique index (b);
show create table t1;
--enable_info
alter table t1 add index (b);
--disable_info
show create table t1;
--enable_info
alter table t1 add unique index (c), add index (d);
--disable_info
show create table t1;
analyze table t1;
explain select * from t1 force index(c) order by c;
--enable_info
alter table t1 add primary key (a), drop index c;
show create table t1;
--error ER_MULTIPLE_PRI_KEY
alter table t1 add primary key (c);
--error ER_DUP_ENTRY
alter table t1 drop primary key, add primary key (b);
create unique index c on t1 (c);
--disable_info
show create table t1;
analyze table t1;
explain select * from t1 force index(c) order by c;
select * from t1 force index(c) order by c;
--enable_info
alter table t1 drop index b, add index (b);
--disable_info
show create table t1;
insert into t1 values(6,1,'ggg','ggg');
select * from t1;
select * from t1 force index(b) order by b;
select * from t1 force index(c) order by c;
select * from t1 force index(d) order by d;
-- disable_result_log
analyze table t1;
-- enable_result_log
explain select * from t1 force index(b) order by b;
explain select * from t1 force index(c) order by c;
explain select * from t1 force index(d) order by d;
show create table t1;
drop table t1;
create table t1(a int not null, b int, c char(10), d varchar(20), primary key (a)) engine = innodb;
insert into t1 values (1,1,'ab','ab'),(2,2,'ac','ac'),(3,3,'ad','ad'),(4,4,'afe','afe');
commit;
--enable_info
alter table t1 add index (c(2));
show create table t1;
alter table t1 add unique index (d(10));
show create table t1;
--disable_info
insert into t1 values(5,1,'ggg','ggg');
-- disable_result_log
analyze table t1;
-- enable_result_log
select * from t1;
select * from t1 force index(c) order by c;
select * from t1 force index(d) order by d;
explain select * from t1 order by b;
explain select * from t1 force index(c) order by c;
explain select * from t1 force index(d) order by d;
show create table t1;
--enable_info
alter table t1 drop index d;
--disable_info
insert into t1 values(8,9,'fff','fff');
select * from t1;
select * from t1 force index(c) order by c;
-- disable_result_log
analyze table t1;
-- enable_result_log
explain select * from t1 order by b;
explain select * from t1 force index(c) order by c;
explain select * from t1 order by d;
show create table t1;
drop table t1;
create table t1(a int not null, b int, c char(10), d varchar(20), primary key (a)) engine = innodb;
insert into t1 values (1,1,'ab','ab'),(2,2,'ac','ac'),(3,2,'ad','ad'),(4,4,'afe','afe');
commit;
--enable_info
alter table t1 add unique index (b,c);
--disable_info
insert into t1 values(8,9,'fff','fff');
select * from t1;
select * from t1 force index(b) order by b;
-- disable_result_log
analyze table t1;
-- enable_result_log
explain select * from t1 force index(b) order by b;
show create table t1;
--enable_info
alter table t1 add index (b,c);
--disable_info
insert into t1 values(11,11,'kkk','kkk');
select * from t1;
select * from t1 force index(b) order by b;
-- disable_result_log
analyze table t1;
-- enable_result_log
explain select * from t1 force index(b) order by b;
show create table t1;
--enable_info
alter table t1 add unique index (c,d);
--disable_info
insert into t1 values(13,13,'yyy','aaa');
select * from t1;
select * from t1 force index(b) order by b;
select * from t1 force index(c) order by c;
-- disable_result_log
analyze table t1;
-- enable_result_log
explain select * from t1 force index(b) order by b;
explain select * from t1 force index(c) order by c;
show create table t1;
drop table t1;
create table t1(a int not null, b int not null, c int, primary key (a), key (b)) engine = innodb;
create table t3(a int not null, c int not null, d int, primary key (a), key (c)) engine = innodb;
create table t4(a int not null, d int not null, e int, primary key (a), key (d)) engine = innodb;
create table t2(a int not null, b int, c int, d int, e int,
foreign key (b) references t1(b) on delete set null,
foreign key (c) references t3(c), foreign key (d) references t4(d) on update set null)
engine = innodb;
--error ER_FK_NO_INDEX_PARENT
alter table t1 drop index b;
--error ER_FK_NO_INDEX_PARENT
alter table t3 drop index c;
--error ER_FK_NO_INDEX_PARENT
alter table t4 drop index d;
--error ER_FK_NO_INDEX_CHILD
alter table t2 drop index b;
--error ER_FK_NO_INDEX_CHILD
alter table t2 drop index b, drop index c, drop index d;
--error ER_FK_COLUMN_NOT_NULL
alter table t2 MODIFY b INT NOT NULL, ALGORITHM=COPY;
# NULL -> NOT NULL only allowed INPLACE if strict sql_mode is on.
set @old_sql_mode = @@sql_mode;
set @@sql_mode = 'STRICT_TRANS_TABLES';
--error ER_FK_COLUMN_NOT_NULL
alter table t2 MODIFY b INT NOT NULL, ALGORITHM=INPLACE;
set @@sql_mode = @old_sql_mode;
SET FOREIGN_KEY_CHECKS=0;
--error ER_WRONG_FK_DEF
alter table t2 DROP COLUMN b, ALGORITHM=COPY;
--error ER_WRONG_FK_DEF
alter table t2 DROP COLUMN b;
--error ER_FK_COLUMN_CANNOT_DROP_CHILD
alter table t1 DROP COLUMN b, ALGORITHM=COPY;
--error ER_FK_COLUMN_CANNOT_DROP_CHILD
alter table t1 DROP COLUMN b;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
--enable_info
# Apparently, the following makes mysql_alter_table() drop index d.
create unique index dc on t2 (d,c);
create index dc on t1 (b,c);
# This should preserve the foreign key constraints.
--disable_info
# NULL -> NOT NULL only allowed INPLACE if strict sql_mode is on.
# And adding a PRIMARY KEY will also add NOT NULL implicitly!
set @@sql_mode = 'STRICT_TRANS_TABLES';
--enable_info
--error ER_FK_COLUMN_NOT_NULL
alter table t2 add primary key (alpha), change a alpha int,
change b beta int not null, change c charlie int not null;
--error ER_FK_COLUMN_NOT_NULL
alter table t2 add primary key (alpha), change a alpha int,
change c charlie int not null, change d delta int not null;
alter table t2 add primary key (alpha), change a alpha int,
change b beta int, modify c int not null;
--disable_info
set @@sql_mode = @old_sql_mode;
insert into t1 values (1,1,1);
insert into t3 values (1,1,1);
insert into t4 values (1,1,1);
insert into t2 values (1,1,1,1,1);
commit;
--enable_info
alter table t4 add constraint dc foreign key (a) references t1(a);
--disable_info
show create table t4;
# mysqltest first does replace_regex, then replace_result
# Embedded server doesn't chdir to data directory
--replace_result $MYSQLD_DATADIR ./ master-data/ ''
alter table t3 add constraint dc foreign key (a) references t1(a);
alter table t3 drop foreign key dc;
alter table t3 add constraint dc foreign key (a) references t1(a);
show create table t3;
--enable_info
alter table t2 drop index b, add index (beta);
--disable_info
show create table t2;
--error ER_ROW_IS_REFERENCED_2
delete from t1;
--error ER_CANT_DROP_FIELD_OR_KEY
drop index dc on t4;
--enable_info
alter table t3 drop foreign key dc;
alter table t4 drop foreign key dc;
--disable_info
select * from t2;
delete from t1;
select * from t2;
drop table t2,t4,t3,t1;
-- let charset = utf8
-- source include/innodb-index.inc
create table t1(a int not null, b int) engine = innodb;
insert into t1 values (1,1),(1,1),(1,1),(1,1);
--error ER_DUP_ENTRY
alter table t1 add unique index (a);
--error ER_DUP_ENTRY
alter table t1 add unique index (b);
--error ER_DUP_ENTRY
alter table t1 add unique index (a), add unique index(b);
show create table t1;
drop table t1;
create table t1(a int not null, c int not null,b int, primary key(a), unique key(c), key(b)) engine = innodb;
alter table t1 drop index c, drop index b;
show create table t1;
drop table t1;
create table t1(a int not null, b int, primary key(a)) engine = innodb;
alter table t1 add index (b);
show create table t1;
drop table t1;
create table t1(a int not null, b int, c char(10), d varchar(20), primary key (a)) engine = innodb;
insert into t1 values (1,1,'ab','ab'),(2,2,'ac','ac'),(3,3,'ac','ac'),(4,4,'afe','afe'),(5,4,'affe','affe');
--error ER_DUP_ENTRY
alter table t1 add unique index (b), add unique index (c), add unique index (d);
--error ER_DUP_ENTRY
alter table t1 add unique index (c), add unique index (b), add index (d);
show create table t1;
drop table t1;
create table t1(a int not null, b int not null, c int, primary key (a), key(c)) engine=innodb;
insert into t1 values (5,1,5),(4,2,4),(3,3,3),(2,4,2),(1,5,1);
--enable_info
alter table t1 add unique index (b);
--disable_info
insert into t1 values (10,20,20),(11,19,19),(12,18,18),(13,17,17);
show create table t1;
check table t1;
-- disable_result_log
analyze table t1;
-- enable_result_log
explain select * from t1 force index(c) order by c;
explain select * from t1 order by a;
explain select * from t1 force index(b) order by b;
select * from t1 order by a;
select * from t1 force index(b) order by b;
select * from t1 force index(c) order by c;
drop table t1;
create table t1(a int not null, b int not null) engine=innodb;
insert into t1 values (1,1);
--enable_info
alter table t1 add primary key(b);
--disable_info
insert into t1 values (2,2);
show create table t1;
check table t1;
select * from t1;
analyze table t1;
explain select * from t1;
explain select * from t1 order by a;
explain select * from t1 order by b;
checksum table t1;
drop table t1;
create table t1(a int not null) engine=innodb;
insert into t1 values (1);
--enable_info
alter table t1 add primary key(a);
--disable_info
insert into t1 values (2);
show create table t1;
check table t1;
commit;
select * from t1;
analyze table t1;
explain select * from t1;
explain select * from t1 order by a;
drop table t1;
#
# Test to check whether CREATE INDEX handles implicit foreign key
# constraint modifications (Issue #70, Bug #38786)
#
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET FOREIGN_KEY_CHECKS=0;
CREATE TABLE t1(
c1 BIGINT(12) NOT NULL,
PRIMARY KEY (c1)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE t2(
c1 BIGINT(16) NOT NULL,
c2 BIGINT(12) NOT NULL,
c3 BIGINT(12) NOT NULL,
PRIMARY KEY (c1)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
FOREIGN KEY (c3) REFERENCES t1(c1);
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
SHOW CREATE TABLE t2;
CREATE INDEX i_t2_c3_c2 ON t2(c3, c2);
SHOW CREATE TABLE t2;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
--error ER_NO_REFERENCED_ROW_2
INSERT INTO t2 VALUES(0,0,0);
INSERT INTO t1 VALUES(0);
INSERT INTO t2 VALUES(0,0,0);
DROP TABLE t2;
CREATE TABLE t2(
c1 BIGINT(16) NOT NULL,
c2 BIGINT(12) NOT NULL,
c3 BIGINT(12) NOT NULL,
PRIMARY KEY (c1,c2,c3)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET FOREIGN_KEY_CHECKS=0;
ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
FOREIGN KEY (c3) REFERENCES t1(c1);
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SHOW CREATE TABLE t2;
CREATE INDEX i_t2_c3_c2 ON t2(c3, c2);
SHOW CREATE TABLE t2;
--error ER_NO_REFERENCED_ROW_2
INSERT INTO t2 VALUES(0,0,1);
INSERT INTO t2 VALUES(0,0,0);
--error ER_ROW_IS_REFERENCED_2
DELETE FROM t1;
DELETE FROM t2;
DROP TABLE t2;
DROP TABLE t1;
CREATE TABLE t1(
c1 BIGINT(12) NOT NULL,
c2 INT(4) NOT NULL,
PRIMARY KEY (c2,c1)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE t2(
c1 BIGINT(16) NOT NULL,
c2 BIGINT(12) NOT NULL,
c3 BIGINT(12) NOT NULL,
PRIMARY KEY (c1)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET FOREIGN_KEY_CHECKS=0;
--enable_info
# mysqltest first does replace_regex, then replace_result
# Embedded server doesn't chdir to data directory
--replace_result $MYSQLD_DATADIR ./ master-data/ ''
--error ER_CANT_CREATE_TABLE
ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
FOREIGN KEY (c3,c2) REFERENCES t1(c1,c1), ALGORITHM=COPY;
--error ER_FK_NO_INDEX_PARENT
ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
FOREIGN KEY (c3,c2) REFERENCES t1(c1,c1);
# mysqltest first does replace_regex, then replace_result
# Embedded server doesn't chdir to data directory
--replace_result $MYSQLD_DATADIR ./ master-data/ ''
--error ER_CANT_CREATE_TABLE
ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
FOREIGN KEY (c3,c2) REFERENCES t1(c1,c2), ALGORITHM=COPY;
--error ER_FK_NO_INDEX_PARENT
ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
FOREIGN KEY (c3,c2) REFERENCES t1(c1,c2);
--error ER_FK_INCORRECT_OPTION
ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
FOREIGN KEY (c3,c2) REFERENCES t1(c2,c1), ALGORITHM=INPLACE;
# mysqltest first does replace_regex, then replace_result
# Embedded server doesn't chdir to data directory
--replace_result $MYSQLD_DATADIR ./ master-data/ ''
--error ER_CANT_CREATE_TABLE
ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
FOREIGN KEY (c3,c2) REFERENCES t1(c2,c1), ALGORITHM=COPY;
ALTER TABLE t1 MODIFY COLUMN c2 BIGINT(12) NOT NULL;
# mysqltest first does replace_regex, then replace_result
# Embedded server doesn't chdir to data directory
--replace_result $MYSQLD_DATADIR ./ master-data/ ''
--error ER_CANT_CREATE_TABLE
ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
FOREIGN KEY (c3,c2) REFERENCES t1(c1,c2), ALGORITHM=COPY;
--error ER_FK_NO_INDEX_PARENT
ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
FOREIGN KEY (c3,c2) REFERENCES t1(c1,c2);
ALTER TABLE t2 ADD CONSTRAINT fk_t2_ca
FOREIGN KEY (c3,c2) REFERENCES t1(c2,c1);
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SHOW CREATE TABLE t1;
SHOW CREATE TABLE t2;
CREATE INDEX i_t2_c2_c1 ON t2(c2, c1);
SHOW CREATE TABLE t2;
CREATE INDEX i_t2_c3_c1_c2 ON t2(c3, c1, c2);
SHOW CREATE TABLE t2;
CREATE INDEX i_t2_c3_c2 ON t2(c3, c2);
SHOW CREATE TABLE t2;
--disable_info
DROP TABLE t2;
DROP TABLE t1;
connect (a,localhost,root,,);
connect (b,localhost,root,,);
connection a;
CREATE TABLE t1 (a INT, b CHAR(1)) ENGINE=InnoDB;
INSERT INTO t1 VALUES (3,'a'),(3,'b'),(1,'c'),(0,'d'),(1,'e');
CREATE TABLE t2 (a INT, b CHAR(1)) ENGINE=InnoDB;
CREATE TABLE t2i (a INT, b CHAR(1) NOT NULL) ENGINE=InnoDB;
CREATE TABLE t2c (a INT, b CHAR(1) NOT NULL) ENGINE=InnoDB;
INSERT INTO t2 SELECT * FROM t1;
INSERT INTO t2i SELECT * FROM t1;
INSERT INTO t2c SELECT * FROM t1;
connection b;
BEGIN;
# This acquires a MDL lock on t1 until commit.
SELECT * FROM t1;
connection a;
# This times out before of the MDL lock held by connection b.
SET lock_wait_timeout=1;
--error ER_LOCK_WAIT_TIMEOUT
CREATE INDEX t1a ON t1(a);
--enable_info
CREATE INDEX t2a ON t2(a);
--disable_info
set @old_sql_mode = @@sql_mode;
# NULL -> NOT NULL only allowed INPLACE if strict sql_mode is on.
# And adding a PRIMARY KEY will also add NOT NULL implicitly!
set @@sql_mode = 'STRICT_TRANS_TABLES';
--enable_info
ALTER TABLE t2i ADD PRIMARY KEY(a,b), ADD INDEX t2a(a), ALGORITHM=INPLACE;
--disable_info
set @@sql_mode = @old_sql_mode;
--enable_info
ALTER TABLE t2c ADD PRIMARY KEY(a,b), ADD INDEX t2a(a), ALGORITHM=COPY;
--disable_info
connection b;
# t2i and t2c are too new for this transaction, because they were rebuilt
--error ER_TABLE_DEF_CHANGED
SELECT * FROM t2i;
--error ER_TABLE_DEF_CHANGED
SELECT * FROM t2i FORCE INDEX(t2a) ORDER BY a;
--error ER_TABLE_DEF_CHANGED
SELECT * FROM t2c;
--error ER_TABLE_DEF_CHANGED
SELECT * FROM t2c FORCE INDEX(t2a) ORDER BY a;
# In t2, only the new index t2a is too new for this transaction.
SELECT * FROM t2;
--error ER_TABLE_DEF_CHANGED
SELECT * FROM t2 FORCE INDEX(t2a) ORDER BY a;
SELECT * FROM t2;
COMMIT;
# For a new transaction, all of t2, t2i, t2c are accessible.
SELECT * FROM t2;
SELECT * FROM t2 FORCE INDEX(t2a) ORDER BY a;
SELECT * FROM t2i;
SELECT * FROM t2i FORCE INDEX(t2a) ORDER BY a;
SELECT * FROM t2c;
SELECT * FROM t2c FORCE INDEX(t2a) ORDER BY a;
connection default;
disconnect a;
disconnect b;
--error ER_DUP_KEYNAME
alter table t2 add index t2a(b);
alter table t2 drop index t2a, add index t2a(b);
show create table t2;
show create table t2i;
show create table t2c;
--disable_info
DROP TABLE t1,t2,t2c,t2i;
CREATE TABLE t1 (c VARCHAR(1024),
c1 CHAR(255) NOT NULL,c2 CHAR(255) NOT NULL,c3 CHAR(255) NOT NULL,
c4 CHAR(255) NOT NULL,c5 CHAR(255) NOT NULL,c6 CHAR(255) NOT NULL,
c7 CHAR(255) NOT NULL,c8 CHAR(255) NOT NULL,c9 CHAR(255) NOT NULL,
ca CHAR(255) NOT NULL,cb CHAR(255) NOT NULL,cc CHAR(255) NOT NULL,
cd CHAR(255) NOT NULL,ce CHAR(255) NOT NULL,cf CHAR(255) NOT NULL,
d0 CHAR(255) NOT NULL,d1 CHAR(255) NOT NULL,d2 CHAR(255) NOT NULL,
d3 CHAR(255) NOT NULL,d4 CHAR(255) NOT NULL,d5 CHAR(255) NOT NULL,
d6 CHAR(255) NOT NULL,d7 CHAR(255) NOT NULL,d8 CHAR(255) NOT NULL,
d9 CHAR(255) NOT NULL,da CHAR(255) NOT NULL,db CHAR(255) NOT NULL,
dc CHAR(255) NOT NULL,dd CHAR(255) NOT NULL,de CHAR(255) NOT NULL,
UNIQUE KEY(c))
ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
INSERT INTO t1 VALUES
(repeat('a',999),'','','','','','','','','','','','','','','','','','','','','','','','','','','','','',''),
(CONCAT(repeat('a',999),'b'),'','','','','','','','','','','','','','','','','','','','','','','','','','','','','','');
--error ER_INDEX_COLUMN_TOO_LONG
ALTER TABLE t1 ROW_FORMAT=REDUNDANT, algorithm=inplace;
--error ER_INDEX_COLUMN_TOO_LONG
ALTER TABLE t1 ROW_FORMAT=REDUNDANT, algorithm=copy;
SELECT COUNT(*) FROM t1;
CHECK TABLE t1;
DROP TABLE t1;
--echo #
--echo # Bug#19811005 ALTER TABLE ADD INDEX DOES NOT UPDATE INDEX_LENGTH
--echo # IN I_S TABLES
--echo #
let $i_s_query=SELECT cast(DATA_LENGTH/@@innodb_page_size as int) D,
cast(INDEX_LENGTH/@@innodb_page_size as int) I
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test';
CREATE TABLE t1(a INT, b INT) ENGINE=INNODB, STATS_PERSISTENT=1;
eval $i_s_query;
--enable_info
ALTER TABLE t1 ADD INDEX (a);
--disable_info
eval $i_s_query;
--enable_info
ALTER TABLE t1 ADD INDEX (b);
--disable_info
eval $i_s_query;
DROP TABLE t1;
--echo #
--echo # Bug #17657223 EXCESSIVE TEMPORARY FILE USAGE IN ALTER TABLE
--echo #
SET GLOBAL innodb_monitor_enable = module_ddl;
let $innodb_metrics_select=
SELECT name, count_reset FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
subsystem = 'ddl' and count_reset > 0;
# Table with Blob data.
create table t1(f1 int not null, f2 blob)engine=innodb;
insert into t1 values(1, repeat('a',20000));
--echo # Skip sort
--echo # Reusing the same pk
--enable_info
alter table t1 force;
--disable_info
eval $innodb_metrics_select;
SET GLOBAL innodb_monitor_reset = module_ddl;
drop table t1;
# Table with small data.
create table t1(f1 int not null, f2 int not null,
primary key(f1))engine=innodb;
insert into t1 values(1,2), (3,4);
--echo # Add Secondary index.
--echo # Skip temp file usage due to small table size
--enable_info
alter table t1 add key(f2);
--disable_info
eval $innodb_metrics_select;
SET GLOBAL innodb_monitor_reset = module_ddl;
drop table t1;
# Table with large data which is greater than sort buffer
create table t480(a serial)engine=innodb;
insert into t480
values(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),
(),(),(),(),(),(),(),();
insert into t480 select 0 from t480;
insert into t480 select 0 from t480;
insert into t480 select 0 from t480;
insert into t480 select 0 from t480;
create table t1(f1 int auto_increment not null,
f2 char(200) not null, f3 char(200) not null,
f4 char(200) not null,primary key(f1))engine=innodb;
insert into t1 select NULL,'aaa','bbb','ccc' from t480;
insert into t1 select NULL,'aaaa','bbbb','cccc' from t480;
insert into t1 select NULL,'aaaaa','bbbbb','ccccc' from t480;
insert into t1 select NULL,'aaaaaa','bbbbbb','cccccc' from t480;
insert into t1 select NULL,'aaaaaaa','bbbbbbb','ccccccc' from t480;
insert into t1 select NULL,'aaaaaaaa','bbbbbbbb','cccccccc' from t480;
select count(*) from t1;
--echo # Skip sort
--echo # Change PK from (f1) to (f1,f2,f3,f4)
--enable_info
alter table t1 drop primary key, add primary key(f1,f2,f3,f4);
--disable_info
eval $innodb_metrics_select;
SET GLOBAL innodb_monitor_reset = module_ddl;
# Temp file not used during clustered index rebuild
# for the following alter table commands.
--echo # Skip sort
--echo # Change PK from (f1,f2,f3,f4) to (f1,f2,added_columns)
--enable_info
alter table t1 drop primary key,add column f5 int not null,
add column f6 int not null,add primary key(f1,f2,f5,f6);
--disable_info
eval $innodb_metrics_select;
SET GLOBAL innodb_monitor_reset = module_ddl;
--echo # Skip sort
--echo # Change PK from (f1,f2,f5,f6) to (f1,f2,f5)
--error ER_KEY_COLUMN_DOES_NOT_EXIST
alter table t1 drop column f6;
--enable_info
alter table t1 drop column f6, drop primary key, add primary key(f1,f2,f5);
--disable_info
eval $innodb_metrics_select;
SET GLOBAL innodb_monitor_reset = module_ddl;
--echo # Skip sort
--echo # Reusing the same PK
--enable_info
alter table t1 add column f6 int;
--disable_info
eval $innodb_metrics_select;
SET GLOBAL innodb_monitor_reset = module_ddl;
--echo # Skip sort
--echo # Reusing the same pk
--enable_info
alter table t1 drop column f6;
--disable_info
eval $innodb_metrics_select;
SET GLOBAL innodb_monitor_reset = module_ddl;
--echo # Must sort
--echo # Change PK from (f1,f2,f5) to (f1,f5)
--error ER_KEY_COLUMN_DOES_NOT_EXIST
alter table t1 drop column f2;
--enable_info
alter table t1 drop column f2, drop primary key, add primary key(f1,f5);
--disable_info
eval $innodb_metrics_select;
SET GLOBAL innodb_monitor_reset = module_ddl;
--echo # Skip sort
--echo # Reusing the same pk
--enable_info
alter table t1 add column f2n int after f1, drop primary key, add
primary key (f1,f5,f2n);
--disable_info
eval $innodb_metrics_select;
SET GLOBAL innodb_monitor_reset = module_ddl;
--echo # Skip sort
--echo # Reusing the same pk
--enable_info
alter table t1 change f5 f2n int not null,change f2n f5 int not null,
add column f8 int not null;
--disable_info
eval $innodb_metrics_select;
SET GLOBAL innodb_monitor_reset = module_ddl;
--echo # Skip sort
--echo # Change PK from (f1,f4,f2n) to (f1,f4,added_column,f2n)
--enable_info
alter table t1 add column f7 int, drop primary key,
add primary key (f1,f5,f7,f2n);
--disable_info
eval $innodb_metrics_select;
SET GLOBAL innodb_monitor_reset = module_ddl;
--echo # Skip sort
--echo # Reusing the same pk
--enable_info
alter table t1 force;
--disable_info
eval $innodb_metrics_select;
SET GLOBAL innodb_monitor_reset = module_ddl;
--echo # Skip sort
--echo # Reusing the same pk
--enable_info
alter table t1 row_format=compact;
--disable_info
eval $innodb_metrics_select;
SET GLOBAL innodb_monitor_reset = module_ddl;
--echo # Skip sort
--echo # Reusing the same pk
--enable_info
alter table t1 engine=innodb;
--disable_info
eval $innodb_metrics_select;
SET GLOBAL innodb_monitor_reset = module_ddl;
--echo # Skip sort
--echo # Optimize table
--enable_info
optimize table t1;
--disable_info
eval $innodb_metrics_select;
SET GLOBAL innodb_monitor_reset = module_ddl;
--echo # Sort files used for adding secondary index
--enable_info
alter table t1 drop primary key, add primary key(f1,f5,f7), add index
i(f3);
--disable_info
eval $innodb_metrics_select;
SET GLOBAL innodb_monitor_reset = module_ddl;
--echo # No sort files used for dropping secondary index
--enable_info
alter table t1 drop primary key, add primary key(f1,f5),drop index i;
--disable_info
eval $innodb_metrics_select;
SET GLOBAL innodb_monitor_reset = module_ddl;
--echo # Skip sort
--echo # Change PK(f1,f5) to (f1,added_columns) and drop f5
--enable_info
alter table t1 drop primary key, add primary key(f1,f12),
drop column f5, add column f12 int not null;
--disable_info
eval $innodb_metrics_select;
SET GLOBAL innodb_monitor_reset = module_ddl;
--echo # Must sort
--echo # Change PK(f1,f12) to (f1,existing_columns)
--enable_info
alter table t1 drop primary key, add primary key(f1,f3);
--disable_info
eval $innodb_metrics_select;
SET GLOBAL innodb_monitor_reset = module_ddl;
--echo # Skip sort
--echo # Change PK(f1,f3) to (f1,added_column,f3,added_column)
--enable_info
alter table t1 drop primary key, add column f3n int,
add column f4n int, add primary key(f1,f3n,f3,f4n);
--disable_info
eval $innodb_metrics_select;
SET GLOBAL innodb_monitor_reset = module_ddl;
--echo # Adding Secondary index alone.
--enable_info
alter table t1 add key(f1);
--disable_info
eval $innodb_metrics_select;
SET GLOBAL innodb_monitor_reset = module_ddl;
--echo # Must sort
--echo # Change PK(f1,f3) to (existing_column,f1)
--enable_info
alter table t1 drop primary key, add primary key(f4,f1);
--disable_info
eval $innodb_metrics_select;
SET GLOBAL innodb_monitor_reset = module_ddl;
--echo # Skip sort for PK.
--echo # Change PK(f4,f1) to (added_columns,f4,f1)
--echo # Secondary index rebuild happens
--enable_info
alter table t1 drop primary key, add column f5n int,
add column f6n int, add primary key(f5n,f6n,f4,f1);
--disable_info
eval $innodb_metrics_select;
SET GLOBAL innodb_monitor_reset = module_ddl;
--echo # Skip sort for PK.
--echo # Change PK(f5n,f6n,f4,f1) to
--echo # (added_columns,f5n,added_column,f6n,f4,f1)
--echo # Secondary index rebuild happens
--enable_info
alter table t1 drop primary key, add column f7n int,
add column f8n int, add primary key(f7n,f5n,f8n,f6n,f4,f1);
--disable_info
eval $innodb_metrics_select;
SET GLOBAL innodb_monitor_reset = module_ddl;
SET GLOBAL innodb_monitor_disable = module_ddl;
select count(*) from t1;
drop table t1;
--disable_warnings
SET GLOBAL innodb_monitor_reset = default;
SET GLOBAL innodb_monitor_enable = default;
SET GLOBAL innodb_monitor_disable = default;
--enable_warnings
--echo # Bug#19163915 INNODB: DUPLICATE RECORDS COULD EXIST
--echo # WHEN SKIPPING SORT FOR CLUSTER INDEX
# last mtuple in previous buffer and first mtuple in next buffer
# are equal.
SELECT @@innodb_sort_buffer_size;
create table t1(f1 int auto_increment not null,
f2 char(200) not null, f3 char(200) not null,
f4 char(200) not null,primary key(f1,f2,f3,f4));
insert into t1 select NULL,'aaa','bbb','ccc' from t480;
insert into t1 values(106, 'aaa','bbb','cccc');
select count(*) from t1;
--echo # Skip sort
--echo # Change PK from (f1,f2,f3,f4) to (f1,f2,f3)
--error ER_DUP_ENTRY
alter table t1 drop primary key, add primary key(f1,f2,f3);
select count(*) from t1;
drop table t1;
# Duplicates exist with in the buffer
create table t1(f1 int auto_increment not null,
f2 char(200) not null, f3 char(200) not null,
f4 char(200) not null,primary key(f1,f2,f3,f4));
insert into t1 select NULL,'aaa','bbb','ccc' from t480;
insert into t1 values(108,'aaa','bbb','cccc');
select count(*) from t1;
--error ER_DUP_ENTRY
alter table t1 drop primary key, add primary key(f1,f2,f3);
select count(*) from t1;
drop table t1, t480;
--echo #
--echo # Bug #19896922 SORTING SKIPPED WHEN PREFIX LENGTH OF THE PK
--echo # FIELD IS CHANGED
--echo #
# Prefix length changes for the varchar column.
create table t1(a int not null, b varchar(30) not null,
primary key (b(10), a)) engine = innodb;
insert into t1 values(0,'khdHps6UxW8Lwaoxa604oK6zkb'),(1,'khdHps6UxW8L');
select * from t1;
alter table t1 drop primary key, add primary key (b(18),a);
select * from t1;
drop table t1;
create table t1(a int not null, b varchar(30) not null,
primary key (b(10), a)) engine = innodb;
insert into t1 values(0,'khdHps6UxW8Lwaoxa604oK6zkb'),(1,'khdHps6UtW8L');
select * from t1;
alter table t1 drop primary key, add primary key (b(8),a);
select * from t1;
drop table t1;
--echo #
--echo # Bug #21103101 SORTING SKIPPED WHEN DROPPING THE SINGLE
--echo # COLUMN PRIMARY KEY
--echo #
# Drop primary key column.
create table t1(f1 int not null, f2 int not null,
primary key (f1), unique key(f1, f2))engine=innodb;
insert into t1 values(1,3), (2,2);
--error ER_KEY_COLUMN_DOES_NOT_EXIST
alter table t1 drop column f1;
--error ER_KEY_COLUMN_DOES_NOT_EXIST
alter table t1 drop column f1, drop primary key;
# DROP PRIMARY KEY is implied for a single-column PRIMARY KEY
alter table t1 drop column f1, drop key f1;
drop table t1;
# Drop Primary key when lock is none.
create table t1(f1 int not null, f2 int not null,
primary key (f1), unique key(f1, f2))engine=innodb;
insert into t1 values(1,3), (2,2);
alter table t1 drop primary key, lock=none;
--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
alter table t1 drop index f1, algorithm=inplace, lock=none;
drop table t1;
--echo #
--echo # BUG#21612714 ALTER TABLE SORTING SKIPPED WHEN CHANGE PK AND DROP
--echo # LAST COLUMN OF OLD PK
--echo #
# no skip sort cases
# pk(o1,o2) to pk(o1,o3), drop o2
create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
insert into t1 values(1,1,2),(1,2,1);
alter table t1 drop primary key, add primary key(o1,o3), drop o2, lock=none;
drop table t1;
# pk(o1,o2) to pk(o3), drop o1, o2
create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
insert into t1 values(1,1,2),(1,2,1);
alter table t1 drop o1, drop o2, add primary key(o3), lock=none;
drop table t1;
# pk(o1,o2) to pk(o1,o3)
create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
insert into t1 values(1,1,2),(1,2,1);
alter table t1 drop primary key, add primary key(o1,o3), lock=none;
drop table t1;
# pk(o1,o2) to pk(o3)
create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
insert into t1 values(1,1,2),(1,2,1);
alter table t1 drop primary key, add primary key(o3), lock=none;
drop table t1;
# pk(o1,o2) to pk(n1,o3)
create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
insert into t1 values(1,1,2),(1,2,1);
alter table t1 add column n1 int not null, drop primary key, add primary key(n1,o3), lock=none;
drop table t1;
# pk(o1,o2) to pk(o3,n1)
create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
insert into t1 values(1,1,2),(2,2,1);
alter table t1 add column n1 int not null, drop primary key, add primary key(o3,n1), lock=none;
drop table t1;
# pk(o1,o2) to pk(o2,o1)
create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
insert into t1 values(1,2,2),(2,1,1);
alter table t1 drop primary key, add primary key(o2, o1), lock=none;
drop table t1;
# pk(o1,o2) to pk(o2)
create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
insert into t1 values(1,2,2),(2,1,1);
alter table t1 drop primary key, add primary key(o2), lock=none;
drop table t1;
# pk(o1,o2) to pk(o2,o3)
create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
insert into t1 values(1,2,2),(2,1,1);
alter table t1 drop primary key, add primary key(o2,o3), lock=none;
drop table t1;
# pk(o2,o1) to pk(o2,o3)
create table t1(o1 int, o2 int, o3 int not null, primary key(o2,o1)) engine = innodb;
insert into t1 values(1,1,2),(2,1,1);
alter table t1 drop primary key, add primary key(o2,o3), lock=none;
drop table t1;
# pk(o1,o2) to pk(o1,o3,o2)
create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
insert into t1 values(1,1,2),(1,2,1);
alter table t1 drop primary key, add primary key(o1,o3,o2), lock=none;
drop table t1;
# pk(o1,o2) to pk(o3,o1,o2)
create table t1(o1 int, o2 int, o3 int not null, primary key(o1,o2)) engine = innodb;
insert into t1 values(1,2,2),(2,1,1);
alter table t1 drop primary key, add primary key(o3,o1,o2), lock=none;
drop table t1;
# pk(o1,o2,o3) to pk(o1,o3)
create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) engine = innodb;
insert into t1 values(1,1,2),(1,2,1);
alter table t1 drop primary key, add primary key(o1,o3), lock=none;
drop table t1;
# pk(o1,o2,o3) to pk(o2,o3) by drop o1
create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) engine = innodb;
insert into t1 values(1,2,2),(2,1,1);
--error ER_KEY_COLUMN_DOES_NOT_EXIST
alter table t1 drop o1, lock=none;
alter table t1 drop o1, drop primary key, add primary key(o2,o3), lock=none;
drop table t1;
# pk(o1,o2,o3) to pk(o1,o3) by drop o2
create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) engine = innodb;
insert into t1 values(1,1,2),(1,2,1);
--error ER_KEY_COLUMN_DOES_NOT_EXIST
alter table t1 drop o2, lock=none;
alter table t1 drop o2, drop primary key, add primary key(o1,o3), lock=none;
drop table t1;
# pk(o1,o2,o3) to pk(o3) by drop o1,o2
create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) engine = innodb;
insert into t1 values(1,2,2),(2,1,1);
--error ER_KEY_COLUMN_DOES_NOT_EXIST
alter table t1 drop o1, drop o2, lock=none;
alter table t1 drop o1, drop o2,drop primary key,add primary key(o3),lock=none;
drop table t1;
# no skip sort for prefix change
# pk(o1(2),o2) to pk(o1(3),o2)
create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1(2), o2)) engine = innodb;
insert into t1 values('abd', 1, 1), ('abc', 2, 2);
alter table t1 drop primary key, add primary key(o1(3), o2), lock=none;
drop table t1;
# pk(o1(2),o2) to pk(o1,o2)
create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1(2), o2)) engine = innodb;
insert into t1 values('abd', 1, 1), ('abc', 2, 2);
alter table t1 drop primary key, add primary key(o1, o2), lock=none;
drop table t1;
# pk(o1(2),o2) to pk(o1(3),o3)
create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1(2), o2)) engine = innodb;
insert into t1 values('abd', 1, 1), ('abc', 2, 2);
alter table t1 drop primary key, add primary key(o1(3), o3), lock=none;
drop table t1;
# pk(o1(2),o2) to pk(o1,o3)
create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1(2), o2)) engine = innodb;
insert into t1 values('abd', 1, 1), ('abc', 2, 2);
alter table t1 drop primary key, add primary key(o1, o3), lock=none;
drop table t1;
# pk(o1(3),o2) to pk(o1(2),o2)
create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1(3), o2)) engine = innodb;
insert into t1 values('abc', 2, 1), ('abd', 1, 2);
alter table t1 drop primary key, add primary key(o1(2), o2), lock=none;
drop table t1;
# pk(o1,o2) to pk(o1(2),o2)
create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1, o2)) engine = innodb;
insert into t1 values('abc', 2, 1), ('abd', 1, 2);
alter table t1 drop primary key, add primary key(o1(2), o2), lock=none;
drop table t1;
# pk(o1(3),o2) to pk(o1(2),o3)
create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1(3), o2)) engine = innodb;
insert into t1 values('abc', 2, 2), ('abd', 1, 1);
alter table t1 drop primary key, add primary key(o1(2), o3), lock=none;
drop table t1;
# pk(o1,o2) to pk(o1(2),o3)
create table t1(o1 varchar(10), o2 int, o3 int not null, primary key(o1, o2)) engine = innodb;
insert into t1 values('abc', 2, 2), ('abd', 1, 1);
alter table t1 drop primary key, add primary key(o1(2), o3), lock=none;
drop table t1;
# pk(o1,o2(2),o3) to pk(o1,o2(3))
create table t1(o1 int, o2 varchar(10), o3 int, primary key(o1,o2(2),o3)) engine = innodb;
insert into t1 values(1, 'abd', 1), (1, 'abc', 2);
alter table t1 drop primary key, add primary key(o1,o2(3)), lock=none;
drop table t1;
# pk(o1,o2(2),o3) to pk(o1,o2)
create table t1(o1 int, o2 varchar(10), o3 int, primary key(o1,o2(2),o3)) engine = innodb;
insert into t1 values(1, 'abd', 1), (1, 'abc', 2);
alter table t1 drop primary key, add primary key(o1,o2), lock=none;
drop table t1;
# pk(o1(3),o2(3)) to pk(o1(2),o2(3))
create table t1(o1 varchar(10), o2 varchar(10), primary key(o1(3),o2(3))) engine = innodb;
insert into t1 values('abc', 'acd'), ('abd', 'abd');
alter table t1 drop primary key, add primary key(o1(2),o2(3)), lock=none;
drop table t1;
# pk(o1,o2) to pk(o1(2),o2)
create table t1(o1 varchar(10), o2 varchar(10), primary key(o1,o2)) engine = innodb;
insert into t1 values('abc', 'acd'), ('abd', 'abd');
alter table t1 drop primary key, add primary key(o1(2),o2), lock=none;
drop table t1;
# pk(o1(3),o2(3)) to pk(o2(3),o1(3))
create table t1(o1 varchar(10), o2 varchar(10), primary key(o1(3),o2(3))) engine = innodb;
insert into t1 values('abd', 'acd'), ('acd', 'abd');
alter table t1 drop primary key, add primary key(o2(3),o1(3)), lock=none;
drop table t1;
# pk(o1,o2) to pk(o2,o1)
create table t1(o1 varchar(10), o2 varchar(10), primary key(o1,o2)) engine = innodb;
insert into t1 values('abd', 'acd'), ('acd', 'abd');
alter table t1 drop primary key, add primary key(o2,o1), lock=none;
drop table t1;
# no skip sort cases
--source suite/innodb/include/alter_table_pk_no_sort.inc
--echo #
--echo # MDEV-15325 Incomplete validation of missing tablespace during recovery
--echo #
--source include/no_checkpoint_start.inc
CREATE TABLE t1(f1 INT PRIMARY KEY)ENGINE=InnoDB;
CREATE TABLE t2(f1 INT PRIMARY KEY)ENGINE=InnoDB;
--let CLEANUP_IF_CHECKPOINT=DROP TABLE t1, t2;
--source include/no_checkpoint_end.inc
let SEARCH_FILE= $MYSQLTEST_VARDIR/log/mysqld.1.err;
let $check_no_innodb=SELECT * FROM INFORMATION_SCHEMA.ENGINES
WHERE engine = 'innodb'
AND support IN ('YES', 'DEFAULT', 'ENABLED');
--echo # Wrong space_id in a dirty file and a missing file
--copy_file $MYSQLD_DATADIR/test/t1.ibd $MYSQLD_DATADIR/test/t0.ibd
--move_file $MYSQLD_DATADIR/test/t2.ibd $MYSQLD_DATADIR/test/t1.ibd
--source include/start_mysqld.inc
--eval $check_no_innodb
--source include/shutdown_mysqld.inc
--echo # Restore t1 and t2
--move_file $MYSQLD_DATADIR/test/t1.ibd $MYSQLD_DATADIR/test/t2.ibd
--move_file $MYSQLD_DATADIR/test/t0.ibd $MYSQLD_DATADIR/test/t1.ibd
--source include/start_mysqld.inc
SELECT * FROM t1;
SELECT * FROM t2;
DROP TABLE t1, t2;
--echo #
--echo # MDEV-18186 assertion failure on missing InnoDB index
--echo #
--disable_query_log
call mtr.add_suppression("Cannot find index i1 in InnoDB index dictionary");
call mtr.add_suppression("InnoDB indexes are inconsistent with what defined");
call mtr.add_suppression("Table test/t contains 0 indexes");
call mtr.add_suppression("InnoDB could not find key no");
--enable_query_log
# Test an attempt to rename a nonexistent index inside InnoDB
-- let $MYSQL_DATA_DIR = `SELECT @@datadir`
CREATE TABLE t (a INT, INDEX i1 (a)) ENGINE=INNODB;
-- copy_file $MYSQL_DATA_DIR/test/t.frm $MYSQL_DATA_DIR/test/t.fr_
DROP TABLE t;
CREATE TABLE t (a INT) ENGINE=INNODB;
-- remove_file $MYSQL_DATA_DIR/test/t.frm
-- move_file $MYSQL_DATA_DIR/test/t.fr_ $MYSQL_DATA_DIR/test/t.frm
--enable_prepare_warnings
SHOW CREATE TABLE t;
--disable_prepare_warnings
DROP TABLE t;
--echo #
--echo # MDEV-27374 InnoDB table becomes corrupt after renaming DESC-indexed column
--echo #
CREATE TABLE t1 (a VARCHAR(8), PRIMARY KEY(a DESC)) ENGINE=InnoDB;
ALTER TABLE t1 RENAME COLUMN a TO b, ALGORITHM=INPLACE;
--disable_cursor_protocol
SELECT TABLE_ID INTO @table_id FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME="test/t1";
SELECT INDEX_ID INTO @index_id FROM INFORMATION_SCHEMA.INNODB_SYS_INDEXES WHERE TABLE_ID = @table_id;
--enable_cursor_protocol
SELECT NAME FROM INFORMATION_SCHEMA.INNODB_SYS_FIELDS WHERE INDEX_ID=@index_id;
DROP TABLE t1;
--echo #
--echo # MDEV-27432 ASC/DESC primary and unique keys cause index
--echo # inconsistency between InnoDB and server
--echo #
CREATE TABLE t1 (id INT NOT NULL, UNIQUE(id DESC)) ENGINE=InnoDB;
ALTER TABLE t1 ADD PRIMARY KEY (id), ALGORITHM=INPLACE;
SHOW CREATE TABLE t1;
DROP TABLE t1;
--echo #
--echo # MDEV-27445 Index inconsistency and assertion failure after
--echo # renaming virtual column with DESC key
--echo #
CREATE TABLE t1(a INT, b INT AS (a), KEY(a, b DESC)) ENGINE=InnoDB;
ALTER TABLE t1 RENAME COLUMN IF EXISTS b TO v;
ALTER TABLE t1 FORCE;
DROP TABLE t1;
--echo #
--echo # MDEV-27592 DESC primary index fails to set wide format
--echo # while renaming the column
--echo #
CREATE TABLE t1 (id INT PRIMARY KEY, a CHAR(8), b INT, KEY(a DESC,b)) ENGINE=InnoDB;
INSERT INTO t1 VALUES (1,'foo',10);
ALTER TABLE t1 RENAME COLUMN b TO c, ALGORITHM=INPLACE;
--disable_cursor_protocol
SELECT TABLE_ID INTO @table_id FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME="test/t1";
SELECT INDEX_ID INTO @index_id FROM INFORMATION_SCHEMA.INNODB_SYS_INDEXES WHERE TABLE_ID = @table_id ORDER BY INDEX_ID DESC LIMIT 1;
--enable_cursor_protocol
SELECT NAME FROM INFORMATION_SCHEMA.INNODB_SYS_FIELDS WHERE INDEX_ID=@index_id;
DROP TABLE t1;
--echo # End of 10.8 tests
--echo #
--echo # MDEV-35849: index records in a wrong order
--echo #
CREATE TABLE t (pk INT PRIMARY KEY, a VARCHAR(1), KEY (a DESC)) ENGINE=InnoDB;
INSERT INTO t VALUES (1,NULL),(2,'x');
CHECK TABLE t;
DROP TABLE t;
--echo # End of 11.8 tests
--disable_query_log
call mtr.add_suppression("InnoDB: Tablespace .* was not found at .*t[12].ibd.");
call mtr.add_suppression("InnoDB: Plugin initialization aborted");
call mtr.add_suppression("Plugin 'InnoDB' init function returned error");
call mtr.add_suppression("Plugin 'InnoDB' registration as a STORAGE ENGINE failed");
call mtr.add_suppression("Fewer engines are enabled now than were before the crash");
--enable_query_log
--source include/test_db_charset_restore.inc
Reference in a new issue View git blame Copy permalink