diff options
Diffstat (limited to 'mysql-test/r')
66 files changed, 1123 insertions, 91 deletions
diff --git a/mysql-test/r/1st.result b/mysql-test/r/1st.result index 792d9eaf2f1..f8e4754f861 100644 --- a/mysql-test/r/1st.result +++ b/mysql-test/r/1st.result @@ -17,12 +17,17 @@ help_keyword help_relation help_topic host +innodb_index_stats +innodb_table_stats ndb_binlog_index plugin proc procs_priv proxies_priv servers +slave_master_info +slave_relay_log_info +slave_worker_info slow_log tables_priv time_zone diff --git a/mysql-test/r/alter_table-big.result b/mysql-test/r/alter_table-big.result index 33af60938a1..d1ec258873f 100644 --- a/mysql-test/r/alter_table-big.result +++ b/mysql-test/r/alter_table-big.result @@ -14,7 +14,7 @@ set debug_sync='now WAIT_FOR parked'; insert into t2 values (1); insert into t1 values (1, 1, 1);; set debug_sync='now SIGNAL go'; -show binlog events from <binlog_start>; +include/show_binlog_events.inc Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query # # BEGIN master-bin.000001 # Query # # use `test`; insert into t2 values (1) @@ -57,7 +57,7 @@ rename table t1 to t3; drop table t3; set debug_sync='alter_table_before_main_binlog SIGNAL parked WAIT_FOR go'; set debug_sync='RESET'; -show binlog events from <binlog_start>; +include/show_binlog_events.inc Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query # # use `test`; alter table t1 change i c char(10) default 'Test1' master-bin.000001 # Query # # BEGIN diff --git a/mysql-test/r/bug58669.result b/mysql-test/r/bug58669.result index 03032e27e94..c9bd43e244b 100644 --- a/mysql-test/r/bug58669.result +++ b/mysql-test/r/bug58669.result @@ -8,7 +8,7 @@ CREATE TABLE db1.t1(a INT); SELECT CURRENT_USER(); CURRENT_USER() user1@localhost -SHOW VARIABLES LIKE "%read_only%"; +SHOW VARIABLES LIKE "read_only%"; Variable_name Value read_only ON INSERT INTO db1.t1 VALUES (1); diff --git a/mysql-test/r/commit.result b/mysql-test/r/commit.result index 8620d2077c6..a62d2940d85 100644 --- a/mysql-test/r/commit.result +++ b/mysql-test/r/commit.result @@ -12,7 +12,7 @@ INSERT INTO t1 VALUES (1),(2); COMMIT; START TRANSACTION; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; -ERROR 25001: Transaction isolation level can't be changed while a transaction is in progress +ERROR 25001: Transaction characteristics can't be changed while a transaction is in progress COMMIT; SET @@autocommit=0; COMMIT; @@ -264,9 +264,313 @@ Should only read the '1000' as this transaction is now in REP READ COMMIT AND NO CHAIN; SET @@completion_type=0; COMMIT AND NO CHAIN; -SET @autocommit=1; +SET @@autocommit=1; COMMIT; DROP TABLE t1; # # End of test cases for Bug#20837 # +# +# WL#5968 Implement START TRANSACTION READ (WRITE|ONLY); +# +# +# Test 1: Check supported syntax +START TRANSACTION; +COMMIT; +START TRANSACTION READ ONLY; +COMMIT; +START TRANSACTION READ WRITE; +COMMIT; +START TRANSACTION READ ONLY, READ WRITE; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1 +START TRANSACTION READ ONLY, WITH CONSISTENT SNAPSHOT; +COMMIT; +START TRANSACTION READ WRITE, WITH CONSISTENT SNAPSHOT; +COMMIT; +START TRANSACTION WITH CONSISTENT SNAPSHOT, READ ONLY; +COMMIT; +START TRANSACTION WITH CONSISTENT SNAPSHOT, READ WRITE; +COMMIT; +START TRANSACTION READ ONLY, WITH CONSISTENT SNAPSHOT, READ WRITE; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1 +SET TRANSACTION READ ONLY; +SET TRANSACTION READ WRITE; +SET TRANSACTION ISOLATION LEVEL SERIALIZABLE, READ ONLY; +SET TRANSACTION ISOLATION LEVEL SERIALIZABLE, READ WRITE; +SET TRANSACTION READ ONLY, ISOLATION LEVEL READ COMMITTED; +SET TRANSACTION READ WRITE, ISOLATION LEVEL READ COMMITTED; +SET TRANSACTION READ ONLY, READ WRITE; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'READ WRITE' at line 1 +COMMIT; +# +# Test 2: Check setting of variable. +SET SESSION TRANSACTION READ WRITE; +SELECT @@tx_read_only; +@@tx_read_only +0 +SET SESSION TRANSACTION READ ONLY; +SELECT @@tx_read_only; +@@tx_read_only +1 +SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ, READ WRITE; +SELECT @@tx_read_only; +@@tx_read_only +0 +SET SESSION TRANSACTION READ ONLY, ISOLATION LEVEL REPEATABLE READ; +SELECT @@tx_read_only; +@@tx_read_only +1 +START TRANSACTION; +# Not allowed inside a transaction +SET TRANSACTION READ ONLY; +ERROR 25001: Transaction characteristics can't be changed while a transaction is in progress +# But these are allowed. +SET SESSION TRANSACTION READ ONLY; +SET GLOBAL TRANSACTION READ ONLY; +COMMIT; +SET SESSION TRANSACTION READ WRITE; +SET GLOBAL TRANSACTION READ WRITE; +# +# Test 3: Test that write operations are properly blocked. +CREATE TABLE t1(a INT); +CREATE TEMPORARY TABLE temp_t2(a INT); +SET SESSION TRANSACTION READ ONLY; +# 1: DDL should be blocked, also on temporary tables. +CREATE TABLE t3(a INT); +ERROR 25006: Cannot execute statement in a READ ONLY transaction. +ALTER TABLE t1 COMMENT "Test"; +ERROR 25006: Cannot execute statement in a READ ONLY transaction. +DROP TABLE t1; +ERROR 25006: Cannot execute statement in a READ ONLY transaction. +CREATE TEMPORARY TABLE temp_t3(a INT); +ERROR 25006: Cannot execute statement in a READ ONLY transaction. +ALTER TABLE temp_t2 COMMENT "Test"; +ERROR 25006: Cannot execute statement in a READ ONLY transaction. +DROP TEMPORARY TABLE temp_t2; +ERROR 25006: Cannot execute statement in a READ ONLY transaction. +CREATE FUNCTION f1() RETURNS INT RETURN 1; +ERROR 25006: Cannot execute statement in a READ ONLY transaction. +DROP FUNCTION f1; +ERROR 25006: Cannot execute statement in a READ ONLY transaction. +CREATE PROCEDURE p1() BEGIN END; +ERROR 25006: Cannot execute statement in a READ ONLY transaction. +DROP PROCEDURE p1; +ERROR 25006: Cannot execute statement in a READ ONLY transaction. +CREATE VIEW v1 AS SELECT 1; +ERROR 25006: Cannot execute statement in a READ ONLY transaction. +SET SESSION TRANSACTION READ WRITE; +CREATE VIEW v1 AS SELECT 1; +SET SESSION TRANSACTION READ ONLY; +DROP VIEW v1; +ERROR 25006: Cannot execute statement in a READ ONLY transaction. +SET SESSION TRANSACTION READ WRITE; +DROP VIEW v1; +SET SESSION TRANSACTION READ ONLY; +RENAME TABLE t1 TO t2; +ERROR 25006: Cannot execute statement in a READ ONLY transaction. +RENAME TABLE temp_t2 TO temp_t3; +ERROR 25006: Cannot execute statement in a READ ONLY transaction. +TRUNCATE TABLE t1; +ERROR 25006: Cannot execute statement in a READ ONLY transaction. +CREATE DATABASE db1; +ERROR 25006: Cannot execute statement in a READ ONLY transaction. +DROP DATABASE db1; +ERROR 25006: Cannot execute statement in a READ ONLY transaction. +SET SESSION TRANSACTION READ WRITE; +# 2: DML should be blocked on non-temporary tables. +START TRANSACTION READ ONLY; +INSERT INTO t1 VALUES (1), (2); +ERROR 25006: Cannot execute statement in a READ ONLY transaction. +UPDATE t1 SET a= 3; +ERROR 25006: Cannot execute statement in a READ ONLY transaction. +DELETE FROM t1; +ERROR 25006: Cannot execute statement in a READ ONLY transaction. +# 3: DML should be allowed on temporary tables. +INSERT INTO temp_t2 VALUES (1), (2); +UPDATE temp_t2 SET a= 3; +DELETE FROM temp_t2; +# 4: Queries should not be blocked. +SELECT * FROM t1; +a +SELECT * FROM temp_t2; +a +HANDLER t1 OPEN; +HANDLER t1 READ FIRST; +a +HANDLER t1 CLOSE; +HANDLER temp_t2 OPEN; +HANDLER temp_t2 READ FIRST; +a +HANDLER temp_t2 CLOSE; +# 5: Prepared statements +PREPARE stmt FROM "DELETE FROM t1"; +ERROR 25006: Cannot execute statement in a READ ONLY transaction. +PREPARE stmt FROM "DELETE FROM temp_t2"; +EXECUTE stmt; +DEALLOCATE PREPARE stmt; +COMMIT; +# 6: Stored routines +CREATE FUNCTION f1() RETURNS INT +BEGIN +DELETE FROM t1; +RETURN 1; +END| +CREATE FUNCTION f2() RETURNS INT +BEGIN +DELETE FROM temp_t2; +RETURN 1; +END| +CREATE PROCEDURE p1() DELETE FROM t1; +CREATE PROCEDURE p2() DELETE FROM temp_t2; +START TRANSACTION READ ONLY; +SELECT f1(); +ERROR 25006: Cannot execute statement in a READ ONLY transaction. +SELECT f2(); +f2() +1 +CALL p1(); +ERROR 25006: Cannot execute statement in a READ ONLY transaction. +CALL p2(); +COMMIT; +DROP FUNCTION f1; +DROP FUNCTION f2; +DROP PROCEDURE p1; +DROP PROCEDURE p2; +# 7: Views +CREATE VIEW v1 AS SELECT a FROM t1; +START TRANSACTION READ ONLY; +INSERT INTO v1 VALUES (1), (2); +ERROR 25006: Cannot execute statement in a READ ONLY transaction. +SELECT * FROM v1; +a +COMMIT; +DROP VIEW v1; +# 8: LOCK TABLE +SET SESSION TRANSACTION READ ONLY; +LOCK TABLE t1 WRITE; +ERROR 25006: Cannot execute statement in a READ ONLY transaction. +LOCK TABLE t1 READ; +UNLOCK TABLES; +SET SESSION TRANSACTION READ WRITE; +DROP TABLE temp_t2, t1; +# +# Test 4: SET TRANSACTION, CHAINing transactions +CREATE TABLE t1(a INT); +SET SESSION TRANSACTION READ ONLY; +START TRANSACTION; +DELETE FROM t1; +ERROR 25006: Cannot execute statement in a READ ONLY transaction. +COMMIT; +START TRANSACTION READ WRITE; +DELETE FROM t1; +COMMIT; +SET SESSION TRANSACTION READ WRITE; +SET TRANSACTION READ ONLY; +START TRANSACTION; +DELETE FROM t1; +ERROR 25006: Cannot execute statement in a READ ONLY transaction. +COMMIT; +START TRANSACTION READ WRITE; +DELETE FROM t1; +COMMIT; +START TRANSACTION READ ONLY; +SELECT * FROM t1; +a +COMMIT AND CHAIN; +DELETE FROM t1; +ERROR 25006: Cannot execute statement in a READ ONLY transaction. +COMMIT; +START TRANSACTION READ ONLY; +SELECT * FROM t1; +a +ROLLBACK AND CHAIN; +DELETE FROM t1; +ERROR 25006: Cannot execute statement in a READ ONLY transaction. +COMMIT; +DROP TABLE t1; +# +# Test 5: Test that reserved keyword ONLY is still allowed as +# identifier - both directly and in SPs. +SET @only= 1; +CREATE TABLE t1 (only INT); +INSERT INTO t1 (only) values (1); +SELECT only FROM t1 WHERE only = 1; +only +1 +DROP TABLE t1; +CREATE PROCEDURE p1() +BEGIN +DECLARE only INT DEFAULT 1; +END| +CALL p1(); +DROP PROCEDURE p1; +# +# Test 6: Check that XA transactions obey default access mode. +CREATE TABLE t1(a INT); +SET TRANSACTION READ ONLY; +XA START 'test1'; +INSERT INTO t1 VALUES (1); +ERROR 25006: Cannot execute statement in a READ ONLY transaction. +UPDATE t1 SET a=2; +ERROR 25006: Cannot execute statement in a READ ONLY transaction. +DELETE FROM t1; +ERROR 25006: Cannot execute statement in a READ ONLY transaction. +XA END 'test1'; +XA PREPARE 'test1'; +XA COMMIT 'test1'; +DROP TABLE t1; +# +# Test 7: SET TRANSACTION inside stored routines +CREATE PROCEDURE p1() SET SESSION TRANSACTION READ ONLY; +CALL p1(); +SELECT @@tx_read_only; +@@tx_read_only +1 +SET SESSION TRANSACTION READ WRITE; +DROP PROCEDURE p1; +CREATE PROCEDURE p1() SET SESSION TRANSACTION READ ONLY, +ISOLATION LEVEL SERIALIZABLE; +CALL p1(); +SELECT @@tx_read_only; +@@tx_read_only +1 +SET SESSION TRANSACTION READ WRITE, ISOLATION LEVEL REPEATABLE READ; +DROP PROCEDURE p1; +CREATE FUNCTION f1() RETURNS INT +BEGIN +SET SESSION TRANSACTION READ ONLY; +RETURN 1; +END| +SELECT f1(); +f1() +1 +SELECT @@tx_read_only; +@@tx_read_only +1 +SET SESSION TRANSACTION READ WRITE; +DROP FUNCTION f1; +CREATE FUNCTION f1() RETURNS INT +BEGIN +SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE, READ ONLY; +RETURN 1; +END| +SELECT f1(); +f1() +1 +SELECT @@tx_read_only; +@@tx_read_only +1 +SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ, READ WRITE; +DROP FUNCTION f1; +# +# Test 8: SET TRANSACTION and auto-commit +SELECT @@autocommit; +@@autocommit +1 +CREATE TABLE t1(a INT) engine=InnoDB; +SET TRANSACTION READ ONLY; +SELECT * FROM t1; +a +# This statement should work, since last statement committed. +INSERT INTO t1 VALUES (1); +DROP TABLE t1; diff --git a/mysql-test/r/connect.result b/mysql-test/r/connect.result index 09eaf9855b0..2150df99030 100644 --- a/mysql-test/r/connect.result +++ b/mysql-test/r/connect.result @@ -11,12 +11,17 @@ help_keyword help_relation help_topic host +innodb_index_stats +innodb_table_stats ndb_binlog_index plugin proc procs_priv proxies_priv servers +slave_master_info +slave_relay_log_info +slave_worker_info slow_log tables_priv time_zone @@ -45,12 +50,17 @@ help_keyword help_relation help_topic host +innodb_index_stats +innodb_table_stats ndb_binlog_index plugin proc procs_priv proxies_priv servers +slave_master_info +slave_relay_log_info +slave_worker_info slow_log tables_priv time_zone @@ -87,12 +97,17 @@ help_keyword help_relation help_topic host +innodb_index_stats +innodb_table_stats ndb_binlog_index plugin proc procs_priv proxies_priv servers +slave_master_info +slave_relay_log_info +slave_worker_info slow_log tables_priv time_zone diff --git a/mysql-test/r/create-big.result b/mysql-test/r/create-big.result index 4cce5d8618c..783c646545c 100644 --- a/mysql-test/r/create-big.result +++ b/mysql-test/r/create-big.result @@ -186,7 +186,7 @@ t2 CREATE TABLE `t2` ( `i` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t2; -show binlog events from <binlog_start>; +include/show_binlog_events.inc Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query # # BEGIN master-bin.000001 # Query # # use `test`; insert into t1 values (1) @@ -214,7 +214,7 @@ drop table t1;; set debug_sync='now SIGNAL go'; drop table t2; set debug_sync='RESET'; -show binlog events from <binlog_start>; +include/show_binlog_events.inc Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query # # use `test`; create table t2 like t1 master-bin.000001 # Query # # BEGIN diff --git a/mysql-test/r/ctype_cp932_binlog_row.result b/mysql-test/r/ctype_cp932_binlog_row.result index cbac6b14669..f231c6a519d 100644 --- a/mysql-test/r/ctype_cp932_binlog_row.result +++ b/mysql-test/r/ctype_cp932_binlog_row.result @@ -6,7 +6,7 @@ CREATE TABLE t1(f1 blob); PREPARE stmt1 FROM 'INSERT INTO t1 VALUES(?)'; SET @var1= x'8300'; EXECUTE stmt1 USING @var1; -show binlog events from <binlog_start>; +include/show_binlog_events.inc Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query # # use `test`; CREATE TABLE t1(f1 blob) master-bin.000001 # Query # # BEGIN diff --git a/mysql-test/r/ctype_cp932_binlog_stm.result b/mysql-test/r/ctype_cp932_binlog_stm.result index 1e62787835f..93e4bebcd8f 100644 --- a/mysql-test/r/ctype_cp932_binlog_stm.result +++ b/mysql-test/r/ctype_cp932_binlog_stm.result @@ -6,7 +6,7 @@ CREATE TABLE t1(f1 blob); PREPARE stmt1 FROM 'INSERT INTO t1 VALUES(?)'; SET @var1= x'8300'; EXECUTE stmt1 USING @var1; -show binlog events from <binlog_start>; +include/show_binlog_events.inc Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query # # use `test`; CREATE TABLE t1(f1 blob) master-bin.000001 # Query # # BEGIN @@ -32,7 +32,7 @@ HEX(s1) HEX(s2) d 466F6F2773206120426172 ED40ED41ED42 47.93 DROP PROCEDURE bug18293| DROP TABLE t4| -show binlog events from <binlog_start>| +include/show_binlog_events.inc Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query # # use `test`; CREATE TABLE t4 (s1 CHAR(50) CHARACTER SET latin1, s2 CHAR(50) CHARACTER SET cp932, diff --git a/mysql-test/r/events_2.result b/mysql-test/r/events_2.result index 66ec00d7357..3b7f3566cab 100644 --- a/mysql-test/r/events_2.result +++ b/mysql-test/r/events_2.result @@ -378,7 +378,7 @@ do select 1; alter event event_35981 on schedule every 1 hour starts '1999-01-01 00:00:00' ends '1999-01-02 00:00:00'; -ERROR HY000: Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was dropped immediately after creation. +ERROR HY000: Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was not changed. Specify a time in the future. drop event event_35981; create event event_35981 on schedule every 1 hour starts current_timestamp on completion not preserve @@ -399,7 +399,7 @@ Warnings: Note 1544 Event execution time is in the past. Event has been disabled alter event event_35981 on schedule every 1 hour starts '1999-01-01 00:00:00' ends '1999-01-02 00:00:00' on completion not preserve; -ERROR HY000: Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was dropped immediately after creation. +ERROR HY000: Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was not changed. Specify a time in the future. alter event event_35981 on schedule every 1 hour starts '1999-01-01 00:00:00' ends '1999-01-02 00:00:00' on completion preserve; Warnings: diff --git a/mysql-test/r/events_bugs.result b/mysql-test/r/events_bugs.result index 30bc4f89d8c..fe90bd3288b 100644 --- a/mysql-test/r/events_bugs.result +++ b/mysql-test/r/events_bugs.result @@ -454,10 +454,10 @@ e3 +00:00 CREATE DEFINER=`root`@`localhost` EVENT `e3` ON SCHEDULE EVERY 1 DAY The following should fail, and nothing should be altered. ALTER EVENT e1 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00' ENDS '1999-01-02 00:00:00'; -ERROR HY000: Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was dropped immediately after creation. +ERROR HY000: Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was not changed. Specify a time in the future. ALTER EVENT e1 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00' ENDS '1999-01-02 00:00:00' DISABLE; -ERROR HY000: Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was dropped immediately after creation. +ERROR HY000: Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was not changed. Specify a time in the future. The following should give warnings, and nothing should be created. CREATE EVENT e4 ON SCHEDULE EVERY 1 HOUR STARTS '1999-01-01 00:00:00' ENDS '1999-01-02 00:00:00' diff --git a/mysql-test/r/flush_block_commit_notembedded.result b/mysql-test/r/flush_block_commit_notembedded.result index 08643527b75..130146bf252 100644 --- a/mysql-test/r/flush_block_commit_notembedded.result +++ b/mysql-test/r/flush_block_commit_notembedded.result @@ -10,13 +10,11 @@ SELECT 1; 1 # Switch to connection con2 FLUSH TABLES WITH READ LOCK; -show binlog events from <binlog_start>; -Log_name Pos Event_type Server_id End_log_pos Info +include/show_binlog_events.inc # Switch to connection con1 INSERT INTO t1 VALUES (1); # Switch to connection con2 -show binlog events from <binlog_start>; -Log_name Pos Event_type Server_id End_log_pos Info +include/show_binlog_events.inc UNLOCK TABLES; # Switch to connection con1 DROP TABLE t1; diff --git a/mysql-test/r/grant.result b/mysql-test/r/grant.result index 10caf4cd10f..f1cf94e4f19 100644 --- a/mysql-test/r/grant.result +++ b/mysql-test/r/grant.result @@ -612,7 +612,7 @@ create database mysqltest; create table mysqltest.t1 (a int,b int,c int); grant all on mysqltest.t1 to mysqltest_1@localhost; alter table t1 rename t2; -ERROR 42000: INSERT,CREATE command denied to user 'mysqltest_1'@'localhost' for table 't2' +ERROR 42000: INSERT, CREATE command denied to user 'mysqltest_1'@'localhost' for table 't2' revoke all privileges on mysqltest.t1 from mysqltest_1@localhost; delete from mysql.user where user=_binary'mysqltest_1'; drop database mysqltest; @@ -1106,9 +1106,9 @@ Grants for mysqltest_1@localhost GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' GRANT SELECT ON `mysqltest1`.`t1` TO 'mysqltest_1'@'localhost' RENAME TABLE t1 TO t2; -ERROR 42000: DROP,ALTER command denied to user 'mysqltest_1'@'localhost' for table 't1' +ERROR 42000: DROP, ALTER command denied to user 'mysqltest_1'@'localhost' for table 't1' ALTER TABLE t1 RENAME TO t2; -ERROR 42000: DROP,ALTER command denied to user 'mysqltest_1'@'localhost' for table 't1' +ERROR 42000: DROP, ALTER command denied to user 'mysqltest_1'@'localhost' for table 't1' GRANT DROP ON mysqltest1.t1 TO mysqltest_1@localhost; RENAME TABLE t1 TO t2; ERROR 42000: ALTER command denied to user 'mysqltest_1'@'localhost' for table 't1' @@ -1120,9 +1120,9 @@ Grants for mysqltest_1@localhost GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' GRANT SELECT, DROP, ALTER ON `mysqltest1`.`t1` TO 'mysqltest_1'@'localhost' RENAME TABLE t1 TO t2; -ERROR 42000: INSERT,CREATE command denied to user 'mysqltest_1'@'localhost' for table 't2' +ERROR 42000: INSERT, CREATE command denied to user 'mysqltest_1'@'localhost' for table 't2' ALTER TABLE t1 RENAME TO t2; -ERROR 42000: INSERT,CREATE command denied to user 'mysqltest_1'@'localhost' for table 't2' +ERROR 42000: INSERT, CREATE command denied to user 'mysqltest_1'@'localhost' for table 't2' GRANT INSERT, CREATE ON mysqltest1.t1 TO mysqltest_1@localhost; SHOW GRANTS; Grants for mysqltest_1@localhost diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result index ea4af4590e0..1983d1cc784 100644 --- a/mysql-test/r/group_by.result +++ b/mysql-test/r/group_by.result @@ -359,7 +359,7 @@ A 4 B 4 a 1 b 1 -SET SQL_BIG_TABLES=1; +SET BIG_TABLES=1; SELECT a FROM t1 GROUP BY a; a NULL @@ -398,7 +398,7 @@ A 4 B 4 a 1 b 1 -SET SQL_BIG_TABLES=0; +SET BIG_TABLES=0; drop table t1; CREATE TABLE t1 ( `a` char(193) default NULL, @@ -515,7 +515,7 @@ a count(*) NULL 9 3 b 1 -set option sql_big_tables=1; +set big_tables=1; select a,count(*) from t1 group by a; a count(*) NULL 9 @@ -1918,7 +1918,7 @@ DROP TABLE t1; # Bug#11765254 (58200): Assertion failed: param.sort_length when grouping # by functions # -SET SQL_BIG_TABLES=1; +SET BIG_TABLES=1; CREATE TABLE t1(a INT); INSERT INTO t1 VALUES (0),(0); SELECT 1 FROM t1 GROUP BY IF(`a`,'',''); @@ -1945,7 +1945,7 @@ Warning 1292 Truncated incorrect INTEGER value: 'jxW<' Warning 1292 Truncated incorrect INTEGER value: 'K' Warning 1292 Truncated incorrect INTEGER value: 'jxW<' DROP TABLE t1; -SET SQL_BIG_TABLES=0; +SET BIG_TABLES=0; # End of 5.1 tests # # LP bug#694450 Wrong result with non-standard GROUP BY + ORDER BY diff --git a/mysql-test/r/have_ssl.require b/mysql-test/r/have_ssl.require deleted file mode 100644 index 9bdffdf0c13..00000000000 --- a/mysql-test/r/have_ssl.require +++ /dev/null @@ -1,2 +0,0 @@ -Variable_name Value -have_ssl YES diff --git a/mysql-test/r/have_ssl_is_yes_or_disabled_only.require b/mysql-test/r/have_ssl_is_yes_or_disabled_only.require deleted file mode 100644 index dfd6d2dad01..00000000000 --- a/mysql-test/r/have_ssl_is_yes_or_disabled_only.require +++ /dev/null @@ -1,2 +0,0 @@ -Variable_name Value -have_ssl yesordisabled diff --git a/mysql-test/r/init_file_longline_3816.result b/mysql-test/r/init_file_longline_3816.result new file mode 100644 index 00000000000..0155f764592 --- /dev/null +++ b/mysql-test/r/init_file_longline_3816.result @@ -0,0 +1,3 @@ +select count(*) from t4; +count(*) +0 diff --git a/mysql-test/r/innodb_mrr_cpk.result b/mysql-test/r/innodb_mrr_cpk.result index 90f59b96e61..15ef32447a8 100644 --- a/mysql-test/r/innodb_mrr_cpk.result +++ b/mysql-test/r/innodb_mrr_cpk.result @@ -149,3 +149,25 @@ set @@join_cache_level= @save_join_cache_level; set storage_engine=@save_storage_engine; set optimizer_switch=@innodb_mrr_cpk_tmp; drop table t0; +# +# MDEV-3817: Wrong result with index_merge+index_merge_intersection, InnoDB table, join, AND and OR conditions +# +set @tmp_mdev3817=@@optimizer_switch; +SET optimizer_switch='index_merge=on,index_merge_intersection=on'; +CREATE TABLE t1 ( +a INT PRIMARY KEY, +b INT, +c VARCHAR(1024) CHARACTER SET utf8, +d INT, +KEY (b) +) ENGINE=InnoDB; +INSERT INTO t1 VALUES +(1, 9, 'one', 11), (2, 6, 'two', 12), (3, 2, 'three', 13), (4, 5, 'four', 14); +CREATE TABLE t2 (e INT, g INT) ENGINE=InnoDB; +INSERT INTO t2 VALUES (1,9), (2,6) ; +SELECT * FROM t1, t2 WHERE g = b AND ( a < 7 OR a > e ); +a b c d e g +1 9 one 11 1 9 +2 6 two 12 2 6 +DROP TABLE t1, t2; +set optimizer_switch=@tmp_mdev3817; diff --git a/mysql-test/r/innodb_mysql_lock.result b/mysql-test/r/innodb_mysql_lock.result index 4c7e7f11987..70b86017008 100644 --- a/mysql-test/r/innodb_mysql_lock.result +++ b/mysql-test/r/innodb_mysql_lock.result @@ -1,3 +1,6 @@ +set @old_innodb_lock_wait_timeout=@@global.innodb_lock_wait_timeout; +set global innodb_lock_wait_timeout=300; +set session innodb_lock_wait_timeout=300; # # Bug #22876 Four-way deadlock # @@ -172,3 +175,4 @@ id value 1 12345 COMMIT; DROP TABLE t1; +set global innodb_lock_wait_timeout=@old_innodb_lock_wait_timeout; diff --git a/mysql-test/r/insert_notembedded.result b/mysql-test/r/insert_notembedded.result index 2315d695abe..ba3e2221922 100644 --- a/mysql-test/r/insert_notembedded.result +++ b/mysql-test/r/insert_notembedded.result @@ -32,7 +32,7 @@ INNER JOIN view_stations AS stations ON table_source.id = stations.icao LEFT JOIN table_target AS old USING (mexs_id); -ERROR 42000: INSERT,DELETE command denied to user 'user20989'@'localhost' for table 'table_target' +ERROR 42000: INSERT, DELETE command denied to user 'user20989'@'localhost' for table 'table_target' REPLACE INTO view_target2 SELECT stations.mexs_id AS mexs_id, datetime AS messzeit FROM table_source @@ -40,7 +40,7 @@ INNER JOIN view_stations AS stations ON table_source.id = stations.icao LEFT JOIN view_target2 AS old USING (mexs_id); -ERROR 42000: INSERT,DELETE command denied to user 'user20989'@'localhost' for table 'view_target2' +ERROR 42000: INSERT, DELETE command denied to user 'user20989'@'localhost' for table 'view_target2' REPLACE INTO view_target3 SELECT stations.mexs_id AS mexs_id, datetime AS messzeit FROM table_source @@ -60,7 +60,7 @@ ON table_source.id = stations.icao LEFT JOIN table_target AS old USING (mexs_id); REPLACE INTO table_target2 VALUES ('00X45Y78','2006-07-12 07:50:00'); -ERROR 42000: INSERT,DELETE command denied to user 'user20989'@'localhost' for table 'table_target2' +ERROR 42000: INSERT, DELETE command denied to user 'user20989'@'localhost' for table 'table_target2' REPLACE INTO view_target2 VALUES ('12X45Y78','2006-07-12 07:50:00'); SELECT stations.mexs_id AS mexs_id, datetime AS messzeit FROM table_source diff --git a/mysql-test/r/log_tables.result b/mysql-test/r/log_tables.result index dd3bee0ac88..76784be1e16 100644 --- a/mysql-test/r/log_tables.result +++ b/mysql-test/r/log_tables.result @@ -363,6 +363,7 @@ Tables_in_mysql (%log%) general_log general_log_new ndb_binlog_index +slave_relay_log_info slow_log slow_log_new drop table slow_log_new, general_log_new; diff --git a/mysql-test/r/log_tables_upgrade.result b/mysql-test/r/log_tables_upgrade.result index 5ed59eecc31..9a123d73e45 100644 --- a/mysql-test/r/log_tables_upgrade.result +++ b/mysql-test/r/log_tables_upgrade.result @@ -28,6 +28,8 @@ mysql.help_keyword OK mysql.help_relation OK mysql.help_topic OK mysql.host OK +mysql.innodb_index_stats OK +mysql.innodb_table_stats OK mysql.ndb_binlog_index OK mysql.plugin OK mysql.proc OK @@ -35,6 +37,9 @@ mysql.procs_priv OK mysql.proxies_priv OK mysql.renamed_general_log OK mysql.servers OK +mysql.slave_master_info OK +mysql.slave_relay_log_info OK +mysql.slave_worker_info OK mysql.tables_priv OK mysql.time_zone OK mysql.time_zone_leap_second OK diff --git a/mysql-test/r/lowercase_table4.result b/mysql-test/r/lowercase_table4.result index f896b9008e3..aa81eff5194 100755..100644 --- a/mysql-test/r/lowercase_table4.result +++ b/mysql-test/r/lowercase_table4.result @@ -30,6 +30,17 @@ Create Table CREATE TABLE `Table2` ( ) ENGINE=InnoDB DEFAULT CHARSET=latin1 SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS; CONSTRAINT_CATALOG def +CONSTRAINT_SCHEMA mysql +CONSTRAINT_NAME innodb_index_stats_ibfk_1 +UNIQUE_CONSTRAINT_CATALOG def +UNIQUE_CONSTRAINT_SCHEMA mysql +UNIQUE_CONSTRAINT_NAME PRIMARY +MATCH_OPTION NONE +UPDATE_RULE RESTRICT +DELETE_RULE RESTRICT +TABLE_NAME innodb_index_stats +REFERENCED_TABLE_NAME innodb_table_stats +CONSTRAINT_CATALOG def CONSTRAINT_SCHEMA test CONSTRAINT_NAME fk1 UNIQUE_CONSTRAINT_CATALOG def @@ -89,6 +100,17 @@ Create Table CREATE TABLE `Customer` ( ) ENGINE=InnoDB DEFAULT CHARSET=latin1 SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS; CONSTRAINT_CATALOG def +CONSTRAINT_SCHEMA mysql +CONSTRAINT_NAME innodb_index_stats_ibfk_1 +UNIQUE_CONSTRAINT_CATALOG def +UNIQUE_CONSTRAINT_SCHEMA mysql +UNIQUE_CONSTRAINT_NAME PRIMARY +MATCH_OPTION NONE +UPDATE_RULE RESTRICT +DELETE_RULE RESTRICT +TABLE_NAME innodb_index_stats +REFERENCED_TABLE_NAME innodb_table_stats +CONSTRAINT_CATALOG def CONSTRAINT_SCHEMA test CONSTRAINT_NAME product_order_ibfk_1 UNIQUE_CONSTRAINT_CATALOG def diff --git a/mysql-test/r/multi_update.result b/mysql-test/r/multi_update.result index e991326cdce..55e5db231a9 100644 --- a/mysql-test/r/multi_update.result +++ b/mysql-test/r/multi_update.result @@ -603,7 +603,7 @@ select * from t2 /* must be (3,1), (4,4) */; a b 3 1 4 4 -show binlog events from <binlog_start>; +include/show_binlog_events.inc Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query # # BEGIN master-bin.000001 # Table_map # # table_id: # (test.t2) @@ -616,7 +616,7 @@ insert into t2 values (1,2),(3,4),(4,4); reset master; UPDATE t2,t1 SET t2.a=t2.b where t2.a=t1.a; ERROR 23000: Duplicate entry '4' for key 'PRIMARY' -show binlog events from <binlog_start>; +include/show_binlog_events.inc Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query # # BEGIN master-bin.000001 # Table_map # # table_id: # (test.t2) diff --git a/mysql-test/r/mysql_upgrade.result b/mysql-test/r/mysql_upgrade.result index 247c2b80d62..b526ce6e0dd 100644 --- a/mysql-test/r/mysql_upgrade.result +++ b/mysql-test/r/mysql_upgrade.result @@ -16,12 +16,17 @@ mysql.help_keyword OK mysql.help_relation OK mysql.help_topic OK mysql.host OK +mysql.innodb_index_stats OK +mysql.innodb_table_stats OK mysql.ndb_binlog_index OK mysql.plugin OK mysql.proc OK mysql.procs_priv OK mysql.proxies_priv OK mysql.servers OK +mysql.slave_master_info OK +mysql.slave_relay_log_info OK +mysql.slave_worker_info OK mysql.tables_priv OK mysql.time_zone OK mysql.time_zone_leap_second OK @@ -53,12 +58,17 @@ mysql.help_keyword OK mysql.help_relation OK mysql.help_topic OK mysql.host OK +mysql.innodb_index_stats OK +mysql.innodb_table_stats OK mysql.ndb_binlog_index OK mysql.plugin OK mysql.proc OK mysql.procs_priv OK mysql.proxies_priv OK mysql.servers OK +mysql.slave_master_info OK +mysql.slave_relay_log_info OK +mysql.slave_worker_info OK mysql.tables_priv OK mysql.time_zone OK mysql.time_zone_leap_second OK @@ -90,12 +100,17 @@ mysql.help_keyword OK mysql.help_relation OK mysql.help_topic OK mysql.host OK +mysql.innodb_index_stats OK +mysql.innodb_table_stats OK mysql.ndb_binlog_index OK mysql.plugin OK mysql.proc OK mysql.procs_priv OK mysql.proxies_priv OK mysql.servers OK +mysql.slave_master_info OK +mysql.slave_relay_log_info OK +mysql.slave_worker_info OK mysql.tables_priv OK mysql.time_zone OK mysql.time_zone_leap_second OK @@ -130,12 +145,17 @@ mysql.help_keyword OK mysql.help_relation OK mysql.help_topic OK mysql.host OK +mysql.innodb_index_stats OK +mysql.innodb_table_stats OK mysql.ndb_binlog_index OK mysql.plugin OK mysql.proc OK mysql.procs_priv OK mysql.proxies_priv OK mysql.servers OK +mysql.slave_master_info OK +mysql.slave_relay_log_info OK +mysql.slave_worker_info OK mysql.tables_priv OK mysql.time_zone OK mysql.time_zone_leap_second OK @@ -173,12 +193,17 @@ mysql.help_keyword OK mysql.help_relation OK mysql.help_topic OK mysql.host OK +mysql.innodb_index_stats OK +mysql.innodb_table_stats OK mysql.ndb_binlog_index OK mysql.plugin OK mysql.proc OK mysql.procs_priv OK mysql.proxies_priv OK mysql.servers OK +mysql.slave_master_info OK +mysql.slave_relay_log_info OK +mysql.slave_worker_info OK mysql.tables_priv OK mysql.time_zone OK mysql.time_zone_leap_second OK @@ -219,12 +244,17 @@ mysql.help_keyword OK mysql.help_relation OK mysql.help_topic OK mysql.host OK +mysql.innodb_index_stats OK +mysql.innodb_table_stats OK mysql.ndb_binlog_index OK mysql.plugin OK mysql.proc OK mysql.procs_priv OK mysql.proxies_priv OK mysql.servers OK +mysql.slave_master_info OK +mysql.slave_relay_log_info OK +mysql.slave_worker_info OK mysql.tables_priv OK mysql.time_zone OK mysql.time_zone_leap_second OK @@ -268,12 +298,17 @@ mysql.help_keyword OK mysql.help_relation OK mysql.help_topic OK mysql.host OK +mysql.innodb_index_stats OK +mysql.innodb_table_stats OK mysql.ndb_binlog_index OK mysql.plugin OK mysql.proc OK mysql.procs_priv OK mysql.proxies_priv OK mysql.servers OK +mysql.slave_master_info OK +mysql.slave_relay_log_info OK +mysql.slave_worker_info OK mysql.tables_priv OK mysql.time_zone OK mysql.time_zone_leap_second OK diff --git a/mysql-test/r/mysql_upgrade_ssl.result b/mysql-test/r/mysql_upgrade_ssl.result index a08e7c115cc..683a4aa8c98 100644 --- a/mysql-test/r/mysql_upgrade_ssl.result +++ b/mysql-test/r/mysql_upgrade_ssl.result @@ -18,12 +18,17 @@ mysql.help_keyword OK mysql.help_relation OK mysql.help_topic OK mysql.host OK +mysql.innodb_index_stats OK +mysql.innodb_table_stats OK mysql.ndb_binlog_index OK mysql.plugin OK mysql.proc OK mysql.procs_priv OK mysql.proxies_priv OK mysql.servers OK +mysql.slave_master_info OK +mysql.slave_relay_log_info OK +mysql.slave_worker_info OK mysql.tables_priv OK mysql.time_zone OK mysql.time_zone_leap_second OK diff --git a/mysql-test/r/mysqlbinlog.result b/mysql-test/r/mysqlbinlog.result index 255b0679244..63b0ea2a71a 100644 --- a/mysql-test/r/mysqlbinlog.result +++ b/mysql-test/r/mysqlbinlog.result @@ -890,7 +890,7 @@ CREATE TABLE t1(id int); DROP TABLE t1; DROP DATABASE test1; FLUSH LOGS; -show binlog events in 'master-bin.000002' from <binlog_start>; +include/show_binlog_events.inc Log_name Pos Event_type Server_id End_log_pos Info master-bin.000002 # Binlog_checkpoint # # master-bin.000002 master-bin.000002 # Query # # CREATE DATABASE test1 diff --git a/mysql-test/r/mysqlcheck.result b/mysql-test/r/mysqlcheck.result index ab707ceef80..10d09b2a8a7 100644 --- a/mysql-test/r/mysqlcheck.result +++ b/mysql-test/r/mysqlcheck.result @@ -12,12 +12,17 @@ mysql.help_keyword OK mysql.help_relation OK mysql.help_topic OK mysql.host OK +mysql.innodb_index_stats OK +mysql.innodb_table_stats OK mysql.ndb_binlog_index OK mysql.plugin OK mysql.proc OK mysql.procs_priv OK mysql.proxies_priv OK mysql.servers OK +mysql.slave_master_info OK +mysql.slave_relay_log_info OK +mysql.slave_worker_info OK mysql.tables_priv OK mysql.time_zone OK mysql.time_zone_leap_second OK @@ -36,12 +41,21 @@ mysql.help_keyword OK mysql.help_relation OK mysql.help_topic OK mysql.host OK +mysql.innodb_index_stats +note : Table does not support optimize, doing recreate + analyze instead +status : OK +mysql.innodb_table_stats +note : Table does not support optimize, doing recreate + analyze instead +status : OK mysql.ndb_binlog_index OK mysql.plugin OK mysql.proc OK mysql.procs_priv OK mysql.proxies_priv OK mysql.servers OK +mysql.slave_master_info OK +mysql.slave_relay_log_info OK +mysql.slave_worker_info OK mysql.tables_priv OK mysql.time_zone OK mysql.time_zone_leap_second OK @@ -58,12 +72,17 @@ mysql.help_keyword OK mysql.help_relation OK mysql.help_topic OK mysql.host OK +mysql.innodb_index_stats OK +mysql.innodb_table_stats OK mysql.ndb_binlog_index OK mysql.plugin OK mysql.proc OK mysql.procs_priv OK mysql.proxies_priv OK mysql.servers OK +mysql.slave_master_info OK +mysql.slave_relay_log_info OK +mysql.slave_worker_info OK mysql.tables_priv OK mysql.time_zone OK mysql.time_zone_leap_second OK @@ -80,12 +99,21 @@ mysql.help_keyword Table is already up to date mysql.help_relation Table is already up to date mysql.help_topic Table is already up to date mysql.host Table is already up to date +mysql.innodb_index_stats +note : Table does not support optimize, doing recreate + analyze instead +status : OK +mysql.innodb_table_stats +note : Table does not support optimize, doing recreate + analyze instead +status : OK mysql.ndb_binlog_index Table is already up to date mysql.plugin Table is already up to date mysql.proc Table is already up to date mysql.procs_priv Table is already up to date mysql.proxies_priv Table is already up to date mysql.servers Table is already up to date +mysql.slave_master_info Table is already up to date +mysql.slave_relay_log_info Table is already up to date +mysql.slave_worker_info Table is already up to date mysql.tables_priv Table is already up to date mysql.time_zone Table is already up to date mysql.time_zone_leap_second Table is already up to date diff --git a/mysql-test/r/mysqld--help,win.rdiff b/mysql-test/r/mysqld--help,win.rdiff index cd39445f3e6..0fa6e41391f 100644 --- a/mysql-test/r/mysqld--help,win.rdiff +++ b/mysql-test/r/mysqld--help,win.rdiff @@ -1,5 +1,5 @@ ---- mysql-test/r/mysqld--help.result 2012-09-08 22:22:06 +0000 -+++ mysql-test/r/mysqld--help.result 2012-10-01 14:03:59 +0000 +--- r/mysqld--help.result 2012-10-25 14:02:57.000000000 +0200 ++++ mysqld--help,win.result~ 2012-10-30 11:37:08.000000000 +0100 @@ -244,7 +244,6 @@ The number of segments in a key cache -L, --language=name Client error messages in given language. May be given as @@ -8,7 +8,7 @@ --lc-messages=name Set the language used for the error messages. -L, --lc-messages-dir=name Directory where error messages are -@@ -440,6 +439,7 @@ +@@ -437,6 +436,7 @@ NULLS_UNEQUAL (default behavior for 4.1 and later), NULLS_EQUAL (emulate 4.0 behavior), and NULLS_IGNORED --myisam-use-mmap Use memory mapping for reading and writing MyISAM tables @@ -16,7 +16,7 @@ --net-buffer-length=# Buffer length for TCP/IP and socket communication --net-read-timeout=# -@@ -707,6 +707,9 @@ +@@ -774,6 +774,9 @@ files within specified directory --server-id=# Uniquely identifies the server instance in the community of replication partners @@ -26,7 +26,7 @@ --show-slave-auth-info Show user and password in SHOW SLAVE HOSTS on this master. -@@ -774,6 +777,10 @@ +@@ -841,6 +844,10 @@ Log slow queries to given log file. Defaults logging to 'hostname'-slow.log. Must be enabled to activate other slow log options @@ -37,7 +37,7 @@ --socket=name Socket file to use for connection --sort-buffer-size=# Each thread that needs to do a sort allocates a buffer of -@@ -782,6 +789,7 @@ +@@ -849,6 +856,7 @@ for the complete list of valid sql modes --stack-trace Print a symbolic stack trace on failure (Defaults to on; use --skip-stack-trace to disable.) @@ -45,7 +45,7 @@ --stored-program-cache=# The soft upper limit for number of cached stored routines for one connection. -@@ -822,8 +830,8 @@ +@@ -889,8 +897,8 @@ size, MySQL will automatically convert it to an on-disk MyISAM or Aria table -t, --tmpdir=name Path for temporary files. Several paths may be specified, @@ -56,7 +56,7 @@ --transaction-alloc-block-size=# Allocation block size for transactions to be stored in binary log -@@ -923,7 +931,6 @@ key-cache-age-threshold 300 +@@ -993,7 +1001,6 @@ key-cache-block-size 1024 key-cache-division-limit 100 key-cache-segments 0 @@ -64,7 +64,7 @@ lc-messages en_US lc-messages-dir MYSQL_SHAREDIR/ lc-time-names en_US -@@ -986,6 +993,7 @@ myisam-repair-threads 1 +@@ -1056,6 +1063,7 @@ myisam-sort-buffer-size 8388608 myisam-stats-method nulls_unequal myisam-use-mmap FALSE @@ -72,7 +72,7 @@ net-buffer-length 16384 net-read-timeout 30 net-retry-count 10 -@@ -1051,6 +1059,8 @@ safe-user-create FALSE +@@ -1148,6 +1156,8 @@ secure-auth FALSE secure-file-priv (No default value) server-id 0 @@ -81,7 +81,7 @@ show-slave-auth-info FALSE skip-grant-tables TRUE skip-name-resolve FALSE -@@ -1067,6 +1077,7 @@ slave-transaction-retries 10 +@@ -1164,6 +1174,7 @@ slave-type-conversions slow-launch-time 2 slow-query-log FALSE @@ -89,4 +89,3 @@ sort-buffer-size 2097152 sql-mode stack-trace TRUE - diff --git a/mysql-test/r/mysqld--help.result b/mysql-test/r/mysqld--help.result index da4aecc180e..9fb3502c5c5 100644 --- a/mysql-test/r/mysqld--help.result +++ b/mysql-test/r/mysqld--help.result @@ -260,8 +260,6 @@ The following options may be given as the first argument: --lock-wait-timeout=# Timeout in seconds to wait for a lock before returning an error. - -l, --log[=name] Log connections and queries to file (deprecated option, - use --general-log/--general-log-file instead). --log-basename=name Basename for all log files and the .pid file. This sets all log file names at once (in 'datadir') and is normally the only option you need for specifying log files. Sets @@ -502,10 +500,66 @@ The following options may be given as the first argument: val is one of {on, off, default} --performance-schema Enable the performance schema. + --performance-schema-accounts-size=# + Maximum number of instrumented user@host accounts. + --performance-schema-consumer-events-stages-current + Default startup value for the events_stages_current + consumer. + --performance-schema-consumer-events-stages-history + Default startup value for the events_stages_history + consumer. + --performance-schema-consumer-events-stages-history-long + Default startup value for the events_stages_history_long + consumer. + --performance-schema-consumer-events-statements-current + Default startup value for the events_statements_current + consumer. + (Defaults to on; use --skip-performance-schema-consumer-events-statements-current to disable.) + --performance-schema-consumer-events-statements-history + Default startup value for the events_statements_history + consumer. + --performance-schema-consumer-events-statements-history-long + Default startup value for the + events_statements_history_long consumer. + --performance-schema-consumer-events-waits-current + Default startup value for the events_waits_current + consumer. + --performance-schema-consumer-events-waits-history + Default startup value for the events_waits_history + consumer. + --performance-schema-consumer-events-waits-history-long + Default startup value for the events_waits_history_long + consumer. + --performance-schema-consumer-global-instrumentation + Default startup value for the global_instrumentation + consumer. + (Defaults to on; use --skip-performance-schema-consumer-global-instrumentation to disable.) + --performance-schema-consumer-statements-digest + Default startup value for the statements_digest consumer. + (Defaults to on; use --skip-performance-schema-consumer-statements-digest to disable.) + --performance-schema-consumer-thread-instrumentation + Default startup value for the thread_instrumentation + consumer. + (Defaults to on; use --skip-performance-schema-consumer-thread-instrumentation to disable.) + --performance-schema-digests-size=# + Size of the statement digest. + --performance-schema-events-stages-history-long-size=# + Number of rows in EVENTS_STAGES_HISTORY_LONG. + --performance-schema-events-stages-history-size=# + Number of rows per thread in EVENTS_STAGES_HISTORY. + --performance-schema-events-statements-history-long-size=# + Number of rows in EVENTS_STATEMENTS_HISTORY_LONG. + --performance-schema-events-statements-history-size=# + Number of rows per thread in EVENTS_STATEMENTS_HISTORY. --performance-schema-events-waits-history-long-size=# Number of rows in EVENTS_WAITS_HISTORY_LONG. --performance-schema-events-waits-history-size=# Number of rows per thread in EVENTS_WAITS_HISTORY. + --performance-schema-hosts-size=# + Maximum number of instrumented hosts. + --performance-schema-instrument[=name] + Default startup value for a performance schema + instrument. --performance-schema-max-cond-classes=# Maximum number of condition instruments. --performance-schema-max-cond-instances=# @@ -524,6 +578,14 @@ The following options may be given as the first argument: Maximum number of rwlock instruments. --performance-schema-max-rwlock-instances=# Maximum number of instrumented RWLOCK objects. + --performance-schema-max-socket-classes=# + Maximum number of socket instruments. + --performance-schema-max-socket-instances=# + Maximum number of opened instrumented sockets. + --performance-schema-max-stage-classes=# + Maximum number of stage instruments. + --performance-schema-max-statement-classes=# + Maximum number of statement instruments. --performance-schema-max-table-handles=# Maximum number of opened instrumented tables. --performance-schema-max-table-instances=# @@ -532,12 +594,23 @@ The following options may be given as the first argument: Maximum number of thread instruments. --performance-schema-max-thread-instances=# Maximum number of instrumented threads. + --performance-schema-setup-actors-size=# + Maximum number of rows in SETUP_ACTORS. + --performance-schema-setup-objects-size=# + Maximum number of rows in SETUP_OBJECTS. + --performance-schema-users-size=# + Maximum number of instrumented users. --pid-file=name Pid file used by safe_mysqld --plugin-dir=name Directory for plugins --plugin-load=name Semicolon-separated list of plugins to load, where each plugin is specified as ether a plugin_name=library_file pair or only a library_file. If the latter case, all plugins from a given library_file will be loaded. + --plugin-load-add=name + Optional semicolon-separated list of plugins to load. + This option adds to the list speficied by --plugin-load + in an incremental way. It can be specified many times, + adding more plugins every time. --plugin-maturity=name The lowest desirable plugin maturity (unknown, experimental, alpha, beta, gamma, or stable). Plugins @@ -835,6 +908,9 @@ The following options may be given as the first argument: --transaction-prealloc-size=# Persistent buffer for transactions to be stored in binary log + --transaction-read-only + Default transaction access mode. True if transactions are + read-only. --updatable-views-with-limit=name YES = Don't issue an error message (warning only) if a VIEW without presence of a key of the underlying table is @@ -1002,8 +1078,28 @@ optimizer-prune-level 1 optimizer-search-depth 62 optimizer-switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on performance-schema FALSE +performance-schema-accounts-size 100 +performance-schema-consumer-events-stages-current FALSE +performance-schema-consumer-events-stages-history FALSE +performance-schema-consumer-events-stages-history-long FALSE +performance-schema-consumer-events-statements-current TRUE +performance-schema-consumer-events-statements-history FALSE +performance-schema-consumer-events-statements-history-long FALSE +performance-schema-consumer-events-waits-current FALSE +performance-schema-consumer-events-waits-history FALSE +performance-schema-consumer-events-waits-history-long FALSE +performance-schema-consumer-global-instrumentation TRUE +performance-schema-consumer-statements-digest TRUE +performance-schema-consumer-thread-instrumentation TRUE +performance-schema-digests-size 200 +performance-schema-events-stages-history-long-size 10000 +performance-schema-events-stages-history-size 10 +performance-schema-events-statements-history-long-size 10000 +performance-schema-events-statements-history-size 10 performance-schema-events-waits-history-long-size 10000 performance-schema-events-waits-history-size 10 +performance-schema-hosts-size 100 +performance-schema-instrument performance-schema-max-cond-classes 80 performance-schema-max-cond-instances 1000 performance-schema-max-file-classes 50 @@ -1013,11 +1109,17 @@ performance-schema-max-mutex-classes 200 performance-schema-max-mutex-instances 1000000 performance-schema-max-rwlock-classes 30 performance-schema-max-rwlock-instances 1000000 -performance-schema-max-table-handles 100000 -performance-schema-max-table-instances 50000 +performance-schema-max-socket-classes 10 +performance-schema-max-socket-instances 1000 +performance-schema-max-stage-classes 150 +performance-schema-max-statement-classes 173 +performance-schema-max-table-handles 10000 +performance-schema-max-table-instances 1000 performance-schema-max-thread-classes 50 performance-schema-max-thread-instances 1000 -plugin-load (No default value) +performance-schema-setup-actors-size 100 +performance-schema-setup-objects-size 100 +performance-schema-users-size 100 plugin-maturity unknown port 3306 port-open-timeout 0 @@ -1094,6 +1196,7 @@ tmp-table-size 16777216 transaction-alloc-block-size 8192 transaction-isolation REPEATABLE-READ transaction-prealloc-size 4096 +transaction-read-only FALSE updatable-views-with-limit YES userstat FALSE verbose TRUE diff --git a/mysql-test/r/mysqldump.result b/mysql-test/r/mysqldump.result index ef8bcea3649..6b0e044a491 100644 --- a/mysql-test/r/mysqldump.result +++ b/mysql-test/r/mysqldump.result @@ -3642,8 +3642,8 @@ reset master; mysqldump: Couldn't execute 'FLUSH /*!40101 LOCAL */ TABLES': Access denied; you need (at least one of) the RELOAD privilege(s) for this operation (1227) mysqldump: Couldn't execute 'FLUSH /*!40101 LOCAL */ TABLES': Access denied; you need (at least one of) the RELOAD privilege(s) for this operation (1227) grant RELOAD on *.* to mysqltest_1@localhost; -mysqldump: Couldn't execute 'SHOW MASTER STATUS': Access denied; you need (at least one of) the SUPER,REPLICATION CLIENT privilege(s) for this operation (1227) -mysqldump: Couldn't execute 'SHOW MASTER STATUS': Access denied; you need (at least one of) the SUPER,REPLICATION CLIENT privilege(s) for this operation (1227) +mysqldump: Couldn't execute 'SHOW MASTER STATUS': Access denied; you need (at least one of) the SUPER, REPLICATION CLIENT privilege(s) for this operation (1227) +mysqldump: Couldn't execute 'SHOW MASTER STATUS': Access denied; you need (at least one of) the SUPER, REPLICATION CLIENT privilege(s) for this operation (1227) grant REPLICATION CLIENT on *.* to mysqltest_1@localhost; drop table t1; drop user mysqltest_1@localhost; diff --git a/mysql-test/r/old-mode.result b/mysql-test/r/old-mode.result index 6e6f9965e73..eec08d4d5c8 100644 --- a/mysql-test/r/old-mode.result +++ b/mysql-test/r/old-mode.result @@ -18,4 +18,4 @@ test.t2 2948697075 drop table t1,t2; SHOW PROCESSLIST; Id User Host db Command Time State Info -<Id> root <Host> test Query <Time> NULL SHOW PROCESSLIST +<Id> root <Host> test Query <Time> <State> SHOW PROCESSLIST diff --git a/mysql-test/r/partition_binlog.result b/mysql-test/r/partition_binlog.result index c8fa02c4b99..2c4ab1d0a30 100644 --- a/mysql-test/r/partition_binlog.result +++ b/mysql-test/r/partition_binlog.result @@ -39,7 +39,7 @@ Table Op Msg_type Msg_text test.t1 repair status OK ALTER TABLE t1 TRUNCATE PARTITION p0; ALTER TABLE t1 DROP PARTITION p0; -show binlog events in 'master-bin.000001' from <binlog_start>; +include/show_binlog_events.inc Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query # # use `test`; ALTER TABLE t1 ANALYZE PARTITION p0 master-bin.000001 # Query # # use `test`; ALTER TABLE t1 OPTIMIZE PARTITION p0 diff --git a/mysql-test/r/partition_innodb.result b/mysql-test/r/partition_innodb.result index dd403f64b5c..adfe3287459 100644 --- a/mysql-test/r/partition_innodb.result +++ b/mysql-test/r/partition_innodb.result @@ -61,6 +61,7 @@ DROP TABLE t1; # SELECT is not detected # SET @old_innodb_thread_concurrency:= @@innodb_thread_concurrency; +SET @old_innodb_thread_sleep_delay := @@innodb_thread_sleep_delay; SET GLOBAL innodb_thread_concurrency = 1; CREATE TABLE t1 (user_num BIGINT, @@ -91,6 +92,7 @@ COMMIT; # con1, reaping ALTER. # Disconnecting con1 and switching to default. Cleaning up. SET GLOBAL innodb_thread_concurrency = @old_innodb_thread_concurrency; +SET GLOBAL innodb_thread_sleep_delay = @old_innodb_thread_sleep_delay; DROP TABLE t1; # # Bug#50418: DROP PARTITION does not interact with transactions @@ -574,4 +576,24 @@ a b 1 2 0 1 DROP TABLE t1; +# +# BUG#12912171 - ASSERTION FAILED: QUICK->HEAD->READ_SET == +# SAVE_READ_SET +# +CREATE TABLE t1 ( +a INT, +b INT, +c INT, +PRIMARY KEY (c,a), KEY (a),KEY (a) +) ENGINE=INNODB PARTITION BY KEY () PARTITIONS 2; +INSERT INTO t1 VALUES (1,5,1),(2,4,1),(3,3,1),(4,2,1),(5,1,1); +UPDATE t1 SET b = 0, c=1 WHERE a <=>0; +SELECT * FROM t1; +a b c +1 5 1 +2 4 1 +3 3 1 +4 2 1 +5 1 1 +DROP TABLE t1; set global default_storage_engine=default; diff --git a/mysql-test/r/plugin_load.result b/mysql-test/r/plugin_load.result index 33116cc20ab..35bacf670a5 100644 --- a/mysql-test/r/plugin_load.result +++ b/mysql-test/r/plugin_load.result @@ -1,3 +1,10 @@ SELECT @@global.example_enum_var = 'e2'; @@global.example_enum_var = 'e2' 1 +select plugin_name, plugin_status from information_schema.plugins +where plugin_name in ('SIMPLE_PARSER', 'EXAMPLE', +'DAEMON_EXAMPLE', 'TWO_QUESTIONS', 'THREE_ATTEMPTS'); +plugin_name plugin_status +EXAMPLE ACTIVE +daemon_example ACTIVE +simple_parser ACTIVE diff --git a/mysql-test/r/query_cache.result b/mysql-test/r/query_cache.result index 2dd89d0e4bb..62f8a9728c3 100644 --- a/mysql-test/r/query_cache.result +++ b/mysql-test/r/query_cache.result @@ -652,14 +652,14 @@ select * from t1; a 1 2 -SET OPTION SQL_SELECT_LIMIT=1; +SET SQL_SELECT_LIMIT=1; select * from t1; a 1 show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 2 -SET OPTION SQL_SELECT_LIMIT=DEFAULT; +SET SQL_SELECT_LIMIT=DEFAULT; drop table t1; flush query cache; reset query cache; @@ -1932,6 +1932,18 @@ set local query_cache_type= on; select @@query_cache_size, @@global.query_cache_type, @@local.query_cache_type; @@query_cache_size @@global.query_cache_type @@local.query_cache_type 20971520 ON ON +# +# Bug#12977203: SERVER CRASH ON A DERIVED TABLE WITH QUERY CACHE ENABLED +# +SET @qc= @@query_cache_size; +SET GLOBAL query_cache_size=1355776; +CREATE TABLE t1 (f1 blob); +SELECT COUNT(*) FROM (SELECT * FROM t1 ) tt; +COUNT(*) +0 +DROP TABLE t1; +SET GLOBAL query_cache_size= @qc; +# restore defaults SET GLOBAL query_cache_type= default; SET GLOBAL query_cache_size= default; diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result index 3b7d07d525e..146d250d687 100644 --- a/mysql-test/r/range.result +++ b/mysql-test/r/range.result @@ -1953,3 +1953,49 @@ a b c d 14 1 1 q 9 7 1 s DROP TABLE t1; +# +# BUG#13256446 - ASSERTION QUICK->HEAD->READ_SET == +# SAVE_READ_SET' FAILED IN OPT_RANGE.CC:1606 +# +CREATE TABLE t1 ( +f1 INT AUTO_INCREMENT, +f2 INT, +f3 INT, +f4 INT, +PRIMARY KEY (f1),KEY(f2) +) ENGINE=INNODB; +CREATE ALGORITHM=MERGE VIEW v2 AS SELECT f1,f2,f3,f4 FROM t1 WHERE f2=103; +INSERT INTO t1 VALUES (154,0,NULL,0),(0,NULL,9,0), +(NULL,102,NULL,3),(0,3,NULL,0), (9,0,NULL,0),(0,9,NULL,157); +SELECT * FROM v2; +f1 f2 f3 f4 +UPDATE v2 SET f4=0, f2=NULL, f1=NULL WHERE f1 > 16 ORDER BY f1; +SELECT * FROM v2; +f1 f2 f3 f4 +DROP TABLE t1; +DROP VIEW v2; +CREATE TABLE t1 ( +f1 INT AUTO_INCREMENT, +f2 INT, +f3 INT, +f4 INT, +PRIMARY KEY (f1),KEY(f2) +) ENGINE=INNODB; +INSERT INTO t1 VALUES(1,NULL,NULL,0), (2,2,0,3), (9,0,107,18), +(10,0,0,0), (231,0,0,0), (232,0,8,0), (234,0,0,NULL), (235,8,0,3); +CREATE ALGORITHM=MERGE VIEW v3 AS SELECT f1,f2,f3,f4 FROM t1 WHERE f1<=85 ; +SELECT * FROM v3; +f1 f2 f3 f4 +1 NULL NULL 0 +2 2 0 3 +9 0 107 18 +10 0 0 0 +UPDATE v3 SET f3=0, f4=4 WHERE f2=68 ORDER BY f1; +SELECT * FROM v3; +f1 f2 f3 f4 +1 NULL NULL 0 +2 2 0 3 +9 0 107 18 +10 0 0 0 +DROP TABLE t1; +DROP VIEW v3; diff --git a/mysql-test/r/range_mrr_icp.result b/mysql-test/r/range_mrr_icp.result index 354a4758e7c..3769ceb9145 100644 --- a/mysql-test/r/range_mrr_icp.result +++ b/mysql-test/r/range_mrr_icp.result @@ -1955,4 +1955,50 @@ a b c d 9 7 1 s 14 1 1 q DROP TABLE t1; +# +# BUG#13256446 - ASSERTION QUICK->HEAD->READ_SET == +# SAVE_READ_SET' FAILED IN OPT_RANGE.CC:1606 +# +CREATE TABLE t1 ( +f1 INT AUTO_INCREMENT, +f2 INT, +f3 INT, +f4 INT, +PRIMARY KEY (f1),KEY(f2) +) ENGINE=INNODB; +CREATE ALGORITHM=MERGE VIEW v2 AS SELECT f1,f2,f3,f4 FROM t1 WHERE f2=103; +INSERT INTO t1 VALUES (154,0,NULL,0),(0,NULL,9,0), +(NULL,102,NULL,3),(0,3,NULL,0), (9,0,NULL,0),(0,9,NULL,157); +SELECT * FROM v2; +f1 f2 f3 f4 +UPDATE v2 SET f4=0, f2=NULL, f1=NULL WHERE f1 > 16 ORDER BY f1; +SELECT * FROM v2; +f1 f2 f3 f4 +DROP TABLE t1; +DROP VIEW v2; +CREATE TABLE t1 ( +f1 INT AUTO_INCREMENT, +f2 INT, +f3 INT, +f4 INT, +PRIMARY KEY (f1),KEY(f2) +) ENGINE=INNODB; +INSERT INTO t1 VALUES(1,NULL,NULL,0), (2,2,0,3), (9,0,107,18), +(10,0,0,0), (231,0,0,0), (232,0,8,0), (234,0,0,NULL), (235,8,0,3); +CREATE ALGORITHM=MERGE VIEW v3 AS SELECT f1,f2,f3,f4 FROM t1 WHERE f1<=85 ; +SELECT * FROM v3; +f1 f2 f3 f4 +1 NULL NULL 0 +2 2 0 3 +9 0 107 18 +10 0 0 0 +UPDATE v3 SET f3=0, f4=4 WHERE f2=68 ORDER BY f1; +SELECT * FROM v3; +f1 f2 f3 f4 +1 NULL NULL 0 +2 2 0 3 +9 0 107 18 +10 0 0 0 +DROP TABLE t1; +DROP VIEW v3; set optimizer_switch=@mrr_icp_extra_tmp; diff --git a/mysql-test/r/read_only.result b/mysql-test/r/read_only.result index 1ffe2b86f70..3811c6c5487 100644 --- a/mysql-test/r/read_only.result +++ b/mysql-test/r/read_only.result @@ -157,3 +157,40 @@ delete from mysql.columns_priv where User like 'mysqltest_%'; flush privileges; drop database mysqltest_db1; set global read_only= @start_read_only; +# +# WL#5968 Implement START TRANSACTION READ (WRITE|ONLY); +# +# +# Test interaction with read_only system variable. +DROP TABLE IF EXISTS t1; +CREATE TABLE t1(a INT); +INSERT INTO t1 VALUES (1), (2); +CREATE USER user1; +SET GLOBAL read_only= 1; +# All allowed with super privilege +START TRANSACTION; +COMMIT; +START TRANSACTION READ ONLY; +COMMIT; +START TRANSACTION READ WRITE; +COMMIT; +# We allow implicit RW transaction without super privilege +# for compatibility reasons +START TRANSACTION; +# Check that table updates are still disallowed. +INSERT INTO t1 VALUES (3); +ERROR HY000: The MariaDB server is running with the --read-only option so it cannot execute this statement +UPDATE t1 SET a= 1; +ERROR HY000: The MariaDB server is running with the --read-only option so it cannot execute this statement +DELETE FROM t1; +ERROR HY000: The MariaDB server is running with the --read-only option so it cannot execute this statement +COMMIT; +START TRANSACTION READ ONLY; +COMMIT; +# Explicit RW trans is not allowed without super privilege +START TRANSACTION READ WRITE; +ERROR HY000: The MariaDB server is running with the --read-only option so it cannot execute this statement +COMMIT; +DROP USER user1; +SET GLOBAL read_only= 0; +DROP TABLE t1; diff --git a/mysql-test/r/show_explain_ps.result b/mysql-test/r/show_explain_ps.result index 625b9cfddae..f9b82c85f01 100644 --- a/mysql-test/r/show_explain_ps.result +++ b/mysql-test/r/show_explain_ps.result @@ -2,6 +2,8 @@ drop table if exists t0, t1; select * from performance_schema.setup_instruments where name like '%show_explain%'; NAME ENABLED TIMED wait/synch/cond/sql/show_explain YES YES +stage/sql/show explain YES YES +statement/sql/show_explain YES YES # We've got no instances select * from performance_schema.cond_instances where name like '%show_explain%'; NAME OBJECT_INSTANCE_BEGIN diff --git a/mysql-test/r/signal_code.result b/mysql-test/r/signal_code.result index 4cd1de97729..ca46f1d2079 100644 --- a/mysql-test/r/signal_code.result +++ b/mysql-test/r/signal_code.result @@ -20,16 +20,16 @@ return 0; end $$ show procedure code signal_proc; Pos Instruction -0 stmt 132 "SIGNAL foo" -1 stmt 132 "SIGNAL foo SET MESSAGE_TEXT = "This i..." -2 stmt 133 "RESIGNAL foo" -3 stmt 133 "RESIGNAL foo SET MESSAGE_TEXT = "This..." +0 stmt 130 "SIGNAL foo" +1 stmt 130 "SIGNAL foo SET MESSAGE_TEXT = "This i..." +2 stmt 131 "RESIGNAL foo" +3 stmt 131 "RESIGNAL foo SET MESSAGE_TEXT = "This..." drop procedure signal_proc; show function code signal_func; Pos Instruction -0 stmt 132 "SIGNAL foo" -1 stmt 132 "SIGNAL foo SET MESSAGE_TEXT = "This i..." -2 stmt 133 "RESIGNAL foo" -3 stmt 133 "RESIGNAL foo SET MESSAGE_TEXT = "This..." +0 stmt 130 "SIGNAL foo" +1 stmt 130 "SIGNAL foo SET MESSAGE_TEXT = "This i..." +2 stmt 131 "RESIGNAL foo" +3 stmt 131 "RESIGNAL foo SET MESSAGE_TEXT = "This..." 4 freturn 3 0 drop function signal_func; diff --git a/mysql-test/r/sp-code.result b/mysql-test/r/sp-code.result index 3061c58747a..3bead4fc826 100644 --- a/mysql-test/r/sp-code.result +++ b/mysql-test/r/sp-code.result @@ -155,11 +155,11 @@ Pos Instruction 0 stmt 9 "drop temporary table if exists sudoku..." 1 stmt 1 "create temporary table sudoku_work ( ..." 2 stmt 1 "create temporary table sudoku_schedul..." -3 stmt 90 "call sudoku_init()" +3 stmt 88 "call sudoku_init()" 4 jump_if_not 7(8) p_naive@0 5 stmt 4 "update sudoku_work set cnt = 0 where ..." 6 jump 8 -7 stmt 90 "call sudoku_count()" +7 stmt 88 "call sudoku_count()" 8 stmt 6 "insert into sudoku_schedule (row,col)..." 9 set v_scounter@2 0 10 set v_i@3 1 @@ -851,7 +851,7 @@ drop procedure if exists p_20906_b; create procedure p_20906_a() SET @a=@a+1, @b=@b+1; show procedure code p_20906_a; Pos Instruction -0 stmt 31 "SET @a=@a+1" +0 stmt 31 "SET @a=@a+1" 1 stmt 31 "SET @b=@b+1" set @a=1; set @b=1; @@ -862,7 +862,7 @@ select @a, @b; create procedure p_20906_b() SET @a=@a+1, @b=@b+1, @c=@c+1; show procedure code p_20906_b; Pos Instruction -0 stmt 31 "SET @a=@a+1" +0 stmt 31 "SET @a=@a+1" 1 stmt 31 "SET @b=@b+1" 2 stmt 31 "SET @c=@c+1" set @a=1; diff --git a/mysql-test/r/sp-destruct.result b/mysql-test/r/sp-destruct.result index a529a62680b..6d85c3ce496 100644 --- a/mysql-test/r/sp-destruct.result +++ b/mysql-test/r/sp-destruct.result @@ -149,7 +149,7 @@ alter table mysql.proc drop column type; # The below statement should not cause assertion failure. drop database mysqltest; Warnings: -Error 1547 Column count of mysql.proc is wrong. Expected 20, found 19. The table is probably corrupted +Error 1728 Column count of mysql.proc is wrong. Expected 20, found 19. The table is probably corrupted # Restore mysql.proc. drop table mysql.proc; # @@ -166,7 +166,7 @@ CREATE PROCEDURE db1.p1() SET @foo = 10; ALTER TABLE mysql.proc MODIFY comment CHAR (32); DROP DATABASE db1; Warnings: -Error 1548 Cannot load from mysql.proc. The table is probably corrupted +Error 1729 Cannot load from mysql.proc. The table is probably corrupted # Restore mysql.proc DROP TABLE mysql.proc; RENAME TABLE proc_backup TO mysql.proc; diff --git a/mysql-test/r/sp-threads.result b/mysql-test/r/sp-threads.result index 4dc4d81224b..4ce61c44762 100644 --- a/mysql-test/r/sp-threads.result +++ b/mysql-test/r/sp-threads.result @@ -36,7 +36,7 @@ show processlist; Id User Host db Command Time State Info Progress # root localhost test Sleep # NULL 0.000 # root localhost test Query # Waiting for table metadata lock update t1, t2 set val= 1 where id1=id2 0.000 -# root localhost test Query # NULL show processlist 0.000 +# root localhost test Query # init show processlist 0.000 # root localhost test Sleep # NULL 0.000 unlock tables; drop procedure bug9486; diff --git a/mysql-test/r/sp_trans_log.result b/mysql-test/r/sp_trans_log.result index e3463dec571..d2e1eb18efb 100644 --- a/mysql-test/r/sp_trans_log.result +++ b/mysql-test/r/sp_trans_log.result @@ -11,16 +11,16 @@ insert into t1 values (null); select count(*) from t1 into @a; return @a; end| -reset master| -insert into t2 values (bug23333(),1)| +reset master; +insert into t2 values (bug23333(),1); ERROR 23000: Duplicate entry '1' for key 'PRIMARY' -show binlog events from <binlog_start> limit 0, 4| +include/show_binlog_events.inc Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query # # BEGIN master-bin.000001 # Table_map # # table_id: # (test.t1) master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F master-bin.000001 # Query # # COMMIT -select count(*),@a from t1 /* must be 1,1 */| +select count(*),@a from t1 /* must be 1,1 */; count(*) @a 1 1 drop table t1,t2; diff --git a/mysql-test/r/ssl-crl-revoked-crl.result b/mysql-test/r/ssl-crl-revoked-crl.result new file mode 100644 index 00000000000..8ecc6a509d3 --- /dev/null +++ b/mysql-test/r/ssl-crl-revoked-crl.result @@ -0,0 +1 @@ +# try logging in with a certificate in the server's --ssl-crl : should fail diff --git a/mysql-test/r/ssl.result b/mysql-test/r/ssl.result index 53fbb1988e7..14c37c1575d 100644 --- a/mysql-test/r/ssl.result +++ b/mysql-test/r/ssl.result @@ -1,6 +1,12 @@ SHOW STATUS LIKE 'Ssl_cipher'; Variable_name Value Ssl_cipher DHE-RSA-AES256-SHA +SHOW STATUS LIKE 'Ssl_server_not_before'; +Variable_name Value +Ssl_server_not_before Jan 29 11:56:49 2010 GMT +SHOW STATUS LIKE 'Ssl_server_not_after'; +Variable_name Value +Ssl_server_not_after Jan 28 11:56:49 2015 GMT drop table if exists t1,t2,t3,t4; CREATE TABLE t1 ( Period smallint(4) unsigned zerofill DEFAULT '0000' NOT NULL, diff --git a/mysql-test/r/ssl_crl.result b/mysql-test/r/ssl_crl.result new file mode 100644 index 00000000000..570dd9cab06 --- /dev/null +++ b/mysql-test/r/ssl_crl.result @@ -0,0 +1,23 @@ +# test --crl for the client : should connect +Variable_name Value +have_openssl YES +have_ssl YES +ssl_ca MYSQL_TEST_DIR/std_data/crl-ca-cert.pem +ssl_capath +ssl_cert MYSQL_TEST_DIR/std_data/crl-server-cert.pem +ssl_cipher +ssl_crl MYSQL_TEST_DIR/std_data/crl-client-revoked.crl +ssl_crlpath +ssl_key MYSQL_TEST_DIR/std_data/crl-server-key.pem +# test --crlpath for the client : should connect +Variable_name Value +have_openssl YES +have_ssl YES +ssl_ca MYSQL_TEST_DIR/std_data/crl-ca-cert.pem +ssl_capath +ssl_cert MYSQL_TEST_DIR/std_data/crl-server-cert.pem +ssl_cipher +ssl_crl MYSQL_TEST_DIR/std_data/crl-client-revoked.crl +ssl_crlpath +ssl_key MYSQL_TEST_DIR/std_data/crl-server-key.pem +# try logging in with a certificate in the server's --ssl-crl : should fail diff --git a/mysql-test/r/ssl_crl_clients-valid.result b/mysql-test/r/ssl_crl_clients-valid.result new file mode 100644 index 00000000000..f3f47d51846 --- /dev/null +++ b/mysql-test/r/ssl_crl_clients-valid.result @@ -0,0 +1,24 @@ +# Test clients with and without CRL lists +############ Test mysql ############## +# Test mysql connecting to a server with an empty crl +Variable_name Value +have_openssl YES +have_ssl YES +ssl_ca MYSQL_TEST_DIR/std_data/crl-ca-cert.pem +ssl_capath +ssl_cert MYSQL_TEST_DIR/std_data/crl-client-cert.pem +ssl_cipher +ssl_crl +ssl_crlpath +ssl_key MYSQL_TEST_DIR/std_data/crl-client-key.pem +# Test mysql connecting to a server with a certificate revoked by -crl +# Test mysql connecting to a server with a certificate revoked by -crlpath +############ Test mysqladmin ############## +# Test mysqladmin connecting to a server with an empty crl +mysqld is alive +# Test mysqladmin connecting to a server with a certificate revoked by -crl +mysqladmin: connect to server at 'localhost' failed +error: 'SSL connection error: Failed to set ciphers to use' +# Test mysqladmin connecting to a server with a certificate revoked by -crlpath +mysqladmin: connect to server at 'localhost' failed +error: 'SSL connection error: error:00000005:lib(0):func(0):DH lib' diff --git a/mysql-test/r/ssl_crl_clients.result b/mysql-test/r/ssl_crl_clients.result new file mode 100644 index 00000000000..3bb9b4ee7e2 --- /dev/null +++ b/mysql-test/r/ssl_crl_clients.result @@ -0,0 +1,7 @@ +# Test clients with and without CRL lists +############ Test mysql ############## +# Test mysql connecting to a server with a certificate revoked by -crl +# Test mysql connecting to a server with a certificate revoked by -crlpath +############ Test mysqladmin ############## +# Test mysqladmin connecting to a server with a certificate revoked by -crl +# Test mysqladmin connecting to a server with a certificate revoked by -crlpath diff --git a/mysql-test/r/ssl_crl_clients_valid.result b/mysql-test/r/ssl_crl_clients_valid.result new file mode 100644 index 00000000000..32ee72d5c39 --- /dev/null +++ b/mysql-test/r/ssl_crl_clients_valid.result @@ -0,0 +1,16 @@ +# Test clients with and without CRL lists +############ Test mysql ############## +# Test mysql connecting to a server with an empty crl +Variable_name Value +have_openssl YES +have_ssl YES +ssl_ca MYSQL_TEST_DIR/std_data/crl-ca-cert.pem +ssl_capath +ssl_cert MYSQL_TEST_DIR/std_data/crl-client-valid-cert.pem +ssl_cipher +ssl_crl MYSQL_TEST_DIR/std_data/crl-client-revoked.crl +ssl_crlpath +ssl_key MYSQL_TEST_DIR/std_data/crl-client-valid-key.pem +############ Test mysqladmin ############## +# Test mysqladmin connecting to a server with an empty crl +mysqld is alive diff --git a/mysql-test/r/ssl_crl_clrpath.result b/mysql-test/r/ssl_crl_clrpath.result new file mode 100644 index 00000000000..b4b4d13eca1 --- /dev/null +++ b/mysql-test/r/ssl_crl_clrpath.result @@ -0,0 +1,23 @@ +# test --crl for the client : should connect +Variable_name Value +have_openssl YES +have_ssl YES +ssl_ca MYSQL_TEST_DIR/std_data/crl-ca-cert.pem +ssl_capath +ssl_cert MYSQL_TEST_DIR/std_data/crl-server-cert.pem +ssl_cipher +ssl_crl +ssl_crlpath MYSQL_TEST_DIR/std_data/crldir +ssl_key MYSQL_TEST_DIR/std_data/crl-server-key.pem +# test --crlpath for the client : should connect +Variable_name Value +have_openssl YES +have_ssl YES +ssl_ca MYSQL_TEST_DIR/std_data/crl-ca-cert.pem +ssl_capath +ssl_cert MYSQL_TEST_DIR/std_data/crl-server-cert.pem +ssl_cipher +ssl_crl +ssl_crlpath MYSQL_TEST_DIR/std_data/crldir +ssl_key MYSQL_TEST_DIR/std_data/crl-server-key.pem +# try logging in with a certificate in the server's --ssl-crlpath : should fail diff --git a/mysql-test/r/status.result b/mysql-test/r/status.result index 0da65a5ab51..140db7c5721 100644 --- a/mysql-test/r/status.result +++ b/mysql-test/r/status.result @@ -275,6 +275,7 @@ Variable_name Value Handler_commit 0 Handler_delete 0 Handler_discover 0 +Handler_external_lock 0 Handler_icp_attempts 0 Handler_icp_match 0 Handler_mrr_init 0 @@ -303,7 +304,7 @@ Created_tmp_files 0 Created_tmp_tables 2 Handler_tmp_update 2 Handler_tmp_write 7 -Rows_tmp_read 41 +Rows_tmp_read 42 drop table t1; CREATE TABLE t1 (i int(11) DEFAULT NULL, KEY i (i) ) ENGINE=MyISAM; insert into t1 values (1),(2),(3),(4),(5); @@ -316,6 +317,7 @@ Variable_name Value Handler_commit 0 Handler_delete 0 Handler_discover 0 +Handler_external_lock 0 Handler_icp_attempts 0 Handler_icp_match 0 Handler_mrr_init 0 diff --git a/mysql-test/r/status_user.result b/mysql-test/r/status_user.result index f43e217b8a5..6236e62afb9 100644 --- a/mysql-test/r/status_user.result +++ b/mysql-test/r/status_user.result @@ -100,6 +100,7 @@ Variable_name Value Handler_commit 19 Handler_delete 1 Handler_discover 0 +Handler_external_lock 0 Handler_icp_attempts 0 Handler_icp_match 0 Handler_mrr_init 0 diff --git a/mysql-test/r/subselect_cache.result b/mysql-test/r/subselect_cache.result index 7bd10dc11a6..c08aa6124b8 100644 --- a/mysql-test/r/subselect_cache.result +++ b/mysql-test/r/subselect_cache.result @@ -1612,7 +1612,7 @@ Subquery_cache_hit 0 Subquery_cache_miss 4 drop table t1; #test of sql_big_tables switch and outer table reference in subquery with grouping -set option sql_big_tables=1; +set big_tables=1; CREATE TABLE t1 (a INT PRIMARY KEY, b INT); INSERT INTO t1 VALUES (1,1),(2,1),(3,2),(4,2),(5,3),(6,3); SELECT (SELECT t1_outer.a FROM t1 AS t1_inner GROUP BY b LIMIT 1) FROM t1 AS t1_outer; @@ -1624,7 +1624,7 @@ SELECT (SELECT t1_outer.a FROM t1 AS t1_inner GROUP BY b LIMIT 1) FROM t1 AS t1_ 5 6 drop table t1; -set option sql_big_tables=0; +set big_tables=0; #test of function reference to outer query set local group_concat_max_len=400; create table t2 (a int, b int); diff --git a/mysql-test/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result index f8598a3864d..20fee7fb7f8 100644 --- a/mysql-test/r/subselect_sj_mat.result +++ b/mysql-test/r/subselect_sj_mat.result @@ -94,7 +94,7 @@ a1 a2 explain extended select * from t1i where a1 in (select b1 from t2i where b1 > '0'); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t2i index it2i1,it2i3 it2i1 # NULL 5 40.00 Using where; Using index; LooseScan +1 PRIMARY t2i index it2i1,it2i3 it2i1 # NULL 5 50.00 Using where; Using index; LooseScan 1 PRIMARY t1i ref _it1_idx _it1_idx # _ref_ 1 100.00 Warnings: Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` semi join (`test`.`t2i`) where ((`test`.`t1i`.`a1` = `test`.`t2i`.`b1`) and (`test`.`t2i`.`b1` > '0')) @@ -117,7 +117,7 @@ a1 a2 explain extended select * from t1i where (a1, a2) in (select b1, b2 from t2i where b1 > '0'); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t2i index it2i1,it2i2,it2i3 it2i3 # NULL 5 40.00 Using where; Using index; LooseScan +1 PRIMARY t2i index it2i1,it2i2,it2i3 it2i3 # NULL 5 50.00 Using where; Using index; LooseScan 1 PRIMARY t1i ref _it1_idx _it1_idx # _ref_ 1 100.00 Warnings: Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from `test`.`t1i` semi join (`test`.`t2i`) where ((`test`.`t1i`.`a2` = `test`.`t2i`.`b2`) and (`test`.`t1i`.`a1` = `test`.`t2i`.`b1`) and (`test`.`t2i`.`b1` > '0')) @@ -319,7 +319,7 @@ where (a1, a2) in (select b1, b2 from t2i where b1 > '0') and (a1, a2) in (select c1, c2 from t3i where (c1, c2) in (select b1, b2 from t2i where b2 > '0')); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t2i index it2i1,it2i2,it2i3 # # # 5 40.00 # +1 PRIMARY t2i index it2i1,it2i2,it2i3 # # # 5 50.00 # 1 PRIMARY t1i ref it1i1,it1i2,it1i3 # # # 1 100.00 # 1 PRIMARY t3i ref it3i1,it3i2,it3i3 # # # 1 100.00 # 1 PRIMARY t2i ref it2i1,it2i2,it2i3 # # # 2 100.00 # @@ -407,7 +407,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 MATERIALIZED t2 ALL NULL # # # 5 100.00 # 4 MATERIALIZED t3 ALL NULL # # # 4 100.00 # 3 MATERIALIZED t3 ALL NULL # # # 4 100.00 # -7 UNION t2i index it2i1,it2i2,it2i3 # # # 5 40.00 # +7 UNION t2i index it2i1,it2i2,it2i3 # # # 5 50.00 # 7 UNION t1i ref it1i1,it1i2,it1i3 # # # 1 100.00 # 7 UNION t3i ref it3i1,it3i2,it3i3 # # # 1 100.00 # 7 UNION t2i ref it2i1,it2i2,it2i3 # # # 2 100.00 # diff --git a/mysql-test/r/system_mysql_db.result b/mysql-test/r/system_mysql_db.result index 9eb617d94f7..be43ef65701 100644 --- a/mysql-test/r/system_mysql_db.result +++ b/mysql-test/r/system_mysql_db.result @@ -10,12 +10,17 @@ help_keyword help_relation help_topic host +innodb_index_stats +innodb_table_stats ndb_binlog_index plugin proc procs_priv proxies_priv servers +slave_master_info +slave_relay_log_info +slave_worker_info slow_log tables_priv time_zone diff --git a/mysql-test/r/system_mysql_db_fix40123.result b/mysql-test/r/system_mysql_db_fix40123.result index 9eb617d94f7..be43ef65701 100644 --- a/mysql-test/r/system_mysql_db_fix40123.result +++ b/mysql-test/r/system_mysql_db_fix40123.result @@ -10,12 +10,17 @@ help_keyword help_relation help_topic host +innodb_index_stats +innodb_table_stats ndb_binlog_index plugin proc procs_priv proxies_priv servers +slave_master_info +slave_relay_log_info +slave_worker_info slow_log tables_priv time_zone diff --git a/mysql-test/r/system_mysql_db_fix50030.result b/mysql-test/r/system_mysql_db_fix50030.result index 9eb617d94f7..be43ef65701 100644 --- a/mysql-test/r/system_mysql_db_fix50030.result +++ b/mysql-test/r/system_mysql_db_fix50030.result @@ -10,12 +10,17 @@ help_keyword help_relation help_topic host +innodb_index_stats +innodb_table_stats ndb_binlog_index plugin proc procs_priv proxies_priv servers +slave_master_info +slave_relay_log_info +slave_worker_info slow_log tables_priv time_zone diff --git a/mysql-test/r/system_mysql_db_fix50117.result b/mysql-test/r/system_mysql_db_fix50117.result index 9eb617d94f7..be43ef65701 100644 --- a/mysql-test/r/system_mysql_db_fix50117.result +++ b/mysql-test/r/system_mysql_db_fix50117.result @@ -10,12 +10,17 @@ help_keyword help_relation help_topic host +innodb_index_stats +innodb_table_stats ndb_binlog_index plugin proc procs_priv proxies_priv servers +slave_master_info +slave_relay_log_info +slave_worker_info slow_log tables_priv time_zone diff --git a/mysql-test/r/trans_read_only.result b/mysql-test/r/trans_read_only.result new file mode 100644 index 00000000000..2b1055d4a7b --- /dev/null +++ b/mysql-test/r/trans_read_only.result @@ -0,0 +1,40 @@ +# +# WL#5968: Implement START TRANSACTION READ (WRITE|ONLY); +# +# +# Test9: The --transaction-read-only startup option. +# Check that the option was set by the .opt file. +SELECT @@tx_read_only; +@@tx_read_only +1 +# Also for new connections. Switching to con1 +SELECT @@tx_read_only; +@@tx_read_only +1 +SET SESSION TRANSACTION READ WRITE; +SELECT @@tx_read_only; +@@tx_read_only +0 +# Connection default +SELECT @@tx_read_only; +@@tx_read_only +1 +# +# Test 10: SET TRANSACTION / START TRANSACTION + implicit commit. +SET SESSION TRANSACTION READ WRITE; +SET TRANSACTION READ ONLY; +# Since DDL does implicit commit before starting, SET TRANSACTION +# will have no effect because the "next" transaction will already +# be over before the DDL statement starts. +CREATE TABLE t1 (a INT); +START TRANSACTION READ ONLY; +# The same happens with START TRANSACTION +DROP TABLE t1; +# +# Test 11: INSERT DELAYED +CREATE TABLE t1(a INT); +START TRANSACTION READ ONLY; +INSERT DELAYED INTO t1 VALUES (1); +ERROR 25006: Cannot execute statement in a READ ONLY transaction. +COMMIT; +DROP TABLE t1; diff --git a/mysql-test/r/trigger_notembedded.result b/mysql-test/r/trigger_notembedded.result index d94713cbe02..0597a52a674 100644 --- a/mysql-test/r/trigger_notembedded.result +++ b/mysql-test/r/trigger_notembedded.result @@ -383,7 +383,7 @@ CALL p2(NEW.i1); INSERT INTO t1 VALUES (7); ERROR 42000: UPDATE command denied to user 'mysqltest_dfn'@'localhost' for column 'i1' in table 't1' INSERT INTO t2 VALUES (11); -ERROR 42000: SELECT,UPDATE command denied to user 'mysqltest_dfn'@'localhost' for column 'i1' in table 't2' +ERROR 42000: SELECT, UPDATE command denied to user 'mysqltest_dfn'@'localhost' for column 'i1' in table 't2' DROP TRIGGER t2_bi; DROP TRIGGER t1_bi; GRANT SELECT ON mysqltest_db1.* TO mysqltest_dfn@localhost; diff --git a/mysql-test/r/type_blob.result b/mysql-test/r/type_blob.result index 723efbdcb23..ff4ebd6b15c 100644 --- a/mysql-test/r/type_blob.result +++ b/mysql-test/r/type_blob.result @@ -232,7 +232,7 @@ HELLO HELLO MY a hello -set option sql_big_tables=1; +set big_tables=1; select distinct t from t1; t NULL @@ -313,7 +313,7 @@ HELLO HELLO MY a hello -set option sql_big_tables=0; +set big_tables=0; select distinct * from t1; t c b d NULL NULL NULL NULL diff --git a/mysql-test/r/update.result b/mysql-test/r/update.result index 427374fd995..3f3a3ac07df 100644 --- a/mysql-test/r/update.result +++ b/mysql-test/r/update.result @@ -561,3 +561,45 @@ ERROR HY000: View 'test.v1' references invalid table(s) or column(s) or function DROP VIEW v1; DROP FUNCTION f1; DROP TABLE t1; +# +# Verify that UPDATE does the same number of handler_update +# operations, no matter if there is ORDER BY or not. +# +CREATE TABLE t1 (i INT) ENGINE=INNODB; +INSERT INTO t1 VALUES (10),(11),(12),(13),(14),(15),(16),(17),(18),(19), +(20),(21),(22),(23),(24),(25),(26),(27),(28),(29), +(30),(31),(32),(33),(34),(35); +CREATE TABLE t2 (a CHAR(2), b CHAR(2), c CHAR(2), d CHAR(2), +INDEX idx (a,b(1),c)) ENGINE=INNODB; +INSERT INTO t2 SELECT i, i, i, i FROM t1; +FLUSH STATUS; +START TRANSACTION; +UPDATE t2 SET d = 10 WHERE b = 10 LIMIT 5; +SHOW STATUS LIKE 'HANDLER_UPDATE'; +Variable_name Value +Handler_update 1 +ROLLBACK; +FLUSH STATUS; +START TRANSACTION; +UPDATE t2 SET d = 10 WHERE b = 10 ORDER BY a, c LIMIT 5; +SHOW STATUS LIKE 'HANDLER_UPDATE'; +Variable_name Value +Handler_update 1 +ROLLBACK; +Same test with a different UPDATE. +ALTER TABLE t2 DROP INDEX idx, ADD INDEX idx2 (a, b); +FLUSH STATUS; +START TRANSACTION; +UPDATE t2 SET c = 10 LIMIT 5; +SHOW STATUS LIKE 'HANDLER_UPDATE'; +Variable_name Value +Handler_update 5 +ROLLBACK; +FLUSH STATUS; +START TRANSACTION; +UPDATE t2 SET c = 10 ORDER BY a, b DESC LIMIT 5; +SHOW STATUS LIKE 'HANDLER_UPDATE'; +Variable_name Value +Handler_update 5 +ROLLBACK; +DROP TABLE t1, t2; diff --git a/mysql-test/r/user_var-binlog.result b/mysql-test/r/user_var-binlog.result index 68d2364986d..27a0d7f0a1b 100644 --- a/mysql-test/r/user_var-binlog.result +++ b/mysql-test/r/user_var-binlog.result @@ -6,7 +6,7 @@ INSERT INTO t1 VALUES(@`a b`); set @var1= "';aaa"; SET @var2=char(ascii('a')); insert into t1 values (@var1),(@var2); -show binlog events from <binlog_start>; +include/show_binlog_events.inc Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query # # BEGIN master-bin.000001 # User var # # @`a b`=_latin1 0x68656C6C6F COLLATE latin1_swedish_ci diff --git a/mysql-test/r/variables.result b/mysql-test/r/variables.result index 6ec9d77230b..29986344f88 100644 --- a/mysql-test/r/variables.result +++ b/mysql-test/r/variables.result @@ -982,6 +982,8 @@ ssl_ca # ssl_capath # ssl_cert # ssl_cipher # +ssl_crl # +ssl_crlpath # ssl_key # select * from information_schema.session_variables where variable_name like 'ssl%' order by 1; VARIABLE_NAME VARIABLE_VALUE @@ -989,6 +991,8 @@ SSL_CA # SSL_CAPATH # SSL_CERT # SSL_CIPHER # +SSL_CRL # +SSL_CRLPATH # SSL_KEY # select @@log_queries_not_using_indexes; @@log_queries_not_using_indexes diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index 8d2d1561456..622cc001f63 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -4857,4 +4857,110 @@ SET optimizer_switch=@save_optimizer_switch_MDEV_3874; # ----------------------------------------------------------------- # -- End of 5.5 tests. # ----------------------------------------------------------------- +# some subqueries in SELECT list test +create table t1 (a int, b int); +create table t2 (a int, b int); +insert into t1 values (1,2), (3,4), (3,3), (5,6), (7,8), (9,10); +insert into t2 values (1,2), (3,4), (3,3), (5,6), (7,8), (9,10); +create algorithm=merge view v1 as select t1.a as a, (select max(b) from t2 where t1.a=t2.a) as c from t1; +explain extended +select * from v1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 6 100.00 +3 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 6 100.00 Using where +Warnings: +Note 1276 Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #2 +Note 1003 select `test`.`t1`.`a` AS `a`,(select max(`test`.`t2`.`b`) from `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`)) AS `c` from `test`.`t1` +select * from v1; +a c +1 2 +3 4 +3 4 +5 6 +7 8 +9 10 +explain extended +select * from t2, v1 where t2.a=v1.a; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) +3 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 6 100.00 Using where +Warnings: +Note 1276 Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #2 +Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t1`.`a` AS `a`,(select max(`test`.`t2`.`b`) from `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`)) AS `c` from `test`.`t2` join `test`.`t1` where (`test`.`t1`.`a` = `test`.`t2`.`a`) +select * from t2, v1 where t2.a=v1.a; +a b a c +1 2 1 2 +3 4 3 4 +3 3 3 4 +3 4 3 4 +3 3 3 4 +5 6 5 6 +7 8 7 8 +9 10 9 10 +explain extended +select * from t1, v1 where t1.a=v1.a; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 6 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) +3 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 6 100.00 Using where +Warnings: +Note 1276 Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #2 +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`a` AS `a`,(select max(`test`.`t2`.`b`) from `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`)) AS `c` from `test`.`t1` join `test`.`t1` where (`test`.`t1`.`a` = `test`.`t1`.`a`) +select * from t1, v1 where t1.a=v1.a; +a b a c +1 2 1 2 +3 4 3 4 +3 3 3 4 +3 4 3 4 +3 3 3 4 +5 6 5 6 +7 8 7 8 +9 10 9 10 +explain extended +select * from t1, v1 where t1.b=v1.c; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 6 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) +3 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 6 100.00 Using where +Warnings: +Note 1276 Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #2 +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`a` AS `a`,(select max(`test`.`t2`.`b`) from `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`)) AS `c` from `test`.`t1` join `test`.`t1` where (`test`.`t1`.`b` = (select max(`test`.`t2`.`b`) from `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`))) +select * from t1, v1 where t1.b=v1.c; +a b a c +1 2 1 2 +3 4 3 4 +3 4 3 4 +5 6 5 6 +7 8 7 8 +9 10 9 10 +explain extended +select * from t2, t1, v1 where t1.a=t2.a and t1.a=v1.a; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 6 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (incremental, BNL join) +3 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 6 100.00 Using where +Warnings: +Note 1276 Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #2 +Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`a` AS `a`,(select max(`test`.`t2`.`b`) from `test`.`t2` where (`test`.`t1`.`a` = `test`.`t2`.`a`)) AS `c` from `test`.`t2` join `test`.`t1` join `test`.`t1` where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t1`.`a` = `test`.`t2`.`a`)) +select * from t2, t1, v1 where t1.a=t2.a and t1.a=v1.a; +a b a b a c +1 2 1 2 1 2 +3 4 3 4 3 4 +3 3 3 4 3 4 +3 4 3 3 3 4 +3 3 3 3 3 4 +3 4 3 4 3 4 +3 3 3 4 3 4 +3 4 3 3 3 4 +3 3 3 3 3 4 +5 6 5 6 5 6 +7 8 7 8 7 8 +9 10 9 10 9 10 +drop view v1; +drop table t1,t2; +# ----------------------------------------------------------------- +# -- End of 10.0 tests. +# ----------------------------------------------------------------- SET optimizer_switch=@save_optimizer_switch; |