diff options
Diffstat (limited to 'mysql-test/r')
91 files changed, 3193 insertions, 156 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/comments.result b/mysql-test/r/comments.result index 8aebe95c5ac..e65c886014d 100644 --- a/mysql-test/r/comments.result +++ b/mysql-test/r/comments.result @@ -10,7 +10,7 @@ ERROR 42000: Query was empty select 1 /*!32301 +1 */; 1 +1 2 -select 1 /*!52301 +1 */; +select 1 /*!952301 +1 */; 1 1 select 1--1; @@ -35,19 +35,19 @@ select 1 /*M!50000 +1 */; select 1 /*M!50300 +1 */; 1 +1 2 -select 2 /*M!99999 +1 */; +select 2 /*M!999999 +1 */; 2 2 select 2 /*M!0000 +1 */; 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 '0000 +1 */' at line 1 select 1/*!2*/; 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 '2*/' at line 1 -select 1/*!000002*/; +select 1/*!0000002*/; 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 '2*/' at line 1 select 1/*!999992*/; 1 1 -select 1 + /*!00000 2 */ + 3 /*!99999 noise*/ + 4; +select 1 + /*!00000 2 */ + 3 /*!999999 noise*/ + 4; 1 + 2 + 3 + 4 10 drop table if exists table_28779; @@ -60,8 +60,8 @@ prepare bar from "DELETE FROM table_28779 WHERE a = 7 OR 1=1/*! AND 2=2;"; 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 prepare bar from "DELETE FROM table_28779 WHERE a = 7 OR 1=1/*! AND 2=2;*"; 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 -prepare bar from "DELETE FROM table_28779 WHERE a = 7 OR 1=1/*!98765' AND b = 'bar';"; -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 '/*!98765' AND b = 'bar'' at line 1 -prepare bar from "DELETE FROM table_28779 WHERE a = 7 OR 1=1/*!98765' AND b = 'bar';*"; -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 '/*!98765' AND b = 'bar';*' at line 1 +prepare bar from "DELETE FROM table_28779 WHERE a = 7 OR 1=1/*!998765' AND b = 'bar';"; +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 '/*!998765' AND b = 'bar'' at line 1 +prepare bar from "DELETE FROM table_28779 WHERE a = 7 OR 1=1/*!998765' AND b = 'bar';*"; +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 '/*!998765' AND b = 'bar';*' at line 1 drop table table_28779; 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/filesort_debug.result b/mysql-test/r/filesort_debug.result index 6d6eb817259..13912ac8756 100644 --- a/mysql-test/r/filesort_debug.result +++ b/mysql-test/r/filesort_debug.result @@ -4,7 +4,7 @@ SET @old_debug= @@session.debug; # CREATE TABLE t1(f0 int auto_increment primary key, f1 int); INSERT INTO t1(f1) VALUES (0),(1),(2),(3),(4),(5); -SET session debug_dbug= '+d,make_sort_keys_alloc_fail'; +SET session debug_dbug= '+d,alloc_sort_buffer_fail'; CALL mtr.add_suppression("Out of sort memory"); SELECT * FROM t1 ORDER BY f1 ASC, f0; ERROR HY001: Out of sort memory, consider increasing server sort buffer size diff --git a/mysql-test/r/flush.result b/mysql-test/r/flush.result index d3b3cd16210..b64351045bf 100644 --- a/mysql-test/r/flush.result +++ b/mysql-test/r/flush.result @@ -489,3 +489,10 @@ UNLOCK TABLES; # Switching to connection 'default'. COMMIT; DROP TABLE t1; +# +# Test flushing slave or relay logs twice +# +flush relay logs,relay logs; +ERROR HY000: Incorrect usage of FLUSH and RELAY LOGS +flush slave,slave; +ERROR HY000: Incorrect usage of FLUSH and SLAVE 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/func_system.result b/mysql-test/r/func_system.result index fa05021eb47..c0d63200f82 100644 --- a/mysql-test/r/func_system.result +++ b/mysql-test/r/func_system.result @@ -25,14 +25,14 @@ user() like _latin1"%@%" select charset(user()); charset(user()) utf8 -select version()>="3.23.29"; -version()>="3.23.29" +select version()>="03.23.29"; +version()>="03.23.29" 1 -select version()>=_utf8"3.23.29"; -version()>=_utf8"3.23.29" +select version()>=_utf8"03.23.29"; +version()>=_utf8"03.23.29" 1 -select version()>=_latin1"3.23.29"; -version()>=_latin1"3.23.29" +select version()>=_latin1"03.23.29"; +version()>=_latin1"03.23.29" 1 select charset(version()); charset(version()) diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result index 15120af869c..98854db4812 100644 --- a/mysql-test/r/func_time.result +++ b/mysql-test/r/func_time.result @@ -1023,11 +1023,11 @@ MAKETIME(CAST(-1 AS UNSIGNED), 0, 0) Warnings: Note 1105 Cast to unsigned converted negative integer to it's positive complement Warning 1292 Truncated incorrect time value: '18446744073709551615:00:00' -SELECT EXTRACT(HOUR FROM '100000:02:03'); -EXTRACT(HOUR FROM '100000:02:03') +SELECT EXTRACT(HOUR FROM '10000:02:03'); +EXTRACT(HOUR FROM '10000:02:03') 838 Warnings: -Warning 1292 Truncated incorrect time value: '100000:02:03' +Warning 1292 Truncated incorrect time value: '10000:02:03' CREATE TABLE t1(f1 TIME); INSERT INTO t1 VALUES('916:00:00 a'); Warnings: 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 222977e5106..72c53697e61 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 @@ -2149,3 +2149,48 @@ f1 MIN(f2) MAX(f2) 4 00:25:00 00:25:00 DROP TABLE t1; #End of test#49771 +# +# Bug #58782 +# Missing rows with SELECT .. WHERE .. IN subquery +# with full GROUP BY and no aggr +# +CREATE TABLE t1 ( +pk INT NOT NULL, +col_int_nokey INT, +PRIMARY KEY (pk) +); +INSERT INTO t1 VALUES (10,7); +INSERT INTO t1 VALUES (11,1); +INSERT INTO t1 VALUES (12,5); +INSERT INTO t1 VALUES (13,3); +SELECT pk AS field1, col_int_nokey AS field2 +FROM t1 +WHERE col_int_nokey > 0 +GROUP BY field1, field2; +field1 field2 +10 7 +11 1 +12 5 +13 3 +CREATE TABLE where_subselect +SELECT pk AS field1, col_int_nokey AS field2 +FROM t1 +WHERE col_int_nokey > 0 +GROUP BY field1, field2 +; +SELECT * +FROM where_subselect +WHERE (field1, field2) IN ( +SELECT pk AS field1, col_int_nokey AS field2 +FROM t1 +WHERE col_int_nokey > 0 +GROUP BY field1, field2 +); +field1 field2 +10 7 +11 1 +12 5 +13 3 +DROP TABLE t1; +DROP TABLE where_subselect; +# End of Bug #58782 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/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/innodb_mysql_sync.result b/mysql-test/r/innodb_mysql_sync.result index 58254412c5b..044e582ceb6 100644 --- a/mysql-test/r/innodb_mysql_sync.result +++ b/mysql-test/r/innodb_mysql_sync.result @@ -18,10 +18,10 @@ SET DEBUG_SYNC='now SIGNAL table_altered'; # Complete optimization Table Op Msg_type Msg_text test.t1 optimize note Table does not support optimize, doing recreate + analyze instead -test.t1 optimize error Got error -1 from storage engine +test.t1 optimize error Got error -1 "Internal error < 0 (Not system error)" from storage engine test.t1 optimize status Operation failed Warnings: -Error 1030 Got error -1 from storage engine +Error 1030 Got error -1 "Internal error < 0 (Not system error)" from storage engine DROP TABLE t1; SET DEBUG_SYNC='RESET'; # 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/loaddata.result b/mysql-test/r/loaddata.result index ee0e63706a1..c4c8216c14a 100644 --- a/mysql-test/r/loaddata.result +++ b/mysql-test/r/loaddata.result @@ -5,8 +5,8 @@ Warnings: Warning 1265 Data truncated for column 'a' at row 1 Warning 1265 Data truncated for column 'c' at row 1 Warning 1265 Data truncated for column 'd' at row 1 -Warning 1264 Out of range value for column 'a' at row 2 -Warning 1264 Out of range value for column 'b' at row 2 +Warning 1265 Data truncated for column 'a' at row 2 +Warning 1265 Data truncated for column 'b' at row 2 Warning 1265 Data truncated for column 'd' at row 2 load data infile '../../std_data/loaddata1.dat' into table t1 fields terminated by ',' IGNORE 2 LINES; SELECT * from t1; @@ -20,7 +20,7 @@ load data infile '../../std_data/loaddata1.dat' into table t1 fields terminated Warnings: Warning 1265 Data truncated for column 'c' at row 1 Warning 1265 Data truncated for column 'd' at row 1 -Warning 1264 Out of range value for column 'b' at row 2 +Warning 1265 Data truncated for column 'b' at row 2 Warning 1265 Data truncated for column 'd' at row 2 SELECT * from t1; a b c d diff --git a/mysql-test/r/log_tables.result b/mysql-test/r/log_tables.result index 62775f3db4d..c25bd6727df 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_table2.result b/mysql-test/r/lowercase_table2.result index abd16641b56..c478d792e52 100644 --- a/mysql-test/r/lowercase_table2.result +++ b/mysql-test/r/lowercase_table2.result @@ -272,7 +272,7 @@ Database Table In_use Name_locked test t_bug44738_uppercase 0 0 # So attempt to create table with the same name should fail. create table t_bug44738_UPPERCASE (i int); -ERROR HY000: Can't find file: 't_bug44738_uppercase' (errno: 2) +ERROR HY000: Can't find file: 't_bug44738_uppercase' (errno: 2 "No such file or directory") # And should succeed after FLUSH TABLES. flush tables; create table t_bug44738_UPPERCASE (i int); 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/myisam-system.result b/mysql-test/r/myisam-system.result index b3ba8066f5c..924e7885814 100644 --- a/mysql-test/r/myisam-system.result +++ b/mysql-test/r/myisam-system.result @@ -2,7 +2,7 @@ drop table if exists t1,t2; create table t1 (a int) engine=myisam; drop table if exists t1; Warnings: -Warning 2 Can't find file: 't1' (errno: 2) +Warning 2 Can't find file: 't1' (errno: 2 "No such file or directory") create table t1 (a int) engine=myisam; drop table t1; Got one of the listed errors diff --git a/mysql-test/r/myisam.result b/mysql-test/r/myisam.result index 64c61d4ee36..6c0826775ad 100644 --- a/mysql-test/r/myisam.result +++ b/mysql-test/r/myisam.result @@ -1697,7 +1697,7 @@ t1 CREATE TABLE `t1` ( ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t1 (v varchar(65535)); -ERROR 42000: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs +ERROR 42000: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs set storage_engine=MyISAM; set @save_concurrent_insert=@@concurrent_insert; set global concurrent_insert=1; diff --git a/mysql-test/r/myisampack.result b/mysql-test/r/myisampack.result index 56f61ccdf47..2cefa385048 100644 --- a/mysql-test/r/myisampack.result +++ b/mysql-test/r/myisampack.result @@ -113,7 +113,7 @@ ERROR 42S02: Table 'test.t3' doesn't exist # ===== myisampack.4 ===== #Tests the myisampack join operation with an existing destination .frm,.MYI,.MDI #the command should fail with exit status 2 -myisampack: Can't create/write to file (Errcode: 17) +myisampack: Can't create/write to file (Errcode: 17 "File exists") Aborted: file is not compressed DROP TABLE t1,t2,t3; DROP TABLE mysql_db1.t1; 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 33904dfd9bd..63b0ea2a71a 100644 --- a/mysql-test/r/mysqlbinlog.result +++ b/mysql-test/r/mysqlbinlog.result @@ -890,8 +890,9 @@ 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 master-bin.000002 # Query # # use `test1`; CREATE TABLE t1(id int) master-bin.000002 # Query # # DROP DATABASE test1 diff --git a/mysql-test/r/mysqlbinlog2.result b/mysql-test/r/mysqlbinlog2.result index 806cf74479e..bf65bab602d 100644 --- a/mysql-test/r/mysqlbinlog2.result +++ b/mysql-test/r/mysqlbinlog2.result @@ -697,7 +697,6 @@ SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; -SET INSERT_ID=6/*!*/; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; @@ -1483,17 +1482,6 @@ COMMIT /*!*/; DELIMITER ; DELIMITER /*!*/; -SET TIMESTAMP=1579609943/*!*/; -SET @@session.pseudo_thread_id=999999999/*!*/; -SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; -SET @@session.sql_mode=0/*!*/; -SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; -/*!\C latin1 *//*!*/; -SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; -SET @@session.lc_time_names=0/*!*/; -SET @@session.collation_database=DEFAULT/*!*/; -BEGIN -/*!*/; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; diff --git a/mysql-test/r/mysqlbinlog_row.result b/mysql-test/r/mysqlbinlog_row.result index ce46b7ea898..6361cdadb32 100644 --- a/mysql-test/r/mysqlbinlog_row.result +++ b/mysql-test/r/mysqlbinlog_row.result @@ -335,6 +335,8 @@ DELIMITER /*!*/; #010909 4:46:40 server id 1 end_log_pos # Start: binlog v 4, server v #.##.## created 010909 4:46:40 at startup ROLLBACK/*!*/; # at # +#010909 4:46:40 server id 1 end_log_pos # Binlog checkpoint master-bin.000001 +# at # #010909 4:46:40 server id 1 end_log_pos # Query thread_id=# exec_time=# error_code=0 use `test`/*!*/; SET TIMESTAMP=1000000000/*!*/; diff --git a/mysql-test/r/mysqlbinlog_row_innodb.result b/mysql-test/r/mysqlbinlog_row_innodb.result index 63b64489a37..4be266d586c 100644 --- a/mysql-test/r/mysqlbinlog_row_innodb.result +++ b/mysql-test/r/mysqlbinlog_row_innodb.result @@ -2252,6 +2252,8 @@ DELIMITER /*!*/; #010909 4:46:40 server id 1 end_log_pos # Start: binlog v 4, server v #.##.## created 010909 4:46:40 at startup ROLLBACK/*!*/; # at # +#010909 4:46:40 server id 1 end_log_pos # Binlog checkpoint master-bin.000001 +# at # #010909 4:46:40 server id 1 end_log_pos # Query thread_id=# exec_time=# error_code=0 use `test`/*!*/; SET TIMESTAMP=1000000000/*!*/; @@ -3875,6 +3877,8 @@ DELIMITER /*!*/; #010909 4:46:40 server id 1 end_log_pos # Start: binlog v 4, server v #.##.## created 010909 4:46:40 at startup ROLLBACK/*!*/; # at # +#010909 4:46:40 server id 1 end_log_pos # Binlog checkpoint master-bin.000001 +# at # #010909 4:46:40 server id 1 end_log_pos # Query thread_id=# exec_time=# error_code=0 use `test`/*!*/; SET TIMESTAMP=1000000000/*!*/; @@ -4242,6 +4246,8 @@ DELIMITER /*!*/; #010909 4:46:40 server id 1 end_log_pos # Start: binlog v 4, server v #.##.## created 010909 4:46:40 at startup ROLLBACK/*!*/; # at # +#010909 4:46:40 server id 1 end_log_pos # Binlog checkpoint master-bin.000001 +# at # #010909 4:46:40 server id 1 end_log_pos # Query thread_id=# exec_time=# error_code=0 use `test`/*!*/; SET TIMESTAMP=1000000000/*!*/; @@ -4803,6 +4809,8 @@ DELIMITER /*!*/; #010909 4:46:40 server id 1 end_log_pos # Start: binlog v 4, server v #.##.## created 010909 4:46:40 at startup ROLLBACK/*!*/; # at # +#010909 4:46:40 server id 1 end_log_pos # Binlog checkpoint master-bin.000001 +# at # #010909 4:46:40 server id 1 end_log_pos # Query thread_id=# exec_time=# error_code=0 use `test`/*!*/; SET TIMESTAMP=1000000000/*!*/; diff --git a/mysql-test/r/mysqlbinlog_row_myisam.result b/mysql-test/r/mysqlbinlog_row_myisam.result index 6e8c7cd64f9..d4062565320 100644 --- a/mysql-test/r/mysqlbinlog_row_myisam.result +++ b/mysql-test/r/mysqlbinlog_row_myisam.result @@ -2252,6 +2252,8 @@ DELIMITER /*!*/; #010909 4:46:40 server id 1 end_log_pos # Start: binlog v 4, server v #.##.## created 010909 4:46:40 at startup ROLLBACK/*!*/; # at # +#010909 4:46:40 server id 1 end_log_pos # Binlog checkpoint master-bin.000001 +# at # #010909 4:46:40 server id 1 end_log_pos # Query thread_id=# exec_time=# error_code=0 use `test`/*!*/; SET TIMESTAMP=1000000000/*!*/; @@ -3897,6 +3899,8 @@ DELIMITER /*!*/; #010909 4:46:40 server id 1 end_log_pos # Start: binlog v 4, server v #.##.## created 010909 4:46:40 at startup ROLLBACK/*!*/; # at # +#010909 4:46:40 server id 1 end_log_pos # Binlog checkpoint master-bin.000001 +# at # #010909 4:46:40 server id 1 end_log_pos # Query thread_id=# exec_time=# error_code=0 use `test`/*!*/; SET TIMESTAMP=1000000000/*!*/; @@ -4270,6 +4274,8 @@ DELIMITER /*!*/; #010909 4:46:40 server id 1 end_log_pos # Start: binlog v 4, server v #.##.## created 010909 4:46:40 at startup ROLLBACK/*!*/; # at # +#010909 4:46:40 server id 1 end_log_pos # Binlog checkpoint master-bin.000001 +# at # #010909 4:46:40 server id 1 end_log_pos # Query thread_id=# exec_time=# error_code=0 use `test`/*!*/; SET TIMESTAMP=1000000000/*!*/; @@ -4841,6 +4847,8 @@ DELIMITER /*!*/; #010909 4:46:40 server id 1 end_log_pos # Start: binlog v 4, server v #.##.## created 010909 4:46:40 at startup ROLLBACK/*!*/; # at # +#010909 4:46:40 server id 1 end_log_pos # Binlog checkpoint master-bin.000001 +# at # #010909 4:46:40 server id 1 end_log_pos # Query thread_id=# exec_time=# error_code=0 use `test`/*!*/; SET TIMESTAMP=1000000000/*!*/; diff --git a/mysql-test/r/mysqlbinlog_row_trans.result b/mysql-test/r/mysqlbinlog_row_trans.result index 33c997d76a7..85106ab9684 100644 --- a/mysql-test/r/mysqlbinlog_row_trans.result +++ b/mysql-test/r/mysqlbinlog_row_trans.result @@ -131,6 +131,8 @@ DELIMITER /*!*/; #010909 4:46:40 server id 1 end_log_pos # Start: binlog v 4, server v #.##.## created 010909 4:46:40 at startup ROLLBACK/*!*/; # at # +#010909 4:46:40 server id 1 end_log_pos # Binlog checkpoint master-bin.000001 +# at # #010909 4:46:40 server id 1 end_log_pos # Query thread_id=# exec_time=# error_code=0 use `test`/*!*/; SET TIMESTAMP=1000000000/*!*/; 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 c36cc96bb1a..c065f949802 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 @@ -361,8 +359,7 @@ The following options may be given as the first argument: --max-binlog-cache-size=# Sets the total size of the transactional cache --max-binlog-size=# Binary log will be rotated automatically when the size - exceeds this value. Will also apply to relay logs if - max_relay_log_size is 0 + exceeds this value. --max-binlog-stmt-cache-size=# Sets the total size of the statement cache --max-connect-errors=# @@ -388,9 +385,9 @@ The following options may be given as the first argument: --max-prepared-stmt-count=# Maximum number of prepared statements in the server --max-relay-log-size=# - If non-zero: relay log will be rotated automatically when - the size exceeds this value; if zero: when the size - exceeds max_binlog_size + relay log will be rotated automatically when the size + exceeds this value. If 0 are startup, it's set to + max_binlog_size --max-seeks-for-key=# Limit assumed max number of seeks when looking up rows based on a key @@ -503,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=# @@ -525,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=# @@ -533,6 +594,12 @@ 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 @@ -745,7 +812,7 @@ The following options may be given as the first argument: The maximum packet length to sent successfully from the master to slave. --slave-net-timeout=# - Number of seconds to wait for more data from a + Number of seconds to wait for more data from any master/slave connection before aborting the read --slave-skip-errors=name Tells the slave thread to continue replication when a @@ -836,6 +903,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 @@ -970,7 +1040,7 @@ max-join-size 18446744073709551615 max-length-for-sort-data 1024 max-long-data-size 1048576 max-prepared-stmt-count 16382 -max-relay-log-size 0 +max-relay-log-size 1073741824 max-seeks-for-key 18446744073709551615 max-sort-length 1024 max-sp-recursion-depth 0 @@ -1003,8 +1073,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 @@ -1014,10 +1104,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 +performance-schema-setup-actors-size 100 +performance-schema-setup-objects-size 100 +performance-schema-users-size 100 plugin-load (No default value) plugin-maturity unknown port 3306 @@ -1095,6 +1192,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-max.result b/mysql-test/r/mysqldump-max.result index 6722f308358..fb0be20c119 100644 --- a/mysql-test/r/mysqldump-max.result +++ b/mysql-test/r/mysqldump-max.result @@ -332,12 +332,12 @@ a b 2 1 DROP TABLE t1; DROP TABLE t2; -SHOW BINLOG EVENTS LIMIT 6,3; +SHOW BINLOG EVENTS LIMIT 7,3; Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000001 663 Query 1 731 BEGIN -master-bin.000001 731 Query 1 828 use `test`; INSERT INTO t2 VALUES (1,0), (2,0) -master-bin.000001 828 Xid 1 855 COMMIT /* XID */ --- CHANGE MASTER TO MASTER_LOG_FILE='master-bin.000001', MASTER_LOG_POS=855; +master-bin.000001 704 Query 1 772 BEGIN +master-bin.000001 772 Query 1 869 use `test`; INSERT INTO t2 VALUES (1,0), (2,0) +master-bin.000001 869 Xid 1 896 COMMIT /* XID */ +-- CHANGE MASTER TO MASTER_LOG_FILE='master-bin.000001', MASTER_LOG_POS=896; SELECT * FROM t1 ORDER BY a; a 1 diff --git a/mysql-test/r/mysqldump.result b/mysql-test/r/mysqldump.result index 7b650addd3d..b58a904bc1f 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/mysqltest.result b/mysql-test/r/mysqltest.result index fdb3029059f..d08ffd96a04 100644 --- a/mysql-test/r/mysqltest.result +++ b/mysql-test/r/mysqltest.result @@ -232,7 +232,7 @@ mysqltest: At line 2: Spurious text after `query` expression mysqltest: At line 1: Missing argument(s) to 'error' mysqltest: At line 1: Missing argument(s) to 'error' mysqltest: At line 1: The sqlstate definition must start with an uppercase S -mysqltest: At line 1: The error name definition must start with an uppercase E +mysqltest: At line 1: The error name definition must start with an uppercase E or W mysqltest: At line 1: Invalid argument to error: '9eeeee' - the errno may only consist of digits[0-9] mysqltest: At line 1: Invalid argument to error: '1sssss' - the errno may only consist of digits[0-9] mysqltest: At line 1: The sqlstate must be exactly 5 chars long 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/order_by.result b/mysql-test/r/order_by.result index 53fed519a15..1ded1e90314 100644 --- a/mysql-test/r/order_by.result +++ b/mysql-test/r/order_by.result @@ -1439,6 +1439,7 @@ CALL mtr.add_suppression("Out of sort memory"); select * from t1 order by b; ERROR HY001: Out of sort memory, consider increasing server sort buffer size drop table t1; +set session sort_buffer_size= 30000; # # Bug #39844: Query Crash Mysql Server 5.0.67 # @@ -1533,6 +1534,890 @@ ppfcz1 DE ppfcz1 14 8 57.5 ppfcz1 DE ppfcz1 14 9 59.5 ppfcz1 DE ppfcz1 14 10 61.5 DROP TABLE t1,t2,t3; +# +# WL#1393 - Optimizing filesort with small limit +# +CREATE TABLE t1(f0 int auto_increment primary key, f1 int, f2 varchar(200)); +INSERT INTO t1(f1, f2) VALUES +(0,"0"),(1,"1"),(2,"2"),(3,"3"),(4,"4"),(5,"5"), +(6,"6"),(7,"7"),(8,"8"),(9,"9"),(10,"10"), +(11,"11"),(12,"12"),(13,"13"),(14,"14"),(15,"15"), +(16,"16"),(17,"17"),(18,"18"),(19,"19"),(20,"20"), +(21,"21"),(22,"22"),(23,"23"),(24,"24"),(25,"25"), +(26,"26"),(27,"27"),(28,"28"),(29,"29"),(30,"30"), +(31,"31"),(32,"32"),(33,"33"),(34,"34"),(35,"35"), +(36,"36"),(37,"37"),(38,"38"),(39,"39"),(40,"40"), +(41,"41"),(42,"42"),(43,"43"),(44,"44"),(45,"45"), +(46,"46"),(47,"47"),(48,"48"),(49,"49"),(50,"50"), +(51,"51"),(52,"52"),(53,"53"),(54,"54"),(55,"55"), +(56,"56"),(57,"57"),(58,"58"),(59,"59"),(60,"60"), +(61,"61"),(62,"62"),(63,"63"),(64,"64"),(65,"65"), +(66,"66"),(67,"67"),(68,"68"),(69,"69"),(70,"70"), +(71,"71"),(72,"72"),(73,"73"),(74,"74"),(75,"75"), +(76,"76"),(77,"77"),(78,"78"),(79,"79"),(80,"80"), +(81,"81"),(82,"82"),(83,"83"),(84,"84"),(85,"85"), +(86,"86"),(87,"87"),(88,"88"),(89,"89"),(90,"90"), +(91,"91"),(92,"92"),(93,"93"),(94,"94"),(95,"95"), +(96,"96"),(97,"97"),(98,"98"),(99,"99"); +SELECT * FROM t1 ORDER BY f1 ASC, f0 LIMIT 100; +f0 f1 f2 +1 0 0 +2 1 1 +3 2 2 +4 3 3 +5 4 4 +6 5 5 +7 6 6 +8 7 7 +9 8 8 +10 9 9 +11 10 10 +12 11 11 +13 12 12 +14 13 13 +15 14 14 +16 15 15 +17 16 16 +18 17 17 +19 18 18 +20 19 19 +21 20 20 +22 21 21 +23 22 22 +24 23 23 +25 24 24 +26 25 25 +27 26 26 +28 27 27 +29 28 28 +30 29 29 +31 30 30 +32 31 31 +33 32 32 +34 33 33 +35 34 34 +36 35 35 +37 36 36 +38 37 37 +39 38 38 +40 39 39 +41 40 40 +42 41 41 +43 42 42 +44 43 43 +45 44 44 +46 45 45 +47 46 46 +48 47 47 +49 48 48 +50 49 49 +51 50 50 +52 51 51 +53 52 52 +54 53 53 +55 54 54 +56 55 55 +57 56 56 +58 57 57 +59 58 58 +60 59 59 +61 60 60 +62 61 61 +63 62 62 +64 63 63 +65 64 64 +66 65 65 +67 66 66 +68 67 67 +69 68 68 +70 69 69 +71 70 70 +72 71 71 +73 72 72 +74 73 73 +75 74 74 +76 75 75 +77 76 76 +78 77 77 +79 78 78 +80 79 79 +81 80 80 +82 81 81 +83 82 82 +84 83 83 +85 84 84 +86 85 85 +87 86 86 +88 87 87 +89 88 88 +90 89 89 +91 90 90 +92 91 91 +93 92 92 +94 93 93 +95 94 94 +96 95 95 +97 96 96 +98 97 97 +99 98 98 +100 99 99 +SELECT * FROM t1 ORDER BY f1 ASC, f0 LIMIT 30; +f0 f1 f2 +1 0 0 +2 1 1 +3 2 2 +4 3 3 +5 4 4 +6 5 5 +7 6 6 +8 7 7 +9 8 8 +10 9 9 +11 10 10 +12 11 11 +13 12 12 +14 13 13 +15 14 14 +16 15 15 +17 16 16 +18 17 17 +19 18 18 +20 19 19 +21 20 20 +22 21 21 +23 22 22 +24 23 23 +25 24 24 +26 25 25 +27 26 26 +28 27 27 +29 28 28 +30 29 29 +SELECT * FROM t1 ORDER BY f1 ASC, f0 LIMIT 0; +f0 f1 f2 +SELECT * FROM t1 ORDER BY f2 DESC, f0 LIMIT 30; +f0 f1 f2 +100 99 99 +99 98 98 +98 97 97 +97 96 96 +96 95 95 +95 94 94 +94 93 93 +93 92 92 +92 91 91 +91 90 90 +10 9 9 +90 89 89 +89 88 88 +88 87 87 +87 86 86 +86 85 85 +85 84 84 +84 83 83 +83 82 82 +82 81 81 +81 80 80 +9 8 8 +80 79 79 +79 78 78 +78 77 77 +77 76 76 +76 75 75 +75 74 74 +74 73 73 +73 72 72 +SELECT * FROM t1 ORDER BY f2 DESC, f0 LIMIT 0; +f0 f1 f2 +SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 20; +f0 f1 f2 +12 11 11 +13 12 12 +14 13 13 +15 14 14 +16 15 15 +17 16 16 +18 17 17 +19 18 18 +20 19 19 +21 20 20 +22 21 21 +23 22 22 +24 23 23 +25 24 24 +26 25 25 +27 26 26 +28 27 27 +29 28 28 +30 29 29 +31 30 30 +SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 0; +f0 f1 f2 +SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 10 OFFSET 10; +f0 f1 f2 +22 21 21 +23 22 22 +24 23 23 +25 24 24 +26 25 25 +27 26 26 +28 27 27 +29 28 28 +30 29 29 +31 30 30 +SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 0 OFFSET 10; +f0 f1 f2 +set sort_buffer_size= 32768; +CREATE TEMPORARY TABLE tmp (f1 int, f2 varchar(20)); +INSERT INTO tmp SELECT f1, f2 FROM t1; +INSERT INTO t1(f1, f2) SELECT * FROM tmp; +INSERT INTO tmp SELECT f1, f2 FROM t1; +INSERT INTO t1(f1, f2) SELECT * FROM tmp; +SELECT * FROM t1 ORDER BY f1 ASC, f0 LIMIT 30; +f0 f1 f2 +1 0 0 +101 0 0 +201 0 0 +301 0 0 +401 0 0 +2 1 1 +102 1 1 +202 1 1 +302 1 1 +402 1 1 +3 2 2 +103 2 2 +203 2 2 +303 2 2 +403 2 2 +4 3 3 +104 3 3 +204 3 3 +304 3 3 +404 3 3 +5 4 4 +105 4 4 +205 4 4 +305 4 4 +405 4 4 +6 5 5 +106 5 5 +206 5 5 +306 5 5 +406 5 5 +SELECT * FROM t1 ORDER BY f1 ASC, f0 LIMIT 0; +f0 f1 f2 +SELECT * FROM t1 ORDER BY f2 DESC, f0 LIMIT 30; +f0 f1 f2 +100 99 99 +200 99 99 +300 99 99 +400 99 99 +500 99 99 +99 98 98 +199 98 98 +299 98 98 +399 98 98 +499 98 98 +98 97 97 +198 97 97 +298 97 97 +398 97 97 +498 97 97 +97 96 96 +197 96 96 +297 96 96 +397 96 96 +497 96 96 +96 95 95 +196 95 95 +296 95 95 +396 95 95 +496 95 95 +95 94 94 +195 94 94 +295 94 94 +395 94 94 +495 94 94 +SELECT * FROM t1 ORDER BY f2 DESC, f0 LIMIT 0; +f0 f1 f2 +SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 20; +f0 f1 f2 +12 11 11 +112 11 11 +212 11 11 +312 11 11 +412 11 11 +13 12 12 +113 12 12 +213 12 12 +313 12 12 +413 12 12 +14 13 13 +114 13 13 +214 13 13 +314 13 13 +414 13 13 +15 14 14 +115 14 14 +215 14 14 +315 14 14 +415 14 14 +SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 0; +f0 f1 f2 +SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 10 OFFSET 10; +f0 f1 f2 +14 13 13 +114 13 13 +214 13 13 +314 13 13 +414 13 13 +15 14 14 +115 14 14 +215 14 14 +315 14 14 +415 14 14 +SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 0 OFFSET 10; +f0 f1 f2 +set sort_buffer_size= 32768; +SELECT SQL_CALC_FOUND_ROWS * FROM t1 +ORDER BY f1, f0 LIMIT 30; +f0 f1 f2 +1 0 0 +101 0 0 +201 0 0 +301 0 0 +401 0 0 +2 1 1 +102 1 1 +202 1 1 +302 1 1 +402 1 1 +3 2 2 +103 2 2 +203 2 2 +303 2 2 +403 2 2 +4 3 3 +104 3 3 +204 3 3 +304 3 3 +404 3 3 +5 4 4 +105 4 4 +205 4 4 +305 4 4 +405 4 4 +6 5 5 +106 5 5 +206 5 5 +306 5 5 +406 5 5 +SELECT FOUND_ROWS(); +FOUND_ROWS() +500 +SELECT SQL_CALC_FOUND_ROWS * FROM t1 +ORDER BY f1, f0 LIMIT 0; +f0 f1 f2 +SELECT FOUND_ROWS(); +FOUND_ROWS() +500 +SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE f1>10 +ORDER BY f2, f0 LIMIT 20; +f0 f1 f2 +12 11 11 +112 11 11 +212 11 11 +312 11 11 +412 11 11 +13 12 12 +113 12 12 +213 12 12 +313 12 12 +413 12 12 +14 13 13 +114 13 13 +214 13 13 +314 13 13 +414 13 13 +15 14 14 +115 14 14 +215 14 14 +315 14 14 +415 14 14 +SELECT FOUND_ROWS(); +FOUND_ROWS() +445 +SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE f1>10 +ORDER BY f2, f0 LIMIT 0; +f0 f1 f2 +SELECT FOUND_ROWS(); +FOUND_ROWS() +445 +SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE f1>10 +ORDER BY f2, f0 LIMIT 10 OFFSET 10; +f0 f1 f2 +14 13 13 +114 13 13 +214 13 13 +314 13 13 +414 13 13 +15 14 14 +115 14 14 +215 14 14 +315 14 14 +415 14 14 +SELECT FOUND_ROWS(); +FOUND_ROWS() +445 +SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE f1>10 +ORDER BY f2, f0 LIMIT 0 OFFSET 10; +f0 f1 f2 +SELECT FOUND_ROWS(); +FOUND_ROWS() +445 +set sort_buffer_size= 327680; +SELECT * FROM t1 JOIN tmp on t1.f2=tmp.f2 +ORDER BY tmp.f1, f0 LIMIT 30; +f0 f1 f2 f1 f2 +1 0 0 0 0 +1 0 0 0 0 +1 0 0 0 0 +101 0 0 0 0 +101 0 0 0 0 +101 0 0 0 0 +201 0 0 0 0 +201 0 0 0 0 +201 0 0 0 0 +301 0 0 0 0 +301 0 0 0 0 +301 0 0 0 0 +401 0 0 0 0 +401 0 0 0 0 +401 0 0 0 0 +2 1 1 1 1 +2 1 1 1 1 +2 1 1 1 1 +102 1 1 1 1 +102 1 1 1 1 +102 1 1 1 1 +202 1 1 1 1 +202 1 1 1 1 +202 1 1 1 1 +302 1 1 1 1 +302 1 1 1 1 +302 1 1 1 1 +402 1 1 1 1 +402 1 1 1 1 +402 1 1 1 1 +SELECT * FROM t1 JOIN tmp on t1.f2=tmp.f2 +ORDER BY tmp.f1, f0 LIMIT 30 OFFSET 30; +f0 f1 f2 f1 f2 +3 2 2 2 2 +3 2 2 2 2 +3 2 2 2 2 +103 2 2 2 2 +103 2 2 2 2 +103 2 2 2 2 +203 2 2 2 2 +203 2 2 2 2 +203 2 2 2 2 +303 2 2 2 2 +303 2 2 2 2 +303 2 2 2 2 +403 2 2 2 2 +403 2 2 2 2 +403 2 2 2 2 +4 3 3 3 3 +4 3 3 3 3 +4 3 3 3 3 +104 3 3 3 3 +104 3 3 3 3 +104 3 3 3 3 +204 3 3 3 3 +204 3 3 3 3 +204 3 3 3 3 +304 3 3 3 3 +304 3 3 3 3 +304 3 3 3 3 +404 3 3 3 3 +404 3 3 3 3 +404 3 3 3 3 +SELECT SQL_CALC_FOUND_ROWS * FROM t1 JOIN tmp on t1.f2=tmp.f2 +ORDER BY tmp.f1, f0 LIMIT 30 OFFSET 30; +f0 f1 f2 f1 f2 +3 2 2 2 2 +3 2 2 2 2 +3 2 2 2 2 +103 2 2 2 2 +103 2 2 2 2 +103 2 2 2 2 +203 2 2 2 2 +203 2 2 2 2 +203 2 2 2 2 +303 2 2 2 2 +303 2 2 2 2 +303 2 2 2 2 +403 2 2 2 2 +403 2 2 2 2 +403 2 2 2 2 +4 3 3 3 3 +4 3 3 3 3 +4 3 3 3 3 +104 3 3 3 3 +104 3 3 3 3 +104 3 3 3 3 +204 3 3 3 3 +204 3 3 3 3 +204 3 3 3 3 +304 3 3 3 3 +304 3 3 3 3 +304 3 3 3 3 +404 3 3 3 3 +404 3 3 3 3 +404 3 3 3 3 +SELECT FOUND_ROWS(); +FOUND_ROWS() +1500 +SELECT SQL_CALC_FOUND_ROWS * FROM t1 JOIN tmp on t1.f2=tmp.f2 +WHERE t1.f2>20 +ORDER BY tmp.f1, f0 LIMIT 30 OFFSET 30; +f0 f1 f2 f1 f2 +24 23 23 23 23 +24 23 23 23 23 +24 23 23 23 23 +124 23 23 23 23 +124 23 23 23 23 +124 23 23 23 23 +224 23 23 23 23 +224 23 23 23 23 +224 23 23 23 23 +324 23 23 23 23 +324 23 23 23 23 +324 23 23 23 23 +424 23 23 23 23 +424 23 23 23 23 +424 23 23 23 23 +25 24 24 24 24 +25 24 24 24 24 +25 24 24 24 24 +125 24 24 24 24 +125 24 24 24 24 +125 24 24 24 24 +225 24 24 24 24 +225 24 24 24 24 +225 24 24 24 24 +325 24 24 24 24 +325 24 24 24 24 +325 24 24 24 24 +425 24 24 24 24 +425 24 24 24 24 +425 24 24 24 24 +SELECT FOUND_ROWS(); +FOUND_ROWS() +1185 +CREATE VIEW v1 as SELECT * FROM t1 ORDER BY f1, f0 LIMIT 30; +SELECT * FROM v1; +f0 f1 f2 +1 0 0 +101 0 0 +201 0 0 +301 0 0 +401 0 0 +2 1 1 +102 1 1 +202 1 1 +302 1 1 +402 1 1 +3 2 2 +103 2 2 +203 2 2 +303 2 2 +403 2 2 +4 3 3 +104 3 3 +204 3 3 +304 3 3 +404 3 3 +5 4 4 +105 4 4 +205 4 4 +305 4 4 +405 4 4 +6 5 5 +106 5 5 +206 5 5 +306 5 5 +406 5 5 +drop view v1; +CREATE VIEW v1 as SELECT * FROM t1 ORDER BY f1, f0 LIMIT 100; +SELECT * FROM v1 ORDER BY f2, f0 LIMIT 30; +f0 f1 f2 +1 0 0 +101 0 0 +201 0 0 +301 0 0 +401 0 0 +2 1 1 +102 1 1 +202 1 1 +302 1 1 +402 1 1 +11 10 10 +111 10 10 +211 10 10 +311 10 10 +411 10 10 +12 11 11 +112 11 11 +212 11 11 +312 11 11 +412 11 11 +13 12 12 +113 12 12 +213 12 12 +313 12 12 +413 12 12 +14 13 13 +114 13 13 +214 13 13 +314 13 13 +414 13 13 +CREATE VIEW v2 as SELECT * FROM t1 ORDER BY f2, f0 LIMIT 100; +SELECT * FROM v1 JOIN v2 on v1.f1=v2.f1 ORDER BY v1.f2,v1.f0,v2.f0 +LIMIT 30; +f0 f1 f2 f0 f1 f2 +1 0 0 1 0 0 +1 0 0 101 0 0 +1 0 0 201 0 0 +1 0 0 301 0 0 +1 0 0 401 0 0 +101 0 0 1 0 0 +101 0 0 101 0 0 +101 0 0 201 0 0 +101 0 0 301 0 0 +101 0 0 401 0 0 +201 0 0 1 0 0 +201 0 0 101 0 0 +201 0 0 201 0 0 +201 0 0 301 0 0 +201 0 0 401 0 0 +301 0 0 1 0 0 +301 0 0 101 0 0 +301 0 0 201 0 0 +301 0 0 301 0 0 +301 0 0 401 0 0 +401 0 0 1 0 0 +401 0 0 101 0 0 +401 0 0 201 0 0 +401 0 0 301 0 0 +401 0 0 401 0 0 +2 1 1 2 1 1 +2 1 1 102 1 1 +2 1 1 202 1 1 +2 1 1 302 1 1 +2 1 1 402 1 1 +SELECT floor(f1/10) f3, count(f2) FROM t1 +GROUP BY 1 ORDER BY 2,1 LIMIT 5; +f3 count(f2) +0 50 +1 50 +2 50 +3 50 +4 50 +SELECT floor(f1/10) f3, count(f2) FROM t1 +GROUP BY 1 ORDER BY 2,1 LIMIT 0; +f3 count(f2) +CREATE PROCEDURE wl1393_sp_test() +BEGIN +SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 30; +SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 15 OFFSET 15; +SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE f1>10 +ORDER BY f2, f0 LIMIT 15 OFFSET 15; +SELECT FOUND_ROWS(); +SELECT * FROM v1 ORDER BY f2, f0 LIMIT 30; +END| +CALL wl1393_sp_test()| +f0 f1 f2 +12 11 11 +112 11 11 +212 11 11 +312 11 11 +412 11 11 +13 12 12 +113 12 12 +213 12 12 +313 12 12 +413 12 12 +14 13 13 +114 13 13 +214 13 13 +314 13 13 +414 13 13 +15 14 14 +115 14 14 +215 14 14 +315 14 14 +415 14 14 +16 15 15 +116 15 15 +216 15 15 +316 15 15 +416 15 15 +17 16 16 +117 16 16 +217 16 16 +317 16 16 +417 16 16 +f0 f1 f2 +15 14 14 +115 14 14 +215 14 14 +315 14 14 +415 14 14 +16 15 15 +116 15 15 +216 15 15 +316 15 15 +416 15 15 +17 16 16 +117 16 16 +217 16 16 +317 16 16 +417 16 16 +f0 f1 f2 +15 14 14 +115 14 14 +215 14 14 +315 14 14 +415 14 14 +16 15 15 +116 15 15 +216 15 15 +316 15 15 +416 15 15 +17 16 16 +117 16 16 +217 16 16 +317 16 16 +417 16 16 +FOUND_ROWS() +445 +f0 f1 f2 +1 0 0 +101 0 0 +201 0 0 +301 0 0 +401 0 0 +2 1 1 +102 1 1 +202 1 1 +302 1 1 +402 1 1 +11 10 10 +111 10 10 +211 10 10 +311 10 10 +411 10 10 +12 11 11 +112 11 11 +212 11 11 +312 11 11 +412 11 11 +13 12 12 +113 12 12 +213 12 12 +313 12 12 +413 12 12 +14 13 13 +114 13 13 +214 13 13 +314 13 13 +414 13 13 +DROP PROCEDURE wl1393_sp_test| +SELECT d1.f1, d1.f2 FROM t1 +LEFT JOIN (SELECT * FROM t1 ORDER BY f1 LIMIT 30) d1 on t1.f1=d1.f1 +ORDER BY d1.f2 DESC LIMIT 30; +f1 f2 +5 5 +5 5 +5 5 +5 5 +5 5 +5 5 +5 5 +5 5 +5 5 +5 5 +5 5 +5 5 +5 5 +5 5 +5 5 +5 5 +5 5 +5 5 +5 5 +5 5 +5 5 +5 5 +5 5 +5 5 +5 5 +4 4 +4 4 +4 4 +4 4 +4 4 +SELECT * FROM t1 WHERE f1 = (SELECT f1 FROM t1 ORDER BY 1 LIMIT 1); +f0 f1 f2 +1 0 0 +101 0 0 +201 0 0 +301 0 0 +401 0 0 +SELECT * FROM t1 WHERE f1 = (SELECT f1 FROM t1 ORDER BY 1 LIMIT 2); +ERROR 21000: Subquery returns more than 1 row +DROP TABLE t1, tmp; +DROP VIEW v1, v2; +# end of WL#1393 - Optimizing filesort with small limit +# +# Bug #58761 +# Crash in Field::is_null in field.h on subquery in WHERE clause +# +CREATE TABLE t1 ( +pk INT NOT NULL AUTO_INCREMENT, +col_int_key INT DEFAULT NULL, +col_varchar_key VARCHAR(1) DEFAULT NULL, +PRIMARY KEY (pk), +KEY col_varchar_key (col_varchar_key,col_int_key) +); +INSERT INTO t1 VALUES (27,7,'x'); +INSERT INTO t1 VALUES (28,6,'m'); +INSERT INTO t1 VALUES (29,4,'c'); +CREATE TABLE where_subselect +SELECT DISTINCT `pk` AS field1 , `pk` AS field2 +FROM t1 AS alias1 +WHERE alias1 . `col_int_key` > 229 +OR alias1 . `col_varchar_key` IS NOT NULL +GROUP BY field1, field2 +; +SELECT * +FROM where_subselect +WHERE (field1, field2) IN ( +SELECT DISTINCT `pk` AS field1 , `pk` AS field2 +FROM t1 AS alias1 +WHERE alias1 . `col_int_key` > 229 +OR alias1 . `col_varchar_key` IS NOT NULL +GROUP BY field1, field2 +); +field1 field2 +27 27 +28 28 +29 29 +DROP TABLE t1; +DROP TABLE where_subselect; +# End of Bug #58761 CREATE TABLE t1 ( id1 INT NULL, id2 INT NOT NULL, diff --git a/mysql-test/r/order_by_sortkey.result b/mysql-test/r/order_by_sortkey.result new file mode 100644 index 00000000000..717780f0af2 --- /dev/null +++ b/mysql-test/r/order_by_sortkey.result @@ -0,0 +1,159 @@ +CREATE TABLE t1( +f0 int auto_increment PRIMARY KEY, +f1 int, +f2 varchar(200) +); +INSERT INTO t1(f1, f2) VALUES +(0,"0"),(1,"1"),(2,"2"),(3,"3"),(4,"4"),(5,"5"), +(6,"6"),(7,"7"),(8,"8"),(9,"9"),(10,"10"), +(11,"11"),(12,"12"),(13,"13"),(14,"14"),(15,"15"), +(16,"16"),(17,"17"),(18,"18"),(19,"19"),(20,"20"), +(21,"21"),(22,"22"),(23,"23"),(24,"24"),(25,"25"), +(26,"26"),(27,"27"),(28,"28"),(29,"29"),(30,"30"), +(31,"31"),(32,"32"),(33,"33"),(34,"34"),(35,"35"), +(36,"36"),(37,"37"),(38,"38"),(39,"39"),(40,"40"), +(41,"41"),(42,"42"),(43,"43"),(44,"44"),(45,"45"), +(46,"46"),(47,"47"),(48,"48"),(49,"49"),(50,"50"), +(51,"51"),(52,"52"),(53,"53"),(54,"54"),(55,"55"), +(56,"56"),(57,"57"),(58,"58"),(59,"59"),(60,"60"), +(61,"61"),(62,"62"),(63,"63"),(64,"64"),(65,"65"), +(66,"66"),(67,"67"),(68,"68"),(69,"69"),(70,"70"), +(71,"71"),(72,"72"),(73,"73"),(74,"74"),(75,"75"), +(76,"76"),(77,"77"),(78,"78"),(79,"79"),(80,"80"), +(81,"81"),(82,"82"),(83,"83"),(84,"84"),(85,"85"), +(86,"86"),(87,"87"),(88,"88"),(89,"89"),(90,"90"), +(91,"91"),(92,"92"),(93,"93"),(94,"94"),(95,"95"), +(96,"96"),(97,"97"),(98,"98"),(99,"99"); +CREATE TEMPORARY TABLE tmp (f1 int, f2 varchar(20)); +INSERT INTO tmp SELECT f1,f2 FROM t1; +INSERT INTO t1(f1,f2) SELECT * FROM tmp; +INSERT INTO tmp SELECT f1,f2 FROM t1; +INSERT INTO t1(f1,f2) SELECT * FROM tmp; +INSERT INTO t1(f1,f2) SELECT * FROM tmp; +INSERT INTO tmp SELECT f1,f2 FROM t1; +INSERT INTO t1(f1,f2) SELECT * FROM tmp; +INSERT INTO tmp SELECT f1,f2 FROM t1; +INSERT INTO t1(f1,f2) SELECT * FROM tmp; +INSERT INTO tmp SELECT f1,f2 FROM t1; +INSERT INTO t1(f1,f2) SELECT * FROM tmp; +INSERT INTO tmp SELECT f1,f2 FROM t1; +INSERT INTO t1(f1,f2) SELECT * FROM tmp; +INSERT INTO tmp SELECT f1,f2 FROM t1; +INSERT INTO t1(f1,f2) SELECT * FROM tmp; +set sort_buffer_size= 32768; +FLUSH STATUS; +SHOW SESSION STATUS LIKE 'Sort%'; +Variable_name Value +Sort_merge_passes 0 +Sort_range 0 +Sort_rows 0 +Sort_scan 0 +SELECT * FROM t1 ORDER BY f2 LIMIT 100; +f0 f1 f2 +1 0 0 +101 0 0 +201 0 0 +301 0 0 +401 0 0 +501 0 0 +601 0 0 +701 0 0 +801 0 0 +901 0 0 +1001 0 0 +1101 0 0 +1201 0 0 +1301 0 0 +1401 0 0 +1501 0 0 +1601 0 0 +1701 0 0 +1801 0 0 +1901 0 0 +2001 0 0 +2101 0 0 +2201 0 0 +2301 0 0 +2401 0 0 +2501 0 0 +2601 0 0 +2701 0 0 +2801 0 0 +2901 0 0 +3001 0 0 +3101 0 0 +3201 0 0 +3301 0 0 +3401 0 0 +3501 0 0 +3601 0 0 +3701 0 0 +3801 0 0 +3901 0 0 +4001 0 0 +4101 0 0 +4201 0 0 +4301 0 0 +4401 0 0 +4501 0 0 +4601 0 0 +4701 0 0 +4801 0 0 +4901 0 0 +5001 0 0 +5101 0 0 +5201 0 0 +5301 0 0 +5401 0 0 +5501 0 0 +5601 0 0 +5701 0 0 +5801 0 0 +5901 0 0 +6001 0 0 +6101 0 0 +6201 0 0 +6301 0 0 +6401 0 0 +6501 0 0 +6601 0 0 +6701 0 0 +6801 0 0 +6901 0 0 +7001 0 0 +7101 0 0 +7201 0 0 +7301 0 0 +7401 0 0 +7501 0 0 +7601 0 0 +7701 0 0 +7801 0 0 +7901 0 0 +8001 0 0 +8101 0 0 +8201 0 0 +8301 0 0 +8401 0 0 +8501 0 0 +8601 0 0 +8701 0 0 +8801 0 0 +8901 0 0 +9001 0 0 +9101 0 0 +9201 0 0 +9301 0 0 +9401 0 0 +9501 0 0 +9601 0 0 +9701 0 0 +9801 0 0 +9901 0 0 +SHOW SESSION STATUS LIKE 'Sort%'; +Variable_name Value +Sort_merge_passes 0 +Sort_range 0 +Sort_rows 100 +Sort_scan 1 +DROP TABLE t1, tmp; diff --git a/mysql-test/r/parser.result b/mysql-test/r/parser.result index 10700d0ba73..54378f16d49 100644 --- a/mysql-test/r/parser.result +++ b/mysql-test/r/parser.result @@ -441,7 +441,7 @@ select master_pos_wait(); ERROR 42000: Incorrect parameter count in the call to native function 'master_pos_wait' select master_pos_wait(1); ERROR 42000: Incorrect parameter count in the call to native function 'master_pos_wait' -select master_pos_wait(1, 2, 3, 4); +select master_pos_wait(1, 2, 3, 4, 5); ERROR 42000: Incorrect parameter count in the call to native function 'master_pos_wait' select rand(1, 2, 3); ERROR 42000: Incorrect parameter count in the call to native function 'rand' 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_datatype.result b/mysql-test/r/partition_datatype.result index e0658f71612..8d72d8781ee 100644 --- a/mysql-test/r/partition_datatype.result +++ b/mysql-test/r/partition_datatype.result @@ -315,11 +315,11 @@ drop table t1; create table t1 (a varchar(3070)) partition by key (a); ERROR HY000: The total length of the partitioning fields is too large create table t1 (a varchar(65533)) partition by key (a); -ERROR 42000: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs +ERROR 42000: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs create table t1 (a varchar(65534) not null) partition by key (a); -ERROR 42000: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs +ERROR 42000: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs create table t1 (a varchar(65535)) partition by key (a); -ERROR 42000: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs +ERROR 42000: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs create table t1 (a bit(27), primary key (a)) engine=myisam partition by hash (a) (partition p0, partition p1, partition p2); diff --git a/mysql-test/r/partition_innodb.result b/mysql-test/r/partition_innodb.result index dd403f64b5c..3215a60387a 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 diff --git a/mysql-test/r/partition_innodb_plugin.result b/mysql-test/r/partition_innodb_plugin.result index 21f18f715d0..fa2ef5c6c18 100644 --- a/mysql-test/r/partition_innodb_plugin.result +++ b/mysql-test/r/partition_innodb_plugin.result @@ -67,7 +67,7 @@ LOCK TABLE t1 WRITE; # ALTER fails because COMPRESSED/KEY_BLOCK_SIZE # are incompatible with innodb_file_per_table = OFF; ALTER TABLE t1 ADD PARTITION PARTITIONS 1; -ERROR HY000: Got error 1478 from storage engine +ERROR HY000: Got error 140 "Wrong create options" from storage engine t1#P#p0.ibd t1.frm t1.par @@ -76,18 +76,18 @@ t1.par SET innodb_strict_mode = OFF; ALTER TABLE t1 ADD PARTITION PARTITIONS 2; Warnings: -Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table. -Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=4. -Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table. -Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=4. -Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table. -Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=4. +Warning 140 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table. +Warning 140 InnoDB: ignoring KEY_BLOCK_SIZE=4. +Warning 140 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table. +Warning 140 InnoDB: ignoring KEY_BLOCK_SIZE=4. +Warning 140 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table. +Warning 140 InnoDB: ignoring KEY_BLOCK_SIZE=4. t1.frm t1.par ALTER TABLE t1 REBUILD PARTITION p0; Warnings: -Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table. -Warning 1478 InnoDB: ignoring KEY_BLOCK_SIZE=4. +Warning 140 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table. +Warning 140 InnoDB: ignoring KEY_BLOCK_SIZE=4. UNLOCK TABLES; SHOW CREATE TABLE t1; Table Create Table diff --git a/mysql-test/r/partition_open_files_limit.result b/mysql-test/r/partition_open_files_limit.result index 1441ba4e78e..fed32a69c44 100644 --- a/mysql-test/r/partition_open_files_limit.result +++ b/mysql-test/r/partition_open_files_limit.result @@ -5,7 +5,7 @@ ENGINE=MyISAM PARTITION BY KEY () PARTITIONS 1; INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11); # if the bug exists, then crash will happen here ALTER TABLE t1 ADD PARTITION PARTITIONS 511; -ERROR HY000: Out of resources when opening file '<partition file>' (Errcode: 24) +ERROR HY000: Out of resources when opening file '<partition file>' (Errcode: 24 "Too many open files") SELECT * FROM t1; a 1 diff --git a/mysql-test/r/ps_1general.result b/mysql-test/r/ps_1general.result index 0a3d16cf48e..3ca0cf8554f 100644 --- a/mysql-test/r/ps_1general.result +++ b/mysql-test/r/ps_1general.result @@ -533,7 +533,7 @@ drop table t2; prepare stmt1 from ' rename table t5 to t6, t7 to t8 ' ; create table t5 (a int) ; execute stmt1 ; -ERROR HY000: Can't find file: './test/t7' (errno: 2) +ERROR HY000: Can't find file: './test/t7' (errno: 2 "No such file or directory") create table t7 (a int) ; execute stmt1 ; execute stmt1 ; 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/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/row-checksum-old.result b/mysql-test/r/row-checksum-old.result index ef523463860..87f0bb8af2d 100644 --- a/mysql-test/r/row-checksum-old.result +++ b/mysql-test/r/row-checksum-old.result @@ -73,7 +73,7 @@ test.t1 4108368782 drop table if exists t1; create table t1 (a int null, v varchar(100)) engine=innodb checksum=0 row_format=fixed; Warnings: -Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT. +Warning 140 InnoDB: assuming ROW_FORMAT=COMPACT. insert into t1 values(null, null), (1, "hello"); checksum table t1; Table Checksum diff --git a/mysql-test/r/row-checksum.result b/mysql-test/r/row-checksum.result index fb8a1260a1d..9e58d6fa96e 100644 --- a/mysql-test/r/row-checksum.result +++ b/mysql-test/r/row-checksum.result @@ -73,7 +73,7 @@ test.t1 3885665021 drop table if exists t1; create table t1 (a int null, v varchar(100)) engine=innodb checksum=0 row_format=fixed; Warnings: -Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT. +Warning 140 InnoDB: assuming ROW_FORMAT=COMPACT. insert into t1 values(null, null), (1, "hello"); checksum table t1; Table Checksum diff --git a/mysql-test/r/show_explain.result b/mysql-test/r/show_explain.result new file mode 100644 index 00000000000..bf96ad88f23 --- /dev/null +++ b/mysql-test/r/show_explain.result @@ -0,0 +1,1078 @@ +drop table if exists t0, t1, t2, t3, t4; +drop view if exists v1; +create table t0 (a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1 (a int); +insert into t1 select A.a + 10*B.a + 100*C.a from t0 A, t0 B, t0 C; +alter table t1 add b int, add c int, add filler char(32); +update t1 set b=a, c=a, filler='fooo'; +alter table t1 add key(a), add key(b); +show explain for 2000000000; +ERROR HY000: Unknown thread id: 2000000000 +show explain for (select max(a) from t0); +ERROR HY000: You may only use constant expressions in this statement +show explain for $thr2; +ERROR HY000: Target is not running an EXPLAINable command +show explain for $thr1; +ERROR HY000: Target is not running an EXPLAINable command +set @show_explain_probe_select_id=1; +set debug_dbug='d,show_explain_probe_join_exec_start'; +select count(*) from t1 where a < 100000; +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index a a 5 NULL 1000 Using where; Using index +Warnings: +Note 1003 select count(*) from t1 where a < 100000 +count(*) +1000 +select max(c) from t1 where a < 10; +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a a 5 NULL 10 Using index condition +Warnings: +Note 1003 select max(c) from t1 where a < 10 +max(c) +9 +# We can catch EXPLAIN, too. +set @show_expl_tmp= @@optimizer_switch; +set optimizer_switch='index_condition_pushdown=on,mrr=on,mrr_sort_keys=on'; +explain select max(c) from t1 where a < 10; +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a a 5 NULL 10 Using index condition; Rowid-ordered scan +Warnings: +Note 1003 explain select max(c) from t1 where a < 10 +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a a 5 NULL 10 Using index condition; Rowid-ordered scan +set optimizer_switch= @show_expl_tmp; +# UNION, first branch +set @show_explain_probe_select_id=1; +set debug_dbug='d,show_explain_probe_join_exec_start'; +explain select a from t0 A union select a+1 from t0 B; +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY A ALL NULL NULL NULL NULL 10 +2 UNION B ALL NULL NULL NULL NULL 10 +NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL +Warnings: +Note 1003 explain select a from t0 A union select a+1 from t0 B +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY A ALL NULL NULL NULL NULL 10 +2 UNION B ALL NULL NULL NULL NULL 10 +NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL +# UNION, second branch +set @show_explain_probe_select_id=1; +set debug_dbug='d,show_explain_probe_join_exec_start'; +explain select a from t0 A union select a+1 from t0 B; +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY A ALL NULL NULL NULL NULL 10 +2 UNION B ALL NULL NULL NULL NULL 10 +NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL +Warnings: +Note 1003 explain select a from t0 A union select a+1 from t0 B +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY A ALL NULL NULL NULL NULL 10 +2 UNION B ALL NULL NULL NULL NULL 10 +NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL +# Uncorrelated subquery, select +set @show_explain_probe_select_id=1; +set debug_dbug='d,show_explain_probe_join_exec_start'; +select a, (select max(a) from t0 B) from t0 A where a<1; +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY A ALL NULL NULL NULL NULL 10 Using where +2 SUBQUERY B ALL NULL NULL NULL NULL 10 +Warnings: +Note 1003 select a, (select max(a) from t0 B) from t0 A where a<1 +a (select max(a) from t0 B) +0 9 +# Uncorrelated subquery, explain +set @show_explain_probe_select_id=1; +set debug_dbug='d,show_explain_probe_join_exec_start'; +explain select a, (select max(a) from t0 B) from t0 A where a<1; +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY A ALL NULL NULL NULL NULL 10 Using where +2 SUBQUERY B ALL NULL NULL NULL NULL 10 +Warnings: +Note 1003 explain select a, (select max(a) from t0 B) from t0 A where a<1 +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY A ALL NULL NULL NULL NULL 10 Using where +2 SUBQUERY B ALL NULL NULL NULL NULL 10 +# correlated subquery, select +set @show_explain_probe_select_id=1; +set debug_dbug='d,show_explain_probe_join_exec_start'; +select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1; +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY a ALL NULL NULL NULL NULL 10 Using where +2 DEPENDENT SUBQUERY b ALL NULL NULL NULL NULL 10 Using where +Warnings: +Note 1003 select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1 +a (select max(a) from t0 b where b.a+a.a<10) +0 9 +# correlated subquery, explain +set @show_explain_probe_select_id=1; +set debug_dbug='d,show_explain_probe_join_exec_start'; +select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1; +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY a ALL NULL NULL NULL NULL 10 Using where +2 DEPENDENT SUBQUERY b ALL NULL NULL NULL NULL 10 Using where +Warnings: +Note 1003 select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1 +a (select max(a) from t0 b where b.a+a.a<10) +0 9 +# correlated subquery, select, while inside the subquery +set @show_explain_probe_select_id=2; +set debug_dbug='d,show_explain_probe_join_exec_start'; +select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1; +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY a ALL NULL NULL NULL NULL 10 Using where +2 DEPENDENT SUBQUERY b ALL NULL NULL NULL NULL 10 Using where +Warnings: +Note 1003 select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1 +a (select max(a) from t0 b where b.a+a.a<10) +0 9 +# correlated subquery, explain, while inside the subquery +set @show_explain_probe_select_id=2; +set debug_dbug='d,show_explain_probe_join_exec_start'; +select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1; +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY a ALL NULL NULL NULL NULL 10 Using where +2 DEPENDENT SUBQUERY b ALL NULL NULL NULL NULL 10 Using where +Warnings: +Note 1003 select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1 +a (select max(a) from t0 b where b.a+a.a<10) +0 9 +# correlated subquery, explain, while inside the subquery +set @show_explain_probe_select_id=1; +set debug_dbug='d,show_explain_probe_join_exec_end'; +select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<1; +show explain for $thr2; +ERROR HY000: Target is not running an EXPLAINable command +a (select max(a) from t0 b where b.a+a.a<10) +0 9 +# Try to do SHOW EXPLAIN for a query that runs a SET command: +# I've found experimentally that select_id==2 here... +# +set @show_explain_probe_select_id=2; +set debug_dbug='d,show_explain_probe_join_exec_start'; +set @foo= (select max(a) from t0 where sin(a) >0); +show explain for $thr2; +ERROR HY000: Target is not running an EXPLAINable command +# +# Attempt SHOW EXPLAIN for an UPDATE +# +create table t2 as select a as a, a as dummy from t0 limit 2; +set @show_explain_probe_select_id=2; +set debug_dbug='d,show_explain_probe_join_exec_start'; +update t2 set dummy=0 where (select max(a) from t0 where t2.a + t0.a <3) >3 ; +show explain for $thr2; +ERROR HY000: Target is not running an EXPLAINable command +show explain for $thr2; +ERROR HY000: Target is not running an EXPLAINable command +drop table t2; +# +# Attempt SHOW EXPLAIN for a DELETE +# +create table t2 as select a as a, a as dummy from t0 limit 2; +set @show_explain_probe_select_id=2; +set debug_dbug='d,show_explain_probe_join_exec_start'; +delete from t2 where (select max(a) from t0 where t2.a + t0.a <3) >3 ; +show explain for $thr2; +ERROR HY000: Target is not running an EXPLAINable command +show explain for $thr2; +ERROR HY000: Target is not running an EXPLAINable command +drop table t2; +# +# Multiple SHOW EXPLAIN calls for one select +# +create table t2 as select a as a, a as dummy from t0 limit 3; +set @show_explain_probe_select_id=2; +set debug_dbug='d,show_explain_probe_join_exec_start'; +select t2.a, ((select max(a) from t0 where t2.a + t0.a <3) >3) as SUBQ from t2; +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 3 +2 DEPENDENT SUBQUERY t0 ALL NULL NULL NULL NULL 10 Using where +Warnings: +Note 1003 select t2.a, ((select max(a) from t0 where t2.a + t0.a <3) >3) as SUBQ from t2 +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 3 +2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Query plan already deleted +Warnings: +Note 1003 select t2.a, ((select max(a) from t0 where t2.a + t0.a <3) >3) as SUBQ from t2 +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 3 +2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Query plan already deleted +Warnings: +Note 1003 select t2.a, ((select max(a) from t0 where t2.a + t0.a <3) >3) as SUBQ from t2 +a SUBQ +0 0 +1 0 +2 0 +drop table t2; +# +# SHOW EXPLAIN for SELECT ... ORDER BY with "Using filesort" +# +explain select * from t0 order by a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t0 ALL NULL NULL NULL NULL 10 Using filesort +set debug_dbug='d,show_explain_probe_join_exec_start'; +set @show_explain_probe_select_id=1; +select * from t0 order by a; +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t0 ALL NULL NULL NULL NULL 10 Using filesort +Warnings: +Note 1003 select * from t0 order by a +a +0 +1 +2 +3 +4 +5 +6 +7 +8 +9 +# +# SHOW EXPLAIN for SELECT ... with "Using temporary" +# +explain select distinct a from t0; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t0 ALL NULL NULL NULL NULL 10 Using temporary +set debug_dbug='d,show_explain_probe_join_exec_start'; +set @show_explain_probe_select_id=1; +select distinct a from t0; +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t0 ALL NULL NULL NULL NULL 10 Using temporary +Warnings: +Note 1003 select distinct a from t0 +a +0 +1 +2 +3 +4 +5 +6 +7 +8 +9 +# +# SHOW EXPLAIN for SELECT ... with "Using temporary; Using filesort" +# +explain select distinct a from t0; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t0 ALL NULL NULL NULL NULL 10 Using temporary +set debug_dbug='d,show_explain_probe_join_exec_start'; +set @show_explain_probe_select_id=1; +select distinct a from t0; +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t0 ALL NULL NULL NULL NULL 10 Using temporary +Warnings: +Note 1003 select distinct a from t0 +a +0 +1 +2 +3 +4 +5 +6 +7 +8 +9 +set debug_dbug=''; +# +# MDEV-238: SHOW EXPLAIN: Server crashes in JOIN::print_explain with FROM subquery and GROUP BY +# +CREATE TABLE t2 ( a INT ); +INSERT INTO t2 VALUES (1),(2),(1),(4),(2); +explain SELECT alias.a FROM t2, ( SELECT * FROM t2 ) AS alias GROUP BY alias.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 Using temporary; Using filesort +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 Using join buffer (flat, BNL join) +set debug_dbug='d,show_explain_in_find_all_keys'; +SELECT alias.a FROM t2, ( SELECT * FROM t2 ) AS alias GROUP BY alias.a; +# FIXED by "conservative assumptions about when QEP is available" fix: +# NOTE: current code will not show "Using join buffer": +show explain for $thr2; +ERROR HY000: Target is not running an EXPLAINable command +a +1 +2 +4 +set debug_dbug=''; +DROP TABLE t2; +# +# MDEV-239: Assertion `field_types == 0 ... ' failed in Protocol_text::store(double, uint32, String*) with +# SHOW EXPLAIN over EXPLAIN EXTENDED +# +CREATE TABLE t2 (a INT); +INSERT INTO t2 VALUES (1),(2),(1),(4),(2); +EXPLAIN EXTENDED SELECT alias.a FROM t2, ( SELECT * FROM t2 ) AS alias GROUP BY alias.a ; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 Using temporary; Using filesort +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` join `test`.`t2` group by `test`.`t2`.`a` +set @show_explain_probe_select_id=1; +set debug_dbug='d,show_explain_probe_join_exec_end'; +EXPLAIN EXTENDED SELECT alias.a FROM t2, ( SELECT * FROM t2 ) AS alias GROUP BY alias.a ; +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 Using temporary; Using filesort +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 Using join buffer (flat, BNL join) +Warnings: +Note 1003 EXPLAIN EXTENDED SELECT alias.a FROM t2, ( SELECT * FROM t2 ) AS alias GROUP BY alias.a +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 Using temporary; Using filesort +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 100.00 Using join buffer (flat, BNL join) +Warnings: +Note 1003 select `test`.`t2`.`a` AS `a` from `test`.`t2` join `test`.`t2` group by `test`.`t2`.`a` +set debug_dbug=''; +DROP TABLE t2; +# +# MDEV-240: SHOW EXPLAIN: Assertion `this->optimized == 2' failed in +# JOIN::print_explain on query with a JOIN, TEMPTABLE view, +# +CREATE TABLE t3 (a INT); +CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t3; +INSERT INTO t3 VALUES (8); +CREATE TABLE t2 (b INT); +INSERT INTO t2 VALUES (4),(5),(6),(7),(8),(9); +explain SELECT * FROM v1, t2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> system NULL NULL NULL NULL 1 +1 PRIMARY t2 ALL NULL NULL NULL NULL 6 +2 DERIVED t3 system NULL NULL NULL NULL 1 +set @show_explain_probe_select_id=2; +set debug_dbug='d,show_explain_probe_join_exec_end'; +SELECT * FROM v1, t2; +show explain for $thr2; +ERROR HY000: Target is not running an EXPLAINable command +a b +8 4 +8 5 +8 6 +8 7 +8 8 +8 9 +set debug_dbug=''; +DROP VIEW v1; +DROP TABLE t2, t3; +# +# MDEV-267: SHOW EXPLAIN: Server crashes in JOIN::print_explain on most of queries +# +set @show_explain_probe_select_id=1; +set debug_dbug='d,show_explain_probe_join_exec_end'; +select sleep(1); +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 select sleep(1) +sleep(1) +0 +set debug_dbug=''; +# +# Same as above, but try another reason for JOIN to be degenerate +# +set @show_explain_probe_select_id=1; +set debug_dbug='d,show_explain_probe_join_exec_end'; +select * from t0 where 1>10; +show explain for $thr2; +ERROR HY000: Target is not running an EXPLAINable command +a +set debug_dbug=''; +# +# Same as above, but try another reason for JOIN to be degenerate (2) +# +create table t3(a int primary key); +insert into t3 select a from t0; +set @show_explain_probe_select_id=1; +set debug_dbug='d,show_explain_probe_join_exec_end'; +select * from t0,t3 where t3.a=112233; +show explain for $thr2; +ERROR HY000: Target is not running an EXPLAINable command +a a +set debug_dbug=''; +drop table t3; +# +# MDEV-270: SHOW EXPLAIN: server crashes in JOIN::print_explain on a query with +# select tables optimized away +# +CREATE TABLE t2 (pk INT PRIMARY KEY, a INT ) ENGINE=MyISAM; +INSERT INTO t2 VALUES +(1,4),(2,62),(3,7),(4,1),(5,0),(6,7),(7,7),(8,1),(9,7),(10,1), +(11,5),(12,2),(13,0),(14,1),(15,8),(16,1),(17,1),(18,9),(19,1),(20,5) ; +explain SELECT * FROM t2 WHERE a = +(SELECT MAX(a) FROM t2 +WHERE pk= (SELECT MAX(pk) FROM t2 WHERE pk = 3) +); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 20 Using where +2 SUBQUERY t2 const PRIMARY PRIMARY 4 const 1 Using where +3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +set @show_explain_probe_select_id=2; +set debug_dbug='d,show_explain_probe_do_select'; +SELECT * FROM t2 WHERE a = +(SELECT MAX(a) FROM t2 +WHERE pk= (SELECT MAX(pk) FROM t2 WHERE pk = 3) +); +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 20 Using where +2 SUBQUERY t2 const PRIMARY PRIMARY 4 const 1 Using where +3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away +Warnings: +Note 1003 SELECT * FROM t2 WHERE a = +(SELECT MAX(a) FROM t2 +WHERE pk= (SELECT MAX(pk) FROM t2 WHERE pk = 3) +) +pk a +3 7 +6 7 +7 7 +9 7 +set debug_dbug=''; +drop table t2; +# +# MDEV-273: SHOW EXPLAIN: server crashes in JOIN::print_explain on a query with impossible WHERE +# +CREATE TABLE t2 (a1 INT, KEY(a1)) ENGINE=MyISAM; +INSERT INTO t2 VALUES +(4),(6),(7),(1),(0),(7),(7),(1),(7),(1), +(5),(2),(0),(1),(8),(1),(1),(9),(1),(5); +CREATE TABLE t3 (b1 INT) ENGINE=MyISAM; +INSERT INTO t3 VALUES +(4),(5),(8),(4),(8),(2),(9),(6),(4),(8), +(3),(5),(9),(6),(8),(3),(2),(6),(3),(1), +(4),(3),(1),(7),(0),(0),(9),(5),(9),(0), +(2),(2),(5),(9),(1),(4),(8),(6),(5),(5), +(1),(7),(2),(8),(9),(3),(2),(6),(6),(5), +(4),(3),(2),(7),(4),(6),(0),(8),(5),(8), +(2),(9),(7),(5),(7),(0),(4),(3),(1),(0), +(6),(2),(8),(3),(7),(3),(5),(5),(1),(2), +(1),(7),(1),(9),(9),(8),(3); +CREATE TABLE t4 (c1 INT) ENGINE=MyISAM; +EXPLAIN +SELECT count(*) FROM t2, t3 +WHERE a1 < ALL ( +SELECT a1 FROM t2 +WHERE a1 IN ( SELECT a1 FROM t2, t4 ) +); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 index NULL a1 5 NULL 20 Using where; Using index +1 PRIMARY t3 ALL NULL NULL NULL NULL 87 Using join buffer (flat, BNL join) +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +set @show_explain_probe_select_id=1; +set debug_dbug='d,show_explain_probe_do_select'; +SELECT count(*) FROM t2, t3 +WHERE a1 < ALL ( +SELECT a1 FROM t2 +WHERE a1 IN ( SELECT a1 FROM t2, t4 ) +); +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 index NULL a1 5 NULL 20 Using where; Using index +1 PRIMARY t3 ALL NULL NULL NULL NULL 87 Using join buffer (flat, BNL join) +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table +Warnings: +Note 1003 SELECT count(*) FROM t2, t3 +WHERE a1 < ALL ( +SELECT a1 FROM t2 +WHERE a1 IN ( SELECT a1 FROM t2, t4 ) +) +count(*) +1740 +set debug_dbug=''; +drop table t2, t3, t4; +# +# MDEV-275: SHOW EXPLAIN: server crashes in JOIN::print_explain with IN subquery and aggregate function +# +CREATE TABLE t2 ( `pk` INT NOT NULL PRIMARY KEY, `a1` INT NOT NULL, KEY(`a1`)) ENGINE=MyISAM; +INSERT INTO t2 VALUES +(1,5),(2,4),(3,6),(4,9),(5,2),(6,8),(7,4),(8,8),(9,0),(10,43), +(11,23),(12,3),(13,45),(14,16),(15,2),(16,33),(17,2),(18,5),(19,9),(20,2); +set @show_explain_probe_select_id=1; +set debug_dbug='d,show_explain_probe_join_exec_end'; +SELECT * FROM t2 WHERE (5, 78) IN (SELECT `a1`, MAX(`a1`) FROM t2 GROUP BY `a1`); +show explain for $thr2; +ERROR HY000: Target is not running an EXPLAINable command +pk a1 +set debug_dbug=''; +DROP TABLE t2; +DROP TABLE t1; +# +# MDEV-305: SHOW EXPLAIN: ref returned by SHOW EXPLAIN is different from the normal EXPLAIN ('const' vs empty string) +# +CREATE TABLE t1(a INT, KEY(a)); +INSERT INTO t1 VALUES (3),(1),(5),(1); +set @show_explain_probe_select_id=1; +set debug_dbug='d,show_explain_probe_join_exec_start'; +SELECT 'test' FROM t1 WHERE a=1; +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref a a 5 const 1 Using index +Warnings: +Note 1003 SELECT 'test' FROM t1 WHERE a=1 +test +test +test +set debug_dbug=''; +DROP TABLE t1; +# +# MDEV-299: SHOW EXPLAIN: Plan produced by SHOW EXPLAIN changes back and forth during query execution +# +create table t1 (key1 int, col1 int, col2 int, filler char(100), key(key1)); +insert into t1 select A.a+ 10 * B.a, 10, 10, 'filler-data' from t0 A, t0 B; +update t1 set col1=3, col2=10 where key1=1; +update t1 set col1=3, col2=1000 where key1=2; +update t1 set col1=3, col2=10 where key1=3; +update t1 set col1=3, col2=1000 where key1=4; +set @tmp_mdev299_jcl= @@join_cache_level; +set join_cache_level=0; +explain select count(*) from t1 A, t1 B where B.key1 < A.col2 and A.col1=3 AND B.col2 + 1 < 100; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE A ALL NULL NULL NULL NULL 100 Using where +1 SIMPLE B ALL key1 NULL NULL NULL 100 Range checked for each record (index map: 0x1) +set @show_explain_probe_select_id=1; +set debug_dbug='d,show_explain_probe_test_if_quick_select'; +select count(*) from t1 A, t1 B where B.key1 < A.col2 and A.col1=3 AND B.col2 + 1 < 100; +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE A ALL NULL NULL NULL NULL 100 Using where +1 SIMPLE B ALL key1 NULL NULL NULL 100 Range checked for each record (index map: 0x1) +Warnings: +Note 1003 select count(*) from t1 A, t1 B where B.key1 < A.col2 and A.col1=3 AND B.col2 + 1 < 100 +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE A ALL NULL NULL NULL NULL 100 Using where +1 SIMPLE B ALL key1 NULL NULL NULL 100 Range checked for each record (index map: 0x1) +Warnings: +Note 1003 select count(*) from t1 A, t1 B where B.key1 < A.col2 and A.col1=3 AND B.col2 + 1 < 100 +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE A ALL NULL NULL NULL NULL 100 Using where +1 SIMPLE B ALL key1 NULL NULL NULL 100 Range checked for each record (index map: 0x1) +Warnings: +Note 1003 select count(*) from t1 A, t1 B where B.key1 < A.col2 and A.col1=3 AND B.col2 + 1 < 100 +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE A ALL NULL NULL NULL NULL 100 Using where +1 SIMPLE B ALL key1 NULL NULL NULL 100 Range checked for each record (index map: 0x1) +Warnings: +Note 1003 select count(*) from t1 A, t1 B where B.key1 < A.col2 and A.col1=3 AND B.col2 + 1 < 100 +count(*) +212 +set debug_dbug=''; +drop table t1; +# +# MDEV-297: SHOW EXPLAIN: Server gets stuck until timeout occurs while +# executing SHOW INDEX and SHOW EXPLAIN in parallel +# +CREATE TABLE t1(a INT, b INT, c INT, KEY(a), KEY(b), KEY(c)); +INSERT INTO t1 (a) VALUES (3),(1),(5),(1); +set @show_explain_probe_select_id=1; +set debug_dbug='d,show_explain_probe_join_exec_start'; +SHOW INDEX FROM t1; +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE STATISTICS ALL NULL NULL NULL NULL NULL Skip_open_table; Scanned all databases +Warnings: +Note 1003 SHOW INDEX FROM t1 +Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment +t1 1 a 1 a A NULL NULL NULL YES BTREE +t1 1 b 1 b A NULL NULL NULL YES BTREE +t1 1 c 1 c A NULL NULL NULL YES BTREE +set debug_dbug=''; +DROP TABLE t1; +# +# MDEV-324: SHOW EXPLAIN: Plan produced by SHOW EXPLAIN for a query with TEMPTABLE view +# loses 'DERIVED' line on the way without saying that the plan was already deleted +# +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2); +CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1; +EXPLAIN SELECT a + 1 FROM v1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 +2 DERIVED t1 ALL NULL NULL NULL NULL 2 +set debug_dbug='d,show_explain_probe_join_tab_preread'; +set @show_explain_probe_select_id=1; +SELECT a + 1 FROM v1; +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 +2 DERIVED NULL NULL NULL NULL NULL NULL NULL Query plan already deleted +Warnings: +Note 1003 SELECT a + 1 FROM v1 +a + 1 +2 +3 +set debug_dbug=''; +DROP VIEW v1; +DROP TABLE t1; +# +# MDEV-323: SHOW EXPLAIN: Plan produced by SHOW EXPLAIN loses +# 'UNION RESULT' line on the way without saying that the plan was already deleted +# +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (4),(6); +EXPLAIN +SELECT a FROM t1 WHERE a IN ( SELECT 1+SLEEP(0.01) UNION SELECT 2 ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where +2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used +3 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL +set debug_dbug='d,show_explain_probe_union_read'; +SELECT a FROM t1 WHERE a IN ( SELECT 1+SLEEP(0.01) UNION SELECT 2 ); +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where +2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used +3 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL +Warnings: +Note 1003 SELECT a FROM t1 WHERE a IN ( SELECT 1+SLEEP(0.01) UNION SELECT 2 ) +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where +2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used +3 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL +Warnings: +Note 1003 SELECT a FROM t1 WHERE a IN ( SELECT 1+SLEEP(0.01) UNION SELECT 2 ) +a +set debug_dbug=''; +DROP TABLE t1; +# +# MDEV-327: SHOW EXPLAIN: Different select_type in plans produced by SHOW EXPLAIN +# and standard EXPLAIN: 'SUBQUERY' vs 'DEPENDENT SUBQUERY' +# +CREATE TABLE t1 (a INT) ENGINE=Aria; +INSERT INTO t1 VALUES +(4),(6),(3),(5),(3),(246),(2),(9),(3),(8), +(1),(8),(8),(5),(7),(5),(1),(6),(2),(9); +CREATE TABLE t2 (b INT) ENGINE=Aria; +INSERT INTO t2 VALUES +(1),(7),(4),(7),(0),(2),(9),(4),(0),(9), +(1),(3),(8),(8),(18),(84),(6),(3),(6),(6); +EXPLAIN +SELECT * FROM t1, ( SELECT * FROM t2 ) AS alias +WHERE a < ALL ( SELECT b FROM t1, t2 WHERE a = b ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 20 Using where +1 PRIMARY t2 ALL NULL NULL NULL NULL 20 +3 SUBQUERY t1 ALL NULL NULL NULL NULL 20 +3 SUBQUERY t2 ALL NULL NULL NULL NULL 20 Using where +set @show_explain_probe_select_id=1; +set debug_dbug='d,show_explain_probe_join_exec_start'; +SELECT * FROM t1, ( SELECT * FROM t2 ) AS alias +WHERE a < ALL ( SELECT b FROM t1, t2 WHERE a = b ); +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 20 Using where +1 PRIMARY t2 ALL NULL NULL NULL NULL 20 +3 SUBQUERY t1 ALL NULL NULL NULL NULL 20 +3 SUBQUERY t2 ALL NULL NULL NULL NULL 20 Using where +Warnings: +Note 1003 SELECT * FROM t1, ( SELECT * FROM t2 ) AS alias +WHERE a < ALL ( SELECT b FROM t1, t2 WHERE a = b ) +a b +set debug_dbug=''; +DROP TABLE t1, t2; +# +# Test that SHOW EXPLAIN will print 'Distinct'. +# +CREATE TABLE t1 (a int(10) unsigned not null primary key,b int(10) unsigned); +INSERT INTO t1 VALUES (1,1),(2,1),(3,1),(4,1); +CREATE TABLE t3 (a int(10) unsigned, key(A), b text); +INSERT INTO t3 VALUES (1,'1'),(2,'2'); +create temporary table t4 select * from t3; +insert into t3 select * from t4; +insert into t4 select * from t3; +insert into t3 select * from t4; +insert into t4 select * from t3; +insert into t3 select * from t4; +insert into t4 select * from t3; +insert into t3 select * from t4; +explain select distinct t1.a from t1,t3 where t1.a=t3.a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 4 Using index; Using temporary +1 SIMPLE t3 ref a a 5 test.t1.a 7 Using index; Distinct +set @show_explain_probe_select_id=1; +set debug_dbug='d,show_explain_probe_join_exec_start'; +select distinct t1.a from t1,t3 where t1.a=t3.a; +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 4 Using index; Using temporary +1 SIMPLE t3 ref a a 5 test.t1.a 7 Using index; Distinct +Warnings: +Note 1003 select distinct t1.a from t1,t3 where t1.a=t3.a +a +1 +2 +set debug_dbug=''; +drop table t1,t3,t4; +# +# ---------- SHOW EXPLAIN and permissions ----------------- +# +grant ALL on test.* to test2@localhost; +grant super on *.* to test2@localhost; +# +# First, make sure that user 'test2' cannot do SHOW EXPLAIN on us +# +set @show_explain_probe_select_id=1; +set debug_dbug='d,show_explain_probe_join_exec_start'; +select * from t0 where a < 3; +show explain for $thr2; +ERROR 42000: Access denied; you need (at least one of) the PROCESS privilege(s) for this operation +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t0 ALL NULL NULL NULL NULL 10 Using where +Warnings: +Note 1003 select * from t0 where a < 3 +a +0 +1 +2 +set debug_dbug=''; +# +# Check that user test2 can do SHOW EXPLAIN on its own queries +# +set @show_explain_probe_select_id=1; +set debug_dbug='d,show_explain_probe_join_exec_start'; +select * from t0 where a < 3; +show explain for $thr_con2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t0 ALL NULL NULL NULL NULL 10 Using where +Warnings: +Note 1003 select * from t0 where a < 3 +a +0 +1 +2 +# +# Now, grant test2 a PROCESSLIST permission, and see that he's able to observe us +# +grant process on *.* to test2@localhost; +set @show_explain_probe_select_id=1; +set debug_dbug='d,show_explain_probe_join_exec_start'; +select * from t0 where a < 3; +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t0 ALL NULL NULL NULL NULL 10 Using where +Warnings: +Note 1003 select * from t0 where a < 3 +a +0 +1 +2 +set debug_dbug=''; +revoke all privileges on test.* from test2@localhost; +drop user test2@localhost; +# +# Test that it is possible to KILL a SHOW EXPLAIN command that's waiting +# on its target thread +# +create table t1 (pk int primary key, data char(64)) engine=innodb; +insert into t1 select A.a + 10 * B.a + 100 * C.a, 'data1' from t0 A, t0 B, t0 C; +# Lock two threads +set autocommit=0; +select * from t1 where pk between 10 and 20 for update; +pk data +10 data1 +11 data1 +12 data1 +13 data1 +14 data1 +15 data1 +16 data1 +17 data1 +18 data1 +19 data1 +20 data1 +set autocommit=0; +select * from t1 where pk between 10 and 20 for update; +# do: send_eval show explain for thr2; +kill query $thr_default; +ERROR 70100: Query execution was interrupted +rollback; +pk data +10 data1 +11 data1 +12 data1 +13 data1 +14 data1 +15 data1 +16 data1 +17 data1 +18 data1 +19 data1 +20 data1 +drop table t1; +# +# Check that the I_S table is invisible +# +select table_name from information_schema.tables where table_schema='information_schema' and table_name like '%explain%'; +table_name +# +# MDEV-325: SHOW EXPLAIN: Plan produced by SHOW EXPLAIN is different from standard EXPLAIN: type ALL vs 'index_merge'.. +# +CREATE TABLE t1 (a INT, b INT, KEY(a), KEY(b)) ENGINE=MyISAM; +INSERT INTO t1 VALUES +(8,0),(128,5050),(5372,8),(234,7596),(2,0),(2907,8930),(1,0), +(0,5224),(8,7638),(960,5),(9872,1534),(0,2295),(3408,9809), +(7,0),(1168,0),(2089,5570),(0,205),(88,1018),(0,26528), +(0,0),(4,5567),(1444,145),(6,0),(1,7535),(7793,534),(70,9), +(178,1),(44,5),(189,0),(3,0); +EXPLAIN +SELECT a+SLEEP(0.01) FROM t1 +WHERE a IN ( 255, 0 ) OR b BETWEEN 6 AND 129 +ORDER BY b; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index_merge a,b a,b 5,5 NULL 8 Using sort_union(a,b); Using where; Using filesort +set @show_explain_probe_select_id=1; +set debug_dbug='d,show_explain_probe_join_exec_start'; +SELECT a+SLEEP(0.01) FROM t1 +WHERE a IN ( 255, 0 ) OR b BETWEEN 6 AND 129 +ORDER BY b; +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index_merge a,b a,b 5,5 NULL 8 Using sort_union(a,b); Using where; Using filesort +Warnings: +Note 1003 SELECT a+SLEEP(0.01) FROM t1 +WHERE a IN ( 255, 0 ) OR b BETWEEN 6 AND 129 +ORDER BY b +a+SLEEP(0.01) +0 +5372 +70 +0 +0 +0 +0 +set @show_explain_probe_select_id=1; +set debug_dbug='d,show_explain_probe_do_select'; +SELECT a+SLEEP(0.01) FROM t1 +WHERE a IN ( 255, 0 ) OR b BETWEEN 6 AND 129 +ORDER BY b; +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index_merge a,b a,b 5,5 NULL 8 Using sort_union(a,b); Using where; Using filesort +Warnings: +Note 1003 SELECT a+SLEEP(0.01) FROM t1 +WHERE a IN ( 255, 0 ) OR b BETWEEN 6 AND 129 +ORDER BY b +a+SLEEP(0.01) +0 +5372 +70 +0 +0 +0 +0 +set debug_dbug=''; +drop table t1; +# +# MDEV-298: SHOW EXPLAIN: Plan returned by SHOW EXPLAIN only contains +# 'Using temporary' while the standard EXPLAIN says 'Using temporary; Using filesort' +# +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9), +(10),(11),(12),(13),(14),(15),(16); +INSERT INTO t1 SELECT t11.a FROM t1 t11, t1 t12, t1 t13; +EXPLAIN SELECT a FROM t1 GROUP BY a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4112 Using temporary; Using filesort +set @show_explain_probe_select_id=1; +set debug_dbug='d,show_explain_probe_join_exec_start'; +SELECT a FROM t1 GROUP BY a; +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4112 Using temporary; Using filesort +Warnings: +Note 1003 SELECT a FROM t1 GROUP BY a +a +1 +2 +3 +4 +5 +6 +7 +8 +9 +10 +11 +12 +13 +14 +15 +16 +set debug_dbug=''; +drop table t1; +# +# MDEV-408: SHOW EXPLAIN: Some values are chopped off in SHOW EXPLAIN output +# +CREATE TABLE t1 (a INT, b VARCHAR(35)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (3989,'Abilene'),(3873,'Akron'); +CREATE TABLE t2 (c INT, d VARCHAR(52) PRIMARY KEY, KEY(c)) ENGINE=InnoDB; +INSERT INTO t2 VALUES (86,'English'),(87,'Russian'); +explain SELECT SUM(a + SLEEP(0.1)) FROM t1 WHERE a IN ( SELECT c FROM t2 WHERE d < b ) OR b < 's'; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where +2 DEPENDENT SUBQUERY t2 index_subquery PRIMARY,c c 5 func 1 Using index; Using where +set @show_explain_probe_select_id=1; +set debug_dbug='d,show_explain_probe_join_exec_start'; +SELECT SUM(a + SLEEP(0.1)) FROM t1 WHERE a IN ( SELECT c FROM t2 WHERE d < b ) OR b < 's'; +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where +2 DEPENDENT SUBQUERY t2 index_subquery PRIMARY,c c 5 func 1 Using index; Using where +Warnings: +Note 1003 SELECT SUM(a + SLEEP(0.1)) FROM t1 WHERE a IN ( SELECT c FROM t2 WHERE d < b ) OR b < 's' +SUM(a + SLEEP(0.1)) +7862 +set debug_dbug=''; +drop table t1, t2; +# +# MDEV-412: SHOW EXPLAIN: Server crashes in JOIN::print_explain on a query with inner join and ORDER BY the same column twice +# +CREATE TABLE t1 (a INT PRIMARY KEY, b VARCHAR(3), KEY(b)) ENGINE=MyISAM; +INSERT INTO t1 VALUES +(3795,'USA'),(3913,'USA'),(3846,'ITA'),(4021,'USA'),(4005,'RUS'),(4038,'USA'), +(3825,'USA'),(3840,'USA'),(3987,'USA'),(3807,'USA'),(3896,'USA'),(4052,'USA'), +(3973,'USA'),(3982,'ITA'),(3965,'USA'),(3852,'RUS'),(4006,'USA'),(3800,'USA'), +(4020,'USA'),(4040,'USA'),(3916,'USA'),(3817,'USA'),(3885,'USA'),(3802,'USA'), +(4009,'ITA'),(3895,'USA'),(3963,'RUS'),(4045,'USA'),(3988,'USA'),(3815,'USA'), +(4063,'USA'),(3978,'USA'),(4019,'USA'),(3954,'USA'),(3950,'USA'),(3974,'ITA'), +(4054,'USA'),(4061,'RUS'),(3976,'USA'),(3966,'USA'),(3957,'USA'),(3981,'USA'), +(3923,'USA'),(3876,'USA'),(3819,'USA'),(3877,'USA'),(3829,'ITA'),(3964,'USA'), +(4053,'RUS'),(3917,'USA'),(3874,'USA'),(4023,'USA'),(4001,'USA'),(3872,'USA'), +(3890,'USA'),(3962,'USA'),(3886,'USA'),(4026,'ITA'),(3869,'USA'),(3937,'RUS'), +(3975,'USA'),(3944,'USA'),(3908,'USA'),(3867,'USA'),(3947,'USA'),(3838,'USA'), +(3796,'USA'),(3893,'USA'),(3920,'ITA'),(3994,'USA'),(3875,'RUS'),(4011,'USA'), +(4013,'USA'),(3810,'USA'),(3834,'USA'),(3968,'USA'),(3931,'USA'),(3839,'USA'), +(4042,'USA'),(4039,'ITA'),(3811,'USA'),(3837,'RUS'),(4041,'USA'),(3884,'USA'), +(3894,'USA'),(3879,'USA'),(3942,'USA'),(3959,'USA'),(3814,'USA'),(4044,'USA'), +(3971,'ITA'),(3823,'USA'),(3793,'RUS'),(3855,'USA'),(3905,'USA'),(3865,'USA'), +(4046,'USA'),(3990,'USA'),(4022,'USA'),(3833,'USA'),(3918,'USA'),(4064,'ITA'), +(3821,'USA'),(3836,'RUS'),(3921,'USA'),(3914,'USA'),(3888,'USA'); +CREATE TABLE t2 (c VARCHAR(3) PRIMARY KEY) ENGINE=MyISAM; +INSERT INTO t2 VALUES ('USA'); +CREATE TABLE t3 (d VARCHAR(3), e VARCHAR(52), PRIMARY KEY (d,e)) ENGINE=MyISAM; +INSERT INTO t3 VALUES +('JPN','Japanese'),('KOR','Korean'),('POL','Polish'),('PRT','Portuguese'), +('ESP','Spanish'),('FRA','French'),('VNM','Vietnamese'); +explain +SELECT b AS field1, b AS field2 FROM t1, t2, t3 WHERE d = b ORDER BY field1, field2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 system NULL NULL NULL NULL 1 Using filesort +1 SIMPLE t1 index b b 6 NULL 107 Using where; Using index +1 SIMPLE t3 ref PRIMARY PRIMARY 5 test.t1.b 1 Using index +set @show_explain_probe_select_id=1; +set debug_dbug='d,show_explain_probe_do_select'; +SELECT b AS field1, b AS field2 FROM t1, t2, t3 WHERE d = b ORDER BY field1, field2; +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 system NULL NULL NULL NULL 1 Using filesort +1 SIMPLE t1 index b b 6 NULL 107 Using where; Using index +1 SIMPLE t3 ref PRIMARY PRIMARY 5 test.t1.b 1 Using index +Warnings: +Note 1003 SELECT b AS field1, b AS field2 FROM t1, t2, t3 WHERE d = b ORDER BY field1, field2 +field1 field2 +set debug_dbug=''; +DROP TABLE t1,t2,t3; +# +# MDEV-423: SHOW EXPLAIN: 'Using where' for a subquery is shown in EXPLAIN, but not in SHOW EXPLAIN output +# +CREATE TABLE t1 (a INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (7),(0),(9),(3),(4),(2),(5),(7),(0),(9),(3),(4),(2),(5); +CREATE TABLE t2 (b INT, c INT) ENGINE=MyISAM; +INSERT INTO t2 VALUES +(0,4),(8,6),(1,3),(8,5),(9,3),(24,246),(6,2),(1,9),(6,3),(2,8), +(4,1),(8,8),(4,8),(4,5),(7,7),(4,5),(1,1),(9,6),(4,2),(8,9); +create table t3 like t2; +insert into t3 select * from t2; +explain +SELECT max(a+b+c) FROM t1 AS alias1, ( SELECT * FROM t2 ) AS alias +WHERE EXISTS ( SELECT * FROM t3 WHERE b = c ) OR a <= 10; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY alias1 ALL NULL NULL NULL NULL 14 +1 PRIMARY t2 ALL NULL NULL NULL NULL 20 +3 SUBQUERY t3 ALL NULL NULL NULL NULL 20 Using where +set @show_explain_probe_select_id=1; +set debug_dbug='d,show_explain_probe_join_exec_start'; +SELECT max(a+b+c) FROM t1 AS alias1, ( SELECT * FROM t2 ) AS alias +WHERE EXISTS ( SELECT * FROM t3 WHERE b = c ) OR a <= 10; +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY alias1 ALL NULL NULL NULL NULL 14 +1 PRIMARY t2 ALL NULL NULL NULL NULL 20 +3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Query plan already deleted +Warnings: +Note 1003 SELECT max(a+b+c) FROM t1 AS alias1, ( SELECT * FROM t2 ) AS alias +WHERE EXISTS ( SELECT * FROM t3 WHERE b = c ) OR a <= 10 +max(a+b+c) +279 +set debug_dbug=''; +DROP TABLE t1,t2,t3; +# +# MDEV-416: Server crashes in SQL_SELECT::cleanup on EXPLAIN with SUM ( DISTINCT ) in a non-correlated subquery (5.5-show-explain tree) +# +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (b INT); +INSERT INTO t2 VALUES (8),(9); +EXPLAIN SELECT * FROM t1 +WHERE ( 8, 89 ) IN ( SELECT b, SUM( DISTINCT b ) FROM t2 GROUP BY b ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using filesort +DROP TABLE t1,t2; +# +# Check if queries in non-default charsets work. +# +set names cp1251; +select charset('ãû'); +charset('ãû') +cp1251 +select hex('ãû'); +hex('ãû') +E3FB +set @show_explain_probe_select_id=1; +set debug_dbug='d,show_explain_probe_join_exec_start'; +select * from t0 where length('ãû') = a; +set names utf8; +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t0 ALL NULL NULL NULL NULL 10 Using where +Warnings: +Note 1003 select * from t0 where length('гы') = a +set names default; +a +2 +set debug_dbug=''; +set names default; +# +# MDEV-462: SHOW EXPLAIN: Assertion `table_list->table' fails in find_field_in_table_ref if FOR contains a non-numeric value +# +show explain for foo; +ERROR HY000: You may only use constant expressions in this statement +# End +drop table t0; diff --git a/mysql-test/r/show_explain_ps.result b/mysql-test/r/show_explain_ps.result new file mode 100644 index 00000000000..f9b82c85f01 --- /dev/null +++ b/mysql-test/r/show_explain_ps.result @@ -0,0 +1,29 @@ +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 +# Check out if our cond is hit. +create table t0 (a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +set @show_explain_probe_select_id=1; +set debug_dbug='d,show_explain_probe_join_exec_start'; +select count(*) from t0 where a < 100000; +show explain for $thr2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t0 ALL NULL NULL NULL NULL 10 Using where +Warnings: +Note 1003 select count(*) from t0 where a < 100000 +count(*) +10 +set debug_dbug=''; +select event_name +from performance_schema.events_waits_history_long +where event_name='wait/synch/cond/sql/show_explain'; +event_name +wait/synch/cond/sql/show_explain +drop table t0; diff --git a/mysql-test/r/sp-code.result b/mysql-test/r/sp-code.result index 67651294498..3bead4fc826 100644 --- a/mysql-test/r/sp-code.result +++ b/mysql-test/r/sp-code.result @@ -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.result b/mysql-test/r/sp.result index b5e6e84a49a..293400ece2f 100644 --- a/mysql-test/r/sp.result +++ b/mysql-test/r/sp.result @@ -6403,14 +6403,14 @@ select 1; /*! select 2; */ select 3; /*!00000 select 4; */ -/*!99999 select 5; */ +/*!999999 select 5; */ end $$ create procedure proc_25411_b( /* real comment */ /*! p1 int, */ /*!00000 p2 int */ -/*!99999 ,p3 int */ +/*!999999 ,p3 int */ ) begin select p1, p2; @@ -6418,11 +6418,11 @@ end $$ create procedure proc_25411_c() begin -select 1/*!,2*//*!00000,3*//*!99999,4*/; -select 1/*! ,2*//*!00000 ,3*//*!99999 ,4*/; -select 1/*!,2 *//*!00000,3 *//*!99999,4 */; -select 1/*! ,2 *//*!00000 ,3 *//*!99999 ,4 */; -select 1 /*!,2*/ /*!00000,3*/ /*!99999,4*/ ; +select 1/*!,2*//*!00000,3*//*!999999,4*/; +select 1/*! ,2*//*!00000 ,3*//*!999999 ,4*/; +select 1/*!,2 *//*!00000,3 *//*!999999,4 */; +select 1/*! ,2 *//*!00000 ,3 *//*!999999 ,4 */; +select 1 /*!,2*/ /*!00000,3*/ /*!999999,4*/ ; end $$ show create procedure proc_25411_a; 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/str_to_datetime_457.result b/mysql-test/r/str_to_datetime_457.result new file mode 100644 index 00000000000..4fd0d00691c --- /dev/null +++ b/mysql-test/r/str_to_datetime_457.result @@ -0,0 +1,51 @@ +select cast('01:02:03 ' as time), cast('01:02:03 ' as time); +cast('01:02:03 ' as time) cast('01:02:03 ' as time) +01:02:03 00:00:00 +select cast('2002-011-012' as date), cast('2002.11.12' as date), cast('2002.011.012' as date); +cast('2002-011-012' as date) cast('2002.11.12' as date) cast('2002.011.012' as date) +2002-11-12 2002-11-12 2002-11-12 +select cast('2012103123595912' as datetime(6)), cast('20121031235959123' as datetime(6)); +cast('2012103123595912' as datetime(6)) cast('20121031235959123' as datetime(6)) +2012-10-31 23:59:59.000000 2012-10-31 23:59:59.000000 +Warnings: +Warning 1292 Truncated incorrect datetime value: '2012103123595912' +Warning 1292 Truncated incorrect datetime value: '20121031235959123' +select cast(0 as date), cast('0000-00-00' as date), cast('0' as date); +cast(0 as date) cast('0000-00-00' as date) cast('0' as date) +0000-00-00 0000-00-00 NULL +Warnings: +Warning 1292 Incorrect datetime value: '0' +select extract(hour from '100000:02:03'), extract(hour from '100000:02:03 '); +extract(hour from '100000:02:03') extract(hour from '100000:02:03 ') +NULL NULL +Warnings: +Warning 1292 Truncated incorrect time value: '100000:02:03' +Warning 1292 Truncated incorrect time value: '100000:02:03 ' +# +# backward compatibility craziness +# +select cast('12:00:00.12.34.56' as time); +cast('12:00:00.12.34.56' as time) +12:00:00 +Warnings: +Warning 1292 Truncated incorrect time value: '12:00:00.12.34.56' +select cast('12:00:00 12.34.56' as time); +cast('12:00:00 12.34.56' as time) +12:34:56 +select cast('12:00:00-12.34.56' as time); +cast('12:00:00-12.34.56' as time) +12:00:00 +Warnings: +Warning 1292 Truncated incorrect time value: '12:00:00-12.34.56' +select cast('12:00:00.12.34.56' as datetime); +cast('12:00:00.12.34.56' as datetime) +2012-00-00 12:34:56 +select cast('12:00:00-12.34.56' as datetime); +cast('12:00:00-12.34.56' as datetime) +2012-00-00 12:34:56 +select cast('12:00:00 12.34.56' as datetime); +cast('12:00:00 12.34.56' as datetime) +2012-00-00 12:34:56 +select cast('12:00:00.123456' as time); +cast('12:00:00.123456' as time) +12:00:00 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_innodb.result b/mysql-test/r/subselect_innodb.result index a4670872fad..6c1e52a112e 100644 --- a/mysql-test/r/subselect_innodb.result +++ b/mysql-test/r/subselect_innodb.result @@ -248,6 +248,47 @@ NULL drop procedure p1; drop tables t1,t2,t3; # +# Bug #58756 +# Crash in heap_rrnd on query with HAVING ... IN (subquery) + LIMIT +# +CREATE TABLE t1 ( +col_time_key time DEFAULT NULL, +col_datetime_key datetime DEFAULT NULL, +col_varchar_nokey varchar(1) DEFAULT NULL, +KEY col_time_key (col_time_key), +KEY col_datetime_key (col_datetime_key) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +INSERT INTO t1 VALUES ('17:53:30','2005-11-10 12:40:29','h'); +INSERT INTO t1 VALUES ('11:35:49','2009-04-25 00:00:00','b'); +INSERT INTO t1 VALUES (NULL,'2002-11-27 00:00:00','s'); +INSERT INTO t1 VALUES ('06:01:40','2004-01-26 20:32:32','e'); +INSERT INTO t1 VALUES ('05:45:11','2007-10-26 11:41:40','j'); +INSERT INTO t1 VALUES ('00:00:00','2005-10-07 00:00:00','e'); +INSERT INTO t1 VALUES ('00:00:00','2000-07-15 05:00:34','f'); +INSERT INTO t1 VALUES ('06:11:01','2000-04-03 16:33:32','v'); +INSERT INTO t1 VALUES ('13:02:46',NULL,'x'); +INSERT INTO t1 VALUES ('21:44:25','2001-04-25 01:26:12','m'); +INSERT INTO t1 VALUES ('22:43:58','2000-12-27 00:00:00','c'); +CREATE TABLE t2 ( +col_time_key time DEFAULT NULL, +col_datetime_key datetime DEFAULT NULL, +col_varchar_nokey varchar(1) DEFAULT NULL, +KEY col_time_key (col_time_key), +KEY col_datetime_key (col_datetime_key) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +INSERT INTO t2 VALUES ('11:28:45','2004-10-11 18:13:16','w'); +SELECT col_time_key, col_datetime_key +FROM +( SELECT * FROM t1 ) AS table1 +HAVING ( 'r' , 'e' ) IN +( SELECT col_varchar_nokey , col_varchar_nokey FROM t2 ) +ORDER BY col_datetime_key +LIMIT 10; +col_time_key col_datetime_key +DROP TABLE t1; +DROP TABLE t2; +# End of Bug #58756 +# # Bug#60085 crash in Item::save_in_field() with time data type # CREATE TABLE t1(a date, b int, unique(b), unique(a), key(b)) engine=innodb; diff --git a/mysql-test/r/symlink.result b/mysql-test/r/symlink.result index 20736aec47f..279af1e44d2 100644 --- a/mysql-test/r/symlink.result +++ b/mysql-test/r/symlink.result @@ -120,7 +120,7 @@ CREATE TABLE t2(a INT) DATA DIRECTORY='TEST_DIR/tmp' INDEX DIRECTORY='TEST_DIR/tmp'; RENAME TABLE t2 TO t1; -ERROR HY000: Can't create/write to file 'TEST_DIR/tmp/t1.MYI' (Errcode: 17) +ERROR HY000: Can't create/write to file 'TEST_DIR/tmp/t1.MYI' (Errcode: 17 "File exists") DROP TABLE t2; create temporary table t1 (a int) engine=myisam data directory="MYSQLTEST_VARDIR/log" select 9 a; show create table t1; @@ -167,7 +167,7 @@ INDEX DIRECTORY='MYSQLD_DATADIR'; DROP TABLE IF EXISTS t1; CREATE TABLE t1(a INT) INDEX DIRECTORY='TEST_DIR/master-data_var'; -ERROR HY000: Can't create/write to file 'TEST_DIR/master-data_var/t1.MYI' (Errcode: 2) +ERROR HY000: Can't create/write to file 'TEST_DIR/master-data_var/t1.MYI' (Errcode: 2 "No such file or directory") SET @OLD_SQL_MODE=@@SQL_MODE, @@SQL_MODE='NO_DIR_IN_CREATE'; CREATE TABLE t1(a INT) DATA DIRECTORY='MYSQLTEST_VARDIR/tmp' INDEX DIRECTORY='MYSQLTEST_VARDIR/tmp'; Warnings: diff --git a/mysql-test/r/system_mysql_db.result b/mysql-test/r/system_mysql_db.result index d5409136ed4..4d458e7284c 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 d5409136ed4..4d458e7284c 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 d5409136ed4..4d458e7284c 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 d5409136ed4..4d458e7284c 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.result b/mysql-test/r/trigger.result index 6047d00c858..3310209df58 100644 --- a/mysql-test/r/trigger.result +++ b/mysql-test/r/trigger.result @@ -969,7 +969,7 @@ trigger_schema trigger_name event_object_schema event_object_table action_statem test t1_bi test t1 set @a:=new.id test t1_ai test t1 set @b:=new.id rename table t1 to t2; -ERROR HY000: Can't create/write to file './test/t1_ai.TRN~' (Errcode: 13) +ERROR HY000: Can't create/write to file './test/t1_ai.TRN~' (Errcode: 13 "Permission denied") insert into t1 values (102); select @a, @b; @a @b 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/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 e055a333faf..ad5e39a2851 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/xa_binlog.result b/mysql-test/r/xa_binlog.result index 3ce64953902..395f0dc62a4 100644 --- a/mysql-test/r/xa_binlog.result +++ b/mysql-test/r/xa_binlog.result @@ -18,7 +18,7 @@ a 1 2 3 -SHOW BINLOG EVENTS LIMIT 1,9; +SHOW BINLOG EVENTS LIMIT 2,9; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 # Query 1 # BEGIN master-bin.000001 # Query 1 # use `test`; INSERT INTO t1 VALUES (1) |