diff options
Diffstat (limited to 'mysql-test/suite/innodb')
34 files changed, 1663 insertions, 136 deletions
diff --git a/mysql-test/suite/innodb/r/binlog_consistent.result b/mysql-test/suite/innodb/r/binlog_consistent.result index edfa5947eb8..47f9900c449 100644 --- a/mysql-test/suite/innodb/r/binlog_consistent.result +++ b/mysql-test/suite/innodb/r/binlog_consistent.result @@ -104,4 +104,13 @@ master-bin.000001 1757 Gtid 1 1795 BEGIN GTID 0-1-9 master-bin.000001 1795 Query 1 1887 use `test`; INSERT INTO t1 VALUES (3, "") master-bin.000001 1887 Xid 1 1914 COMMIT /* XID */ master-bin.000001 1914 Rotate 1 1958 master-bin.000002;pos=4 -DROP TABLE t1,t2; +*** MDEV-7310: last_commit_pos_offset set to wrong value after binlog rotate in group commit *** +SET @old_size= @@GLOBAL.max_binlog_size; +SET GLOBAL max_binlog_size=4096; +CREATE TABLE t3 (a INT PRIMARY KEY, b VARBINARY(8192)) ENGINE=MyISAM; +INSERT INTO t3 VALUES (10, ''); +INSERT INTO t3 VALUES (11, 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'); +test_result +OK +SET GLOBAL max_binlog_size=@old_size; +DROP TABLE t1,t2, t3; diff --git a/mysql-test/suite/innodb/r/innodb-alter-table-disk-full.result b/mysql-test/suite/innodb/r/innodb-alter-table-disk-full.result deleted file mode 100644 index ffeacae7951..00000000000 --- a/mysql-test/suite/innodb/r/innodb-alter-table-disk-full.result +++ /dev/null @@ -1,50 +0,0 @@ -create table t1(a int not null primary key, b int) engine=innodb; -create procedure innodb_insert_proc (repeat_count int) -begin -declare current_num int; -set current_num = 0; -while current_num < repeat_count do -insert into t1 values(current_num, current_num); -set current_num = current_num + 1; -end while; -end// -commit; -set autocommit=0; -call innodb_insert_proc(10000); -commit; -set autocommit=1; -set DEBUG_DBUG='+d,ib_os_aio_func_io_failure_28'; -alter table t1 add testcol int; -ERROR HY000: The table 't1' is full -show create table t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `a` int(11) NOT NULL, - `b` int(11) DEFAULT NULL, - PRIMARY KEY (`a`) -) ENGINE=InnoDB DEFAULT CHARSET=latin1 -set DEBUG_DBUG='+d,ib_os_aio_func_io_failure_28_2'; -alter table t1 add testcol int; -ERROR HY000: The table 't1' is full -show create table t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `a` int(11) NOT NULL, - `b` int(11) DEFAULT NULL, - PRIMARY KEY (`a`) -) ENGINE=InnoDB DEFAULT CHARSET=latin1 -set DEBUG_DBUG=NULL; -alter table t1 add testcol2 int; -show create table t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `a` int(11) NOT NULL, - `b` int(11) DEFAULT NULL, - `testcol2` int(11) DEFAULT NULL, - PRIMARY KEY (`a`) -) ENGINE=InnoDB DEFAULT CHARSET=latin1 -select count(1) from t1; -count(1) -10000 -drop procedure innodb_insert_proc; -drop table t1; diff --git a/mysql-test/suite/innodb/r/innodb-alter-tempfile.result b/mysql-test/suite/innodb/r/innodb-alter-tempfile.result new file mode 100644 index 00000000000..ce13ad0978b --- /dev/null +++ b/mysql-test/suite/innodb/r/innodb-alter-tempfile.result @@ -0,0 +1,40 @@ +# +# Bug #18734396 INNODB IN-PLACE ALTER FAILURES BLOCK FUTURE ALTERS +# +# Temporary tablename will be unique. This makes sure that future +# in-place ALTERs of the same table will not be blocked due to +# temporary tablename. +# Crash the server in ha_innobase::commit_inplace_alter_table() +CREATE TABLE t1 (f1 INT NOT NULL, f2 INT NOT NULL) ENGINE=innodb; +SET debug='d,innodb_alter_commit_crash_before_commit'; +Warnings: +Warning 1287 '@@debug' is deprecated and will be removed in a future release. Please use '@@debug_dbug' instead +# Write file to make mysql-test-run.pl expect crash +# Execute the statement that causes the crash +ALTER TABLE t1 ADD PRIMARY KEY (f2, f1); +ERROR HY000: Lost connection to MySQL server during query +# Startup the server after the crash +# Read and remember the temporary table name +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `f1` int(11) NOT NULL, + `f2` int(11) NOT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +# Consecutive Alter table does not create same temporary file name +ALTER TABLE t1 ADD PRIMARY KEY (f2, f1); +# Shutdown the server to allow manual recovery +# Manual recovery begin. The dictionary was not updated +# and the files were not renamed. The rebuilt table +# was left behind on purpose, to faciliate data recovery. +# Manual recovery end +# Startup the server after manual recovery +# Drop the orphaned rebuilt table. +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `f1` int(11) NOT NULL, + `f2` int(11) NOT NULL, + PRIMARY KEY (`f2`,`f1`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +drop table t1; diff --git a/mysql-test/suite/innodb/r/innodb-bigblob.result b/mysql-test/suite/innodb/r/innodb-bigblob.result new file mode 100644 index 00000000000..314bd3ed088 --- /dev/null +++ b/mysql-test/suite/innodb/r/innodb-bigblob.result @@ -0,0 +1,7 @@ +call mtr.add_suppression("Resizing redo log from *"); +call mtr.add_suppression("Starting to delete and rewrite log files."); +call mtr.add_suppression("New log files created, LSN=*"); +create table foo (id varchar(37) not null, content longblob) engine=INNODB; +insert into foo (id, content) values('xyz', ''); +update foo set content=repeat('a', 43941888) where id='xyz'; +drop table foo; diff --git a/mysql-test/suite/innodb/r/innodb-fk.result b/mysql-test/suite/innodb/r/innodb-fk.result index cf883d83874..956dbce58ed 100644 --- a/mysql-test/suite/innodb/r/innodb-fk.result +++ b/mysql-test/suite/innodb/r/innodb-fk.result @@ -33,3 +33,112 @@ select * from fk_29; f1 29 drop table t1; +CREATE TABLE t1 ( +id int(11) NOT NULL AUTO_INCREMENT, +f1 int(11) DEFAULT NULL, +PRIMARY KEY (id), +CONSTRAINT fk1 FOREIGN KEY (f1) REFERENCES t1 (id) ON DELETE CASCADE +) ENGINE=InnoDB; +CREATE TABLE t2 ( +id int(11) NOT NULL AUTO_INCREMENT, +f2 int(11) NOT NULL, +f3 int(11) NOT NULL, +PRIMARY KEY (`id`), +CONSTRAINT fk2 FOREIGN KEY (f2) REFERENCES t1 (`id`) ON DELETE CASCADE, +CONSTRAINT fk3 FOREIGN KEY (f3) REFERENCES t3 (id) ON DELETE CASCADE +) ENGINE=InnoDB; +ERROR HY000: Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed") +show warnings; +Level Code Message +Error 1005 Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed") +Warning 1215 Cannot add foreign key constraint +CREATE TABLE t2 ( +id int(11) NOT NULL AUTO_INCREMENT, +f2 int(11) NOT NULL, +f3 int(11) NOT NULL, +PRIMARY KEY (`id`), +CONSTRAINT fk2 FOREIGN KEY (f2) REFERENCES t1 (`id`) ON DELETE CASCADE +) ENGINE=InnoDB; +ALTER TABLE t2 ADD CONSTRAINT fk3 FOREIGN KEY (f3) REFERENCES t3 (id) ON DELETE CASCADE; +ERROR HY000: Can't create table `test`.`#sql-temporary` (errno: 150 "Foreign key constraint is incorrectly formed") +show warnings; +Level Code Message +Error 1005 Can't create table `test`.`#sql-temporary` (errno: 150 "Foreign key constraint is incorrectly formed") +Warning 1215 Cannot add foreign key constraint +drop table t2; +drop table t1; +CREATE TABLE `#departaments` ( +`id_depart` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, +PRIMARY KEY (`id_depart`) +) ENGINE=INNODB DEFAULT CHARSET=utf8; +CREATE TABLE `#departaments_tree` ( +`id_depart` INT(10) UNSIGNED NOT NULL, +`id_depart_in` INT(10) UNSIGNED NOT NULL, +PRIMARY KEY (`id_depart`,`id_depart_in`), +CONSTRAINT `#departaments_tree_ibfk_1` FOREIGN KEY (`id_depart`) REFERENCES `#departaments` (`id_depart`) +) ENGINE=INNODB DEFAULT CHARSET=utf8; +ALTER TABLE `#departaments_tree` + ADD FOREIGN KEY (`id_depart_in`) REFERENCES `#departaments`(`id_depart`); +SHOW CREATE TABLE `#departaments_tree`; +Table Create Table +#departaments_tree CREATE TABLE `#departaments_tree` ( + `id_depart` int(10) unsigned NOT NULL, + `id_depart_in` int(10) unsigned NOT NULL, + PRIMARY KEY (`id_depart`,`id_depart_in`), + KEY `id_depart_in` (`id_depart_in`), + CONSTRAINT `#departaments_tree_ibfk_1` FOREIGN KEY (`id_depart`) REFERENCES `#departaments` (`id_depart`), + CONSTRAINT `#departaments_tree_ibfk_2` FOREIGN KEY (`id_depart_in`) REFERENCES `#departaments` (`id_depart`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 +DROP TABLE `#departaments_tree`; +DROP TABLE `#departaments`; +CREATE TABLE `boroda` ( +`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, +`a` INT(11) UNSIGNED DEFAULT NULL, +`b` INT(11) UNSIGNED DEFAULT NULL, +PRIMARY KEY (`id`), +KEY `a` (`a`), +CONSTRAINT `boroda_ibfk_1` FOREIGN KEY (`a`) REFERENCES `boroda` (`id`) +) ENGINE=INNODB DEFAULT CHARSET=utf8; +ALTER TABLE `boroda` + ADD FOREIGN KEY (`b`) REFERENCES `boroda`(`id`); +ALTER TABLE `boroda` DROP FOREIGN KEY `boroda_ibfk_2`; +RENAME TABLE `boroda` TO `#boroda`; +ALTER TABLE `#boroda` +ADD FOREIGN KEY (`b`) REFERENCES `#boroda`(`id`); +SHOW CREATE TABLE `#boroda`; +Table Create Table +#boroda CREATE TABLE `#boroda` ( + `id` int(10) unsigned NOT NULL AUTO_INCREMENT, + `a` int(11) unsigned DEFAULT NULL, + `b` int(11) unsigned DEFAULT NULL, + PRIMARY KEY (`id`), + KEY `a` (`a`), + KEY `b` (`b`), + CONSTRAINT `#boroda_ibfk_1` FOREIGN KEY (`a`) REFERENCES `#boroda` (`id`), + CONSTRAINT `#boroda_ibfk_2` FOREIGN KEY (`b`) REFERENCES `#boroda` (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 +DROP TABLE `#boroda`; +CREATE TABLE `boroda` ( +`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, +`a` INT(11) UNSIGNED DEFAULT NULL, +`b` INT(11) UNSIGNED DEFAULT NULL, +PRIMARY KEY (`id`), +KEY `a` (`a`), +CONSTRAINT `boroda_ibfk_1` FOREIGN KEY (`a`) REFERENCES `boroda` (`id`) +) ENGINE=INNODB DEFAULT CHARSET=utf8; +RENAME TABLE `boroda` TO `bor#oda`; +ALTER TABLE `bor#oda` +ADD FOREIGN KEY (`b`) REFERENCES `bor#oda`(`id`); +SHOW CREATE TABLE `bor#oda`; +Table Create Table +bor#oda CREATE TABLE `bor#oda` ( + `id` int(10) unsigned NOT NULL AUTO_INCREMENT, + `a` int(11) unsigned DEFAULT NULL, + `b` int(11) unsigned DEFAULT NULL, + PRIMARY KEY (`id`), + KEY `a` (`a`), + KEY `b` (`b`), + CONSTRAINT `bor#oda_ibfk_1` FOREIGN KEY (`a`) REFERENCES `bor#oda` (`id`), + CONSTRAINT `bor#oda_ibfk_2` FOREIGN KEY (`b`) REFERENCES `bor#oda` (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 +DROP TABLE `bor#oda`; diff --git a/mysql-test/suite/innodb/r/innodb-mdev-7408.result b/mysql-test/suite/innodb/r/innodb-mdev-7408.result new file mode 100644 index 00000000000..8f6ad139192 --- /dev/null +++ b/mysql-test/suite/innodb/r/innodb-mdev-7408.result @@ -0,0 +1,12 @@ +select @@global.innodb_ft_server_stopword_table; +@@global.innodb_ft_server_stopword_table +NULL +CREATE TABLE `stop_it-IT` ENGINE = InnoDB SELECT * FROM information_schema.INNODB_FT_DEFAULT_STOPWORD; +SET @@global.innodb_ft_server_stopword_table = 'test/stop_it-IT'; +ERROR 42000: Variable 'innodb_ft_server_stopword_table' can't be set to the value of 'test/stop_it-IT' +SET @@global.innodb_ft_server_stopword_table = 'test/stop_it@002dIT'; +drop table `stop_it-IT`; +CREATE TABLE stop_it ENGINE = InnoDB SELECT * FROM information_schema.INNODB_FT_DEFAULT_STOPWORD; +SET @@global.innodb_ft_server_stopword_table = 'test/stop_it'; +SET @@global.innodb_ft_server_stopword_table = NULL; +drop table stop_it; diff --git a/mysql-test/suite/innodb/r/innodb-mdev-7513.result b/mysql-test/suite/innodb/r/innodb-mdev-7513.result new file mode 100644 index 00000000000..bb3531e3f90 --- /dev/null +++ b/mysql-test/suite/innodb/r/innodb-mdev-7513.result @@ -0,0 +1,204 @@ +call mtr.add_suppression("Row size too large (> 8126)*"); +CREATE TABLE t1 ( text1 TEXT, +text2 TEXT, +text3 TEXT, +text4 TEXT, +text5 TEXT, +text6 TEXT, +text7 TEXT, +text8 TEXT, +text9 TEXT, +text10 TEXT, +text11 TEXT, +text12 TEXT, +text13 TEXT, +text14 TEXT, +text15 TEXT, +text16 TEXT, +text17 TEXT, +text18 TEXT, +text19 TEXT, +text20 TEXT, +text21 TEXT, +text22 TEXT, +text23 TEXT, +text24 TEXT, +text25 TEXT, +text26 TEXT, +text27 TEXT, +text28 TEXT, +text29 TEXT, +text30 TEXT, +text31 TEXT, +text32 TEXT, +text33 TEXT, +text34 TEXT, +text35 TEXT, +text36 TEXT, +text37 TEXT, +text38 TEXT, +text39 TEXT, +text40 TEXT, +text41 TEXT, +text42 TEXT, +text43 TEXT, +text44 TEXT, +text45 TEXT, +text46 TEXT, +text47 TEXT, +text48 TEXT, +text49 TEXT, +text50 TEXT, +text51 TEXT, +text52 TEXT, +text53 TEXT, +text54 TEXT, +text55 TEXT, +text56 TEXT, +text57 TEXT, +text58 TEXT, +text59 TEXT, +text60 TEXT, +text61 TEXT, +text62 TEXT, +text63 TEXT, +text64 TEXT, +text65 TEXT, +text66 TEXT, +text67 TEXT, +text68 TEXT, +text69 TEXT, +text70 TEXT, +text71 TEXT, +text72 TEXT, +text73 TEXT, +text74 TEXT, +text75 TEXT, +text76 TEXT, +text77 TEXT, +text78 TEXT, +text79 TEXT, +text80 TEXT, +text81 TEXT, +text82 TEXT, +text83 TEXT, +text84 TEXT, +text85 TEXT, +text86 TEXT, +text87 TEXT, +text88 TEXT, +text89 TEXT, +text90 TEXT, +text91 TEXT, +text92 TEXT, +text93 TEXT, +text94 TEXT, +text95 TEXT, +text96 TEXT, +text97 TEXT, +text98 TEXT, +text99 TEXT, +text100 TEXT, +text101 TEXT, +text102 TEXT, +text103 TEXT, +text104 TEXT, +text105 TEXT, +text106 TEXT, +text107 TEXT, +text108 TEXT, +text109 TEXT, +text110 TEXT, +text111 TEXT, +text112 TEXT, +text113 TEXT, +text114 TEXT, +text115 TEXT, +text116 TEXT, +text117 TEXT, +text118 TEXT, +text119 TEXT, +text120 TEXT, +text121 TEXT, +text122 TEXT, +text123 TEXT, +text124 TEXT, +text125 TEXT, +text126 TEXT, +text127 TEXT, +text128 TEXT, +text129 TEXT, +text130 TEXT, +text131 TEXT, +text132 TEXT, +text133 TEXT, +text134 TEXT, +text135 TEXT, +text136 TEXT, +text137 TEXT, +text138 TEXT, +text139 TEXT, +text140 TEXT, +text141 TEXT, +text142 TEXT, +text143 TEXT, +text144 TEXT, +text145 TEXT, +text146 TEXT, +text147 TEXT, +text148 TEXT, +text149 TEXT, +text150 TEXT, +text151 TEXT, +text152 TEXT, +text153 TEXT, +text154 TEXT, +text155 TEXT, +text156 TEXT, +text157 TEXT, +text158 TEXT, +text159 TEXT, +text160 TEXT, +text161 TEXT, +text162 TEXT, +text163 TEXT, +text164 TEXT, +text165 TEXT, +text166 TEXT, +text167 TEXT, +text168 TEXT, +text169 TEXT, +text170 TEXT, +text171 TEXT, +text172 TEXT, +text173 TEXT, +text174 TEXT, +text175 TEXT, +text176 TEXT, +text177 TEXT, +text178 TEXT, +text179 TEXT, +text180 TEXT, +text181 TEXT, +text182 TEXT, +text183 TEXT, +text184 TEXT, +text185 TEXT, +text186 TEXT, +text187 TEXT, +text188 TEXT, +text189 TEXT, +text190 TEXT, +text191 TEXT, +text192 TEXT, +text193 TEXT, +text194 TEXT, +text195 TEXT, +text196 TEXT, +text197 TEXT +) ENGINE = InnoDB; +INSERT INTO t1 VALUES ('abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef'); +DELETE FROM t1 WHERE text1 = 'abcdef'; +SELECT * from t1; +text1 text2 text3 text4 text5 text6 text7 text8 text9 text10 text11 text12 text13 text14 text15 text16 text17 text18 text19 text20 text21 text22 text23 text24 text25 text26 text27 text28 text29 text30 text31 text32 text33 text34 text35 text36 text37 text38 text39 text40 text41 text42 text43 text44 text45 text46 text47 text48 text49 text50 text51 text52 text53 text54 text55 text56 text57 text58 text59 text60 text61 text62 text63 text64 text65 text66 text67 text68 text69 text70 text71 text72 text73 text74 text75 text76 text77 text78 text79 text80 text81 text82 text83 text84 text85 text86 text87 text88 text89 text90 text91 text92 text93 text94 text95 text96 text97 text98 text99 text100 text101 text102 text103 text104 text105 text106 text107 text108 text109 text110 text111 text112 text113 text114 text115 text116 text117 text118 text119 text120 text121 text122 text123 text124 text125 text126 text127 text128 text129 text130 text131 text132 text133 text134 text135 text136 text137 text138 text139 text140 text141 text142 text143 text144 text145 text146 text147 text148 text149 text150 text151 text152 text153 text154 text155 text156 text157 text158 text159 text160 text161 text162 text163 text164 text165 text166 text167 text168 text169 text170 text171 text172 text173 text174 text175 text176 text177 text178 text179 text180 text181 text182 text183 text184 text185 text186 text187 text188 text189 text190 text191 text192 text193 text194 text195 text196 text197 +DROP TABLE t1; diff --git a/mysql-test/suite/innodb/r/innodb-virtual-columns.result b/mysql-test/suite/innodb/r/innodb-virtual-columns.result new file mode 100644 index 00000000000..558bb23de0a --- /dev/null +++ b/mysql-test/suite/innodb/r/innodb-virtual-columns.result @@ -0,0 +1,322 @@ +CREATE TABLE IF NOT EXISTS gso_grad_supr ( +term char(4) NOT NULL DEFAULT '', +uw_id int(8) UNSIGNED NOT NULL DEFAULT 0, +plan varchar(10) NOT NULL DEFAULT '', +wdraw_rsn varchar(4) NOT NULL DEFAULT '', +admit_term char(4) NOT NULL DEFAULT '', +CONSTRAINT gso_grad_supr_pky PRIMARY KEY (uw_id, term) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +INSERT INTO `gso_grad_supr` VALUES ('1031',2,'CSM','','1009'); +INSERT INTO `gso_grad_supr` VALUES ('1035',2,'CSM','ACAD','1009'); +CREATE TABLE IF NOT EXISTS grad_degree ( +student_id int(8) UNSIGNED NOT NULL, +plan varchar(10) NOT NULL, +admit_term char(4) NOT NULL, +wdraw_rsn varchar(4) NOT NULL DEFAULT '', +ofis_deg_status varchar(15) AS ( +CASE +WHEN wdraw_rsn = '' THEN 'In progress' + WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed' + ELSE 'Not Completed' + END) VIRTUAL, +deg_start_term char(4) NOT NULL DEFAULT '' COMMENT 'Educated guess at the beginning of the data', +deg_as_of_term char(4) NOT NULL COMMENT 'In most cases also end term', +CONSTRAINT grad_degree_stu_plan_admit_pky PRIMARY KEY (student_id, plan, admit_term) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +CREATE INDEX grad_degree_wdraw_rsn_ndx ON grad_degree (wdraw_rsn); +CREATE INDEX grad_degree_as_of_term_ndx ON grad_degree (deg_as_of_term); +INSERT IGNORE grad_degree ( +student_id, +plan, +admit_term, +wdraw_rsn, +deg_start_term, +deg_as_of_term +) +SELECT +ggs.uw_id AS c_student_id, +ggs.plan, +ggs.admit_term, +ggs.wdraw_rsn, +IF( (SELECT COUNT(*) FROM grad_degree AS gd WHERE gd.student_id = ggs.uw_id AND gd.admit_term = ggs.admit_term) > 0, ggs.term, ggs.admit_term ) AS c_deg_start_term, +ggs.term AS c_as_of_term +FROM gso_grad_supr AS ggs +LEFT OUTER JOIN +grad_degree AS gd +ON ( gd.student_id = ggs.uw_id AND gd.plan = ggs.plan AND gd.admit_term = ggs.admit_term ) +WHERE +ggs.term = 1031 AND +gd.student_id IS NULL +; +UPDATE grad_degree AS gd +INNER JOIN +gso_grad_supr AS ggs +ON ( gd.student_id = ggs.uw_id AND gd.plan = ggs.plan AND gd.admit_term = ggs.admit_term ) +SET +gd.wdraw_rsn = ggs.wdraw_rsn, +gd.deg_as_of_term = 1035 +WHERE +gd.wdraw_rsn NOT IN ('DCMP', 'TRDC') AND +ggs.term = 1035 +; +drop table grad_degree; +CREATE TABLE IF NOT EXISTS grad_degree ( +student_id int(8) UNSIGNED NOT NULL, +plan varchar(10) NOT NULL, +admit_term char(4) NOT NULL, +wdraw_rsn varchar(4) NOT NULL DEFAULT '', +ofis_deg_status varchar(15) AS ( +CASE +WHEN wdraw_rsn = '' THEN 'In progress' + WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed' + ELSE 'Not Completed' + END) VIRTUAL, +ofis_deg_status2 varchar(15) AS ( +CASE +WHEN wdraw_rsn = '' THEN 'In progress2' + WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed2' + ELSE 'Not Completed2' + END) VIRTUAL, +ofis_deg_status3 varchar(15) AS ( +CASE +WHEN wdraw_rsn = '' THEN 'In progress3' + WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed3' + ELSE 'Not Completed3' + END) VIRTUAL, +ofis_deg_status4 varchar(15) AS ( +CASE +WHEN wdraw_rsn = '' THEN 'In progress4' + WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed4' + ELSE 'Not Completed4' + END) VIRTUAL, +ofis_deg_status5 varchar(15) AS ( +CASE +WHEN wdraw_rsn = '' THEN 'In progress5' + WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed5' + ELSE 'Not Completed5' + END) VIRTUAL, +ofis_deg_status6 varchar(15) AS ( +CASE +WHEN wdraw_rsn = '' THEN 'In progress6' + WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed6' + ELSE 'Not Completed6' + END) VIRTUAL, +ofis_deg_status7 varchar(15) AS ( +CASE +WHEN wdraw_rsn = '' THEN 'In progress7' + WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed7' + ELSE 'Not Completed7' + END) VIRTUAL, +ofis_deg_status8 varchar(15) AS ( +CASE +WHEN wdraw_rsn = '' THEN 'In progress8' + WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed8' + ELSE 'Not Completed8' + END) VIRTUAL, +deg_start_term char(4) NOT NULL DEFAULT '' COMMENT 'Educated guess at the beginning of the data', +deg_as_of_term char(4) NOT NULL COMMENT 'In most cases also end term', +CONSTRAINT grad_degree_stu_plan_admit_pky PRIMARY KEY (student_id, plan, admit_term) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +CREATE INDEX grad_degree_wdraw_rsn_ndx ON grad_degree (wdraw_rsn); +CREATE INDEX grad_degree_as_of_term_ndx ON grad_degree (deg_as_of_term); +INSERT IGNORE grad_degree ( +student_id, +plan, +admit_term, +wdraw_rsn, +deg_start_term, +deg_as_of_term +) +SELECT +ggs.uw_id AS c_student_id, +ggs.plan, +ggs.admit_term, +ggs.wdraw_rsn, +IF( (SELECT COUNT(*) FROM grad_degree AS gd WHERE gd.student_id = ggs.uw_id AND gd.admit_term = ggs.admit_term) > 0, ggs.term, ggs.admit_term ) AS c_deg_start_term, +ggs.term AS c_as_of_term +FROM gso_grad_supr AS ggs +LEFT OUTER JOIN +grad_degree AS gd +ON ( gd.student_id = ggs.uw_id AND gd.plan = ggs.plan AND gd.admit_term = ggs.admit_term ) +WHERE +ggs.term = 1031 AND +gd.student_id IS NULL +; +UPDATE grad_degree AS gd +INNER JOIN +gso_grad_supr AS ggs +ON ( gd.student_id = ggs.uw_id AND gd.plan = ggs.plan AND gd.admit_term = ggs.admit_term ) +SET +gd.wdraw_rsn = ggs.wdraw_rsn, +gd.deg_as_of_term = 1035 +WHERE +gd.wdraw_rsn NOT IN ('DCMP', 'TRDC') AND +ggs.term = 1035 +; +ALTER TABLE grad_degree DROP INDEX grad_degree_wdraw_rsn_ndx; +ALTER TABLE grad_degree DROP COLUMN deg_start_term; +SHOW CREATE TABLE grad_degree; +Table Create Table +grad_degree CREATE TABLE `grad_degree` ( + `student_id` int(8) unsigned NOT NULL, + `plan` varchar(10) NOT NULL, + `admit_term` char(4) NOT NULL, + `wdraw_rsn` varchar(4) NOT NULL DEFAULT '', + `ofis_deg_status` varchar(15) AS ( +CASE +WHEN wdraw_rsn = '' THEN 'In progress' + WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed' + ELSE 'Not Completed' + END) VIRTUAL, + `ofis_deg_status2` varchar(15) AS ( +CASE +WHEN wdraw_rsn = '' THEN 'In progress2' + WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed2' + ELSE 'Not Completed2' + END) VIRTUAL, + `ofis_deg_status3` varchar(15) AS ( +CASE +WHEN wdraw_rsn = '' THEN 'In progress3' + WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed3' + ELSE 'Not Completed3' + END) VIRTUAL, + `ofis_deg_status4` varchar(15) AS ( +CASE +WHEN wdraw_rsn = '' THEN 'In progress4' + WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed4' + ELSE 'Not Completed4' + END) VIRTUAL, + `ofis_deg_status5` varchar(15) AS ( +CASE +WHEN wdraw_rsn = '' THEN 'In progress5' + WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed5' + ELSE 'Not Completed5' + END) VIRTUAL, + `ofis_deg_status6` varchar(15) AS ( +CASE +WHEN wdraw_rsn = '' THEN 'In progress6' + WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed6' + ELSE 'Not Completed6' + END) VIRTUAL, + `ofis_deg_status7` varchar(15) AS ( +CASE +WHEN wdraw_rsn = '' THEN 'In progress7' + WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed7' + ELSE 'Not Completed7' + END) VIRTUAL, + `ofis_deg_status8` varchar(15) AS ( +CASE +WHEN wdraw_rsn = '' THEN 'In progress8' + WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed8' + ELSE 'Not Completed8' + END) VIRTUAL, + `deg_as_of_term` char(4) NOT NULL COMMENT 'In most cases also end term', + PRIMARY KEY (`student_id`,`plan`,`admit_term`), + KEY `grad_degree_as_of_term_ndx` (`deg_as_of_term`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 +DROP TABLE grad_degree; +CREATE TABLE IF NOT EXISTS grad_degree ( +student_id int(8) UNSIGNED NOT NULL, +plan varchar(10) NOT NULL, +admit_term char(4) NOT NULL, +wdraw_rsn varchar(4) NOT NULL DEFAULT '', +ofis_deg_status varchar(15) AS ( +CASE +WHEN wdraw_rsn = '' THEN 'In progress' + WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed' + ELSE 'Not Completed' + END) VIRTUAL, +ofis_deg_status2 varchar(15) AS ( +CASE +WHEN wdraw_rsn = '' THEN 'In progress2' + WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed2' + ELSE 'Not Completed2' + END) VIRTUAL, +ofis_deg_status3 varchar(15) AS ( +CASE +WHEN wdraw_rsn = '' THEN 'In progress3' + WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed3' + ELSE 'Not Completed3' + END) VIRTUAL, +ofis_deg_status4 varchar(15) AS ( +CASE +WHEN wdraw_rsn = '' THEN 'In progress4' + WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed4' + ELSE 'Not Completed4' + END) VIRTUAL, +ofis_deg_status5 varchar(15) AS ( +CASE +WHEN wdraw_rsn = '' THEN 'In progress5' + WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed5' + ELSE 'Not Completed5' + END) VIRTUAL, +ofis_deg_status6 varchar(15) AS ( +CASE +WHEN wdraw_rsn = '' THEN 'In progress6' + WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed6' + ELSE 'Not Completed6' + END) VIRTUAL, +ofis_deg_status7 varchar(15) AS ( +CASE +WHEN wdraw_rsn = '' THEN 'In progress7' + WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed7' + ELSE 'Not Completed7' + END) VIRTUAL, +ofis_deg_status8 varchar(15) AS ( +CASE +WHEN wdraw_rsn = '' THEN 'In progress8' + WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed8' + ELSE 'Not Completed8' + END) VIRTUAL, +deg_start_term char(4) NOT NULL DEFAULT '' COMMENT 'Educated guess at the beginning of the data', +deg_as_of_term char(4) NOT NULL COMMENT 'In most cases also end term', +CONSTRAINT grad_degree_stu_plan_admit_pky PRIMARY KEY (student_id, plan, admit_term) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +CREATE INDEX grad_degree_wdraw_rsn_ndx ON grad_degree (wdraw_rsn); +ALTER TABLE grad_degree DROP COLUMN ofis_deg_status2, DROP COLUMN ofis_deg_status3, +DROP COLUMN ofis_deg_status4, DROP COLUMN ofis_deg_status5, DROP COLUMN ofis_deg_status6, +DROP COLUMN ofis_deg_status7, DROP COLUMN ofis_deg_status8; +CREATE INDEX grad_degree_as_of_term_ndx ON grad_degree (deg_as_of_term); +INSERT IGNORE grad_degree ( +student_id, +plan, +admit_term, +wdraw_rsn, +deg_start_term, +deg_as_of_term +) +SELECT +ggs.uw_id AS c_student_id, +ggs.plan, +ggs.admit_term, +ggs.wdraw_rsn, +IF( (SELECT COUNT(*) FROM grad_degree AS gd WHERE gd.student_id = ggs.uw_id AND gd.admit_term = ggs.admit_term) > 0, ggs.term, ggs.admit_term ) AS c_deg_start_term, +ggs.term AS c_as_of_term +FROM gso_grad_supr AS ggs +LEFT OUTER JOIN +grad_degree AS gd +ON ( gd.student_id = ggs.uw_id AND gd.plan = ggs.plan AND gd.admit_term = ggs.admit_term ) +WHERE +ggs.term = 1031 AND +gd.student_id IS NULL +; +UPDATE grad_degree AS gd +INNER JOIN +gso_grad_supr AS ggs +ON ( gd.student_id = ggs.uw_id AND gd.plan = ggs.plan AND gd.admit_term = ggs.admit_term ) +SET +gd.wdraw_rsn = ggs.wdraw_rsn, +gd.deg_as_of_term = 1035 +WHERE +gd.wdraw_rsn NOT IN ('DCMP', 'TRDC') AND +ggs.term = 1035 +; +select * from grad_degree; +student_id plan admit_term wdraw_rsn ofis_deg_status deg_start_term deg_as_of_term +2 CSM 1009 ACAD Not Completed 1009 1035 +select * from gso_grad_supr; +term uw_id plan wdraw_rsn admit_term +1031 2 CSM 1009 +1035 2 CSM ACAD 1009 +drop table grad_degree; +drop table gso_grad_supr; diff --git a/mysql-test/suite/innodb/r/innodb-xa.result b/mysql-test/suite/innodb/r/innodb-xa.result new file mode 100644 index 00000000000..6eae842b14c --- /dev/null +++ b/mysql-test/suite/innodb/r/innodb-xa.result @@ -0,0 +1,17 @@ +xa rollback 'xid2'; +ERROR XAE04: XAER_NOTA: Unknown XID +drop table if exists t1; +Warnings: +Note 1051 Unknown table 'test.t1' +create table t1(a int)engine=innodb; +rollback; +xa start 'xid2'; +insert into `t1` values (1); +savepoint `sv1`; +xa end 'xid2'; +start transaction; +ERROR XAE07: XAER_RMFAIL: The command cannot be executed when global transaction is in the IDLE state +xa prepare 'xid2'; +release savepoint `sv1`; +xa commit 'xid2'; +drop table t1; diff --git a/mysql-test/suite/innodb/r/innodb_blob_unrecoverable_crash.result b/mysql-test/suite/innodb/r/innodb_blob_unrecoverable_crash.result new file mode 100644 index 00000000000..9f6b7ca6a23 --- /dev/null +++ b/mysql-test/suite/innodb/r/innodb_blob_unrecoverable_crash.result @@ -0,0 +1,23 @@ +call mtr.add_suppression("InnoDB: The total blob data length"); +SET GLOBAL max_allowed_packet = 100*1024*1024; +# Connection big_packets: +CREATE TABLE t1 (a BIGINT PRIMARY KEY, b LONGBLOB) ENGINE=InnoDB; +INSERT INTO t1 (a, b) VALUES (1, '1'); +INSERT INTO t1 (a, b) VALUES (2, '2'); +INSERT INTO t1 (a, b) VALUES (3, '3'); +INSERT INTO t1 (a, b) VALUES (4, '4'); +INSERT INTO t1 (a, b) VALUES (5, '5'); +start transaction; +INSERT INTO t1 (a, b) VALUES (6, REPEAT('a', 20*1024*1024)); +ERROR 42000: The size of BLOB/TEXT data inserted in one transaction is greater than 10% of redo log size. Increase the redo log size using innodb_log_file_size. +# Connection default: +# Quick shutdown and restart server +# Connection default: +SELECT a FROM t1; +a +1 +2 +3 +4 +5 +DROP TABLE t1; diff --git a/mysql-test/suite/innodb/r/innodb_bug34300.result b/mysql-test/suite/innodb/r/innodb_bug34300.result index bf07febca73..09fc0b44579 100644 --- a/mysql-test/suite/innodb/r/innodb_bug34300.result +++ b/mysql-test/suite/innodb/r/innodb_bug34300.result @@ -1,3 +1,3 @@ -ERROR 42000: Row size too large (> ####). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline. +ERROR 42000: The size of BLOB/TEXT data inserted in one transaction is greater than 10% of redo log size. Increase the redo log size using innodb_log_file_size. f4 f8 f4 f8 diff --git a/mysql-test/suite/innodb/r/xa_recovery.result b/mysql-test/suite/innodb/r/xa_recovery.result new file mode 100644 index 00000000000..84cb37ef7c9 --- /dev/null +++ b/mysql-test/suite/innodb/r/xa_recovery.result @@ -0,0 +1,17 @@ +CREATE TABLE t1 (a INT) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1); +XA START 'x'; +UPDATE t1 set a=2; +XA END 'x'; +XA PREPARE 'x'; +call mtr.add_suppression("Found 1 prepared XA transactions"); +SELECT * FROM t1 LOCK IN SHARE MODE; +SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; +SELECT * FROM t1; +a +2 +XA ROLLBACK 'x'; +SELECT * FROM t1; +a +1 +DROP TABLE t1; diff --git a/mysql-test/suite/innodb/t/binlog_consistent.test b/mysql-test/suite/innodb/t/binlog_consistent.test index 20023871093..a97bef4cf38 100644 --- a/mysql-test/suite/innodb/t/binlog_consistent.test +++ b/mysql-test/suite/innodb/t/binlog_consistent.test @@ -86,4 +86,36 @@ SHOW MASTER STATUS; --replace_regex /\/\* xid=.* \*\//\/* XID *\// /Server ver: .*, Binlog ver: .*/Server ver: #, Binlog ver: #/ /table_id: [0-9]+/table_id: #/ SHOW BINLOG EVENTS; -DROP TABLE t1,t2; + +--echo *** MDEV-7310: last_commit_pos_offset set to wrong value after binlog rotate in group commit *** + +SET @old_size= @@GLOBAL.max_binlog_size; +SET GLOBAL max_binlog_size=4096; + +CREATE TABLE t3 (a INT PRIMARY KEY, b VARBINARY(8192)) ENGINE=MyISAM; +INSERT INTO t3 VALUES (10, ''); +--let $bigdata= `SELECT REPEAT('a', 5000)` +eval INSERT INTO t3 VALUES (11, '$bigdata'); + +# The bug was that binlog_snapshot_file pointed to the new file after binlog +# rotation, but binlog_snapshot_position was the offset in the old file before +# binlog rotation. So the position was invalid. +# So here, we check that the values are consistent with SHOW MASTER STATUS, +# which uses a different code path and did not have the bug. + +--source include/wait_for_binlog_checkpoint.inc +--let $snap_file= query_get_value(SHOW STATUS LIKE 'binlog_snapshot_file', Value, 1) +--let $snap_pos= query_get_value(SHOW STATUS LIKE 'binlog_snapshot_position', Value, 1) + +--let $master_file= query_get_value(SHOW MASTER STATUS, File, 1) +--let $master_pos= query_get_value(SHOW MASTER STATUS, Position, 1) + +--disable_query_log +eval SET @errmsg= 'ERROR: ($snap_file, $snap_pos) != ($master_file, $master_pos)'; +eval SELECT IF('$snap_file' = '$master_file' AND $snap_pos = $master_pos, 'OK', @errmsg) AS test_result; +--enable_query_log + +SET GLOBAL max_binlog_size=@old_size; + + +DROP TABLE t1,t2, t3; diff --git a/mysql-test/suite/innodb/t/foreign-keys.test b/mysql-test/suite/innodb/t/foreign-keys.test index 8ee96347208..2d586e2d6be 100644 --- a/mysql-test/suite/innodb/t/foreign-keys.test +++ b/mysql-test/suite/innodb/t/foreign-keys.test @@ -1,11 +1,6 @@ --source include/have_innodb.inc --source include/have_debug.inc -if (`select plugin_auth_version <= "5.5.39-MariaDB-36.0" from information_schema.plugins where plugin_name='innodb'`) -{ - --skip Not fixed in XtraDB as of 5.5.39-MariaDB-36.0 or earlier -} - --echo # --echo # Bug #19471516 SERVER CRASHES WHEN EXECUTING ALTER TABLE --echo # ADD FOREIGN KEY diff --git a/mysql-test/suite/innodb/t/innodb-alter-table-disk-full-master.opt b/mysql-test/suite/innodb/t/innodb-alter-table-disk-full-master.opt deleted file mode 100644 index 9c2ee7846b6..00000000000 --- a/mysql-test/suite/innodb/t/innodb-alter-table-disk-full-master.opt +++ /dev/null @@ -1 +0,0 @@ ---innodb-use-native-aio=0 diff --git a/mysql-test/suite/innodb/t/innodb-alter-table-disk-full.test b/mysql-test/suite/innodb/t/innodb-alter-table-disk-full.test deleted file mode 100644 index adeb2ef9fd2..00000000000 --- a/mysql-test/suite/innodb/t/innodb-alter-table-disk-full.test +++ /dev/null @@ -1,50 +0,0 @@ -# MDEV-6288: Innodb causes server crash after disk full, then can't ALTER TABLE any more ---source include/have_innodb.inc ---source include/not_windows.inc ---source include/not_valgrind.inc ---source include/not_embedded.inc - -# DEBUG_SYNC must be compiled in. ---source include/have_debug_sync.inc - -create table t1(a int not null primary key, b int) engine=innodb; - -delimiter //; -create procedure innodb_insert_proc (repeat_count int) -begin - declare current_num int; - set current_num = 0; - while current_num < repeat_count do - insert into t1 values(current_num, current_num); - set current_num = current_num + 1; - end while; -end// -delimiter ;// -commit; - -set autocommit=0; -call innodb_insert_proc(10000); -commit; -set autocommit=1; - -# This caused crash earlier -set DEBUG_DBUG='+d,ib_os_aio_func_io_failure_28'; ---error 1114 -alter table t1 add testcol int; -show create table t1; - -# This caused crash earlier -set DEBUG_DBUG='+d,ib_os_aio_func_io_failure_28_2'; ---error 1114 -alter table t1 add testcol int; -show create table t1; - -set DEBUG_DBUG=NULL; -alter table t1 add testcol2 int; -show create table t1; - -select count(1) from t1; - -drop procedure innodb_insert_proc; -drop table t1; - diff --git a/mysql-test/suite/innodb/t/innodb-alter-tempfile.test b/mysql-test/suite/innodb/t/innodb-alter-tempfile.test new file mode 100644 index 00000000000..ec1ea35f1cf --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb-alter-tempfile.test @@ -0,0 +1,75 @@ +# Not supported in embedded +--source include/not_embedded.inc + +# This test case needs to crash the server. Needs a debug server. +--source include/have_debug.inc + +# Don't test this under valgrind, memory leaks will occur. +--source include/not_valgrind.inc + +# Avoid CrashReporter popup on Mac +--source include/not_crashrep.inc + +# InnoDB is required +--source include/have_innodb.inc + +--echo # +--echo # Bug #18734396 INNODB IN-PLACE ALTER FAILURES BLOCK FUTURE ALTERS +--echo # +--echo # Temporary tablename will be unique. This makes sure that future +--echo # in-place ALTERs of the same table will not be blocked due to +--echo # temporary tablename. + +let datadir= `select @@datadir`; + +--let $_server_id= `SELECT @@server_id` +--let $_expect_file_name=$MYSQLTEST_VARDIR/tmp/mysqld.$_server_id.expect + +--echo # Crash the server in ha_innobase::commit_inplace_alter_table() +CREATE TABLE t1 (f1 INT NOT NULL, f2 INT NOT NULL) ENGINE=innodb; +SET debug='d,innodb_alter_commit_crash_before_commit'; + +let $orig_table_id = `SELECT table_id + FROM information_schema.innodb_sys_tables + WHERE name = 'test/t1'`; + +--echo # Write file to make mysql-test-run.pl expect crash +--exec echo "restart" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect + +--echo # Execute the statement that causes the crash +--error 2013 +ALTER TABLE t1 ADD PRIMARY KEY (f2, f1); +--echo # Startup the server after the crash +--source include/start_mysqld.inc + +--echo # Read and remember the temporary table name +let $temp_table_name = `SELECT SUBSTRING(name,6) + FROM information_schema.innodb_sys_tables + WHERE name LIKE "test/#sql-ib$orig_table_id%"`; +# This second copy is an environment variable for the perl script below. +let temp_table_name = $temp_table_name; +show create table t1; +--echo # Consecutive Alter table does not create same temporary file name +ALTER TABLE t1 ADD PRIMARY KEY (f2, f1); +--echo # Shutdown the server to allow manual recovery +--source include/shutdown_mysqld.inc + +--echo # Manual recovery begin. The dictionary was not updated +--echo # and the files were not renamed. The rebuilt table +--echo # was left behind on purpose, to faciliate data recovery. + +perl; +my @frm_file = glob "$ENV{'datadir'}/test/#sql-*.frm"; +my $target_frm = "$ENV{'datadir'}/test/$ENV{'temp_table_name'}.frm"; +rename($frm_file[0], $target_frm); +EOF +--echo # Manual recovery end +--echo # Startup the server after manual recovery +--source include/start_mysqld.inc + +--echo # Drop the orphaned rebuilt table. +--disable_query_log +eval DROP TABLE `#mysql50#$temp_table_name`; +--enable_query_log +show create table t1; +drop table t1; diff --git a/mysql-test/suite/innodb/t/innodb-autoinc.test b/mysql-test/suite/innodb/t/innodb-autoinc.test index fd40b50ebbc..a8dda12cadd 100644 --- a/mysql-test/suite/innodb/t/innodb-autoinc.test +++ b/mysql-test/suite/innodb/t/innodb-autoinc.test @@ -1,8 +1,3 @@ -if (`select plugin_auth_version <= "5.5.37-MariaDB-34.0" from information_schema.plugins where plugin_name='innodb'`) -{ - --skip Not fixed in XtraDB as of 5.5.37-MariaDB-34.0 or earlier -} - --source include/have_innodb.inc # embedded server ignores 'delayed', so skip this -- source include/not_embedded.inc diff --git a/mysql-test/suite/innodb/t/innodb-bigblob.opt b/mysql-test/suite/innodb/t/innodb-bigblob.opt new file mode 100644 index 00000000000..06cc9e2b979 --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb-bigblob.opt @@ -0,0 +1,2 @@ +--max-allowed-packet=128M +--innodb-log-file-size=210M diff --git a/mysql-test/suite/innodb/t/innodb-bigblob.test b/mysql-test/suite/innodb/t/innodb-bigblob.test new file mode 100644 index 00000000000..7665c890316 --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb-bigblob.test @@ -0,0 +1,20 @@ +-- source include/have_innodb.inc +-- source include/big_test.inc +-- source include/not_embedded.inc + +--disable_query_log +let $status_orig=`SELECT @@innodb_status_output`; +--enable_query_log + +call mtr.add_suppression("Resizing redo log from *"); +call mtr.add_suppression("Starting to delete and rewrite log files."); +call mtr.add_suppression("New log files created, LSN=*"); + +create table foo (id varchar(37) not null, content longblob) engine=INNODB; +insert into foo (id, content) values('xyz', ''); +update foo set content=repeat('a', 43941888) where id='xyz'; +drop table foo; + +--disable_query_log +EVAL SET GLOBAL innodb_status_output = $status_orig; +--enable_query_log diff --git a/mysql-test/suite/innodb/t/innodb-fk.test b/mysql-test/suite/innodb/t/innodb-fk.test index 9839cd2d084..f7bcbe238dd 100644 --- a/mysql-test/suite/innodb/t/innodb-fk.test +++ b/mysql-test/suite/innodb/t/innodb-fk.test @@ -84,3 +84,106 @@ while ($i) drop table t1; +# +# MDEV-7672: Crash creating an InnoDB table with foreign keys +# + +CREATE TABLE t1 ( + id int(11) NOT NULL AUTO_INCREMENT, + f1 int(11) DEFAULT NULL, + PRIMARY KEY (id), + CONSTRAINT fk1 FOREIGN KEY (f1) REFERENCES t1 (id) ON DELETE CASCADE +) ENGINE=InnoDB; + +--error 1005 +CREATE TABLE t2 ( + id int(11) NOT NULL AUTO_INCREMENT, + f2 int(11) NOT NULL, + f3 int(11) NOT NULL, + PRIMARY KEY (`id`), + CONSTRAINT fk2 FOREIGN KEY (f2) REFERENCES t1 (`id`) ON DELETE CASCADE, + CONSTRAINT fk3 FOREIGN KEY (f3) REFERENCES t3 (id) ON DELETE CASCADE +) ENGINE=InnoDB; + +show warnings; + +CREATE TABLE t2 ( + id int(11) NOT NULL AUTO_INCREMENT, + f2 int(11) NOT NULL, + f3 int(11) NOT NULL, + PRIMARY KEY (`id`), + CONSTRAINT fk2 FOREIGN KEY (f2) REFERENCES t1 (`id`) ON DELETE CASCADE +) ENGINE=InnoDB; + +--replace_regex /#sql-[0-9_a-f-]*/#sql-temporary/ +--error 1005 +ALTER TABLE t2 ADD CONSTRAINT fk3 FOREIGN KEY (f3) REFERENCES t3 (id) ON DELETE CASCADE; + +--replace_regex /#sql-[0-9_a-f-]*/#sql-temporary/ +show warnings; + +drop table t2; +drop table t1; + +# +# MDEV-7627: Some symbols in table name can cause to Error Code: 1050 when created FK +# + +CREATE TABLE `#departaments` ( + `id_depart` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, + PRIMARY KEY (`id_depart`) +) ENGINE=INNODB DEFAULT CHARSET=utf8; + +CREATE TABLE `#departaments_tree` ( + `id_depart` INT(10) UNSIGNED NOT NULL, + `id_depart_in` INT(10) UNSIGNED NOT NULL, + PRIMARY KEY (`id_depart`,`id_depart_in`), + CONSTRAINT `#departaments_tree_ibfk_1` FOREIGN KEY (`id_depart`) REFERENCES `#departaments` (`id_depart`) +) ENGINE=INNODB DEFAULT CHARSET=utf8; + +ALTER TABLE `#departaments_tree` + ADD FOREIGN KEY (`id_depart_in`) REFERENCES `#departaments`(`id_depart`); + +SHOW CREATE TABLE `#departaments_tree`; + +DROP TABLE `#departaments_tree`; +DROP TABLE `#departaments`; + +CREATE TABLE `boroda` ( + `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, + `a` INT(11) UNSIGNED DEFAULT NULL, + `b` INT(11) UNSIGNED DEFAULT NULL, + PRIMARY KEY (`id`), + KEY `a` (`a`), + CONSTRAINT `boroda_ibfk_1` FOREIGN KEY (`a`) REFERENCES `boroda` (`id`) +) ENGINE=INNODB DEFAULT CHARSET=utf8; + +ALTER TABLE `boroda` + ADD FOREIGN KEY (`b`) REFERENCES `boroda`(`id`); + +ALTER TABLE `boroda` DROP FOREIGN KEY `boroda_ibfk_2`; + +RENAME TABLE `boroda` TO `#boroda`; + +ALTER TABLE `#boroda` +ADD FOREIGN KEY (`b`) REFERENCES `#boroda`(`id`); + +SHOW CREATE TABLE `#boroda`; +DROP TABLE `#boroda`; + +CREATE TABLE `boroda` ( + `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, + `a` INT(11) UNSIGNED DEFAULT NULL, + `b` INT(11) UNSIGNED DEFAULT NULL, + PRIMARY KEY (`id`), + KEY `a` (`a`), + CONSTRAINT `boroda_ibfk_1` FOREIGN KEY (`a`) REFERENCES `boroda` (`id`) +) ENGINE=INNODB DEFAULT CHARSET=utf8; + +RENAME TABLE `boroda` TO `bor#oda`; + +ALTER TABLE `bor#oda` +ADD FOREIGN KEY (`b`) REFERENCES `bor#oda`(`id`); + +SHOW CREATE TABLE `bor#oda`; +DROP TABLE `bor#oda`; diff --git a/mysql-test/suite/innodb/t/innodb-mdev-7408.opt b/mysql-test/suite/innodb/t/innodb-mdev-7408.opt new file mode 100644 index 00000000000..1c256483e10 --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb-mdev-7408.opt @@ -0,0 +1,2 @@ +--innodb_ft_default_stopword + diff --git a/mysql-test/suite/innodb/t/innodb-mdev-7408.test b/mysql-test/suite/innodb/t/innodb-mdev-7408.test new file mode 100644 index 00000000000..d1cd1879bb1 --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb-mdev-7408.test @@ -0,0 +1,16 @@ +--source include/have_innodb.inc + +select @@global.innodb_ft_server_stopword_table; +CREATE TABLE `stop_it-IT` ENGINE = InnoDB SELECT * FROM information_schema.INNODB_FT_DEFAULT_STOPWORD; +--error 1231 +SET @@global.innodb_ft_server_stopword_table = 'test/stop_it-IT'; +--error 0,1231 +SET @@global.innodb_ft_server_stopword_table = 'test/stop_it@002dIT'; +drop table `stop_it-IT`; + +CREATE TABLE stop_it ENGINE = InnoDB SELECT * FROM information_schema.INNODB_FT_DEFAULT_STOPWORD; +SET @@global.innodb_ft_server_stopword_table = 'test/stop_it'; + +SET @@global.innodb_ft_server_stopword_table = NULL; + +drop table stop_it; diff --git a/mysql-test/suite/innodb/t/innodb-mdev-7513.test b/mysql-test/suite/innodb/t/innodb-mdev-7513.test new file mode 100644 index 00000000000..b929ea14781 --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb-mdev-7513.test @@ -0,0 +1,220 @@ +-- source include/have_innodb.inc +-- source include/not_embedded.inc + +# MDEV-7513: ib_warn_row_too_big dereferences null thd + +call mtr.add_suppression("Row size too large (> 8126)*"); + +--disable_warnings +CREATE TABLE t1 ( text1 TEXT, +text2 TEXT, +text3 TEXT, +text4 TEXT, +text5 TEXT, +text6 TEXT, +text7 TEXT, +text8 TEXT, +text9 TEXT, +text10 TEXT, +text11 TEXT, +text12 TEXT, +text13 TEXT, +text14 TEXT, +text15 TEXT, +text16 TEXT, +text17 TEXT, +text18 TEXT, +text19 TEXT, +text20 TEXT, +text21 TEXT, +text22 TEXT, +text23 TEXT, +text24 TEXT, +text25 TEXT, +text26 TEXT, +text27 TEXT, +text28 TEXT, +text29 TEXT, +text30 TEXT, +text31 TEXT, +text32 TEXT, +text33 TEXT, +text34 TEXT, +text35 TEXT, +text36 TEXT, +text37 TEXT, +text38 TEXT, +text39 TEXT, +text40 TEXT, +text41 TEXT, +text42 TEXT, +text43 TEXT, +text44 TEXT, +text45 TEXT, +text46 TEXT, +text47 TEXT, +text48 TEXT, +text49 TEXT, +text50 TEXT, +text51 TEXT, +text52 TEXT, +text53 TEXT, +text54 TEXT, +text55 TEXT, +text56 TEXT, +text57 TEXT, +text58 TEXT, +text59 TEXT, +text60 TEXT, +text61 TEXT, +text62 TEXT, +text63 TEXT, +text64 TEXT, +text65 TEXT, +text66 TEXT, +text67 TEXT, +text68 TEXT, +text69 TEXT, +text70 TEXT, +text71 TEXT, +text72 TEXT, +text73 TEXT, +text74 TEXT, +text75 TEXT, +text76 TEXT, +text77 TEXT, +text78 TEXT, +text79 TEXT, +text80 TEXT, +text81 TEXT, +text82 TEXT, +text83 TEXT, +text84 TEXT, +text85 TEXT, +text86 TEXT, +text87 TEXT, +text88 TEXT, +text89 TEXT, +text90 TEXT, +text91 TEXT, +text92 TEXT, +text93 TEXT, +text94 TEXT, +text95 TEXT, +text96 TEXT, +text97 TEXT, +text98 TEXT, +text99 TEXT, +text100 TEXT, +text101 TEXT, +text102 TEXT, +text103 TEXT, +text104 TEXT, +text105 TEXT, +text106 TEXT, +text107 TEXT, +text108 TEXT, +text109 TEXT, +text110 TEXT, +text111 TEXT, +text112 TEXT, +text113 TEXT, +text114 TEXT, +text115 TEXT, +text116 TEXT, +text117 TEXT, +text118 TEXT, +text119 TEXT, +text120 TEXT, +text121 TEXT, +text122 TEXT, +text123 TEXT, +text124 TEXT, +text125 TEXT, +text126 TEXT, +text127 TEXT, +text128 TEXT, +text129 TEXT, +text130 TEXT, +text131 TEXT, +text132 TEXT, +text133 TEXT, +text134 TEXT, +text135 TEXT, +text136 TEXT, +text137 TEXT, +text138 TEXT, +text139 TEXT, +text140 TEXT, +text141 TEXT, +text142 TEXT, +text143 TEXT, +text144 TEXT, +text145 TEXT, +text146 TEXT, +text147 TEXT, +text148 TEXT, +text149 TEXT, +text150 TEXT, +text151 TEXT, +text152 TEXT, +text153 TEXT, +text154 TEXT, +text155 TEXT, +text156 TEXT, +text157 TEXT, +text158 TEXT, +text159 TEXT, +text160 TEXT, +text161 TEXT, +text162 TEXT, +text163 TEXT, +text164 TEXT, +text165 TEXT, +text166 TEXT, +text167 TEXT, +text168 TEXT, +text169 TEXT, +text170 TEXT, +text171 TEXT, +text172 TEXT, +text173 TEXT, +text174 TEXT, +text175 TEXT, +text176 TEXT, +text177 TEXT, +text178 TEXT, +text179 TEXT, +text180 TEXT, +text181 TEXT, +text182 TEXT, +text183 TEXT, +text184 TEXT, +text185 TEXT, +text186 TEXT, +text187 TEXT, +text188 TEXT, +text189 TEXT, +text190 TEXT, +text191 TEXT, +text192 TEXT, +text193 TEXT, +text194 TEXT, +text195 TEXT, +text196 TEXT, +text197 TEXT +) ENGINE = InnoDB; + +INSERT INTO t1 VALUES ('abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef', 'abcdef'); +--enable_warnings + +DELETE FROM t1 WHERE text1 = 'abcdef'; + +--source include/restart_mysqld.inc + +--disable_warnings +SELECT * from t1; +--enable_warnings + +DROP TABLE t1; + diff --git a/mysql-test/suite/innodb/t/innodb-mdev7046.test b/mysql-test/suite/innodb/t/innodb-mdev7046.test index 4033f284e65..b4085228e02 100644 --- a/mysql-test/suite/innodb/t/innodb-mdev7046.test +++ b/mysql-test/suite/innodb/t/innodb-mdev7046.test @@ -1,5 +1,7 @@ --source include/have_innodb.inc --source include/have_partition.inc +# Test causes OS error printout +--source include/not_windows.inc --disable_query_log --disable_result_log diff --git a/mysql-test/suite/innodb/t/innodb-virtual-columns.test b/mysql-test/suite/innodb/t/innodb-virtual-columns.test new file mode 100644 index 00000000000..368c6fc8cb1 --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb-virtual-columns.test @@ -0,0 +1,302 @@ +--source include/have_innodb.inc + +# +# MDEV-7367: Updating a virtual column corrupts table which crashes server +# MySQL table columns contains virtual columns but InnoDB table +# definition does not. +# +CREATE TABLE IF NOT EXISTS gso_grad_supr ( + term char(4) NOT NULL DEFAULT '', + uw_id int(8) UNSIGNED NOT NULL DEFAULT 0, + plan varchar(10) NOT NULL DEFAULT '', + wdraw_rsn varchar(4) NOT NULL DEFAULT '', + admit_term char(4) NOT NULL DEFAULT '', + CONSTRAINT gso_grad_supr_pky PRIMARY KEY (uw_id, term) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +INSERT INTO `gso_grad_supr` VALUES ('1031',2,'CSM','','1009'); +INSERT INTO `gso_grad_supr` VALUES ('1035',2,'CSM','ACAD','1009'); + +CREATE TABLE IF NOT EXISTS grad_degree ( + student_id int(8) UNSIGNED NOT NULL, + plan varchar(10) NOT NULL, + admit_term char(4) NOT NULL, + wdraw_rsn varchar(4) NOT NULL DEFAULT '', + ofis_deg_status varchar(15) AS ( + CASE + WHEN wdraw_rsn = '' THEN 'In progress' + WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed' + ELSE 'Not Completed' + END) VIRTUAL, + deg_start_term char(4) NOT NULL DEFAULT '' COMMENT 'Educated guess at the beginning of the data', + deg_as_of_term char(4) NOT NULL COMMENT 'In most cases also end term', + CONSTRAINT grad_degree_stu_plan_admit_pky PRIMARY KEY (student_id, plan, admit_term) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +CREATE INDEX grad_degree_wdraw_rsn_ndx ON grad_degree (wdraw_rsn); +CREATE INDEX grad_degree_as_of_term_ndx ON grad_degree (deg_as_of_term); + +INSERT IGNORE grad_degree ( + student_id, + plan, + admit_term, + wdraw_rsn, + deg_start_term, + deg_as_of_term +) +SELECT + ggs.uw_id AS c_student_id, + ggs.plan, + ggs.admit_term, + ggs.wdraw_rsn, + IF( (SELECT COUNT(*) FROM grad_degree AS gd WHERE gd.student_id = ggs.uw_id AND gd.admit_term = ggs.admit_term) > 0, ggs.term, ggs.admit_term ) AS c_deg_start_term, + ggs.term AS c_as_of_term +FROM gso_grad_supr AS ggs +LEFT OUTER JOIN + grad_degree AS gd + ON ( gd.student_id = ggs.uw_id AND gd.plan = ggs.plan AND gd.admit_term = ggs.admit_term ) +WHERE + ggs.term = 1031 AND + gd.student_id IS NULL +; + +UPDATE grad_degree AS gd +INNER JOIN + gso_grad_supr AS ggs + ON ( gd.student_id = ggs.uw_id AND gd.plan = ggs.plan AND gd.admit_term = ggs.admit_term ) +SET + gd.wdraw_rsn = ggs.wdraw_rsn, + gd.deg_as_of_term = 1035 +WHERE + gd.wdraw_rsn NOT IN ('DCMP', 'TRDC') AND + ggs.term = 1035 +; + +drop table grad_degree; + +# +# Test with more virtual columns so that MySQL table has +# more columns than InnoDB and index definition is out +# of number of actual InnoDB columns. +# +CREATE TABLE IF NOT EXISTS grad_degree ( + student_id int(8) UNSIGNED NOT NULL, + plan varchar(10) NOT NULL, + admit_term char(4) NOT NULL, + wdraw_rsn varchar(4) NOT NULL DEFAULT '', + ofis_deg_status varchar(15) AS ( + CASE + WHEN wdraw_rsn = '' THEN 'In progress' + WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed' + ELSE 'Not Completed' + END) VIRTUAL, + ofis_deg_status2 varchar(15) AS ( + CASE + WHEN wdraw_rsn = '' THEN 'In progress2' + WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed2' + ELSE 'Not Completed2' + END) VIRTUAL, + ofis_deg_status3 varchar(15) AS ( + CASE + WHEN wdraw_rsn = '' THEN 'In progress3' + WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed3' + ELSE 'Not Completed3' + END) VIRTUAL, + ofis_deg_status4 varchar(15) AS ( + CASE + WHEN wdraw_rsn = '' THEN 'In progress4' + WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed4' + ELSE 'Not Completed4' + END) VIRTUAL, + ofis_deg_status5 varchar(15) AS ( + CASE + WHEN wdraw_rsn = '' THEN 'In progress5' + WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed5' + ELSE 'Not Completed5' + END) VIRTUAL, + ofis_deg_status6 varchar(15) AS ( + CASE + WHEN wdraw_rsn = '' THEN 'In progress6' + WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed6' + ELSE 'Not Completed6' + END) VIRTUAL, + ofis_deg_status7 varchar(15) AS ( + CASE + WHEN wdraw_rsn = '' THEN 'In progress7' + WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed7' + ELSE 'Not Completed7' + END) VIRTUAL, + ofis_deg_status8 varchar(15) AS ( + CASE + WHEN wdraw_rsn = '' THEN 'In progress8' + WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed8' + ELSE 'Not Completed8' + END) VIRTUAL, + deg_start_term char(4) NOT NULL DEFAULT '' COMMENT 'Educated guess at the beginning of the data', + deg_as_of_term char(4) NOT NULL COMMENT 'In most cases also end term', + CONSTRAINT grad_degree_stu_plan_admit_pky PRIMARY KEY (student_id, plan, admit_term) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +CREATE INDEX grad_degree_wdraw_rsn_ndx ON grad_degree (wdraw_rsn); +CREATE INDEX grad_degree_as_of_term_ndx ON grad_degree (deg_as_of_term); + +INSERT IGNORE grad_degree ( + student_id, + plan, + admit_term, + wdraw_rsn, + deg_start_term, + deg_as_of_term +) +SELECT + ggs.uw_id AS c_student_id, + ggs.plan, + ggs.admit_term, + ggs.wdraw_rsn, + IF( (SELECT COUNT(*) FROM grad_degree AS gd WHERE gd.student_id = ggs.uw_id AND gd.admit_term = ggs.admit_term) > 0, ggs.term, ggs.admit_term ) AS c_deg_start_term, + ggs.term AS c_as_of_term +FROM gso_grad_supr AS ggs +LEFT OUTER JOIN + grad_degree AS gd + ON ( gd.student_id = ggs.uw_id AND gd.plan = ggs.plan AND gd.admit_term = ggs.admit_term ) +WHERE + ggs.term = 1031 AND + gd.student_id IS NULL +; + +UPDATE grad_degree AS gd +INNER JOIN + gso_grad_supr AS ggs + ON ( gd.student_id = ggs.uw_id AND gd.plan = ggs.plan AND gd.admit_term = ggs.admit_term ) +SET + gd.wdraw_rsn = ggs.wdraw_rsn, + gd.deg_as_of_term = 1035 +WHERE + gd.wdraw_rsn NOT IN ('DCMP', 'TRDC') AND + ggs.term = 1035 +; + +# +# Verify that indexes can be dropped +# +ALTER TABLE grad_degree DROP INDEX grad_degree_wdraw_rsn_ndx; + +# +# Verify that we can drop columns +# +ALTER TABLE grad_degree DROP COLUMN deg_start_term; + +SHOW CREATE TABLE grad_degree; + +DROP TABLE grad_degree; + +# +# Verify after dropping virtual columns +# +CREATE TABLE IF NOT EXISTS grad_degree ( + student_id int(8) UNSIGNED NOT NULL, + plan varchar(10) NOT NULL, + admit_term char(4) NOT NULL, + wdraw_rsn varchar(4) NOT NULL DEFAULT '', + ofis_deg_status varchar(15) AS ( + CASE + WHEN wdraw_rsn = '' THEN 'In progress' + WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed' + ELSE 'Not Completed' + END) VIRTUAL, + ofis_deg_status2 varchar(15) AS ( + CASE + WHEN wdraw_rsn = '' THEN 'In progress2' + WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed2' + ELSE 'Not Completed2' + END) VIRTUAL, + ofis_deg_status3 varchar(15) AS ( + CASE + WHEN wdraw_rsn = '' THEN 'In progress3' + WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed3' + ELSE 'Not Completed3' + END) VIRTUAL, + ofis_deg_status4 varchar(15) AS ( + CASE + WHEN wdraw_rsn = '' THEN 'In progress4' + WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed4' + ELSE 'Not Completed4' + END) VIRTUAL, + ofis_deg_status5 varchar(15) AS ( + CASE + WHEN wdraw_rsn = '' THEN 'In progress5' + WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed5' + ELSE 'Not Completed5' + END) VIRTUAL, + ofis_deg_status6 varchar(15) AS ( + CASE + WHEN wdraw_rsn = '' THEN 'In progress6' + WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed6' + ELSE 'Not Completed6' + END) VIRTUAL, + ofis_deg_status7 varchar(15) AS ( + CASE + WHEN wdraw_rsn = '' THEN 'In progress7' + WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed7' + ELSE 'Not Completed7' + END) VIRTUAL, + ofis_deg_status8 varchar(15) AS ( + CASE + WHEN wdraw_rsn = '' THEN 'In progress8' + WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed8' + ELSE 'Not Completed8' + END) VIRTUAL, + deg_start_term char(4) NOT NULL DEFAULT '' COMMENT 'Educated guess at the beginning of the data', + deg_as_of_term char(4) NOT NULL COMMENT 'In most cases also end term', + CONSTRAINT grad_degree_stu_plan_admit_pky PRIMARY KEY (student_id, plan, admit_term) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +CREATE INDEX grad_degree_wdraw_rsn_ndx ON grad_degree (wdraw_rsn); + +ALTER TABLE grad_degree DROP COLUMN ofis_deg_status2, DROP COLUMN ofis_deg_status3, +DROP COLUMN ofis_deg_status4, DROP COLUMN ofis_deg_status5, DROP COLUMN ofis_deg_status6, +DROP COLUMN ofis_deg_status7, DROP COLUMN ofis_deg_status8; + +CREATE INDEX grad_degree_as_of_term_ndx ON grad_degree (deg_as_of_term); + +INSERT IGNORE grad_degree ( + student_id, + plan, + admit_term, + wdraw_rsn, + deg_start_term, + deg_as_of_term +) +SELECT + ggs.uw_id AS c_student_id, + ggs.plan, + ggs.admit_term, + ggs.wdraw_rsn, + IF( (SELECT COUNT(*) FROM grad_degree AS gd WHERE gd.student_id = ggs.uw_id AND gd.admit_term = ggs.admit_term) > 0, ggs.term, ggs.admit_term ) AS c_deg_start_term, + ggs.term AS c_as_of_term +FROM gso_grad_supr AS ggs +LEFT OUTER JOIN + grad_degree AS gd + ON ( gd.student_id = ggs.uw_id AND gd.plan = ggs.plan AND gd.admit_term = ggs.admit_term ) +WHERE + ggs.term = 1031 AND + gd.student_id IS NULL +; + +UPDATE grad_degree AS gd +INNER JOIN + gso_grad_supr AS ggs + ON ( gd.student_id = ggs.uw_id AND gd.plan = ggs.plan AND gd.admit_term = ggs.admit_term ) +SET + gd.wdraw_rsn = ggs.wdraw_rsn, + gd.deg_as_of_term = 1035 +WHERE + gd.wdraw_rsn NOT IN ('DCMP', 'TRDC') AND + ggs.term = 1035 +; + +select * from grad_degree; +select * from gso_grad_supr; + +drop table grad_degree; +drop table gso_grad_supr; diff --git a/mysql-test/suite/innodb/t/innodb-xa.test b/mysql-test/suite/innodb/t/innodb-xa.test new file mode 100644 index 00000000000..d94cd75aa14 --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb-xa.test @@ -0,0 +1,17 @@ +--source include/have_innodb.inc + +--disable_abort_on_error +xa rollback 'xid2'; +drop table if exists t1; +create table t1(a int)engine=innodb; +rollback; +xa start 'xid2'; +insert into `t1` values (1); +savepoint `sv1`; +xa end 'xid2'; +start transaction; +xa prepare 'xid2'; +release savepoint `sv1`; +xa commit 'xid2'; +drop table t1; + diff --git a/mysql-test/suite/innodb/t/innodb_blob_unrecoverable_crash.test b/mysql-test/suite/innodb/t/innodb_blob_unrecoverable_crash.test new file mode 100644 index 00000000000..16fb570737d --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb_blob_unrecoverable_crash.test @@ -0,0 +1,55 @@ +--source include/not_embedded.inc +--source include/not_crashrep.inc +--source include/have_innodb.inc + +call mtr.add_suppression("InnoDB: The total blob data length"); + +let $old_max_allowed_packet = `select @@max_allowed_packet`; +SET GLOBAL max_allowed_packet = 100*1024*1024; + +--echo # Connection big_packets: +connect(big_packets,localhost,root,,); +connection big_packets; + +CREATE TABLE t1 (a BIGINT PRIMARY KEY, b LONGBLOB) ENGINE=InnoDB; + +# Insert a few rows (it doesn't really matter how many). These transactions +# are committed once they are acked, so they should not be lost. +INSERT INTO t1 (a, b) VALUES (1, '1'); +INSERT INTO t1 (a, b) VALUES (2, '2'); +INSERT INTO t1 (a, b) VALUES (3, '3'); +INSERT INTO t1 (a, b) VALUES (4, '4'); +INSERT INTO t1 (a, b) VALUES (5, '5'); + +# The BLOB insert will fail, and should disappear. However all data committed +# up to this point should not be lost. +start transaction; +--replace_regex /\(> [0-9]*\)/(> ####)/ +--error ER_TOO_BIG_ROWSIZE +INSERT INTO t1 (a, b) VALUES (6, REPEAT('a', 20*1024*1024)); + +--echo # Connection default: +connection default; + +# We expect a restart. +--exec echo "restart" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect + +--echo # Quick shutdown and restart server +--shutdown_server 0 + +# Wait for the server to come back up, and reconnect. +--enable_reconnect +--source include/wait_until_connected_again.inc + +--echo # Connection default: +connection default; + +# We should see (1,2,3,4,5) here. +SELECT a FROM t1; + +# Clean up. +DROP TABLE t1; + +--disable_query_log +eval set global max_allowed_packet = $old_max_allowed_packet; +--enable_query_log diff --git a/mysql-test/suite/innodb/t/innodb_bug34300.test b/mysql-test/suite/innodb/t/innodb_bug34300.test index 800f326707d..13c708b48d6 100644 --- a/mysql-test/suite/innodb/t/innodb_bug34300.test +++ b/mysql-test/suite/innodb/t/innodb_bug34300.test @@ -5,6 +5,11 @@ -- source include/have_innodb.inc +if (`select plugin_auth_version <= "5.6.22-MariaDB-72.0" from information_schema.plugins where plugin_name='innodb'`) +{ + --skip Not fixed in XtraDB as of 5.6.22-MariaDB-72.0 or earlier +} + -- disable_query_log -- disable_result_log diff --git a/mysql-test/suite/innodb/t/innodb_corrupt_bit.test b/mysql-test/suite/innodb/t/innodb_corrupt_bit.test index 12c560934ef..d6d77076b83 100644 --- a/mysql-test/suite/innodb/t/innodb_corrupt_bit.test +++ b/mysql-test/suite/innodb/t/innodb_corrupt_bit.test @@ -4,11 +4,6 @@ -- source include/have_innodb.inc -- source include/have_innodb_16k.inc -if (`select plugin_auth_version <= "5.5.40-MariaDB-36.1" from information_schema.plugins where plugin_name='innodb'`) -{ - --skip Not fixed in XtraDB as of 5.5.40-MariaDB-36.1 or earlier -} - # Issues with innodb_change_buffering_debug on Windows, so the test scenario # cannot be created on windows --source include/not_windows.inc @@ -30,10 +25,10 @@ SET GLOBAL innodb_change_buffering_debug = 1; SET UNIQUE_CHECKS=0; CREATE DATABASE pad; -let $i=345; +let $i=338; while ($i) { - --eval CREATE TABLE pad.t$i (a INT PRIMARY KEY) ENGINE=InnoDB; +--eval CREATE TABLE pad.t$i(a INT PRIMARY KEY)ENGINE=InnoDB; dec $i; } diff --git a/mysql-test/suite/innodb/t/insert_debug.test b/mysql-test/suite/innodb/t/insert_debug.test index 666b634bef9..36ceba2ee8b 100644 --- a/mysql-test/suite/innodb/t/insert_debug.test +++ b/mysql-test/suite/innodb/t/insert_debug.test @@ -2,11 +2,6 @@ --source include/have_debug.inc --source include/have_partition.inc -if (`select plugin_auth_version < "5.6.22" from information_schema.plugins where plugin_name='innodb'`) -{ - --skip Not fixed in InnoDB/XtraDB as of 5.6.21 or earlier -} - --echo # --echo # Bug#19904003 INNODB_LIMIT_OPTIMISTIC_INSERT_DEBUG=1 --echo # CAUSES INFINITE PAGE SPLIT diff --git a/mysql-test/suite/innodb/t/sp_temp_table.test b/mysql-test/suite/innodb/t/sp_temp_table.test index 9a6be85fd7e..b2883f551b9 100644 --- a/mysql-test/suite/innodb/t/sp_temp_table.test +++ b/mysql-test/suite/innodb/t/sp_temp_table.test @@ -1,11 +1,6 @@ --source include/have_innodb.inc --source include/big_test.inc -if (`select plugin_auth_version < "5.6.22" from information_schema.plugins where plugin_name='innodb'`) -{ - --skip Not fixed in InnoDB/XtraDB as of 5.6.21 or earlier -} - --echo # --echo # Bug #19306524 FAILING ASSERTION WITH TEMP TABLE FOR A PROCEDURE --echo # CALLED FROM A FUNCTION diff --git a/mysql-test/suite/innodb/t/strict_mode.test b/mysql-test/suite/innodb/t/strict_mode.test index 9b115091f84..86b56a09c0e 100644 --- a/mysql-test/suite/innodb/t/strict_mode.test +++ b/mysql-test/suite/innodb/t/strict_mode.test @@ -1,10 +1,5 @@ --source include/have_innodb.inc -if (`select plugin_auth_version <= "5.5.40-MariaDB-36.1" from information_schema.plugins where plugin_name='innodb'`) -{ - --skip Not fixed in XtraDB as of 5.5.40-MariaDB-36.1 or earlier -} - --echo # --echo # Bug #17852083 PRINT A WARNING WHEN DDL HAS AN ERROR IN --echo # INNODB_STRICT_MODE = 1 diff --git a/mysql-test/suite/innodb/t/xa_recovery.test b/mysql-test/suite/innodb/t/xa_recovery.test new file mode 100644 index 00000000000..837b33cb3c4 --- /dev/null +++ b/mysql-test/suite/innodb/t/xa_recovery.test @@ -0,0 +1,47 @@ +if (`select plugin_auth_version <= "5.6.24" from information_schema.plugins where plugin_name='innodb'`) +{ + --skip Not fixed in InnoDB as of 5.6.24 or earlier +} +--source include/have_innodb.inc +# Embedded server does not support restarting. +--source include/not_embedded.inc + +CREATE TABLE t1 (a INT) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1); +connect (con1,localhost,root); +XA START 'x'; UPDATE t1 set a=2; XA END 'x'; XA PREPARE 'x'; +connection default; + +call mtr.add_suppression("Found 1 prepared XA transactions"); + +# Kill and restart the server. +-- exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect +-- shutdown_server 0 +-- source include/wait_until_disconnected.inc + +-- exec echo "restart" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect +-- enable_reconnect +-- source include/wait_until_connected_again.inc +-- disable_reconnect + +disconnect con1; +connect (con1,localhost,root); +--send SELECT * FROM t1 LOCK IN SHARE MODE + +connection default; +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = 'Sending data' and + info = 'SELECT * FROM t1 LOCK IN SHARE MODE'; +--source include/wait_condition.inc + +--source include/restart_mysqld.inc + +disconnect con1; + +SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; +SELECT * FROM t1; +XA ROLLBACK 'x'; +SELECT * FROM t1; + +DROP TABLE t1; |