diff options
Diffstat (limited to 'mysql-test/t')
72 files changed, 1960 insertions, 110 deletions
diff --git a/mysql-test/t/alter_table.test b/mysql-test/t/alter_table.test index 416c73009e6..cbb73bbb3d4 100644 --- a/mysql-test/t/alter_table.test +++ b/mysql-test/t/alter_table.test @@ -1326,6 +1326,14 @@ SHOW CREATE TABLE t2; DROP TABLE t2; DROP TABLE t1; +CREATE TABLE t1 ( + `transaction_id` int(11) NOT NULL DEFAULT '0', + KEY `transaction_id` (`transaction_id`)); +ALTER TABLE t1 DROP KEY IF EXISTS transaction_id, ADD PRIMARY KEY IF NOT EXISTS (transaction_id); +SHOW CREATE TABLE t1; + +DROP TABLE t1; + --echo # Bug#11748057 (formerly known as 34972): ALTER TABLE statement doesn't --echo # identify correct column name. --echo # diff --git a/mysql-test/t/alter_table_online.test b/mysql-test/t/alter_table_online.test index be8037abe11..20dbfe9c46a 100644 --- a/mysql-test/t/alter_table_online.test +++ b/mysql-test/t/alter_table_online.test @@ -7,7 +7,7 @@ drop table if exists t1,t2,t3; --enable_warnings # -# Test of things that can be done online +# Test of things that can not be done online # create table t1 (a int not null primary key, b int, c varchar(80), e enum('a','b')); @@ -24,6 +24,13 @@ alter online table t1 comment "new comment"; --error ER_ALTER_OPERATION_NOT_SUPPORTED alter online table t1 rename to t2; +# No OPs + +alter online table t1 algorithm=INPLACE, lock=NONE; +alter online table t1; +alter table t1 algorithm=INPLACE; +alter table t1 lock=NONE; + drop table t1; # diff --git a/mysql-test/t/blackhole.test b/mysql-test/t/blackhole.test index 32d7d70200a..7f394e0f846 100644 --- a/mysql-test/t/blackhole.test +++ b/mysql-test/t/blackhole.test @@ -17,6 +17,16 @@ SELECT 1 FROM t1 WHERE a = ANY (SELECT a FROM t2); DROP TABLE t1, t2; +# +# Bug#19786309 - CRASH IN UNLOCK TABLES AFTER LOCKING AND TRUNCATING TEMPORARY TABLE. +# +create temporary table t1 (a int) engine=blackhole; +lock table t1 write; +truncate table t1; +select * from t1; +unlock tables; +drop temporary table t1; + --echo End of 5.5 tests --echo # diff --git a/mysql-test/t/bootstrap.test b/mysql-test/t/bootstrap.test index 2c6040af7a1..97376eb7412 100644 --- a/mysql-test/t/bootstrap.test +++ b/mysql-test/t/bootstrap.test @@ -60,3 +60,32 @@ SELECT 'bug' as '' FROM INFORMATION_SCHEMA.ENGINES WHERE engine='innodb' and SUPPORT='YES'; --echo End of 5.5 tests + +--source include/not_windows_embedded.inc +--source include/have_example_plugin.inc +# +# Check that --bootstrap can install and uninstall plugins +# +let $PLUGIN_DIR=`select @@plugin_dir`; +--write_file $MYSQLTEST_VARDIR/tmp/install_plugin.sql +install soname 'ha_example'; +uninstall plugin unusable; +EOF +--exec $MYSQLD_BOOTSTRAP_CMD --plugin-dir=$PLUGIN_DIR < $MYSQLTEST_VARDIR/tmp/install_plugin.sql >> $MYSQLTEST_VARDIR/tmp/bootstrap.log 2>&1 +--remove_file $MYSQLTEST_VARDIR/tmp/install_plugin.sql + +# +# Check that installed plugins are *not* automatically loaded in --bootstrap +# +--write_file $MYSQLTEST_VARDIR/tmp/bootstrap_plugins.sql +use test; +create table t1(a int) engine=example; +EOF +--exec $MYSQLD_BOOTSTRAP_CMD --plugin-dir=$PLUGIN_DIR < $MYSQLTEST_VARDIR/tmp/bootstrap_plugins.sql >> $MYSQLTEST_VARDIR/tmp/bootstrap.log 2>&1 +--remove_file $MYSQLTEST_VARDIR/tmp/bootstrap_plugins.sql +flush tables; +show create table t1; +drop table t1; +--replace_result .dll .so +select * from mysql.plugin; +truncate table mysql.plugin; diff --git a/mysql-test/t/bug46261-master.opt b/mysql-test/t/bug46261-master.opt deleted file mode 100644 index 5699a3387b8..00000000000 --- a/mysql-test/t/bug46261-master.opt +++ /dev/null @@ -1 +0,0 @@ ---skip-grant-tables diff --git a/mysql-test/t/bug46261.test b/mysql-test/t/bug46261.test deleted file mode 100644 index 9d8eecf3d52..00000000000 --- a/mysql-test/t/bug46261.test +++ /dev/null @@ -1,16 +0,0 @@ ---source include/not_embedded.inc ---source include/have_example_plugin.inc - ---echo # ---echo # Bug#46261 Plugins can be installed with --skip-grant-tables ---echo # - ---replace_regex /\.dll/.so/ ---error ER_OPTION_PREVENTS_STATEMENT -eval INSTALL PLUGIN example SONAME '$HA_EXAMPLE_SO'; - ---replace_regex /\.dll/.so/ ---error ER_OPTION_PREVENTS_STATEMENT -eval UNINSTALL PLUGIN example; - ---echo End of 5.1 tests diff --git a/mysql-test/t/ctype_binary.test b/mysql-test/t/ctype_binary.test index 4a2646d1db5..3d3f90b444b 100644 --- a/mysql-test/t/ctype_binary.test +++ b/mysql-test/t/ctype_binary.test @@ -20,5 +20,10 @@ SET NAMES binary; --source include/ctype_like_cond_propagation.inc --echo # +--echo # MDEV-7629 Regression: Bit and hex string literals changed column names in 10.0.14 +--echo # +SELECT _binary 0x7E, _binary X'7E', _binary B'01111110'; + +--echo # --echo # End of 10.0 tests --echo # diff --git a/mysql-test/t/ctype_create.test b/mysql-test/t/ctype_create.test index 060c09a0459..61fc5292094 100644 --- a/mysql-test/t/ctype_create.test +++ b/mysql-test/t/ctype_create.test @@ -105,3 +105,39 @@ ALTER DATABASE DEFAULT CHARACTER SET latin2; ALTER DATABASE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa DEFAULT CHARACTER SET latin2; --error 1102 ALTER DATABASE `` DEFAULT CHARACTER SET latin2; +USE test; + +--echo # +--echo # Start of 10.0 tests +--echo # + +--echo # +--echo # MDEV-7387 Alter table xxx CHARACTER SET utf8, CONVERT TO CHARACTER SET latin1 should fail +--echo # +--error ER_CONFLICTING_DECLARATIONS +CREATE TABLE t1 (a VARCHAR(10)) CHARACTER SET DEFAULT, CHARACTER SET utf8; +--error ER_CONFLICTING_DECLARATIONS +CREATE TABLE t1 (a VARCHAR(10)) CHARACTER SET utf8, CHARACTER SET DEFAULT; +CREATE TABLE t1 (a VARCHAR(10)) CHARACTER SET utf8, CHARACTER SET utf8; +DROP TABLE t1; +CREATE TABLE t1 (a VARCHAR(10)) CHARACTER SET DEFAULT, CHARACTER SET DEFAULT; +DROP TABLE t1; + +CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET latin1, b VARCHAR(10) CHARACTER SET utf8); +--error ER_CONFLICTING_DECLARATIONS +ALTER TABLE t1 CONVERT TO CHARACTER SET utf8, CHARACTER SET latin1; +--error ER_CONFLICTING_DECLARATIONS +ALTER TABLE t1 CONVERT TO CHARACTER SET utf8, CHARACTER SET DEFAULT; +--error ER_CONFLICTING_DECLARATIONS +ALTER TABLE t1 CONVERT TO CHARACTER SET latin1, CHARACTER SET utf8; +--error ER_CONFLICTING_DECLARATIONS +ALTER TABLE t1 CONVERT TO CHARACTER SET latin1, CHARACTER SET DEFAULT; +--error ER_CONFLICTING_DECLARATIONS +ALTER TABLE t1 CONVERT TO CHARACTER SET DEFAULT, CHARACTER SET utf8; +--error ER_CONFLICTING_DECLARATIONS +ALTER TABLE t1 CONVERT TO CHARACTER SET DEFAULT, CHARACTER SET latin1; +DROP TABLE t1; + +--echo # +--echo # End of 10.0 tests +--echo # diff --git a/mysql-test/t/ctype_latin1.test b/mysql-test/t/ctype_latin1.test index 336d8ca761d..aeaad2cc026 100644 --- a/mysql-test/t/ctype_latin1.test +++ b/mysql-test/t/ctype_latin1.test @@ -241,5 +241,10 @@ SELECT HEX(a),a FROM t1; DROP TABLE t1; --echo # +--echo # MDEV-7629 Regression: Bit and hex string literals changed column names in 10.0.14 +--echo # +SELECT _latin1 0x7E, _latin1 X'7E', _latin1 B'01111110'; + +--echo # --echo # End of 10.0 tests --echo # diff --git a/mysql-test/t/ctype_uca.test b/mysql-test/t/ctype_uca.test index 95008d83a38..5e8195e4718 100644 --- a/mysql-test/t/ctype_uca.test +++ b/mysql-test/t/ctype_uca.test @@ -507,6 +507,17 @@ select * from t1 where c1 = '=> dž'; select * from t1 where concat(c1) = '=> dž'; drop table t1; + +--echo # +--echo # MDEV-7649 wrong result when comparing utf8 column with an invalid literal +--echo # + +SET NAMES utf8 COLLATE utf8_unicode_ci; +--let ENGINE=MyISAM +--source include/ctype_utf8_ilseq.inc +--let ENGINE=HEAP +--source include/ctype_utf8_ilseq.inc + --echo # --echo # End of 5.5 tests --echo # diff --git a/mysql-test/t/ctype_uca_innodb.test b/mysql-test/t/ctype_uca_innodb.test new file mode 100644 index 00000000000..cb6caff4a03 --- /dev/null +++ b/mysql-test/t/ctype_uca_innodb.test @@ -0,0 +1,25 @@ +# +# Tests for UCA collations with InnoDB +# + +let collation=utf8_unicode_ci; +--source include/have_collation.inc +--source include/have_innodb.inc + + +--echo # +--echo # Start of 5.5 tests +--echo # + + +--echo # +--echo # MDEV-7649 wrong result when comparing utf8 column with an invalid literal +--echo # + +SET NAMES utf8 COLLATE utf8_unicode_ci; +--let ENGINE=InnoDB +--source include/ctype_utf8_ilseq.inc + +--echo # +--echo # End of 5.5 tests +--echo # diff --git a/mysql-test/t/ctype_utf8.test b/mysql-test/t/ctype_utf8.test index 1e9047cca8e..fcd19c112e9 100644 --- a/mysql-test/t/ctype_utf8.test +++ b/mysql-test/t/ctype_utf8.test @@ -1616,6 +1616,42 @@ UNION ALL SELECT 'a' AS id, REPEAT('bla bla', 100) AS body) t1; --echo # +--echo # MDEV-7814 Assertion `args[0]->fixed' fails in Item_func_conv_charset::Item_func_conv_charset +--echo # +CREATE TABLE t1(a CHAR(1) CHARACTER SET latin1, b INT NOT NULL); +CREATE TABLE t2(a CHAR(1) CHARACTER SET utf8 COLLATE utf8_general_ci, b INT NOT NULL); +SELECT (SELECT t2.a FROM t2 WHERE t2.a=t1.a) AS aa, b, COUNT(b) FROM t1 GROUP BY aa; +DROP TABLE t1,t2; + +--echo # +--echo # MDEV-7649 wrong result when comparing utf8 column with an invalid literal +--echo # + +SET NAMES utf8 COLLATE utf8_general_ci; +--let ENGINE=InnoDB +--source include/ctype_utf8_ilseq.inc +--let ENGINE=MyISAM +--source include/ctype_utf8_ilseq.inc +--let ENGINE=HEAP +--source include/ctype_utf8_ilseq.inc + +--echo # +--echo # MDEV-8067 correct fix for MySQL Bug # 19699237: UNINITIALIZED VARIABLE IN ITEM_FIELD::STR_RESULT +--echo # +CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8); +CREATE TABLE t2 (a VARCHAR(10) CHARACTER SET latin1); +INSERT INTO t1 VALUES ('aaa'); +INSERT INTO t2 VALUES ('aaa'); +SELECT (SELECT CONCAT(a),1 FROM t1) <=> (SELECT CONCAT(a),1 FROM t2); +INSERT INTO t1 VALUES ('aaa'); +INSERT INTO t2 VALUES ('aaa'); +# Running the below query crashed with two rows +--error ER_SUBQUERY_NO_1_ROW +SELECT (SELECT CONCAT(a),1 FROM t1) <=> (SELECT CONCAT(a),1 FROM t2); +DROP TABLE t1, t2; + + +--echo # --echo # End of 5.5 tests --echo # @@ -1744,6 +1780,11 @@ EXECUTE stmt USING @b,@b; DEALLOCATE PREPARE stmt; DROP TABLE t1; +--echo # +--echo # MDEV-7629 Regression: Bit and hex string literals changed column names in 10.0.14 +--echo # +SELECT _utf8 0x7E, _utf8 X'7E', _utf8 B'01111110'; + let $ctype_unescape_combinations=selected; --source include/ctype_unescape.inc diff --git a/mysql-test/t/derived.test b/mysql-test/t/derived.test index 61ae3695a1f..ddce7f55292 100644 --- a/mysql-test/t/derived.test +++ b/mysql-test/t/derived.test @@ -492,5 +492,17 @@ 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-6892: WHERE does not apply +--echo # +create table t1 (id int); +create table t2 (id int); +insert into t1 values(1),(2),(3); +insert into t2 values(4),(5),(6); +#explain extended +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; diff --git a/mysql-test/t/dyncol.test b/mysql-test/t/dyncol.test index 65a94dcb49e..86dcee8148a 100644 --- a/mysql-test/t/dyncol.test +++ b/mysql-test/t/dyncol.test @@ -657,6 +657,13 @@ SELECT select column_get(column_create(1, "18446744073709552001" as char), 1 as int); --echo # +--echo # MDEV-7505 - Too large scale in DECIMAL dynamic column getter crashes +--echo # mysqld +--echo # +--error ER_TOO_BIG_SCALE +SELECT COLUMN_GET(`x`, 'y' AS DECIMAL(5,34)); + +--echo # --echo # test of symbolic names --echo # --echo # creation test (names) diff --git a/mysql-test/t/empty_server_name-8224.test b/mysql-test/t/empty_server_name-8224.test new file mode 100644 index 00000000000..528bce5dac5 --- /dev/null +++ b/mysql-test/t/empty_server_name-8224.test @@ -0,0 +1,9 @@ +# +# MDEV-8224 Server crashes in get_server_from_table_to_cache on empty name +# +--source include/not_embedded.inc +create server '' foreign data wrapper w2 options (host '127.0.0.1'); +--exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect +--shutdown_server 10 +--source include/wait_until_disconnected.inc +--exec echo "restart" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect diff --git a/mysql-test/t/error_simulation-master.opt b/mysql-test/t/error_simulation-master.opt new file mode 100644 index 00000000000..c9f8d7bd0c0 --- /dev/null +++ b/mysql-test/t/error_simulation-master.opt @@ -0,0 +1 @@ +--tmpdir=$MYSQLTEST_VARDIR/tmp diff --git a/mysql-test/t/error_simulation.test b/mysql-test/t/error_simulation.test index 58ead1e3ced..7c343ed6566 100644 --- a/mysql-test/t/error_simulation.test +++ b/mysql-test/t/error_simulation.test @@ -1,4 +1,5 @@ -- source include/have_debug.inc +--source include/not_embedded.inc # # Bug #28499: crash for grouping query when tmp_table_size is too small @@ -112,3 +113,38 @@ DROP TABLE t1,t2; --echo # --echo # End of 5.1 tests --echo # + + +--echo # +--echo # BUG#11747548:DETECT ORPHAN TEMP-POOL FILES, AND HANDLE GRACEFULLY. +--echo # + +--echo #Set up. +CREATE TABLE pid_table(pid_no INT); +CREATE TABLE t1 (a BLOB); +INSERT INTO t1 VALUES (1), (2); + +--echo #Create MYD and MYI files for intrinsic temp table. +--let $pid_file=`SELECT @@pid_file` +--replace_result $pid_file pid_file +--eval LOAD DATA LOCAL INFILE '$pid_file' INTO TABLE pid_table +--let $temp_file= `SELECT CONCAT('#sql_', LCASE(HEX(pid_no)), '_0') FROM pid_table` +--write_file $MYSQLTEST_VARDIR/tmp/$temp_file.MYD +EOF +--write_file $MYSQLTEST_VARDIR/tmp/$temp_file.MYI +EOF +--write_file $MYSQLTEST_VARDIR/tmp/$temp_file.MAD +EOF +--write_file $MYSQLTEST_VARDIR/tmp/$temp_file.MAI +EOF + +--echo #Reports an error since the temp file already exists. +--replace_regex /.*Can't create\/write *.*/Can't create or write to file/ +#--error 1 +SELECT a FROM t1 ORDER BY rand(1); + +--echo #With patch, the query executes successfully. +SELECT a FROM t1 ORDER BY rand(1); + +--echo #cleanup +DROP TABLE t1, pid_table; diff --git a/mysql-test/t/explain_non_select.test b/mysql-test/t/explain_non_select.test index f05373c312e..e36fd518b61 100644 --- a/mysql-test/t/explain_non_select.test +++ b/mysql-test/t/explain_non_select.test @@ -197,4 +197,14 @@ select 'OK'; drop function f1; drop table t1; +--echo # +--echo # MDEV-7038: Assertion `status_var.memory_used == 0' failed in THD::~THD() +--echo # on disconnect after executing EXPLAIN for multi-table UPDATE +--echo # +CREATE TABLE t1 (a INT); +CREATE VIEW v1 AS SELECT * FROM t1; +INSERT INTO t1 VALUES (1),(2); +EXPLAIN UPDATE v1, mysql.user SET v1.a = v1.a + 1; +DROP TABLE t1; +DROP VIEW v1; diff --git a/mysql-test/t/file_contents.test b/mysql-test/t/file_contents.test index f52f0a93c82..186394df4e3 100644 --- a/mysql-test/t/file_contents.test +++ b/mysql-test/t/file_contents.test @@ -17,7 +17,7 @@ if ($dir_bin eq '/usr/') { $dir_docs =~ s|/lib|/share/doc|; if(-d "$dir_docs/packages") { # SuSE: "packages/" in the documentation path - $dir_docs = glob "$dir_docs/packages/MySQL-server*"; + $dir_docs = glob "$dir_docs/packages/*-server*"; } else { # RedHat: version number in directory name $dir_docs = glob "$dir_docs/MySQL-server*"; @@ -27,7 +27,7 @@ if ($dir_bin eq '/usr/') { $dir_docs = "$dir_bin/share/doc"; if(-d "$dir_docs/packages") { # SuSE: "packages/" in the documentation path - $dir_docs = glob "$dir_docs/packages/MySQL-server*"; + $dir_docs = glob "$dir_docs/packages/*-server*"; } else { # RedHat/Debian: version number in directory name $dir_docs = glob "$dir_docs/mariadb-server-*"; @@ -36,7 +36,7 @@ if ($dir_bin eq '/usr/') { # Slackware $dir_docs = glob "$dir_bin/doc/mariadb-[0-9]*" unless -d $dir_docs; } else { - # tar.gz package, Windows, or developer work (in BZR) + # tar.gz package, Windows, or developer work (in git) $dir_docs = $dir_bin; if(-d "$dir_docs/docs") { $dir_docs = "$dir_docs/docs"; # package @@ -49,7 +49,7 @@ $found_revision = "No line 'revision-id: .....' in $dir_docs/INFO_SRC"; open(I_SRC,"<","$dir_docs/INFO_SRC") or print "Cannot open 'INFO_SRC' in '$dir_docs' (starting from bindir '$dir_bin')\n"; while(defined ($line = <I_SRC>)) { if ($line =~ m|^MySQL source \d+\.\d\.\d+|) {$found_version = "Found MySQL version number";} - if ($line =~ m|^revision-id: .*@.*-2\d{13}-\w+$|) {$found_revision = "Found BZR revision id";} + if ($line =~ m|^commit: \w{40}$|) {$found_revision = "Found GIT revision id";} } close I_SRC; print "INFO_SRC: $found_version / $found_revision\n"; diff --git a/mysql-test/t/filesort_bad_i_s-7585.test b/mysql-test/t/filesort_bad_i_s-7585.test new file mode 100644 index 00000000000..daf70acdd52 --- /dev/null +++ b/mysql-test/t/filesort_bad_i_s-7585.test @@ -0,0 +1,14 @@ +# +# MDEV-7585 Assertion `thd->is_error() || kill_errno || thd->killed == ABORT_QUERY' failed in ha_rows filesort +# +SET sql_mode=STRICT_TRANS_TABLES; + +CREATE TABLE t0 (i INT) ENGINE=MyISAM; +CREATE VIEW v1 AS SELECT * FROM t0; +CREATE VIEW v2 AS SELECT * FROM v1; +DROP VIEW IF EXISTS v1; + +CREATE TABLE t1 (i INT) ENGINE=MyISAM; +INSERT INTO t1 SELECT TABLE_ROWS FROM information_schema.tables ORDER BY TABLE_ROWS; +DROP VIEW v2; +DROP TABLE t1, t0; diff --git a/mysql-test/t/func_gconcat.test b/mysql-test/t/func_gconcat.test index 7643676ea61..42a30760a86 100644 --- a/mysql-test/t/func_gconcat.test +++ b/mysql-test/t/func_gconcat.test @@ -803,3 +803,21 @@ create table t1 (a char(1) character set utf8); insert into t1 values ('a'),('b'); select 1 from t1 where a in (select group_concat(a) from t1); drop table t1; + +# +# MDEV-7820 Server crashes in in my_strcasecmp_utf8 on subquery in ORDER BY clause of GROUP_CONCAT +# +CREATE TABLE t1 (f1 VARCHAR(10)) ENGINE=MyISAM; +INSERT INTO t1 VALUES ('a'),('b'); + +CREATE TABLE t2 (f2 VARCHAR(10)) ENGINE=MyISAM; +INSERT INTO t2 VALUES ('c'); + +CREATE TABLE t3 (f3 VARCHAR(10)) ENGINE=MyISAM; +INSERT INTO t3 VALUES ('d'),('e'); + +SELECT GROUP_CONCAT( f2 ORDER BY ( f2 IN ( SELECT f1 FROM t1 WHERE f1 <= f2 ) ) ) AS field +FROM ( SELECT * FROM t2 ) AS sq2, t3 +ORDER BY field; + +drop table t3, t2, t1; diff --git a/mysql-test/t/func_regexp_pcre.test b/mysql-test/t/func_regexp_pcre.test index 6710f5cf096..c9b4c10007d 100644 --- a/mysql-test/t/func_regexp_pcre.test +++ b/mysql-test/t/func_regexp_pcre.test @@ -402,3 +402,27 @@ SET default_regex_flags=DEFAULT; --echo # MDEV-6965 non-captured group \2 in regexp_replace --echo # SELECT REGEXP_REPLACE('1 foo and bar', '(\\d+) foo and (\\d+ )?bar', '\\1 this and \\2that'); + +--echo # +--echo # MDEV-8102 REGEXP function fails to match hex values when expression is stored as a variable +--echo # + +--echo # Testing a warning +SET NAMES latin1; +SET @regCheck= '\\xE0\\x01'; +SELECT 0xE001 REGEXP @regCheck; + +--echo # Testing workaround N1: This makes the pattern to be a binary string: +SET NAMES latin1; +SET @regCheck= X'E001'; +SELECT 0xE001 REGEXP @regCheck; + +--echo # Testing workaround N2: This also makes the pattern to be a binary string, using a different syntax: +SET NAMES latin1; +SET @regCheck= _binary '\\xE0\\x01'; +SELECT 0xE001 REGEXP @regCheck; + +--echo # Testing workarond N3: This makes derivation of the subject string stronger (IMLICIT instead of COERCIBLE) +SET NAMES latin1; +SET @regCheck= '\\xE0\\x01'; +SELECT CAST(0xE001 AS BINARY) REGEXP @regCheck; diff --git a/mysql-test/t/func_regexp_pcre_debug.test b/mysql-test/t/func_regexp_pcre_debug.test new file mode 100644 index 00000000000..c2581fa4110 --- /dev/null +++ b/mysql-test/t/func_regexp_pcre_debug.test @@ -0,0 +1,6 @@ +--source include/have_debug.inc + +SET debug_dbug='+d,pcre_exec_error_123'; +SELECT 'a' RLIKE 'a'; +SET debug_dbug=''; +SELECT 'a' RLIKE 'a'; diff --git a/mysql-test/t/func_time.test b/mysql-test/t/func_time.test index 2b189765bbc..3fb87e91b17 100644 --- a/mysql-test/t/func_time.test +++ b/mysql-test/t/func_time.test @@ -1649,3 +1649,44 @@ INSERT INTO t1 VALUES (17, NULL); INSERT INTO t1 VALUES (18, '2010-10-13'); SELECT a.id,a.date1,FROM_DAYS(TO_DAYS(a.date1)-10) as date2, DATE_ADD(a.date1,INTERVAL -10 DAY),TO_DAYS(a.date1)-10 FROM t1 a ORDER BY a.id; DROP TABLE t1; + + +--echo # +--echo # Start of 10.0 tests +--echo # + +--echo # +--echo # MDEV-8205 timediff returns null when comparing decimal time to time string value +--echo # + +# 1h difference +SELECT + TIMEDIFF('2014-01-01 00:00:00' , '2014-01-01 01:00:00' ) AS str_str, + TIMEDIFF('2014-01-01 00:00:00' , 20140101010000.000 ) AS str_dec, + TIMEDIFF(20140101000000.000 , 20140101010000.000 ) AS dec_dec, + TIMEDIFF(20140101000000.000 , '2014-01-01 01:00:00' ) AS dec_str; + +# 1D1h difference +SELECT + TIMEDIFF('2014-01-01 00:00:00' , '2014-01-02 01:00:00' ) AS str_str, + TIMEDIFF('2014-01-01 00:00:00' , 20140102010000.000 ) AS str_dec, + TIMEDIFF(20140101000000.000 , 20140102010000.000 ) AS dec_dec, + TIMEDIFF(20140101000000.000 , '2014-01-02 01:00:00' ) AS dec_str; + +# 1M1D1h difference +SELECT + TIMEDIFF('2014-01-01 00:00:00' , '2014-02-02 01:00:00' ) AS str_str, + TIMEDIFF('2014-01-01 00:00:00' , 20140202010000.000 ) AS str_dec, + TIMEDIFF(20140101000000.000 , 20140202010000.000 ) AS dec_dec, + TIMEDIFF(20140101000000.000 , '2014-02-02 01:00:00' ) AS dec_str; + +# 2M1D1h difference +SELECT + TIMEDIFF('2014-01-01 00:00:00' , '2014-03-02 01:00:00' ) AS str_str, + TIMEDIFF('2014-01-01 00:00:00' , 20140302010000.000 ) AS str_dec, + TIMEDIFF(20140101000000.000 , 20140302010000.000 ) AS dec_dec, + TIMEDIFF(20140101000000.000 , '2014-03-02 01:00:00' ) AS dec_str; + +--echo # +--echo # End of 10.0 tests +--echo # diff --git a/mysql-test/t/galera_sst_mode.test b/mysql-test/t/galera_sst_mode.test deleted file mode 100644 index dd8f9531c9d..00000000000 --- a/mysql-test/t/galera_sst_mode.test +++ /dev/null @@ -1,43 +0,0 @@ -# Embedded server doesn't support external clients ---source include/not_embedded.inc -# Binlog is required ---source include/have_log_bin.inc - ---echo # ---echo # Test for mysqldump's galera-sst-mode option ---echo # - ---echo # ---echo # MDEV-6490: mysqldump unknown option --galera-sst-mode ---echo # -CREATE DATABASE bug6490; -USE bug6490; -CREATE TABLE t1(c1 INT); -INSERT INTO t1 values (1); -INSERT INTO t1 values (2); - ---echo # Save the current gtid_binlog_state. ---let $before= `SELECT @@global.gtid_binlog_state` - ---echo # Take a dump of bug6490 database ---exec $MYSQL_DUMP --galera-sst-mode bug6490 > $MYSQLTEST_VARDIR/tmp/bug6490.sql -DROP TABLE t1; - ---echo # Load the dump -RESET MASTER; ---exec $MYSQL -uroot bug6490 < $MYSQLTEST_VARDIR/tmp/bug6490.sql - -SELECT * from t1; - ---echo # Compare the two gtid_binlog_state's ---let $after= `SELECT @@global.gtid_binlog_state` -if (`SELECT STRCMP($before, $after)`) -{ - --die ERROR: The two gtid_binlog_state's did not match. -} - ---echo # Cleanup ---remove_file $MYSQLTEST_VARDIR/tmp/bug6490.sql -DROP DATABASE bug6490; - ---echo # End of test diff --git a/mysql-test/t/gis.test b/mysql-test/t/gis.test index 2625ec5d3c9..9378ea55377 100644 --- a/mysql-test/t/gis.test +++ b/mysql-test/t/gis.test @@ -7,6 +7,7 @@ --disable_warnings DROP TABLE IF EXISTS t1, gis_point, gis_line, gis_polygon, gis_multi_point, gis_multi_line, gis_multi_polygon, gis_geometrycollection, gis_geometry; +DROP VIEW IF EXISTS v1; --enable_warnings CREATE TABLE gis_point (fid INTEGER NOT NULL PRIMARY KEY, g POINT); @@ -1477,4 +1478,17 @@ create table t1 (pt point); insert into t1 values(Geomfromtext('POLYGON((1 1, 2 2, 2 1, 1 1))')); drop table t1; ---echo End of 5.5 tests +# +# MDEV-7516 Assertion `!cur_p->event' failed in Gcalc_scan_iterator::arrange_event(int, int) +# +SELECT st_astext(ST_Buffer(ST_PolygonFromText('POLYGON((3 5, 2 4, 2 5, 3 5))'), -100)); + +# +# MDEV-7779 View definition changes upon creation +# +CREATE VIEW v1 AS SELECT POINT(1,1) AS p; +SHOW CREATE VIEW v1; +SELECT ASTEXT(p) FROM v1; +DROP VIEW v1; + +# --echo End of 5.5 tests diff --git a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test index 4b1cb82d0f9..4b992faa306 100644 --- a/mysql-test/t/group_by.test +++ b/mysql-test/t/group_by.test @@ -1515,6 +1515,17 @@ let $query= SELECT MIN(a), b FROM t1 WHERE t1.b = 'a' GROUP BY b; --echo DROP TABLE t1; + +# +# Bug#19880368 : GROUP_CONCAT CRASHES AFTER DUMP_LEAF_KEY +# +create table t1 (a int, b int); +insert into t1 values (1,11), (1,12), (2,22),(2,23), (4,44),(4,45); +create table t2 (c int, d int); +insert into t2 values (1,11), (2,22), (4,44); +select distinct a,sum(b), (select d from t2 where c=a order by max(b) limit 1) from t1 group by a order by max(b); +drop table t1, t2; + --echo # --echo # LP bug#993726 Wrong result from a query with ALL subquery predicate in WHERE --echo # diff --git a/mysql-test/t/group_by_innodb.test b/mysql-test/t/group_by_innodb.test index 75ee3d0802a..e072a94fada 100644 --- a/mysql-test/t/group_by_innodb.test +++ b/mysql-test/t/group_by_innodb.test @@ -85,4 +85,44 @@ a.oid=b.oid WHERE a.oidGroup=1 GROUP BY a.oid; DROP TABLE t1, t2; +--echo # +--echo # MDEV-7193: Incorrect Query Result (MySQL Bug 68897) in MariaDB 10.0.14 +--echo # (fixed by MDEV-5719) +--echo # + +CREATE TABLE `t1` ( + `param` int(11) NOT NULL, + `idx` int(11) NOT NULL, + `text` varchar(255) default NULL, + PRIMARY KEY (`param`,`idx`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +INSERT INTO `t1` (`param`, `idx`, `text`) VALUES +(1, 0, 'select'), +(1, 1, 'Kabel mit Stecker 5-polig'), +(1, 2, 'Kabel ohne Stecker'), +(2, 0, 'number'), +(2, 1, '22'), +(2, 2, '25'); +CREATE TABLE `t2` ( + `id` int PRIMARY KEY +); + +INSERT INTO t2 VALUES (1),(2),(3),(4); +SELECT t2.id AS id, T.text AS xtext,GROUP_CONCAT(T3.text) AS optionen +FROM t2 +LEFT JOIN t1 AS T ON(T.param=t2.id AND T.idx=0 ) +LEFT JOIN t1 AS T3 ON(T3.param=t2.id AND T3.idx>0 ) +GROUP BY t2.id +ORDER BY id ASC; + +SELECT t2.id AS id, T.text AS xtext,GROUP_CONCAT(T3.text) AS optionen +FROM t2 +LEFT JOIN t1 AS T ON(T.param=t2.id AND T.idx=0 ) +LEFT JOIN t1 AS T3 ON(T3.param=t2.id AND T3.idx>0 ) +GROUP BY t2.id +ORDER BY id DESC; + +DROP TABLE t1, t2; + --echo # End of tests diff --git a/mysql-test/t/having.test b/mysql-test/t/having.test index cd6a4c9d328..0f6be0b0ec6 100644 --- a/mysql-test/t/having.test +++ b/mysql-test/t/having.test @@ -694,6 +694,41 @@ DROP TABLE t1; --echo End of 5.3 tests --echo # +--echo # Bug MDEV-7301: Unknown column quoted with backticks in HAVING clause when using function. +--echo # Bug#16221433 MYSQL REJECTS QUERY DUE TO BAD RESOLUTION OF NAMES IN HAVING; VIEW UNREADABLE +--echo # + +CREATE TABLE `t1` ( + `id` int(11) NOT NULL, + `title` varchar(45) DEFAULT NULL, + PRIMARY KEY (`id`) +) DEFAULT CHARSET=utf8; + +INSERT INTO `t1` VALUES ('1', 'Just for fun'); +INSERT INTO `t1` VALUES ('2', 'Wait until a sunhine'); +INSERT INTO `t1` VALUES ('3', 'Take a new turn'); + +SELECT `id`, SHA1(`title`) AS `column_1` +FROM `t1` +HAVING `column_1` LIKE '8%'; + +--echo expected 1 row(s) returned + +SELECT `id`, SHA1(`title`) AS `column_1` +FROM `t1` +HAVING UPPER(column_1) LIKE '8%'; + +--echo expected -- 1 row(s) returned + +SELECT `id`, SHA1(`title`) AS `column_1` +FROM `t1` +HAVING UPPER(`column_1`) LIKE '8%'; + +--echo expected -- 1 row(s) returned not ER_BAD_FIELD_ERROR + +drop table t1; + +--echo # --echo # Bug mdev-5160: two-way join with HAVING over the second table --echo # @@ -708,4 +743,3 @@ SELECT * FROM t1 JOIN t2 ON c1 = c2 HAVING c2 > 'a' ORDER BY c2 LIMIT 1; DROP TABLE t1,t2; --echo End of 10.0 tests - diff --git a/mysql-test/t/information_schema.test b/mysql-test/t/information_schema.test index 9fdc29e88de..b2e18097c65 100644 --- a/mysql-test/t/information_schema.test +++ b/mysql-test/t/information_schema.test @@ -1819,6 +1819,23 @@ drop view v1; --echo # Clean-up. drop database mysqltest; +--echo # +--echo # Test for bug #16869534 - "QUERYING SUBSET OF COLUMNS DOESN'T USE TABLE +--echo # CACHE; OPENED_TABLES INCREASES" +--echo # +--disable_result_log +SELECT * FROM INFORMATION_SCHEMA.TABLES; +--enable_result_log +SELECT VARIABLE_VALUE INTO @val1 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE + VARIABLE_NAME LIKE 'Opened_tables'; +--disable_result_log +SELECT ENGINE FROM INFORMATION_SCHEMA.TABLES; +--enable_result_log +--echo # The below SELECT query should give same output as above SELECT query. +SELECT VARIABLE_VALUE INTO @val2 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE + VARIABLE_NAME LIKE 'Opened_tables'; +--echo # The below select should return '1' +SELECT @val1 = @val2; --echo # --echo # End of 5.5 tests diff --git a/mysql-test/t/information_schema2.test b/mysql-test/t/information_schema2.test index c2479087f47..d577cdc1e4d 100644 --- a/mysql-test/t/information_schema2.test +++ b/mysql-test/t/information_schema2.test @@ -7,3 +7,16 @@ select variable_name from information_schema.session_status where variable_name select variable_name from information_schema.session_variables where variable_name = (select variable_name from information_schema.session_variables where variable_name = 'basedir'); +# +# information_schema tables inside subqueries, they should not be re-populated +# (i_s.columns needs to scan i_s itself, creating a tmp table for every i_s +# table. if it's re-populated, it'll do that multiple times) +# +create table t1 (a char); +insert t1 values ('a'),('t'),('z'); +flush status; +select a, exists (select 1 from information_schema.columns where table_schema=concat('tes',a)) from t1; +# fix the result in ps-protocol +--replace_result 44 43 +show status like 'created_tmp_tables'; +drop table t1; diff --git a/mysql-test/t/join_cache.test b/mysql-test/t/join_cache.test index 0e4610b9f54..7d873c555f6 100644 --- a/mysql-test/t/join_cache.test +++ b/mysql-test/t/join_cache.test @@ -3848,6 +3848,18 @@ set join_buffer_space_limit=default; drop table t1; +--echo # +--echo # MDEV-6687: Assertion `0' failed in Protocol::end_statement on query +--echo # +SET join_cache_level = 3; +--echo # The following should have +--echo # - table order PROFILING,user, +--echo # - table user accessed with hash_ALL: +explain +SELECT * FROM INFORMATION_SCHEMA.PROFILING, mysql.user WHERE password_expired = PAGE_FAULTS_MINOR; + +set join_cache_level=default; + # The following command must be the last one the file # this must be the last command in the file set @@optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/t/log_errchk.test b/mysql-test/t/log_errchk.test index e4bc6a841dd..2808458e9f1 100644 --- a/mysql-test/t/log_errchk.test +++ b/mysql-test/t/log_errchk.test @@ -6,7 +6,8 @@ # Bug#14757009 : WHEN THE GENERAL_LOG IS A SOCKET AND THE READER GOES AWAY, # MYSQL QUITS WORKING. # -call mtr.add_suppression("Could not use"); +# MDEV-6870 Not possible to use FIFO file as a general_log file +# --let $gen_log_file= $MYSQLTEST_VARDIR/tmp/general_log.fifo --let $slow_query_log_file= $MYSQLTEST_VARDIR/tmp/slow_log.fifo @@ -15,10 +16,9 @@ call mtr.add_suppression("Could not use"); --exec mkfifo $gen_log_file --exec mkfifo $slow_query_log_file ---echo # Case 1: Setting fife file to general_log_file and slow_query_log_file +--echo # Case 1: Setting fifo file to general_log_file and slow_query_log_file --echo # system variable. -# Only regular files can be set to general log. Setting fifo file to general log -# reports an error. +# Setting fifo file to general log reports an error because the other end is closed --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR --error ER_WRONG_VALUE_FOR_VAR --eval SET GLOBAL general_log_file="$gen_log_file"; @@ -38,11 +38,12 @@ call mtr.add_suppression("Could not use"); --exec echo "restart: --general-log-file=$gen_log_file --slow-query-log-file=$slow_query_log_file" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect --source include/wait_until_connected_again.inc -# With fix error should be reported in the error log file if file is not a -# regular file. +# Error 6 is reported, because the other end is closed +call mtr.add_suppression("Could not use .* for logging \\(error 6\\)"); +call mtr.add_suppression("File '.*' not found \\(Errcode: 6 "); --perl my $file= $ENV{'GREP_FILE'}; - my $pattern= "Turning logging off for the whole duration"; + my $pattern= "for logging \\(error 6\\)\\. Turning logging off for the whole duration"; open(FILE, "$file") or die("Unable to open $file: $!\n"); my $count = 0; while (<FILE>) { diff --git a/mysql-test/t/multi_update.test b/mysql-test/t/multi_update.test index 4f8736f0c2d..c013938416e 100644 --- a/mysql-test/t/multi_update.test +++ b/mysql-test/t/multi_update.test @@ -849,6 +849,212 @@ update t1 join v3 using (id) set t1.a=0; drop view v1, v2, v3; drop table t2, t3, t1; +--echo # +--echo # MDEV-7613: MariaDB 5.5.40 server crash on update table left join +--echo # with a view +--echo # + +CREATE TABLE `t1` ( + `f1` varchar(6) COLLATE latin1_general_ci DEFAULT NULL, + `f2` varchar(6) COLLATE latin1_general_ci DEFAULT NULL, + `f3` varchar(7) COLLATE latin1_general_ci DEFAULT NULL, + `f4` varchar(15) COLLATE latin1_general_ci DEFAULT NULL, + `f5` datetime DEFAULT NULL, + `f6` varchar(2) COLLATE latin1_general_ci DEFAULT NULL, + `f7` varchar(2) COLLATE latin1_general_ci DEFAULT NULL, + `ff1` int(1) DEFAULT NULL, + `ff2` int(1) DEFAULT NULL, + `ff3` int(1) DEFAULT NULL, + `ff4` int(1) DEFAULT NULL, + `ff5` int(1) DEFAULT NULL, + `ff6` int(1) DEFAULT NULL, + `ff7` int(1) DEFAULT NULL, + `ff8` int(2) DEFAULT NULL, + `ff9` int(1) DEFAULT NULL, + `ff10` int(1) DEFAULT NULL, + `ff11` int(1) DEFAULT NULL, + `ff12` int(1) DEFAULT NULL, + `ff13` int(1) DEFAULT NULL, + `ff14` int(1) DEFAULT NULL, + `ff15` int(1) DEFAULT NULL, + `f8` varchar(70) COLLATE latin1_general_ci DEFAULT NULL, + `f9` varchar(20) COLLATE latin1_general_ci DEFAULT NULL, + `f10` varchar(50) COLLATE latin1_general_ci NOT NULL, + `f11` varchar(50) COLLATE latin1_general_ci DEFAULT NULL, + `f12` varchar(255) COLLATE latin1_general_ci DEFAULT NULL, + `f13` text COLLATE latin1_general_ci, + `f14` time DEFAULT NULL, + `f15` varchar(30) COLLATE latin1_general_ci DEFAULT NULL, + `fg1` int(11) DEFAULT NULL, + `fg2` int(11) DEFAULT NULL, + `fg3` int(11) DEFAULT NULL, + `fg4` int(11) DEFAULT NULL, + `fg5` int(11) DEFAULT NULL, + `fg6` int(11) DEFAULT NULL, + `fg7` int(11) DEFAULT NULL, + `fg9` int(11) DEFAULT NULL, + `fg10` int(11) DEFAULT NULL, + `fg11` int(11) DEFAULT NULL, + `fg12` int(11) DEFAULT NULL, + `fg13` int(11) DEFAULT NULL, + `fg14` int(11) DEFAULT NULL, + `fg15` int(11) DEFAULT NULL, + `f16` double DEFAULT NULL, + `f17` double DEFAULT NULL, + `f18` int(11) DEFAULT NULL, + `f19` int(11) DEFAULT NULL, + `f20` varchar(255) COLLATE latin1_general_ci DEFAULT NULL, + `f21` int(11) DEFAULT NULL, + `f22` int(11) DEFAULT NULL, + `f23` int(11) DEFAULT NULL, + `f24` double DEFAULT NULL, + `f25` int(11) DEFAULT NULL, + `f26` double DEFAULT NULL, + `f27` int(11) DEFAULT NULL, + `f28` int(11) DEFAULT NULL, + `f29` double DEFAULT NULL, + `f30` int(11) DEFAULT NULL, + `f31` double DEFAULT NULL, + `PZ` double DEFAULT NULL, + `f32` varchar(50) COLLATE latin1_general_ci DEFAULT NULL, + `f33` varchar(50) COLLATE latin1_general_ci DEFAULT NULL, + `f34` varchar(50) COLLATE latin1_general_ci DEFAULT NULL, + `f35` varchar(30) COLLATE latin1_general_ci DEFAULT NULL, + `f36` varchar(20) COLLATE latin1_general_ci DEFAULT NULL, + `f37` varchar(50) COLLATE latin1_general_ci DEFAULT NULL, + `f20_2` varchar(20) COLLATE latin1_general_ci DEFAULT NULL, + `f38` varchar(30) COLLATE latin1_general_ci DEFAULT NULL COMMENT 'Email = E-Mail / Whitemail = Brief', + `insert_ts` timestamp NULL DEFAULT CURRENT_TIMESTAMP, + PRIMARY KEY (`f10`), + KEY `f5_f12` (`f5`,`f12`), + KEY `f5_f20` (`f5`,`f20`), + KEY `f5_f33` (`f5`,`f33`) +) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci ROW_FORMAT=COMPACT; + +INSERT INTO `t1` VALUES ('2011/2','201105','2011/19','gstfbnfr','2011-05-06 +00:00:00','gg','Ag',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,0,'','','','','','','21:56:28','',0,0,0,0,0,0,0,0,0,0,0,0,0,0,NULL,NULL,0,0,'Dffgult',1,0,0,NULL,0,NULL,0,0,NULL,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'ggggil',NULL),('2008/4','200812','2008/50','hgckbgfx','2008-12-08 +00:00:00','gg','Ag',2,NULL,2,1,1,1,1,24,1,NULL,1,1,1,2,0,'gusschlifßlich +zugg +gflffonifrfn','88.77.79.214','10001614','fg-gtgggggdgtfn','fg-gtgggggdgtfn','birgit.tfrpfllf@gggx.df','11:55:21',NULL,1,0,1,1,1,1,1,1,0,1,1,1,0,0,NULL,NULL,0,4,'ffrtrgg',1,6,10,1.66666666666667,4,1,10,14,1.4,1,NULL,NULL,'out',NULL,NULL,'49','ggobilcogg','k.A.',NULL,'ggggil',NULL),('2008/4','200812','2008/51','hgckbgfx','2008-12-15 +00:00:00','gg','Ag',4,5,5,4,5,5,5,NULL,4,5,1,1,1,4,0,'gusschlifßlich zugg +gflffonifrfn','79.197.185.64','10001686','fg-gtgggggdgtfn','fg-gtgggggdgtfn','kgtjg@swfftys.df','09:28:42',NULL,1,1,1,1,1,1,1,1,1,1,1,1,0,0,NULL,NULL,0,5,'ffrtrgg',1,7,11,4.71428571428571,16,1.2,12,49,4.08111111111111,1,NULL,NULL,'out',NULL,NULL,'49','ggobilcogg','k.A.',NULL,'ggggil',NULL),('2008/4','200812','2008/50','nufchti','2008-12-08 +00:00:00','gg','Ag',4,1,1,5,5,5,5,12,4,5,1,1,2,1,0,'gusschlifßlich zugg +gflffonifrfn','89.54.151.216','10001700','fg-gtgggggdgtfn','fg-gtgggggdgtfn','H_K2006@frffnft.df','16:41:45',NULL,1,1,1,1,1,1,1,1,1,1,1,1,0,0,NULL,NULL,0,5,'ffrtrgg',1,7,10,4.28571428571429,11,2.6,12,41,1.58111111111111,1,NULL,NULL,'ffrtrgg +Bgckofficf 5','vb5','Nufchtfr, +Iris','49','ggobilcogg','grfurt','Intfrn','ggggil',NULL),('2008/4','200812','2008/50','junghdro','2008-12-11 +00:00:00','Do','Ag',2,2,5,5,4,4,2,72,2,5,2,2,1,1,0,'gusschlifßlich zugg +gflffonifrfn','84.61.20.216','10001849','fg-ggriff','fg-ggriff','schofnf-glftfr@grcor.df','20:18:05',NULL,1,1,1,1,1,1,1,1,1,1,1,1,0,0,NULL,NULL,0,5,'ffrtrgg',1,7,24,1.42857142857141,12,2.4,12,16,1,1,NULL,NULL,'ffrtrgg +Bgckofficf 5','vb5','Junghfinrich, +Dorothfg','49','ggobilcogg','grfurt','Intfrn','ggggil',NULL),('2008/4','200812','2008/50','fbflktj','2008-12-08 +00:00:00','gg','Ag',4,2,2,5,1,1,1,24,NULL,NULL,NULL,NULL,NULL,0,0,'Kgggfrg +bzw. DigiCggg +Funktion','217.84.62.6','10001888','fg-Kündigungfn','fg-Kündigungfn','f.frofschkf@gggx.df','21:05:59',NULL,1,1,1,1,1,1,1,0,0,0,0,0,0,0,NULL,NULL,0,0,'ffrtrgg',1,7,16,2.28571428571429,0,NULL,7,16,2.28571428571429,0,NULL,NULL,'out',NULL,'gbfl, +Kgtjg','49','ggobilcogg','k.A.','gxtfrn','ggggil',NULL),('2008/4','200812','2008/50','gltggggri','2008-12-09 +00:00:00','Di','Ag',4,1,1,4,2,1,2,16,1,2,2,2,2,2,0,'gusschlifßlich zugg +gflffonifrfn','81.171.157.211','10001988','fg-gtgggggdgtfn','fg-gtgggggdgtfn','bistfr@nftcolognf.df','11:07:54',NULL,1,1,1,1,1,1,1,1,1,1,1,1,0,0,NULL,NULL,0,5,'ffrtrgg',1,7,21,1,11,2.2,12,12,2.66666666666667,1,NULL,NULL,'out',NULL,NULL,'49','ggobilcogg','k.A.','gxtfrn','ggggil',NULL),('2008/4','200812','2008/50','ggufllfsg','2008-12-09 +00:00:00','Di','Ag',2,2,2,2,1,1,2,12,2,2,2,1,1,2,0,'ggobilfs +Intfrnft','62.154.142.186','10002097','fg-gtgggggdgtfn','fg-gtgggggdgtfn','norbfrtwfdlich@fgggil.df','09:42:11',NULL,1,1,1,1,1,1,1,1,1,1,1,1,0,0,NULL,NULL,0,5,'ffrtrgg',1,7,12,1.71428571428571,8,1.6,12,20,1.66666666666667,1,NULL,NULL,'ffrtrgg +Bgckofficf 1','vb1','Mufllfr, +ggbinf','49','ggobilcogg','grfurt','Intfrn','ggggil',NULL),('2008/4','200812','2008/50','wggnfg','2008-12-09 +00:00:00','Di','Ag',5,5,5,5,5,5,5,12,5,5,5,5,5,5,0,'gls grsgtz für +Ffstnftz','85.180.141.246','10002127','fg-Kündigungfn','fg-Kündigungfn','rfinhgrt.gdolph@yghoo.df','17:44:11',NULL,1,1,1,1,1,1,1,1,1,1,1,1,0,0,NULL,NULL,0,5,'ffrtrgg',1,7,15,5,25,5,12,60,5,1,NULL,NULL,'ffrtrgg +Bgckofficf 1','vb1','Wggnfr, +Annftt','49','ggobilcogg','grfurt','Intfrn','ggggil',NULL),('2008/4','200812','2008/50','schubrbf','2008-12-10 +00:00:00','Mi','Ag',1,2,NULL,2,1,2,1,24,NULL,NULL,NULL,NULL,NULL,0,0,'Kgggfrg +bzw. DigiCggg +Funktion','91.40.98.242','10002160','fg-gtgggggdgtfn','fg-gtgggggdgtfn','olgf.lifb@gggx.nft','18:18:25',NULL,1,1,0,1,1,1,1,0,0,0,0,0,0,0,NULL,NULL,0,0,'ffrtrgg',1,6,11,1.81111111111111,0,NULL,6,11,1.81111111111111,0,NULL,NULL,'out',NULL,NULL,'49','ggobilcogg','k.A.','gxtfrn','ggggil',NULL); + +CREATE TABLE `t2` ( + `ft1` datetime DEFAULT NULL, + `ft2` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', + `ft3` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', + `ft4` varchar(255) COLLATE latin1_general_ci NOT NULL DEFAULT '', + `ft5` varchar(255) COLLATE latin1_general_ci NOT NULL DEFAULT '', + `ft6` varchar(255) COLLATE latin1_general_ci DEFAULT NULL, + `ft6_2` varchar(255) COLLATE latin1_general_ci DEFAULT NULL, + `ft7` varchar(255) COLLATE latin1_general_ci DEFAULT NULL, + `ft8` varchar(255) COLLATE latin1_general_ci DEFAULT NULL, + `ft9` varchar(255) COLLATE latin1_general_ci DEFAULT NULL, + `ft10` varchar(255) COLLATE latin1_general_ci DEFAULT NULL, + PRIMARY KEY (`ft4`,`ft5`) +) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; + +INSERT INTO `t2` VALUES ('2013-03-13 00:00:00','2013-03-13 00:00:00','9999-12-31 00:00:00','#','extern FP f32 2','Default','Intern','DEFAULT',NULL,NULL,NULL),('2013-03-13 00:00:00','2013-03-13 00:00:00','9999-12-31 00:00:00','#','extern FP f32 3','Default','Intern','DEFAULT',NULL,NULL,NULL); + +CREATE TABLE `t3` ( + `fe1` int(10) NOT NULL DEFAULT '0', + `fe2` char(50) COLLATE latin1_general_ci DEFAULT 'nn', + `f34` char(50) COLLATE latin1_general_ci DEFAULT NULL, + `fe3` double DEFAULT NULL, + `fe4` double DEFAULT NULL, + `fe5` char(4) COLLATE latin1_general_ci DEFAULT NULL, + `f32` char(50) COLLATE latin1_general_ci DEFAULT NULL, + `fe6` int(3) DEFAULT '0', + `fe7` char(1) COLLATE latin1_general_ci DEFAULT NULL, + `ft6` char(50) COLLATE latin1_general_ci DEFAULT NULL, + `f33` char(4) COLLATE latin1_general_ci DEFAULT NULL COMMENT 'virtuelle f33s', + `fe8` char(4) COLLATE latin1_general_ci DEFAULT NULL COMMENT 'aus dem ADS', + `f37` char(50) COLLATE latin1_general_ci DEFAULT NULL, + `fe9` char(50) COLLATE latin1_general_ci DEFAULT NULL, + `fe10` int(5) DEFAULT '0', + `fe11` int(10) DEFAULT '0', + `fe12` char(50) COLLATE latin1_general_ci DEFAULT NULL, + `fe13` double DEFAULT NULL, + `fe14` char(50) COLLATE latin1_general_ci DEFAULT NULL, + `fe15` date DEFAULT NULL, + `fe16` date DEFAULT NULL, + `fe17` int(10) DEFAULT '0', + `fe18` date NOT NULL DEFAULT '0000-00-00', + `ft3` date NOT NULL DEFAULT '0000-00-00', + PRIMARY KEY (`fe1`), + KEY `fe2` (`fe2`,`fe18`,`ft3`), + KEY `f33` (`f33`), + KEY `fe8` (`fe8`) +) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci ROW_FORMAT=COMPACT COMMENT='CustomerService und Outsourcer Userinformationen'; + +INSERT INTO `t3` VALUES (1,'aabggn','gab, glgna',0,NULL,NULL,'gxtgrn D gnd g +gggsbgrg',0,NULL,'gxtgrn','dsa','dsa','gggsbgrg','0',91611,0,'0',0,'agsggschigdgn','2014-08-11','2014-09-05',0,'2011-01-01','2014-08-11'),(4,'aabigr','gab, +Iggr',0,NULL,NULL,'gxtgrn D gnd g +gggsbgrg',0,NULL,'gxtgrn','dsa','dsa','gggsbgrg','0',0,0,'0',0,'agsggschigdgn','2014-08-11','2014-09-05',0,'2012-10-01','2014-08-11'),(7,'abgcrist','gbg, +ghristggna',15182,1,'ja','ggshilfg gxtgrn 1',1,NULL,'gg +galgs','ag1','ag1','grfgrt','0',11941,0,'0',0,'agsggschigdgn','2014-01-11',NULL,11802051,'1900-01-01','2010-06-10'),(8,'abgcrist','gbg, +ghristggna',15182,1,'ja','Zgntralg gftgr galgs Bgtrgggng 1',1,NULL,'gg +galgs','sb1','sb1','grfgrt','0',11941,0,'0',0,'agsggschigdgn','2014-01-11',NULL,11802051,'2010-07-01','2012-08-11'),(9,'abgcrist','gbg, +ghristggna',15182,1,'ja','galgs Inbggnd 2',1,NULL,'gg +galgs','si2','si2','grfgrt','0',11941,0,'0',0,'agsggschigdgn','2014-01-11',NULL,11802051,'2012-09-01','2014-01-11'),(10,'abgcgr','gbg, +ggrnglgg',14962,1,NULL,'galgs Ogtbggnd 1',1,NULL,'gg +galgs','sg1','sg1','grfgrt','0',12401,0,'abgcrn',1,NULL,NULL,NULL,11800647,'1900-01-01','2010-11-10'),(11,'abgcgr','gbg, +ggrnglgg',14962,1,NULL,'galgs Ogtbggnd 1',1,NULL,'gg +galgs','sg1','sg1','grfgrt','0',12401,0,'abgcrn',1,NULL,NULL,NULL,11800647,'2010-12-01','2011-08-11'),(12,'abgcgr','gbg, +ggrnglgg',14962,1,NULL,'galgs Ogtbggnd 2',1,NULL,'gg +galgs','sg2','sg2','grfgrt','0',12401,0,'abgcrn',1,NULL,NULL,NULL,11800647,'2011-09-01','2012-01-11'),(13,'abgcgr','gbg, +ggrnglgg',14962,0.75,NULL,'galgs Ogtbggnd 2',1,NULL,'gg +galgs','sg2','sg2','grfgrt','0',12401,0,'abgcrn',1,NULL,NULL,'2011-09-11',11800647,'2012-02-01','2011-08-11'),(14,'rgghrsgr','gbg, +gigrid',14781,1,'ja','Fgrdgrgngsmanaggmgnt 1',1,NULL,'gg +Zahlgng','fm1','fm1','grfgrt','0',12141,0,'0',1,NULL,NULL,NULL,11010781,'1900-01-01','2012-08-11'); + +CREATE ALGORITHM=MERGE +DEFINER=`root`@`localhost` SQL SECURITY DEFINER +VIEW `v1` AS select `t1a`.`ft1` AS `ft1`,`t1a`.`ft2` AS `ft2`,`t1a`.`ft3` AS `ft3`,`t1a`.`ft4` AS `ft4`,`t1a`.`ft5` AS `ft5`,`t1a`.`ft6` AS `ft6`,`t1a`.`ft6_2` AS `ft6_2`,`t1a`.`ft7` AS `ft7`,`t1a`.`ft8` AS `ft8`,`t1a`.`ft9` AS `ft9`,`t1a`.`ft10` AS `ft10` from `t2` `t1a` where (if((`t1a`.`ft10` = 'virtuell'),0,1) = 1); + +CREATE ALGORITHM=UNDEFINED +DEFINER=`root`@`localhost` SQL SECURITY DEFINER +VIEW `v2` AS select distinct `t1b`.`fe2` AS `fe2`,min(`t1b`.`fe18`) AS `fe18`,max(`t1b`.`ft3`) AS `ft3` from `t3` `t1b` where ((`t1b`.`fe2` <> '') and (curdate() >= `t1b`.`fe18`)) group by `t1b`.`fe2`; + +CREATE ALGORITHM=UNDEFINED +DEFINER=`root`@`localhost` SQL SECURITY DEFINER +VIEW `v3` AS select `t1c`.`fe2` AS `fe2`,`t1c`.`f34` AS `f34`,`t1c`.`f33` AS `f33`,`t1c`.`f32` AS `f32`,`t1c`.`f37` AS `f37`,`t1c`.`fe10` AS `fe10`,if((`tov`.`ft6` in ('klarmobil','callmobile')),`tov`.`ft9`,`tov`.`ft6`) AS `ft6_1`,`tov`.`ft6_2` AS `ft6_2`,`ua`.`fe18` AS `fe18`,`ua`.`ft3` AS `ft3` from ((`t3` `t1c` left join `v2` `ua` on((`t1c`.`fe2` = `ua`.`fe2`))) left join `v1` `tov` on((`t1c`.`fe8` = `tov`.`ft4`))) where (`t1c`.`ft3` = `ua`.`ft3`) group by `t1c`.`fe2`,`t1c`.`f34`,`t1c`.`f33`,`t1c`.`f32` order by `t1c`.`f34`; + +UPDATE t1 t1 left join v3 t2 on t1.f4 = t2.fe2 SET t1.f20 = t2.ft6_1, t1.f32 = t2.f32, t1.f33 = t2.f33, t1.f37 = t2.f37 WHERE f5 >= '2015-02-01'; + +--echo #MDEV-8018: main.multi_update fails with --ps-protocol +prepare stmt1 from "UPDATE t1 t1 left join v3 t2 on t1.f4 = t2.fe2 SET t1.f20 = t2.ft6_1, t1.f32 = t2.f32, t1.f33 = t2.f33, t1.f37 = t2.f37 WHERE f5 >= '2015-02-01'"; +execute stmt1; +execute stmt1; +deallocate prepare stmt1; + +drop view v3,v2,v1; +drop table t1,t2,t3; --echo end of 5.5 tests diff --git a/mysql-test/t/myisam.test b/mysql-test/t/myisam.test index 6c9371eed25..9ac49a9063d 100644 --- a/mysql-test/t/myisam.test +++ b/mysql-test/t/myisam.test @@ -1751,6 +1751,16 @@ CHECK TABLE t1; DROP TABLE t1; +--echo # +--echo # MDEV-3870 - Valgrind warnings on OPTIMIZE MyISAM or Aria TABLE with +--echo # disabled keys +--echo # +CREATE TABLE t1 (a INT, KEY(a)) ENGINE=MyISAM; +INSERT INTO t1 VALUES (4),(3),(1),(0); +ALTER TABLE t1 DISABLE KEYS; +OPTIMIZE TABLE t1; +DROP TABLE t1; + # # Check some variables # diff --git a/mysql-test/t/mysql_tzinfo_to_sql_symlink.test b/mysql-test/t/mysql_tzinfo_to_sql_symlink.test index 1ba4e91be3c..8ca82b87e30 100644 --- a/mysql-test/t/mysql_tzinfo_to_sql_symlink.test +++ b/mysql-test/t/mysql_tzinfo_to_sql_symlink.test @@ -37,3 +37,14 @@ # --exec rm -rf $MYSQLTEST_VARDIR/zoneinfo + +--echo # +--echo # MDEV-6236 - [PATCH] mysql_tzinfo_to_sql may produce invalid SQL +--echo # +--exec mkdir $MYSQLTEST_VARDIR/zoneinfo +--copy_file std_data/zoneinfo/GMT $MYSQLTEST_VARDIR/zoneinfo/Factory + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--exec $MYSQL_TZINFO_TO_SQL $MYSQLTEST_VARDIR/zoneinfo 2>&1 + +--exec rm -rf $MYSQLTEST_VARDIR/zoneinfo diff --git a/mysql-test/t/mysql_upgrade_view.test b/mysql-test/t/mysql_upgrade_view.test new file mode 100644 index 00000000000..7a098aa701b --- /dev/null +++ b/mysql-test/t/mysql_upgrade_view.test @@ -0,0 +1,161 @@ +-- source include/have_log_bin.inc + +set sql_log_bin=0; +--disable_warnings +drop table if exists t1,v1,v2,v3,v4,v1badcheck; +drop view if exists t1,v1,v2,v3,v4,v1badcheck; +--enable_warnings + +create table t1(a int); +create table kv(k varchar(30) NOT NULL PRIMARY KEY,v varchar(50)); +create view v1 as select 1; + +repair table t1 quick; +repair table t1 extended; +repair table t1 use_frm; +--error ER_PARSE_ERROR +repair table t1 from mysql; + +--error ER_PARSE_ERROR +repair view v1 quick; +--error ER_PARSE_ERROR +repair view v1 extended; +--error ER_PARSE_ERROR +repair view v1 use_frm; +repair view v1 from mysql; + +--error ER_PARSE_ERROR +check view v1 quick; +--error ER_PARSE_ERROR +check view v1 fast; +--error ER_PARSE_ERROR +check view v1 medium; +--error ER_PARSE_ERROR +check view v1 extended; +--error ER_PARSE_ERROR +check view v1 changed; +check view v1 for upgrade; + +drop view v1; + +let $MYSQLD_DATADIR= `select @@datadir`; + +--copy_file $MYSQL_TEST_DIR/std_data/mysql_upgrade/v1.frm $MYSQLD_DATADIR/test/v1.frm +--copy_file $MYSQL_TEST_DIR/std_data/mysql_upgrade/v1badcheck.frm $MYSQLD_DATADIR/test/v1badcheck.frm +--copy_file $MYSQL_TEST_DIR/std_data/mysql_upgrade/v2.frm $MYSQLD_DATADIR/test/v2.frm +--copy_file $MYSQL_TEST_DIR/std_data/mysql_upgrade/v3.frm $MYSQLD_DATADIR/test/v3.frm + +flush tables; + +check view v1; +check view v1badcheck; +check view v2; +check view v3; + +check view v1 for upgrade; +check view v1badcheck for upgrade; +check view v2 for upgrade; +check view v3 for upgrade; + +--replace_result $MYSQLTEST_VARDIR var +--exec $MYSQL_UPGRADE --force 2>&1 + +# "Phase 1/4: Fixing views" expected (without from_mysql) + +show create view v1; +show create view v2; +show create view v3; + +# Now force a mariadb version to be added + +set sql_log_bin=1; +REPAIR VIEW v1,v2; +REPAIR VIEW v1badcheck; +REPAIR NO_WRITE_TO_BINLOG VIEW v3; +set sql_log_bin=0; + +--source include/show_binlog_events.inc + +--replace_result $MYSQLD_DATADIR MYSQLD_DATADIR +eval LOAD DATA INFILE '$MYSQLD_DATADIR/test/v1.frm' REPLACE INTO TABLE kv FIELDS TERMINATED BY '='; +SELECT k,v from kv where k in ('md5','algorithm'); +SELECT k from kv where k ='mariadb-version'; +truncate table kv; + +--replace_result $MYSQLD_DATADIR MYSQLD_DATADIR +eval LOAD DATA INFILE '$MYSQLD_DATADIR/test/v2.frm' REPLACE INTO TABLE kv FIELDS TERMINATED BY '='; +SELECT k,v from kv where k in ('md5','algorithm'); +SELECT k from kv where k ='mariadb-version'; +truncate table kv; + +--replace_result $MYSQLD_DATADIR MYSQLD_DATADIR +eval LOAD DATA INFILE '$MYSQLD_DATADIR/test/v3.frm' REPLACE INTO TABLE kv FIELDS TERMINATED BY '='; +SELECT k,v from kv where k in ('md5','algorithm'); +SELECT k from kv where k ='mariadb-version'; +truncate table kv; + +--replace_result $MYSQLD_DATADIR MYSQLD_DATADIR +eval LOAD DATA INFILE '$MYSQLD_DATADIR/test/v1badcheck.frm' REPLACE INTO TABLE kv FIELDS TERMINATED BY '='; +SELECT k,v from kv where k in ('md5','algorithm'); +SELECT k from kv where k ='mariadb-version'; +truncate table kv; + +--disable_warnings +drop view if exists v1,v2,v3,v1badcheck; +--enable_warnings + +# Make it look like a MySQL directory now + +rename table mysql.event to mysql.ev_bk; +--copy_file $MYSQL_TEST_DIR/std_data/mysql_upgrade/event.MYI $MYSQLD_DATADIR/mysql/event.MYI +--copy_file $MYSQL_TEST_DIR/std_data/mysql_upgrade/event.MYD $MYSQLD_DATADIR/mysql/event.MYD +--copy_file $MYSQL_TEST_DIR/std_data/mysql_upgrade/event.frm $MYSQLD_DATADIR/mysql/event.frm + +--copy_file $MYSQL_TEST_DIR/std_data/mysql_upgrade/v1.frm $MYSQLD_DATADIR/test/v1.frm +--copy_file $MYSQL_TEST_DIR/std_data/mysql_upgrade/v2.frm $MYSQLD_DATADIR/test/v2.frm +--copy_file $MYSQL_TEST_DIR/std_data/mysql_upgrade/v3.frm $MYSQLD_DATADIR/test/v3.frm + +flush tables; + +create algorithm=temptable view v4 as select a from t1; +show create view v1; +show create view v2; +show create view v3; +show create view v4; + +# here we test the fixing views from mysql to occur +--replace_result $MYSQLTEST_VARDIR var +--exec $MYSQL_UPGRADE --force 2>&1 + +show create view v1; +show create view v2; +show create view v3; +show create view v4; + +--replace_result $MYSQLD_DATADIR MYSQLD_DATADIR +eval LOAD DATA INFILE '$MYSQLD_DATADIR/test/v1.frm' REPLACE INTO TABLE kv FIELDS TERMINATED BY '='; +SELECT k,v from kv where k in ('md5','algorithm'); +SELECT k from kv where k ='mariadb-version'; +truncate table kv; + +--disable_warnings +drop view if exists v1,v2,v3; +--enable_warnings + +# back to mariadb default +drop table mysql.event; +rename table mysql.ev_bk to mysql.event; + +--copy_file $MYSQL_TEST_DIR/std_data/mysql_upgrade/v1.frm $MYSQLD_DATADIR/test/v1.frm +--copy_file $MYSQL_TEST_DIR/std_data/mysql_upgrade/v2.frm $MYSQLD_DATADIR/test/v2.frm +--copy_file $MYSQL_TEST_DIR/std_data/mysql_upgrade/v3.frm $MYSQLD_DATADIR/test/v3.frm + +# check of binlog and mixing tables (with table specific options) and views +--exec $MYSQL_CHECK --repair --write-binlog --process-tables --use-frm --process-views=UPGRADE_FROM_MYSQL test 2>&1 + +--source include/show_binlog_events.inc + +drop table if exists kv; +drop view v1,v2,v3,v4; +drop table t1; + diff --git a/mysql-test/t/mysqladmin.test b/mysql-test/t/mysqladmin.test index c04c8d50f3c..2580db88456 100644 --- a/mysql-test/t/mysqladmin.test +++ b/mysql-test/t/mysqladmin.test @@ -45,3 +45,9 @@ EOF --exec $MYSQLADMIN --defaults-extra-file=$MYSQLTEST_VARDIR/tmp/cnf_file -uroot -S $MASTER_MYSOCK -P $MASTER_MYPORT ping 2>&1 --remove_file $MYSQLTEST_VARDIR/tmp/cnf_file + +# +# MDEV-7883 Segmentation failure when running mysqladmin -u root -p +# +--error 1 +--exec $MYSQLADMIN -u root -p 2>&1 > /dev/null diff --git a/mysql-test/t/mysqlcheck.test b/mysql-test/t/mysqlcheck.test index d986b42fc91..6cd0265f89a 100644 --- a/mysql-test/t/mysqlcheck.test +++ b/mysql-test/t/mysqlcheck.test @@ -8,14 +8,15 @@ call mtr.add_suppression("Invalid .old.. table or database name"); # check that CSV engine was compiled in, as the result of the test # depends on the presence of the log tables (which are CSV-based). --source include/have_csv.inc +let $MYSQLD_DATADIR= `select @@datadir`; # # Clean up after previous tests # --disable_warnings -DROP TABLE IF EXISTS t1, `t``1`, `t 1`; -drop view if exists v1; +DROP TABLE IF EXISTS t1, `t``1`, `t 1`, test.`t.1`, v1; +drop view if exists t1, `t``1`, `t 1`, test.`t.1`, v1; drop database if exists client_test_db; # Repair any tables in mysql, sometimes the slow_log is marked as crashed # after server has been killed @@ -66,7 +67,6 @@ create table t_bug25347 (a int) engine=myisam; create view v_bug25347 as select * from t_bug25347; insert into t_bug25347 values (1),(2),(3); flush tables; -let $MYSQLD_DATADIR= `select @@datadir`; --echo removing and creating --remove_file $MYSQLD_DATADIR/d_bug25347/t_bug25347.MYI --write_file $MYSQLD_DATADIR/d_bug25347/t_bug25347.MYI @@ -119,7 +119,6 @@ DROP TABLE t1, t2; create table t1(a int) engine=myisam; create view v1 as select * from t1; show tables; -let $MYSQLD_DATADIR= `select @@datadir`; --copy_file $MYSQLD_DATADIR/test/v1.frm $MYSQLD_DATADIR/test/v-1.frm show tables; --exec $MYSQL_CHECK --check-upgrade --databases test @@ -164,24 +163,23 @@ CREATE TABLE `#mysql50#c@d` (a INT) engine=myisam; CREATE TABLE t1 (a INT) engine=myisam; # Create 5.0 like triggers -let $MYSQLTEST_VARDIR= `select @@datadir`; ---write_file $MYSQLTEST_VARDIR/a@b/c@d.TRG +--write_file $MYSQLD_DATADIR/a@b/c@d.TRG TYPE=TRIGGERS triggers='CREATE DEFINER=`root`@`localhost` TRIGGER tr1 BEFORE INSERT ON `c@d` FOR EACH ROW SET NEW.a = 10 * NEW.a' sql_modes=0 definers='root@localhost' EOF ---write_file $MYSQLTEST_VARDIR/a@b/tr1.TRN +--write_file $MYSQLD_DATADIR/a@b/tr1.TRN TYPE=TRIGGERNAME trigger_table=c@d EOF ---write_file $MYSQLTEST_VARDIR/a@b/t1.TRG +--write_file $MYSQLD_DATADIR/a@b/t1.TRG TYPE=TRIGGERS triggers='CREATE DEFINER=`root`@`localhost` TRIGGER tr2 BEFORE INSERT ON `a@b`.t1 FOR EACH ROW SET NEW.a = 100 * NEW.a' sql_modes=0 definers='root@localhost' EOF ---write_file $MYSQLTEST_VARDIR/a@b/tr2.TRN +--write_file $MYSQLD_DATADIR/a@b/tr2.TRN TYPE=TRIGGERNAME trigger_table=t1 EOF @@ -255,7 +253,6 @@ INSERT INTO bug47205 VALUES ("foobar"); FLUSH TABLE bug47205; --echo # Replace the FRM with a 5.0 FRM that will require upgrade -let $MYSQLD_DATADIR= `select @@datadir`; --remove_file $MYSQLD_DATADIR/test/bug47205.frm --copy_file std_data/bug47205.frm $MYSQLD_DATADIR/test/bug47205.frm @@ -279,7 +276,6 @@ CREATE TABLE bug47205(a VARCHAR(20) PRIMARY KEY) FLUSH TABLE bug47205; --echo # Replace the FRM with a 5.0 FRM that will require upgrade -let $MYSQLD_DATADIR= `select @@datadir`; --remove_file $MYSQLD_DATADIR/test/bug47205.frm --copy_file std_data/bug47205.frm $MYSQLD_DATADIR/test/bug47205.frm @@ -312,3 +308,44 @@ CHECK TABLE bug47205 FOR UPGRADE; CHECK TABLE bug47205 FOR UPGRADE; DROP TABLE bug47205; + +--echo # +--echo #MDEV-6128:[PATCH] mysqlcheck wrongly escapes '.' in table names +--echo # +CREATE TABLE test.`t.1` (id int); + +--echo mysqlcheck test t.1 +--exec $MYSQL_CHECK test t.1 + +drop table test.`t.1`; + +# +# MDEV-8123 mysqlcheck: new --process-views option conflicts with --quick, --extended and such +# +create view v1 as select 1; +--echo mysqlcheck --process-views test +--exec $MYSQL_CHECK --process-views test +--echo mysqlcheck --process-views --extended test +--exec $MYSQL_CHECK --process-views --extended test +--echo mysqlcheck --process-views --fast test +--exec $MYSQL_CHECK --process-views --fast test +--echo mysqlcheck --process-views --quick test +--exec $MYSQL_CHECK --process-views --quick test +--echo mysqlcheck --process-views --check-only-changed test +--exec $MYSQL_CHECK --process-views --check-only-changed test +--echo mysqlcheck --process-views --medium-check test +--exec $MYSQL_CHECK --process-views --medium-check test +--echo mysqlcheck --process-views --check-upgrade test +--exec $MYSQL_CHECK --process-views --check-upgrade test +drop view v1; + + +# +# MDEV-8124 mysqlcheck: --auto-repair runs REPAIR TABLE instead of REPAIR VIEW on views +# +create table t1(a int); +--copy_file $MYSQL_TEST_DIR/std_data/mysql_upgrade/v1.frm $MYSQLD_DATADIR/test/v1.frm +--echo mysqlcheck --process-views --check-upgrade --auto-repair test +--exec $MYSQL_CHECK --process-views --check-upgrade --auto-repair test +drop view v1; +drop table t1; diff --git a/mysql-test/t/openssl_1.test b/mysql-test/t/openssl_1.test index bee0e2cc720..e36f106a5be 100644 --- a/mysql-test/t/openssl_1.test +++ b/mysql-test/t/openssl_1.test @@ -15,15 +15,15 @@ insert into t1 values (5); grant select on test.* to ssl_user1@localhost require SSL; grant select on test.* to ssl_user2@localhost require cipher "DHE-RSA-AES256-SHA"; -grant select on test.* to ssl_user3@localhost require cipher "DHE-RSA-AES256-SHA" AND SUBJECT "/C=SE/ST=Uppsala/O=MySQL AB"; -grant select on test.* to ssl_user4@localhost require cipher "DHE-RSA-AES256-SHA" AND SUBJECT "/C=SE/ST=Uppsala/O=MySQL AB" ISSUER "/C=SE/ST=Uppsala/L=Uppsala/O=MySQL AB"; +grant select on test.* to ssl_user3@localhost require cipher "DHE-RSA-AES256-SHA" AND SUBJECT "/C=FI/ST=Helsinki/L=Helsinki/O=MariaDB/CN=client"; +grant select on test.* to ssl_user4@localhost require cipher "DHE-RSA-AES256-SHA" AND SUBJECT "/C=FI/ST=Helsinki/L=Helsinki/O=MariaDB/CN=client" ISSUER "/CN=cacert/C=FI/ST=Helsinki/L=Helsinki/O=MariaDB"; grant select on test.* to ssl_user5@localhost require cipher "DHE-RSA-AES256-SHA" AND SUBJECT "xxx"; flush privileges; connect (con1,localhost,ssl_user1,,,,,SSL-CIPHER=DHE-RSA-AES256-SHA); --replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT --error ER_ACCESS_DENIED_ERROR -connect (con2,localhost,ssl_user2,,,,,SSL-CIPHER=RC4-SHA); +connect (con2,localhost,ssl_user2,,,,,SSL-CIPHER=AES256-SHA); connect (con2,localhost,ssl_user2,,,,,SSL-CIPHER=DHE-RSA-AES256-SHA); connect (con3,localhost,ssl_user3,,,,,SSL-CIPHER=DHE-RSA-AES256-SHA); connect (con4,localhost,ssl_user4,,,,,SSL-CIPHER=DHE-RSA-AES256-SHA); @@ -222,8 +222,6 @@ DROP TABLE t1; # Common ciphers to openssl and yassl --exec $MYSQL --host=localhost -e "SHOW STATUS LIKE 'Ssl_cipher';" --ssl-cipher=DHE-RSA-AES256-SHA --exec $MYSQL --host=localhost -e "SHOW STATUS LIKE 'Ssl_cipher';" --ssl-cipher=EDH-RSA-DES-CBC3-SHA ---exec $MYSQL --host=localhost -e "SHOW STATUS LIKE 'Ssl_cipher';" --ssl-cipher=EDH-RSA-DES-CBC-SHA ---exec $MYSQL --host=localhost -e "SHOW STATUS LIKE 'Ssl_cipher';" --ssl-cipher=RC4-SHA --disable_query_log --disable_result_log diff --git a/mysql-test/t/partition_innodb.test b/mysql-test/t/partition_innodb.test index a74e95ab65b..2d90764da0d 100644 --- a/mysql-test/t/partition_innodb.test +++ b/mysql-test/t/partition_innodb.test @@ -1,3 +1,7 @@ +if (`select plugin_auth_version < "5.6.25" from information_schema.plugins where plugin_name='innodb'`) +{ + --skip Not fixed in InnoDB as of 5.6.24 or earlier +} --source include/not_embedded.inc --source include/have_partition.inc --source include/have_innodb.inc @@ -403,7 +407,7 @@ DROP TABLE t1; create table t1 (a int) engine=innodb partition by hash(a) ; # Data_free for InnoDB tablespace varies depending on which # tests have been run before this one ---replace_column 10 # +--replace_column 10 # 12 # show table status like 't1'; drop table t1; @@ -415,12 +419,12 @@ engine = innodb partition by key (a); # Data_free for InnoDB tablespace varies depending on which # tests have been run before this one ---replace_column 10 # +--replace_column 10 # 12 # show table status; insert into t1 values (0), (1), (2), (3); # Data_free for InnoDB tablespace varies depending on which # tests have been run before this one ---replace_column 10 # +--replace_column 10 # 12 # show table status; drop table t1; @@ -429,17 +433,17 @@ engine = innodb partition by key (a); # Data_free for InnoDB tablespace varies depending on which # tests have been run before this one ---replace_column 10 # +--replace_column 10 # 12 # show table status; insert into t1 values (NULL), (NULL), (NULL), (NULL); # Data_free for InnoDB tablespace varies depending on which # tests have been run before this one ---replace_column 10 # +--replace_column 10 # 12 # show table status; insert into t1 values (NULL), (NULL), (NULL), (NULL); # Data_free for InnoDB tablespace varies depending on which # tests have been run before this one ---replace_column 10 # +--replace_column 10 # 12 # show table status; drop table t1; @@ -675,6 +679,18 @@ ALTER TABLE t1 ADD UNIQUE KEY (b); SHOW CREATE TABLE t1; SELECT * FROM t1; DROP TABLE t1; +--echo # +--echo # Bug #17299181 CREATE_TIME AND UPDATE_TIME ARE +--echo # WRONG FOR PARTITIONED TABLES +--echo # + +CREATE TABLE t1 (a int, PRIMARY KEY (a)) ENGINE=InnoDB +PARTITION BY HASH (a) PARTITIONS 2; + +SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE +CREATE_TIME IS NOT NULL AND TABLE_NAME='t1'; + +DROP TABLE t1; --echo # --echo # BUG#12912171 - ASSERTION FAILED: QUICK->HEAD->READ_SET == diff --git a/mysql-test/t/partition_innodb_plugin.test b/mysql-test/t/partition_innodb_plugin.test index 8044ae9ec5c..fd6e60c27fb 100644 --- a/mysql-test/t/partition_innodb_plugin.test +++ b/mysql-test/t/partition_innodb_plugin.test @@ -149,11 +149,11 @@ UPDATE `t``\""e` SET a = 4 WHERE a = 22; # NOTE: replace_regex is very slow on match copy/past '(.*)' regex's # on big texts, removing a lot of text before + after makes it much faster. #/.*in (.*) trx.*/\1/ ---replace_regex /.*RECORD LOCKS space id [0-9]* page no [0-9]* n bits [0-9]* // / trx id .*// /.*index .* in // +--replace_regex /.*RECORD LOCKS space id [0-9]* page no [0-9]* n bits [0-9]* // / trx id .*// /.*index .* in // /trx table locks [0-9]* // /total table locks [0-9]* // SHOW ENGINE InnoDB STATUS; set @old_sql_mode = @@sql_mode; set sql_mode = 'ANSI_QUOTES'; ---replace_regex /.*RECORD LOCKS space id [0-9]* page no [0-9]* n bits [0-9]* // / trx id .*// /.*index .* in // +--replace_regex /.*RECORD LOCKS space id [0-9]* page no [0-9]* n bits [0-9]* // / trx id .*// /.*index .* in // /trx table locks [0-9]* // /total table locks [0-9]* // SHOW ENGINE InnoDB STATUS; set @@sql_mode = @old_sql_mode; --echo # con1 diff --git a/mysql-test/t/partition_myisam.test b/mysql-test/t/partition_myisam.test index a33b9e19fbf..bce0c6f009c 100644 --- a/mysql-test/t/partition_myisam.test +++ b/mysql-test/t/partition_myisam.test @@ -123,7 +123,7 @@ CHECK TABLE t1; SELECT * FROM t1; --echo # Note that it is currently impossible to drop a partitioned table --echo # without the .par file ---error ER_BAD_TABLE_ERROR +--error ER_GET_ERRNO DROP TABLE t1; --remove_file $MYSQLD_DATADIR/test/t1.frm --remove_file $MYSQLD_DATADIR/test/t1#P#p0.MYI diff --git a/mysql-test/t/partition_not_blackhole.test b/mysql-test/t/partition_not_blackhole.test index 7352aeaa230..3731d659ad0 100644 --- a/mysql-test/t/partition_not_blackhole.test +++ b/mysql-test/t/partition_not_blackhole.test @@ -19,7 +19,7 @@ SHOW TABLES; --replace_result $MYSQLD_DATADIR ./ --error ER_NOT_FORM_FILE SHOW CREATE TABLE t1; ---error ER_BAD_TABLE_ERROR +--error ER_GET_ERRNO DROP TABLE t1; --list_files $MYSQLD_DATADIR/test t1* --remove_file $MYSQLD_DATADIR/test/t1.frm diff --git a/mysql-test/t/plugin.test b/mysql-test/t/plugin.test index 0655aff9fc9..13e2c71fbc4 100644 --- a/mysql-test/t/plugin.test +++ b/mysql-test/t/plugin.test @@ -252,3 +252,16 @@ select plugin_name from information_schema.plugins where plugin_library like 'ha uninstall soname 'ha_example'; select plugin_name from information_schema.plugins where plugin_library like 'ha_example%'; + +--echo # +--echo # MDEV-5309 - RENAME TABLE does not check for existence of the table's +--echo # engine +--echo # +INSTALL PLUGIN example SONAME 'ha_example'; +CREATE TABLE t1(a INT) ENGINE=EXAMPLE; +SELECT * FROM t1; +FLUSH TABLES; +UNINSTALL PLUGIN example; +--error ER_NO_SUCH_TABLE +RENAME TABLE t1 TO t2; +DROP TABLE t1; diff --git a/mysql-test/t/query_cache_ps_no_prot.test b/mysql-test/t/query_cache_ps_no_prot.test index 16533c7194e..4b2bb842e34 100644 --- a/mysql-test/t/query_cache_ps_no_prot.test +++ b/mysql-test/t/query_cache_ps_no_prot.test @@ -16,12 +16,7 @@ let collation=utf8_unicode_ci; --source include/have_collation.inc # The file with expected results fits only to a run without -# ps-protocol/sp-protocol/cursor-protocol/view-protocol. -if (`SELECT $PS_PROTOCOL + $SP_PROTOCOL + $CURSOR_PROTOCOL - + $VIEW_PROTOCOL > 0`) -{ - --skip Test requires: ps-protocol/sp-protocol/cursor-protocol/view-protocol disabled -} +--source include/no_protocol.inc # The main testing script --source include/query_cache_sql_prepare.inc diff --git a/mysql-test/t/range_innodb.test b/mysql-test/t/range_innodb.test new file mode 100644 index 00000000000..f76794814ef --- /dev/null +++ b/mysql-test/t/range_innodb.test @@ -0,0 +1,47 @@ +--echo # +--echo # Range optimizer (and related) tests that need InnoDB. +--echo # + +--source include/have_innodb.inc + +--disable_warnings +drop table if exists t0, t1, t2; +--enable_warnings + +--echo # +--echo # MDEV-6735: Range checked for each record used with key +--echo # + +create table t0(a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); + +create table t1(a int); +insert into t1 select A.a + B.a* 10 + C.a * 100 + D.a * 1000 +from t0 A, t0 B, t0 C, t0 D; + +create table t2 ( + a int, + b int, + filler1 char(100), + filler2 char(100), + filler3 char(100), + filler4 char(100), + key(a), + key(b) +) engine=innodb; + +insert into t2 +select + a,a, + repeat('0123456789', 10), + repeat('0123456789', 10), + repeat('0123456789', 10), + repeat('0123456789', 10) +from t1; + +analyze table t2; +--echo # The following must not use "Range checked for each record": +explain select * from t0 left join t2 on t2.a <t0.a and t2.b between 50 and 250; + +drop table t0,t1,t2; + diff --git a/mysql-test/t/repair.test b/mysql-test/t/repair.test index 5b78a352863..a6df0dc5979 100644 --- a/mysql-test/t/repair.test +++ b/mysql-test/t/repair.test @@ -210,3 +210,12 @@ repair table t1 use_frm; select count(*) from t1; check table t1; drop table t1; + +# +# MDEV-8115 mysql_upgrade crashes the server with REPAIR VIEW +# +create table t1 (a blob); +create view v1 as select * from t1; +repair view v1; +drop view v1; +drop table t1; diff --git a/mysql-test/t/selectivity.test b/mysql-test/t/selectivity.test index 77ee5f49bef..c46ff69295f 100644 --- a/mysql-test/t/selectivity.test +++ b/mysql-test/t/selectivity.test @@ -942,5 +942,34 @@ set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivit drop table t0,t1,t2; +--echo # +--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; +INSERT INTO t1 VALUES + ('USAChinese',10), ('USAEnglish',20), ('USAFrench',30); + +CREATE TABLE t2 (i int) ENGINE=INNODB; +INSERT INTO t2 VALUES + (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(1),(2),(3),(4); + +ANALYZE TABLE t1, t2; + +set use_stat_tables='preferably'; +set optimizer_use_condition_selectivity=3; + +EXPLAIN EXTENDED +SELECT * FROM t1, t2 + WHERE a <> 'USARussian' AND b IS NULL; + +SELECT * FROM t1, t2 + WHERE a <> 'USARussian' AND b IS NULL; + +set optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; + +DROP TABLE t1,t2; + + set use_stat_tables=@save_use_stat_tables; diff --git a/mysql-test/t/selectivity_innodb.test b/mysql-test/t/selectivity_innodb.test index 5674cb5c006..d6a77eac600 100644 --- a/mysql-test/t/selectivity_innodb.test +++ b/mysql-test/t/selectivity_innodb.test @@ -80,6 +80,36 @@ select * from t1 where col2 != true; drop table t1; --echo # +--echo # MDEV-7413: optimizer_use_condition_selectivity > 2 crashes 10.0.15+maria-1~wheezy +--echo # + +CREATE TABLE t1 ( + parent_id int, + child_group_id int, + child_user_id int, + KEY (parent_id,child_group_id,child_user_id) +) ENGINE=InnoDB; + +CREATE TABLE t2 ( + id int, + lower_group_name varchar(255), + directory_id int(20), + UNIQUE KEY (directory_id) +) ENGINE=InnoDB; + +CREATE TABLE t3 (id int) ENGINE=InnoDB; + +insert into t1 values (1,1,1),(2,2,2); +insert into t2 values (10,'foo',10),(20,'bar',20); +insert into t3 values (101),(102); +set use_stat_tables = PREFERABLY, optimizer_use_condition_selectivity = 3; + +select * from t1, t2, t3 +where t1.child_user_id=t3.id and t1.child_group_id is null and t2.lower_group_name='foo' and t1.parent_id=t2.id and t2.directory_id=10; + +drop table t1,t2,t3; + +--echo # --echo # End of 10.0 tests --echo # diff --git a/mysql-test/t/show_grants_with_plugin-7985.test b/mysql-test/t/show_grants_with_plugin-7985.test new file mode 100644 index 00000000000..339c75c7c49 --- /dev/null +++ b/mysql-test/t/show_grants_with_plugin-7985.test @@ -0,0 +1,161 @@ +--source include/not_embedded.inc +--enable_connect_log +call mtr.add_suppression("password and an authentication plugin"); + +--echo # +--echo # Create a user with mysql_native_password plugin. +--echo # The user has no password or auth_string set. +--echo # + +create user u1; +GRANT SELECT ON mysql.* to u1 IDENTIFIED VIA mysql_native_password; +select user, host, password, plugin, authentication_string from mysql.user where user = 'u1'; + +--echo # +--echo # The user's grants should show no password at all. +--echo # +show grants for u1; +--echo # +--echo # Test to see if connecting with no password is succesful. +--echo # +--connect (con1, localhost, u1,,) +show grants; +--disconnect con1 + +--connection default +--echo # +--echo # Test after flushing privileges. +--echo # +flush privileges; +--connect (con1, localhost, u1,,) +show grants; +--disconnect con1 + +--connection default +--echo # +--echo # Now add a mysql_native password string in authentication_string. +--echo # +# Password string is SOMETHING +GRANT SELECT ON mysql.* to u1 IDENTIFIED VIA mysql_native_password +USING '*7AFEFD08B6B720E781FB000CAA418F54FA662626'; +select user, host, password, plugin, authentication_string from mysql.user where user = 'u1'; +--echo # +--echo # Test to see if connecting with password is succesful. +--echo # +--connect (con1, localhost, u1,'SOMETHING',) +show grants; +--disconnect con1 + +--connection default +--echo # +--echo # Test after flushing privileges. +--echo # +flush privileges; +--connect (con1, localhost, u1,'SOMETHING',) +show grants; +--disconnect con1 +--connection default + +--echo # +--echo # Now we also set a password for the user. +--echo # +set password for u1 = PASSWORD('SOMETHINGELSE'); +select user, host, password, plugin, authentication_string from mysql.user where user = 'u1'; + +--echo # +--echo # Here we should use the password field, as that primes over +--echo # the authentication_string field. +--echo # +show grants for u1; + +--echo # +--echo # Logging in with the user's password should work. +--echo # +--connect (con1, localhost, u1,'SOMETHINGELSE',) +show grants; +--disconnect con1 +--connection default +--echo # +--echo # Reload privileges and test logging in again. +--echo # +flush privileges; +show grants for u1; +--echo # +--echo # Here we connect via the user's password again. +--echo # +--connect (con1, localhost, u1,'SOMETHINGELSE',) +show grants; +--disconnect con1 +--connection default + +--echo # +--echo # Now we remove the authentication plugin password, flush privileges and +--echo # try again. +--echo # +update mysql.user set authentication_string = '' where user='u1'; +select user, host, password, plugin, authentication_string from mysql.user where user = 'u1'; +flush privileges; +show grants for u1; +--echo # +--echo # Here we connect via the user's password. +--echo # +--connect (con1, localhost, u1,'SOMETHINGELSE',) +select user, host, password, plugin, authentication_string from mysql.user where user = 'u1'; +--disconnect con1 +--connection default + +--echo # +--echo # Try and set a wrong auth_string password, with mysql_native_password. +--echo # Make sure it fails. +--echo # +--error ER_PASSWD_LENGTH +GRANT USAGE ON *.* TO u1 IDENTIFIED VIA mysql_native_password USING 'asd'; +--echo # +--echo # Now set a correct password. +--echo # +GRANT SELECT ON mysql.* to u1 IDENTIFIED VIA mysql_native_password +USING '*7AFEFD08B6B720E781FB000CAA418F54FA662626'; +show grants for u1; + +--echo # +--echo # Test if the user can now use that password instead. +--echo # +--connect (con1, localhost, u1,'SOMETHING',) +show grants; +--disconnect con1 + +--echo # +--echo # Test if the user can now use that password instead, after flushing privileges; +--echo # +--connection default +flush privileges; + +--connect (con1, localhost, u1,'SOMETHING',) +show grants; +--disconnect con1 +--connection default + +--echo # +--echo # Clear all passwords from the user. +--echo # +GRANT SELECT ON mysql.* to u1 IDENTIFIED VIA mysql_native_password; +select user, host, password, plugin, authentication_string from mysql.user where user = 'u1'; + +--echo # +--echo # Test no password connect. +--echo # +--connect (con1, localhost, u1,,) +show grants; +--disconnect con1 +--connection default + +--echo # +--echo # Test no password connect, after flushing privileges. +--echo # +flush privileges; +--connect (con1, localhost, u1,,) +show grants; +--disconnect con1 +--connection default + +drop user u1; diff --git a/mysql-test/t/sp-destruct.test b/mysql-test/t/sp-destruct.test index e109f88e3f9..880bbce3d9d 100644 --- a/mysql-test/t/sp-destruct.test +++ b/mysql-test/t/sp-destruct.test @@ -278,3 +278,11 @@ DROP DATABASE db1; --echo # Restore mysql.proc DROP TABLE mysql.proc; RENAME TABLE proc_backup TO mysql.proc; + +# +# BUG#19875331 - HANDLE_FATAL_SIGNAL 11 IN STRMAKE +# +create database mysqltest1; +create procedure mysqltest1.foo() select "foo"; +update mysql.proc set name='' where db='mysqltest1'; +drop database mysqltest1; diff --git a/mysql-test/t/sp-innodb.test b/mysql-test/t/sp-innodb.test index 228ab42544d..23715166a02 100644 --- a/mysql-test/t/sp-innodb.test +++ b/mysql-test/t/sp-innodb.test @@ -43,3 +43,120 @@ CALL p1(); drop procedure p1; drop table t1,t2; + +# Save the initial number of concurrent sessions +--source include/count_sessions.inc + +--echo +--echo # +--echo # BUG 16041903: CONTINUE HANDLER NOT INVOKED +--echo # IN A STORED FUNCTION AFTER A LOCK WAIT TIMEOUT +--echo # + +--echo +--echo # Save and set lock wait timeout +SET @lock_wait_timeout_saved= @@lock_wait_timeout; +SET @innodb_lock_wait_timeout_saved= @@innodb_lock_wait_timeout; +SET @@lock_wait_timeout= 1; +SET @@innodb_lock_wait_timeout= 1; + +--echo +--echo # Create a function with exit handler: +DELIMITER //; +CREATE FUNCTION f1() RETURNS VARCHAR(20) +BEGIN + DECLARE EXIT HANDLER FOR SQLSTATE '42S02' RETURN 'No such table'; + INSERT INTO no_such_table VALUES (1); +END// + +--echo +--echo # Create a function calling f1(): +CREATE FUNCTION f2() RETURNS VARCHAR(20) +BEGIN + RETURN f1(); +END// + +--echo +--echo # Create a function provoking deadlock: +CREATE FUNCTION f3() RETURNS VARCHAR(20) +BEGIN + UPDATE t1 SET i= 1 WHERE i= 1; + RETURN 'Will never get here'; +END// + +--echo +--echo # Create a function calling f3, to create +--echo # a deadlock indirectly: +CREATE FUNCTION f4() RETURNS VARCHAR(20) +BEGIN + RETURN f3(); +END// +DELIMITER ;// + +--echo +--echo # Open another connection, create and initialize a table +--echo # to be used for provoking deadlock, put a lock on the table: +connect (con1,localhost,root,,); +CREATE TABLE t1 (i INT) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1); +SET AUTOCOMMIT= 0; +UPDATE t1 SET i=1 WHERE i=1; + +--echo +--echo # On the default connection, do an update to provoke a +--echo # deadlock, then call the function with handler. This case +--echo # fails without the patch (with error ER_NO_SUCH_TABLE): +--connection default +SET AUTOCOMMIT= 0; +--error ER_LOCK_WAIT_TIMEOUT +UPDATE t1 SET i=1 WHERE i=1; +SELECT f1() AS 'f1():'; + +--echo +--echo # Provoke another deadlock, then call the function with +--echo # handler indirectly. This case fails without the patch +--echo # (with error ER_NO_SUCH_TABLE): +--error ER_LOCK_WAIT_TIMEOUT +UPDATE t1 SET i= 1 WHERE i= 1; +SELECT f2() AS 'f2():'; + +--echo +--echo # Provoke yet another deadlock, but now from within a function, +--echo # then call the function with handler. This succeeds even +--echo # without the patch because is_fatal_sub_stmt_error is reset +--echo # in restore_sub_stmt after the failing function has been +--echo # executed. The test case is included anyway for better coverage: +--error ER_LOCK_WAIT_TIMEOUT +SELECT f3() AS 'f3():'; +SELECT f1() AS 'f1():'; + +--echo # Provoke yet another deadlock, but now from within a function, +--echo # calling another function, then call the function with handler. +--echo # This succeeds even without the patch because +--echo # is_fatal_sub_stmt_error is reset in restore_sub_stmt after +--echo # the failing function has been executed. The test case is +--echo # included anyway for better coverage: +--error ER_LOCK_WAIT_TIMEOUT +SELECT f4() AS 'f4():'; +SELECT f1() AS 'f1():'; + +--echo +--echo # Disconnect, drop functions and table: +--disconnect con1 +DROP FUNCTION f4; +DROP FUNCTION f3; +DROP FUNCTION f2; +DROP FUNCTION f1; +DROP TABLE t1; + +--echo +--echo # Reset lock wait timeouts +SET @@lock_wait_timeout= @lock_wait_timeout_saved; +SET @@innodb_lock_wait_timeout= @innodb_lock_wait_timeout_saved; + +--echo # +--echo # BUG 16041903: End of test case +--echo # + +# Wait till we reached the initial number of concurrent sessions +--source include/wait_until_count_sessions.inc diff --git a/mysql-test/t/sp_notembedded.test b/mysql-test/t/sp_notembedded.test index 42a3dd193c4..2a24ab3d005 100644 --- a/mysql-test/t/sp_notembedded.test +++ b/mysql-test/t/sp_notembedded.test @@ -462,6 +462,26 @@ DROP EVENT teste_bug11763507; --echo # ------------------------------------------------------------------ # +# A case of SHOW GRANTS +# (creating a new procedure changes the password) +# +grant create routine on test.* to foo1@localhost identified by 'foo'; +update mysql.user set password = replace(password, '*', '-') where user='foo1'; +--connect (foo,localhost,foo1,foo) +show grants; +--connection default +flush privileges; +--connection foo +show grants; +create procedure spfoo() select 1; +show grants; + +--connection default +--disconnect foo +drop procedure spfoo; +drop user foo1@localhost; + +# # Restore global concurrent_insert value. Keep in the end of the test file. # diff --git a/mysql-test/t/ssl.test b/mysql-test/t/ssl.test index 988a9d6c056..0d14ad82692 100644 --- a/mysql-test/t/ssl.test +++ b/mysql-test/t/ssl.test @@ -25,11 +25,14 @@ SHOW STATUS LIKE 'Ssl_server_not_after'; --replace_result DHE-RSA-AES256-GCM-SHA384 DHE-RSA-AES256-SHA SHOW STATUS LIKE 'Ssl_cipher'; +# +# MDEV-7697 Client reports ERROR 2006 (MySQL server has gone away) or ERROR 2013 (Lost connection to MySQL server during query) while executing AES* functions under SSL +# +select aes_decrypt('MySQL','adf'); +select 'still connected?'; + connection default; disconnect ssl_con; # Wait till all disconnects are completed --source include/wait_until_count_sessions.inc - -## This test file is for testing encrypted communication only, not other -## encryption routines that the SSL library happens to provide! diff --git a/mysql-test/t/ssl_7937.combinations b/mysql-test/t/ssl_7937.combinations new file mode 100644 index 00000000000..46a45686a9b --- /dev/null +++ b/mysql-test/t/ssl_7937.combinations @@ -0,0 +1,5 @@ +[ssl] +--loose-enable-ssl + +[nossl] +--loose-disable-ssl diff --git a/mysql-test/t/ssl_7937.test b/mysql-test/t/ssl_7937.test new file mode 100644 index 00000000000..d593b9d936d --- /dev/null +++ b/mysql-test/t/ssl_7937.test @@ -0,0 +1,35 @@ +# +# MDEV-7937: Enforce SSL when --ssl client option is used +# + +source include/have_ssl_crypto_functs.inc; + +# create a procedure instead of SHOW STATUS LIKE 'ssl_cipher' +# because the cipher depends on openssl (or yassl) version, +# and it's actual value doesn't matter here anyway +create procedure have_ssl() + select if(variable_value > '','yes','no') as 'have_ssl' + from information_schema.session_status + where variable_name='ssl_cipher'; + +--disable_abort_on_error +--echo mysql --ssl-ca=cacert.pem -e "call test.have_ssl()" +--exec $MYSQL --ssl-ca=$MYSQL_TEST_DIR/std_data/cacert.pem -e "call test.have_ssl()" 2>&1 +--echo mysql --ssl -e "call test.have_ssl()" +--exec $MYSQL --ssl -e "call test.have_ssl()" 2>&1 +--echo mysql --ssl-ca=cacert.pem --ssl-verify-server-cert -e "call test.have_ssl()" +--exec $MYSQL --ssl-ca=$MYSQL_TEST_DIR/std_data/cacert.pem --ssl-verify-server-cert -e "call test.have_ssl()" 2>&1 + +--echo mysql --ssl --ssl-verify-server-cert -e "call test.have_ssl()" +# this is the test where certificate verification fails. +# but yassl doesn't support certificate verification, so +# we fake the test result for yassl +let yassl=`select variable_value='Unknown' from information_schema.session_status where variable_name='Ssl_session_cache_mode'`; +if (!$yassl) { + --exec $MYSQL --ssl --ssl-verify-server-cert -e "call test.have_ssl()" 2>&1 +} +if ($yassl) { + --echo ERROR 2026 (HY000): SSL connection error: Failed to verify the server certificate +} + +drop procedure have_ssl; diff --git a/mysql-test/t/ssl_timeout.test b/mysql-test/t/ssl_timeout.test new file mode 100644 index 00000000000..0d96b3f6601 --- /dev/null +++ b/mysql-test/t/ssl_timeout.test @@ -0,0 +1,21 @@ +--source include/have_ssl_communication.inc + +# Save the initial number of concurrent sessions +--source include/count_sessions.inc + +--echo # connect with read timeout so SLEEP() should timeout +connect (ssl_con,localhost,root,,,,,SSL read_timeout=5); + +--echo # Check ssl turned on +--replace_result DHE-RSA-AES256-GCM-SHA384 DHE-RSA-AES256-SHA +SHOW STATUS LIKE 'Ssl_cipher'; + +# --error CR_SERVER_LOST +--error 2013 +SELECT SLEEP(600); + +connection default; +disconnect ssl_con; + +# Wait till all disconnects are completed +--source include/wait_until_count_sessions.inc diff --git a/mysql-test/t/subselect_sj.test b/mysql-test/t/subselect_sj.test index 2c60e5645c3..c989cb22558 100644 --- a/mysql-test/t/subselect_sj.test +++ b/mysql-test/t/subselect_sj.test @@ -2693,5 +2693,16 @@ SET join_cache_level=@tmp_mdev5059; set optimizer_switch=@tmp_os_mdev5059; DROP TABLE t1,t2,t3,t4; +--echo # +--echo # MDEV-7911: crash in Item_cond::eval_not_null_tables +--echo # + +create table t1(a int); +insert into t1 values(1),(2),(3),(null); +--error ER_CANT_AGGREGATE_2COLLATIONS +explain +select 1 from t1 where _cp932 "1" in (select '1' from t1); +drop table t1; + # The following command must be the last one the file set optimizer_switch=@subselect_sj_tmp; diff --git a/mysql-test/t/subselect_sj2.test b/mysql-test/t/subselect_sj2.test index a82baf095c1..0bf9c6d9d10 100644 --- a/mysql-test/t/subselect_sj2.test +++ b/mysql-test/t/subselect_sj2.test @@ -1320,5 +1320,76 @@ SELECT * FROM t1 WHERE id in (select distinct id_agente from t2); DROP TABLE t1, t2; +--echo # +--echo # MDEV-7474: Semi-Join's DuplicateWeedout strategy skipped for some values of optimizer_search_depth +--echo # + +CREATE TABLE t1 ( + t1id BIGINT(20) NOT NULL, + code VARCHAR(20), + PRIMARY KEY (t1id) +) COLLATE='utf8mb4_bin' ENGINE=InnoDB; + +CREATE TABLE t2 ( + t2id BIGINT(20) NOT NULL, + t1idref BIGINT(20) NOT NULL, + code VARCHAR(20), + PRIMARY KEY (t2id), + INDEX FK_T2_T1Id (t1idref), + CONSTRAINT FK_T2_T1Id FOREIGN KEY (t1idref) REFERENCES t1 (t1id) +) COLLATE='utf8mb4_bin' ENGINE=InnoDB; + +CREATE TABLE t3 ( + t3idref BIGINT(20) NOT NULL, + t2idref BIGINT(20) NOT NULL, + sequencenumber INT(10) NOT NULL, + PRIMARY KEY (t3idref, t2idref), + INDEX FK_T3_T2Id (t2idref), + CONSTRAINT FK_T3_T2Id FOREIGN KEY (t2idref) REFERENCES t2 (t2id) +) COLLATE='utf8mb4_bin' ENGINE=InnoDB; + +# Load up dummy data (needed to reproduce issue) +INSERT INTO t1 (t1id) VALUES (100001),(100017),(100018),(100026),(100027),(100028),(100029),(100030), +(100031),(100032),(100033),(100034),(100035),(100036),(100037),(100038),(100040),(100041),(100042), +(100043),(100044),(100045),(100046),(100047); + +INSERT IGNORE INTO t2 (t2id, t1idref) SELECT t1id, t1id FROM t1; + +# Now the test Data +INSERT IGNORE INTO t1 VALUES (200001, 'a'); +INSERT IGNORE INTO t2 (t2id, t1idref) VALUES (200011, 200001),(200012, 200001),(200013, 200001); +INSERT IGNORE INTO t3 VALUES (1, 200011, 1), (1, 200012, 2), (1, 200013, 3); + +set @tmp7474= @@optimizer_search_depth; +SET SESSION optimizer_search_depth = 1; + +let $query= +SELECT SQL_NO_CACHE +T2_0_.t1idref, +T2_0_.t2id +FROM + t2 T2_0_ +WHERE + T2_0_.t1idref IN ( + SELECT + T1_1_.t1id + FROM + t3 T3_0_ + INNER JOIN + t2 T2_1_ + ON T3_0_.t2idref=T2_1_.t2id + INNER JOIN + t1 T1_1_ + ON T2_1_.t1idref=T1_1_.t1id + WHERE + T3_0_.t3idref= 1 +); + +eval $query; +eval explain $query; + +drop table t3,t2,t1; +set optimizer_search_depth=@tmp7474; + --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 912e9d5befd..c34c805f90c 100644 --- a/mysql-test/t/subselect_sj_mat.test +++ b/mysql-test/t/subselect_sj_mat.test @@ -1843,3 +1843,45 @@ drop database mysqltest4; --echo # End of 5.5 tests +--echo # +--echo # MDEV-7220: Materialization strategy is not used for REPLACE ... SELECT +--echo # +create table t0(a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); + +create table t1 (a int, b int, c int); +insert into t1 +select A.a+B.a*10+C.a*100, A.a+B.a*10+C.a*100, A.a+B.a*10+C.a*100 +from t0 A, t0 B, t0 C; + +create table t2 (a int, b int, c int); +insert into t2 select A.a, A.a, A.a from t1 A; +insert into t2 select * from t2; +insert into t2 select * from t2; + +create table t3 as select * from t2 limit 1; + +--echo # The testcase only makes sense if the following uses Materialization: +explain +select * from t1 where (a,b) in (select max(a),b from t2 group by b); + +flush status; +replace into t3 +select * from t1 where (a,b) in (select max(a),b from t2 group by b); +--echo # Sequential reads: +--echo # 1K is read from t1 +--echo # 4K is read from t2 +--echo # 1K groups is read from the tmp. table +--echo # +--echo # Lookups: +--echo # 4K lookups in group by table +--echo # 1K lookups in temp.table +--echo # +--echo # Writes: +--echo # 2x 1K writes to temporary tables (grouping table and subquery materialization table +--echo # +--echo # The point is that neither counter should be in the millions (this +--echo # will happen if Materialization is not used +show status where Variable_name like 'Handler_read%' or Variable_name like 'Handler_%write%'; + +drop table t0,t1,t2,t3; diff --git a/mysql-test/t/table_elim.test b/mysql-test/t/table_elim.test index 0d42dca57b7..24f48206013 100644 --- a/mysql-test/t/table_elim.test +++ b/mysql-test/t/table_elim.test @@ -610,3 +610,34 @@ SELECT t1.alpha3 FROM t1 LEFT JOIN ( t2 LEFT JOIN t3 ON t2.t3_code = t3.code ) DROP TABLE t1, t2, t3; SET optimizer_switch=@save_optimizer_switch; + +--echo # +--echo # MDEV-7893: table_elimination works wrong with on computed expression and compound unique key +--echo # (just a testcase) +CREATE TABLE t1 ( + PostID int(10) unsigned NOT NULL +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +INSERT INTO t1 (PostID) VALUES (1), (2); + +CREATE TABLE t2 ( + VoteID int(10) unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT, + EntityID int(10) unsigned NOT NULL, + UserID int(10) unsigned NOT NULL, + UNIQUE KEY EntityID (EntityID,UserID) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +INSERT INTO t2 (EntityID, UserID) VALUES (1, 30), (2, 30); + +SELECT t1.*, T.Voted as Voted +FROM +t1 LEFT JOIN ( + SELECT 1 AS Voted, EntityID + FROM t2 + WHERE t2.UserID = '20' ) AS T + ON T.EntityID = t1.PostID +WHERE t1.PostID='1' +LIMIT 1; + +DROP TABLE t1,t2; + diff --git a/mysql-test/t/table_keyinfo-6838.test b/mysql-test/t/table_keyinfo-6838.test new file mode 100644 index 00000000000..5f4c9f44f86 --- /dev/null +++ b/mysql-test/t/table_keyinfo-6838.test @@ -0,0 +1,18 @@ +# Test case for MDEV-6838. +# Due to incorrect key_length computation, this usecase failed with the error +# Using too big key for internal temp table. + +CREATE TABLE t1 (i INT, state VARCHAR(997)) ENGINE=MyISAM; +INSERT INTO t1 VALUES (2,'Louisiana'),(9,'Maine'); + +CREATE TABLE t2 (state VARCHAR(997), j INT) ENGINE=MyISAM; +INSERT INTO t2 VALUES ('Louisiana',9),('Alaska',5); +INSERT INTO t2 SELECT t2.* FROM t2 JOIN t2 AS t3 JOIN t2 AS t4 JOIN t2 AS t5 JOIN t2 AS t6; + +SET @@max_heap_table_size= 16384; +set @@optimizer_switch='derived_merge=OFF'; +set @@optimizer_switch='extended_keys=ON'; + +SELECT * FROM t1 AS t1_1 LEFT JOIN ( t1 AS t1_2 INNER JOIN (SELECT * FROM t2) v2 ON t1_2.i = j ) ON t1_1.state = v2.state LIMIT 1; + +DROP TABLE t1, t2; diff --git a/mysql-test/t/tmp_table_count-7586.test b/mysql-test/t/tmp_table_count-7586.test new file mode 100644 index 00000000000..e7bac127815 --- /dev/null +++ b/mysql-test/t/tmp_table_count-7586.test @@ -0,0 +1,56 @@ +# MDEV-7586 regression test. +# Test if created_tmp_tables status variable is correctly incremented. +--source include/have_perfschema.inc +--source include/not_embedded.inc + +create table t2 (a int); +insert into t2 values (1),(2),(3); +create view v2 as select a from t2; + +flush status; +select * from v2; +show status like '%Created_tmp%'; + +explain select * from v2; + +select * from (select * from t2) T1; +show status like '%Created_tmp%'; + +explain select * from (select * from t2) T1; + + +drop view v2; +drop table t2; + + +--disable_ps_protocol + +CREATE TABLE t1(a int); +INSERT INTO t1 values(1),(2); +CREATE TABLE t2(a int); +INSERT INTO t2 values(1),(2); + +EXPLAIN SELECT * FROM t1 WHERE a IN (SELECT * FROM t2 GROUP BY a HAVING a > 1); +truncate table performance_schema.events_statements_history_long; +flush status; +CREATE TABLE t3 SELECT * FROM t1 WHERE a IN (SELECT * FROM t2 GROUP BY a HAVING a > 1); +--echo # Performance schema should be the same as "Created_tmp_tables" variable below +select sum(created_tmp_tables) from performance_schema.events_statements_history_long; +show status like '%Created_tmp%'; +drop table t3; + +EXPLAIN SELECT * FROM t1 WHERE a IN (SELECT * FROM t2 GROUP BY a); +truncate table performance_schema.events_statements_history_long; +flush status; +CREATE TABLE t3 SELECT * FROM t1 WHERE a IN (SELECT * FROM t2 GROUP BY a); +--echo # Performance schema should be the same as "Created_tmp_tables" variable below +select sum(created_tmp_tables) from performance_schema.events_statements_history_long; +show status like '%Created_tmp%'; + +drop table t1,t2,t3; + +truncate table performance_schema.events_statements_history_long; +flush status; +--echo # Performance schema should be the same as "Created_tmp_tables" variable below +select sum(created_tmp_tables) from performance_schema.events_statements_history_long; +show status like '%Created_tmp%'; diff --git a/mysql-test/t/trigger-trans.test b/mysql-test/t/trigger-trans.test index 82bee7aa224..c17202055e1 100644 --- a/mysql-test/t/trigger-trans.test +++ b/mysql-test/t/trigger-trans.test @@ -2,6 +2,9 @@ # (or just InnoDB storage engine) --source include/have_innodb.inc +# Save the initial number of concurrent sessions +--source include/count_sessions.inc + --disable_warnings drop table if exists t1; --enable_warnings @@ -182,3 +185,54 @@ insert into t1 values ( 654, 'a'), ( 654, 'b'), ( 654, 'c'), select * from t2 order by b; drop trigger t1_after_insert; drop table t1,t2; + +--echo # +--echo #Bug#19683834 SOME INNODB ERRORS CAUSES STORED FUNCTION +--echo # AND TRIGGER HANDLERS TO BE IGNORED + +--echo #Code fixed in Bug#16041903 +--enable_connect_log + +CREATE TABLE t1 (id int unsigned PRIMARY KEY, val int DEFAULT 0) +ENGINE=InnoDB; +INSERT INTO t1 (id) VALUES (1), (2); + +CREATE TABLE t2 (id int PRIMARY KEY); +CREATE TABLE t3 LIKE t2; + +# Trigger with continue handler for ER_DUP_ENTRY(1062) +DELIMITER //; +CREATE TRIGGER bef_insert BEFORE INSERT ON t2 FOR EACH ROW +BEGIN + DECLARE CONTINUE HANDLER FOR 1062 BEGIN END; + INSERT INTO t3 (id) VALUES (NEW.id); + INSERT INTO t3 (id) VALUES (NEW.id); +END// +DELIMITER ;// + +# Transaction 1: Grab locks on t1 +START TRANSACTION; +UPDATE t1 SET val = val + 1; + +# Transaction 2: +--connect (con2,localhost,root,,test,,) +SET SESSION innodb_lock_wait_timeout = 2; +# Trigger lock timeout (1205) +--error ER_LOCK_WAIT_TIMEOUT +UPDATE t1 SET val = val + 1; + +# This insert should go through, as the continue handler should +# handle ER_DUP_ENTRY, even after ER_LOCK_WAIT_TIMEOUT (Bug#16041903) +INSERT INTO t2 (id) VALUES (1); + +# Cleanup +disconnect con2; +--source include/wait_until_disconnected.inc +connection default; + +DROP TABLE t3, t2, t1; + +--disable_connect_log + +# Wait till we reached the initial number of concurrent sessions +--source include/wait_until_count_sessions.inc diff --git a/mysql-test/t/truncate-stale-6500.test b/mysql-test/t/truncate-stale-6500.test new file mode 100644 index 00000000000..47dffb1966d --- /dev/null +++ b/mysql-test/t/truncate-stale-6500.test @@ -0,0 +1,32 @@ +--source include/have_innodb.inc +--source include/have_partition.inc + +SET GLOBAL query_cache_size=1024*1024*8; +CREATE TABLE `test` ( + `uniqueId` INT NOT NULL, + `partitionId` INT NOT NULL, + PRIMARY KEY (`uniqueId`,`partitionId`) +) ENGINE=InnoDB PARTITION BY LIST (partitionId) ( + PARTITION p01 VALUES IN (1), + PARTITION p02 VALUES IN (2) +); + + +INSERT INTO `test`(`uniqueId`,`partitionId`) VALUES(407237055, 2); + +SELECT * FROM `test`; + +--echo #Confirms 1 row in partition 'p02' +SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS FROM information_schema.PARTITIONS where TABLE_NAME = 'test'; +ALTER TABLE `test` TRUNCATE PARTITION `p02`; + +--echo #Confirms no more rows in partition 'p02' +SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS FROM information_schema.PARTITIONS where TABLE_NAME = 'test'; + +--echo #Before the patch, this returned the previously existing values. +SELECT * FROM `test`; +SELECT SQL_CACHE * FROM `test`; +SELECT SQL_NO_CACHE * FROM `test`; + +DROP TABLE test; +SET GLOBAL query_cache_size=DEFAULT; diff --git a/mysql-test/t/type_decimal.test b/mysql-test/t/type_decimal.test index 1d4ef345747..659e75270ca 100644 --- a/mysql-test/t/type_decimal.test +++ b/mysql-test/t/type_decimal.test @@ -583,3 +583,27 @@ JOIN WHERE CONCAT(t1.col1, CAST(subq.col0 AS DECIMAL)); DROP TABLE t1, t2; + + +--echo # +--echo # Start of 5.5 tests +--echo # + +--echo # +--echo # MDEV-8267 Add /*old*/ comment into I_S.COLUMN_TYPE for old DECIMAL +--echo # + +let $MYSQLD_DATADIR= `select @@datadir`; + +--copy_file std_data/old_decimal/t1dec102.frm $MYSQLD_DATADIR/test/t1dec102.frm +--copy_file std_data/old_decimal/t1dec102.MYD $MYSQLD_DATADIR/test/t1dec102.MYD +--copy_file std_data/old_decimal/t1dec102.MYI $MYSQLD_DATADIR/test/t1dec102.MYI + +SHOW CREATE TABLE t1dec102; +SHOW COLUMNS FROM t1dec102; +SELECT COLUMN_NAME, DATA_TYPE, COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='t1dec102'; +DROP TABLE t1dec102; + +--echo # +--echo # End of 5.5 tests +--echo # diff --git a/mysql-test/t/type_timestamp.test b/mysql-test/t/type_timestamp.test index 77ce8c595ca..195252a2df9 100644 --- a/mysql-test/t/type_timestamp.test +++ b/mysql-test/t/type_timestamp.test @@ -492,4 +492,31 @@ drop function f1; set timestamp=0; SET time_zone=DEFAULT; +--echo # +--echo # MDEV-7778 impossible create copy of table, if table contain default value for timestamp field +--echo # + +SET sql_mode="NO_ZERO_DATE"; +CREATE TABLE t1 ( + ts timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP +); +CREATE TABLE t2 AS SELECT * from t1 LIMIT 0; +SHOW CREATE TABLE t1; +SHOW CREATE TABLE t2; +DROP TABLE t1,t2; +SET sql_mode=DEFAULT; + +--echo # +--echo # MDEV-8082 ON UPDATE is not preserved by CREATE TABLE .. SELECT +--echo # +CREATE TABLE t1 ( + vc VARCHAR(10) NOT NULL DEFAULT 'test', + ts timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP +); +CREATE TABLE t2 AS SELECT * FROM t1 LIMIT 0; +SHOW CREATE TABLE t1; +SHOW CREATE TABLE t2; +DROP TABLE t1,t2; + + --echo End of 10.0 tests diff --git a/mysql-test/t/uniques_crash-7912.test b/mysql-test/t/uniques_crash-7912.test new file mode 100644 index 00000000000..8dc82f8f540 --- /dev/null +++ b/mysql-test/t/uniques_crash-7912.test @@ -0,0 +1,26 @@ +# +# MDEV-7912 +# +# multitable delete with wrongly set sort_buffer_size crashes in merge_buffers + +--source include/have_innodb.inc +--source include/have_debug.inc +--source include/windows.inc + +call mtr.add_suppression("Out of memory"); + +set sql_mode=""; +--disable_warnings +drop table if exists t1,t2; +create table `t1` (`a` datetime not null) engine=InnoDB; +create table `t2` (`a` int not null) engine=innodb; +replace into t1 values (),(); +insert into t2 values(0); +set session sort_buffer_size = 1024*1024*1024*1024; +#Either fail with EE_OUTOFMEMORY, or succeed +--error 0 , 5 +delete d2 from t2 as d1, t1 as d2 where d1.a <=> d2.a; +--enable_warnings + +drop table t2; +drop table t1; diff --git a/mysql-test/t/update_innodb.test b/mysql-test/t/update_innodb.test new file mode 100644 index 00000000000..67c356c4e2e --- /dev/null +++ b/mysql-test/t/update_innodb.test @@ -0,0 +1,39 @@ +--source include/have_innodb.inc + +CREATE TABLE `t1` ( + `c1` int(11) NOT NULL, + `c2` datetime DEFAULT NULL, + PRIMARY KEY (`c1`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +CREATE TABLE `t2` ( + `c0` varchar(10) NOT NULL, + `c1` int(11) NOT NULL, + `c2` int(11) NOT NULL, + PRIMARY KEY (`c0`,`c1`), + KEY `c1` (`c1`), + KEY `c2` (`c2`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +CREATE TABLE `t3` ( + `id` int(11) unsigned NOT NULL AUTO_INCREMENT, + `c1` datetime NOT NULL, + `c2` bigint(20) NOT NULL, + `c3` int(4) unsigned NOT NULL, + PRIMARY KEY (`id`), + KEY `c2` (`c2`), + KEY `c3` (`c3`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +CREATE TABLE `t4` ( + `c1` int(11) NOT NULL, + `c2` bigint(20) DEFAULT NULL, + `c3` int(11) NOT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + + CREATE ALGORITHM=UNDEFINED VIEW `v1` AS select `t4`.`c1` AS `c1`,`t4`.`c2` AS `c2`,`t4`.`c3` AS `c3` from `t4`; + +UPDATE t1 a JOIN t2 b ON a.c1 = b.c1 JOIN v1 vw ON b.c2 = vw.c1 JOIN t3 del ON vw.c2 = del.c2 SET a.c2 = ( SELECT max(t.c1) FROM t3 t, v1 i WHERE del.c2 = t.c2 AND vw.c3 = i.c3 AND t.c3 = 4 ) WHERE a.c2 IS NULL OR a.c2 < '2011-05-01'; + +drop view v1; +drop table t1,t2,t3,t4; diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test index ae78d5504cf..e95194e3f2c 100644 --- a/mysql-test/t/view.test +++ b/mysql-test/t/view.test @@ -5345,6 +5345,41 @@ create view v1 as select 1; drop view v1; +--echo # +--echo # MDEV-7260: Crash in get_best_combination when executing multi-table +--echo # UPDATE with nested views +--echo # + +CREATE TABLE `t1` (`id` bigint(20)); + +INSERT INTO `t1` VALUES (1),(2); + +CREATE TABLE `t2` (`id` bigint(20)); + +CREATE TABLE `t3` (`id` bigint(20), `flag` tinyint(4)); + +create view v1 as select id from t1; + +create view v2 as select t2.* from (t2 left join v1 using (id)); + +update t3 left join v2 using (id) set flag=flag+1; + +drop view v2, v1; +drop table t1, t2, t3; + +--echo # +--echo # MDEV-7207 - ALTER VIEW does not change ALGORITM +--echo # +create table t1 (a int, b int); +create algorithm=temptable view v2 (c) as select b+1 from t1; +show create view v2; +alter algorithm=undefined view v2 (c) as select b+1 from t1; +show create view v2; +alter algorithm=merge view v2 (c) as select b+1 from t1; +show create view v2; +drop view v2; +drop table t1; + --echo # ----------------------------------------------------------------- --echo # -- End of 5.5 tests. --echo # ----------------------------------------------------------------- diff --git a/mysql-test/t/view_alias.test b/mysql-test/t/view_alias.test index b155ba6c2a9..09d707296bb 100644 --- a/mysql-test/t/view_alias.test +++ b/mysql-test/t/view_alias.test @@ -90,3 +90,12 @@ eval CREATE VIEW v1 AS $query; DROP VIEW v1; DROP TABLE t1, t2; + +# +# MDEV-7482 VIEW containing INTERVAL(...) +# +create view v1 as select interval(55,10) as my_col; +show create view v1; +select * from v1; +drop view v1; + |