diff options
Diffstat (limited to 'mysql-test/suite/innodb/include')
32 files changed, 1644 insertions, 0 deletions
diff --git a/mysql-test/suite/innodb/include/alter_table_pk_no_sort.inc b/mysql-test/suite/innodb/include/alter_table_pk_no_sort.inc new file mode 100644 index 00000000..61e304a7 --- /dev/null +++ b/mysql-test/suite/innodb/include/alter_table_pk_no_sort.inc @@ -0,0 +1,272 @@ +# skip sort for prefix change +# pk(o1(2)) to pk(o1(3)) +create table t1(o1 varchar(10), primary key(o1(2))) engine = innodb; +insert into t1 values('abd'), ('acd'); +alter table t1 drop primary key, add primary key(o1(3)), lock=none; +drop table t1; + +# pk(o1(2)) to pk(o1) +create table t1(o1 varchar(10), primary key(o1(2))) engine = innodb; +insert into t1 values('abd'), ('acd'); +alter table t1 drop primary key, add primary key(o1), lock=none; +drop table t1; + +# pk(o1(2)) to pk(o1(3),n1) +create table t1(o1 varchar(10), primary key(o1(2))) engine = innodb; +insert into t1 values('abd'), ('acd'); +alter table t1 add n1 int not null, drop primary key, add primary key(o1(3), n1), lock=none; +drop table t1; + +# pk(o1(2)) to pk(o1,n1) +create table t1(o1 varchar(10), primary key(o1(2))) engine = innodb; +insert into t1 values('abd'), ('acd'); +alter table t1 add n1 int not null, drop primary key, add primary key(o1, n1), lock=none; +drop table t1; + +# pk(o1(2)) to pk(o1(3), o2) +create table t1(o1 varchar(10), o2 int not null, primary key(o1(2))) engine = innodb; +insert into t1 values('abd', 1), ('acd', 2); +alter table t1 add n1 int not null, drop primary key, add primary key(o1(3), o2), lock=none; +drop table t1; + +# pk(o1(2)) to pk(o1, o2) +create table t1(o1 varchar(10), o2 int not null, primary key(o1(2))) engine = innodb; +insert into t1 values('abd', 1), ('acd', 2); +alter table t1 add n1 int not null, drop primary key, add primary key(o1, o2), lock=none; +drop table t1; + +# pk(o1(3)) to pk(o1(2)) +create table t1(o1 varchar(10), primary key(o1(3))) engine = innodb; +insert into t1 values('abd'), ('acd'); +alter table t1 drop primary key, add primary key(o1(2)), lock=none; +drop table t1; + +# pk(o1) to pk(o1(2)) +create table t1(o1 varchar(10), primary key(o1)) engine = innodb; +insert into t1 values('abd'), ('acd'); +alter table t1 drop primary key, add primary key(o1(2)), lock=none; +drop table t1; + +# pk(o1(3),o2) to pk(o1(2)) +create table t1(o1 varchar(10), o2 int, primary key(o1(3), o2)) engine = innodb; +insert into t1 values('abd', 1), ('acd', 2); +alter table t1 drop primary key, add primary key(o1(2)), lock=none; +drop table t1; + +# pk(o1,o2) to pk(o1(2)) +create table t1(o1 varchar(10), o2 int, primary key(o1, o2)) engine = innodb; +insert into t1 values('abd', 1), ('acd', 2); +alter table t1 drop primary key, add primary key(o1(2)), lock=none; +drop table t1; + +# pk(o1(3),o2) to pk(o1(2),n1) +create table t1(o1 varchar(10), o2 int, primary key(o1(3), o2)) engine = innodb; +insert into t1 values('abd', 1), ('acd', 2); +alter table t1 add n1 int not null, drop primary key, add primary key(o1(2),n1), lock=none; +drop table t1; + +# pk(o1,o2) to pk(o1(2),n1) +create table t1(o1 varchar(10), o2 int, primary key(o1, o2)) engine = innodb; +insert into t1 values('abd', 1), ('acd', 2); +alter table t1 add n1 int not null, drop primary key, add primary key(o1(2),n1), lock=none; +drop table t1; + +# pk(o1(3),o2) to pk(o1(3),n1) +create table t1(o1 varchar(10), o2 int, primary key(o1(3), o2)) engine = innodb; +insert into t1 values('abd', 1), ('acd', 2); +alter table t1 add n1 int not null, drop primary key, add primary key(o1(3),n1), lock=none; +drop table t1; + +# pk(o1,o2) to pk(o1,n1) +create table t1(o1 varchar(10), o2 int, primary key(o1, o2)) engine = innodb; +insert into t1 values('abd', 1), ('acd', 2); +alter table t1 add n1 int not null, drop primary key, add primary key(o1,n1), lock=none; +drop table t1; + +# pk(o1,o2(3)) to pk(o1,o2(2)) +create table t1(o1 int, o2 varchar(10), primary key(o1,o2(3))) engine = innodb; +insert into t1 values(1,'abd'), (2,'acd'); +alter table t1 drop primary key, add primary key(o1,o2(2)), lock=none; +drop table t1; + +# pk(o1,o2) to pk(o1,o2(2)) +create table t1(o1 int, o2 varchar(10), primary key(o1,o2)) engine = innodb; +insert into t1 values(1,'abd'), (2,'acd'); +alter table t1 drop primary key, add primary key(o1,o2(2)), lock=none; +drop table t1; + +# pk(o1,o2(2)) to pk(o1,o2(3)) +create table t1(o1 int, o2 varchar(10), primary key(o1,o2(2))) engine = innodb; +insert into t1 values(1, 'abd'), (2, 'acd'); +alter table t1 drop primary key, add primary key(o1,o2(3)), lock=none; +drop table t1; + +# pk(o1,o2(2)) to pk(o1,o2) +create table t1(o1 int, o2 varchar(10), primary key(o1,o2(2))) engine = innodb; +insert into t1 values(1, 'abd'), (2, 'acd'); +alter table t1 drop primary key, add primary key(o1,o2), lock=none; +drop table t1; + +# pk(o1,o2(3),o3) to pk(o1,o2(2)) +create table t1(o1 int, o2 varchar(10), o3 int, primary key(o1,o2(3),o3)) engine = innodb; +insert into t1 values(1, 'abd', 1), (2, 'acd', 2); +alter table t1 drop primary key, add primary key(o1,o2(2)), lock=none; +drop table t1; + +# pk(o1,o2,o3) to pk(o1,o2(2)) +create table t1(o1 int, o2 varchar(10), o3 int, primary key(o1,o2,o3)) engine = innodb; +insert into t1 values(1, 'abd', 1), (2, 'acd', 2); +alter table t1 drop primary key, add primary key(o1,o2(2)), lock=none; +drop table t1; + +# pk(o1(3),o2(3)) to pk(o1(3),o2(2)) +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(o1(3),o2(2)), lock=none; +drop table t1; + +# pk(o1,o2) to pk(o1,o2(2)) +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(o1,o2(2)), lock=none; +drop table t1; + +# pk(o1(3),o2(2)) to pk(o1(3),o2(3)) +create table t1(o1 varchar(10), o2 varchar(10), primary key(o1(3),o2(2))) engine = innodb; +insert into t1 values('abd', 'acd'), ('acd', 'abd'); +alter table t1 drop primary key, add primary key(o1(3),o2(3)), lock=none; +drop table t1; + +# pk(o1,o2(2)) to pk(o1,o2) +create table t1(o1 varchar(10), o2 varchar(10), primary key(o1,o2(2))) engine = innodb; +insert into t1 values('abd', 'acd'), ('acd', 'abd'); +alter table t1 drop primary key, add primary key(o1,o2), lock=none; +drop table t1; + +# pk(o1(3),o2,o3(2)) to pk(o1(3),o2,o3(3)) +create table t1(o1 varchar(10), o2 int, o3 varchar(10), primary key(o1(3),o2,o3(2))) engine = innodb; +insert into t1 values('abd', 1, 'acd'), ('acd', 2, 'abd'); +alter table t1 drop primary key, add primary key(o1(3),o2,o3(3)), lock=none; +drop table t1; + +# pk(o1,o2,o3(2)) to pk(o1,o2,o3) +create table t1(o1 varchar(10), o2 int, o3 varchar(10), primary key(o1,o2,o3(2))) engine = innodb; +insert into t1 values('abd', 1, 'acd'), ('acd', 2, 'abd'); +alter table t1 drop primary key, add primary key(o1,o2,o3), lock=none; +drop table t1; + +# pk(o1(3),o2,o3(3)) to pk(o1(3),o2,o3(2)) +create table t1(o1 varchar(10), o2 int, o3 varchar(10), primary key(o1(3),o2,o3(3))) engine = innodb; +insert into t1 values('abd', 1, 'acd'), ('acd', 2, 'abd'); +alter table t1 drop primary key, add primary key(o1(3),o2,o3(2)), lock=none; +drop table t1; + +# pk(o1,o2,o3) to pk(o1,o2,o3(2)) +create table t1(o1 varchar(10), o2 int, o3 varchar(10), primary key(o1,o2,o3(3))) engine = innodb; +insert into t1 values('abd', 1, 'acd'), ('acd', 2, 'abd'); +alter table t1 drop primary key, add primary key(o1,o2,o3(2)), lock=none; +drop table t1; + +# skip sort for adding existing columns/newly added columns, dropping pk columns at the end. +# pk(o1) to pk(o1,o2) +create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb; +insert into t1 values(1,1),(2,2); +alter table t1 drop primary key, add primary key(o1,o2), lock=none; +drop table t1; + +# pk(o1) to pk(o1,n1) +create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb; +insert into t1 values(1,1),(2,2); +alter table t1 add n1 int not null, drop primary key, add primary key(o1,n1), lock=none; +drop table t1; + +# pk(o1) to pk(n1,o1) +create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb; +insert into t1 values(1,1),(2,2); +alter table t1 add n1 int not null, drop primary key, add primary key(n1,o1), lock=none; +drop table t1; + +# pk(o1) to pk(n1,o1,n2) +create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb; +insert into t1 values(1,1),(2,2); +alter table t1 add n1 int not null, add n2 int not null, drop primary key, add primary key(n1,o1,n2), lock=none; +drop table t1; + +# pk(o1) to pk(n1,n2,o1) +create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb; +insert into t1 values(1,1),(2,2); +alter table t1 add n1 int not null, add n2 int not null, drop primary key, add primary key(n1,n2,o1), lock=none; +drop table t1; + +# pk(o1) to pk(o1,n1,n2) +create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb; +insert into t1 values(1,1),(2,2); +alter table t1 add n1 int not null, add n2 int not null, drop primary key, add primary key(o1,n1,n2), lock=none; +drop table t1; + +# pk(o1) to pk(o1,o2,n1) +create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb; +insert into t1 values(1,1),(2,2); +alter table t1 add n1 int not null, drop primary key, add primary key(o1,o2,n1), lock=none; +drop table t1; + +# pk(o1) to pk(o1,n1,o2) +create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb; +insert into t1 values(1,1),(2,2); +alter table t1 add n1 int not null, drop primary key, add primary key(o1,n1,o2), lock=none; +drop table t1; + +# pk(o1) to pk(n1,o1,o2) +create table t1(o1 int, o2 int not null, primary key(o1)) engine = innodb; +insert into t1 values(1,1),(2,2); +alter table t1 add n1 int not null, drop primary key, add primary key(n1,o1,o2), lock=none; +drop table t1; + +# pk(o1) to pk(o1,o2,o3) +create table t1(o1 int, o2 int not null, o3 int not null, primary key(o1)) engine = innodb; +insert into t1 values(1,1,2),(2,2,1); +alter table t1 drop primary key, add primary key(o1,o2,o3), lock=none; +drop table t1; + +# pk(o1) to pk(o1,o3,o2) +create table t1(o1 int, o2 int not null, o3 int not null, primary key(o1)) engine = innodb; +insert into t1 values(1,1,2),(2,2,1); +alter table t1 drop primary key, add primary key(o1,o3,o2), lock=none; +drop table t1; + +# pk(o1,o2,o3) to pk(o1,o2) +create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) engine = innodb; +insert into t1 values(1,1,2),(2,2,1); +alter table t1 drop primary key, add primary key(o1,o2), lock=none; +drop table t1; + +# pk(o1,o2,o3) to pk(o1,o2,o3,o4) +create table t1(o1 int, o2 int, o3 int, o4 int not null, primary key(o1,o2,o3)) engine = innodb; +insert into t1 values(1,1,2,2),(2,2,1,1); +alter table t1 add n1 int not null, drop primary key, add primary key(o1,o2,o3,o4), lock=none; +drop table t1; + +# pk(o1,o2,o3) to pk(o1,o2,n1) +create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) engine = innodb; +insert into t1 values(1,1,2),(2,2,1); +alter table t1 add n1 int not null, drop primary key, add primary key(o1,o2,n1), lock=none; +drop table t1; + +# pk(o1,o2,o3) to pk(o1,n1,o2) +create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) engine = innodb; +insert into t1 values(1,1,2),(2,2,1); +alter table t1 add n1 int not null, drop primary key, add primary key(o1,n1,o2), lock=none; +drop table t1; + +# pk(o1,o2,o3) to pk(o1) +create table t1(o1 int, o2 int, o3 int, primary key(o1,o2,o3)) engine = innodb; +insert into t1 values(1,1,2),(2,2,1); +alter table t1 drop primary key, add primary key(o1), lock=none; +drop table t1; + +# pk(o1,o2) to pk(o1,o2,autoinc) must not sort +create table t1(o1 int, o2 int, primary key(o1,o2)) engine = innodb; +insert into t1 values(1,1),(2,1); +alter table t1 drop primary key, add column a int unique auto_increment, +add primary key(o1,o2,a), algorithm=inplace; +drop table t1; diff --git a/mysql-test/suite/innodb/include/autoinc_persist_alter.inc b/mysql-test/suite/innodb/include/autoinc_persist_alter.inc new file mode 100644 index 00000000..bbf5f265 --- /dev/null +++ b/mysql-test/suite/innodb/include/autoinc_persist_alter.inc @@ -0,0 +1,57 @@ + +eval CREATE TABLE $table LIKE $template; + +eval INSERT INTO $table SELECT * FROM $template; + +eval SELECT * FROM $table; + +eval SHOW CREATE TABLE $table; + +--echo # This will keep the autoinc counter +eval ALTER TABLE $table AUTO_INCREMENT = 250, ALGORITHM = $algorithm; +--echo # We expect the counter to be 250 +eval SHOW CREATE TABLE $table; + +--echo # This should keep the autoinc counter as well +eval ALTER TABLE $table ADD COLUMN b INT, ALGORITHM = $algorithm; +--echo # We expect the counter to be 250 +eval SHOW CREATE TABLE $table; + +eval DELETE FROM $table WHERE a > 150; + +eval SELECT * FROM $table; + +--echo # This should reset the autoinc counter to the one specified +--echo # Since it's smaller than current one but bigger than existing +--echo # biggest counter in the table +eval ALTER TABLE $table AUTO_INCREMENT = 180, ALGORITHM = $algorithm; +--echo # We expect the counter to be 180 +eval SHOW CREATE TABLE $table; + +--echo # This should reset the autoinc counter to the next value of +--echo # current max counter in the table, since the specified value +--echo # is smaller than the existing biggest value(50 < 123) +eval ALTER TABLE $table DROP COLUMN b, AUTO_INCREMENT = 50, ALGORITHM = $algorithm; +--echo # We expect the counter to be 123 +eval SHOW CREATE TABLE $table; + +eval INSERT INTO $table VALUES(0), (0); + +eval SELECT MAX(a) AS `Expect 124` FROM $table; + +eval OPTIMIZE TABLE $table; + +eval DELETE FROM $table WHERE a >= 123; + +eval CREATE TABLE i$table(a INT AUTO_INCREMENT, INDEX(a)) AUTO_INCREMENT=125 ENGINE=InnoDB; +eval CREATE UNIQUE INDEX idx_aa ON i$table(a); + +--source include/restart_mysqld.inc + +eval INSERT INTO $table VALUES(0), (0); +eval INSERT INTO i$table VALUES(0), (0); + +eval SELECT MAX(a) AS `Expect 126` FROM $table; +eval SELECT MAX(a) AS `Expect 126` FROM i$table; + +eval DROP TABLE $table, i$table; diff --git a/mysql-test/suite/innodb/include/crc32.pl b/mysql-test/suite/innodb/include/crc32.pl new file mode 100644 index 00000000..c2bce09d --- /dev/null +++ b/mysql-test/suite/innodb/include/crc32.pl @@ -0,0 +1,33 @@ +# The following is Public Domain / Creative Commons CC0 from +# http://billauer.co.il/blog/2011/05/perl-crc32-crc-xs-module/ + +sub mycrc32 { + my ($input, $init_value, $polynomial) = @_; + + $init_value = 0 unless (defined $init_value); + $polynomial = 0xedb88320 unless (defined $polynomial); + + my @lookup_table; + + for (my $i=0; $i<256; $i++) { + my $x = $i; + for (my $j=0; $j<8; $j++) { + if ($x & 1) { + $x = ($x >> 1) ^ $polynomial; + } else { + $x = $x >> 1; + } + } + push @lookup_table, $x; + } + + my $crc = $init_value ^ 0xffffffff; + + foreach my $x (unpack ('C*', $input)) { + $crc = (($crc >> 8) & 0xffffff) ^ $lookup_table[ ($crc ^ $x) & 0xff ]; + } + + $crc = $crc ^ 0xffffffff; + + return $crc; +} diff --git a/mysql-test/suite/innodb/include/dml_ops.inc b/mysql-test/suite/innodb/include/dml_ops.inc new file mode 100644 index 00000000..4908dfb6 --- /dev/null +++ b/mysql-test/suite/innodb/include/dml_ops.inc @@ -0,0 +1,82 @@ +delimiter |; +create procedure populate_t1() +begin + declare i int default 1; + while (i <= 200) do + insert into t1 values (i, 'a', 'b'); + set i = i + 1; + end while; +end| +create procedure populate_t1_small() +begin + declare i int default 1; + while (i <= 20) do + insert into t1 values (i, 'c', 'd'); + set i = i + 1; + end while; +end| +create procedure populate_t1_small2() +begin + declare i int default 30; + while (i <= 50) do + insert into t1 values (i, 'e', 'f'); + set i = i + 1; + end while; +end| +delimiter ;| +# +begin; +select count(*) from t1; +call populate_t1(); +select count(*) from t1; +select * from t1 limit 10; +rollback; +select count(*) from t1; +# +begin; +call populate_t1(); +select count(*) from t1; +commit; +select count(*) from t1; +# +truncate table t1; +select count(*) from t1; +# +call populate_t1_small(); +select count(*) from t1; +rollback; +select count(*) from t1; +truncate table t1; +# +call populate_t1(); +select count(*) from t1; +delete from t1 where keyc <= 60; +select count(*) from t1; +call populate_t1_small(); +select count(*) from t1; +select * from t1 limit 10; +begin; +call populate_t1_small2(); +select count(*) from t1; +select * from t1 where keyc > 30 limit 10; +rollback; +select count(*) from t1; +select * from t1 where keyc > 30 limit 10; +# +update t1 set keyc = keyc + 2000; +select * from t1 limit 10; +rollback; +begin; +update t1 set keyc = keyc + 2000; +select * from t1 limit 10; +rollback; +select * from t1 limit 10; +commit; +select * from t1 limit 10; +# +insert into t2 select * from t1 where keyc < 2101; +select count(*) from t2; +# +drop procedure populate_t1; +drop procedure populate_t1_small; +drop procedure populate_t1_small2; diff --git a/mysql-test/suite/innodb/include/have_innodb_bzip2.opt b/mysql-test/suite/innodb/include/have_innodb_bzip2.opt new file mode 100644 index 00000000..64680f7b --- /dev/null +++ b/mysql-test/suite/innodb/include/have_innodb_bzip2.opt @@ -0,0 +1 @@ +--plugin-load-add=$PROVIDER_BZIP2_SO diff --git a/mysql-test/suite/innodb/include/have_innodb_lz4.opt b/mysql-test/suite/innodb/include/have_innodb_lz4.opt new file mode 100644 index 00000000..eac86dee --- /dev/null +++ b/mysql-test/suite/innodb/include/have_innodb_lz4.opt @@ -0,0 +1,2 @@ +--plugin-load-add=$PROVIDER_LZ4_SO + diff --git a/mysql-test/suite/innodb/include/have_innodb_lzma.opt b/mysql-test/suite/innodb/include/have_innodb_lzma.opt new file mode 100644 index 00000000..ecb6bd7a --- /dev/null +++ b/mysql-test/suite/innodb/include/have_innodb_lzma.opt @@ -0,0 +1,2 @@ +--plugin-load-add=$PROVIDER_LZMA_SO + diff --git a/mysql-test/suite/innodb/include/have_innodb_lzo.opt b/mysql-test/suite/innodb/include/have_innodb_lzo.opt new file mode 100644 index 00000000..6033fc5d --- /dev/null +++ b/mysql-test/suite/innodb/include/have_innodb_lzo.opt @@ -0,0 +1,2 @@ +--plugin-load-add=$PROVIDER_LZO_SO + diff --git a/mysql-test/suite/innodb/include/have_innodb_punchhole.inc b/mysql-test/suite/innodb/include/have_innodb_punchhole.inc new file mode 100644 index 00000000..74cd5c4e --- /dev/null +++ b/mysql-test/suite/innodb/include/have_innodb_punchhole.inc @@ -0,0 +1,4 @@ +if (!`SELECT COUNT(*) FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE LOWER(variable_name) = 'innodb_have_punch_hole' AND variable_value = 'ON'`) +{ + --skip Test requires InnoDB compiled with fallocate(FALLOC_PUNCH_HOLE| FALLOC_KEEP_SIZE) +} diff --git a/mysql-test/suite/innodb/include/have_innodb_snappy.opt b/mysql-test/suite/innodb/include/have_innodb_snappy.opt new file mode 100644 index 00000000..80c3325a --- /dev/null +++ b/mysql-test/suite/innodb/include/have_innodb_snappy.opt @@ -0,0 +1,2 @@ +--plugin-load-add=$PROVIDER_SNAPPY_SO + diff --git a/mysql-test/suite/innodb/include/have_undo_tablespaces.combinations b/mysql-test/suite/innodb/include/have_undo_tablespaces.combinations new file mode 100644 index 00000000..dbfe4e6c --- /dev/null +++ b/mysql-test/suite/innodb/include/have_undo_tablespaces.combinations @@ -0,0 +1,2 @@ +[2] +innodb-undo-tablespaces=2 diff --git a/mysql-test/suite/innodb/include/ibd_convert.pl b/mysql-test/suite/innodb/include/ibd_convert.pl new file mode 100644 index 00000000..dfa29ee1 --- /dev/null +++ b/mysql-test/suite/innodb/include/ibd_convert.pl @@ -0,0 +1,45 @@ +# Convert tablespace flags to the format understood by MariaDB 10.1.0..10.1.20, +# with the assumption that the flags were correct. + +sub convert_to_mariadb_101 +{ + my ($file, $page_size) = @_; + open(FILE, "+<", $file) or die "Unable to open $file\n"; + sysread(FILE, $_, $page_size)==$page_size||die "Unable to read $file\n"; + sysseek(FILE, 0, 0)||die "Unable to seek $file\n"; + + # FIL_PAGE_DATA + FSP_SPACE_FLAGS = 38 + 16 = 54 bytes from the start + my($flags) = unpack "x[54]N", $_; + my $badflags = ($flags & 0x3f); + my $compression_level=3; + $badflags |= 1<<6|$compression_level<<7 if ($flags & 1 << 16); + $badflags |= ($flags & 15 << 6) << 7; # PAGE_SSIZE + + if ($badflags != $flags) + { + warn "$file: changing $flags to $badflags\n"; + substr ($_, 54, 4) = pack("N", $badflags); + # Compute and replace the innodb_checksum_algorithm=crc32 checksum + my $polynomial = 0x82f63b78; # CRC-32C + if ($page_size == 1024) + { + # ROW_FORMAT=COMPRESSED + substr($_,0,4)=pack("N", + mycrc32(substr($_, 4, 12), 0, $polynomial) ^ + mycrc32(substr($_, 24, 2), 0, $polynomial) ^ + mycrc32(substr($_, 34, $page_size - 34), 0, + $polynomial)); + } + else + { + my $ck=pack("N", + mycrc32(substr($_, 4, 22), 0, $polynomial) ^ + mycrc32(substr($_, 38, $page_size - 38 - 8), 0, + $polynomial)); + substr($_, 0, 4) = $ck; + substr ($_, $page_size - 8, 4) = $ck; + } + syswrite(FILE, $_, $page_size)==$page_size||die "Unable to write $file\n"; + } + close(FILE); +} diff --git a/mysql-test/suite/innodb/include/import.inc b/mysql-test/suite/innodb/include/import.inc new file mode 100644 index 00000000..e8265cb3 --- /dev/null +++ b/mysql-test/suite/innodb/include/import.inc @@ -0,0 +1,40 @@ +# Export Table and Import from saved files .cfg and .ibd +# Caller should create t1 table definition and populate table + +let $MYSQLD_DATADIR = `SELECT @@datadir`; + +if(!$source_db) { + let $source_db = test; +} + +if(!$dest_db) { + let $dest_db = test; +} + +eval FLUSH TABLES $source_db.t1 FOR EXPORT; + +--copy_file $MYSQLD_DATADIR/$source_db/t1.cfg $MYSQLD_DATADIR/t1.cfg_back +--copy_file $MYSQLD_DATADIR/$source_db/t1.ibd $MYSQLD_DATADIR/t1.ibd_back + +UNLOCK TABLES; + +if($source_db != $dest_db) { + eval USE $dest_db; + let $create1 = query_get_value(SHOW CREATE TABLE $source_db.t1, Create Table, 1); + eval $create1; +} + +eval ALTER TABLE $dest_db.t1 DISCARD TABLESPACE; + +--move_file $MYSQLD_DATADIR/t1.cfg_back $MYSQLD_DATADIR/$dest_db/t1.cfg +--move_file $MYSQLD_DATADIR/t1.ibd_back $MYSQLD_DATADIR/$dest_db/t1.ibd + +eval ALTER TABLE $dest_db.t1 IMPORT TABLESPACE; + +eval CHECK TABLE $dest_db.t1; +eval SHOW CREATE TABLE $dest_db.t1; +eval SELECT * FROM $dest_db.t1; + +if($source_db != $dest_db) { + eval DROP TABLE $dest_db.t1; +} diff --git a/mysql-test/suite/innodb/include/innodb-util.pl b/mysql-test/suite/innodb/include/innodb-util.pl new file mode 100644 index 00000000..241545da --- /dev/null +++ b/mysql-test/suite/innodb/include/innodb-util.pl @@ -0,0 +1,126 @@ +# +# Utility functions to copy files for WL#5522 +# +# All the tables must be in the same database, you can call it like so: +# ib_backup_tablespaces("test", "t1", "blah", ...). + +use File::Copy; +use File::Spec; + +sub ib_normalize_path { + my ($path) = @_; +} + +sub ib_backup_tablespace { + my ($db, $table) = @_; + my $datadir = $ENV{'MYSQLD_DATADIR'}; + my $cfg_file = sprintf("%s.cfg", $table); + my $ibd_file = sprintf("%s.ibd", $table); + my $tmpd = $ENV{'MYSQLTEST_VARDIR'} . "/tmp"; + + my @args = (File::Spec->catfile($datadir, $db, $ibd_file), + File::Spec->catfile($tmpd, $ibd_file)); + + copy(@args) or die "copy @args failed: $!"; + + my @args = (File::Spec->catfile($datadir, $db, $cfg_file), + File::Spec->catfile($tmpd, $cfg_file)); + + copy(@args) or die "copy @args failed: $!"; +} + +sub ib_cleanup { + my ($db, $table) = @_; + my $datadir = $ENV{'MYSQLD_DATADIR'}; + my $cfg_file = sprintf("%s.cfg", $table); + + print "unlink: $cfg_file\n"; + + # These may or may not exist + unlink(File::Spec->catfile($datadir, $db, $cfg_file)); +} + +sub ib_unlink_tablespace { + my ($db, $table) = @_; + my $datadir = $ENV{'MYSQLD_DATADIR'}; + my $ibd_file = sprintf("%s.ibd", $table); + + print "unlink: $ibd_file\n"; + # This may or may not exist + unlink(File::Spec->catfile($datadir, $db, $ibd_file)); + + ib_cleanup($db, $table); +} + +sub ib_backup_tablespaces { + my ($db, @tables) = @_; + + foreach my $table (@tables) { + print "backup: $table\n"; + ib_backup_tablespace($db, $table); + } +} + +sub ib_discard_tablespace { } + +sub ib_discard_tablespaces { } + +sub ib_restore_cfg_file { + my ($tmpd, $datadir, $db, $table) = @_; + my $cfg_file = sprintf("%s.cfg", $table); + + my @args = (File::Spec->catfile($tmpd, $cfg_file), + File::Spec->catfile($datadir, "$db", $cfg_file)); + + copy(@args) or die "copy @args failed: $!"; +} + +sub ib_restore_ibd_file { + my ($tmpd, $datadir, $db, $table) = @_; + my $ibd_file = sprintf("%s.ibd", $table); + + my @args = (File::Spec->catfile($tmpd, $ibd_file), + File::Spec->catfile($datadir, $db, $ibd_file)); + + copy(@args) or die "copy @args failed: $!"; +} + +sub ib_restore_tablespace { + my ($db, $table) = @_; + my $datadir = $ENV{'MYSQLD_DATADIR'}; + my $tmpd = $ENV{'MYSQLTEST_VARDIR'} . "/tmp"; + + ib_restore_cfg_file($tmpd, $datadir, $db, $table); + ib_restore_ibd_file($tmpd, $datadir, $db, $table); +} + +sub ib_restore_tablespaces { + my ($db, @tables) = @_; + + foreach my $table (@tables) { + print "restore: $table .ibd and .cfg files\n"; + ib_restore_tablespace($db, $table); + } +} + +sub ib_restore_cfg_files { + my ($db, @tables) = @_; + my $datadir = $ENV{'MYSQLD_DATADIR'}; + my $tmpd = $ENV{'MYSQLTEST_VARDIR'} . "/tmp"; + + foreach my $table (@tables) { + print "restore: $table .cfg file\n"; + ib_restore_cfg_file($tmpd, $datadir, $db, $table); + } +} + +sub ib_restore_ibd_files { + my ($db, @tables) = @_; + my $datadir = $ENV{'MYSQLD_DATADIR'}; + my $tmpd = $ENV{'MYSQLTEST_VARDIR'} . "/tmp"; + + foreach my $table (@tables) { + print "restore: $table .ibd file\n"; + ib_restore_ibd_file($tmpd, $datadir, $db, $table); + } +} diff --git a/mysql-test/suite/innodb/include/innodb-wl6045.inc b/mysql-test/suite/innodb/include/innodb-wl6045.inc new file mode 100644 index 00000000..33a2ecd7 --- /dev/null +++ b/mysql-test/suite/innodb/include/innodb-wl6045.inc @@ -0,0 +1,22 @@ +--echo ===> Testing size=$size +--disable_warnings +--eval CREATE TABLE t1(id INT AUTO_INCREMENT PRIMARY KEY, msg VARCHAR(255)) ENGINE=INNODB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=$size +--enable_warnings + +insert into t1 values(1,"I"); +insert into t1 values(2,"AM"); +insert into t1 values(3,"COMPRESSED"); + +--source include/shutdown_mysqld.inc + +#STOP; + +--exec $INNOCHECKSUM $MYSQLD_DATADIR/test/t1.ibd +--exec $INNOCHECKSUM --write=crc32 $MYSQLD_DATADIR/test/t1.ibd +--exec $INNOCHECKSUM --strict-check=crc32 $MYSQLD_DATADIR/test/t1.ibd +--exec $INNOCHECKSUM --write=none $MYSQLD_DATADIR/test/t1.ibd +--exec $INNOCHECKSUM --strict-check=none $MYSQLD_DATADIR/test/t1.ibd + +--source include/start_mysqld.inc +select * from t1; +drop table t1; diff --git a/mysql-test/suite/innodb/include/innodb_binlog.combinations b/mysql-test/suite/innodb/include/innodb_binlog.combinations new file mode 100644 index 00000000..46d31e73 --- /dev/null +++ b/mysql-test/suite/innodb/include/innodb_binlog.combinations @@ -0,0 +1,3 @@ +[log-bin] +log-bin +[skip-log-bin] diff --git a/mysql-test/suite/innodb/include/innodb_binlog.inc b/mysql-test/suite/innodb/include/innodb_binlog.inc new file mode 100644 index 00000000..3f6ece24 --- /dev/null +++ b/mysql-test/suite/innodb/include/innodb_binlog.inc @@ -0,0 +1,3 @@ +# See innodb_binlog.combinations +# --log-bin is ignored in the embedded server +--source include/not_embedded.inc diff --git a/mysql-test/suite/innodb/include/innodb_bulk_create_index.inc b/mysql-test/suite/innodb/include/innodb_bulk_create_index.inc new file mode 100644 index 00000000..3c105179 --- /dev/null +++ b/mysql-test/suite/innodb/include/innodb_bulk_create_index.inc @@ -0,0 +1,185 @@ +# +# wl#7277: InnoDB: Bulk Load for Create Index +# + +# Create Insert Procedure +DELIMITER |; +CREATE PROCEDURE populate_t1(load_even INT) +BEGIN + DECLARE i int DEFAULT 1; + + START TRANSACTION; + WHILE (i <= 10000) DO + IF i%2 = 0 AND load_even = 1 THEN + INSERT INTO t1 VALUES (i, i, CONCAT('a', i)); + END IF; + IF i%2 != 0 AND load_even != 1 THEN + INSERT INTO t1 VALUES (i, i, CONCAT('a', i)); + END IF; + SET i = i + 1; + END WHILE; + COMMIT; +END| +DELIMITER ;| + +SELECT @@innodb_fill_factor; + +if ($row_format != 'COMPRESSED') +{ + eval CREATE TABLE t1( + class INT, + id INT, + title VARCHAR(100) + ) ENGINE=InnoDB ROW_FORMAT=$row_format; +} + +if ($row_format == 'COMPRESSED') +{ + SET GLOBAL innodb_file_per_table=1; + + eval CREATE TABLE t1( + class INT, + id INT, + title VARCHAR(100) + ) ENGINE=InnoDB ROW_FORMAT=$row_format KEY_BLOCK_SIZE=4; +} + +-- disable_query_log +# Load half records +CALL populate_t1(1); +-- enable_query_log + +SELECT COUNT(*) FROM t1; + +/* Create index. */ +CREATE INDEX idx_id ON t1(id); + +CREATE INDEX idx_title ON t1(title); + +/* Check table. */ +CHECK TABLE t1; + +/* Select by index. */ +EXPLAIN SELECT * FROM t1 WHERE id = 10; +EXPLAIN SELECT * FROM t1 WHERE title = 'a10'; + +SELECT * FROM t1 WHERE id = 10; +SELECT * FROM t1 WHERE title = 'a10'; + +SELECT * FROM t1 WHERE id = 5000; +SELECT * FROM t1 WHERE title = 'a5000'; + +SELECT * FROM t1 WHERE id = 10000; +SELECT * FROM t1 WHERE title = 'a10000'; + +SELECT * FROM t1 WHERE id = 10010; +SELECT * FROM t1 WHERE title = 'a10010'; + +/*Insert/Update/Delete. */ +DELETE FROM t1 WHERE id < 4010 AND id > 3990; +INSERT INTO t1 VALUES(4000, 4000, 'b4000'); +UPDATE t1 SET title = CONCAT('b', id) WHERE id < 3010 AND id > 2990; + +SELECT * FROM t1 WHERE id = 3000; +SELECT * FROM t1 WHERE title = 'a3000'; +SELECT * FROM t1 WHERE title = 'b3000'; + +SELECT * FROM t1 WHERE id = 4000; +SELECT * FROM t1 WHERE title = 'a4000'; +SELECT * FROM t1 WHERE title = 'b4000'; + +SELECT * FROM t1 WHERE id = 4001; +SELECT * FROM t1 WHERE title = 'a4001'; + +-- disable_query_log +# Load half records (follow up load) +CALL populate_t1(0); +-- enable_query_log +SELECT COUNT(*) FROM t1; + + +/* Add column. */ +ALTER TABLE t1 ADD COLUMN content TEXT; + +CHECK TABLE t1; + +SELECT * FROM t1 WHERE id = 10; +SELECT * FROM t1 WHERE title = 'a10'; + +SELECT * FROM t1 WHERE id = 5000; +SELECT * FROM t1 WHERE title = 'a5000'; + +SELECT * FROM t1 WHERE id = 10000; +SELECT * FROM t1 WHERE title = 'a10000'; + +SELECT * FROM t1 WHERE id = 10010; +SELECT * FROM t1 WHERE title = 'a10010'; + +/* Drop column. */ +ALTER TABLE t1 DROP COLUMN content; + +CHECK TABLE t1; + +SELECT * FROM t1 WHERE id = 10; +SELECT * FROM t1 WHERE title = 'a10'; + +SELECT * FROM t1 WHERE id = 5000; +SELECT * FROM t1 WHERE title = 'a5000'; + +SELECT * FROM t1 WHERE id = 10000; +SELECT * FROM t1 WHERE title = 'a10000'; + +SELECT * FROM t1 WHERE id = 10010; +SELECT * FROM t1 WHERE title = 'a10010'; + +DROP TABLE t1; + +# Test Blob +if ($row_format != 'COMPRESSED') { + eval CREATE TABLE t1( + a INT PRIMARY KEY, + b TEXT, + c TEXT) ENGINE=InnoDB ROW_FORMAT=$row_format; +} + +if ($row_format == 'COMPRESSED') { + eval CREATE TABLE t1( + a INT PRIMARY KEY, + b BLOB, + c TEXT) ENGINE=InnoDB ROW_FORMAT=$row_format KEY_BLOCK_SIZE=4; +} + +let $cnt= 5000; +-- disable_query_log +WHILE ($cnt>=4950) +{ +EVAL INSERT INTO t1 VALUES + ($cnt, REPEAT(CONCAT('a', $cnt),2000), CONCAT('a', $cnt)); +dec $cnt; +} +-- enable_query_log +ALTER TABLE t1 ADD INDEX `idx` (a,b(5)); + +SELECT CHAR_LENGTH(b) FROM t1 WHERE a=4975; +SELECT b=REPEAT(CONCAT('a',4975),2000) FROM t1 WHERE a=4975 AND b like 'a4975%'; +UPDATE t1 SET b = REPEAT(CONCAT('b',4975),2000) WHERE a=4975 AND b like 'a4975%'; +SELECT b=REPEAT(CONCAT('a',4975),2000) FROM t1 WHERE a=4975 AND b like 'a4975%'; +SELECT b=REPEAT(CONCAT('b',4975),2000) FROM t1 WHERE a=4975 AND b like 'b4975%'; +DELETE FROM t1 WHERE a=4975 AND b like 'b4975%'; +SELECT b=REPEAT(CONCAT('b',4975),2000) FROM t1 WHERE a=4975 AND b like 'b4975%'; + +ALTER TABLE t1 DROP COLUMN c; + +CHECK TABLE t1; + +SELECT CHAR_LENGTH(b) FROM t1 WHERE a=4975; + +DROP TABLE t1; + +# Restore global variables +if ($row_format == 'COMPRESSED') +{ + SET GLOBAL innodb_file_per_table=default; +} + +DROP PROCEDURE populate_t1; diff --git a/mysql-test/suite/innodb/include/innodb_bulk_create_index_debug.inc b/mysql-test/suite/innodb/include/innodb_bulk_create_index_debug.inc new file mode 100644 index 00000000..85466e5e --- /dev/null +++ b/mysql-test/suite/innodb/include/innodb_bulk_create_index_debug.inc @@ -0,0 +1,210 @@ +# +# wl#7277: InnoDB: Bulk Load for Create Index +# + +# Not supported in embedded +-- source include/not_embedded.inc +-- source include/have_debug.inc +-- source include/have_debug_sync.inc + +# Create Insert Procedure +DELIMITER |; +CREATE PROCEDURE populate_t1() +BEGIN + DECLARE i int DEFAULT 1; + + START TRANSACTION; + WHILE (i <= 10000) DO + INSERT INTO t1 VALUES (i, i, CONCAT('a', i)); + SET i = i + 1; + END WHILE; + COMMIT; +END| +DELIMITER ;| + +# Test scenarios: +# 1. Test restart; +# 2. Test crash recovery. + +# Test Restart +if ($row_format != 'COMPRESSED') +{ + eval CREATE TABLE t1( + class INT, + id INT, + title VARCHAR(100) + ) ENGINE=InnoDB ROW_FORMAT=$row_format; +} + +if ($row_format == 'COMPRESSED') +{ + SET GLOBAL innodb_file_per_table=1; + + eval CREATE TABLE t1( + class INT, + id INT, + title VARCHAR(100) + ) ENGINE=InnoDB ROW_FORMAT=$row_format KEY_BLOCK_SIZE=4; +} + +-- disable_query_log +CALL populate_t1(); +-- enable_query_log + +SELECT COUNT(*) FROM t1; + +--enable_info +CREATE INDEX idx_title ON t1(title); +--disable_info + +RENAME TABLE t1 TO t0; + +-- echo # Test Blob + +if ($row_format != 'COMPRESSED') { + eval CREATE TABLE t1( + a INT PRIMARY KEY, + b TEXT, + c TEXT) ENGINE=InnoDB ROW_FORMAT=$row_format; +} + +if ($row_format == 'COMPRESSED') { + SET GLOBAL innodb_file_per_table=1; + + eval CREATE TABLE t1( + a INT PRIMARY KEY, + b TEXT, + c TEXT) ENGINE=InnoDB ROW_FORMAT=$row_format KEY_BLOCK_SIZE=4; +} + +INSERT INTO t1 VALUES + (1, REPEAT('a',10000), 'a'), + (2, REPEAT('b',20000), 'b'), + (3, REPEAT('c',40000), 'c'), + (4, REPEAT('d',60000), 'd'); + +SELECT CHAR_LENGTH(b) FROM t1; + +--enable_info +ALTER TABLE t1 DROP COLUMN c, FORCE; +--disable_info + +--source include/restart_mysqld.inc + +CHECK TABLE t0,t1; + +SELECT CHAR_LENGTH(b) FROM t1; + +DROP TABLE t1; + +RENAME TABLE t0 to t1; + +CHECK TABLE t1; + +SELECT * FROM t1 WHERE title = 'a10'; + +SELECT * FROM t1 WHERE title = 'a5000'; + +SELECT * FROM t1 WHERE title = 'a10000'; + +SELECT * FROM t1 WHERE title = 'a10010'; + +DROP TABLE t1; + +# Test Crash Recovery + +if ($row_format != 'COMPRESSED') +{ + eval CREATE TABLE t1( + class INT, + id INT, + title VARCHAR(100) + ) ENGINE=InnoDB ROW_FORMAT=$row_format; +} + +if ($row_format == 'COMPRESSED') +{ + SET GLOBAL innodb_file_per_table=1; + + eval CREATE TABLE t1( + class INT, + id INT, + title VARCHAR(100) + ) ENGINE=InnoDB ROW_FORMAT=$row_format KEY_BLOCK_SIZE=4; +} + +-- disable_query_log +CALL populate_t1(); +-- enable_query_log + +connect (hang,localhost,root); +SET DEBUG_SYNC='alter_table_inplace_trans_commit SIGNAL hung WAIT_FOR ever'; +send +CREATE INDEX idx_title ON t1(title); + +connection default; +SET DEBUG_SYNC='now WAIT_FOR hung'; +let $shutdown_timeout=0; +--source include/restart_mysqld.inc +disconnect hang; + +SELECT COUNT(*) FROM t1; + +CHECK TABLE t1; + +EXPLAIN SELECT * FROM t1 WHERE title = 'a10'; + +SELECT * FROM t1 WHERE title = 'a10'; + +SELECT * FROM t1 WHERE title = 'a5000'; + +SELECT * FROM t1 WHERE title = 'a10000'; + +SELECT * FROM t1 WHERE title = 'a10010'; + +DROP TABLE t1; + +-- echo # Test Blob + +if ($row_format != 'COMPRESSED') { + eval CREATE TABLE t1( + a INT PRIMARY KEY, + b TEXT, + c TEXT) ENGINE=InnoDB ROW_FORMAT=$row_format; +} + +if ($row_format == 'COMPRESSED') { + SET GLOBAL innodb_file_per_table=1; + + eval CREATE TABLE t1( + a INT PRIMARY KEY, + b TEXT, + c TEXT) ENGINE=InnoDB ROW_FORMAT=$row_format KEY_BLOCK_SIZE=4; +} + +INSERT INTO t1 VALUES + (1, REPEAT('a',10000), 'a'), + (2, REPEAT('b',20000), 'b'), + (3, REPEAT('c',40000), 'c'), + (4, REPEAT('d',60000), 'd'); + +SELECT CHAR_LENGTH(b) FROM t1; + +connect (hang,localhost,root); +SET DEBUG_SYNC='alter_table_inplace_trans_commit SIGNAL hung WAIT_FOR ever'; +send +ALTER TABLE t1 DROP COLUMN c, FORCE; + +connection default; +SET DEBUG_SYNC='now WAIT_FOR hung'; +--source include/restart_mysqld.inc +disconnect hang; +let $shutdown_timeout=60; + +CHECK TABLE t1; + +SELECT CHAR_LENGTH(b) FROM t1; + +DROP TABLE t1; + +DROP PROCEDURE populate_t1; diff --git a/mysql-test/suite/innodb/include/innodb_dict.inc b/mysql-test/suite/innodb/include/innodb_dict.inc new file mode 100644 index 00000000..1e051812 --- /dev/null +++ b/mysql-test/suite/innodb/include/innodb_dict.inc @@ -0,0 +1,9 @@ +SELECT i.NAME,i.POS,i.MTYPE,i.PRTYPE,i.LEN +FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS i +INNER JOIN sys_tables st ON i.TABLE_ID=st.TABLE_ID; + +SELECT si.NAME,i.POS,i.NAME FROM INFORMATION_SCHEMA.INNODB_SYS_FIELDS i +INNER JOIN sys_indexes si ON i.INDEX_ID=si.INDEX_ID; + +SELECT i.* FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS i +INNER JOIN sys_foreign sf ON i.ID = sf.ID; diff --git a/mysql-test/suite/innodb/include/innodb_isolation_selects.inc b/mysql-test/suite/innodb/include/innodb_isolation_selects.inc new file mode 100644 index 00000000..922f5c1c --- /dev/null +++ b/mysql-test/suite/innodb/include/innodb_isolation_selects.inc @@ -0,0 +1,15 @@ +# These same selects are used many times in innodb_isolation.test + +--echo ########## innodb_isolation_selects.inc ########## +SELECT * FROM t1; +SELECT COUNT(*) FROM t1; +SELECT COUNT(c1) FROM t1; +SELECT COUNT(c2) FROM t1; # Uses secondary index k2 +SELECT COUNT(c3) FROM t1; # Uses clustered index +SELECT SUM(c1) FROM t1; # Uses secondary index +SELECT SUM(c2) FROM t1; # Uses secondary index +SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; # Uses clustered index +SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); +--echo ############################################### diff --git a/mysql-test/suite/innodb/include/innodb_merge_threshold_delete.inc b/mysql-test/suite/innodb/include/innodb_merge_threshold_delete.inc new file mode 100644 index 00000000..8c60cd6e --- /dev/null +++ b/mysql-test/suite/innodb/include/innodb_merge_threshold_delete.inc @@ -0,0 +1,111 @@ +# +# Test to cause merge of the pages (by deleting) +# test/tab1 should be created already with innodb_file_per_table=ON +# The definition is intended to be based on +# "create table tab1 (a bigint primary key, b varchar(2048)) engine=InnoDB;" +# + +--source include/have_innodb.inc +--source include/have_innodb_16k.inc + +# turn on flags +--disable_query_log +SET GLOBAL innodb_monitor_enable=index_page_merge_attempts; +SET GLOBAL innodb_monitor_reset=index_page_merge_attempts; +SET GLOBAL innodb_monitor_enable=index_page_merge_successful; +SET GLOBAL innodb_monitor_reset=index_page_merge_successful; +--enable_query_log + +--echo # check MERGE_THRESHOLD +--replace_result tab1#P tab1#p +select t.NAME as TABLE_NAME, i.NAME as INDEX_NAME, i.MERGE_THRESHOLD +from INFORMATION_SCHEMA.INNODB_SYS_TABLES t, INFORMATION_SCHEMA.INNODB_SYS_INDEXES i +where t.TABLE_ID = i.TABLE_ID and t.NAME like 'test/tab1%'; + +insert into tab1 values (1, repeat('a',2048)); +insert into tab1 values (2, repeat('a',2048)); +insert into tab1 values (3, repeat('a',2048)); +insert into tab1 values (8, repeat('a',2048)); +insert into tab1 values (9, repeat('a',2048)); +insert into tab1 values (10, repeat('a',2048)); +insert into tab1 values (11, repeat('a',2048)); +insert into tab1 values (12, repeat('a',2048)); +insert into tab1 values (4, repeat('a',2048)); +insert into tab1 values (5, repeat('a',2048)); +insert into tab1 values (6, repeat('a',2048)); +insert into tab1 values (7, repeat('a',2048)); +insert into tab1 values (13, repeat('a',2048)); +insert into tab1 values (14, repeat('a',2048)); + +# filled 2 leaf pages have been prepared +# | 1,..,7 | 8,..,14 | + +select PAGE_NUMBER, NUMBER_RECORDS +from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES s1, +INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2 +where s1.SPACE = s2.SPACE AND NAME like 'test/tab1%' +and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS; + +begin; +delete from tab1 where a = 12; +delete from tab1 where a = 13; +delete from tab1 where a = 14; +delete from tab1 where a = 5; +delete from tab1 where a = 6; +delete from tab1 where a = 7; +commit; + +# wait for purge view progress (records are deleted actually by purge) +--source include/wait_all_purged.inc + +# not merged yet +# | 1,2,3,4 | 8,9,10,11 | + +--echo # check page merge happens (nothing is expected) +SELECT name,count_reset FROM information_schema.innodb_metrics +WHERE name like 'index_page_merge_%'; + +select PAGE_NUMBER, NUMBER_RECORDS +from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES s1, +INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2 +where s1.SPACE = s2.SPACE AND NAME like 'test/tab1%' +and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS; + +delete from tab1 where a = 11; +# wait for purge view progress (records are deleted actually by purge) +--source include/wait_all_purged.inc + +--echo # check page merge happens (MERGE_THRESHOLD=50 causes merge here) +SELECT name,count_reset FROM information_schema.innodb_metrics +WHERE name like 'index_page_merge_%'; + + +delete from tab1 where a = 10; +# wait for purge view progress (records are deleted actually by purge) +--source include/wait_all_purged.inc + +--echo # check page merge happens (MERGE_THRESHOLD=35 causes merge here) +SELECT name,count_reset FROM information_schema.innodb_metrics +WHERE name like 'index_page_merge_%'; + + +delete from tab1 where a = 9; +# wait for purge view progress (records are deleted actually by purge) +--source include/wait_all_purged.inc + +--echo # check page merge happens (MERGE_THRESHOLD=25 causes merge here) +SELECT name,count_reset FROM information_schema.innodb_metrics +WHERE name like 'index_page_merge_%'; + +--disable_query_log +# Reset flags +SET GLOBAL innodb_monitor_disable=index_page_merge_attempts; +SET GLOBAL innodb_monitor_disable=index_page_merge_successful; + +--disable_warnings +set global innodb_monitor_enable = default; +set global innodb_monitor_disable = default; +set global innodb_monitor_reset = default; +set global innodb_monitor_reset_all = default; +--enable_warnings +--enable_query_log diff --git a/mysql-test/suite/innodb/include/innodb_merge_threshold_secondary.inc b/mysql-test/suite/innodb/include/innodb_merge_threshold_secondary.inc new file mode 100644 index 00000000..8e821365 --- /dev/null +++ b/mysql-test/suite/innodb/include/innodb_merge_threshold_secondary.inc @@ -0,0 +1,158 @@ +# +# Test to cause merge of the pages (at secondary index by deleting) +# test/tab1 should be created already with innodb_file_per_table=ON +# The definition is intended to be based on +# "create table tab1 (a bigint primary key, b blob) engine=InnoDB row_format=dynamic;" +# "create index index1 on tab1(b(750));" +# + +--source include/have_innodb.inc +--source include/have_innodb_16k.inc + +# turn on flags +--disable_query_log +SET GLOBAL innodb_monitor_enable=index_page_merge_attempts; +SET GLOBAL innodb_monitor_reset=index_page_merge_attempts; +SET GLOBAL innodb_monitor_enable=index_page_merge_successful; +SET GLOBAL innodb_monitor_reset=index_page_merge_successful; +--enable_query_log + +--echo # check MERGE_THRESHOLD +select t.NAME as TABLE_NAME, i.NAME as INDEX_NAME, i.MERGE_THRESHOLD +from INFORMATION_SCHEMA.INNODB_SYS_TABLES t, INFORMATION_SCHEMA.INNODB_SYS_INDEXES i +where t.TABLE_ID = i.TABLE_ID and t.NAME like 'test/tab1%'; + +INSERT INTO tab1 VALUES (1, concat("01", repeat('a',8190))); +INSERT INTO tab1 VALUES (2, concat("02", repeat('a',8190))); +INSERT INTO tab1 VALUES (3, concat("03", repeat('a',8190))); +INSERT INTO tab1 VALUES (4, concat("04", repeat('a',8190))); +INSERT INTO tab1 VALUES (5, concat("05", repeat('a',8190))); +INSERT INTO tab1 VALUES (6, concat("06", repeat('a',8190))); +INSERT INTO tab1 VALUES (7, concat("07", repeat('a',8190))); +INSERT INTO tab1 VALUES (8, concat("08", repeat('a',8190))); +INSERT INTO tab1 VALUES (9, concat("09", repeat('a',8190))); +INSERT INTO tab1 VALUES (10, concat("10", repeat('a',8190))); + +INSERT INTO tab1 VALUES (22, concat("22", repeat('a',8190))); +INSERT INTO tab1 VALUES (23, concat("23", repeat('a',8190))); +INSERT INTO tab1 VALUES (24, concat("24", repeat('a',8190))); +INSERT INTO tab1 VALUES (25, concat("25", repeat('a',8190))); +INSERT INTO tab1 VALUES (26, concat("26", repeat('a',8190))); +INSERT INTO tab1 VALUES (27, concat("27", repeat('a',8190))); +INSERT INTO tab1 VALUES (28, concat("28", repeat('a',8190))); +INSERT INTO tab1 VALUES (29, concat("29", repeat('a',8190))); +INSERT INTO tab1 VALUES (30, concat("30", repeat('a',8190))); +INSERT INTO tab1 VALUES (31, concat("31", repeat('a',8190))); +INSERT INTO tab1 VALUES (32, concat("32", repeat('a',8190))); +INSERT INTO tab1 VALUES (33, concat("33", repeat('a',8190))); + +INSERT INTO tab1 VALUES (11, concat("11", repeat('a',8190))); +INSERT INTO tab1 VALUES (12, concat("12", repeat('a',8190))); +INSERT INTO tab1 VALUES (13, concat("13", repeat('a',8190))); +INSERT INTO tab1 VALUES (14, concat("14", repeat('a',8190))); +INSERT INTO tab1 VALUES (15, concat("15", repeat('a',8190))); +INSERT INTO tab1 VALUES (16, concat("16", repeat('a',8190))); +INSERT INTO tab1 VALUES (17, concat("17", repeat('a',8190))); +INSERT INTO tab1 VALUES (18, concat("18", repeat('a',8190))); +INSERT INTO tab1 VALUES (19, concat("19", repeat('a',8190))); +INSERT INTO tab1 VALUES (20, concat("20", repeat('a',8190))); +INSERT INTO tab1 VALUES (21, concat("21", repeat('a',8190))); + +INSERT INTO tab1 VALUES (34, concat("34", repeat('a',8190))); +INSERT INTO tab1 VALUES (35, concat("35", repeat('a',8190))); +INSERT INTO tab1 VALUES (36, concat("36", repeat('a',8190))); +INSERT INTO tab1 VALUES (37, concat("37", repeat('a',8190))); +INSERT INTO tab1 VALUES (38, concat("38", repeat('a',8190))); +INSERT INTO tab1 VALUES (39, concat("39", repeat('a',8190))); +INSERT INTO tab1 VALUES (40, concat("40", repeat('a',8190))); +INSERT INTO tab1 VALUES (41, concat("41", repeat('a',8190))); +INSERT INTO tab1 VALUES (42, concat("42", repeat('a',8190))); + +# clustered index is still root page only with the 42 records. +# secondary index is filled 2 leaf pages have been prepared +# | 1,..,21 | 22,..,42 | + +select PAGE_NUMBER, NUMBER_RECORDS +from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES s1, +INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2 +where s1.SPACE = s2.SPACE AND NAME like 'test/tab1%' +and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS; + +begin; +delete from tab1 where a = 33; +delete from tab1 where a = 34; +delete from tab1 where a = 35; +delete from tab1 where a = 36; +delete from tab1 where a = 37; +delete from tab1 where a = 38; +delete from tab1 where a = 39; +delete from tab1 where a = 40; +delete from tab1 where a = 41; +delete from tab1 where a = 42; +delete from tab1 where a = 12; +delete from tab1 where a = 13; +delete from tab1 where a = 14; +delete from tab1 where a = 15; +delete from tab1 where a = 16; +delete from tab1 where a = 17; +delete from tab1 where a = 18; +delete from tab1 where a = 19; +delete from tab1 where a = 20; +delete from tab1 where a = 21; +commit; + +# wait for purge view progress (records are deleted actually by purge) +--source include/wait_all_purged.inc + +# secondary index is not merged yet +# | 1,..,11 | 22,..,32 | + +--echo # check page merge happens (nothing is expected) +SELECT name,count_reset FROM information_schema.innodb_metrics +WHERE name like 'index_page_merge_%'; + +select PAGE_NUMBER, NUMBER_RECORDS +from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES s1, +INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2 +where s1.SPACE = s2.SPACE AND NAME like 'test/tab1%' +and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS; + + +delete from tab1 where a = 32; +# wait for purge view progress (records are deleted actually by purge) +--source include/wait_all_purged.inc + +--echo # check page merge happens (MERGE_THRESHOLD=50 causes merge here) +SELECT name,count_reset FROM information_schema.innodb_metrics +WHERE name like 'index_page_merge_%'; + + +delete from tab1 where a = 31; +# wait for purge view progress (records are deleted actually by purge) +--source include/wait_all_purged.inc + +--echo # check page merge happens (MERGE_THRESHOLD=45 causes merge here) +SELECT name,count_reset FROM information_schema.innodb_metrics +WHERE name like 'index_page_merge_%'; + + +delete from tab1 where a = 30; +# wait for purge view progress (records are deleted actually by purge) +--source include/wait_all_purged.inc + +--echo # check page merge happens (MERGE_THRESHOLD=40 causes merge here) +SELECT name,count_reset FROM information_schema.innodb_metrics +WHERE name like 'index_page_merge_%'; + +--disable_query_log +# Reset flags +SET GLOBAL innodb_monitor_disable=index_page_merge_attempts; +SET GLOBAL innodb_monitor_disable=index_page_merge_successful; + +--disable_warnings +set global innodb_monitor_enable = default; +set global innodb_monitor_disable = default; +set global innodb_monitor_reset = default; +set global innodb_monitor_reset_all = default; +--enable_warnings +--enable_query_log diff --git a/mysql-test/suite/innodb/include/innodb_merge_threshold_update.inc b/mysql-test/suite/innodb/include/innodb_merge_threshold_update.inc new file mode 100644 index 00000000..61e961ac --- /dev/null +++ b/mysql-test/suite/innodb/include/innodb_merge_threshold_update.inc @@ -0,0 +1,100 @@ +# +# Test to cause merge of the pages (by updating to smaller) +# test/tab1 should be created already with innodb_file_per_table=ON +# The definition is intended to be based on +# "create table tab1 (a bigint primary key, b varchar(2048)) engine=InnoDB;" +# + +--source include/have_innodb.inc +--source include/have_innodb_16k.inc + +# turn on flags +--disable_query_log +SET GLOBAL innodb_monitor_enable=index_page_merge_attempts; +SET GLOBAL innodb_monitor_reset=index_page_merge_attempts; +SET GLOBAL innodb_monitor_enable=index_page_merge_successful; +SET GLOBAL innodb_monitor_reset=index_page_merge_successful; +--enable_query_log + +--echo # check MERGE_THRESHOLD +select t.NAME as TABLE_NAME, i.NAME as INDEX_NAME, i.MERGE_THRESHOLD +from INFORMATION_SCHEMA.INNODB_SYS_TABLES t, INFORMATION_SCHEMA.INNODB_SYS_INDEXES i +where t.TABLE_ID = i.TABLE_ID and t.NAME like 'test/tab1%'; + +insert into tab1 values (1, repeat('a',2048)); +insert into tab1 values (2, repeat('a',2048)); +insert into tab1 values (3, repeat('a',2048)); +insert into tab1 values (8, repeat('a',2048)); +insert into tab1 values (9, repeat('a',2048)); +insert into tab1 values (10, repeat('a',2048)); +insert into tab1 values (11, repeat('a',2048)); +insert into tab1 values (12, repeat('a',2048)); +insert into tab1 values (4, repeat('a',2048)); +insert into tab1 values (5, repeat('a',2048)); +insert into tab1 values (6, repeat('a',2048)); +insert into tab1 values (7, repeat('a',2048)); +insert into tab1 values (13, repeat('a',2048)); +insert into tab1 values (14, repeat('a',2048)); + +# filled 2 leaf pages have been prepared +# | 1,..,7 | 8,..,14 | + +select PAGE_NUMBER, NUMBER_RECORDS +from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES s1, +INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2 +where s1.SPACE = s2.SPACE AND NAME like 'test/tab1%' +and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS; + +update tab1 set b='' where a = 12; +update tab1 set b='' where a = 13; +update tab1 set b='' where a = 14; +update tab1 set b='' where a = 5; +update tab1 set b='' where a = 6; +update tab1 set b='' where a = 7; + +# not merged yet +# | 1,2,3,4 | 8,9,10,11 | + +--echo # check page merge happens (nothing is expected) +SELECT name,count_reset FROM information_schema.innodb_metrics +WHERE name like 'index_page_merge_%'; + +select PAGE_NUMBER, NUMBER_RECORDS +from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES s1, +INFORMATION_SCHEMA.INNODB_BUFFER_PAGE s2 +where s1.SPACE = s2.SPACE AND NAME like 'test/tab1%' +and PAGE_TYPE = "INDEX" order by PAGE_NUMBER, NUMBER_RECORDS; + + +update tab1 set b='' where a = 11; + +--echo # check page merge happens (MERGE_THRESHOLD=50 causes merge here) +SELECT name,count_reset FROM information_schema.innodb_metrics +WHERE name like 'index_page_merge_%'; + + +update tab1 set b='' where a = 10; + +--echo # check page merge happens (MERGE_THRESHOLD=35 causes merge here) +SELECT name,count_reset FROM information_schema.innodb_metrics +WHERE name like 'index_page_merge_%'; + + +update tab1 set b='' where a = 9; + +--echo # check page merge happens (MERGE_THRESHOLD=25 causes merge here) +SELECT name,count_reset FROM information_schema.innodb_metrics +WHERE name like 'index_page_merge_%'; + +--disable_query_log +# Reset flags +SET GLOBAL innodb_monitor_disable=index_page_merge_attempts; +SET GLOBAL innodb_monitor_disable=index_page_merge_successful; + +--disable_warnings +set global innodb_monitor_enable = default; +set global innodb_monitor_disable = default; +set global innodb_monitor_reset = default; +set global innodb_monitor_reset_all = default; +--enable_warnings +--enable_query_log diff --git a/mysql-test/suite/innodb/include/innodb_stats.inc b/mysql-test/suite/innodb/include/innodb_stats.inc new file mode 100644 index 00000000..c765521d --- /dev/null +++ b/mysql-test/suite/innodb/include/innodb_stats.inc @@ -0,0 +1,26 @@ +TRUNCATE TABLE test_innodb_stats; + +-- eval $insert + +ANALYZE TABLE test_innodb_stats; + +query_vertical SELECT +stat_name, +stat_value, +sample_size, +stat_description +FROM mysql.innodb_index_stats +WHERE +database_name = DATABASE() AND +table_name = 'test_innodb_stats' AND +index_name = 'a_key' AND +stat_name IN ('n_diff_pfx01', 'n_diff_pfx02', 'n_leaf_pages', 'size') +ORDER BY stat_name; + +# provoke reading from persistent storage upon next table access (the SELECT +# from information_schema.statistics below) +FLUSH TABLE test_innodb_stats; + +# see what we are going to read +query_vertical SELECT * FROM information_schema.statistics +WHERE table_name = 'test_innodb_stats'; diff --git a/mysql-test/suite/innodb/include/log_file_cleanup.inc b/mysql-test/suite/innodb/include/log_file_cleanup.inc new file mode 100644 index 00000000..80ce1df0 --- /dev/null +++ b/mysql-test/suite/innodb/include/log_file_cleanup.inc @@ -0,0 +1,14 @@ +# Remove ibtmp* which are re-generated after each mysqld invocation +# skip auto generated auto.cnf from list_files +--remove_files_wildcard $bugdir ibtmp* +--remove_files_wildcard $bugdir auto.cnf +--list_files $bugdir +--remove_files_wildcard $bugdir ibdata* +--remove_files_wildcard $bugdir ib_logfile* +--remove_files_wildcard $bugdir undo00* +--copy_file $bugdir/bak_ibdata1 $bugdir/ibdata1 +--copy_file $bugdir/bak_ibdata2 $bugdir/ibdata2 +--copy_file $bugdir/bak_ib_logfile0 $bugdir/ib_logfile0 +--copy_file $bugdir/bak_undo001 $bugdir/undo001 +--copy_file $bugdir/bak_undo002 $bugdir/undo002 +--copy_file $bugdir/bak_undo003 $bugdir/undo003 diff --git a/mysql-test/suite/innodb/include/no_checkpoint_end.inc b/mysql-test/suite/innodb/include/no_checkpoint_end.inc new file mode 100644 index 00000000..61721650 --- /dev/null +++ b/mysql-test/suite/innodb/include/no_checkpoint_end.inc @@ -0,0 +1,37 @@ +# Check that the latest checkpoint in the redo log files +# is not newer than the checkpoint sampled by no_checkpoint_start.inc + +if (!$no_checkpoint_kill) { +--source include/kill_mysqld.inc +} + +perl; +my $cp = $ENV{CHECKPOINT_LSN}; +$cp =~ s/^InnoDB\t\t//; +my $log = "$ENV{MYSQLD_DATADIR}ib_logfile0"; +open(LOG, "<$log") || die "Unable to open $log"; +seek(LOG, 4096, 0) || die "Unable to seek $log"; +die unless read(LOG, $_, 8) == 8; +my ($cp1hi,$cp1lo) = unpack("NN", $_); +seek(LOG, 8192, 0) || die "Unable to seek $log"; +die unless read(LOG, $_, 8) == 8; +my ($cp2hi,$cp2lo) = unpack("NN", $_); +close(LOG); + +my $cp1 = $cp1hi << 32 | $cp1lo; +my $cp2 = $cp2hi << 32 | $cp2lo; + +open(OUT, ">$ENV{MYSQLTEST_VARDIR}/log/check.txt") || die; + +if ($cp1 > $cp || $cp2 > $cp) { + print OUT "--source include/start_mysqld.inc\n" + unless $ENV{no_checkpoint_kill}; + print OUT "$ENV{CLEANUP_IF_CHECKPOINT}\n"; + print OUT "--skip Extra checkpoint 1 after $cp ($cp1,$cp2)\n"; +} + +close(OUT); +EOF + +--source $MYSQLTEST_VARDIR/log/check.txt +--remove_file $MYSQLTEST_VARDIR/log/check.txt diff --git a/mysql-test/suite/innodb/include/no_checkpoint_start.inc b/mysql-test/suite/innodb/include/no_checkpoint_start.inc new file mode 100644 index 00000000..a903fee6 --- /dev/null +++ b/mysql-test/suite/innodb/include/no_checkpoint_start.inc @@ -0,0 +1,5 @@ +# Preparation for using no_checkpoint_end.inc + +let MYSQLD_DATADIR= `select @@datadir`; +--replace_regex /.*Last checkpoint at[ ]*([0-9]+).*/\1/ +let CHECKPOINT_LSN=`SHOW ENGINE INNODB STATUS`; diff --git a/mysql-test/suite/innodb/include/restart_and_reinit.inc b/mysql-test/suite/innodb/include/restart_and_reinit.inc new file mode 100644 index 00000000..b3f7cae6 --- /dev/null +++ b/mysql-test/suite/innodb/include/restart_and_reinit.inc @@ -0,0 +1,25 @@ +# +# few innodb tests depend on innodb tablespace being completelty clean and new +# +# this file deletes old innodb files and restarts mysqld +# +source include/not_embedded.inc; + +--disable_query_log +let $innodb_index_stats = query_get_value(show create table mysql.innodb_index_stats, Create Table, 1); +let $innodb_table_stats = query_get_value(show create table mysql.innodb_table_stats, Create Table, 1); +let $database=`select database()`; +drop table mysql.innodb_index_stats, mysql.innodb_table_stats; + +let $datadir= `SELECT @@datadir`; +--source include/shutdown_mysqld.inc +remove_files_wildcard $datadir ib_logfile*; +remove_file $datadir/ibdata1; +--source include/start_mysqld.inc + +--disable_query_log +use mysql; +eval $innodb_table_stats; +eval $innodb_index_stats; +eval use $database; +--enable_query_log diff --git a/mysql-test/suite/innodb/include/show_i_s_tables.inc b/mysql-test/suite/innodb/include/show_i_s_tables.inc new file mode 100644 index 00000000..5fe34c37 --- /dev/null +++ b/mysql-test/suite/innodb/include/show_i_s_tables.inc @@ -0,0 +1,19 @@ +--echo === information_schema.innodb_sys_tables and innodb_sys_tablespaces === +--disable_query_log +--replace_result #P# #p# #SP# #sp# +--replace_regex /FTS_([0-9a-f_]+)([A-Z0-9_]+)/FTS_AUX_\2/ + +SELECT t.name 'Table Name', + s.name 'Tablespace', + t.flag 'Table Flags', + t.n_cols 'Columns', + t.row_format 'Row Format', + t.zip_page_size 'Zip Size' + FROM information_schema.innodb_sys_tables t LEFT JOIN + information_schema.innodb_sys_tablespaces s + ON t.space = s.space + WHERE t.name not like 'SYS_%' + AND t.name NOT LIKE 'mysql/%' + AND t.name NOT LIKE 'sys/%' + ORDER BY t.name; +--enable_query_log diff --git a/mysql-test/suite/innodb/include/show_i_s_tablespaces.inc b/mysql-test/suite/innodb/include/show_i_s_tablespaces.inc new file mode 100644 index 00000000..a85a2948 --- /dev/null +++ b/mysql-test/suite/innodb/include/show_i_s_tablespaces.inc @@ -0,0 +1,16 @@ +# This script assumes that the caller did the following; +# LET $MYSQLD_DATADIR = `select @@datadir`; +# LET $INNODB_PAGE_SIZE = `select @@innodb_page_size`; +--echo === information_schema.innodb_sys_tablespaces and innodb_sys_datafiles === +--disable_query_log +--replace_regex /#P#/#p#/ /#SP#/#sp#/ +--replace_result ./ MYSQLD_DATADIR/ $MYSQLD_DATADIR/ MYSQLD_DATADIR/ $MYSQLD_DATADIR MYSQLD_DATADIR/ $MYSQL_TMP_DIR MYSQL_TMP_DIR $INNODB_PAGE_SIZE DEFAULT +SELECT name 'Space_Name', + @@GLOBAL.innodb_page_size 'Page_Size', + page_size 'Zip_Size', + filename 'Path' + FROM information_schema.innodb_sys_tablespaces + WHERE name != 'innodb_system' + AND name NOT LIKE 'mysql/%' + ORDER BY space; +--enable_query_log diff --git a/mysql-test/suite/innodb/include/wait_all_purged.inc b/mysql-test/suite/innodb/include/wait_all_purged.inc new file mode 100644 index 00000000..e374a7c0 --- /dev/null +++ b/mysql-test/suite/innodb/include/wait_all_purged.inc @@ -0,0 +1,16 @@ +# Wait for everything to be purged. + +--disable_query_log +if (!$wait_all_purged) +{ + SET GLOBAL innodb_max_purge_lag_wait= 0; +} +if ($wait_all_purged) +{ + eval SET GLOBAL innodb_max_purge_lag_wait= $wait_all_purged; +} +--enable_query_log + +--replace_regex /.*History list length ([0-9]+).*/\1/ +let $remaining= `SHOW ENGINE INNODB STATUS`; +echo $remaining transactions not purged; |