diff options
| author | Georgi Kodinov <Georgi.Kodinov@Oracle.com> | 2011-02-09 10:30:44 +0200 |
|---|---|---|
| committer | Georgi Kodinov <Georgi.Kodinov@Oracle.com> | 2011-02-09 10:30:44 +0200 |
| commit | e458739471ed83162111a86a2efe78d63cabb3ec (patch) | |
| tree | 98fc0ea6e3ce7811e5bf496237b905a2b8f69e43 /mysql-test | |
| parent | 03e27ac1618182794012659c70660af01b15ab86 (diff) | |
| parent | 107b46070df5ee7c0d8ca7424f44a75538d07d14 (diff) | |
| download | mariadb-git-e458739471ed83162111a86a2efe78d63cabb3ec.tar.gz | |
weave merge mysql-5.1->mysql-5.1-security
Diffstat (limited to 'mysql-test')
52 files changed, 1419 insertions, 128 deletions
diff --git a/mysql-test/collections/default.weekly b/mysql-test/collections/default.weekly index f10bc0776a0..a2765bcaf0c 100755 --- a/mysql-test/collections/default.weekly +++ b/mysql-test/collections/default.weekly @@ -1,4 +1,3 @@ -perl mysql-test-run.pl --timer --force --comment=1st --experimental=collections/default.experimental 1st perl mysql-test-run.pl --timer --force --comment=big-tests --experimental=collections/default.experimental --vardir=var-big-tests --big-test --testcase-timeout=60 --suite-timeout=600 parts.partition_alter1_2_ndb parts.part_supported_sql_func_innodb parts.partition_alter1_2_innodb parts.partition_alter4_innodb parts.partition_alter1_1_2_ndb parts.partition_alter1_1_2_innodb parts.partition_alter1_1_ndb rpl_ndb.rpl_truncate_7ndb_2 main.archive-big main.sum_distinct-big main.mysqlbinlog_row_big main.alter_table-big main.variables-big main.type_newdecimal-big main.read_many_rows_innodb main.log_tables-big main.count_distinct3 main.events_time_zone main.merge-big main.create-big main.events_stress main.ssl-big funcs_1.myisam_views-big perl mysql-test-run.pl --timer --force --parallel=auto --comment=eits-tests-myisam-engine --experimental=collections/default.experimental --vardir=var-stmt-eits-tests-myisam-engine --suite=engines/iuds,engines/funcs --suite-timeout=500 --max-test-fail=0 --retry-failure=0 --mysqld=--default-storage-engine=myisam perl mysql-test-run.pl --timer --force --parallel=auto --comment=eits-rpl-binlog-row-tests-myisam-engine --experimental=collections/default.experimental --vardir=var-binlog-row-eits-tests-myisam-engine --suite=engines/iuds,engines/funcs --suite-timeout=500 --max-test-fail=0 --retry-failure=0 --mysqld=--default-storage-engine=myisam --do-test=rpl --mysqld=--binlog-format=row diff --git a/mysql-test/collections/mysql-5.1-bugteam.daily b/mysql-test/collections/mysql-5.1-bugteam.daily deleted file mode 100644 index 0503bd49f73..00000000000 --- a/mysql-test/collections/mysql-5.1-bugteam.daily +++ /dev/null @@ -1,5 +0,0 @@ -perl mysql-test-run.pl --timer --force --parallel=auto --comment=n_mix --vardir=var-n_mix --mysqld=--binlog-format=mixed --experimental=collections/default.experimental -perl mysql-test-run.pl --timer --force --parallel=auto --comment=ps_row --vardir=var-ps_row --ps-protocol --mysqld=--binlog-format=row --experimental=collections/default.experimental -perl mysql-test-run.pl --timer --force --parallel=auto --comment=embedded --vardir=var-emebbed --embedded --experimental=collections/default.experimental -perl mysql-test-run.pl --timer --force --parallel=auto --comment=rpl_binlog_row --vardir=var-rpl_binlog_row --suite=rpl,binlog --mysqld=--binlog-format=row --experimental=collections/default.experimental -perl mysql-test-run.pl --timer --force --parallel=auto --comment=funcs_1 --vardir=var-funcs_1 --suite=funcs_1 --experimental=collections/default.experimental diff --git a/mysql-test/collections/mysql-5.1-bugteam.push b/mysql-test/collections/mysql-5.1-bugteam.push deleted file mode 100644 index d01b98eb87f..00000000000 --- a/mysql-test/collections/mysql-5.1-bugteam.push +++ /dev/null @@ -1,4 +0,0 @@ -perl mysql-test-run.pl --timer --force --parallel=auto --comment=n_mix --vardir=var-n_mix --mysqld=--binlog-format=mixed --experimental=collections/default.experimental --skip-ndb -perl mysql-test-run.pl --timer --force --parallel=auto --comment=ps_row --vardir=var-ps_row --suite=main --ps-protocol --mysqld=--binlog-format=row --experimental=collections/default.experimental --skip-ndb -perl mysql-test-run.pl --timer --force --parallel=auto --comment=embedded --vardir=var-emebbed --suite=main --embedded --experimental=collections/default.experimental --skip-ndb -perl mysql-test-run.pl --timer --force --parallel=auto --comment=funcs_1 --vardir=var-funcs_1 --suite=funcs_1 --experimental=collections/default.experimental --skip-ndb diff --git a/mysql-test/extra/rpl_tests/rpl_insert_duplicate.test b/mysql-test/extra/rpl_tests/rpl_insert_duplicate.test new file mode 100644 index 00000000000..77140174f4b --- /dev/null +++ b/mysql-test/extra/rpl_tests/rpl_insert_duplicate.test @@ -0,0 +1,64 @@ +# BUG#59338 Inconsistency in binlog for statements that don't change any rows STATEMENT SBR +# In SBR, if a statement does not fail, it is always written to the binary log, +# regardless if rows are changed or not. If there is a failure, a statement is +# only written to the binary log if a non-transactional (.e.g. MyIsam) engine +# is updated. INSERT ON DUPLICATE KEY UPDATE was not following the rule above +# and was not written to the binary log, if then engine was Innodb. +# +# In this test case, we check if INSERT ON DUPLICATE KEY UPDATE that does not +# change anything is still written to the binary log. + +# Prepare environment +--connection master + +eval CREATE TABLE t1 ( + a INT UNSIGNED NOT NULL PRIMARY KEY +) ENGINE=$engine_type; + +eval CREATE TABLE t2 ( + a INT UNSIGNED +) ENGINE=$engine_type; + +INSERT INTO t1 VALUES (1); +INSERT INTO t2 VALUES (1); + +# An insert duplicate that does not update anything must be written to the binary +# log in SBR and MIXED modes. We check this property by summing a before and after +# the update and comparing the binlog positions. The sum should be the same at both +# points and the statement should be in the binary log. +--let $binlog_file= query_get_value("SHOW MASTER STATUS", File, 1) +--let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1) +--let $statement_file=INSERT INTO t1 SELECT t2.a FROM t2 ORDER BY t2.a ON DUPLICATE KEY UPDATE t1.a= t1.a +--eval $statement_file + +--let $assert_cond= SUM(a) = 1 FROM t1 +--let $assert_text= Sum of elements in t1 should be 1. +--source include/assert.inc + +if (`SELECT @@BINLOG_FORMAT = 'ROW'`) +{ + --let $binlog_position_cmp= = + --let $assert_cond= [SHOW MASTER STATUS, Position, 1] $binlog_position_cmp $binlog_start + --let $assert_text= In SBR or MIXED modes, the event in the binlog should be the same that was executed. In RBR mode, binlog position should stay unchanged. +} +if (`SELECT @@BINLOG_FORMAT != 'ROW' && UPPER('$engine_type') = UPPER('Innodb')`) +{ + --let $assert_cond= \'[\'SHOW BINLOG EVENTS IN "$binlog_file" FROM $binlog_start LIMIT 1, 1\', Info, 1]\' LIKE \'%$statement_file\' + --let $assert_text= In SBR or MIXED modes, the event in the binlog should be the same that was executed. In RBR mode, binlog position should stay unchanged. +} +if (`SELECT @@BINLOG_FORMAT != 'ROW' && UPPER('$engine_type') = UPPER('MyIsam')`) +{ + --let $assert_cond= \'[\'SHOW BINLOG EVENTS IN "$binlog_file" FROM $binlog_start LIMIT 0, 1\', Info, 1]\' LIKE \'%$statement_file\' + --let $assert_text= In SBR or MIXED modes, the event in the binlog should be the same that was executed. In RBR mode, binlog position should stay unchanged. +} +--source include/assert.inc + +# Compare master and slave +--sync_slave_with_master +--let $diff_tables= master:test.t1 , slave:test.t1 +--source include/diff_tables.inc + +# Clean up +--connection master +drop table t1, t2; +--sync_slave_with_master diff --git a/mysql-test/extra/rpl_tests/rpl_insert_ignore.test b/mysql-test/extra/rpl_tests/rpl_insert_ignore.test index 43d45ef6c60..1eb66358423 100644 --- a/mysql-test/extra/rpl_tests/rpl_insert_ignore.test +++ b/mysql-test/extra/rpl_tests/rpl_insert_ignore.test @@ -5,6 +5,7 @@ # Slave needs to be started with --innodb to store table in InnoDB. # Same test for MyISAM (which had no bug). +--connection master eval CREATE TABLE t1 ( a int unsigned not null auto_increment primary key, b int unsigned, @@ -32,38 +33,49 @@ INSERT INTO t2 VALUES (5, 4); INSERT INTO t2 VALUES (6, 6); INSERT IGNORE INTO t1 SELECT NULL, t2.b FROM t2 ORDER BY t2.a; - -# Compare results - -SELECT * FROM t1 ORDER BY a; - -sync_slave_with_master; -SELECT * FROM t1 ORDER BY a; - -# Now do the same for MyISAM - -connection master; -drop table t1; -eval CREATE TABLE t1 ( - a int unsigned not null auto_increment primary key, - b int unsigned, - unique (b) -) ENGINE=$engine_type2; - -INSERT INTO t1 VALUES (1, 1); -INSERT INTO t1 VALUES (2, 2); -INSERT INTO t1 VALUES (3, 3); -INSERT INTO t1 VALUES (4, 4); - -INSERT IGNORE INTO t1 SELECT NULL, t2.b FROM t2 ORDER BY t2.a; - -SELECT * FROM t1 ORDER BY a; - -sync_slave_with_master; -SELECT * FROM t1 ORDER BY a; - -connection master; +--let $assert_cond= COUNT(*) = 6 FROM t1 +--let $assert_text= Count of elements in t1 should be 6. +--source include/assert.inc + +# Compare master and slave +--sync_slave_with_master +--let $diff_tables= master:test.t1 , slave:test.t1 +--source include/diff_tables.inc + +# BUG#59338 Inconsistency in binlog for statements that don't change any rows STATEMENT SBR +# An insert ignore that does not update anything must be written to the binary log in SBR +# and MIXED modes. We check this property by counting occurrences in t1 before and after +# the insert and comparing the binlog positions. The count should be the same in both points +# and the statement should be in the binary log. +--connection master +--let $binlog_file= query_get_value("SHOW MASTER STATUS", File, 1) +--let $binlog_start= query_get_value("SHOW MASTER STATUS", Position, 1) +--let $statement_file=INSERT IGNORE INTO t1 SELECT NULL, t2.b FROM t2 ORDER BY t2.a +--eval $statement_file + +--let $assert_cond= COUNT(*) = 6 FROM t1 +--let $assert_text= Count of elements in t1 should be 6. +--source include/assert.inc + +if (`SELECT @@BINLOG_FORMAT = 'ROW'`) +{ + --let $binlog_position_cmp= = + --let $assert_cond= [SHOW MASTER STATUS, Position, 1] $binlog_position_cmp $binlog_start + --let $assert_text= In SBR or MIXED modes, the event in the binlog should be the same that was executed. In RBR mode, binlog position should stay unchanged. +} +if (`SELECT @@BINLOG_FORMAT != 'ROW' && UPPER('$engine_type') = UPPER('Innodb')`) +{ + --let $assert_cond= \'[\'SHOW BINLOG EVENTS IN "$binlog_file" FROM $binlog_start LIMIT 2, 1\', Info, 1]\' LIKE \'%$statement_file\' + --let $assert_text= In SBR or MIXED modes, the event in the binlog should be the same that was executed. In RBR mode, binlog position should stay unchanged. +} +if (`SELECT @@BINLOG_FORMAT != 'ROW' && UPPER('$engine_type') = UPPER('MyIsam')`) +{ + --let $assert_cond= \'[\'SHOW BINLOG EVENTS IN "$binlog_file" FROM $binlog_start LIMIT 1, 1\', Info, 1]\' LIKE \'%$statement_file\' + --let $assert_text= In SBR or MIXED modes, the event in the binlog should be the same that was executed. In RBR mode, binlog position should stay unchanged. +} +--source include/assert.inc + +# Clean up +--connection master drop table t1, t2; -sync_slave_with_master; - -# End of 4.1 tests +--sync_slave_with_master diff --git a/mysql-test/include/commit.inc b/mysql-test/include/commit.inc index d412eae8364..7924f9bc96f 100644 --- a/mysql-test/include/commit.inc +++ b/mysql-test/include/commit.inc @@ -502,16 +502,16 @@ call p_verify_status_increment(2, 2, 2, 2); --echo # 12. Read-write statement: IODKU, change 0 rows. --echo # insert t1 set a=2 on duplicate key update a=2; -call p_verify_status_increment(1, 0, 1, 0); +call p_verify_status_increment(2, 2, 1, 0); commit; -call p_verify_status_increment(1, 0, 1, 0); +call p_verify_status_increment(2, 2, 1, 0); --echo # 13. Read-write statement: INSERT IGNORE, change 0 rows. --echo # insert ignore t1 set a=2; -call p_verify_status_increment(1, 0, 1, 0); +call p_verify_status_increment(2, 2, 1, 0); commit; -call p_verify_status_increment(1, 0, 1, 0); +call p_verify_status_increment(2, 2, 1, 0); --echo # 14. Read-write statement: INSERT IGNORE, change 1 row. --echo # diff --git a/mysql-test/include/gis_keys.inc b/mysql-test/include/gis_keys.inc index c75311f062a..ad00c7e1ef9 100644 --- a/mysql-test/include/gis_keys.inc +++ b/mysql-test/include/gis_keys.inc @@ -44,3 +44,19 @@ SELECT COUNT(*) FROM t2 IGNORE INDEX(p) WHERE p=POINTFROMTEXT('POINT(1 2)'); DROP TABLE t1, t2; --echo End of 5.0 tests + + +--echo # +--echo # Test for bug #58650 "Failing assertion: primary_key_no == -1 || +--echo # primary_key_no == 0". +--echo # +--disable_warnings +drop table if exists t1; +--enable_warnings +--echo # The minimal test case. +create table t1 (a int not null, b linestring not null, unique key b (b(12)), unique key a (a)); +drop table t1; +--echo # The original test case. +create table t1 (a int not null, b linestring not null, unique key b (b(12))); +create unique index a on t1(a); +drop table t1; diff --git a/mysql-test/include/have_dbi_dbd-mysql.inc b/mysql-test/include/have_dbi_dbd-mysql.inc new file mode 100644 index 00000000000..212e36ac353 --- /dev/null +++ b/mysql-test/include/have_dbi_dbd-mysql.inc @@ -0,0 +1,78 @@ +# +# Originally created by John Embretsen, 2011-01-26. +# +# Checks for the existence of Perl modules DBI and DBD::mysql as seen from the +# perl installation used by "external" executable perl scripts, i.e. scripts +# that are executed as standalone scripts interpreted by the perl installation +# specified by the "shebang" line in the top of these scripts. +# +# If either module is not found, the test will be skipped. +# +# For use in tests that call perl scripts that require these modules. +# +# This file is intended to work on Unix. Windows may need different treatment. +# Reasoning: +# - "shebangs" are not relevant on Windows, but need to be handled here. +# - Perl scripts cannot be made executable on Windows, interpreter must be +# specified. +# +# Note that if there are multiple perl installations and not all have the +# required modules, this check may fail even if the perl in path does have +# the modules available. This may happen if the perl specified by the script's +# shebang (e.g. #!/usr/bin/perl) does not have these modules, and script is +# called without specifying the perl interpreter. However, this will be +# a correct result in cases where a test calls a script with a similar shebang. +# +################################################################################ + +--source include/not_windows.inc + +# We jump through some hoops since there is no direct way to check if an +# external command went OK or not from a mysql-test file: +# +# - In theory, we could do as simple as "exec perl -MDBI -MDBD::mysql -e 1", +# however we cannot check the result (exit code) from within a test script. +# Also, this may not yield the same result as other uses of perl due to the +# shebang issue mentioned above. +# - Instead we use a separate helper perl script that checks for the modules. +# - If the modules are found, the perl script leaves a file which sets a +# variable that can be read by this file. +# If the modules are not found, the perl script does not set this variable, +# but leaves an empty file instead. +# +# This is done because there is apparently no direct way to transfer +# information from perl to the test script itself. + +--disable_query_log +--disable_result_log +--disable_warnings + +# We do not use embedded perl in this script because that would not have yielded +# correct results for a situation where an external Perl script is called like +# "scriptname" instead of "perl scriptname" and the shebang in the script points +# to a specific perl that may be different than the perl in PATH. +# +# Instead, we call a separate helper script which checks for the modules in its +# own environment. We call it without "perl" in front. + +--let $perlChecker= $MYSQLTEST_VARDIR/std_data/checkDBI_DBD-mysql.pl +--let $resultFile= $MYSQL_TMP_DIR/dbidbd-mysql.txt + +# Make the script executable and execute it. +--chmod 0755 $perlChecker +--exec $perlChecker + +# Source the resulting temporary file and look for a variable being set. +--source $resultFile + +if (!$dbidbd) { + --skip Test needs Perl modules DBI and DBD::mysql +} + +# Clean up +--remove_file $resultFile + +--enable_query_log +--enable_result_log +--enable_warnings + diff --git a/mysql-test/include/mysqlhotcopy.inc b/mysql-test/include/mysqlhotcopy.inc index 91e0eff1e0f..fcf57a68644 100644 --- a/mysql-test/include/mysqlhotcopy.inc +++ b/mysql-test/include/mysqlhotcopy.inc @@ -4,12 +4,26 @@ --source include/not_windows.inc --source include/not_embedded.inc +--source include/have_dbi_dbd-mysql.inc if (!$MYSQLHOTCOPY) { + # Fail the test if the mysqlhotcopy script is missing. + # If the tool's location changes, mysql-test-run.pl must be updated to + # reflect this (look for "MYSQLHOTCOPY"). die due to missing mysqlhotcopy tool; } +# NOTE (johnemb, 2011-01-26): +# In this test mysqlhotcopy (a perl script) is executed as a standalone +# executable, i.e. not necessarily using the perl interpreter in PATH, +# because that is how the documentation demonstrates it. +# +# We include have_dbi_dbd-mysql.inc above so that the test will +# be skipped if Perl modules required by the mysqlhotcopy tool are not +# found when the script is run this way. + + let $MYSQLD_DATADIR= `SELECT @@datadir`; --disable_warnings DROP DATABASE IF EXISTS hotcopy_test; @@ -93,7 +107,7 @@ DROP DATABASE hotcopy_save; --replace_result $MYSQLD_DATADIR MYSQLD_DATADIR --list_files $MYSQLD_DATADIR/hotcopy_save --replace_result $MASTER_MYSOCK MASTER_MYSOCK ---error 9,2304 +--error 9,11,2304 --exec $MYSQLHOTCOPY --quiet -S $MASTER_MYSOCK -u root hotcopy_test hotcopy_save --replace_result $MASTER_MYSOCK MASTER_MYSOCK --exec $MYSQLHOTCOPY --quiet --allowold -S $MASTER_MYSOCK -u root hotcopy_test hotcopy_save diff --git a/mysql-test/include/rpl_sync.inc b/mysql-test/include/rpl_sync.inc index a05bee23981..be2904528ff 100644 --- a/mysql-test/include/rpl_sync.inc +++ b/mysql-test/include/rpl_sync.inc @@ -88,7 +88,7 @@ while ($_rpl_i) { { --echo Sync IO: $_rpl_slave_io_running; Sync SQL: $_rpl_slave_sql_running } - --let $_rpl_slave_io_running= `SELECT IF('$_rpl_slave_io_running' = 'Yes', 1, '')` + --let $_rpl_slave_io_running= `SELECT IF('$_rpl_slave_io_running' != 'No', 1, '')` --let $_rpl_slave_sql_running= `SELECT IF('$_rpl_slave_sql_running' = 'Yes', 1, '')` if ($_rpl_slave_io_running) { diff --git a/mysql-test/mysql-test-run.pl b/mysql-test/mysql-test-run.pl index 1b4588a5dd9..2301b2444d3 100755 --- a/mysql-test/mysql-test-run.pl +++ b/mysql-test/mysql-test-run.pl @@ -182,6 +182,7 @@ my $opt_cursor_protocol; my $opt_view_protocol; our $opt_debug; +our $opt_debug_server; our @opt_cases; # The test cases names in argv our $opt_embedded_server; @@ -936,6 +937,7 @@ sub command_line_setup { # Debugging 'debug' => \$opt_debug, + 'debug-server' => \$opt_debug_server, 'gdb' => \$opt_gdb, 'client-gdb' => \$opt_client_gdb, 'manual-gdb' => \$opt_manual_gdb, @@ -1082,6 +1084,9 @@ sub command_line_setup { my $path_share= dirname($path_language); $path_charsetsdir= mtr_path_exists("$path_share/charsets"); + # --debug implies we run debug server + $opt_debug_server= 1 if $opt_debug; + if (using_extern()) { # Connect to the running mysqld and find out what it supports @@ -1712,7 +1717,7 @@ sub find_mysqld { my @mysqld_names= ("mysqld", "mysqld-max-nt", "mysqld-max", "mysqld-nt"); - if ( $opt_debug ){ + if ( $opt_debug_server ){ # Put mysqld-debug first in the list of binaries to look for mtr_verbose("Adding mysqld-debug first in list of binaries to look for"); unshift(@mysqld_names, "mysqld-debug"); @@ -1783,9 +1788,12 @@ sub executable_setup () { sub client_debug_arg($$) { my ($args, $client_name)= @_; + # Workaround for Bug #50627: drop any debug opt + return if $client_name =~ /^mysqlbinlog/; + if ( $opt_debug ) { mtr_add_arg($args, - "--debug=d:t:A,%s/log/%s.trace", + "--loose-debug=d:t:A,%s/log/%s.trace", $path_vardir_trace, $client_name) } } @@ -2409,9 +2417,9 @@ sub check_debug_support ($) { #mtr_report(" - binaries are not debug compiled"); $debug_compiled_binaries= 0; - if ( $opt_debug ) + if ( $opt_debug_server ) { - mtr_error("Can't use --debug, binaries does not support it"); + mtr_error("Can't use --debug[-server], binary does not support it"); } return; } @@ -5589,6 +5597,8 @@ Options for debugging the product client-gdb Start mysqltest client in gdb ddd Start mysqld in ddd debug Dump trace output for all servers and client programs + debug-server Use debug version of server, but without turning on + tracing debugger=NAME Start mysqld in the selected debugger gdb Start the mysqld(s) in gdb manual-debug Let user manually start mysqld in debugger, before diff --git a/mysql-test/r/commit_1innodb.result b/mysql-test/r/commit_1innodb.result index 1f0b2c8019b..d21af5ef555 100644 --- a/mysql-test/r/commit_1innodb.result +++ b/mysql-test/r/commit_1innodb.result @@ -518,21 +518,21 @@ SUCCESS # 12. Read-write statement: IODKU, change 0 rows. # insert t1 set a=2 on duplicate key update a=2; -call p_verify_status_increment(1, 0, 1, 0); +call p_verify_status_increment(2, 2, 1, 0); SUCCESS commit; -call p_verify_status_increment(1, 0, 1, 0); +call p_verify_status_increment(2, 2, 1, 0); SUCCESS # 13. Read-write statement: INSERT IGNORE, change 0 rows. # insert ignore t1 set a=2; -call p_verify_status_increment(1, 0, 1, 0); +call p_verify_status_increment(2, 2, 1, 0); SUCCESS commit; -call p_verify_status_increment(1, 0, 1, 0); +call p_verify_status_increment(2, 2, 1, 0); SUCCESS # 14. Read-write statement: INSERT IGNORE, change 1 row. diff --git a/mysql-test/r/csv_not_null.result b/mysql-test/r/csv_not_null.result index af583a36837..aed9bcb1587 100644 --- a/mysql-test/r/csv_not_null.result +++ b/mysql-test/r/csv_not_null.result @@ -19,13 +19,16 @@ INSERT INTO t1 VALUES(); SELECT * FROM t1; a b c d e f 0 foo 0000-00-00 +INSERT INTO t1 VALUES(default,default,default,default,default,default); SELECT * FROM t1; a b c d e f 0 foo 0000-00-00 +0 foo 0000-00-00 INSERT INTO t1 VALUES(0,'abc','def','ghi','bar','1999-12-31'); SELECT * FROM t1; a b c d e f 0 foo 0000-00-00 +0 foo 0000-00-00 0 abc def ghi bar 1999-12-31 # === insert failures === INSERT INTO t1 VALUES(NULL,'ab','a','b','foo','2007-01-01'); diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result index 48e837f180f..0d4ce9414e5 100644 --- a/mysql-test/r/func_time.result +++ b/mysql-test/r/func_time.result @@ -1301,6 +1301,24 @@ SELECT '2008-02-18' + INTERVAL 1 FRAC_SECOND; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FRAC_SECOND' at line 1 SELECT '2008-02-18' - INTERVAL 1 FRAC_SECOND; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FRAC_SECOND' at line 1 +# +# Bug #52315 part 2 : utc_date() crashes when system time > year 2037 +# +SET TIMESTAMP=-147490000; +SELECT UTC_TIMESTAMP(); +SET TIMESTAMP=2147483648; +SELECT UTC_TIMESTAMP(); +SET TIMESTAMP=2147483646; +SELECT UTC_TIMESTAMP(); +SET TIMESTAMP=2147483647; +SELECT UTC_TIMESTAMP(); +SET TIMESTAMP=0; +SELECT UTC_TIMESTAMP(); +SET TIMESTAMP=-1; +SELECT UTC_TIMESTAMP(); +SET TIMESTAMP=1; +SELECT UTC_TIMESTAMP(); +SET TIMESTAMP=0; End of 5.0 tests select date_sub("0050-01-01 00:00:01",INTERVAL 2 SECOND); date_sub("0050-01-01 00:00:01",INTERVAL 2 SECOND) diff --git a/mysql-test/r/gis.result b/mysql-test/r/gis.result index f4aa361ffcf..a9beb9631ae 100644 --- a/mysql-test/r/gis.result +++ b/mysql-test/r/gis.result @@ -960,6 +960,18 @@ COUNT(*) 2 DROP TABLE t1, t2; End of 5.0 tests +# +# Test for bug #58650 "Failing assertion: primary_key_no == -1 || +# primary_key_no == 0". +# +drop table if exists t1; +# The minimal test case. +create table t1 (a int not null, b linestring not null, unique key b (b(12)), unique key a (a)); +drop table t1; +# The original test case. +create table t1 (a int not null, b linestring not null, unique key b (b(12))); +create unique index a on t1(a); +drop table t1; create table `t1` (`col002` point)engine=myisam; insert into t1 values (),(),(); select min(`col002`) from t1 union select `col002` from t1; diff --git a/mysql-test/r/not_embedded_server.result b/mysql-test/r/not_embedded_server.result index 60c92bd0196..7219e29b2a6 100644 --- a/mysql-test/r/not_embedded_server.result +++ b/mysql-test/r/not_embedded_server.result @@ -4,3 +4,7 @@ select 1; SHOW VARIABLES like 'slave_skip_errors'; Variable_name Value slave_skip_errors OFF +# +# Bug#58026: massive recursion and crash in regular expression handling +# +SELECT '1' RLIKE RPAD('1', 10000, '('); diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result index ba639fa9763..30879af418a 100644 --- a/mysql-test/r/order_by.result +++ b/mysql-test/r/order_by.result @@ -1638,4 +1638,29 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL a 8 NULL 10 Using index; Using temporary; Using filesort 1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 Using where DROP TABLE t1, t2; +# +# Bug #59110: Memory leak of QUICK_SELECT_I allocated memory +# and +# Bug #59308: Incorrect result for +SELECT DISTINCT <col>... ORDER BY <col> DESC + +# Use Valgrind to detect #59110! +# +CREATE TABLE t1 (a INT,KEY (a)); +INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10); +EXPLAIN SELECT DISTINCT a,1 FROM t1 WHERE a <> 1 ORDER BY a DESC; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index a a 5 NULL 10 Using where; Using index; Using filesort +SELECT DISTINCT a,1 FROM t1 WHERE a <> 1 ORDER BY a DESC; +a 1 +10 1 +9 1 +8 1 +7 1 +6 1 +5 1 +4 1 +3 1 +2 1 +DROP TABLE t1; End of 5.1 tests diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result index d989896514c..ae63edf87b9 100644 --- a/mysql-test/r/range.result +++ b/mysql-test/r/range.result @@ -1666,4 +1666,105 @@ c_key c_notkey 1 1 3 3 DROP TABLE t1; +# +# Bug #57030: 'BETWEEN' evaluation is incorrect +# +CREATE TABLE t1(pk INT PRIMARY KEY, i4 INT); +CREATE UNIQUE INDEX i4_uq ON t1(i4); +INSERT INTO t1 VALUES (1,10), (2,20), (3,30); +EXPLAIN +SELECT * FROM t1 WHERE i4 BETWEEN 10 AND 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 const i4_uq i4_uq 5 const 1 +SELECT * FROM t1 WHERE i4 BETWEEN 10 AND 10; +pk i4 +1 10 +EXPLAIN +SELECT * FROM t1 WHERE 10 BETWEEN i4 AND i4; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 const i4_uq i4_uq 5 const 1 +SELECT * FROM t1 WHERE 10 BETWEEN i4 AND i4; +pk i4 +1 10 +EXPLAIN +SELECT * FROM t1 WHERE 10 BETWEEN 10 AND i4; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range i4_uq i4_uq 5 NULL 3 Using where +SELECT * FROM t1 WHERE 10 BETWEEN 10 AND i4; +pk i4 +1 10 +2 20 +3 30 +EXPLAIN +SELECT * FROM t1 WHERE 10 BETWEEN i4 AND 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range i4_uq i4_uq 5 NULL 1 Using where +SELECT * FROM t1 WHERE 10 BETWEEN i4 AND 10; +pk i4 +1 10 +EXPLAIN +SELECT * FROM t1 WHERE 10 BETWEEN 10 AND 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 +SELECT * FROM t1 WHERE 10 BETWEEN 10 AND 10; +pk i4 +1 10 +2 20 +3 30 +EXPLAIN +SELECT * FROM t1 WHERE 10 BETWEEN 11 AND 11; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +SELECT * FROM t1 WHERE 10 BETWEEN 11 AND 11; +pk i4 +EXPLAIN +SELECT * FROM t1 WHERE 10 BETWEEN 100 AND 0; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +SELECT * FROM t1 WHERE 10 BETWEEN 100 AND 0; +pk i4 +EXPLAIN +SELECT * FROM t1 WHERE i4 BETWEEN 100 AND 0; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +SELECT * FROM t1 WHERE i4 BETWEEN 100 AND 0; +pk i4 +EXPLAIN +SELECT * FROM t1 WHERE i4 BETWEEN 10 AND 99999999999999999; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range i4_uq i4_uq 5 NULL 2 Using where +SELECT * FROM t1 WHERE i4 BETWEEN 10 AND 99999999999999999; +pk i4 +1 10 +2 20 +3 30 +EXPLAIN +SELECT * FROM t1 WHERE i4 BETWEEN 999999999999999 AND 30; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +SELECT * FROM t1 WHERE i4 BETWEEN 999999999999999 AND 30; +pk i4 +EXPLAIN +SELECT * FROM t1 WHERE i4 BETWEEN 10 AND '20'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range i4_uq i4_uq 5 NULL 1 Using where +SELECT * FROM t1 WHERE i4 BETWEEN 10 AND '20'; +pk i4 +1 10 +2 20 +EXPLAIN +SELECT * FROM t1, t1 as t2 WHERE t2.pk BETWEEN t1.i4 AND t1.i4; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL i4_uq NULL NULL NULL 3 +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.i4 1 Using where +SELECT * FROM t1, t1 as t2 WHERE t2.pk BETWEEN t1.i4 AND t1.i4; +pk i4 pk i4 +EXPLAIN +SELECT * FROM t1, t1 as t2 WHERE t1.i4 BETWEEN t2.pk AND t2.pk; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL i4_uq NULL NULL NULL 3 +1 SIMPLE t2 eq_ref PRIMARY PRIMARY 4 test.t1.i4 1 Using where +SELECT * FROM t1, t1 as t2 WHERE t1.i4 BETWEEN t2.pk AND t2.pk; +pk i4 pk i4 +DROP TABLE t1; End of 5.1 tests diff --git a/mysql-test/std_data/checkDBI_DBD-mysql.pl b/mysql-test/std_data/checkDBI_DBD-mysql.pl new file mode 100644 index 00000000000..f001e471081 --- /dev/null +++ b/mysql-test/std_data/checkDBI_DBD-mysql.pl @@ -0,0 +1,97 @@ +#!/usr/bin/perl + +# Copyright (c) 2011, 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 Library General Public +# License as published by the Free Software Foundation; version 2 +# of the License. +# +# This program is distributed in the hope that it will be useful, +# but WITHOUT ANY WARRANTY; without even the implied warranty of +# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU +# Library General Public License for more details. +# +# You should have received a copy of the GNU General Public License +# along with this program; if not, write to the Free Software +# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA + + +################################################################################ +# +# This perl script checks for availability of the Perl modules DBI and +# DBD::mysql using the "current" perl interpreter. +# +# Useful for test environment checking before testing executable perl scripts +# in the MySQL Server distribution. +# +# NOTE: The "shebang" on the first line of this script should always point to +# /usr/bin/perl, so that we can use this script to check whether or not we +# support running perl scripts with such a shebang without specifying the +# perl interpreter on the command line. Such a script is mysqlhotcopy. +# +# When run as "checkDBI_DBD-mysql.pl" the shebang line will be evaluated +# and used. When run as "perl checkDBI_DBD-mysql.pl" the shebang line is +# not used. +# +# NOTE: This script will create a temporary file in MTR's tmp dir. +# If modules are found, a mysql-test statement which sets a special +# variable is written to this file. If one of the modules is not found +# (or cannot be loaded), the file will remain empty. +# A test (or include file) which sources that file can then easily do +# an if-check on the special variable to determine success or failure. +# +# Example: +# +# --let $perlChecker= $MYSQLTEST_VARDIR/std_data/checkDBI_DBD-mysql.pl +# --let $resultFile= $MYSQL_TMP_DIR/dbidbd-mysql.txt +# --chmod 0755 $perlChecker +# --exec $perlChecker +# --source $resultFile +# if (!$dbidbd) { +# --skip Test needs Perl modules DBI and DBD::mysql +# } +# +# The calling script is also responsible for cleaning up after use: +# +# --remove_file $resultFile +# +# Windows notes: +# - shebangs may work differently - call this script with "perl " in front. +# +# See mysql-test/include/have_dbi_dbd-mysql.inc for example use of this script. +# This script should be executable for the user running MTR. +# +################################################################################ + +BEGIN { + # By using eval inside BEGIN we can suppress warnings and continue after. + # We need to catch "Can't locate" as well as "Can't load" errors. + eval{ + $FOUND_DBI=0; + $FOUND_DBD_MYSQL=0; + + # Check for DBI module: + $FOUND_DBI=1 if require DBI; + + # Check for DBD::mysql module + $FOUND_DBD_MYSQL=1 if require DBD::mysql; + }; +}; + +# Open a file to be used for transfer of result back to mysql-test. +# The file must be created whether we write to it or not, otherwise mysql-test +# will complain if trying to source it. +# An empty file indicates failure to load modules. +open(FILE, ">", $ENV{'MYSQL_TMP_DIR'}.'/dbidbd-mysql.txt'); + +if ($FOUND_DBI && $FOUND_DBD_MYSQL) { + # write a mysql-test command setting a variable to indicate success + print(FILE 'let $dbidbd= FOUND_DBI_DBD-MYSQL;'."\n"); +} + +# close the file. +close(FILE); + +1; + diff --git a/mysql-test/suite/engines/funcs/r/ps_string_not_null.result b/mysql-test/suite/engines/funcs/r/ps_string_not_null.result Binary files differindex 859fab8b490..5f2a630811c 100644 --- a/mysql-test/suite/engines/funcs/r/ps_string_not_null.result +++ b/mysql-test/suite/engines/funcs/r/ps_string_not_null.result diff --git a/mysql-test/suite/engines/funcs/t/ps_string_not_null.test b/mysql-test/suite/engines/funcs/t/ps_string_not_null.test index f9e937cb24d..662adfd7a88 100644 --- a/mysql-test/suite/engines/funcs/t/ps_string_not_null.test +++ b/mysql-test/suite/engines/funcs/t/ps_string_not_null.test @@ -1,5 +1,5 @@ --disable_warnings -DROP TABLE IF EXISTS t2; +DROP TABLE IF EXISTS t1; --enable_warnings CREATE TABLE t1(c1 CHAR(100) NOT NULL); PREPARE stmt1 FROM 'INSERT INTO t1 (c1) VALUES(?)'; diff --git a/mysql-test/suite/innodb/r/innodb_bug30423.result b/mysql-test/suite/innodb/r/innodb_bug30423.result new file mode 100644 index 00000000000..a19809366ae --- /dev/null +++ b/mysql-test/suite/innodb/r/innodb_bug30423.result @@ -0,0 +1,95 @@ +set global innodb_stats_method = default; +select @@innodb_stats_method; +@@innodb_stats_method +nulls_equal +select count(*) from bug30243_3 where org_id is not NULL; +count(*) +20 +select count(*) from bug30243_3 where org_id is NULL; +count(*) +16384 +select count(*) from bug30243_2 where org_id is not NULL; +count(*) +224 +select count(*) from bug30243_2 where org_id is NULL; +count(*) +65536 +select @@innodb_stats_method; +@@innodb_stats_method +nulls_equal +analyze table bug30243_1; +Table Op Msg_type Msg_text +test.bug30243_1 analyze status OK +analyze table bug30243_2; +Table Op Msg_type Msg_text +test.bug30243_2 analyze status OK +analyze table bug30243_3; +Table Op Msg_type Msg_text +test.bug30243_3 analyze status OK +set global innodb_stats_method = "NULL"; +ERROR 42000: Variable 'stats_method' can't be set to the value of 'NULL' +set global innodb_stats_method = "nulls_ignored"; +select @@innodb_stats_method; +@@innodb_stats_method +nulls_ignored +analyze table bug30243_1; +Table Op Msg_type Msg_text +test.bug30243_1 analyze status OK +analyze table bug30243_2; +Table Op Msg_type Msg_text +test.bug30243_2 analyze status OK +analyze table bug30243_3; +Table Op Msg_type Msg_text +test.bug30243_3 analyze status OK +explain SELECT COUNT(*), 0 +FROM bug30243_1 orgs +LEFT JOIN bug30243_3 sa_opportunities +ON orgs.org_id=sa_opportunities.org_id +LEFT JOIN bug30243_2 contacts +ON orgs.org_id=contacts.org_id ; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE orgs index NULL org_id 4 NULL 128 Using index +1 SIMPLE sa_opportunities ref org_id org_id 5 test.orgs.org_id 1 Using index +1 SIMPLE contacts ref contacts$org_id contacts$org_id 5 test.orgs.org_id 1 Using index +select @@innodb_stats_method; +@@innodb_stats_method +nulls_ignored +set global innodb_stats_method = "nulls_unequal"; +select @@innodb_stats_method; +@@innodb_stats_method +nulls_unequal +analyze table bug30243_1; +Table Op Msg_type Msg_text +test.bug30243_1 analyze status OK +analyze table bug30243_2; +Table Op Msg_type Msg_text +test.bug30243_2 analyze status OK +analyze table bug30243_3; +Table Op Msg_type Msg_text +test.bug30243_3 analyze status OK +explain SELECT COUNT(*), 0 +FROM bug30243_1 orgs +LEFT JOIN bug30243_3 sa_opportunities +ON orgs.org_id=sa_opportunities.org_id +LEFT JOIN bug30243_2 contacts +ON orgs.org_id=contacts.org_id; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE orgs index NULL org_id 4 NULL 128 Using index +1 SIMPLE sa_opportunities ref org_id org_id 5 test.orgs.org_id 1 Using index +1 SIMPLE contacts ref contacts$org_id contacts$org_id 5 test.orgs.org_id 1 Using index +SELECT COUNT(*) FROM table_bug30423 WHERE org_id IS NULL; +COUNT(*) +1024 +set global innodb_stats_method = "nulls_unequal"; +analyze table table_bug30423; +Table Op Msg_type Msg_text +test.table_bug30423 analyze status OK +set global innodb_stats_method = "nulls_ignored"; +analyze table table_bug30423; +Table Op Msg_type Msg_text +test.table_bug30423 analyze status OK +set global innodb_stats_method = nulls_equal; +drop table bug30243_2; +drop table bug30243_1; +drop table bug30243_3; +drop table table_bug30423; diff --git a/mysql-test/suite/innodb/r/innodb_gis.result b/mysql-test/suite/innodb/r/innodb_gis.result index c6c775afc9f..a52a1387b6e 100644 --- a/mysql-test/suite/innodb/r/innodb_gis.result +++ b/mysql-test/suite/innodb/r/innodb_gis.result @@ -585,5 +585,17 @@ COUNT(*) 2 DROP TABLE t1, t2; End of 5.0 tests +# +# Test for bug #58650 "Failing assertion: primary_key_no == -1 || +# primary_key_no == 0". +# +drop table if exists t1; +# The minimal test case. +create table t1 (a int not null, b linestring not null, unique key b (b(12)), unique key a (a)); +drop table t1; +# The original test case. +create table t1 (a int not null, b linestring not null, unique key b (b(12))); +create unique index a on t1(a); +drop table t1; create table t1 (g geometry not null, spatial gk(g)) engine=innodb; ERROR HY000: The used table type doesn't support SPATIAL indexes diff --git a/mysql-test/suite/innodb/t/innodb_bug30423.test b/mysql-test/suite/innodb/t/innodb_bug30423.test new file mode 100644 index 00000000000..f2a3ee8d099 --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb_bug30423.test @@ -0,0 +1,211 @@ +# Test for Bug #30423, InnoDBs treatment of NULL in index stats causes +# bad "rows examined" estimates. +# Implemented InnoDB system variable "innodb_stats_method" with +# "nulls_equal" (default), "nulls_unequal", and "nulls_ignored" options. + +-- source include/have_innodb.inc + +let $innodb_stats_method_orig = `select @@innodb_stats_method`; + +# default setting for innodb_stats_method is "nulls_equal" +set global innodb_stats_method = default; + +select @@innodb_stats_method; + +# create three tables, bug30243_1, bug30243_2 and bug30243_3. +# The test scenario is adopted from original bug #30423 report. +# table bug30243_1 and bug30243_3 have many NULL values + +-- disable_result_log +-- disable_query_log + +DROP TABLE IF EXISTS bug30243_1; +CREATE TABLE bug30243_1 ( + org_id int(11) NOT NULL default '0', + UNIQUE KEY (org_id) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +LOCK TABLES bug30243_1 WRITE; +INSERT INTO bug30243_1 VALUES (11),(15),(16),(17),(19),(20),(21),(23),(24), +(25),(26),(27),(28),(29),(30),(31),(32),(33),(34),(35),(37),(38),(40),(41), +(42),(43),(44),(45),(46),(47),(48),(49),(50),(51),(52),(53),(54),(55),(56), +(57),(58),(59),(60),(61),(62),(63),(64),(65),(66),(67),(68),(69),(70),(71), +(72),(73),(74),(75),(76),(77),(78),(79),(80),(81),(82),(83),(84),(85),(86), +(87),(88),(89),(90),(91),(92),(93),(94),(95),(96),(97),(98),(99),(100),(101), +(102),(103),(104),(105),(106),(107),(108),(109),(110),(111),(112),(113),(114), +(115),(116),(117),(118),(119),(120),(121),(122),(123),(124),(125),(126),(127), +(128),(129),(130),(131),(132),(133),(134),(135),(136),(137),(138),(139),(140), +(141),(142),(143),(144),(145); +UNLOCK TABLES; + +DROP TABLE IF EXISTS bug30243_3; +CREATE TABLE bug30243_3 ( + org_id int(11) default NULL, + KEY (org_id) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +INSERT INTO bug30243_3 VALUES (NULL); + +begin; +let $i=14; +while ($i) +{ + INSERT INTO bug30243_3 SELECT NULL FROM bug30243_3; + dec $i; +} + +INSERT INTO bug30243_3 VALUES (34),(34),(35),(56),(58),(62),(62),(64),(65),(66),(80),(135),(137),(138),(139),(140),(142),(143),(144),(145); +commit; + +DROP TABLE IF EXISTS bug30243_2; +CREATE TABLE bug30243_2 ( + org_id int(11) default NULL, + KEY `contacts$org_id` (org_id) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +INSERT INTO bug30243_2 VALUES (NULL); + +begin; +let $i=16; +while ($i) +{ + INSERT INTO bug30243_2 SELECT NULL FROM bug30243_2; + dec $i; +} + +INSERT INTO bug30243_2 VALUES (11),(15),(16),(17),(20),(21),(23),(24),(25), +(26),(27),(28),(29),(30),(31),(32),(33),(34),(37),(38),(40),(41),(42),(43), +(44),(45),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46), +(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46), +(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46), +(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46), +(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46), +(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(48), +(48),(50),(51),(52),(52),(53),(54),(55),(57),(60),(61),(62),(62),(62),(62), +(62),(63),(64),(64),(65),(66),(66),(67),(68),(69),(70),(71),(72),(73),(74), +(75),(76),(77),(78),(79),(80),(80),(81),(82),(83),(84),(85),(86),(87),(88), +(89),(90),(91),(92),(93),(94),(95),(96),(97),(98),(99),(100),(101),(102), +(103),(104),(105),(106),(107),(108),(109),(110),(111),(112),(113),(114), +(115),(116),(117),(118),(119),(120),(121),(122),(123),(124),(125),(126), +(127),(128),(129),(130),(131),(132),(133),(133),(135),(135),(135),(135), +(136),(136),(138),(138),(139),(139),(139),(140),(141),(141),(142),(143), +(143),(145),(145); +commit; + + +-- enable_result_log +-- enable_query_log + +# check tables's value +select count(*) from bug30243_3 where org_id is not NULL; +select count(*) from bug30243_3 where org_id is NULL; + +select count(*) from bug30243_2 where org_id is not NULL; +select count(*) from bug30243_2 where org_id is NULL; + +select @@innodb_stats_method; + +analyze table bug30243_1; +analyze table bug30243_2; +analyze table bug30243_3; + +# Following query plan shows that we over estimate the rows per +# unique value (since there are many NULLs). +# Skip this query log since the stats estimate could vary from runs +-- disable_query_log +-- disable_result_log +explain SELECT COUNT(*), 0 + FROM bug30243_1 orgs + LEFT JOIN bug30243_3 sa_opportunities + ON orgs.org_id=sa_opportunities.org_id + LEFT JOIN bug30243_2 contacts + ON orgs.org_id=contacts.org_id ; +-- enable_query_log +-- enable_result_log + +# following set operation will fail +#--error ER_WRONG_VALUE_FOR_VAR +--error 1231 +set global innodb_stats_method = "NULL"; + +set global innodb_stats_method = "nulls_ignored"; + +select @@innodb_stats_method; + +# Regenerate the stats with "nulls_ignored" option + +analyze table bug30243_1; +analyze table bug30243_2; +analyze table bug30243_3; + +# Following query plan shows that we get the correct rows per +# unique value (should be approximately 1 row per value) +explain SELECT COUNT(*), 0 + FROM bug30243_1 orgs + LEFT JOIN bug30243_3 sa_opportunities + ON orgs.org_id=sa_opportunities.org_id + LEFT JOIN bug30243_2 contacts + ON orgs.org_id=contacts.org_id ; + +select @@innodb_stats_method; + +# Try the "nulls_unequal" option +set global innodb_stats_method = "nulls_unequal"; + +select @@innodb_stats_method; + +analyze table bug30243_1; +analyze table bug30243_2; +analyze table bug30243_3; + +# Following query plan shows that we get the correct rows per +# unique value (~1) +explain SELECT COUNT(*), 0 + FROM bug30243_1 orgs + LEFT JOIN bug30243_3 sa_opportunities + ON orgs.org_id=sa_opportunities.org_id + LEFT JOIN bug30243_2 contacts + ON orgs.org_id=contacts.org_id; + + +# Create a table with all NULL values, make sure the stats calculation +# does not crash with table of all NULL values +-- disable_query_log +CREATE TABLE table_bug30423 ( + org_id int(11) default NULL, + KEY(org_id) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +INSERT INTO `table_bug30423` VALUES (NULL); + +begin; +let $i=10; +while ($i) +{ + INSERT INTO table_bug30423 SELECT NULL FROM table_bug30423; + dec $i; +} +commit; + +-- enable_query_log + +SELECT COUNT(*) FROM table_bug30423 WHERE org_id IS NULL; + +# calculate the statistics for the table for "nulls_ignored" and +# "nulls_unequal" option +set global innodb_stats_method = "nulls_unequal"; +analyze table table_bug30423; + +set global innodb_stats_method = "nulls_ignored"; +analyze table table_bug30423; + + +eval set global innodb_stats_method = $innodb_stats_method_orig; + +drop table bug30243_2; + +drop table bug30243_1; + +drop table bug30243_3; + +drop table table_bug30423; diff --git a/mysql-test/suite/innodb/t/innodb_bug56143.test b/mysql-test/suite/innodb/t/innodb_bug56143.test index 1218ae6621c..b69d0048ee8 100644 --- a/mysql-test/suite/innodb/t/innodb_bug56143.test +++ b/mysql-test/suite/innodb/t/innodb_bug56143.test @@ -8,6 +8,11 @@ -- disable_query_log -- disable_result_log +if ($VALGRIND_TEST) +{ + call mtr.add_suppression("InnoDB: Warning: a long semaphore wait:"); +} + SET foreign_key_checks=0; DROP TABLE IF EXISTS bug56143; CREATE TABLE `bug56143` ( diff --git a/mysql-test/suite/innodb_plugin/r/innodb_bug30423.result b/mysql-test/suite/innodb_plugin/r/innodb_bug30423.result new file mode 100644 index 00000000000..a19809366ae --- /dev/null +++ b/mysql-test/suite/innodb_plugin/r/innodb_bug30423.result @@ -0,0 +1,95 @@ +set global innodb_stats_method = default; +select @@innodb_stats_method; +@@innodb_stats_method +nulls_equal +select count(*) from bug30243_3 where org_id is not NULL; +count(*) +20 +select count(*) from bug30243_3 where org_id is NULL; +count(*) +16384 +select count(*) from bug30243_2 where org_id is not NULL; +count(*) +224 +select count(*) from bug30243_2 where org_id is NULL; +count(*) +65536 +select @@innodb_stats_method; +@@innodb_stats_method +nulls_equal +analyze table bug30243_1; +Table Op Msg_type Msg_text +test.bug30243_1 analyze status OK +analyze table bug30243_2; +Table Op Msg_type Msg_text +test.bug30243_2 analyze status OK +analyze table bug30243_3; +Table Op Msg_type Msg_text +test.bug30243_3 analyze status OK +set global innodb_stats_method = "NULL"; +ERROR 42000: Variable 'stats_method' can't be set to the value of 'NULL' +set global innodb_stats_method = "nulls_ignored"; +select @@innodb_stats_method; +@@innodb_stats_method +nulls_ignored +analyze table bug30243_1; +Table Op Msg_type Msg_text +test.bug30243_1 analyze status OK +analyze table bug30243_2; +Table Op Msg_type Msg_text +test.bug30243_2 analyze status OK +analyze table bug30243_3; +Table Op Msg_type Msg_text +test.bug30243_3 analyze status OK +explain SELECT COUNT(*), 0 +FROM bug30243_1 orgs +LEFT JOIN bug30243_3 sa_opportunities +ON orgs.org_id=sa_opportunities.org_id +LEFT JOIN bug30243_2 contacts +ON orgs.org_id=contacts.org_id ; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE orgs index NULL org_id 4 NULL 128 Using index +1 SIMPLE sa_opportunities ref org_id org_id 5 test.orgs.org_id 1 Using index +1 SIMPLE contacts ref contacts$org_id contacts$org_id 5 test.orgs.org_id 1 Using index +select @@innodb_stats_method; +@@innodb_stats_method +nulls_ignored +set global innodb_stats_method = "nulls_unequal"; +select @@innodb_stats_method; +@@innodb_stats_method +nulls_unequal +analyze table bug30243_1; +Table Op Msg_type Msg_text +test.bug30243_1 analyze status OK +analyze table bug30243_2; +Table Op Msg_type Msg_text +test.bug30243_2 analyze status OK +analyze table bug30243_3; +Table Op Msg_type Msg_text +test.bug30243_3 analyze status OK +explain SELECT COUNT(*), 0 +FROM bug30243_1 orgs +LEFT JOIN bug30243_3 sa_opportunities +ON orgs.org_id=sa_opportunities.org_id +LEFT JOIN bug30243_2 contacts +ON orgs.org_id=contacts.org_id; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE orgs index NULL org_id 4 NULL 128 Using index +1 SIMPLE sa_opportunities ref org_id org_id 5 test.orgs.org_id 1 Using index +1 SIMPLE contacts ref contacts$org_id contacts$org_id 5 test.orgs.org_id 1 Using index +SELECT COUNT(*) FROM table_bug30423 WHERE org_id IS NULL; +COUNT(*) +1024 +set global innodb_stats_method = "nulls_unequal"; +analyze table table_bug30423; +Table Op Msg_type Msg_text +test.table_bug30423 analyze status OK +set global innodb_stats_method = "nulls_ignored"; +analyze table table_bug30423; +Table Op Msg_type Msg_text +test.table_bug30423 analyze status OK +set global innodb_stats_method = nulls_equal; +drop table bug30243_2; +drop table bug30243_1; +drop table bug30243_3; +drop table table_bug30423; diff --git a/mysql-test/suite/innodb_plugin/r/innodb_gis.result b/mysql-test/suite/innodb_plugin/r/innodb_gis.result index c6c775afc9f..a52a1387b6e 100644 --- a/mysql-test/suite/innodb_plugin/r/innodb_gis.result +++ b/mysql-test/suite/innodb_plugin/r/innodb_gis.result @@ -585,5 +585,17 @@ COUNT(*) 2 DROP TABLE t1, t2; End of 5.0 tests +# +# Test for bug #58650 "Failing assertion: primary_key_no == -1 || +# primary_key_no == 0". +# +drop table if exists t1; +# The minimal test case. +create table t1 (a int not null, b linestring not null, unique key b (b(12)), unique key a (a)); +drop table t1; +# The original test case. +create table t1 (a int not null, b linestring not null, unique key b (b(12))); +create unique index a on t1(a); +drop table t1; create table t1 (g geometry not null, spatial gk(g)) engine=innodb; ERROR HY000: The used table type doesn't support SPATIAL indexes diff --git a/mysql-test/suite/innodb_plugin/t/innodb_bug30423.test b/mysql-test/suite/innodb_plugin/t/innodb_bug30423.test new file mode 100644 index 00000000000..458c2967e19 --- /dev/null +++ b/mysql-test/suite/innodb_plugin/t/innodb_bug30423.test @@ -0,0 +1,211 @@ +# Test for Bug #30423, InnoDBs treatment of NULL in index stats causes +# bad "rows examined" estimates. +# Implemented InnoDB system variable "innodb_stats_method" with +# "nulls_equal" (default), "nulls_unequal", and "nulls_ignored" options. + +-- source include/have_innodb_plugin.inc + +let $innodb_stats_method_orig = `select @@innodb_stats_method`; + +# default setting for innodb_stats_method is "nulls_equal" +set global innodb_stats_method = default; + +select @@innodb_stats_method; + +# create three tables, bug30243_1, bug30243_2 and bug30243_3. +# The test scenario is adopted from original bug #30423 report. +# table bug30243_1 and bug30243_3 have many NULL values + +-- disable_result_log +-- disable_query_log + +DROP TABLE IF EXISTS bug30243_1; +CREATE TABLE bug30243_1 ( + org_id int(11) NOT NULL default '0', + UNIQUE KEY (org_id) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +LOCK TABLES bug30243_1 WRITE; +INSERT INTO bug30243_1 VALUES (11),(15),(16),(17),(19),(20),(21),(23),(24), +(25),(26),(27),(28),(29),(30),(31),(32),(33),(34),(35),(37),(38),(40),(41), +(42),(43),(44),(45),(46),(47),(48),(49),(50),(51),(52),(53),(54),(55),(56), +(57),(58),(59),(60),(61),(62),(63),(64),(65),(66),(67),(68),(69),(70),(71), +(72),(73),(74),(75),(76),(77),(78),(79),(80),(81),(82),(83),(84),(85),(86), +(87),(88),(89),(90),(91),(92),(93),(94),(95),(96),(97),(98),(99),(100),(101), +(102),(103),(104),(105),(106),(107),(108),(109),(110),(111),(112),(113),(114), +(115),(116),(117),(118),(119),(120),(121),(122),(123),(124),(125),(126),(127), +(128),(129),(130),(131),(132),(133),(134),(135),(136),(137),(138),(139),(140), +(141),(142),(143),(144),(145); +UNLOCK TABLES; + +DROP TABLE IF EXISTS bug30243_3; +CREATE TABLE bug30243_3 ( + org_id int(11) default NULL, + KEY (org_id) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +INSERT INTO bug30243_3 VALUES (NULL); + +begin; +let $i=14; +while ($i) +{ + INSERT INTO bug30243_3 SELECT NULL FROM bug30243_3; + dec $i; +} + +INSERT INTO bug30243_3 VALUES (34),(34),(35),(56),(58),(62),(62),(64),(65),(66),(80),(135),(137),(138),(139),(140),(142),(143),(144),(145); +commit; + +DROP TABLE IF EXISTS bug30243_2; +CREATE TABLE bug30243_2 ( + org_id int(11) default NULL, + KEY `contacts$org_id` (org_id) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +INSERT INTO bug30243_2 VALUES (NULL); + +begin; +let $i=16; +while ($i) +{ + INSERT INTO bug30243_2 SELECT NULL FROM bug30243_2; + dec $i; +} + +INSERT INTO bug30243_2 VALUES (11),(15),(16),(17),(20),(21),(23),(24),(25), +(26),(27),(28),(29),(30),(31),(32),(33),(34),(37),(38),(40),(41),(42),(43), +(44),(45),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46), +(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46), +(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46), +(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46), +(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46), +(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(46),(48), +(48),(50),(51),(52),(52),(53),(54),(55),(57),(60),(61),(62),(62),(62),(62), +(62),(63),(64),(64),(65),(66),(66),(67),(68),(69),(70),(71),(72),(73),(74), +(75),(76),(77),(78),(79),(80),(80),(81),(82),(83),(84),(85),(86),(87),(88), +(89),(90),(91),(92),(93),(94),(95),(96),(97),(98),(99),(100),(101),(102), +(103),(104),(105),(106),(107),(108),(109),(110),(111),(112),(113),(114), +(115),(116),(117),(118),(119),(120),(121),(122),(123),(124),(125),(126), +(127),(128),(129),(130),(131),(132),(133),(133),(135),(135),(135),(135), +(136),(136),(138),(138),(139),(139),(139),(140),(141),(141),(142),(143), +(143),(145),(145); +commit; + + +-- enable_result_log +-- enable_query_log + +# check tables's value +select count(*) from bug30243_3 where org_id is not NULL; +select count(*) from bug30243_3 where org_id is NULL; + +select count(*) from bug30243_2 where org_id is not NULL; +select count(*) from bug30243_2 where org_id is NULL; + +select @@innodb_stats_method; + +analyze table bug30243_1; +analyze table bug30243_2; +analyze table bug30243_3; + +# Following query plan shows that we over estimate the rows per +# unique value (since there are many NULLs). +# Skip this query log since the stats estimate could vary from runs +-- disable_query_log +-- disable_result_log +explain SELECT COUNT(*), 0 + FROM bug30243_1 orgs + LEFT JOIN bug30243_3 sa_opportunities + ON orgs.org_id=sa_opportunities.org_id + LEFT JOIN bug30243_2 contacts + ON orgs.org_id=contacts.org_id ; +-- enable_query_log +-- enable_result_log + +# following set operation will fail +#--error ER_WRONG_VALUE_FOR_VAR +--error 1231 +set global innodb_stats_method = "NULL"; + +set global innodb_stats_method = "nulls_ignored"; + +select @@innodb_stats_method; + +# Regenerate the stats with "nulls_ignored" option + +analyze table bug30243_1; +analyze table bug30243_2; +analyze table bug30243_3; + +# Following query plan shows that we get the correct rows per +# unique value (should be approximately 1 row per value) +explain SELECT COUNT(*), 0 + FROM bug30243_1 orgs + LEFT JOIN bug30243_3 sa_opportunities + ON orgs.org_id=sa_opportunities.org_id + LEFT JOIN bug30243_2 contacts + ON orgs.org_id=contacts.org_id ; + +select @@innodb_stats_method; + +# Try the "nulls_unequal" option +set global innodb_stats_method = "nulls_unequal"; + +select @@innodb_stats_method; + +analyze table bug30243_1; +analyze table bug30243_2; +analyze table bug30243_3; + +# Following query plan shows that we get the correct rows per +# unique value (~1) +explain SELECT COUNT(*), 0 + FROM bug30243_1 orgs + LEFT JOIN bug30243_3 sa_opportunities + ON orgs.org_id=sa_opportunities.org_id + LEFT JOIN bug30243_2 contacts + ON orgs.org_id=contacts.org_id; + + +# Create a table with all NULL values, make sure the stats calculation +# does not crash with table of all NULL values +-- disable_query_log +CREATE TABLE table_bug30423 ( + org_id int(11) default NULL, + KEY(org_id) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +INSERT INTO `table_bug30423` VALUES (NULL); + +begin; +let $i=10; +while ($i) +{ + INSERT INTO table_bug30423 SELECT NULL FROM table_bug30423; + dec $i; +} +commit; + +-- enable_query_log + +SELECT COUNT(*) FROM table_bug30423 WHERE org_id IS NULL; + +# calculate the statistics for the table for "nulls_ignored" and +# "nulls_unequal" option +set global innodb_stats_method = "nulls_unequal"; +analyze table table_bug30423; + +set global innodb_stats_method = "nulls_ignored"; +analyze table table_bug30423; + + +eval set global innodb_stats_method = $innodb_stats_method_orig; + +drop table bug30243_2; + +drop table bug30243_1; + +drop table bug30243_3; + +drop table table_bug30423; diff --git a/mysql-test/suite/innodb_plugin/t/innodb_bug56143.test b/mysql-test/suite/innodb_plugin/t/innodb_bug56143.test index 7c7472303db..0f135a44f5d 100644 --- a/mysql-test/suite/innodb_plugin/t/innodb_bug56143.test +++ b/mysql-test/suite/innodb_plugin/t/innodb_bug56143.test @@ -8,6 +8,11 @@ -- disable_query_log -- disable_result_log +if ($VALGRIND_TEST) +{ + call mtr.add_suppression("InnoDB: Warning: a long semaphore wait:"); +} + SET foreign_key_checks=0; DROP TABLE IF EXISTS bug56143_1; diff --git a/mysql-test/suite/parts/inc/partition_check_drop.inc b/mysql-test/suite/parts/inc/partition_check_drop.inc index daaa5e541c7..137de4165a0 100644 --- a/mysql-test/suite/parts/inc/partition_check_drop.inc +++ b/mysql-test/suite/parts/inc/partition_check_drop.inc @@ -66,10 +66,10 @@ if ($found_garbage) } # Do a manual cleanup, because the following tests should not suffer from # remaining files - --exec rm -f $MYSQLD_DATADIR/test/t1* || true + --remove_files_wildcard $MYSQLD_DATADIR/test t1* if ($with_directories) { - --exec rm -f $MYSQLTEST_VARDIR/tmp/t1* || true + --remove_files_wildcard $MYSQLTEST_VARDIR/tmp t1* } } --enable_query_log diff --git a/mysql-test/suite/parts/inc/partition_layout_check1.inc b/mysql-test/suite/parts/inc/partition_layout_check1.inc index bca41b6f9c7..66a93d5d8d7 100644 --- a/mysql-test/suite/parts/inc/partition_layout_check1.inc +++ b/mysql-test/suite/parts/inc/partition_layout_check1.inc @@ -29,14 +29,10 @@ DELETE FROM t0_definition; let $MYSQLD_DATADIR= `select LEFT(@@datadir, LENGTH(@@datadir)-1)`; #echo MYSQLD_DATADIR: $MYSQLD_DATADIR; -# Dump the current definition of the table t1 to tmp1 -# This complicated method - let another mysqltest collect the output - is used -# because of two reasons +# Save the current definition of the table t1 # - SHOW CREATE TABLE t1 is at least currently most probably more reliable than # the corresponding SELECT on the INFORMATION_SCHEMA -# - SHOW CREATE TABLE .. cannot write its out put into a file like SELECT -let $show_file= $MYSQLD_DATADIR/test/tmp1; ---exec echo "SHOW CREATE TABLE t1; exit; " | $MYSQL_TEST > $show_file 2>&1 +let $show_create= `SHOW CREATE TABLE t1`; if ($do_file_tests) { # List the files belonging to the table t1 @@ -57,12 +53,13 @@ if (!$do_file_tests) # Insert the current definition of the table t1 into t0_definition eval INSERT INTO t0_definition SET state = 'old', - create_command = load_file('$show_file'), + create_command = "$show_create", file_list = @aux; # Print the create table statement into the protocol +# Added the concat to avoid changing the result files --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR '\r' '' -SELECT create_command FROM t0_definition WHERE state = 'old'; +SELECT concat('SHOW CREATE TABLE t1;\nTable\tCreate Table\n',create_command,'\n') as `create_command` FROM t0_definition WHERE state = 'old'; if ($do_file_tests) { # We stored the list of files, therefore printing the content makes sense diff --git a/mysql-test/suite/parts/inc/partition_layout_check2.inc b/mysql-test/suite/parts/inc/partition_layout_check2.inc index 7ff871a4c45..2f74455a811 100644 --- a/mysql-test/suite/parts/inc/partition_layout_check2.inc +++ b/mysql-test/suite/parts/inc/partition_layout_check2.inc @@ -28,9 +28,8 @@ DELETE FROM t0_definition WHERE state = 'new'; let $MYSQLD_DATADIR= `select LEFT(@@datadir, LENGTH(@@datadir)-1)`; #echo MYSQLD_DATADIR: $MYSQLD_DATADIR; -# Dump the current definition of the table t1 to tmp1 -let $show_file= $MYSQLD_DATADIR/test/tmp1; ---exec echo "SHOW CREATE TABLE t1; exit; " | $MYSQL_TEST > $show_file 2>&1 +# Save the current definition of the table t1 +let $show_create= `SHOW CREATE TABLE t1`; if ($do_file_tests) { @@ -52,7 +51,7 @@ if (!$do_file_tests) # Insert the current definition of the table t1 into t0_definition eval INSERT INTO t0_definition SET state = 'new', - create_command = load_file('$show_file'), + create_command = "$show_create", file_list = @aux; # Print the old and new table layout, if they differ diff --git a/mysql-test/suite/parts/r/partition_recover_myisam.result b/mysql-test/suite/parts/r/partition_recover_myisam.result index 49775ee498e..cf3ccaa73b3 100644 --- a/mysql-test/suite/parts/r/partition_recover_myisam.result +++ b/mysql-test/suite/parts/r/partition_recover_myisam.result @@ -1,4 +1,4 @@ -call mtr.add_suppression("./test/t1_will_crash"); +call mtr.add_suppression("t1_will_crash"); call mtr.add_suppression("Got an error from unknown thread, ha_myisam.cc"); CREATE TABLE t1_will_crash (a INT, KEY (a)) ENGINE=MyISAM; INSERT INTO t1_will_crash VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11); diff --git a/mysql-test/suite/parts/t/partition_debug_sync_innodb.test b/mysql-test/suite/parts/t/partition_debug_sync_innodb.test index 79ef3d537bf..e9abe134d84 100644 --- a/mysql-test/suite/parts/t/partition_debug_sync_innodb.test +++ b/mysql-test/suite/parts/t/partition_debug_sync_innodb.test @@ -14,6 +14,7 @@ partition by range (a) insert into t1 values (1), (11), (21), (33); SELECT * FROM t1; SHOW CREATE TABLE t1; +--replace_result #p# #P# --list_files $MYSQLD_DATADIR/test SET DEBUG_SYNC='before_open_in_get_all_tables SIGNAL parked WAIT_FOR open'; @@ -36,6 +37,7 @@ ALTER TABLE t1 REORGANIZE PARTITION p0 INTO disconnect con1; connection default; --reap +--replace_result #p# #P# --list_files $MYSQLD_DATADIR/test SHOW CREATE TABLE t1; SELECT * FROM t1; diff --git a/mysql-test/suite/parts/t/partition_recover_myisam.test b/mysql-test/suite/parts/t/partition_recover_myisam.test index 64bc821ac37..043ec16a9f4 100644 --- a/mysql-test/suite/parts/t/partition_recover_myisam.test +++ b/mysql-test/suite/parts/t/partition_recover_myisam.test @@ -1,6 +1,6 @@ # test the auto-recover (--myisam-recover) of partitioned myisam tables -call mtr.add_suppression("./test/t1_will_crash"); +call mtr.add_suppression("t1_will_crash"); call mtr.add_suppression("Got an error from unknown thread, ha_myisam.cc"); --source include/have_partition.inc @@ -20,6 +20,8 @@ FLUSH TABLES; let $MYSQLD_DATADIR= `select @@datadir`; --remove_file $MYSQLD_DATADIR/test/t1_will_crash.MYI --copy_file std_data/corrupt_t1.MYI $MYSQLD_DATADIR/test/t1_will_crash.MYI +--replace_result \\ / +--replace_regex /Table '.*data/Table './ SELECT * FROM t1_will_crash; DROP TABLE t1_will_crash; CREATE TABLE t1_will_crash (a INT, KEY (a)) @@ -33,5 +35,7 @@ FLUSH TABLES; --echo # head -c1024 t1#P#p1.MYI > corrupt_t1#P#p1.MYI --remove_file $MYSQLD_DATADIR/test/t1_will_crash#P#p1.MYI --copy_file std_data/corrupt_t1#P#p1.MYI $MYSQLD_DATADIR/test/t1_will_crash#P#p1.MYI +--replace_result \\ / +--replace_regex /Table '.*data/Table './ SELECT * FROM t1_will_crash; DROP TABLE t1_will_crash; diff --git a/mysql-test/suite/parts/t/partition_special_innodb.test b/mysql-test/suite/parts/t/partition_special_innodb.test index eac19f6d588..f237b02f331 100644 --- a/mysql-test/suite/parts/t/partition_special_innodb.test +++ b/mysql-test/suite/parts/t/partition_special_innodb.test @@ -58,8 +58,8 @@ ENGINE = InnoDB PARTITION BY HASH (a) PARTITIONS 2; -connect (con1,127.0.0.1,root,,test,$MASTER_MYPORT,); -connect (con2,127.0.0.1,root,,test,$MASTER_MYPORT,); +connect (con1, localhost, root,,); +connect (con2, localhost, root,,); --connection con1 SET autocommit=OFF; diff --git a/mysql-test/suite/rpl/r/rpl_circular_for_4_hosts.result b/mysql-test/suite/rpl/r/rpl_circular_for_4_hosts.result index fb1d3f8258e..6981e549918 100644 --- a/mysql-test/suite/rpl/r/rpl_circular_for_4_hosts.result +++ b/mysql-test/suite/rpl/r/rpl_circular_for_4_hosts.result @@ -121,11 +121,11 @@ Master D 12 D * Remove wrong event from C and restore B->C->D * include/stop_slave.inc DELETE FROM t1 WHERE a = 6; -START SLAVE; +include/start_slave.inc RESET MASTER; RESET SLAVE; include/rpl_change_topology.inc [new topology=1->2->3->4->1] -START SLAVE; +include/start_slave.inc include/rpl_sync.inc * Check data inserted before restoring schema A->B->C->D->A * diff --git a/mysql-test/suite/rpl/r/rpl_insert_duplicate.result b/mysql-test/suite/rpl/r/rpl_insert_duplicate.result new file mode 100644 index 00000000000..61ebbaaa5a9 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_insert_duplicate.result @@ -0,0 +1,29 @@ +include/master-slave.inc +[connection master] +CREATE TABLE t1 ( +a INT UNSIGNED NOT NULL PRIMARY KEY +) ENGINE=innodb; +CREATE TABLE t2 ( +a INT UNSIGNED +) ENGINE=innodb; +INSERT INTO t1 VALUES (1); +INSERT INTO t2 VALUES (1); +INSERT INTO t1 SELECT t2.a FROM t2 ORDER BY t2.a ON DUPLICATE KEY UPDATE t1.a= t1.a; +include/assert.inc [Sum of elements in t1 should be 1.] +include/assert.inc [In SBR or MIXED modes, the event in the binlog should be the same that was executed. In RBR mode, binlog position should stay unchanged.] +include/diff_tables.inc [master:test.t1 , slave:test.t1] +drop table t1, t2; +CREATE TABLE t1 ( +a INT UNSIGNED NOT NULL PRIMARY KEY +) ENGINE=myisam; +CREATE TABLE t2 ( +a INT UNSIGNED +) ENGINE=myisam; +INSERT INTO t1 VALUES (1); +INSERT INTO t2 VALUES (1); +INSERT INTO t1 SELECT t2.a FROM t2 ORDER BY t2.a ON DUPLICATE KEY UPDATE t1.a= t1.a; +include/assert.inc [Sum of elements in t1 should be 1.] +include/assert.inc [In SBR or MIXED modes, the event in the binlog should be the same that was executed. In RBR mode, binlog position should stay unchanged.] +include/diff_tables.inc [master:test.t1 , slave:test.t1] +drop table t1, t2; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_insert_ignore.result b/mysql-test/suite/rpl/r/rpl_insert_ignore.result index 6937c3d0987..63b9244a40d 100644 --- a/mysql-test/suite/rpl/r/rpl_insert_ignore.result +++ b/mysql-test/suite/rpl/r/rpl_insert_ignore.result @@ -20,48 +20,36 @@ INSERT INTO t2 VALUES (4, 3); INSERT INTO t2 VALUES (5, 4); INSERT INTO t2 VALUES (6, 6); INSERT IGNORE INTO t1 SELECT NULL, t2.b FROM t2 ORDER BY t2.a; -SELECT * FROM t1 ORDER BY a; -a b -1 1 -2 2 -3 3 -4 4 -5 5 -6 6 -SELECT * FROM t1 ORDER BY a; -a b -1 1 -2 2 -3 3 -4 4 -5 5 -6 6 -drop table t1; +include/assert.inc [Count of elements in t1 should be 6.] +include/diff_tables.inc [master:test.t1 , slave:test.t1] +INSERT IGNORE INTO t1 SELECT NULL, t2.b FROM t2 ORDER BY t2.a; +include/assert.inc [Count of elements in t1 should be 6.] +include/assert.inc [In SBR or MIXED modes, the event in the binlog should be the same that was executed. In RBR mode, binlog position should stay unchanged.] +drop table t1, t2; CREATE TABLE t1 ( a int unsigned not null auto_increment primary key, b int unsigned, unique (b) ) ENGINE=myisam; -INSERT INTO t1 VALUES (1, 1); -INSERT INTO t1 VALUES (2, 2); -INSERT INTO t1 VALUES (3, 3); -INSERT INTO t1 VALUES (4, 4); +CREATE TABLE t2 ( +a int unsigned, # to force INSERT SELECT to have a certain order +b int unsigned +) ENGINE=myisam; +INSERT INTO t1 VALUES (NULL, 1); +INSERT INTO t1 VALUES (NULL, 2); +INSERT INTO t1 VALUES (NULL, 3); +INSERT INTO t1 VALUES (NULL, 4); +INSERT INTO t2 VALUES (1, 1); +INSERT INTO t2 VALUES (2, 2); +INSERT INTO t2 VALUES (3, 5); +INSERT INTO t2 VALUES (4, 3); +INSERT INTO t2 VALUES (5, 4); +INSERT INTO t2 VALUES (6, 6); +INSERT IGNORE INTO t1 SELECT NULL, t2.b FROM t2 ORDER BY t2.a; +include/assert.inc [Count of elements in t1 should be 6.] +include/diff_tables.inc [master:test.t1 , slave:test.t1] INSERT IGNORE INTO t1 SELECT NULL, t2.b FROM t2 ORDER BY t2.a; -SELECT * FROM t1 ORDER BY a; -a b -1 1 -2 2 -3 3 -4 4 -5 5 -6 6 -SELECT * FROM t1 ORDER BY a; -a b -1 1 -2 2 -3 3 -4 4 -5 5 -6 6 +include/assert.inc [Count of elements in t1 should be 6.] +include/assert.inc [In SBR or MIXED modes, the event in the binlog should be the same that was executed. In RBR mode, binlog position should stay unchanged.] drop table t1, t2; include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/disabled.def b/mysql-test/suite/rpl/t/disabled.def index 3b3a55fe4cd..33f65ff3ecc 100644 --- a/mysql-test/suite/rpl/t/disabled.def +++ b/mysql-test/suite/rpl/t/disabled.def @@ -11,7 +11,6 @@ ############################################################################## rpl_row_create_table : Bug#51574 Feb 27 2010 andrei failed different way than earlier with bug#45576 -rpl_log_pos : BUG#55675 Sep 10 2010 27 2010 alfranio rpl.rpl_log_pos fails sporadically with error binlog truncated in the middle rpl_get_master_version_and_clock : Bug#59178 Jan 05 2011 joro Valgrind warnings rpl_get_master_version_and_clock rpl_row_until : BUG#59543 Jan 26 2011 alfranio Replication test from eits suite rpl_row_until times out rpl_stm_until : BUG#59543 Jan 26 2011 alfranio Replication test from eits suite rpl_row_until times out diff --git a/mysql-test/suite/rpl/t/rpl_circular_for_4_hosts.test b/mysql-test/suite/rpl/t/rpl_circular_for_4_hosts.test index 6099637e3e9..820ffc42933 100644 --- a/mysql-test/suite/rpl/t/rpl_circular_for_4_hosts.test +++ b/mysql-test/suite/rpl/t/rpl_circular_for_4_hosts.test @@ -175,7 +175,7 @@ SELECT 'Master D',a,b FROM t1 WHERE c = 3 ORDER BY a,b; source include/stop_slave.inc; --connection server_3 DELETE FROM t1 WHERE a = 6; -START SLAVE; +--source include/start_slave.inc --connection server_2 --sync_slave_with_master server_3 RESET MASTER; @@ -189,7 +189,7 @@ RESET SLAVE; --source include/rpl_change_topology.inc #--replace_result $SERVER_MYPORT_3 SERVER_MYPORT_3 $file_d LOG_FILE $pos_d LOG_POS #--eval CHANGE MASTER TO master_host='127.0.0.1',master_port=$SERVER_MYPORT_3,master_user='root',master_log_file='$file_d',master_log_pos=$pos_d -START SLAVE; +--source include/start_slave.inc --connection server_3 --sync_slave_with_master server_4 --source include/rpl_sync.inc diff --git a/mysql-test/suite/rpl/t/rpl_insert_duplicate.test b/mysql-test/suite/rpl/t/rpl_insert_duplicate.test new file mode 100644 index 00000000000..7dd38a696ae --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_insert_duplicate.test @@ -0,0 +1,14 @@ +######################################### +# Wrapper for rpl_insert_duplicate.test # +######################################### +-- source include/master-slave.inc +-- source include/have_innodb.inc +#-- source include/have_binlog_format_mixed_or_statement.inc + +let $engine_type=innodb; +-- source extra/rpl_tests/rpl_insert_duplicate.test + +let $engine_type=myisam; +-- source extra/rpl_tests/rpl_insert_duplicate.test + +--source include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_insert_ignore.test b/mysql-test/suite/rpl/t/rpl_insert_ignore.test index 1d6c8e7168e..4129d4d553e 100644 --- a/mysql-test/suite/rpl/t/rpl_insert_ignore.test +++ b/mysql-test/suite/rpl/t/rpl_insert_ignore.test @@ -4,7 +4,11 @@ -- source include/not_ndb_default.inc -- source include/have_innodb.inc -- source include/master-slave.inc -let $engine_type=innodb; -let $engine_type2=myisam; + +-- let $engine_type=innodb -- source extra/rpl_tests/rpl_insert_ignore.test + +-- let $engine_type=myisam +-- source extra/rpl_tests/rpl_insert_ignore.test + --source include/rpl_end.inc diff --git a/mysql-test/t/csv_not_null.test b/mysql-test/t/csv_not_null.test index 03ed566fb22..bebea53b2f7 100644 --- a/mysql-test/t/csv_not_null.test +++ b/mysql-test/t/csv_not_null.test @@ -55,10 +55,8 @@ INSERT INTO t1 VALUES(); SELECT * FROM t1; -- disable_warnings -# NOTE - Test disabled due to enum crash for this INSERT -# See Bug#33717 - INSERT...(default) fails for enum. -# Crashes CSV tables, loads spaces for MyISAM -#INSERT INTO t1 VALUES(default,default,default,default,default,default); +# Bug#33717 - INSERT...(default) fails for enum. +INSERT INTO t1 VALUES(default,default,default,default,default,default); -- enable_warnings SELECT * FROM t1; diff --git a/mysql-test/t/disabled.def b/mysql-test/t/disabled.def index 4510652b03f..c244d08e308 100644 --- a/mysql-test/t/disabled.def +++ b/mysql-test/t/disabled.def @@ -11,7 +11,5 @@ ############################################################################## kill : Bug#37780 2008-12-03 HHunger need some changes to be robust enough for pushbuild. query_cache_28249 : Bug#43861 2009-03-25 main.query_cache_28249 fails sporadically -main.mysqlhotcopy_myisam : Bug#56817 2010-10-21 anitha mysqlhotcopy* fails -main.mysqlhotcopy_archive: Bug#56817 2010-10-21 anitha mysqlhotcopy* fails log_tables-big : Bug#48646 2010-11-15 mattiasj report already exists read_many_rows_innodb : Bug#37635 2010-11-15 mattiasj report already exists diff --git a/mysql-test/t/func_time.test b/mysql-test/t/func_time.test index 8fce7072319..f32110ef87c 100644 --- a/mysql-test/t/func_time.test +++ b/mysql-test/t/func_time.test @@ -819,6 +819,28 @@ SELECT '2008-02-18' + INTERVAL 1 FRAC_SECOND; --error ER_PARSE_ERROR SELECT '2008-02-18' - INTERVAL 1 FRAC_SECOND; + +--echo # +--echo # Bug #52315 part 2 : utc_date() crashes when system time > year 2037 +--echo # + +--disable_result_log +--error ER_WRONG_VALUE_FOR_VAR +SET TIMESTAMP=-147490000; SELECT UTC_TIMESTAMP(); +--error ER_WRONG_VALUE_FOR_VAR +SET TIMESTAMP=2147483648; SELECT UTC_TIMESTAMP(); +SET TIMESTAMP=2147483646; SELECT UTC_TIMESTAMP(); +SET TIMESTAMP=2147483647; SELECT UTC_TIMESTAMP(); +SET TIMESTAMP=0; SELECT UTC_TIMESTAMP(); +--error ER_WRONG_VALUE_FOR_VAR +SET TIMESTAMP=-1; SELECT UTC_TIMESTAMP(); +SET TIMESTAMP=1; SELECT UTC_TIMESTAMP(); +--enable_result_log + +#reset back the timestamp value +SET TIMESTAMP=0; + + --echo End of 5.0 tests # diff --git a/mysql-test/t/mysql.test b/mysql-test/t/mysql.test index 2dcc77a16c2..aa774036d10 100644 --- a/mysql-test/t/mysql.test +++ b/mysql-test/t/mysql.test @@ -412,6 +412,12 @@ drop table t1; --echo --exec $MYSQL --skip-column-names --vertical test -e "select 1 as a" +# +# Bug#57450: mysql client enter in an infinite loop if the standard input is a directory +# +--error 1 +--exec $MYSQL < . + --echo --echo # diff --git a/mysql-test/t/not_embedded_server.test b/mysql-test/t/not_embedded_server.test index fa2b659ec57..43cdb977386 100644 --- a/mysql-test/t/not_embedded_server.test +++ b/mysql-test/t/not_embedded_server.test @@ -42,4 +42,14 @@ select 1; SHOW VARIABLES like 'slave_skip_errors'; +--echo # +--echo # Bug#58026: massive recursion and crash in regular expression handling +--echo # + +--disable_result_log +--error ER_STACK_OVERRUN_NEED_MORE +SELECT '1' RLIKE RPAD('1', 10000, '('); +--enable_result_log + + # End of 5.1 tests diff --git a/mysql-test/t/order_by.test b/mysql-test/t/order_by.test index a8a6ad00648..1064320b65c 100644 --- a/mysql-test/t/order_by.test +++ b/mysql-test/t/order_by.test @@ -1492,4 +1492,21 @@ LIMIT 2; DROP TABLE t1, t2; +--echo # +--echo # Bug #59110: Memory leak of QUICK_SELECT_I allocated memory +--echo # and +--echo # Bug #59308: Incorrect result for +--echo SELECT DISTINCT <col>... ORDER BY <col> DESC +--echo +--echo # Use Valgrind to detect #59110! +--echo # + +CREATE TABLE t1 (a INT,KEY (a)); +INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10); + +EXPLAIN SELECT DISTINCT a,1 FROM t1 WHERE a <> 1 ORDER BY a DESC; +SELECT DISTINCT a,1 FROM t1 WHERE a <> 1 ORDER BY a DESC; + +DROP TABLE t1; + --echo End of 5.1 tests diff --git a/mysql-test/t/range.test b/mysql-test/t/range.test index 0ad3d3e8504..6c9320b708a 100644 --- a/mysql-test/t/range.test +++ b/mysql-test/t/range.test @@ -1325,4 +1325,71 @@ SELECT * FROM t1 WHERE 2 NOT BETWEEN c_notkey AND c_key; DROP TABLE t1; +--echo # +--echo # Bug #57030: 'BETWEEN' evaluation is incorrect +--echo # + +# Test some BETWEEN predicates which does *not* follow the +# 'normal' pattern of <field> BETWEEN <low const> AND <high const> + +CREATE TABLE t1(pk INT PRIMARY KEY, i4 INT); +CREATE UNIQUE INDEX i4_uq ON t1(i4); + +INSERT INTO t1 VALUES (1,10), (2,20), (3,30); + +EXPLAIN +SELECT * FROM t1 WHERE i4 BETWEEN 10 AND 10; +SELECT * FROM t1 WHERE i4 BETWEEN 10 AND 10; + +EXPLAIN +SELECT * FROM t1 WHERE 10 BETWEEN i4 AND i4; +SELECT * FROM t1 WHERE 10 BETWEEN i4 AND i4; + +EXPLAIN +SELECT * FROM t1 WHERE 10 BETWEEN 10 AND i4; +SELECT * FROM t1 WHERE 10 BETWEEN 10 AND i4; + +EXPLAIN +SELECT * FROM t1 WHERE 10 BETWEEN i4 AND 10; +SELECT * FROM t1 WHERE 10 BETWEEN i4 AND 10; + +EXPLAIN +SELECT * FROM t1 WHERE 10 BETWEEN 10 AND 10; +SELECT * FROM t1 WHERE 10 BETWEEN 10 AND 10; + +EXPLAIN +SELECT * FROM t1 WHERE 10 BETWEEN 11 AND 11; +SELECT * FROM t1 WHERE 10 BETWEEN 11 AND 11; + +EXPLAIN +SELECT * FROM t1 WHERE 10 BETWEEN 100 AND 0; +SELECT * FROM t1 WHERE 10 BETWEEN 100 AND 0; + +EXPLAIN +SELECT * FROM t1 WHERE i4 BETWEEN 100 AND 0; +SELECT * FROM t1 WHERE i4 BETWEEN 100 AND 0; + +EXPLAIN +SELECT * FROM t1 WHERE i4 BETWEEN 10 AND 99999999999999999; +SELECT * FROM t1 WHERE i4 BETWEEN 10 AND 99999999999999999; + +EXPLAIN +SELECT * FROM t1 WHERE i4 BETWEEN 999999999999999 AND 30; +SELECT * FROM t1 WHERE i4 BETWEEN 999999999999999 AND 30; + +EXPLAIN +SELECT * FROM t1 WHERE i4 BETWEEN 10 AND '20'; +SELECT * FROM t1 WHERE i4 BETWEEN 10 AND '20'; + +#Should detect the EQ_REF 't2.pk=t1.i4' +EXPLAIN +SELECT * FROM t1, t1 as t2 WHERE t2.pk BETWEEN t1.i4 AND t1.i4; +SELECT * FROM t1, t1 as t2 WHERE t2.pk BETWEEN t1.i4 AND t1.i4; + +EXPLAIN +SELECT * FROM t1, t1 as t2 WHERE t1.i4 BETWEEN t2.pk AND t2.pk; +SELECT * FROM t1, t1 as t2 WHERE t1.i4 BETWEEN t2.pk AND t2.pk; + +DROP TABLE t1; + --echo End of 5.1 tests diff --git a/mysql-test/t/variables.test b/mysql-test/t/variables.test index 74d5c8450a2..2513f4fbcc8 100644 --- a/mysql-test/t/variables.test +++ b/mysql-test/t/variables.test @@ -791,7 +791,7 @@ SET @@myisam_mmap_size= 500M; --echo # Bug #52315: utc_date() crashes when system time > year 2037 --echo # ---error 0, ER_UNKNOWN_ERROR +--error 0, ER_WRONG_VALUE_FOR_VAR SET TIMESTAMP=2*1024*1024*1024; --echo #Should not crash --disable_result_log diff --git a/mysql-test/valgrind.supp b/mysql-test/valgrind.supp index 1016923a360..1983a8e6137 100644 --- a/mysql-test/valgrind.supp +++ b/mysql-test/valgrind.supp @@ -744,3 +744,50 @@ Memcheck:Addr1 fun:buf_buddy_relocate } + +{ + Bug 59874 Valgrind warning in InnoDB compression code + Memcheck:Cond + fun:* + fun:* + fun:deflate + fun:btr_store_big_rec_extern_fields_func + fun:row_ins_index_entry_low + fun:row_ins_index_entry + fun:row_ins_index_entry_step + fun:row_ins + fun:row_ins_step + fun:row_insert_for_mysql +} + +{ + In page0zip.c we have already checked that the memory is initialized before calling deflate() + Memcheck:Cond + fun:* + fun:* + fun:deflate + fun:page_zip_compress + fun:page_cur_insert_rec_zip_reorg + fun:page_cur_insert_rec_zip + fun:page_cur_tuple_insert + fun:btr_cur_optimistic_insert + fun:row_ins_index_entry_low + fun:row_ins_index_entry + fun:row_ins_index_entry_step + fun:row_ins + fun:row_ins_step + fun:row_insert_for_mysql +} + +{ + Bug 59875 Valgrind warning in buf0buddy.c + Memcheck:Addr1 + fun:mach_read_from_4 + fun:buf_buddy_relocate + fun:buf_buddy_free_low + fun:buf_buddy_free + fun:buf_LRU_block_remove_hashed_page + fun:buf_LRU_invalidate_tablespace + fun:fil_delete_tablespace + fun:row_drop_table_for_mysql +} |