diff options
Diffstat (limited to 'mysql-test/r')
100 files changed, 2389 insertions, 764 deletions
diff --git a/mysql-test/r/1st.result b/mysql-test/r/1st.result index 7e4ab09b09d..cb82cb5fe7d 100644 --- a/mysql-test/r/1st.result +++ b/mysql-test/r/1st.result @@ -22,15 +22,11 @@ host index_stats innodb_index_stats innodb_table_stats -ndb_binlog_index plugin proc procs_priv proxies_priv servers -slave_master_info -slave_relay_log_info -slave_worker_info slow_log table_stats tables_priv diff --git a/mysql-test/r/alter_table.result b/mysql-test/r/alter_table.result index b6e99952c23..f01eba1aa05 100644 --- a/mysql-test/r/alter_table.result +++ b/mysql-test/r/alter_table.result @@ -270,8 +270,8 @@ ERROR 42000: Incorrect table name '' drop table t1; drop table if exists t1, t2; Warnings: -Note 1051 Unknown table 't1' -Note 1051 Unknown table 't2' +Note 1051 Unknown table 'test.t1' +Note 1051 Unknown table 'test.t2' create table t1 ( a varchar(10) not null primary key ) engine=myisam; create table t2 ( a varchar(10) not null primary key ) engine=merge union=(t1); flush tables; @@ -556,7 +556,7 @@ create database mysqltest; create table t1 (c1 int); alter table t1 rename mysqltest.t1; drop table t1; -ERROR 42S02: Unknown table 't1' +ERROR 42S02: Unknown table 'test.t1' alter table mysqltest.t1 rename t1; drop table t1; create table t1 (c1 int); @@ -973,7 +973,7 @@ SHOW CREATE TABLE `tt+1`; Table Create Table tt+1 CREATE TEMPORARY TABLE `tt+1` ( `c1` int(11) DEFAULT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 +) ENGINE=MyISAM DEFAULT CHARSET=latin1 DELAY_KEY_WRITE=1 SHOW CREATE TABLE `tt+2`; Table Create Table tt+2 CREATE TEMPORARY TABLE `tt+2` ( @@ -1327,6 +1327,16 @@ CREATE DATABASE db1 CHARACTER SET utf8; CREATE TABLE db1.t1 (bar TINYTEXT, KEY (bar(100))); ALTER TABLE db1.t1 ADD baz INT; DROP DATABASE db1; +# Additional coverage for refactoring which is made as part +# of fix for bug #27480 "Extend CREATE TEMPORARY TABLES privilege +# to allow temp table operations". +# +# At some point the below test case failed on assertion. +DROP TABLE IF EXISTS t1; +CREATE TEMPORARY TABLE t1 (i int) ENGINE=MyISAM; +ALTER TABLE t1 DISCARD TABLESPACE; +ERROR HY000: Storage engine MyISAM of the table `test`.`t1` doesn't have this option +DROP TABLE t1; # # Bug#11938039 RE-EXECUTION OF FRM-ONLY ALTER TABLE WITH RENAME # CLAUSE FAILS OR ABORTS SERVER. @@ -1380,3 +1390,480 @@ t1 CREATE TABLE `t1` ( KEY `x_param1` (`x_param`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t1; +# +# Bug#11938817 ALTER BEHAVIOR DIFFERENT THEN DOCUMENTED +# +DROP TABLE IF EXISTS t1; +CREATE TABLE t1(a INT) engine=innodb; +INSERT INTO t1 VALUES (1), (2); +# This should not do anything +ALTER TABLE t1; +affected rows: 0 +# Check that we rebuild the table +ALTER TABLE t1 engine=innodb; +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +# This should also rebuild the table +ALTER TABLE t1 FORCE; +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +DROP TABLE t1; +# Bug#11748057 (formerly known as 34972): ALTER TABLE statement doesn't +# identify correct column name. +# +CREATE TABLE t1 (c1 int unsigned , c2 char(100) not null default ''); +ALTER TABLE t1 ADD c3 char(16) NOT NULL DEFAULT '' AFTER c2, +MODIFY c2 char(100) NOT NULL DEFAULT '' AFTER c1; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c1` int(10) unsigned DEFAULT NULL, + `c2` char(100) NOT NULL DEFAULT '', + `c3` char(16) NOT NULL DEFAULT '' +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +# +# WL#5534 Online ALTER, Phase 1 +# +# Single thread tests. +# See innodb_mysql_sync.test for multi thread tests. +DROP TABLE IF EXISTS t1; +CREATE TABLE t1(a INT PRIMARY KEY, b INT) engine=InnoDB; +CREATE TABLE m1(a INT PRIMARY KEY, b INT) engine=MyISAM; +INSERT INTO t1 VALUES (1,1), (2,2); +INSERT INTO m1 VALUES (1,1), (2,2); +# +# 1: Test ALGORITHM keyword +# +# --enable_info allows us to see how many rows were updated +# by ALTER TABLE. in-place will show 0 rows, while copy > 0. +ALTER TABLE t1 ADD INDEX i1(b); +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE t1 ADD INDEX i2(b), ALGORITHM= DEFAULT; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE t1 ADD INDEX i3(b), ALGORITHM= COPY; +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE t1 ADD INDEX i4(b), ALGORITHM= INPLACE; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE t1 ADD INDEX i5(b), ALGORITHM= INVALID; +ERROR HY000: Unknown ALGORITHM 'INVALID' +ALTER TABLE m1 ENABLE KEYS; +affected rows: 0 +ALTER TABLE m1 ENABLE KEYS, ALGORITHM= DEFAULT; +affected rows: 0 +ALTER TABLE m1 ENABLE KEYS, ALGORITHM= COPY; +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE m1 ENABLE KEYS, ALGORITHM= INPLACE; +affected rows: 0 +ALTER TABLE t1 DROP INDEX i1, DROP INDEX i2, DROP INDEX i3, DROP INDEX i4; +# +# 2: Test ALGORITHM + old_alter_table +# +SET SESSION old_alter_table= 1; +affected rows: 0 +ALTER TABLE t1 ADD INDEX i1(b); +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE t1 ADD INDEX i2(b), ALGORITHM= DEFAULT; +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE t1 ADD INDEX i3(b), ALGORITHM= COPY; +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE t1 ADD INDEX i4(b), ALGORITHM= INPLACE; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +SET SESSION old_alter_table= 0; +affected rows: 0 +ALTER TABLE t1 DROP INDEX i1, DROP INDEX i2, DROP INDEX i3, DROP INDEX i4; +# +# 3: Test unsupported in-place operation +# +ALTER TABLE t1 ADD COLUMN (c1 INT); +ALTER TABLE t1 ADD COLUMN (c2 INT), ALGORITHM= DEFAULT; +ALTER TABLE t1 ADD COLUMN (c3 INT), ALGORITHM= COPY; +ALTER TABLE t1 ADD COLUMN (c4 INT), ALGORITHM= INPLACE; +ALTER TABLE t1 DROP COLUMN c1, DROP COLUMN c2, DROP COLUMN c3, DROP COLUMN c4; +# +# 4: Test LOCK keyword +# +ALTER TABLE t1 ADD INDEX i1(b), LOCK= DEFAULT; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE t1 ADD INDEX i2(b), LOCK= NONE; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE t1 ADD INDEX i3(b), LOCK= SHARED; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE t1 ADD INDEX i4(b), LOCK= EXCLUSIVE; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE t1 ADD INDEX i5(b), LOCK= INVALID; +ERROR HY000: Unknown LOCK type 'INVALID' +ALTER TABLE m1 ENABLE KEYS, LOCK= DEFAULT; +ALTER TABLE m1 ENABLE KEYS, LOCK= NONE; +ERROR 0A000: LOCK=NONE/SHARED is not supported for this operation. Try LOCK=EXCLUSIVE. +ALTER TABLE m1 ENABLE KEYS, LOCK= SHARED; +ERROR 0A000: LOCK=NONE/SHARED is not supported for this operation. Try LOCK=EXCLUSIVE. +ALTER TABLE m1 ENABLE KEYS, LOCK= EXCLUSIVE; +ALTER TABLE t1 DROP INDEX i1, DROP INDEX i2, DROP INDEX i3, DROP INDEX i4; +# +# 5: Test ALGORITHM + LOCK +# +ALTER TABLE t1 ADD INDEX i1(b), ALGORITHM= INPLACE, LOCK= NONE; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE t1 ADD INDEX i2(b), ALGORITHM= INPLACE, LOCK= SHARED; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE t1 ADD INDEX i3(b), ALGORITHM= INPLACE, LOCK= EXCLUSIVE; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE t1 ADD INDEX i4(b), ALGORITHM= COPY, LOCK= NONE; +ERROR 0A000: LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED. +ALTER TABLE t1 ADD INDEX i5(b), ALGORITHM= COPY, LOCK= SHARED; +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE t1 ADD INDEX i6(b), ALGORITHM= COPY, LOCK= EXCLUSIVE; +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE m1 ENABLE KEYS, ALGORITHM= INPLACE, LOCK= NONE; +ERROR 0A000: LOCK=NONE/SHARED is not supported for this operation. Try LOCK=EXCLUSIVE. +ALTER TABLE m1 ENABLE KEYS, ALGORITHM= INPLACE, LOCK= SHARED; +ERROR 0A000: LOCK=NONE/SHARED is not supported for this operation. Try LOCK=EXCLUSIVE. +ALTER TABLE m1 ENABLE KEYS, ALGORITHM= INPLACE, LOCK= EXCLUSIVE; +affected rows: 0 +ALTER TABLE m1 ENABLE KEYS, ALGORITHM= COPY, LOCK= NONE; +ERROR 0A000: LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED. +ALTER TABLE m1 ENABLE KEYS, ALGORITHM= COPY, LOCK= SHARED; +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE m1 ENABLE KEYS, ALGORITHM= COPY, LOCK= EXCLUSIVE; +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +DROP TABLE t1, m1; +# +# 6: Possible deadlock involving thr_lock.c +# +CREATE TABLE t1(a INT PRIMARY KEY, b INT); +INSERT INTO t1 VALUES (1,1), (2,2); +START TRANSACTION; +INSERT INTO t1 VALUES (3,3); +# Connection con1 +# Sending: +ALTER TABLE t1 DISABLE KEYS; +# Connection default +# Waiting until ALTER TABLE is blocked. +UPDATE t1 SET b = 4; +COMMIT; +# Connection con1 +# Reaping: ALTER TABLE t1 DISABLE KEYS +# Connection default +DROP TABLE t1; +# +# 7: Which operations require copy and which can be done in-place? +# +# Test which ALTER TABLE operations are done in-place and +# which operations are done using temporary table copy. +# +# --enable_info allows us to see how many rows were updated +# by ALTER TABLE. in-place will show 0 rows, while copy > 0. +# +DROP TABLE IF EXISTS ti1, ti2, ti3, tm1, tm2, tm3; +# Single operation tests +CREATE TABLE ti1(a INT NOT NULL, b INT, c INT) engine=InnoDB; +CREATE TABLE tm1(a INT NOT NULL, b INT, c INT) engine=MyISAM; +CREATE TABLE ti2(a INT PRIMARY KEY AUTO_INCREMENT, b INT, c INT) engine=InnoDB; +CREATE TABLE tm2(a INT PRIMARY KEY AUTO_INCREMENT, b INT, c INT) engine=MyISAM; +INSERT INTO ti1 VALUES (1,1,1), (2,2,2); +INSERT INTO ti2 VALUES (1,1,1), (2,2,2); +INSERT INTO tm1 VALUES (1,1,1), (2,2,2); +INSERT INTO tm2 VALUES (1,1,1), (2,2,2); +ALTER TABLE ti1; +affected rows: 0 +ALTER TABLE tm1; +affected rows: 0 +ALTER TABLE ti1 ADD COLUMN d VARCHAR(200); +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE tm1 ADD COLUMN d VARCHAR(200); +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE ti1 ADD COLUMN d2 VARCHAR(200); +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE tm1 ADD COLUMN d2 VARCHAR(200); +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE ti1 ADD COLUMN e ENUM('a', 'b') FIRST; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE tm1 ADD COLUMN e ENUM('a', 'b') FIRST; +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE ti1 ADD COLUMN f INT AFTER a; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE tm1 ADD COLUMN f INT AFTER a; +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE ti1 ADD INDEX ii1(b); +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE tm1 ADD INDEX im1(b); +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE ti1 ADD UNIQUE INDEX ii2 (c); +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE tm1 ADD UNIQUE INDEX im2 (c); +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE ti1 ADD FULLTEXT INDEX ii3 (d); +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 1 +Warnings: +Warning 124 InnoDB rebuilding table to add column FTS_DOC_ID +ALTER TABLE tm1 ADD FULLTEXT INDEX im3 (d); +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE ti1 ADD FULLTEXT INDEX ii4 (d2); +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE tm1 ADD FULLTEXT INDEX im4 (d2); +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE ti1 ADD PRIMARY KEY(a), ALGORITHM=INPLACE; +ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: InnoDB presently supports one FULLTEXT index creation at a time. Try ALGORITHM=COPY. +ALTER TABLE ti1 ADD PRIMARY KEY(a); +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE tm1 ADD PRIMARY KEY(a); +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE ti1 DROP INDEX ii3; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE tm1 DROP INDEX im3; +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE ti1 DROP COLUMN d2; +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE tm1 DROP COLUMN d2; +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE ti1 ADD CONSTRAINT fi1 FOREIGN KEY (b) REFERENCES ti2(a); +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE tm1 ADD CONSTRAINT fm1 FOREIGN KEY (b) REFERENCES tm2(a); +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE ti1 ALTER COLUMN b SET DEFAULT 1; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE tm1 ALTER COLUMN b SET DEFAULT 1; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE ti1 ALTER COLUMN b DROP DEFAULT; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE tm1 ALTER COLUMN b DROP DEFAULT; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE ti1 CHANGE COLUMN f g INT; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE tm1 CHANGE COLUMN f g INT; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE ti1 CHANGE COLUMN g h VARCHAR(20); +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE tm1 CHANGE COLUMN g h VARCHAR(20); +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE ti1 MODIFY COLUMN e ENUM('a', 'b', 'c'); +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE tm1 MODIFY COLUMN e ENUM('a', 'b', 'c'); +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE ti1 MODIFY COLUMN e INT; +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE tm1 MODIFY COLUMN e INT; +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE ti1 MODIFY COLUMN e INT AFTER h; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE tm1 MODIFY COLUMN e INT AFTER h; +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE ti1 MODIFY COLUMN e INT FIRST; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE tm1 MODIFY COLUMN e INT FIRST; +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +SET @orig_sql_mode = @@sql_mode; +SET @@sql_mode = 'STRICT_TRANS_TABLES'; +ALTER TABLE ti1 MODIFY COLUMN c INT NOT NULL; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +SET @@sql_mode = @orig_sql_mode; +ALTER TABLE tm1 MODIFY COLUMN c INT NOT NULL; +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE ti1 MODIFY COLUMN c INT NULL; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE tm1 MODIFY COLUMN c INT NULL; +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE ti1 MODIFY COLUMN h VARCHAR(30); +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE tm1 MODIFY COLUMN h VARCHAR(30); +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE ti1 MODIFY COLUMN h VARCHAR(30) AFTER d; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE tm1 MODIFY COLUMN h VARCHAR(30) AFTER d; +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE ti1 DROP COLUMN h; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE tm1 DROP COLUMN h; +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE ti1 DROP INDEX ii2; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE tm1 DROP INDEX im2; +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE ti1 DROP PRIMARY KEY; +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE tm1 DROP PRIMARY KEY; +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE ti1 DROP FOREIGN KEY fi1; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE tm1 DROP FOREIGN KEY fm1; +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE ti1 RENAME TO ti3; +affected rows: 0 +ALTER TABLE tm1 RENAME TO tm3; +affected rows: 0 +ALTER TABLE ti3 RENAME TO ti1; +affected rows: 0 +ALTER TABLE tm3 RENAME TO tm1; +affected rows: 0 +ALTER TABLE ti1 ORDER BY b; +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE tm1 ORDER BY b; +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE ti1 CONVERT TO CHARACTER SET utf16; +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE tm1 CONVERT TO CHARACTER SET utf16; +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE ti1 DEFAULT CHARACTER SET utf8; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE tm1 DEFAULT CHARACTER SET utf8; +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE ti1 FORCE; +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE tm1 FORCE; +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE ti1 AUTO_INCREMENT 3; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE tm1 AUTO_INCREMENT 3; +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE ti1 AVG_ROW_LENGTH 10; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE tm1 AVG_ROW_LENGTH 10; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE ti1 CHECKSUM 1; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE tm1 CHECKSUM 1; +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE ti1 COMMENT 'test'; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE tm1 COMMENT 'test'; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE ti1 MAX_ROWS 100; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE tm1 MAX_ROWS 100; +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +ALTER TABLE ti1 MIN_ROWS 1; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE tm1 MIN_ROWS 1; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE ti1 PACK_KEYS 1; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE tm1 PACK_KEYS 1; +affected rows: 2 +info: Records: 2 Duplicates: 0 Warnings: 0 +DROP TABLE ti1, ti2, tm1, tm2; +# Tests of >1 operation (InnoDB) +CREATE TABLE ti1(a INT PRIMARY KEY AUTO_INCREMENT, b INT) engine=InnoDB; +INSERT INTO ti1(b) VALUES (1), (2); +ALTER TABLE ti1 RENAME TO ti3, ADD INDEX ii1(b); +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +ALTER TABLE ti3 DROP INDEX ii1, AUTO_INCREMENT 5; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +INSERT INTO ti3(b) VALUES (5); +ALTER TABLE ti3 ADD INDEX ii1(b), AUTO_INCREMENT 7; +affected rows: 0 +info: Records: 0 Duplicates: 0 Warnings: 0 +INSERT INTO ti3(b) VALUES (7); +SELECT * FROM ti3; +a b +1 1 +2 2 +5 5 +7 7 +DROP TABLE ti3; +# +# 8: Scenario in which ALTER TABLE was returning an unwarranted +# ER_ILLEGAL_HA error at some point during work on this WL. +# +CREATE TABLE tm1(i INT DEFAULT 1) engine=MyISAM; +ALTER TABLE tm1 ADD INDEX ii1(i), ALTER COLUMN i DROP DEFAULT; +DROP TABLE tm1; diff --git a/mysql-test/r/alter_table_online.result b/mysql-test/r/alter_table_online.result index 83e82191541..1e7bc5e83cd 100644 --- a/mysql-test/r/alter_table_online.result +++ b/mysql-test/r/alter_table_online.result @@ -11,61 +11,59 @@ drop table t1; create temporary table t1 (a int not null primary key, b int, c varchar(80), e enum('a','b')); insert into t1 (a) values (1),(2),(3); alter online table t1 modify b int default 5; -ERROR HY000: Can't execute the given 'ALTER' command as online +ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY. alter online table t1 change b new_name int; -ERROR HY000: Can't execute the given 'ALTER' command as online +ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY. alter online table t1 modify e enum('a','b','c'); -ERROR HY000: Can't execute the given 'ALTER' command as online +ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY. alter online table t1 comment "new comment"; -ERROR HY000: Can't execute the given 'ALTER' command as online +ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY. alter online table t1 rename to t2; -ERROR HY000: Can't execute the given 'ALTER' command as online +ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY. drop table t1; create table t1 (a int not null primary key, b int, c varchar(80), e enum('a','b')); insert into t1 (a) values (1),(2),(3); alter online table t1 drop column b, add b int; -ERROR HY000: Can't execute the given 'ALTER' command as online +ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY. alter online table t1 modify b bigint; -ERROR HY000: Can't execute the given 'ALTER' command as online +ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY. alter online table t1 modify e enum('c','a','b'); -ERROR HY000: Can't execute the given 'ALTER' command as online +ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY. alter online table t1 modify c varchar(50); -ERROR HY000: Can't execute the given 'ALTER' command as online +ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY. alter online table t1 modify c varchar(100); -ERROR HY000: Can't execute the given 'ALTER' command as online +ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY. alter online table t1 add f int; -ERROR HY000: Can't execute the given 'ALTER' command as online +ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY. alter online table t1 engine=memory; -ERROR HY000: Can't execute the given 'ALTER' command as online +ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY. alter table t1 engine=innodb; alter table t1 add index (b); alter online table t1 add index c (c); -ERROR HY000: Can't execute the given 'ALTER' command as online alter online table t1 drop index b; -ERROR HY000: Can't execute the given 'ALTER' command as online drop table t1; create temporary table t1 (a int not null primary key, b int, c varchar(80), e enum('a','b')); insert into t1 (a) values (1),(2),(3); alter online table t1 drop column b, add b int; -ERROR HY000: Can't execute the given 'ALTER' command as online +ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY. alter online table t1 modify b bigint; -ERROR HY000: Can't execute the given 'ALTER' command as online +ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY. alter online table t1 modify e enum('c','a','b'); -ERROR HY000: Can't execute the given 'ALTER' command as online +ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY. alter online table t1 modify c varchar(50); -ERROR HY000: Can't execute the given 'ALTER' command as online +ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY. alter online table t1 modify c varchar(100); -ERROR HY000: Can't execute the given 'ALTER' command as online +ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY. alter online table t1 add f int; -ERROR HY000: Can't execute the given 'ALTER' command as online +ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY. alter online table t1 engine=memory; -ERROR HY000: Can't execute the given 'ALTER' command as online +ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY. alter table t1 engine=innodb; alter table t1 add index (b); alter online table t1 add index c (c); -ERROR HY000: Can't execute the given 'ALTER' command as online +ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY. alter online table t1 drop index b; -ERROR HY000: Can't execute the given 'ALTER' command as online +ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY. drop table t1; create table t1 (a int not null primary key, b int, c varchar(80)); create table t2 (a int not null primary key, b int, c varchar(80)); diff --git a/mysql-test/r/bootstrap.result b/mysql-test/r/bootstrap.result index 8bef6f90ab4..2e2082441f8 100644 --- a/mysql-test/r/bootstrap.result +++ b/mysql-test/r/bootstrap.result @@ -1,7 +1,7 @@ drop table if exists t1; drop table t1; drop table t1; -ERROR 42S02: Unknown table 't1' +ERROR 42S02: Unknown table 'test.t1' set @my_max_allowed_packet= @@max_allowed_packet; set global max_allowed_packet=100*@@max_allowed_packet; set global max_allowed_packet=@my_max_allowed_packet; diff --git a/mysql-test/r/cast.result b/mysql-test/r/cast.result index 03b4b84e461..3b57b4833a9 100644 --- a/mysql-test/r/cast.result +++ b/mysql-test/r/cast.result @@ -768,13 +768,19 @@ CAST(CAST('20:05:05' AS TIME) as date) set sql_mode= TRADITIONAL; select cast("2101-00-01 02:03:04" as datetime); cast("2101-00-01 02:03:04" as datetime) -2101-00-01 02:03:04 +NULL +Warnings: +Warning 1292 Incorrect datetime value: '2101-00-01 02:03:04' select cast(cast("2101-00-01 02:03:04" as datetime) as time); cast(cast("2101-00-01 02:03:04" as datetime) as time) -02:03:04 +NULL +Warnings: +Warning 1292 Incorrect datetime value: '2101-00-01 02:03:04' SELECT CAST(CAST('20:05:05' AS TIME) as date); CAST(CAST('20:05:05' AS TIME) as date) -0000-00-00 +NULL +Warnings: +Warning 1292 Truncated incorrect date value: '0000-00-00' set sql_mode=DEFAULT; create table t1 (f1 time, f2 date, f3 datetime); insert into t1 values ('11:22:33','2011-12-13','2011-12-13 11:22:33'); diff --git a/mysql-test/r/commit_1innodb.result b/mysql-test/r/commit_1innodb.result index af198edc4ca..3583e8ed396 100644 --- a/mysql-test/r/commit_1innodb.result +++ b/mysql-test/r/commit_1innodb.result @@ -1,6 +1,6 @@ call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT"); set sql_mode=no_engine_substitution; -set storage_engine = InnoDB; +set default_storage_engine = InnoDB; set autocommit=1; drop table if exists t1; drop table if exists t2; @@ -842,7 +842,7 @@ call p_verify_status_increment(2, 0, 2, 0); SUCCESS alter table t3 rename t4; -call p_verify_status_increment(2, 0, 2, 0); +call p_verify_status_increment(0, 0, 0, 0); SUCCESS rename table t4 to t3; diff --git a/mysql-test/r/connect.result b/mysql-test/r/connect.result index 3fcc5b5d2c1..84a1e9dbab2 100644 --- a/mysql-test/r/connect.result +++ b/mysql-test/r/connect.result @@ -16,15 +16,11 @@ host index_stats innodb_index_stats innodb_table_stats -ndb_binlog_index plugin proc procs_priv proxies_priv servers -slave_master_info -slave_relay_log_info -slave_worker_info slow_log table_stats tables_priv @@ -59,15 +55,11 @@ host index_stats innodb_index_stats innodb_table_stats -ndb_binlog_index plugin proc procs_priv proxies_priv servers -slave_master_info -slave_relay_log_info -slave_worker_info slow_log table_stats tables_priv @@ -110,15 +102,11 @@ host index_stats innodb_index_stats innodb_table_stats -ndb_binlog_index plugin proc procs_priv proxies_priv servers -slave_master_info -slave_relay_log_info -slave_worker_info slow_log table_stats tables_priv diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result index d0d953f4e38..fff8733cfdf 100644 --- a/mysql-test/r/create.result +++ b/mysql-test/r/create.result @@ -28,15 +28,15 @@ create table t2 select auto+1 from t1; ERROR 42S02: Table 'test.t1' doesn't exist drop table if exists t1,t2; Warnings: -Note 1051 Unknown table 't1' -Note 1051 Unknown table 't2' +Note 1051 Unknown table 'test.t1' +Note 1051 Unknown table 'test.t2' create table t1 (b char(0) not null, index(b)); ERROR 42000: The storage engine MyISAM can't index column `b` create table t1 (a int not null,b text) engine=heap; ERROR 42000: Storage engine MEMORY doesn't support BLOB/TEXT columns drop table if exists t1; Warnings: -Note 1051 Unknown table 't1' +Note 1051 Unknown table 'test.t1' create table t1 (ordid int(8) not null auto_increment, ord varchar(50) not null, primary key (ord,ordid)) engine=heap; ERROR 42000: Incorrect table definition; there can be only one auto column and it must be defined as a key create table not_existing_database.test (a int); @@ -157,17 +157,17 @@ create table t2 (a int, a float) select * from t1; ERROR 42S21: Duplicate column name 'a' drop table if exists t2; Warnings: -Note 1051 Unknown table 't2' +Note 1051 Unknown table 'test.t2' create table t2 (a int) select a as b, a+1 as b from t1; ERROR 42S21: Duplicate column name 'b' drop table if exists t2; Warnings: -Note 1051 Unknown table 't2' +Note 1051 Unknown table 'test.t2' create table t2 (b int) select a as b, a+1 as b from t1; ERROR 42S21: Duplicate column name 'b' drop table if exists t1,t2; Warnings: -Note 1051 Unknown table 't2' +Note 1051 Unknown table 'test.t2' CREATE TABLE t1 (a int not null); INSERT INTO t1 values (1),(2),(1); CREATE TABLE t2 (primary key(a)) SELECT * FROM t1; @@ -177,7 +177,7 @@ ERROR 42S02: Table 'test.t2' doesn't exist DROP TABLE t1; DROP TABLE IF EXISTS t2; Warnings: -Note 1051 Unknown table 't2' +Note 1051 Unknown table 'test.t2' create table t1 (a int not null, b int, primary key(a), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b)); show create table t1; Table Create Table @@ -1610,12 +1610,12 @@ CREATE TABLE t2 (primary key (a)) select * from t1; ERROR 23000: Duplicate entry '1' for key 'PRIMARY' drop table if exists t2; Warnings: -Note 1051 Unknown table 't2' +Note 1051 Unknown table 'test.t2' CREATE TEMPORARY TABLE t2 (primary key (a)) select * from t1; ERROR 23000: Duplicate entry '1' for key 'PRIMARY' drop table if exists t2; Warnings: -Note 1051 Unknown table 't2' +Note 1051 Unknown table 'test.t2' CREATE TABLE t2 (a int, b int, primary key (a)); INSERT INTO t2 select * from t1; ERROR 23000: Duplicate entry '1' for key 'PRIMARY' diff --git a/mysql-test/r/ctype_errors.result b/mysql-test/r/ctype_errors.result index 90d0c28eebf..5ae8c53ce8b 100644 --- a/mysql-test/r/ctype_errors.result +++ b/mysql-test/r/ctype_errors.result @@ -24,11 +24,11 @@ lc_messages ru_RU SET GLOBAL lc_messages=en_US; DROP TABLE t1; drop table `ק`; -ERROR 42S02: Unknown table 'ק' +ERROR 42S02: Unknown table 'test.ק' SET lc_messages=cs_CZ; SET NAMES UTF8; USE nonexistant; -ERROR 42000: Nezn-Bámá databáze 'nonexistant' +ERROR 42000: Neznámá databáze 'nonexistant' # # Bug#12736295: Buffer overflow for variable converted_err # with non-latin1 server error message diff --git a/mysql-test/r/ctype_tis620.result b/mysql-test/r/ctype_tis620.result index 5699c044d70..c86b8392b32 100644 --- a/mysql-test/r/ctype_tis620.result +++ b/mysql-test/r/ctype_tis620.result @@ -138,7 +138,7 @@ year DROP TABLE t1; DROP TABLE IF EXISTS t1; Warnings: -Note 1051 Unknown table 't1' +Note 1051 Unknown table 'test.t1' CREATE TABLE t1 ( name varchar(50) NOT NULL default '', diff --git a/mysql-test/r/ctype_ujis.result b/mysql-test/r/ctype_ujis.result index b801a7f45a4..3db6aee37cc 100644 --- a/mysql-test/r/ctype_ujis.result +++ b/mysql-test/r/ctype_ujis.result @@ -94,7 +94,7 @@ select @ujis4 = CONVERT(@utf84 USING ujis); 1 drop table if exists t1; Warnings: -Note 1051 Unknown table 't1' +Note 1051 Unknown table 'test.t1' create table t1 (c1 varchar(8)) default character set 'ujis'; insert into t1 values (0xA4A2),(0xA2A2),(0xA4A2); select c1 as 'no index' from t1 where c1 like cast(concat(0xA4A2, '%') as char character set ujis); @@ -168,7 +168,7 @@ a b DROP TABLE t1; DROP TABLE IF EXISTS t1; Warnings: -Note 1051 Unknown table 't1' +Note 1051 Unknown table 'test.t1' CREATE TABLE t1(c char(1)) character set ujis; INSERT INTO t1 VALUES(0xA2AF); INSERT INTO t1 VALUES(0xA2B0); diff --git a/mysql-test/r/ctype_utf8.result b/mysql-test/r/ctype_utf8.result index d25c454913d..714b4183594 100644 --- a/mysql-test/r/ctype_utf8.result +++ b/mysql-test/r/ctype_utf8.result @@ -1229,7 +1229,7 @@ DROP TABLE t1; SET NAMES utf8; DROP TABLE IF EXISTS t1; Warnings: -Note 1051 Unknown table 't1' +Note 1051 Unknown table 'test.t1' CREATE TABLE t1(a VARCHAR(255), KEY(a)) ENGINE=MyISAM DEFAULT CHARSET=utf8; INSERT INTO t1 VALUES('uuABCDEFGHIGKLMNOPRSTUVWXYZ̈bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb'); INSERT INTO t1 VALUES('uu'); diff --git a/mysql-test/r/ctype_utf8mb4.result b/mysql-test/r/ctype_utf8mb4.result index d8642955b89..f4be208e0f7 100644 --- a/mysql-test/r/ctype_utf8mb4.result +++ b/mysql-test/r/ctype_utf8mb4.result @@ -1256,7 +1256,7 @@ DROP TABLE t1; SET NAMES utf8mb4; DROP TABLE IF EXISTS t1; Warnings: -Note 1051 Unknown table 't1' +Note 1051 Unknown table 'test.t1' CREATE TABLE t1(a VARCHAR(255), KEY(a)) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4; Warnings: Warning 1071 Specified key was too long; max key length is 1000 bytes @@ -2452,7 +2452,6 @@ MODIFY subject varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, MODIFY p varchar(255) CHARACTER SET utf8; Warnings: Warning 1071 Specified key was too long; max key length is 1000 bytes -Warning 1071 Specified key was too long; max key length is 1000 bytes SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( @@ -2539,8 +2538,7 @@ t2 CREATE TABLE `t2` ( ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t1, t2; # -# Bug#13581962 HIGH MEMORY USAGE ATTEMPT, THEN CRASH WITH -# LONGTEXT, UNION, USER VARIABLE +# Bug#13581962 HIGH MEMORY USAGE ATTEMPT, THEN CRASH WITH LONGTEXT, UNION, USER VARIABLE # Bug#14096619 UNABLE TO RESTORE DATABASE DUMP # CREATE TABLE t1(f1 LONGTEXT CHARACTER SET utf8mb4); diff --git a/mysql-test/r/ctype_utf8mb4_heap.result b/mysql-test/r/ctype_utf8mb4_heap.result index 63de75b37b7..94ea59c1a0c 100644 --- a/mysql-test/r/ctype_utf8mb4_heap.result +++ b/mysql-test/r/ctype_utf8mb4_heap.result @@ -1160,7 +1160,7 @@ DROP TABLE t1; SET NAMES utf8mb4; DROP TABLE IF EXISTS t1; Warnings: -Note 1051 Unknown table 't1' +Note 1051 Unknown table 'test.t1' CREATE TABLE t1(a VARCHAR(255), KEY(a)) ENGINE=heap DEFAULT CHARSET=utf8mb4; INSERT INTO t1 VALUES('uuABCDEFGHIGKLMNOPRSTUVWXYZ̈bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb'); INSERT INTO t1 VALUES('uu'); diff --git a/mysql-test/r/ctype_utf8mb4_innodb.result b/mysql-test/r/ctype_utf8mb4_innodb.result index 2db7066d478..b0e5bcef176 100644 --- a/mysql-test/r/ctype_utf8mb4_innodb.result +++ b/mysql-test/r/ctype_utf8mb4_innodb.result @@ -1231,7 +1231,7 @@ DROP TABLE t1; SET NAMES utf8mb4; DROP TABLE IF EXISTS t1; Warnings: -Note 1051 Unknown table 't1' +Note 1051 Unknown table 'test.t1' CREATE TABLE t1(a VARCHAR(255), KEY(a)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; Warnings: Warning 1071 Specified key was too long; max key length is 767 bytes diff --git a/mysql-test/r/ctype_utf8mb4_myisam.result b/mysql-test/r/ctype_utf8mb4_myisam.result index b82e5687eda..6f5d79ff6df 100644 --- a/mysql-test/r/ctype_utf8mb4_myisam.result +++ b/mysql-test/r/ctype_utf8mb4_myisam.result @@ -1231,7 +1231,7 @@ DROP TABLE t1; SET NAMES utf8mb4; DROP TABLE IF EXISTS t1; Warnings: -Note 1051 Unknown table 't1' +Note 1051 Unknown table 'test.t1' CREATE TABLE t1(a VARCHAR(255), KEY(a)) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4; Warnings: Warning 1071 Specified key was too long; max key length is 1000 bytes diff --git a/mysql-test/r/drop.result b/mysql-test/r/drop.result index 55309e54fb3..209eb896978 100644 --- a/mysql-test/r/drop.result +++ b/mysql-test/r/drop.result @@ -2,7 +2,7 @@ drop table if exists t1; drop database if exists mysqltest; drop database if exists client_test_db; drop table t1; -ERROR 42S02: Unknown table 't1' +ERROR 42S02: Unknown table 'test.t1' create table t1(n int); insert into t1 values(1); create temporary table t1( n int); @@ -30,13 +30,13 @@ table7, table8, table9, table10, table11, table12, table13, table14, table15, table16, table17, table18, table19, table20, table21, table22, table23, table24, table25, table26, table27, table28; -ERROR 42S02: Unknown table 'table1,table2,table3,table4,table5,table6,table7,table8,table9,table10,table11,table12,table13,table' +ERROR 42S02: Unknown table 'mysqltest.table1,mysqltest.table2,mysqltest.table3,mysqltest.table4,mysqltest.table5,mysqltest.table' drop table table1, table2, table3, table4, table5, table6, table7, table8, table9, table10, table11, table12, table13, table14, table15, table16, table17, table18, table19, table20, table21, table22, table23, table24, table25, table26, table27, table28, table29, table30; -ERROR 42S02: Unknown table 'table1,table2,table3,table4,table5,table6,table7,table8,table9,table10,table11,table12,table13,table' +ERROR 42S02: Unknown table 'mysqltest.table1,mysqltest.table2,mysqltest.table3,mysqltest.table4,mysqltest.table5,mysqltest.table' use test; drop database mysqltest; flush tables with read lock; @@ -154,10 +154,10 @@ End of 5.1 tests # -- DROP TABLE IF EXISTS t1; DROP TABLE t1; -ERROR 42S02: Unknown table 't1' +ERROR 42S02: Unknown table 'test.t1' SHOW WARNINGS; Level Code Message -Error 1051 Unknown table 't1' +Error 1051 Unknown table 'test.t1' # -- # -- End of Bug#37431. diff --git a/mysql-test/r/dyncol.result b/mysql-test/r/dyncol.result index 172179bd7f4..c5040728af7 100644 --- a/mysql-test/r/dyncol.result +++ b/mysql-test/r/dyncol.result @@ -1036,9 +1036,7 @@ Warnings: Warning 1292 Truncated incorrect time value: '2011-13-01 8:46:06.23434' select column_get(column_create(1, "2011-02-30 8:46:06.23434" AS CHAR), 1 as time); column_get(column_create(1, "2011-02-30 8:46:06.23434" AS CHAR), 1 as time) -NULL -Warnings: -Warning 1292 Truncated incorrect time value: '2011-02-30 8:46:06.23434' +08:46:06 select column_get(column_create(1, "2001-02-03"), 1 as time); column_get(column_create(1, "2001-02-03"), 1 as time) 00:20:01 diff --git a/mysql-test/r/error_simulation.result b/mysql-test/r/error_simulation.result index d2f5a24ef1d..88a9d114bc6 100644 --- a/mysql-test/r/error_simulation.result +++ b/mysql-test/r/error_simulation.result @@ -1,6 +1,6 @@ DROP TABLE IF EXISTS t1; Warnings: -Note 1051 Unknown table 't1' +Note 1051 Unknown table 'test.t1' CREATE TABLE t1 ( a varchar(32) character set utf8 collate utf8_bin NOT NULL, b varchar(32) character set utf8 collate utf8_bin NOT NULL ) diff --git a/mysql-test/r/events_restart.result b/mysql-test/r/events_restart.result index 6a751fa29f8..ba3aa503b63 100644 --- a/mysql-test/r/events_restart.result +++ b/mysql-test/r/events_restart.result @@ -65,3 +65,26 @@ select @@event_scheduler; ON drop table execution_log; drop database events_test; +# +# Test for bug#11748899 -- EVENT SET TO DISABLED AND ON COMPLETION +# NOT PRESERVE IS DELETED AT SERVER +# +SELECT @@event_scheduler; +@@event_scheduler +ON +USE test; +DROP EVENT IF EXISTS e1; +CREATE EVENT e1 ON SCHEDULE EVERY 1 SECOND DISABLE DO SELECT 1; +SHOW EVENTS; +Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation +test e1 root@localhost SYSTEM RECURRING # 1 SECOND # # DISABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci +"Now we restart the server" +USE test; +SELECT @@event_scheduler; +@@event_scheduler +ON +SHOW EVENTS; +Db Name Definer Time zone Type Execute at Interval value Interval field Starts Ends Status Originator character_set_client collation_connection Database Collation +test e1 root@localhost SYSTEM RECURRING # 1 SECOND # # DISABLED 1 latin1 latin1_swedish_ci latin1_swedish_ci +DROP EVENT e1; +# end test for bug#11748899 diff --git a/mysql-test/r/flush_read_lock.result b/mysql-test/r/flush_read_lock.result index 05fab64330d..c2e8531d01f 100644 --- a/mysql-test/r/flush_read_lock.result +++ b/mysql-test/r/flush_read_lock.result @@ -544,11 +544,10 @@ Success: Was not able to run 'drop table t2_base' under FTWRL. Success: 'drop table t2_base' is blocked by FTWRL active in another connection. Success: FTWRL is blocked when 'drop table t2_base' is active in another connection. # 13.1.b) DROP TABLES which affects only temporary tables -# in theory can be compatible with FTWRL. -# In practice it is not yet. -Success: Was not able to run 'drop table t2_temp' under FTWRL. -Success: 'drop table t2_temp' is blocked by FTWRL active in another connection. -Success: FTWRL is blocked when 'drop table t2_temp' is active in another connection. +# is compatible with FTWRL. +Success: Was able to run 'drop table t2_temp' under FTWRL. +Success: Was able to run 'drop table t2_temp' with FTWRL active in another connection. +Success: Was able to run FTWRL while 'drop table t2_temp' was active in another connection. # # 13.1.c) DROP TEMPORARY TABLES should be compatible with FTWRL. Success: Was able to run 'drop temporary table t2_temp' under FTWRL. @@ -1461,24 +1460,10 @@ Success: Was able to run 'analyze table t3_temp_trans' under FTWRL. Success: Was able to run 'analyze table t3_temp_trans' with FTWRL active in another connection. Success: Was able to run FTWRL while 'analyze table t3_temp_trans' was active in another connection. # -# 39.2.c) Some statements do implicit commit and not -# considered read-only. As result they are -# not compatible with FTWRL. -# -flush tables with read lock; -# Implicit commits are allowed under FTWRL. -alter table t3_temp_trans add column c1 int; -unlock tables; -# -# Switching to connection 'con1'. -flush tables with read lock; -# Switching to connection 'default'. -alter table t3_temp_trans drop column c1; -# Switching to connection 'con1'. -# Check that ALTER TABLE is blocked. -unlock tables; -# Switching to connection 'default'. -# Reap ALTER TABLE +# And ALTER TABLE: +Success: Was able to run 'alter table t3_temp_trans add column c1 int' under FTWRL. +Success: Was able to run 'alter table t3_temp_trans add column c1 int' with FTWRL active in another connection. +Success: Was able to run FTWRL while 'alter table t3_temp_trans add column c1 int' was active in another connection. # # 40) Test effect of implicit commit for DDL which is otherwise # compatible with FTWRL. Implicit commit at the start of DDL diff --git a/mysql-test/r/func_analyse.result b/mysql-test/r/func_analyse.result index f82439090f6..2c300559a32 100644 --- a/mysql-test/r/func_analyse.result +++ b/mysql-test/r/func_analyse.result @@ -128,7 +128,7 @@ End of 5.0 tests # DROP TABLE IF EXISTS t1; Warnings: -Note 1051 Unknown table 't1' +Note 1051 Unknown table 'test.t1' CREATE TABLE t1 (a VARCHAR(2) CHARSET UTF8 NOT NULL); INSERT INTO t1 VALUES ('e'),('e'),('e-'); SELECT * FROM t1 PROCEDURE ANALYSE(); diff --git a/mysql-test/r/func_crypt.result b/mysql-test/r/func_crypt.result index c2f369b3941..1eda56ac114 100644 --- a/mysql-test/r/func_crypt.result +++ b/mysql-test/r/func_crypt.result @@ -43,7 +43,7 @@ old_password(NULL) NULL select password(NULL); password(NULL) -NULL + set global old_passwords=on; select password(''); password('') diff --git a/mysql-test/r/func_rollback.result b/mysql-test/r/func_rollback.result index 57968910051..91151302a06 100644 --- a/mysql-test/r/func_rollback.result +++ b/mysql-test/r/func_rollback.result @@ -190,8 +190,6 @@ END; SELECT f1_insert_select(2); f1_insert_select(2) 1 -Warnings: -Warning 1048 Column 'f2' cannot be null SELECT * FROM t1_not_null ORDER BY f1,f2; f1 f2 2 0 @@ -267,8 +265,6 @@ END; SELECT f1_insert_with_two_rows(); f1_insert_with_two_rows() 1 -Warnings: -Warning 1048 Column 'f2' cannot be null SELECT * FROM t1_not_null ORDER BY f1,f2; f1 f2 10 0 diff --git a/mysql-test/r/func_sapdb.result b/mysql-test/r/func_sapdb.result index 72c7a5a128f..ace7283e192 100644 --- a/mysql-test/r/func_sapdb.result +++ b/mysql-test/r/func_sapdb.result @@ -62,7 +62,9 @@ datediff("1997-11-30 23:59:59.000001","1997-12-31") SET @@SQL_MODE="ALLOW_INVALID_DATES"; select datediff("1997-11-31 23:59:59.000001","1997-12-31"); datediff("1997-11-31 23:59:59.000001","1997-12-31") --30 +NULL +Warnings: +Warning 1292 Incorrect datetime value: '1997-11-31 23:59:59.000001' SET @@SQL_MODE=""; select datediff("1997-11-31 23:59:59.000001","1997-12-31"); datediff("1997-11-31 23:59:59.000001","1997-12-31") diff --git a/mysql-test/r/gis-rtree.result b/mysql-test/r/gis-rtree.result index 22c30479125..c400ebb39ef 100644 --- a/mysql-test/r/gis-rtree.result +++ b/mysql-test/r/gis-rtree.result @@ -712,7 +712,7 @@ count(*) DROP TABLE t2; drop table if exists t1; Warnings: -Note 1051 Unknown table 't1' +Note 1051 Unknown table 'test.t1' CREATE TABLE t1 (a geometry NOT NULL, SPATIAL (a)); INSERT INTO t1 VALUES (GeomFromText("LINESTRING(100 100, 200 200, 300 300)")); INSERT INTO t1 VALUES (GeomFromText("LINESTRING(100 100, 200 200, 300 300)")); diff --git a/mysql-test/r/grant.result b/mysql-test/r/grant.result index f1cf94e4f19..be05f17c281 100644 --- a/mysql-test/r/grant.result +++ b/mysql-test/r/grant.result @@ -55,6 +55,7 @@ max_connections 0 max_user_connections 0 plugin authentication_string +password_expired N show grants for mysqltest_1@localhost; Grants for mysqltest_1@localhost GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' REQUIRE CIPHER 'EDH-RSA-DES-CBC3-SHA' @@ -126,6 +127,7 @@ max_connections 0 max_user_connections 0 plugin authentication_string +password_expired N show grants for mysqltest_1@localhost; Grants for mysqltest_1@localhost GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' WITH MAX_QUERIES_PER_HOUR 10 @@ -173,6 +175,7 @@ max_connections 30 max_user_connections 0 plugin authentication_string +password_expired N show grants for mysqltest_1@localhost; Grants for mysqltest_1@localhost GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' WITH MAX_QUERIES_PER_HOUR 10 MAX_UPDATES_PER_HOUR 20 MAX_CONNECTIONS_PER_HOUR 30 @@ -1353,7 +1356,7 @@ FLUSH PRIVILEGES; DROP TABLE mysql.user; drop table if exists test; Warnings: -Note 1051 Unknown table 'test' +Note 1051 Unknown table 'test.test' drop function if exists test_function; Warnings: Note 1305 FUNCTION test.test_function does not exist diff --git a/mysql-test/r/handlersocket.result b/mysql-test/r/handlersocket.result index 765d954d3dc..e1fbc2d9840 100644 --- a/mysql-test/r/handlersocket.result +++ b/mysql-test/r/handlersocket.result @@ -5,7 +5,7 @@ plugin_version 1.0 plugin_status ACTIVE plugin_type DAEMON plugin_library handlersocket.so -plugin_library_version 1.5 +plugin_library_version 1.7 plugin_author higuchi dot akira at dena dot jp plugin_description Direct access into InnoDB plugin_license BSD diff --git a/mysql-test/r/innodb_mysql_sync.result b/mysql-test/r/innodb_mysql_sync.result index 7c41ffec344..2164b936938 100644 --- a/mysql-test/r/innodb_mysql_sync.result +++ b/mysql-test/r/innodb_mysql_sync.result @@ -101,7 +101,7 @@ DROP TABLE IF EXISTS t1; CREATE DATABASE db1; CREATE TABLE db1.t1(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, value INT) engine=innodb; INSERT INTO db1.t1(value) VALUES (1), (2); -SET DEBUG_SYNC= "alter_table_manage_keys SIGNAL manage WAIT_FOR query"; +SET DEBUG_SYNC= "alter_table_inplace_after_lock_downgrade SIGNAL manage WAIT_FOR query"; # Sending: ALTER TABLE db1.t1 ADD INDEX(value); # Connection con1 @@ -115,45 +115,47 @@ SET DEBUG_SYNC= "now SIGNAL query"; # Connection default # Reaping: ALTER TABLE db1.t1 ADD INDEX(value) DROP DATABASE db1; -# Test 2: Primary index (implicit), should block reads. +# Test 2: Primary index (implicit), should block writes. CREATE TABLE t1(a INT NOT NULL, b INT NOT NULL) engine=innodb; -SET DEBUG_SYNC= "alter_table_manage_keys SIGNAL manage WAIT_FOR query"; +SET DEBUG_SYNC= "alter_table_inplace_after_lock_downgrade SIGNAL manage WAIT_FOR query"; # Sending: -ALTER TABLE t1 ADD UNIQUE INDEX(a); +ALTER TABLE t1 ADD UNIQUE INDEX(a), LOCK=SHARED; # Connection con1 SET DEBUG_SYNC= "now WAIT_FOR manage"; USE test; -# Sending: SELECT * FROM t1; +a b +# Sending: +UPDATE t1 SET a=NULL; # Connection con2 # Waiting for SELECT to be blocked by the metadata lock on t1 SET DEBUG_SYNC= "now SIGNAL query"; # Connection default # Reaping: ALTER TABLE t1 ADD UNIQUE INDEX(a) # Connection con1 -# Reaping: SELECT * FROM t1 -a b -# Test 3: Primary index (explicit), should block reads. +# Reaping: UPDATE t1 SET a=NULL +# Test 3: Primary index (explicit), should block writes. # Connection default ALTER TABLE t1 DROP INDEX a; -SET DEBUG_SYNC= "alter_table_manage_keys SIGNAL manage WAIT_FOR query"; +SET DEBUG_SYNC= "alter_table_inplace_after_lock_downgrade SIGNAL manage WAIT_FOR query"; # Sending: -ALTER TABLE t1 ADD PRIMARY KEY (a); +ALTER TABLE t1 ADD PRIMARY KEY (a), LOCK=SHARED; # Connection con1 SET DEBUG_SYNC= "now WAIT_FOR manage"; -# Sending: SELECT * FROM t1; +a b +# Sending: +UPDATE t1 SET a=NULL; # Connection con2 # Waiting for SELECT to be blocked by the metadata lock on t1 SET DEBUG_SYNC= "now SIGNAL query"; # Connection default # Reaping: ALTER TABLE t1 ADD PRIMARY KEY (a) # Connection con1 -# Reaping: SELECT * FROM t1 -a b +# Reaping: UPDATE t1 SET a=NULL # Test 4: Secondary unique index, should not block reads. # Connection default -SET DEBUG_SYNC= "alter_table_manage_keys SIGNAL manage WAIT_FOR query"; +SET DEBUG_SYNC= "alter_table_inplace_after_lock_downgrade SIGNAL manage WAIT_FOR query"; # Sending: ALTER TABLE t1 ADD UNIQUE (b); # Connection con1 @@ -186,3 +188,170 @@ a b 1 12345 2 23456 DROP TABLE t1; +# +# Bug#13417754 ASSERT IN ROW_DROP_DATABASE_FOR_MYSQL DURING DROP SCHEMA +# +DROP TABLE IF EXISTS t1; +DROP DATABASE IF EXISTS db1; +CREATE TABLE t1(a int) engine=InnoDB; +CREATE DATABASE db1; +# Connection con1 +SET DEBUG_SYNC= 'after_innobase_rename_table SIGNAL locked WAIT_FOR continue'; +# Sending: +ALTER TABLE t1 RENAME db1.t1; +# Connection con2 +SET DEBUG_SYNC= 'now WAIT_FOR locked'; +# DROP DATABASE db1 should now be blocked by ALTER TABLE +# Sending: +DROP DATABASE db1; +# Connection default +# Check that DROP DATABASE is blocked by IX lock on db1 +# Resume ALTER TABLE +SET DEBUG_SYNC= 'now SIGNAL continue'; +# Connection con1 +# Reaping: ALTER TABLE t1 RENAME db1.t1; +# Connection con2 +# Reaping: DROP DATABASE db1 +# Connection default; +SET DEBUG_SYNC= 'RESET'; +# +# WL#5534 Online ALTER, Phase 1 +# +# Multi thread tests. +# See alter_table.test for single thread tests. +DROP TABLE IF EXISTS t1; +CREATE TABLE t1(a INT PRIMARY KEY, b INT) engine=InnoDB; +INSERT INTO t1 VALUES (1,1), (2,2); +SET DEBUG_SYNC= 'RESET'; +SET SESSION lock_wait_timeout= 1; +# +# 1: In-place + writes blocked. +# +# Connection default +SET DEBUG_SYNC= 'alter_opened_table SIGNAL opened WAIT_FOR continue1'; +SET DEBUG_SYNC= 'alter_table_inplace_after_lock_upgrade SIGNAL upgraded WAIT_FOR continue2'; +SET DEBUG_SYNC= 'alter_table_inplace_before_commit SIGNAL beforecommit WAIT_FOR continue3'; +SET DEBUG_SYNC= 'alter_table_before_main_binlog SIGNAL binlog WAIT_FOR continue4'; +# Sending: +ALTER TABLE t1 ADD INDEX i1(b), ALGORITHM= INPLACE, LOCK= SHARED; +# Connection con1; +SET DEBUG_SYNC= 'now WAIT_FOR opened'; +# At this point, neither reads nor writes should be blocked. +SELECT * FROM t1; +a b +1 1 +2 2 +INSERT INTO t1 VALUES (3,3); +SET DEBUG_SYNC= 'now SIGNAL continue1'; +SET DEBUG_SYNC= 'now WAIT_FOR upgraded'; +# Now both reads and writes should be blocked +SELECT * FROM t1; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +INSERT INTO t1 VALUES (4,4); +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +SET DEBUG_SYNC= 'now SIGNAL continue2'; +SET DEBUG_SYNC= 'now WAIT_FOR beforecommit'; +# Still both reads and writes should be blocked. +SELECT * FROM t1; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +INSERT INTO t1 VALUES (5,5); +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +SET DEBUG_SYNC= 'now SIGNAL continue3'; +SET DEBUG_SYNC= 'now WAIT_FOR binlog'; +# Same here. +SELECT * FROM t1; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +INSERT INTO t1 VALUES (6,6); +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +SET DEBUG_SYNC= 'now SIGNAL continue4'; +# Connection default +# Reaping ALTER TABLE ... +SET DEBUG_SYNC= 'RESET'; +DELETE FROM t1 WHERE a= 3; +# +# 2: Copy + writes blocked. +# +SET DEBUG_SYNC= 'alter_opened_table SIGNAL opened WAIT_FOR continue1'; +SET DEBUG_SYNC= 'alter_table_copy_after_lock_upgrade SIGNAL upgraded WAIT_FOR continue2'; +SET DEBUG_SYNC= 'alter_table_before_main_binlog SIGNAL binlog WAIT_FOR continue3'; +# Sending: +ALTER TABLE t1 ADD INDEX i2(b), ALGORITHM= COPY, LOCK= SHARED; +# Connection con1; +SET DEBUG_SYNC= 'now WAIT_FOR opened'; +# At this point, neither reads nor writes should be blocked. +SELECT * FROM t1; +a b +1 1 +2 2 +INSERT INTO t1 VALUES (3,3); +SET DEBUG_SYNC= 'now SIGNAL continue1'; +SET DEBUG_SYNC= 'now WAIT_FOR upgraded'; +# Now writes should be blocked, reads still allowed. +SELECT * FROM t1; +a b +1 1 +2 2 +3 3 +INSERT INTO t1 VALUES (4,4); +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +SET DEBUG_SYNC= 'now SIGNAL continue2'; +SET DEBUG_SYNC= 'now WAIT_FOR binlog'; +# Now both reads and writes should be blocked. +SELECT * FROM t1 limit 1; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +INSERT INTO t1 VALUES (5,5); +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +SET DEBUG_SYNC= 'now SIGNAL continue3'; +# Connection default +# Reaping ALTER TABLE ... +SET DEBUG_SYNC= 'RESET'; +DELETE FROM t1 WHERE a= 3; +# +# 3: In-place + writes allowed. +# +# TODO: Enable this test once WL#5526 is pushed +# +# 4: In-place + reads and writes blocked. +# +# Connection default +SET DEBUG_SYNC= 'alter_opened_table SIGNAL opened WAIT_FOR continue1'; +SET DEBUG_SYNC= 'alter_table_inplace_after_lock_upgrade SIGNAL upgraded WAIT_FOR continue2'; +SET DEBUG_SYNC= 'alter_table_inplace_before_commit SIGNAL beforecommit WAIT_FOR continue3'; +SET DEBUG_SYNC= 'alter_table_before_main_binlog SIGNAL binlog WAIT_FOR continue4'; +# Sending: +ALTER TABLE t1 ADD INDEX i4(b), ALGORITHM= INPLACE, LOCK= EXCLUSIVE; +# Connection con1; +SET DEBUG_SYNC= 'now WAIT_FOR opened'; +# At this point, neither reads nor writes should be blocked. +SELECT * FROM t1; +a b +1 1 +2 2 +INSERT INTO t1 VALUES (3,3); +SET DEBUG_SYNC= 'now SIGNAL continue1'; +SET DEBUG_SYNC= 'now WAIT_FOR upgraded'; +# Now both reads and writes should be blocked. +SELECT * FROM t1; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +INSERT INTO t1 VALUES (4,4); +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +SET DEBUG_SYNC= 'now SIGNAL continue2'; +SET DEBUG_SYNC= 'now WAIT_FOR beforecommit'; +# Same here. +SELECT * FROM t1; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +INSERT INTO t1 VALUES (5,5); +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +SET DEBUG_SYNC= 'now SIGNAL continue3'; +SET DEBUG_SYNC= 'now WAIT_FOR binlog'; +# Same here. +SELECT * FROM t1; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +INSERT INTO t1 VALUES (6,6); +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +SET DEBUG_SYNC= 'now SIGNAL continue4'; +# Connection default +# Reaping ALTER TABLE ... +SET DEBUG_SYNC= 'RESET'; +DROP TABLE t1; +SET DEBUG_SYNC= 'RESET'; diff --git a/mysql-test/r/key.result b/mysql-test/r/key.result index e63afeab126..cb21e5e8134 100644 --- a/mysql-test/r/key.result +++ b/mysql-test/r/key.result @@ -273,7 +273,7 @@ t drop table t1; DROP TABLE IF EXISTS t1; Warnings: -Note 1051 Unknown table 't1' +Note 1051 Unknown table 'test.t1' CREATE TABLE t1 ( c1 int, c2 varbinary(240), diff --git a/mysql-test/r/log_slow.result b/mysql-test/r/log_slow.result index 6500ba3ca53..4414a32d821 100644 --- a/mysql-test/r/log_slow.result +++ b/mysql-test/r/log_slow.result @@ -56,6 +56,7 @@ last_insert_id int(11) NO NULL insert_id int(11) NO NULL server_id int(10) unsigned NO NULL sql_text mediumtext NO NULL +thread_id bigint(21) unsigned NO NULL flush slow logs; set long_query_time=0.1; set log_slow_filter=''; diff --git a/mysql-test/r/log_state.result b/mysql-test/r/log_state.result index 3ccd1451bc4..1ce7eb0d2aa 100644 --- a/mysql-test/r/log_state.result +++ b/mysql-test/r/log_state.result @@ -45,7 +45,7 @@ select sleep(@long_query_time + 1); sleep(@long_query_time + 1) 0 select * from mysql.slow_log where sql_text NOT LIKE '%slow_log%'; -start_time user_host query_time lock_time rows_sent rows_examined db last_insert_id insert_id server_id sql_text +start_time user_host query_time lock_time rows_sent rows_examined db last_insert_id insert_id server_id sql_text thread_id # Switch to connection default set global slow_query_log= ON; # Switch to connection con1 @@ -54,8 +54,8 @@ select sleep(@long_query_time + 1); sleep(@long_query_time + 1) 0 select * from mysql.slow_log where sql_text NOT LIKE '%slow_log%'; -start_time user_host query_time lock_time rows_sent rows_examined db last_insert_id insert_id server_id sql_text -TIMESTAMP USER_HOST QUERY_TIME 00:00:00.000000 1 0 test 0 0 1 select sleep(@long_query_time + 1) +start_time user_host query_time lock_time rows_sent rows_examined db last_insert_id insert_id server_id sql_text thread_id +TIMESTAMP USER_HOST QUERY_TIME 00:00:00.000000 1 0 test 0 0 1 select sleep(@long_query_time + 1) THREAD_ID # Switch to connection default show global variables where Variable_name = 'log' or Variable_name = 'log_slow_queries' or diff --git a/mysql-test/r/log_tables.result b/mysql-test/r/log_tables.result index 18da8765d4b..10eb5ca5fcd 100644 --- a/mysql-test/r/log_tables.result +++ b/mysql-test/r/log_tables.result @@ -17,7 +17,7 @@ event_time user_host thread_id server_id command_type argument TIMESTAMP USER_HOST THREAD_ID 1 Query select * from general_log truncate table slow_log; select * from slow_log; -start_time user_host query_time lock_time rows_sent rows_examined db last_insert_id insert_id server_id sql_text +start_time user_host query_time lock_time rows_sent rows_examined db last_insert_id insert_id server_id sql_text thread_id truncate table general_log; select * from general_log where argument like '%general_log%'; event_time user_host thread_id server_id command_type argument @@ -55,7 +55,7 @@ Table Create Table general_log CREATE TABLE `general_log` ( `event_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), `user_host` mediumtext NOT NULL, - `thread_id` int(11) NOT NULL, + `thread_id` bigint(21) unsigned NOT NULL, `server_id` int(10) unsigned NOT NULL, `command_type` varchar(64) NOT NULL, `argument` mediumtext NOT NULL @@ -64,7 +64,7 @@ show fields from mysql.general_log; Field Type Null Key Default Extra event_time timestamp(6) NO CURRENT_TIMESTAMP(6) on update CURRENT_TIMESTAMP user_host mediumtext NO NULL -thread_id int(11) NO NULL +thread_id bigint(21) unsigned NO NULL server_id int(10) unsigned NO NULL command_type varchar(64) NO NULL argument mediumtext NO NULL @@ -81,7 +81,8 @@ slow_log CREATE TABLE `slow_log` ( `last_insert_id` int(11) NOT NULL, `insert_id` int(11) NOT NULL, `server_id` int(10) unsigned NOT NULL, - `sql_text` mediumtext NOT NULL + `sql_text` mediumtext NOT NULL, + `thread_id` bigint(21) unsigned NOT NULL ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log' show fields from mysql.slow_log; Field Type Null Key Default Extra @@ -96,6 +97,7 @@ last_insert_id int(11) NO NULL insert_id int(11) NO NULL server_id int(10) unsigned NO NULL sql_text mediumtext NO NULL +thread_id bigint(21) unsigned NO NULL flush logs; flush tables; SET GLOBAL GENERAL_LOG=ON; @@ -146,8 +148,8 @@ select sleep(2); sleep(2) 0 select * from mysql.slow_log; -start_time user_host query_time lock_time rows_sent rows_examined db last_insert_id insert_id server_id sql_text -TIMESTAMP USER_HOST QUERY_TIME 00:00:00.000000 1 0 mysql 0 0 1 select sleep(2) +start_time user_host query_time lock_time rows_sent rows_examined db last_insert_id insert_id server_id sql_text thread_id +TIMESTAMP USER_HOST QUERY_TIME 00:00:00.000000 1 0 mysql 0 0 1 select sleep(2) THREAD_ID set @@session.long_query_time = @saved_long_query_time; alter table mysql.general_log engine=myisam; ERROR HY000: You cannot 'ALTER' a log table if logging is enabled @@ -166,7 +168,7 @@ Table Create Table general_log CREATE TABLE `general_log` ( `event_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), `user_host` mediumtext NOT NULL, - `thread_id` int(11) NOT NULL, + `thread_id` bigint(21) unsigned NOT NULL, `server_id` int(10) unsigned NOT NULL, `command_type` varchar(64) NOT NULL, `argument` mediumtext NOT NULL @@ -184,7 +186,8 @@ slow_log CREATE TABLE `slow_log` ( `last_insert_id` int(11) NOT NULL, `insert_id` int(11) NOT NULL, `server_id` int(10) unsigned NOT NULL, - `sql_text` mediumtext NOT NULL + `sql_text` mediumtext NOT NULL, + `thread_id` bigint(21) unsigned NOT NULL ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log' alter table mysql.general_log engine=myisam; alter table mysql.slow_log engine=myisam; @@ -193,7 +196,7 @@ Table Create Table general_log CREATE TABLE `general_log` ( `event_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), `user_host` mediumtext NOT NULL, - `thread_id` int(11) NOT NULL, + `thread_id` bigint(21) unsigned NOT NULL, `server_id` int(10) unsigned NOT NULL, `command_type` varchar(64) NOT NULL, `argument` mediumtext NOT NULL @@ -211,7 +214,8 @@ slow_log CREATE TABLE `slow_log` ( `last_insert_id` int(11) NOT NULL, `insert_id` int(11) NOT NULL, `server_id` int(10) unsigned NOT NULL, - `sql_text` mediumtext NOT NULL + `sql_text` mediumtext NOT NULL, + `thread_id` bigint(21) unsigned NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Slow log' set global general_log='ON'; set global slow_query_log='ON'; @@ -256,15 +260,15 @@ set storage_engine= @save_storage_engine; drop table mysql.slow_log; drop table mysql.general_log; drop table mysql.general_log; -ERROR 42S02: Unknown table 'general_log' +ERROR 42S02: Unknown table 'mysql.general_log' drop table mysql.slow_log; -ERROR 42S02: Unknown table 'slow_log' +ERROR 42S02: Unknown table 'mysql.slow_log' use mysql; CREATE TABLE `general_log` ( -`event_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP +`event_time` TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `user_host` mediumtext NOT NULL, -`thread_id` int(11) NOT NULL, +`thread_id` BIGINT(21) UNSIGNED NOT NULL, `server_id` int(10) unsigned NOT NULL, `command_type` varchar(64) NOT NULL, `argument` mediumtext NOT NULL @@ -281,7 +285,8 @@ ON UPDATE CURRENT_TIMESTAMP, `last_insert_id` int(11) NOT NULL, `insert_id` int(11) NOT NULL, `server_id` int(10) unsigned NOT NULL, -`sql_text` mediumtext NOT NULL +`sql_text` mediumtext NOT NULL, +`thread_id` BIGINT(21) UNSIGNED NOT NULL ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log'; set global general_log='ON'; set global slow_query_log='ON'; @@ -308,7 +313,7 @@ event_time user_host thread_id server_id command_type argument TIMESTAMP USER_HOST THREAD_ID 1 Query select * from general_log truncate table slow_log; select * from slow_log; -start_time user_host query_time lock_time rows_sent rows_examined db last_insert_id insert_id server_id sql_text +start_time user_host query_time lock_time rows_sent rows_examined db last_insert_id insert_id server_id sql_text thread_id create table general_log_new like general_log; rename table general_log TO renamed_general_log, general_log_new TO general_log; create table slow_log_new like slow_log; @@ -329,9 +334,9 @@ TIMESTAMP USER_HOST THREAD_ID 1 Query select * from slow_log TIMESTAMP USER_HOST THREAD_ID 1 Query create table general_log_new like general_log TIMESTAMP USER_HOST THREAD_ID 1 Query rename table general_log TO renamed_general_log, general_log_new TO general_log select * from slow_log; -start_time user_host query_time lock_time rows_sent rows_examined db last_insert_id insert_id server_id sql_text +start_time user_host query_time lock_time rows_sent rows_examined db last_insert_id insert_id server_id sql_text thread_id select * from renamed_slow_log; -start_time user_host query_time lock_time rows_sent rows_examined db last_insert_id insert_id server_id sql_text +start_time user_host query_time lock_time rows_sent rows_examined db last_insert_id insert_id server_id sql_text thread_id set global general_log='OFF'; RENAME TABLE general_log TO general_log2; set global slow_query_log='OFF'; @@ -362,8 +367,6 @@ show tables like "%log%"; Tables_in_mysql (%log%) general_log general_log_new -ndb_binlog_index -slave_relay_log_info slow_log slow_log_new drop table slow_log_new, general_log_new; @@ -426,10 +429,10 @@ SELECT "My own slow query", sleep(2); My own slow query sleep(2) My own slow query 0 SELECT * FROM mysql.slow_log WHERE seq >= 2 LIMIT 3; -start_time user_host query_time lock_time rows_sent rows_examined db last_insert_id insert_id server_id sql_text seq -START_TIME USER_HOST QUERY_TIME 00:00:00.000000 1 0 test 0 0 1 SELECT "My own slow query", sleep(2) 2 -START_TIME USER_HOST QUERY_TIME 00:00:00.000000 1 0 test 0 0 1 SELECT "My own slow query", sleep(2) 3 -START_TIME USER_HOST QUERY_TIME 00:00:00.000000 1 0 test 0 0 1 SELECT "My own slow query", sleep(2) 4 +start_time user_host query_time lock_time rows_sent rows_examined db last_insert_id insert_id server_id sql_text thread_id seq +START_TIME USER_HOST QUERY_TIME 00:00:00.000000 1 0 test 0 0 1 SELECT "My own slow query", sleep(2) 2 2 +START_TIME USER_HOST QUERY_TIME 00:00:00.000000 1 0 test 0 0 1 SELECT "My own slow query", sleep(2) 2 3 +START_TIME USER_HOST QUERY_TIME 00:00:00.000000 1 0 test 0 0 1 SELECT "My own slow query", sleep(2) 2 4 SET GLOBAL slow_query_log = 0; SET SESSION long_query_time =@saved_long_query_time; FLUSH LOGS; @@ -548,6 +551,7 @@ BEGIN DECLARE start_time, query_time, lock_time CHAR(28); DECLARE user_host MEDIUMTEXT; DECLARE rows_set, rows_examined, last_insert_id, insert_id, server_id INT; +DECLARE thread_id BIGINT UNSIGNED; DECLARE dbname MEDIUMTEXT; DECLARE sql_text BLOB; DECLARE done INT DEFAULT 0; @@ -561,14 +565,14 @@ DECLARE CONTINUE HANDLER FOR ER_SP_FETCH_NO_DATA SET done = 1; FETCH cur1 INTO start_time, user_host, query_time, lock_time, rows_set, rows_examined, dbname, last_insert_id, -insert_id, server_id, sql_text; +insert_id, server_id, sql_text, thread_id; END; IF NOT done THEN BEGIN INSERT INTO `db_17876.slow_log_data` VALUES(start_time, user_host, query_time, lock_time, rows_set, rows_examined, -dbname, last_insert_id, insert_id, server_id, sql_text); +dbname, last_insert_id, insert_id, server_id, sql_text, thread_id); END; END IF; END; diff --git a/mysql-test/r/log_tables_upgrade.result b/mysql-test/r/log_tables_upgrade.result index 5732b94a90c..5a53ca03736 100644 --- a/mysql-test/r/log_tables_upgrade.result +++ b/mysql-test/r/log_tables_upgrade.result @@ -33,16 +33,12 @@ mysql.host OK mysql.index_stats OK mysql.innodb_index_stats OK mysql.innodb_table_stats OK -mysql.ndb_binlog_index OK mysql.plugin OK mysql.proc OK mysql.procs_priv OK mysql.proxies_priv OK mysql.renamed_general_log OK mysql.servers OK -mysql.slave_master_info OK -mysql.slave_relay_log_info OK -mysql.slave_worker_info OK mysql.table_stats OK mysql.tables_priv OK mysql.time_zone OK diff --git a/mysql-test/r/lowercase_table4.result b/mysql-test/r/lowercase_table4.result index aa81eff5194..02e2012a186 100644 --- a/mysql-test/r/lowercase_table4.result +++ b/mysql-test/r/lowercase_table4.result @@ -28,18 +28,7 @@ Create Table CREATE TABLE `Table2` ( KEY `fk1` (`c2`), CONSTRAINT `fk1` FOREIGN KEY (`c2`) REFERENCES `Table1` (`c1`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 -SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS; -CONSTRAINT_CATALOG def -CONSTRAINT_SCHEMA mysql -CONSTRAINT_NAME innodb_index_stats_ibfk_1 -UNIQUE_CONSTRAINT_CATALOG def -UNIQUE_CONSTRAINT_SCHEMA mysql -UNIQUE_CONSTRAINT_NAME PRIMARY -MATCH_OPTION NONE -UPDATE_RULE RESTRICT -DELETE_RULE RESTRICT -TABLE_NAME innodb_index_stats -REFERENCED_TABLE_NAME innodb_table_stats +SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_SCHEMA='test'; CONSTRAINT_CATALOG def CONSTRAINT_SCHEMA test CONSTRAINT_NAME fk1 @@ -98,18 +87,7 @@ Create Table CREATE TABLE `Customer` ( `Id` int(11) NOT NULL, PRIMARY KEY (`Id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 -SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS; -CONSTRAINT_CATALOG def -CONSTRAINT_SCHEMA mysql -CONSTRAINT_NAME innodb_index_stats_ibfk_1 -UNIQUE_CONSTRAINT_CATALOG def -UNIQUE_CONSTRAINT_SCHEMA mysql -UNIQUE_CONSTRAINT_NAME PRIMARY -MATCH_OPTION NONE -UPDATE_RULE RESTRICT -DELETE_RULE RESTRICT -TABLE_NAME innodb_index_stats -REFERENCED_TABLE_NAME innodb_table_stats +SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_SCHEMA='test'; CONSTRAINT_CATALOG def CONSTRAINT_SCHEMA test CONSTRAINT_NAME product_order_ibfk_1 diff --git a/mysql-test/r/mdl_sync.result b/mysql-test/r/mdl_sync.result index 1c94f867a54..d71498f90dd 100644 --- a/mysql-test/r/mdl_sync.result +++ b/mysql-test/r/mdl_sync.result @@ -5,7 +5,7 @@ create table t2 (i int); connection: default lock tables t2 read; connection: con1 -set debug_sync='mdl_upgrade_shared_lock_to_exclusive SIGNAL parked WAIT_FOR go'; +set debug_sync='mdl_upgrade_lock SIGNAL parked WAIT_FOR go'; alter table t1 rename t3; connection: default set debug_sync= 'now WAIT_FOR parked'; @@ -16,7 +16,7 @@ connection: con1 connection: default unlock tables; connection: con2 -ERROR 42S02: Unknown table 't1' +ERROR 42S02: Unknown table 'test.t1' drop table t3; SET DEBUG_SYNC= 'RESET'; # @@ -48,8 +48,13 @@ select count(*) from t1; count(*) 0 insert into t1 values (1), (1); +# Check that SU lock is compatible with it. To do this use ALTER TABLE +# which will fail when constructing .frm and thus obtaining SU metadata +# lock. +alter table t1 add index (not_exist); +ERROR 42000: Key column 'not_exist' doesn't exist in table # Check that SNW lock is compatible with it. To do this use ALTER TABLE -# which will fail after opening the table and thus obtaining SNW metadata +# which will fail during copying the table and thus obtaining SNW metadata # lock. alter table t1 add primary key (c1); ERROR 23000: Duplicate entry '1' for key 'PRIMARY' @@ -139,8 +144,13 @@ select count(*) from t1; count(*) 3 insert into t1 values (1); +# Check that SU lock is compatible with it. To do this use ALTER TABLE +# which will fail when constructing .frm and thus obtaining SU metadata +# lock. +alter table t1 add index (not_exist); +ERROR 42000: Key column 'not_exist' doesn't exist in table # Check that SNW lock is compatible with it. To do this use ALTER TABLE -# which will fail after opening the table and thus obtaining SNW metadata +# which will fail during copying the table and thus obtaining SNW metadata # lock. alter table t1 add primary key (c1); ERROR 23000: Duplicate entry '1' for key 'PRIMARY' @@ -244,8 +254,13 @@ select count(*) from t1; count(*) 3 insert into t1 values (1); +# Check that SU lock is compatible with it. To do this use ALTER TABLE +# which will fail when constructing .frm and thus obtaining SU metadata +# lock. +alter table t1 add index (not_exist); +ERROR 42000: Key column 'not_exist' doesn't exist in table # Check that SNW lock is compatible with it. To do this use ALTER TABLE -# which will fail after opening the table and thus obtaining SNW metadata +# which will fail during copying the table and thus obtaining SNW metadata # lock. alter table t1 add primary key (c1); ERROR 23000: Duplicate entry '1' for key 'PRIMARY' @@ -334,8 +349,13 @@ c1 # effects of concurrent insert. select * from t1; insert into t1 values (1); +# Check that SU lock is compatible with it. To do this use ALTER TABLE +# which will fail when constructing .frm and thus obtaining SU metadata +# lock. +alter table t1 add index (not_exist); +ERROR 42000: Key column 'not_exist' doesn't exist in table # Check that SNW lock is not compatible with SW lock. -# Again we use ALTER TABLE which fails after opening +# Again we use ALTER TABLE which fails during copying # the table to avoid upgrade of SNW -> X. # Sending: alter table t1 add primary key (c1);; @@ -397,15 +417,111 @@ rename table t2 to t1; # Switching to connection 'default'. # # -# 5) Acquire SNW lock on the table. We have to use DEBUG_SYNC for -# this, to prevent SNW from being immediately upgraded to X. +# 5) Acquire SU lock on the table. We have to use DEBUG_SYNC for +# this, to prevent SU from being immediately upgraded to X. # -set debug_sync= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish'; +set debug_sync= 'alter_opened_table SIGNAL locked WAIT_FOR finish'; +# Sending: +alter table t1 add primary key (c1);; +# +# Switching to connection 'mdl_con1'. +set debug_sync= 'now WAIT_FOR locked'; +# Check that S, SH, SR and SW locks are compatible with it. +handler t1 open; +handler t1 close; +select column_name from information_schema.columns where +table_schema='test' and table_name='t1'; +column_name +c1 +select count(*) from t1; +count(*) +5 +delete from t1 limit 1; +# Check that SU lock is incompatible with SU lock. +# Sending: +alter table t1 add primary key (c1);; +# +# Switching to connection 'mdl_con2'. +# Check that the above ALTER is blocked because of SU lock. +# Unblock ALTERs. +set debug_sync= 'now SIGNAL finish'; +# +# Switching to connection 'default'. +# Reaping first ALTER TABLE. +ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +# +# Switching to connection 'mdl_con1'. +# Reaping another ALTER TABLE. +ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +# +# Switching to connection 'default'. +set debug_sync= 'alter_opened_table SIGNAL locked WAIT_FOR finish'; # Sending: alter table t1 add primary key (c1);; # # Switching to connection 'mdl_con1'. set debug_sync= 'now WAIT_FOR locked'; +# Check that SNRW lock is incompatible with SU lock. +# Sending: +lock table t1 write;; +# +# Switching to connection 'mdl_con2'. +# Check that the above LOCK TABLES is blocked because of SU lock. +# Unblock ALTER and thus LOCK TABLES. +set debug_sync= 'now SIGNAL finish'; +# +# Switching to connection 'default'. +# Reaping ALTER TABLE. +ERROR 23000: Duplicate entry '1' for key 'PRIMARY' +# +# Switching to connection 'mdl_con1'. +# Reaping LOCK TABLES +insert into t1 values (1); +unlock tables; +# +# Switching to connection 'default'. +set debug_sync= 'alter_opened_table SIGNAL locked WAIT_FOR finish'; +# Sending: +alter table t1 add primary key (c1);; +# +# Switching to connection 'mdl_con1'. +set debug_sync= 'now WAIT_FOR locked'; +# Check that X lock is incompatible with SU lock. +# Sending: +rename table t1 to t2;; +# +# Switching to connection 'mdl_con2'. +# Check that the above RENAME is blocked because of SU lock. +# Unblock ALTER and thus RENAME TABLE. +set debug_sync= 'now SIGNAL finish'; +# +# Switching to connection 'default'. +# Now we have ALTER TABLE with SU->SNW and RENAME TABLE with pending +# X-lock. In this case ALTER TABLE should be chosen as victim. +# Reaping ALTER TABLE. +ERROR 40001: Deadlock found when trying to get lock; try restarting transaction +# +# Switching to connection 'mdl_con1'. +# Reaping RENAME TABLE +# Revert back to original state of things. +rename table t2 to t1; +# +# There is no need to check that upgrade from SNW/SNRW to X is +# blocked by presence of another SU lock because SNW/SNRW is +# incompatible with SU anyway. +# +# Switching to connection 'default'. +# +# +# 6) Acquire SNW lock on the table. We have to use DEBUG_SYNC for +# this, to prevent SNW from being immediately upgraded to X. +# +set debug_sync= 'alter_table_copy_after_lock_upgrade SIGNAL locked WAIT_FOR finish'; +# Sending: +alter table t1 add primary key (c1), lock=shared, algorithm=copy;; +# +# Switching to connection 'mdl_con1'. +set debug_sync= 'now WAIT_FOR locked'; # Check that S, SH and SR locks are compatible with it. handler t1 open; handler t1 close; @@ -433,13 +549,13 @@ ERROR 23000: Duplicate entry '1' for key 'PRIMARY' # Reaping DELETE. # # Switching to connection 'default'. -set debug_sync= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish'; +set debug_sync= 'alter_table_copy_after_lock_upgrade SIGNAL locked WAIT_FOR finish'; # Sending: -alter table t1 add primary key (c1);; +alter table t1 add primary key (c1), lock=shared, algorithm=copy;; # # Switching to connection 'mdl_con1'. set debug_sync= 'now WAIT_FOR locked'; -# Check that SNW lock is incompatible with SNW lock. +# Check that SU lock is incompatible with SNW lock. # Sending: alter table t1 add primary key (c1);; # @@ -456,10 +572,14 @@ ERROR 23000: Duplicate entry '1' for key 'PRIMARY' # Reaping another ALTER TABLE. ERROR 23000: Duplicate entry '1' for key 'PRIMARY' # +# Note that we can't easily check SNW vs SNW locks since +# SNW is only used by ALTER TABLE after upgrading from SU +# and SU is also incompatible with SNW. +# # Switching to connection 'default'. -set debug_sync= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish'; +set debug_sync= 'alter_table_copy_after_lock_upgrade SIGNAL locked WAIT_FOR finish'; # Sending: -alter table t1 add primary key (c1);; +alter table t1 add primary key (c1), lock=shared, algorithm=copy;; # # Switching to connection 'mdl_con1'. set debug_sync= 'now WAIT_FOR locked'; @@ -482,9 +602,9 @@ insert into t1 values (1); unlock tables; # # Switching to connection 'default'. -set debug_sync= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish'; +set debug_sync= 'alter_table_copy_after_lock_upgrade SIGNAL locked WAIT_FOR finish'; # Sending: -alter table t1 add primary key (c1);; +alter table t1 add primary key (c1), algorithm=copy, lock=shared;; # # Switching to connection 'mdl_con1'. set debug_sync= 'now WAIT_FOR locked'; @@ -513,7 +633,7 @@ rename table t2 to t1; # Switching to connection 'default'. # # -# 6) Acquire SNRW lock on the table. +# 7) Acquire SNRW lock on the table. # # lock table t1 write; @@ -560,12 +680,12 @@ unlock tables; lock table t1 write; # # Switching to connection 'mdl_con1'. -# Check that SNW lock is incompatible with SNRW lock. +# Check that SU lock is incompatible with SNRW lock. # Sending: alter table t1 add primary key (c1);; # # Switching to connection 'default'. -# Check that the above ALTER is blocked because of UNWR lock. +# Check that the above ALTER is blocked because of SNRW lock. # Unblock ALTER. unlock tables; # @@ -573,6 +693,10 @@ unlock tables; # Reaping ALTER TABLE. ERROR 23000: Duplicate entry '1' for key 'PRIMARY' # +# Note that we can't easily check SNW vs SNRW locks since +# SNW is only used by ALTER TABLE after upgrading from SU +# and SU is also incompatible with SNRW. +# # Switching to connection 'default'. lock table t1 write; # @@ -616,7 +740,7 @@ rename table t2 to t1; # Switching to connection 'default'. # # -# 7) Now do the same round of tests for X lock. We use additional +# 8) Now do the same round of tests for X lock. We use additional # table to get long-lived lock of this type. # create table t2 (c1 int); @@ -744,7 +868,7 @@ rename table t1 to t2;; # # Switching to connection 'mdl_con1'. # Check that RENAME has acquired X lock on t1 and is waiting for t2. -# Check that SNW lock is incompatible with X lock. +# Check that SU lock is incompatible with X lock. # Sending: alter table t1 add primary key (c1);; # @@ -761,7 +885,11 @@ ERROR 42S01: Table 't2' already exists # Switching to connection 'mdl_con1'. # Reaping ALTER. ERROR 23000: Duplicate entry '1' for key 'PRIMARY' -# +# +# Note that we can't easily check SNW vs X locks since +# SNW is only used by ALTER TABLE after upgrading from SU +# and SU is also incompatible with X. +# # Switching to connection 'mdl_con2'. # Prepare for blocking RENAME TABLE. lock tables t2 read; @@ -822,6 +950,9 @@ rename table t3 to t1; # are pending. I.e. let us test rules for priorities between # different types of metadata locks. # +# Note: No tests for pending SU lock as this lock requires +# even stronger active or pending lock. +# # # Switching to connection 'mdl_con2'. # @@ -1138,6 +1269,9 @@ unlock tables; # transactional context. Obviously we are mostly interested # in conflicting types of locks. # +# Note: No tests for active/pending SU lock since +# ALTER TABLE is in its own transaction. +# # # 1) Let us check how various locks used within transactional # context interact with active/pending SNW lock. @@ -1154,9 +1288,9 @@ count(*) # We have to use DEBUG_SYNC facility as otherwise SNW lock # will be immediately released (or upgraded to X lock). insert into t2 values (1), (1); -set debug_sync= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish'; +set debug_sync= 'alter_table_copy_after_lock_upgrade SIGNAL locked WAIT_FOR finish'; # Sending: -alter table t2 add primary key (c1);; +alter table t2 add primary key (c1), algorithm=copy, lock=shared;; # # Switching to connection 'default'. set debug_sync= 'now WAIT_FOR locked'; @@ -1199,9 +1333,9 @@ count(*) # # Switching to connection 'mdl_con1'. # Create an active SNW lock on t1. -set debug_sync= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish'; +set debug_sync= 'alter_table_copy_after_lock_upgrade SIGNAL locked WAIT_FOR finish'; # Sending: -alter table t1 add primary key (c1);; +alter table t1 add primary key (c1), algorithm=copy, lock=shared;; # # Switching to connection 'default'. set debug_sync= 'now WAIT_FOR locked'; @@ -1986,7 +2120,7 @@ drop tables t1, t2; # create table t1 (i int); # Ensure that ALTER waits once it has acquired SNW lock. -set debug_sync='after_open_table_mdl_shared SIGNAL parked1 WAIT_FOR go1'; +set debug_sync='alter_table_copy_after_lock_upgrade SIGNAL parked1 WAIT_FOR go1'; # Sending: alter table t1 add column j int; # @@ -2293,13 +2427,18 @@ set global log_output=@save_log_output; # drop tables if exists t1, t2; create table t1 (i int); +insert into t1 values(1); # Let us check that we won't deadlock if during filling # of I_S table we encounter conflicting metadata lock # which owner is in its turn waiting for our connection. lock tables t1 read; +# Switching to connection 'con46044_2'. +# Sending: +update t1 set i = 2; # Switching to connection 'con46044'. +# Waiting until UPDATE t1 SET ... is blocked. # Sending: -create table t2 select * from t1 for update;; +create table t2 select * from t1;; # Switching to connection 'default'. # Waiting until CREATE TABLE ... SELECT ... is blocked. # First let us check that SHOW FIELDS/DESCRIBE doesn't @@ -2329,6 +2468,7 @@ unlock tables; # Switching to connection 'con46044'. # Reaping CREATE TABLE ... SELECT ... . drop table t2; +# Reaping UPDATE t1 statement # # Let us also check that queries to I_S wait for conflicting metadata # locks to go away instead of skipping table with a warning in cases @@ -2338,9 +2478,13 @@ drop table t2; # We check same three queries to I_S in this new situation. # Switching to connection 'con46044_2'. lock tables t1 read; +# Switching to connection 'con46044_3'. +# Sending: +update t1 set i = 3; # Switching to connection 'con46044'. +# Waiting until UPDATE t1 SET ... is blocked. # Sending: -create table t2 select * from t1 for update;; +create table t2 select * from t1;; # Switching to connection 'default'. # Waiting until CREATE TABLE ... SELECT ... is blocked. # Let us check that SHOW FIELDS/DESCRIBE gets blocked. @@ -2356,11 +2500,16 @@ unlock tables; Field Type Null Key Default Extra i int(11) YES NULL drop table t2; +# Reaping UPDATE t1 statement # Switching to connection 'con46044_2'. lock tables t1 read; +# Switching to connection 'con46044_3'. +# Sending: +update t1 set i = 4; # Switching to connection 'con46044'. +# Waiting until UPDATE t1 SET ... is blocked. # Sending: -create table t2 select * from t1 for update;; +create table t2 select * from t1;; # Switching to connection 'default'. # Waiting until CREATE TABLE ... SELECT ... is blocked. # Check that I_S query which reads only .FRMs gets blocked. @@ -2376,11 +2525,16 @@ unlock tables; column_name i drop table t2; +# Reaping UPDATE t1 statement # Switching to connection 'con46044_2'. lock tables t1 read; +# Switching to connection 'con46044_3'. +# Sending: +update t1 set i = 5; # Switching to connection 'con46044'. +# Waiting until UPDATE t1 SET ... is blocked. # Sending: -create table t2 select * from t1 for update;; +create table t2 select * from t1;; # Switching to connection 'default'. # Waiting until CREATE TABLE ... SELECT ... is blocked. # Finally, check that I_S query which does full-blown table open @@ -2397,6 +2551,7 @@ unlock tables; table_name table_type auto_increment table_comment t2 BASE TABLE NULL drop table t2; +# Reaping UPDATE t1 statement # Switching to connection 'default'. # Clean-up. drop table t1; @@ -2414,7 +2569,7 @@ c1 c2 c3 3 3 0 # # Switching to connection 'con46273'. -set debug_sync='after_lock_tables_takes_lock SIGNAL alter_table_locked WAIT_FOR alter_go'; +set debug_sync='alter_table_copy_after_lock_upgrade SIGNAL alter_table_locked WAIT_FOR alter_go'; alter table t1 add column e int, rename to t2;; # # Switching to connection 'default'. @@ -2558,9 +2713,9 @@ drop table if exists t1; set debug_sync= 'RESET'; create table t1 (i int) engine=InnoDB; # Switching to connection 'con50913_1'. -set debug_sync= 'thr_multi_lock_after_thr_lock SIGNAL parked WAIT_FOR go'; +set debug_sync= 'alter_table_copy_after_lock_upgrade SIGNAL parked WAIT_FOR go'; # Sending: -alter table t1 add column j int; +alter table t1 add column j int, ALGORITHM=COPY; # Switching to connection 'default'. # Wait until ALTER TABLE gets blocked on a sync point after # acquiring thr_lock.c lock. @@ -2600,7 +2755,7 @@ i # Switching to connection 'default'. # Start ALTER TABLE which will acquire SNW lock and # table lock and get blocked on sync point. -set debug_sync= 'thr_multi_lock_after_thr_lock SIGNAL parked WAIT_FOR go'; +set debug_sync= 'alter_table_copy_after_lock_upgrade SIGNAL parked WAIT_FOR go'; # Sending: alter table t1 add column j int; # Switching to connection 'con1'. @@ -2889,7 +3044,7 @@ SET DEBUG_SYNC= 'now SIGNAL blocked'; # Reaping: DROP DATABASE db1 # Connection con2 # Reaping: DROP TABLE db1.t1 -ERROR 42S02: Unknown table 't1' +ERROR 42S02: Unknown table 'db1.t1' # Connection default SET DEBUG_SYNC= 'RESET'; # @@ -2934,12 +3089,16 @@ CREATE TABLE m1(a INT) engine=MERGE UNION=(t1, t2); INSERT INTO t1 VALUES (1), (2); INSERT INTO t2 VALUES (3), (4); # Connection con1 -SET DEBUG_SYNC= 'mdl_upgrade_shared_lock_to_exclusive SIGNAL upgrade WAIT_FOR continue'; +# We need EXECUTE 2 since ALTER TABLE does SU => SNW => X and we want +# to stop at the second upgrade. +SET DEBUG_SYNC= 'mdl_upgrade_lock SIGNAL upgrade WAIT_FOR continue EXECUTE 2'; # Sending: ALTER TABLE m1 engine=MERGE UNION=(t2, t1); # Connection con2 # Waiting for ALTER TABLE to try lock upgrade SET DEBUG_SYNC= 'now WAIT_FOR upgrade'; +SET DEBUG_SYNC= 'now SIGNAL continue'; +SET DEBUG_SYNC= 'now WAIT_FOR upgrade'; # Sending: DELETE FROM t2 WHERE a = 3; # Connection default diff --git a/mysql-test/r/myisam-system.result b/mysql-test/r/myisam-system.result index 9d5a59459ec..af5de8f2749 100644 --- a/mysql-test/r/myisam-system.result +++ b/mysql-test/r/myisam-system.result @@ -16,4 +16,4 @@ drop table t1; Warnings: Warning 2 Can't find file: './test/t1.MYI' (errno: 2 "No such file or directory") drop table t1; -ERROR 42S02: Unknown table 't1' +ERROR 42S02: Unknown table 'test.t1' diff --git a/mysql-test/r/myisam.result b/mysql-test/r/myisam.result index 267110be487..5b0dbbf6957 100644 --- a/mysql-test/r/myisam.result +++ b/mysql-test/r/myisam.result @@ -454,7 +454,7 @@ a b c drop table t1; DROP TABLE IF EXISTS t1; Warnings: -Note 1051 Unknown table 't1' +Note 1051 Unknown table 'test.t1' CREATE TABLE t1 (a varchar(150) NOT NULL, KEY (a)); INSERT t1 VALUES ("can \tcan"); INSERT t1 VALUES ("can can"); diff --git a/mysql-test/r/mysql_upgrade.result b/mysql-test/r/mysql_upgrade.result index a08e6f63fb4..74832162afb 100644 --- a/mysql-test/r/mysql_upgrade.result +++ b/mysql-test/r/mysql_upgrade.result @@ -21,15 +21,11 @@ mysql.host OK mysql.index_stats OK mysql.innodb_index_stats OK mysql.innodb_table_stats OK -mysql.ndb_binlog_index OK mysql.plugin OK mysql.proc OK mysql.procs_priv OK mysql.proxies_priv OK mysql.servers OK -mysql.slave_master_info OK -mysql.slave_relay_log_info OK -mysql.slave_worker_info OK mysql.table_stats OK mysql.tables_priv OK mysql.time_zone OK @@ -67,15 +63,11 @@ mysql.host OK mysql.index_stats OK mysql.innodb_index_stats OK mysql.innodb_table_stats OK -mysql.ndb_binlog_index OK mysql.plugin OK mysql.proc OK mysql.procs_priv OK mysql.proxies_priv OK mysql.servers OK -mysql.slave_master_info OK -mysql.slave_relay_log_info OK -mysql.slave_worker_info OK mysql.table_stats OK mysql.tables_priv OK mysql.time_zone OK @@ -113,15 +105,11 @@ mysql.host OK mysql.index_stats OK mysql.innodb_index_stats OK mysql.innodb_table_stats OK -mysql.ndb_binlog_index OK mysql.plugin OK mysql.proc OK mysql.procs_priv OK mysql.proxies_priv OK mysql.servers OK -mysql.slave_master_info OK -mysql.slave_relay_log_info OK -mysql.slave_worker_info OK mysql.table_stats OK mysql.tables_priv OK mysql.time_zone OK @@ -161,15 +149,11 @@ mysql.host OK mysql.index_stats OK mysql.innodb_index_stats OK mysql.innodb_table_stats OK -mysql.ndb_binlog_index OK mysql.plugin OK mysql.proc OK mysql.procs_priv OK mysql.proxies_priv OK mysql.servers OK -mysql.slave_master_info OK -mysql.slave_relay_log_info OK -mysql.slave_worker_info OK mysql.table_stats OK mysql.tables_priv OK mysql.time_zone OK @@ -213,15 +197,11 @@ mysql.host OK mysql.index_stats OK mysql.innodb_index_stats OK mysql.innodb_table_stats OK -mysql.ndb_binlog_index OK mysql.plugin OK mysql.proc OK mysql.procs_priv OK mysql.proxies_priv OK mysql.servers OK -mysql.slave_master_info OK -mysql.slave_relay_log_info OK -mysql.slave_worker_info OK mysql.table_stats OK mysql.tables_priv OK mysql.time_zone OK @@ -268,15 +248,11 @@ mysql.host OK mysql.index_stats OK mysql.innodb_index_stats OK mysql.innodb_table_stats OK -mysql.ndb_binlog_index OK mysql.plugin OK mysql.proc OK mysql.procs_priv OK mysql.proxies_priv OK mysql.servers OK -mysql.slave_master_info OK -mysql.slave_relay_log_info OK -mysql.slave_worker_info OK mysql.table_stats OK mysql.tables_priv OK mysql.time_zone OK @@ -326,15 +302,11 @@ mysql.host OK mysql.index_stats OK mysql.innodb_index_stats OK mysql.innodb_table_stats OK -mysql.ndb_binlog_index OK mysql.plugin OK mysql.proc OK mysql.procs_priv OK mysql.proxies_priv OK mysql.servers OK -mysql.slave_master_info OK -mysql.slave_relay_log_info OK -mysql.slave_worker_info OK mysql.table_stats OK mysql.tables_priv OK mysql.time_zone OK diff --git a/mysql-test/r/mysql_upgrade_ssl.result b/mysql-test/r/mysql_upgrade_ssl.result index 60bf427cef6..d0609deb552 100644 --- a/mysql-test/r/mysql_upgrade_ssl.result +++ b/mysql-test/r/mysql_upgrade_ssl.result @@ -23,15 +23,11 @@ mysql.host OK mysql.index_stats OK mysql.innodb_index_stats OK mysql.innodb_table_stats OK -mysql.ndb_binlog_index OK mysql.plugin OK mysql.proc OK mysql.procs_priv OK mysql.proxies_priv OK mysql.servers OK -mysql.slave_master_info OK -mysql.slave_relay_log_info OK -mysql.slave_worker_info OK mysql.table_stats OK mysql.tables_priv OK mysql.time_zone OK diff --git a/mysql-test/r/mysqlcheck.result b/mysql-test/r/mysqlcheck.result index 17dc9ad9a35..ce9bf367945 100644 --- a/mysql-test/r/mysqlcheck.result +++ b/mysql-test/r/mysqlcheck.result @@ -17,15 +17,11 @@ mysql.host OK mysql.index_stats OK mysql.innodb_index_stats OK mysql.innodb_table_stats OK -mysql.ndb_binlog_index OK mysql.plugin OK mysql.proc OK mysql.procs_priv OK mysql.proxies_priv OK mysql.servers OK -mysql.slave_master_info OK -mysql.slave_relay_log_info OK -mysql.slave_worker_info OK mysql.table_stats OK mysql.tables_priv OK mysql.time_zone OK @@ -54,15 +50,11 @@ status : OK mysql.innodb_table_stats note : Table does not support optimize, doing recreate + analyze instead status : OK -mysql.ndb_binlog_index OK mysql.plugin OK mysql.proc OK mysql.procs_priv OK mysql.proxies_priv OK mysql.servers OK -mysql.slave_master_info OK -mysql.slave_relay_log_info OK -mysql.slave_worker_info OK mysql.table_stats OK mysql.tables_priv OK mysql.time_zone OK @@ -85,15 +77,11 @@ mysql.host OK mysql.index_stats OK mysql.innodb_index_stats OK mysql.innodb_table_stats OK -mysql.ndb_binlog_index OK mysql.plugin OK mysql.proc OK mysql.procs_priv OK mysql.proxies_priv OK mysql.servers OK -mysql.slave_master_info OK -mysql.slave_relay_log_info OK -mysql.slave_worker_info OK mysql.table_stats OK mysql.tables_priv OK mysql.time_zone OK @@ -120,15 +108,11 @@ status : OK mysql.innodb_table_stats note : Table does not support optimize, doing recreate + analyze instead status : OK -mysql.ndb_binlog_index Table is already up to date mysql.plugin Table is already up to date mysql.proc Table is already up to date mysql.procs_priv Table is already up to date mysql.proxies_priv Table is already up to date mysql.servers Table is already up to date -mysql.slave_master_info Table is already up to date -mysql.slave_relay_log_info Table is already up to date -mysql.slave_worker_info Table is already up to date mysql.table_stats Table is already up to date mysql.tables_priv Table is already up to date mysql.time_zone Table is already up to date diff --git a/mysql-test/r/mysqld--help.result b/mysql-test/r/mysqld--help.result index 5f8cf38ac09..5fec9b50596 100644 --- a/mysql-test/r/mysqld--help.result +++ b/mysql-test/r/mysqld--help.result @@ -217,6 +217,7 @@ The following options may be given as the first argument: Possible values are: SINGLE_PREC_HB - single precision height-balanced, DOUBLE_PREC_HB - double precision height-balanced. + --host-cache-size=# How many host names should be cached to avoid resolving. --ignore-builtin-innodb Disable initialization of builtin InnoDB plugin --ignore-db-dirs=name @@ -422,6 +423,8 @@ The following options may be given as the first argument: --memlock Lock mysqld in memory. --metadata-locks-cache-size=# Size of unused metadata locks cache + --metadata-locks-hash-instances=# + Number of metadata locks hash instances --min-examined-row-limit=# Don't write queries to slow log that examine fewer rows than that @@ -535,8 +538,10 @@ The following options may be given as the first argument: record samples --performance-schema Enable the performance schema. + (Defaults to on; use --skip-performance-schema to disable.) --performance-schema-accounts-size=# - Maximum number of instrumented user@host accounts. + Maximum number of instrumented user@host accounts. Use 0 + to disable, -1 for automated sizing. --performance-schema-consumer-events-stages-current Default startup value for the events_stages_current consumer. @@ -577,64 +582,84 @@ The following options may be given as the first argument: consumer. (Defaults to on; use --skip-performance-schema-consumer-thread-instrumentation to disable.) --performance-schema-digests-size=# - Size of the statement digest. + Size of the statement digest. Use 0 to disable, -1 for + automated sizing. --performance-schema-events-stages-history-long-size=# - Number of rows in EVENTS_STAGES_HISTORY_LONG. + Number of rows in EVENTS_STAGES_HISTORY_LONG. Use 0 to + disable, -1 for automated sizing. --performance-schema-events-stages-history-size=# - Number of rows per thread in EVENTS_STAGES_HISTORY. + Number of rows per thread in EVENTS_STAGES_HISTORY. Use 0 + to disable, -1 for automated sizing. --performance-schema-events-statements-history-long-size=# - Number of rows in EVENTS_STATEMENTS_HISTORY_LONG. + Number of rows in EVENTS_STATEMENTS_HISTORY_LONG. Use 0 + to disable, -1 for automated sizing. --performance-schema-events-statements-history-size=# Number of rows per thread in EVENTS_STATEMENTS_HISTORY. + Use 0 to disable, -1 for automated sizing. --performance-schema-events-waits-history-long-size=# - Number of rows in EVENTS_WAITS_HISTORY_LONG. + Number of rows in EVENTS_WAITS_HISTORY_LONG. Use 0 to + disable, -1 for automated sizing. --performance-schema-events-waits-history-size=# - Number of rows per thread in EVENTS_WAITS_HISTORY. + Number of rows per thread in EVENTS_WAITS_HISTORY. Use 0 + to disable, -1 for automated sizing. --performance-schema-hosts-size=# - Maximum number of instrumented hosts. + Maximum number of instrumented hosts. Use 0 to disable, + -1 for automated sizing. --performance-schema-instrument[=name] Default startup value for a performance schema instrument. --performance-schema-max-cond-classes=# Maximum number of condition instruments. --performance-schema-max-cond-instances=# - Maximum number of instrumented condition objects. + Maximum number of instrumented condition objects. Use 0 + to disable, -1 for automated sizing. --performance-schema-max-file-classes=# Maximum number of file instruments. --performance-schema-max-file-handles=# Maximum number of opened instrumented files. --performance-schema-max-file-instances=# - Maximum number of instrumented files. + Maximum number of instrumented files. Use 0 to disable, + -1 for automated sizing. --performance-schema-max-mutex-classes=# Maximum number of mutex instruments. --performance-schema-max-mutex-instances=# - Maximum number of instrumented MUTEX objects. + Maximum number of instrumented MUTEX objects. Use 0 to + disable, -1 for automated sizing. --performance-schema-max-rwlock-classes=# Maximum number of rwlock instruments. --performance-schema-max-rwlock-instances=# - Maximum number of instrumented RWLOCK objects. + Maximum number of instrumented RWLOCK objects. Use 0 to + disable, -1 for automated sizing. --performance-schema-max-socket-classes=# Maximum number of socket instruments. --performance-schema-max-socket-instances=# - Maximum number of opened instrumented sockets. + Maximum number of opened instrumented sockets. Use 0 to + disable, -1 for automated sizing. --performance-schema-max-stage-classes=# Maximum number of stage instruments. --performance-schema-max-statement-classes=# Maximum number of statement instruments. --performance-schema-max-table-handles=# - Maximum number of opened instrumented tables. + Maximum number of opened instrumented tables. Use 0 to + disable, -1 for automated sizing. --performance-schema-max-table-instances=# - Maximum number of instrumented tables. + Maximum number of instrumented tables. Use 0 to disable, + -1 for automated sizing. --performance-schema-max-thread-classes=# Maximum number of thread instruments. --performance-schema-max-thread-instances=# - Maximum number of instrumented threads. + Maximum number of instrumented threads. Use 0 to disable, + -1 for automated sizing. + --performance-schema-session-connect-attrs-size=# + Size of session attribute string buffer per thread. Use 0 + to disable, -1 for automated sizing. --performance-schema-setup-actors-size=# Maximum number of rows in SETUP_ACTORS. --performance-schema-setup-objects-size=# Maximum number of rows in SETUP_OBJECTS. --performance-schema-users-size=# - Maximum number of instrumented users. + Maximum number of instrumented users. Use 0 to disable, + -1 for automated sizing. --pid-file=name Pid file used by safe_mysqld --plugin-dir=name Directory for plugins --plugin-load=name Semicolon-separated list of plugins to load, where each @@ -1047,6 +1072,7 @@ gtid-strict-mode FALSE help TRUE histogram-size 0 histogram-type SINGLE_PREC_HB +host-cache-size 128 ignore-builtin-innodb FALSE ignore-db-dirs init-connect @@ -1096,7 +1122,7 @@ max-allowed-packet 1048576 max-binlog-cache-size 18446744073709547520 max-binlog-size 1073741824 max-binlog-stmt-cache-size 18446744073709547520 -max-connect-errors 10 +max-connect-errors 100 max-connections 151 max-delayed-threads 20 max-error-count 64 @@ -1114,6 +1140,7 @@ max-user-connections 0 max-write-lock-count 18446744073709551615 memlock FALSE metadata-locks-cache-size 1024 +metadata-locks-hash-instances 8 min-examined-row-limit 0 mrr-buffer-size 262144 multi-range-count 256 @@ -1139,8 +1166,8 @@ optimizer-search-depth 62 optimizer-selectivity-sampling-limit 100 optimizer-switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on optimizer-use-condition-selectivity 1 -performance-schema FALSE -performance-schema-accounts-size 100 +performance-schema TRUE +performance-schema-accounts-size 10 performance-schema-consumer-events-stages-current FALSE performance-schema-consumer-events-stages-history FALSE performance-schema-consumer-events-stages-history-long FALSE @@ -1153,35 +1180,36 @@ performance-schema-consumer-events-waits-history-long FALSE performance-schema-consumer-global-instrumentation TRUE performance-schema-consumer-statements-digest TRUE performance-schema-consumer-thread-instrumentation TRUE -performance-schema-digests-size 200 -performance-schema-events-stages-history-long-size 10000 -performance-schema-events-stages-history-size 10 -performance-schema-events-statements-history-long-size 10000 -performance-schema-events-statements-history-size 10 -performance-schema-events-waits-history-long-size 10000 -performance-schema-events-waits-history-size 10 -performance-schema-hosts-size 100 +performance-schema-digests-size 1000 +performance-schema-events-stages-history-long-size 100 +performance-schema-events-stages-history-size 5 +performance-schema-events-statements-history-long-size 100 +performance-schema-events-statements-history-size 5 +performance-schema-events-waits-history-long-size 100 +performance-schema-events-waits-history-size 5 +performance-schema-hosts-size 20 performance-schema-instrument performance-schema-max-cond-classes 80 -performance-schema-max-cond-instances 1000 +performance-schema-max-cond-instances 836 performance-schema-max-file-classes 50 performance-schema-max-file-handles 32768 -performance-schema-max-file-instances 10000 +performance-schema-max-file-instances 1556 performance-schema-max-mutex-classes 200 -performance-schema-max-mutex-instances 1000000 +performance-schema-max-mutex-instances 3282 performance-schema-max-rwlock-classes 30 -performance-schema-max-rwlock-instances 1000000 +performance-schema-max-rwlock-instances 1724 performance-schema-max-socket-classes 10 -performance-schema-max-socket-instances 1000 +performance-schema-max-socket-instances 179 performance-schema-max-stage-classes 150 -performance-schema-max-statement-classes 174 -performance-schema-max-table-handles 10000 -performance-schema-max-table-instances 1000 +performance-schema-max-statement-classes 175 +performance-schema-max-table-handles 445 +performance-schema-max-table-instances 445 performance-schema-max-thread-classes 50 -performance-schema-max-thread-instances 1000 +performance-schema-max-thread-instances 224 +performance-schema-session-connect-attrs-size 512 performance-schema-setup-actors-size 100 performance-schema-setup-objects-size 100 -performance-schema-users-size 100 +performance-schema-users-size 5 plugin-maturity unknown port 3306 port-open-timeout 0 diff --git a/mysql-test/r/mysqldump.result b/mysql-test/r/mysqldump.result index 33e10f29201..ffaa1a06b95 100644 --- a/mysql-test/r/mysqldump.result +++ b/mysql-test/r/mysqldump.result @@ -2698,13 +2698,13 @@ DROP TABLE t1, t2; # DROP TABLE IF EXISTS `test1`; Warnings: -Note 1051 Unknown table 'test1' +Note 1051 Unknown table 'test.test1' CREATE TABLE `test1` ( `a1` int(11) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1; DROP TABLE IF EXISTS `test2`; Warnings: -Note 1051 Unknown table 'test2' +Note 1051 Unknown table 'test.test2' CREATE TABLE `test2` ( `a2` int(11) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1; @@ -5241,7 +5241,7 @@ Table Create Table general_log CREATE TABLE `general_log` ( `event_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), `user_host` mediumtext NOT NULL, - `thread_id` int(11) NOT NULL, + `thread_id` bigint(21) unsigned NOT NULL, `server_id` int(10) unsigned NOT NULL, `command_type` varchar(64) NOT NULL, `argument` mediumtext NOT NULL @@ -5259,7 +5259,8 @@ slow_log CREATE TABLE `slow_log` ( `last_insert_id` int(11) NOT NULL, `insert_id` int(11) NOT NULL, `server_id` int(10) unsigned NOT NULL, - `sql_text` mediumtext NOT NULL + `sql_text` mediumtext NOT NULL, + `thread_id` bigint(21) unsigned NOT NULL ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log' SET @@global.log_output= @old_log_output_state; SET @@global.slow_query_log= @old_slow_query_log_state; diff --git a/mysql-test/r/partition.result b/mysql-test/r/partition.result index c68d43831ad..5ee29f8584f 100644 --- a/mysql-test/r/partition.result +++ b/mysql-test/r/partition.result @@ -67,6 +67,19 @@ AND A.b = '06' AND A.c = 343; DROP TABLE t1; # +# Bug#59503: explain extended crash in get_mm_leaf +# +CREATE TABLE t1 (a VARCHAR(51) CHARACTER SET latin1) +ENGINE=MyISAM +PARTITION BY KEY (a) PARTITIONS 1; +INSERT INTO t1 VALUES ('a'),('b'),('c'); +EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE a > 1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where +Warnings: +Note 1003 select 1 AS `1` from `test`.`t1` where (`test`.`t1`.`a` > 1) +DROP TABLE t1; +# # Bug#57778: failed primary key add to partitioned innodb table # inconsistent and crashes # @@ -303,6 +316,32 @@ INSERT INTO t1 VALUES (NULL); SELECT * FROM t1 WHERE pk < 0 ORDER BY pk; pk DROP TABLE t1; +SET sql_mode=no_engine_substitution; +CREATE TABLE t1 (a INT) +ENGINE=NonExistentEngine; +ERROR 42000: Unknown storage engine 'NonExistentEngine' +CREATE TABLE t1 (a INT) +ENGINE=NonExistentEngine +PARTITION BY HASH (a); +ERROR 42000: Unknown storage engine 'NonExistentEngine' +CREATE TABLE t1 (a INT) +ENGINE=Memory; +ALTER TABLE t1 ENGINE=NonExistentEngine; +ERROR 42000: Unknown storage engine 'NonExistentEngine' +ALTER TABLE t1 +PARTITION BY HASH (a) +(PARTITION p0 ENGINE=Memory, +PARTITION p1 ENGINE=NonExistentEngine); +ERROR 42000: Unknown storage engine 'NonExistentEngine' +ALTER TABLE t1 ENGINE=NonExistentEngine; +ERROR 42000: Unknown storage engine 'NonExistentEngine' +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL +) ENGINE=MEMORY DEFAULT CHARSET=latin1 +DROP TABLE t1; +SET sql_mode=''; CREATE TABLE t1 (a INT) ENGINE=NonExistentEngine; Warnings: @@ -339,6 +378,7 @@ t1 CREATE TABLE `t1` ( (PARTITION p0 ENGINE = MEMORY, PARTITION p1 ENGINE = MEMORY) */ DROP TABLE t1; +SET sql_mode=DEFAULT; CREATE TABLE t1 (a INT NOT NULL, KEY(a)) PARTITION BY RANGE(a) (PARTITION p1 VALUES LESS THAN (200), PARTITION pmax VALUES LESS THAN MAXVALUE); @@ -1056,13 +1096,13 @@ select * from t1 where f1 = 10; f1 f2 10 1 drop table t1; -set session storage_engine= 'memory'; +set session default_storage_engine= 'memory'; create table t1 (f_int1 int(11) default null) engine = memory partition by range (f_int1) subpartition by hash (f_int1) (partition part1 values less than (1000) (subpartition subpart11 engine = memory)); drop table t1; -set session storage_engine='myisam'; +set session default_storage_engine='myisam'; create table t1 (f_int1 integer, f_int2 integer, primary key (f_int1)) partition by hash(f_int1) partitions 2; insert into t1 values (1,1),(2,2); @@ -1885,8 +1925,7 @@ WHERE t1.id IN ( SELECT distinct id FROM t4 WHERE taken BETWEEN @f_date AND date_add(@t_date, INTERVAL 1 DAY)) -ORDER BY t1.id -; +ORDER BY t1.id; MyISAM_part 16421 19092 @@ -1907,7 +1946,7 @@ INSERT INTO t1 VALUES ('2006-09-29 21:50:01',22589,'Verified'); DROP TABLE IF EXISTS t2; Warnings: -Note 1051 Unknown table 't2' +Note 1051 Unknown table 'test.t2' CREATE TABLE t2 ( id int(8) NOT NULL, severity tinyint(4) NOT NULL DEFAULT '0', diff --git a/mysql-test/r/partition_binlog.result b/mysql-test/r/partition_binlog.result index 1db427868f4..21eca8f1c00 100644 --- a/mysql-test/r/partition_binlog.result +++ b/mysql-test/r/partition_binlog.result @@ -9,7 +9,7 @@ PARTITION BY RANGE (id) PARTITION pmax VALUES LESS THAN (MAXVALUE)); INSERT INTO t1 VALUES (1), (10), (100), (1000); ALTER TABLE t1 TRUNCATE PARTITION p1; -ERROR HY000: Incorrect partition name +ERROR HY000: Unknown partition 'p1' in table 't1' ALTER TABLE t1 DROP PARTITION p1; ERROR HY000: Error in list of partitions to DROP # No error returned, output in table format instead: diff --git a/mysql-test/r/partition_debug_sync.result b/mysql-test/r/partition_debug_sync.result index 0549a6a8bdd..c30651c1c0d 100644 --- a/mysql-test/r/partition_debug_sync.result +++ b/mysql-test/r/partition_debug_sync.result @@ -5,7 +5,9 @@ SET DEBUG_SYNC= 'RESET'; # Test when remove partitioning is done while drop table is waiting # for the table. # After MDL was introduced, there is no longer any race, so test is done -# by adding a small sleep to verify that the delete waits. +# by adding a small sleep to verify that the delete waits. This happens +# only until ALTER tries to upgrade its MDL lock, which ends up in MDL +# deadlock which is correctly reported. # Con 1 SET DEBUG_SYNC= 'RESET'; CREATE TABLE t1 @@ -19,14 +21,15 @@ PARTITION p1 VALUES LESS THAN (20), PARTITION p2 VALUES LESS THAN (100), PARTITION p3 VALUES LESS THAN MAXVALUE ) */; SET DEBUG_SYNC= 'alter_table_before_create_table_no_lock SIGNAL removing_partitioning WAIT_FOR waiting_for_alter'; -SET DEBUG_SYNC= 'alter_table_before_main_binlog SIGNAL partitioning_removed'; +SET DEBUG_SYNC= 'mdl_acquire_lock_wait SIGNAL waiting_for_upgrade'; ALTER TABLE t1 REMOVE PARTITIONING; # Con default SET DEBUG_SYNC= 'now WAIT_FOR removing_partitioning'; SET DEBUG_SYNC= 'mdl_acquire_lock_wait SIGNAL waiting_for_alter'; -SET DEBUG_SYNC= 'rm_table_no_locks_before_delete_table WAIT_FOR partitioning_removed'; +SET DEBUG_SYNC= 'rm_table_no_locks_before_delete_table WAIT_FOR waiting_for_upgrade'; DROP TABLE IF EXISTS t1; # Con 1 +ERROR 40001: Deadlock found when trying to get lock; try restarting transaction SET DEBUG_SYNC= 'RESET'; SET DEBUG_SYNC= 'RESET'; # @@ -58,3 +61,27 @@ SET DEBUG_SYNC= 'RESET'; # Con default SET DEBUG_SYNC= 'RESET'; End of 5.1 tests +# +# Coverage test for non pruned ha_partition::store_lock() +# +CREATE TABLE t1 (a int) ENGINE = InnoDB; +CREATE TABLE t2 (a int PRIMARY KEY) +ENGINE = InnoDB PARTITION BY HASH (a) PARTITIONS 3; +HANDLER t1 OPEN; +# Con1 +LOCK TABLES t1 WRITE, t2 READ; +# Default +SET DEBUG_SYNC="wait_for_lock SIGNAL locking"; +INSERT INTO t2 VALUES (1), (2), (3); +# Con1 +SET DEBUG_SYNC="now WAIT_FOR locking"; +ALTER TABLE t1 ADD COLUMN b int; +# Default +ERROR HY000: Wait on a lock was aborted due to a pending exclusive lock +SELECT 1; +1 +1 +# Con1 +UNLOCK TABLES; +# Default +DROP TABLE t1, t2; diff --git a/mysql-test/r/partition_disabled.result b/mysql-test/r/partition_disabled.result index 505bec79610..edf3a56d9b2 100644 --- a/mysql-test/r/partition_disabled.result +++ b/mysql-test/r/partition_disabled.result @@ -56,7 +56,7 @@ ERROR HY000: The MariaDB server is running with the --skip-partition option so i ALTER TABLE t1 PARTITION BY KEY(joined) PARTITIONS 2; ERROR HY000: The MariaDB server is running with the --skip-partition option so it cannot execute this statement drop table t1; -ERROR 42S02: Unknown table 't1' +ERROR 42S02: Unknown table 'test.t1' CREATE TABLE t1 ( firstname VARCHAR(25) NOT NULL, lastname VARCHAR(25) NOT NULL, @@ -73,7 +73,7 @@ PARTITION p4 VALUES LESS THAN MAXVALUE ); ERROR HY000: The MariaDB server is running with the --skip-partition option so it cannot execute this statement drop table t1; -ERROR 42S02: Unknown table 't1' +ERROR 42S02: Unknown table 'test.t1' CREATE TABLE t1 (id INT, purchased DATE) PARTITION BY RANGE( YEAR(purchased) ) SUBPARTITION BY HASH( TO_DAYS(purchased) ) @@ -84,7 +84,7 @@ PARTITION p2 VALUES LESS THAN MAXVALUE ); ERROR HY000: The MariaDB server is running with the --skip-partition option so it cannot execute this statement drop table t1; -ERROR 42S02: Unknown table 't1' +ERROR 42S02: Unknown table 'test.t1' create table t1 (a varchar(10) charset latin1 collate latin1_bin); insert into t1 values (''),(' '),('a'),('a '),('a '); explain partitions select * from t1 where a='a ' OR a='a'; diff --git a/mysql-test/r/partition_innodb.result b/mysql-test/r/partition_innodb.result index a1accfa8e3d..2d35fe0bf07 100644 --- a/mysql-test/r/partition_innodb.result +++ b/mysql-test/r/partition_innodb.result @@ -31,6 +31,17 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range PRIMARY,b b 67 NULL 18 Using where; Using index DROP TABLE t1; # +# Bug#13007154: Crash in keys_to_use_for_scanning with ORDER BY +# and PARTITIONING +# +CREATE TABLE t1 (a INT, KEY(a)) +ENGINE = InnoDB +PARTITION BY KEY (a) PARTITIONS 1; +SELECT 1 FROM t1 WHERE a > (SELECT LAST_INSERT_ID() FROM t1 LIMIT 0) +ORDER BY a; +1 +DROP TABLE t1; +# # Bug#56287: crash when using Partition datetime in sub in query # CREATE TABLE t1 @@ -60,7 +71,7 @@ DROP TABLE t1; # Bug#54747: Deadlock between REORGANIZE PARTITION and # SELECT is not detected # -SET @old_innodb_thread_concurrency:= @@innodb_thread_concurrency; +SET @old_innodb_thread_concurrency := @@innodb_thread_concurrency; SET @old_innodb_thread_sleep_delay := @@innodb_thread_sleep_delay; SET GLOBAL innodb_thread_concurrency = 1; CREATE TABLE t1 diff --git a/mysql-test/r/partition_innodb_plugin.result b/mysql-test/r/partition_innodb_plugin.result index ceade2a793c..7a84745e611 100644 --- a/mysql-test/r/partition_innodb_plugin.result +++ b/mysql-test/r/partition_innodb_plugin.result @@ -76,18 +76,18 @@ t1.par SET innodb_strict_mode = OFF; ALTER TABLE t1 ADD PARTITION PARTITIONS 2; Warnings: -Warning 140 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table. -Warning 140 InnoDB: ignoring KEY_BLOCK_SIZE=4. -Warning 140 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table. -Warning 140 InnoDB: ignoring KEY_BLOCK_SIZE=4. -Warning 140 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table. -Warning 140 InnoDB: ignoring KEY_BLOCK_SIZE=4. +Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table. +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=4. +Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table. +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=4. +Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table. +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=4. t1.frm t1.par ALTER TABLE t1 REBUILD PARTITION p0; Warnings: -Warning 140 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table. -Warning 140 InnoDB: ignoring KEY_BLOCK_SIZE=4. +Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table. +Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=4. UNLOCK TABLES; SHOW CREATE TABLE t1; Table Create Table diff --git a/mysql-test/r/partition_mgm_err.result b/mysql-test/r/partition_mgm_err.result index a13278d724e..cbf45a2b7be 100644 --- a/mysql-test/r/partition_mgm_err.result +++ b/mysql-test/r/partition_mgm_err.result @@ -98,7 +98,7 @@ PARTITION BY KEY (a) (PARTITION x0, PARTITION x1); ALTER TABLE t1 ADD PARTITION PARTITIONS 0; ERROR HY000: At least one partition must be added -ALTER TABLE t1 ADD PARTITION PARTITIONS 1024; +ALTER TABLE t1 ADD PARTITION PARTITIONS 8192; ERROR HY000: Too many partitions (including subpartitions) were defined ALTER TABLE t1 DROP PARTITION x0; ERROR HY000: DROP PARTITION can only be used on RANGE/LIST partitions diff --git a/mysql-test/r/partition_myisam.result b/mysql-test/r/partition_myisam.result index 10586ddc548..80b3a9511ea 100644 --- a/mysql-test/r/partition_myisam.result +++ b/mysql-test/r/partition_myisam.result @@ -1,80 +1,62 @@ DROP TABLE IF EXISTS t1, t2; # -# Bug#50036: Inconsistent errors when using TIMESTAMP -# columns/expressions -# Added test with existing TIMESTAMP partitioning (when it was allowed). -CREATE TABLE t1 (a TIMESTAMP) -ENGINE = MyISAM -PARTITION BY HASH (UNIX_TIMESTAMP(a)); -INSERT INTO t1 VALUES ('2000-01-02 03:04:05'); -SELECT * FROM t1; -a -2000-01-02 03:04:05 -FLUSH TABLES; -# replacing t1.frm with TO_DAYS(a) which was allowed earlier. -# Disable warnings, since the result would differ when running with -# --ps-protocol (only for the 'SELECT * FROM t1' statement). -SELECT * FROM t1; -a -2000-01-02 03:04:05 -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `a` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP -) ENGINE=<curr_engine> DEFAULT CHARSET=latin1 -/*!50100 PARTITION BY HASH (TO_DAYS(a)) */ -INSERT INTO t1 VALUES ('2001-02-03 04:05:06'); -SELECT * FROM t1; -a -2000-01-02 03:04:05 -2001-02-03 04:05:06 -ALTER TABLE t1 ADD PARTITION PARTITIONS 2; -Warnings: -Warning 1486 Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed -ALTER TABLE t1 -PARTITION BY RANGE (TO_DAYS(a)) -(PARTITION p0 VALUES LESS THAN (10000), -PARTITION p1 VALUES LESS THAN (MAXVALUE)); -ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed +# BUG#11933226 - 60681: CHECKSUM TABLE RETURNS 0 FOR PARTITIONED TABLE +# +CREATE TABLE t1 ( +i INT +) +ENGINE=MyISAM +PARTITION BY RANGE (i) +(PARTITION p3 VALUES LESS THAN (3), +PARTITION p5 VALUES LESS THAN (5), +PARTITION pMax VALUES LESS THAN MAXVALUE); +INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6); +CHECKSUM TABLE t1; +Table Checksum +test.t1 2653438147 +ALTER TABLE t1 CHECKSUM = 1; +CHECKSUM TABLE t1 EXTENDED; +Table Checksum +test.t1 2653438147 +# Before patch this returned 0! +CHECKSUM TABLE t1; +Table Checksum +test.t1 2653438147 SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( - `a` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP -) ENGINE=MyISAM DEFAULT CHARSET=latin1 -/*!50100 PARTITION BY HASH (TO_DAYS(a)) -PARTITIONS 3 */ -CREATE TABLE t2 LIKE t1; -SHOW CREATE TABLE t2; -Table Create Table -t2 CREATE TABLE `t2` ( - `a` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP -) ENGINE=MyISAM DEFAULT CHARSET=latin1 -/*!50100 PARTITION BY HASH (TO_DAYS(a)) -PARTITIONS 3 */ -Warnings: -Warning 1486 Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed -DROP TABLE t2; -CREATE TABLE t2 SELECT * FROM t1; -DROP TABLE t2; -ALTER TABLE t1 PARTITION BY HASH (UNIX_TIMESTAMP(a)); + `i` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 CHECKSUM=1 +/*!50100 PARTITION BY RANGE (i) +(PARTITION p3 VALUES LESS THAN (3) ENGINE = MyISAM, + PARTITION p5 VALUES LESS THAN (5) ENGINE = MyISAM, + PARTITION pMax VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */ +DROP TABLE t1; +# Same test without partitioning +CREATE TABLE t1 ( +i INT +) ENGINE=MyISAM; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( - `a` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP + `i` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 -/*!50100 PARTITION BY HASH (UNIX_TIMESTAMP(a)) */ -ALTER TABLE t1 ADD PARTITION PARTITIONS 2; +INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6); +CHECKSUM TABLE t1; +Table Checksum +test.t1 2653438147 +ALTER TABLE t1 CHECKSUM = 1; +CHECKSUM TABLE t1 EXTENDED; +Table Checksum +test.t1 2653438147 +CHECKSUM TABLE t1; +Table Checksum +test.t1 2653438147 SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( - `a` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP -) ENGINE=MyISAM DEFAULT CHARSET=latin1 -/*!50100 PARTITION BY HASH (UNIX_TIMESTAMP(a)) -PARTITIONS 3 */ -SELECT * FROM t1; -a -2000-01-02 03:04:05 -2001-02-03 04:05:06 + `i` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 CHECKSUM=1 DROP TABLE t1; # # Bug#31931: Mix of handlers error message @@ -108,7 +90,7 @@ ERROR HY000: Failed to read from the .par file # Note that it is currently impossible to drop a partitioned table # without the .par file DROP TABLE t1; -ERROR 42S02: Unknown table 't1' +ERROR 42S02: Unknown table 'test.t1' # # Bug#50392: insert_id is not reset for partitioned tables # auto_increment on duplicate entry @@ -247,3 +229,18 @@ PARTITION p1 VALUES LESS THAN (100) MAX_ROWS=100, PARTITION pMax VALUES LESS THAN MAXVALUE); INSERT INTO t1 VALUES (1, "Partition p1, first row"); DROP TABLE t1; +# +# bug#11760213-52599: ALTER TABLE REMOVE PARTITIONING ON NON-PARTITIONED +# TABLE CORRUPTS MYISAM +DROP TABLE if exists `t1`; +CREATE TABLE `t1`(`a` INT)ENGINE=myisam; +ALTER TABLE `t1` ADD COLUMN `b` INT; +CREATE UNIQUE INDEX `i1` ON `t1`(`b`); +CREATE UNIQUE INDEX `i2` ON `t1`(`a`); +ALTER TABLE `t1` ADD PRIMARY KEY (`a`); +ALTER TABLE `t1` REMOVE PARTITIONING; +ERROR HY000: Partition management on a not partitioned table is not possible +CHECK TABLE `t1` EXTENDED; +Table Op Msg_type Msg_text +test.t1 check status OK +DROP TABLE t1; diff --git a/mysql-test/r/partition_not_blackhole.result b/mysql-test/r/partition_not_blackhole.result index dc0339f8c48..923d70c0ad6 100644 --- a/mysql-test/r/partition_not_blackhole.result +++ b/mysql-test/r/partition_not_blackhole.result @@ -11,6 +11,6 @@ t1 SHOW CREATE TABLE t1; ERROR HY000: Incorrect information in file: './test/t1.frm' DROP TABLE t1; -ERROR 42S02: Unknown table 't1' +ERROR 42S02: Unknown table 'test.t1' t1.frm t1.par diff --git a/mysql-test/r/partition_truncate.result b/mysql-test/r/partition_truncate.result index 66c0cd3d9da..7a82e47d818 100644 --- a/mysql-test/r/partition_truncate.result +++ b/mysql-test/r/partition_truncate.result @@ -5,7 +5,7 @@ partition by list (a) alter table t1 truncate partition p1,p1; ERROR HY000: Incorrect partition name alter table t1 truncate partition p0; -ERROR HY000: Incorrect partition name +ERROR HY000: Unknown partition 'p0' in table 't1' drop table t1; create table t1 (a int) partition by list (a) diff --git a/mysql-test/r/plugin.result b/mysql-test/r/plugin.result index 241e7a11ecc..ce338938d6f 100644 --- a/mysql-test/r/plugin.result +++ b/mysql-test/r/plugin.result @@ -15,7 +15,7 @@ PLUGIN_STATUS ACTIVE PLUGIN_TYPE STORAGE ENGINE PLUGIN_TYPE_VERSION # PLUGIN_LIBRARY ha_example.so -PLUGIN_LIBRARY_VERSION 1.5 +PLUGIN_LIBRARY_VERSION 1.7 PLUGIN_AUTHOR Brian Aker, MySQL AB PLUGIN_DESCRIPTION Example storage engine PLUGIN_LICENSE GPL @@ -28,7 +28,7 @@ PLUGIN_STATUS ACTIVE PLUGIN_TYPE DAEMON PLUGIN_TYPE_VERSION # PLUGIN_LIBRARY ha_example.so -PLUGIN_LIBRARY_VERSION 1.5 +PLUGIN_LIBRARY_VERSION 1.7 PLUGIN_AUTHOR Sergei Golubchik PLUGIN_DESCRIPTION Unusable Daemon PLUGIN_LICENSE GPL @@ -60,7 +60,7 @@ PLUGIN_STATUS DELETED PLUGIN_TYPE STORAGE ENGINE PLUGIN_TYPE_VERSION # PLUGIN_LIBRARY ha_example.so -PLUGIN_LIBRARY_VERSION 1.5 +PLUGIN_LIBRARY_VERSION 1.7 PLUGIN_AUTHOR Brian Aker, MySQL AB PLUGIN_DESCRIPTION Example storage engine PLUGIN_LICENSE GPL @@ -137,12 +137,16 @@ t1 CREATE TABLE `t1` ( ) ENGINE=EXAMPLE DEFAULT CHARSET=latin1 `ULL`=10000000000000000000 `one_or_two`='ttt' `YESNO`=SSS `VAROPT`='5' #alter table alter table t1 ULL=10000000; +Warnings: +Note 1105 EXAMPLE DEBUG: ULL 4294967290 -> 10000000 show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL ) ENGINE=EXAMPLE DEFAULT CHARSET=latin1 `one_or_two`='ttt' `YESNO`=SSS `VAROPT`='5' `ULL`=10000000 alter table t1 change a a int complex='c,c,c'; +Warnings: +Note 1105 EXAMPLE DEBUG: Field `a` COMPLEX '(null)' -> 'c,c,c' show create table t1; Table Create Table t1 CREATE TABLE `t1` ( @@ -168,6 +172,8 @@ select create_options from information_schema.tables where table_schema='test' a create_options `ULL`=4660 `VAROPT`='5' ALTER TABLE t1 ULL=DEFAULT; +Warnings: +Note 1105 EXAMPLE DEBUG: ULL 4660 -> 4294967295 SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( diff --git a/mysql-test/r/profiling.result b/mysql-test/r/profiling.result index 6292cd085e4..4c531a8a5f7 100644 --- a/mysql-test/r/profiling.result +++ b/mysql-test/r/profiling.result @@ -123,8 +123,8 @@ select query_id, count(*), sum(duration) from information_schema.profiling group select CPU_user, CPU_system, Context_voluntary, Context_involuntary, Block_ops_in, Block_ops_out, Messages_sent, Messages_received, Page_faults_major, Page_faults_minor, Swaps, Source_function, Source_file, Source_line from information_schema.profiling; drop table if exists t1, t2, t3; Warnings: -Note 1051 Unknown table 't2' -Note 1051 Unknown table 't3' +Note 1051 Unknown table 'test.t2' +Note 1051 Unknown table 'test.t3' create table t1 (id int ); create table t2 (id int not null); create table t3 (id int not null primary key); @@ -309,7 +309,7 @@ select @@profiling; set session profiling = OFF; drop table if exists profile_log; Warnings: -Note 1051 Unknown table 'profile_log' +Note 1051 Unknown table 'test.profile_log' create table profile_log (how_many int); drop procedure if exists p1; drop procedure if exists p2; diff --git a/mysql-test/r/ps.result b/mysql-test/r/ps.result index 95217d9716a..f6a2a16f038 100644 --- a/mysql-test/r/ps.result +++ b/mysql-test/r/ps.result @@ -51,7 +51,7 @@ execute stmt4; prepare stmt4 from 'drop table t2'; execute stmt4; execute stmt4; -ERROR 42S02: Unknown table 't2' +ERROR 42S02: Unknown table 'test.t2' prepare stmt5 from 'select ? + a from t1'; set @a=1; execute stmt5 using @a; @@ -526,7 +526,7 @@ FOUND_ROWS() deallocate prepare stmt; drop table if exists t1; Warnings: -Note 1051 Unknown table 't1' +Note 1051 Unknown table 'test.t1' create table t1 (c1 int(11) not null, c2 int(11) not null, primary key (c1,c2), key c2 (c2), key c1 (c1)); insert into t1 values (200887, 860); @@ -1201,13 +1201,13 @@ SET @aux= "SELECT COUNT(*) prepare my_stmt from @aux; execute my_stmt; COUNT(*) -42 +43 execute my_stmt; COUNT(*) -42 +43 execute my_stmt; COUNT(*) -42 +43 deallocate prepare my_stmt; drop procedure if exists p1| drop table if exists t1| @@ -2799,48 +2799,48 @@ drop table if exists t2; create procedure proc_1() show warnings; drop table if exists t1; Warnings: -Note 1051 Unknown table 't1' +Note 1051 Unknown table 'test.t1' call proc_1(); Level Code Message -Note 1051 Unknown table 't1' +Note 1051 Unknown table 'test.t1' drop table if exists t2; Warnings: -Note 1051 Unknown table 't2' +Note 1051 Unknown table 'test.t2' call proc_1(); Level Code Message -Note 1051 Unknown table 't2' +Note 1051 Unknown table 'test.t2' drop table if exists t1, t2; Warnings: -Note 1051 Unknown table 't1' -Note 1051 Unknown table 't2' +Note 1051 Unknown table 'test.t1' +Note 1051 Unknown table 'test.t2' call proc_1(); Level Code Message -Note 1051 Unknown table 't1' -Note 1051 Unknown table 't2' +Note 1051 Unknown table 'test.t1' +Note 1051 Unknown table 'test.t2' drop procedure proc_1; create function func_1() returns int begin show warnings; return 1; end| ERROR 0A000: Not allowed to return a result set from a function prepare abc from "show warnings"; drop table if exists t1; Warnings: -Note 1051 Unknown table 't1' +Note 1051 Unknown table 'test.t1' execute abc; Level Code Message -Note 1051 Unknown table 't1' +Note 1051 Unknown table 'test.t1' drop table if exists t2; Warnings: -Note 1051 Unknown table 't2' +Note 1051 Unknown table 'test.t2' execute abc; Level Code Message -Note 1051 Unknown table 't2' +Note 1051 Unknown table 'test.t2' drop table if exists t1, t2; Warnings: -Note 1051 Unknown table 't1' -Note 1051 Unknown table 't2' +Note 1051 Unknown table 'test.t1' +Note 1051 Unknown table 'test.t2' execute abc; Level Code Message -Note 1051 Unknown table 't1' -Note 1051 Unknown table 't2' +Note 1051 Unknown table 'test.t1' +Note 1051 Unknown table 'test.t2' deallocate prepare abc; set @my_password="password"; set @my_data="clear text to encode"; @@ -2926,7 +2926,7 @@ i j DROP TABLE t1, t2; drop table if exists t1; Warnings: -Note 1051 Unknown table 't1' +Note 1051 Unknown table 'test.t1' prepare stmt from "create table t1 (c char(100) character set utf8, key (c(10)))"; execute stmt; diff --git a/mysql-test/r/ps_1general.result b/mysql-test/r/ps_1general.result index 0a8aea94e8a..9981156bc5f 100644 --- a/mysql-test/r/ps_1general.result +++ b/mysql-test/r/ps_1general.result @@ -325,10 +325,10 @@ drop table if exists t5; prepare stmt1 from ' drop table if exists t5 ' ; execute stmt1 ; Warnings: -Note 1051 Unknown table 't5' +Note 1051 Unknown table 'test.t5' prepare stmt1 from ' drop table t5 ' ; execute stmt1 ; -ERROR 42S02: Unknown table 't5' +ERROR 42S02: Unknown table 'test.t5' prepare stmt1 from ' SELECT @@version ' ; execute stmt1 ; @@version diff --git a/mysql-test/r/ps_ddl1.result b/mysql-test/r/ps_ddl1.result index 87abcd90590..667cbed8a7a 100644 --- a/mysql-test/r/ps_ddl1.result +++ b/mysql-test/r/ps_ddl1.result @@ -420,7 +420,7 @@ call p_verify_reprepare_count(0); SUCCESS execute stmt; -ERROR 42S02: Unknown table 't1' +ERROR 42S02: Unknown table 'test.t1' call p_verify_reprepare_count(0); SUCCESS diff --git a/mysql-test/r/query_cache.result b/mysql-test/r/query_cache.result index 62f8a9728c3..18f9db1743d 100644 --- a/mysql-test/r/query_cache.result +++ b/mysql-test/r/query_cache.result @@ -1944,6 +1944,41 @@ COUNT(*) DROP TABLE t1; SET GLOBAL query_cache_size= @qc; # +End of 5.5 tests +# +# MDEV-617 LP:671189 - Query cache is not used for tables or +# databases with dots in their names +# +CREATE DATABASE `foo.bar`; +use `foo.bar`; +flush status; +CREATE TABLE moocow (a int); +INSERT INTO moocow VALUES (1), (2), (3); +SHOW STATUS LIKE 'Qcache_inserts'; +Variable_name Value +Qcache_inserts 0 +SELECT * FROM moocow; +a +1 +2 +3 +SHOW STATUS LIKE 'Qcache_inserts'; +Variable_name Value +Qcache_inserts 1 +SHOW STATUS LIKE 'Qcache_hits'; +Variable_name Value +Qcache_hits 0 +SELECT * FROM moocow; +a +1 +2 +3 +SHOW STATUS LIKE 'Qcache_hits'; +Variable_name Value +Qcache_hits 1 +use test; +drop database `foo.bar`; +End of 10.0 tests restore defaults SET GLOBAL query_cache_type= default; SET GLOBAL query_cache_size= default; diff --git a/mysql-test/r/read_only.result b/mysql-test/r/read_only.result index 3811c6c5487..c9c569137b2 100644 --- a/mysql-test/r/read_only.result +++ b/mysql-test/r/read_only.result @@ -117,10 +117,10 @@ select @@global.read_only; 1 unlock tables; drop temporary table ttt; -ERROR 42S02: Unknown table 'ttt' +ERROR 42S02: Unknown table 'test.ttt' drop temporary table if exists ttt; Warnings: -Note 1051 Unknown table 'ttt' +Note 1051 Unknown table 'test.ttt' connection default; set global read_only=0; drop table t1,t2; diff --git a/mysql-test/r/rename.result b/mysql-test/r/rename.result index 7433ab8a0c9..74370ba74dd 100644 --- a/mysql-test/r/rename.result +++ b/mysql-test/r/rename.result @@ -39,7 +39,7 @@ select * from t3; 3 table 3 drop table if exists t1,t2,t3,t4; Warnings: -Note 1051 Unknown table 't4' +Note 1051 Unknown table 'test.t4' CREATE TABLE t1 (a int); CREATE TABLE t3 (a int); FLUSH TABLES WITH READ LOCK; diff --git a/mysql-test/r/row-checksum-old.result b/mysql-test/r/row-checksum-old.result index 87f0bb8af2d..ef523463860 100644 --- a/mysql-test/r/row-checksum-old.result +++ b/mysql-test/r/row-checksum-old.result @@ -73,7 +73,7 @@ test.t1 4108368782 drop table if exists t1; create table t1 (a int null, v varchar(100)) engine=innodb checksum=0 row_format=fixed; Warnings: -Warning 140 InnoDB: assuming ROW_FORMAT=COMPACT. +Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT. insert into t1 values(null, null), (1, "hello"); checksum table t1; Table Checksum diff --git a/mysql-test/r/row-checksum.result b/mysql-test/r/row-checksum.result index 9e58d6fa96e..fb8a1260a1d 100644 --- a/mysql-test/r/row-checksum.result +++ b/mysql-test/r/row-checksum.result @@ -73,7 +73,7 @@ test.t1 3885665021 drop table if exists t1; create table t1 (a int null, v varchar(100)) engine=innodb checksum=0 row_format=fixed; Warnings: -Warning 140 InnoDB: assuming ROW_FORMAT=COMPACT. +Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT. insert into t1 values(null, null), (1, "hello"); checksum table t1; Table Checksum diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index c086a62275a..d639580acf9 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -4175,18 +4175,24 @@ str_to_date('2007-10-00','%Y-%m-%d') between '2007/09/01 00:00:00' set SQL_MODE=TRADITIONAL; select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34'; str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34' -1 +NULL +Warnings: +Warning 1411 Incorrect datetime value: '2007-10-00 12:34' for function str_to_date select str_to_date('2007-10-01 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34'; str_to_date('2007-10-01 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34' 0 select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-01 12:34'; str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-01 12:34' -0 +NULL +Warnings: +Warning 1411 Incorrect datetime value: '2007-10-00 12:34' for function str_to_date select str_to_date('2007-10-00','%Y-%m-%d') between '2007/09/01' and '2007/10/20'; str_to_date('2007-10-00','%Y-%m-%d') between '2007/09/01' and '2007/10/20' -1 +NULL +Warnings: +Warning 1411 Incorrect datetime value: '2007-10-00' for function str_to_date set SQL_MODE=DEFAULT; select str_to_date('2007-10-00','%Y-%m-%d') between '' and '2007/10/20'; str_to_date('2007-10-00','%Y-%m-%d') between '' and '2007/10/20' diff --git a/mysql-test/r/select_jcl6.result b/mysql-test/r/select_jcl6.result index 00b356fc1c0..18b050b53e7 100644 --- a/mysql-test/r/select_jcl6.result +++ b/mysql-test/r/select_jcl6.result @@ -4186,18 +4186,24 @@ str_to_date('2007-10-00','%Y-%m-%d') between '2007/09/01 00:00:00' set SQL_MODE=TRADITIONAL; select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34'; str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34' -1 +NULL +Warnings: +Warning 1411 Incorrect datetime value: '2007-10-00 12:34' for function str_to_date select str_to_date('2007-10-01 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34'; str_to_date('2007-10-01 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34' 0 select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-01 12:34'; str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-01 12:34' -0 +NULL +Warnings: +Warning 1411 Incorrect datetime value: '2007-10-00 12:34' for function str_to_date select str_to_date('2007-10-00','%Y-%m-%d') between '2007/09/01' and '2007/10/20'; str_to_date('2007-10-00','%Y-%m-%d') between '2007/09/01' and '2007/10/20' -1 +NULL +Warnings: +Warning 1411 Incorrect datetime value: '2007-10-00' for function str_to_date set SQL_MODE=DEFAULT; select str_to_date('2007-10-00','%Y-%m-%d') between '' and '2007/10/20'; str_to_date('2007-10-00','%Y-%m-%d') between '' and '2007/10/20' diff --git a/mysql-test/r/select_pkeycache.result b/mysql-test/r/select_pkeycache.result index c086a62275a..d639580acf9 100644 --- a/mysql-test/r/select_pkeycache.result +++ b/mysql-test/r/select_pkeycache.result @@ -4175,18 +4175,24 @@ str_to_date('2007-10-00','%Y-%m-%d') between '2007/09/01 00:00:00' set SQL_MODE=TRADITIONAL; select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34'; str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34' -1 +NULL +Warnings: +Warning 1411 Incorrect datetime value: '2007-10-00 12:34' for function str_to_date select str_to_date('2007-10-01 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34'; str_to_date('2007-10-01 12:34','%Y-%m-%d %H:%i') = '2007-10-00 12:34' 0 select str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-01 12:34'; str_to_date('2007-10-00 12:34','%Y-%m-%d %H:%i') = '2007-10-01 12:34' -0 +NULL +Warnings: +Warning 1411 Incorrect datetime value: '2007-10-00 12:34' for function str_to_date select str_to_date('2007-10-00','%Y-%m-%d') between '2007/09/01' and '2007/10/20'; str_to_date('2007-10-00','%Y-%m-%d') between '2007/09/01' and '2007/10/20' -1 +NULL +Warnings: +Warning 1411 Incorrect datetime value: '2007-10-00' for function str_to_date set SQL_MODE=DEFAULT; select str_to_date('2007-10-00','%Y-%m-%d') between '' and '2007/10/20'; str_to_date('2007-10-00','%Y-%m-%d') between '' and '2007/10/20' diff --git a/mysql-test/r/signal.result b/mysql-test/r/signal.result index 062b866475d..a5eb24442b4 100644 --- a/mysql-test/r/signal.result +++ b/mysql-test/r/signal.result @@ -1191,8 +1191,6 @@ end $$ call test_signal() $$ Caught by SQLSTATE Caught by SQLSTATE -Warnings: -Warning 1012 Raising a warning drop procedure test_signal $$ create procedure test_signal() begin @@ -1208,8 +1206,6 @@ end $$ call test_signal() $$ Caught by number Caught by number -Warnings: -Warning 1012 Raising a warning drop procedure test_signal $$ create procedure test_signal() begin @@ -1225,8 +1221,6 @@ end $$ call test_signal() $$ Caught by SQLWARNING Caught by SQLWARNING -Warnings: -Warning 1012 Raising a warning drop procedure test_signal $$ create procedure test_signal() begin @@ -1242,8 +1236,6 @@ end $$ call test_signal() $$ Caught by SQLSTATE Caught by SQLSTATE -Warnings: -Error 1012 Raising a not found drop procedure test_signal $$ create procedure test_signal() begin @@ -1259,8 +1251,6 @@ end $$ call test_signal() $$ Caught by number Caught by number -Warnings: -Error 1012 Raising a not found drop procedure test_signal $$ create procedure test_signal() begin @@ -1276,8 +1266,6 @@ end $$ call test_signal() $$ Caught by NOT FOUND Caught by NOT FOUND -Warnings: -Error 1012 Raising a not found drop procedure test_signal $$ create procedure test_signal() begin @@ -1293,8 +1281,6 @@ end $$ call test_signal() $$ Caught by SQLSTATE Caught by SQLSTATE -Warnings: -Error 1012 Raising an error drop procedure test_signal $$ create procedure test_signal() begin @@ -1310,8 +1296,6 @@ end $$ call test_signal() $$ Caught by number Caught by number -Warnings: -Error 1012 Raising an error drop procedure test_signal $$ create procedure test_signal() begin @@ -1327,25 +1311,29 @@ end $$ call test_signal() $$ Caught by SQLEXCEPTION Caught by SQLEXCEPTION -Warnings: -Error 1012 Raising an error drop procedure test_signal $$ # # Test where SIGNAL can be used # + +# RETURN statement clears Diagnostics Area, thus +# the warnings raised in a stored function are not +# visible outsidef the stored function. So, we're using +# @@warning_count variable to check that SIGNAL succeeded. + create function test_signal_func() returns integer begin +DECLARE v INT; DECLARE warn CONDITION FOR SQLSTATE "01XXX"; SIGNAL warn SET MESSAGE_TEXT = "This function SIGNAL a warning", MYSQL_ERRNO = 1012; -return 5; +SELECT @@warning_count INTO v; +return v; end $$ select test_signal_func() $$ test_signal_func() -5 -Warnings: -Warning 1012 This function SIGNAL a warning +1 drop function test_signal_func $$ create function test_signal_func() returns integer begin @@ -1468,7 +1456,6 @@ after RESIGNAL after RESIGNAL Warnings: Warning 1012 Raising a warning -Warning 1012 Raising a warning drop procedure test_resignal $$ create procedure test_resignal() begin @@ -1523,7 +1510,6 @@ after RESIGNAL after RESIGNAL Warnings: Warning 1264 Out of range value for column 'a' at row 1 -Warning 1264 Out of range value for column 'a' at row 1 drop procedure test_resignal $$ create procedure test_resignal() begin @@ -1557,7 +1543,7 @@ end $$ call test_resignal() $$ before RESIGNAL before RESIGNAL -ERROR 42S02: Unknown table 'no_such_table' +ERROR 42S02: Unknown table 'test.no_such_table' drop procedure test_resignal $$ create procedure test_resignal() begin @@ -1580,7 +1566,6 @@ before RESIGNAL after RESIGNAL after RESIGNAL Warnings: -Warning 1012 Raising a warning Warning 5555 RESIGNAL of a warning drop procedure test_resignal $$ create procedure test_resignal() @@ -1641,7 +1626,6 @@ before RESIGNAL after RESIGNAL after RESIGNAL Warnings: -Warning 1264 Out of range value for column 'a' at row 1 Warning 5555 RESIGNAL of a warning drop procedure test_resignal $$ create procedure test_resignal() @@ -2054,7 +2038,7 @@ before RESIGNAL after RESIGNAL after RESIGNAL Warnings: -Error 1051 Unknown table 'no_such_table' +Error 1051 Unknown table 'test.no_such_table' Warning 5555 RESIGNAL to a warning drop procedure test_resignal $$ create procedure test_resignal() @@ -2075,7 +2059,7 @@ before RESIGNAL ERROR 02444: RESIGNAL to a not found show warnings $$ Level Code Message -Error 1051 Unknown table 'no_such_table' +Error 1051 Unknown table 'test.no_such_table' Error 5555 RESIGNAL to a not found drop procedure test_resignal $$ create procedure test_resignal() @@ -2096,7 +2080,7 @@ before RESIGNAL ERROR 44444: RESIGNAL to an error show warnings $$ Level Code Message -Error 1051 Unknown table 'no_such_table' +Error 1051 Unknown table 'test.no_such_table' Error 5555 RESIGNAL to an error drop procedure test_resignal $$ # @@ -2143,9 +2127,6 @@ CALL peter_p2() $$ ERROR 42000: Hi, I am a useless error message show warnings $$ Level Code Message -Error 1231 Variable 'sql_mode' can't be set to the value of 'NULL' -Error 1231 Variable 'sql_mode' can't be set to the value of 'NULL' -Error 9999 Variable 'sql_mode' can't be set to the value of 'NULL' Error 9999 Hi, I am a useless error message drop procedure peter_p1 $$ drop procedure peter_p2 $$ diff --git a/mysql-test/r/signal_demo2.result b/mysql-test/r/signal_demo2.result index 223030b0624..5c8ac328a4a 100644 --- a/mysql-test/r/signal_demo2.result +++ b/mysql-test/r/signal_demo2.result @@ -74,7 +74,7 @@ In proc_bottom() In proc_bottom() Doing something that fail (simulate an error) ... Doing something that fail (simulate an error) ... -ERROR 42S02: Unknown table 'no_such_table' +ERROR 42S02: Unknown table 'demo.no_such_table' call proc_top_a(3); Starting ... Starting ... @@ -167,7 +167,7 @@ Doing something that fail (simulate an error) ... Doing something that fail (simulate an error) ... Doing cleanup ! Doing cleanup ! -ERROR 42S02: Unknown table 'no_such_table' +ERROR 42S02: Unknown table 'demo.no_such_table' call proc_top_b(3); Starting ... Starting ... diff --git a/mysql-test/r/signal_demo3.result b/mysql-test/r/signal_demo3.result index a89ce703d20..cc7042269bb 100644 --- a/mysql-test/r/signal_demo3.result +++ b/mysql-test/r/signal_demo3.result @@ -77,7 +77,7 @@ call proc_1(); ERROR 45000: Oops in proc_1 show warnings; Level Code Message -Error 1051 Unknown table 'oops_it_is_not_here' +Error 1051 Unknown table 'demo.oops_it_is_not_here' Error 1644 Oops in proc_9 Error 1644 Oops in proc_8 Error 1644 Oops in proc_7 @@ -95,11 +95,11 @@ call proc_1(); ERROR 45000: Oops in proc_1 show warnings; Level Code Message -Error 1051 Unknown table 'oops_it_is_not_here' -Error 1644 Oops in proc_9 -Error 1644 Oops in proc_8 -Error 1644 Oops in proc_7 -Error 1644 Oops in proc_6 +Error 1644 Oops in proc_5 +Error 1644 Oops in proc_4 +Error 1644 Oops in proc_3 +Error 1644 Oops in proc_2 +Error 1644 Oops in proc_1 SET @@session.max_error_count = 7; SELECT @@session.max_error_count; @@session.max_error_count @@ -108,13 +108,13 @@ call proc_1(); ERROR 45000: Oops in proc_1 show warnings; Level Code Message -Error 1051 Unknown table 'oops_it_is_not_here' -Error 1644 Oops in proc_9 -Error 1644 Oops in proc_8 Error 1644 Oops in proc_7 Error 1644 Oops in proc_6 Error 1644 Oops in proc_5 Error 1644 Oops in proc_4 +Error 1644 Oops in proc_3 +Error 1644 Oops in proc_2 +Error 1644 Oops in proc_1 SET @@session.max_error_count = 9; SELECT @@session.max_error_count; @@session.max_error_count @@ -123,7 +123,6 @@ call proc_1(); ERROR 45000: Oops in proc_1 show warnings; Level Code Message -Error 1051 Unknown table 'oops_it_is_not_here' Error 1644 Oops in proc_9 Error 1644 Oops in proc_8 Error 1644 Oops in proc_7 @@ -132,6 +131,7 @@ Error 1644 Oops in proc_5 Error 1644 Oops in proc_4 Error 1644 Oops in proc_3 Error 1644 Oops in proc_2 +Error 1644 Oops in proc_1 drop database demo; SET @@global.max_error_count = @start_global_value; SELECT @@global.max_error_count; diff --git a/mysql-test/r/sp-big.result b/mysql-test/r/sp-big.result index d28b7004330..9765508859c 100644 --- a/mysql-test/r/sp-big.result +++ b/mysql-test/r/sp-big.result @@ -46,8 +46,6 @@ end while; close cur1; end| call p1(); -Warnings: -Error 1329 No data - zero rows fetched, selected, or processed select count(*) from t1; count(*) 256 diff --git a/mysql-test/r/sp-bugs.result b/mysql-test/r/sp-bugs.result index 9d9deaebcc3..e34f8f9e63a 100644 --- a/mysql-test/r/sp-bugs.result +++ b/mysql-test/r/sp-bugs.result @@ -43,8 +43,6 @@ END| SELECT f2 (); f2 () NULL -Warnings: -Error 1305 FUNCTION testdb.f_not_exists does not exist DROP SCHEMA testdb; USE test; # @@ -134,6 +132,15 @@ DROP DATABASE testdb; USE test; End of 5.1 tests # +# BUG#13489996 valgrind:conditional jump or move depends on +# uninitialised values-field_blob +# +CREATE FUNCTION sf() RETURNS BLOB RETURN ""; +SELECT sf(); +sf() + +DROP FUNCTION sf; +# # Bug#11763507 - 56224: FUNCTION NAME IS CASE-SENSITIVE # SET @@SQL_MODE = ''; diff --git a/mysql-test/r/sp-code.result b/mysql-test/r/sp-code.result index 3bead4fc826..c9d2f7b023a 100644 --- a/mysql-test/r/sp-code.result +++ b/mysql-test/r/sp-code.result @@ -711,8 +711,6 @@ looping i looping 1 looping i looping 0 -Warnings: -Error 1062 Duplicate entry '1' for key 'a' call proc_26977_works(2); do something do something @@ -732,8 +730,6 @@ looping i looping 0 optimizer: keep hreturn optimizer: keep hreturn -Warnings: -Error 1062 Duplicate entry '2' for key 'a' drop table t1; drop procedure proc_26977_broken; drop procedure proc_26977_works; diff --git a/mysql-test/r/sp-destruct.result b/mysql-test/r/sp-destruct.result index 6d85c3ce496..172e40cb40c 100644 --- a/mysql-test/r/sp-destruct.result +++ b/mysql-test/r/sp-destruct.result @@ -149,7 +149,7 @@ alter table mysql.proc drop column type; # The below statement should not cause assertion failure. drop database mysqltest; Warnings: -Error 1728 Column count of mysql.proc is wrong. Expected 20, found 19. The table is probably corrupted +Error 1805 Column count of mysql.proc is wrong. Expected 20, found 19. The table is probably corrupted # Restore mysql.proc. drop table mysql.proc; # @@ -166,7 +166,7 @@ CREATE PROCEDURE db1.p1() SET @foo = 10; ALTER TABLE mysql.proc MODIFY comment CHAR (32); DROP DATABASE db1; Warnings: -Error 1729 Cannot load from mysql.proc. The table is probably corrupted +Error 1728 Cannot load from mysql.proc. The table is probably corrupted # Restore mysql.proc DROP TABLE mysql.proc; RENAME TABLE proc_backup TO mysql.proc; diff --git a/mysql-test/r/sp-dynamic.result b/mysql-test/r/sp-dynamic.result index cdfeb8ab020..7309ba4c765 100644 --- a/mysql-test/r/sp-dynamic.result +++ b/mysql-test/r/sp-dynamic.result @@ -249,7 +249,7 @@ drop procedure p1| drop table if exists t1| drop table if exists t2| Warnings: -Note 1051 Unknown table 't2' +Note 1051 Unknown table 'test.t2' create table t1 (id integer primary key auto_increment, stmt_text char(35), status varchar(20))| insert into t1 (stmt_text) values diff --git a/mysql-test/r/sp-error.result b/mysql-test/r/sp-error.result index 24cbb945fd2..71fab8c9654 100644 --- a/mysql-test/r/sp-error.result +++ b/mysql-test/r/sp-error.result @@ -466,7 +466,7 @@ drop table t5; end| create table t5 (x int)| call bug3294()| -ERROR 42S02: Unknown table 't5' +ERROR 42S02: Unknown table 'test.t5' drop procedure bug3294| drop procedure if exists bug8776_1| drop procedure if exists bug8776_2| @@ -1344,8 +1344,6 @@ set @in_func := 0; select func_20713_a(); func_20713_a() NULL -Warnings: -Error 1146 Table 'test.bogus_table_20713' doesn't exist select @in_func; @in_func 2 @@ -1353,8 +1351,6 @@ set @in_func := 0; select func_20713_b(); func_20713_b() NULL -Warnings: -Error 1146 Table 'test.bogus_table_20713' doesn't exist select @in_func; @in_func 2 @@ -1567,7 +1563,7 @@ f2() 1 drop function f2; drop table t2; -ERROR 42S02: Unknown table 't2' +ERROR 42S02: Unknown table 'test.t2' End of 5.1 tests drop procedure if exists proc_33983_a; drop procedure if exists proc_33983_b; @@ -1821,11 +1817,8 @@ CAST('10 ' as unsigned integer) c 3 @@warning_count -1 +0 Level Code Message -Warning 1292 Truncated incorrect INTEGER value: '10 ' -Warnings: -Warning 1292 Truncated incorrect INTEGER value: '10 ' CALL p6(); CAST('10 ' as unsigned integer) 10 @@ -1833,8 +1826,6 @@ Level Code Message Warning 1292 Truncated incorrect INTEGER value: '10 ' c 1 -Warnings: -Warning 1292 Truncated incorrect INTEGER value: '10 ' DROP PROCEDURE p1; DROP PROCEDURE p2; DROP PROCEDURE p3; @@ -1885,9 +1876,6 @@ END| CALL p1(); exception exception -Warnings: -Warning 1292 Truncated incorrect INTEGER value: '10 ' -Error 1048 Column 'b' cannot be null DROP TABLE t1; DROP PROCEDURE p1; # @@ -1931,11 +1919,8 @@ CALL p1(); NULL warning caught (expected) warning caught (expected) -Warnings: -Warning 1365 Division by 0 SHOW WARNINGS; Level Code Message -Warning 1365 Division by 0 CALL p2(); 5 / 0 NULL @@ -2008,3 +1993,878 @@ Error 1048 Column 'c' cannot be null DROP TABLE t1; DROP TABLE t2; DROP PROCEDURE p1; + +################################################################### +# Tests for the following bugs: +# - Bug#11763171: 55852 - Possibly inappropriate handler activation. +# - Bug#11749343: 38806 - Wrong scope for SQL HANDLERS in SP. +################################################################### + + +# -- Check that SQL-conditions thrown by Statement-blocks are +# -- handled by Handler-decl blocks properly. + +CREATE PROCEDURE p1() +BEGIN +DECLARE CONTINUE HANDLER FOR SQLEXCEPTION +SELECT 'H1' AS HandlerId; +DECLARE CONTINUE HANDLER FOR SQLWARNING +SELECT 'H2' AS HandlerId; +SIGNAL SQLSTATE '01000'; # Should be handled by H2. +END| + +CALL p1()| +HandlerId +H2 + +# -- Check that SQL-conditions thrown by Statement-blocks are +# -- handled by Handler-decl blocks properly in case of nested +# -- SQL-blocks. + +CREATE PROCEDURE p2() +BEGIN +DECLARE CONTINUE HANDLER FOR SQLEXCEPTION +SELECT 'H1' AS HandlerId; +DECLARE CONTINUE HANDLER FOR SQLWARNING +SELECT 'H2' AS HandlerId; +BEGIN +SELECT 'B1' AS BlockId; +BEGIN +SELECT 'B2' AS BlockId; +BEGIN +SELECT 'B3' AS BlockId; +SIGNAL SQLSTATE '01000'; # Should be handled by H2. +END; +END; +END; +END| + +CALL p2()| +BlockId +B1 +BlockId +B2 +BlockId +B3 +HandlerId +H2 + +# -- Check SQL-handler resolution rules. + +CREATE PROCEDURE p3() +BEGIN +DECLARE CONTINUE HANDLER FOR SQLEXCEPTION +SELECT 'H1' AS HandlerId; +DECLARE CONTINUE HANDLER FOR SQLWARNING +SELECT 'H2' AS HandlerId; +DECLARE CONTINUE HANDLER FOR SQLSTATE '01000' + SELECT 'H3' AS HandlerId; +SIGNAL SQLSTATE '01000'; # Should be handled by H3. +END| + +CALL p3()| +HandlerId +H3 + +CREATE PROCEDURE p4() +BEGIN +DECLARE CONTINUE HANDLER FOR SQLEXCEPTION +SELECT 'H1' AS HandlerId; +DECLARE CONTINUE HANDLER FOR SQLSTATE '01000' + SELECT 'H2' AS HandlerId; +DECLARE CONTINUE HANDLER FOR SQLWARNING +SELECT 'H3' AS HandlerId; +SIGNAL SQLSTATE '01000'; # Should be handled by H2. +END| + +CALL p4()| +HandlerId +H2 + +CREATE PROCEDURE p5() +BEGIN +DECLARE CONTINUE HANDLER FOR SQLEXCEPTION +SELECT 'H1' AS HandlerId; +DECLARE CONTINUE HANDLER FOR SQLSTATE '01000' + SELECT 'H2' AS HandlerId; +BEGIN +DECLARE CONTINUE HANDLER FOR SQLWARNING +SELECT 'H3' AS HandlerId; +SIGNAL SQLSTATE '01000'; # Should be handled by H3. +END; +END| + +CALL p5()| +HandlerId +H3 + +# -- Check that handlers don't handle its own exceptions. + +CREATE PROCEDURE p6() +BEGIN +DECLARE CONTINUE HANDLER FOR SQLEXCEPTION +BEGIN +SELECT 'H1' AS HandlerId; +SIGNAL SQLSTATE 'HY000'; # Should *not* be handled by H1. +END; +SELECT 'S1' AS SignalId; +SIGNAL SQLSTATE 'HY000'; # Should be handled by H1. +END| + +CALL p6()| +SignalId +S1 +HandlerId +H1 +ERROR HY000: Unhandled user-defined exception condition + +# -- Check that handlers don't handle its own warnings. + +CREATE PROCEDURE p7() +BEGIN +DECLARE CONTINUE HANDLER FOR SQLWARNING +BEGIN +SELECT 'H1' AS HandlerId; +SIGNAL SQLSTATE '01000'; # Should *not* be handled by H1. +END; +SELECT 'S1' AS SignalId; +SIGNAL SQLSTATE '01000'; # Should be handled by H1. +END| + +CALL p7()| +SignalId +S1 +HandlerId +H1 +Warnings: +Warning 1642 Unhandled user-defined warning condition + +# -- Check that conditions for handlers are not handled by the handlers +# -- from the same block. + +CREATE PROCEDURE p8() +BEGIN +DECLARE CONTINUE HANDLER FOR SQLWARNING +SELECT 'H1' AS HandlerId; +DECLARE CONTINUE HANDLER FOR SQLEXCEPTION +BEGIN +SELECT 'H2' AS HandlerId; +SIGNAL SQLSTATE '01000'; # Should *not* be handled by H1. +END; +SELECT 'S1' AS SignalId; +SIGNAL SQLSTATE 'HY000'; # Should be handled by H2. +END| + +CALL p8()| +SignalId +S1 +HandlerId +H2 +Warnings: +Warning 1642 Unhandled user-defined warning condition + +# -- Check that conditions for handlers are not handled by the handlers +# -- from the same block even if they are thrown deep down the stack. + +CREATE PROCEDURE p9() +BEGIN +DECLARE CONTINUE HANDLER FOR SQLSTATE '01000' + SELECT 'Wrong:H1:1' AS HandlerId; +DECLARE CONTINUE HANDLER FOR SQLWARNING +SELECT 'Wrong:H1:2' AS HandlerId; +DECLARE CONTINUE HANDLER FOR SQLEXCEPTION +BEGIN +DECLARE CONTINUE HANDLER FOR SQLSTATE '01000' + SELECT 'Wrong:H2:1' AS HandlerId; +DECLARE CONTINUE HANDLER FOR SQLWARNING +SELECT 'Wrong:H2:2' AS HandlerId; +DECLARE CONTINUE HANDLER FOR SQLEXCEPTION +BEGIN +DECLARE CONTINUE HANDLER FOR SQLSTATE '01000' + SELECT 'Wrong:H3:1' AS HandlerId; +DECLARE CONTINUE HANDLER FOR SQLWARNING +SELECT 'Wrong:H3:2' AS HandlerId; +DECLARE CONTINUE HANDLER FOR SQLEXCEPTION +BEGIN +DECLARE CONTINUE HANDLER FOR SQLSTATE '01000' + SELECT 'Wrong:H4:1' AS HandlerId; +DECLARE CONTINUE HANDLER FOR SQLWARNING +SELECT 'Wrong:H4:2' AS HandlerId; +DECLARE CONTINUE HANDLER FOR SQLEXCEPTION +BEGIN +DECLARE CONTINUE HANDLER FOR SQLSTATE '01000' + SELECT 'Wrong:H5:1' AS HandlerId; +DECLARE CONTINUE HANDLER FOR SQLWARNING +SELECT 'Wrong:H5:2' AS HandlerId; +DECLARE CONTINUE HANDLER FOR SQLEXCEPTION +BEGIN +DECLARE CONTINUE HANDLER FOR SQLSTATE '01000' + SELECT 'Wrong:H6:1' AS HandlerId; +DECLARE CONTINUE HANDLER FOR SQLWARNING +SELECT 'Wrong:H6:2' AS HandlerId; +DECLARE CONTINUE HANDLER FOR SQLEXCEPTION +BEGIN +SELECT 'H2' AS HandlerId; +SIGNAL SQLSTATE '01000'; # Should *not* be handled by H1. +END; +SELECT 'S6' AS SignalId; +SIGNAL SQLSTATE 'HY000'; +END; +SELECT 'S5' AS SignalId; +SIGNAL SQLSTATE 'HY000'; +END; +SELECT 'S4' AS SignalId; +SIGNAL SQLSTATE 'HY000'; +END; +SELECT 'S3' AS SignalId; +SIGNAL SQLSTATE 'HY000'; +END; +SELECT 'S2' AS SignalId; +SIGNAL SQLSTATE 'HY000'; +END; +SELECT 'S1' AS SignalId; +SIGNAL SQLSTATE 'HY000'; # Should be handled by H2. +END| + +CALL p9()| +SignalId +S1 +SignalId +S2 +SignalId +S3 +SignalId +S4 +SignalId +S5 +SignalId +S6 +HandlerId +H2 +Warnings: +Warning 1642 Unhandled user-defined warning condition + +# -- Check that handlers are choosen properly in case of deep stack and +# -- nested SQL-blocks. + +CREATE PROCEDURE p10() +BEGIN +DECLARE CONTINUE HANDLER FOR SQLSTATE '01000' + SELECT 'H1' AS HandlerId; +DECLARE CONTINUE HANDLER FOR SQLWARNING +SELECT 'H2' AS HandlerId; +BEGIN +BEGIN +BEGIN +DECLARE CONTINUE HANDLER FOR SQLSTATE '01000' + SELECT 'Wrong:H1:1' AS HandlerId; +DECLARE CONTINUE HANDLER FOR SQLWARNING +SELECT 'Wrong:H1:2' AS HandlerId; +DECLARE CONTINUE HANDLER FOR SQLEXCEPTION +BEGIN +DECLARE CONTINUE HANDLER FOR SQLSTATE '01000' + SELECT 'Wrong:H2:1' AS HandlerId; +DECLARE CONTINUE HANDLER FOR SQLWARNING +SELECT 'Wrong:H2:2' AS HandlerId; +DECLARE CONTINUE HANDLER FOR SQLEXCEPTION +BEGIN +DECLARE CONTINUE HANDLER FOR SQLSTATE '01000' + SELECT 'Wrong:H3:1' AS HandlerId; +DECLARE CONTINUE HANDLER FOR SQLWARNING +SELECT 'Wrong:H3:2' AS HandlerId; +DECLARE CONTINUE HANDLER FOR SQLEXCEPTION +BEGIN +DECLARE CONTINUE HANDLER FOR SQLSTATE '01000' + SELECT 'Wrong:H4:1' AS HandlerId; +DECLARE CONTINUE HANDLER FOR SQLWARNING +SELECT 'Wrong:H4:2' AS HandlerId; +DECLARE CONTINUE HANDLER FOR SQLEXCEPTION +BEGIN +DECLARE CONTINUE HANDLER FOR SQLSTATE '01000' + SELECT 'Wrong:H5:1' AS HandlerId; +DECLARE CONTINUE HANDLER FOR SQLWARNING +SELECT 'Wrong:H5:2' AS HandlerId; +DECLARE CONTINUE HANDLER FOR SQLEXCEPTION +BEGIN +DECLARE CONTINUE HANDLER FOR SQLSTATE '01000' + SELECT 'Wrong:H6:1' AS HandlerId; +DECLARE CONTINUE HANDLER FOR SQLWARNING +SELECT 'Wrong:H6:2' AS HandlerId; +DECLARE CONTINUE HANDLER FOR SQLEXCEPTION +BEGIN +SELECT 'H2' AS HandlerId; +SIGNAL SQLSTATE '01000'; # Should be handled by H1. +END; +SELECT 'S6' AS SignalId; +SIGNAL SQLSTATE 'HY000'; +END; +SELECT 'S5' AS SignalId; +SIGNAL SQLSTATE 'HY000'; +END; +SELECT 'S4' AS SignalId; +SIGNAL SQLSTATE 'HY000'; +END; +SELECT 'S3' AS SignalId; +SIGNAL SQLSTATE 'HY000'; +END; +SELECT 'S2' AS SignalId; +SIGNAL SQLSTATE 'HY000'; +END; +SELECT 'S1' AS SignalId; +SIGNAL SQLSTATE 'HY000'; # Should be handled by H2. +END; +END; +END; +END| + +CALL p10()| +SignalId +S1 +SignalId +S2 +SignalId +S3 +SignalId +S4 +SignalId +S5 +SignalId +S6 +HandlerId +H2 +HandlerId +H1 + +# -- Test stored procedure from Peter's mail. + +CREATE PROCEDURE p11() +BEGIN +DECLARE CONTINUE HANDLER FOR SQLEXCEPTION +SELECT 'H1' AS HandlerId; +DECLARE CONTINUE HANDLER FOR SQLWARNING +SELECT 'H2' AS HandlerId; +BEGIN +DECLARE CONTINUE HANDLER FOR SQLSTATE '01000', 1249 +BEGIN +DECLARE CONTINUE HANDLER FOR SQLEXCEPTION +SELECT 'H3' AS HandlerId; +DECLARE CONTINUE HANDLER FOR SQLWARNING +SELECT 'H4' AS HandlerId; +BEGIN +SELECT 'H5' AS HandlerId; +SELECT 'S3' AS SignalId; +SIGNAL SQLSTATE 'HY000'; # H3 +SELECT 'S4' AS SignalId; +SIGNAL SQLSTATE '22003'; # H3 +SELECT 'S5' AS SignalId; +SIGNAL SQLSTATE '01000' SET MYSQL_ERRNO = 1249; # H4 +END; +END; +SELECT 'S6' AS SignalId; +SIGNAL SQLSTATE 'HY000'; # H1 +SELECT 'S7' AS SignalId; +SIGNAL SQLSTATE '22003'; # H1 +SELECT 'S8' AS SignalId; +SIGNAL SQLSTATE '01000' SET MYSQL_ERRNO = 1249; # H5 +END; +SELECT 'S1' AS SignalId; +SIGNAL SQLSTATE 'HY000'; # H1 +SELECT 'S2' AS SignalId; +SIGNAL SQLSTATE '01000' SET MYSQL_ERRNO = 1249; # H2 +END| + +CALL p11()| +SignalId +S6 +HandlerId +H1 +SignalId +S7 +HandlerId +H1 +SignalId +S8 +HandlerId +H5 +SignalId +S3 +HandlerId +H3 +SignalId +S4 +HandlerId +H3 +SignalId +S5 +HandlerId +H4 +SignalId +S1 +HandlerId +H1 +SignalId +S2 +HandlerId +H2 + +# -- Check that runtime stack-trace can be deeper than parsing-time one. + +CREATE PROCEDURE p12() +BEGIN +DECLARE CONTINUE HANDLER FOR SQLSTATE '01001' + BEGIN +DECLARE CONTINUE HANDLER FOR SQLSTATE '01001' + BEGIN +DECLARE CONTINUE HANDLER FOR SQLSTATE '01001' + BEGIN +DECLARE CONTINUE HANDLER FOR SQLSTATE '01001' + BEGIN +DECLARE CONTINUE HANDLER FOR SQLSTATE '01001' + BEGIN +SELECT 'H1:5' AS HandlerId; +SIGNAL SQLSTATE '01002'; +END; +SELECT 'H1:4' AS HandlerId; +SIGNAL SQLSTATE '01001'; +END; +SELECT 'H1:3' AS HandlerId; +SIGNAL SQLSTATE '01001'; +END; +SELECT 'H1:2' AS HandlerId; +SIGNAL SQLSTATE '01001'; +END; +SELECT 'H1:1' AS HandlerId; +SIGNAL SQLSTATE '01001'; +END; +######################################################### +DECLARE CONTINUE HANDLER FOR SQLSTATE '01002' + SELECT 'OK' AS Msg; +######################################################### +BEGIN +DECLARE CONTINUE HANDLER FOR SQLWARNING +BEGIN +DECLARE CONTINUE HANDLER FOR SQLWARNING +BEGIN +DECLARE CONTINUE HANDLER FOR SQLWARNING +BEGIN +DECLARE CONTINUE HANDLER FOR SQLWARNING +BEGIN +DECLARE CONTINUE HANDLER FOR SQLWARNING +BEGIN +SELECT 'H2:5' AS HandlerId; +SIGNAL SQLSTATE '01001'; +END; +SELECT 'H2:4' AS HandlerId; +SIGNAL SQLSTATE '01000'; +END; +SELECT 'H2:3' AS HandlerId; +SIGNAL SQLSTATE '01000'; +END; +SELECT 'H2:2' AS HandlerId; +SIGNAL SQLSTATE '01000'; +END; +SELECT 'H2:1' AS HandlerId; +SIGNAL SQLSTATE '01000'; +END; +####################################################### +SELECT 'Throw 01000' AS Msg; +SIGNAL SQLSTATE '01000'; +END; +END| + +CALL p12()| +Msg +Throw 01000 +HandlerId +H2:1 +HandlerId +H2:2 +HandlerId +H2:3 +HandlerId +H2:4 +HandlerId +H2:5 +HandlerId +H1:1 +HandlerId +H1:2 +HandlerId +H1:3 +HandlerId +H1:4 +HandlerId +H1:5 +Warnings: +Warning 1642 Unhandled user-defined warning condition + +# -- Check that handler-call-frames are removed properly for EXIT +# -- handlers. + +CREATE PROCEDURE p13() +BEGIN +DECLARE CONTINUE HANDLER FOR SQLWARNING +BEGIN +DECLARE CONTINUE HANDLER FOR SQLWARNING +BEGIN +DECLARE EXIT HANDLER FOR SQLWARNING +BEGIN +SELECT 'EXIT handler 3' AS Msg; +END; +SELECT 'CONTINUE handler 2: 1' AS Msg; +SIGNAL SQLSTATE '01000'; +SELECT 'CONTINUE handler 2: 2' AS Msg; +END; +SELECT 'CONTINUE handler 1: 1' AS Msg; +SIGNAL SQLSTATE '01000'; +SELECT 'CONTINUE handler 1: 2' AS Msg; +END; +SELECT 'Throw 01000' AS Msg; +SIGNAL SQLSTATE '01000'; +END| + +CALL p13()| +Msg +Throw 01000 +Msg +CONTINUE handler 1: 1 +Msg +CONTINUE handler 2: 1 +Msg +EXIT handler 3 +Msg +CONTINUE handler 1: 2 + +# That's it. Cleanup. + +DROP PROCEDURE p1; +DROP PROCEDURE p2; +DROP PROCEDURE p3; +DROP PROCEDURE p4; +DROP PROCEDURE p5; +DROP PROCEDURE p6; +DROP PROCEDURE p7; +DROP PROCEDURE p8; +DROP PROCEDURE p9; +DROP PROCEDURE p10; +DROP PROCEDURE p11; +DROP PROCEDURE p12; +DROP PROCEDURE p13; + +# Bug#12731619: NESTED SP HANDLERS CAN TRIGGER ASSERTION + +DROP FUNCTION IF EXISTS f1; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1(msg VARCHAR(255)); +CREATE FUNCTION f1() RETURNS INT +BEGIN +DECLARE CONTINUE HANDLER FOR SQLEXCEPTION # handler 1 +BEGIN +DECLARE CONTINUE HANDLER FOR SQLEXCEPTION # handler 2 +BEGIN +INSERT INTO t1 VALUE('WRONG: Inside H2'); +RETURN 2; +END; +INSERT INTO t1 VALUE('CORRECT: Inside H1'); +RETURN 1; +END; +BEGIN +DECLARE CONTINUE HANDLER FOR SQLWARNING # handler 3 +BEGIN +INSERT INTO t1 VALUE('WRONG: Inside H3'); +RETURN 3; +END; +INSERT INTO t1 VALUE('CORRECT: Calling f1()'); +RETURN f1(); # -- exception here +END; +INSERT INTO t1 VALUE('WRONG: Returning 10'); +RETURN 10; +END| + +SELECT f1(); +f1() +1 + +SELECT * FROM t1; +msg +CORRECT: Calling f1() +CORRECT: Inside H1 + +DROP FUNCTION f1; +DROP TABLE t1; + +# Check that handled SQL-conditions are properly cleared from DA. + +DROP TABLE IF EXISTS t1; +DROP TABLE IF EXISTS t2; +DROP PROCEDURE IF EXISTS p1; +DROP PROCEDURE IF EXISTS p2; +DROP PROCEDURE IF EXISTS p3; +DROP PROCEDURE IF EXISTS p4; +DROP PROCEDURE IF EXISTS p5; +CREATE TABLE t1(a CHAR, b CHAR, c CHAR); +CREATE TABLE t2(a SMALLINT, b SMALLINT, c SMALLINT); + +# Check that SQL-conditions for which SQL-handler has been invoked, +# are cleared from the Diagnostics Area. Note, there might be several +# SQL-conditions, but SQL-handler must be invoked only once. + +CREATE PROCEDURE p1() +BEGIN +DECLARE EXIT HANDLER FOR SQLWARNING +SELECT 'Warning caught' AS msg; +# The INSERT below raises 3 SQL-conditions (warnings). The EXIT HANDLER +# above must be invoked once (for one condition), but all three conditions +# must be cleared from the Diagnostics Area. +INSERT INTO t1 VALUES('qqqq', 'ww', 'eee'); +# The following INSERT will not be executed, because of the EXIT HANDLER. +INSERT INTO t1 VALUES('zzz', 'xx', 'yyyy'); +END| + +CALL p1()| +msg +Warning caught + +SELECT * FROM t1| +a b c +q w e + +# Check that SQL-conditions for which SQL-handler has *not* been +# invoked, are *still* cleared from the Diagnostics Area. + +CREATE PROCEDURE p2() +BEGIN +DECLARE CONTINUE HANDLER FOR 1292 +SELECT 'Warning 1292 caught' AS msg; +# The following INSERT raises 6 SQL-warnings with code 1292, +# and 3 SQL-warnings with code 1264. The CONTINUE HANDLER above must be +# invoked once, and all nine SQL-warnings must be cleared from +# the Diagnostics Area. +INSERT INTO t2 +SELECT +CAST(CONCAT(CAST('1 ' AS UNSIGNED INTEGER), '999999 ') AS SIGNED INTEGER), +CAST(CONCAT(CAST('2 ' AS UNSIGNED INTEGER), '999999 ') AS SIGNED INTEGER), +CAST(CONCAT(CAST('3 ' AS UNSIGNED INTEGER), '999999 ') AS SIGNED INTEGER); +END| + +CALL p2()| +msg +Warning 1292 caught + +# Check that if there are two equally ranked SQL-handlers to handle +# SQL-conditions from SQL-statement, only one of them will be invoked. + +CREATE PROCEDURE p3() +BEGIN +DECLARE CONTINUE HANDLER FOR 1292 +SELECT 'Warning 1292 caught' AS msg; +DECLARE CONTINUE HANDLER FOR 1264 +SELECT 'Warning 1264 caught' AS msg; +# The following INSERT raises 6 SQL-warnings with code 1292, +# and 3 SQL-warnings with code 1264. Only one of the CONTINUE HANDLERs above +# must be called, and only once. The SQL Standard does not define, which one +# should be invoked. +INSERT INTO t2 +SELECT +CAST(CONCAT(CAST('1 ' AS UNSIGNED INTEGER), '999999 ') AS SIGNED INTEGER), +CAST(CONCAT(CAST('2 ' AS UNSIGNED INTEGER), '999999 ') AS SIGNED INTEGER), +CAST(CONCAT(CAST('3 ' AS UNSIGNED INTEGER), '999999 ') AS SIGNED INTEGER); +END| + +CALL p3()| +msg +Warning 1264 caught + +# The same as p3, but 1264 comes first. + +CREATE PROCEDURE p4() +BEGIN +DECLARE CONTINUE HANDLER FOR 1292 +SELECT 'Warning 1292 caught' AS msg; +DECLARE CONTINUE HANDLER FOR 1264 +SELECT 'Warning 1264 caught' AS msg; +# The following INSERT raises 4 SQL-warnings with code 1292, +# and 3 SQL-warnings with code 1264. Only one of the CONTINUE HANDLERs above +# must be called, and only once. The SQL Standard does not define, which one +# should be invoked. +INSERT INTO t2 +SELECT +CAST(999999 AS SIGNED INTEGER), +CAST(CONCAT(CAST('2 ' AS UNSIGNED INTEGER), '999999 ') AS SIGNED INTEGER), +CAST(CONCAT(CAST('3 ' AS UNSIGNED INTEGER), '999999 ') AS SIGNED INTEGER); +END| + +CALL p4()| +msg +Warning 1264 caught + +# Check that if a SQL-handler raised its own SQL-conditions, there are +# preserved after handler exit. + +CREATE PROCEDURE p5() +BEGIN +DECLARE EXIT HANDLER FOR 1292 +BEGIN +SELECT 'Handler for 1292 (1)' AS Msg; +SIGNAL SQLSTATE '01000' SET MYSQL_ERRNO = 1234; +SHOW WARNINGS; +SELECT 'Handler for 1292 (2)' AS Msg; +END; +INSERT INTO t2 +SELECT +CAST(999999 AS SIGNED INTEGER), +CAST(CONCAT(CAST('2 ' AS UNSIGNED INTEGER), '999999 ') AS SIGNED INTEGER), +CAST(CONCAT(CAST('3 ' AS UNSIGNED INTEGER), '999999 ') AS SIGNED INTEGER); +END| + +CALL p5()| +Msg +Handler for 1292 (1) +Level Code Message +Warning 1234 Unhandled user-defined warning condition +Msg +Handler for 1292 (2) +Warnings: +Warning 1234 Unhandled user-defined warning condition + +# Check that SQL-conditions are available inside the handler, but +# cleared after the handler exits. + +CREATE PROCEDURE p6() +BEGIN +DECLARE CONTINUE HANDLER FOR 1292 +BEGIN +SHOW WARNINGS; +SELECT 'Handler for 1292' Msg; +END; +INSERT INTO t2 +SELECT +CAST(CONCAT(CAST('1 ' AS UNSIGNED INTEGER), '999999 ') AS SIGNED INTEGER), +CAST(CONCAT(CAST('2 ' AS UNSIGNED INTEGER), '999999 ') AS SIGNED INTEGER), +CAST(CONCAT(CAST('3 ' AS UNSIGNED INTEGER), '999999 ') AS SIGNED INTEGER); +END| + +CALL p6()| +Level Code Message +Warning 1292 Truncated incorrect INTEGER value: '1 ' +Warning 1292 Truncated incorrect INTEGER value: '1999999 ' +Warning 1264 Out of range value for column 'a' at row 1 +Warning 1292 Truncated incorrect INTEGER value: '2 ' +Warning 1292 Truncated incorrect INTEGER value: '2999999 ' +Warning 1264 Out of range value for column 'b' at row 1 +Warning 1292 Truncated incorrect INTEGER value: '3 ' +Warning 1292 Truncated incorrect INTEGER value: '3999999 ' +Warning 1264 Out of range value for column 'c' at row 1 +Msg +Handler for 1292 + +DROP PROCEDURE p1; +DROP PROCEDURE p2; +DROP PROCEDURE p3; +DROP PROCEDURE p4; +DROP PROCEDURE p5; +DROP PROCEDURE p6; +DROP TABLE t1; +DROP TABLE t2; + +# Bug#13059316: ASSERTION FAILURE IN SP_RCONTEXT.CC +# Check DECLARE statements that raise conditions before handlers +# are declared. + +DROP PROCEDURE IF EXISTS p1; +DROP PROCEDURE IF EXISTS p2; +CREATE PROCEDURE p1() +BEGIN +DECLARE var1 INTEGER DEFAULT 'string'; +DECLARE EXIT HANDLER FOR SQLWARNING SELECT 'H1'; +END| + +CALL p1()| +Warnings: +Warning 1366 Incorrect integer value: 'string' for column 'var1' at row 1 + +CREATE PROCEDURE p2() +BEGIN +DECLARE EXIT HANDLER FOR SQLWARNING SELECT 'H2'; +CALL p1(); +END| + +CALL p2()| +H2 +H2 + +DROP PROCEDURE p1; +DROP PROCEDURE p2; +# +# Bug#13113222 RQG_SIGNAL_RESIGNAL FAILED WITH ASSERTION. +# +DROP PROCEDURE IF EXISTS p1; +DROP PROCEDURE IF EXISTS p2; +CREATE PROCEDURE p1() +BEGIN +DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SELECT 'triggered p1'; +# This will trigger an error. +SIGNAL SQLSTATE 'HY000'; +END| +CREATE PROCEDURE p2() +BEGIN +DECLARE CONTINUE HANDLER FOR SQLWARNING SELECT 'triggered p2'; +# This will trigger a warning. +SIGNAL SQLSTATE '01000'; +END| +SET @old_max_error_count= @@session.max_error_count; +SET SESSION max_error_count= 0; +CALL p1(); +triggered p1 +triggered p1 +CALL p2(); +SET SESSION max_error_count= @old_max_error_count; +DROP PROCEDURE p1; +DROP PROCEDURE p2; + +# Bug#12652873: 61392: Continue handler for NOT FOUND being triggered +# from internal stored function. + +DROP FUNCTION IF EXISTS f1; +DROP FUNCTION IF EXISTS f2; +DROP TABLE IF EXISTS t1; + +CREATE TABLE t1 (a INT, b INT); +INSERT INTO t1 VALUES (1, 2); + +# f1() raises NOT_FOUND condition. +# Raising NOT_FOUND can not be simulated by SIGNAL, +# because SIGNAL would raise SQL-error in that case. + +CREATE FUNCTION f1() RETURNS INTEGER +BEGIN +DECLARE v VARCHAR(5) DEFAULT -1; +SELECT b FROM t1 WHERE a = 2 INTO v; +RETURN v; +END| + +# Here we check that the NOT_FOUND condition raised in f1() +# is not visible in the outer function (f2), i.e. the continue +# handler in f2() will not be called. + +CREATE FUNCTION f2() RETURNS INTEGER +BEGIN +DECLARE v INTEGER; +DECLARE CONTINUE HANDLER FOR NOT FOUND +SET @msg = 'Handler activated.'; +SELECT f1() INTO v; +RETURN v; +END| +SET @msg = ''; + +SELECT f2(); +f2() +-1 + +SELECT @msg; +@msg + + +DROP FUNCTION f1; +DROP FUNCTION f2; +DROP TABLE t1; diff --git a/mysql-test/r/sp-prelocking.result b/mysql-test/r/sp-prelocking.result index 186b2c05d34..9e82a966268 100644 --- a/mysql-test/r/sp-prelocking.result +++ b/mysql-test/r/sp-prelocking.result @@ -22,7 +22,7 @@ call sp1(); my-col 1 Warnings: -Note 1051 Unknown table 't1' +Note 1051 Unknown table 'mysqltest.t1' select database(); database() mysqltest @@ -34,7 +34,7 @@ call mysqltest.sp1(); my-col 1 Warnings: -Note 1051 Unknown table 't1' +Note 1051 Unknown table 'mysqltest.t1' select database(); database() test diff --git a/mysql-test/r/sp-vars.result b/mysql-test/r/sp-vars.result index a465a29ee4f..0a7ea0d68c5 100644 --- a/mysql-test/r/sp-vars.result +++ b/mysql-test/r/sp-vars.result @@ -400,7 +400,7 @@ Warnings: Note 1305 PROCEDURE test.p2 does not exist DROP TABLE IF EXISTS t1; Warnings: -Note 1051 Unknown table 't1' +Note 1051 Unknown table 'test.t1' CREATE TABLE t1(log_msg VARCHAR(1024)); CREATE PROCEDURE p1(arg VARCHAR(255)) BEGIN diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result index 6e6f05667ed..75a6c6278be 100644 --- a/mysql-test/r/sp.result +++ b/mysql-test/r/sp.result @@ -737,8 +737,6 @@ close c; end| insert into t2 values ("foo", 42, -1.9), ("bar", 3, 12.1), ("zap", 666, -3.14)| call cur1()| -Warnings: -Error 1329 No data - zero rows fetched, selected, or processed select * from t1| id data foo 40 @@ -774,8 +772,6 @@ close c1; close c2; end| call cur2()| -Warnings: -Error 1329 No data - zero rows fetched, selected, or processed select * from t3 order by i,s| s i bar 3 @@ -865,8 +861,6 @@ end$ set @@sql_mode = ''| set sql_select_limit = 1| call modes(@c1, @c2)| -Warnings: -Error 1329 No data - zero rows fetched, selected, or processed set sql_select_limit = default| select @c1, @c2| @c1 @c2 @@ -1688,64 +1682,42 @@ end| call h_ee()| h_ee Inner (good) -Warnings: -Error 1062 Duplicate entry '1' for key 'PRIMARY' call h_es()| h_es -Outer (good) -Warnings: -Error 1062 Duplicate entry '1' for key 'PRIMARY' +Inner (bad) call h_en()| h_en -Outer (good) -Warnings: -Warning 1329 No data - zero rows fetched, selected, or processed +Inner (bad) call h_ew()| h_ew -Outer (good) +Inner (bad) call h_ex()| h_ex -Outer (good) -Warnings: -Error 1062 Duplicate entry '1' for key 'PRIMARY' +Inner (bad) call h_se()| h_se Inner (good) -Warnings: -Error 1062 Duplicate entry '1' for key 'PRIMARY' call h_ss()| h_ss Inner (good) -Warnings: -Error 1062 Duplicate entry '1' for key 'PRIMARY' call h_sn()| h_sn -Outer (good) -Warnings: -Warning 1329 No data - zero rows fetched, selected, or processed +Inner (bad) call h_sw()| h_sw -Outer (good) +Inner (bad) call h_sx()| h_sx -Outer (good) -Warnings: -Error 1062 Duplicate entry '1' for key 'PRIMARY' +Inner (bad) call h_ne()| h_ne Inner (good) -Warnings: -Warning 1329 No data - zero rows fetched, selected, or processed call h_ns()| h_ns Inner (good) -Warnings: -Warning 1329 No data - zero rows fetched, selected, or processed call h_nn()| h_nn Inner (good) -Warnings: -Warning 1329 No data - zero rows fetched, selected, or processed call h_we()| h_we Inner (good) @@ -1758,18 +1730,12 @@ Inner (good) call h_xe()| h_xe Inner (good) -Warnings: -Error 1062 Duplicate entry '1' for key 'PRIMARY' call h_xs()| h_xs Inner (good) -Warnings: -Error 1062 Duplicate entry '1' for key 'PRIMARY' call h_xx()| h_xx Inner (good) -Warnings: -Error 1062 Duplicate entry '1' for key 'PRIMARY' drop table t3| drop procedure h_ee| drop procedure h_es| @@ -1918,8 +1884,6 @@ set @x2 = 2; close c1; end| call bug2260()| -Warnings: -Error 1329 No data - zero rows fetched, selected, or processed select @x2| @x2 2 @@ -2063,8 +2027,6 @@ insert into t3 values (123456789012); insert into t3 values (0); end| call bug2780()| -Warnings: -Warning 1264 Out of range value for column 's1' at row 1 select @x| @x 1 @@ -2487,8 +2449,6 @@ declare continue handler for sqlstate 'HY000' begin end; select s1 from t3 union select s2 from t3; end| call bug4904()| -Warnings: -Error 1267 Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (latin2_general_ci,IMPLICIT) for operation 'UNION' drop procedure bug4904| drop table t3| drop procedure if exists bug336| @@ -2628,17 +2588,13 @@ select row_count()| row_count() 1 call bug4905()| -Warnings: -Error 1062 Duplicate entry '1' for key 'PRIMARY' select row_count()| row_count() --1 +0 call bug4905()| -Warnings: -Error 1062 Duplicate entry '1' for key 'PRIMARY' select row_count()| row_count() --1 +0 select * from t3| s1 1 @@ -2659,14 +2615,10 @@ insert into t3 values (1)| call bug6029()| sqlstate 23000 sqlstate 23000 -Warnings: -Error 1062 Duplicate entry '1' for key 'PRIMARY' delete from t3| call bug6029()| 1136 1136 -Warnings: -Error 1136 Column count doesn't match value count at row 1 drop procedure bug6029| drop table t3| drop procedure if exists bug8540| @@ -2961,23 +2913,15 @@ end| call bug6900()| 2 2 -Warnings: -Error 1136 Column count doesn't match value count at row 1 call bug9074()| x1 x2 x3 x4 x5 x6 1 1 1 1 1 1 -Warnings: -Error 1062 Duplicate entry 'a' for key 'w' call bug6900_9074(0)| sqlexception sqlexception -Warnings: -Error 1136 Column count doesn't match value count at row 1 call bug6900_9074(1)| -23000 -23000 -Warnings: -Error 1062 Duplicate entry 'a' for key 'w' +sqlexception +sqlexception drop procedure bug6900| drop procedure bug9074| drop procedure bug6900_9074| @@ -3020,13 +2964,9 @@ delete from t1| call bug9856()| 16 16 -Warnings: -Error 1329 No data - zero rows fetched, selected, or processed call bug9856()| 16 16 -Warnings: -Error 1329 No data - zero rows fetched, selected, or processed drop procedure bug9856| drop procedure if exists bug9674_1| drop procedure if exists bug9674_2| @@ -3256,8 +3196,6 @@ x 2 x 3 -Warnings: -Error 1326 Cursor is not open call bug10961()| x 1 @@ -3265,8 +3203,6 @@ x 2 x 3 -Warnings: -Error 1326 Cursor is not open drop procedure bug10961| DROP PROCEDURE IF EXISTS bug6866| DROP VIEW IF EXISTS tv| @@ -3274,9 +3210,9 @@ Warnings: Note 1051 Unknown table 'test.tv' DROP TABLE IF EXISTS tt1,tt2,tt3| Warnings: -Note 1051 Unknown table 'tt1' -Note 1051 Unknown table 'tt2' -Note 1051 Unknown table 'tt3' +Note 1051 Unknown table 'test.tt1' +Note 1051 Unknown table 'test.tt2' +Note 1051 Unknown table 'test.tt3' CREATE TABLE tt1 (a1 int, a2 int, a3 int, data varchar(10))| CREATE TABLE tt2 (a2 int, data2 varchar(10))| CREATE TABLE tt3 (a3 int, data3 varchar(10))| @@ -3382,11 +3318,7 @@ insert into t1 values ('Name4', 13), ('Name5', 14)| call bug11529()| -Warnings: -Error 1329 No data - zero rows fetched, selected, or processed call bug11529()| -Warnings: -Error 1329 No data - zero rows fetched, selected, or processed delete from t1| drop procedure bug11529| set character set utf8| @@ -3560,32 +3492,24 @@ end; end if; end| call bug12168('a')| -Warnings: -Error 1329 No data - zero rows fetched, selected, or processed select * from t4| a 1 3 truncate t4| call bug12168('b')| -Warnings: -Error 1329 No data - zero rows fetched, selected, or processed select * from t4| a 2 4 truncate t4| call bug12168('a')| -Warnings: -Error 1329 No data - zero rows fetched, selected, or processed select * from t4| a 1 3 truncate t4| call bug12168('b')| -Warnings: -Error 1329 No data - zero rows fetched, selected, or processed select * from t4| a 2 @@ -3885,8 +3809,6 @@ end| call bug7049_2()| Result Caught it -Warnings: -Error 1062 Duplicate entry '42' for key 'x' select * from t3| x 42 @@ -3894,16 +3816,12 @@ delete from t3| call bug7049_4()| Result Caught it -Warnings: -Error 1062 Duplicate entry '42' for key 'x' select * from t3| x 42 select bug7049_2()| bug7049_2() 1 -Warnings: -Error 1062 Duplicate entry '42' for key 'x' drop table t3| drop procedure bug7049_1| drop procedure bug7049_2| @@ -4031,8 +3949,6 @@ end| call bug14845()| a 0 -Warnings: -Error 1329 No data - zero rows fetched, selected, or processed drop procedure bug14845| drop procedure if exists bug13549_1| drop procedure if exists bug13549_2| @@ -4236,8 +4152,6 @@ end| call bug13729()| 55 55 -Warnings: -Error 1062 Duplicate entry '1' for key 'PRIMARY' select * from t3| s1 1 @@ -4274,15 +4188,11 @@ Handler boo v isnull(v) NULL 1 -Warnings: -Error 1054 Unknown column 'undefined_var' in 'field list' call bug14643_2()| Handler boo Handler boo -Warnings: -Error 1054 Unknown column 'undefined_var' in 'field list' drop procedure bug14643_1| drop procedure bug14643_2| drop procedure if exists bug14304| @@ -4606,15 +4516,11 @@ Handler error End done -Warnings: -Error 1054 Unknown column 'v' in 'field list' call bug14498_2()| Handler error End done -Warnings: -Error 1054 Unknown column 'v' in 'field list' call bug14498_3()| v maybe @@ -4622,22 +4528,16 @@ Handler error End done -Warnings: -Error 1054 Unknown column 'v' in 'field list' call bug14498_4()| Handler error End done -Warnings: -Error 1054 Unknown column 'v' in 'field list' call bug14498_5()| Handler error End done -Warnings: -Error 1054 Unknown column 'v' in 'field list' drop procedure bug14498_1| drop procedure bug14498_2| drop procedure bug14498_3| @@ -4702,8 +4602,6 @@ Before NOT FOUND condition is triggered After NOT FOUND condtition is triggered xid xdone 1 1 -Warnings: -Warning 1329 No data - zero rows fetched, selected, or processed call bug15231_3()| Result Missed it (correct) @@ -4711,8 +4609,6 @@ Level Code Message Warning 1366 Incorrect decimal value: 'zap' for column 'x' at row 1 Result Caught it (correct) -Warnings: -Warning 1366 Incorrect decimal value: 'zap' for column 'x' at row 1 call bug15231_5()| Result Missed it (correct) @@ -4741,8 +4637,6 @@ end| call bug15011()| Handler Inner -Warnings: -Error 1062 Duplicate entry '1' for key 'PRIMARY' drop procedure bug15011| drop table t3| drop procedure if exists bug17476| @@ -4818,8 +4712,6 @@ i 1 i 0 -Warnings: -Error 1329 No data - zero rows fetched, selected, or processed drop table t3| drop procedure bug16887| drop procedure if exists bug16474_1| @@ -4892,8 +4784,6 @@ declare continue handler for sqlexception begin end; select no_such_function(); end| call bug18787()| -Warnings: -Error 1305 FUNCTION test.no_such_function does not exist drop procedure bug18787| create database bug18344_012345678901| use bug18344_012345678901| @@ -5267,8 +5157,6 @@ statement failed statement failed statement after update statement after update -Warnings: -Error 1242 Subquery returns more than 1 row select * from t3| a 1 @@ -5280,8 +5168,6 @@ statement failed statement failed statement after update statement after update -Warnings: -Error 1242 Subquery returns more than 1 row select * from t3| a 1 @@ -5314,8 +5200,6 @@ in continue handler in continue handler reachable code a2 reachable code a2 -Warnings: -Error 1242 Subquery returns more than 1 row select * from t3| a 1 @@ -5331,8 +5215,6 @@ in continue handler in continue handler reachable code a2 reachable code a2 -Warnings: -Error 1242 Subquery returns more than 1 row select * from t3| a 1 @@ -5366,8 +5248,6 @@ in continue handler in continue handler reachable code a2 reachable code a2 -Warnings: -Error 1305 FUNCTION test.no_such_function does not exist drop procedure bug8153_proc_a| drop procedure bug8153_proc_b| drop table t3| @@ -5547,13 +5427,9 @@ end| select func_20028_a()| func_20028_a() 0 -Warnings: -Warning 1329 No data - zero rows fetched, selected, or processed select func_20028_b()| func_20028_b() 0 -Warnings: -Warning 1329 No data - zero rows fetched, selected, or processed select func_20028_c()| ERROR 22012: Division by 0 call proc_20028_a()| @@ -5606,13 +5482,9 @@ end| select func_20028_a()| func_20028_a() 0 -Warnings: -Warning 1329 No data - zero rows fetched, selected, or processed select func_20028_b()| func_20028_b() 0 -Warnings: -Warning 1329 No data - zero rows fetched, selected, or processed select func_20028_c()| func_20028_c() NULL @@ -5936,13 +5808,9 @@ end| select func_8407_a()| func_8407_a() NULL -Warnings: -Error 1146 Table 'test.no_such_view' doesn't exist select func_8407_b()| func_8407_b() 1500 -Warnings: -Error 1146 Table 'test.no_such_view' doesn't exist drop function func_8407_a| drop function func_8407_b| drop table if exists table_26503| @@ -6064,8 +5932,6 @@ looping i looping 0 leaving handler leaving handler -Warnings: -Error 1062 Duplicate entry '1' for key 'a' call proc_26503_ok_2(2)| do something do something @@ -6077,8 +5943,6 @@ looping i looping 4 leaving handler leaving handler -Warnings: -Error 1062 Duplicate entry '2' for key 'a' call proc_26503_ok_3(3)| do something do something @@ -6098,8 +5962,6 @@ looping i looping 0 leaving handler leaving handler -Warnings: -Error 1062 Duplicate entry '3' for key 'a' call proc_26503_ok_4(4)| do something do something @@ -6111,8 +5973,6 @@ looping i looping 4 leaving handler leaving handler -Warnings: -Error 1062 Duplicate entry '4' for key 'a' drop table table_26503| drop procedure proc_26503_ok_1| drop procedure proc_26503_ok_2| @@ -6244,8 +6104,6 @@ END| SELECT bug5274_f2()| bug5274_f2() x -Warnings: -Warning 1265 Data truncated for column 'bug5274_f1' at row 1 DROP FUNCTION bug5274_f1| DROP FUNCTION bug5274_f2| drop procedure if exists proc_21513| @@ -6338,19 +6196,13 @@ c1 SELECT f1(2); f1(2) 0 -Warnings: -Warning 1329 No data - zero rows fetched, selected, or processed PREPARE s1 FROM 'SELECT f1(2)'; EXECUTE s1; f1(2) 0 -Warnings: -Warning 1329 No data - zero rows fetched, selected, or processed EXECUTE s1; f1(2) 0 -Warnings: -Warning 1329 No data - zero rows fetched, selected, or processed DROP PROCEDURE p1; DROP PROCEDURE p2; DROP FUNCTION f1; @@ -6826,8 +6678,6 @@ DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET @exception:= 'run'; SELECT x FROM t1; END| CALL bug29770(); -Warnings: -Error 1054 Unknown column 'x' in 'field list' SELECT @state, @exception; @state @exception run NULL @@ -6866,8 +6716,6 @@ end; end while; end// call proc_33618(20); -Warnings: -Error 1329 No data - zero rows fetched, selected, or processed drop table t_33618; drop procedure proc_33618; # @@ -7803,9 +7651,6 @@ END $ SELECT f1(); f1() 1 -Warnings: -Error 1424 Recursive stored functions and triggers are not allowed. -Error 1305 FUNCTION test.f1 does not exist DROP FUNCTION f1; # ------------------------------------------------------------------ # -- End of 5.1 tests diff --git a/mysql-test/r/sp_trans.result b/mysql-test/r/sp_trans.result index b91dc898f12..5526fc19aae 100644 --- a/mysql-test/r/sp_trans.result +++ b/mysql-test/r/sp_trans.result @@ -99,8 +99,6 @@ return i; end| set @error_in_func:= 0| insert into t1 values (bug10015_6(5)), (bug10015_6(6))| -Warnings: -Error 1062 Duplicate entry '1' for key 'PRIMARY' select @error_in_func| @error_in_func 1 @@ -526,8 +524,6 @@ until done end repeat; close c; end| call bug14210()| -Warnings: -Error 1329 No data - zero rows fetched, selected, or processed select count(*) from t4| count(*) 256 diff --git a/mysql-test/r/statistics.result b/mysql-test/r/statistics.result index ad12c60cce5..e7d25f3476c 100644 --- a/mysql-test/r/statistics.result +++ b/mysql-test/r/statistics.result @@ -1113,9 +1113,19 @@ test t2 idx4 4 1.0000 ALTER TABLE t2 CHANGE COLUMN b b varchar(32); SELECT * FROM mysql.index_stats ORDER BY index_name, prefix_arity, table_name; db_name table_name index_name prefix_arity avg_frequency +test t2 PRIMARY 1 1.0000 +test t2 PRIMARY 2 1.0000 test t2 idx2 1 7.0000 test t2 idx2 2 2.3846 +test t2 idx2 3 1.0000 +test t2 idx2 4 1.0000 test t2 idx3 1 8.5000 +test t2 idx3 2 1.0000 +test t2 idx3 3 1.0000 +test t2 idx4 1 6.2000 +test t2 idx4 2 1.7222 +test t2 idx4 3 1.1154 +test t2 idx4 4 1.0000 ANALYZE TABLE t2 PERSISTENT FOR COLUMNS ALL INDEXES ALL; Table Op Msg_type Msg_text test.t2 analyze status OK @@ -1172,13 +1182,13 @@ test t2 f 1 5 0.2000 1.0000 6.4000 0 NULL NULL SELECT * FROM mysql.index_stats; db_name table_name index_name prefix_arity avg_frequency test t2 idx3 1 8.5000 -test t2 idx3 2 1.0000 -test t2 idx2 3 1.0000 test t2 idx2 1 7.0000 test t2 idx2 2 2.3846 -test t2 idx4 3 1.0000 +test t2 idx2 3 1.0000 test t2 idx4 1 6.2000 test t2 idx4 2 2.2308 +test t2 idx4 3 1.0000 +test t2 idx3 2 1.0000 test t2 PRIMARY 1 1.0000 ANALYZE TABLE t1; Table Op Msg_type Msg_text @@ -1198,17 +1208,17 @@ test t1 f 1 5 0.2000 1.0000 6.4000 0 NULL NULL test t1 b NULL NULL 0.2000 17.1250 NULL NULL NULL NULL SELECT * FROM mysql.index_stats; db_name table_name index_name prefix_arity avg_frequency -test t2 idx3 1 8.5000 -test t2 idx3 2 1.0000 -test t2 idx2 3 1.0000 -test t2 idx2 1 7.0000 -test t2 idx2 2 2.3846 test t1 idx2 1 7.0000 +test t2 idx3 1 8.5000 test t1 idx3 1 8.5000 test t1 PRIMARY 1 1.0000 -test t2 idx4 3 1.0000 +test t2 idx2 1 7.0000 +test t2 idx2 2 2.3846 +test t2 idx2 3 1.0000 test t2 idx4 1 6.2000 test t2 idx4 2 2.2308 +test t2 idx4 3 1.0000 +test t2 idx3 2 1.0000 test t2 PRIMARY 1 1.0000 test t1 idx2 2 2.3846 test t1 idx1 1 NULL diff --git a/mysql-test/r/strict.result b/mysql-test/r/strict.result index cee4cf3ebe6..1321545798f 100644 --- a/mysql-test/r/strict.result +++ b/mysql-test/r/strict.result @@ -1190,8 +1190,6 @@ select'a'; insert into t1 values (200); end;| call t1(); a a -Warnings: -Error 1264 Out of range value for column 'col1' at row 1 select * from t1; col1 drop procedure t1; @@ -1501,3 +1499,30 @@ count(*) 0 drop table t1; End of 5.0 tests +# +# Start of 5.6 tests +# +# +# WL#946 TIME/TIMESTAMP/DATETIME with fractional seconds: CAST to DATETIME +# +# +# STR_TO_DATE with NO_ZERO_DATE did not return NULL (with warning) +# in get_date(). Only did in val_str() and val_int(). +SET sql_mode='NO_ZERO_DATE'; +SELECT STR_TO_DATE('2001','%Y'),CONCAT(STR_TO_DATE('2001','%Y')), STR_TO_DATE('2001','%Y')+1, STR_TO_DATE('0','%Y')+1, STR_TO_DATE('0000','%Y')+1; +STR_TO_DATE('2001','%Y') CONCAT(STR_TO_DATE('2001','%Y')) STR_TO_DATE('2001','%Y')+1 STR_TO_DATE('0','%Y')+1 STR_TO_DATE('0000','%Y')+1 +2001-00-00 2001-00-00 20010001 20000001 NULL +Warnings: +Warning 1411 Incorrect datetime value: '0000' for function str_to_date +SET sql_mode='NO_ZERO_IN_DATE'; +SELECT STR_TO_DATE('2001','%Y'),CONCAT(STR_TO_DATE('2001','%Y')), STR_TO_DATE('2001','%Y')+1, STR_TO_DATE('0000','%Y')+1; +STR_TO_DATE('2001','%Y') CONCAT(STR_TO_DATE('2001','%Y')) STR_TO_DATE('2001','%Y')+1 STR_TO_DATE('0000','%Y')+1 +NULL NULL NULL NULL +Warnings: +Warning 1411 Incorrect datetime value: '2001' for function str_to_date +Warning 1411 Incorrect datetime value: '2001' for function str_to_date +Warning 1411 Incorrect datetime value: '2001' for function str_to_date +Warning 1411 Incorrect datetime value: '0000' for function str_to_date +# +# End of 5.6 tests +# diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index eac53365329..824079c3a59 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -5898,7 +5898,7 @@ CREATE TABLE t2 ( f3 int, f10 int, KEY (f10,f3)) ; INSERT IGNORE INTO t2 VALUES (NULL,NULL),(5,0); DROP TABLE IF EXISTS t3; Warnings: -Note 1051 Unknown table 't3' +Note 1051 Unknown table 'test.t3' CREATE TABLE t3 ( f3 int) ; INSERT INTO t3 VALUES (0),(0); SELECT a1.f3 AS r FROM t2 AS a1 , t1 WHERE a1.f3 < ALL ( SELECT f3 FROM t3 WHERE f3 = 1 ) ; diff --git a/mysql-test/r/subselect2.result b/mysql-test/r/subselect2.result index 4fd303dfd44..38e955e349c 100644 --- a/mysql-test/r/subselect2.result +++ b/mysql-test/r/subselect2.result @@ -184,7 +184,7 @@ DROP TABLE t1,t2,t3; # DROP TABLE IF EXISTS `t1`; Warnings: -Note 1051 Unknown table 't1' +Note 1051 Unknown table 'test.t1' CREATE TABLE `t1` ( `node_uid` bigint(20) unsigned DEFAULT NULL, `date` datetime DEFAULT NULL, diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result index ff768886434..df7733a1cd3 100644 --- a/mysql-test/r/subselect4.result +++ b/mysql-test/r/subselect4.result @@ -2174,7 +2174,7 @@ FROM t1 AS alias1 ) IS NOT NULL; ERROR 21000: Subquery returns more than 1 row DROP TABLE t2; -ERROR 42S02: Unknown table 't2' +ERROR 42S02: Unknown table 'test.t2' DROP TABLE t1; # # LP BUG#1000649 EXPLAIN shows incorrectly a non-correlated constant IN subquery is correlated diff --git a/mysql-test/r/subselect_innodb.result b/mysql-test/r/subselect_innodb.result index a5800883711..e9e1ccd0bf6 100644 --- a/mysql-test/r/subselect_innodb.result +++ b/mysql-test/r/subselect_innodb.result @@ -437,8 +437,8 @@ drop table t1; # drop table if exists `t1`,`t2`; Warnings: -Note 1051 Unknown table 't1' -Note 1051 Unknown table 't2' +Note 1051 Unknown table 'test.t1' +Note 1051 Unknown table 'test.t2' create table `t1`(`a` char(1) character set utf8)engine=innodb; create table `t2`(`b` char(1) character set utf8)engine=memory; select distinct (select 1 from `t2` where `a`) `d2` from `t1`; diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result index bf98b912a9e..627e5b03e32 100644 --- a/mysql-test/r/subselect_no_mat.result +++ b/mysql-test/r/subselect_no_mat.result @@ -5899,7 +5899,7 @@ CREATE TABLE t2 ( f3 int, f10 int, KEY (f10,f3)) ; INSERT IGNORE INTO t2 VALUES (NULL,NULL),(5,0); DROP TABLE IF EXISTS t3; Warnings: -Note 1051 Unknown table 't3' +Note 1051 Unknown table 'test.t3' CREATE TABLE t3 ( f3 int) ; INSERT INTO t3 VALUES (0),(0); SELECT a1.f3 AS r FROM t2 AS a1 , t1 WHERE a1.f3 < ALL ( SELECT f3 FROM t3 WHERE f3 = 1 ) ; diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result index 05f7a25e0ef..5368198f77a 100644 --- a/mysql-test/r/subselect_no_opts.result +++ b/mysql-test/r/subselect_no_opts.result @@ -5895,7 +5895,7 @@ CREATE TABLE t2 ( f3 int, f10 int, KEY (f10,f3)) ; INSERT IGNORE INTO t2 VALUES (NULL,NULL),(5,0); DROP TABLE IF EXISTS t3; Warnings: -Note 1051 Unknown table 't3' +Note 1051 Unknown table 'test.t3' CREATE TABLE t3 ( f3 int) ; INSERT INTO t3 VALUES (0),(0); SELECT a1.f3 AS r FROM t2 AS a1 , t1 WHERE a1.f3 < ALL ( SELECT f3 FROM t3 WHERE f3 = 1 ) ; diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result index ee84bfd1eca..ad0d2ffe6a6 100644 --- a/mysql-test/r/subselect_no_scache.result +++ b/mysql-test/r/subselect_no_scache.result @@ -5904,7 +5904,7 @@ CREATE TABLE t2 ( f3 int, f10 int, KEY (f10,f3)) ; INSERT IGNORE INTO t2 VALUES (NULL,NULL),(5,0); DROP TABLE IF EXISTS t3; Warnings: -Note 1051 Unknown table 't3' +Note 1051 Unknown table 'test.t3' CREATE TABLE t3 ( f3 int) ; INSERT INTO t3 VALUES (0),(0); SELECT a1.f3 AS r FROM t2 AS a1 , t1 WHERE a1.f3 < ALL ( SELECT f3 FROM t3 WHERE f3 = 1 ) ; diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result index 5a7e303f4b9..39cc060e955 100644 --- a/mysql-test/r/subselect_no_semijoin.result +++ b/mysql-test/r/subselect_no_semijoin.result @@ -5895,7 +5895,7 @@ CREATE TABLE t2 ( f3 int, f10 int, KEY (f10,f3)) ; INSERT IGNORE INTO t2 VALUES (NULL,NULL),(5,0); DROP TABLE IF EXISTS t3; Warnings: -Note 1051 Unknown table 't3' +Note 1051 Unknown table 'test.t3' CREATE TABLE t3 ( f3 int) ; INSERT INTO t3 VALUES (0),(0); SELECT a1.f3 AS r FROM t2 AS a1 , t1 WHERE a1.f3 < ALL ( SELECT f3 FROM t3 WHERE f3 = 1 ) ; diff --git a/mysql-test/r/system_mysql_db.result b/mysql-test/r/system_mysql_db.result index 08c17b1afd5..eda11d95b22 100644 --- a/mysql-test/r/system_mysql_db.result +++ b/mysql-test/r/system_mysql_db.result @@ -15,15 +15,11 @@ host index_stats innodb_index_stats innodb_table_stats -ndb_binlog_index plugin proc procs_priv proxies_priv servers -slave_master_info -slave_relay_log_info -slave_worker_info slow_log table_stats tables_priv @@ -131,6 +127,7 @@ user CREATE TABLE `user` ( `max_user_connections` int(11) NOT NULL DEFAULT '0', `plugin` char(64) CHARACTER SET latin1 NOT NULL DEFAULT '', `authentication_string` text COLLATE utf8_bin NOT NULL, + `password_expired` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', PRIMARY KEY (`Host`,`User`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges' show create table func; @@ -253,7 +250,7 @@ Table Create Table general_log CREATE TABLE `general_log` ( `event_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), `user_host` mediumtext NOT NULL, - `thread_id` int(11) NOT NULL, + `thread_id` bigint(21) unsigned NOT NULL, `server_id` int(10) unsigned NOT NULL, `command_type` varchar(64) NOT NULL, `argument` mediumtext NOT NULL @@ -271,7 +268,8 @@ slow_log CREATE TABLE `slow_log` ( `last_insert_id` int(11) NOT NULL, `insert_id` int(11) NOT NULL, `server_id` int(10) unsigned NOT NULL, - `sql_text` mediumtext NOT NULL + `sql_text` mediumtext NOT NULL, + `thread_id` bigint(21) unsigned NOT NULL ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log' show create table table_stats; Table Create Table diff --git a/mysql-test/r/system_mysql_db_fix40123.result b/mysql-test/r/system_mysql_db_fix40123.result index 2f76ee654c8..71e94a7432d 100644 --- a/mysql-test/r/system_mysql_db_fix40123.result +++ b/mysql-test/r/system_mysql_db_fix40123.result @@ -15,15 +15,11 @@ host index_stats innodb_index_stats innodb_table_stats -ndb_binlog_index plugin proc procs_priv proxies_priv servers -slave_master_info -slave_relay_log_info -slave_worker_info slow_log table_stats tables_priv @@ -131,6 +127,7 @@ user CREATE TABLE `user` ( `max_user_connections` int(11) NOT NULL DEFAULT '0', `plugin` char(64) CHARACTER SET latin1 NOT NULL DEFAULT '', `authentication_string` text COLLATE utf8_bin NOT NULL, + `password_expired` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', PRIMARY KEY (`Host`,`User`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges' show create table func; @@ -253,7 +250,7 @@ Table Create Table general_log CREATE TABLE `general_log` ( `event_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), `user_host` mediumtext NOT NULL, - `thread_id` int(11) NOT NULL, + `thread_id` bigint(21) unsigned NOT NULL, `server_id` int(10) unsigned NOT NULL, `command_type` varchar(64) NOT NULL, `argument` mediumtext NOT NULL @@ -271,7 +268,8 @@ slow_log CREATE TABLE `slow_log` ( `last_insert_id` int(11) NOT NULL, `insert_id` int(11) NOT NULL, `server_id` int(10) unsigned NOT NULL, - `sql_text` mediumtext NOT NULL + `sql_text` mediumtext NOT NULL, + `thread_id` bigint(21) unsigned NOT NULL ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log' show create table table_stats; Table Create Table diff --git a/mysql-test/r/system_mysql_db_fix50030.result b/mysql-test/r/system_mysql_db_fix50030.result index 2f76ee654c8..71e94a7432d 100644 --- a/mysql-test/r/system_mysql_db_fix50030.result +++ b/mysql-test/r/system_mysql_db_fix50030.result @@ -15,15 +15,11 @@ host index_stats innodb_index_stats innodb_table_stats -ndb_binlog_index plugin proc procs_priv proxies_priv servers -slave_master_info -slave_relay_log_info -slave_worker_info slow_log table_stats tables_priv @@ -131,6 +127,7 @@ user CREATE TABLE `user` ( `max_user_connections` int(11) NOT NULL DEFAULT '0', `plugin` char(64) CHARACTER SET latin1 NOT NULL DEFAULT '', `authentication_string` text COLLATE utf8_bin NOT NULL, + `password_expired` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', PRIMARY KEY (`Host`,`User`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges' show create table func; @@ -253,7 +250,7 @@ Table Create Table general_log CREATE TABLE `general_log` ( `event_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), `user_host` mediumtext NOT NULL, - `thread_id` int(11) NOT NULL, + `thread_id` bigint(21) unsigned NOT NULL, `server_id` int(10) unsigned NOT NULL, `command_type` varchar(64) NOT NULL, `argument` mediumtext NOT NULL @@ -271,7 +268,8 @@ slow_log CREATE TABLE `slow_log` ( `last_insert_id` int(11) NOT NULL, `insert_id` int(11) NOT NULL, `server_id` int(10) unsigned NOT NULL, - `sql_text` mediumtext NOT NULL + `sql_text` mediumtext NOT NULL, + `thread_id` bigint(21) unsigned NOT NULL ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log' show create table table_stats; Table Create Table diff --git a/mysql-test/r/system_mysql_db_fix50117.result b/mysql-test/r/system_mysql_db_fix50117.result index 2f76ee654c8..71e94a7432d 100644 --- a/mysql-test/r/system_mysql_db_fix50117.result +++ b/mysql-test/r/system_mysql_db_fix50117.result @@ -15,15 +15,11 @@ host index_stats innodb_index_stats innodb_table_stats -ndb_binlog_index plugin proc procs_priv proxies_priv servers -slave_master_info -slave_relay_log_info -slave_worker_info slow_log table_stats tables_priv @@ -131,6 +127,7 @@ user CREATE TABLE `user` ( `max_user_connections` int(11) NOT NULL DEFAULT '0', `plugin` char(64) CHARACTER SET latin1 NOT NULL DEFAULT '', `authentication_string` text COLLATE utf8_bin NOT NULL, + `password_expired` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', PRIMARY KEY (`Host`,`User`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges' show create table func; @@ -253,7 +250,7 @@ Table Create Table general_log CREATE TABLE `general_log` ( `event_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), `user_host` mediumtext NOT NULL, - `thread_id` int(11) NOT NULL, + `thread_id` bigint(21) unsigned NOT NULL, `server_id` int(10) unsigned NOT NULL, `command_type` varchar(64) NOT NULL, `argument` mediumtext NOT NULL @@ -271,7 +268,8 @@ slow_log CREATE TABLE `slow_log` ( `last_insert_id` int(11) NOT NULL, `insert_id` int(11) NOT NULL, `server_id` int(10) unsigned NOT NULL, - `sql_text` mediumtext NOT NULL + `sql_text` mediumtext NOT NULL, + `thread_id` bigint(21) unsigned NOT NULL ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log' show create table table_stats; Table Create Table diff --git a/mysql-test/r/temp_table.result b/mysql-test/r/temp_table.result index 33f5c6b5165..a5182a03e63 100644 --- a/mysql-test/r/temp_table.result +++ b/mysql-test/r/temp_table.result @@ -1,5 +1,25 @@ drop table if exists t1,t2; drop view if exists v1; +# +# test basic creation of temporary tables together with normal table +# +create table t1 (a int); +create temporary table t1 AS SELECT 1; +create temporary table t1 AS SELECT 1; +ERROR 42S01: Table 't1' already exists +create temporary table t1 (a int); +ERROR 42S01: Table 't1' already exists +drop temporary table t1; +drop table t1; +create temporary table t1 AS SELECT 1; +create temporary table t1 AS SELECT 1; +ERROR 42S01: Table 't1' already exists +create temporary table t1 (a int); +ERROR 42S01: Table 't1' already exists +drop temporary table t1; +# +# Test with rename +# CREATE TABLE t1 (c int not null, d char (10) not null); insert into t1 values(1,""),(2,"a"),(3,"b"); CREATE TEMPORARY TABLE t1 (a int not null, b char (10) not null); @@ -145,7 +165,7 @@ DROP TABLE t1; CREATE TABLE t1 (i INT); CREATE TEMPORARY TABLE t2 (i INT); DROP TEMPORARY TABLE t2, t1; -ERROR 42S02: Unknown table 't1' +ERROR 42S02: Unknown table 'test.t1' SELECT * FROM t2; ERROR 42S02: Table 'test.t2' doesn't exist SELECT * FROM t1; diff --git a/mysql-test/r/truncate_coverage.result b/mysql-test/r/truncate_coverage.result index 728702f7ab5..395c71b2e6b 100644 --- a/mysql-test/r/truncate_coverage.result +++ b/mysql-test/r/truncate_coverage.result @@ -11,7 +11,7 @@ HANDLER t1 OPEN; # # connection default LOCK TABLE t1 WRITE; -SET DEBUG_SYNC='mdl_upgrade_shared_lock_to_exclusive SIGNAL waiting'; +SET DEBUG_SYNC='mdl_upgrade_lock SIGNAL waiting'; TRUNCATE TABLE t1; # # connection con2 @@ -37,7 +37,7 @@ HANDLER t1 OPEN; # # connection default LOCK TABLE t1 WRITE; -SET DEBUG_SYNC='mdl_upgrade_shared_lock_to_exclusive SIGNAL waiting'; +SET DEBUG_SYNC='mdl_upgrade_lock SIGNAL waiting'; TRUNCATE TABLE t1; # # connection con2 @@ -50,7 +50,7 @@ HANDLER t1 CLOSE; ERROR 42S02: Table 'test.t1' doesn't exist UNLOCK TABLES; DROP TABLE t1; -ERROR 42S02: Unknown table 't1' +ERROR 42S02: Unknown table 'test.t1' SET DEBUG_SYNC='RESET'; CREATE TABLE t1 (c1 INT); INSERT INTO t1 VALUES (1); diff --git a/mysql-test/r/type_newdecimal.result b/mysql-test/r/type_newdecimal.result index 6b6b5a2392c..5b3594fe503 100644 --- a/mysql-test/r/type_newdecimal.result +++ b/mysql-test/r/type_newdecimal.result @@ -680,7 +680,7 @@ select 0.8 = 0.7 + 0.1; 1 drop table if exists t1; Warnings: -Note 1051 Unknown table 't1' +Note 1051 Unknown table 'test.t1' create table t1 (col1 decimal(38)); insert into t1 values (12345678901234567890123456789012345678); select * from t1; diff --git a/mysql-test/r/upgrade.result b/mysql-test/r/upgrade.result index d565bb2dbd6..d9252791c0a 100644 --- a/mysql-test/r/upgrade.result +++ b/mysql-test/r/upgrade.result @@ -50,6 +50,10 @@ show tables; Tables_in_test txu#p#p1 txu@0023p@0023p1 +insert into `txu@0023p@0023p1` values (2); +select * from `txu@0023p@0023p1`; +s1 +2 select * from `txu#p#p1`; s1 1 diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index 40d6c71b5af..98b4b77e425 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -205,7 +205,7 @@ ERROR 42S02: Unknown table 'v100' drop view t1; ERROR HY000: 'test.t1' is not VIEW drop table v1; -ERROR 42S02: Unknown table 'v1' +ERROR 42S02: Unknown table 'test.v1' drop view v1,v2; drop table t1; create table t1 (a int); @@ -3951,8 +3951,6 @@ create view a as select 1; end| call p(); call p(); -Warnings: -Error 1050 Table 'a' already exists drop view a; drop procedure p; # diff --git a/mysql-test/r/view_grant.result b/mysql-test/r/view_grant.result index bfd09bfa9cd..2078bc6d63f 100644 --- a/mysql-test/r/view_grant.result +++ b/mysql-test/r/view_grant.result @@ -357,13 +357,9 @@ use mysqltest; select * from v1; f2() NULL -Warnings: -Warning 1329 No data - zero rows fetched, selected, or processed select * from v2; f2() NULL -Warnings: -Warning 1329 No data - zero rows fetched, selected, or processed select * from v3; ERROR HY000: View 'mysqltest.v3' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them select * from v4; @@ -403,13 +399,9 @@ ERROR HY000: View 'mysqltest.v2' references invalid table(s) or column(s) or fun select * from v3; f2() NULL -Warnings: -Warning 1329 No data - zero rows fetched, selected, or processed select * from v4; f2() NULL -Warnings: -Warning 1329 No data - zero rows fetched, selected, or processed select * from v5; ERROR HY000: View 'mysqltest.v5' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them drop view v1, v2, v3, v4, v5; diff --git a/mysql-test/r/warnings.result b/mysql-test/r/warnings.result index e033b358b6b..b4b345ca260 100644 --- a/mysql-test/r/warnings.result +++ b/mysql-test/r/warnings.result @@ -48,13 +48,13 @@ drop table t1; set SQL_WARNINGS=0; drop temporary table if exists not_exists; Warnings: -Note 1051 Unknown table 'not_exists' +Note 1051 Unknown table 'test.not_exists' drop table if exists not_exists_table; Warnings: -Note 1051 Unknown table 'not_exists_table' +Note 1051 Unknown table 'test.not_exists_table' show warnings limit 1; Level Code Message -Note 1051 Unknown table 'not_exists_table' +Note 1051 Unknown table 'test.not_exists_table' drop database if exists not_exists_db; Warnings: Note 1008 Can't drop database 'not_exists_db'; database doesn't exist @@ -311,10 +311,10 @@ insert into t2 values(@q); ERROR 22001: Data too long for column 'c_tinyblob' at row 1 drop table t1, t2; DROP TABLE t1; -ERROR 42S02: Unknown table 't1' +ERROR 42S02: Unknown table 'test.t1' SHOW ERRORS; Level Code Message -Error 1051 Unknown table 't1' +Error 1051 Unknown table 'test.t1' End of 5.0 tests set sql_mode = default; select CAST(a AS DECIMAL(13,5)) FROM (SELECT '' as a) t; |