diff options
41 files changed, 1402 insertions, 987 deletions
diff --git a/mysql-test/include/wait_until_count_sessions.inc b/mysql-test/include/wait_until_count_sessions.inc index 41348bee129..de4f0eeb652 100644 --- a/mysql-test/include/wait_until_count_sessions.inc +++ b/mysql-test/include/wait_until_count_sessions.inc @@ -2,14 +2,23 @@ # # SUMMARY # -# Waits until the passed number ($count_sessions) of concurrent sessions was -# observed via +# Waits until the passed number ($count_sessions) of concurrent sessions or +# a smaller number was observed via # SHOW STATUS LIKE 'Threads_connected' # or the operation times out. -# Note: Starting with 5.1 we could also use -# SELECT COUNT(*) FROM information_schema.processlist -# I stay with "SHOW STATUS LIKE 'Threads_connected'" because this -# runs in all versions 5.0+ +# Note: +# 1. We wait for $current_sessions <= $count_sessions because in the use case +# with count_sessions.inc before and wait_until_count_sessions.inc after +# the core of the test it could happen that the disconnects of sessions +# belonging to the preceeding test are not finished. +# sessions at test begin($count_sessions) = m + n +# sessions of the previous test which will be soon disconnected = n (n >= 0) +# sessions at test end ($current sessions, assuming the test disconnects +# all additional sessions) = m +# 2. Starting with 5.1 we could also use +# SELECT COUNT(*) FROM information_schema.processlist +# I stay with "SHOW STATUS LIKE 'Threads_connected'" because this +# runs in all versions 5.0+ # # # USAGE @@ -19,20 +28,20 @@ # # OR typical example of a test which uses more than one session # Such a test could harm successing tests if there is no server shutdown -# and start between.cw +# and start between. # # If the testing box is slow than the disconnect of sessions belonging to # the current test might happen when the successing test gets executed. # This means the successing test might see activities like unexpected # rows within the general log or the PROCESSLIST. # Example from bug http://bugs.mysql.com/bug.php?id=40377 -# --- bzr_mysql-6.0-rpl/.../r/log_state.result +# --- bzr_mysql-6.0-rpl/.../r/log_state.result # +++ bzr_mysql-6.0-rpl/.../r/log_state.reject # @@ -25,6 +25,7 @@ -# event_time user_host ... command_type argument -# TIMESTAMP USER_HOST ... Query create table t1(f1 int) -# TIMESTAMP USER_HOST ... Query select * from mysql.general_log -# +TIMESTAMP USER_HOST ... Quit +# event_time user_host ... command_type argument +# TIMESTAMP USER_HOST ... Query create table t1(f1 int) +# TIMESTAMP USER_HOST ... Query select * from mysql.general_log +# +TIMESTAMP USER_HOST ... Quit # .... # # What to do? @@ -79,7 +88,11 @@ # backup.test, grant3.test # # -# Created: 2009-01-14 mleich +# Created: +# 2009-01-14 mleich +# Modified: +# 2009-02-24 mleich Fix Bug#43114 wait_until_count_sessions too restrictive, +# random PB failures # let $wait_counter= 100; @@ -93,7 +106,7 @@ let $wait_timeout= 0; while ($wait_counter) { let $current_sessions= query_get_value(SHOW STATUS LIKE 'Threads_connected', Value, 1); - let $success= `SELECT $current_sessions = $count_sessions`; + let $success= `SELECT $current_sessions <= $count_sessions`; if ($success) { let $wait_counter= 0; @@ -107,7 +120,7 @@ while ($wait_counter) if (!$success) { --echo # Timeout in wait_until_count_sessions.inc - --echo # Number of sessions expected: $count_sessions found: $current_sessions + --echo # Number of sessions expected: <= $count_sessions found: $current_sessions SHOW PROCESSLIST; } diff --git a/mysql-test/r/connect.result b/mysql-test/r/connect.result index 727433d3032..5e6c013bb38 100644 --- a/mysql-test/r/connect.result +++ b/mysql-test/r/connect.result @@ -188,7 +188,7 @@ DROP USER mysqltest_u1@localhost; # -- End of Bug#33507. -# -- Bug#35074: max_used_connections is not correct. +# -- Bug#35074: max_used_connections is not correct. FLUSH STATUS; diff --git a/mysql-test/r/consistent_snapshot.result b/mysql-test/r/consistent_snapshot.result index 90606abbe4e..694c996a58e 100644 --- a/mysql-test/r/consistent_snapshot.result +++ b/mysql-test/r/consistent_snapshot.result @@ -1,15 +1,23 @@ -drop table if exists t1; -create table t1 (a int) engine=innodb; -start transaction with consistent snapshot; -insert into t1 values(1); -select * from t1; +DROP TABLE IF EXISTS t1; +# Establish connection con1 (user=root) +# Establish connection con2 (user=root) +# Switch to connection con1 +CREATE TABLE t1 (a INT) ENGINE=innodb; +START TRANSACTION WITH CONSISTENT SNAPSHOT; +# Switch to connection con2 +INSERT INTO t1 VALUES(1); +# Switch to connection con1 +SELECT * FROM t1; a -commit; -delete from t1; -start transaction; -insert into t1 values(1); -select * from t1; +COMMIT; +DELETE FROM t1; +START TRANSACTION; +# Switch to connection con2 +INSERT INTO t1 VALUES(1); +# Switch to connection con1 +SELECT * FROM t1; a 1 -commit; -drop table t1; +COMMIT; +# Switch to connection default + close connections con1 and con2 +DROP TABLE t1; diff --git a/mysql-test/r/dirty_close.result b/mysql-test/r/dirty_close.result index c4fc19a35f8..b49b72f1b95 100644 --- a/mysql-test/r/dirty_close.result +++ b/mysql-test/r/dirty_close.result @@ -1,9 +1,9 @@ -drop table if exists t1; -create table t1 (n int); -insert into t1 values (1),(2),(3); -select * from t1; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 (n INT); +INSERT INTO t1 VALUES (1),(2),(3); +SELECT * FROM t1; n 1 2 3 -drop table t1; +DROP TABLE t1; diff --git a/mysql-test/r/flush_block_commit.result b/mysql-test/r/flush_block_commit.result index d5b10868358..d2197beaaab 100644 --- a/mysql-test/r/flush_block_commit.result +++ b/mysql-test/r/flush_block_commit.result @@ -1,39 +1,57 @@ -drop table if exists t1; -create table t1 (a int) engine=innodb; -begin; -insert into t1 values(1); -flush tables with read lock; -select * from t1; +# Establish connection con1 (user=root) +# Establish connection con2 (user=root) +# Establish connection con3 (user=root) +# Switch to connection con1 +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 (a INT) ENGINE=innodb; +BEGIN; +INSERT INTO t1 VALUES(1); +# Switch to connection con2 +FLUSH TABLES WITH READ LOCK; +SELECT * FROM t1; a -commit; -select * from t1; +# Switch to connection con1 +COMMIT; +# Switch to connection con2 +SELECT * FROM t1; a -unlock tables; -begin; -select * from t1 for update; +UNLOCK TABLES; +# Switch to connection con1 +# Switch to connection con1 +BEGIN; +SELECT * FROM t1 FOR UPDATE; a 1 -begin; -select * from t1 for update; -flush tables with read lock; -commit; +# Switch to connection con2 +BEGIN; +SELECT * FROM t1 FOR UPDATE; +# Switch to connection con3 +FLUSH TABLES WITH READ LOCK; +# Switch to connection con1 +COMMIT; +# Switch to connection con2 a 1 -unlock tables; -commit; -begin; -insert into t1 values(10); -flush tables with read lock; -commit; -unlock tables; -flush tables with read lock; -unlock tables; -begin; -select * from t1; +# Switch to connection con3 +UNLOCK TABLES; +# Switch to connection con2 +COMMIT; +# Switch to connection con1 +BEGIN; +INSERT INTO t1 VALUES(10); +FLUSH TABLES WITH READ LOCK; +COMMIT; +UNLOCK TABLES; +# Switch to connection con2 +FLUSH TABLES WITH READ LOCK; +UNLOCK TABLES; +BEGIN; +SELECT * FROM t1; a 1 10 -show create database test; +SHOW CREATE DATABASE test; Database Create Database test CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET latin1 */ -drop table t1; +DROP TABLE t1; +# Switch to connection default and close connections con1, con2, con3 diff --git a/mysql-test/r/flush_block_commit_notembedded.result b/mysql-test/r/flush_block_commit_notembedded.result index 16fb143ee4c..c7fd7a11877 100644 --- a/mysql-test/r/flush_block_commit_notembedded.result +++ b/mysql-test/r/flush_block_commit_notembedded.result @@ -1,15 +1,23 @@ -create table t1 (a int) engine=innodb; -reset master; -set autocommit=0; -insert t1 values (1); -flush tables with read lock; -show master status; +# Establish connection con1 (user=root) +# Establish connection con2 (user=root) +# Switch to connection con1 +CREATE TABLE t1 (a INT) ENGINE=innodb; +RESET MASTER; +SET AUTOCOMMIT=0; +INSERT t1 VALUES (1); +# Switch to connection con2 +FLUSH TABLES WITH READ LOCK; +SHOW MASTER STATUS; File Position Binlog_Do_DB Binlog_Ignore_DB master-bin.000001 106 -commit; -show master status; +# Switch to connection con1 +COMMIT; +# Switch to connection con2 +SHOW MASTER STATUS; File Position Binlog_Do_DB Binlog_Ignore_DB master-bin.000001 106 -unlock tables; -drop table t1; -set autocommit=1; +UNLOCK TABLES; +# Switch to connection con1 +DROP TABLE t1; +SET AUTOCOMMIT=1; +# Switch to connection default and close connections con1 and con2 diff --git a/mysql-test/r/flush_read_lock_kill.result b/mysql-test/r/flush_read_lock_kill.result index 0b599f343f7..b16a8b114b3 100644 --- a/mysql-test/r/flush_read_lock_kill.result +++ b/mysql-test/r/flush_read_lock_kill.result @@ -1,12 +1,12 @@ -set @old_concurrent_insert= @@global.concurrent_insert; -set @@global.concurrent_insert= 0; -drop table if exists t1; -create table t1 (kill_id int); -insert into t1 values(connection_id()); -flush tables with read lock; -select ((@id := kill_id) - kill_id) from t1; +SET @old_concurrent_insert= @@global.concurrent_insert; +SET @@global.concurrent_insert= 0; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 (kill_id INT); +INSERT INTO t1 VALUES(connection_id()); +FLUSH TABLES WITH READ LOCK; +SELECT ((@id := kill_id) - kill_id) FROM t1; ((@id := kill_id) - kill_id) 0 -kill connection @id; -drop table t1; -set @@global.concurrent_insert= @old_concurrent_insert; +KILL CONNECTION @id; +DROP TABLE t1; +SET @@global.concurrent_insert= @old_concurrent_insert; diff --git a/mysql-test/r/lock_multi.result b/mysql-test/r/lock_multi.result index a0e3d9fad06..50e37d28dd6 100644 --- a/mysql-test/r/lock_multi.result +++ b/mysql-test/r/lock_multi.result @@ -51,10 +51,10 @@ Field Type Null Key Default Extra a int(11) YES NULL unlock tables; drop table t1; -use mysql; +USE mysql; LOCK TABLES columns_priv WRITE, db WRITE, host WRITE, user WRITE; FLUSH TABLES; -use mysql; +USE mysql; SELECT user.Select_priv FROM user, db WHERE user.user = db.user LIMIT 1; OPTIMIZE TABLES columns_priv, db, host, user; Table Op Msg_type Msg_text @@ -65,7 +65,7 @@ mysql.user optimize status OK UNLOCK TABLES; Select_priv N -use test; +USE test; use test; CREATE TABLE t1 (c1 int); LOCK TABLE t1 WRITE; @@ -133,8 +133,8 @@ DROP TABLE t1; End of 5.0 tests create table t1 (i int); lock table t1 read; -update t1 set i= 10;; -select * from t1;; +update t1 set i= 10; +select * from t1; kill query ID; i ERROR 70100: Query execution was interrupted diff --git a/mysql-test/r/mysqlbinlog.result b/mysql-test/r/mysqlbinlog.result index 1efa944bf9b..34d695a0272 100644 --- a/mysql-test/r/mysqlbinlog.result +++ b/mysql-test/r/mysqlbinlog.result @@ -391,9 +391,9 @@ DELIMITER ; ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; CREATE TABLE t1 (c1 CHAR(10)); -flush logs; +FLUSH LOGS; INSERT INTO t1 VALUES ('0123456789'); -flush logs; +FLUSH LOGS; DROP TABLE t1; We expect this value to be 1 The bug being tested was that 'Query' lines were not preceded by '#' @@ -403,16 +403,16 @@ SELECT COUNT(*) AS `BUG#28293_expect_1` FROM patch WHERE a LIKE '%Query%'; BUG#28293_expect_1 1 DROP TABLE patch; -flush logs; -create table t1(a int); -insert into t1 values(connection_id()); -flush logs; -drop table t1; +FLUSH LOGS; +CREATE TABLE t1(a INT); +INSERT INTO t1 VALUES(connection_id()); +FLUSH LOGS; +DROP TABLE t1; 1 -drop table t1; +DROP TABLE t1; shell> mysqlbinlog std_data/corrupt-relay-bin.000624 > var/tmp/bug31793.sql -flush logs; -BUG#31611: Security risk with BINLOG statement +FLUSH LOGS; +Bug#31611 Security risk with BINLOG statement SET BINLOG_FORMAT=ROW; CREATE DATABASE mysqltest1; CREATE USER untrusted@localhost; @@ -435,7 +435,7 @@ a b 1 root@localhost DROP DATABASE mysqltest1; DROP USER untrusted@localhost; -BUG#32580: mysqlbinlog cannot read binlog event with user variables +Bug#32580 mysqlbinlog cannot read binlog event with user variables USE test; SET BINLOG_FORMAT = STATEMENT; FLUSH LOGS; @@ -460,15 +460,15 @@ an_int 1000 a_decimal 907.79 a_string Just a test DROP TABLE t1; -set @@global.server_id= 4294967295; -reset master; -flush logs; -select -(@a:=load_file("MYSQLTEST_VARDIR/tmp/mysqlbinlog_bug37313.binlog")) -is not null; -(@a:=load_file("MYSQLTEST_VARDIR/tmp/mysqlbinlog_bug37313.binlog")) -is not null +SET @@global.server_id= 4294967295; +RESET MASTER; +FLUSH LOGS; +SELECT +(@a:=LOAD_FILE("MYSQLTEST_VARDIR/tmp/mysqlbinlog_bug37313.binlog")) +IS NOT NULL; +(@a:=LOAD_FILE("MYSQLTEST_VARDIR/tmp/mysqlbinlog_bug37313.binlog")) +IS NOT NULL 1 *** Unsigned server_id 4294967295 is found: 1 *** -set @@global.server_id= 1; +SET @@global.server_id= 1; End of 5.1 tests diff --git a/mysql-test/r/mysqltest.result b/mysql-test/r/mysqltest.result index a61f25058d9..a9c20e34517 100644 --- a/mysql-test/r/mysqltest.result +++ b/mysql-test/r/mysqltest.result @@ -743,10 +743,12 @@ select 1; -- a comment for the server; mysqltest: At line 1: Found line beginning with -- that didn't contain a valid mysqltest command, check your syntax or use # if you intended to write a comment con1 +con2 default con1 -default -con1 +con2 con1 +con2 +con2 -closed_connection- End of tests diff --git a/mysql-test/r/read_only.result b/mysql-test/r/read_only.result index 558e0356c5a..7b28da5a577 100644 --- a/mysql-test/r/read_only.result +++ b/mysql-test/r/read_only.result @@ -128,7 +128,7 @@ set global read_only=0; drop table t1,t2; drop user test@localhost; # -# Bug #27440 read_only allows create and drop database +# Bug#27440 read_only allows create and drop database # set global read_only= 1; drop database if exists mysqltest_db1; diff --git a/mysql-test/r/show_check.result b/mysql-test/r/show_check.result index d707ebaeba5..e6550bee954 100644 --- a/mysql-test/r/show_check.result +++ b/mysql-test/r/show_check.result @@ -545,9 +545,9 @@ mysqltest CREATE DATABASE `mysqltest` /*!40100 DEFAULT CHARACTER SET latin1 */ drop table mysqltest.t1; drop database mysqltest; set names binary; -delete from mysql.user +delete from mysql.user where user='mysqltest_1' || user='mysqltest_2' || user='mysqltest_3'; -delete from mysql.db +delete from mysql.db where user='mysqltest_1' || user='mysqltest_2' || user='mysqltest_3'; flush privileges; CREATE TABLE t1 (i int, KEY (i)) ENGINE=MEMORY; @@ -664,7 +664,7 @@ show create table t1; ERROR HY000: Incorrect information in file: './test/t1.frm' drop table if exists t1; -# Bug#12183: SHOW OPEN TABLES behavior doesn't match grammar. +# Bug#12183 SHOW OPEN TABLES behavior doesn't match grammar. DROP DATABASE IF EXISTS mysqltest1; CREATE DATABASE mysqltest1; @@ -784,7 +784,7 @@ show status like 'slow_queries'; Variable_name Value Slow_queries 1 create table t1 (a int); -create trigger tr1 before insert on t1 for each row +create trigger tr1 before insert on t1 for each row begin end; create view v1 as select a from t1; @@ -1010,7 +1010,7 @@ def TRIGGERS DATABASE_COLLATION Database Collation 253 96 17 N 1 0 33 Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation t1_bi INSERT t1 SET @a = 1 BEFORE NULL root@localhost binary binary latin1_swedish_ci ---------------------------------------------------------------- -SELECT +SELECT TRIGGER_CATALOG, TRIGGER_SCHEMA, TRIGGER_NAME, diff --git a/mysql-test/r/skip_name_resolve.result b/mysql-test/r/skip_name_resolve.result index 8ef52e75238..47741fed250 100644 --- a/mysql-test/r/skip_name_resolve.result +++ b/mysql-test/r/skip_name_resolve.result @@ -5,10 +5,10 @@ GRANT USAGE ON *.* TO 'mysqltest_1'@'127.0.0.1/255.255.255.255' GRANT ALL PRIVILEGES ON `test`.* TO 'mysqltest_1'@'127.0.0.1/255.255.255.255' REVOKE ALL ON test.* FROM mysqltest_1@'127.0.0.1/255.255.255.255'; DROP USER mysqltest_1@'127.0.0.1/255.255.255.255'; -select user(); -user() +SELECT USER(); +USER() # -show processlist; +SHOW PROCESSLIST; Id User Host db Command Time State Info <id> root <host> test <command> <time> <state> <info> <id> root <host> test <command> <time> <state> <info> diff --git a/mysql-test/r/sp-security.result b/mysql-test/r/sp-security.result index 66177395ac9..65c94577a57 100644 --- a/mysql-test/r/sp-security.result +++ b/mysql-test/r/sp-security.result @@ -342,12 +342,12 @@ GRANT SUPER ON *.* TO mysqltest_2@localhost; GRANT ALL PRIVILEGES ON mysqltest.* TO mysqltest_2@localhost; ---> connection: mysqltest_2_con -use mysqltest; +USE mysqltest; CREATE PROCEDURE wl2897_p1() SELECT 1; CREATE FUNCTION wl2897_f1() RETURNS INT RETURN 1; ---> connection: mysqltest_1_con -use mysqltest; +USE mysqltest; CREATE DEFINER=root@localhost PROCEDURE wl2897_p2() SELECT 2; ERROR 42000: Access denied; you need the SUPER privilege for this operation CREATE DEFINER=root@localhost FUNCTION wl2897_f2() RETURNS INT RETURN 2; @@ -363,7 +363,7 @@ Warnings: Note 1449 The user specified as a definer ('a @ b @ c'@'localhost') does not exist ---> connection: con1root -use mysqltest; +USE mysqltest; SHOW CREATE PROCEDURE wl2897_p1; Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation wl2897_p1 CREATE DEFINER=`mysqltest_2`@`localhost` PROCEDURE `wl2897_p1`() @@ -393,7 +393,7 @@ CREATE USER mysqltest_2@localhost; GRANT ALL PRIVILEGES ON mysqltest.* TO mysqltest_2@localhost; ---> connection: mysqltest_1_con -use mysqltest; +USE mysqltest; CREATE PROCEDURE bug13198_p1() SELECT 1; CREATE FUNCTION bug13198_f1() RETURNS INT @@ -406,7 +406,7 @@ bug13198_f1() 1 ---> connection: mysqltest_2_con -use mysqltest; +USE mysqltest; CALL bug13198_p1(); 1 1 @@ -418,7 +418,7 @@ bug13198_f1() DROP USER mysqltest_1@localhost; ---> connection: mysqltest_2_con -use mysqltest; +USE mysqltest; CALL bug13198_p1(); ERROR HY000: The user specified as a definer ('mysqltest_1'@'localhost') does not exist SELECT bug13198_f1(); @@ -435,7 +435,7 @@ Host User Password localhost user19857 *82DC221D557298F6CE9961037DB1C90604792F5C ---> connection: mysqltest_2_con -use test; +USE test; CREATE PROCEDURE sp19857() DETERMINISTIC BEGIN DECLARE a INT; diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index 38d05ae84fe..0905fc0109b 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -1673,9 +1673,9 @@ INSERT INTO t2 VALUES (4,3,'n'); INSERT INTO t2 VALUES (6,1,'n'); INSERT INTO t2 VALUES (8,1,'y'); CREATE VIEW v1 AS SELECT * FROM t1; -SELECT a.col1,a.col2,b.col2,b.col3 +SELECT a.col1,a.col2,b.col2,b.col3 FROM t1 a LEFT JOIN t2 b ON a.col1=b.col1 -WHERE b.col2 IS NULL OR +WHERE b.col2 IS NULL OR b.col2=(SELECT MAX(col2) FROM t2 b WHERE b.col1=a.col1); col1 col2 col2 col3 1 trudy 2 y @@ -1687,9 +1687,9 @@ col1 col2 col2 col3 7 carsten NULL NULL 8 ranger 1 y 10 matt NULL NULL -SELECT a.col1,a.col2,b.col2,b.col3 +SELECT a.col1,a.col2,b.col2,b.col3 FROM v1 a LEFT JOIN t2 b ON a.col1=b.col1 -WHERE b.col2 IS NULL OR +WHERE b.col2 IS NULL OR b.col2=(SELECT MAX(col2) FROM t2 b WHERE b.col1=a.col1); col1 col2 col2 col3 1 trudy 2 y @@ -1743,7 +1743,7 @@ A A 2 2 3 3 create table t3 as select a a,a b from t2; -create view v2 as select * from t3 where +create view v2 as select * from t3 where a in (select * from t1) or b in (select * from t2); select * from v2 A, v2 B where A.a = B.b; a b a b @@ -1999,7 +1999,7 @@ dkjhgd drop view v1; create table t1 (f59 int, f60 int, f61 int); insert into t1 values (19,41,32); -create view v1 as select f59, f60 from t1 where f59 in +create view v1 as select f59, f60 from t1 where f59 in (select f59 from t1); update v1 set f60=2345; ERROR HY000: The target table v1 of the UPDATE is not updatable @@ -2115,7 +2115,7 @@ pid int NOT NULL INSERT INTO t1 VALUES(1,'a','b'), (2,'c','d'); INSERT INTO t2 values (1,1), (2,1), (2,2); CREATE VIEW v1 AS SELECT t1.*,t2.pid FROM t1,t2 WHERE t1.aid = t2.aid; -SELECT pid,GROUP_CONCAT(CONCAT(fn,' ',ln) ORDER BY 1) FROM t1,t2 +SELECT pid,GROUP_CONCAT(CONCAT(fn,' ',ln) ORDER BY 1) FROM t1,t2 WHERE t1.aid = t2.aid GROUP BY pid; pid GROUP_CONCAT(CONCAT(fn,' ',ln) ORDER BY 1) 1 a b,c d @@ -2217,7 +2217,7 @@ group_name varchar(32) NOT NULL ) engine = InnoDB; create table t2 ( r_object_id char(16) NOT NULL, -i_position int(11) NOT NULL, +i_position int(11) NOT NULL, users_names varchar(32) default NULL ) Engine = InnoDB; create view v1 as select r_object_id, group_name from t1; @@ -2230,7 +2230,7 @@ insert into t1 values('120001a080000542','tstgroup1'); insert into t2 values('120001a080000542',-1, 'guser01'); insert into t2 values('120001a080000542',-2, 'guser02'); select v1.r_object_id, v2.users_names from v1, v2 -where (v1.group_name='tstgroup1') and v2.r_object_id=v1.r_object_id +where (v1.group_name='tstgroup1') and v2.r_object_id=v1.r_object_id order by users_names; r_object_id users_names 120001a080000542 guser01 @@ -2380,8 +2380,8 @@ create table t4 (x int, y int, z int); create view v1 as select t1.x from ( -(t1 join t2 on ((t1.y = t2.y))) -join +(t1 join t2 on ((t1.y = t2.y))) +join (t3 left join t4 on (t3.y = t4.y) and (t3.z = t4.z)) ); prepare stmt1 from "select count(*) from v1 where x = ?"; @@ -2557,12 +2557,12 @@ Warnings: Warning 1052 Column 'x' in group statement is ambiguous DROP VIEW v1; DROP TABLE t1; -drop table if exists t1; -drop view if exists v1; -create table t1 (id int); -create view v1 as select * from t1; -drop table t1; -show create view v1; +drop table if exists t1; +drop view if exists v1; +create table t1 (id int); +create view v1 as select * from t1; +drop table t1; +show create view v1; drop view v1; // View Create View character_set_client collation_connection @@ -2609,7 +2609,7 @@ DROP VIEW v2; DROP TABLE t1, t2; CREATE TABLE t1 (id int NOT NULL PRIMARY KEY, td date DEFAULT NULL, KEY idx(td)); -INSERT INTO t1 VALUES +INSERT INTO t1 VALUES (1, '2005-01-01'), (2, '2005-01-02'), (3, '2005-01-02'), (4, '2005-01-03'), (5, '2005-01-04'), (6, '2005-01-05'), (7, '2005-01-05'), (8, '2005-01-05'), (9, '2005-01-06'); @@ -2973,10 +2973,10 @@ drop view v1; drop table t1; CREATE TABLE t1(pk int PRIMARY KEY); CREATE TABLE t2(pk int PRIMARY KEY, fk int, ver int, org int); -CREATE ALGORITHM=MERGE VIEW v1 AS +CREATE ALGORITHM=MERGE VIEW v1 AS SELECT t1.* -FROM t1 JOIN t2 -ON t2.fk = t1.pk AND +FROM t1 JOIN t2 +ON t2.fk = t1.pk AND t2.ver = (SELECT MAX(t.ver) FROM t2 t WHERE t.org = t2.org); SHOW WARNINGS; Level Code Message @@ -3306,7 +3306,7 @@ name char(10) NOT NULL INSERT INTO t1 (lid, name) VALUES (1, 'YES'), (2, 'NO'); CREATE TABLE t2 ( -id int NOT NULL PRIMARY KEY, +id int NOT NULL PRIMARY KEY, gid int NOT NULL, lid int NOT NULL, dt date @@ -3405,8 +3405,8 @@ CREATE TABLE t1 (id int); CREATE TABLE t2 (id int, c int DEFAULT 0); INSERT INTO t1 (id) VALUES (1); INSERT INTO t2 (id) VALUES (1); -CREATE VIEW v1 AS -SELECT t2.c FROM t1, t2 +CREATE VIEW v1 AS +SELECT t2.c FROM t1, t2 WHERE t1.id=t2.id AND 1 IN (SELECT id FROM t1) WITH CHECK OPTION; UPDATE v1 SET c=1; DROP VIEW v1; @@ -3514,7 +3514,7 @@ role_name varchar(100) default NULL, app_name varchar(40) NOT NULL, INDEX idx_app_name(app_name) ); -CREATE VIEW v1 AS +CREATE VIEW v1 AS SELECT profile.person_id AS person_id FROM t1 profile, t2 userrole, t3 role WHERE userrole.person_id = profile.person_id AND @@ -3526,7 +3526,7 @@ INSERT INTO t1 VALUES (-717462680,'ENTS Ta','0'), (-904346964,'ndard SQL\n','0'); INSERT INTO t2 VALUES (1,3,6),(2,4,7),(3,5,8),(4,6,9),(5,1,6),(6,1,7),(7,1,8),(8,1,9),(9,1,10); -INSERT INTO t3 VALUES +INSERT INTO t3 VALUES (1,'NUCANS_APP_USER','NUCANSAPP'),(2,'NUCANS_TRGAPP_USER','NUCANSAPP'), (3,'IA_INTAKE_COORDINATOR','IACANS'),(4,'IA_SCREENER','IACANS'), (5,'IA_SUPERVISOR','IACANS'),(6,'IA_READONLY','IACANS'), @@ -3552,7 +3552,7 @@ i 2 3 4 -select table_name, is_updatable from information_schema.views +select table_name, is_updatable from information_schema.views where table_name = 'v1'; table_name is_updatable v1 NO @@ -3598,8 +3598,8 @@ DROP VIEW v2; DROP VIEW v3; DROP TABLE t1; # -# Bug#29477: Not all fields of the target table were checked to have -# a default value when inserting into a view. +# Bug#29477 Not all fields of the target table were checked to have +# a default value when inserting into a view. # create table t1(f1 int, f2 int not null); create view v1 as select f1 from t1; @@ -3616,7 +3616,7 @@ drop table t1; create table t1 (a int, key(a)); create table t2 (c int); create view v1 as select a b from t1; -create view v2 as select 1 a from t2, v1 where c in +create view v2 as select 1 a from t2, v1 where c in (select 1 from t1 where b = a); insert into t1 values (1), (1); insert into t2 values (1), (1); @@ -3638,7 +3638,7 @@ MAX(a) COUNT(DISTINCT a) DROP VIEW v1; DROP TABLE t1; # ----------------------------------------------------------------- -# -- Bug#34337: Server crash when Altering a view using a table name. +# -- Bug#34337 Server crash when Altering a view using a table name. # ----------------------------------------------------------------- DROP TABLE IF EXISTS t1; @@ -3655,8 +3655,8 @@ DROP TABLE t1; # -- End of test case for Bug#34337. # ----------------------------------------------------------------- -# -- Bug#35193: VIEW query is rewritten without "FROM DUAL", -# -- causing syntax error +# -- Bug#35193 VIEW query is rewritten without "FROM DUAL", +# -- causing syntax error # ----------------------------------------------------------------- CREATE VIEW v1 AS SELECT 1 FROM DUAL WHERE 1; @@ -3723,7 +3723,7 @@ DROP DATABASE `d-1`; USE test; # -# Bug#26676: VIEW using old table schema in a session. +# Bug#26676 VIEW using old table schema in a session. # DROP VIEW IF EXISTS v1; @@ -3765,7 +3765,7 @@ DROP TABLE t1; # End of test case for Bug#26676. # ----------------------------------------------------------------- -# -- Bug#32538: View definition picks up character set, but not collation +# -- Bug#32538 View definition picks up character set, but not collation # ----------------------------------------------------------------- DROP VIEW IF EXISTS v1; diff --git a/mysql-test/r/view_grant.result b/mysql-test/r/view_grant.result index 1ae94926928..7e280fa2fe5 100644 --- a/mysql-test/r/view_grant.result +++ b/mysql-test/r/view_grant.result @@ -646,7 +646,7 @@ CREATE DATABASE test2; CREATE TABLE test1.t0 (a VARCHAR(20)); CREATE TABLE test2.t1 (a VARCHAR(20)); CREATE VIEW test2.t3 AS SELECT * FROM test1.t0; -CREATE OR REPLACE VIEW test.v1 AS +CREATE OR REPLACE VIEW test.v1 AS SELECT ta.a AS col1, tb.a AS col2 FROM test2.t3 ta, test2.t1 tb; DROP VIEW test.v1; DROP VIEW test2.t3; @@ -790,7 +790,7 @@ v3 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VI DROP USER u26813@localhost; DROP DATABASE db26813; # -# Bug#29908: A user can gain additional access through the ALTER VIEW. +# Bug#29908 A user can gain additional access through the ALTER VIEW. # CREATE DATABASE mysqltest_29908; USE mysqltest_29908; @@ -1043,3 +1043,4 @@ DROP VIEW v1, v2; DROP DATABASE mysqltest1; DROP VIEW test.v3; DROP USER mysqluser1@localhost; +USE test; diff --git a/mysql-test/t/alter_table-big.test b/mysql-test/t/alter_table-big.test index 5d2c0ba0bb6..1dcc1f1c9bd 100644 --- a/mysql-test/t/alter_table-big.test +++ b/mysql-test/t/alter_table-big.test @@ -9,10 +9,9 @@ # in proper order. --source include/have_binlog_format_mixed_or_statement.inc - # -# Test for bug #25044 "ALTER TABLE ... ENABLE KEYS acquires global -# 'opening tables' lock". +# Test for Bug#25044 ALTER TABLE ... ENABLE KEYS acquires global +# 'opening tables' lock # # ALTER TABLE ... ENABLE KEYS should not acquire LOCK_open mutex for # the whole its duration as it prevents other queries from execution. @@ -56,6 +55,7 @@ show binlog events in 'master-bin.000001' from 106; # Clean up drop tables t1, t2; +disconnect addconroot; --echo End of 5.0 tests @@ -76,6 +76,7 @@ create table t1 (i int); reset master; set session debug="+d,sleep_alter_before_main_binlog"; --send alter table t1 change i c char(10) default 'Test1'; +connect (addconroot, localhost, root,,); connection addconroot; --sleep 2 insert into t1 values (); @@ -105,6 +106,7 @@ connection addconroot; rename table t1 to t3; connection default; --reap +disconnect addconroot; drop table t3; set session debug="-d,sleep_alter_before_main_binlog"; @@ -114,3 +116,4 @@ show binlog events in 'master-bin.000001' from 106; --echo End of 5.1 tests + diff --git a/mysql-test/t/connect.test b/mysql-test/t/connect.test index 0893bf9ad18..f609240646a 100644 --- a/mysql-test/t/connect.test +++ b/mysql-test/t/connect.test @@ -9,11 +9,14 @@ # of the log tables (which are CSV-based). By connect mysql; show tables; --source include/have_csv.inc +# Save the initial number of concurrent sessions +--source include/count_sessions.inc + + --disable_warnings drop table if exists t1,t2; --enable_warnings - #connect (con1,localhost,root,,""); #show tables; connect (con1,localhost,root,,mysql); @@ -22,12 +25,16 @@ connect (con2,localhost,root,,test); show tables; --replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT ---error 1045 +--error ER_ACCESS_DENIED_ERROR connect (fail_con,localhost,root,z,test2); --replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT ---error 1045 +--error ER_ACCESS_DENIED_ERROR connect (fail_con,localhost,root,z,); +connection default; +disconnect con1; +disconnect con2; + grant ALL on *.* to test@localhost identified by "gambling"; grant ALL on *.* to test@127.0.0.1 identified by "gambling"; @@ -39,20 +46,23 @@ show tables; connect (con4,localhost,test,gambling,test); show tables; +connection default; +disconnect con3; +disconnect con4; + --replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT ---error 1045 +--error ER_ACCESS_DENIED_ERROR connect (fail_con,localhost,test,,test2); --replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT ---error 1045 +--error ER_ACCESS_DENIED_ERROR connect (fail_con,localhost,test,,""); --replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT ---error 1045 +--error ER_ACCESS_DENIED_ERROR connect (fail_con,localhost,test,zorro,test2); --replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT ---error 1045 +--error ER_ACCESS_DENIED_ERROR connect (fail_con,localhost,test,zorro,); - # check if old password version also works update mysql.user set password=old_password("gambling2") where user=_binary"test"; flush privileges; @@ -61,55 +71,57 @@ connect (con10,localhost,test,gambling2,); connect (con5,localhost,test,gambling2,mysql); connection con5; set password=""; ---error 1372 +--error ER_PASSWD_LENGTH set password='gambling3'; set password=old_password('gambling3'); show tables; connect (con6,localhost,test,gambling3,test); show tables; +connection default; +disconnect con10; +disconnect con5; +disconnect con6; + --replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT ---error 1045 +--error ER_ACCESS_DENIED_ERROR connect (fail_con,localhost,test,,test2); --replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT ---error 1045 +--error ER_ACCESS_DENIED_ERROR connect (fail_con,localhost,test,,); --replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT ---error 1045 +--error ER_ACCESS_DENIED_ERROR connect (fail_con,localhost,test,zorro,test2); --replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT ---error 1045 +--error ER_ACCESS_DENIED_ERROR connect (fail_con,localhost,test,zorro,); # remove user 'test' so that other tests which may use 'test' # do not depend on this test. - delete from mysql.user where user=_binary"test"; flush privileges; # -# Bug#12517: Clear user variables and replication events before -# closing temp tables in thread cleanup. +# Bug#12517 Clear user variables and replication events before +# closing temp tables in thread cleanup. connect (con7,localhost,root,,test); connection con7; +let $connection_id= `select connection_id()`; create table t1 (id integer not null auto_increment primary key); create temporary table t2(id integer not null auto_increment primary key); set @id := 1; delete from t1 where id like @id; -disconnect con7; ---sleep 5 connection default; +disconnect con7; +# Wait till the session con7 is disconnected +let $wait_condition = + SELECT COUNT(*) = 0 + FROM information_schema.processlist + WHERE id = '$connection_id'; +--source include/wait_condition.inc drop table t1; ---disconnect con1 ---disconnect con2 ---disconnect con3 ---disconnect con4 ---disconnect con5 ---disconnect con6 ---disconnect con10 - --echo # ------------------------------------------------------------------ --echo # -- End of 4.1 tests --echo # ------------------------------------------------------------------ @@ -147,7 +159,7 @@ let $wait_condition = --echo # -- Check that we allow exactly three user connections, no matter how --echo # -- many threads are running. ---echo +--echo --echo # -- Connecting (1)... let $con_name = con_1; let $con_user_name = mysqltest_u1; @@ -237,7 +249,7 @@ DROP USER mysqltest_u1@localhost; ########################################################################### ---echo # -- Bug#35074: max_used_connections is not correct. +--echo # -- Bug#35074: max_used_connections is not correct. --echo FLUSH STATUS; @@ -250,11 +262,7 @@ SHOW STATUS LIKE 'max_used_connections'; SET GLOBAL event_scheduler = ON; --echo # -- Waiting for Event Scheduler to start... -let $wait_condition = - SELECT COUNT(*) = 1 - FROM information_schema.processlist - WHERE user = 'event_scheduler'; ---source include/wait_condition.inc +--source include/running_event_scheduler.inc # NOTE: We should use a new connection here instead of reconnect in order to # avoid races (we can not for sure when the connection being disconnected is @@ -278,11 +286,7 @@ SHOW STATUS LIKE 'max_used_connections'; SET GLOBAL event_scheduler = OFF; --echo # -- Waiting for Event Scheduler to stop... -let $wait_condition = - SELECT COUNT(*) = 0 - FROM information_schema.processlist - WHERE user = 'event_scheduler'; ---source include/wait_condition.inc +--source include/no_running_events.inc --echo --echo # -- End of Bug#35074. @@ -291,3 +295,7 @@ let $wait_condition = --echo # ------------------------------------------------------------------ --echo # -- End of 5.1 tests --echo # ------------------------------------------------------------------ + +# Wait till all disconnects are completed +--source include/wait_until_count_sessions.inc + diff --git a/mysql-test/t/consistent_snapshot.test b/mysql-test/t/consistent_snapshot.test index 8da8e9ce660..82edf2e22b2 100644 --- a/mysql-test/t/consistent_snapshot.test +++ b/mysql-test/t/consistent_snapshot.test @@ -1,43 +1,61 @@ --- source include/have_innodb.inc +--source include/have_innodb.inc + +# Save the initial number of concurrent sessions +--source include/count_sessions.inc --disable_warnings -drop table if exists t1; +DROP TABLE IF EXISTS t1; --enable_warnings +--echo # Establish connection con1 (user=root) connect (con1,localhost,root,,); +--echo # Establish connection con2 (user=root) connect (con2,localhost,root,,); ### Test 1: ### - While a consistent snapshot transaction is executed, ### no external inserts should be visible to the transaction. +--echo # Switch to connection con1 connection con1; -create table t1 (a int) engine=innodb; -start transaction with consistent snapshot; +CREATE TABLE t1 (a INT) ENGINE=innodb; +START TRANSACTION WITH CONSISTENT SNAPSHOT; +--echo # Switch to connection con2 connection con2; -insert into t1 values(1); +INSERT INTO t1 VALUES(1); +--echo # Switch to connection con1 connection con1; -select * from t1; # if consistent snapshot was set as expected, we +SELECT * FROM t1; # if consistent snapshot was set as expected, we # should see nothing. -commit; +COMMIT; ### Test 2: ### - For any non-consistent snapshot transaction, external ### committed inserts should be visible to the transaction. -delete from t1; -start transaction; # Now we omit WITH CONSISTENT SNAPSHOT +DELETE FROM t1; +START TRANSACTION; # Now we omit WITH CONSISTENT SNAPSHOT +--echo # Switch to connection con2 connection con2; -insert into t1 values(1); +INSERT INTO t1 VALUES(1); +--echo # Switch to connection con1 connection con1; -select * from t1; # if consistent snapshot was not set, as expected, we +SELECT * FROM t1; # if consistent snapshot was not set, as expected, we # should see 1. -commit; +COMMIT; -drop table t1; +--echo # Switch to connection default + close connections con1 and con2 +connection default; +disconnect con1; +disconnect con2; +DROP TABLE t1; # End of 4.1 tests + +# Wait till all disconnects are completed +--source include/wait_until_count_sessions.inc + diff --git a/mysql-test/t/dirty_close.test b/mysql-test/t/dirty_close.test index f1c2c88ae83..1bbd53e8c06 100644 --- a/mysql-test/t/dirty_close.test +++ b/mysql-test/t/dirty_close.test @@ -1,3 +1,7 @@ + +# Save the initial number of concurrent sessions +--source include/count_sessions.inc + connect (con1,localhost,root,,); connect (con2,localhost,root,,); connection con1; @@ -5,12 +9,19 @@ dirty_close con1; connection con2; --disable_warnings -drop table if exists t1; +DROP TABLE IF EXISTS t1; --enable_warnings -create table t1 (n int); -insert into t1 values (1),(2),(3); -select * from t1; -drop table t1; +CREATE TABLE t1 (n INT); +INSERT INTO t1 VALUES (1),(2),(3); +SELECT * FROM t1; +DROP TABLE t1; + +connection default; +disconnect con2; # End of 4.1 tests + +# Wait till all disconnects are completed +--source include/wait_until_count_sessions.inc + diff --git a/mysql-test/t/flush_block_commit.test b/mysql-test/t/flush_block_commit.test index 0c1d2b82df6..74892def63f 100644 --- a/mysql-test/t/flush_block_commit.test +++ b/mysql-test/t/flush_block_commit.test @@ -4,74 +4,106 @@ # This is intended to mimick how mysqldump and innobackup work. # And it requires InnoDB --- source include/have_innodb.inc +--source include/have_innodb.inc +# Save the initial number of concurrent sessions +--source include/count_sessions.inc + +--echo # Establish connection con1 (user=root) connect (con1,localhost,root,,); +--echo # Establish connection con2 (user=root) connect (con2,localhost,root,,); +--echo # Establish connection con3 (user=root) connect (con3,localhost,root,,); +--echo # Switch to connection con1 connection con1; --disable_warnings -drop table if exists t1; +DROP TABLE IF EXISTS t1; --enable_warnings -create table t1 (a int) engine=innodb; +CREATE TABLE t1 (a INT) ENGINE=innodb; # blocks COMMIT ? -begin; -insert into t1 values(1); +BEGIN; +INSERT INTO t1 VALUES(1); +--echo # Switch to connection con2 connection con2; -flush tables with read lock; -select * from t1; +FLUSH TABLES WITH READ LOCK; +SELECT * FROM t1; +--echo # Switch to connection con1 connection con1; -send commit; # blocked by con2 +send COMMIT; # blocked by con2 sleep 1; +--echo # Switch to connection con2 connection con2; -select * from t1; # verify con1 was blocked and data did not move -unlock tables; +SELECT * FROM t1; # verify con1 was blocked and data did not move +UNLOCK TABLES; +--echo # Switch to connection con1 connection con1; reap; # No deadlock ? +--echo # Switch to connection con1 connection con1; -begin; -select * from t1 for update; +BEGIN; +SELECT * FROM t1 FOR UPDATE; +--echo # Switch to connection con2 connection con2; -begin; -send select * from t1 for update; # blocked by con1 +BEGIN; +send SELECT * FROM t1 FOR UPDATE; # blocked by con1 sleep 1; +--echo # Switch to connection con3 connection con3; -send flush tables with read lock; # blocked by con2 +send FLUSH TABLES WITH READ LOCK; # blocked by con2 +--echo # Switch to connection con1 connection con1; -commit; # should not be blocked by con3 +COMMIT; # should not be blocked by con3 +--echo # Switch to connection con2 connection con2; reap; +--echo # Switch to connection con3 connection con3; reap; -unlock tables; +UNLOCK TABLES; -# BUG#6732 FLUSH TABLES WITH READ LOCK + COMMIT hangs later FLUSH TABLES -# WITH READ LOCK +# Bug#6732 FLUSH TABLES WITH READ LOCK + COMMIT hangs later FLUSH TABLES +# WITH READ LOCK +--echo # Switch to connection con2 connection con2; -commit; # unlock InnoDB row locks to allow insertions +COMMIT; # unlock InnoDB row locks to allow insertions +--echo # Switch to connection con1 connection con1; -begin; -insert into t1 values(10); -flush tables with read lock; -commit; -unlock tables; +BEGIN; +INSERT INTO t1 VALUES(10); +FLUSH TABLES WITH READ LOCK; +COMMIT; +UNLOCK TABLES; +--echo # Switch to connection con2 connection con2; -flush tables with read lock; # bug caused hang here -unlock tables; +FLUSH TABLES WITH READ LOCK; # bug caused hang here +UNLOCK TABLES; + +# Bug#7358 SHOW CREATE DATABASE fails if open transaction + +BEGIN; +SELECT * FROM t1; +SHOW CREATE DATABASE test; -# BUG#7358 SHOW CREATE DATABASE fails if open transaction +DROP TABLE t1; -begin; -select * from t1; -show create database test; -drop table t1; +# Cleanup +--echo # Switch to connection default and close connections con1, con2, con3 +connection default; +disconnect con1; +disconnect con2; +disconnect con3; # End of 4.1 tests + +# Wait till all disconnects are completed +--source include/wait_until_count_sessions.inc + diff --git a/mysql-test/t/flush_block_commit_notembedded.test b/mysql-test/t/flush_block_commit_notembedded.test index 4a0300acf78..aea38250218 100644 --- a/mysql-test/t/flush_block_commit_notembedded.test +++ b/mysql-test/t/flush_block_commit_notembedded.test @@ -3,32 +3,51 @@ # We verify that we did not introduce a deadlock. # This is intended to mimick how mysqldump and innobackup work. --- source include/have_log_bin.inc +--source include/have_log_bin.inc # And it requires InnoDB --- source include/have_log_bin.inc --- source include/have_innodb.inc +--source include/have_log_bin.inc +--source include/have_innodb.inc +# Save the initial number of concurrent sessions +--source include/count_sessions.inc + + +--echo # Establish connection con1 (user=root) connect (con1,localhost,root,,); +--echo # Establish connection con2 (user=root) connect (con2,localhost,root,,); # FLUSH TABLES WITH READ LOCK should block writes to binlog too +--echo # Switch to connection con1 connection con1; -create table t1 (a int) engine=innodb; -reset master; -set autocommit=0; -insert t1 values (1); +CREATE TABLE t1 (a INT) ENGINE=innodb; +RESET MASTER; +SET AUTOCOMMIT=0; +INSERT t1 VALUES (1); +--echo # Switch to connection con2 connection con2; -flush tables with read lock; -show master status; +FLUSH TABLES WITH READ LOCK; +SHOW MASTER STATUS; +--echo # Switch to connection con1 connection con1; -send commit; +send COMMIT; +--echo # Switch to connection con2 connection con2; sleep 1; -show master status; -unlock tables; +SHOW MASTER STATUS; +UNLOCK TABLES; +--echo # Switch to connection con1 connection con1; reap; -drop table t1; -set autocommit=1; +DROP TABLE t1; +SET AUTOCOMMIT=1; + +--echo # Switch to connection default and close connections con1 and con2 +connection default; +disconnect con1; +disconnect con2; + +# Wait till all disconnects are completed +--source include/wait_until_count_sessions.inc diff --git a/mysql-test/t/flush_read_lock_kill.test b/mysql-test/t/flush_read_lock_kill.test index c03f3be2534..aeb09d52460 100644 --- a/mysql-test/t/flush_read_lock_kill.test +++ b/mysql-test/t/flush_read_lock_kill.test @@ -8,24 +8,27 @@ # won't test anything interesting). # This also won't work with the embedded server test --- source include/not_embedded.inc +--source include/not_embedded.inc --- source include/have_debug.inc +--source include/have_debug.inc + +# Save the initial number of concurrent sessions +--source include/count_sessions.inc # Disable concurrent inserts to avoid test failures when reading the # connection id which was inserted into a table by another thread. -set @old_concurrent_insert= @@global.concurrent_insert; -set @@global.concurrent_insert= 0; +SET @old_concurrent_insert= @@global.concurrent_insert; +SET @@global.concurrent_insert= 0; connect (con1,localhost,root,,); connect (con2,localhost,root,,); connection con1; --disable_warnings -drop table if exists t1; +DROP TABLE IF EXISTS t1; --enable_warnings -create table t1 (kill_id int); -insert into t1 values(connection_id()); +CREATE TABLE t1 (kill_id INT); +INSERT INTO t1 VALUES(connection_id()); # Thanks to the parameter we passed to --debug, this FLUSH will # block on a debug build running with our --debug=make_global... It @@ -33,11 +36,11 @@ insert into t1 values(connection_id()); # --debug) it will succeed immediately connection con1; -send flush tables with read lock; +send FLUSH TABLES WITH READ LOCK; # kill con1 connection con2; -select ((@id := kill_id) - kill_id) from t1; +SELECT ((@id := kill_id) - kill_id) FROM t1; # Wait for the debug sync point, test won't run on non-debug # builds anyway. @@ -47,7 +50,7 @@ let $wait_condition= and info = "flush tables with read lock"; --source include/wait_condition.inc -kill connection @id; +KILL CONNECTION @id; connection con1; # On debug builds it will be error 1053 (killed); on non-debug, or @@ -58,8 +61,14 @@ connection con1; reap; connection con2; -drop table t1; +DROP TABLE t1; + connection default; +disconnect con2; # Restore global concurrent_insert value -set @@global.concurrent_insert= @old_concurrent_insert; +SET @@global.concurrent_insert= @old_concurrent_insert; + +# Wait till all disconnects are completed +--source include/wait_until_count_sessions.inc + diff --git a/mysql-test/t/init_connect.test b/mysql-test/t/init_connect.test index 0a08559279c..b6bac5f65fa 100644 --- a/mysql-test/t/init_connect.test +++ b/mysql-test/t/init_connect.test @@ -5,6 +5,9 @@ # should work with embedded server after mysqltest is fixed --source include/not_embedded.inc +# Save the initial number of concurrent sessions +--source include/count_sessions.inc + --source include/add_anonymous_users.inc connect (con0,localhost,root,,); @@ -233,7 +236,8 @@ connect (con1,localhost,mysqltest1,,); connection con1; select * from t1; -connection con0; +connection default; +disconnect con0; disconnect con1; drop trigger trg1; @@ -244,3 +248,7 @@ set global init_connect="set @a='a\\0c'"; revoke all privileges, grant option from mysqltest1@localhost; drop user mysqltest1@localhost; drop table t1, t2; + +# Wait till all disconnects are completed +--source include/wait_until_count_sessions.inc + diff --git a/mysql-test/t/lock_multi.test b/mysql-test/t/lock_multi.test index 08747b0f92e..586cfb174be 100644 --- a/mysql-test/t/lock_multi.test +++ b/mysql-test/t/lock_multi.test @@ -1,4 +1,8 @@ -- source include/not_embedded.inc + +# Save the initial number of concurrent sessions +--source include/count_sessions.inc + --disable_warnings drop table if exists t1,t2; --enable_warnings @@ -14,14 +18,18 @@ create table t1(n int); insert into t1 values (1); lock tables t1 write; connection writer; -send update low_priority t1 set n = 4; +send +update low_priority t1 set n = 4; connection reader; +# Sleep a bit till the update of connection writer is in work and hangs let $wait_condition= select count(*) = 1 from information_schema.processlist where state = "Locked" and info = "update low_priority t1 set n = 4"; --source include/wait_condition.inc -send select n from t1; +send +select n from t1; connection locker; +# Sleep a bit till the select of connection reader is in work and hangs let $wait_condition= select count(*) = 1 from information_schema.processlist where state = "Locked" and info = "select n from t1"; @@ -38,8 +46,10 @@ create table t1(n int); insert into t1 values (1); lock tables t1 read; connection writer; -send update low_priority t1 set n = 4; +send +update low_priority t1 set n = 4; connection reader; +# Sleep a bit till the update of connection writer is in work and hangs let $wait_condition= select count(*) = 1 from information_schema.processlist where state = "Locked" and info = "update low_priority t1 set n = 4"; @@ -72,7 +82,7 @@ drop table t1; drop table t2; # -# Test problem when using locks on many tables and droping a table that +# Test problem when using locks on many tables and dropping a table that # is to-be-locked by another thread # # @@ -81,7 +91,8 @@ create table t1 (a int); create table t2 (a int); lock table t1 write, t2 write; connection reader; -send insert t1 select * from t2; +send +insert t1 select * from t2; connection locker; let $wait_condition= select count(*) = 1 from information_schema.processlist @@ -89,7 +100,7 @@ let $wait_condition= --source include/wait_condition.inc drop table t2; connection reader; ---error 1146 +--error ER_NO_SUCH_TABLE reap; connection locker; drop table t1; @@ -103,15 +114,17 @@ create table t1 (a int); create table t2 (a int); lock table t1 write, t2 write, t1 as t1_2 write, t2 as t2_2 write; connection reader; -send insert t1 select * from t2; +send +insert t1 select * from t2; connection locker; +# Sleep a bit till the insert of connection reader is in work and hangs let $wait_condition= select count(*) = 1 from information_schema.processlist where state = "Locked" and info = "insert t1 select * from t2"; --source include/wait_condition.inc drop table t2; connection reader; ---error 1146 +--error ER_NO_SUCH_TABLE reap; connection locker; drop table t1; @@ -120,7 +133,7 @@ drop table t1; --echo End of 4.1 tests # -# BUG#9998 - MySQL client hangs on USE "database" +# Bug#9998 MySQL client hangs on USE "database" # create table t1(a int); lock tables t1 write; @@ -131,26 +144,28 @@ unlock tables; drop table t1; # -# Bug#16986 - Deadlock condition with MyISAM tables +# Bug#16986 Deadlock condition with MyISAM tables # # Need a matching user in mysql.user for multi-table select --source include/add_anonymous_users.inc connection locker; -use mysql; +USE mysql; LOCK TABLES columns_priv WRITE, db WRITE, host WRITE, user WRITE; FLUSH TABLES; # connection reader; -use mysql; -#NOTE: This must be a multi-table select, otherwise the deadlock will not occur -send SELECT user.Select_priv FROM user, db WHERE user.user = db.user LIMIT 1; +USE mysql; +# Note: This must be a multi-table select, otherwise the deadlock will not occur +send +SELECT user.Select_priv FROM user, db WHERE user.user = db.user LIMIT 1; # connection locker; +# Sleep a bit till the select of connection reader is in work and hangs let $wait_condition= select count(*) = 1 from information_schema.processlist - where state = "Waiting for table" and info = + where state = "Waiting for table" and info = "SELECT user.Select_priv FROM user, db WHERE user.user = db.user LIMIT 1"; --source include/wait_condition.inc # Make test case independent from earlier grants. @@ -160,7 +175,7 @@ UNLOCK TABLES; # connection reader; reap; -use test; +USE test; # connection locker; use test; @@ -175,9 +190,11 @@ LOCK TABLE t1 WRITE; # # This waits until t1 is unlocked. connection locker; -send FLUSH TABLES WITH READ LOCK; +send +FLUSH TABLES WITH READ LOCK; # connection writer; +# Sleep a bit till the flush of connection locker is in work and hangs let $wait_condition= select count(*) = 1 from information_schema.processlist where state = "Flushing tables" and info = "FLUSH TABLES WITH READ LOCK"; @@ -202,15 +219,17 @@ LOCK TABLE t1 WRITE; # # This waits until t1 is unlocked. connection locker; -send FLUSH TABLES WITH READ LOCK; +send +FLUSH TABLES WITH READ LOCK; # # This must not block. connection writer; +# Sleep a bit till the flush of connection locker is in work and hangs let $wait_condition= select count(*) = 1 from information_schema.processlist where state = "Flushing tables" and info = "FLUSH TABLES WITH READ LOCK"; --source include/wait_condition.inc ---error 1100 +--error ER_TABLE_NOT_LOCKED CREATE TABLE t2 AS SELECT * FROM t1; UNLOCK TABLES; # @@ -225,7 +244,7 @@ DROP TABLE t1; --source include/delete_anonymous_users.inc # -# Bug#19815 - CREATE/RENAME/DROP DATABASE can deadlock on a global read lock +# Bug#19815 CREATE/RENAME/DROP DATABASE can deadlock on a global read lock # connect (con1,localhost,root,,); connect (con2,localhost,root,,); @@ -237,7 +256,8 @@ FLUSH TABLES WITH READ LOCK; # With bug in place: acquire LOCK_mysql_create_table and # wait in wait_if_global_read_lock(). connection con2; -send DROP DATABASE mysqltest_1; +send +DROP DATABASE mysqltest_1; # # With bug in place: try to acquire LOCK_mysql_create_table... # When fixed: Reject dropping db because of the read lock. @@ -263,7 +283,7 @@ disconnect con2; DROP DATABASE mysqltest_1; # -# Bug #17264: MySQL Server freeze +# Bug#17264 MySQL Server freeze # connection locker; # Disable warnings to allow test to run also without InnoDB @@ -272,14 +292,18 @@ create table t1 (f1 int(12) unsigned not null auto_increment, primary key(f1)) e --enable_warnings lock tables t1 write; connection writer; -send alter table t1 auto_increment=0; +send +alter table t1 auto_increment=0; connection reader; +# Wait till connection writer is blocked let $wait_condition= select count(*) = 1 from information_schema.processlist where state = "Locked" and info = "alter table t1 auto_increment=0"; --source include/wait_condition.inc -send alter table t1 auto_increment=0; +send +alter table t1 auto_increment=0; connection locker; +# Wait till connection reader is blocked let $wait_condition= select count(*) = 2 from information_schema.processlist where state = "Locked" and info = "alter table t1 auto_increment=0"; @@ -293,8 +317,8 @@ connection locker; drop table t1; # -# Bug #38691: segfault/abort in ``UPDATE ...JOIN'' while -# ``FLUSH TABLES WITH READ LOCK'' +# Bug#38691 segfault/abort in ``UPDATE ...JOIN'' while +# ``FLUSH TABLES WITH READ LOCK'' # --connection default @@ -365,7 +389,7 @@ while ($i) { dec $i; --connection locker ---error 0,1060 +--error 0,ER_DUP_FIELDNAME ALTER TABLE t2 ADD COLUMN a int(11) unsigned default NULL; UPDATE t2 SET a=b; @@ -373,11 +397,11 @@ while ($i) { --send UPDATE t2 INNER JOIN (t1 JOIN t3 USING(a)) USING(a) SET a = NULL WHERE t1.b <> t2.b --connection locker ---error 0,1091 +--error 0,ER_CANT_DROP_FIELD_OR_KEY ALTER TABLE t2 DROP COLUMN a; --connection writer ---error 0,1054 +--error 0,ER_BAD_FIELD_ERROR --reap } --enable_query_log @@ -390,7 +414,7 @@ while ($i) { dec $i; --connection locker ---error 0,1060 +--error 0,ER_DUP_FIELDNAME ALTER TABLE t2 ADD COLUMN a int(11) unsigned default NULL; UPDATE t2 SET a=b; @@ -399,11 +423,11 @@ while ($i) { --send EXECUTE stmt --connection locker ---error 0,1091 +--error 0,ER_CANT_DROP_FIELD_OR_KEY ALTER TABLE t2 DROP COLUMN a; --connection writer ---error 0,1054 +--error 0,ER_BAD_FIELD_ERROR --reap } @@ -411,9 +435,10 @@ while ($i) { --connection default DROP TABLE t1, t2, t3; + # -# Bug#38499: flush tables and multitable table update with derived table cause -# crash +# Bug#38499 flush tables and multitable table update with derived table cause +# crash # CREATE TABLE t1( a INT, b INT ); @@ -471,7 +496,7 @@ while ($i) { dec $i; --connection locker ---error 0,1060 +--error 0,ER_DUP_FIELDNAME ALTER TABLE t1 ADD COLUMN a int(11) unsigned default NULL; UPDATE t1 SET a=b; @@ -479,11 +504,11 @@ while ($i) { --send UPDATE t1, (SELECT 1 FROM t1 t1i) d SET a = 0 WHERE 1=0; --connection locker ---error 0,1091 +--error 0,ER_CANT_DROP_FIELD_OR_KEY ALTER TABLE t1 DROP COLUMN a; --connection writer ---error 0,1054 # unknown column error +--error 0,ER_BAD_FIELD_ERROR # unknown column error --reap } --enable_query_log @@ -496,7 +521,7 @@ while ($i) { dec $i; --connection locker ---error 0,1060 +--error 0,ER_DUP_FIELDNAME ALTER TABLE t1 ADD COLUMN a INT; UPDATE t1 SET a=b; @@ -505,11 +530,11 @@ while ($i) { --send EXECUTE stmt --connection locker ---error 0,1091 +--error 0,ER_CANT_DROP_FIELD_OR_KEY ALTER TABLE t1 DROP COLUMN a; --connection writer ---error 0,1054 # Unknown column 'a' in 'field list' +--error 0,ER_BAD_FIELD_ERROR # Unknown column 'a' in 'field list' --reap } --enable_query_log @@ -568,7 +593,7 @@ while ($i) { dec $i; --connection locker ---error 0,1060 +--error 0,ER_DUP_FIELDNAME ALTER TABLE t1 ADD COLUMN a int(11) unsigned default NULL; UPDATE t1 SET a=b; @@ -576,11 +601,11 @@ while ($i) { --send UPDATE t1, ((SELECT 1 FROM t1 t1i) UNION (SELECT 2 FROM t1 t1ii)) e SET a = 0 WHERE 1=0; --connection locker ---error 0,1091 +--error 0,ER_CANT_DROP_FIELD_OR_KEY ALTER TABLE t1 DROP COLUMN a; --connection writer ---error 0,1054 # Unknown column 'a' in 'field list' +--error 0,ER_BAD_FIELD_ERROR # Unknown column 'a' in 'field list' --reap } --enable_query_log @@ -593,7 +618,7 @@ while ($i) { dec $i; --connection locker ---error 0,1060 +--error 0,ER_DUP_FIELDNAME ALTER TABLE t1 ADD COLUMN a INT; UPDATE t1 SET a=b; @@ -602,11 +627,11 @@ while ($i) { --send EXECUTE stmt --connection locker ---error 0,1091 +--error 0,ER_CANT_DROP_FIELD_OR_KEY ALTER TABLE t1 DROP COLUMN a; --connection writer ---error 0,1054 # Unknown column 'a' in 'field list' +--error 0,ER_BAD_FIELD_ERROR # Unknown column 'a' in 'field list' --reap } --enable_query_log @@ -617,20 +642,22 @@ DROP TABLE t1; # -# Bug #21281 "Pending write lock is incorrectly removed when its -# statement being KILLed" +# Bug#21281 Pending write lock is incorrectly removed when its +# statement being KILLed # create table t1 (i int); connection locker; lock table t1 read; connection writer; ---send update t1 set i= 10; +send +update t1 set i= 10; connection reader; let $wait_condition= select count(*) = 1 from information_schema.processlist where state = "Locked" and info = "update t1 set i= 10"; --source include/wait_condition.inc ---send select * from t1; +send +select * from t1; connection default; let $wait_condition= select count(*) = 1 from information_schema.processlist @@ -642,7 +669,7 @@ eval kill query $ID; connection reader; --reap connection writer; ---error ER_QUERY_INTERRUPTED +--error ER_QUERY_INTERRUPTED --reap connection locker; unlock tables; @@ -650,7 +677,7 @@ connection default; drop table t1; # -# Bug#25856 - HANDLER table OPEN in one connection lock DROP TABLE in another one +# Bug#25856 HANDLER table OPEN in one connection lock DROP TABLE in another one # --disable_warnings drop table if exists t1; @@ -658,12 +685,19 @@ drop table if exists t1; create table t1 (a int) ENGINE=MEMORY; --echo --> client 2 connection locker; ---error 1031 +--error ER_ILLEGAL_HA handler t1 open; --echo --> client 1 connection default; drop table t1; + +# Disconnect sessions used in many subtests above +disconnect locker; +disconnect reader; +disconnect writer; + + # # Bug#32395 Alter table under a impending global read lock causes a server crash # @@ -764,7 +798,7 @@ disconnect flush; drop table t1; # -# Bug#30331: Table_locks_waited shows inaccurate values +# Bug#30331 Table_locks_waited shows inaccurate values # --disable_warnings @@ -794,3 +828,7 @@ eval SET @tlwb= SUBSTRING_INDEX('$tlwb', ' ', -1); select @tlwa < @tlwb; --echo End of 5.1 tests + +# Wait till all disconnects are completed +--source include/wait_until_count_sessions.inc + diff --git a/mysql-test/t/mysqlbinlog.test b/mysql-test/t/mysqlbinlog.test index d73f4356877..46060649784 100644 --- a/mysql-test/t/mysqlbinlog.test +++ b/mysql-test/t/mysqlbinlog.test @@ -24,7 +24,7 @@ insert into t2 values (); # set @a:=1 # insert into t2 values (@a); -# test for load data and load data distributed among the several +# test for load data and load data distributed among the several # files (we need to fill up first binlog) load data infile '../../std_data/words.dat' into table t1; load data infile '../../std_data/words.dat' into table t1; @@ -109,7 +109,7 @@ select "--- --position --" as ""; --replace_regex /SQL_LOAD_MB-[0-9]-[0-9]/SQL_LOAD_MB-#-#/ --exec $MYSQL_BINLOG --short-form --local-load=$MYSQLTEST_VARDIR/tmp/ --read-from-remote-server --position=239 --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000002 -# Bug#7853 (mysqlbinlog does not accept input from stdin) +# Bug#7853 mysqlbinlog does not accept input from stdin --disable_query_log select "--- reading stdin --" as ""; --enable_query_log @@ -123,7 +123,7 @@ select "--- reading stdin --" as ""; drop table t1,t2; # -#BUG#14157: utf8 encoding in binlog without set character_set_client +# Bug#14157 utf8 encoding in binlog without set character_set_client # flush logs; --write_file $MYSQLTEST_VARDIR/tmp/bug14157.sql @@ -136,8 +136,8 @@ EOF --exec $MYSQL test < $MYSQLTEST_VARDIR/tmp/bug14157.sql --remove_file $MYSQLTEST_VARDIR/tmp/bug14157.sql -# resulted binlog, parly consisting of multi-byte utf8 chars, -# must be digestable for both client and server. In 4.1 the client +# resulted binlog, parly consisting of multi-byte utf8 chars, +# must be digestable for both client and server. In 4.1 the client # should use default-character-set same as the server. flush logs; --exec $MYSQL_BINLOG --short-form $MYSQLD_DATADIR/master-bin.000004 | $MYSQL @@ -164,7 +164,7 @@ select * from t5 order by c1; drop table t5; # -# Bug#20396 Bin Log does not get DELIMETER cmd - Recover StoredProc fails +# Bug#20396 Bin Log does not get DELIMETER cmd - Recover StoredProc fails # --disable_warnings drop procedure if exists p1; @@ -180,7 +180,7 @@ delimiter ;// flush logs; call p1(); drop procedure p1; ---error ER_SP_DOES_NOT_EXIST +--error ER_SP_DOES_NOT_EXIST call p1(); --replace_regex /SQL_LOAD_MB-[0-9]-[0-9]/SQL_LOAD_MB-#-#/ --exec $MYSQL_BINLOG --short-form $MYSQLD_DATADIR/master-bin.000008 @@ -223,14 +223,14 @@ flush logs; # # Bug#28293 missed '#' sign in the hex dump when the dump length -# is divisible by 16. +# is divisible by 16. # CREATE TABLE t1 (c1 CHAR(10)); # we need this for getting fixed timestamps inside of this test -flush logs; +FLUSH LOGS; INSERT INTO t1 VALUES ('0123456789'); -flush logs; +FLUSH LOGS; DROP TABLE t1; # We create a table, patch, and load the output into it @@ -238,11 +238,11 @@ DROP TABLE t1; # We can easily see if a 'Query' line is missing the '#' character # as described in the original bug ---disable_query_log -CREATE TABLE patch (a blob); +--disable_query_log +CREATE TABLE patch (a BLOB); --exec $MYSQL_BINLOG --hexdump --local-load=$MYSQLTEST_VARDIR/tmp/ $MYSQLD_DATADIR/master-bin.000012 > $MYSQLTEST_VARDIR/tmp/mysqlbinlog_tmp.dat eval LOAD DATA LOCAL INFILE '$MYSQLTEST_VARDIR/tmp/mysqlbinlog_tmp.dat' - INTO TABLE patch FIELDS TERMINATED by '' LINES STARTING BY '#'; + INTO TABLE patch FIELDS TERMINATED BY '' LINES STARTING BY '#'; --remove_file $MYSQLTEST_VARDIR/tmp/mysqlbinlog_tmp.dat --enable_query_log @@ -254,40 +254,42 @@ SELECT COUNT(*) AS `BUG#28293_expect_1` FROM patch WHERE a LIKE '%Query%'; DROP TABLE patch; # -# Bug #29928: incorrect connection_id() restoring from mysqlbinlog out +# Bug#29928 incorrect connection_id() restoring from mysqlbinlog out # -flush logs; -create table t1(a int); -insert into t1 values(connection_id()); -let $a= `select a from t1`; -flush logs; ---exec $MYSQL_BINLOG $MYSQLD_DATADIR/master-bin.000014 > $MYSQLTEST_VARDIR/tmp/bug29928.sql -drop table t1; -connect (con1, localhost, root, , test); +FLUSH LOGS; +CREATE TABLE t1(a INT); +INSERT INTO t1 VALUES(connection_id()); +let $a= `SELECT a FROM t1`; +FLUSH LOGS; +let $outfile= $MYSQLTEST_VARDIR/tmp/bug29928.sql; +--exec $MYSQL_BINLOG $MYSQLD_DATADIR/master-bin.000014 > $outfile +DROP TABLE t1; +connect (con1, localhost, root, , test); connection con1; ---exec $MYSQL test < $MYSQLTEST_VARDIR/tmp/bug29928.sql ---remove_file $MYSQLTEST_VARDIR/tmp/bug29928.sql -let $b= `select a from t1`; +--exec $MYSQL test < $outfile +--remove_file $outfile +let $b= `SELECT a FROM t1`; disconnect con1; connection default; -let $c= `select $a=$b`; +let $c= `SELECT $a=$b`; --echo $c -drop table t1; +DROP TABLE t1; echo shell> mysqlbinlog std_data/corrupt-relay-bin.000624 > var/tmp/bug31793.sql; error 1; exec $MYSQL_BINLOG $MYSQL_TEST_DIR/std_data/corrupt-relay-bin.000624 > $MYSQLTEST_VARDIR/tmp/bug31793.sql; +--remove_file $MYSQLTEST_VARDIR/tmp/bug31793.sql # # Test --disable-force-if-open and --force-if-open # -flush logs; +FLUSH LOGS; --error 1 --exec $MYSQL_BINLOG $MYSQLD_DATADIR/master-bin.000016 >/dev/null 2>/dev/null --exec $MYSQL_BINLOG --force-if-open $MYSQLD_DATADIR/master-bin.000016 >/dev/null 2>/dev/null ---echo BUG#31611: Security risk with BINLOG statement +--echo Bug#31611 Security risk with BINLOG statement SET BINLOG_FORMAT=ROW; CREATE DATABASE mysqltest1; @@ -306,6 +308,7 @@ connect (unsecure,localhost,untrusted,,mysqltest1); echo mysql mysqltest1 -uuntrusted < var/tmp/bug31611.sql; error 1; exec $MYSQL mysqltest1 -uuntrusted < $MYSQLTEST_VARDIR/tmp/bug31611.sql; +--remove_file $MYSQLTEST_VARDIR/tmp/bug31611.sql connection unsecure; error ER_TABLEACCESS_DENIED_ERROR; INSERT INTO t1 VALUES (1,USER()); @@ -315,7 +318,7 @@ connection default; DROP DATABASE mysqltest1; DROP USER untrusted@localhost; ---echo BUG#32580: mysqlbinlog cannot read binlog event with user variables +--echo Bug#32580 mysqlbinlog cannot read binlog event with user variables # Testing that various kinds of events can be read and restored properly. @@ -343,24 +346,25 @@ query_vertical SELECT * FROM t1; DROP TABLE t1; # -# Bug #37313 BINLOG Contains Incorrect server id +# Bug#37313 BINLOG Contains Incorrect server id # -let $save_server_id= `select @@global.server_id`; -let $s_id_max=`select (1 << 32) - 1`; -eval set @@global.server_id= $s_id_max; +let $binlog_file= $MYSQLTEST_VARDIR/tmp/mysqlbinlog_bug37313.binlog; +let $save_server_id= `SELECT @@global.server_id`; +let $s_id_max= `SELECT (1 << 32) - 1`; +eval SET @@global.server_id= $s_id_max; -reset master; -flush logs; ---exec $MYSQL_BINLOG $MYSQLD_DATADIR/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/mysqlbinlog_bug37313.binlog +RESET MASTER; +FLUSH LOGS; +--exec $MYSQL_BINLOG $MYSQLD_DATADIR/master-bin.000001 > $binlog_file --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR -eval select -(@a:=load_file("$MYSQLTEST_VARDIR/tmp/mysqlbinlog_bug37313.binlog")) -is not null; -let $s_id_unsigned= `select @a like "%server id $s_id_max%" /* must return 1 */`; +eval SELECT +(@a:=LOAD_FILE("$binlog_file")) +IS NOT NULL; +let $s_id_unsigned= `SELECT @a LIKE "%server id $s_id_max%" /* must return 1 */`; echo *** Unsigned server_id $s_id_max is found: $s_id_unsigned ***; -eval set @@global.server_id= $save_server_id; ---remove_file $MYSQLTEST_VARDIR/tmp/mysqlbinlog_bug37313.binlog +eval SET @@global.server_id= $save_server_id; +--remove_file $binlog_file --echo End of 5.1 tests diff --git a/mysql-test/t/mysqltest.test b/mysql-test/t/mysqltest.test index 6689a805c5a..55cd041aaf5 100644 --- a/mysql-test/t/mysqltest.test +++ b/mysql-test/t/mysqltest.test @@ -6,6 +6,9 @@ # This test uses chmod, can't be run with root permissions -- source include/not_as_root.inc +# Save the initial number of concurrent sessions +--source include/count_sessions.inc + # ============================================================================ # # Test of mysqltest itself @@ -50,7 +53,7 @@ select otto from (select 1 as otto) as t1; # ---------------------------------------------------------------------------- # Negative case(statement): -# The derived table t1 does not contain a column named 'friedrich' . +# The derived table t1 does not contain a column named 'friedrich' . # --> ERROR 42S22: Unknown column 'friedrich' in 'field list and # --> 1054: Unknown column 'friedrich' in 'field list' # ---------------------------------------------------------------------------- @@ -121,7 +124,7 @@ select friedrich from (select 1 as otto) as t1; # $mysql_errno is a builtin variable of mysqltest and contains the return code # of the last command sent to the server. # -# The following test cases often initialize $mysql_errno to 1064 by +# The following test cases often initialize $mysql_errno to 1064 by # a command with wrong syntax. # Example: --error 1064 To prevent the abort after the error. # garbage ; @@ -485,7 +488,7 @@ remove_file $MYSQLTEST_VARDIR/tmp/mysqltest.sql; # Allow trailing # comment --sleep 1 # Wait for insert delayed to be executed. ---sleep 1 # Wait for insert delayed to be executed. +--sleep 1 # Wait for insert delayed to be executed. # ---------------------------------------------------------------------------- # Test error @@ -707,7 +710,7 @@ echo Not a banana: $cat; --error 1 --exec echo "let hi;" | $MYSQL_TEST 2>&1 -# More advanced test for bug#17280 +# More advanced test for Bug#17280 let $success= 1; --echo # Execute: --echo # <whatever> success: \$success --echo # <whatever> success: $success @@ -1386,12 +1389,12 @@ connection default; # ---------------------------------------------------------------------------- -# TODO Test queries, especially their errormessages... so it's easy to debug +# TODO Test queries, especially their errormessages... so it's easy to debug # new scripts and diagnose errors # ---------------------------------------------------------------------------- # ---------------------------------------------------------------------------- -# Test bug#12386 +# Test Bug#12386 # ---------------------------------------------------------------------------- let $num= 2; while ($num) @@ -1407,7 +1410,7 @@ SELECT 1 as a; # -# Bug #10251: Identifiers containing quotes not handled correctly +# Bug#10251 Identifiers containing quotes not handled correctly # select 1 as `a'b`, 2 as `a"b`; @@ -1428,7 +1431,7 @@ let $message= `SELECT USER()`; # The message contains more then 80 characters on multiple lines # and is kept between double quotes. -let $message= +let $message= "Here comes a very very long message that - is longer then 80 characters and - consists of several lines"; @@ -1465,6 +1468,8 @@ remove_file $MYSQLTEST_VARDIR/tmp/zero_length_file.result; --error 0,1 remove_file $MYSQLTEST_VARDIR/tmp/zero_length_file.reject; --error 0,1 +remove_file $MYSQLTEST_VARDIR/tmp/zero_length_file.log; +--error 0,1 remove_file $MYSQL_TEST_DIR/r/zero_length_file.reject; # @@ -1487,8 +1492,8 @@ remove_file $MYSQLTEST_VARDIR/tmp/query.sql; #--exec $MYSQL_TEST -x $MYSQLTEST_VARDIR/tmp/query.sql -R $MYSQLTEST_VARDIR/tmp/result_file.result 2>&1 # -# Bug #11731 mysqltest in multi-statement queries ignores errors in -# non-1st queries +# Bug#11731 mysqltest in multi-statement queries ignores errors in +# non-1st queries # echo Failing multi statement query; @@ -1539,7 +1544,7 @@ remove_file $MYSQLTEST_VARDIR/log/bug11731.log; remove_file $MYSQLTEST_VARDIR/tmp/bug11731.sql; # -# Bug#19890 mysqltest: "query" command is broken +# Bug#19890 mysqltest "query" command is broken # # It should be possible to use the command "query" to force mysqltest to @@ -1565,7 +1570,7 @@ select "at" as col1, "c" as col2; select "at" as col1, "AT" as col2, "c" as col3; --replace_regex /a/b/ /ct/d/ -select "a" as col1, "ct" as col2; +select "a" as col1, "ct" as col2; --replace_regex /(strawberry)/raspberry and \1/ /blueberry/blackberry/ /potato/tomato/; select "strawberry","blueberry","potato"; @@ -1583,7 +1588,7 @@ select "strawberry","blueberry","potato"; --error 1 --exec echo "--replace_regex /a b c" | $MYSQL_TEST 2>&1 --error 1 ---exec echo "replace_regex /a /b c ;" | $MYSQL_TEST 2>&1 +--exec echo "replace_regex /a /b c ;" | $MYSQL_TEST 2>&1 # REQUIREMENT # replace_regex should replace substitutions from left to right in output @@ -1954,7 +1959,7 @@ SELECT '2' as "my_col1",2 as "my_col2" UNION SELECT '1',1 from t2; -# 9. Ensure that several result formatting options including "sorted_result" +# 9. Ensure that several result formatting options including "sorted_result" # - have all an effect # - "--sorted_result" does not need to be direct before the statement # - Row sorting is applied after modification of the column content @@ -2170,15 +2175,15 @@ remove_file $MYSQLTEST_VARDIR/tmp/testdir/file3.txt; rmdir $MYSQLTEST_VARDIR/tmp/testdir; # -# Bug #36041: mysql-test-run doesn't seem to string match 100% effectively -# on Windows +# Bug#36041 mysql-test-run doesn't seem to string match 100% effectively +# on Windows # --replace_result c:\\a.txt z SELECT 'c:\\a.txt' AS col; # -# Bug #32307 mysqltest - does not detect illegal if syntax +# Bug#32307 mysqltest - does not detect illegal if syntax # let $test= 1; @@ -2212,12 +2217,14 @@ select 1; # ---------------------------------------------------------------------------- -# BUG#35701: please allow test language variables in connection and sync_slave_with_master +# BUG#35701 please allow test language variables in connection and sync_slave_with_master # Test that "connection $variable" works and that $CURRENT_CONNECTION has the right value. # ---------------------------------------------------------------------------- connect (con1,localhost,root,,); --echo $CURRENT_CONNECTION +connect (con2,localhost,root,,); +--echo $CURRENT_CONNECTION connection default; --echo $CURRENT_CONNECTION @@ -2225,8 +2232,11 @@ connection default; connection con1; --echo $CURRENT_CONNECTION -let $x= default; -let $y= con1; +connection con2; +--echo $CURRENT_CONNECTION + +let $x= con1; +let $y= con2; connection $x; --echo $CURRENT_CONNECTION @@ -2245,3 +2255,6 @@ disconnect $y; --echo End of tests +connection default; +# Wait till we reached the initial number of concurrent sessions +--source include/wait_until_count_sessions.inc diff --git a/mysql-test/t/read_only.test b/mysql-test/t/read_only.test index 5a498404b03..abc016bafce 100644 --- a/mysql-test/t/read_only.test +++ b/mysql-test/t/read_only.test @@ -3,10 +3,12 @@ # should work with embedded server after mysqltest is fixed --source include/not_embedded.inc ---source include/count_sessions.inc set @start_read_only= @@global.read_only; +# Save the initial number of concurrent sessions +--source include/count_sessions.inc + --disable_warnings DROP TABLE IF EXISTS t1,t2,t3; --enable_warnings @@ -110,7 +112,7 @@ drop table t1; insert into t1 values(1); # -# BUG#11733: COMMITs should not happen if read-only is set +# Bug#11733 COMMITs should not happen if read-only is set # # LOCK TABLE ... WRITE / READ_ONLY @@ -238,8 +240,9 @@ set global read_only=1; connection default; select @@global.read_only; unlock tables; +disconnect root2; -# BUG #22077 "DROP TEMPORARY TABLE fails with wrong error if read_only is set" +# Bug#22077 DROP TEMPORARY TABLE fails with wrong error if read_only is set # # check if DROP TEMPORARY on a non-existing temporary table returns the right # error @@ -256,10 +259,12 @@ drop temporary table if exists ttt; --echo connection default; connection default; set global read_only=0; +disconnect con1; drop table t1,t2; drop user test@localhost; + --echo # ---echo # Bug #27440 read_only allows create and drop database +--echo # Bug#27440 read_only allows create and drop database --echo # set global read_only= 1; --disable_warnings @@ -297,7 +302,7 @@ delete from mysql.columns_priv where User like 'mysqltest_%'; flush privileges; drop database mysqltest_db1; set global read_only= @start_read_only; -disconnect con1; -disconnect root2; + +# Wait till all disconnects are completed --source include/wait_until_count_sessions.inc diff --git a/mysql-test/t/show_check.test b/mysql-test/t/show_check.test index e960dbddf02..0ce807ae73e 100644 --- a/mysql-test/t/show_check.test +++ b/mysql-test/t/show_check.test @@ -5,6 +5,9 @@ # depends on the presence of the log tables (which are CSV-based). --source include/have_csv.inc +# Save the initial number of concurrent sessions +--source include/count_sessions.inc + # # Test of some show commands # @@ -195,7 +198,7 @@ show columns from t1; drop table t1; # -# Test for Bug #2593 "SHOW CREATE TABLE doesn't properly double quotes" +# Test for Bug#2593 SHOW CREATE TABLE doesn't properly double quotes # SET @old_sql_mode= @@sql_mode, sql_mode= ''; @@ -223,7 +226,7 @@ CREATE TABLE """a" (i INT); SHOW CREATE TABLE """a"; DROP TABLE """a"; -#Bug #4374 SHOW TABLE STATUS FROM ignores collation_connection +# Bug#4374 SHOW TABLE STATUS FROM ignores collation_connection #set names latin1; #create database `ä`; #create table `ä`.`ä` (a int) engine=heap; @@ -248,7 +251,7 @@ SET sql_quote_show_create= @old_sql_quote_show_create; SET sql_mode= @old_sql_mode; # -# Test for bug #2719 "Heap tables status shows wrong or missing data." +# Test for Bug#2719 Heap tables status shows wrong or missing data. # select @@max_heap_table_size; @@ -309,7 +312,7 @@ show table status; drop table t1, t2, t3; # -# Test for bug #3342 SHOW CREATE DATABASE seems to require DROP privilege +# Test for Bug#3342 SHOW CREATE DATABASE seems to require DROP privilege # create database mysqltest; @@ -324,35 +327,38 @@ connect (con1,localhost,mysqltest_1,,mysqltest); connection con1; select * from t1; show create database mysqltest; ---error 1142 +--error ER_TABLEACCESS_DENIED_ERROR drop table t1; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR drop database mysqltest; +disconnect con1; connect (con2,localhost,mysqltest_2,,test); connection con2; ---error 1142 +--error ER_TABLEACCESS_DENIED_ERROR select * from mysqltest.t1; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR show create database mysqltest; ---error 1142 +--error ER_TABLEACCESS_DENIED_ERROR drop table mysqltest.t1; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR drop database mysqltest; +disconnect con2; connect (con3,localhost,mysqltest_3,,test); connection con3; ---error 1142 +--error ER_TABLEACCESS_DENIED_ERROR select * from mysqltest.t1; show create database mysqltest; drop table mysqltest.t1; drop database mysqltest; +disconnect con3; connection default; set names binary; -delete from mysql.user +delete from mysql.user where user='mysqltest_1' || user='mysqltest_2' || user='mysqltest_3'; -delete from mysql.db +delete from mysql.db where user='mysqltest_1' || user='mysqltest_2' || user='mysqltest_3'; flush privileges; @@ -366,7 +372,7 @@ flush privileges; #drop database `ä`; # Test that USING <keytype> is always shown in SHOW CREATE TABLE when it was -# specified during table creation, but not otherwise. (Bug #7235) +# specified during table creation, but not otherwise. (Bug#7235) CREATE TABLE t1 (i int, KEY (i)) ENGINE=MEMORY; SHOW CREATE TABLE t1; DROP TABLE t1; @@ -397,7 +403,7 @@ ALTER TABLE t1 ENGINE=MEMORY; SHOW CREATE TABLE t1; DROP TABLE t1; -# Test for BUG#9439 "Reporting wrong datatype for sub_part on show index" +# Test for Bug#9439 Reporting wrong datatype for sub_part on show index CREATE TABLE t1( field1 text NOT NULL, PRIMARY KEY(field1(1000)) @@ -407,7 +413,7 @@ show index from t1; --disable_metadata drop table t1; -# Test for BUG#11635: mysqldump exports TYPE instead of USING for HASH +# Test for Bug#11635 mysqldump exports TYPE instead of USING for HASH create table t1 ( c1 int NOT NULL, c2 int NOT NULL, @@ -417,7 +423,7 @@ create table t1 ( SHOW CREATE TABLE t1; DROP TABLE t1; -# Test for BUG#93: 4.1 protocl crash on corupted frm and SHOW TABLE STATUS +# Test for Bug#93 4.1 protocl crash on corupted frm and SHOW TABLE STATUS flush tables; @@ -426,19 +432,19 @@ let $MYSQLD_DATADIR= `select @@datadir`; system echo "this is a junk file for test" >> $MYSQLD_DATADIR/test/t1.frm ; --replace_column 6 # 7 # 8 # 9 # SHOW TABLE STATUS like 't1'; ---error 1033 +--error ER_NOT_FORM_FILE show create table t1; drop table if exists t1; --error 1,0 --remove_file $MYSQLD_DATADIR/test/t1.frm # -# BUG 12183 - SHOW OPEN TABLES behavior doesn't match grammar +# Bug#12183 SHOW OPEN TABLES behavior doesn't match grammar # First we close all open tables with FLUSH tables and then we open some. # --echo ---echo # Bug#12183: SHOW OPEN TABLES behavior doesn't match grammar. +--echo # Bug#12183 SHOW OPEN TABLES behavior doesn't match grammar. --echo # NOTE: SHOW OPEN TABLES does not sort result list by database or table names. @@ -497,15 +503,15 @@ use test; --echo # -# BUG #12591 (SHOW TABLES FROM dbname produces wrong error message) +# Bug#12591 SHOW TABLES FROM dbname produces wrong error message # ---error 1049 +--error ER_BAD_DB_ERROR SHOW TABLES FROM non_existing_database; --echo End of 4.1 tests # -# Bug#17203: "sql_no_cache sql_cache" in views created from prepared +# Bug#17203 "sql_no_cache sql_cache" in views created from prepared # statement # # The problem was that initial user setting was forgotten, and current @@ -585,7 +591,7 @@ SHOW COLUMNS FROM no_such_table; # -# Bug #19764: SHOW commands end up in the slow log as table scans +# Bug#19764 SHOW commands end up in the slow log as table scans # flush status; show status like 'slow_queries'; @@ -597,7 +603,7 @@ select 1 from information_schema.tables limit 1; show status like 'slow_queries'; create table t1 (a int); -create trigger tr1 before insert on t1 for each row +create trigger tr1 before insert on t1 for each row begin end; create view v1 as select a from t1; @@ -657,9 +663,10 @@ drop table t1; drop procedure p1; drop function f1; drop event e1; + # -# BUG#10491: Server returns data as charset binary SHOW CREATE TABLE or SELECT -# FROM I_S. +# Bug#10491 Server returns data as charset binary SHOW CREATE TABLE or SELECT +# FROM I_S. # # @@ -774,7 +781,7 @@ SHOW TRIGGERS LIKE 't1'; --echo ---------------------------------------------------------------- -SELECT +SELECT TRIGGER_CATALOG, TRIGGER_SCHEMA, TRIGGER_NAME, @@ -930,7 +937,7 @@ DROP DATABASE mysqltest1; use test; # -# Bug #28808: log_queries_not_using_indexes variable dynamic change is ignored +# Bug#28808 log_queries_not_using_indexes variable dynamic change is ignored # flush status; show variables like "log_queries_not_using_indexes"; @@ -946,7 +953,7 @@ select 1 from information_schema.tables limit 1; show status like 'slow_queries'; # -# Bug #30088: Can't disable myisam-recover by a value of "" +# Bug#30088 Can't disable myisam-recover by a value of "" # show variables like 'myisam_recover_options'; @@ -977,7 +984,8 @@ SHOW AUTHORS; --enable_result_log # -# Test for bug #9785 SELECT privilege for the whole database is needed to do SHOW CREATE DATABASE +# Bug#9785 SELECT privilege for the whole database is needed to do +# SHOW CREATE DATABASE # create database mysqltest; show create database mysqltest; @@ -993,6 +1001,8 @@ delete from mysql.db where user='mysqltest_4'; delete from mysql.tables_priv where user='mysqltest_4'; flush privileges; drop database mysqltest; +connection default; +disconnect con4; # # Ensure that show plugin code is tested @@ -1008,8 +1018,8 @@ show plugins; --enable_result_log # -# Bug #19874: SHOW COLUMNS and SHOW KEYS handle identifiers containing -# \ incorrectly +# Bug#19874 SHOW COLUMNS and SHOW KEYS handle identifiers containing +# \ incorrectly # create database `mysqlttest\1`; create table `mysqlttest\1`.`a\b` (a int); @@ -1021,7 +1031,7 @@ drop table `mysqlttest\1`.`a\b`; drop database `mysqlttest\1`; # -# Bug#24392: SHOW ENGINE MUTEX STATUS is a synonym for SHOW INNODB STATUS +# Bug#24392 SHOW ENGINE MUTEX STATUS is a synonym for SHOW INNODB STATUS # --error ER_UNKNOWN_STORAGE_ENGINE @@ -1107,8 +1117,8 @@ DROP PROCEDURE p1; DEALLOCATE PREPARE stmt1; # -# BUG#10491: Server returns data as charset binary SHOW CREATE TABLE or SELECT -# FROM INFORMATION_SCHEMA. +# Bug#10491 Server returns data as charset binary SHOW CREATE TABLE or SELECT +# FROM INFORMATION_SCHEMA. # # Before the change performed to fix the bug, the metadata of the output of # SHOW CREATE statements would always describe the result as 'binary'. That @@ -1171,17 +1181,17 @@ DROP TABLE t1; DROP EVENT ev1; # -# Bug #30036: SHOW TABLE TYPES causes the debug client to crash +# Bug#30036 SHOW TABLE TYPES causes the debug client to crash # --disable_result_log SHOW TABLE TYPES; --enable_result_log + # -# Bug #32710: SHOW INNODB STATUS requires SUPER +# Bug#32710 SHOW INNODB STATUS requires SUPER # - CREATE USER test_u@localhost; GRANT PROCESS ON *.* TO test_u@localhost; @@ -1198,3 +1208,7 @@ DROP USER test_u@localhost; --echo End of 5.1 tests + +# Wait till all disconnects are completed +--source include/wait_until_count_sessions.inc + diff --git a/mysql-test/t/skip_name_resolve.test b/mysql-test/t/skip_name_resolve.test index 3f732c8912b..4e7d927fb15 100644 --- a/mysql-test/t/skip_name_resolve.test +++ b/mysql-test/t/skip_name_resolve.test @@ -1,7 +1,10 @@ # Can't be tested with embedded server --- source include/not_embedded.inc +--source include/not_embedded.inc -# Bug #8471: IP address with mask fail when skip-name-resolve is on +# Save the initial number of concurrent sessions +--source include/count_sessions.inc + +# Bug#8471 IP address with mask fail when skip-name-resolve is on GRANT ALL ON test.* TO mysqltest_1@'127.0.0.1/255.255.255.255'; SHOW GRANTS FOR mysqltest_1@'127.0.0.1/255.255.255.255'; REVOKE ALL ON test.* FROM mysqltest_1@'127.0.0.1/255.255.255.255'; @@ -9,12 +12,17 @@ DROP USER mysqltest_1@'127.0.0.1/255.255.255.255'; # End of 4.1 tests -# Bug #13407 "Remote connecting crashes server". +# Bug#13407 Remote connecting crashes server # Server crashed when one used USER() function in connection for which # was impossible to obtain peer hostname. connect (con1, 127.0.0.1, root, , test, $MASTER_MYPORT, ); --replace_column 1 # -select user(); +SELECT USER(); --replace_column 1 <id> 3 <host> 5 <command> 6 <time> 7 <state> 8 <info> -show processlist; +SHOW PROCESSLIST; connection default; +disconnect con1; + +# Wait till all disconnects are completed +--source include/wait_until_count_sessions.inc + diff --git a/mysql-test/t/sp-security.test b/mysql-test/t/sp-security.test index b372c05b6e3..a80fe89082a 100644 --- a/mysql-test/t/sp-security.test +++ b/mysql-test/t/sp-security.test @@ -5,6 +5,9 @@ # Can't test with embedded server that doesn't support grants -- source include/not_embedded.inc +# Save the initial number of concurrent sessions +--source include/count_sessions.inc + connect (con1root,localhost,root,,); connection con1root; @@ -156,7 +159,7 @@ call db1_secret.stamp(6); select db1_secret.db(); # -# BUG#2777 +# Bug#2777 Stored procedure doesn't observe definer's rights # connection con1root; @@ -215,7 +218,7 @@ call q(); select * from t2; # -# BUG#6030: Stored procedure has no appropriate DROP privilege +# Bug#6030 Stored procedure has no appropriate DROP privilege # (or ALTER for that matter) # still connection con2user1 in db2 @@ -330,7 +333,7 @@ flush privileges; drop table t1; # -# BUG#9503: reseting correct parameters of thread after error in SP function +# Bug#9503 reseting correct parameters of thread after error in SP function # connect (root,localhost,root,,test); connection root; @@ -366,10 +369,12 @@ REVOKE ALL PRIVILEGES, GRANT OPTION FROM user1@localhost; drop function bug_9503; use test; drop database mysqltest; +connection default; +disconnect root; # # correct value from current_user() in function run from "security definer" -# (BUG#7291) +# (Bug#7291 Stored procedures: wrong CURRENT_USER value) # connection con1root; use test; @@ -398,10 +403,10 @@ REVOKE ALL PRIVILEGES, GRANT OPTION FROM user1@localhost; drop user user1@localhost; # -# Bug #12318: Wrong error message when accessing an inaccessible stored +# Bug#12318 Wrong error message when accessing an inaccessible stored # procedure in another database when the current database is # information_schema. -# +# --disable_warnings drop database if exists mysqltest_1; @@ -438,7 +443,7 @@ revoke usage on *.* from mysqltest_1@localhost; drop user mysqltest_1@localhost; # -# BUG#12812 create view calling a function works without execute right +# Bug#12812 create view calling a function works without execute right # on function delimiter |; --disable_warnings @@ -464,7 +469,7 @@ delimiter ;| # -# BUG#14834: Server denies to execute Stored Procedure +# Bug#14834 Server denies to execute Stored Procedure # # The problem here was with '_' in the database name. # @@ -507,7 +512,7 @@ drop database db_bug14834; # -# BUG#14533: 'desc tbl' in stored procedure causes error +# Bug#14533 'desc tbl' in stored procedure causes error # ER_TABLEACCESS_DENIED_ERROR # create database db_bug14533; @@ -546,7 +551,7 @@ drop database db_bug14533; # -# WL#2897: Complete definer support in the stored routines. +# WL#2897 Complete definer support in the stored routines. # # The following cases are tested: # 1. check that if DEFINER-clause is not explicitly specified, stored routines @@ -591,7 +596,7 @@ GRANT ALL PRIVILEGES ON mysqltest.* TO mysqltest_2@localhost; --echo ---> connection: mysqltest_2_con --connection mysqltest_2_con -use mysqltest; +USE mysqltest; CREATE PROCEDURE wl2897_p1() SELECT 1; @@ -603,7 +608,7 @@ CREATE FUNCTION wl2897_f1() RETURNS INT RETURN 1; --echo ---> connection: mysqltest_1_con --connection mysqltest_1_con -use mysqltest; +USE mysqltest; --error ER_SPECIFIC_ACCESS_DENIED_ERROR CREATE DEFINER=root@localhost PROCEDURE wl2897_p2() SELECT 2; @@ -629,7 +634,7 @@ CREATE DEFINER='a @ b @ c'@localhost FUNCTION wl2897_f3() RETURNS INT RETURN 3; --echo ---> connection: con1root --connection con1root -use mysqltest; +USE mysqltest; SHOW CREATE PROCEDURE wl2897_p1; SHOW CREATE PROCEDURE wl2897_p3; @@ -649,7 +654,7 @@ DROP DATABASE mysqltest; # -# BUG#13198: SP executes if definer does not exist +# Bug#13198 SP executes if definer does not exist # # Prepare environment. @@ -679,7 +684,7 @@ GRANT ALL PRIVILEGES ON mysqltest.* TO mysqltest_2@localhost; --echo ---> connection: mysqltest_1_con --connection mysqltest_1_con -use mysqltest; +USE mysqltest; CREATE PROCEDURE bug13198_p1() SELECT 1; @@ -697,7 +702,7 @@ SELECT bug13198_f1(); --echo ---> connection: mysqltest_2_con --connection mysqltest_2_con -use mysqltest; +USE mysqltest; CALL bug13198_p1(); @@ -719,7 +724,7 @@ DROP USER mysqltest_1@localhost; --echo ---> connection: mysqltest_2_con --connection mysqltest_2_con -use mysqltest; +USE mysqltest; --error ER_NO_SUCH_USER CALL bug13198_p1(); @@ -740,8 +745,8 @@ DROP USER mysqltest_2@localhost; DROP DATABASE mysqltest; # -# Bug#19857 - When a user with CREATE ROUTINE priv creates a routine, -# it results in NULL p/w +# Bug#19857 When a user with CREATE ROUTINE priv creates a routine, +# it results in NULL p/w # # Can't test with embedded server that doesn't support grants @@ -756,7 +761,7 @@ SELECT Host,User,Password FROM mysql.user WHERE User='user19857'; --echo ---> connection: mysqltest_2_con --connection mysqltest_2_con -use test; +USE test; DELIMITER //; CREATE PROCEDURE sp19857() DETERMINISTIC @@ -790,8 +795,7 @@ DROP USER user19857@localhost; use test; # -# BUG#18630: Arguments of suid routine calculated in wrong security -# context +# Bug#18630 Arguments of suid routine calculated in wrong security context # # Arguments of suid routines were calculated in definer's security # context instead of caller's context thus creating security hole. @@ -862,3 +866,7 @@ DROP FUNCTION f_suid; DROP TABLE t1; --echo End of 5.0 tests. + +# Wait till all disconnects are completed +--source include/wait_until_count_sessions.inc + diff --git a/mysql-test/t/sp_notembedded.test b/mysql-test/t/sp_notembedded.test index 16ebb710f25..f540126c405 100644 --- a/mysql-test/t/sp_notembedded.test +++ b/mysql-test/t/sp_notembedded.test @@ -4,13 +4,17 @@ set @old_concurrent_insert= @@global.concurrent_insert; set @@global.concurrent_insert= 0; +# Save the initial number of concurrent sessions +--source include/count_sessions.inc + --disable_warnings drop table if exists t1,t3; --enable_warnings delimiter |; + # -# BUG#4902: Stored procedure with SHOW WARNINGS leads to packet error +# Bug#4902 Stored procedure with SHOW WARNINGS leads to packet error # # Added tests for show grants command --disable_warnings @@ -52,15 +56,14 @@ show warnings| drop procedure bug4902_2| # -# BUG#3583: query cache doesn't work for stored procedures +# Bug#3583 query cache doesn't work for stored procedures # - --disable_warnings drop table if exists t1| --enable_warnings create table t1 ( - id char(16) not null default '', - data int not null + id char(16) not null default '', + data int not null )| --disable_warnings drop procedure if exists bug3583| @@ -97,8 +100,9 @@ delete from t1| drop procedure bug3583| drop table t1| + # -# BUG#6807: Stored procedure crash if CREATE PROCEDURE ... KILL QUERY +# Bug#6807 Stored procedure crash if CREATE PROCEDURE ... KILL QUERY # --disable_warnings drop procedure if exists bug6807| @@ -112,16 +116,16 @@ begin select 'Not reached'; end| ---error 1317 +--error ER_QUERY_INTERRUPTED call bug6807()| ---error 1317 +--error ER_QUERY_INTERRUPTED call bug6807()| drop procedure bug6807| # -# BUG#10100: function (and stored procedure?) recursivity problem +# Bug#10100 function (and stored procedure?) recursivity problem # --disable_warnings drop function if exists bug10100f| @@ -220,11 +224,11 @@ begin close c; end| -#end of the stack checking +# end of the stack checking set @@max_sp_recursion_depth=255| set @var=1| -#disable log because error about stack overrun contains numbers which -#depend on a system +# disable log because error about stack overrun contains numbers which +# depend on a system -- disable_result_log -- error ER_STACK_OVERRUN_NEED_MORE call bug10100p(255, @var)| @@ -253,6 +257,7 @@ drop table t3| delimiter ;| + # # Bug#15298 SHOW GRANTS FOR CURRENT_USER: Incorrect output in DEFINER context # @@ -269,6 +274,7 @@ call 15298_1(); call 15298_2(); connection default; +disconnect con1; drop user mysqltest_1@localhost; drop procedure 15298_1; drop procedure 15298_2; @@ -334,6 +340,7 @@ connection default; disconnect rl_holder; disconnect rl_acquirer; disconnect rl_wait; +disconnect rl_contender; drop procedure p1; drop table t1; set session low_priority_updates=default; @@ -343,3 +350,7 @@ set session low_priority_updates=default; # set @@global.concurrent_insert= @old_concurrent_insert; + +# Wait till all disconnects are completed +--source include/wait_until_count_sessions.inc + diff --git a/mysql-test/t/ssl-big.test b/mysql-test/t/ssl-big.test index 099c64df08f..91103f1d782 100644 --- a/mysql-test/t/ssl-big.test +++ b/mysql-test/t/ssl-big.test @@ -4,12 +4,15 @@ -- source include/have_ssl.inc -- source include/big_test.inc +# Save the initial number of concurrent sessions +--source include/count_sessions.inc + --disable_warnings DROP TABLE IF EXISTS t1, t2; --enable_warnings # -# Bug #29579 Clients using SSL can hang the server +# Bug#29579 Clients using SSL can hang the server # connect (ssl_con,localhost,root,,,,,SSL); @@ -18,7 +21,7 @@ create table t1 (a int); disconnect ssl_con; - + --disable_query_log --disable_result_log @@ -26,31 +29,36 @@ let $count= 2000; while ($count) { connect (ssl_con,localhost,root,,,,,SSL); - + eval insert into t1 values ($count); dec $count; - - # This select causes the net buffer to fill as the server sends the results + + # This select causes the net buffer to fill as the server sends the results # but the client doesn't reap the results. The results are larger each time # through the loop, so that eventually the buffer is completely full # at the exact moment the server attempts to the close the connection with # the lock held. send select * from t1; - + # now send the quit the command so the server will initiate the shutdown. - send_quit ssl_con; - + send_quit ssl_con; + # if the server is hung, this will hang too: connect (ssl_con2,localhost,root,,,,,SSL); - + # no hang if we get here, close and retry disconnect ssl_con2; disconnect ssl_con; -} +} --enable_query_log --enable_result_log connect (ssl_con,localhost,root,,,,,SSL); drop table t1; +connection default; +disconnect ssl_con; + +# Wait till all disconnects are completed +--source include/wait_until_count_sessions.inc diff --git a/mysql-test/t/ssl.test b/mysql-test/t/ssl.test index a15f0212fbd..936652eaa3d 100644 --- a/mysql-test/t/ssl.test +++ b/mysql-test/t/ssl.test @@ -3,6 +3,9 @@ -- source include/have_ssl.inc +# Save the initial number of concurrent sessions +--source include/count_sessions.inc + connect (ssl_con,localhost,root,,,,,SSL); # Check ssl turned on @@ -14,4 +17,9 @@ SHOW STATUS LIKE 'Ssl_cipher'; # Check ssl turned on SHOW STATUS LIKE 'Ssl_cipher'; +connection default; +disconnect ssl_con; + +# Wait till all disconnects are completed +--source include/wait_until_count_sessions.inc diff --git a/mysql-test/t/ssl_compress.test b/mysql-test/t/ssl_compress.test index 23051c0e367..b6e11621bf6 100644 --- a/mysql-test/t/ssl_compress.test +++ b/mysql-test/t/ssl_compress.test @@ -4,6 +4,9 @@ -- source include/have_ssl.inc -- source include/have_compress.inc +# Save the initial number of concurrent sessions +--source include/count_sessions.inc + connect (ssl_compress_con,localhost,root,,,,,SSL COMPRESS); # Check ssl turned on @@ -20,3 +23,10 @@ SHOW STATUS LIKE 'Ssl_cipher'; # Check compression turned on SHOW STATUS LIKE 'Compression'; + +connection default; +disconnect ssl_compress_con; + +# Wait till all disconnects are completed +--source include/wait_until_count_sessions.inc + diff --git a/mysql-test/t/status.test b/mysql-test/t/status.test index 69ae56ff9a2..5842f59af5c 100644 --- a/mysql-test/t/status.test +++ b/mysql-test/t/status.test @@ -268,6 +268,7 @@ show status like 'Com%function'; # connect (root, localhost, root,,test); connection root; +let $root_connection_id= `select connection_id()`; --disable_warnings create database db37908; --enable_warnings @@ -282,6 +283,7 @@ delimiter ;| connect (user1,localhost,mysqltest_1,,test); connection user1; +let $user1_connection_id= `select connection_id()`; --error ER_TABLEACCESS_DENIED_ERROR select * from db37908.t1; @@ -300,8 +302,12 @@ drop procedure proc37908; drop function func37908; REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; DROP USER mysqltest_1@localhost; -# Wait till we reached the initial number of concurrent sessions ---source include/wait_until_count_sessions.inc +# Wait till the sessions user1 and root are disconnected +let $wait_condition = + SELECT COUNT(*) = 0 + FROM information_schema.processlist + WHERE id in ('$root_connection_id','$user1_connection_id'); +--source include/wait_condition.inc # # Bug#41131 "Questions" fails to increment - ignores statements instead stored procs diff --git a/mysql-test/t/user_limits.test b/mysql-test/t/user_limits.test index af0f6545ac4..41af032b97e 100644 --- a/mysql-test/t/user_limits.test +++ b/mysql-test/t/user_limits.test @@ -3,9 +3,12 @@ # # Requires privileges to be enabled --- source include/not_embedded.inc +--source include/not_embedded.inc -# Prepare play-ground +# Save the initial number of concurrent sessions +--source include/count_sessions.inc + +# Prepare play-ground --disable_warnings drop table if exists t1; --enable_warnings @@ -28,11 +31,11 @@ connect (mqph, localhost, mysqltest_1,,); connection mqph; select * from t1; select * from t1; ---error 1226 +--error ER_USER_LIMIT_REACHED select * from t1; connect (mqph2, localhost, mysqltest_1,,); connection mqph2; ---error 1226 +--error ER_USER_LIMIT_REACHED select * from t1; # cleanup connection default; @@ -50,12 +53,12 @@ select * from t1; select * from t1; delete from t1; delete from t1; ---error 1226 +--error ER_USER_LIMIT_REACHED delete from t1; select * from t1; connect (muph2, localhost, mysqltest_1,,); connection muph2; ---error 1226 +--error ER_USER_LIMIT_REACHED delete from t1; select * from t1; # Cleanup @@ -74,7 +77,7 @@ connect (mcph2, localhost, mysqltest_1,,); connection mcph2; select * from t1; --replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK ---error 1226 +--error ER_USER_LIMIT_REACHED connect (mcph3, localhost, mysqltest_1,,); # Old connection is still ok select * from t1; @@ -83,7 +86,7 @@ select * from t1; disconnect mcph1; disconnect mcph2; --replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK ---error 1226 +--error ER_USER_LIMIT_REACHED connect (mcph3, localhost, mysqltest_1,,); # Cleanup connection default; @@ -101,13 +104,13 @@ connect (muc2, localhost, mysqltest_1,,); connection muc2; select * from t1; --replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK ---error 1226 +--error ER_USER_LIMIT_REACHED connect (muc3, localhost, mysqltest_1,,); # Closing of one of connections should help disconnect muc1; connect (muc3, localhost, mysqltest_1,,); select * from t1; -# Changing of limit should also help (and immediately) +# Changing of limit should also help (and immediately) connection default; grant usage on *.* to mysqltest_1@localhost with max_user_connections 3; flush user_resources; @@ -115,7 +118,7 @@ connect (muc4, localhost, mysqltest_1,,); connection muc4; select * from t1; --replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK ---error 1226 +--error ER_USER_LIMIT_REACHED connect (muc5, localhost, mysqltest_1,,); # Clean up connection default; @@ -129,10 +132,10 @@ drop user mysqltest_1@localhost; select @@session.max_user_connections, @@global.max_user_connections; # Local max_user_connections variable can't be set directly # since this limit is per-account ---error 1229 -set session max_user_connections= 2; +--error ER_GLOBAL_VARIABLE +set session max_user_connections= 2; # But it is ok to set global max_user_connections -set global max_user_connections= 2; +set global max_user_connections= 2; select @@session.max_user_connections, @@global.max_user_connections; # Let us check that global limit works grant usage on *.* to mysqltest_1@localhost; @@ -144,7 +147,7 @@ connect (muca2, localhost, mysqltest_1,,); connection muca2; select * from t1; --replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK ---error 1203 +--error ER_TOO_MANY_USER_CONNECTIONS connect (muca3, localhost, mysqltest_1,,); # Now we are testing that per-account limit prevails over gloabl limit connection default; @@ -154,16 +157,20 @@ connect (muca3, localhost, mysqltest_1,,); connection muca3; select @@session.max_user_connections, @@global.max_user_connections; --replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK ---error 1226 +--error ER_USER_LIMIT_REACHED connect (muca4, localhost, mysqltest_1,,); # Cleanup connection default; disconnect muca1; disconnect muca2; disconnect muca3; -set global max_user_connections= 0; +set global max_user_connections= 0; drop user mysqltest_1@localhost; --enable_ps_protocol # Final cleanup drop table t1; + +# Wait till all disconnects are completed +--source include/wait_until_count_sessions.inc + diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test index 9e22006ea67..c9d01266e9e 100644 --- a/mysql-test/t/view.test +++ b/mysql-test/t/view.test @@ -10,12 +10,12 @@ use test; # # create view on nonexistent table --- error 1146 +-- error ER_NO_SUCH_TABLE create view v1 (c,d) as select a,b from t1; create temporary table t1 (a int, b int); # view on temporary table --- error 1352 +-- error ER_VIEW_SELECT_TMPTABLE create view v1 (c) as select b+1 from t1; drop table t1; @@ -42,18 +42,18 @@ select * from t1; select c from v1; show create table v1; show create view v1; --- error 1347 +-- error ER_WRONG_OBJECT show create view t1; drop table t1; # try to use fields from underlying table --- error 1054 +-- error ER_BAD_FIELD_ERROR select a from v1; --- error 1054 +-- error ER_BAD_FIELD_ERROR select v1.a from v1; --- error 1054 +-- error ER_BAD_FIELD_ERROR select b from v1; --- error 1054 +-- error ER_BAD_FIELD_ERROR select v1.b from v1; # view with different algorithms (explain output differs) @@ -64,9 +64,9 @@ select c from v2; explain extended select c from v2; # try to use underlying table fields in VIEW creation process --- error 1054 +-- error ER_BAD_FIELD_ERROR create view v3 (c) as select a+1 from v1; --- error 1054 +-- error ER_BAD_FIELD_ERROR create view v3 (c) as select b+1 from v1; @@ -104,7 +104,7 @@ select * from v1; select * from v2; # try to create VIEW with name of existing VIEW --- error 1050 +-- error ER_TABLE_EXISTS_ERROR create view v1 (c,d,e,f) as select a,b, a in (select a+2 from t1), a = all (select a from t1) from t1; # 'or replace' should work in this case @@ -112,7 +112,7 @@ create or replace view v1 (c,d,e,f) as select a,b, a in (select a+2 from t1), a # try to ALTER unexisting VIEW drop view v2; --- error 1146 +-- error ER_NO_SUCH_TABLE alter view v2 as select c, d from v1; # 'or replace' on unexisting view @@ -126,15 +126,15 @@ select * from v1; select * from v2; # try to drop nonexistent VIEW --- error 1051 +-- error ER_BAD_TABLE_ERROR drop view v100; # try to drop table with DROP VIEW --- error 1347 +-- error ER_WRONG_OBJECT drop view t1; # try to drop VIEW with DROP TABLE --- error 1051 +-- error ER_BAD_TABLE_ERROR drop table v1; # try to drop table with DROP VIEW @@ -175,7 +175,7 @@ drop table t1; # syntax compatibility # create table t1 (a int); --- error 1368 +-- error ER_VIEW_NONUPD_CHECK create view v1 as select distinct a from t1 WITH CHECK OPTION; create view v1 as select a from t1 WITH CHECK OPTION; create view v2 as select a from t1 WITH CASCADED CHECK OPTION; @@ -232,10 +232,10 @@ create algorithm=temptable view v2 (a,c) as select a, b+1 from t1; select is_updatable from information_schema.views where table_name='v2'; select is_updatable from information_schema.views where table_name='v1'; # try to update expression --- error 1348 +-- error ER_NONUPDATEABLE_COLUMN update v1 set c=a+c; # try to update VIEW with forced TEMPORARY TABLE algorithm --- error 1288 +-- error ER_NON_UPDATABLE_TABLE update v2 set a=a+c; # updatable field of updateable view update v1 set a=a+c; @@ -254,10 +254,10 @@ insert into t2 values (10), (20); create view v1 (a,c) as select a, b+1 from t1; create algorithm=temptable view v2 (a,c) as select a, b+1 from t1; # try to update expression --- error 1348 +-- error ER_NONUPDATEABLE_COLUMN update t2,v1 set v1.c=v1.a+v1.c where t2.x=v1.a; # try to update VIEW with forced TEMPORARY TABLE algorithm --- error 1288 +-- error ER_NON_UPDATABLE_TABLE update t2,v2 set v2.a=v2.v2.a+c where t2.x=v2.a; # updatable field of updateable view update t2,v1 set v1.a=v1.a+v1.c where t2.x=v1.a; @@ -292,7 +292,7 @@ insert into t1 values (1,2), (2,3), (3,4), (4,5), (5,10); create view v1 (a,c) as select a, b+1 from t1; create algorithm=temptable view v2 (a,c) as select a, b+1 from t1; # try to update VIEW with forced TEMPORARY TABLE algorithm --- error 1288 +-- error ER_NON_UPDATABLE_TABLE delete from v2 where c < 4; # updatable field of updateable view delete from v1 where c < 4; @@ -311,7 +311,7 @@ insert into t2 values (1), (2), (3), (4); create view v1 (a,c) as select a, b+1 from t1; create algorithm=temptable view v2 (a,c) as select a, b+1 from t1; # try to update VIEW with forced TEMPORARY TABLE algorithm --- error 1288 +-- error ER_NON_UPDATABLE_TABLE delete v2 from t2,v2 where t2.x=v2.a; # updatable field of updateable view delete v1 from t2,v1 where t2.x=v1.a; @@ -331,7 +331,7 @@ set updatable_views_with_limit=NO; update v1 set x=x+1; update v2 set x=x+1; update v1 set x=x+1 limit 1; --- error 1288 +-- error ER_NON_UPDATABLE_TABLE update v2 set x=x+1 limit 1; set updatable_views_with_limit=YES; update v1 set x=x+1 limit 1; @@ -424,7 +424,7 @@ create table t1 (a int, primary key(a)); insert into t1 values (1), (2), (3), (200); create ALGORITHM=TEMPTABLE view v1 (x) as select a from t1; create view v2 (y) as select x from v1; --- error 1288 +-- error ER_NON_UPDATABLE_TABLE update v2 set y=10 where y=2; drop table t1; drop view v1,v2; @@ -479,17 +479,17 @@ create table t1 (col1 char(5),col2 char(5)); create view v1 as select * from t1; drop table t1; create table t1 (col1 char(5),newcol2 char(5)); --- error 1356 +-- error ER_VIEW_INVALID insert into v1 values('a','aa'); drop table t1; --- error 1356 +-- error ER_VIEW_INVALID select * from v1; drop view v1; # # check of duplication of column names # --- error 1060 +-- error ER_DUP_FIELDNAME create view v1 (a,a) as select 'a','a'; # @@ -559,7 +559,7 @@ drop table t1; # # error on preparation # --- error 1096 +-- error ER_NO_TABLES_USED CREATE VIEW v02 AS SELECT * FROM DUAL; SHOW TABLES; @@ -575,7 +575,7 @@ drop view v1; # create table t1 (col1 int,col2 char(22)); create view v1 as select * from t1; --- error 1347 +-- error ER_WRONG_OBJECT create index i1 on v1 (col1); drop view v1; drop table t1; @@ -735,7 +735,7 @@ create function x1 () returns int return 5; create table t1 (s1 int); create view v1 as select x1() from t1; drop function x1; --- error 1356 +-- error ER_VIEW_INVALID select * from v1; --replace_column 8 # 12 # 13 # show table status; @@ -794,10 +794,10 @@ create table t1 (a int); create view v1 as select a from t1; create view v3 as select a from t1; create database mysqltest; --- error 1450 +-- error ER_FORBID_SCHEMA_CHANGE rename table v1 to mysqltest.v1; rename table v1 to v2; ---error 1050 +--error ER_TABLE_EXISTS_ERROR rename table v3 to v1, v2 to t1; drop table t1; drop view v2,v3; @@ -810,19 +810,19 @@ create view v1 as select 'a',1; create view v2 as select * from v1 union all select * from v1; create view v3 as select * from v2 where 1 = (select `1` from v2); create view v4 as select * from v3; --- error 1242 +-- error ER_SUBQUERY_NO_1_ROW select * from v4; drop view v4, v3, v2, v1; # # VIEW over SELECT with prohibited clauses # --- error 1350 +-- error ER_VIEW_SELECT_CLAUSE create view v1 as select 5 into @w; --- error 1350 +-- error ER_VIEW_SELECT_CLAUSE create view v1 as select 5 into outfile 'ttt'; create table t1 (a int); --- error 1350 +-- error ER_VIEW_SELECT_CLAUSE create view v1 as select a from t1 procedure analyse(); -- error ER_VIEW_SELECT_DERIVED create view v1 as select 1 from (select 1) as d1; @@ -848,109 +848,109 @@ create table t3 (col1 datetime not null); create view v1 as select * from t1; create view v2 as select * from v1; create view v3 as select v2.col1 from v2,t2 where v2.col1 = t2.col1; --- error 1443 +-- error ER_VIEW_PREVENT_UPDATE update v2 set col1 = (select max(col1) from v1); --- error 1443 +-- error ER_VIEW_PREVENT_UPDATE update v2 set col1 = (select max(col1) from t1); --- error 1093 +-- error ER_UPDATE_TABLE_USED update v2 set col1 = (select max(col1) from v2); --- error 1443 +-- error ER_VIEW_PREVENT_UPDATE update v2,t2 set v2.col1 = (select max(col1) from v1) where v2.col1 = t2.col1; --- error 1443 +-- error ER_VIEW_PREVENT_UPDATE update t1,t2 set t1.col1 = (select max(col1) from v1) where t1.col1 = t2.col1; --- error 1093 +-- error ER_UPDATE_TABLE_USED update v1,t2 set v1.col1 = (select max(col1) from v1) where v1.col1 = t2.col1; --- error 1443 +-- error ER_VIEW_PREVENT_UPDATE update t2,v2 set v2.col1 = (select max(col1) from v1) where v2.col1 = t2.col1; --- error 1443 +-- error ER_VIEW_PREVENT_UPDATE update t2,t1 set t1.col1 = (select max(col1) from v1) where t1.col1 = t2.col1; --- error 1443 +-- error ER_VIEW_PREVENT_UPDATE update t2,v1 set v1.col1 = (select max(col1) from v1) where v1.col1 = t2.col1; --- error 1443 +-- error ER_VIEW_PREVENT_UPDATE update v2,t2 set v2.col1 = (select max(col1) from t1) where v2.col1 = t2.col1; --- error 1093 +-- error ER_UPDATE_TABLE_USED update t1,t2 set t1.col1 = (select max(col1) from t1) where t1.col1 = t2.col1; --- error 1443 +-- error ER_VIEW_PREVENT_UPDATE update v1,t2 set v1.col1 = (select max(col1) from t1) where v1.col1 = t2.col1; --- error 1093 +-- error ER_UPDATE_TABLE_USED update t2,v2 set v2.col1 = (select max(col1) from t1) where v2.col1 = t2.col1; --- error 1093 +-- error ER_UPDATE_TABLE_USED update t2,t1 set t1.col1 = (select max(col1) from t1) where t1.col1 = t2.col1; --- error 1093 +-- error ER_UPDATE_TABLE_USED update t2,v1 set v1.col1 = (select max(col1) from t1) where v1.col1 = t2.col1; --- error 1093 +-- error ER_UPDATE_TABLE_USED update v2,t2 set v2.col1 = (select max(col1) from v2) where v2.col1 = t2.col1; --- error 1443 +-- error ER_VIEW_PREVENT_UPDATE update t1,t2 set t1.col1 = (select max(col1) from v2) where t1.col1 = t2.col1; --- error 1443 +-- error ER_VIEW_PREVENT_UPDATE update v1,t2 set v1.col1 = (select max(col1) from v2) where v1.col1 = t2.col1; --- error 1443 +-- error ER_VIEW_PREVENT_UPDATE update t2,v2 set v2.col1 = (select max(col1) from v2) where v2.col1 = t2.col1; --- error 1443 +-- error ER_VIEW_PREVENT_UPDATE update t2,t1 set t1.col1 = (select max(col1) from v2) where t1.col1 = t2.col1; --- error 1443 +-- error ER_VIEW_PREVENT_UPDATE update t2,v1 set v1.col1 = (select max(col1) from v2) where v1.col1 = t2.col1; --- error 1443 +-- error ER_VIEW_PREVENT_UPDATE update v3 set v3.col1 = (select max(col1) from v1); --- error 1443 +-- error ER_VIEW_PREVENT_UPDATE update v3 set v3.col1 = (select max(col1) from t1); --- error 1443 +-- error ER_VIEW_PREVENT_UPDATE update v3 set v3.col1 = (select max(col1) from v2); --- error 1093 +-- error ER_UPDATE_TABLE_USED update v3 set v3.col1 = (select max(col1) from v3); --- error 1443 +-- error ER_VIEW_PREVENT_UPDATE delete from v2 where col1 = (select max(col1) from v1); --- error 1443 +-- error ER_VIEW_PREVENT_UPDATE delete from v2 where col1 = (select max(col1) from t1); --- error 1093 +-- error ER_UPDATE_TABLE_USED delete from v2 where col1 = (select max(col1) from v2); --- error 1443 +-- error ER_VIEW_PREVENT_UPDATE delete v2 from v2,t2 where (select max(col1) from v1) > 0 and v2.col1 = t2.col1; --- error 1443 +-- error ER_VIEW_PREVENT_UPDATE delete t1 from t1,t2 where (select max(col1) from v1) > 0 and t1.col1 = t2.col1; --- error 1093 +-- error ER_UPDATE_TABLE_USED delete v1 from v1,t2 where (select max(col1) from v1) > 0 and v1.col1 = t2.col1; --- error 1443 +-- error ER_VIEW_PREVENT_UPDATE delete v2 from v2,t2 where (select max(col1) from t1) > 0 and v2.col1 = t2.col1; --- error 1093 +-- error ER_UPDATE_TABLE_USED delete t1 from t1,t2 where (select max(col1) from t1) > 0 and t1.col1 = t2.col1; --- error 1443 +-- error ER_VIEW_PREVENT_UPDATE delete v1 from v1,t2 where (select max(col1) from t1) > 0 and v1.col1 = t2.col1; --- error 1093 +-- error ER_UPDATE_TABLE_USED delete v2 from v2,t2 where (select max(col1) from v2) > 0 and v2.col1 = t2.col1; --- error 1443 +-- error ER_VIEW_PREVENT_UPDATE delete t1 from t1,t2 where (select max(col1) from v2) > 0 and t1.col1 = t2.col1; --- error 1443 +-- error ER_VIEW_PREVENT_UPDATE delete v1 from v1,t2 where (select max(col1) from v2) > 0 and v1.col1 = t2.col1; --- error 1443 +-- error ER_VIEW_PREVENT_UPDATE insert into v2 values ((select max(col1) from v1)); --- error 1443 +-- error ER_VIEW_PREVENT_UPDATE insert into t1 values ((select max(col1) from v1)); --- error 1443 +-- error ER_VIEW_PREVENT_UPDATE insert into v2 values ((select max(col1) from v1)); --- error 1443 +-- error ER_VIEW_PREVENT_UPDATE insert into v2 values ((select max(col1) from t1)); --- error 1093 +-- error ER_UPDATE_TABLE_USED insert into t1 values ((select max(col1) from t1)); --- error 1443 +-- error ER_VIEW_PREVENT_UPDATE insert into v2 values ((select max(col1) from t1)); --- error 1093 +-- error ER_UPDATE_TABLE_USED insert into v2 values ((select max(col1) from v2)); --- error 1443 +-- error ER_VIEW_PREVENT_UPDATE insert into t1 values ((select max(col1) from v2)); --- error 1093 +-- error ER_UPDATE_TABLE_USED insert into v2 values ((select max(col1) from v2)); --- error 1443 +-- error ER_VIEW_PREVENT_UPDATE insert into v3 (col1) values ((select max(col1) from v1)); --- error 1443 +-- error ER_VIEW_PREVENT_UPDATE insert into v3 (col1) values ((select max(col1) from t1)); --- error 1443 +-- error ER_VIEW_PREVENT_UPDATE insert into v3 (col1) values ((select max(col1) from v2)); -#check with TZ tables in list --- error 1443 +# check with TZ tables in list +-- error ER_VIEW_PREVENT_UPDATE insert into v3 (col1) values ((select CONVERT_TZ('20050101000000','UTC','MET') from v2)); insert into v3 (col1) values ((select CONVERT_TZ('20050101000000','UTC','MET') from t2)); --- error 1048 +-- error ER_BAD_NULL_ERROR insert into t3 values ((select CONVERT_TZ('20050101000000','UTC','MET') from t2)); # temporary table algorithm view should be equal to subquery in the from clause create algorithm=temptable view v4 as select * from t1; @@ -966,7 +966,7 @@ drop table t1,t2,t3; # create table t1 (s1 int); create view v1 as select * from t1; --- error 1347 +-- error ER_WRONG_OBJECT handler v1 open as xx; drop view v1; drop table t1; @@ -1014,7 +1014,7 @@ create table t2 (a int); create view v1 as select * from t1; lock tables t1 read, v1 read; select * from v1; --- error 1100 +-- error ER_TABLE_NOT_LOCKED select * from t2; drop view v1; drop table t1, t2; @@ -1026,7 +1026,7 @@ create table t1 (a int); create view v1 as select * from t1 where a < 2 with check option; # simple insert insert into v1 values(1); --- error 1369 +-- error ER_VIEW_CHECK_FAILED insert into v1 values(3); # simple insert with ignore insert ignore into v1 values (2),(3),(0); @@ -1035,7 +1035,7 @@ select * from t1; delete from t1; # INSERT SELECT test insert into v1 SELECT 1; --- error 1369 +-- error ER_VIEW_CHECK_FAILED insert into v1 SELECT 3; # prepare data for next check create table t2 (a int); @@ -1043,9 +1043,9 @@ insert into t2 values (2),(3),(0); # INSERT SELECT with ignore test insert ignore into v1 SELECT a from t2; select * from t1 order by a desc; -#simple UPDATE test +# simple UPDATE test update v1 set a=-1 where a=0; --- error 1369 +-- error ER_VIEW_CHECK_FAILED update v1 set a=2 where a=1; select * from t1 order by a desc; # prepare data for next check @@ -1072,12 +1072,12 @@ create view v2 as select * from v1 where a > 0 with local check option; create view v3 as select * from v1 where a > 0 with cascaded check option; insert into v2 values (1); insert into v3 values (1); --- error 1369 +-- error ER_VIEW_CHECK_FAILED insert into v2 values (0); --- error 1369 +-- error ER_VIEW_CHECK_FAILED insert into v3 values (0); insert into v2 values (2); --- error 1369 +-- error ER_VIEW_CHECK_FAILED insert into v3 values (2); select * from t1; drop view v3,v2,v1; @@ -1089,7 +1089,7 @@ drop table t1; create table t1 (a int, primary key (a)); create view v1 as select * from t1 where a < 2 with check option; insert into v1 values (1) on duplicate key update a=2; --- error 1369 +-- error ER_VIEW_CHECK_FAILED insert into v1 values (1) on duplicate key update a=2; insert ignore into v1 values (1) on duplicate key update a=2; select * from t1; @@ -1102,13 +1102,13 @@ drop table t1; create table t1 (s1 int); create view v1 as select * from t1; create view v2 as select * from v1; --- error 1146 +-- error ER_NO_SUCH_TABLE alter view v1 as select * from v2; --- error 1146 +-- error ER_NO_SUCH_TABLE alter view v1 as select * from v1; --- error 1146 +-- error ER_NO_SUCH_TABLE create or replace view v1 as select * from v2; --- error 1146 +-- error ER_NO_SUCH_TABLE create or replace view v1 as select * from v1; drop view v2,v1; drop table t1; @@ -1143,7 +1143,7 @@ select * from t2; # check it with check option alter view v2 as select * from t2 where s1 in (select s1 from t1) with check option; insert into v2 values (5); --- error 1369 +-- error ER_VIEW_CHECK_FAILED update v2 set s1 = 1; insert into t1 values (1); update v2 set s1 = 1; @@ -1175,7 +1175,7 @@ drop table t1; create table t1 (s1 tinyint); create view v1 as select * from t1 where s1 <> 0 with local check option; create view v2 as select * from v1 with cascaded check option; --- error 1369 +-- error ER_VIEW_CHECK_FAILED insert into v2 values (0); drop view v2, v1; drop table t1; @@ -1186,7 +1186,7 @@ drop table t1; create table t1 (s1 int); create view v1 as select * from t1 where s1 < 5 with check option; #single value --- error 1369 +-- error ER_VIEW_CHECK_FAILED insert ignore into v1 values (6); #several values insert ignore into v1 values (6),(3); @@ -1200,7 +1200,7 @@ drop table t1; create table t1 (s1 tinyint); create trigger t1_bi before insert on t1 for each row set new.s1 = 500; create view v1 as select * from t1 where s1 <> 127 with check option; --- error 1369 +-- error ER_VIEW_CHECK_FAILED insert into v1 values (0); select * from v1; select * from t1; @@ -1214,7 +1214,7 @@ drop table t1; create table t1 (s1 tinyint); create view v1 as select * from t1 where s1 <> 0; create view v2 as select * from v1 where s1 <> 1 with cascaded check option; --- error 1369 +-- error ER_VIEW_CHECK_FAILED insert into v2 values (0); select * from v2; select * from t1; @@ -1227,7 +1227,7 @@ drop table t1; # fixed length fields create table t1 (a int, b char(10)); create view v1 as select * from t1 where a != 0 with check option; --- error 1369 +-- error ER_VIEW_CHECK_FAILED load data infile '../../std_data/loaddata3.dat' into table v1 fields terminated by '' enclosed by '' ignore 1 lines; select * from t1; select * from v1; @@ -1240,7 +1240,7 @@ drop table t1; # variable length fields create table t1 (a text, b text); create view v1 as select * from t1 where a <> 'Field A' with check option; --- error 1369 +-- error ER_VIEW_CHECK_FAILED load data infile '../../std_data/loaddata2.dat' into table v1 fields terminated by ',' enclosed by ''''; select concat('|',a,'|'), concat('|',b,'|') from t1; select concat('|',a,'|'), concat('|',b,'|') from v1; @@ -1321,7 +1321,7 @@ select * from t2; # view without primary key create view v2 (a,b) as select t1.b as a, t2.a as b from t1, t2; set updatable_views_with_limit=NO; --- error 1288 +-- error ER_NON_UPDATABLE_TABLE update v2 set a= 10 where a=200 limit 1; set updatable_views_with_limit=DEFAULT; # just view selects @@ -1349,14 +1349,14 @@ create table t2 (a int, primary key (a), b int); insert into t2 values (1000, 2000); create view v3 (a,b) as select t1.a as a, t2.a as b from t1, t2; # inserting into join view without field list --- error 1394 +-- error ER_VIEW_NO_INSERT_FIELD_LIST insert into v3 values (1,2); --- error 1394 +-- error ER_VIEW_NO_INSERT_FIELD_LIST insert into v3 select * from t2; # inserting in several tables of join view --- error 1393 +-- error ER_VIEW_MULTIUPDATE insert into v3(a,b) values (1,2); --- error 1393 +-- error ER_VIEW_MULTIUPDATE insert into v3(a,b) select * from t2; # correct inserts into join view insert into v3(a) values (1); @@ -1367,11 +1367,11 @@ insert into v3(a) values (1) on duplicate key update a=a+10000+VALUES(a); select * from t1; select * from t2; # try delete from join view --- error 1395 +-- error ER_VIEW_DELETE_MERGE_VIEW delete from v3; --- error 1395 +-- error ER_VIEW_DELETE_MERGE_VIEW delete v3,t1 from v3,t1; --- error 1395 +-- error ER_VIEW_DELETE_MERGE_VIEW delete t1,v3 from t1,v3; # delete from t1 just to reduce result set size delete from t1; @@ -1394,7 +1394,7 @@ drop view v3; drop tables t1,t2; # -# View field names should be case insensitive +# View field names should be case insensitive # create table t1(f1 int); create view v1 as select f1 from t1; @@ -1403,7 +1403,7 @@ drop view v1; drop table t1; # -# Resolving view fields in subqueries in VIEW (Bug #6394) +# Resolving view fields in subqueries in VIEW (Bug#6394) # create table t1(c1 int); create table t2(c2 int); @@ -1420,7 +1420,7 @@ drop view v2, v1; drop table t1, t2; # -# view over other view setup (BUG#7433) +# view over other view setup (Bug#7433) # CREATE TABLE t1 (C1 INT, C2 INT); CREATE TABLE t2 (C2 INT); @@ -1431,10 +1431,10 @@ drop view v2, v1; drop table t1, t2; # -# view and group_concat() (BUG#7116) +# view and group_concat() (Bug#7116) # -create table t1 (col1 char(5),col2 int,col3 int); -insert into t1 values ('one',10,25), ('two',10,50), ('two',10,50), ('one',20,25), ('one',30,25); +create table t1 (col1 char(5),col2 int,col3 int); +insert into t1 values ('one',10,25), ('two',10,50), ('two',10,50), ('one',20,25), ('one',30,25); create view v1 as select * from t1; select col1,group_concat(col2,col3) from t1 group by col1; select col1,group_concat(col2,col3) from v1 group by col1; @@ -1442,18 +1442,18 @@ drop view v1; drop table t1; # -# Item_ref resolved as view field (BUG#6894) +# Item_ref resolved as view field (Bug#6894) # create table t1 (s1 int, s2 char); create view v1 as select s1, s2 from t1; --- error 1054 +-- error ER_BAD_FIELD_ERROR select s2 from v1 vq1 where 2 = (select count(*) from v1 vq2 having vq1.s2 = vq2.s2); select s2 from v1 vq1 where 2 = (select count(*) aa from v1 vq2 having vq1.s2 = aa); drop view v1; drop table t1; # -# Test case for bug #9398 CREATE TABLE with SELECT from a multi-table view +# Test case for Bug#9398 CREATE TABLE with SELECT from a multi-table view # CREATE TABLE t1 (a1 int); CREATE TABLE t2 (a2 int); @@ -1469,7 +1469,7 @@ DROP VIEW v1; DROP TABLE t1,t2,t3; # -# Test for BUG#8703 "insert into table select from view crashes" +# Test for Bug#8703 insert into table select from view crashes # create table t1 (a int); create table t2 like t1; @@ -1481,84 +1481,84 @@ drop view v1; drop table t1,t2,t3; # -# Test for BUG #6106: query over a view using subquery for the underlying table -# - -CREATE TABLE t1 (col1 int PRIMARY KEY, col2 varchar(10)); -INSERT INTO t1 VALUES(1,'trudy'); -INSERT INTO t1 VALUES(2,'peter'); -INSERT INTO t1 VALUES(3,'sanja'); -INSERT INTO t1 VALUES(4,'monty'); -INSERT INTO t1 VALUES(5,'david'); -INSERT INTO t1 VALUES(6,'kent'); -INSERT INTO t1 VALUES(7,'carsten'); -INSERT INTO t1 VALUES(8,'ranger'); -INSERT INTO t1 VALUES(10,'matt'); -CREATE TABLE t2 (col1 int, col2 int, col3 char(1)); -INSERT INTO t2 VALUES (1,1,'y'); -INSERT INTO t2 VALUES (1,2,'y'); -INSERT INTO t2 VALUES (2,1,'n'); -INSERT INTO t2 VALUES (3,1,'n'); -INSERT INTO t2 VALUES (4,1,'y'); -INSERT INTO t2 VALUES (4,2,'n'); -INSERT INTO t2 VALUES (4,3,'n'); -INSERT INTO t2 VALUES (6,1,'n'); +# Test for Bug#6106 query over a view using subquery for the underlying table +# + +CREATE TABLE t1 (col1 int PRIMARY KEY, col2 varchar(10)); +INSERT INTO t1 VALUES(1,'trudy'); +INSERT INTO t1 VALUES(2,'peter'); +INSERT INTO t1 VALUES(3,'sanja'); +INSERT INTO t1 VALUES(4,'monty'); +INSERT INTO t1 VALUES(5,'david'); +INSERT INTO t1 VALUES(6,'kent'); +INSERT INTO t1 VALUES(7,'carsten'); +INSERT INTO t1 VALUES(8,'ranger'); +INSERT INTO t1 VALUES(10,'matt'); +CREATE TABLE t2 (col1 int, col2 int, col3 char(1)); +INSERT INTO t2 VALUES (1,1,'y'); +INSERT INTO t2 VALUES (1,2,'y'); +INSERT INTO t2 VALUES (2,1,'n'); +INSERT INTO t2 VALUES (3,1,'n'); +INSERT INTO t2 VALUES (4,1,'y'); +INSERT INTO t2 VALUES (4,2,'n'); +INSERT INTO t2 VALUES (4,3,'n'); +INSERT INTO t2 VALUES (6,1,'n'); INSERT INTO t2 VALUES (8,1,'y'); - -CREATE VIEW v1 AS SELECT * FROM t1; -SELECT a.col1,a.col2,b.col2,b.col3 +CREATE VIEW v1 AS SELECT * FROM t1; + +SELECT a.col1,a.col2,b.col2,b.col3 FROM t1 a LEFT JOIN t2 b ON a.col1=b.col1 - WHERE b.col2 IS NULL OR + WHERE b.col2 IS NULL OR b.col2=(SELECT MAX(col2) FROM t2 b WHERE b.col1=a.col1); -SELECT a.col1,a.col2,b.col2,b.col3 +SELECT a.col1,a.col2,b.col2,b.col3 FROM v1 a LEFT JOIN t2 b ON a.col1=b.col1 - WHERE b.col2 IS NULL OR + WHERE b.col2 IS NULL OR b.col2=(SELECT MAX(col2) FROM t2 b WHERE b.col1=a.col1); -CREATE VIEW v2 AS SELECT * FROM t2; +CREATE VIEW v2 AS SELECT * FROM t2; SELECT a.col1,a.col2,b.col2,b.col3 FROM v2 b RIGHT JOIN v1 a ON a.col1=b.col1 WHERE b.col2 IS NULL OR - b.col2=(SELECT MAX(col2) FROM v2 b WHERE b.col1=a.col1); + b.col2=(SELECT MAX(col2) FROM v2 b WHERE b.col1=a.col1); -# Tests from the report for bug #6107 +# Tests from the report for Bug#6107 SELECT a.col1,a.col2,b.col2,b.col3 FROM v2 b RIGHT JOIN v1 a ON a.col1=b.col1 WHERE a.col1 IN (1,5,9) AND (b.col2 IS NULL OR - b.col2=(SELECT MAX(col2) FROM v2 b WHERE b.col1=a.col1)); + b.col2=(SELECT MAX(col2) FROM v2 b WHERE b.col1=a.col1)); CREATE VIEW v3 AS SELECT * FROM t1 WHERE col1 IN (1,5,9); SELECT a.col1,a.col2,b.col2,b.col3 FROM v2 b RIGHT JOIN v3 a ON a.col1=b.col1 WHERE b.col2 IS NULL OR - b.col2=(SELECT MAX(col2) FROM v2 b WHERE b.col1=a.col1); - + b.col2=(SELECT MAX(col2) FROM v2 b WHERE b.col1=a.col1); + DROP VIEW v1,v2,v3; DROP TABLE t1,t2; # -# BUG#8490 Select from views containing subqueries causes server to hang -# forever. +# Bug#8490 Select from views containing subqueries causes server to hang +# forever. # create table t1 as select 1 A union select 2 union select 3; create table t2 as select * from t1; create view v1 as select * from t1 where a in (select * from t2); select * from v1 A, v1 B where A.a = B.a; create table t3 as select a a,a b from t2; -create view v2 as select * from t3 where +create view v2 as select * from t3 where a in (select * from t1) or b in (select * from t2); select * from v2 A, v2 B where A.a = B.b; drop view v1, v2; drop table t1, t2, t3; # -# Test case for bug #8528: select from view over multi-table view +# Test case for Bug#8528 select from view over multi-table view # CREATE TABLE t1 (a int); CREATE TABLE t2 (b int); @@ -1574,7 +1574,7 @@ DROP VIEW v2,v1; DROP TABLE t1, t2; # -# Correct restoring view name in SP table locking BUG#9758 +# Correct restoring view name in SP table locking Bug#9758 # create table t1 (a int); create view v1 as select sum(a) from t1 group by a; @@ -1603,7 +1603,7 @@ SELECT d, c FROM v1 ORDER BY d,c; DROP VIEW v1; DROP TABLE t1, t2; # -# using sum(distinct ) & avg(distinct ) in views (BUG#7015) +# using sum(distinct ) & avg(distinct ) in views (Bug#7015) # create table t1 (s1 int); create view v1 as select sum(distinct s1) from t1; @@ -1615,14 +1615,14 @@ drop view v1; drop table t1; # -# using cast(... as decimal) in views (BUG#11387); +# using cast(... as decimal) in views (Bug#11387); # create view v1 as select cast(1 as decimal); select * from v1; drop view v1; # -# Bug#11298 insert into select from VIEW produces incorrect result when +# Bug#11298 insert into select from VIEW produces incorrect result when # using ORDER BY create table t1(f1 int); create table t2(f2 int); @@ -1636,7 +1636,7 @@ drop view v1; drop table t1,t2,t3; # -# Generation unique names for columns, and correct names check (BUG#7448) +# Generation unique names for columns, and correct names check (Bug#7448) # # names with ' and \ create view v1 as select '\\','\\shazam'; @@ -1679,24 +1679,24 @@ create view v1 as select 's1', 's1', s1 from t1; select * from v1; drop view v1; # underlying field name conflict with set name --- error 1060 +-- error ER_DUP_FIELDNAME create view v1 as select 1 as s1, 's1', s1 from t1; --- error 1060 +-- error ER_DUP_FIELDNAME create view v1 as select 's1', s1, 1 as s1 from t1; drop table t1; # set names differ by case only --- error 1060 +-- error ER_DUP_FIELDNAME create view v1(k, K) as select 1,2; # -# using time_format in view (BUG#7521) +# using time_format in view (Bug#7521) # create view v1 as SELECT TIME_FORMAT(SEC_TO_TIME(3600),'%H:%i') as t; select * from v1; drop view v1; # -# evaluation constant functions in WHERE (BUG#4663) +# evaluation constant functions in WHERE (Bug#4663) # create table t1 (a timestamp default now()); create table t2 (b timestamp default now()); @@ -1717,7 +1717,7 @@ DROP VIEW v1; DROP TABLE t1; # -# checking views after some view with error (BUG#11337) +# checking views after some view with error (Bug#11337) # CREATE TABLE t1 (col1 time); CREATE TABLE t2 (col1 time); @@ -1758,7 +1758,7 @@ drop view v1, v2, v3, v4, v5, v6; drop table t2,t3; # -# bug #11325 Wrong date comparison in views +# Bug#11325 Wrong date comparison in views # create table t1 (f1 date); insert into t1 values ('2005-01-01'),('2005-02-02'); @@ -1769,7 +1769,7 @@ drop view v1; drop table t1; # -# using encrypt & substring_index in view (BUG#7024) +# using encrypt & substring_index in view (Bug#7024) # CREATE VIEW v1 AS SELECT ENCRYPT("dhgdhgd"); disable_result_log; @@ -1781,21 +1781,21 @@ SELECT * FROM v1; drop view v1; # -# hide underlying tables names in case of imposibility to update (BUG#10773) +# hide underlying tables names in case of imposibility to update (Bug#10773) # create table t1 (f59 int, f60 int, f61 int); insert into t1 values (19,41,32); -create view v1 as select f59, f60 from t1 where f59 in +create view v1 as select f59, f60 from t1 where f59 in (select f59 from t1); --- error 1288 +-- error ER_NON_UPDATABLE_TABLE update v1 set f60=2345; --- error 1443 +-- error ER_VIEW_PREVENT_UPDATE update t1 set f60=(select max(f60) from v1); drop view v1; drop table t1; # -# Using var_samp with view (BUG#10651) +# Using var_samp with view (Bug#10651) # create table t1 (s1 int); create view v1 as select var_samp(s1) from t1; @@ -1803,24 +1803,26 @@ show create view v1; drop view v1; drop table t1; + # # Correct inserting data check (absence of default value) for view -# underlying tables (BUG#6443) +# underlying tables (Bug#6443) # set sql_mode='strict_all_tables'; CREATE TABLE t1 (col1 INT NOT NULL, col2 INT NOT NULL); CREATE VIEW v1 (vcol1) AS SELECT col1 FROM t1; CREATE VIEW v2 (vcol1) AS SELECT col1 FROM t1 WHERE col2 > 2; --- error 1364 +-- error ER_NO_DEFAULT_FOR_FIELD INSERT INTO t1 (col1) VALUES(12); --- error 1423 +-- error ER_NO_DEFAULT_FOR_VIEW_FIELD INSERT INTO v1 (vcol1) VALUES(12); --- error 1423 +-- error ER_NO_DEFAULT_FOR_VIEW_FIELD INSERT INTO v2 (vcol1) VALUES(12); set sql_mode=default; drop view v2,v1; drop table t1; + # # Bug#11399 Use an alias in a select statement on a view # @@ -1831,8 +1833,9 @@ select f1 as alias from v1; drop view v1; drop table t1; + # -# Test for bug #6120: SP cache to be invalidated when altering a view +# Test for Bug#6120 SP cache to be invalidated when altering a view # CREATE TABLE t1 (s1 int, s2 int); @@ -1851,8 +1854,9 @@ DROP PROCEDURE p1; DROP VIEW v1; DROP TABLE t1; + # -# Test for bug #11709 View was ordered by wrong column +# Test for Bug#11709 View was ordered by wrong column # create table t1 (f1 int, f2 int); create view v1 as select f1 as f3, f2 as f1 from t1; @@ -1861,8 +1865,9 @@ select * from v1 order by f1; drop view v1; drop table t1; + # -# Test for bug #11771: wrong query_id in SELECT * FROM <view> +# Test for Bug#11771 wrong query_id in SELECT * FROM <view> # CREATE TABLE t1 (f1 char); INSERT INTO t1 VALUES ('A'); @@ -1875,8 +1880,9 @@ SELECT * FROM t1; DROP VIEW v1; DROP TABLE t1; + # -# opening table in correct locking mode (BUG#9597) +# opening table in correct locking mode (Bug#9597) # CREATE TABLE t1 ( bug_table_seq INTEGER NOT NULL); CREATE OR REPLACE VIEW v1 AS SELECT * from t1; @@ -1893,8 +1899,9 @@ DROP PROCEDURE p1; DROP VIEW v1; DROP TABLE t1; + # -# Bug #11760 Typo in Item_func_add_time::print() results in NULLs returned +# Bug#11760 Typo in Item_func_add_time::print() results in NULLs returned # subtime() in view create table t1(f1 datetime); insert into t1 values('2005.01.01 12:0:0'); @@ -1903,8 +1910,9 @@ select * from v1; drop view v1; drop table t1; + # -# Test for bug #11412: query over a multitable view with GROUP_CONCAT +# Test for Bug#11412 query over a multitable view with GROUP_CONCAT # CREATE TABLE t1 ( aid int PRIMARY KEY, @@ -1920,15 +1928,16 @@ INSERT INTO t2 values (1,1), (2,1), (2,2); CREATE VIEW v1 AS SELECT t1.*,t2.pid FROM t1,t2 WHERE t1.aid = t2.aid; -SELECT pid,GROUP_CONCAT(CONCAT(fn,' ',ln) ORDER BY 1) FROM t1,t2 +SELECT pid,GROUP_CONCAT(CONCAT(fn,' ',ln) ORDER BY 1) FROM t1,t2 WHERE t1.aid = t2.aid GROUP BY pid; SELECT pid,GROUP_CONCAT(CONCAT(fn,' ',ln) ORDER BY 1) FROM v1 GROUP BY pid; DROP VIEW v1; DROP TABLE t1,t2; + # -# Test for bug #12382: SELECT * FROM view after INSERT command +# Test for Bug#12382 SELECT * FROM view after INSERT command # CREATE TABLE t1 (id int PRIMARY KEY, f varchar(255)); @@ -1942,9 +1951,10 @@ SELECT * FROM v1; DROP VIEW v1; DROP TABLE t1; + # -# Test for bug #12470: crash for a simple select from a view defined -# as a join over 5 tables +# Test for Bug#12470 crash for a simple select from a view defined +# as a join over 5 tables CREATE TABLE t1 (pk int PRIMARY KEY, b int); CREATE TABLE t2 (pk int PRIMARY KEY, fk int, INDEX idx(fk)); @@ -1961,27 +1971,29 @@ SELECT a FROM v1; DROP VIEW v1; DROP TABLE t1,t2,t3,t4,t5; + # -# Bug #12298 Typo in function name results in erroneous view being created. +# Bug#12298 Typo in function name results in erroneous view being created. # create view v1 as select timestampdiff(day,'1997-01-01 00:00:00','1997-01-02 00:00:00') as f1; select * from v1; drop view v1; # -# repeatable CREATE VIEW statement BUG#12468 +# repeatable CREATE VIEW statement Bug#12468 # create table t1(a int); create procedure p1() create view v1 as select * from t1; drop table t1; --- error 1146 +-- error ER_NO_SUCH_TABLE call p1(); --- error 1146 +-- error ER_NO_SUCH_TABLE call p1(); drop procedure p1; + # -# Bug #10624 Views with multiple UNION and UNION ALL produce incorrect results +# Bug#10624 Views with multiple UNION and UNION ALL produce incorrect results # create table t1 (f1 int); create table t2 (f1 int); @@ -1991,20 +2003,23 @@ create view v1 as select * from t1 union select * from t2 union all select * fro select * from v1; drop view v1; drop table t1,t2; + + +# +# Test for Bug#10970 view referring a temporary table indirectly # -# Test for bug #10970: view referring a temporary table indirectly -# CREATE TEMPORARY TABLE t1 (a int); CREATE FUNCTION f1 () RETURNS int RETURN (SELECT COUNT(*) FROM t1); --- error 1352 +-- error ER_VIEW_SELECT_TMPTABLE CREATE VIEW v1 AS SELECT f1(); DROP FUNCTION f1; DROP TABLE t1; + # -# BUG #12533 (crash on DESCRIBE <view> after renaming base table column) +# Bug#12533 (crash on DESCRIBE <view> after renaming base table column) # --disable_warnings DROP TABLE IF EXISTS t1; @@ -2016,13 +2031,14 @@ CREATE VIEW v1 AS SELECT * FROM t1; DESCRIBE v1; ALTER TABLE t1 CHANGE COLUMN f4 f4x CHAR(5); ---error 1356 +--error ER_VIEW_INVALID DESCRIBE v1; DROP TABLE t1; DROP VIEW v1; -# -# Bug #12489 wrongly printed strcmp() function results in creation of broken + +# +# Bug#12489 wrongly printed strcmp() function results in creation of broken # view create table t1 (f1 char); create view v1 as select strcmp(f1,'a') from t1; @@ -2030,8 +2046,9 @@ select * from v1; drop view v1; drop table t1; + # -# Bug #12922 if(sum(),...) with group from view returns wrong results +# Bug#12922 if(sum(),...) with group from view returns wrong results # create table t1 (f1 int, f2 int,f3 int); insert into t1 values (1,10,20),(2,0,0); @@ -2039,7 +2056,9 @@ create view v1 as select * from t1; select if(sum(f1)>1,f2,f3) from v1 group by f1; drop view v1; drop table t1; -# BUG#12941 + + +# Bug#12941 # --disable_warnings create table t1 ( @@ -2049,7 +2068,7 @@ create table t1 ( create table t2 ( r_object_id char(16) NOT NULL, - i_position int(11) NOT NULL, + i_position int(11) NOT NULL, users_names varchar(32) default NULL ) Engine = InnoDB; --enable_warnings @@ -2067,23 +2086,25 @@ insert into t2 values('120001a080000542',-1, 'guser01'); insert into t2 values('120001a080000542',-2, 'guser02'); select v1.r_object_id, v2.users_names from v1, v2 -where (v1.group_name='tstgroup1') and v2.r_object_id=v1.r_object_id +where (v1.group_name='tstgroup1') and v2.r_object_id=v1.r_object_id order by users_names; drop view v1, v2; drop table t1, t2; + # -# Bug #6808 - Views: CREATE VIEW v ... FROM t AS v fails +# Bug#6808 Views: CREATE VIEW v ... FROM t AS v fails # -create table t1 (s1 int); +create table t1 (s1 int); create view abc as select * from t1 as abc; drop table t1; drop view abc; + # -# Bug #12993 View column rename broken in subselect +# Bug#12993 View column rename broken in subselect # create table t1(f1 char(1)); create view v1 as select * from t1; @@ -2091,15 +2112,17 @@ select * from (select f1 as f2 from v1) v where v.f2='a'; drop view v1; drop table t1; + # -# Bug #11416 Server crash if using a view that uses function convert_tz +# Bug#11416 Server crash if using a view that uses function convert_tz # create view v1 as SELECT CONVERT_TZ('2004-01-01 12:00:00','GMT','MET'); select * from v1; drop view v1; + # -# Bugs #12963, #13000: wrong creation of VIEW with DAYNAME, DAYOFWEEK, and WEEKDAY +# Bugs#12963, #13000 wrong creation of VIEW with DAYNAME, DAYOFWEEK, and WEEKDAY # CREATE TABLE t1 (date DATE NOT NULL); @@ -2129,8 +2152,9 @@ SELECT * FROM v3; DROP TABLE t1; DROP VIEW v1, v2, v3; + # -# Bug #13411: crash when using non-qualified view column in HAVING clause +# Bug#13411 crash when using non-qualified view column in HAVING clause # CREATE TABLE t1 ( a int, b int ); @@ -2142,8 +2166,9 @@ SELECT v1.a FROM v1 GROUP BY v1.a HAVING a > 1; DROP VIEW v1; DROP TABLE t1; + # -# Bug #13410: failed name resolution for qualified view column in HAVING +# Bug#13410 failed name resolution for qualified view column in HAVING # CREATE TABLE t1 ( a int, b int ); @@ -2157,8 +2182,9 @@ SELECT v_1.a FROM v1 AS v_1 GROUP BY v_1.a HAVING v_1.a IN (1,2,3); DROP VIEW v1; DROP TABLE t1; + # -# Bug #13327 view wasn't using index for const condition +# Bug#13327 view wasn't using index for const condition # CREATE TABLE t1 (a INT, b INT, INDEX(a,b)); @@ -2175,8 +2201,9 @@ EXPLAIN SELECT * FROM v2 WHERE a=1; DROP VIEW v1,v2; DROP TABLE t1,t2,t3; + # -# Bug #13622 Wrong view .frm created if some field's alias contain \n +# Bug#13622 Wrong view .frm created if some field's alias contain \n # create table t1 (f1 int); create view v1 as select t1.f1 as '123 @@ -2185,7 +2212,8 @@ select * from v1; drop view v1; drop table t1; -# Bug #14466 lost sort order in GROUP_CONCAT() in a view + +# Bug#14466 lost sort order in GROUP_CONCAT() in a view # create table t1 (f1 int, f2 int); insert into t1 values(1,1),(1,2),(1,3); @@ -2196,8 +2224,9 @@ select * from v2; drop view v1,v2; drop table t1; + # -# BUG#14026 Crash on second PS execution when using views +# Bug#14026 Crash on second PS execution when using views # create table t1 (x int, y int); create table t2 (x int, y int, z int); @@ -2207,8 +2236,8 @@ create table t4 (x int, y int, z int); create view v1 as select t1.x from ( - (t1 join t2 on ((t1.y = t2.y))) - join + (t1 join t2 on ((t1.y = t2.y))) + join (t3 left join t4 on (t3.y = t4.y) and (t3.z = t4.z)) ); @@ -2220,8 +2249,9 @@ execute stmt1 using @parm1; drop view v1; drop table t1,t2,t3,t4; + # -# Bug #14540: OPTIMIZE, ANALYZE, REPAIR applied to not a view +# Bug#14540 OPTIMIZE, ANALYZE, REPAIR applied to not a view # CREATE TABLE t1(id INT); @@ -2240,7 +2270,7 @@ DROP VIEW v1; # -# BUG#14719: Views DEFINER grammar is incorrect +# Bug#14719 Views DEFINER grammar is incorrect # create definer = current_user() sql security invoker view v1 as select 1; @@ -2251,8 +2281,9 @@ create definer = current_user sql security invoker view v1 as select 1; show create view v1; drop view v1; + # -# Bug #14816 test_if_order_by_key() expected only Item_fields. +# Bug#14816 test_if_order_by_key() expected only Item_fields. # create table t1 (id INT, primary key(id)); insert into t1 values (1),(2); @@ -2261,8 +2292,9 @@ explain select id from v1 order by id; drop view v1; drop table t1; + # -# Bug #14850 Item_ref's values wasn't updated +# Bug#14850 Item_ref's values wasn't updated # create table t1(f1 int, f2 int); insert into t1 values (null, 10), (null,2); @@ -2272,8 +2304,9 @@ select f1, sum(f2) from v1 group by f1; drop view v1; drop table t1; + # -# BUG#14885: incorrect SOURCE in view created in a procedure +# Bug#14885 incorrect SOURCE in view created in a procedure # TODO: here SOURCE string must be shown when it will be possible # --disable_warnings @@ -2291,8 +2324,9 @@ show create view v1; drop view v1; drop procedure p1; + # -# BUG#15096: using function with view for view creation +# Bug#15096 using function with view for view creation # CREATE VIEW v1 AS SELECT 42 AS Meaning; --disable_warnings @@ -2312,8 +2346,9 @@ select * from v2; drop view v2,v1; drop function f1; + # -# Bug#14861: aliased column names are not preserved. +# Bug#14861 aliased column names are not preserved. # create table t1 (id numeric, warehouse_id numeric); create view v1 as select id from t1; @@ -2331,8 +2366,9 @@ order by v2.receipt_id; drop view v2, v1; drop table t1; + # -# Bug#16016: MIN/MAX optimization for views +# Bug#16016 MIN/MAX optimization for views # CREATE TABLE t1 (a int PRIMARY KEY, b int); @@ -2355,9 +2391,10 @@ EXPLAIN SELECT MIN(a) FROM v1; DROP VIEW v1; DROP TABLE t1; + # -# Bug#16382: grouping name is resolved against a view column name -# which coincides with a select column name +# Bug#16382 grouping name is resolved against a view column name +# which coincides with a select column name CREATE TABLE t1 (x varchar(10)); INSERT INTO t1 VALUES (null), ('foo'), ('bar'), (null); @@ -2372,21 +2409,23 @@ SELECT IF(x IS NULL, 'blank', 'not blank') AS x FROM v1 GROUP BY x; DROP VIEW v1; DROP TABLE t1; + # -# BUG#15943: mysql_next_result hangs on invalid SHOW CREATE VIEW +# Bug#15943 mysql_next_result hangs on invalid SHOW CREATE VIEW # delimiter //; -drop table if exists t1; -drop view if exists v1; -create table t1 (id int); -create view v1 as select * from t1; -drop table t1; -show create view v1; +drop table if exists t1; +drop view if exists v1; +create table t1 (id int); +create view v1 as select * from t1; +drop table t1; +show create view v1; drop view v1; // delimiter ;// + # # Bug#17726 Not checked empty list caused endless loop # @@ -2401,9 +2440,10 @@ select * from v2; drop view v2, v1; drop table t1; + # -# Bug #18386: select from view over a table with ORDER BY view_col clause -# given view_col is not an image of any column from the base table +# Bug#18386 select from view over a table with ORDER BY view_col clause +# given view_col is not an image of any column from the base table CREATE TABLE t1 (a int); INSERT INTO t1 VALUES (1), (2); @@ -2415,9 +2455,10 @@ SELECT my_sqrt FROM v1 ORDER BY my_sqrt; DROP VIEW v1; DROP TABLE t1; + +# +# Bug#18237 invalid count optimization applied to an outer join with a view # -# Bug #18237: invalid count optimization applied to an outer join with a view -# CREATE TABLE t1 (id int PRIMARY KEY); CREATE TABLE t2 (id int PRIMARY KEY); @@ -2436,14 +2477,16 @@ DROP VIEW v2; DROP TABLE t1, t2; + # -# Bug #16069: VIEW does return the same results as underlying SELECT -# with WHERE condition containing BETWEEN over dates +# Bug#16069 VIEW does return the same results as underlying SELECT +# with WHERE condition containing BETWEEN over dates # Dates as strings should be casted to date type + CREATE TABLE t1 (id int NOT NULL PRIMARY KEY, td date DEFAULT NULL, KEY idx(td)); -INSERT INTO t1 VALUES +INSERT INTO t1 VALUES (1, '2005-01-01'), (2, '2005-01-02'), (3, '2005-01-02'), (4, '2005-01-03'), (5, '2005-01-04'), (6, '2005-01-05'), (7, '2005-01-05'), (8, '2005-01-05'), (9, '2005-01-06'); @@ -2456,8 +2499,9 @@ SELECT * FROM v1 WHERE td BETWEEN CAST('2005.01.02' AS DATE) AND CAST('2005.01.0 DROP VIEW v1; DROP TABLE t1; + # -# BUG#14308: Recursive view definitions +# Bug#14308 Recursive view definitions # # using view only create table t1 (a int); @@ -2487,8 +2531,9 @@ select * from v1; drop function f1; drop view t1, v1; + # -# Bug #15153: CONVERT_TZ() is not allowed in all places in VIEWs +# Bug#15153 CONVERT_TZ() is not allowed in all places in VIEWs # # Error was reported when one tried to use CONVERT_TZ() function # select list of view which was processed using MERGE algorithm. @@ -2499,7 +2544,7 @@ insert into t1 values (20040101000000), (20050101000000), (20060101000000); create view v1 as select convert_tz(dt, 'UTC', 'Europe/Moscow') as ldt from t1; select * from v1; drop view v1; -# And in its where part +# And in its where part create view v1 as select * from t1 where convert_tz(dt, 'UTC', 'Europe/Moscow') >= 20050101000000; select * from v1; # Other interesting case - a view which uses convert_tz() function @@ -2514,9 +2559,10 @@ select * from v2; drop view v1, v2; drop table t1; + # -# Bug #19490: usage of view specified by a query with GROUP BY -# an expression containing non-constant interval +# Bug#19490 usage of view specified by a query with GROUP BY +# an expression containing non-constant interval CREATE TABLE t1 (id int NOT NULL PRIMARY KEY, d datetime); @@ -2530,8 +2576,9 @@ SELECT * FROM v1; DROP VIEW v1; DROP TABLE t1; + # -# Bug#19077: A nested materialized view is used before being populated. +# Bug#19077 A nested materialized view is used before being populated. # CREATE TABLE t1 (i INT, j BIGINT); INSERT INTO t1 VALUES (1, 2), (2, 2), (3, 2); @@ -2541,9 +2588,10 @@ SELECT * FROM v2; DROP VIEW v2, v1; DROP TABLE t1; + +# +# Bug#19573 VIEW with HAVING that refers an alias name # -# Bug #19573: VIEW with HAVING that refers an alias name -# CREATE TABLE t1( fName varchar(25) NOT NULL, @@ -2551,7 +2599,7 @@ CREATE TABLE t1( DOB date NOT NULL, test_date date NOT NULL, uID int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY); - + INSERT INTO t1(fName, lName, DOB, test_date) VALUES ('Hank', 'Hill', '1964-09-29', '2007-01-01'), ('Tom', 'Adams', '1908-02-14', '2007-01-01'), @@ -2559,8 +2607,8 @@ INSERT INTO t1(fName, lName, DOB, test_date) VALUES CREATE VIEW v1 AS SELECT (year(test_date)-year(DOB)) AS Age - FROM t1 HAVING Age < 75; -SHOW CREATE VIEW v1; + FROM t1 HAVING Age < 75; +SHOW CREATE VIEW v1; SELECT (year(test_date)-year(DOB)) AS Age FROM t1 HAVING Age < 75; SELECT * FROM v1; @@ -2568,8 +2616,9 @@ SELECT * FROM v1; DROP VIEW v1; DROP TABLE t1; + # -# Bug #19089: wrong inherited dafault values in temp table views +# Bug#19089 wrong inherited dafault values in temp table views # CREATE TABLE t1 (id int NOT NULL PRIMARY KEY, a char(6) DEFAULT 'xxx'); @@ -2599,8 +2648,9 @@ SELECT * FROM t2; DROP VIEW v1; DROP TABLE t1,t2; + # -# Bug#16110: insert permitted into view col w/o default value +# Bug#16110 insert permitted into view col w/o default value # CREATE TABLE t1 (a INT NOT NULL, b INT NULL DEFAULT NULL); CREATE VIEW v1 AS SELECT a, b FROM t1; @@ -2608,7 +2658,7 @@ CREATE VIEW v1 AS SELECT a, b FROM t1; INSERT INTO v1 (b) VALUES (2); SET SQL_MODE = STRICT_ALL_TABLES; ---error 1423 +--error ER_NO_DEFAULT_FOR_VIEW_FIELD INSERT INTO v1 (b) VALUES (4); SET SQL_MODE = ''; @@ -2617,8 +2667,9 @@ SELECT * FROM t1; DROP VIEW v1; DROP TABLE t1; + # -# Bug #18243: expression over a view column that with the REVERSE function +# Bug#18243 expression over a view column that with the REVERSE function # CREATE TABLE t1 (firstname text, surname text); @@ -2633,21 +2684,23 @@ SELECT CONCAT(LEFT(name,LENGTH(name)-INSTR(REVERSE(name)," ")), DROP VIEW v1; DROP TABLE t1; + # -# Bug #19714: wrong type of a view column specified by an expressions over ints +# Bug#19714 wrong type of a view column specified by an expressions over ints # CREATE TABLE t1 (i int, j int); CREATE VIEW v1 AS SELECT COALESCE(i,j) FROM t1; DESCRIBE v1; -CREATE TABLE t2 SELECT COALESCE(i,j) FROM t1; +CREATE TABLE t2 SELECT COALESCE(i,j) FROM t1; DESCRIBE t2; DROP VIEW v1; DROP TABLE t1,t2; + # -# Bug #17526: views with TRIM functions +# Bug#17526 views with TRIM functions # CREATE TABLE t1 (s varchar(10)); @@ -2658,20 +2711,21 @@ CREATE VIEW v1 AS SELECT TRIM(BOTH 'y' FROM s) FROM t1; SELECT * FROM v1; DROP VIEW v1; -SELECT TRIM(LEADING 'y' FROM s) FROM t1; +SELECT TRIM(LEADING 'y' FROM s) FROM t1; CREATE VIEW v1 AS SELECT TRIM(LEADING 'y' FROM s) FROM t1; SELECT * FROM v1; DROP VIEW v1; -SELECT TRIM(TRAILING 'y' FROM s) FROM t1; +SELECT TRIM(TRAILING 'y' FROM s) FROM t1; CREATE VIEW v1 AS SELECT TRIM(TRAILING 'y' FROM s) FROM t1; SELECT * FROM v1; DROP VIEW v1; DROP TABLE t1; + # -#Bug #21080: ALTER VIEW makes user restate SQL SECURITY mode, and ALGORITHM +# Bug#21080 ALTER VIEW makes user restate SQL SECURITY mode, and ALGORITHM # CREATE TABLE t1 (x INT, y INT); CREATE ALGORITHM=TEMPTABLE SQL SECURITY INVOKER VIEW v1 AS SELECT x FROM t1; @@ -2682,8 +2736,10 @@ SHOW CREATE VIEW v1; DROP VIEW v1; DROP TABLE t1; -# Bug #21086: server crashes when VIEW defined with a SELECT with COLLATE -# clause is called + + +# Bug#21086 server crashes when VIEW defined with a SELECT with COLLATE +# clause is called # CREATE TABLE t1 (s1 char); INSERT INTO t1 VALUES ('Z'); @@ -2700,19 +2756,20 @@ SELECT s1 FROM t1; DROP VIEW v1, v2; DROP TABLE t1; + # -# Bug #11551: Asymmetric + undocumented behaviour of DROP VIEW and DROP TABLE +# Bug#11551 Asymmetric + undocumented behaviour of DROP VIEW and DROP TABLE # CREATE TABLE t1 (id INT); CREATE VIEW v1 AS SELECT id FROM t1; SHOW TABLES; ---error 1051 +--error ER_BAD_TABLE_ERROR DROP VIEW v2,v1; SHOW TABLES; CREATE VIEW v1 AS SELECT id FROM t1; ---error 1347 +--error ER_WRONG_OBJECT DROP VIEW t1,v1; SHOW TABLES; @@ -2721,13 +2778,14 @@ DROP TABLE t1; DROP VIEW IF EXISTS v1; --enable_warnings + # -# Bug #21261: Wrong access rights was required for an insert to a view +# Bug#21261 Wrong access rights was required for an insert to a view # CREATE DATABASE bug21261DB; USE bug21261DB; -CONNECT (root,localhost,root,,bug21261DB); -CONNECTION root; +connect (root,localhost,root,,bug21261DB); +connection root; CREATE TABLE t1 (x INT); CREATE SQL SECURITY INVOKER VIEW v1 AS SELECT x FROM t1; @@ -2736,34 +2794,41 @@ GRANT INSERT, UPDATE ON t1 TO 'user21261'@'localhost'; CREATE TABLE t2 (y INT); GRANT SELECT ON t2 TO 'user21261'@'localhost'; -CONNECT (user21261, localhost, user21261,, bug21261DB); -CONNECTION user21261; +connect (user21261, localhost, user21261,, bug21261DB); +connection user21261; INSERT INTO v1 (x) VALUES (5); UPDATE v1 SET x=1; -CONNECTION root; +connection root; GRANT SELECT ON v1 TO 'user21261'@'localhost'; GRANT SELECT ON t1 TO 'user21261'@'localhost'; -CONNECTION user21261; +connection user21261; UPDATE v1,t2 SET x=1 WHERE x=y; -CONNECTION root; +connection root; SELECT * FROM t1; REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user21261'@'localhost'; DROP USER 'user21261'@'localhost'; DROP VIEW v1; DROP TABLE t1; DROP DATABASE bug21261DB; + +connection default; USE test; +disconnect root; +disconnect user21261; + # -# Bug #15950: NOW() optimized away in VIEWs +# Bug#15950 NOW() optimized away in VIEWs # create table t1 (f1 datetime); create view v1 as select * from t1 where f1 between now() and now() + interval 1 minute; show create view v1; drop view v1; drop table t1; + + # -# Test for BUG#16899: Possible buffer overflow in handling of DEFINER-clause. +# Test for Bug#16899 Possible buffer overflow in handling of DEFINER-clause. # # Prepare. @@ -2790,8 +2855,7 @@ DROP TABLE t1; # -# BUG#17591: Updatable view not possible with trigger or stored -# function +# Bug#17591 Updatable view not possible with trigger or stored function # # During prelocking phase we didn't update lock type of view tables, # hence READ lock was always requested. @@ -2835,11 +2899,12 @@ DROP FUNCTION f2; DROP VIEW v1, v2; DROP TABLE t1; + # -# Bug #5500: wrong select_type in EXPLAIN output for queries over views +# Bug#5500 wrong select_type in EXPLAIN output for queries over views # -CREATE TABLE t1 (s1 int); +CREATE TABLE t1 (s1 int); CREATE VIEW v1 AS SELECT * FROM t1; EXPLAIN SELECT * FROM t1; @@ -2847,34 +2912,36 @@ EXPLAIN SELECT * FROM v1; INSERT INTO t1 VALUES (1), (3), (2); -EXPLAIN SELECT * FROM t1 t WHERE t.s1+1 < (SELECT MAX(t1.s1) FROM t1); -EXPLAIN SELECT * FROM v1 t WHERE t.s1+1 < (SELECT MAX(t1.s1) FROM t1); +EXPLAIN SELECT * FROM t1 t WHERE t.s1+1 < (SELECT MAX(t1.s1) FROM t1); +EXPLAIN SELECT * FROM v1 t WHERE t.s1+1 < (SELECT MAX(t1.s1) FROM t1); DROP VIEW v1; DROP TABLE t1; + # -# Bug #5505: Wrong error message on INSERT into a view +# Bug#5505 Wrong error message on INSERT into a view # create table t1 (s1 int); create view v1 as select s1 as a, s1 as b from t1; --error ER_NON_INSERTABLE_TABLE -insert into v1 values (1,1); +insert into v1 values (1,1); update v1 set a = 5; drop view v1; drop table t1; + # -# Bug #21646: view qith a subquery in ON expression +# Bug#21646 view qith a subquery in ON expression # -CREATE TABLE t1(pk int PRIMARY KEY); +CREATE TABLE t1(pk int PRIMARY KEY); CREATE TABLE t2(pk int PRIMARY KEY, fk int, ver int, org int); -CREATE ALGORITHM=MERGE VIEW v1 AS +CREATE ALGORITHM=MERGE VIEW v1 AS SELECT t1.* - FROM t1 JOIN t2 - ON t2.fk = t1.pk AND + FROM t1 JOIN t2 + ON t2.fk = t1.pk AND t2.ver = (SELECT MAX(t.ver) FROM t2 t WHERE t.org = t2.org); SHOW WARNINGS; SHOW CREATE VIEW v1; @@ -2882,9 +2949,9 @@ SHOW CREATE VIEW v1; DROP VIEW v1; DROP TABLE t1, t2; + # -# Bug#19111: TRIGGERs selecting from a VIEW on the firing base table -# fail +# Bug#19111 TRIGGERs selecting from a VIEW on the firing base table fail # # Allow to select from a view on a table being modified in a trigger # and stored function, since plain select is allowed there. @@ -2915,22 +2982,24 @@ DROP FUNCTION f1; DROP VIEW v1; DROP TABLE t1; + # -# Bug #16813 (WITH CHECK OPTION doesn't work with UPDATE) +# Bug#16813 (WITH CHECK OPTION doesn't work with UPDATE) # CREATE TABLE t1(id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, val INT UNSIGNED NOT NULL); CREATE VIEW v1 AS SELECT id, val FROM t1 WHERE val >= 1 AND val <= 5 WITH CHECK OPTION; INSERT INTO v1 (val) VALUES (2); INSERT INTO v1 (val) VALUES (4); --- error 1369 +-- error ER_VIEW_CHECK_FAILED INSERT INTO v1 (val) VALUES (6); --- error 1369 +-- error ER_VIEW_CHECK_FAILED UPDATE v1 SET val=6 WHERE id=2; DROP VIEW v1; DROP TABLE t1; + # -# BUG#22584: last_insert_id not updated after inserting a record +# Bug#22584 last_insert_id not updated after inserting a record # through a updatable view # # We still do not update LAST_INSERT_ID if AUTO_INCREMENT column is @@ -2966,8 +3035,9 @@ SELECT * FROM t1; DROP VIEW v1, v2; DROP TABLE t1; + # -# Bug #25580: !0 as an operand in a select expression of a view +# Bug#25580 !0 as an operand in a select expression of a view # CREATE VIEW v AS SELECT !0 * 5 AS x FROM DUAL; @@ -2978,8 +3048,9 @@ SELECT * FROM v; DROP VIEW v; + # -# BUG#24293: '\Z' token is not handled correctly in views +# Bug#24293 '\Z' token is not handled correctly in views # --disable_warnings @@ -2993,8 +3064,9 @@ SHOW CREATE VIEW v1; DROP VIEW v1; + # -# Bug #26124: BETWEEN over a view column of the DATETIME type +# Bug#26124 BETWEEN over a view column of the DATETIME type # CREATE TABLE t1 (mydate DATETIME); @@ -3009,8 +3081,9 @@ SELECT * FROM v1 WHERE mydate BETWEEN '2007-01-01' AND '2007-01-31'; DROP VIEW v1; DROP TABLE t1; + # -# Bug #25931: update of a multi-table view with check option +# Bug#25931 update of a multi-table view with check option # CREATE TABLE t1 (a int); @@ -3022,7 +3095,7 @@ CREATE VIEW v1 AS SELECT t2.b FROM t1,t2 WHERE t1.a = t2.b WITH CHECK OPTION; SELECT * FROM v1; ---error 1369 +--error ER_VIEW_CHECK_FAILED UPDATE v1 SET b=3; SELECT * FROM v1; SELECT * FROM t1; @@ -3031,8 +3104,9 @@ SELECT * FROM t2; DROP VIEW v1; DROP TABLE t1,t2; + # -# Bug#12122: Views with ORDER BY can't be resolved using MERGE algorithm. +# Bug#12122 Views with ORDER BY can't be resolved using MERGE algorithm. # create table t1(f1 int, f2 int); insert into t1 values(1,2),(1,3),(1,1),(2,3),(2,1),(2,2); @@ -3046,7 +3120,7 @@ drop view v1; drop table t1; # -# Bug#26209: queries with GROUP BY and ORDER BY using views +# Bug#26209 queries with GROUP BY and ORDER BY using views # CREATE TABLE t1 ( @@ -3065,9 +3139,9 @@ SELECT code, COUNT(DISTINCT country) FROM v1 GROUP BY code ORDER BY MAX(id); DROP VIEW v1; DROP TABLE t1; + # -# BUG#25897: Some queries are no longer possible after a CREATE VIEW -# fails +# Bug#25897 Some queries are no longer possible after a CREATE VIEW fails # --disable_warnings DROP VIEW IF EXISTS v1; @@ -3081,9 +3155,9 @@ eval CREATE VIEW v1 AS $query; --echo # Previously the following would fail. eval $query; + # -# Bug#24532: The return data type of IS TRUE is different from similar -# operations +# Bug#24532 The return data type of IS TRUE is different from similar operations # --disable_warnings @@ -3168,8 +3242,9 @@ drop view view_24532_a; drop view view_24532_b; drop table table_24532; + # -# Bug#26560: view using subquery with a reference to an outer alias +# Bug#26560 view using subquery with a reference to an outer alias # CREATE TABLE t1 ( @@ -3180,7 +3255,7 @@ INSERT INTO t1 (lid, name) VALUES (1, 'YES'), (2, 'NO'); CREATE TABLE t2 ( - id int NOT NULL PRIMARY KEY, + id int NOT NULL PRIMARY KEY, gid int NOT NULL, lid int NOT NULL, dt date @@ -3208,8 +3283,9 @@ SELECT * FROM v1; DROP VIEW v1; DROP table t1,t2; + # -# Bug#27786: Inconsistent Operation Performing UNION On View With ORDER BY +# Bug#27786 Inconsistent Operation Performing UNION On View With ORDER BY # CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (1),(2),(3); CREATE VIEW v1 AS SELECT a FROM t1 ORDER BY a; @@ -3224,8 +3300,9 @@ EXPLAIN SELECT * FROM t1 UNION SELECT * FROM v1 ORDER BY a; DROP VIEW v1; DROP TABLE t1; + # -# Bug #27921 View ignores precision for CAST() +# Bug#27921 View ignores precision for CAST() # CREATE VIEW v1 AS SELECT CAST( 1.23456789 AS DECIMAL( 7,5 ) ) AS col; SELECT * FROM v1; @@ -3236,9 +3313,10 @@ CREATE VIEW v1 AS SELECT CAST(1.23456789 AS DECIMAL(8,0)) AS col; SHOW CREATE VIEW v1; DROP VIEW v1; + # -# Bug #28716: CHECK OPTION expression is evaluated over expired record buffers -# when VIEW is updated via temporary tables +# Bug#28716 CHECK OPTION expression is evaluated over expired record buffers +# when VIEW is updated via temporary tables # CREATE TABLE t1 (a INT); CREATE TABLE t2 (b INT, c INT DEFAULT 0); @@ -3252,9 +3330,10 @@ SELECT * FROM v1; DROP VIEW v1; DROP TABLE t1,t2; + # -# Bug #28561: update on multi-table view with CHECK OPTION and -# a subquery in WHERE condition +# Bug#28561 update on multi-table view with CHECK OPTION and a subquery +# in WHERE condition # CREATE TABLE t1 (id int); @@ -3262,8 +3341,8 @@ CREATE TABLE t2 (id int, c int DEFAULT 0); INSERT INTO t1 (id) VALUES (1); INSERT INTO t2 (id) VALUES (1); -CREATE VIEW v1 AS - SELECT t2.c FROM t1, t2 +CREATE VIEW v1 AS + SELECT t2.c FROM t1, t2 WHERE t1.id=t2.id AND 1 IN (SELECT id FROM t1) WITH CHECK OPTION; UPDATE v1 SET c=1; @@ -3271,9 +3350,10 @@ UPDATE v1 SET c=1; DROP VIEW v1; DROP TABLE t1,t2; + # -# Bug #27827: CHECK OPTION ignores ON conditions when updating -# a multi-table view with CHECK OPTION. +# Bug#27827 CHECK OPTION ignores ON conditions when updating +# a multi-table view with CHECK OPTION. # CREATE TABLE t1 (a1 INT, c INT DEFAULT 0); @@ -3289,14 +3369,14 @@ CREATE VIEW v1 AS SELECT t1.a1, t1.c FROM t1 JOIN t2 ON t1.a1=t2.a2 AND t1.c < 3 WITH CHECK OPTION; SELECT * FROM v1; ---error 1369 +--error ER_VIEW_CHECK_FAILED UPDATE v1 SET c=3; PREPARE t FROM 'UPDATE v1 SET c=3'; ---error 1369 +--error ER_VIEW_CHECK_FAILED EXECUTE t; ---error 1369 +--error ER_VIEW_CHECK_FAILED EXECUTE t; ---error 1369 +--error ER_VIEW_CHECK_FAILED INSERT INTO v1(a1, c) VALUES (3, 3); UPDATE v1 SET c=1 WHERE a1=1; SELECT * FROM v1; @@ -3307,14 +3387,14 @@ CREATE VIEW v2 AS SELECT t1.a1, t1.c JOIN (t3 JOIN t4 ON t3.a3=t4.a4) ON t2.a2=t3.a3 WITH CHECK OPTION; SELECT * FROM v2; ---error 1369 +--error ER_VIEW_CHECK_FAILED UPDATE v2 SET c=3; PREPARE t FROM 'UPDATE v2 SET c=3'; ---error 1369 +--error ER_VIEW_CHECK_FAILED EXECUTE t; ---error 1369 +--error ER_VIEW_CHECK_FAILED EXECUTE t; ---error 1369 +--error ER_VIEW_CHECK_FAILED INSERT INTO v2(a1, c) VALUES (3, 3); UPDATE v2 SET c=2 WHERE a1=1; SELECT * FROM v2; @@ -3323,10 +3403,11 @@ SELECT * FROM t1; DROP VIEW v1,v2; DROP TABLE t1,t2,t3,t4; + # -# Bug #29104: assertion abort for a query with a view column reference -# in the GROUP BY list and a condition requiring the value -# of another view column to be equal to a constant +# Bug#29104 assertion abort for a query with a view column reference +# in the GROUP BY list and a condition requiring the value +# of another view column to be equal to a constant # CREATE TABLE t1 (a int, b int); @@ -3347,9 +3428,10 @@ EXPLAIN SELECT a, SUM(b) FROM v1 WHERE a=1 GROUP BY a; DROP VIEW v1; DROP TABLE t1; + # -# Bug #29392: SELECT over a multi-table view with ORDER BY -# selecting the same view column with two different aliases +# Bug#29392 SELECT over a multi-table view with ORDER BY +# selecting the same view column with two different aliases # CREATE TABLE t1 ( @@ -3373,7 +3455,7 @@ CREATE TABLE t3 ( INDEX idx_app_name(app_name) ); -CREATE VIEW v1 AS +CREATE VIEW v1 AS SELECT profile.person_id AS person_id FROM t1 profile, t2 userrole, t3 role WHERE userrole.person_id = profile.person_id AND @@ -3387,35 +3469,37 @@ INSERT INTO t1 VALUES INSERT INTO t2 VALUES (1,3,6),(2,4,7),(3,5,8),(4,6,9),(5,1,6),(6,1,7),(7,1,8),(8,1,9),(9,1,10); -INSERT INTO t3 VALUES +INSERT INTO t3 VALUES (1,'NUCANS_APP_USER','NUCANSAPP'),(2,'NUCANS_TRGAPP_USER','NUCANSAPP'), (3,'IA_INTAKE_COORDINATOR','IACANS'),(4,'IA_SCREENER','IACANS'), (5,'IA_SUPERVISOR','IACANS'),(6,'IA_READONLY','IACANS'), (7,'SOC_USER','SOCCANS'),(8,'CAYIT_USER','CAYITCANS'), (9,'RTOS_DCFSPOS_SUPERVISOR','RTOS'); - + EXPLAIN SELECT t.person_id AS a, t.person_id AS b FROM v1 t WHERE t.person_id=6; SELECT t.person_id AS a, t.person_id AS b FROM v1 t WHERE t.person_id=6; DROP VIEW v1; DROP TABLE t1,t2,t3; + # -# Bug#30020: Insufficient check led to a wrong info provided by the -# information schema table. +# Bug#30020 Insufficient check led to a wrong info provided by the +# information schema table. # create table t1 (i int); insert into t1 values (1), (2), (1), (3), (2), (4); create view v1 as select distinct i from t1; select * from v1; -select table_name, is_updatable from information_schema.views +select table_name, is_updatable from information_schema.views where table_name = 'v1'; drop view v1; drop table t1; + # -# Bug #28701: SELECTs from VIEWs completely ignore USE/FORCE KEY, allowing -# invalid statements +# Bug#28701 SELECTs from VIEWs completely ignore USE/FORCE KEY, allowing +# invalid statements # CREATE TABLE t1 (a INT); @@ -3431,8 +3515,9 @@ SELECT * FROM v1 IGNORE KEY(non_existant); DROP VIEW v1; DROP TABLE t1; + # -# Bug #28702: VIEWs defined with USE/FORCE KEY ignore that request +# Bug#28702 VIEWs defined with USE/FORCE KEY ignore that request # CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT, b INT NOT NULL DEFAULT 0, PRIMARY KEY(a), KEY (b)); @@ -3452,9 +3537,10 @@ DROP VIEW v2; DROP VIEW v3; DROP TABLE t1; + --echo # ---echo # Bug#29477: Not all fields of the target table were checked to have ---echo # a default value when inserting into a view. +--echo # Bug#29477 Not all fields of the target table were checked to have +--echo # a default value when inserting into a view. --echo # create table t1(f1 int, f2 int not null); create view v1 as select f1 from t1; @@ -3467,29 +3553,31 @@ set @@sql_mode=@old_mode; drop view v1; drop table t1; + # -# Bug #33389: Selecting from a view into a table from within SP or trigger -# crashes server +# Bug#33389 Selecting from a view into a table from within SP or trigger +# crashes server # create table t1 (a int, key(a)); create table t2 (c int); - + create view v1 as select a b from t1; -create view v2 as select 1 a from t2, v1 where c in +create view v2 as select 1 a from t2, v1 where c in (select 1 from t1 where b = a); - + insert into t1 values (1), (1); insert into t2 values (1), (1); - + prepare stmt from "select * from v2 where a = 1"; -execute stmt; +execute stmt; drop view v1, v2; drop table t1, t2; + # -# Bug #33049: Assert while running test-as3ap test(mysql-bench suite) +# Bug#33049 Assert while running test-as3ap test(mysql-bench suite) # CREATE TABLE t1 (a INT); @@ -3504,7 +3592,7 @@ DROP TABLE t1; ########################################################################### --echo # ----------------------------------------------------------------- ---echo # -- Bug#34337: Server crash when Altering a view using a table name. +--echo # -- Bug#34337 Server crash when Altering a view using a table name. --echo # ----------------------------------------------------------------- --echo @@ -3534,8 +3622,8 @@ DROP TABLE t1; ########################################################################### --echo # ----------------------------------------------------------------- ---echo # -- Bug#35193: VIEW query is rewritten without "FROM DUAL", ---echo # -- causing syntax error +--echo # -- Bug#35193 VIEW query is rewritten without "FROM DUAL", +--echo # -- causing syntax error --echo # ----------------------------------------------------------------- --echo @@ -3557,15 +3645,16 @@ DROP VIEW v1; ########################################################################### # -# Bug#39040: valgrind errors/crash when creating views with binlog logging -# enabled +# Bug#39040 valgrind errors/crash when creating views with binlog logging +# enabled # # Bug is visible only when running in valgrind with binary logging. CREATE VIEW v1 AS SELECT 1; DROP VIEW v1; + # -# Bug #33461: SELECT ... FROM <view> USE INDEX (...) throws an error +# Bug#33461 SELECT ... FROM <view> USE INDEX (...) throws an error # CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT, INDEX (c2)); @@ -3616,7 +3705,7 @@ USE test; --echo --echo # ---echo # Bug#26676: VIEW using old table schema in a session. +--echo # Bug#26676 VIEW using old table schema in a session. --echo # --echo @@ -3668,7 +3757,7 @@ DROP TABLE t1; ########################################################################### --echo # ----------------------------------------------------------------- ---echo # -- Bug#32538: View definition picks up character set, but not collation +--echo # -- Bug#32538 View definition picks up character set, but not collation --echo # ----------------------------------------------------------------- --echo diff --git a/mysql-test/t/view_grant.test b/mysql-test/t/view_grant.test index a08d0d0e097..824c67d867e 100644 --- a/mysql-test/t/view_grant.test +++ b/mysql-test/t/view_grant.test @@ -1,6 +1,9 @@ # Can't test with embedded server -- source include/not_embedded.inc +# Save the initial number of concurrent sessions +--source include/count_sessions.inc + --disable_warnings drop database if exists mysqltest; drop view if exists v1,v2,v3; @@ -32,19 +35,19 @@ grant create view,select on test.* to mysqltest_1@localhost; connect (user1,localhost,mysqltest_1,,test); connection user1; --- error ER_SPECIFIC_ACCESS_DENIED_ERROR +--error ER_SPECIFIC_ACCESS_DENIED_ERROR create definer=root@localhost view v1 as select * from mysqltest.t1; create view v1 as select * from mysqltest.t1; # try to modify view without DROP privilege on it --- error 1142 +--error ER_TABLEACCESS_DENIED_ERROR alter view v1 as select * from mysqltest.t1; --- error 1142 +--error ER_TABLEACCESS_DENIED_ERROR create or replace view v1 as select * from mysqltest.t1; # no CRETE VIEW privilege --- error 1142 +--error ER_TABLEACCESS_DENIED_ERROR create view mysqltest.v2 as select * from mysqltest.t1; # no SELECT privilege --- error 1142 +--error ER_TABLEACCESS_DENIED_ERROR create view v2 as select * from mysqltest.t2; connection root; @@ -54,7 +57,7 @@ show create view v1; grant create view,drop,select on test.* to mysqltest_1@localhost; connection user1; -# following 'use' command is workaround of bug #9582 and should be removed +# following 'use' command is workaround of Bug#9582 and should be removed # when that bug will be fixed use test; alter view v1 as select * from mysqltest.t1; @@ -82,7 +85,7 @@ grant select (c) on mysqltest.v1 to mysqltest_1@localhost; connection user1; select c from mysqltest.v1; # there are no privileges on column 'd' --- error 1143 +--error ER_COLUMNACCESS_DENIED_ERROR select d from mysqltest.v1; connection root; @@ -102,7 +105,7 @@ grant select (c) on mysqltest.v1 to mysqltest_1@localhost; connection user1; select c from mysqltest.v1; # there are no privileges on column 'd' --- error 1143 +--error ER_COLUMNACCESS_DENIED_ERROR select d from mysqltest.v1; connection root; @@ -117,7 +120,7 @@ connection root; --disable_warnings create database mysqltest; --enable_warnings -#prepare views and tables +# prepare views and tables create table mysqltest.t1 (a int, b int); create table mysqltest.t2 (a int, b int); create view mysqltest.v1 (c,d) as select a+1,b+1 from mysqltest.t1; @@ -139,21 +142,21 @@ select c from mysqltest.v4; show columns from mysqltest.v1; show columns from mysqltest.v2; # but explain/show do not --- error 1345 +--error ER_VIEW_NO_EXPLAIN explain select c from mysqltest.v1; --- error 1142 +--error ER_TABLEACCESS_DENIED_ERROR show create view mysqltest.v1; --- error 1345 +--error ER_VIEW_NO_EXPLAIN explain select c from mysqltest.v2; --- error 1142 +--error ER_TABLEACCESS_DENIED_ERROR show create view mysqltest.v2; --- error 1345 +--error ER_VIEW_NO_EXPLAIN explain select c from mysqltest.v3; --- error 1142 +--error ER_TABLEACCESS_DENIED_ERROR show create view mysqltest.v3; --- error 1345 +--error ER_VIEW_NO_EXPLAIN explain select c from mysqltest.v4; --- error 1142 +--error ER_TABLEACCESS_DENIED_ERROR show create view mysqltest.v4; # allow to see one of underlying table @@ -162,19 +165,19 @@ grant select on mysqltest.t1 to mysqltest_1@localhost; connection user1; # EXPLAIN of view on above table works explain select c from mysqltest.v1; --- error 1142 +--error ER_TABLEACCESS_DENIED_ERROR show create view mysqltest.v1; explain select c from mysqltest.v2; --- error 1142 +--error ER_TABLEACCESS_DENIED_ERROR show create view mysqltest.v2; # but other EXPLAINs do not --- error 1345 +--error ER_VIEW_NO_EXPLAIN explain select c from mysqltest.v3; --- error 1142 +--error ER_TABLEACCESS_DENIED_ERROR show create view mysqltest.v3; --- error 1345 +--error ER_VIEW_NO_EXPLAIN explain select c from mysqltest.v4; --- error 1142 +--error ER_TABLEACCESS_DENIED_ERROR show create view mysqltest.v4; # allow to see any view in mysqltest database @@ -228,14 +231,14 @@ select * from t1; update v2 set a=a+c; select * from t1; # no rights on column --- error 1143 +--error ER_COLUMNACCESS_DENIED_ERROR update t2,v2 set v2.c=v2.a+v2.c where t2.x=v2.c; --- error 1143 +--error ER_COLUMNACCESS_DENIED_ERROR update v2 set c=a+c; # no rights for view --- error 1142 +--error ER_TABLEACCESS_DENIED_ERROR update t2,v3 set v3.a=v3.a+v3.c where t2.x=v3.c; --- error 1142 +--error ER_TABLEACCESS_DENIED_ERROR update v3 set a=a+c; use test; @@ -269,9 +272,9 @@ select * from t1; delete v1 from t2,v1 where t2.x=v1.c; select * from t1; # no rights for view --- error 1142 +--error ER_TABLEACCESS_DENIED_ERROR delete v2 from t2,v2 where t2.x=v2.c; --- error 1142 +--error ER_TABLEACCESS_DENIED_ERROR delete from v2 where c < 4; use test; @@ -305,9 +308,9 @@ select * from t1; insert into v1 select x,y from t2; select * from t1; # no rights for view --- error 1142 +--error ER_TABLEACCESS_DENIED_ERROR insert into v2 values (5,6); --- error 1142 +--error ER_TABLEACCESS_DENIED_ERROR insert into v2 select x,y from t2; use test; @@ -335,10 +338,10 @@ connection user1; create view v1 as select * from mysqltest.t1; create view v2 as select b from mysqltest.t2; # There are not rights on mysqltest.v1 --- error 1142 +--error ER_TABLEACCESS_DENIED_ERROR create view mysqltest.v1 as select * from mysqltest.t1; # There are not any rights on mysqltest.t2.a --- error 1143 +--error ER_COLUMNACCESS_DENIED_ERROR create view v3 as select a from mysqltest.t2; # give CREATE VIEW privileges (without any privileges for result column) @@ -358,13 +361,13 @@ create view mysqltest.v3 as select b from mysqltest.t2; # Expression need select privileges --- error 1143 +--error ER_COLUMNACCESS_DENIED_ERROR create view v4 as select b+1 from mysqltest.t2; connection root; grant create view,update,select on test.* to mysqltest_1@localhost; connection user1; --- error 1143 +--error ER_COLUMNACCESS_DENIED_ERROR create view v4 as select b+1 from mysqltest.t2; connection root; @@ -417,7 +420,7 @@ connection root; # check view definer information show create view v1; revoke select on mysqltest.t1 from mysqltest_1@localhost; --- error ER_VIEW_INVALID +--error ER_VIEW_INVALID select * from v1; grant select on mysqltest.t1 to mysqltest_1@localhost; select * from v1; @@ -426,7 +429,7 @@ drop view v1; drop database mysqltest; # -# rights on execution of view underlying functiond (BUG#9505) +# rights on execution of view underlying functiond (Bug#9505) # connection root; --disable_warnings @@ -459,11 +462,11 @@ connection user1; use mysqltest; select * from v1; select * from v2; --- error ER_VIEW_INVALID +--error ER_VIEW_INVALID select * from v3; --- error ER_VIEW_INVALID +--error ER_VIEW_INVALID select * from v4; --- error ER_VIEW_INVALID +--error ER_VIEW_INVALID select * from v5; use test; @@ -511,13 +514,13 @@ use test; connection root; create view v5 as select * from v1; revoke execute on function f2 from mysqltest_1@localhost; --- error ER_VIEW_INVALID +--error ER_VIEW_INVALID select * from v1; --- error ER_VIEW_INVALID +--error ER_VIEW_INVALID select * from v2; select * from v3; select * from v4; --- error ER_VIEW_INVALID +--error ER_VIEW_INVALID select * from v5; drop view v1, v2, v3, v4, v5; @@ -555,13 +558,13 @@ use test; connection root; revoke select on t1 from mysqltest_1@localhost; --- error ER_VIEW_INVALID +--error ER_VIEW_INVALID select * from v1; --- error ER_VIEW_INVALID +--error ER_VIEW_INVALID select * from v2; select * from v3; select * from v4; --- error ER_VIEW_INVALID +--error ER_VIEW_INVALID select * from v5; #drop view v1, v2, v3, v4, v5; @@ -594,11 +597,11 @@ connection user1; use mysqltest; select * from v1; select * from v2; --- error ER_VIEW_INVALID +--error ER_VIEW_INVALID select * from v3; --- error ER_VIEW_INVALID +--error ER_VIEW_INVALID select * from v4; --- error ER_VIEW_INVALID +--error ER_VIEW_INVALID select * from v5; use test; @@ -610,7 +613,7 @@ REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; drop database mysqltest; # -# BUG#14256: definer in view definition is not fully qualified +# Bug#14256 definer in view definition is not fully qualified # --disable_warnings drop view if exists v1; @@ -647,6 +650,7 @@ drop view v1; select @v1def1, @v1def2, @v1def1=@v1def2; connection root; +disconnect test14256; drop user test14256; # Restore the anonymous users. @@ -656,8 +660,8 @@ flush privileges; drop table t1; # -# BUG#14726: freeing stack variable in case of an error of opening -# a view when we have locked tables with LOCK TABLES statement. +# Bug#14726 freeing stack variable in case of an error of opening a view when +# we have locked tables with LOCK TABLES statement. # connection root; --disable_warnings @@ -674,7 +678,7 @@ connection user1; use mysqltest; LOCK TABLES v1 READ; --- error ER_TABLEACCESS_DENIED_ERROR +--error ER_TABLEACCESS_DENIED_ERROR SHOW CREATE TABLE v1; UNLOCK TABLES; use test; @@ -685,7 +689,7 @@ drop user mysqltest_1@localhost; drop database mysqltest; # -# switch to default connaction +# switch to default connection # disconnect user1; disconnect root; @@ -702,7 +706,7 @@ drop view v1; drop view v2; # -# Bug#18681: View privileges are broken +# Bug#18681 View privileges are broken # CREATE DATABASE mysqltest1; CREATE USER readonly@localhost; @@ -723,54 +727,55 @@ GRANT UPDATE,SELECT ON mysqltest1.v_tus TO readonly@localhost; GRANT DELETE ON mysqltest1.v_td TO readonly@localhost; GRANT DELETE,SELECT ON mysqltest1.v_tds TO readonly@localhost; -CONNECT (n1,localhost,readonly,,); -CONNECTION n1; +connect (n1,localhost,readonly,,); +connection n1; ---error 1356 +--error ER_VIEW_INVALID SELECT * FROM mysqltest1.v_t1; ---error 1356 +--error ER_VIEW_INVALID INSERT INTO mysqltest1.v_t1 VALUES(4); ---error 1356 +--error ER_VIEW_INVALID DELETE FROM mysqltest1.v_t1 WHERE x = 1; ---error 1356 +--error ER_VIEW_INVALID UPDATE mysqltest1.v_t1 SET x = 3 WHERE x = 2; ---error 1356 +--error ER_VIEW_INVALID UPDATE mysqltest1.v_t1 SET x = 3; ---error 1356 +--error ER_VIEW_INVALID DELETE FROM mysqltest1.v_t1; ---error 1356 +--error ER_VIEW_INVALID SELECT 1 FROM mysqltest1.v_t1; ---error 1142 +--error ER_TABLEACCESS_DENIED_ERROR SELECT * FROM mysqltest1.t1; SELECT * FROM mysqltest1.v_ts; ---error 1142 +--error ER_TABLEACCESS_DENIED_ERROR SELECT * FROM mysqltest1.v_ts, mysqltest1.t1 WHERE mysqltest1.t1.x = mysqltest1.v_ts.x; ---error 1142 +--error ER_TABLEACCESS_DENIED_ERROR SELECT * FROM mysqltest1.v_ti; ---error 1142 +--error ER_TABLEACCESS_DENIED_ERROR INSERT INTO mysqltest1.v_ts VALUES (100); INSERT INTO mysqltest1.v_ti VALUES (100); ---error 1142 +--error ER_TABLEACCESS_DENIED_ERROR UPDATE mysqltest1.v_ts SET x= 200 WHERE x = 100; ---error 1142 +--error ER_TABLEACCESS_DENIED_ERROR UPDATE mysqltest1.v_ts SET x= 200; UPDATE mysqltest1.v_tu SET x= 200 WHERE x = 100; UPDATE mysqltest1.v_tus SET x= 200 WHERE x = 100; UPDATE mysqltest1.v_tu SET x= 200; ---error 1142 +--error ER_TABLEACCESS_DENIED_ERROR DELETE FROM mysqltest1.v_ts WHERE x= 200; ---error 1142 +--error ER_TABLEACCESS_DENIED_ERROR DELETE FROM mysqltest1.v_ts; ---error 1143 +--error ER_COLUMNACCESS_DENIED_ERROR DELETE FROM mysqltest1.v_td WHERE x= 200; DELETE FROM mysqltest1.v_tds WHERE x= 200; DELETE FROM mysqltest1.v_td; -CONNECTION default; +connection default; +disconnect n1; DROP VIEW mysqltest1.v_tds; DROP VIEW mysqltest1.v_td; DROP VIEW mysqltest1.v_tus; @@ -783,21 +788,21 @@ DROP USER readonly@localhost; DROP DATABASE mysqltest1; # -# BUG#14875: Bad view DEFINER makes SHOW CREATE VIEW fail +# Bug#14875 Bad view DEFINER makes SHOW CREATE VIEW fail # CREATE TABLE t1 (a INT PRIMARY KEY); INSERT INTO t1 VALUES (1), (2), (3); CREATE DEFINER = 'no-such-user'@localhost VIEW v AS SELECT a from t1; -#--warning 1448 +#--warning ER_VIEW_OTHER_USER SHOW CREATE VIEW v; ---error 1449 +--error ER_NO_SUCH_USER SELECT * FROM v; DROP VIEW v; DROP TABLE t1; USE test; # -# Bug#20363: Create view on just created view is now denied +# Bug#20363 Create view on just created view is now denied # eval CREATE USER mysqltest_db1@localhost identified by 'PWD'; eval GRANT ALL ON mysqltest_db1.* TO mysqltest_db1@localhost WITH GRANT OPTION; @@ -828,6 +833,7 @@ SELECT * FROM view2; SELECT * from view3; connection default; +disconnect session1; DROP VIEW mysqltest_db1.view3; DROP VIEW mysqltest_db1.view2; DROP VIEW mysqltest_db1.view1; @@ -835,8 +841,8 @@ DROP TABLE mysqltest_db1.t1; DROP SCHEMA mysqltest_db1; DROP USER mysqltest_db1@localhost; # -# BUG#20482: failure on Create join view with sources views/tables -# in different schemas +# Bug#20482 failure on Create join view with sources views/tables +# in different schemas # --disable_warnings CREATE DATABASE test1; @@ -846,7 +852,7 @@ CREATE DATABASE test2; CREATE TABLE test1.t0 (a VARCHAR(20)); CREATE TABLE test2.t1 (a VARCHAR(20)); CREATE VIEW test2.t3 AS SELECT * FROM test1.t0; -CREATE OR REPLACE VIEW test.v1 AS +CREATE OR REPLACE VIEW test.v1 AS SELECT ta.a AS col1, tb.a AS col2 FROM test2.t3 ta, test2.t1 tb; DROP VIEW test.v1; @@ -857,8 +863,8 @@ DROP DATABASE test1; # -# BUG#20570: CURRENT_USER() in a VIEW with SQL SECURITY DEFINER -# returns invoker name +# Bug#20570 CURRENT_USER() in a VIEW with SQL SECURITY DEFINER returns +# invoker name # --disable_warnings DROP VIEW IF EXISTS v1; @@ -917,7 +923,7 @@ DROP USER mysqltest_u1@localhost; # -# Bug#17254: Error for DEFINER security on VIEW provides too much info +# Bug#17254 Error for DEFINER security on VIEW provides too much info # connect (root,localhost,root,,); connection root; @@ -941,12 +947,12 @@ DROP USER def_17254@localhost; connect (inv,localhost,inv_17254,,db17254); connection inv; --echo for a user ---error 1142 +--error ER_TABLEACCESS_DENIED_ERROR SELECT * FROM v1; connection root; --echo for a superuser ---error 1449 +--error ER_NO_SUCH_USER SELECT * FROM v1; DROP USER inv_17254@localhost; DROP DATABASE db17254; @@ -955,7 +961,7 @@ disconnect inv; # -# BUG#24404: strange bug with view+permission+prepared statement +# Bug#24404 strange bug with view+permission+prepared statement # --disable_warnings DROP DATABASE IF EXISTS mysqltest_db1; @@ -1023,8 +1029,8 @@ DROP USER mysqltest_u1@localhost; DROP USER mysqltest_u2@localhost; # -# Bug#26813: The SUPER privilege is wrongly required to alter a view created -# by another user. +# Bug#26813 The SUPER privilege is wrongly required to alter a view created +# by another user. # connection root; CREATE DATABASE db26813; @@ -1056,7 +1062,7 @@ DROP DATABASE db26813; disconnect u1; --echo # ---echo # Bug#29908: A user can gain additional access through the ALTER VIEW. +--echo # Bug#29908 A user can gain additional access through the ALTER VIEW. --echo # connection root; CREATE DATABASE mysqltest_29908; @@ -1101,7 +1107,7 @@ disconnect u2; --echo ####################################################################### # -# BUG#24040: Create View don't succed with "all privileges" on a database. +# Bug#24040 Create View don't succed with "all privileges" on a database. # # Prepare. @@ -1185,7 +1191,7 @@ SELECT * FROM mysqltest1.t4; # Cleanup. --- disconnect bug24040_con +disconnect bug24040_con; DROP DATABASE mysqltest1; DROP DATABASE mysqltest2; @@ -1193,8 +1199,8 @@ DROP USER mysqltest_u1@localhost; # -# Bug #41354: Access control is bypassed when all columns of a view are -# selected by * wildcard +# Bug#41354 Access control is bypassed when all columns of a view are +# selected by * wildcard CREATE DATABASE db1; USE db1; @@ -1208,7 +1214,6 @@ connect (addconfoo, localhost, foo,,); connection addconfoo; USE db1; - SELECT f1 FROM t1; --error ER_COLUMNACCESS_DENIED_ERROR SELECT f2 FROM t1; @@ -1222,8 +1227,9 @@ SELECT f2 FROM v1; SELECT * FROM v1; connection default; -USE test; +disconnect root; disconnect addconfoo; +USE test; REVOKE SELECT (f1) ON db1.t1 FROM foo; REVOKE SELECT (f1) ON db1.v1 FROM foo; DROP USER foo; @@ -1332,8 +1338,8 @@ DROP DATABASE mysqltest1; DROP USER mysqluser1@localhost; # -# Bug#35600: Security breach via view, I_S table and prepared -# statement/stored procedure +# Bug#35600 Security breach via view, I_S table and prepared +# statement/stored procedure # CREATE USER mysqluser1@localhost; CREATE DATABASE mysqltest1; @@ -1374,3 +1380,8 @@ DROP VIEW v1, v2; DROP DATABASE mysqltest1; DROP VIEW test.v3; DROP USER mysqluser1@localhost; +USE test; + +# Wait till we reached the initial number of concurrent sessions +--source include/wait_until_count_sessions.inc + diff --git a/mysql-test/t/wait_timeout.test b/mysql-test/t/wait_timeout.test index 63809252a01..6947e346675 100644 --- a/mysql-test/t/wait_timeout.test +++ b/mysql-test/t/wait_timeout.test @@ -10,8 +10,11 @@ -- source include/not_embedded.inc -- source include/one_thread_per_connection.inc +# Save the initial number of concurrent sessions +--source include/count_sessions.inc + # -# Bug#8731: wait_timeout does not work on Mac OS X +# Bug#8731 wait_timeout does not work on Mac OS X # let $start_value= `SELECT @@global.wait_timeout`; @@ -113,6 +116,7 @@ while (!`select @aborted_clients`) # the disconnect has reached client let $wait_condition= SELECT COUNT(*)=1 FROM information_schema.processlist; --source include/wait_condition.inc +disconnect wait_con; --echo connection con1; connection con1; @@ -126,8 +130,13 @@ SELECT 2; SELECT 3; --replace_result $start_value <start_value> eval SET @@global.wait_timeout= $start_value; -# ML: The start value might be changed in future ... --echo disconnection con1; disconnect con1; +# The last connect is to keep tools checking the current test happy. +connect (default,localhost,root,,test,,); + +# Wait till all disconnects are completed +--source include/wait_until_count_sessions.inc + diff --git a/mysql-test/t/xa.test b/mysql-test/t/xa.test index 591d7ac2c4d..04ecf518577 100644 --- a/mysql-test/t/xa.test +++ b/mysql-test/t/xa.test @@ -2,6 +2,10 @@ # WL#1756 # -- source include/have_innodb.inc + +# Save the initial number of concurrent sessions +--source include/count_sessions.inc + --disable_warnings drop table if exists t1, t2; --enable_warnings @@ -14,10 +18,10 @@ xa rollback 'test1'; select * from t1; xa start 'test2'; ---error 1399 +--error ER_XAER_RMFAIL xa start 'test-bad'; insert t1 values (20); ---error 1399 +--error ER_XAER_RMFAIL xa prepare 'test2'; xa end 'test2'; xa prepare 'test2'; @@ -27,22 +31,22 @@ select * from t1; xa start 'testa','testb'; insert t1 values (30); ---error 1399 +--error ER_XAER_RMFAIL commit; xa end 'testa','testb'; ---error 1399 +--error ER_XAER_RMFAIL begin; ---error 1399 +--error ER_XAER_RMFAIL create table t2 (a int); connect (con1,localhost,root,,); connection con1; ---error 1440 +--error ER_XAER_DUPID xa start 'testa','testb'; ---error 1440 +--error ER_XAER_DUPID xa start 'testa','testb', 123; # gtrid [ , bqual [ , formatID ] ] @@ -51,7 +55,7 @@ insert t1 values (40); xa end 'testb',' 0@P`',11; xa prepare 'testb',0x2030405060,11; ---error 1399 +--error ER_XAER_RMFAIL start transaction; xa recover; @@ -64,11 +68,11 @@ xa prepare 'testa','testb'; xa recover; ---error 1397 +--error ER_XAER_NOTA xa commit 'testb',0x2030405060,11; xa rollback 'testa','testb'; ---error 1064 +--error ER_PARSE_ERROR xa start 'zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz'; select * from t1; @@ -119,3 +123,7 @@ xa start 'a','c'; drop table t1; --echo End of 5.0 tests + +# Wait till all disconnects are completed +--source include/wait_until_count_sessions.inc + |