From ac143744a90d1069f0b4f8a47516cdcca915fbfa Mon Sep 17 00:00:00 2001 From: Arun Kuruvila Date: Wed, 28 Sep 2016 15:52:05 +0530 Subject: Bug#24707666: DEFAULT SETTING FOR SECURE-FILE-PRIV SHOULD BE RESTRICTED IN ALL GA RELEASES Back port of WL#6782 to 5.5 and 5.6. This also includes back port of Bug#20771331, Bug#20741572 and Bug#20770671. Bug#24695274 and Bug#24679907 are also handled along with this. --- mysql-test/include/mtr_warnings.sql | 7 +++- mysql-test/include/mysqld--help.inc | 3 +- mysql-test/mysql-test-run.pl | 4 +- mysql-test/r/mysqld--help-notwin.result | 1 - mysql-test/r/mysqld--help-win.result | 1 - .../suite/auth_sec/r/secure_file_priv_error.result | 7 ++++ .../suite/auth_sec/r/secure_file_priv_null.result | 21 ++++++++++ .../auth_sec/r/secure_file_priv_warnings.result | 17 ++++++++ .../r/secure_file_priv_warnings_not_win.result | 9 +++++ .../r/secure_file_priv_warnings_win.result | 8 ++++ .../suite/auth_sec/t/secure_file_priv_error.test | 39 ++++++++++++++++++ .../auth_sec/t/secure_file_priv_null-master.opt | 1 + .../suite/auth_sec/t/secure_file_priv_null.test | 42 +++++++++++++++++++ .../t/secure_file_priv_warnings-master.opt | 1 + .../auth_sec/t/secure_file_priv_warnings.test | 47 ++++++++++++++++++++++ .../t/secure_file_priv_warnings_not_win.test | 24 +++++++++++ .../auth_sec/t/secure_file_priv_warnings_win.test | 35 ++++++++++++++++ 17 files changed, 262 insertions(+), 5 deletions(-) create mode 100644 mysql-test/suite/auth_sec/r/secure_file_priv_error.result create mode 100644 mysql-test/suite/auth_sec/r/secure_file_priv_null.result create mode 100644 mysql-test/suite/auth_sec/r/secure_file_priv_warnings.result create mode 100644 mysql-test/suite/auth_sec/r/secure_file_priv_warnings_not_win.result create mode 100644 mysql-test/suite/auth_sec/r/secure_file_priv_warnings_win.result create mode 100644 mysql-test/suite/auth_sec/t/secure_file_priv_error.test create mode 100644 mysql-test/suite/auth_sec/t/secure_file_priv_null-master.opt create mode 100644 mysql-test/suite/auth_sec/t/secure_file_priv_null.test create mode 100644 mysql-test/suite/auth_sec/t/secure_file_priv_warnings-master.opt create mode 100644 mysql-test/suite/auth_sec/t/secure_file_priv_warnings.test create mode 100644 mysql-test/suite/auth_sec/t/secure_file_priv_warnings_not_win.test create mode 100644 mysql-test/suite/auth_sec/t/secure_file_priv_warnings_win.test (limited to 'mysql-test') diff --git a/mysql-test/include/mtr_warnings.sql b/mysql-test/include/mtr_warnings.sql index 45acbc03b7e..0a3c3bc60b3 100644 --- a/mysql-test/include/mtr_warnings.sql +++ b/mysql-test/include/mtr_warnings.sql @@ -1,4 +1,4 @@ --- Copyright (c) 2008, 2011, Oracle and/or its affiliates. All rights reserved. +-- Copyright (c) 2008, 2016, Oracle and/or its affiliates. All rights reserved. -- -- This program is free software; you can redistribute it and/or modify -- it under the terms of the GNU General Public License as published by @@ -204,6 +204,11 @@ INSERT INTO global_suppressions VALUES */ ("Found lock of type 6 that is write and read locked"), + /* + Warnings related to --secure-file-priv + */ + ("Insecure configuration for --secure-file-priv:*"), + ("THE_LAST_SUPPRESSION")|| diff --git a/mysql-test/include/mysqld--help.inc b/mysql-test/include/mysqld--help.inc index 380a7f6c8cf..7fa57abbe1e 100644 --- a/mysql-test/include/mysqld--help.inc +++ b/mysql-test/include/mysqld--help.inc @@ -18,7 +18,8 @@ perl; # their paths may vary: @skipvars=qw/basedir open-files-limit general-log-file log plugin-dir log-slow-queries pid-file slow-query-log-file - datadir slave-load-tmpdir tmpdir socket/; + datadir slave-load-tmpdir tmpdir socket + secure-file-priv/; # Plugins which may or may not be there: @plugins=qw/innodb ndb archive blackhole federated partition ndbcluster debug temp-pool ssl des-key-file diff --git a/mysql-test/mysql-test-run.pl b/mysql-test/mysql-test-run.pl index 684d262f410..3eb70c1bdb9 100755 --- a/mysql-test/mysql-test-run.pl +++ b/mysql-test/mysql-test-run.pl @@ -1,7 +1,7 @@ #!/usr/bin/perl # -*- cperl -*- -# Copyright (c) 2004, 2015, Oracle and/or its affiliates. All rights reserved. +# Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved. # # This program is free software; you can redistribute it and/or modify # it under the terms of the GNU General Public License as published by @@ -1823,6 +1823,7 @@ sub collect_mysqld_features { mtr_init_args(\$args); mtr_add_arg($args, "--no-defaults"); mtr_add_arg($args, "--datadir=%s", mixed_path($tmpdir)); + mtr_add_arg($args, "--secure-file-priv=\"\""); mtr_add_arg($args, "--lc-messages-dir=%s", $path_language); mtr_add_arg($args, "--skip-grant-tables"); mtr_add_arg($args, "--verbose"); @@ -3297,6 +3298,7 @@ sub mysql_install_db { mtr_add_arg($args, "--loose-skip-falcon"); mtr_add_arg($args, "--loose-skip-ndbcluster"); mtr_add_arg($args, "--tmpdir=%s", "$opt_vardir/tmp/"); + mtr_add_arg($args, "--secure-file-priv=%s", "$opt_vardir"); mtr_add_arg($args, "--core-file"); if ( $opt_debug ) diff --git a/mysql-test/r/mysqld--help-notwin.result b/mysql-test/r/mysqld--help-notwin.result index d527d6cb702..78dc9ab4d88 100644 --- a/mysql-test/r/mysqld--help-notwin.result +++ b/mysql-test/r/mysqld--help-notwin.result @@ -923,7 +923,6 @@ report-user (No default value) rpl-recovery-rank 0 safe-user-create FALSE secure-auth FALSE -secure-file-priv (No default value) server-id 0 show-slave-auth-info FALSE skip-grant-tables TRUE diff --git a/mysql-test/r/mysqld--help-win.result b/mysql-test/r/mysqld--help-win.result index 2ce9e763b14..1d56da7aa5e 100644 --- a/mysql-test/r/mysqld--help-win.result +++ b/mysql-test/r/mysqld--help-win.result @@ -931,7 +931,6 @@ report-user (No default value) rpl-recovery-rank 0 safe-user-create FALSE secure-auth FALSE -secure-file-priv (No default value) server-id 0 shared-memory FALSE shared-memory-base-name MYSQL diff --git a/mysql-test/suite/auth_sec/r/secure_file_priv_error.result b/mysql-test/suite/auth_sec/r/secure_file_priv_error.result new file mode 100644 index 00000000000..4bb4d87c5f0 --- /dev/null +++ b/mysql-test/suite/auth_sec/r/secure_file_priv_error.result @@ -0,0 +1,7 @@ +#----------------------------------------------------------------------- +# Setup +# Try to restart server with invalid value for --secure-file-priv +# Search for : Failed to access directory for --secure-file-priv. +# Restart completed. +# Restart +#----------------------------------------------------------------------- diff --git a/mysql-test/suite/auth_sec/r/secure_file_priv_null.result b/mysql-test/suite/auth_sec/r/secure_file_priv_null.result new file mode 100644 index 00000000000..e2a5102c627 --- /dev/null +++ b/mysql-test/suite/auth_sec/r/secure_file_priv_null.result @@ -0,0 +1,21 @@ +#----------------------------------------------------------------------- +# Setup +#----------------------------------------------------------------------- +# Search for : --secure-file-priv is set to NULL. Operations +# related to importing and exporting data are +# disabled +show variables like 'secure_file_priv'; +Variable_name Value +secure_file_priv null +use test; +drop table if exists secure_file_priv_test_null; +create table secure_file_priv_test_null(c1 int); +insert into secure_file_priv_test_null values (1), (2), (3), (4); +select * from secure_file_priv_test_null into outfile 'blah'; +ERROR HY000: The MySQL server is running with the --secure-file-priv option so it cannot execute this statement +select * from secure_file_priv_test_null into outfile 'null/blah'; +ERROR HY000: The MySQL server is running with the --secure-file-priv option so it cannot execute this statement +drop table secure_file_priv_test_null; +#----------------------------------------------------------------------- +# Clean-up +#----------------------------------------------------------------------- diff --git a/mysql-test/suite/auth_sec/r/secure_file_priv_warnings.result b/mysql-test/suite/auth_sec/r/secure_file_priv_warnings.result new file mode 100644 index 00000000000..3b80cbe8d6f --- /dev/null +++ b/mysql-test/suite/auth_sec/r/secure_file_priv_warnings.result @@ -0,0 +1,17 @@ +#----------------------------------------------------------------------- +# Setup +#----------------------------------------------------------------------- +# Search for : Insecure configuration for --secure-file-priv: Current +# value does not restrict location of generated files. +# Consider setting it to a valid, non-empty path. +SHOW VARIABLES LIKE 'secure_file_priv'; +Variable_name Value +secure_file_priv +#----------------------------------------------------------------------- +# Restart completed. +# Search for : Insecure configuration for --secure-file-priv: Plugin +# directory is accessible through --secure-file-priv. +# Consider choosing a different directory. +#----------------------------------------------------------------------- +# Clean-up +#----------------------------------------------------------------------- diff --git a/mysql-test/suite/auth_sec/r/secure_file_priv_warnings_not_win.result b/mysql-test/suite/auth_sec/r/secure_file_priv_warnings_not_win.result new file mode 100644 index 00000000000..84e2f8ac3c2 --- /dev/null +++ b/mysql-test/suite/auth_sec/r/secure_file_priv_warnings_not_win.result @@ -0,0 +1,9 @@ +#----------------------------------------------------------------------- +# Search for : Insecure configuration for --secure-file-priv: Data +# directory is accessible through --secure-file-priv. +# Consider choosing a different directory. +#----------------------------------------------------------------------- +# Search for : Insecure configuration for --secure-file-priv: Location +# is accessible to all OS users. Consider choosing a +# different directory. +#----------------------------------------------------------------------- diff --git a/mysql-test/suite/auth_sec/r/secure_file_priv_warnings_win.result b/mysql-test/suite/auth_sec/r/secure_file_priv_warnings_win.result new file mode 100644 index 00000000000..3beff6c4747 --- /dev/null +++ b/mysql-test/suite/auth_sec/r/secure_file_priv_warnings_win.result @@ -0,0 +1,8 @@ +#----------------------------------------------------------------------- +# Test 2 : Restarting mysqld with : +# --secure-file-priv=MYSQLTEST_VARDIR/mysqld.1/Data +# Restart completed. +# Search for : Insecure configuration for --secure-file-priv: Data +# directory is accessible through --secure-file-priv. +# Consider choosing a different directory. +#----------------------------------------------------------------------- diff --git a/mysql-test/suite/auth_sec/t/secure_file_priv_error.test b/mysql-test/suite/auth_sec/t/secure_file_priv_error.test new file mode 100644 index 00000000000..9f8d185d8f5 --- /dev/null +++ b/mysql-test/suite/auth_sec/t/secure_file_priv_error.test @@ -0,0 +1,39 @@ +--source include/no_valgrind_without_big.inc +--source include/not_embedded.inc + +--echo #----------------------------------------------------------------------- +--echo # Setup +let restart_log= $MYSQLTEST_VARDIR/log/my_restart.err; +let SEARCH_FILE= $restart_log; +let $restart_file= $MYSQLTEST_VARDIR/tmp/mysqld.1.expect; + +--echo # Try to restart server with invalid value for --secure-file-priv +--exec echo "wait" > $restart_file +--shutdown_server +--source include/wait_until_disconnected.inc + +--error 0,1 +--remove_file $restart_log +# Following should fail +--error 1 +--exec $MYSQLD_CMD --secure-file-priv=blahblahblah --loose-console > $restart_log 2>&1 + +--echo # Search for : Failed to access directory for --secure-file-priv. +let SEARCH_PATTERN= Failed to access directory for --secure-file-priv; +--source include/search_pattern_in_file.inc + +--remove_file $restart_log + +--source include/wait_until_disconnected.inc +# Dummy argument for restart +--exec echo "restart:" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect +--enable_reconnect +--source include/wait_until_connected_again.inc +--disable_reconnect +--echo # Restart completed. + +--echo # Restart +--disable_warnings +--source include/force_restart.inc +--enable_warnings +--echo #----------------------------------------------------------------------- diff --git a/mysql-test/suite/auth_sec/t/secure_file_priv_null-master.opt b/mysql-test/suite/auth_sec/t/secure_file_priv_null-master.opt new file mode 100644 index 00000000000..80d7f3cd469 --- /dev/null +++ b/mysql-test/suite/auth_sec/t/secure_file_priv_null-master.opt @@ -0,0 +1 @@ +--secure-file-priv=null diff --git a/mysql-test/suite/auth_sec/t/secure_file_priv_null.test b/mysql-test/suite/auth_sec/t/secure_file_priv_null.test new file mode 100644 index 00000000000..8d394a13589 --- /dev/null +++ b/mysql-test/suite/auth_sec/t/secure_file_priv_null.test @@ -0,0 +1,42 @@ +--source include/no_valgrind_without_big.inc +--source include/not_embedded.inc + +--echo #----------------------------------------------------------------------- +--echo # Setup +let server_log= $MYSQLTEST_VARDIR/log/mysqld.1.err; +let SEARCH_FILE= $server_log; +let $restart_file= $MYSQLTEST_VARDIR/tmp/mysqld.1.expect; +--echo #----------------------------------------------------------------------- + +--echo # Search for : --secure-file-priv is set to NULL. Operations +--echo # related to importing and exporting data are +--echo # disabled +let SEARCH_PATTERN= --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled; +--source include/search_pattern_in_file.inc + +connect(test4_con,localhost,root,,,,,); +show variables like 'secure_file_priv'; + +use test; +--disable_warnings +drop table if exists secure_file_priv_test_null; +--enable_warnings +create table secure_file_priv_test_null(c1 int); +insert into secure_file_priv_test_null values (1), (2), (3), (4); +--error 1290 +select * from secure_file_priv_test_null into outfile 'blah'; +--error 1290 +select * from secure_file_priv_test_null into outfile 'null/blah'; +drop table secure_file_priv_test_null; + +connection default; +disconnect test4_con; + +--echo #----------------------------------------------------------------------- + +--echo # Clean-up +--disable_warnings +--source include/force_restart.inc +--enable_warnings + +--echo #----------------------------------------------------------------------- diff --git a/mysql-test/suite/auth_sec/t/secure_file_priv_warnings-master.opt b/mysql-test/suite/auth_sec/t/secure_file_priv_warnings-master.opt new file mode 100644 index 00000000000..22520f0aa99 --- /dev/null +++ b/mysql-test/suite/auth_sec/t/secure_file_priv_warnings-master.opt @@ -0,0 +1 @@ +--secure-file-priv="" diff --git a/mysql-test/suite/auth_sec/t/secure_file_priv_warnings.test b/mysql-test/suite/auth_sec/t/secure_file_priv_warnings.test new file mode 100644 index 00000000000..cc7a79d5b3c --- /dev/null +++ b/mysql-test/suite/auth_sec/t/secure_file_priv_warnings.test @@ -0,0 +1,47 @@ +--source include/no_valgrind_without_big.inc +--source include/not_embedded.inc + +--echo #----------------------------------------------------------------------- +--echo # Setup +let server_log= $MYSQLTEST_VARDIR/log/mysqld.1.err; +let SEARCH_FILE= $server_log; +let $restart_file= $MYSQLTEST_VARDIR/tmp/mysqld.1.expect; +let PLUGIN_DIR= $MYSQLTEST_VARDIR/tmp; +--echo #----------------------------------------------------------------------- + +--echo # Search for : Insecure configuration for --secure-file-priv: Current +--echo # value does not restrict location of generated files. +--echo # Consider setting it to a valid, non-empty path. +let SEARCH_PATTERN= Insecure configuration for --secure-file-priv: Current value does not restrict location of generated files. Consider setting it to a valid, non-empty path.; +--source include/search_pattern_in_file.inc + +# Must show empty string +SHOW VARIABLES LIKE 'secure_file_priv'; + +--echo #----------------------------------------------------------------------- + +let $restart_file= $MYSQLTEST_VARDIR/tmp/mysqld.1.expect; +--exec echo "wait" > $restart_file +--shutdown_server +--source include/wait_until_disconnected.inc +--remove_file $server_log +--exec echo "restart:--plugin-dir=$PLUGIN_DIR --secure-file-priv=$PLUGIN_DIR" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect +--enable_reconnect +--source include/wait_until_connected_again.inc +--disable_reconnect +--echo # Restart completed. + +--echo # Search for : Insecure configuration for --secure-file-priv: Plugin +--echo # directory is accessible through --secure-file-priv. +--echo # Consider choosing a different directory. +let SEARCH_PATTERN= Insecure configuration for --secure-file-priv: Plugin directory is accessible through --secure-file-priv. Consider choosing a different directory.; +--source include/search_pattern_in_file.inc + +--echo #----------------------------------------------------------------------- + +--echo # Clean-up +--disable_warnings +--source include/force_restart.inc +--enable_warnings + +--echo #----------------------------------------------------------------------- diff --git a/mysql-test/suite/auth_sec/t/secure_file_priv_warnings_not_win.test b/mysql-test/suite/auth_sec/t/secure_file_priv_warnings_not_win.test new file mode 100644 index 00000000000..ec027d4a743 --- /dev/null +++ b/mysql-test/suite/auth_sec/t/secure_file_priv_warnings_not_win.test @@ -0,0 +1,24 @@ +--source include/no_valgrind_without_big.inc +--source include/not_windows.inc +--source include/not_embedded.inc + +let server_log= $MYSQLTEST_VARDIR/log/mysqld.1.err; +let SEARCH_FILE= $server_log; + +--echo #----------------------------------------------------------------------- + +--echo # Search for : Insecure configuration for --secure-file-priv: Data +--echo # directory is accessible through --secure-file-priv. +--echo # Consider choosing a different directory. +let SEARCH_PATTERN= Insecure configuration for --secure-file-priv: Data directory is accessible through --secure-file-priv. Consider choosing a different directory.; +--source include/search_pattern_in_file.inc + +--echo #----------------------------------------------------------------------- + +--echo # Search for : Insecure configuration for --secure-file-priv: Location +--echo # is accessible to all OS users. Consider choosing a +--echo # different directory. +let SEARCH_PATTERN= Insecure configuration for --secure-file-priv: Location is accessible to all OS users. Consider choosing a different directory.; +--source include/search_pattern_in_file.inc + +--echo #----------------------------------------------------------------------- diff --git a/mysql-test/suite/auth_sec/t/secure_file_priv_warnings_win.test b/mysql-test/suite/auth_sec/t/secure_file_priv_warnings_win.test new file mode 100644 index 00000000000..bb175fb40ea --- /dev/null +++ b/mysql-test/suite/auth_sec/t/secure_file_priv_warnings_win.test @@ -0,0 +1,35 @@ +--source include/no_valgrind_without_big.inc +--source include/windows.inc +--source include/not_embedded.inc + +let server_log= $MYSQLTEST_VARDIR/log/mysqld.1.err; +let SEARCH_FILE= $server_log; + +--echo #----------------------------------------------------------------------- + +--echo # Test 2 : Restarting mysqld with : +--echo # --secure-file-priv=MYSQLTEST_VARDIR/mysqld.1/Data + +let $restart_file= $MYSQLTEST_VARDIR/tmp/mysqld.1.expect; +--exec echo "wait" > $restart_file +--shutdown_server +--source include/wait_until_disconnected.inc +--error 0,1 +--remove_file $server_log +--exec echo "restart: --secure-file-priv=$MYSQLTEST_VARDIR/mysqld.1/Data" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect +--enable_reconnect +--source include/wait_until_connected_again.inc +--disable_reconnect +--echo # Restart completed. + +--echo # Search for : Insecure configuration for --secure-file-priv: Data +--echo # directory is accessible through --secure-file-priv. +--echo # Consider choosing a different directory. +let SEARCH_PATTERN= Insecure configuration for --secure-file-priv: Data directory is accessible through --secure-file-priv. Consider choosing a different directory.; +--source include/search_pattern_in_file.inc + +--disable_warnings +--source include/force_restart.inc +--enable_warnings + +--echo #----------------------------------------------------------------------- -- cgit v1.2.1 From c3cf7f47f0f4a1ec314001aaf0c3d9c1c1f62097 Mon Sep 17 00:00:00 2001 From: Thayumanavar S Date: Fri, 28 Oct 2016 14:45:03 +0200 Subject: BUG#24487120 - SLAVE'S SLAVE_SQL_RUNNING IS STOPPED DURING LOAD DATA AT MASTER. Revert "BUG#23080148 - BACKPORT BUG 14653594 AND BUG 20683959 TO" This reverts commit 1d31f5b3090d129382b50b95512f2f79305715a1. The commit causes replication incompatibility between minor revisions and based on discussion with Srinivasarao, the patch is reverted. --- mysql-test/r/loaddata.result | 26 +------------------------- mysql-test/std_data/bug20683959loaddata.txt | 1 - mysql-test/t/loaddata.test | 25 +------------------------ 3 files changed, 2 insertions(+), 50 deletions(-) delete mode 100644 mysql-test/std_data/bug20683959loaddata.txt (limited to 'mysql-test') diff --git a/mysql-test/r/loaddata.result b/mysql-test/r/loaddata.result index 2f2a3579eec..2d67d24bedd 100644 --- a/mysql-test/r/loaddata.result +++ b/mysql-test/r/loaddata.result @@ -507,7 +507,7 @@ DROP TABLE t1; # Bug#11765139 58069: LOAD DATA INFILE: VALGRIND REPORTS INVALID MEMORY READS AND WRITES WITH U # CREATE TABLE t1(f1 INT); -SELECT 0xE1C330 INTO OUTFILE 't1.dat'; +SELECT 0xE1BB30 INTO OUTFILE 't1.dat'; LOAD DATA INFILE 't1.dat' IGNORE INTO TABLE t1 CHARACTER SET utf8; DROP TABLE t1; # @@ -532,27 +532,3 @@ FIELDS TERMINATED BY 't' LINES TERMINATED BY ''; Got one of the listed errors SET @@sql_mode= @old_mode; DROP TABLE t1; - -# -# Bug#23080148 - Backport of Bug#20683959. -# Bug#20683959 LOAD DATA INFILE IGNORES A SPECIFIC ROW SILENTLY -# UNDER DB CHARSET IS UTF8. -# -CREATE DATABASE d1 CHARSET latin1; -USE d1; -CREATE TABLE t1 (val TEXT); -LOAD DATA INFILE '../../std_data/bug20683959loaddata.txt' INTO TABLE t1; -SELECT COUNT(*) FROM t1; -COUNT(*) -1 -SELECT HEX(val) FROM t1; -HEX(val) -C38322525420406E696F757A656368756E3A20E98198E2889AF58081AEE7B99DE4B88AE383A3E7B99DE69690F58087B3E7B9A7EFBDA8E7B99DEFBDB3E7B99DE78999E880B3E7B8BAEFBDAAE7B9A7E89699E296A1E7B8BAE4BBA3EFBD8CE7B8BAEFBDA9E7B8B2E2889AE38184E7B99DEFBDB3E7B99DE4B88AE383A3E7B99DE69690F58087B3E7B9A7EFBDA8E7B99DEFBDB3E7B99DE5B3A8EFBD84E8ABA0EFBDA8E89C89F580948EE599AAE7B8BAEFBDAAE7B8BAE9A198EFBDA9EFBDB1E7B9A7E581B5E289A0E7B8BAEFBDBEE7B9A7E9A194EFBDA9E882B4EFBDA5EFBDB5E980A7F5808B96E28693E99EABE38287E58F99E7B8BAE58AB1E28691E7B8BAF5808B9AE7828AE98095EFBDB1E7B8BAEFBDAFE7B8B2E288ABE6A89FE89EB3E6BA98F58081ADE88EA0EFBDBAE98095E6BA98F58081AEE89D93EFBDBAE8AD9BEFBDACE980A7F5808B96E28693E7B8BAF580918EE288AAE7B8BAE4B88AEFBC9EE7B8BAE4B99DE28691E7B8BAF5808B96EFBCA0E88DB3E6A68AEFBDB9EFBDB3E981B2E5B3A8E296A1E7B8BAE7A4BCE7828AE88DB3E6A68AEFBDB0EFBDBDE7B8BAA0E7B8BAE88B93EFBDBEE5B899EFBC9E -CREATE DATABASE d2 CHARSET utf8; -USE d2; -CREATE TABLE t1 (val TEXT); -LOAD DATA INFILE '../../std_data/bug20683959loaddata.txt' INTO TABLE t1; -ERROR HY000: Invalid utf8 character string: '"RT @niouzechun: \9058\221A' -DROP TABLE d1.t1, d2.t1; -DROP DATABASE d1; -DROP DATABASE d2; diff --git a/mysql-test/std_data/bug20683959loaddata.txt b/mysql-test/std_data/bug20683959loaddata.txt deleted file mode 100644 index 1878cc78879..00000000000 --- a/mysql-test/std_data/bug20683959loaddata.txt +++ /dev/null @@ -1 +0,0 @@ -Ã"RT @niouzechun: 遘√繝上ャ繝斐繧ｨ繝ｳ繝牙耳縺ｪ繧薙□縺代ｌ縺ｩ縲√い繝ｳ繝上ャ繝斐繧ｨ繝ｳ繝峨ｄ諠ｨ蜉噪縺ｪ縺願ｩｱ繧偵≠縺ｾ繧顔ｩ肴･ｵ逧↓鞫ょ叙縺励↑縺炊逕ｱ縺ｯ縲∫樟螳溘莠ｺ逕溘蝓ｺ譛ｬ逧↓縺∪縺上＞縺九↑縺＠荳榊ｹｳ遲峨□縺礼炊荳榊ｰｽ縺縺苓ｾ帙＞ diff --git a/mysql-test/t/loaddata.test b/mysql-test/t/loaddata.test index 9a664b84843..aa7be52484e 100644 --- a/mysql-test/t/loaddata.test +++ b/mysql-test/t/loaddata.test @@ -610,7 +610,7 @@ disconnect con1; --echo # CREATE TABLE t1(f1 INT); -EVAL SELECT 0xE1C330 INTO OUTFILE 't1.dat'; +EVAL SELECT 0xE1BB30 INTO OUTFILE 't1.dat'; --disable_warnings LOAD DATA INFILE 't1.dat' IGNORE INTO TABLE t1 CHARACTER SET utf8; --enable_warnings @@ -656,26 +656,3 @@ SET @@sql_mode= @old_mode; --remove_file $MYSQLTEST_VARDIR/mysql DROP TABLE t1; ---echo ---echo # ---echo # Bug#23080148 - Backport of Bug#20683959. ---echo # Bug#20683959 LOAD DATA INFILE IGNORES A SPECIFIC ROW SILENTLY ---echo # UNDER DB CHARSET IS UTF8. ---echo # - -CREATE DATABASE d1 CHARSET latin1; -USE d1; -CREATE TABLE t1 (val TEXT); -LOAD DATA INFILE '../../std_data/bug20683959loaddata.txt' INTO TABLE t1; -SELECT COUNT(*) FROM t1; -SELECT HEX(val) FROM t1; - -CREATE DATABASE d2 CHARSET utf8; -USE d2; -CREATE TABLE t1 (val TEXT); ---error ER_INVALID_CHARACTER_STRING -LOAD DATA INFILE '../../std_data/bug20683959loaddata.txt' INTO TABLE t1; - -DROP TABLE d1.t1, d2.t1; -DROP DATABASE d1; -DROP DATABASE d2; -- cgit v1.2.1 From 42732cc195fd237c9b17490e96f1bb29db433b0b Mon Sep 17 00:00:00 2001 From: Dyre Tjeldvoll Date: Thu, 24 Nov 2016 09:57:54 +0100 Subject: Bug#25092566: CREATE TABLE WITH DATA DIRECTORY CLAUSE DOES NOT REQUIRE SPECIAL PRIVILEGES Require FILE privilege when creating tables using external data directory or index directory. --- mysql-test/r/partition_symlink.result | 2 ++ mysql-test/t/partition_symlink.test | 2 ++ 2 files changed, 4 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/partition_symlink.result b/mysql-test/r/partition_symlink.result index f26a1642a52..91cc78f1361 100644 --- a/mysql-test/r/partition_symlink.result +++ b/mysql-test/r/partition_symlink.result @@ -4,6 +4,8 @@ DROP DATABASE IF EXISTS mysqltest2; # test.t1 have partitions in mysqltest2-directory! # user root: CREATE USER mysqltest_1@localhost; +# Need FILE permission to use external datadir or indexdir. +GRANT FILE ON *.* TO mysqltest_1@localhost; CREATE DATABASE mysqltest2; USE mysqltest2; CREATE TABLE t1 (a INT) ENGINE = MyISAM; diff --git a/mysql-test/t/partition_symlink.test b/mysql-test/t/partition_symlink.test index 5fdde8e0abc..9e57d616213 100644 --- a/mysql-test/t/partition_symlink.test +++ b/mysql-test/t/partition_symlink.test @@ -32,6 +32,8 @@ DROP DATABASE IF EXISTS mysqltest2; -- echo # test.t1 have partitions in mysqltest2-directory! -- echo # user root: CREATE USER mysqltest_1@localhost; +-- echo # Need FILE permission to use external datadir or indexdir. + GRANT FILE ON *.* TO mysqltest_1@localhost; CREATE DATABASE mysqltest2; USE mysqltest2; CREATE TABLE t1 (a INT) ENGINE = MyISAM; -- cgit v1.2.1 From 14e1f32894cdbe63a614738cfd93e9d0818dedee Mon Sep 17 00:00:00 2001 From: Elena Stepanova Date: Sun, 11 Dec 2016 00:50:00 +0200 Subject: Follow-up for 02d153c7b9 (str2decimal: don't return a negative zero) --- mysql-test/suite/engines/iuds/r/insert_decimal.result | 1 - 1 file changed, 1 deletion(-) (limited to 'mysql-test') diff --git a/mysql-test/suite/engines/iuds/r/insert_decimal.result b/mysql-test/suite/engines/iuds/r/insert_decimal.result index 50fde80d81d..eab8592c4ee 100644 --- a/mysql-test/suite/engines/iuds/r/insert_decimal.result +++ b/mysql-test/suite/engines/iuds/r/insert_decimal.result @@ -48,7 +48,6 @@ Warning 1264 Out of range value for column 'c2' at row 1 Note 1265 Data truncated for column 'c3' at row 1 insert into t2 values ("0.0","0.0","0.0",7),("-0.0","-0.0","-0.0",8),("+0.0","+0.0","+0.0",9),("01.0","01.0","01.0",10),("+01.0","+01.0","+01.0",11),("-01.0","-01.0","-01.0",12); Warnings: -Warning 1264 Out of range value for column 'c2' at row 2 Warning 1264 Out of range value for column 'c2' at row 6 insert into t2 values ("-.1","-.1","-.1",13),("+.1","+.1","+.1",14),(".1",".1",".1",15); Warnings: -- cgit v1.2.1 From e86580c3dda707788fb0ca35244cf602d7e8d50d Mon Sep 17 00:00:00 2001 From: Sergei Golubchik Date: Thu, 15 Dec 2016 18:20:58 +0100 Subject: MDEV-11552 Queries executed by event scheduler are written to slow log incorrectly or not written at all because thd->update_server_status() is used to measure the query time for the slow log (not only to set protocol level flags), it needs to be called also when the server isn't going to send anything to the client. --- mysql-test/r/events_slowlog.result | 12 ++++++++++++ mysql-test/t/events_slowlog.test | 28 ++++++++++++++++++++++++++++ 2 files changed, 40 insertions(+) create mode 100644 mysql-test/r/events_slowlog.result create mode 100644 mysql-test/t/events_slowlog.test (limited to 'mysql-test') diff --git a/mysql-test/r/events_slowlog.result b/mysql-test/r/events_slowlog.result new file mode 100644 index 00000000000..c97fe8a2ab9 --- /dev/null +++ b/mysql-test/r/events_slowlog.result @@ -0,0 +1,12 @@ +set @event_scheduler_save= @@global.event_scheduler; +set @slow_query_log_save= @@global.slow_query_log; +set global event_scheduler= on; +set global slow_query_log= on; +set global long_query_time=0.2; +create table t1 (i int); +insert into t1 values (0); +create event ev on schedule at CURRENT_TIMESTAMP + INTERVAL 1 second do update t1 set i=1+sleep(0.5); +drop table t1; +set global event_scheduler= @event_scheduler_save; +set global slow_query_log= @slow_query_log_save; +set global long_query_time= @@session.long_query_time; diff --git a/mysql-test/t/events_slowlog.test b/mysql-test/t/events_slowlog.test new file mode 100644 index 00000000000..9679714dba3 --- /dev/null +++ b/mysql-test/t/events_slowlog.test @@ -0,0 +1,28 @@ +--source include/not_embedded.inc +# +# MDEV-11552 Queries executed by event scheduler are written to slow log incorrectly or not written at all +# +set @event_scheduler_save= @@global.event_scheduler; +set @slow_query_log_save= @@global.slow_query_log; + +set global event_scheduler= on; +set global slow_query_log= on; +set global long_query_time=0.2; + +create table t1 (i int); +insert into t1 values (0); +create event ev on schedule at CURRENT_TIMESTAMP + INTERVAL 1 second do update t1 set i=1+sleep(0.5); + +--let wait_condition= select i from t1 where i > 0 +--source include/wait_condition.inc + +--let SEARCH_FILE = `SELECT @@slow_query_log_file` +--let SEARCH_PATTERN= update t1 set i=1 +--let SEARCH_RANGE= -1000 +--source include/search_pattern_in_file.inc + +drop table t1; + +set global event_scheduler= @event_scheduler_save; +set global slow_query_log= @slow_query_log_save; +set global long_query_time= @@session.long_query_time; -- cgit v1.2.1 From b03b38dd6515e60689adb6c9ca57d9612618e2bf Mon Sep 17 00:00:00 2001 From: Sergei Golubchik Date: Fri, 16 Dec 2016 10:10:08 +0100 Subject: cleanup: rpl.rpl_row_mysqlbinlog some trivial simplifications. drinking the ocean, one drop at a time --- mysql-test/suite/rpl/r/rpl_row_mysqlbinlog.result | 37 +++---- mysql-test/suite/rpl/t/rpl_row_mysqlbinlog.test | 120 +++++----------------- 2 files changed, 38 insertions(+), 119 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/suite/rpl/r/rpl_row_mysqlbinlog.result b/mysql-test/suite/rpl/r/rpl_row_mysqlbinlog.result index 1489af830cc..19611ac52b5 100644 --- a/mysql-test/suite/rpl/r/rpl_row_mysqlbinlog.result +++ b/mysql-test/suite/rpl/r/rpl_row_mysqlbinlog.result @@ -1,14 +1,21 @@ include/master-slave.inc [connection master] - ---Setup Section -- set timestamp=1000000000; -DROP TABLE IF EXISTS t1,t2,t3; CREATE TABLE t1(word VARCHAR(20)); CREATE TABLE t2(id INT AUTO_INCREMENT NOT NULL PRIMARY KEY); CREATE TABLE t3(c1 INT NOT NULL PRIMARY KEY, c2 LONGBLOB, c3 TIMESTAMP, c4 TEXT, c5 FLOAT); - ----Test1 check table load -- +INSERT INTO t1 VALUES ("abirvalg"); +LOAD DATA INFILE '../../std_data/words.dat' INTO TABLE t1; +LOAD DATA INFILE '../../std_data/words.dat' INTO TABLE t1; +LOAD DATA INFILE '../../std_data/words.dat' INTO TABLE t1; +LOAD DATA INFILE '../../std_data/words.dat' INTO TABLE t1; +LOAD DATA INFILE '../../std_data/words.dat' INTO TABLE t1; +set @d1 = 'dd1'; +set @d1 = concat(@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1); +set @d1 = concat(@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1); +set @d1 = concat(@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1); +---Test 1 check table load -- SELECT COUNT(*) from t1; COUNT(*) 351 @@ -71,9 +78,7 @@ c1 c3 c4 c5 5 2006-02-22 00:00:00 Tested in Texas 11 insert into t1 values ("Alas"); flush logs; - --- Test 1 Dump binlog to file -- - --- Test 1 delete tables, clean master and slave -- DROP TABLE t1; DROP TABLE t2; @@ -84,9 +89,7 @@ reset master; reset slave; start slave; include/wait_for_slave_to_start.inc - --- Test 1 Load from Dump binlog file -- - --- Test 1 Check Load Results -- SELECT COUNT(*) from t1; COUNT(*) @@ -148,7 +151,6 @@ c1 c3 c4 c5 3 2006-02-22 00:00:00 Tested in Texas 6.6 4 2006-02-22 00:00:00 Tested in Texas 8.8 5 2006-02-22 00:00:00 Tested in Texas 11 - --- Test 2 position test -- /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!40019 SET @@session.max_insert_delayed_threads=0*/; @@ -172,7 +174,6 @@ DELIMITER ; ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; - --- Test 3 First Remote test -- /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!40019 SET @@session.max_insert_delayed_threads=0*/; @@ -189,9 +190,6 @@ SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/ SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; -DROP TABLE IF EXISTS `t1`,`t2`,`t3` /* generated by server */ -/*!*/; -SET TIMESTAMP=1000000000/*!*/; CREATE TABLE t1(word VARCHAR(20)) /*!*/; SET TIMESTAMP=1000000000/*!*/; @@ -205,7 +203,6 @@ DELIMITER ; ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; - --- Test 4 Second Remote test -- DROP TABLE t1; DROP TABLE t2; @@ -276,7 +273,6 @@ c1 c3 c4 c5 3 2006-02-22 00:00:00 Tested in Texas 6.6 4 2006-02-22 00:00:00 Tested in Texas 8.8 5 2006-02-22 00:00:00 Tested in Texas 11 - --- Test 5 LOAD DATA -- /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!40019 SET @@session.max_insert_delayed_threads=0*/; @@ -287,7 +283,6 @@ DELIMITER ; ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; - --- Test 6 reading stdin -- /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!40019 SET @@session.max_insert_delayed_threads=0*/; @@ -304,9 +299,6 @@ SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/ SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; -DROP TABLE IF EXISTS `t1`,`t2`,`t3` /* generated by server */ -/*!*/; -SET TIMESTAMP=1000000000/*!*/; CREATE TABLE t1(word VARCHAR(20)) /*!*/; SET TIMESTAMP=1000000000/*!*/; @@ -320,7 +312,6 @@ DELIMITER ; ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; - --- Test 7 reading stdin w/position -- /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!40019 SET @@session.max_insert_delayed_threads=0*/; @@ -344,7 +335,6 @@ DELIMITER ; ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; - --- Test 8 switch internal charset -- stop slave; include/wait_for_slave_to_stop.inc @@ -380,14 +370,13 @@ HEX(f) select HEX(f) from t5; HEX(f) 835C - --- Test cleanup -- -DROP TABLE IF EXISTS t1; +DROP TABLE t1, t2, t3, t04, t05, t4, t5; CREATE TABLE t1 (a INT NOT NULL KEY, b INT); INSERT INTO t1 VALUES(1,1); SELECT * FROM t1; a b 1 1 FLUSH LOGS; -DROP TABLE IF EXISTS t1, t2, t3, t04, t05, t4, t5; +DROP TABLE t1; include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_row_mysqlbinlog.test b/mysql-test/suite/rpl/t/rpl_row_mysqlbinlog.test index ed0f31b75be..678679f0cf1 100644 --- a/mysql-test/suite/rpl/t/rpl_row_mysqlbinlog.test +++ b/mysql-test/suite/rpl/t/rpl_row_mysqlbinlog.test @@ -4,43 +4,27 @@ # Purpose: To test changes to mysqlbinlog for row based bin logs # # We are using .opt file since we need small binlog size # ################################################################## -# Include Section -# Make sure that we have row based bin log -- source include/have_binlog_format_row.inc -# Embedded server doesn't support binlogging -- source include/not_embedded.inc -# This test requires the cp932 charset compiled in -- source include/have_cp932.inc -# Slow test, don't run during staging part --- source include/not_staging.inc - -- source include/master-slave.inc -# Setup Section -# we need this for getting fixed timestamps inside of this test - ---disable_query_log -select "---Setup Section --" as ""; ---enable_query_log +--echo ---Setup Section -- +# we need this for getting fixed timestamps inside of this test set timestamp=1000000000; ---disable_warnings -DROP TABLE IF EXISTS t1,t2,t3; ---enable_warnings - -connection master; CREATE TABLE t1(word VARCHAR(20)); CREATE TABLE t2(id INT AUTO_INCREMENT NOT NULL PRIMARY KEY); ---let $position= query_get_value(SHOW MASTER STATUS, Position, 1) +--let position= query_get_value(SHOW MASTER STATUS, Position, 1) CREATE TABLE t3(c1 INT NOT NULL PRIMARY KEY, c2 LONGBLOB, c3 TIMESTAMP, c4 TEXT, c5 FLOAT); ---let $stop_position=query_get_value(SHOW MASTER STATUS, Position, 1) ---let $stop_position1=`select $stop_position - 1` ---let $binlog_start_pos=query_get_value(SHOW BINLOG EVENTS LIMIT 1, End_log_pos, 1) +--let stop_position=query_get_value(SHOW MASTER STATUS, Position, 1) +--let stop_position1=`select $stop_position - 1` +--let binlog_start_pos=query_get_value(SHOW BINLOG EVENTS LIMIT 1, End_log_pos, 1) + # Test Section # Lets start by putting some data into the tables. ---disable_query_log INSERT INTO t1 VALUES ("abirvalg"); LOAD DATA INFILE '../../std_data/words.dat' INTO TABLE t1; LOAD DATA INFILE '../../std_data/words.dat' INTO TABLE t1; @@ -54,7 +38,8 @@ set @d1 = concat(@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1); set @d1 = concat(@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1); set @d1 = concat(@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1,@d1); -let $count=500; +--disable_query_log +let count=500; while ($count) { INSERT INTO t2 VALUES (NULL); @@ -63,10 +48,7 @@ while ($count) } --enable_query_log - ---disable_query_log -select "---Test1 check table load --" as ""; ---enable_query_log +--echo ---Test 1 check table load -- # Lets Check the tables on the Master SELECT COUNT(*) from t1; @@ -95,34 +77,26 @@ insert into t1 values ("Alas"); flush logs; # delimiters are for easier debugging in future ---disable_query_log -select "--- Test 1 Dump binlog to file --" as ""; ---enable_query_log +--echo --- Test 1 Dump binlog to file -- # # Prepare local temporary file to recreate what we have currently. -let $MYSQLD_DATADIR= `select @@datadir;`; +let MYSQLD_DATADIR= `select @@datadir;`; --exec $MYSQL_BINLOG $MYSQLD_DATADIR/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/master.sql --exec $MYSQL_BINLOG $MYSQLD_DATADIR/master-bin.000002 >> $MYSQLTEST_VARDIR/tmp/master.sql # Now that we have our file, lets get rid of the current database. # Cleanup the master and the slave and try to recreate. ---disable_query_log -select "--- Test 1 delete tables, clean master and slave --" as ""; ---enable_query_log +--echo --- Test 1 delete tables, clean master and slave -- DROP TABLE t1; DROP TABLE t2; DROP TABLE t3; sync_slave_with_master; -#we expect STOP SLAVE to produce a warning as the slave is stopped -#(the server was started with skip-slave-start) ---disable_warnings stop slave; --source include/wait_for_slave_to_stop.inc ---enable_warnings connection master; reset master; connection slave; @@ -132,15 +106,11 @@ start slave; connection master; # We should be clean at this point, now we will run in the file from above. ---disable_query_log -select "--- Test 1 Load from Dump binlog file --" as ""; ---enable_query_log +--echo --- Test 1 Load from Dump binlog file -- --exec $MYSQL -e "source $MYSQLTEST_VARDIR/tmp/master.sql" ---disable_query_log -select "--- Test 1 Check Load Results --" as ""; ---enable_query_log +--echo --- Test 1 Check Load Results -- # Lets Check the tables on the Master SELECT COUNT(*) from t1; @@ -168,28 +138,20 @@ remove_file $MYSQLTEST_VARDIR/tmp/master.sql; # this test for start-position option # By setting this position to 416, we should only get the create of t3 ---disable_query_log -select "--- Test 2 position test --" as ""; ---enable_query_log -let $MYSQLD_DATADIR= `select @@datadir;`; +--echo --- Test 2 position test -- --exec $MYSQL_BINLOG --short-form --local-load=$MYSQLTEST_VARDIR/tmp/ --start-position=$position --stop-position=$stop_position $MYSQLD_DATADIR/master-bin.000001 # These are tests for remote binlog. # They should return the same as previous test. ---disable_query_log -select "--- Test 3 First Remote test --" as ""; ---enable_query_log +--echo --- Test 3 First Remote test -- # This is broken now --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR --exec $MYSQL_BINLOG --short-form --local-load=$MYSQLTEST_VARDIR/tmp/ --stop-position=$stop_position --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000001 ---disable_query_log -select "--- Test 4 Second Remote test --" as ""; ---enable_query_log +--echo --- Test 4 Second Remote test -- --exec $MYSQL_BINLOG --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000001 > $MYSQLTEST_VARDIR/tmp/remote.sql - --exec $MYSQL_BINLOG --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000002 >> $MYSQLTEST_VARDIR/tmp/remote.sql # Now that we have our file, lets get rid of the current database. @@ -201,13 +163,8 @@ DROP TABLE t3; sync_slave_with_master; -#we expect STOP SLAVE to produce a warning as the slave is stopped -#(the server was started with skip-slave-start) - ---disable_warnings stop slave; --source include/wait_for_slave_to_stop.inc ---enable_warnings connection master; reset master; connection slave; @@ -251,40 +208,26 @@ connection master; # transactions. /Matz # LOAD DATA ---disable_query_log -select "--- Test 5 LOAD DATA --" as ""; ---enable_query_log +--echo --- Test 5 LOAD DATA -- --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR --exec $MYSQL_BINLOG --short-form --local-load=$MYSQLTEST_VARDIR/tmp/ --stop-position=$binlog_start_pos --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000002 # Bug#7853 (mysqlbinlog does not accept input from stdin) ---disable_query_log -select "--- Test 6 reading stdin --" as ""; ---enable_query_log -let $MYSQLD_DATADIR= `select @@datadir;`; +--echo --- Test 6 reading stdin -- --replace_result $MYSQL_TEST_DIR MYSQL_TEST_DIR --exec $MYSQL_BINLOG --short-form --stop-position=$stop_position1 - < $MYSQLD_DATADIR/master-bin.000001 ---disable_query_log -select "--- Test 7 reading stdin w/position --" as ""; ---enable_query_log +--echo --- Test 7 reading stdin w/position -- --replace_result $MYSQL_TEST_DIR MYSQL_TEST_DIR --exec $MYSQL_BINLOG --short-form --start-position=$position --stop-position=$stop_position - < $MYSQLD_DATADIR/master-bin.000001 # Bug#16217 (mysql client did not know how not switch its internal charset) ---disable_query_log -select "--- Test 8 switch internal charset --" as ""; ---enable_query_log +--echo --- Test 8 switch internal charset -- sync_slave_with_master; -#we expect STOP SLAVE to produce a warning as the slave is stopped -#(the server was started with skip-slave-start) - ---disable_warnings stop slave; --source include/wait_for_slave_to_stop.inc ---enable_warnings connection master; reset master; connection slave; @@ -297,7 +240,6 @@ create table t4 (f text character set utf8); create table t5 (f text character set cp932); --exec $MYSQL --default-character-set=utf8 test -e "insert into t4 values(_utf8'ソ')" --exec $MYSQL --default-character-set=cp932 test -e "insert into t5 values(_cp932'\');" -let $MYSQLD_DATADIR= `select @@datadir;`; flush logs; rename table t4 to t04, t5 to t05; --exec $MYSQL_BINLOG $MYSQLD_DATADIR/master-bin.000001 | $MYSQL --default-character-set=utf8 @@ -314,42 +256,30 @@ select HEX(f) from t4; select HEX(f) from t05; select HEX(f) from t5; ---disable_query_log -select "--- Test cleanup --" as ""; ---enable_query_log +--echo --- Test cleanup -- # clean up connection master; sync_slave_with_master; connection master; +DROP TABLE t1, t2, t3, t04, t05, t4, t5; # BUG#17654 also test mysqlbinlog to ensure it can read the binlog from a remote server # and ensure that the results are the same as if read from a file (the same file). ---disable_warnings -DROP TABLE IF EXISTS t1; ---enable_warnings - CREATE TABLE t1 (a INT NOT NULL KEY, b INT); - INSERT INTO t1 VALUES(1,1); - SELECT * FROM t1; - -let $MYSQLD_DATADIR= `select @@datadir;`; - FLUSH LOGS; --exec $MYSQL_BINLOG --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000001 > $MYSQLTEST_VARDIR/tmp/remote.sql --exec $MYSQL_BINLOG $MYSQLD_DATADIR/master-bin.000001 > $MYSQLTEST_VARDIR/tmp/local.sql --diff_files $MYSQLTEST_VARDIR/tmp/local.sql $MYSQLTEST_VARDIR/tmp/remote.sql - --remove_file $MYSQLTEST_VARDIR/tmp/remote.sql - --remove_file $MYSQLTEST_VARDIR/tmp/local.sql +DROP TABLE t1; -DROP TABLE IF EXISTS t1, t2, t3, t04, t05, t4, t5; sync_slave_with_master; # End of 4.1 tests -- cgit v1.2.1 From 2f6fede8d5f7e98319b4b7b557bd565fdb42fac3 Mon Sep 17 00:00:00 2001 From: Alexander Barkov Date: Mon, 19 Dec 2016 14:28:08 +0400 Subject: MDEV-10524 Assertion `arg1_int >= 0' failed in Item_func_additive_op::result_precision() This change is a backport from 10.0 to 5.5 for: 1. The full patch for: MDEV-4841 Wrong character set of ADDTIME() and DATE_ADD() 9adb6e991ec87b65d04929f115d9d0c899e4ab19 2. A small fragment of: MDEV-5298 Illegal mix of collations on timestamp 03f6778d61a74bdd7d09103a16473a2a5624cf66 which overrides Item_temporal_hybrid_func::cmp_type(), and adds a new line into cache_temporal_4265.result. --- mysql-test/include/ctype_numconv.inc | 26 +++++++++++++++++- mysql-test/r/cache_temporal_4265.result | 1 + mysql-test/r/ctype_binary.result | 42 +++++++++++++++++++++++++++-- mysql-test/r/ctype_cp1251.result | 46 ++++++++++++++++++++++++++++--- mysql-test/r/ctype_latin1.result | 42 +++++++++++++++++++++++++++-- mysql-test/r/ctype_ucs.result | 48 +++++++++++++++++++++++++++++---- mysql-test/r/ctype_utf8.result | 46 ++++++++++++++++++++++++++++--- mysql-test/r/func_time.result | 6 +++++ mysql-test/t/func_time.test | 5 ++++ 9 files changed, 244 insertions(+), 18 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/include/ctype_numconv.inc b/mysql-test/include/ctype_numconv.inc index 9b21e7f38c1..47b52be9cec 100644 --- a/mysql-test/include/ctype_numconv.inc +++ b/mysql-test/include/ctype_numconv.inc @@ -1743,6 +1743,11 @@ DROP TABLE t1; --echo # Bug #31384 DATE_ADD() and DATE_SUB() return binary data --echo # SELECT @@collation_connection, @@character_set_results; +SELECT + CHARSET(DATE_SUB('2007-08-03', INTERVAL 1 MINUTE)) AS field_str1, + CHARSET(DATE_SUB('2007-08-03 17:33:00', INTERVAL 1 MINUTE)) AS field_str2, + CHARSET(DATE_SUB(DATE('2007-08-03'), INTERVAL 1 DAY)) AS field_date, + CHARSET(DATE_SUB(CAST('2007-08-03 17:33:00' AS DATETIME), INTERVAL 1 MINUTE)) AS field_datetime; CREATE TABLE t1 AS SELECT DATE_SUB('2007-08-03', INTERVAL 1 MINUTE) AS field_str1, @@ -1766,7 +1771,26 @@ SELECT HEX(DATE_SUB('2007-08-03 17:33:00', INTERVAL 1 MINUTE)) AS field1_str2, HEX(DATE_SUB(DATE('2007-08-03'), INTERVAL 1 DAY)) AS field_date, HEX(DATE_SUB(CAST('2007-08-03 17:33:00' AS DATETIME), INTERVAL 1 MINUTE)) AS field_datetime; - + +--echo # +--echo # MDEV-4841 Wrong character set of ADDTIME() and DATE_ADD() +--echo # +SELECT @@collation_connection, @@character_set_results; +SELECT + CHARSET(ADDTIME(_latin1'10:01:01',_latin1'10:00:00')) AS addtime1, + CHARSET(ADDTIME('10:01:01','10:00:00')) AS addtime2, + CHARSET(DATE_ADD(_latin1'2001-01-01 10:01:01',interval 10 second)) AS date_add1, + CHARSET(DATE_ADD('2001-01-01 10:01:01',interval 10 second)) AS date_add2; +CREATE TABLE t1 AS +SELECT + ADDTIME(_latin1'10:01:01',_latin1'10:00:00') AS addtime1, + ADDTIME('10:01:01','10:00:00') AS addtime2, + DATE_ADD(_latin1'2001-01-01 10:01:01',interval 10 second) AS date_add1, + DATE_ADD('2001-01-01 10:01:01',interval 10 second) AS date_add2; +SHOW CREATE TABLE t1; +SELECT * FROM t1; +DROP TABLE t1; + --echo # --echo # Bug#11926811 / Bug#60625 Illegal mix of collations --echo # diff --git a/mysql-test/r/cache_temporal_4265.result b/mysql-test/r/cache_temporal_4265.result index 7f215de43fb..980bb957e19 100644 --- a/mysql-test/r/cache_temporal_4265.result +++ b/mysql-test/r/cache_temporal_4265.result @@ -7,6 +7,7 @@ a 2002-03-04 Warnings: Note 1003 2000-01-01 +Note 1003 2000-01-06 set debug_dbug=''; drop table t1; create table t1 (id int not null, ut timestamp(6) not null); diff --git a/mysql-test/r/ctype_binary.result b/mysql-test/r/ctype_binary.result index e7bf1238210..3fc440b8354 100644 --- a/mysql-test/r/ctype_binary.result +++ b/mysql-test/r/ctype_binary.result @@ -2777,6 +2777,13 @@ DROP TABLE t1; SELECT @@collation_connection, @@character_set_results; @@collation_connection @@character_set_results binary binary +SELECT +CHARSET(DATE_SUB('2007-08-03', INTERVAL 1 MINUTE)) AS field_str1, +CHARSET(DATE_SUB('2007-08-03 17:33:00', INTERVAL 1 MINUTE)) AS field_str2, +CHARSET(DATE_SUB(DATE('2007-08-03'), INTERVAL 1 DAY)) AS field_date, +CHARSET(DATE_SUB(CAST('2007-08-03 17:33:00' AS DATETIME), INTERVAL 1 MINUTE)) AS field_datetime; +field_str1 field_str2 field_date field_datetime +binary binary binary binary CREATE TABLE t1 AS SELECT DATE_SUB('2007-08-03', INTERVAL 1 MINUTE) AS field_str1, @@ -2786,8 +2793,8 @@ DATE_SUB(CAST('2007-08-03 17:33:00' AS DATETIME), INTERVAL 1 MINUTE) AS field_da SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( - `field_str1` varchar(19) DEFAULT NULL, - `field1_str2` varchar(19) DEFAULT NULL, + `field_str1` varbinary(19) DEFAULT NULL, + `field1_str2` varbinary(19) DEFAULT NULL, `field_date` date DEFAULT NULL, `field_datetime` datetime DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 @@ -2812,6 +2819,37 @@ HEX(DATE_SUB(CAST('2007-08-03 17:33:00' AS DATETIME), INTERVAL 1 MINUTE)) AS fie field_str1 field1_str2 field_date field_datetime 323030372D30382D30322032333A35393A3030 323030372D30382D30332031373A33323A3030 323030372D30382D3032 323030372D30382D30332031373A33323A3030 # +# MDEV-4841 Wrong character set of ADDTIME() and DATE_ADD() +# +SELECT @@collation_connection, @@character_set_results; +@@collation_connection @@character_set_results +binary binary +SELECT +CHARSET(ADDTIME(_latin1'10:01:01',_latin1'10:00:00')) AS addtime1, +CHARSET(ADDTIME('10:01:01','10:00:00')) AS addtime2, +CHARSET(DATE_ADD(_latin1'2001-01-01 10:01:01',interval 10 second)) AS date_add1, +CHARSET(DATE_ADD('2001-01-01 10:01:01',interval 10 second)) AS date_add2; +addtime1 addtime2 date_add1 date_add2 +binary binary binary binary +CREATE TABLE t1 AS +SELECT +ADDTIME(_latin1'10:01:01',_latin1'10:00:00') AS addtime1, +ADDTIME('10:01:01','10:00:00') AS addtime2, +DATE_ADD(_latin1'2001-01-01 10:01:01',interval 10 second) AS date_add1, +DATE_ADD('2001-01-01 10:01:01',interval 10 second) AS date_add2; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `addtime1` varbinary(26) DEFAULT NULL, + `addtime2` varbinary(26) DEFAULT NULL, + `date_add1` varbinary(19) DEFAULT NULL, + `date_add2` varbinary(19) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +SELECT * FROM t1; +addtime1 addtime2 date_add1 date_add2 +20:01:01 20:01:01 2001-01-01 10:01:11 2001-01-01 10:01:11 +DROP TABLE t1; +# # Bug#11926811 / Bug#60625 Illegal mix of collations # SELECT @@collation_connection; diff --git a/mysql-test/r/ctype_cp1251.result b/mysql-test/r/ctype_cp1251.result index 2a7d4ed1fb4..9d5dea2f286 100644 --- a/mysql-test/r/ctype_cp1251.result +++ b/mysql-test/r/ctype_cp1251.result @@ -3169,6 +3169,13 @@ DROP TABLE t1; SELECT @@collation_connection, @@character_set_results; @@collation_connection @@character_set_results cp1251_general_ci cp1251 +SELECT +CHARSET(DATE_SUB('2007-08-03', INTERVAL 1 MINUTE)) AS field_str1, +CHARSET(DATE_SUB('2007-08-03 17:33:00', INTERVAL 1 MINUTE)) AS field_str2, +CHARSET(DATE_SUB(DATE('2007-08-03'), INTERVAL 1 DAY)) AS field_date, +CHARSET(DATE_SUB(CAST('2007-08-03 17:33:00' AS DATETIME), INTERVAL 1 MINUTE)) AS field_datetime; +field_str1 field_str2 field_date field_datetime +cp1251 cp1251 binary binary CREATE TABLE t1 AS SELECT DATE_SUB('2007-08-03', INTERVAL 1 MINUTE) AS field_str1, @@ -3178,8 +3185,8 @@ DATE_SUB(CAST('2007-08-03 17:33:00' AS DATETIME), INTERVAL 1 MINUTE) AS field_da SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( - `field_str1` varchar(19) DEFAULT NULL, - `field1_str2` varchar(19) DEFAULT NULL, + `field_str1` varchar(19) CHARACTER SET cp1251 DEFAULT NULL, + `field1_str2` varchar(19) CHARACTER SET cp1251 DEFAULT NULL, `field_date` date DEFAULT NULL, `field_datetime` datetime DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 @@ -3190,8 +3197,8 @@ DATE_SUB('2007-08-03 17:33:00', INTERVAL 1 MINUTE) AS field1_str2, DATE_SUB(DATE('2007-08-03'), INTERVAL 1 DAY) AS field_date, DATE_SUB(CAST('2007-08-03 17:33:00' AS DATETIME), INTERVAL 1 MINUTE) AS field_datetime; Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr -def field_str1 254 19 10 Y 128 0 63 -def field1_str2 254 19 19 Y 128 0 63 +def field_str1 254 19 10 Y 0 0 51 +def field1_str2 254 19 19 Y 0 0 51 def field_date 10 10 10 Y 128 0 63 def field_datetime 12 19 19 Y 128 0 63 field_str1 field1_str2 field_date field_datetime @@ -3204,6 +3211,37 @@ HEX(DATE_SUB(CAST('2007-08-03 17:33:00' AS DATETIME), INTERVAL 1 MINUTE)) AS fie field_str1 field1_str2 field_date field_datetime 323030372D30382D30322032333A35393A3030 323030372D30382D30332031373A33323A3030 323030372D30382D3032 323030372D30382D30332031373A33323A3030 # +# MDEV-4841 Wrong character set of ADDTIME() and DATE_ADD() +# +SELECT @@collation_connection, @@character_set_results; +@@collation_connection @@character_set_results +cp1251_general_ci cp1251 +SELECT +CHARSET(ADDTIME(_latin1'10:01:01',_latin1'10:00:00')) AS addtime1, +CHARSET(ADDTIME('10:01:01','10:00:00')) AS addtime2, +CHARSET(DATE_ADD(_latin1'2001-01-01 10:01:01',interval 10 second)) AS date_add1, +CHARSET(DATE_ADD('2001-01-01 10:01:01',interval 10 second)) AS date_add2; +addtime1 addtime2 date_add1 date_add2 +cp1251 cp1251 cp1251 cp1251 +CREATE TABLE t1 AS +SELECT +ADDTIME(_latin1'10:01:01',_latin1'10:00:00') AS addtime1, +ADDTIME('10:01:01','10:00:00') AS addtime2, +DATE_ADD(_latin1'2001-01-01 10:01:01',interval 10 second) AS date_add1, +DATE_ADD('2001-01-01 10:01:01',interval 10 second) AS date_add2; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `addtime1` varchar(26) CHARACTER SET cp1251 DEFAULT NULL, + `addtime2` varchar(26) CHARACTER SET cp1251 DEFAULT NULL, + `date_add1` varchar(19) CHARACTER SET cp1251 DEFAULT NULL, + `date_add2` varchar(19) CHARACTER SET cp1251 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +SELECT * FROM t1; +addtime1 addtime2 date_add1 date_add2 +20:01:01 20:01:01 2001-01-01 10:01:11 2001-01-01 10:01:11 +DROP TABLE t1; +# # Bug#11926811 / Bug#60625 Illegal mix of collations # SELECT @@collation_connection; diff --git a/mysql-test/r/ctype_latin1.result b/mysql-test/r/ctype_latin1.result index dc96495b1ac..db9d03a8656 100644 --- a/mysql-test/r/ctype_latin1.result +++ b/mysql-test/r/ctype_latin1.result @@ -3351,6 +3351,13 @@ DROP TABLE t1; SELECT @@collation_connection, @@character_set_results; @@collation_connection @@character_set_results latin1_swedish_ci latin1 +SELECT +CHARSET(DATE_SUB('2007-08-03', INTERVAL 1 MINUTE)) AS field_str1, +CHARSET(DATE_SUB('2007-08-03 17:33:00', INTERVAL 1 MINUTE)) AS field_str2, +CHARSET(DATE_SUB(DATE('2007-08-03'), INTERVAL 1 DAY)) AS field_date, +CHARSET(DATE_SUB(CAST('2007-08-03 17:33:00' AS DATETIME), INTERVAL 1 MINUTE)) AS field_datetime; +field_str1 field_str2 field_date field_datetime +latin1 latin1 binary binary CREATE TABLE t1 AS SELECT DATE_SUB('2007-08-03', INTERVAL 1 MINUTE) AS field_str1, @@ -3372,8 +3379,8 @@ DATE_SUB('2007-08-03 17:33:00', INTERVAL 1 MINUTE) AS field1_str2, DATE_SUB(DATE('2007-08-03'), INTERVAL 1 DAY) AS field_date, DATE_SUB(CAST('2007-08-03 17:33:00' AS DATETIME), INTERVAL 1 MINUTE) AS field_datetime; Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr -def field_str1 254 19 10 Y 128 0 63 -def field1_str2 254 19 19 Y 128 0 63 +def field_str1 254 19 10 Y 0 0 8 +def field1_str2 254 19 19 Y 0 0 8 def field_date 10 10 10 Y 128 0 63 def field_datetime 12 19 19 Y 128 0 63 field_str1 field1_str2 field_date field_datetime @@ -3386,6 +3393,37 @@ HEX(DATE_SUB(CAST('2007-08-03 17:33:00' AS DATETIME), INTERVAL 1 MINUTE)) AS fie field_str1 field1_str2 field_date field_datetime 323030372D30382D30322032333A35393A3030 323030372D30382D30332031373A33323A3030 323030372D30382D3032 323030372D30382D30332031373A33323A3030 # +# MDEV-4841 Wrong character set of ADDTIME() and DATE_ADD() +# +SELECT @@collation_connection, @@character_set_results; +@@collation_connection @@character_set_results +latin1_swedish_ci latin1 +SELECT +CHARSET(ADDTIME(_latin1'10:01:01',_latin1'10:00:00')) AS addtime1, +CHARSET(ADDTIME('10:01:01','10:00:00')) AS addtime2, +CHARSET(DATE_ADD(_latin1'2001-01-01 10:01:01',interval 10 second)) AS date_add1, +CHARSET(DATE_ADD('2001-01-01 10:01:01',interval 10 second)) AS date_add2; +addtime1 addtime2 date_add1 date_add2 +latin1 latin1 latin1 latin1 +CREATE TABLE t1 AS +SELECT +ADDTIME(_latin1'10:01:01',_latin1'10:00:00') AS addtime1, +ADDTIME('10:01:01','10:00:00') AS addtime2, +DATE_ADD(_latin1'2001-01-01 10:01:01',interval 10 second) AS date_add1, +DATE_ADD('2001-01-01 10:01:01',interval 10 second) AS date_add2; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `addtime1` varchar(26) DEFAULT NULL, + `addtime2` varchar(26) DEFAULT NULL, + `date_add1` varchar(19) DEFAULT NULL, + `date_add2` varchar(19) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +SELECT * FROM t1; +addtime1 addtime2 date_add1 date_add2 +20:01:01 20:01:01 2001-01-01 10:01:11 2001-01-01 10:01:11 +DROP TABLE t1; +# # Bug#11926811 / Bug#60625 Illegal mix of collations # SELECT @@collation_connection; diff --git a/mysql-test/r/ctype_ucs.result b/mysql-test/r/ctype_ucs.result index 96e0970e4a6..c38a03f76a8 100644 --- a/mysql-test/r/ctype_ucs.result +++ b/mysql-test/r/ctype_ucs.result @@ -4167,6 +4167,13 @@ DROP TABLE t1; SELECT @@collation_connection, @@character_set_results; @@collation_connection @@character_set_results ucs2_general_ci latin1 +SELECT +CHARSET(DATE_SUB('2007-08-03', INTERVAL 1 MINUTE)) AS field_str1, +CHARSET(DATE_SUB('2007-08-03 17:33:00', INTERVAL 1 MINUTE)) AS field_str2, +CHARSET(DATE_SUB(DATE('2007-08-03'), INTERVAL 1 DAY)) AS field_date, +CHARSET(DATE_SUB(CAST('2007-08-03 17:33:00' AS DATETIME), INTERVAL 1 MINUTE)) AS field_datetime; +field_str1 field_str2 field_date field_datetime +ucs2 ucs2 binary binary CREATE TABLE t1 AS SELECT DATE_SUB('2007-08-03', INTERVAL 1 MINUTE) AS field_str1, @@ -4176,8 +4183,8 @@ DATE_SUB(CAST('2007-08-03 17:33:00' AS DATETIME), INTERVAL 1 MINUTE) AS field_da SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( - `field_str1` varchar(19) DEFAULT NULL, - `field1_str2` varchar(19) DEFAULT NULL, + `field_str1` varchar(19) CHARACTER SET ucs2 DEFAULT NULL, + `field1_str2` varchar(19) CHARACTER SET ucs2 DEFAULT NULL, `field_date` date DEFAULT NULL, `field_datetime` datetime DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 @@ -4188,8 +4195,8 @@ DATE_SUB('2007-08-03 17:33:00', INTERVAL 1 MINUTE) AS field1_str2, DATE_SUB(DATE('2007-08-03'), INTERVAL 1 DAY) AS field_date, DATE_SUB(CAST('2007-08-03 17:33:00' AS DATETIME), INTERVAL 1 MINUTE) AS field_datetime; Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr -def field_str1 254 19 10 Y 128 0 63 -def field1_str2 254 19 19 Y 128 0 63 +def field_str1 254 19 10 Y 0 0 8 +def field1_str2 254 19 19 Y 0 0 8 def field_date 10 10 10 Y 128 0 63 def field_datetime 12 19 19 Y 128 0 63 field_str1 field1_str2 field_date field_datetime @@ -4200,7 +4207,38 @@ HEX(DATE_SUB('2007-08-03 17:33:00', INTERVAL 1 MINUTE)) AS field1_str2, HEX(DATE_SUB(DATE('2007-08-03'), INTERVAL 1 DAY)) AS field_date, HEX(DATE_SUB(CAST('2007-08-03 17:33:00' AS DATETIME), INTERVAL 1 MINUTE)) AS field_datetime; field_str1 field1_str2 field_date field_datetime -323030372D30382D30322032333A35393A3030 323030372D30382D30332031373A33323A3030 323030372D30382D3032 323030372D30382D30332031373A33323A3030 +0032003000300037002D00300038002D00300032002000320033003A00350039003A00300030 0032003000300037002D00300038002D00300033002000310037003A00330032003A00300030 323030372D30382D3032 323030372D30382D30332031373A33323A3030 +# +# MDEV-4841 Wrong character set of ADDTIME() and DATE_ADD() +# +SELECT @@collation_connection, @@character_set_results; +@@collation_connection @@character_set_results +ucs2_general_ci latin1 +SELECT +CHARSET(ADDTIME(_latin1'10:01:01',_latin1'10:00:00')) AS addtime1, +CHARSET(ADDTIME('10:01:01','10:00:00')) AS addtime2, +CHARSET(DATE_ADD(_latin1'2001-01-01 10:01:01',interval 10 second)) AS date_add1, +CHARSET(DATE_ADD('2001-01-01 10:01:01',interval 10 second)) AS date_add2; +addtime1 addtime2 date_add1 date_add2 +ucs2 ucs2 ucs2 ucs2 +CREATE TABLE t1 AS +SELECT +ADDTIME(_latin1'10:01:01',_latin1'10:00:00') AS addtime1, +ADDTIME('10:01:01','10:00:00') AS addtime2, +DATE_ADD(_latin1'2001-01-01 10:01:01',interval 10 second) AS date_add1, +DATE_ADD('2001-01-01 10:01:01',interval 10 second) AS date_add2; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `addtime1` varchar(26) CHARACTER SET ucs2 DEFAULT NULL, + `addtime2` varchar(26) CHARACTER SET ucs2 DEFAULT NULL, + `date_add1` varchar(19) CHARACTER SET ucs2 DEFAULT NULL, + `date_add2` varchar(19) CHARACTER SET ucs2 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +SELECT * FROM t1; +addtime1 addtime2 date_add1 date_add2 +20:01:01 20:01:01 2001-01-01 10:01:11 2001-01-01 10:01:11 +DROP TABLE t1; # # Bug#11926811 / Bug#60625 Illegal mix of collations # diff --git a/mysql-test/r/ctype_utf8.result b/mysql-test/r/ctype_utf8.result index 294c2cb2be1..74fed6a3162 100644 --- a/mysql-test/r/ctype_utf8.result +++ b/mysql-test/r/ctype_utf8.result @@ -4988,6 +4988,13 @@ DROP TABLE t1; SELECT @@collation_connection, @@character_set_results; @@collation_connection @@character_set_results utf8_general_ci utf8 +SELECT +CHARSET(DATE_SUB('2007-08-03', INTERVAL 1 MINUTE)) AS field_str1, +CHARSET(DATE_SUB('2007-08-03 17:33:00', INTERVAL 1 MINUTE)) AS field_str2, +CHARSET(DATE_SUB(DATE('2007-08-03'), INTERVAL 1 DAY)) AS field_date, +CHARSET(DATE_SUB(CAST('2007-08-03 17:33:00' AS DATETIME), INTERVAL 1 MINUTE)) AS field_datetime; +field_str1 field_str2 field_date field_datetime +utf8 utf8 binary binary CREATE TABLE t1 AS SELECT DATE_SUB('2007-08-03', INTERVAL 1 MINUTE) AS field_str1, @@ -4997,8 +5004,8 @@ DATE_SUB(CAST('2007-08-03 17:33:00' AS DATETIME), INTERVAL 1 MINUTE) AS field_da SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( - `field_str1` varchar(19) DEFAULT NULL, - `field1_str2` varchar(19) DEFAULT NULL, + `field_str1` varchar(19) CHARACTER SET utf8 DEFAULT NULL, + `field1_str2` varchar(19) CHARACTER SET utf8 DEFAULT NULL, `field_date` date DEFAULT NULL, `field_datetime` datetime DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 @@ -5009,8 +5016,8 @@ DATE_SUB('2007-08-03 17:33:00', INTERVAL 1 MINUTE) AS field1_str2, DATE_SUB(DATE('2007-08-03'), INTERVAL 1 DAY) AS field_date, DATE_SUB(CAST('2007-08-03 17:33:00' AS DATETIME), INTERVAL 1 MINUTE) AS field_datetime; Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr -def field_str1 254 19 10 Y 128 0 63 -def field1_str2 254 19 19 Y 128 0 63 +def field_str1 254 57 10 Y 0 0 33 +def field1_str2 254 57 19 Y 0 0 33 def field_date 10 10 10 Y 128 0 63 def field_datetime 12 19 19 Y 128 0 63 field_str1 field1_str2 field_date field_datetime @@ -5023,6 +5030,37 @@ HEX(DATE_SUB(CAST('2007-08-03 17:33:00' AS DATETIME), INTERVAL 1 MINUTE)) AS fie field_str1 field1_str2 field_date field_datetime 323030372D30382D30322032333A35393A3030 323030372D30382D30332031373A33323A3030 323030372D30382D3032 323030372D30382D30332031373A33323A3030 # +# MDEV-4841 Wrong character set of ADDTIME() and DATE_ADD() +# +SELECT @@collation_connection, @@character_set_results; +@@collation_connection @@character_set_results +utf8_general_ci utf8 +SELECT +CHARSET(ADDTIME(_latin1'10:01:01',_latin1'10:00:00')) AS addtime1, +CHARSET(ADDTIME('10:01:01','10:00:00')) AS addtime2, +CHARSET(DATE_ADD(_latin1'2001-01-01 10:01:01',interval 10 second)) AS date_add1, +CHARSET(DATE_ADD('2001-01-01 10:01:01',interval 10 second)) AS date_add2; +addtime1 addtime2 date_add1 date_add2 +utf8 utf8 utf8 utf8 +CREATE TABLE t1 AS +SELECT +ADDTIME(_latin1'10:01:01',_latin1'10:00:00') AS addtime1, +ADDTIME('10:01:01','10:00:00') AS addtime2, +DATE_ADD(_latin1'2001-01-01 10:01:01',interval 10 second) AS date_add1, +DATE_ADD('2001-01-01 10:01:01',interval 10 second) AS date_add2; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `addtime1` varchar(26) CHARACTER SET utf8 DEFAULT NULL, + `addtime2` varchar(26) CHARACTER SET utf8 DEFAULT NULL, + `date_add1` varchar(19) CHARACTER SET utf8 DEFAULT NULL, + `date_add2` varchar(19) CHARACTER SET utf8 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +SELECT * FROM t1; +addtime1 addtime2 date_add1 date_add2 +20:01:01 20:01:01 2001-01-01 10:01:11 2001-01-01 10:01:11 +DROP TABLE t1; +# # Bug#11926811 / Bug#60625 Illegal mix of collations # SELECT @@collation_connection; diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result index ef5e3487a99..68b1e0f04ad 100644 --- a/mysql-test/r/func_time.result +++ b/mysql-test/r/func_time.result @@ -2620,3 +2620,9 @@ id date1 date2 DATE_ADD(a.date1,INTERVAL -10 DAY) TO_DAYS(a.date1)-10 17 NULL NULL NULL NULL 18 2010-10-13 2010-10-03 2010-10-03 734413 DROP TABLE t1; +# +# MDEV-10524 Assertion `arg1_int >= 0' failed in Item_func_additive_op::result_precision() +# +SELECT 1 MOD ADDTIME( '13:58:57', '00:00:01' ) + 2; +1 MOD ADDTIME( '13:58:57', '00:00:01' ) + 2 +3 diff --git a/mysql-test/t/func_time.test b/mysql-test/t/func_time.test index 45214aed6e7..92e1c38cec2 100644 --- a/mysql-test/t/func_time.test +++ b/mysql-test/t/func_time.test @@ -1597,3 +1597,8 @@ 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 # MDEV-10524 Assertion `arg1_int >= 0' failed in Item_func_additive_op::result_precision() +--echo # +SELECT 1 MOD ADDTIME( '13:58:57', '00:00:01' ) + 2; -- cgit v1.2.1 From 19896d4b3ab459d135aee6ee67cb92bce92f9b87 Mon Sep 17 00:00:00 2001 From: Alexey Botchkov Date: Mon, 19 Dec 2016 16:09:20 +0400 Subject: MDEV-10274 Bundling insert with create statement for table with unsigned Decimal primary key issues warning 1194. Flags are important for key_length calculations, so them should be set before it, not after. --- mysql-test/r/create.result | 7 +++++++ mysql-test/t/create.test | 9 +++++++++ 2 files changed, 16 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result index 0164aa4b610..829b54dea49 100644 --- a/mysql-test/r/create.result +++ b/mysql-test/r/create.result @@ -2498,4 +2498,11 @@ end| create table t1 as select f1(); ERROR 42S02: Table 'test.t1' doesn't exist drop function f1; +# +# MDEV-10274 Bundling insert with create statement +# for table with unsigned Decimal primary key issues warning 1194 +# +create table t1(ID decimal(2,1) unsigned NOT NULL, PRIMARY KEY (ID))engine=memory +select 2.1 ID; +drop table t1; End of 5.5 tests diff --git a/mysql-test/t/create.test b/mysql-test/t/create.test index 5c90c3e17a4..1e77dac9bc9 100644 --- a/mysql-test/t/create.test +++ b/mysql-test/t/create.test @@ -2081,4 +2081,13 @@ DELIMITER ;| create table t1 as select f1(); drop function f1; +--echo # +--echo # MDEV-10274 Bundling insert with create statement +--echo # for table with unsigned Decimal primary key issues warning 1194 +--echo # + +create table t1(ID decimal(2,1) unsigned NOT NULL, PRIMARY KEY (ID))engine=memory + select 2.1 ID; +drop table t1; + --echo End of 5.5 tests -- cgit v1.2.1 From 268bb69beaec027b9f713d13316aa78c5c292817 Mon Sep 17 00:00:00 2001 From: Sergei Petrunia Date: Fri, 16 Dec 2016 17:08:31 +0300 Subject: MDEV-7691: Assertion `outer_context || !*from_field || *from_field == not_found_field' ... The bug occurred when a subquery - has a reference to outside, to grand-parent query or further up - is converted to a semi-join (i.e. merged into its parent). Then the reference to outside had form Item_ref(Item_field(...)). - Conversion to semi-join would call item->fix_after_pullout() for the outside reference. - Item_ref::fix_after_pullout would call Item_field->fix_after_pullout - The Item_field would construct a new Name_resolution_context object This process ignored the fact that the Item_field does not belong to any of the subselects being flattened. The result was crash in the next call to Item_field::fix_fields(), where we would try to use an invalid Name_resolution_context object. Fixed by not creating Name_resolution_context object if the Item_field's context does not belong to the subselect(s) that were flattened. --- mysql-test/r/subselect4.result | 39 +++++++++++++++++++++++++++++++++++++++ mysql-test/t/subselect4.test | 41 +++++++++++++++++++++++++++++++++++++++++ 2 files changed, 80 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result index c7f22cf05e5..89fb0902f53 100644 --- a/mysql-test/r/subselect4.result +++ b/mysql-test/r/subselect4.result @@ -2410,5 +2410,44 @@ SELECT x FROM t1 WHERE id > (SELECT MAX(id) - 1000 FROM t1) ORDER BY x LIMIT 1; x 0 drop table t1; +# +# MDEV-7691: Assertion `outer_context || !*from_field || *from_field == not_found_field' ... +# +set optimizer_switch=default; +CREATE TABLE t1 (a INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (4),(6); +CREATE TABLE t2 (b INT) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1),(8); +PREPARE stmt FROM " +SELECT * FROM t2 +HAVING 0 IN ( + SELECT a FROM t1 + WHERE a IN ( + SELECT a FROM t1 + WHERE b = a + ) +) +"; +EXECUTE stmt; +b +EXECUTE stmt; +b +# Alternative test case, without HAVING +CREATE TABLE t3 (i INT) ENGINE=MyISAM; +INSERT INTO t3 VALUES (4),(6); +PREPARE stmt FROM " +SELECT * FROM t3 AS t10 +WHERE EXISTS ( + SELECT * FROM t3 AS t20 WHERE t10.i IN ( + SELECT i FROM t3 + ) +)"; +EXECUTE stmt; +i +6 +EXECUTE stmt; +i +6 +drop table t1, t2, t3; SET optimizer_switch= @@global.optimizer_switch; set @@tmp_table_size= @@global.tmp_table_size; diff --git a/mysql-test/t/subselect4.test b/mysql-test/t/subselect4.test index b179ead39d7..7a7dd7e492e 100644 --- a/mysql-test/t/subselect4.test +++ b/mysql-test/t/subselect4.test @@ -1959,5 +1959,46 @@ SELECT x FROM t1 WHERE id > (SELECT MAX(id) - 1000 FROM t1) ORDER BY x LIMIT 1; drop table t1; +--echo # +--echo # MDEV-7691: Assertion `outer_context || !*from_field || *from_field == not_found_field' ... +--echo # +set optimizer_switch=default; +CREATE TABLE t1 (a INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (4),(6); + +CREATE TABLE t2 (b INT) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1),(8); + +PREPARE stmt FROM " +SELECT * FROM t2 +HAVING 0 IN ( + SELECT a FROM t1 + WHERE a IN ( + SELECT a FROM t1 + WHERE b = a + ) +) +"; + +EXECUTE stmt; +EXECUTE stmt; + +--echo # Alternative test case, without HAVING +CREATE TABLE t3 (i INT) ENGINE=MyISAM; +INSERT INTO t3 VALUES (4),(6); + +PREPARE stmt FROM " +SELECT * FROM t3 AS t10 +WHERE EXISTS ( + SELECT * FROM t3 AS t20 WHERE t10.i IN ( + SELECT i FROM t3 + ) +)"; + +EXECUTE stmt; +EXECUTE stmt; + +drop table t1, t2, t3; + SET optimizer_switch= @@global.optimizer_switch; set @@tmp_table_size= @@global.tmp_table_size; -- cgit v1.2.1 From f23b41b9b8a30e0e54a1ec7a8923057b0e57e0f5 Mon Sep 17 00:00:00 2001 From: Sergei Petrunia Date: Fri, 16 Dec 2016 17:16:02 +0300 Subject: MDEV-10148: Database crashes in the query to the View Fix st_select_lex::is_merged_child_of to work across merged views or derived tables. --- mysql-test/r/subselect2.result | 35 +++++++++++++++++++++++++++++++++++ mysql-test/t/subselect2.test | 37 +++++++++++++++++++++++++++++++++++++ 2 files changed, 72 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/subselect2.result b/mysql-test/r/subselect2.result index 72ca9d33354..b6dc940d9fb 100644 --- a/mysql-test/r/subselect2.result +++ b/mysql-test/r/subselect2.result @@ -348,4 +348,39 @@ where t1.a = t2.a and ( t1.a = ( select min(a) from t1 ) or 0 ); a a a FRA FRA FRA drop table t1,t2,t3; +# +# MDEV-10148: Database crashes in the query to the View +# +CREATE TABLE t1 ( +key_code INT(11) NOT NULL, +value_string VARCHAR(50) NULL DEFAULT NULL, +PRIMARY KEY (key_code) +) COLLATE='utf8_general_ci' ENGINE=InnoDB ; +CREATE TABLE t2 ( +key_code INT(11) NOT NULL, +target_date DATE NULL DEFAULT NULL, +PRIMARY KEY (key_code) +) COLLATE='utf8_general_ci' ENGINE=InnoDB ; +CREATE TABLE t3 ( +now_date DATE NOT NULL, +PRIMARY KEY (now_date) +) COLLATE='utf8_general_ci' ENGINE=InnoDB ; +CREATE VIEW v1 +AS +SELECT +B.key_code, +B.target_date +FROM +t2 B INNER JOIN t3 C ON +B.target_date = C.now_date +; +SET @s = 'SELECT A.* FROM t1 A WHERE A.key_code IN (SELECT key_code FROM v1)'; +PREPARE stmt FROM @s; +EXECUTE stmt; +key_code value_string +EXECUTE stmt; +key_code value_string +DEALLOCATE PREPARE stmt; +DROP VIEW v1; +DROP TABLE t1,t2,t3; set optimizer_switch=@subselect2_test_tmp; diff --git a/mysql-test/t/subselect2.test b/mysql-test/t/subselect2.test index b3c1322184d..f795cef648c 100644 --- a/mysql-test/t/subselect2.test +++ b/mysql-test/t/subselect2.test @@ -359,5 +359,42 @@ where t1.a = t2.a and ( t1.a = ( select min(a) from t1 ) or 0 ); drop table t1,t2,t3; +--echo # +--echo # MDEV-10148: Database crashes in the query to the View +--echo # +CREATE TABLE t1 ( + key_code INT(11) NOT NULL, + value_string VARCHAR(50) NULL DEFAULT NULL, + PRIMARY KEY (key_code) +) COLLATE='utf8_general_ci' ENGINE=InnoDB ; + +CREATE TABLE t2 ( + key_code INT(11) NOT NULL, + target_date DATE NULL DEFAULT NULL, + PRIMARY KEY (key_code) +) COLLATE='utf8_general_ci' ENGINE=InnoDB ; + +CREATE TABLE t3 ( + now_date DATE NOT NULL, + PRIMARY KEY (now_date) +) COLLATE='utf8_general_ci' ENGINE=InnoDB ; + +CREATE VIEW v1 +AS +SELECT + B.key_code, + B.target_date +FROM + t2 B INNER JOIN t3 C ON + B.target_date = C.now_date +; +SET @s = 'SELECT A.* FROM t1 A WHERE A.key_code IN (SELECT key_code FROM v1)'; +PREPARE stmt FROM @s; +EXECUTE stmt; #1st time -> success +EXECUTE stmt; #2nd time -> crash +DEALLOCATE PREPARE stmt; +DROP VIEW v1; +DROP TABLE t1,t2,t3; + set optimizer_switch=@subselect2_test_tmp; -- cgit v1.2.1 From aaff3d6c35f51dde60907f3c0fc4b2a40bc63c38 Mon Sep 17 00:00:00 2001 From: Oleksandr Byelkin Date: Tue, 20 Dec 2016 10:25:25 +0100 Subject: MDEV-10172: UNION query returns incorrect rows outside conditional evaluation count duplicate of UNION SELECT separately to awoid influence on lokal LIMIT clause. --- mysql-test/r/union.result | 33 ++++++++++++++++++++++++++++----- mysql-test/t/union.test | 22 +++++++++++++++++++++- 2 files changed, 49 insertions(+), 6 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result index 40f5a77e3d0..5a6cd8907e9 100644 --- a/mysql-test/r/union.result +++ b/mysql-test/r/union.result @@ -362,7 +362,7 @@ a 2 select found_rows(); found_rows() -6 +5 SELECT SQL_CALC_FOUND_ROWS * FROM t1 UNION SELECT * FROM t2 LIMIT 100; a 1 @@ -1169,12 +1169,9 @@ a b select * from ((select * from t1 limit 1) union (select * from t1 limit 1)) a; a b 1 a -2 b select * from ((select * from t1 limit 1) union (select * from t1 limit 1) union (select * from t1 limit 1)) a; a b 1 a -2 b -3 c select * from ((((select * from t1))) union (select * from t1) union (select * from t1)) a; a b 1 a @@ -1553,7 +1550,6 @@ NULL UNION RESULT ALL NULL NULL NULL NULL NULL NULL Using filesort Warnings: Note 1003 select NULL AS `a` from `test`.`t1` union select NULL AS `a` from `test`.`t1` order by `a` DROP TABLE t1; -End of 5.0 tests # # Bug#32858: Error: "Incorrect usage of UNION and INTO" does not take # subselects into account @@ -1659,6 +1655,14 @@ a 4 5 6 +(select a from t1 where false) UNION (select a from t1) limit 8; +a +10 +2 +3 +4 +5 +6 7 8 drop table t1; @@ -1955,3 +1959,22 @@ cccc bbbb dddd drop table t1; +# +# MDEV-10172: UNION query returns incorrect rows outside +# conditional evaluation +# +create table t1 (d datetime not null primary key); +insert into t1(d) values ('2016-06-01'),('2016-06-02'),('2016-06-03'),('2016-06-04'); +select * from +( +select * from t1 where d between '2016-06-02' and '2016-06-05' + union +(select * from t1 where d < '2016-06-05' order by d desc limit 1) +) onlyJun2toJun4 +order by d; +d +2016-06-02 00:00:00 +2016-06-03 00:00:00 +2016-06-04 00:00:00 +drop table t1; +End of 5.0 tests diff --git a/mysql-test/t/union.test b/mysql-test/t/union.test index 9204ddd22e5..f4dc6a5d449 100644 --- a/mysql-test/t/union.test +++ b/mysql-test/t/union.test @@ -1022,7 +1022,6 @@ ORDER BY a; DROP TABLE t1; ---echo End of 5.0 tests -- echo # -- echo # Bug#32858: Error: "Incorrect usage of UNION and INTO" does not take -- echo # subselects into account @@ -1126,6 +1125,8 @@ create table t1 (a int); insert into t1 values (10),(10),(10),(2),(3),(4),(5),(6),(7),(8),(9),(1),(10); --sorted_result select a from t1 where false UNION select a from t1 limit 8; +--sorted_result +(select a from t1 where false) UNION (select a from t1) limit 8; drop table t1; --echo # @@ -1350,3 +1351,22 @@ UNION ; drop table t1; + + +--echo # +--echo # MDEV-10172: UNION query returns incorrect rows outside +--echo # conditional evaluation +--echo # + +create table t1 (d datetime not null primary key); +insert into t1(d) values ('2016-06-01'),('2016-06-02'),('2016-06-03'),('2016-06-04'); +select * from +( + select * from t1 where d between '2016-06-02' and '2016-06-05' + union + (select * from t1 where d < '2016-06-05' order by d desc limit 1) +) onlyJun2toJun4 +order by d; +drop table t1; + +--echo End of 5.0 tests -- cgit v1.2.1 From ef82fd8ca33f0ecab06ddfa11b51dd5ea9019653 Mon Sep 17 00:00:00 2001 From: Sergey Vojtovich Date: Tue, 20 Dec 2016 17:42:08 +0400 Subject: MDEV-11353 - Identical logical conditions Added test case. --- mysql-test/r/information_schema_part.result | 8 ++++++++ mysql-test/t/information_schema_part.test | 7 +++++++ 2 files changed, 15 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/information_schema_part.result b/mysql-test/r/information_schema_part.result index b34183ebdee..77959de256e 100644 --- a/mysql-test/r/information_schema_part.result +++ b/mysql-test/r/information_schema_part.result @@ -151,3 +151,11 @@ select create_options from information_schema.tables where table_schema="test"; create_options partitioned drop table t1; +# +# MDEV-11353 - Identical logical conditions +# +CREATE TABLE t1(a INT) CHECKSUM=1 SELECT 1; +SELECT CHECKSUM FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; +CHECKSUM +3036305396 +DROP TABLE t1; diff --git a/mysql-test/t/information_schema_part.test b/mysql-test/t/information_schema_part.test index f1415d12f79..ea88f364c07 100644 --- a/mysql-test/t/information_schema_part.test +++ b/mysql-test/t/information_schema_part.test @@ -131,3 +131,10 @@ drop table if exists t1; create table t1 (f1 int key) partition by key(f1) partitions 2; select create_options from information_schema.tables where table_schema="test"; drop table t1; + +--echo # +--echo # MDEV-11353 - Identical logical conditions +--echo # +CREATE TABLE t1(a INT) CHECKSUM=1 SELECT 1; +SELECT CHECKSUM FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1'; +DROP TABLE t1; -- cgit v1.2.1 From 5e051bfa15d201228b103d7f536436a61cde8707 Mon Sep 17 00:00:00 2001 From: Alexander Barkov Date: Wed, 21 Dec 2016 15:39:45 +0400 Subject: MDEV-10386 Assertion `fixed == 1' failed in virtual String* Item_func_conv_charset::val_str(String*) The patch b96c196f1cd5d77e524cbf952539bdd33c65ffc1 added a new call for safe_charset_converter() without a corresponding fix_fields(). In case of a sub-query the created Item remained in non-fixed state. The problem did not show up with literal derived expressions, only subselects were affected. This patch adds a corresponding fix_fields() to the previously added safe_charset_converter(). --- mysql-test/r/subselect.result | 11 +++++++++++ mysql-test/r/subselect_no_mat.result | 11 +++++++++++ mysql-test/r/subselect_no_opts.result | 11 +++++++++++ mysql-test/r/subselect_no_scache.result | 11 +++++++++++ mysql-test/r/subselect_no_semijoin.result | 11 +++++++++++ mysql-test/t/subselect.test | 10 ++++++++++ 6 files changed, 65 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 0a599a64f80..789cfe2fdca 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -7105,3 +7105,14 @@ group by round((select 1 from t1 limit 1)); round((select 1 from t1 limit 1)) 1 drop table t1; +# +# MDEV-10386 Assertion `fixed == 1' failed in virtual String* Item_func_conv_charset::val_str(String*) +# +CREATE TABLE t1 (f1 CHAR(3) CHARACTER SET utf8 NULL, f2 CHAR(3) CHARACTER SET latin1 NULL); +INSERT INTO t1 VALUES ('foo','bar'); +SELECT * FROM t1 WHERE f2 >= SOME ( SELECT f1 FROM t1 ); +f1 f2 +SELECT * FROM t1 WHERE f2 <= SOME ( SELECT f1 FROM t1 ); +f1 f2 +foo bar +DROP TABLE t1; diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result index b819b1e4ef9..c729c17f94f 100644 --- a/mysql-test/r/subselect_no_mat.result +++ b/mysql-test/r/subselect_no_mat.result @@ -7102,6 +7102,17 @@ group by round((select 1 from t1 limit 1)); round((select 1 from t1 limit 1)) 1 drop table t1; +# +# MDEV-10386 Assertion `fixed == 1' failed in virtual String* Item_func_conv_charset::val_str(String*) +# +CREATE TABLE t1 (f1 CHAR(3) CHARACTER SET utf8 NULL, f2 CHAR(3) CHARACTER SET latin1 NULL); +INSERT INTO t1 VALUES ('foo','bar'); +SELECT * FROM t1 WHERE f2 >= SOME ( SELECT f1 FROM t1 ); +f1 f2 +SELECT * FROM t1 WHERE f2 <= SOME ( SELECT f1 FROM t1 ); +f1 f2 +foo bar +DROP TABLE t1; set optimizer_switch=default; select @@optimizer_switch like '%materialization=on%'; @@optimizer_switch like '%materialization=on%' diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result index e1001a51658..dc308ea77e5 100644 --- a/mysql-test/r/subselect_no_opts.result +++ b/mysql-test/r/subselect_no_opts.result @@ -7100,4 +7100,15 @@ group by round((select 1 from t1 limit 1)); round((select 1 from t1 limit 1)) 1 drop table t1; +# +# MDEV-10386 Assertion `fixed == 1' failed in virtual String* Item_func_conv_charset::val_str(String*) +# +CREATE TABLE t1 (f1 CHAR(3) CHARACTER SET utf8 NULL, f2 CHAR(3) CHARACTER SET latin1 NULL); +INSERT INTO t1 VALUES ('foo','bar'); +SELECT * FROM t1 WHERE f2 >= SOME ( SELECT f1 FROM t1 ); +f1 f2 +SELECT * FROM t1 WHERE f2 <= SOME ( SELECT f1 FROM t1 ); +f1 f2 +foo bar +DROP TABLE t1; set @optimizer_switch_for_subselect_test=null; diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result index e175e7e0072..e7c85c10f2d 100644 --- a/mysql-test/r/subselect_no_scache.result +++ b/mysql-test/r/subselect_no_scache.result @@ -7111,6 +7111,17 @@ group by round((select 1 from t1 limit 1)); round((select 1 from t1 limit 1)) 1 drop table t1; +# +# MDEV-10386 Assertion `fixed == 1' failed in virtual String* Item_func_conv_charset::val_str(String*) +# +CREATE TABLE t1 (f1 CHAR(3) CHARACTER SET utf8 NULL, f2 CHAR(3) CHARACTER SET latin1 NULL); +INSERT INTO t1 VALUES ('foo','bar'); +SELECT * FROM t1 WHERE f2 >= SOME ( SELECT f1 FROM t1 ); +f1 f2 +SELECT * FROM t1 WHERE f2 <= SOME ( SELECT f1 FROM t1 ); +f1 f2 +foo bar +DROP TABLE t1; set optimizer_switch=default; select @@optimizer_switch like '%subquery_cache=on%'; @@optimizer_switch like '%subquery_cache=on%' diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result index a211d498762..b6261f05098 100644 --- a/mysql-test/r/subselect_no_semijoin.result +++ b/mysql-test/r/subselect_no_semijoin.result @@ -7100,5 +7100,16 @@ group by round((select 1 from t1 limit 1)); round((select 1 from t1 limit 1)) 1 drop table t1; +# +# MDEV-10386 Assertion `fixed == 1' failed in virtual String* Item_func_conv_charset::val_str(String*) +# +CREATE TABLE t1 (f1 CHAR(3) CHARACTER SET utf8 NULL, f2 CHAR(3) CHARACTER SET latin1 NULL); +INSERT INTO t1 VALUES ('foo','bar'); +SELECT * FROM t1 WHERE f2 >= SOME ( SELECT f1 FROM t1 ); +f1 f2 +SELECT * FROM t1 WHERE f2 <= SOME ( SELECT f1 FROM t1 ); +f1 f2 +foo bar +DROP TABLE t1; set @optimizer_switch_for_subselect_test=null; set @join_cache_level_for_subselect_test=NULL; diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 77b6c6c5582..a8ad3ba52a5 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -5988,3 +5988,13 @@ from t1 group by round((select 1 from t1 limit 1)); drop table t1; + +--echo # +--echo # MDEV-10386 Assertion `fixed == 1' failed in virtual String* Item_func_conv_charset::val_str(String*) +--echo # + +CREATE TABLE t1 (f1 CHAR(3) CHARACTER SET utf8 NULL, f2 CHAR(3) CHARACTER SET latin1 NULL); +INSERT INTO t1 VALUES ('foo','bar'); +SELECT * FROM t1 WHERE f2 >= SOME ( SELECT f1 FROM t1 ); +SELECT * FROM t1 WHERE f2 <= SOME ( SELECT f1 FROM t1 ); +DROP TABLE t1; -- cgit v1.2.1 From 706fb790bcf9105a73f34002fe28c75032267c4b Mon Sep 17 00:00:00 2001 From: Varun Gupta Date: Thu, 22 Dec 2016 15:51:37 +0530 Subject: MDEV-10927: Crash When Using sort_union Optimization In file sql/filesort.cc,when merge_buffers() is called then - queue_remove(&queue,0) is called - For the function queue_remove there is assertion states that the element to be removed should have index >=1 - this is causing the assertion to fail. Fixed by removing the top element. --- mysql-test/r/index_merge_innodb.result | 29 +++++++++++++++++++++++++++++ mysql-test/t/index_merge_innodb.test | 33 ++++++++++++++++++++++++++++++++- 2 files changed, 61 insertions(+), 1 deletion(-) (limited to 'mysql-test') diff --git a/mysql-test/r/index_merge_innodb.result b/mysql-test/r/index_merge_innodb.result index b93d15f7bef..00cbf35ec69 100644 --- a/mysql-test/r/index_merge_innodb.result +++ b/mysql-test/r/index_merge_innodb.result @@ -793,3 +793,32 @@ a b c 9 d d DROP TABLE t1; set optimizer_switch= @optimizer_switch_save; +# +# MDEV-10927: Crash When Using sort_union Optimization +# +set @tmp_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='index_merge_sort_intersection=on'; +SET SESSION sort_buffer_size = 1024; +create table t1 ( +pk int(11) NOT NULL AUTO_INCREMENT, +col1 int(11) NOT NULL, +col2 int(11) NOT NULL, +col3 int(11) NOT NULL, +key2 int(11) NOT NULL, +col4 int(11) NOT NULL, +key1 int(11) NOT NULL, +PRIMARY KEY (pk), +KEY key1 (key1), +KEY key2 (key2) +) ENGINE=InnoDB AUTO_INCREMENT=12860259 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT; +create table t2(a int); +insert into t2 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t3(a int); +insert into t3 select A.a + B.a* 10 + C.a * 100 + D.a*1000 from t2 A, t2 B, t2 C, t2 D; +insert into t1 (key1, key2, col1,col2,col3,col4) +select a,a, a,a,a,a from t3; +SELECT sum(col1) FROM t1 FORCE INDEX (key1,key2) WHERE (key1 between 10 and 8191+10) or (key2= 5); +sum(col1) +33632261 +drop table t1,t2,t3; +set optimizer_switch=@tmp_optimizer_switch; diff --git a/mysql-test/t/index_merge_innodb.test b/mysql-test/t/index_merge_innodb.test index 6a1cb53dc40..fb56e44b5ae 100644 --- a/mysql-test/t/index_merge_innodb.test +++ b/mysql-test/t/index_merge_innodb.test @@ -171,6 +171,37 @@ WHERE ( tb.b != ta.b OR tb.a = ta.a ) AND ( tb.b = ta.c OR tb.b = ta.b ); DROP TABLE t1; - set optimizer_switch= @optimizer_switch_save; +--echo # +--echo # MDEV-10927: Crash When Using sort_union Optimization +--echo # + +set @tmp_optimizer_switch=@@optimizer_switch; +SET optimizer_switch='index_merge_sort_intersection=on'; +SET SESSION sort_buffer_size = 1024; + +create table t1 ( +pk int(11) NOT NULL AUTO_INCREMENT, +col1 int(11) NOT NULL, +col2 int(11) NOT NULL, +col3 int(11) NOT NULL, +key2 int(11) NOT NULL, +col4 int(11) NOT NULL, +key1 int(11) NOT NULL, +PRIMARY KEY (pk), +KEY key1 (key1), +KEY key2 (key2) +) ENGINE=InnoDB AUTO_INCREMENT=12860259 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT; + +create table t2(a int); +insert into t2 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); + +create table t3(a int); +insert into t3 select A.a + B.a* 10 + C.a * 100 + D.a*1000 from t2 A, t2 B, t2 C, t2 D; + +insert into t1 (key1, key2, col1,col2,col3,col4) +select a,a, a,a,a,a from t3; +SELECT sum(col1) FROM t1 FORCE INDEX (key1,key2) WHERE (key1 between 10 and 8191+10) or (key2= 5); +drop table t1,t2,t3; +set optimizer_switch=@tmp_optimizer_switch; -- cgit v1.2.1 From 48655ce6985490fdf6c5c8be7c75b37f83f7738e Mon Sep 17 00:00:00 2001 From: Sergei Golubchik Date: Thu, 22 Dec 2016 12:23:48 +0100 Subject: test case for Bug #23303485 : HANDLE_FATAL_SIGNAL (SIG=11) IN SUBSELECT_UNION_ENGINE::NO_ROWS --- mysql-test/r/subselect2.result | 10 ++++++++++ mysql-test/t/subselect2.test | 13 +++++++++++++ 2 files changed, 23 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/subselect2.result b/mysql-test/r/subselect2.result index b6dc940d9fb..64bd86707cc 100644 --- a/mysql-test/r/subselect2.result +++ b/mysql-test/r/subselect2.result @@ -384,3 +384,13 @@ DEALLOCATE PREPARE stmt; DROP VIEW v1; DROP TABLE t1,t2,t3; set optimizer_switch=@subselect2_test_tmp; +create table t1 (a int); +create table t2 (a int); +create table t3(a int); +insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +insert into t2 select a from t1; +insert into t3 select a from t1; +select null in (select a from t1 where a < out3.a union select a from t2 where +(select a from t3) +1 < out3.a+1) from t3 out3; +ERROR 21000: Subquery returns more than 1 row +drop table t1, t2, t3; diff --git a/mysql-test/t/subselect2.test b/mysql-test/t/subselect2.test index f795cef648c..ae210b865a2 100644 --- a/mysql-test/t/subselect2.test +++ b/mysql-test/t/subselect2.test @@ -398,3 +398,16 @@ DROP TABLE t1,t2,t3; set optimizer_switch=@subselect2_test_tmp; +# +# Bug #23303485 : HANDLE_FATAL_SIGNAL (SIG=11) IN SUBSELECT_UNION_ENGINE::NO_ROWS +# +create table t1 (a int); +create table t2 (a int); +create table t3(a int); +insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +insert into t2 select a from t1; +insert into t3 select a from t1; +--error ER_SUBQUERY_NO_1_ROW +select null in (select a from t1 where a < out3.a union select a from t2 where + (select a from t3) +1 < out3.a+1) from t3 out3; +drop table t1, t2, t3; -- cgit v1.2.1 From 0912fbbce179cba38bd41de797ba5934e63dbaad Mon Sep 17 00:00:00 2001 From: Elena Stepanova Date: Wed, 4 Jan 2017 03:33:39 +0200 Subject: MDEV-11719 main.subselect_no_exists_to_in failed in buildbot main.log_slow might leave mysql.slow_log table non-empty, and tests which later use it might fail. Make sure that the table is properly truncated --- mysql-test/r/log_slow.result | 2 +- mysql-test/t/log_slow.test | 2 +- 2 files changed, 2 insertions(+), 2 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/log_slow.result b/mysql-test/r/log_slow.result index 89846dc698c..b396de8a3d8 100644 --- a/mysql-test/r/log_slow.result +++ b/mysql-test/r/log_slow.result @@ -67,9 +67,9 @@ sleep(0.5) select count(*) FROM mysql.slow_log; count(*) 1 -truncate mysql.slow_log; set @@long_query_time=default; set global slow_query_log= @org_slow_query_log; set @@log_slow_filter=default; set @@log_slow_verbosity=default; set global log_output= default; +truncate mysql.slow_log; diff --git a/mysql-test/t/log_slow.test b/mysql-test/t/log_slow.test index 8d5a09d7a94..56e35bd5a20 100644 --- a/mysql-test/t/log_slow.test +++ b/mysql-test/t/log_slow.test @@ -50,7 +50,6 @@ set global slow_query_log=1; set global log_output='TABLE'; select sleep(0.5); select count(*) FROM mysql.slow_log; -truncate mysql.slow_log; # Reset used variables set @@long_query_time=default; @@ -58,3 +57,4 @@ set global slow_query_log= @org_slow_query_log; set @@log_slow_filter=default; set @@log_slow_verbosity=default; set global log_output= default; +truncate mysql.slow_log; -- cgit v1.2.1 From e5d7fc967ede53407a65bfde3faec3181e35f19f Mon Sep 17 00:00:00 2001 From: Elena Stepanova Date: Wed, 4 Jan 2017 13:03:30 +0200 Subject: MDEV-10100 main.pool_of_threads fails sporadically in buildbot Backport the fix to 5.5, because it fails there too The patch fixes two test failures: - on slow builders, sometimes a connection attempt which should fail due to the exceeded number of thread_pool_max_threads actually succeeds; - on even slow builders, MTR sometimes cannot establish the initial connection, and check-testcase fails prior to the test start The problem with check-testcase was caused by connect-timeout=2 which was set for all clients in the test config file. On slow builders it might be not enough. There is no way to override it for the pre-test check, so it needed to be substantially increased or removed. The other problem was caused by a race condition between sleeps that the test performs in existing connections and the connect timeout for the connection attempt which was expected to fail. If sleeps finished before the connect-timeout was exceeded, it would allow the connection to succeed. To solve each problem without making the other one worse, connect-timeout should be configured dynamically during the test. Due to the nature of the test (all connections must be busy at the moment when we need to change the timeout, and cannot execute SET GLOBAL ...), it needs to be done independently from the server. The solution: - recognize 'connect_timeout' as a connection option in mysqltest's "connect" command; - remove connect-timeout from the test configuration file; - use the new connect_timeout option for those connections which are expected to fail; - re-arrange the test flow to allow running a huge SLEEP without affecting the test execution time (because it would be interrupted after the main test flow is finished). The test is still subject to false negatives, e.g. if the connection fails due to timeout rather than due to the exceeded number of allowed threads, or if the connection on extra port succeeds due to a race condition and not because the special logic for the extra port. But those false negatives have always been possible there on slow builders, they should not be critical because faster builders should catch such failures if they appear. Conflicts: client/mysqltest.cc mysql-test/r/pool_of_threads.result mysql-test/t/pool_of_threads.test --- mysql-test/r/pool_of_threads.result | 21 ++++++++--------- mysql-test/t/pool_of_threads.cnf | 3 --- mysql-test/t/pool_of_threads.test | 46 +++++++++++++++++++++---------------- 3 files changed, 36 insertions(+), 34 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/pool_of_threads.result b/mysql-test/r/pool_of_threads.result index 7acb45121d6..f153ebc48f4 100644 --- a/mysql-test/r/pool_of_threads.result +++ b/mysql-test/r/pool_of_threads.result @@ -2157,23 +2157,22 @@ Warnings: Warning 1052 Column 'kundentyp' in group statement is ambiguous drop table t1; SET optimizer_switch=@save_optimizer_switch; -SELECT sleep(5); -SELECT sleep(5); +SELECT sleep(50); +SELECT sleep(50); # -- Success: more than --thread_pool_max_threads normal connections not possible -sleep(5) -0 -sleep(5) -0 -SELECT sleep(5); -SELECT sleep(5); SELECT 'Connection on extra port ok'; Connection on extra port ok Connection on extra port ok +SELECT sleep(5.5); SELECT 'Connection on extra port 2 ok'; Connection on extra port 2 ok Connection on extra port 2 ok # -- Success: more than --extra-max-connections + 1 normal connections not possible -sleep(5) -0 -sleep(5) +KILL QUERY ; +KILL QUERY ; +sleep(50) +1 +sleep(50) +1 +sleep(5.5) 0 diff --git a/mysql-test/t/pool_of_threads.cnf b/mysql-test/t/pool_of_threads.cnf index c03e1da6450..f6651c878de 100644 --- a/mysql-test/t/pool_of_threads.cnf +++ b/mysql-test/t/pool_of_threads.cnf @@ -7,8 +7,5 @@ loose-thread_pool_max_threads= 2 extra-port= @ENV.MASTER_EXTRA_PORT extra-max-connections=1 -[client] -connect-timeout= 2 - [ENV] MASTER_EXTRA_PORT= @OPT.port diff --git a/mysql-test/t/pool_of_threads.test b/mysql-test/t/pool_of_threads.test index 4600128ff43..f13a096985c 100644 --- a/mysql-test/t/pool_of_threads.test +++ b/mysql-test/t/pool_of_threads.test @@ -15,20 +15,26 @@ SET optimizer_switch=@save_optimizer_switch; # connections on the extra port. # First set two connections running, and check that extra connection -# on normal port fails due to--thread-pool-max_threads=2 +# on normal port fails due to --thread-pool-max-threads=2. +# We can afford using a really long sleep, because we won't wait +# till it ends, we'll interrupt it as soon as we don't need it anymore + connection default; -send SELECT sleep(5); +--let $con1_id= `SELECT CONNECTION_ID()` + +send SELECT sleep(50); --sleep 1 connect(con2,localhost,root,,); -connection con2; -send SELECT sleep(5); +--let $con2_id= `SELECT CONNECTION_ID()` + +send SELECT sleep(50); --sleep 0.5 --disable_abort_on_error --disable_result_log --disable_query_log -connect(con3,localhost,root,,); +connect(con3,localhost,root,,,,,connect_timeout=2); --enable_query_log --enable_result_log --enable_abort_on_error @@ -42,24 +48,15 @@ if ($error) --echo # -- Success: more than --thread_pool_max_threads normal connections not possible } -connection default; ---reap -connection con2; ---reap - -# Now try again, but this time use the extra port to successfully connect. - -connection default; -send SELECT sleep(5); - -connection con2; -send SELECT sleep(5); ---sleep 1 - connect(extracon,127.0.0.1,root,,test,$MASTER_EXTRA_PORT,); connection extracon; SELECT 'Connection on extra port ok'; +# Here, sleep just for slightly longer than 5 sec to trigger MDEV-4566 +# (abort in interruptible wait connection check). +send SELECT sleep(5.5); + + connect(extracon2,127.0.0.1,root,,test,$MASTER_EXTRA_PORT,); connection extracon2; SELECT 'Connection on extra port 2 ok'; @@ -67,7 +64,7 @@ SELECT 'Connection on extra port 2 ok'; --disable_abort_on_error --disable_result_log --disable_query_log -connect(extracon3,127.0.0.1,root,,test,$MASTER_EXTRA_PORT,); +connect(extracon3,127.0.0.1,root,,test,$MASTER_EXTRA_PORT,,connect_timeout=2); --enable_query_log --enable_result_log --enable_abort_on_error @@ -81,7 +78,16 @@ if ($error) --echo # -- Success: more than --extra-max-connections + 1 normal connections not possible } +connection extracon2; +--replace_result $con1_id +eval KILL QUERY $con1_id; +--replace_result $con2_id +eval KILL QUERY $con2_id; + connection default; --reap connection con2; --reap + +connection extracon; +--reap -- cgit v1.2.1 From f1ee011a6cbda1069a6ec9b5e2428451a64861fd Mon Sep 17 00:00:00 2001 From: Elena Stepanova Date: Wed, 4 Jan 2017 23:05:22 +0200 Subject: MDEV-11722 main.join_cache fails in buildbot on very slow builders The guilty part of the test checks for performance degradation on a query with numerous joins on an empty table. The test expects the query to take less than 1 second, and fails if it is not so (which can happen on very slow builders). The solution is to add more JOINs to the query. On a fixed server, it should not have any noticeable impact on the query execution, while on the unfixed version the query would take several times longer (e.g. 6.5 sec vs 1.5 sec). Thus, we can increase the margin for the error, and make the test fail when the query takes longer than 5 seconds. --- mysql-test/r/join_cache.result | 16 ++++++++++++---- mysql-test/t/join_cache.test | 10 ++++++++-- 2 files changed, 20 insertions(+), 6 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/join_cache.result b/mysql-test/r/join_cache.result index 8a2bdcec8a2..cc64393f975 100644 --- a/mysql-test/r/join_cache.result +++ b/mysql-test/r/join_cache.result @@ -5702,11 +5702,13 @@ LEFT JOIN t2 c1 ON c1.parent_id = t.id AND c1.col2 = "val" LEFT JOIN t2 c23 ON c23.parent_id = t.id AND c23.col2 = "val" LEFT JOIN t2 c24 ON c24.parent_id = t.id AND c24.col2 = "val" LEFT JOIN t2 c25 ON c25.parent_id = t.id AND c25.col2 = "val" + LEFT JOIN t2 c26 ON c26.parent_id = t.id AND c26.col2 = "val" + LEFT JOIN t2 c27 ON c27.parent_id = t.id AND c27.col2 = "val" ORDER BY col1; id col1 -select timestampdiff(second, @init_time, now()) <= 1; -timestampdiff(second, @init_time, now()) <= 1 +select timestampdiff(second, @init_time, now()) <= 5; +timestampdiff(second, @init_time, now()) <= 5 1 set join_cache_level=2; set @init_time:=now(); @@ -5738,11 +5740,13 @@ LEFT JOIN t2 c1 ON c1.parent_id = t.id AND c1.col2 = "val" LEFT JOIN t2 c23 ON c23.parent_id = t.id AND c23.col2 = "val" LEFT JOIN t2 c24 ON c24.parent_id = t.id AND c24.col2 = "val" LEFT JOIN t2 c25 ON c25.parent_id = t.id AND c25.col2 = "val" + LEFT JOIN t2 c26 ON c26.parent_id = t.id AND c26.col2 = "val" + LEFT JOIN t2 c27 ON c27.parent_id = t.id AND c27.col2 = "val" ORDER BY col1; id col1 -select timestampdiff(second, @init_time, now()) <= 1; -timestampdiff(second, @init_time, now()) <= 1 +select timestampdiff(second, @init_time, now()) <= 5; +timestampdiff(second, @init_time, now()) <= 5 1 EXPLAIN SELECT t.* @@ -5773,6 +5777,8 @@ LEFT JOIN t2 c1 ON c1.parent_id = t.id AND c1.col2 = "val" LEFT JOIN t2 c23 ON c23.parent_id = t.id AND c23.col2 = "val" LEFT JOIN t2 c24 ON c24.parent_id = t.id AND c24.col2 = "val" LEFT JOIN t2 c25 ON c25.parent_id = t.id AND c25.col2 = "val" + LEFT JOIN t2 c26 ON c26.parent_id = t.id AND c26.col2 = "val" + LEFT JOIN t2 c27 ON c27.parent_id = t.id AND c27.col2 = "val" ORDER BY col1; id select_type table type possible_keys key key_len ref rows Extra @@ -5802,6 +5808,8 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE c23 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (incremental, BNL join) 1 SIMPLE c24 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (incremental, BNL join) 1 SIMPLE c25 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE c26 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (incremental, BNL join) +1 SIMPLE c27 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (incremental, BNL join) set join_buffer_size=default; set join_cache_level = default; DROP TABLE t1,t2; diff --git a/mysql-test/t/join_cache.test b/mysql-test/t/join_cache.test index 1d22de86799..77e8fce0d27 100644 --- a/mysql-test/t/join_cache.test +++ b/mysql-test/t/join_cache.test @@ -3709,9 +3709,11 @@ FROM LEFT JOIN t2 c23 ON c23.parent_id = t.id AND c23.col2 = "val" LEFT JOIN t2 c24 ON c24.parent_id = t.id AND c24.col2 = "val" LEFT JOIN t2 c25 ON c25.parent_id = t.id AND c25.col2 = "val" + LEFT JOIN t2 c26 ON c26.parent_id = t.id AND c26.col2 = "val" + LEFT JOIN t2 c27 ON c27.parent_id = t.id AND c27.col2 = "val" ORDER BY col1; -select timestampdiff(second, @init_time, now()) <= 1; +select timestampdiff(second, @init_time, now()) <= 5; set join_cache_level=2; @@ -3744,9 +3746,11 @@ FROM LEFT JOIN t2 c23 ON c23.parent_id = t.id AND c23.col2 = "val" LEFT JOIN t2 c24 ON c24.parent_id = t.id AND c24.col2 = "val" LEFT JOIN t2 c25 ON c25.parent_id = t.id AND c25.col2 = "val" + LEFT JOIN t2 c26 ON c26.parent_id = t.id AND c26.col2 = "val" + LEFT JOIN t2 c27 ON c27.parent_id = t.id AND c27.col2 = "val" ORDER BY col1; -select timestampdiff(second, @init_time, now()) <= 1; +select timestampdiff(second, @init_time, now()) <= 5; EXPLAIN SELECT t.* @@ -3777,6 +3781,8 @@ FROM LEFT JOIN t2 c23 ON c23.parent_id = t.id AND c23.col2 = "val" LEFT JOIN t2 c24 ON c24.parent_id = t.id AND c24.col2 = "val" LEFT JOIN t2 c25 ON c25.parent_id = t.id AND c25.col2 = "val" + LEFT JOIN t2 c26 ON c26.parent_id = t.id AND c26.col2 = "val" + LEFT JOIN t2 c27 ON c27.parent_id = t.id AND c27.col2 = "val" ORDER BY col1; -- cgit v1.2.1 From 5302ef2c955d98bf3b1fb1f94c036d904c8af922 Mon Sep 17 00:00:00 2001 From: Elena Stepanova Date: Sun, 1 Jan 2017 23:13:04 +0200 Subject: MDEV-11700 funcs_2.innodb_charset fails in buldbot on valgrind builder with timeout When the test is run as a part of the suite with valgrind, only allow it to be executed if --big-test is set. If the test is run by specifying its name explicitly, it will still be executed, even with valgrind without big-test, MTR has special logic for that --- mysql-test/suite/funcs_2/t/innodb_charset.test | 1 + 1 file changed, 1 insertion(+) (limited to 'mysql-test') diff --git a/mysql-test/suite/funcs_2/t/innodb_charset.test b/mysql-test/suite/funcs_2/t/innodb_charset.test index b77bacfc01c..da4dea44ad7 100644 --- a/mysql-test/suite/funcs_2/t/innodb_charset.test +++ b/mysql-test/suite/funcs_2/t/innodb_charset.test @@ -6,6 +6,7 @@ # Checking of other prerequisites is in charset_master.test # ################################################################################ +--source include/no_valgrind_without_big.inc --source include/have_innodb.inc let $engine_type= InnoDB; -- cgit v1.2.1 From 9e528d4fdeab83fcc75ec788da939937ce27b98d Mon Sep 17 00:00:00 2001 From: Elena Stepanova Date: Thu, 5 Jan 2017 17:38:55 +0200 Subject: MDEV-11727 Sequences of tests fail with valgrind warnings in buildbot The warning is "blocks are still reachable in loss record", happens in malloc / _dl_close_worker. Suppression added --- mysql-test/valgrind.supp | 8 ++++++++ 1 file changed, 8 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/valgrind.supp b/mysql-test/valgrind.supp index b86cbd23408..154031feb0d 100644 --- a/mysql-test/valgrind.supp +++ b/mysql-test/valgrind.supp @@ -923,6 +923,14 @@ fun:backtrace } +{ + memory leak in mysqld_exit + Memcheck:Leak + fun:malloc + fun:_dl_close_worker + fun:_dl_close +} + # # Bug in Glibc 2.9: http://sourceware.org/bugzilla/show_bug.cgi?id=10391 # Fixed in latest Glibc, but suppressed here for running tests on hosts -- cgit v1.2.1 From ae1b3d1991b679bb38095711de27934d7683deda Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Thu, 5 Jan 2017 13:54:31 -0800 Subject: Fixed bug mdev-10705. The fix for bug mdev-5104 did not take into account that for any call of setup_order the size of ref_array must be big enough. This patch fixes this problem. --- mysql-test/r/order_by.result | 11 +++++++++++ mysql-test/t/order_by.test | 10 ++++++++++ 2 files changed, 21 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result index 6ce12651dba..94a38ca7827 100644 --- a/mysql-test/r/order_by.result +++ b/mysql-test/r/order_by.result @@ -2050,4 +2050,15 @@ t2 A, t2 B where A.b = B.b order by A.col2, B.col2 limit 10, 1000000; drop table t1,t2,t3; +# +# mdev-10705 : long order by list that can be skipped +# +SELECT 1 +UNION +( SELECT 2 +ORDER BY NULL, @a0 := 3, @a1 := 3, @a2 := 3, @a3 := 3, @a4 := 3, +@a5 := 3, @a6 := 3, @a7 := 3, @a8 := 3, @a9 := 3, @a10 := 3 ); +1 +1 +2 End of 5.5 tests diff --git a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test index 2ebf8ba5af1..c96d5c2996a 100644 --- a/mysql-test/t/order_by.test +++ b/mysql-test/t/order_by.test @@ -1746,6 +1746,16 @@ order by A.col2, B.col2 limit 10, 1000000; drop table t1,t2,t3; +--echo # +--echo # mdev-10705 : long order by list that can be skipped +--echo # + +SELECT 1 +UNION +( SELECT 2 + ORDER BY NULL, @a0 := 3, @a1 := 3, @a2 := 3, @a3 := 3, @a4 := 3, + @a5 := 3, @a6 := 3, @a7 := 3, @a8 := 3, @a9 := 3, @a10 := 3 ); + --echo End of 5.5 tests -- cgit v1.2.1