diff options
| author | Otto Kekäläinen <otto@seravo.fi> | 2015-09-05 21:28:14 +0300 |
|---|---|---|
| committer | Otto Kekäläinen <otto@seravo.fi> | 2015-09-05 21:28:14 +0300 |
| commit | d6c5e7e2cc16fa63875ef9e5a63caf5c9f147e7c (patch) | |
| tree | c0c1c07edf6beeb1b8e97783934b5b9a778117da /mysql-test/t | |
| parent | b9fee60871264ea56a5582ca7a436aabdabea6fc (diff) | |
| parent | 67dbfab3d7945886caf6ba8de6a17799e3db25aa (diff) | |
| download | mariadb-git-d6c5e7e2cc16fa63875ef9e5a63caf5c9f147e7c.tar.gz | |
Merge branch '10.1' of https://github.com/MariaDB/server into ok-debpkg-10.1
Diffstat (limited to 'mysql-test/t')
118 files changed, 919 insertions, 101 deletions
diff --git a/mysql-test/t/alter_table.test b/mysql-test/t/alter_table.test index cdc188b7776..bce7f3f95bb 100644 --- a/mysql-test/t/alter_table.test +++ b/mysql-test/t/alter_table.test @@ -1,3 +1,7 @@ +if (`select plugin_auth_version < "5.6.26" from information_schema.plugins where plugin_name='innodb'`) +{ + --skip Not fixed in XtraDB below 5.6.26 +} --source include/have_innodb.inc # # Test of alter table diff --git a/mysql-test/t/analyze_stmt_privileges.test b/mysql-test/t/analyze_stmt_privileges.test index 9dd8f274fce..b565f17c0f7 100644 --- a/mysql-test/t/analyze_stmt_privileges.test +++ b/mysql-test/t/analyze_stmt_privileges.test @@ -11,6 +11,7 @@ use db; create table t1 (i int, c varchar(8)); insert into t1 values (1,'foo'),(2,'bar'),(3,'baz'),(4,'qux'); create view v1 as select * from t1 where i > 1; +CREATE USER u1@localhost; grant ALL on db.v1 to u1@localhost; --connect (con1,localhost,u1,,) diff --git a/mysql-test/t/analyze_stmt_privileges2.test b/mysql-test/t/analyze_stmt_privileges2.test index 6fcdb7d4399..9a0299be535 100644 --- a/mysql-test/t/analyze_stmt_privileges2.test +++ b/mysql-test/t/analyze_stmt_privileges2.test @@ -24,6 +24,8 @@ --source include/count_sessions.inc --enable_connect_log +set GLOBAL sql_mode=""; +set LOCAL sql_mode=""; CREATE DATABASE privtest_db; @@ -5395,6 +5397,7 @@ DROP USER 'privtest'@localhost; USE test; DROP DATABASE privtest_db; +set GLOBAL sql_mode=default; --source include/wait_until_count_sessions.inc diff --git a/mysql-test/t/blackhole_plugin.test b/mysql-test/t/blackhole_plugin.test index 8af8fcc941c..487fc5656f2 100644 --- a/mysql-test/t/blackhole_plugin.test +++ b/mysql-test/t/blackhole_plugin.test @@ -1,13 +1,14 @@ if (!$HA_BLACKHOLE_SO) { --skip Need blackhole plugin } - +set sql_mode=""; CREATE TABLE t1(a int) ENGINE=BLACKHOLE; +set sql_mode=default; DROP TABLE t1; --replace_regex /\.dll/.so/ eval INSTALL PLUGIN blackhole SONAME '$HA_BLACKHOLE_SO'; --replace_regex /\.dll/.so/ ---error 1125 +--error ER_PLUGIN_INSTALLED eval INSTALL PLUGIN BLACKHOLE SONAME '$HA_BLACKHOLE_SO'; UNINSTALL PLUGIN blackhole; diff --git a/mysql-test/t/bootstrap.test b/mysql-test/t/bootstrap.test index 97376eb7412..840b9a12cee 100644 --- a/mysql-test/t/bootstrap.test +++ b/mysql-test/t/bootstrap.test @@ -78,6 +78,7 @@ EOF # Check that installed plugins are *not* automatically loaded in --bootstrap # --write_file $MYSQLTEST_VARDIR/tmp/bootstrap_plugins.sql +SET SQL_MODE=""; use test; create table t1(a int) engine=example; EOF diff --git a/mysql-test/t/change_user.test b/mysql-test/t/change_user.test index 659354e9c48..c918224520d 100644 --- a/mysql-test/t/change_user.test +++ b/mysql-test/t/change_user.test @@ -1,10 +1,14 @@ +# This test is checking that old password authentication works +set global secure_auth=0; # # functional change user tests # - +create user test_nopw; grant select on test.* to test_nopw; -grant select on test.* to test_oldpw identified by password "09301740536db389"; -grant select on test.* to test_newpw identified by "newpw"; +create user test_oldpw identified by password "09301740536db389"; +grant select on test.* to test_oldpw; +create user test_newpw identified by "newpw"; +grant select on test.* to test_newpw; select concat('<', user(), '>'), concat('<', current_user(), '>'), database(); @@ -136,3 +140,5 @@ if ($after != $before){ die The value of com_select changed during change_user; } echo Value of com_select did not change; + +set global secure_auth=default; diff --git a/mysql-test/t/connect.test b/mysql-test/t/connect.test index c4a6fb73b10..20989e70ef9 100644 --- a/mysql-test/t/connect.test +++ b/mysql-test/t/connect.test @@ -12,6 +12,7 @@ # Save the initial number of concurrent sessions --source include/count_sessions.inc +SET global secure_auth=0; --disable_warnings drop table if exists t1,t2; @@ -134,6 +135,7 @@ drop table t1; --echo # -- which results in user lockout. --echo +CREATE USER mysqltest_u1@localhost; GRANT USAGE ON *.* TO mysqltest_u1@localhost; # NOTE: if the test case fails sporadically due to spurious connections, @@ -452,3 +454,6 @@ delimiter ;| call p1(2); drop procedure p1; + + +SET global secure_auth=default; diff --git a/mysql-test/t/create_drop_binlog.test b/mysql-test/t/create_drop_binlog.test index 06fd1c6c1dd..aa772df4805 100644 --- a/mysql-test/t/create_drop_binlog.test +++ b/mysql-test/t/create_drop_binlog.test @@ -1,5 +1,8 @@ --source include/have_udf.inc --source include/have_log_bin.inc +--source include/binlog_start_pos.inc + +--let $pos=`select $binlog_start_pos + 65` --let $binlog_file=query_get_value(SHOW MASTER STATUS, File, 1) --let $binlog_start=query_get_value(SHOW MASTER STATUS, Position, 1) @@ -164,8 +167,11 @@ RESET MASTER; # Test RESET MASTER TO RESET MASTER; +--replace_result $pos <pos> SHOW MASTER STATUS; RESET MASTER TO 100; +--replace_result $pos <pos> SHOW MASTER STATUS; RESET MASTER; +--replace_result $pos <pos> SHOW MASTER STATUS; diff --git a/mysql-test/t/create_or_replace_permission.test b/mysql-test/t/create_or_replace_permission.test index f155e97198c..0e833a125bd 100644 --- a/mysql-test/t/create_or_replace_permission.test +++ b/mysql-test/t/create_or_replace_permission.test @@ -2,6 +2,8 @@ # Grant tests not performed with embedded server -- source include/not_embedded.inc +set local sql_mode=""; +set global sql_mode=""; --echo # --echo # Tests for checking permission denied on CREATE OR REPLACE if DROP --echo # access is revoked @@ -64,3 +66,6 @@ REVOKE ALL ON db1.* FROM mysqltest_1@localhost; DROP DATABASE IF EXISTS db2; DROP DATABASE db1; DROP USER mysqltest_1@localhost; + + +set global sql_mode=default; diff --git a/mysql-test/t/ctype_cp1251.test b/mysql-test/t/ctype_cp1251.test index 6630497db8f..93fd5cc9a1a 100644 --- a/mysql-test/t/ctype_cp1251.test +++ b/mysql-test/t/ctype_cp1251.test @@ -127,5 +127,18 @@ DROP TABLE t1; --echo # +--echo # MDEV-8671 Wrong result for SELECT..WHERE varchar_column=' 1' AND (varchar_column XOR '1') +--echo # +CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET cp1251 COLLATE cp1251_ukrainian_ci); +INSERT INTO t1 VALUES (' 1'),('`1'); +SELECT * FROM t1 WHERE a=' 1'; +SELECT * FROM t1 WHERE (a XOR '0'); +SELECT * FROM t1 WHERE a=' 1' AND (a XOR '0'); +--echo # ' 1' should not be propagated into (a XIR '0') +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=' 1' AND (a XOR '0'); +DROP TABLE t1; + + +--echo # --echo # End of 10.1 tests --echo # diff --git a/mysql-test/t/ctype_latin2_ch.test b/mysql-test/t/ctype_latin2_ch.test index c8ea54e6091..06ccd2c9782 100644 --- a/mysql-test/t/ctype_latin2_ch.test +++ b/mysql-test/t/ctype_latin2_ch.test @@ -144,6 +144,7 @@ SELECT * FROM t1 GROUP BY s1; SELECT * FROM t1 ORDER BY s1; DROP TABLE t1; +set sql_mode=""; --disable_warnings CREATE TABLE t1 ENGINE=INNODB AS SELECT repeat('a', 5) AS s1 LIMIT 0; --enable_warnings @@ -183,6 +184,7 @@ DROP TABLE t1; SET NAMES latin2; + # # Bug#37854 Test fails/aborts for collate latin2_czech_cs used with SET and ENUM datatypes # diff --git a/mysql-test/t/ctype_ucs.test b/mysql-test/t/ctype_ucs.test index 6e5b0850eca..5dd0a5f426c 100644 --- a/mysql-test/t/ctype_ucs.test +++ b/mysql-test/t/ctype_ucs.test @@ -440,9 +440,11 @@ drop table t1; # # Bug #14583 Bug on query using a LIKE on indexed field with ucs2_bin collation # +set sql_mode=""; --disable_warnings create table t1(f1 varchar(5) CHARACTER SET ucs2 COLLATE ucs2_bin NOT NULL) engine=InnoDB; --enable_warnings +set sql_mode=default; insert into t1 values('a'); create index t1f1 on t1(f1); select f1 from t1 where f1 like 'a%'; diff --git a/mysql-test/t/ctype_ujis.test b/mysql-test/t/ctype_ujis.test index 6fc928c7e60..3f444580b13 100644 --- a/mysql-test/t/ctype_ujis.test +++ b/mysql-test/t/ctype_ujis.test @@ -87,6 +87,7 @@ DROP TABLE t1; # # Bug #6345 Unexpected behaviour with partial indices # +set sql_mode=""; --disable_warnings CREATE TABLE t1 ( @@ -96,6 +97,7 @@ CREATE TABLE t1 KEY b (b(10)) ) ENGINE=InnoDB CHARACTER SET 'ujis' COLLATE 'ujis_japanese_ci'; --enable_warnings +set sql_mode=default; INSERT INTO t1 (a, b) VALUES (0, 'aaabbbcccddd'); INSERT INTO t1 (a, b) VALUES (1, 'eeefffggghhh'); INSERT INTO t1 (a, b) VALUES (2, 'iiijjjkkkl'); diff --git a/mysql-test/t/ddl_i18n_koi8r.test b/mysql-test/t/ddl_i18n_koi8r.test index 40f8d07c55d..0a62a8ba0f8 100644 --- a/mysql-test/t/ddl_i18n_koi8r.test +++ b/mysql-test/t/ddl_i18n_koi8r.test @@ -24,6 +24,8 @@ # - restore object; # +set sql_mode=""; + ########################################################################### # # NOTE: this file contains text in UTF8 and KOI8-R encodings. diff --git a/mysql-test/t/ddl_i18n_utf8.test b/mysql-test/t/ddl_i18n_utf8.test index 35109e62100..023047b952e 100644 --- a/mysql-test/t/ddl_i18n_utf8.test +++ b/mysql-test/t/ddl_i18n_utf8.test @@ -30,6 +30,8 @@ # ########################################################################### +set sql_mode=""; + # Test requires server to accept client connections (for mysqldump portions) --source include/not_embedded.inc --source include/have_utf8.inc diff --git a/mysql-test/t/default.test b/mysql-test/t/default.test index 6904d9bb242..17f4383ae22 100644 --- a/mysql-test/t/default.test +++ b/mysql-test/t/default.test @@ -9,6 +9,7 @@ drop database if exists mysqltest; # Bug 10838 # Insert causes warnings for no default values and corrupts tables # +set sql_mode=""; CREATE TABLE t1 (a varchar(30) binary NOT NULL DEFAULT ' ', b varchar(1) binary NOT NULL DEFAULT ' ', c varchar(4) binary NOT NULL DEFAULT '0000', @@ -39,6 +40,7 @@ CREATE TABLE t1 (a varchar(30) binary NOT NULL DEFAULT ' ', b1 tinyblob NULL) ENGINE=InnoDB DEFAULT CHARACTER SET = latin1 COLLATE latin1_bin; --enable_warnings +set sql_mode=default; INSERT into t1 (b) values ('1'); SHOW WARNINGS; diff --git a/mysql-test/t/delete_returning_grant.test b/mysql-test/t/delete_returning_grant.test index f67a766b936..67613603a72 100644 --- a/mysql-test/t/delete_returning_grant.test +++ b/mysql-test/t/delete_returning_grant.test @@ -3,6 +3,9 @@ # --source include/not_embedded.inc +set GLOBAL sql_mode=""; +set LOCAL sql_mode=""; + CREATE TABLE t1 (a int(11), b varchar(32)); INSERT INTO t1 VALUES (7,'ggggggg'),(1,'a'),(3,'ccc'),(4,'dddd'),(1,'A'), (2,'BB'),(4,'DDDD'),(5,'EEEEE'),(7,'GGGGGGG'),(2,'bb'); @@ -76,3 +79,4 @@ DROP USER mysqltest_1@localhost; DROP VIEW v1; DROP TABLE t1; +set GLOBAL sql_mode=default; diff --git a/mysql-test/t/derived.test b/mysql-test/t/derived.test index ddce7f55292..d98e7b56905 100644 --- a/mysql-test/t/derived.test +++ b/mysql-test/t/derived.test @@ -492,7 +492,38 @@ update t1 set balance=(select sum(balance) from (SELECT balance FROM t1 where ac set optimizer_switch=@save_derived_optimizer_switch_bug; drop table t1; -set optimizer_switch=@save_derived_optimizer_switch; +--echo # +--echo # MDEV-6219:Server crashes in Bitmap<64u>::merge +--echo # (this=0x180, map2=...) on 2nd execution of PS with INSERT .. SELECT, +--echo # derived_merge +--echo # + +CREATE TABLE t1 (a VARCHAR(8)) ENGINE=MyISAM; +INSERT INTO t1 VALUES ('foo'),('bar'); + +create procedure p1() + INSERT INTO t1 SELECT * FROM ( + SELECT * FROM t1 + ) AS sq + WHERE sq.a IN ( SELECT 'baz' FROM DUAL ); + +call p1(); +call p1(); +drop procedure p1; + +PREPARE stmt FROM " + INSERT INTO t1 SELECT * FROM ( + SELECT * FROM t1 + ) AS sq + WHERE sq.a IN ( SELECT 'baz' FROM DUAL ) +"; + +EXECUTE stmt; +EXECUTE stmt; + +deallocate prepare stmt; + +drop table t1; --echo # --echo # MDEV-6892: WHERE does not apply @@ -506,3 +537,5 @@ select x.id, message from (select id from t1) x left join (select id, 1 as message from t2) y on x.id=y.id where coalesce(message,0) <> 0; drop table t1,t2; + +set optimizer_switch=@save_derived_optimizer_switch; diff --git a/mysql-test/t/derived_view.test b/mysql-test/t/derived_view.test index 67899837bb2..3d9ab4e4477 100644 --- a/mysql-test/t/derived_view.test +++ b/mysql-test/t/derived_view.test @@ -1600,7 +1600,7 @@ CREATE TABLE IF NOT EXISTS `galleries` ( `year` int(11) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) DEFAULT CHARSET=utf8; CREATE TABLE IF NOT EXISTS `pictures` ( `id` int(11) NOT NULL AUTO_INCREMENT, @@ -1614,7 +1614,7 @@ CREATE TABLE IF NOT EXISTS `pictures` ( `type` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `gallery_id` (`gallery_id`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8 ; +) DEFAULT CHARSET=utf8 ; ALTER TABLE `pictures` ADD CONSTRAINT `pictures_ibfk_1` FOREIGN KEY (`gallery_id`) REFERENCES `galleries` (`id`); diff --git a/mysql-test/t/empty_server_name-8224.test b/mysql-test/t/empty_server_name-8224.test index 528bce5dac5..b15e9d82eb8 100644 --- a/mysql-test/t/empty_server_name-8224.test +++ b/mysql-test/t/empty_server_name-8224.test @@ -7,3 +7,7 @@ create server '' foreign data wrapper w2 options (host '127.0.0.1'); --shutdown_server 10 --source include/wait_until_disconnected.inc --exec echo "restart" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect + +-- enable_reconnect +-- source include/wait_until_connected_again.inc +drop server ''; diff --git a/mysql-test/t/enforce_storage_engine.test b/mysql-test/t/enforce_storage_engine.test index 8256fcd59fa..63f600d1879 100644 --- a/mysql-test/t/enforce_storage_engine.test +++ b/mysql-test/t/enforce_storage_engine.test @@ -1,5 +1,7 @@ -- source include/not_embedded.inc +set local sql_mode=""; +set global sql_mode=""; --disable_warnings drop table if exists t1; --enable_warnings @@ -82,5 +84,6 @@ DROP TABLE t1; connection default; disconnect con1; +set global sql_mode=default; SET SESSION enforce_storage_engine=NULL; SET GLOBAL enforce_storage_engine=NULL; diff --git a/mysql-test/t/enforce_storage_engine_opt.test b/mysql-test/t/enforce_storage_engine_opt.test index 82cc4117cdb..7ca7f94d3dc 100644 --- a/mysql-test/t/enforce_storage_engine_opt.test +++ b/mysql-test/t/enforce_storage_engine_opt.test @@ -1,3 +1,4 @@ +SET SQL_MODE=""; create table t1 (c1 int primary key auto_increment, c2 varchar(10)) engine=memory; show create table t1; set session sql_mode='no_engine_substitution'; diff --git a/mysql-test/t/events_1.test b/mysql-test/t/events_1.test index 89e8f9ca7dc..7cd488bc376 100644 --- a/mysql-test/t/events_1.test +++ b/mysql-test/t/events_1.test @@ -4,6 +4,8 @@ # Can't test with embedded server that doesn't support grants -- source include/not_embedded.inc +set sql_mode=""; + call mtr.add_suppression("Column count of mysql.event is wrong. Expected .*, found .*\. The table is probably corrupted"); --disable_warnings diff --git a/mysql-test/t/events_2.test b/mysql-test/t/events_2.test index 3d609654b21..ec69a2a5591 100644 --- a/mysql-test/t/events_2.test +++ b/mysql-test/t/events_2.test @@ -3,6 +3,8 @@ # Can't test with embedded server that doesn't support grants -- source include/not_embedded.inc +set sql_mode=""; + --disable_warnings drop database if exists events_test; --enable_warnings diff --git a/mysql-test/t/events_bugs.test b/mysql-test/t/events_bugs.test index 1e93917b08e..c0157a7727e 100644 --- a/mysql-test/t/events_bugs.test +++ b/mysql-test/t/events_bugs.test @@ -8,6 +8,7 @@ # Bug#41925 Warning 1366 Incorrect string value: ... for column processlist.info # # Please set $ +SET SQL_MODE=""; let $fixed_bug41925= 0; # # Dear maintainer of this test. Please do NOT remove the next big comment. @@ -1319,5 +1320,4 @@ DROP DATABASE events_test; SET GLOBAL event_scheduler= 'ON'; --source include/running_event_scheduler.inc SET @@global.concurrent_insert= @concurrent_insert; - # THIS MUST BE THE LAST LINE in this file. diff --git a/mysql-test/t/events_trans.test b/mysql-test/t/events_trans.test index 6d829379fea..eea24f19577 100644 --- a/mysql-test/t/events_trans.test +++ b/mysql-test/t/events_trans.test @@ -4,6 +4,8 @@ -- source include/have_innodb.inc -- source include/not_embedded.inc +set sql_mode=""; + --disable_warnings drop database if exists events_test; drop database if exists mysqltest_no_such_database; diff --git a/mysql-test/t/events_trans_notembedded.test b/mysql-test/t/events_trans_notembedded.test index 0353d183386..3cf823bf713 100644 --- a/mysql-test/t/events_trans_notembedded.test +++ b/mysql-test/t/events_trans_notembedded.test @@ -13,6 +13,7 @@ use events_test; # # Privilege checks # +create user mysqltest_user1@localhost; grant create, insert, select, delete on mysqltest_db2.* to mysqltest_user1@localhost; create database mysqltest_db2; diff --git a/mysql-test/t/features.test b/mysql-test/t/features.test index f2ac5a5bba6..225ab40b361 100644 --- a/mysql-test/t/features.test +++ b/mysql-test/t/features.test @@ -6,6 +6,8 @@ drop table if exists t1; --enable_warnings +set sql_mode=""; + flush status; show status like "feature%"; diff --git a/mysql-test/t/fix_priv_tables.test b/mysql-test/t/fix_priv_tables.test index f68c8b518c8..293d3915a05 100644 --- a/mysql-test/t/fix_priv_tables.test +++ b/mysql-test/t/fix_priv_tables.test @@ -22,6 +22,8 @@ if (!$MYSQL_FIX_PRIVILEGE_TABLES) drop table if exists t1,t1aa,t2aa; --enable_warnings +set sql_mode=""; + # # Bug #20589 Missing some table level privileges after upgrade # diff --git a/mysql-test/t/flush-innodb-notembedded.test b/mysql-test/t/flush-innodb-notembedded.test index d08a0647ff5..15bfeb53475 100644 --- a/mysql-test/t/flush-innodb-notembedded.test +++ b/mysql-test/t/flush-innodb-notembedded.test @@ -1,6 +1,8 @@ --source include/have_innodb.inc --source include/not_embedded.inc +set sql_mode=""; + --echo # Test 7: Check privileges required. --echo # diff --git a/mysql-test/t/flush_read_lock.test b/mysql-test/t/flush_read_lock.test index e8ec07392b5..7ba80ea38ac 100644 --- a/mysql-test/t/flush_read_lock.test +++ b/mysql-test/t/flush_read_lock.test @@ -13,6 +13,8 @@ # Save the initial number of concurrent sessions. --source include/count_sessions.inc +set global sql_mode=""; +set local sql_mode=""; --echo # FTWRL takes two global metadata locks -- a global shared --echo # metadata lock and the commit blocker lock. --echo # The first lock prevents DDL from taking place. @@ -2161,6 +2163,7 @@ disconnect con1; disconnect con2; disconnect con3; +set global sql_mode=default; # Check that all connections opened by test cases in this file are really # gone so execution of other tests won't be affected by their presence. --source include/wait_until_count_sessions.inc diff --git a/mysql-test/t/func_compress.test b/mysql-test/t/func_compress.test index fc3d2697426..2a06769b2ef 100644 --- a/mysql-test/t/func_compress.test +++ b/mysql-test/t/func_compress.test @@ -5,6 +5,10 @@ # Note that this test gives error in the gzip library when running under # valgrind, but these warnings can be ignored +set global max_allowed_packet=1048576; +connect (conn1,localhost,root,,); +connection conn1; + select @test_compress_string:='string for test compress function aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa '; select length(@test_compress_string); @@ -142,6 +146,9 @@ DROP TABLE t1; # SELECT UNCOMPRESS(CAST(0 AS BINARY(5))); +disconnect conn1; +connection default; +set global max_allowed_packet=default; --echo # --echo # End of 5.5 tests --echo # diff --git a/mysql-test/t/func_gconcat.test b/mysql-test/t/func_gconcat.test index 42a30760a86..5550eebf1a3 100644 --- a/mysql-test/t/func_gconcat.test +++ b/mysql-test/t/func_gconcat.test @@ -821,3 +821,14 @@ FROM ( SELECT * FROM t2 ) AS sq2, t3 ORDER BY field; drop table t3, t2, t1; + +--echo # +--echo # MDEV-7821 - Server crashes in Item_func_group_concat::fix_fields on 2nd +--echo # execution of PS +--echo # +CREATE TABLE t1(a INT); +INSERT INTO t1 VALUES(1),(2); +PREPARE stmt FROM "SELECT GROUP_CONCAT(t1a.a ORDER BY 1, t1a.a=0) FROM t1 AS t1a, t1 AS t1b GROUP BY t1a.a"; +EXECUTE stmt; +EXECUTE stmt; +DROP TABLE t1; diff --git a/mysql-test/t/func_math.test b/mysql-test/t/func_math.test index 2998742dbcc..b48f4599074 100644 --- a/mysql-test/t/func_math.test +++ b/mysql-test/t/func_math.test @@ -136,9 +136,11 @@ DROP TABLE t1; # # InnoDB is required to reproduce the fault, but it is okay if we default to # MyISAM when testing. +set sql_mode=""; --disable_warnings create table t1 (a varchar(90), ts datetime not null, index (a)) engine=innodb default charset=utf8; --enable_warnings +set sql_mode=default; insert into t1 values ('http://www.foo.com/', now()); select a from t1 where a='http://www.foo.com/' order by abs(timediff(ts, 0)); drop table t1; diff --git a/mysql-test/t/func_misc.test b/mysql-test/t/func_misc.test index 5236987e16f..364266d7f6f 100644 --- a/mysql-test/t/func_misc.test +++ b/mysql-test/t/func_misc.test @@ -379,7 +379,7 @@ CREATE TABLE t1 (a char(2) not null ); INSERT INTO t1 VALUES (4),(7),(1); set @optimizer_switch_save= @@optimizer_switch; set optimizer_switch='materialization=off'; -CREATE TABLE tv (e char(2) not null ) engine=mysql; +CREATE TABLE tv (e char(2) not null ); INSERT INTO tv VALUES (1); CREATE ALGORITHM=MERGE VIEW v_merge AS SELECT * FROM tv; CREATE ALGORITHM=MERGE VIEW vm AS SELECT * FROM tv; @@ -391,6 +391,11 @@ set optimizer_switch=@optimizer_switch_save; drop view v_merge, vm; drop table t1,tv; +--echo # +--echo # MDEV-4017 - GET_LOCK() with negative timeouts has strange behavior +--echo # +SELECT GET_LOCK('ul1', NULL); +SELECT GET_LOCK('ul1', -1); --echo # --echo # GET_LOCK, RELEASE_LOCK, IS_USED_LOCK functions test diff --git a/mysql-test/t/func_str.test b/mysql-test/t/func_str.test index 95b742d3545..726397bc2a7 100644 --- a/mysql-test/t/func_str.test +++ b/mysql-test/t/func_str.test @@ -6,6 +6,10 @@ drop table if exists t1,t2; --enable_warnings +set global max_allowed_packet=1048576; +connect (conn1,localhost,root,,); +connection conn1; + let $mysqld_datadir= `select @@datadir`; set names latin1; @@ -1562,7 +1566,7 @@ round( ) ); ---connection default +--connection conn1 SET @@global.max_allowed_packet:= @tmp_max; --disconnect newconn # @@ -1739,6 +1743,9 @@ EXECUTE stmt; SET NAMES latin2; EXECUTE stmt; +disconnect conn1; +connection default; +set global max_allowed_packet=default; --echo # --echo # End of 5.6 tests diff --git a/mysql-test/t/func_weight_string.test b/mysql-test/t/func_weight_string.test index 6c34ce1e460..cf6524e9753 100644 --- a/mysql-test/t/func_weight_string.test +++ b/mysql-test/t/func_weight_string.test @@ -2,6 +2,11 @@ drop table if exists t1; --enable_warnings + +set global max_allowed_packet=1048576; +connect (conn1,localhost,root,,); +connection conn1; + set names latin1; # @@ -111,3 +116,6 @@ drop table t1; SELECT HEX(WEIGHT_STRING('ab' AS CHAR(1000000000000000000))); SELECT HEX(WEIGHT_STRING('ab' AS BINARY(1000000000000000000))); +disconnect conn1; +connection default; +set global max_allowed_packet=default; diff --git a/mysql-test/t/grant.test b/mysql-test/t/grant.test index 20632038273..1d828cd8693 100644 --- a/mysql-test/t/grant.test +++ b/mysql-test/t/grant.test @@ -6,6 +6,8 @@ # Save the initial number of concurrent sessions --source include/count_sessions.inc +set GLOBAL sql_mode=""; +set LOCAL sql_mode=""; SET @old_log_bin_trust_function_creators= @@global.log_bin_trust_function_creators; SET GLOBAL log_bin_trust_function_creators = 1; @@ -1666,6 +1668,7 @@ FLUSH PRIVILEGES; CREATE DATABASE mysqltest1; CREATE PROCEDURE mysqltest1.test() SQL SECURITY DEFINER SELECT 1; +CREATE FUNCTION mysqltest1.test() RETURNS INT RETURN 1; --error ER_NO_SUCH_TABLE GRANT EXECUTE ON FUNCTION mysqltest1.test TO mysqltest_1@localhost; GRANT ALL PRIVILEGES ON test.* TO mysqltest_1@localhost; @@ -2207,6 +2210,29 @@ disconnect con1; DROP USER untrusted@localhost; DROP DATABASE secret; +--echo # +--echo # BUG#11759114 - '51401: GRANT TREATS NONEXISTENT FUNCTIONS/PRIVILEGES +--echo # DIFFERENTLY'. +--echo # +--disable_warnings +drop database if exists mysqltest_db1; +--enable_warnings +create database mysqltest_db1; +create user mysqltest_u1; +--echo # Both GRANT statements below should fail with the same error. +--error ER_SP_DOES_NOT_EXIST +grant execute on function mysqltest_db1.f1 to mysqltest_u1; +--error ER_SP_DOES_NOT_EXIST +grant execute on procedure mysqltest_db1.p1 to mysqltest_u1; +--echo # Let us show that GRANT behaviour for routines is consistent +--echo # with GRANT behaviour for tables. Attempt to grant privilege +--echo # on non-existent table also results in an error. +--error ER_NO_SUCH_TABLE +grant select on mysqltest_db1.t1 to mysqltest_u1; +show grants for mysqltest_u1; +drop database mysqltest_db1; +drop user mysqltest_u1; + +set GLOBAL sql_mode=default; # Wait till we reached the initial number of concurrent sessions --source include/wait_until_count_sessions.inc - diff --git a/mysql-test/t/grant2.test b/mysql-test/t/grant2.test index 8590dccd1d1..a40fd2afb18 100644 --- a/mysql-test/t/grant2.test +++ b/mysql-test/t/grant2.test @@ -5,6 +5,8 @@ --source include/count_sessions.inc +set GLOBAL sql_mode=""; +set LOCAL sql_mode=""; SET NAMES binary; # @@ -1029,5 +1031,6 @@ DROP USER mysqltest_u4@localhost; DROP USER mysqltest_u5@localhost; +set GLOBAL sql_mode=default; # Wait till we reached the initial number of concurrent sessions --source include/wait_until_count_sessions.inc diff --git a/mysql-test/t/grant3.test b/mysql-test/t/grant3.test index d24b2de17eb..27f565916f7 100644 --- a/mysql-test/t/grant3.test +++ b/mysql-test/t/grant3.test @@ -4,6 +4,8 @@ # Save the initial number of concurrent sessions --source include/count_sessions.inc +set global sql_mode=""; +set local sql_mode=""; # Test of GRANT commands @@ -204,6 +206,7 @@ connection default; DROP USER 'user2'@'%'; DROP DATABASE temp; +set global sql_mode=default; --echo End of 5.0 tests # Wait till we reached the initial number of concurrent sessions diff --git a/mysql-test/t/grant4.test b/mysql-test/t/grant4.test index 36c44fe997a..a3578c9b85a 100644 --- a/mysql-test/t/grant4.test +++ b/mysql-test/t/grant4.test @@ -4,6 +4,8 @@ --disable_warnings drop database if exists mysqltest_db1; --enable_warnings +set GLOBAL sql_mode=""; +set LOCAL sql_mode=""; create database mysqltest_db1; use mysqltest_db1; create table t_column_priv_only (a int, b int); @@ -232,3 +234,6 @@ create user foo2 identified via mysql_native_password using '2222222222222222'; create user foo3 identified via mysql_old_password using '00'; --error ER_PASSWD_LENGTH create user foo4 identified via mysql_old_password using '11111111111111111111111111111111111111111'; + + +set GLOBAL sql_mode=default; diff --git a/mysql-test/t/grant_4332.test b/mysql-test/t/grant_4332.test index d3c3b2d5652..41e0b822f98 100644 --- a/mysql-test/t/grant_4332.test +++ b/mysql-test/t/grant_4332.test @@ -6,6 +6,8 @@ # --source include/not_embedded.inc +set GLOBAL sql_mode=""; +set LOCAL sql_mode=""; alter table mysql.user modify User char(16) binary not null default ''; alter table mysql.db modify User char(16) binary not null default ''; @@ -40,3 +42,4 @@ flush privileges; select user(); --disable_metadata +set GLOBAL sql_mode=default; diff --git a/mysql-test/t/grant_explain_non_select.test b/mysql-test/t/grant_explain_non_select.test index d59a8f3d8ce..701586be325 100644 --- a/mysql-test/t/grant_explain_non_select.test +++ b/mysql-test/t/grant_explain_non_select.test @@ -8,6 +8,9 @@ # Save the initial number of concurrent sessions --source include/count_sessions.inc +set GLOBAL sql_mode=""; +set LOCAL sql_mode=""; + CREATE DATABASE privtest_db; CREATE TABLE privtest_db.t1 (a INT); @@ -254,5 +257,7 @@ DROP USER 'privtest'@localhost; USE test; DROP DATABASE privtest_db; +set GLOBAL sql_mode=default; +set LOCAL sql_mode=default; # Wait till we reached the initial number of concurrent sessions --source include/wait_until_count_sessions.inc diff --git a/mysql-test/t/grant_lowercase.test b/mysql-test/t/grant_lowercase.test index 4b27b4c674d..e31a72b890e 100644 --- a/mysql-test/t/grant_lowercase.test +++ b/mysql-test/t/grant_lowercase.test @@ -4,7 +4,7 @@ # # http://seclists.org/fulldisclosure/2012/Dec/4 # - +set sql_mode=""; # in acl_get(), check_grant_db(), mysql_grant() grant file on *.* to user1@localhost with grant option; grant select on `a%`.* to user1@localhost with grant option; @@ -28,4 +28,3 @@ delete from mysql.db where db like '=>%'; alter table mysql.host modify Db char(64) default ""; alter table mysql.db modify Db char(64) default "", modify User char(80) default ""; flush privileges; - diff --git a/mysql-test/t/grant_lowercase_fs.test b/mysql-test/t/grant_lowercase_fs.test index f57f950ec8c..92191829c58 100644 --- a/mysql-test/t/grant_lowercase_fs.test +++ b/mysql-test/t/grant_lowercase_fs.test @@ -6,6 +6,8 @@ # Bug#41049 does syntax "grant" case insensitive? # create database db1; +create user user_1@localhost; +create user USER_1@localhost; GRANT CREATE ON db1.* to user_1@localhost; GRANT SELECT ON db1.* to USER_1@localhost; diff --git a/mysql-test/t/help.test b/mysql-test/t/help.test index 71821e46771..43e61136e05 100644 --- a/mysql-test/t/help.test +++ b/mysql-test/t/help.test @@ -63,12 +63,14 @@ help 'impossible_function_1'; help 'impossible_category_1'; ############## +set sql_mode=""; --disable_warnings alter table mysql.help_relation engine=innodb; alter table mysql.help_keyword engine=innodb; alter table mysql.help_topic engine=innodb; alter table mysql.help_category engine=innodb; --enable_warnings +set sql_mode=default; ############## help 'function_of_my_dream'; diff --git a/mysql-test/t/host_cache_size_functionality.test b/mysql-test/t/host_cache_size_functionality.test index 1696c2fcb97..5a7376cc5eb 100644 --- a/mysql-test/t/host_cache_size_functionality.test +++ b/mysql-test/t/host_cache_size_functionality.test @@ -35,7 +35,7 @@ SELECT COUNT(@@GLOBAL.Host_Cache_Size); --echo 1 Expected #set @Default_host_cache_size=(select if(if(@@global.max_connections<500,128+@@global.max_connections,128+@@global.max_connections+floor((@@global.max_connections-500)/20))>2000,2000,if(@@global.max_connections<500,128+@@global.max_connections,128+@@global.max_connections+floor((@@global.max_connections-500)/20)))); -set @Default_host_cache_size=128; +set @Default_host_cache_size=279; select @@global.Host_Cache_Size=@Default_host_cache_size; --echo 1 Expected diff --git a/mysql-test/t/implicit_commit.test b/mysql-test/t/implicit_commit.test index 59f8dc3a44c..bcc43865395 100644 --- a/mysql-test/t/implicit_commit.test +++ b/mysql-test/t/implicit_commit.test @@ -2,6 +2,8 @@ source include/have_innodb.inc; source include/not_embedded.inc; source include/have_profiling.inc; +SET SQL_MODE=""; + SET GLOBAL EVENT_SCHEDULER = OFF; SET BINLOG_FORMAT = STATEMENT; diff --git a/mysql-test/t/index_intersect.test b/mysql-test/t/index_intersect.test index 001c698029e..19918c03479 100644 --- a/mysql-test/t/index_intersect.test +++ b/mysql-test/t/index_intersect.test @@ -428,7 +428,7 @@ CREATE TABLE t1 ( f5 int, PRIMARY KEY (f1), KEY (f4) -) ENGINE=InnoDB; +); INSERT INTO t1 VALUES (5,'H',1), (9,'g',0), (527,'i',0), (528,'y',1), (529,'S',6), diff --git a/mysql-test/t/information_schema.test b/mysql-test/t/information_schema.test index cd98bf3aa5c..f1e78441852 100644 --- a/mysql-test/t/information_schema.test +++ b/mysql-test/t/information_schema.test @@ -14,6 +14,8 @@ # Save the initial number of concurrent sessions --source include/count_sessions.inc +set global sql_mode=""; +set local sql_mode=""; # Test for information_schema.schemata & # show databases @@ -1907,3 +1909,4 @@ disconnect con1; # Wait till all disconnects are completed --source include/wait_until_count_sessions.inc +set global sql_mode=default; diff --git a/mysql-test/t/information_schema_db.test b/mysql-test/t/information_schema_db.test index 5b730db47cd..70ad9b5b920 100644 --- a/mysql-test/t/information_schema_db.test +++ b/mysql-test/t/information_schema_db.test @@ -7,6 +7,9 @@ -- source include/testdb_only.inc +set local sql_mode=""; +set global sql_mode=""; + --disable_warnings drop table if exists t1,t2; drop view if exists v1,v2; @@ -250,3 +253,5 @@ connection user1; disconnect user1; --source include/wait_until_disconnected.inc connection default; + +set global sql_mode=default; diff --git a/mysql-test/t/information_schema_routines.test b/mysql-test/t/information_schema_routines.test index c578176a85d..190410c2b82 100644 --- a/mysql-test/t/information_schema_routines.test +++ b/mysql-test/t/information_schema_routines.test @@ -54,6 +54,8 @@ # the routine; otherwise NULL), # DEFINER (shows the user who created the routine). ################################################################################ +set sql_mode=""; +set sql_mode=""; -- echo # ========== routines.1 ========== USE INFORMATION_SCHEMA; --replace_result ENGINE=MyISAM "" ENGINE=MARIA "" ENGINE=Aria "" " PAGE_CHECKSUM=1" "" " PAGE_CHECKSUM=0" "" diff --git a/mysql-test/t/innodb_load_xa.test b/mysql-test/t/innodb_load_xa.test index fe466e1f899..65b74120e8e 100644 --- a/mysql-test/t/innodb_load_xa.test +++ b/mysql-test/t/innodb_load_xa.test @@ -20,3 +20,6 @@ commit; show status like 'Handler_prepare'; drop table t1; uninstall plugin innodb; + +--source include/restart_mysqld.inc + diff --git a/mysql-test/t/insert_notembedded.test b/mysql-test/t/insert_notembedded.test index 4e5fe6f6755..713eaf5db40 100644 --- a/mysql-test/t/insert_notembedded.test +++ b/mysql-test/t/insert_notembedded.test @@ -1,5 +1,8 @@ -- source include/not_embedded.inc +set local sql_mode=""; +set global sql_mode=""; + --disable_warnings drop table if exists t1; --enable_warnings @@ -188,3 +191,6 @@ disconnect select; unlock tables; drop table t1; set low_priority_updates=default; + +set local sql_mode=default; +set global sql_mode=default; diff --git a/mysql-test/t/kill.test b/mysql-test/t/kill.test index b762900f1ec..aaf931a2e7f 100644 --- a/mysql-test/t/kill.test +++ b/mysql-test/t/kill.test @@ -10,6 +10,8 @@ -- source include/not_embedded.inc -- source include/have_debug_sync.inc -- source include/not_threadpool.inc +set local sql_mode=""; +set global sql_mode=""; --disable_warnings SET DEBUG_SYNC = 'RESET'; @@ -675,3 +677,5 @@ DROP USER u1@localhost; SET DEBUG_SYNC = 'RESET'; DROP FUNCTION MY_KILL; + +set global sql_mode=default; diff --git a/mysql-test/t/lock_multi.test b/mysql-test/t/lock_multi.test index 753b6bd6379..efd0fe5d630 100644 --- a/mysql-test/t/lock_multi.test +++ b/mysql-test/t/lock_multi.test @@ -324,9 +324,11 @@ DROP DATABASE mysqltest_1; # connection locker; # Disable warnings to allow test to run also without InnoDB +set sql_mode=""; --disable_warnings create table t1 (f1 int(12) unsigned not null auto_increment, primary key(f1)) engine=innodb; --enable_warnings +set sql_mode=default; lock tables t1 write; connection writer; send diff --git a/mysql-test/t/log_tables.test b/mysql-test/t/log_tables.test index 28f6e6dd829..a4158ba6a24 100644 --- a/mysql-test/t/log_tables.test +++ b/mysql-test/t/log_tables.test @@ -6,6 +6,7 @@ # check that CSV engine was compiled in --source include/have_csv.inc +SET SQL_MODE=""; SET @old_general_log_state = @@global.general_log; SET @old_log_output= @@global.log_output; SET @old_slow_query_log= @@global.slow_query_log; diff --git a/mysql-test/t/lowercase_fs_off.test b/mysql-test/t/lowercase_fs_off.test index 1be0351f9bc..cb5cd711c7f 100644 --- a/mysql-test/t/lowercase_fs_off.test +++ b/mysql-test/t/lowercase_fs_off.test @@ -5,6 +5,8 @@ -- source include/have_case_sensitive_file_system.inc -- source include/not_embedded.inc +set GLOBAL sql_mode=""; +set LOCAL sql_mode=""; connect (master,localhost,root,,); connection master; create database d1; @@ -102,3 +104,5 @@ CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET new.a= 1; RENAME TABLE t1 TO T1; ALTER TABLE T1 RENAME t1; DROP TABLE t1; + +set GLOBAL sql_mode=default; diff --git a/mysql-test/t/lowercase_table_grant.test b/mysql-test/t/lowercase_table_grant.test index 7449231fca5..2f98f570e29 100644 --- a/mysql-test/t/lowercase_table_grant.test +++ b/mysql-test/t/lowercase_table_grant.test @@ -3,6 +3,7 @@ # Test of grants when lower_case_table_names is on use mysql; +set sql_mode=""; # mixed-case database name for testing create database MYSQLtest; diff --git a/mysql-test/t/lowercase_table_qcache.test b/mysql-test/t/lowercase_table_qcache.test index e63ad3b2c16..ab05755bec1 100644 --- a/mysql-test/t/lowercase_table_qcache.test +++ b/mysql-test/t/lowercase_table_qcache.test @@ -3,6 +3,8 @@ # Test of query cache with --lower-case-table-names # set GLOBAL query_cache_size=1355776; +set GLOBAL query_cache_type=ON; +set LOCAL query_cache_type=ON; --disable_warnings drop database if exists MySQLtesT; @@ -26,6 +28,7 @@ select * from MySQL.db; enable_result_log; show status like "Qcache_queries_in_cache"; -set GLOBAL query_cache_size=0; +set GLOBAL query_cache_size=default; +set GLOBAL query_cache_type=default; # End of 4.1 tests diff --git a/mysql-test/t/max_statement_time.test b/mysql-test/t/max_statement_time.test index a25a97e1db3..0882daff139 100644 --- a/mysql-test/t/max_statement_time.test +++ b/mysql-test/t/max_statement_time.test @@ -79,6 +79,7 @@ DROP TABLE t1; --echo # MAX_STATEMENT_TIME account resource --echo +set statement sql_mode="" for GRANT USAGE ON *.* TO user1@localhost WITH MAX_STATEMENT_TIME 1.005; --echo # con1 diff --git a/mysql-test/t/mdev-504.test b/mysql-test/t/mdev-504.test index bc38e99067a..fb5c7666d33 100644 --- a/mysql-test/t/mdev-504.test +++ b/mysql-test/t/mdev-504.test @@ -1,5 +1,7 @@ --disable_ps_protocol +SET GLOBAL net_write_timeout = 900; + CREATE TABLE A ( pk INTEGER AUTO_INCREMENT PRIMARY KEY, fdate DATE @@ -74,4 +76,4 @@ DROP TABLE A; DROP PROCEDURE p_analyze; DROP FUNCTION rnd3; SET GLOBAL use_stat_tables = DEFAULT; - +SET GLOBAL net_write_timeout = DEFAULT; diff --git a/mysql-test/t/merge.test b/mysql-test/t/merge.test index 2697e3b9a5f..5bfe22697dd 100644 --- a/mysql-test/t/merge.test +++ b/mysql-test/t/merge.test @@ -1895,13 +1895,14 @@ DROP TABLE t1; --disable_warnings drop table if exists t_parent; --enable_warnings -set @save_table_definition_cache=@@global.table_definition_cache; +set @save_table_open_cache=@@global.table_open_cache; --echo # ---echo # Set @@global.table_definition_cache to minimum +--echo # Set @@global.table_open_cache to minimum --echo # -set @@global.table_definition_cache=400; +set @@global.table_open_cache=400; +select @@table_open_cache; set @a=null; -let $1 = 400; +let $1 = `select @@table_open_cache;`; --echo # --echo # Create 400 merge children --echo # @@ -1935,7 +1936,7 @@ deallocate prepare stmt; --echo # --echo # Cleanup --echo # -let $1 = 400; +let $1 = `select @@table_open_cache;`; --disable_query_log while ($1) { @@ -1944,7 +1945,7 @@ while ($1) } --enable_query_log drop table t_parent; -set @@global.table_definition_cache=@save_table_definition_cache; +set @@global.table_open_cache=@save_table_open_cache; # # WL#4144 - Lock MERGE engine children diff --git a/mysql-test/t/multi_update.test b/mysql-test/t/multi_update.test index c013938416e..8c620ed2ce4 100644 --- a/mysql-test/t/multi_update.test +++ b/mysql-test/t/multi_update.test @@ -378,6 +378,7 @@ create database mysqltest; create table mysqltest.t1 (a int, b int, primary key (a)); create table mysqltest.t2 (a int, b int, primary key (a)); create table mysqltest.t3 (a int, b int, primary key (a)); +create user mysqltest_1@localhost; grant select on mysqltest.* to mysqltest_1@localhost; grant update on mysqltest.t1 to mysqltest_1@localhost; connect (user1,localhost,mysqltest_1,,mysqltest,$MASTER_MYPORT,$MASTER_MYSOCK); diff --git a/mysql-test/t/merge_recover-master.opt b/mysql-test/t/myisam_recover-master.opt index fca5ea079e3..fca5ea079e3 100644 --- a/mysql-test/t/merge_recover-master.opt +++ b/mysql-test/t/myisam_recover-master.opt diff --git a/mysql-test/t/merge_recover.test b/mysql-test/t/myisam_recover.test index f2cb204eeb6..49fe9c33460 100644 --- a/mysql-test/t/merge_recover.test +++ b/mysql-test/t/myisam_recover.test @@ -1,5 +1,9 @@ +--source include/count_sessions.inc + +--echo # +--echo # Tests for corrupted MyISAM tables and MyISAMMRG tables with corrupted +--echo # children.. --echo # ---echo # Test of MyISAM MRG tables with corrupted children. --echo # Run with --myisam-recover=force option. --echo # --echo # Preparation: we need to make sure that the merge parent @@ -57,10 +61,10 @@ eval $lock; --echo # connection default; --echo # ---echo # We have to disable the ps-protocol, to avoid +--echo # We have to disable the ps-protocol, to avoid --echo # "Prepared statement needs to be re-prepared" errors --echo # -- table def versions change all the time with full table cache. ---echo # +--echo # --disable_ps_protocol --disable_warnings drop table if exists t1, t1_mrg, t1_copy; @@ -69,12 +73,12 @@ let $MYSQLD_DATADIR=`select @@datadir`; --echo # --echo # Prepare a MERGE engine table, that refers to a corrupted --echo # child. ---echo # +--echo # create table t1 (a int, key(a)) engine=myisam; create table t1_mrg (a int) union (t1) engine=merge; --echo # --echo # Create a table with a corrupted index file: ---echo # save an old index file, insert more rows, +--echo # save an old index file, insert more rows, --echo # overwrite the new index file with the old one. --echo # insert into t1 (a) values (1), (2), (3); @@ -111,3 +115,66 @@ set @@global.table_open_cache=default; disconnect con1; connection default; --enable_ps_protocol + +--echo # +--echo # 18075170 - sql node restart required to avoid deadlock after +--echo # restore +--echo # +--echo # Check that auto-repair for MyISAM tables can now happen in the +--echo # middle of transaction, without aborting it. +--enable_prepare_warnings + +connection default; + +create table t1 (a int, key(a)) engine=myisam; +create table t2 (a int); +insert into t2 values (1); + +--echo # Create a table with a corrupted index file: +--echo # save an old index file, insert more rows, +--echo # overwrite the new index file with the old one. +insert into t1 (a) values (1); +flush table t1; +--copy_file $MYSQLD_DATADIR/test/t1.MYI $MYSQLD_DATADIR/test/t1_copy.MYI +insert into t1 (a) values (4); +flush table t1; +--remove_file $MYSQLD_DATADIR/test/t1.MYI +--copy_file $MYSQLD_DATADIR/test/t1_copy.MYI $MYSQLD_DATADIR/test/t1.MYI +--remove_file $MYSQLD_DATADIR/test/t1_copy.MYI + +--echo # Check table is needed to mark the table as crashed. +check table t1; + +--echo # At this point we have a corrupt t1 +set autocommit = 0; +select * from t2; +--echo # Without fix select from t1 will break the transaction. After the fix +--echo # transaction should be active and should hold lock on table t2. Alter +--echo # table from con2 will wait only if the transaction is not broken. +--replace_regex /'.*[\/\\]/'/ +select * from t1; + +connect(con2, localhost, root); +--SEND ALTER TABLE t2 ADD val INT + +connection default; +--echo # With fix we should have alter table waiting for t2 lock here. +let $wait_condition= + SELECT count(*) = 1 FROM information_schema.processlist WHERE state + LIKE "Waiting%" AND info = "ALTER TABLE t2 ADD val INT"; + +--source include/wait_condition.inc +ROLLBACK; +SET autocommit = 1; + +connection con2; +--REAP + +connection default; +disconnect con2; + +--echo # Cleanup +drop table t1, t2; + +# Wait till all disconnects are completed +-- source include/wait_until_count_sessions.inc diff --git a/mysql-test/t/mysql.test b/mysql-test/t/mysql.test index c328ab19ffd..f1813dd0ca4 100644 --- a/mysql-test/t/mysql.test +++ b/mysql-test/t/mysql.test @@ -4,6 +4,8 @@ # # Testing the MySQL command line client(mysql) # +set GLOBAL sql_mode=""; +set LOCAL sql_mode=""; --disable_warnings drop table if exists t1,t2,t3; @@ -632,5 +634,6 @@ EOF --exec $MYSQL --default-character-set=utf8 < $MYSQLTEST_VARDIR/tmp/mdev-6572.sql 2>&1 --remove_file $MYSQLTEST_VARDIR/tmp/mdev-6572.sql +set GLOBAL sql_mode=default; --echo --echo End of tests diff --git a/mysql-test/t/mysql_comments.test b/mysql-test/t/mysql_comments.test index 1f997aeb1ab..7b00f17e259 100644 --- a/mysql-test/t/mysql_comments.test +++ b/mysql-test/t/mysql_comments.test @@ -10,6 +10,8 @@ # # ######################################################################## +set global sql_mode=""; + # # Bug #11230: Keeping comments when storing stored procedures # @@ -34,4 +36,6 @@ drop trigger if exists t1_bi; --echo "Pass 2 : --enable-comments" --exec $MYSQL --enable-comments test 2>&1 < "./t/mysql_comments.sql" + +set global sql_mode=default; --echo End of 5.0 tests diff --git a/mysql-test/t/mysql_upgrade.test b/mysql-test/t/mysql_upgrade.test index 8c641428353..552bb12ec74 100644 --- a/mysql-test/t/mysql_upgrade.test +++ b/mysql-test/t/mysql_upgrade.test @@ -2,6 +2,8 @@ -- source include/have_working_dns.inc -- source include/have_innodb.inc +set sql_mode=""; + # # Basic test that we can run mysql_upgrde and that it finds the # expected binaries it uses. @@ -15,7 +17,6 @@ file_exists $MYSQLD_DATADIR/mysql_upgrade_info; --echo Run it again - should say already completed --replace_result $MYSQL_SERVER_VERSION VERSION ---error 1 --exec $MYSQL_UPGRADE 2>&1 # It should have created a file in the MySQL Servers datadir @@ -159,4 +160,5 @@ SELECT grantor FROM mysql.tables_priv WHERE db = 'mysql' AND table_name = 'user' DROP USER very_long_user_name_number_1, very_long_user_name_number_2, even_longer_user_name_number_3_to_test_the_grantor_and_definer_field_length@localhost; DROP PROCEDURE test.pr; +set sql_mode=default; --echo End of tests diff --git a/mysql-test/t/mysqlbinlog-innodb.test b/mysql-test/t/mysqlbinlog-innodb.test index 49702e8db38..c8bf8095705 100644 --- a/mysql-test/t/mysqlbinlog-innodb.test +++ b/mysql-test/t/mysqlbinlog-innodb.test @@ -1,7 +1,6 @@ -- source include/have_binlog_format_statement.inc -- source include/have_log_bin.inc -- source include/have_innodb.inc - # # MBug#702303: Spurious `use` statements in output from mysqlbinlog --rewrite-db="a->b" # diff --git a/mysql-test/t/mysqldump-max.test b/mysql-test/t/mysqldump-max.test index d0a4870ba31..3f73f046959 100644 --- a/mysql-test/t/mysqldump-max.test +++ b/mysql-test/t/mysqldump-max.test @@ -1193,12 +1193,16 @@ DROP TABLE t1; DROP TABLE t2; --exec $MYSQL test < $MYSQLTEST_VARDIR/tmp/mwl136.sql ---replace_regex /\/\* xid=.* \*\//\/* XID *\// /Server ver: .*, Binlog ver: .*/Server ver: #, Binlog ver: #/ /table_id: [0-9]+/table_id: #/ -SHOW BINLOG EVENTS LIMIT 7,3; +let $binlog_limit= 7, 3; +source include/show_binlog_events.inc; +source include/binlog_start_pos.inc; +let _BINLOG_START_POS= $binlog_start_pos; --perl my $f= "$ENV{MYSQLTEST_VARDIR}/tmp/mwl136.sql"; +my $pos=$ENV{_BINLOG_START_POS} + 691; open F, '<', $f or die "Failed to open $f: $!\n"; while (<F>) { + s/$pos/<pos>/; print if /CHANGE MASTER TO/; } EOF diff --git a/mysql-test/t/mysqldump.test b/mysql-test/t/mysqldump.test index 8461ebff412..c3267ad7a40 100644 --- a/mysql-test/t/mysqldump.test +++ b/mysql-test/t/mysqldump.test @@ -1,5 +1,6 @@ call mtr.add_suppression("@003f.frm' \\(errno: 22\\)"); - +#select * from mysql.user; +#checksum table mysql.user; # Embedded server doesn't support external clients --source include/not_embedded.inc @@ -139,7 +140,7 @@ create table t1(a int); set global sql_mode='ANSI_QUOTES'; --exec $MYSQL_DUMP --comments=0 test --exec $MYSQL_DUMP --comments=0 --compatible=ansi test -set global sql_mode=''; +set global sql_mode=default; drop table t1; --echo # @@ -2488,3 +2489,5 @@ drop table t1; select * from t1; drop table t1; --remove_file $MYSQLTEST_VARDIR/tmp/mysqldump-test.out +#select * from mysql.user; +#checksum table mysql.user; diff --git a/mysql-test/t/openssl_1.test b/mysql-test/t/openssl_1.test index e36f106a5be..8c81bdc9bbb 100644 --- a/mysql-test/t/openssl_1.test +++ b/mysql-test/t/openssl_1.test @@ -6,6 +6,8 @@ # Save the initial number of concurrent sessions --source include/count_sessions.inc +set local sql_mode=""; +set global sql_mode=""; --disable_warnings drop table if exists t1; @@ -263,6 +265,7 @@ disconnect con1; connection default; DROP USER bug42158@localhost; +set global sql_mode=default; --echo End of 5.1 tests # Wait till we reached the initial number of concurrent sessions diff --git a/mysql-test/t/openssl_6975.test b/mysql-test/t/openssl_6975.test index bc6397c5c28..88fe17d0621 100644 --- a/mysql-test/t/openssl_6975.test +++ b/mysql-test/t/openssl_6975.test @@ -7,7 +7,9 @@ source include/have_ssl_communication.inc; # this is OpenSSL test. +create user ssl_sslv3@localhost; grant select on test.* to ssl_sslv3@localhost require cipher "RC4-SHA"; +create user ssl_tls12@localhost; grant select on test.* to ssl_tls12@localhost require cipher "AES128-SHA256"; let $mysql=$MYSQL --ssl-key=$MYSQL_TEST_DIR/std_data/client-key.pem --ssl-cert=$MYSQL_TEST_DIR/std_data/client-cert.pem -e "SHOW STATUS LIKE 'ssl_Cipher'" 2>&1; diff --git a/mysql-test/t/partition_grant.test b/mysql-test/t/partition_grant.test index cf944e507fd..a5df218bb10 100644 --- a/mysql-test/t/partition_grant.test +++ b/mysql-test/t/partition_grant.test @@ -21,6 +21,7 @@ insert into t1 values (1),(2); # cleanup possible grants for mysqltest_1 left by previous tests # and ensure consistent results of SHOW GRANTS command below. --disable_warnings +create user mysqltest_1@localhost; grant usage on *.* to mysqltest_1@localhost; revoke all privileges on *.* from mysqltest_1@localhost; --enable_warnings diff --git a/mysql-test/t/partition_hash.test b/mysql-test/t/partition_hash.test index 5b9aa5707b9..c8f473a0477 100644 --- a/mysql-test/t/partition_hash.test +++ b/mysql-test/t/partition_hash.test @@ -135,12 +135,14 @@ drop table t1; # # Disable warnings to allow this test case to run without # the Blackhole storage engine. +set sql_mode=""; --disable_warnings CREATE TABLE t1 (s1 int) ENGINE=BLACKHOLE PARTITION BY HASH (s1); --enable_warnings --error 0,ER_BINLOG_LOGGING_IMPOSSIBLE INSERT INTO t1 VALUES (0); DROP TABLE t1; +set sql_mode=default; # # BUG 18423 Hash partitioning can lose rows in some queries diff --git a/mysql-test/t/partition_innodb.test b/mysql-test/t/partition_innodb.test index 2d90764da0d..b982ab8d2e8 100644 --- a/mysql-test/t/partition_innodb.test +++ b/mysql-test/t/partition_innodb.test @@ -478,6 +478,7 @@ drop table t1; # # Bug 20397: Partitions: Crash when using non-existing engine # +SET SQL_MODE=""; create table t1 (a int) engine = x partition by key (a); @@ -492,6 +493,7 @@ partition by list (a) alter table t1 engine = x; show create table t1; drop table t1; +SET SQL_MODE=default; # BUG#26117: index_merge sort-union over partitioned table crashes diff --git a/mysql-test/t/plugin.test b/mysql-test/t/plugin.test index 13e2c71fbc4..68c4d5afd64 100644 --- a/mysql-test/t/plugin.test +++ b/mysql-test/t/plugin.test @@ -1,12 +1,12 @@ --source include/not_windows_embedded.inc --source include/have_example_plugin.inc +--error ER_UNKNOWN_STORAGE_ENGINE CREATE TABLE t1(a int) ENGINE=EXAMPLE; -DROP TABLE t1; INSTALL PLUGIN example SONAME 'ha_example'; --replace_regex /\.dll/.so/ ---error 1125 +--error ER_PLUGIN_INSTALLED INSTALL PLUGIN EXAMPLE SONAME 'ha_example'; UNINSTALL PLUGIN example; diff --git a/mysql-test/t/plugin_auth.test b/mysql-test/t/plugin_auth.test index 5e620654e0a..c5d96e0d612 100644 --- a/mysql-test/t/plugin_auth.test +++ b/mysql-test/t/plugin_auth.test @@ -3,6 +3,9 @@ --source include/mysql_upgrade_preparation.inc --source include/have_innodb.inc +SET GLOBAL SQL_MODE=""; +SET LOCAL SQL_MODE=""; + query_vertical SELECT PLUGIN_STATUS, PLUGIN_TYPE, PLUGIN_DESCRIPTION FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME='test_plugin_server'; @@ -60,7 +63,7 @@ disconnect plug_con_rightp; --echo ## test no_auto_create_user sql mode with plugin users SET @@sql_mode=no_auto_create_user; GRANT INSERT ON TEST.* TO grant_user IDENTIFIED WITH 'test_plugin_server'; -SET @@sql_mode=default; +SET @@sql_mode=""; DROP USER grant_user; --echo ## test utf-8 user name @@ -580,4 +583,5 @@ disconnect bug12818542_con2; DROP USER bug12818542@localhost; DROP USER bug12818542_dest@localhost; +SET GLOBAL SQL_MODE=default; --echo End of 5.5 tests diff --git a/mysql-test/t/plugin_auth_qa.test b/mysql-test/t/plugin_auth_qa.test index 3a3921510e0..fce72a66702 100644 --- a/mysql-test/t/plugin_auth_qa.test +++ b/mysql-test/t/plugin_auth_qa.test @@ -3,6 +3,8 @@ --source include/have_plugin_auth.inc --source include/not_embedded.inc +set sql_mode=""; + CREATE DATABASE test_user_db; --sorted_result @@ -332,5 +334,6 @@ DROP USER grant_user,plug_dest; set @@sql_mode= @save_sql_mode; # DROP DATABASE test_user_db; + --exit diff --git a/mysql-test/t/plugin_not_embedded.test b/mysql-test/t/plugin_not_embedded.test index 3e3bd6e86ca..3b4129b6b6b 100644 --- a/mysql-test/t/plugin_not_embedded.test +++ b/mysql-test/t/plugin_not_embedded.test @@ -5,6 +5,7 @@ --echo # Bug#51770: UNINSTALL PLUGIN requires no privileges --echo # +CREATE USER bug51770@localhost; GRANT INSERT ON mysql.plugin TO bug51770@localhost; connect(con1,localhost,bug51770,,); --replace_regex /\.dll/.so/ @@ -29,4 +30,3 @@ let $path = `select CONCAT_WS('/', '..', '$HA_EXAMPLE_SO')`; --replace_regex /\.dll/.so/ --error ER_UDF_NO_PATHS eval INSTALL PLUGIN example SONAME '$path'; - diff --git a/mysql-test/t/ps.test b/mysql-test/t/ps.test index 84c9874260a..96dd88f5eb6 100644 --- a/mysql-test/t/ps.test +++ b/mysql-test/t/ps.test @@ -1,5 +1,6 @@ -- source include/not_embedded.inc -- source include/have_log_bin.inc + call mtr.add_suppression('Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.'); # # SQL Syntax for Prepared Statements test diff --git a/mysql-test/t/query_cache.test b/mysql-test/t/query_cache.test index 387dcd205bd..a97b0b1c815 100644 --- a/mysql-test/t/query_cache.test +++ b/mysql-test/t/query_cache.test @@ -4,7 +4,16 @@ # # Tests with query cache # +SET GLOBAL query_cache_type= Off; +SET LOCAL query_cache_type= Off; +SET LOCAL query_cache_type= 0; +--error ER_QUERY_CACHE_IS_GLOBALY_DISABLED +SET LOCAL query_cache_type= on; +--error ER_QUERY_CACHE_IS_GLOBALY_DISABLED +SET LOCAL query_cache_type= 1; set GLOBAL query_cache_size=1355776; +SET GLOBAL query_cache_type= ON; +SET LOCAL query_cache_type= ON; # Reset query cache variables. @@ -1204,9 +1213,8 @@ set global query_cache_size=102400; set global query_cache_size=0; set global query_cache_type=0; show status like 'Qcache_free_blocks'; - ---error ER_QUERY_CACHE_DISABLED set global query_cache_size=102400; +show status like 'Qcache_free_blocks'; set global query_cache_type=on; set session query_cache_type=on; @@ -1260,6 +1268,8 @@ set GLOBAL query_cache_limit=default; set GLOBAL query_cache_min_res_unit=default; set GLOBAL query_cache_size=default; set local query_cache_type=default; +set GLOBAL query_cache_type=on; +set local query_cache_type=on; # # Bug#33756 - query cache with concurrent_insert=0 appears broken diff --git a/mysql-test/t/query_cache_debug.test b/mysql-test/t/query_cache_debug.test index 854af85f3fb..5eba778cf9e 100644 --- a/mysql-test/t/query_cache_debug.test +++ b/mysql-test/t/query_cache_debug.test @@ -3,6 +3,7 @@ --source include/have_debug_sync.inc --source include/long_test.inc +set global query_cache_type= ON; # # Bug #30887 Server crashes on SET GLOBAL query_cache_size=0 # diff --git a/mysql-test/t/query_cache_merge.test b/mysql-test/t/query_cache_merge.test index 189de8e4092..3046c4ecbfe 100644 --- a/mysql-test/t/query_cache_merge.test +++ b/mysql-test/t/query_cache_merge.test @@ -1,5 +1,8 @@ # Test query cache with many tables +SET GLOBAL query_cache_type=ON; +SET LOCAL query_cache_type=ON; + --source include/have_query_cache.inc let $LIMIT=`SHOW VARIABLES LIKE 'open_files_limit'`; let $MIN_LIMIT=600; @@ -102,7 +105,8 @@ while ($c) dec $c; } -SET @@global.query_cache_size = 0; +SET @@global.query_cache_size = default; SET @@global.table_definition_cache = @save_table_definition_cache; +SET GLOBAL query_cache_type=default; --echo End of 5.1 tests diff --git a/mysql-test/t/query_cache_notembedded-master.opt b/mysql-test/t/query_cache_notembedded-master.opt new file mode 100644 index 00000000000..871d6965726 --- /dev/null +++ b/mysql-test/t/query_cache_notembedded-master.opt @@ -0,0 +1 @@ +--query-cache-size=1355776 diff --git a/mysql-test/t/query_cache_notembedded.test b/mysql-test/t/query_cache_notembedded.test index edb6db43516..dff6c2a139b 100644 --- a/mysql-test/t/query_cache_notembedded.test +++ b/mysql-test/t/query_cache_notembedded.test @@ -8,7 +8,12 @@ # # Tests with query cache # -set GLOBAL query_cache_size=1355776; +set @sql_mode_save=@@global.sql_mode; +set @query_cache_type_save=@@global.query_cache_type; +set @query_cache_size_save=@@global.query_cache_size; + +set GLOBAL sql_mode=""; +set LOCAL sql_mode=""; # Reset query cache variables. @@ -270,7 +275,8 @@ DROP DATABASE bug30269; disconnect bug30269; DROP USER 'bug30269'@'localhost'; -set GLOBAL query_cache_type=default; +set GLOBAL query_cache_type=ON; +set LOCAL query_cache_type=ON; set GLOBAL query_cache_limit=default; set GLOBAL query_cache_min_res_unit=default; set GLOBAL query_cache_size=default; @@ -324,3 +330,7 @@ delimiter ;| # Wait till we reached the initial number of concurrent sessions --source include/wait_until_count_sessions.inc +SET GLOBAL query_cache_size=@query_cache_size_save; +SET GLOBAL query_cache_type=@query_cache_type_save; +set GLOBAL sql_mode=@sql_mode_save; + diff --git a/mysql-test/t/query_cache_with_views.test b/mysql-test/t/query_cache_with_views.test index 97e37c53aa3..9b03d0e99d0 100644 --- a/mysql-test/t/query_cache_with_views.test +++ b/mysql-test/t/query_cache_with_views.test @@ -6,7 +6,8 @@ drop table if exists t1,t2,v1,v2,v3; drop view if exists t1,t2,v1,v2,v3; --enable_warnings - +set GLOBAL query_cache_type=ON; +set LOCAL query_cache_type=ON; set GLOBAL query_cache_size=1355776; flush status; create table t1 (a int, b int); @@ -145,3 +146,4 @@ SET AUTOCOMMIT=DEFAULT; # Reset default environment. set GLOBAL query_cache_size=default; +set GLOBAL query_cache_type=default; diff --git a/mysql-test/t/read_only.test b/mysql-test/t/read_only.test index d0f397697d5..3e5d4fc8ce1 100644 --- a/mysql-test/t/read_only.test +++ b/mysql-test/t/read_only.test @@ -3,7 +3,6 @@ # should work with embedded server after mysqltest is fixed --source include/not_embedded.inc - set @start_read_only= @@global.read_only; # Save the initial number of concurrent sessions @@ -16,6 +15,7 @@ DROP TABLE IF EXISTS t1,t2,t3; # READ_ONLY does nothing to SUPER users # so we use a non-SUPER one: +create user test@localhost; grant CREATE, SELECT, DROP on *.* to test@localhost; --echo connect (con1,localhost,test,,test); @@ -278,6 +278,7 @@ delete from mysql.tables_priv where User like 'mysqltest_%'; delete from mysql.columns_priv where User like 'mysqltest_%'; flush privileges; +create user `mysqltest_u1`@`%`; grant all on mysqltest_db2.* to `mysqltest_u1`@`%`; create database mysqltest_db1; grant all on mysqltest_db1.* to `mysqltest_u1`@`%`; @@ -364,4 +365,3 @@ DROP TABLE t1; # Wait till all disconnects are completed --source include/wait_until_count_sessions.inc - diff --git a/mysql-test/t/read_only_innodb.test b/mysql-test/t/read_only_innodb.test index de237fecbb6..f404ba17579 100644 --- a/mysql-test/t/read_only_innodb.test +++ b/mysql-test/t/read_only_innodb.test @@ -13,6 +13,7 @@ DROP TABLE IF EXISTS table_11733 ; # READ_ONLY does nothing to SUPER users # so we use a non-SUPER one: +create user test@localhost; grant CREATE, SELECT, DROP on *.* to test@localhost; connect (con1,localhost,test,,test); @@ -47,6 +48,7 @@ disconnect con1; # Bug #35732: read-only blocks SELECT statements in InnoDB # # Test 1: read only mode +create user test@localhost; GRANT CREATE, SELECT, DROP ON *.* TO test@localhost; connect(con1, localhost, test, , test); @@ -251,3 +253,4 @@ connection default; SET GLOBAL READ_ONLY = OFF; DROP USER bug33669@localhost; DROP DATABASE db1; + diff --git a/mysql-test/t/selectivity.test b/mysql-test/t/selectivity.test index c46ff69295f..92e38604a30 100644 --- a/mysql-test/t/selectivity.test +++ b/mysql-test/t/selectivity.test @@ -848,6 +848,7 @@ set optimizer_use_condition_selectivity=1; explain extended select * from t1, t2, t1 as t3 where t1.b=t2.c and t2.d=t3.a and t3.b<5 and t1.a < 2000; +--sorted_result select * from t1, t2, t1 as t3 where t1.b=t2.c and t2.d=t3.a and t3.b<5 and t1.a < 2000; @@ -855,6 +856,7 @@ set optimizer_use_condition_selectivity=3; explain extended select * from t1, t2, t1 as t3 where t1.b=t2.c and t2.d=t3.a and t3.b<5 and t1.a < 2000; +--sorted_result select * from t1, t2, t1 as t3 where t1.b=t2.c and t2.d=t3.a and t3.b<5 and t1.a < 2000; @@ -946,11 +948,11 @@ drop table t0,t1,t2; --echo # Bug mdev-7316: a conjunct in WHERE with selectivity == 0 --echo # -CREATE TABLE t1 (a varchar(16), b int, PRIMARY KEY(a), KEY(b)) ENGINE=INNODB; +CREATE TABLE t1 (a varchar(16), b int, PRIMARY KEY(a), KEY(b)); INSERT INTO t1 VALUES ('USAChinese',10), ('USAEnglish',20), ('USAFrench',30); -CREATE TABLE t2 (i int) ENGINE=INNODB; +CREATE TABLE t2 (i int); INSERT INTO t2 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(1),(2),(3),(4); diff --git a/mysql-test/t/servers.test b/mysql-test/t/servers.test index 37817937ab5..645206c376e 100644 --- a/mysql-test/t/servers.test +++ b/mysql-test/t/servers.test @@ -1,4 +1,5 @@ # Generic tests for servers (do not require FEDERATED) +set sql_mode=""; --echo # --echo # MDEV-4594 - CREATE SERVER crashes embedded @@ -21,5 +22,3 @@ DROP SERVER s1; CREATE SERVER s1 FOREIGN DATA WRAPPER mysql OPTIONS(SOCKET 'bar'); SELECT * FROM mysql.servers; DROP SERVER s1; - - diff --git a/mysql-test/t/show_check.test b/mysql-test/t/show_check.test index 8a3d9478946..d7bfb3a973e 100644 --- a/mysql-test/t/show_check.test +++ b/mysql-test/t/show_check.test @@ -322,8 +322,11 @@ create database mysqltest; show create database mysqltest; create table mysqltest.t1(a int); insert into mysqltest.t1 values(1); +create user mysqltest_1@localhost; grant select on `mysqltest`.* to mysqltest_1@localhost; +create user mysqltest_2@localhost; grant usage on `mysqltest`.* to mysqltest_2@localhost; +create user mysqltest_3@localhost; grant drop on `mysqltest`.* to mysqltest_3@localhost; connect (con1,localhost,mysqltest_1,,mysqltest); @@ -990,6 +993,7 @@ create database mysqltest; show create database mysqltest; create table mysqltest.t1(a int); insert into mysqltest.t1 values(1); +create user mysqltest_4@localhost; grant select on `mysqltest`.`t1` to mysqltest_4@localhost; connect (con4,localhost,mysqltest_4,,mysqltest); connection con4; diff --git a/mysql-test/t/show_explain.test b/mysql-test/t/show_explain.test index 83c05d93007..6615ff66737 100644 --- a/mysql-test/t/show_explain.test +++ b/mysql-test/t/show_explain.test @@ -753,6 +753,7 @@ drop table t1,t3,t4; --echo # --echo # ---------- SHOW EXPLAIN and permissions ----------------- --echo # +create user test2@localhost; grant ALL on test.* to test2@localhost; # Give the user SUPER privilege so it can set debug_dbug variable. @@ -1201,4 +1202,3 @@ drop table t0,t1,t2; connection default; disconnect con1; set debug_sync='RESET'; - diff --git a/mysql-test/t/single_delete_update.test b/mysql-test/t/single_delete_update.test index e3ee13f891c..ec939641ea3 100644 --- a/mysql-test/t/single_delete_update.test +++ b/mysql-test/t/single_delete_update.test @@ -452,30 +452,3 @@ SELECT * FROM t2 WHERE c = 10 ORDER BY a DESC, b DESC; DROP TABLE t1, t2; - ---echo # ---echo # Bug #53742: UPDATEs have no effect after applying patch for bug 36569 ---echo # - ---disable_warnings -CREATE TABLE t1 ( - pk INT NOT NULL AUTO_INCREMENT, - c1_idx CHAR(1) DEFAULT 'y', - c2 INT, - PRIMARY KEY (pk), - INDEX c1_idx (c1_idx) -) ENGINE=InnoDB; ---enable_warnings - -INSERT INTO t1 VALUES (), (), (), (); - -SELECT * FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2; -UPDATE t1 SET c2 = 0 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2; -SELECT * FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2; -SELECT * FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC; - -DELETE FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2; -SELECT * FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC; - -DROP TABLE t1; - diff --git a/mysql-test/t/single_delete_update_innodb.test b/mysql-test/t/single_delete_update_innodb.test new file mode 100644 index 00000000000..7e7817d4dd6 --- /dev/null +++ b/mysql-test/t/single_delete_update_innodb.test @@ -0,0 +1,30 @@ +# +# Single table specific update/delete tests (mysql_update and mysql_delete) +# +--source include/have_innodb.inc + + +--echo # +--echo # Bug #53742: UPDATEs have no effect after applying patch for bug 36569 +--echo # + +CREATE TABLE t1 ( + pk INT NOT NULL AUTO_INCREMENT, + c1_idx CHAR(1) DEFAULT 'y', + c2 INT, + PRIMARY KEY (pk), + INDEX c1_idx (c1_idx) +) ENGINE=InnoDB; + +INSERT INTO t1 VALUES (), (), (), (); + +SELECT * FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2; +UPDATE t1 SET c2 = 0 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2; +SELECT * FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2; +SELECT * FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC; + +DELETE FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2; +SELECT * FROM t1 WHERE c1_idx = 'y' ORDER BY pk DESC; + +DROP TABLE t1; + diff --git a/mysql-test/t/skip_name_resolve.test b/mysql-test/t/skip_name_resolve.test index a14f3af5cd6..b0c5118f970 100644 --- a/mysql-test/t/skip_name_resolve.test +++ b/mysql-test/t/skip_name_resolve.test @@ -5,6 +5,7 @@ --source include/count_sessions.inc # Bug#8471 IP address with mask fail when skip-name-resolve is on +CREATE USER mysqltest_1@'127.0.0.1/255.255.255.255'; GRANT ALL ON test.* TO mysqltest_1@'127.0.0.1/255.255.255.255'; SHOW GRANTS FOR mysqltest_1@'127.0.0.1/255.255.255.255'; REVOKE ALL ON test.* FROM mysqltest_1@'127.0.0.1/255.255.255.255'; diff --git a/mysql-test/t/sp-group.test b/mysql-test/t/sp-group.test new file mode 100644 index 00000000000..2083ac97595 --- /dev/null +++ b/mysql-test/t/sp-group.test @@ -0,0 +1,187 @@ +--source include/have_innodb.inc + +drop table if exists t1; +drop view if exists view_t1; + +# +# Test case for MDEV 7601, MDEV-7594 and MDEV-7555 +# Server crashes in functions related to stored procedures +# Server crashes in different ways while executing concurrent +# flow involving views and non-empty sql_mode with ONLY_FULL_GROUP_BY +# + +SET sql_mode=ONLY_FULL_GROUP_BY; + +CREATE TABLE t1 ( + pk INT, + f0 INT, f1 INT, f2 INT, f3 INT, f4 INT, + f5 INT, f6 INT, f7 INT, f8 INT, f9 INT, + PRIMARY KEY (pk) +); + +CREATE VIEW view_t1 AS SELECT * FROM t1; +CREATE PROCEDURE s1() + SELECT * FROM ( + INFORMATION_SCHEMA.`INNODB_BUFFER_PAGE_LRU` AS table1 + LEFT JOIN test.view_t1 AS table2 + ON ( table2.`f6` = table1.FREE_PAGE_CLOCK) + ) + ORDER BY table1.NUMBER_RECORDS + LIMIT 0 +; +CALL s1; +CALL s1; + +drop table t1; +drop view view_t1; +drop procedure s1; + +# +# MDEV-7590 +# Server crashes in st_select_lex_unit::cleanup on executing a trigger +# + +CREATE TABLE A ( + pk INTEGER AUTO_INCREMENT, + col_int_key INTEGER, + col_varchar_key VARCHAR(1), + PRIMARY KEY (pk) +) ENGINE=MyISAM; +CREATE VIEW view_A AS SELECT * FROM A; +CREATE TABLE C ( + pk INTEGER AUTO_INCREMENT, + col_int_nokey INTEGER, + col_int_key INTEGER, + col_date_key DATE, + col_date_nokey DATE, + col_time_key TIME, + col_time_nokey TIME, + col_datetime_key DATETIME, + col_datetime_nokey DATETIME, + col_varchar_key VARCHAR(1), + col_varchar_nokey VARCHAR(1), + PRIMARY KEY (pk) +) ENGINE=MyISAM; +CREATE VIEW view_C AS SELECT * FROM C; +CREATE TABLE AA ( + pk INTEGER AUTO_INCREMENT, + col_int_nokey INTEGER, + col_int_key INTEGER, + col_date_key DATE, + col_date_nokey DATE, + col_time_key TIME, + col_time_nokey TIME, + col_datetime_key DATETIME, + col_datetime_nokey DATETIME, + col_varchar_key VARCHAR(1), + col_varchar_nokey VARCHAR(1), + PRIMARY KEY (pk), + KEY (col_varchar_key, col_int_key) +) ENGINE=MyISAM; +CREATE VIEW view_AA AS SELECT * FROM AA; +CREATE TABLE BB ( + pk INTEGER AUTO_INCREMENT, + col_int_key INTEGER, + col_varchar_key VARCHAR(1), + col_varchar_nokey VARCHAR(1), + PRIMARY KEY (pk), + KEY (col_varchar_key, col_int_key) +) ENGINE=MyISAM; +CREATE VIEW view_BB AS SELECT * FROM BB; +CREATE TABLE DD ( + pk INTEGER AUTO_INCREMENT, + col_int_key INTEGER, + col_date_key DATE, + col_time_key TIME, + col_datetime_key DATETIME, + col_varchar_key VARCHAR(1), + PRIMARY KEY (pk), + KEY (col_varchar_key, col_int_key) +) ENGINE=MyISAM; +CREATE VIEW view_DD AS SELECT * FROM DD; +CREATE TRIGGER k BEFORE INSERT ON `DD` FOR EACH ROW INSERT INTO `view_BB` SELECT * FROM `view_A` LIMIT 0 ; +CREATE TRIGGER r BEFORE INSERT ON `A` FOR EACH ROW INSERT INTO `view_AA` SELECT * FROM `view_C` LIMIT 0 ; +--error ER_WRONG_AUTO_KEY +ALTER TABLE `DD` DROP PRIMARY KEY; +INSERT INTO `view_A` ( `pk` ) VALUES (NULL); +--error 0,ER_WRONG_VALUE_COUNT_ON_ROW +INSERT INTO `DD` ( `pk` ) VALUES (NULL); +INSERT INTO `A` ( `pk` ) VALUES (NULL); +--error 0,ER_WRONG_VALUE_COUNT_ON_ROW +INSERT INTO `view_DD` ( `pk` ) VALUES (NULL); + +drop trigger r; +drop trigger k; +drop view view_A,view_AA,view_C,view_BB,view_DD; +drop table A,C,AA,BB,DD; + +# +# MDEV-7581 +# Server crashes in st_select_lex_unit::cleanup after a sequence of statements +# + +CREATE TABLE A ( + i INT, + i1 INT, + i2 INT, + d1 DATE, + d2 DATE, + col_time_nokey1 TIME, + col_time_nokey2 TIME, + col_datetime_nokey1 DATETIME, + col_datetime_nokey2 DATETIME, + col_varchar_nokey1 VARCHAR(1), + col_varchar_nokey2 VARCHAR(1) +) ENGINE=MyISAM; + +CREATE VIEW view_A AS SELECT * FROM A; + +CREATE TABLE B ( + col_varchar_nokey VARCHAR(1) +) ENGINE=MyISAM; + +CREATE TABLE AA ( + i INT, + i1 INT, + i2 INT, + d1 DATE, + d2 DATE, + col_time_nokey1 TIME, + col_time_nokey2 TIME, + col_datetime_nokey1 DATETIME, + col_datetime_nokey2 DATETIME, + col_varchar_nokey1 VARCHAR(1), + col_varchar_nokey2 VARCHAR(1) +) ENGINE=MyISAM; + +CREATE VIEW view_AA AS SELECT * FROM AA; + +CREATE TABLE DD ( + i INT, + i1 INT, + i2 INT, + d1 DATE, + d2 DATE, + col_time_nokey1 TIME, + col_time_nokey2 TIME, + col_datetime_nokey1 DATETIME, + col_datetime_nokey2 DATETIME, + col_varchar_nokey1 VARCHAR(1), + col_varchar_nokey2 VARCHAR(1) +) ENGINE=MyISAM; + +CREATE VIEW view_DD AS SELECT * FROM DD; + +CREATE TRIGGER tr1 BEFORE INSERT ON `AA` FOR EACH ROW INSERT INTO `view_A` SELECT * FROM `view_AA` LIMIT 0 ; +CREATE TRIGGER tr2 BEFORE INSERT ON `B` FOR EACH ROW INSERT INTO `D` SELECT * FROM `A` LIMIT 0 ; + +INSERT INTO `view_AA` ( `i` ) VALUES (1); +INSERT INTO `AA` ( `i` ) VALUES (2); +DELETE FROM `B`; +INSERT INTO `view_DD` ( `i` ) VALUES (1); +INSERT INTO `view_AA` ( `i` ) VALUES (3); + +drop trigger tr1; +drop trigger tr2; +drop view view_A, view_AA,view_DD; +drop table A,B,AA,DD; diff --git a/mysql-test/t/sp-security.test b/mysql-test/t/sp-security.test index 0646e727232..a5df4859d6b 100644 --- a/mysql-test/t/sp-security.test +++ b/mysql-test/t/sp-security.test @@ -7,13 +7,13 @@ # Save the initial number of concurrent sessions --source include/count_sessions.inc - connect (con1root,localhost,root,,); connection con1root; use test; # Create user user1 with no particular access rights +create user user1@localhost; grant usage on *.* to user1@localhost; flush privileges; @@ -57,10 +57,13 @@ call stamp(1); select * from t1; select db(); +create user user1@'%'; grant execute on procedure db1_secret.stamp to user1@'%'; grant execute on function db1_secret.db to user1@'%'; +set sql_mode=''; grant execute on procedure db1_secret.stamp to ''@'%'; grant execute on function db1_secret.db to ''@'%'; +set sql_mode=default; connect (con2user1,localhost,user1,,); connect (con3anon,localhost,anon,,); @@ -175,6 +178,7 @@ insert into t2 values (0); grant usage on db2.* to user1@localhost; grant select on db2.* to user1@localhost; +create user user2@localhost; grant usage on db2.* to user2@localhost; grant select,insert,update,delete,create routine on db2.* to user2@localhost; grant create routine on db2.* to user1@localhost; @@ -263,8 +267,11 @@ flush privileges; # # Test the new security acls # +create user usera@localhost; grant usage on *.* to usera@localhost; +create user userb@localhost; grant usage on *.* to userb@localhost; +create user userc@localhost; grant usage on *.* to userc@localhost; create database sptest; create table t1 ( u varchar(64), i int ); @@ -340,11 +347,13 @@ connection root; --disable_warnings drop function if exists bug_9503; +drop user if exists user1@localhost; --enable_warnings delimiter //; create database mysqltest// use mysqltest// create table t1 (s1 int)// +create user user1@localhost// grant select on t1 to user1@localhost// create function bug_9503 () returns int sql security invoker begin declare v int; select min(s1) into v from t1; return v; end// @@ -420,6 +429,7 @@ begin end// delimiter ;// +create user mysqltest_1@localhost; grant usage on *.* to mysqltest_1@localhost; connect (n1,localhost,mysqltest_1,,information_schema,$MASTER_MYPORT,$MASTER_MYSOCK); @@ -1047,4 +1057,3 @@ grant execute on procedure sp1 to current_user() identified by 'barfoo'; show grants; drop procedure sp1; set password=''; - diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test index 7dda755481c..9e6b919ea19 100644 --- a/mysql-test/t/sp.test +++ b/mysql-test/t/sp.test @@ -14,7 +14,6 @@ # Tests that require --with-geometry go into sp_gis.test # Tests that require multibyte character sets, which are not always available, # go into separate files (e.g. sp-ucs2.test) - use test; # Test tables @@ -9301,6 +9300,75 @@ DROP TABLE t1; --echo # End of 5.5 test +--echo # +--echo # MDEV-7040: Crash in field_conv, memcpy_field_possible, part#2 +--echo # +create table t1 ( + col1 bigint(20), + col2 char(1), + col3 char(2) +); +insert into t1 values (1,'a','a'), (2,'b','b'); + +create table t2 as select * from t1; +create table t3 as select * from t1; +create table t4 as select * from t1; +create table t5 as select * from t1; +create table t6 as select * from t1; + +flush tables; + +DELIMITER |; + +CREATE PROCEDURE p1() +begin + DECLARE _var1 bigint(20) UNSIGNED; + DECLARE _var2 CHAR(1) DEFAULT NULL; + DECLARE _var3 CHAR(1) DEFAULT NULL; + + DECLARE _done BOOLEAN DEFAULT 0; + + declare cur1 cursor for + select col1, col2, col3 + from t1 + where + col1 in (select t2.col1 from t2 where t2.col2=t1.col2) or + col2 in (select t3.col3 from t3 where t3.col3=t1.col2) ; + + DECLARE CONTINUE HANDLER FOR NOT FOUND SET _done = 1; + + OPEN cur1; + + set _var1 = (select _var1 from t4 limit 1); + set _var1 = (select _var1 from t5 limit 1); + set _var1 = (select _var1 from t6 limit 1); +label1: + LOOP + SET _done = 0; + FETCH cur1 INTO _var1, _var2, _var3; + IF _done THEN + LEAVE label1; + END IF; + END LOOP label1; + CLOSE cur1; +end| +DELIMITER ;| + +set @tmp_toc= @@table_open_cache; +set @tmp_tdc= @@table_definition_cache; + +set global table_open_cache=1; +set global table_definition_cache=1; +call p1(); + +set global table_open_cache= @tmp_toc; +set global table_definition_cache= @tmp_tdc; +drop procedure p1; + +drop table t1,t2,t3,t4,t5,t6; + +--echo # End of 10.0 test + DELIMITER |; CREATE FUNCTION f(f1 VARCHAR(64) COLLATE latin1_german2_ci) RETURNS VARCHAR(64) diff --git a/mysql-test/t/sp_notembedded.test b/mysql-test/t/sp_notembedded.test index 2a24ab3d005..d7aaaa4340f 100644 --- a/mysql-test/t/sp_notembedded.test +++ b/mysql-test/t/sp_notembedded.test @@ -219,6 +219,7 @@ delimiter ;| drop procedure if exists bug15298_1; drop procedure if exists bug15298_2; --enable_warnings +create user 'mysqltest_1'@'localhost'; grant all privileges on test.* to 'mysqltest_1'@'localhost'; create procedure 15298_1 () sql security definer show grants for current_user; create procedure 15298_2 () sql security definer show grants; @@ -486,6 +487,5 @@ drop user foo1@localhost; # set @@global.concurrent_insert= @old_concurrent_insert; - # Wait till all disconnects are completed --source include/wait_until_count_sessions.inc diff --git a/mysql-test/t/sp_trans.test b/mysql-test/t/sp_trans.test index 39ac086071b..a9759b99269 100644 --- a/mysql-test/t/sp_trans.test +++ b/mysql-test/t/sp_trans.test @@ -5,6 +5,8 @@ -- source include/have_log_bin.inc -- source include/have_innodb.inc +set SQL_MODE=""; + disable_query_log; call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT"); enable_query_log; diff --git a/mysql-test/t/stack-crash.test b/mysql-test/t/stack-crash.test index d5bdccc132d..e4feb95a332 100644 --- a/mysql-test/t/stack-crash.test +++ b/mysql-test/t/stack-crash.test @@ -32,7 +32,7 @@ CREATE TABLE t1 ( `sspo_lu_uid` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`sspo_id`), KEY `post_uid` (`sspo_uid`,`sspo_cr_date`) -) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8; +) AUTO_INCREMENT=30 DEFAULT CHARSET=utf8; INSERT INTO t1 VALUES (1,2,'P','test1','',0,'','',NULL,'','','','','A','2013-09-30 00:19:32',2,'2013-09-30 00:19:32',2),(2,2,'P','bbb','',0,'','',NULL,'','','','','A','2013-10-02 15:06:35',2,'2013-10-02 15:06:35',2); CREATE TABLE `t2` ( @@ -48,7 +48,7 @@ CREATE TABLE `t2` ( `spoo_lu_uid` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`spoo_id`), KEY `object_option_main_idx` (`spoo_user_type_id`,`spoo_uid`,`spoo_option_id`,`spoo_value`(255)) -) ENGINE=InnoDB AUTO_INCREMENT=107 DEFAULT CHARSET=utf8; +) AUTO_INCREMENT=107 DEFAULT CHARSET=utf8; INSERT INTO `t2` VALUES (19,1,2,6,'Dortmund','A','2013-09-26 01:36:51',2,'2013-09-26 01:36:51',2),(20,1,2,8,'49','A','2013-09-26 01:36:51',2,'2013-09-26 01:36:51',2); SELECT Count(*) diff --git a/mysql-test/t/stat_tables_rbr.test b/mysql-test/t/stat_tables_rbr.test index 4336c02c34b..29f7c4e6622 100644 --- a/mysql-test/t/stat_tables_rbr.test +++ b/mysql-test/t/stat_tables_rbr.test @@ -23,8 +23,7 @@ SET use_stat_tables = PREFERABLY; CREATE TABLE t1 ( a INT ) ENGINE=MyISAM PARTITION BY HASH(a) PARTITIONS 2; ALTER TABLE t1 ANALYZE PARTITION p1; ---replace_regex /\/\* xid=.* \*\//\/* XID *\// /Server ver: .*, Binlog ver: .*/Server ver: #, Binlog ver: #/ /table_id: [0-9]+/table_id: #/ -SHOW BINLOG EVENTS; +source include/show_binlog_events.inc; SET use_stat_tables = DEFAULT; diff --git a/mysql-test/t/status.test b/mysql-test/t/status.test index 9395323d32b..32b903c0265 100644 --- a/mysql-test/t/status.test +++ b/mysql-test/t/status.test @@ -281,6 +281,7 @@ create database db37908; --enable_warnings create table db37908.t1(f1 int); insert into db37908.t1 values(1); +create user mysqltest_1@localhost; grant usage,execute on test.* to mysqltest_1@localhost; delimiter |; create procedure proc37908() begin select 1; end | diff --git a/mysql-test/t/subselect_sj.test b/mysql-test/t/subselect_sj.test index c989cb22558..c2b93c919dd 100644 --- a/mysql-test/t/subselect_sj.test +++ b/mysql-test/t/subselect_sj.test @@ -2524,7 +2524,7 @@ CREATE TABLE t1 ( PRIMARY KEY (pk), KEY col_int_key (col_int_key), KEY col_varchar_key (col_varchar_key, col_int_key) -) ENGINE=InnoDB; +); INSERT INTO t1 VALUES (10,8,'x','x'), diff --git a/mysql-test/t/subselect_sj2.test b/mysql-test/t/subselect_sj2.test index 7ee52a0fa8a..5d6639a6d5a 100644 --- a/mysql-test/t/subselect_sj2.test +++ b/mysql-test/t/subselect_sj2.test @@ -1410,5 +1410,44 @@ eval explain $query; drop table t3,t2,t1; set optimizer_search_depth=@tmp7474; +--echo # +--echo # +--echo # +CREATE TABLE t1 ( + id int(16) NOT NULL AUTO_INCREMENT, + PRIMARY KEY (id) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +CREATE TABLE t2 ( + id int(16) NOT NULL AUTO_INCREMENT, + t3_id int(16) NOT NULL DEFAULT '0', + t1_id int(16) NOT NULL DEFAULT '0', + PRIMARY KEY (id), + KEY t3_idx (t3_id), + KEY t1_idx (t1_id) +) ENGINE=MyISAM DEFAULT CHARSET=utf8; + +CREATE TABLE t3 ( + id int(16) NOT NULL AUTO_INCREMENT, + PRIMARY KEY (id) +) ENGINE=MyISAM DEFAULT CHARSET=utf8; + + +INSERT INTO t3 VALUES (1); + +INSERT INTO t2 VALUES (1, 1, 1); +INSERT INTO t2 VALUES (2, 1, 2); +INSERT INTO t2 VALUES (3, 1, 2); +INSERT INTO t2 VALUES (4, 1, 1); + +INSERT INTO t1 VALUES (1); +INSERT INTO t1 VALUES (2); + +SELECT * FROM t1 WHERE t1.id IN ( + SELECT t2.t1_id FROM t3 JOIN t2 ON t3.id = t2.t3_id WHERE t3.id = 1 +); + +drop table t1,t2,t3; + --echo # This must be the last in the file: set optimizer_switch=@subselect_sj2_tmp; diff --git a/mysql-test/t/subselect_sj_mat.test b/mysql-test/t/subselect_sj_mat.test index c34c805f90c..d2bafa86028 100644 --- a/mysql-test/t/subselect_sj_mat.test +++ b/mysql-test/t/subselect_sj_mat.test @@ -1841,8 +1841,23 @@ drop database mysqltest2; drop database mysqltest3; drop database mysqltest4; ---echo # End of 5.5 tests +--echo # +--echo # MDEV-7810 Wrong result on execution of a query as a PS +--echo # (both 1st and further executions) + +CREATE TABLE t1 (a INT NOT NULL) ENGINE=MyISAM; +INSERT INTO t1 VALUES (0),(8); + +SELECT a FROM (SELECT DISTINCT * FROM t1) AS sq WHERE a IN (SELECT MIN(t2.a) FROM (t1 AS t2)); +PREPARE stmt FROM " +SELECT a FROM (SELECT DISTINCT * FROM t1) AS sq WHERE a IN (SELECT MIN(t2.a) FROM (t1 AS t2)) +"; +execute stmt; +execute stmt; + +drop table t1; +--echo # End of 5.5 tests --echo # --echo # MDEV-7220: Materialization strategy is not used for REPLACE ... SELECT --echo # @@ -1885,3 +1900,28 @@ select * from t1 where (a,b) in (select max(a),b from t2 group by b); show status where Variable_name like 'Handler_read%' or Variable_name like 'Handler_%write%'; drop table t0,t1,t2,t3; + +--echo # +--echo # MDEV-7971: Assertion `name != __null' failed in ACL_internal_schema_registry::lookup +--echo # on 2nd execution os PS with multi-table update +--echo # +CREATE TABLE t1 (f1 INT); +INSERT INTO t1 VALUES (1),(2); + +CREATE TABLE t2 (f2 INT); +INSERT INTO t2 VALUES (3),(4); + +CREATE TABLE t3 (f3 INT); +INSERT INTO t3 VALUES (5),(6); + +PREPARE stmt FROM ' + UPDATE t1, t2 + SET f1 = 5 + WHERE 8 IN ( SELECT MIN(f3) FROM t3 ) +'; + +EXECUTE stmt; +EXECUTE stmt; + +DROP TABLE t1,t2,t3; + diff --git a/mysql-test/t/table_elim.test b/mysql-test/t/table_elim.test index 24f48206013..52857f9169f 100644 --- a/mysql-test/t/table_elim.test +++ b/mysql-test/t/table_elim.test @@ -616,7 +616,7 @@ SET optimizer_switch=@save_optimizer_switch; --echo # (just a testcase) CREATE TABLE t1 ( PostID int(10) unsigned NOT NULL -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) DEFAULT CHARSET=utf8; INSERT INTO t1 (PostID) VALUES (1), (2); @@ -625,7 +625,7 @@ CREATE TABLE t2 ( EntityID int(10) unsigned NOT NULL, UserID int(10) unsigned NOT NULL, UNIQUE KEY EntityID (EntityID,UserID) -) ENGINE=InnoDB DEFAULT CHARSET=utf8; +) DEFAULT CHARSET=utf8; INSERT INTO t2 (EntityID, UserID) VALUES (1, 30), (2, 30); diff --git a/mysql-test/t/timezone_grant.test b/mysql-test/t/timezone_grant.test index 8013f2b04ce..a6dd6be695e 100644 --- a/mysql-test/t/timezone_grant.test +++ b/mysql-test/t/timezone_grant.test @@ -24,6 +24,7 @@ flush privileges; create table t1 (a int, b datetime); create table t2 (c int, d datetime); +create user mysqltest_1@localhost; grant all privileges on test.* to mysqltest_1@localhost; connect (tzuser, localhost, mysqltest_1,,); connection tzuser; @@ -103,6 +104,7 @@ drop table t1, t2; # Let us rely on that previous tests done proper cleanups create table t1 (a int, b datetime); insert into t1 values (1, 20010101000000), (2, 20020101000000); +create user mysqltest_1@localhost; grant all privileges on test.* to mysqltest_1@localhost; connect (tzuser3, localhost, mysqltest_1,,); create view v1 as select a, convert_tz(b, 'UTC', 'Europe/Moscow') as lb from t1; diff --git a/mysql-test/t/trigger-compat.test b/mysql-test/t/trigger-compat.test index 8b360d16b98..c627d1a6040 100644 --- a/mysql-test/t/trigger-compat.test +++ b/mysql-test/t/trigger-compat.test @@ -19,7 +19,6 @@ # # Prepare environment. # - DELETE FROM mysql.user WHERE User LIKE 'mysqltest_%'; DELETE FROM mysql.db WHERE User LIKE 'mysqltest_%'; DELETE FROM mysql.tables_priv WHERE User LIKE 'mysqltest_%'; diff --git a/mysql-test/t/trigger_notembedded.test b/mysql-test/t/trigger_notembedded.test index b4ce183e114..f0c565be41f 100644 --- a/mysql-test/t/trigger_notembedded.test +++ b/mysql-test/t/trigger_notembedded.test @@ -28,7 +28,6 @@ # # Prepare environment. # - DELETE FROM mysql.user WHERE User LIKE 'mysqltest_%'; DELETE FROM mysql.db WHERE User LIKE 'mysqltest_%'; DELETE FROM mysql.tables_priv WHERE User LIKE 'mysqltest_%'; @@ -1019,6 +1018,7 @@ DROP DATABASE IF EXISTS mysqltest_db1; CREATE DATABASE mysqltest_db1; USE mysqltest_db1; +CREATE USER mysqltest_u1@localhost; GRANT ALL ON mysqltest_db1.* TO mysqltest_u1@localhost; --connect(con1,localhost,mysqltest_u1,,mysqltest_db1) diff --git a/mysql-test/t/type_binary.test b/mysql-test/t/type_binary.test index 4d5a5312472..b583e257aa9 100644 --- a/mysql-test/t/type_binary.test +++ b/mysql-test/t/type_binary.test @@ -100,3 +100,29 @@ select hex(f2), hex(f3) from t1; drop table t1; --echo End of 5.0 tests + +--echo # +--echo # Start of 10.0 tests +--echo # + +--echo # +--echo # MDEV-8472 BINARY, VARBINARY and BLOB return different warnings on CAST to DECIMAL +--echo # +SET NAMES utf8; +CREATE TABLE t1 (a BINARY(30)); +INSERT INTO t1 VALUES ('1äÖüß@µ*$'); +SELECT CAST(a AS DECIMAL) FROM t1; +DROP TABLE t1; +CREATE TABLE t1 (a VARBINARY(30)); +INSERT INTO t1 VALUES ('1äÖüß@µ*$'); +SELECT CAST(a AS DECIMAL) FROM t1; +DROP TABLE t1; +CREATE TABLE t1 (a BLOB); +INSERT INTO t1 VALUES ('1äÖüß@µ*$'); +SELECT CAST(a AS DECIMAL) FROM t1; +DROP TABLE t1; + +--echo # +--echo # End of 10.0 tests +--echo # + diff --git a/mysql-test/t/user_limits.test b/mysql-test/t/user_limits.test index 7c249f61686..ebb4fd4fb88 100644 --- a/mysql-test/t/user_limits.test +++ b/mysql-test/t/user_limits.test @@ -26,6 +26,7 @@ flush privileges; --disable_ps_protocol # Test of MAX_QUERIES_PER_HOUR limit +create user mysqltest_1@localhost; grant usage on *.* to mysqltest_1@localhost with max_queries_per_hour 2; # This ensures that counters are reset and makes test scheduling independent flush user_resources; @@ -46,6 +47,7 @@ disconnect mqph; disconnect mqph2; # Test of MAX_UPDATES_PER_HOUR limit +create user mysqltest_1@localhost; grant usage on *.* to mysqltest_1@localhost with max_updates_per_hour 2; flush user_resources; connect (muph, localhost, mysqltest_1,,); @@ -70,6 +72,7 @@ disconnect muph; disconnect muph2; # Test of MAX_CONNECTIONS_PER_HOUR limit +create user mysqltest_1@localhost; grant usage on *.* to mysqltest_1@localhost with max_connections_per_hour 2; flush user_resources; connect (mcph1, localhost, mysqltest_1,,); @@ -97,6 +100,7 @@ drop user mysqltest_1@localhost; # Test of MAX_USER_CONNECTIONS limit # We need this to reset internal mqh_used variable flush privileges; +create user mysqltest_1@localhost; grant usage on *.* to mysqltest_1@localhost with max_user_connections 2; flush user_resources; connect (muc1, localhost, mysqltest_1,,); @@ -150,6 +154,7 @@ set session max_user_connections= 2; set global max_user_connections= 2; select @@session.max_user_connections, @@global.max_user_connections; # Let us check that global limit works +create user mysqltest_1@localhost; grant usage on *.* to mysqltest_1@localhost; flush user_resources; connect (muca1, localhost, mysqltest_1,,); diff --git a/mysql-test/t/variables.test b/mysql-test/t/variables.test index 4c7c74f8032..8f29528ac25 100644 --- a/mysql-test/t/variables.test +++ b/mysql-test/t/variables.test @@ -1467,9 +1467,11 @@ drop function if exists t1_max; drop function if exists t1_min; --enable_warnings +set sql_mode=""; --disable_warnings create table t1 (a int) engine=innodb; --enable_warnings +set sql_mode=default; insert into t1(a) values (0), (1); create function t1_max() returns int return (select max(a) from t1); create function t1_min() returns int return (select min(a) from t1); diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test index e95194e3f2c..26faae545d8 100644 --- a/mysql-test/t/view.test +++ b/mysql-test/t/view.test @@ -2083,18 +2083,16 @@ drop table t1; # Bug#12941 # ---disable_warnings create table t1 ( r_object_id char(16) NOT NULL, group_name varchar(32) NOT NULL -) engine = InnoDB; +); create table t2 ( r_object_id char(16) NOT NULL, i_position int(11) NOT NULL, users_names varchar(32) default NULL -) Engine = InnoDB; ---enable_warnings +); create view v1 as select r_object_id, group_name from t1; create view v2 as select r_object_id, i_position, users_names from t2; @@ -2806,11 +2804,12 @@ DROP VIEW IF EXISTS v1; # Bug#21261 Wrong access rights was required for an insert to a view # +set GLOBAL sql_mode=""; +set LOCAL sql_mode=""; CREATE DATABASE bug21261DB; USE bug21261DB; connect (root,localhost,root,,bug21261DB); connection root; - CREATE TABLE t1 (x INT); CREATE SQL SECURITY INVOKER VIEW v1 AS SELECT x FROM t1; GRANT INSERT, UPDATE ON v1 TO 'user21261'@'localhost'; @@ -2840,6 +2839,8 @@ USE test; disconnect root; disconnect user21261; +set GLOBAL sql_mode=default; +set LOCAL sql_mode=default; # # Bug#15950 NOW() optimized away in VIEWs @@ -5380,6 +5381,27 @@ show create view v2; drop view v2; drop table t1; +--echo # +--echo # MDEV-8554: Server crashes in base_list_iterator::next_fast on 1st execution of PS with a multi-table update +--echo # +CREATE TABLE t1 (a INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1),(2); # Not necessary, the table can be empty + +CREATE TABLE t2 (b INT) ENGINE=MyISAM; +INSERT INTO t2 VALUES (3),(4); # Not necessary, the table can be empty + +CREATE TABLE t3 (c INT) ENGINE=MyISAM; +INSERT INTO t3 VALUES (5),(6); # Not necessary, the table can be empty + +CREATE OR REPLACE ALGORITHM=MERGE VIEW v3 AS SELECT * FROM t3; + +PREPARE stmt FROM 'UPDATE t1, t2 SET a = 1 WHERE a IN ( SELECT 0 FROM t3 )'; +UPDATE t1, t2 SET a = 1 WHERE a IN ( SELECT 0 FROM v3 ); +EXECUTE stmt; + +DROP TABLE t1, t2, t3; +DROP VIEW v3; + --echo # ----------------------------------------------------------------- --echo # -- End of 5.5 tests. --echo # ----------------------------------------------------------------- diff --git a/mysql-test/t/view_grant.test b/mysql-test/t/view_grant.test index 8907c726ae3..6d0cd8e5c28 100644 --- a/mysql-test/t/view_grant.test +++ b/mysql-test/t/view_grant.test @@ -11,6 +11,7 @@ drop view if exists v1,v2,v3; # simple test of grants +create user test@localhost; grant create view on test.* to test@localhost; show grants for test@localhost; revoke create view on test.* from test@localhost; @@ -29,6 +30,7 @@ create database mysqltest; create table mysqltest.t1 (a int, b int); create table mysqltest.t2 (a int, b int); +create user mysqltest_1@localhost; grant select on mysqltest.t1 to mysqltest_1@localhost; grant create view,select on test.* to mysqltest_1@localhost; @@ -90,7 +92,7 @@ select d from mysqltest.v1; connection root; revoke all privileges on mysqltest.v1 from mysqltest_1@localhost; -delete from mysql.user where user='mysqltest_1'; +drop user mysqltest_1@localhost; drop database mysqltest; # TEMPORARY TABLE algorithm @@ -100,6 +102,7 @@ create database mysqltest; create table mysqltest.t1 (a int, b int); create algorithm=temptable view mysqltest.v1 (c,d) as select a+1,b+1 from mysqltest.t1; +create user mysqltest_1@localhost; grant select (c) on mysqltest.v1 to mysqltest_1@localhost; connection user1; @@ -110,7 +113,7 @@ select d from mysqltest.v1; connection root; revoke all privileges on mysqltest.v1 from mysqltest_1@localhost; -delete from mysql.user where user='mysqltest_1'; +drop user mysqltest_1@localhost; drop database mysqltest; # @@ -129,6 +132,7 @@ create view mysqltest.v3 (c,d) as select a+1,b+1 from mysqltest.t2; create algorithm=temptable view mysqltest.v4 (c,d) as select a+1,b+1 from mysqltest.t2; # v5: SHOW VIEW, but no SELECT create view mysqltest.v5 (c,d) as select a+1,b+1 from mysqltest.t1; +create user mysqltest_1@localhost; grant select on mysqltest.v1 to mysqltest_1@localhost; grant select on mysqltest.v2 to mysqltest_1@localhost; grant select on mysqltest.v3 to mysqltest_1@localhost; @@ -225,7 +229,7 @@ show create view mysqltest.v4; connection root; revoke all privileges on mysqltest.* from mysqltest_1@localhost; -delete from mysql.user where user='mysqltest_1'; +drop user mysqltest_1@localhost; drop database mysqltest; # @@ -244,6 +248,7 @@ create view mysqltest.v1 (a,c) as select a, b+1 from mysqltest.t1; create view mysqltest.v2 (a,c) as select a, b from mysqltest.t1; create view mysqltest.v3 (a,c) as select a, b+1 from mysqltest.t1; +create user mysqltest_1@localhost; grant update (a) on mysqltest.v2 to mysqltest_1@localhost; grant update on mysqltest.v1 to mysqltest_1@localhost; grant select on mysqltest.* to mysqltest_1@localhost; @@ -274,6 +279,7 @@ update v3 set a=a+c; use test; connection root; REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; +drop user mysqltest_1@localhost; drop database mysqltest; # @@ -291,6 +297,7 @@ insert into mysqltest.t2 values (3), (4), (5), (6); create view mysqltest.v1 (a,c) as select a, b+1 from mysqltest.t1; create view mysqltest.v2 (a,c) as select a, b+1 from mysqltest.t1; +create user mysqltest_1@localhost; grant delete on mysqltest.v1 to mysqltest_1@localhost; grant select on mysqltest.* to mysqltest_1@localhost; @@ -310,6 +317,7 @@ delete from v2 where c < 4; use test; connection root; REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; +drop user mysqltest_1@localhost; drop database mysqltest; # @@ -327,6 +335,7 @@ insert into mysqltest.t2 values (3,4); create view mysqltest.v1 (a,c) as select a, b from mysqltest.t1; create view mysqltest.v2 (a,c) as select a, b from mysqltest.t1; +create user mysqltest_1@localhost; grant insert on mysqltest.v1 to mysqltest_1@localhost; grant select on mysqltest.* to mysqltest_1@localhost; @@ -346,6 +355,7 @@ insert into v2 select x,y from t2; use test; connection root; REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; +drop user mysqltest_1@localhost; drop database mysqltest; # @@ -359,6 +369,7 @@ create database mysqltest; create table mysqltest.t1 (a int, b int); create table mysqltest.t2 (a int, b int); +create user mysqltest_1@localhost; grant update on mysqltest.t1 to mysqltest_1@localhost; grant update(b) on mysqltest.t2 to mysqltest_1@localhost; grant create view,update on test.* to mysqltest_1@localhost; @@ -407,6 +418,7 @@ create view v4 as select b+1 from mysqltest.t2; connection root; REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; +drop user mysqltest_1@localhost; drop database mysqltest; drop view v1,v2,v4; @@ -418,6 +430,7 @@ connection root; create database mysqltest; --enable_warnings create table mysqltest.t1 (a int); +create user mysqltest_1@localhost; grant all privileges on mysqltest.* to mysqltest_1@localhost; connection user1; @@ -427,6 +440,7 @@ use test; connection root; revoke all privileges on mysqltest.* from mysqltest_1@localhost; +drop user mysqltest_1@localhost; drop database mysqltest; # @@ -439,6 +453,7 @@ create database mysqltest; create table mysqltest.t1 (a int, b int); +create user mysqltest_1@localhost; grant select on mysqltest.t1 to mysqltest_1@localhost; grant create view,select on test.* to mysqltest_1@localhost; @@ -455,6 +470,7 @@ select * from v1; grant select on mysqltest.t1 to mysqltest_1@localhost; select * from v1; REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; +drop user mysqltest_1@localhost; drop view v1; drop database mysqltest; @@ -482,6 +498,7 @@ create algorithm=MERGE view v2 as select f2() from t1; create algorithm=TEMPTABLE SQL SECURITY INVOKER view v3 as select f2() from t1; create algorithm=MERGE SQL SECURITY INVOKER view v4 as select f2() from t1; create SQL SECURITY INVOKER view v5 as select * from v4; +create user mysqltest_1@localhost; grant select on v1 to mysqltest_1@localhost; grant select on v2 to mysqltest_1@localhost; grant select on v3 to mysqltest_1@localhost; @@ -506,6 +523,7 @@ drop function f2; drop table t1, t2; use test; REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; +drop user mysqltest_1@localhost; drop database mysqltest; # @@ -529,6 +547,7 @@ delimiter //; create function f2 () returns int begin declare v int; select s1 from t2 into v; return v; end// delimiter ;// +create user mysqltest_1@localhost; grant select on t1 to mysqltest_1@localhost; grant execute on function f2 to mysqltest_1@localhost; grant create view on mysqltest.* to mysqltest_1@localhost; @@ -558,6 +577,7 @@ drop function f2; drop table t1, t2; use test; REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; +drop user mysqltest_1@localhost; drop database mysqltest; # @@ -572,6 +592,7 @@ use mysqltest; create table t1 (a int); create table v1 (a int); insert into t1 values (1); +create user mysqltest_1@localhost; grant select on t1 to mysqltest_1@localhost; grant select on v1 to mysqltest_1@localhost; grant create view on mysqltest.* to mysqltest_1@localhost; @@ -601,6 +622,7 @@ select * from v5; drop table t1; use test; REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; +drop user mysqltest_1@localhost; drop database mysqltest; @@ -617,6 +639,7 @@ create algorithm=MERGE view v2 as select *, a as b from t1; create algorithm=TEMPTABLE SQL SECURITY INVOKER view v3 as select *, a as b from t1; create algorithm=MERGE SQL SECURITY INVOKER view v4 as select *, a as b from t1; create SQL SECURITY INVOKER view v5 as select * from v4; +create user mysqltest_1@localhost; grant select on v1 to mysqltest_1@localhost; grant select on v2 to mysqltest_1@localhost; grant select on v3 to mysqltest_1@localhost; @@ -640,6 +663,7 @@ drop view v1, v2, v3, v4, v5; drop table t1; use test; REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost; +drop user mysqltest_1@localhost; drop database mysqltest; # @@ -657,6 +681,7 @@ delete from mysql.user where user=''; flush privileges; # Create the test user +create user 'test14256'@'%'; grant all on test.* to 'test14256'@'%'; connect (test14256,localhost,test14256,,test); @@ -702,6 +727,7 @@ use mysqltest; CREATE TABLE t1 (i INT); CREATE VIEW v1 AS SELECT * FROM t1; SHOW CREATE VIEW v1; +create user mysqltest_1@localhost; GRANT SELECT, LOCK TABLES ON mysqltest.* TO mysqltest_1@localhost; connection user1; @@ -1162,6 +1188,7 @@ INSERT INTO mysqltest1.t2 VALUES (21), (22), (23), (24); INSERT INTO mysqltest1.t3 VALUES (31), (32), (33), (34); INSERT INTO mysqltest1.t4 VALUES (41), (42), (43), (44); +CREATE USER mysqltest_u1@localhost; GRANT SELECT ON mysqltest1.t1 TO mysqltest_u1@localhost; GRANT INSERT ON mysqltest1.t2 TO mysqltest_u1@localhost; GRANT SELECT, UPDATE ON mysqltest1.t3 TO mysqltest_u1@localhost; @@ -1237,6 +1264,7 @@ USE db1; CREATE TABLE t1(f1 INT, f2 INT); CREATE VIEW v1 AS SELECT f1, f2 FROM t1; +CREATE USER foo; GRANT SELECT (f1) ON t1 TO foo; GRANT SELECT (f1) ON v1 TO foo; diff --git a/mysql-test/t/warnings_engine_disabled.test b/mysql-test/t/warnings_engine_disabled.test index 11b22135128..46c686aa6d6 100644 --- a/mysql-test/t/warnings_engine_disabled.test +++ b/mysql-test/t/warnings_engine_disabled.test @@ -12,9 +12,11 @@ if (!`SELECT count(*) FROM information_schema.engines WHERE # Test for handler type, will select MyISAM and print a warning # about that - since InnoDB is disabled # +set sql_mode=""; create table t1 (id int) engine=InnoDB; alter table t1 engine=InnoDB; drop table t1; +set sql_mode=default; # # Bug#29263 disabled storage engines omitted in SHOW ENGINES |