diff options
Diffstat (limited to 'mysql-test')
61 files changed, 1993 insertions, 174 deletions
diff --git a/mysql-test/lib/mtr_report.pl b/mysql-test/lib/mtr_report.pl index e3eebc25091..2a90344c110 100644 --- a/mysql-test/lib/mtr_report.pl +++ b/mysql-test/lib/mtr_report.pl @@ -236,8 +236,10 @@ sub mtr_report_stats ($) { "the documentation at\n", "http://www.mysql.com/doc/en/MySQL_test_suite.html\n"; } - print - "The servers were restarted $tot_restarts times\n"; + if (!$::opt_extern) + { + print "The servers where restarted $tot_restarts times\n"; + } if ( $::opt_timer ) { diff --git a/mysql-test/mysql-test-run.pl b/mysql-test/mysql-test-run.pl index a04ace73bc1..4d385ddee66 100755 --- a/mysql-test/mysql-test-run.pl +++ b/mysql-test/mysql-test-run.pl @@ -702,7 +702,9 @@ sub command_line_setup () { "$glob_basedir/client", "$glob_basedir/bin"); - $exe_mysqld= mtr_exe_exists (vs_config_dirs('sql', 'mysqld'), + if (!$opt_extern) + { + $exe_mysqld= mtr_exe_exists (vs_config_dirs('sql', 'mysqld'), "$glob_basedir/sql/mysqld", "$path_client_bindir/mysqld-max-nt", "$path_client_bindir/mysqld-max", @@ -714,8 +716,16 @@ sub command_line_setup () { "$glob_basedir/bin/mysqld", "$glob_basedir/sbin/mysqld"); - # Use the mysqld found above to find out what features are available - collect_mysqld_features(); + # Use the mysqld found above to find out what features are available + collect_mysqld_features(); + } + else + { + $mysqld_variables{'port'}= 3306; + $mysqld_variables{'master-port'}= 3306; + $opt_skip_ndbcluster= 1; + $opt_skip_im= 1; + } if ( $opt_comment ) { @@ -752,7 +762,7 @@ sub command_line_setup () { # -------------------------------------------------------------------------- # NOTE if the default binlog format is changed, this has to be changed $used_binlog_format= "stmt"; - if ( $mysql_version_id >= 50100 ) + if (!$opt_extern && $mysql_version_id >= 50100 ) { $used_binlog_format= "mixed"; # Default value for binlog format @@ -838,19 +848,20 @@ sub command_line_setup () { # -------------------------------------------------------------------------- # Check im suport # -------------------------------------------------------------------------- - if ( $mysql_version_id < 50000 ) + if (!$opt_extern) { - # Instance manager is not supported until 5.0 - $opt_skip_im= 1; + if ( $mysql_version_id < 50000 ) { + # Instance manager is not supported until 5.0 + $opt_skip_im= 1; - } + } - if ( $glob_win32 ) - { - mtr_report("Disable Instance manager - not supported on Windows"); - $opt_skip_im= 1; - } + if ( $glob_win32 ) { + mtr_report("Disable Instance manager - not supported on Windows"); + $opt_skip_im= 1; + } + } # -------------------------------------------------------------------------- # Record flag # -------------------------------------------------------------------------- @@ -1214,7 +1225,7 @@ sub command_line_setup () { $opt_skip_rpl= 1; # Setup master->[0] with the settings for the extern server - $master->[0]->{'path_sock'}= $opt_socket if $opt_socket; + $master->[0]->{'path_sock'}= $opt_socket ? $opt_socket : "/tmp/mysql.sock"; mtr_report("Using extern server at '$master->[0]->{path_sock}'"); } else @@ -1480,60 +1491,58 @@ sub executable_setup () { $exe_mysqlbinlog= mtr_exe_exists("$path_client_bindir/mysqlbinlog"); $exe_mysqladmin= mtr_exe_exists("$path_client_bindir/mysqladmin"); $exe_mysql= mtr_exe_exists("$path_client_bindir/mysql"); - if ( $mysql_version_id >= 50100 ) - { - $exe_mysqlslap= mtr_exe_exists("$path_client_bindir/mysqlslap"); - } - if ( $mysql_version_id >= 50000 and !$glob_use_embedded_server ) - { - $exe_mysql_upgrade= mtr_exe_exists("$path_client_bindir/mysql_upgrade") - } - else - { - $exe_mysql_upgrade= ""; - } - if ( ! $glob_win32 ) + if (!$opt_extern) { - # Look for mysql_fix_system_table script - $exe_mysql_fix_system_tables= - mtr_script_exists("$glob_basedir/scripts/mysql_fix_privilege_tables", - "$path_client_bindir/mysql_fix_privilege_tables"); - - } # Look for SQL scripts directory $path_sql_dir= mtr_path_exists("$glob_basedir/share", "$glob_basedir/scripts"); - # Look for mysql_fix_privilege_tables.sql script - $file_mysql_fix_privilege_tables= - mtr_file_exists("$path_sql_dir/mysql_fix_privilege_tables.sql"); + if ( $mysql_version_id >= 50100 ) { + $exe_mysqlslap= mtr_exe_exists("$path_client_bindir/mysqlslap"); + } + if ( $mysql_version_id >= 50000 and !$glob_use_embedded_server ) { + $exe_mysql_upgrade= mtr_exe_exists("$path_client_bindir/mysql_upgrade") + } else { + $exe_mysql_upgrade= ""; + } + + if ( ! $glob_win32 ) { + # Look for mysql_fix_system_table script + $exe_mysql_fix_system_tables= + mtr_script_exists("$glob_basedir/scripts/mysql_fix_privilege_tables", + "$path_client_bindir/mysql_fix_privilege_tables"); + } - if ( ! $opt_skip_ndbcluster and executable_setup_ndb()) - { - mtr_warning("Could not find all required ndb binaries, " . - "all ndb tests will fail, use --skip-ndbcluster to " . - "skip testing it."); + # Look for mysql_fix_privilege_tables.sql script + $file_mysql_fix_privilege_tables= + mtr_file_exists("$glob_basedir/scripts/mysql_fix_privilege_tables.sql", + "$glob_basedir/share/mysql_fix_privilege_tables.sql"); - foreach my $cluster (@{$clusters}) - { - $cluster->{"executable_setup_failed"}= 1; + if ( ! $opt_skip_ndbcluster and executable_setup_ndb()) { + mtr_warning("Could not find all required ndb binaries, " . + "all ndb tests will fail, use --skip-ndbcluster to " . + "skip testing it."); + + foreach my $cluster (@{$clusters}) { + $cluster->{"executable_setup_failed"}= 1; + } } - } - if ( ! $opt_skip_im and executable_setup_im()) - { - mtr_warning("Could not find all required instance manager binaries, " . - "all im tests will fail, use --skip-im to " . - "continue without instance manager"); - $instance_manager->{"executable_setup_failed"}= 1; - } + if ( ! $opt_skip_im and executable_setup_im()) { + mtr_warning("Could not find all required instance manager binaries, " . + "all im tests will fail, use --skip-im to " . + "continue without instance manager"); + $instance_manager->{"executable_setup_failed"}= 1; + } - # Look for the udf_example library - $lib_udf_example= - mtr_file_exists(vs_config_dirs('sql', 'udf_example.dll'), - "$glob_basedir/sql/.libs/udf_example.so",); + # Look for the udf_example library + $lib_udf_example= + mtr_file_exists(vs_config_dirs('sql', 'udf_example.dll'), + "$glob_basedir/sql/.libs/udf_example.so",); + + } # Look for mysqltest executable if ( $glob_use_embedded_server ) @@ -1601,7 +1610,7 @@ sub mysql_client_test_arguments() mtr_add_arg($args, "--port=$master->[0]->{'port'}"); mtr_add_arg($args, "--socket=$master->[0]->{'path_sock'}"); - if ( $mysql_version_id >= 50000 ) + if ( $opt_extern || $mysql_version_id >= 50000 ) { mtr_add_arg($args, "--vardir=$opt_vardir") } @@ -1712,7 +1721,7 @@ sub environment_setup () { } $ENV{'LD_LIBRARY_PATH'}= join(":", @ld_library_paths, - $ENV{'LD_LIBRARY_PATHS'} ? + $ENV{'LD_LIBRARY_PATH'} ? split(':', $ENV{'LD_LIBRARY_PATH'}) : ()); mtr_debug("LD_LIBRARY_PATH: $ENV{'LD_LIBRARY_PATH'}"); @@ -1897,7 +1906,7 @@ sub environment_setup () { my $cmdline_mysqlbinlog= mtr_native_path($exe_mysqlbinlog) . " --no-defaults --local-load=$opt_tmpdir"; - if ( $mysql_version_id >= 50000 ) + if (!$opt_extern && $mysql_version_id >= 50000 ) { $cmdline_mysqlbinlog .=" --character-sets-dir=$path_charsetsdir"; } @@ -1929,7 +1938,7 @@ sub environment_setup () { # ---------------------------------------------------- # Setup env so childs can execute mysql_upgrade # ---------------------------------------------------- - if ( $mysql_version_id >= 50000 ) + if ( !$opt_extern && $mysql_version_id >= 50000 ) { $ENV{'MYSQL_UPGRADE'}= mysql_upgrade_arguments(); } @@ -1937,7 +1946,7 @@ sub environment_setup () { # ---------------------------------------------------- # Setup env so childs can execute mysql_fix_system_tables # ---------------------------------------------------- - if ( ! $glob_win32 ) + if ( !$opt_extern && ! $glob_win32 ) { my $cmdline_mysql_fix_system_tables= "$exe_mysql_fix_system_tables --no-defaults --host=localhost " . @@ -1948,7 +1957,10 @@ sub environment_setup () { $ENV{'MYSQL_FIX_SYSTEM_TABLES'}= $cmdline_mysql_fix_system_tables; } - $ENV{'MYSQL_FIX_PRIVILEGE_TABLES'}= $file_mysql_fix_privilege_tables; + if (!$opt_extern) + { + $ENV{'MYSQL_FIX_PRIVILEGE_TABLES'}= $file_mysql_fix_privilege_tables; + } # ---------------------------------------------------- # Setup env so childs can execute my_print_defaults @@ -2262,7 +2274,10 @@ sub check_ssl_support ($) { if ($opt_skip_ssl || $opt_extern) { - mtr_report("Skipping SSL"); + if (!$opt_extern) + { + mtr_report("Skipping SSL"); + } $opt_ssl_supported= 0; $opt_ssl= 0; return; @@ -2337,9 +2352,12 @@ sub vs_config_dirs ($$) { sub check_ndbcluster_support ($) { my $mysqld_variables= shift; - if ($opt_skip_ndbcluster) + if ($opt_skip_ndbcluster || $opt_extern) { - mtr_report("Skipping ndbcluster"); + if (!$opt_extern) + { + mtr_report("Skipping ndbcluster"); + } $opt_skip_ndbcluster_slave= 1; return; } @@ -2755,7 +2773,10 @@ sub initialize_servers () { } else { - mtr_report("No need to create '$opt_vardir' it already exists"); + if ($opt_verbose) + { + mtr_report("No need to create '$opt_vardir' it already exists"); + } } } else @@ -3157,17 +3178,17 @@ sub do_before_run_mysqltest($) unlink("$result_dir/$tname.log"); unlink("$result_dir/$tname.warnings"); - if ( $mysql_version_id < 50000 ) - { - # Set environment variable NDB_STATUS_OK to 1 - # if script decided to run mysqltest cluster _is_ installed ok - $ENV{'NDB_STATUS_OK'} = "1"; - } - elsif ( $mysql_version_id < 50100 ) + if (!$opt_extern) { - # Set environment variable NDB_STATUS_OK to YES - # if script decided to run mysqltest cluster _is_ installed ok - $ENV{'NDB_STATUS_OK'} = "YES"; + if ( $mysql_version_id < 50000 ) { + # Set environment variable NDB_STATUS_OK to 1 + # if script decided to run mysqltest cluster _is_ installed ok + $ENV{'NDB_STATUS_OK'} = "1"; + } elsif ( $mysql_version_id < 50100 ) { + # Set environment variable NDB_STATUS_OK to YES + # if script decided to run mysqltest cluster _is_ installed ok + $ENV{'NDB_STATUS_OK'} = "YES"; + } } } @@ -4949,10 +4970,10 @@ sub usage ($) { if ( $message ) { - print STDERR "$message \n"; + print STDERR "$message\n"; } - print STDERR <<HERE; + print <<HERE; $0 [ OPTIONS ] [ TESTCASE ] diff --git a/mysql-test/ndb/ndb_config_1_node.ini b/mysql-test/ndb/ndb_config_1_node.ini new file mode 100644 index 00000000000..7b0d2829530 --- /dev/null +++ b/mysql-test/ndb/ndb_config_1_node.ini @@ -0,0 +1,43 @@ +[ndbd default] +NoOfReplicas= 1 +MaxNoOfConcurrentTransactions= 64 +MaxNoOfConcurrentOperations= CHOOSE_MaxNoOfConcurrentOperations +DataMemory= CHOOSE_DataMemory +IndexMemory= CHOOSE_IndexMemory +Diskless= CHOOSE_Diskless +TimeBetweenWatchDogCheck= 30000 +DataDir= CHOOSE_FILESYSTEM +MaxNoOfOrderedIndexes= CHOOSE_MaxNoOfOrderedIndexes +MaxNoOfAttributes= CHOOSE_MaxNoOfAttributes +TimeBetweenGlobalCheckpoints= 500 +NoOfFragmentLogFiles= 3 + +# +# Increase deadlock-timeout to cater for slow test-machines +# (possibly running several tests in parallell) +# +#TransactionDeadlockDetectionTimeout= 7500 + +[ndbd] +HostName= CHOOSE_HOSTNAME_1 # hostname is a valid network adress + +[ndb_mgmd] +HostName= CHOOSE_HOSTNAME_1 # hostname is a valid network adress +DataDir= CHOOSE_FILESYSTEM # +PortNumber= CHOOSE_PORT_MGM + +[mysqld] + +[mysqld] + +[mysqld] + +[mysqld] + +[mysqld] + +[mysqld] + +[mysqld] + +[mysqld] diff --git a/mysql-test/ndb/ndb_config_2_node.ini b/mysql-test/ndb/ndb_config_2_node.ini index a6a56376f33..aafed28dbc6 100644 --- a/mysql-test/ndb/ndb_config_2_node.ini +++ b/mysql-test/ndb/ndb_config_2_node.ini @@ -1,5 +1,6 @@ [ndbd default] NoOfReplicas= 2 +MaxNoOfConcurrentTransactions= 64 MaxNoOfConcurrentOperations= CHOOSE_MaxNoOfConcurrentOperations DataMemory= CHOOSE_DataMemory IndexMemory= CHOOSE_IndexMemory @@ -7,6 +8,15 @@ Diskless= CHOOSE_Diskless TimeBetweenWatchDogCheck= 30000 DataDir= CHOOSE_FILESYSTEM MaxNoOfOrderedIndexes= CHOOSE_MaxNoOfOrderedIndexes +MaxNoOfAttributes= CHOOSE_MaxNoOfAttributes +TimeBetweenGlobalCheckpoints= 500 +NoOfFragmentLogFiles= 3 + +# +# Increase deadlock-timeout to cater for slow test-machines +# (possibly running several tests in parallell) +# +#TransactionDeadlockDetectionTimeout= 7500 [ndbd] HostName= CHOOSE_HOSTNAME_1 # hostname is a valid network adress diff --git a/mysql-test/ndb/ndb_config_4_node.ini b/mysql-test/ndb/ndb_config_4_node.ini new file mode 100644 index 00000000000..2154475aa37 --- /dev/null +++ b/mysql-test/ndb/ndb_config_4_node.ini @@ -0,0 +1,52 @@ +[ndbd default] +NoOfReplicas= 2 +MaxNoOfConcurrentTransactions= 64 +MaxNoOfConcurrentOperations= CHOOSE_MaxNoOfConcurrentOperations +DataMemory= CHOOSE_DataMemory +IndexMemory= CHOOSE_IndexMemory +Diskless= CHOOSE_Diskless +TimeBetweenWatchDogCheck= 30000 +DataDir= CHOOSE_FILESYSTEM +MaxNoOfOrderedIndexes= CHOOSE_MaxNoOfOrderedIndexes +MaxNoOfAttributes= CHOOSE_MaxNoOfAttributes +TimeBetweenGlobalCheckpoints= 500 +NoOfFragmentLogFiles= 3 + +# +# Increase deadlock-timeout to cater for slow test-machines +# (possibly running several tests in parallell) +# +#TransactionDeadlockDetectionTimeout= 7500 + +[ndbd] +HostName= CHOOSE_HOSTNAME_1 # hostname is a valid network adress + +[ndbd] +HostName= CHOOSE_HOSTNAME_2 # hostname is a valid network adress + +[ndbd] +HostName= CHOOSE_HOSTNAME_3 # hostname is a valid network adress + +[ndbd] +HostName= CHOOSE_HOSTNAME_4 # hostname is a valid network adress + +[ndb_mgmd] +HostName= CHOOSE_HOSTNAME_1 # hostname is a valid network adress +DataDir= CHOOSE_FILESYSTEM # +PortNumber= CHOOSE_PORT_MGM + +[mysqld] + +[mysqld] + +[mysqld] + +[mysqld] + +[mysqld] + +[mysqld] + +[mysqld] + +[mysqld] diff --git a/mysql-test/ndb/ndbcluster.sh b/mysql-test/ndb/ndbcluster.sh index c53bf1306e6..be08781361e 100644 --- a/mysql-test/ndb/ndbcluster.sh +++ b/mysql-test/ndb/ndbcluster.sh @@ -63,12 +63,17 @@ stop_ndb= initial_ndb= status_ndb= ndb_diskless=0 +ndbd_nodes=2 +relative_config_data_dir= +opt_core= ndb_no_ord=512 +ndb_no_attr=2048 ndb_con_op=105000 ndb_dmem=80M ndb_imem=24M +VERBOSE=100 NDB_MGM_EXTRA_OPTS= NDB_MGMD_EXTRA_OPTS= NDBD_EXTRA_OPTS= @@ -89,6 +94,9 @@ while test $# -gt 0; do --debug*) flags_ndb="$flags_ndb $1" ;; + --ndbd-nodes=*) + ndbd_nodes=`echo "$1" | sed -e "s;--ndbd-nodes=;;"` + ;; --status) status_ndb=1 ;; @@ -104,6 +112,9 @@ while test $# -gt 0; do --data-dir=*) fsdir=`echo "$1" | sed -e "s;--data-dir=;;"` ;; + --relative-config-data-dir) + relative_config_data_dir=1 + ;; --port=*) port=`echo "$1" | sed -e "s;--port=;;"` ;; @@ -122,6 +133,12 @@ while test $# -gt 0; do --character-sets-dir=*) CHARSETSDIR=`echo "$1" | sed -e "s;--character-sets-dir=;;"` ;; + --core) + opt_core="--core" + ;; + --verbose=*) + VERBOSE=`echo "$1" | sed -e "s;--verbose=;;"` + ;; -- ) shift; break ;; --* ) $ECHO "Unrecognized option: $1"; exit 1 ;; * ) break ;; @@ -130,9 +147,10 @@ while test $# -gt 0; do done fs_ndb="$fsdir/ndbcluster-$port" +config_ini=ndb/ndb_config_${ndbd_nodes}_node.ini NDB_HOME= -if [ ! -x "$fsdir" ]; then +if [ ! -d "$fsdir" ]; then echo "$fsdir missing" exit 1 fi @@ -148,11 +166,15 @@ if [ ! -x "$exec_waiter" ]; then echo "$exec_waiter missing" exit 1 fi +if [ ! -f "$config_ini" ]; then + echo "$config_ini missing, unsupported number of nodes" + exit 1 +fi -exec_mgmtclient="$exec_mgmtclient --no-defaults $NDB_MGM_EXTRA_OPTS" -exec_mgmtsrvr="$exec_mgmtsrvr --no-defaults $NDB_MGMD_EXTRA_OPTS" -exec_ndb="$exec_ndb --no-defaults $NDBD_EXTRA_OPTS --character-sets-dir=$CHARSETSDIR" -exec_waiter="$exec_waiter --no-defaults" +exec_mgmtclient="$exec_mgmtclient --no-defaults $opt_core $NDB_MGM_EXTRA_OPTS" +exec_mgmtsrvr="$exec_mgmtsrvr --no-defaults $opt_core $NDB_MGMD_EXTRA_OPTS" +exec_ndb="$exec_ndb --no-defaults $opt_core $NDBD_EXTRA_OPTS --character-sets-dir=$CHARSETSDIR" +exec_waiter="$exec_waiter --no-defaults $opt_core" ndb_host="localhost" ndb_mgmd_port=$port @@ -196,18 +218,24 @@ fi # Start management server as deamon # Edit file system path and ports in config file +if [ $relative_config_data_dir ] ; then + config_fs_ndb="." +else + config_fs_ndb=$fs_ndb +fi if [ $initial_ndb ] ; then - rm -f $fs_ndb/ndb_* 2>&1 | cat > /dev/null + rm -rf $fs_ndb/ndb_* 2>&1 | cat > /dev/null sed \ + -e s,"CHOOSE_MaxNoOfAttributes","$ndb_no_attr",g \ -e s,"CHOOSE_MaxNoOfOrderedIndexes","$ndb_no_ord",g \ -e s,"CHOOSE_MaxNoOfConcurrentOperations","$ndb_con_op",g \ -e s,"CHOOSE_DataMemory","$ndb_dmem",g \ -e s,"CHOOSE_IndexMemory","$ndb_imem",g \ -e s,"CHOOSE_Diskless","$ndb_diskless",g \ -e s,"CHOOSE_HOSTNAME_".*,"$ndb_host",g \ - -e s,"CHOOSE_FILESYSTEM","$fs_ndb",g \ + -e s,"CHOOSE_FILESYSTEM","$config_fs_ndb",g \ -e s,"CHOOSE_PORT_MGM","$ndb_mgmd_port",g \ - < ndb/ndb_config_2_node.ini \ + < "$config_ini" \ > "$fs_ndb/config.ini" fi @@ -218,7 +246,7 @@ if ( cd "$fs_ndb" ; $exec_mgmtsrvr -f config.ini ) ; then :; else echo "Unable to start $exec_mgmtsrvr from `pwd`" exit 1 fi -if sleep_until_file_created $fs_ndb/ndb_3.pid 120 +if sleep_until_file_created $fs_ndb/ndb_`expr $ndbd_nodes + 1`.pid 120 then :; else exit 1 fi @@ -226,38 +254,43 @@ cat `find "$fs_ndb" -name 'ndb_*.pid'` > "$fs_ndb/$pidfile" # Start database node -echo "Starting ndbd" -( cd "$fs_ndb" ; $exec_ndb $flags_ndb & ) -if sleep_until_file_created $fs_ndb/ndb_1.pid 120 -then :; else - stop_default_ndbcluster - exit 1 -fi -cat `find "$fs_ndb" -name 'ndb_*.pid'` > "$fs_ndb/$pidfile" - -# Start database node - -echo "Starting ndbd" -( cd "$fs_ndb" ; $exec_ndb $flags_ndb & ) -if sleep_until_file_created $fs_ndb/ndb_2.pid 120 -then :; else - stop_default_ndbcluster - exit 1 -fi -cat `find "$fs_ndb" -name 'ndb_*.pid'` > "$fs_ndb/$pidfile" +id=1 +while [ $id -le $ndbd_nodes ] +do + if [ `expr $VERBOSE \> 1` = 1 ] ; then + echo "Starting ndbd $id($ndbd_nodes)" + fi + ( cd "$fs_ndb" ; $exec_ndb $flags_ndb & ) + if sleep_until_file_created $fs_ndb/ndb_${id}.pid 120 + then :; else + stop_default_ndbcluster + exit 1 + fi + cat `find "$fs_ndb" -name 'ndb_*.pid'` > "$fs_ndb/$pidfile" + id=`expr $id + 1` +done # test if Ndb Cluster starts properly -echo "Waiting for NDB data nodes to start..." -if ( $exec_waiter ) | grep "NDBT_ProgramExit: 0 - OK" > /dev/null 2>&1; then :; else - echo "Ndbcluster startup failed" +if [ `expr $VERBOSE \> 1` = 1 ] ; then + echo "Waiting for NDB data nodes to start..." +fi +if ( $exec_waiter ) | grep "NDBT_ProgramExit: 0 - OK" > /dev/null 2>&1 ; then :; else + if [ `expr $VERBOSE \> 0` = 1 ] ; then + echo "Ndbcluster startup failed" + fi stop_default_ndbcluster exit 1 fi +if [ `expr $VERBOSE \> 1` = 1 ] ; then + echo "Ok" +fi cat `find "$fs_ndb" -name 'ndb_*.pid'` > $fs_ndb/$pidfile -status_ndbcluster +if [ `expr $VERBOSE \> 2` = 1 ] ; then + status_ndbcluster +fi } status_ndbcluster() { diff --git a/mysql-test/suppress.purify b/mysql-test/purify.supp index 58553130c51..58553130c51 100644 --- a/mysql-test/suppress.purify +++ b/mysql-test/purify.supp diff --git a/mysql-test/r/bdb.result b/mysql-test/r/bdb.result index 33398d1ddd7..91c385112b4 100644 --- a/mysql-test/r/bdb.result +++ b/mysql-test/r/bdb.result @@ -1442,7 +1442,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref c c 11 const # Using where explain select count(*) from t1 where t='a '; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range t t 13 NULL # Using where +1 SIMPLE t1 ref t t 13 const # Using where explain select count(*) from t1 where v like 'a%'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range v v 13 NULL # Using where diff --git a/mysql-test/r/blackhole.result b/mysql-test/r/blackhole.result index 140d7e73d48..0ee5f326e0f 100644 --- a/mysql-test/r/blackhole.result +++ b/mysql-test/r/blackhole.result @@ -123,3 +123,14 @@ master-bin.000001 # Query 1 # use `test`; create table t3 like t1 master-bin.000001 # Query 1 # use `test`; insert into t1 select * from t3 master-bin.000001 # Query 1 # use `test`; replace into t1 select * from t3 drop table t1,t2,t3; +CREATE TABLE t1(a INT, b INT) ENGINE=BLACKHOLE; +DELETE FROM t1 WHERE a=10; +ALTER TABLE t1 ADD INDEX(a); +DELETE FROM t1 WHERE a=10; +ALTER TABLE t1 DROP INDEX a; +ALTER TABLE t1 ADD UNIQUE INDEX(a); +DELETE FROM t1 WHERE a=10; +ALTER TABLE t1 DROP INDEX a; +ALTER TABLE t1 ADD PRIMARY KEY(a); +DELETE FROM t1 WHERE a=10; +DROP TABLE t1; diff --git a/mysql-test/r/ctype_cp932.result b/mysql-test/r/ctype_cp932.result index 01b7fab2aba..6caf22645a8 100755 --- a/mysql-test/r/ctype_cp932.result +++ b/mysql-test/r/ctype_cp932.result @@ -11363,3 +11363,15 @@ c_cp932 ソ ƒ\ ƒ\ +set names utf8; +create table t1 (a text) default character set cp932; +insert into t1 values (_utf8 0xE38182); +show warnings; +Level Code Message +select * from t1; +a +ã‚ +select hex(a) from t1; +hex(a) +82A0 +drop table t1; diff --git a/mysql-test/r/ctype_cp932_binlog.result b/mysql-test/r/ctype_cp932_binlog.result index d3d800b7bf0..ff295961008 100644 --- a/mysql-test/r/ctype_cp932_binlog.result +++ b/mysql-test/r/ctype_cp932_binlog.result @@ -8,9 +8,9 @@ SET @var1= x'8300'; EXECUTE stmt1 USING @var1; SHOW BINLOG EVENTS FROM 98; Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000001 98 Query 1 185 use `test`; CREATE TABLE t1(f1 blob) -master-bin.000001 185 User var 1 224 @`var1`=_binary 0x8300 COLLATE binary -master-bin.000001 224 Query 1 317 use `test`; INSERT INTO t1 VALUES(@'var1') +master-bin.000001 98 Query 1 188 use `test`; CREATE TABLE t1(f1 blob) +master-bin.000001 188 User var 1 227 @`var1`=_binary 0x8300 COLLATE binary +master-bin.000001 227 Query 1 323 use `test`; INSERT INTO t1 VALUES(@'var1') SELECT HEX(f1) FROM t1; HEX(f1) 8300 @@ -30,17 +30,17 @@ HEX(s1) HEX(s2) d 466F6F2773206120426172 ED40ED41ED42 47.93 DROP PROCEDURE bug18293| DROP TABLE t4| -SHOW BINLOG EVENTS FROM 393| +SHOW BINLOG EVENTS FROM 402| Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000001 393 Query 1 556 use `test`; CREATE TABLE t4 (s1 CHAR(50) CHARACTER SET latin1, +master-bin.000001 402 Query 1 568 use `test`; CREATE TABLE t4 (s1 CHAR(50) CHARACTER SET latin1, s2 CHAR(50) CHARACTER SET cp932, d DECIMAL(10,2)) -master-bin.000001 556 Query 1 801 use `test`; CREATE DEFINER=`root`@`localhost` PROCEDURE bug18293 (IN ins1 CHAR(50), +master-bin.000001 568 Query 1 816 use `test`; CREATE DEFINER=`root`@`localhost` PROCEDURE bug18293 (IN ins1 CHAR(50), IN ins2 CHAR(50) CHARACTER SET cp932, IN ind DECIMAL(10,2)) BEGIN INSERT INTO t4 VALUES (ins1, ins2, ind); END -master-bin.000001 801 Query 1 1017 use `test`; INSERT INTO t4 VALUES ( NAME_CONST('ins1',_latin1 0x466F6F2773206120426172), NAME_CONST('ins2',_cp932 0xED40ED41ED42), NAME_CONST('ind',47.93)) -master-bin.000001 1017 Query 1 1103 use `test`; DROP PROCEDURE bug18293 -master-bin.000001 1103 Query 1 1179 use `test`; DROP TABLE t4 +master-bin.000001 816 Query 1 1035 use `test`; INSERT INTO t4 VALUES ( NAME_CONST('ins1',_latin1 0x466F6F2773206120426172), NAME_CONST('ins2',_cp932 0xED40ED41ED42), NAME_CONST('ind',47.93)) +master-bin.000001 1035 Query 1 1124 use `test`; DROP PROCEDURE bug18293 +master-bin.000001 1124 Query 1 1203 use `test`; DROP TABLE t4 diff --git a/mysql-test/r/ctype_cp932_notembedded.result b/mysql-test/r/ctype_cp932_notembedded.result index d04fce7738c..c58bfc65454 100644 --- a/mysql-test/r/ctype_cp932_notembedded.result +++ b/mysql-test/r/ctype_cp932_notembedded.result @@ -8,9 +8,9 @@ SET @var1= x'8300'; EXECUTE stmt1 USING @var1; SHOW BINLOG EVENTS FROM 98; Log_name Pos Event_type Server_id End_log_pos Info -master-bin.000001 98 Query 1 185 use `test`; CREATE TABLE t1(f1 blob) -master-bin.000001 185 User var 1 224 @`var1`=_binary 0x8300 COLLATE binary -master-bin.000001 224 Query 1 317 use `test`; INSERT INTO t1 VALUES(@'var1') +master-bin.000001 98 Query 1 188 use `test`; CREATE TABLE t1(f1 blob) +master-bin.000001 188 User var 1 227 @`var1`=_binary 0x8300 COLLATE binary +master-bin.000001 227 Query 1 323 use `test`; INSERT INTO t1 VALUES(@'var1') SELECT HEX(f1) FROM t1; HEX(f1) 8300 diff --git a/mysql-test/r/endspace.result b/mysql-test/r/endspace.result index 0e68418a80f..003ee7ffd5e 100644 --- a/mysql-test/r/endspace.result +++ b/mysql-test/r/endspace.result @@ -98,7 +98,7 @@ concat('|', text1, '|') |teststring | explain select concat('|', text1, '|') from t1 where text1='teststring '; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range key1 key1 22 NULL 2 Using where +1 SIMPLE t1 ref key1 key1 22 const 2 Using where select concat('|', text1, '|') from t1 where text1 like 'teststring_%'; concat('|', text1, '|') |teststring | diff --git a/mysql-test/r/innodb-replace.result b/mysql-test/r/innodb-replace.result index b7edcc49e56..77e0aeb38fd 100644 --- a/mysql-test/r/innodb-replace.result +++ b/mysql-test/r/innodb-replace.result @@ -2,11 +2,11 @@ drop table if exists t1; create table t1 (c1 char(5) unique not null, c2 int, stamp timestamp) engine=innodb; select * from t1; c1 c2 stamp -replace delayed into t1 (c1, c2) values ( "text1","11"),( "text2","12"); +replace delayed into t1 (c1, c2) values ( "text1","11"); ERROR HY000: Table storage engine for 't1' doesn't have this option select * from t1; c1 c2 stamp -replace delayed into t1 (c1, c2) values ( "text1","12"),( "text2","13"),( "text3","14", "a" ),( "text4","15", "b" ); +replace delayed into t1 (c1, c2) values ( "text1","12"); ERROR HY000: Table storage engine for 't1' doesn't have this option select * from t1; c1 c2 stamp diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result index 9abb95e2cc3..99f0d4100ee 100644 --- a/mysql-test/r/innodb.result +++ b/mysql-test/r/innodb.result @@ -1991,7 +1991,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref c c 11 const # Using where; Using index explain select count(*) from t1 where t='a '; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range t t 13 NULL # Using where +1 SIMPLE t1 ref t t 13 const # Using where explain select count(*) from t1 where v like 'a%'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range v v 13 NULL # Using where; Using index diff --git a/mysql-test/r/insert_select.result b/mysql-test/r/insert_select.result index 2e0acf303c2..92b3ea0e42b 100644 --- a/mysql-test/r/insert_select.result +++ b/mysql-test/r/insert_select.result @@ -731,3 +731,16 @@ select @@identity; @@identity 0 drop table t1; +CREATE TABLE t1 (f1 INT, f2 INT ); +CREATE TABLE t2 (f1 INT PRIMARY KEY, f2 INT); +INSERT INTO t1 VALUES (1,1),(2,2),(10,10); +INSERT INTO t2 (f1, f2) SELECT f1, f2 FROM t1; +INSERT INTO t2 (f1, f2) +SELECT f1, f1 FROM t2 src WHERE f1 < 2 +ON DUPLICATE KEY UPDATE f1 = 100 + src.f1; +SELECT * FROM t2; +f1 f2 +101 1 +2 2 +10 10 +DROP TABLE t1, t2; diff --git a/mysql-test/r/insert_update.result b/mysql-test/r/insert_update.result index 6be37450835..f658ff06624 100644 --- a/mysql-test/r/insert_update.result +++ b/mysql-test/r/insert_update.result @@ -219,3 +219,20 @@ SELECT * FROM t1; a b 45 2 DROP TABLE t1; +CREATE TABLE t1 (i INT PRIMARY KEY, j INT); +INSERT INTO t1 SELECT 1, j; +ERROR 42S22: Unknown column 'j' in 'field list' +DROP TABLE t1; +CREATE TABLE t1 (i INT PRIMARY KEY, j INT); +CREATE TABLE t2 (a INT, b INT); +CREATE TABLE t3 (a INT, c INT); +INSERT INTO t1 SELECT 1, a FROM t2 NATURAL JOIN t3 +ON DUPLICATE KEY UPDATE j= a; +DROP TABLE t1,t2,t3; +CREATE TABLE t1 (i INT PRIMARY KEY, j INT); +CREATE TABLE t2 (a INT); +INSERT INTO t1 VALUES (1, 1); +INSERT INTO t2 VALUES (1), (3); +INSERT INTO t1 SELECT 1, COUNT(*) FROM t2 ON DUPLICATE KEY UPDATE j= a; +ERROR 42S22: Unknown column 'a' in 'field list' +DROP TABLE t1,t2; diff --git a/mysql-test/r/keywords.result b/mysql-test/r/keywords.result index 88a0ab8abd5..c14ae584c86 100644 --- a/mysql-test/r/keywords.result +++ b/mysql-test/r/keywords.result @@ -16,3 +16,16 @@ select events.binlog from events; binlog 1 drop table events; +create table t1 (connection int, b int); +create procedure p1() +begin +declare connection int; +select max(t1.connection) into connection from t1; +select concat("max=",connection) 'p1'; +end| +insert into t1 (connection) values (1); +call p1(); +p1 +max=1 +drop procedure p1; +drop table t1; diff --git a/mysql-test/r/myisam.result b/mysql-test/r/myisam.result index 0f6e0ad537a..83359048a8c 100644 --- a/mysql-test/r/myisam.result +++ b/mysql-test/r/myisam.result @@ -1071,7 +1071,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref c c 11 const # Using where; Using index explain select count(*) from t1 where t='a '; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range t t 13 NULL # Using where +1 SIMPLE t1 ref t t 13 const # Using where explain select count(*) from t1 where v like 'a%'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range v v 13 NULL # Using where; Using index diff --git a/mysql-test/r/mysqlbinlog.result b/mysql-test/r/mysqlbinlog.result index 28dee9be92c..8097657c629 100644 --- a/mysql-test/r/mysqlbinlog.result +++ b/mysql-test/r/mysqlbinlog.result @@ -1,5 +1,5 @@ set timestamp=1000000000; -drop table if exists t1,t2; +drop table if exists t1,t2,t3,t4,t5,t03,t04; create table t1 (word varchar(20)); create table t2 (id int auto_increment not null primary key); insert into t1 values ("abirvalg"); @@ -23,7 +23,7 @@ SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.uniq SET @@session.sql_mode=0/*!*/; /*!\C latin1 *//*!*/; SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; -drop table if exists t1,t2/*!*/; +drop table if exists t1,t2,t3,t4,t5,t03,t04/*!*/; SET TIMESTAMP=1000000000/*!*/; create table t1 (word varchar(20))/*!*/; SET TIMESTAMP=1000000000/*!*/; @@ -100,7 +100,7 @@ SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.uniq SET @@session.sql_mode=0/*!*/; /*!\C latin1 *//*!*/; SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; -drop table if exists t1,t2/*!*/; +drop table if exists t1,t2,t3,t4,t5,t03,t04/*!*/; SET TIMESTAMP=1000000000/*!*/; create table t1 (word varchar(20))/*!*/; SET TIMESTAMP=1000000000/*!*/; @@ -192,13 +192,14 @@ DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; +drop table t1,t2; flush logs; select * from t5 /* must be (1),(1) */; a 1 1 +drop table t5; flush logs; -drop table if exists t5; create table t5 (c1 int, c2 varchar(128) character set latin1 not null); insert into t5 values (1, date_format('2001-01-01','%W')); set lc_time_names=de_DE; @@ -217,6 +218,7 @@ c1 c2 1 Monday 2 Montag 3 Monday +drop table t5; drop procedure if exists p1; flush logs; create procedure p1() @@ -231,6 +233,7 @@ call p1(); drop procedure p1; call p1(); ERROR 42000: PROCEDURE test.p1 does not exist + /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; @@ -251,6 +254,63 @@ ROLLBACK /* added by mysqlbinlog */; call p1(); 1 1 -drop procedure p1; +flush logs; +create table t1 (a varchar(64) character set utf8); +load data infile '../std_data_ln/loaddata6.dat' into table t1; +set character_set_database=koi8r; +load data infile '../std_data_ln/loaddata6.dat' into table t1; +set character_set_database=latin1; +load data infile '../std_data_ln/loaddata6.dat' into table t1; +load data infile '../std_data_ln/loaddata6.dat' into table t1; +set character_set_database=koi8r; +load data infile '../std_data_ln/loaddata6.dat' into table t1; +set character_set_database=latin1; +load data infile '../std_data_ln/loaddata6.dat' into table t1; +load data infile '../std_data_ln/loaddata6.dat' into table t1 character set koi8r; +select hex(a) from t1; +hex(a) +C3BF +D0AA +C3BF +C3BF +D0AA +C3BF +D0AA +drop table t1; +flush logs; +/*!40019 SET @@session.max_insert_delayed_threads=0*/; +/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; +DELIMITER /*!*/; +use test/*!*/; +SET TIMESTAMP=1000000000/*!*/; +SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/; +SET @@session.sql_mode=0/*!*/; +/*!\C latin1 *//*!*/; +SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/; +create table t1 (a varchar(64) character set utf8)/*!*/; +SET TIMESTAMP=1000000000/*!*/; +load data LOCAL INFILE 'MYSQL_TMP_DIR/SQL_LOAD_MB-6-0' INTO table t1/*!*/; +SET TIMESTAMP=1000000000/*!*/; +SET @@session.collation_database=7/*!*/; +load data LOCAL INFILE 'MYSQL_TMP_DIR/SQL_LOAD_MB-7-0' INTO table t1/*!*/; +SET TIMESTAMP=1000000000/*!*/; +SET @@session.collation_database=DEFAULT/*!*/; +load data LOCAL INFILE 'MYSQL_TMP_DIR/SQL_LOAD_MB-8-0' INTO table t1/*!*/; +SET TIMESTAMP=1000000000/*!*/; +load data LOCAL INFILE 'MYSQL_TMP_DIR/SQL_LOAD_MB-9-0' INTO table t1/*!*/; +SET TIMESTAMP=1000000000/*!*/; +SET @@session.collation_database=7/*!*/; +load data LOCAL INFILE 'MYSQL_TMP_DIR/SQL_LOAD_MB-a-0' INTO table t1/*!*/; +SET TIMESTAMP=1000000000/*!*/; +SET @@session.collation_database=DEFAULT/*!*/; +load data LOCAL INFILE 'MYSQL_TMP_DIR/SQL_LOAD_MB-b-0' INTO table t1/*!*/; +SET TIMESTAMP=1000000000/*!*/; +load data LOCAL INFILE 'MYSQL_TMP_DIR/SQL_LOAD_MB-c-0' INTO table t1 character set koi8r/*!*/; +SET TIMESTAMP=1000000000/*!*/; +drop table t1/*!*/; +DELIMITER ; +# End of log file +ROLLBACK /* added by mysqlbinlog */; +/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; drop table t1, t2, t5; End of 5.0 tests diff --git a/mysql-test/r/mysqlcheck.result b/mysql-test/r/mysqlcheck.result index df0835b830c..b2820df8f4c 100644 --- a/mysql-test/r/mysqlcheck.result +++ b/mysql-test/r/mysqlcheck.result @@ -1,3 +1,6 @@ +DROP TABLE IF EXISTS t1; +drop view if exists v1; +drop database if exists client_test_db; mysql.columns_priv OK mysql.db OK mysql.func OK diff --git a/mysql-test/r/mysqltest.result b/mysql-test/r/mysqltest.result index 0b895afe910..0188fe87bbd 100644 --- a/mysql-test/r/mysqltest.result +++ b/mysql-test/r/mysqltest.result @@ -421,6 +421,8 @@ mysqltest: In included file "MYSQLTEST_VARDIR/tmp/mysqltest.sql": At line 7: Con mysqltest: In included file "MYSQLTEST_VARDIR/tmp/mysqltest.sql": At line 3: connection 'test_con1' not found in connection pool mysqltest: In included file "MYSQLTEST_VARDIR/tmp/mysqltest.sql": At line 2: Connection test_con1 already exists connect(localhost,root,,test,MASTER_PORT,MASTER_SOCKET); +show tables; +ERROR 3D000: No database selected Output from mysqltest-x.inc Output from mysqltest-x.inc Output from mysqltest-x.inc diff --git a/mysql-test/r/ndb_lock.result b/mysql-test/r/ndb_lock.result index d5875cb4d47..9a2cea0f512 100644 --- a/mysql-test/r/ndb_lock.result +++ b/mysql-test/r/ndb_lock.result @@ -121,7 +121,7 @@ select * from t1 where x = 1 for update; x y z 1 one 1 select * from t1 where x = 2 for update; -ERROR HY000: Lock wait timeout exceeded; try restarting transaction +Got one of the listed errors rollback; commit; begin; diff --git a/mysql-test/r/rpl_insert_delayed.result b/mysql-test/r/rpl_insert_delayed.result new file mode 100644 index 00000000000..38e2cddd650 --- /dev/null +++ b/mysql-test/r/rpl_insert_delayed.result @@ -0,0 +1,31 @@ +stop slave; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +reset master; +reset slave; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +start slave; +CREATE TABLE t1 (id INT primary key auto_increment, name VARCHAR(64)); +truncate table t1; +insert delayed into t1 values(10, "my name"); +insert delayed into t1 values(10, "is Bond"), (20, "James Bond"); +ERROR 23000: Duplicate entry '10' for key 1 +flush table t1; +select * from t1; +id name +10 my name +select * from t1; +id name +10 my name +delete from t1 where id!=10; +insert delayed into t1 values(20, "is Bond"), (10, "James Bond"); +ERROR 23000: Duplicate entry '10' for key 1 +flush table t1; +select * from t1; +id name +10 my name +20 is Bond +select * from t1; +id name +10 my name +20 is Bond +drop table t1; diff --git a/mysql-test/r/rpl_insert_id.result b/mysql-test/r/rpl_insert_id.result index d133a2ae8ed..a5c8e17f67e 100644 --- a/mysql-test/r/rpl_insert_id.result +++ b/mysql-test/r/rpl_insert_id.result @@ -234,6 +234,64 @@ n b 2 100 3 350 drop table t1; +CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b INT, +UNIQUE(b)); +INSERT INTO t1(b) VALUES(1),(1),(2) ON DUPLICATE KEY UPDATE t1.b=10; +SELECT * FROM t1; +a b +1 10 +2 2 +SELECT * FROM t1; +a b +1 10 +2 2 +drop table t1; +CREATE TABLE t1 ( +id bigint(20) unsigned NOT NULL auto_increment, +field_1 int(10) unsigned NOT NULL, +field_2 varchar(255) NOT NULL, +field_3 varchar(255) NOT NULL, +PRIMARY KEY (id), +UNIQUE KEY field_1 (field_1, field_2) +); +CREATE TABLE t2 ( +field_a int(10) unsigned NOT NULL, +field_b varchar(255) NOT NULL, +field_c varchar(255) NOT NULL +); +INSERT INTO t2 (field_a, field_b, field_c) VALUES (1, 'a', '1a'); +INSERT INTO t2 (field_a, field_b, field_c) VALUES (2, 'b', '2b'); +INSERT INTO t2 (field_a, field_b, field_c) VALUES (3, 'c', '3c'); +INSERT INTO t2 (field_a, field_b, field_c) VALUES (4, 'd', '4d'); +INSERT INTO t2 (field_a, field_b, field_c) VALUES (5, 'e', '5e'); +INSERT INTO t1 (field_1, field_2, field_3) +SELECT t2.field_a, t2.field_b, t2.field_c +FROM t2 +ON DUPLICATE KEY UPDATE +t1.field_3 = t2.field_c; +INSERT INTO t2 (field_a, field_b, field_c) VALUES (6, 'f', '6f'); +INSERT INTO t1 (field_1, field_2, field_3) +SELECT t2.field_a, t2.field_b, t2.field_c +FROM t2 +ON DUPLICATE KEY UPDATE +t1.field_3 = t2.field_c; +SELECT * FROM t1; +id field_1 field_2 field_3 +1 1 a 1a +2 2 b 2b +3 3 c 3c +4 4 d 4d +5 5 e 5e +6 6 f 6f +SELECT * FROM t1; +id field_1 field_2 field_3 +1 1 a 1a +2 2 b 2b +3 3 c 3c +4 4 d 4d +5 5 e 5e +6 6 f 6f +drop table t1, t2; DROP PROCEDURE IF EXISTS p1; DROP TABLE IF EXISTS t1, t2; SELECT LAST_INSERT_ID(0); diff --git a/mysql-test/r/rpl_known_bugs_detection.result b/mysql-test/r/rpl_known_bugs_detection.result new file mode 100644 index 00000000000..c23586c6f61 --- /dev/null +++ b/mysql-test/r/rpl_known_bugs_detection.result @@ -0,0 +1,133 @@ +stop slave; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +reset master; +reset slave; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +start slave; +CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b INT, +UNIQUE(b)); +INSERT INTO t1(b) VALUES(1),(1),(2) ON DUPLICATE KEY UPDATE t1.b=10; +SELECT * FROM t1; +a b +1 10 +2 2 +show slave status;; +Slave_IO_State # +Master_Host 127.0.0.1 +Master_User root +Master_Port # +Connect_Retry 1 +Master_Log_File master-bin.000001 +Read_Master_Log_Pos # +Relay_Log_File # +Relay_Log_Pos # +Relay_Master_Log_File master-bin.000001 +Slave_IO_Running Yes +Slave_SQL_Running No +Replicate_Do_DB +Replicate_Ignore_DB +Replicate_Do_Table +Replicate_Ignore_Table +Replicate_Wild_Do_Table +Replicate_Wild_Ignore_Table +Last_Errno 1105 +Last_Error Error 'master may suffer from http://bugs.mysql.com/bug.php?id=24432 so slave stops; check error log on slave for more info' on query. Default database: 'test'. Query: 'INSERT INTO t1(b) VALUES(1),(1),(2) ON DUPLICATE KEY UPDATE t1.b=10' +Skip_Counter 0 +Exec_Master_Log_Pos 238 +Relay_Log_Space # +Until_Condition None +Until_Log_File +Until_Log_Pos 0 +Master_SSL_Allowed No +Master_SSL_CA_File +Master_SSL_CA_Path +Master_SSL_Cert +Master_SSL_Cipher +Master_SSL_Key +Seconds_Behind_Master # +SELECT * FROM t1; +a b +stop slave; +reset slave; +reset master; +drop table t1; +start slave; +CREATE TABLE t1 ( +id bigint(20) unsigned NOT NULL auto_increment, +field_1 int(10) unsigned NOT NULL, +field_2 varchar(255) NOT NULL, +field_3 varchar(255) NOT NULL, +PRIMARY KEY (id), +UNIQUE KEY field_1 (field_1, field_2) +); +CREATE TABLE t2 ( +field_a int(10) unsigned NOT NULL, +field_b varchar(255) NOT NULL, +field_c varchar(255) NOT NULL +); +INSERT INTO t2 (field_a, field_b, field_c) VALUES (1, 'a', '1a'); +INSERT INTO t2 (field_a, field_b, field_c) VALUES (2, 'b', '2b'); +INSERT INTO t2 (field_a, field_b, field_c) VALUES (3, 'c', '3c'); +INSERT INTO t2 (field_a, field_b, field_c) VALUES (4, 'd', '4d'); +INSERT INTO t2 (field_a, field_b, field_c) VALUES (5, 'e', '5e'); +INSERT INTO t1 (field_1, field_2, field_3) +SELECT t2.field_a, t2.field_b, t2.field_c +FROM t2 +ON DUPLICATE KEY UPDATE +t1.field_3 = t2.field_c; +INSERT INTO t2 (field_a, field_b, field_c) VALUES (6, 'f', '6f'); +INSERT INTO t1 (field_1, field_2, field_3) +SELECT t2.field_a, t2.field_b, t2.field_c +FROM t2 +ON DUPLICATE KEY UPDATE +t1.field_3 = t2.field_c; +SELECT * FROM t1; +id field_1 field_2 field_3 +1 1 a 1a +2 2 b 2b +3 3 c 3c +4 4 d 4d +5 5 e 5e +6 6 f 6f +show slave status;; +Slave_IO_State # +Master_Host 127.0.0.1 +Master_User root +Master_Port # +Connect_Retry 1 +Master_Log_File master-bin.000001 +Read_Master_Log_Pos # +Relay_Log_File # +Relay_Log_Pos # +Relay_Master_Log_File master-bin.000001 +Slave_IO_Running Yes +Slave_SQL_Running No +Replicate_Do_DB +Replicate_Ignore_DB +Replicate_Do_Table +Replicate_Ignore_Table +Replicate_Wild_Do_Table +Replicate_Wild_Ignore_Table +Last_Errno 1105 +Last_Error Error 'master may suffer from http://bugs.mysql.com/bug.php?id=24432 so slave stops; check error log on slave for more info' on query. Default database: 'test'. Query: 'INSERT INTO t1 (field_1, field_2, field_3) +SELECT t2.field_a, t2.field_b, t2.field_c +FROM t2 +ON DUPLICATE KEY UPDATE +t1.field_3 = t2.field_c' +Skip_Counter 0 +Exec_Master_Log_Pos 1270 +Relay_Log_Space # +Until_Condition None +Until_Log_File +Until_Log_Pos 0 +Master_SSL_Allowed No +Master_SSL_CA_File +Master_SSL_CA_Path +Master_SSL_Cert +Master_SSL_Cipher +Master_SSL_Key +Seconds_Behind_Master # +SELECT * FROM t1; +id field_1 field_2 field_3 +drop table t1, t2; +drop table t1, t2; diff --git a/mysql-test/r/rpl_loaddata_charset.result b/mysql-test/r/rpl_loaddata_charset.result new file mode 100644 index 00000000000..929d06e74cf --- /dev/null +++ b/mysql-test/r/rpl_loaddata_charset.result @@ -0,0 +1,37 @@ +stop slave; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +reset master; +reset slave; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +start slave; +create table t1 (a varchar(10) character set utf8); +load data infile '../std_data_ln/loaddata6.dat' into table t1; +set @@character_set_database=koi8r; +load data infile '../std_data_ln/loaddata6.dat' into table t1; +set @@character_set_database=DEFAULT; +load data infile '../std_data_ln/loaddata6.dat' into table t1; +load data infile '../std_data_ln/loaddata6.dat' into table t1; +load data infile '../std_data_ln/loaddata6.dat' into table t1; +set @@character_set_database=koi8r; +load data infile '../std_data_ln/loaddata6.dat' into table t1; +set @@character_set_database=DEFAULT; +load data infile '../std_data_ln/loaddata6.dat' into table t1 character set koi8r; +select hex(a) from t1; +hex(a) +C3BF +D0AA +C3BF +C3BF +C3BF +D0AA +D0AA +select hex(a) from t1; +hex(a) +C3BF +D0AA +C3BF +C3BF +C3BF +D0AA +D0AA +drop table t1; diff --git a/mysql-test/r/rpl_replicate_do.result b/mysql-test/r/rpl_replicate_do.result index f79f6305342..9bacffb0609 100644 --- a/mysql-test/r/rpl_replicate_do.result +++ b/mysql-test/r/rpl_replicate_do.result @@ -41,3 +41,37 @@ select * from t1; ts 2005-08-12 00:00:00 drop table t1; +*** master *** +create table t1 (a int, b int); +create trigger trg1 before insert on t1 for each row set new.b=2; +create table t2 (a int, b int); +create trigger trg2 before insert on t2 for each row set new.b=2; +show tables; +Tables_in_test +t1 +t2 +show triggers; +Trigger Event Table Statement Timing Created sql_mode Definer +trg1 INSERT t1 set new.b=2 BEFORE NULL root@localhost +trg2 INSERT t2 set new.b=2 BEFORE NULL root@localhost +*** slave *** +show tables; +Tables_in_test +t1 +show triggers; +Trigger Event Table Statement Timing Created sql_mode Definer +trg1 INSERT t1 set new.b=2 BEFORE NULL root@localhost +*** master *** +drop trigger trg1; +drop trigger trg2; +show triggers; +Trigger Event Table Statement Timing Created sql_mode Definer +*** slave *** +show tables; +Tables_in_test +t1 +show triggers; +Trigger Event Table Statement Timing Created sql_mode Definer +*** master *** +drop table t1; +drop table t2; diff --git a/mysql-test/r/rpl_user_variables.result b/mysql-test/r/rpl_user_variables.result index 45618688a33..05f2b68042e 100644 --- a/mysql-test/r/rpl_user_variables.result +++ b/mysql-test/r/rpl_user_variables.result @@ -108,4 +108,180 @@ slave-bin.000001 # User var 2 # @`a`=NULL slave-bin.000001 # Query 1 # use `test`; insert into t1 values (@a),(@a),(@a*5) insert into t1 select * FROM (select @var1 union select @var2) AS t2; drop table t1; +End of 4.1 tests. +DROP TABLE IF EXISTS t20; +DROP TABLE IF EXISTS t21; +DROP PROCEDURE IF EXISTS test.insert; +CREATE TABLE t20 (a VARCHAR(20)); +CREATE TABLE t21 (a VARCHAR(20)); +CREATE PROCEDURE test.insert() +BEGIN +IF (@VAR) +THEN +INSERT INTO test.t20 VALUES ('SP_TRUE'); +ELSE +INSERT INTO test.t20 VALUES ('SP_FALSE'); +END IF; +END| +CREATE TRIGGER test.insert_bi BEFORE INSERT +ON test.t20 FOR EACH ROW +BEGIN +IF (@VAR) +THEN +INSERT INTO test.t21 VALUES ('TRIG_TRUE'); +ELSE +INSERT INTO test.t21 VALUES ('TRIG_FALSE'); +END IF; +END| +SET @VAR=0; +CALL test.insert(); +SET @VAR=1; +CALL test.insert(); +On master: Check the tables for correct data +SELECT * FROM t20; +a +SP_FALSE +SP_TRUE +SELECT * FROM t21; +a +TRIG_FALSE +TRIG_TRUE +On slave: Check the tables for correct data and it matches master +SELECT * FROM t20; +a +SP_FALSE +SP_TRUE +SELECT * FROM t21; +a +TRIG_FALSE +TRIG_TRUE +DROP TABLE t20; +DROP TABLE t21; +DROP PROCEDURE test.insert; +DROP TABLE IF EXISTS t1; +DROP FUNCTION IF EXISTS test.square; +CREATE TABLE t1 (i INT); +CREATE FUNCTION test.square() RETURNS INTEGER DETERMINISTIC RETURN (@var * @var); +SET @var = 1; +INSERT INTO t1 VALUES (square()); +SET @var = 2; +INSERT INTO t1 VALUES (square()); +SET @var = 3; +INSERT INTO t1 VALUES (square()); +SET @var = 4; +INSERT INTO t1 VALUES (square()); +SET @var = 5; +INSERT INTO t1 VALUES (square()); +On master: Retrieve the values from the table +SELECT * FROM t1; +i +1 +4 +9 +16 +25 +On slave: Retrieve the values from the table and verify they are the same as on master +SELECT * FROM t1; +i +1 +4 +9 +16 +25 +DROP TABLE t1; +DROP FUNCTION test.square; +DROP TABLE IF EXISTS t1; +DROP FUNCTION IF EXISTS f1; +DROP FUNCTION IF EXISTS f2; +CREATE TABLE t1(a int); +CREATE FUNCTION f1() returns int deterministic +BEGIN +return @a; +END | +CREATE FUNCTION f2() returns int deterministic +BEGIN +IF (@b > 0) then +SET @c = (@a + @b); +else +SET @c = (@a - 1); +END if; +return @c; +END | +SET @a=500; +INSERT INTO t1 values(f1()); +SET @b = 125; +SET @c = 1; +INSERT INTO t1 values(f2()); +On master: Retrieve the values from the table +SELECT * from t1; +a +500 +625 +On slave: Check the tables for correct data and it matches master +SELECT * from t1; +a +500 +625 +DROP TABLE t1; +DROP FUNCTION f1; +DROP FUNCTION f2; +DROP TABLE IF EXISTS t1; +DROP TABLE IF EXISTS t2; +CREATE TABLE t1 (i int); +CREATE TABLE t2 (k int); +CREATE trigger t1_bi before INSERT on t1 for each row +BEGIN +INSERT INTO t2 values (@a); +SET @a:=42; +INSERT INTO t2 values (@a); +END | +SET @a:=100; +INSERT INTO t1 values (5); +On master: Check to see that data was inserted correctly in both tables +SELECT * from t1; +i +5 +SELECT * from t2; +k +100 +42 +On slave: Check the tables for correct data and it matches master +SELECT * from t1; +i +5 +SELECT * from t2; +k +100 +42 +End of 5.0 tests. +DROP TABLE t1; +DROP TABLE t2; +DROP TABLE IF EXISTS t1; +DROP FUNCTION IF EXISTS f1; +DROP FUNCTION IF EXISTS f2; +CREATE TABLE t1 (i INT); +CREATE FUNCTION f1() RETURNS INT RETURN @a; +CREATE FUNCTION f2() RETURNS INT +BEGIN +INSERT INTO t1 VALUES (10 + @a); +RETURN 0; +END| +SET @a:=123; +SELECT f1(), f2(); +f1() f2() +123 0 +On master: Check to see that data was inserted correctly +INSERT INTO t1 VALUES(f1()); +SELECT * FROM t1; +i +133 +123 +On slave: Check the table for correct data and it matches master +SELECT * FROM t1; +i +133 +123 +DROP FUNCTION f1; +DROP FUNCTION f2; +DROP TABLE t1; stop slave; diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result index f50f4859d48..c3132a1b5f6 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -3785,4 +3785,152 @@ case when 1 then cast(1111111111111111111 as unsigned) else 1 end c, coalesce(cast(1111111111111111111 as unsigned), 1) co; i c co 1111111111111111111 1111111111111111111 1111111111111111111 +CREATE TABLE t1 (name varchar(255)); +CREATE TABLE t2 (name varchar(255), n int, KEY (name(3))); +INSERT INTO t1 VALUES ('ccc'), ('bb'), ('cc '), ('aa '), ('aa'); +INSERT INTO t2 VALUES ('bb',1), ('aa',2), ('cc ',3); +INSERT INTO t2 VALUES (concat('cc ', 0x06), 4); +INSERT INTO t2 VALUES ('cc',5), ('bb ',6), ('cc ',7); +SELECT * FROM t2; +name n +bb 1 +aa 2 +cc 3 +cc 4 +cc 5 +bb 6 +cc 7 +SELECT * FROM t2 ORDER BY name; +name n +aa 2 +bb 1 +bb 6 +cc 4 +cc 3 +cc 5 +cc 7 +SELECT name, LENGTH(name), n FROM t2 ORDER BY name; +name LENGTH(name) n +aa 2 2 +bb 2 1 +bb 3 6 +cc 4 4 +cc 5 3 +cc 2 5 +cc 3 7 +EXPLAIN SELECT name, LENGTH(name), n FROM t2 WHERE name='cc '; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ref name name 6 const 3 Using where +SELECT name, LENGTH(name), n FROM t2 WHERE name='cc '; +name LENGTH(name) n +cc 5 3 +cc 2 5 +cc 3 7 +EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 range name name 6 NULL 3 Using where +SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%'; +name LENGTH(name) n +cc 5 3 +cc 4 4 +cc 2 5 +cc 3 7 +EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 range name name 6 NULL 3 Using where; Using filesort +SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name; +name LENGTH(name) n +cc 4 4 +cc 5 3 +cc 2 5 +cc 3 7 +EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 +1 SIMPLE t2 ref name name 6 test.t1.name 2 +SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name; +name name n +ccc NULL NULL +bb bb 1 +bb bb 6 +cc cc 3 +cc cc 5 +cc cc 7 +aa aa 2 +aa aa 2 +DROP TABLE t1,t2; +CREATE TABLE t1 (name text); +CREATE TABLE t2 (name text, n int, KEY (name(3))); +INSERT INTO t1 VALUES ('ccc'), ('bb'), ('cc '), ('aa '), ('aa'); +INSERT INTO t2 VALUES ('bb',1), ('aa',2), ('cc ',3); +INSERT INTO t2 VALUES (concat('cc ', 0x06), 4); +INSERT INTO t2 VALUES ('cc',5), ('bb ',6), ('cc ',7); +SELECT * FROM t2; +name n +bb 1 +aa 2 +cc 3 +cc 4 +cc 5 +bb 6 +cc 7 +SELECT * FROM t2 ORDER BY name; +name n +aa 2 +bb 1 +bb 6 +cc 4 +cc 3 +cc 5 +cc 7 +SELECT name, LENGTH(name), n FROM t2 ORDER BY name; +name LENGTH(name) n +aa 2 2 +bb 2 1 +bb 3 6 +cc 4 4 +cc 5 3 +cc 2 5 +cc 3 7 +EXPLAIN SELECT name, LENGTH(name), n FROM t2 WHERE name='cc '; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ref name name 6 const 3 Using where +SELECT name, LENGTH(name), n FROM t2 WHERE name='cc '; +name LENGTH(name) n +cc 5 3 +cc 2 5 +cc 3 7 +EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 range name name 6 NULL 3 Using where +SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%'; +name LENGTH(name) n +cc 5 3 +cc 4 4 +cc 2 5 +cc 3 7 +EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 range name name 6 NULL 3 Using where; Using filesort +SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name; +name LENGTH(name) n +cc 4 4 +cc 5 3 +cc 2 5 +cc 3 7 +EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 +1 SIMPLE t2 ref name name 6 test.t1.name 2 +SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name; +name name n +ccc NULL NULL +bb bb 1 +bb bb 6 +cc cc 3 +cc cc 5 +cc cc 7 +aa aa 2 +aa aa 2 +DROP TABLE t1,t2; End of 5.0 tests diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index 06f8c019265..7177b4e4432 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -224,7 +224,7 @@ id select_type table type possible_keys key key_len ref rows Extra 2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 3 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 3 Using where Warnings: -Note 1276 Field or reference 't4.a' of SELECT #3 was resolved in SELECT #1 +Note 1276 Field or reference 'test.t4.a' of SELECT #3 was resolved in SELECT #1 Note 1003 select `test`.`t4`.`b` AS `b`,(select avg((`test`.`t2`.`a` + (select min(`test`.`t3`.`a`) AS `min(t3.a)` from `test`.`t3` where (`test`.`t3`.`a` >= `test`.`t4`.`a`)))) AS `avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a))` from `test`.`t2`) AS `(select avg(t2.a+(select min(t3.a) from t3 where t3.a >= t4.a)) from t2)` from `test`.`t4` select * from t3 where exists (select * from t2 where t2.b=t3.a); a @@ -313,8 +313,8 @@ id select_type table type possible_keys key key_len ref rows Extra 3 DEPENDENT UNION t5 ALL NULL NULL NULL NULL 2 Using where NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL Warnings: -Note 1276 Field or reference 't2.a' of SELECT #2 was resolved in SELECT #1 -Note 1276 Field or reference 't2.a' of SELECT #3 was resolved in SELECT #1 +Note 1276 Field or reference 'test.t2.a' of SELECT #2 was resolved in SELECT #1 +Note 1276 Field or reference 'test.t2.a' of SELECT #3 was resolved in SELECT #1 Note 1003 select (select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = `test`.`t2`.`a`) union select `test`.`t5`.`a` AS `a` from `test`.`t5` where (`test`.`t5`.`a` = `test`.`t2`.`a`)) AS `(select a from t1 where t1.a=t2.a union select a from t5 where t5.a=t2.a)`,`test`.`t2`.`a` AS `a` from `test`.`t2` select (select a from t1 where t1.a=t2.a union all select a from t5 where t5.a=t2.a), a from t2; ERROR 21000: Subquery returns more than 1 row @@ -330,9 +330,9 @@ patient_uq clinic_uq explain extended select * from t6 where exists (select * from t7 where uq = clinic_uq); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t6 ALL NULL NULL NULL NULL 4 Using where -2 DEPENDENT SUBQUERY t7 eq_ref PRIMARY PRIMARY 4 test.t6.clinic_uq 1 Using index +2 DEPENDENT SUBQUERY t7 eq_ref PRIMARY PRIMARY 4 t6.clinic_uq 1 Using where; Using index Warnings: -Note 1276 Field or reference 'clinic_uq' of SELECT #2 was resolved in SELECT #1 +Note 1276 Field or reference 'test.t6.clinic_uq' of SELECT #2 was resolved in SELECT #1 Note 1003 select `test`.`t6`.`patient_uq` AS `patient_uq`,`test`.`t6`.`clinic_uq` AS `clinic_uq` from `test`.`t6` where exists(select 1 AS `Not_used` from `test`.`t7` where (`test`.`t7`.`uq` = `test`.`t6`.`clinic_uq`)) select * from t1 where a= (select a from t2,t4 where t2.b=t4.b); ERROR 23000: Column 'a' in field list is ambiguous @@ -868,7 +868,7 @@ explain extended select (select a+1) from t1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Warnings: -Note 1276 Field or reference 'a' of SELECT #2 was resolved in SELECT #1 +Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 Note 1249 Select 2 was reduced during optimization Note 1003 select (`test`.`t1`.`a` + 1) AS `(select a+1)` from `test`.`t1` select (select a+1) from t1; @@ -1741,9 +1741,9 @@ Note 1003 select `test`.`t1`.`id` AS `id`,`test`.`t1`.`text` AS `text` from `tes explain extended select * from t1 as tt where not exists (select id from t1 where id < 8 and (id = tt.id or id is null) having id is not null); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY tt ALL NULL NULL NULL NULL 12 Using where -2 DEPENDENT SUBQUERY t1 eq_ref PRIMARY PRIMARY 4 test.tt.id 1 Using where; Using index +2 DEPENDENT SUBQUERY t1 eq_ref PRIMARY PRIMARY 4 tt.id 1 Using where; Using index Warnings: -Note 1276 Field or reference 'tt.id' of SELECT #2 was resolved in SELECT #1 +Note 1276 Field or reference 'test.tt.id' of SELECT #2 was resolved in SELECT #1 Note 1003 select `test`.`tt`.`id` AS `id`,`test`.`tt`.`text` AS `text` from `test`.`t1` `tt` where (not(exists(select `test`.`t1`.`id` AS `id` from `test`.`t1` where ((`test`.`t1`.`id` < 8) and (`test`.`t1`.`id` = `test`.`tt`.`id`)) having (`test`.`t1`.`id` is not null)))) insert into t1 (id, text) values (1000, 'text1000'), (1001, 'text1001'); create table t2 (id int not null, text varchar(20) not null default '', primary key (id)); @@ -2279,7 +2279,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY up ALL NULL NULL NULL NULL 2 Using where 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where Warnings: -Note 1276 Field or reference 'up.a' of SELECT #2 was resolved in SELECT #1 +Note 1276 Field or reference 'test.up.a' of SELECT #2 was resolved in SELECT #1 Note 1003 select `test`.`up`.`a` AS `a`,`test`.`up`.`b` AS `b` from `test`.`t1` `up` where exists(select 1 AS `Not_used` from `test`.`t1` where (`test`.`t1`.`a` = `test`.`up`.`a`)) drop table t1; CREATE TABLE t1 (t1_a int); @@ -3712,3 +3712,45 @@ bb 2 cc 3 dd 1 DROP TABLE t1,t2,t3; +CREATE TABLE t1(f1 int); +CREATE TABLE t2(f2 int, f21 int, f3 timestamp); +INSERT INTO t1 VALUES (1),(1),(2),(2); +INSERT INTO t2 VALUES (1,1,"2004-02-29 11:11:11"), (2,2,"2004-02-29 11:11:11"); +SELECT ((SELECT f2 FROM t2 WHERE f21=f1 LIMIT 1) * COUNT(f1)) AS sq FROM t1 GROUP BY f1; +sq +2 +4 +SELECT (SELECT SUM(1) FROM t2 ttt GROUP BY t2.f3 LIMIT 1) AS tt FROM t2; +tt +2 +2 +PREPARE stmt1 FROM 'SELECT ((SELECT f2 FROM t2 WHERE f21=f1 LIMIT 1) * COUNT(f1)) AS sq FROM t1 GROUP BY f1'; +EXECUTE stmt1; +sq +2 +4 +EXECUTE stmt1; +sq +2 +4 +DEALLOCATE PREPARE stmt1; +SELECT f2, AVG(f21), +(SELECT t.f3 FROM t2 AS t WHERE t2.f2=t.f2 AND t.f3=MAX(t2.f3)) AS test +FROM t2 GROUP BY f2; +f2 AVG(f21) test +1 1.0000 2004-02-29 11:11:11 +2 2.0000 2004-02-29 11:11:11 +DROP TABLE t1,t2; +CREATE TABLE t1 (a int, b INT, c CHAR(10) NOT NULL); +INSERT INTO t1 VALUES +(1,1,'a'), (1,2,'b'), (1,3,'c'), (1,4,'d'), (1,5,'e'), +(2,1,'f'), (2,2,'g'), (2,3,'h'), (3,4,'i'), (3,3,'j'), +(3,2,'k'), (3,1,'l'), (1,9,'m'); +SELECT a, MAX(b), +(SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b)) AS test +FROM t1 GROUP BY a; +a MAX(b) test +1 9 m +2 3 h +3 4 i +DROP TABLE t1; diff --git a/mysql-test/r/subselect3.result b/mysql-test/r/subselect3.result index 29143b9e504..b975ea8cbdc 100644 --- a/mysql-test/r/subselect3.result +++ b/mysql-test/r/subselect3.result @@ -29,7 +29,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 5 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 Using where; Using temporary; Using filesort Warnings: -Note 1276 Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1 +Note 1276 Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1 Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`oref` AS `oref`,<in_optimizer>(`test`.`t2`.`a`,<exists>(select max(`test`.`t1`.`ie`) AS `max(ie)` from `test`.`t1` where (`test`.`t1`.`oref` = `test`.`t2`.`oref`) group by `test`.`t1`.`grp` having trigcond((<cache>(`test`.`t2`.`a`) = <ref_null_helper>(max(`test`.`t1`.`ie`)))))) AS `Z` from `test`.`t2` explain extended select a, oref from t2 @@ -38,7 +38,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 5 Using where 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 6 Using where; Using temporary; Using filesort Warnings: -Note 1276 Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1 +Note 1276 Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1 Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`oref` AS `oref` from `test`.`t2` where <in_optimizer>(`test`.`t2`.`a`,<exists>(select max(`test`.`t1`.`ie`) AS `max(ie)` from `test`.`t1` where (`test`.`t1`.`oref` = `test`.`t2`.`oref`) group by `test`.`t1`.`grp` having (<cache>(`test`.`t2`.`a`) = <ref_null_helper>(max(`test`.`t1`.`ie`))))) select a, oref, a in ( select max(ie) from t1 where oref=t2.oref group by grp union @@ -91,7 +91,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 4 2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 2 Using where; Full scan on NULL key Warnings: -Note 1276 Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1 +Note 1276 Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1 Note 1003 select `test`.`t2`.`oref` AS `oref`,`test`.`t2`.`a` AS `a`,<in_optimizer>(`test`.`t2`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a checking NULL where (`test`.`t1`.`oref` = `test`.`t2`.`oref`) having trigcond(<is_not_null_test>(`test`.`t1`.`a`))))) AS `Z` from `test`.`t2` flush status; select oref, a from t2 where a in (select a from t1 where oref=t2.oref); @@ -156,7 +156,7 @@ id select_type table type possible_keys key key_len ref rows Extra 2 DEPENDENT SUBQUERY t1 ref_or_null a a 5 func 4 Using where; Full scan on NULL key 2 DEPENDENT SUBQUERY t2 ref a a 5 test.t1.b 1 Using where Warnings: -Note 1276 Field or reference 't3.oref' of SELECT #2 was resolved in SELECT #1 +Note 1276 Field or reference 'test.t3.oref' of SELECT #2 was resolved in SELECT #1 Note 1003 select `test`.`t3`.`a` AS `a`,`test`.`t3`.`oref` AS `oref`,<in_optimizer>(`test`.`t3`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t2`.`b` = `test`.`t3`.`oref`) and trigcond(((<cache>(`test`.`t3`.`a`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`)))) having trigcond(<is_not_null_test>(`test`.`t1`.`a`)))) AS `Z` from `test`.`t3` drop table t1, t2, t3; create table t1 (a int NOT NULL, b int NOT NULL, key(a)); @@ -184,7 +184,7 @@ id select_type table type possible_keys key key_len ref rows Extra 2 DEPENDENT SUBQUERY t1 ref a a 4 func 2 Using where; Full scan on NULL key 2 DEPENDENT SUBQUERY t2 ref a a 4 test.t1.b 1 Using where Warnings: -Note 1276 Field or reference 't3.oref' of SELECT #2 was resolved in SELECT #1 +Note 1276 Field or reference 'test.t3.oref' of SELECT #2 was resolved in SELECT #1 Note 1003 select `test`.`t3`.`a` AS `a`,`test`.`t3`.`oref` AS `oref`,<in_optimizer>(`test`.`t3`.`a`,<exists>(select 1 AS `Not_used` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`a` = `test`.`t1`.`b`) and (`test`.`t2`.`b` = `test`.`t3`.`oref`) and trigcond((<cache>(`test`.`t3`.`a`) = `test`.`t1`.`a`))))) AS `Z` from `test`.`t3` drop table t1,t2,t3; create table t1 (oref int, grp int); @@ -240,7 +240,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 2 DEPENDENT SUBQUERY t1 index_subquery a a 5 func 2 Using where; Full scan on NULL key Warnings: -Note 1276 Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1 +Note 1276 Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1 Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`oref` AS `oref`,<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`b`),<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on a checking NULL where ((`test`.`t1`.`c` = `test`.`t2`.`oref`) and trigcond(((<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`))) and trigcond(((<cache>(`test`.`t2`.`b`) = `test`.`t1`.`b`) or isnull(`test`.`t1`.`b`)))) having (trigcond(<is_not_null_test>(`test`.`t1`.`a`)) and trigcond(<is_not_null_test>(`test`.`t1`.`b`)))))) AS `Z` from `test`.`t2` select a,b, oref, (a,b) in (select a,b from t1 where c=t2.oref) Z from t2; a b oref Z @@ -257,7 +257,7 @@ id select_type table type possible_keys key key_len ref rows Extra 2 DEPENDENT SUBQUERY t1 ref_or_null a a 5 func 2 Using where; Full scan on NULL key 2 DEPENDENT SUBQUERY t4 ALL NULL NULL NULL NULL 100 Using where Warnings: -Note 1276 Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1 +Note 1276 Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1 Note 1003 select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`oref` AS `oref`,<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`b`),<exists>(select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` join `test`.`t4` where ((`test`.`t1`.`c` = `test`.`t2`.`oref`) and trigcond(((<cache>(`test`.`t2`.`a`) = `test`.`t1`.`a`) or isnull(`test`.`t1`.`a`))) and trigcond(((<cache>(`test`.`t2`.`b`) = `test`.`t1`.`b`) or isnull(`test`.`t1`.`b`)))) having (trigcond(<is_not_null_test>(`test`.`t1`.`a`)) and trigcond(<is_not_null_test>(`test`.`t1`.`b`))))) AS `Z` from `test`.`t2` select a,b, oref, (a,b) in (select a,b from t1,t4 where c=t2.oref) Z @@ -302,7 +302,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 8 Using where 2 DEPENDENT SUBQUERY t1 index_subquery idx idx 5 func 4 Using where; Full scan on NULL key Warnings: -Note 1276 Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1 +Note 1276 Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1 Note 1003 select `test`.`t2`.`oref` AS `oref`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`b`),<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on idx checking NULL where ((`test`.`t1`.`oref` = `test`.`t2`.`oref`) and trigcond(((<cache>(`test`.`t2`.`a`) = `test`.`t1`.`ie1`) or isnull(`test`.`t1`.`ie1`))) and trigcond(((<cache>(`test`.`t2`.`b`) = `test`.`t1`.`ie2`) or isnull(`test`.`t1`.`ie2`)))) having (trigcond(<is_not_null_test>(`test`.`t1`.`ie1`)) and trigcond(<is_not_null_test>(`test`.`t1`.`ie2`)))))) AS `Z` from `test`.`t2` where ((`test`.`t2`.`b` = 10) and (`test`.`t2`.`a` = 10)) drop table t1, t2; create table t1 (oref char(4), grp int, ie int); @@ -432,7 +432,7 @@ alter table t1 add index idx(oref,ie); explain select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 7 -2 DEPENDENT SUBQUERY t1 ref_or_null idx idx 10 test.t2.oref,func 4 Using where; Using index; Full scan on NULL key +2 DEPENDENT SUBQUERY t1 ref_or_null idx idx 10 t2.oref,func 4 Using where; Using index; Full scan on NULL key select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2; oref a Z ee NULL NULL @@ -457,7 +457,7 @@ group by grp having min(ie) > 1) Z from t2; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 7 -2 DEPENDENT SUBQUERY t1 ref idx idx 5 test.t2.oref 2 Using where; Using temporary; Using filesort +2 DEPENDENT SUBQUERY t1 ref idx idx 5 t2.oref 2 Using where; Using temporary; Using filesort select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by grp having min(ie) > 1) Z @@ -572,7 +572,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 7 2 DEPENDENT SUBQUERY t1 index_subquery idx idx 5 func 4 Using where; Full scan on NULL key Warnings: -Note 1276 Field or reference 't2.oref' of SELECT #2 was resolved in SELECT #1 +Note 1276 Field or reference 'test.t2.oref' of SELECT #2 was resolved in SELECT #1 Note 1003 select `test`.`t2`.`oref` AS `oref`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`b`),<exists>(<index_lookup>(<cache>(`test`.`t2`.`a`) in t1 on idx checking NULL where ((`test`.`t1`.`oref` = `test`.`t2`.`oref`) and trigcond(((<cache>(`test`.`t2`.`a`) = `test`.`t1`.`ie1`) or isnull(`test`.`t1`.`ie1`))) and trigcond(((<cache>(`test`.`t2`.`b`) = `test`.`t1`.`ie2`) or isnull(`test`.`t1`.`ie2`)))) having (trigcond(<is_not_null_test>(`test`.`t1`.`ie1`)) and trigcond(<is_not_null_test>(`test`.`t1`.`ie2`)))))) AS `Z` from `test`.`t2` drop table t1,t2; create table t1 (oref char(4), grp int, ie int primary key); diff --git a/mysql-test/r/type_blob.result b/mysql-test/r/type_blob.result index 95ba9bb6f25..c72ee005428 100644 --- a/mysql-test/r/type_blob.result +++ b/mysql-test/r/type_blob.result @@ -610,12 +610,12 @@ create table t1 (id integer primary key auto_increment, txt text, index txt_inde insert into t1 (txt) values ('Chevy'), ('Chevy '), (NULL); select * from t1 where txt='Chevy' or txt is NULL; id txt -3 NULL 1 Chevy 2 Chevy +3 NULL explain select * from t1 where txt='Chevy' or txt is NULL; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range txt_index txt_index 23 NULL 2 Using where +1 SIMPLE t1 ref_or_null txt_index txt_index 23 const 2 Using where select * from t1 where txt='Chevy '; id txt 1 Chevy diff --git a/mysql-test/r/update.result b/mysql-test/r/update.result index 9e2bc52657a..b3c5760e2c9 100644 --- a/mysql-test/r/update.result +++ b/mysql-test/r/update.result @@ -434,3 +434,22 @@ Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 21 DROP TABLE t1; +CREATE TABLE t1 ( +a INT(11), +quux decimal( 31, 30 ), +UNIQUE KEY bar (a), +KEY quux (quux) +); +INSERT INTO +t1 ( a, quux ) +VALUES +( 1, 1 ), +( 2, 0.1 ); +INSERT INTO t1( a ) +SELECT @newA := 1 + a FROM t1 WHERE quux <= 0.1; +SELECT * FROM t1; +a quux +1 1.000000000000000000000000000000 +2 0.100000000000000000000000000000 +3 NULL +DROP TABLE t1; diff --git a/mysql-test/r/wait_timeout.result b/mysql-test/r/wait_timeout.result index b865a17454d..6fbff82f908 100644 --- a/mysql-test/r/wait_timeout.result +++ b/mysql-test/r/wait_timeout.result @@ -6,7 +6,7 @@ select 1; 1 1 select 2; -ERROR HY000: MySQL server has gone away +Got one of the listed errors select 3; 3 3 @@ -14,7 +14,7 @@ select 1; 1 1 select 2; -ERROR HY000: MySQL server has gone away +Got one of the listed errors select 3; 3 3 diff --git a/mysql-test/std_data/loaddata6.dat b/mysql-test/std_data/loaddata6.dat new file mode 100644 index 00000000000..29e181ebb88 --- /dev/null +++ b/mysql-test/std_data/loaddata6.dat @@ -0,0 +1 @@ +ÿ diff --git a/mysql-test/t/blackhole.test b/mysql-test/t/blackhole.test index e40b84eb5cd..4bafad2d777 100644 --- a/mysql-test/t/blackhole.test +++ b/mysql-test/t/blackhole.test @@ -127,4 +127,19 @@ show binlog events; drop table t1,t2,t3; +# +#Bug#19717: DELETE Query Error on BLACKHOLE when using WHERE on column with UNIQUE INDEX +# +CREATE TABLE t1(a INT, b INT) ENGINE=BLACKHOLE; +DELETE FROM t1 WHERE a=10; +ALTER TABLE t1 ADD INDEX(a); +DELETE FROM t1 WHERE a=10; +ALTER TABLE t1 DROP INDEX a; +ALTER TABLE t1 ADD UNIQUE INDEX(a); +DELETE FROM t1 WHERE a=10; +ALTER TABLE t1 DROP INDEX a; +ALTER TABLE t1 ADD PRIMARY KEY(a); +DELETE FROM t1 WHERE a=10; +DROP TABLE t1; + # End of 4.1 tests diff --git a/mysql-test/t/ctype_cp932.test b/mysql-test/t/ctype_cp932.test index 440ce02af9e..48376b8e78f 100644 --- a/mysql-test/t/ctype_cp932.test +++ b/mysql-test/t/ctype_cp932.test @@ -427,3 +427,14 @@ drop table t1; --exec $MYSQL --default-character-set=utf8 test -e "charset cp932; select 'ƒ\'" --exec $MYSQL --default-character-set=utf8 test -e "/*charset cp932 */; set character_set_client= cp932; select 'ƒ\'" --exec $MYSQL --default-character-set=utf8 test -e "/*!\C cp932 */; set character_set_client= cp932; select 'ƒ\'" + +# +# Bug#25815 Data truncated for column TEXT +# +set names utf8; +create table t1 (a text) default character set cp932; +insert into t1 values (_utf8 0xE38182); +show warnings; +select * from t1; +select hex(a) from t1; +drop table t1; diff --git a/mysql-test/t/ctype_cp932_binlog.test b/mysql-test/t/ctype_cp932_binlog.test index 3bbbe94f0e3..ee0e588fdae 100644 --- a/mysql-test/t/ctype_cp932_binlog.test +++ b/mysql-test/t/ctype_cp932_binlog.test @@ -51,7 +51,7 @@ CALL bug18293("Foo's a Bar", _cp932 0xED40ED41ED42, 47.93)| SELECT HEX(s1),HEX(s2),d FROM t4| DROP PROCEDURE bug18293| DROP TABLE t4| -SHOW BINLOG EVENTS FROM 393| +SHOW BINLOG EVENTS FROM 402| delimiter ;| # End of 5.0 tests diff --git a/mysql-test/t/disabled.def b/mysql-test/t/disabled.def index 99ba55a7e2b..102c517dd46 100644 --- a/mysql-test/t/disabled.def +++ b/mysql-test/t/disabled.def @@ -13,4 +13,3 @@ im_daemon_life_cycle : Bug#24415 see note: [19 Dec 23:17] Trudy Pelzer ndb_load : Bug#17233 user_limits : Bug#23921 random failure of user_limits.test - diff --git a/mysql-test/t/innodb-replace.test b/mysql-test/t/innodb-replace.test index 51b70f34b65..d44ede65ce8 100644 --- a/mysql-test/t/innodb-replace.test +++ b/mysql-test/t/innodb-replace.test @@ -12,10 +12,10 @@ drop table if exists t1; create table t1 (c1 char(5) unique not null, c2 int, stamp timestamp) engine=innodb; select * from t1; --error 1031 -replace delayed into t1 (c1, c2) values ( "text1","11"),( "text2","12"); +replace delayed into t1 (c1, c2) values ( "text1","11"); select * from t1; --error 1031 -replace delayed into t1 (c1, c2) values ( "text1","12"),( "text2","13"),( "text3","14", "a" ),( "text4","15", "b" ); +replace delayed into t1 (c1, c2) values ( "text1","12"); select * from t1; drop table t1; diff --git a/mysql-test/t/insert_select.test b/mysql-test/t/insert_select.test index 5c60fc8e1f0..31508b3d6c4 100644 --- a/mysql-test/t/insert_select.test +++ b/mysql-test/t/insert_select.test @@ -292,3 +292,18 @@ select @@identity; insert ignore t1(f2) select 1; select @@identity; drop table t1; + +# +# Bug#16630: wrong result, when INSERT t1 SELECT ... FROM t1 ON DUPLICATE +# +CREATE TABLE t1 (f1 INT, f2 INT ); +CREATE TABLE t2 (f1 INT PRIMARY KEY, f2 INT); +INSERT INTO t1 VALUES (1,1),(2,2),(10,10); +INSERT INTO t2 (f1, f2) SELECT f1, f2 FROM t1; +INSERT INTO t2 (f1, f2) + SELECT f1, f1 FROM t2 src WHERE f1 < 2 + ON DUPLICATE KEY UPDATE f1 = 100 + src.f1; +SELECT * FROM t2; +DROP TABLE t1, t2; + + diff --git a/mysql-test/t/insert_update.test b/mysql-test/t/insert_update.test index b3813864464..4581cc7a875 100644 --- a/mysql-test/t/insert_update.test +++ b/mysql-test/t/insert_update.test @@ -139,3 +139,26 @@ INSERT INTO t1 VALUES (45, 1) ON DUPLICATE KEY UPDATE b = SELECT * FROM t1; DROP TABLE t1; + +# +# Bug#25831: Deficiencies in INSERT ... SELECT ... field name resolving. +# +CREATE TABLE t1 (i INT PRIMARY KEY, j INT); +--error ER_BAD_FIELD_ERROR +INSERT INTO t1 SELECT 1, j; +DROP TABLE t1; + +CREATE TABLE t1 (i INT PRIMARY KEY, j INT); +CREATE TABLE t2 (a INT, b INT); +CREATE TABLE t3 (a INT, c INT); +INSERT INTO t1 SELECT 1, a FROM t2 NATURAL JOIN t3 + ON DUPLICATE KEY UPDATE j= a; +DROP TABLE t1,t2,t3; + +CREATE TABLE t1 (i INT PRIMARY KEY, j INT); +CREATE TABLE t2 (a INT); +INSERT INTO t1 VALUES (1, 1); +INSERT INTO t2 VALUES (1), (3); +--error ER_BAD_FIELD_ERROR +INSERT INTO t1 SELECT 1, COUNT(*) FROM t2 ON DUPLICATE KEY UPDATE j= a; +DROP TABLE t1,t2; diff --git a/mysql-test/t/keywords.test b/mysql-test/t/keywords.test index de0159a950e..46933a230ee 100644 --- a/mysql-test/t/keywords.test +++ b/mysql-test/t/keywords.test @@ -19,3 +19,25 @@ select events.binlog from events; drop table events; # End of 4.1 tests + + +# +# Bug#12204 - CONNECTION should not be a reserved word +# + +create table t1 (connection int, b int); +delimiter |; +create procedure p1() +begin + declare connection int; + select max(t1.connection) into connection from t1; + select concat("max=",connection) 'p1'; +end| +delimiter ;| +insert into t1 (connection) values (1); +call p1(); +drop procedure p1; +drop table t1; + + +# End of 5.0 tests diff --git a/mysql-test/t/mysql.test b/mysql-test/t/mysql.test index da9323805b3..1af7c6381fe 100644 --- a/mysql-test/t/mysql.test +++ b/mysql-test/t/mysql.test @@ -254,4 +254,14 @@ DELIMITER \\ EOF --exec $MYSQL < $MYSQLTEST_VARDIR/tmp/bug21412.sql 2>&1 +# +# Some coverage of not normally used parts +# + +--disable_query_log +--exec $MYSQL test -e "show status" 2>&1 > /dev/null +--exec $MYSQL --help 2>&1 > /dev/null +--exec $MYSQL --version 2>&1 > /dev/null +--enable_quary_log + --echo End of 5.0 tests diff --git a/mysql-test/t/mysqlbinlog.test b/mysql-test/t/mysqlbinlog.test index b230b918599..0d61a36af8c 100644 --- a/mysql-test/t/mysqlbinlog.test +++ b/mysql-test/t/mysqlbinlog.test @@ -7,7 +7,7 @@ set timestamp=1000000000; --disable_warnings -drop table if exists t1,t2; +drop table if exists t1,t2,t3,t4,t5,t03,t04; --enable_warnings create table t1 (word varchar(20)); @@ -106,6 +106,7 @@ select "--- reading stdin --" as ""; --replace_result $MYSQL_TEST_DIR MYSQL_TEST_DIR --exec $MYSQL_BINLOG --short-form --position=79 - < $MYSQL_TEST_DIR/std_data/trunc_binlog.000001 +drop table t1,t2; # #BUG#14157: utf8 encoding in binlog without set character_set_client @@ -126,6 +127,7 @@ EOF # should use default-character-set same as the server. --exec $MYSQL_BINLOG --short-form $MYSQLTEST_VARDIR/log/master-bin.000004 | $MYSQL select * from t5 /* must be (1),(1) */; +drop table t5; # # Bug#22645 LC_TIME_NAMES: Statement not replicated @@ -133,7 +135,6 @@ select * from t5 /* must be (1),(1) */; # lc_time_names dependent values correctly # flush logs; -drop table if exists t5; create table t5 (c1 int, c2 varchar(128) character set latin1 not null); insert into t5 values (1, date_format('2001-01-01','%W')); set lc_time_names=de_DE; @@ -145,6 +146,8 @@ flush logs; drop table t5; --exec $MYSQL_BINLOG --short-form $MYSQLTEST_VARDIR/log/master-bin.000005 | $MYSQL select * from t5 order by c1; +drop table t5; + # # Bug#20396 Bin Log does not get DELIMETER cmd - Recover StoredProc fails # @@ -169,7 +172,37 @@ call p1(); call p1(); drop procedure p1; -# clean up +# +# Some coverage of not normally used parts +# +--disable_query_log +--exec $MYSQL_BINLOG --version 2>&1 > /dev/null +--exec $MYSQL_BINLOG --help 2>&1 > /dev/null +--enable_query_log drop table t1, t2, t5; +# +# Bug#15126 character_set_database is not replicated +# (LOAD DATA INFILE need it) +# + +flush logs; +create table t1 (a varchar(64) character set utf8); +load data infile '../std_data_ln/loaddata6.dat' into table t1; +set character_set_database=koi8r; +load data infile '../std_data_ln/loaddata6.dat' into table t1; +set character_set_database=latin1; +load data infile '../std_data_ln/loaddata6.dat' into table t1; +load data infile '../std_data_ln/loaddata6.dat' into table t1; +set character_set_database=koi8r; +load data infile '../std_data_ln/loaddata6.dat' into table t1; +set character_set_database=latin1; +load data infile '../std_data_ln/loaddata6.dat' into table t1; +load data infile '../std_data_ln/loaddata6.dat' into table t1 character set koi8r; +select hex(a) from t1; +drop table t1; +flush logs; +--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR +--exec $MYSQL_BINLOG --short-form $MYSQLTEST_VARDIR/log/master-bin.000011 + --echo End of 5.0 tests diff --git a/mysql-test/t/mysqlcheck.test b/mysql-test/t/mysqlcheck.test index 338e16363ea..f4e18d4004f 100644 --- a/mysql-test/t/mysqlcheck.test +++ b/mysql-test/t/mysqlcheck.test @@ -1,3 +1,12 @@ +# Clean up after previous tests +# + +--disable_warnings +DROP TABLE IF EXISTS t1; +drop view if exists v1; +drop database if exists client_test_db; +--enable_warnings + # Embedded server doesn't support external clients --source include/not_embedded.inc diff --git a/mysql-test/t/mysqltest.test b/mysql-test/t/mysqltest.test index 0f4a498e702..1914f2794e2 100644 --- a/mysql-test/t/mysqltest.test +++ b/mysql-test/t/mysqltest.test @@ -1288,8 +1288,16 @@ EOF connect (con1,localhost,root,,); connection default; connection con1; +disconnect con1; --enable_abort_on_error +# Test connect without a database +connect (con2,localhost,root,,*NO-ONE*); +--error ER_NO_DB_ERROR +show tables; +disconnect con2; +connection default; + # ---------------------------------------------------------------------------- # Test mysqltest arguments # ---------------------------------------------------------------------------- @@ -1716,5 +1724,17 @@ EOF --exec echo "echo Some output; exit; echo Not this;" | $MYSQL_TEST 2>&1 +# ---------------------------------------------------------------------------- +# Some coverage tests +# ---------------------------------------------------------------------------- + +--disable_query_log +--exec $MYSQL_TEST --help 2>&1 > /dev/null +--exec $MYSQL_TEST --version 2>&1 > /dev/null +--enable_quary_log +--disable_abort_on_error +--error 1 +--exec $MYSQL_TEST a b c 2>&1 > /dev/null +--enable_abort_on_error --echo End of tests diff --git a/mysql-test/t/ndb_lock.test b/mysql-test/t/ndb_lock.test index 8dc02dc20cb..bd35a7d28c3 100644 --- a/mysql-test/t/ndb_lock.test +++ b/mysql-test/t/ndb_lock.test @@ -149,7 +149,7 @@ begin; # Have to check with pk access here since scans take locks on # all rows and then release them in chunks select * from t1 where x = 1 for update; ---error 1205 +--error 1105,1205 select * from t1 where x = 2 for update; rollback; diff --git a/mysql-test/t/rpl_insert_delayed.test b/mysql-test/t/rpl_insert_delayed.test new file mode 100644 index 00000000000..3f72f3a3625 --- /dev/null +++ b/mysql-test/t/rpl_insert_delayed.test @@ -0,0 +1,67 @@ +--source include/master-slave.inc +--source include/not_embedded.inc +--source include/not_windows.inc + +connection master; + +let $binlog_format_statement=1; + +CREATE TABLE t1 (id INT primary key auto_increment, name VARCHAR(64)); + +sync_slave_with_master; + +# +# BUG#26116 "If multi-row INSERT DELAYED has errors, +# statement-based binlogging breaks"; +# happened only in statement-based binlogging. +# + +connection master; +truncate table t1; +# first scenario: duplicate on first row +insert delayed into t1 values(10, "my name"); +if ($binlog_format_statement) +{ + # statement below will be converted to non-delayed INSERT and so + # will stop at first error, guaranteeing replication. + --error ER_DUP_ENTRY + insert delayed into t1 values(10, "is Bond"), (20, "James Bond"); +} +if (!$binlog_format_statement) +{ + insert delayed into t1 values(10, "is Bond"), (20, "James Bond"); +} +flush table t1; # to wait for INSERT DELAYED to be done +select * from t1; +sync_slave_with_master; +# when bug existed in statement-based binlogging, t1 on slave had +# different content from on master +select * from t1; + +# second scenario: duplicate on second row +connection master; +delete from t1 where id!=10; +if ($binlog_format_statement) +{ + # statement below will be converted to non-delayed INSERT and so + # will be binlogged with its ER_DUP_ENTRY error code, guaranteeing + # replication (slave will hit the same error code and so be fine). + --error ER_DUP_ENTRY + insert delayed into t1 values(20, "is Bond"), (10, "James Bond"); +} +if (!$binlog_format_statement) +{ + insert delayed into t1 values(20, "is Bond"), (10, "James Bond"); +} +flush table t1; # to wait for INSERT DELAYED to be done +select * from t1; +sync_slave_with_master; +# when bug existed in statement-based binlogging, query was binlogged +# with error_code=0 so slave stopped +select * from t1; + +# clean up +connection master; +drop table t1; +sync_slave_with_master; +connection master; diff --git a/mysql-test/t/rpl_insert_id.test b/mysql-test/t/rpl_insert_id.test index 331a913256c..be2948e9678 100644 --- a/mysql-test/t/rpl_insert_id.test +++ b/mysql-test/t/rpl_insert_id.test @@ -246,6 +246,59 @@ connection master; drop table t1; # +# BUG#24432 "INSERT... ON DUPLICATE KEY UPDATE skips auto_increment values" +# + +# testcase with INSERT VALUES +CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b INT, +UNIQUE(b)); +INSERT INTO t1(b) VALUES(1),(1),(2) ON DUPLICATE KEY UPDATE t1.b=10; +SELECT * FROM t1; +sync_slave_with_master; +SELECT * FROM t1; +connection master; +drop table t1; + +# tescase with INSERT SELECT +CREATE TABLE t1 ( + id bigint(20) unsigned NOT NULL auto_increment, + field_1 int(10) unsigned NOT NULL, + field_2 varchar(255) NOT NULL, + field_3 varchar(255) NOT NULL, + PRIMARY KEY (id), + UNIQUE KEY field_1 (field_1, field_2) +); +CREATE TABLE t2 ( + field_a int(10) unsigned NOT NULL, + field_b varchar(255) NOT NULL, + field_c varchar(255) NOT NULL +); +INSERT INTO t2 (field_a, field_b, field_c) VALUES (1, 'a', '1a'); +INSERT INTO t2 (field_a, field_b, field_c) VALUES (2, 'b', '2b'); +INSERT INTO t2 (field_a, field_b, field_c) VALUES (3, 'c', '3c'); +INSERT INTO t2 (field_a, field_b, field_c) VALUES (4, 'd', '4d'); +INSERT INTO t2 (field_a, field_b, field_c) VALUES (5, 'e', '5e'); +# Updating table t1 based on values from table t2 +INSERT INTO t1 (field_1, field_2, field_3) +SELECT t2.field_a, t2.field_b, t2.field_c +FROM t2 +ON DUPLICATE KEY UPDATE +t1.field_3 = t2.field_c; +# Inserting new record into t2 +INSERT INTO t2 (field_a, field_b, field_c) VALUES (6, 'f', '6f'); +# Updating t1 again +INSERT INTO t1 (field_1, field_2, field_3) +SELECT t2.field_a, t2.field_b, t2.field_c +FROM t2 +ON DUPLICATE KEY UPDATE +t1.field_3 = t2.field_c; +SELECT * FROM t1; +sync_slave_with_master; +SELECT * FROM t1; +connection master; +drop table t1, t2; + +# # BUG#20339: stored procedure using LAST_INSERT_ID() does not # replicate statement-based # diff --git a/mysql-test/t/rpl_known_bugs_detection-master.opt b/mysql-test/t/rpl_known_bugs_detection-master.opt new file mode 100644 index 00000000000..d4ba386a1a0 --- /dev/null +++ b/mysql-test/t/rpl_known_bugs_detection-master.opt @@ -0,0 +1 @@ +--loose-debug=d,pretend_version_50034_in_binlog diff --git a/mysql-test/t/rpl_known_bugs_detection.test b/mysql-test/t/rpl_known_bugs_detection.test new file mode 100644 index 00000000000..4719716d4a1 --- /dev/null +++ b/mysql-test/t/rpl_known_bugs_detection.test @@ -0,0 +1,90 @@ +# Test to see if slave can detect certain known bugs present +# on the master, and appropriately decides to stop +# (assuming the bug is fixed in the slave, slave cannot of course +# imitate the bug, so it has to stop). + +source include/have_debug.inc; +source include/master-slave.inc; + +# +# This is to test that slave properly detects if +# master may suffer from: +# BUG#24432 "INSERT... ON DUPLICATE KEY UPDATE skips auto_increment values" +# (i.e. on master, INSERT ON DUPLICATE KEY UPDATE is used and manipulates +# an auto_increment column, and is binlogged statement-based). +# + +# testcase with INSERT VALUES +CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b INT, +UNIQUE(b)); +sync_slave_with_master; +connection master; +INSERT INTO t1(b) VALUES(1),(1),(2) ON DUPLICATE KEY UPDATE t1.b=10; +SELECT * FROM t1; +connection slave; +wait_for_slave_to_stop; +# show the error message +--replace_column 1 # 4 # 7 # 8 # 9 # 23 # 33 # +--query_vertical show slave status; +# show that it was not replicated +SELECT * FROM t1; + +# restart replication for the next testcase +stop slave; +reset slave; +connection master; +reset master; +drop table t1; +connection slave; +start slave; + +# testcase with INSERT SELECT +connection master; +CREATE TABLE t1 ( + id bigint(20) unsigned NOT NULL auto_increment, + field_1 int(10) unsigned NOT NULL, + field_2 varchar(255) NOT NULL, + field_3 varchar(255) NOT NULL, + PRIMARY KEY (id), + UNIQUE KEY field_1 (field_1, field_2) +); +CREATE TABLE t2 ( + field_a int(10) unsigned NOT NULL, + field_b varchar(255) NOT NULL, + field_c varchar(255) NOT NULL +); +INSERT INTO t2 (field_a, field_b, field_c) VALUES (1, 'a', '1a'); +INSERT INTO t2 (field_a, field_b, field_c) VALUES (2, 'b', '2b'); +INSERT INTO t2 (field_a, field_b, field_c) VALUES (3, 'c', '3c'); +INSERT INTO t2 (field_a, field_b, field_c) VALUES (4, 'd', '4d'); +INSERT INTO t2 (field_a, field_b, field_c) VALUES (5, 'e', '5e'); +sync_slave_with_master; +connection master; +# Updating table t1 based on values from table t2 +INSERT INTO t1 (field_1, field_2, field_3) +SELECT t2.field_a, t2.field_b, t2.field_c +FROM t2 +ON DUPLICATE KEY UPDATE +t1.field_3 = t2.field_c; +# Inserting new record into t2 +INSERT INTO t2 (field_a, field_b, field_c) VALUES (6, 'f', '6f'); +# Updating t1 again +INSERT INTO t1 (field_1, field_2, field_3) +SELECT t2.field_a, t2.field_b, t2.field_c +FROM t2 +ON DUPLICATE KEY UPDATE +t1.field_3 = t2.field_c; +SELECT * FROM t1; +connection slave; +wait_for_slave_to_stop; +# show the error message +--replace_column 1 # 4 # 7 # 8 # 9 # 23 # 33 # +--query_vertical show slave status; +# show that it was not replicated +SELECT * FROM t1; +connection master; +drop table t1, t2; +connection slave; +drop table t1, t2; + +# End of 5.0 tests diff --git a/mysql-test/t/rpl_loaddata_charset.test b/mysql-test/t/rpl_loaddata_charset.test new file mode 100644 index 00000000000..7f2389cb9f6 --- /dev/null +++ b/mysql-test/t/rpl_loaddata_charset.test @@ -0,0 +1,33 @@ +# +# Check LOAD DATA + character sets + replication +# +source include/master-slave.inc; + +# +# Bug#15126 character_set_database is not replicated +# (LOAD DATA INFILE need it) +# +connection master; +create table t1 (a varchar(10) character set utf8); +load data infile '../std_data_ln/loaddata6.dat' into table t1; +set @@character_set_database=koi8r; +load data infile '../std_data_ln/loaddata6.dat' into table t1; +set @@character_set_database=DEFAULT; +load data infile '../std_data_ln/loaddata6.dat' into table t1; +load data infile '../std_data_ln/loaddata6.dat' into table t1; +load data infile '../std_data_ln/loaddata6.dat' into table t1; +set @@character_set_database=koi8r; +load data infile '../std_data_ln/loaddata6.dat' into table t1; +set @@character_set_database=DEFAULT; +load data infile '../std_data_ln/loaddata6.dat' into table t1 character set koi8r; + +select hex(a) from t1; + +save_master_pos; +connection slave; +sync_with_master; + +select hex(a) from t1; +connection master; +drop table t1; +sync_slave_with_master; diff --git a/mysql-test/t/rpl_replicate_do.test b/mysql-test/t/rpl_replicate_do.test index 9dec8c06c79..0e95d71514b 100644 --- a/mysql-test/t/rpl_replicate_do.test +++ b/mysql-test/t/rpl_replicate_do.test @@ -59,3 +59,35 @@ drop table t1; sync_slave_with_master; # End of 4.1 tests + +# +# Bug#24478 DROP TRIGGER is not caught by replicate-*-table filters +# +--echo *** master *** +connection master; +create table t1 (a int, b int); +create trigger trg1 before insert on t1 for each row set new.b=2; +create table t2 (a int, b int); +create trigger trg2 before insert on t2 for each row set new.b=2; +show tables; +show triggers; +sync_slave_with_master; +--echo *** slave *** +connection slave; +show tables; +show triggers; +--echo *** master *** +connection master; +drop trigger trg1; +drop trigger trg2; +show triggers; +sync_slave_with_master; +--echo *** slave *** +connection slave; +show tables; +show triggers; +--echo *** master *** +connection master; +drop table t1; +drop table t2; +sync_slave_with_master; diff --git a/mysql-test/t/rpl_user_variables.test b/mysql-test/t/rpl_user_variables.test index dbe75a15038..b2f4bfbb29d 100644 --- a/mysql-test/t/rpl_user_variables.test +++ b/mysql-test/t/rpl_user_variables.test @@ -57,8 +57,305 @@ insert into t1 select * FROM (select @var1 union select @var2) AS t2; drop table t1; save_master_pos; -connection slave; -sync_with_master; +--echo End of 4.1 tests. + +# BUG#20141 +# The following tests ensure that if user-defined variables are used in SF/Triggers +# that they are replicated correctly. These tests should be run in both SBR and RBR +# modes. + +# This test uses a procedure that inserts data values based on the value of a +# user-defined variable. It also has a trigger that inserts data based on the +# same variable. Successful test runs show that the @var is replicated +# properly and that the procedure and trigger insert the correct data on the +# slave. +# +# The test of stored procedure was included for completeness. Replication of stored +# procedures was not directly affected by BUG#20141. +# +# This test was constructed for BUG#20141 + +--disable_warnings +DROP TABLE IF EXISTS t20; +DROP TABLE IF EXISTS t21; +DROP PROCEDURE IF EXISTS test.insert; +--enable_warnings + +CREATE TABLE t20 (a VARCHAR(20)); +CREATE TABLE t21 (a VARCHAR(20)); +DELIMITER |; + +# Create a procedure that uses the @var for flow control + +CREATE PROCEDURE test.insert() +BEGIN + IF (@VAR) + THEN + INSERT INTO test.t20 VALUES ('SP_TRUE'); + ELSE + INSERT INTO test.t20 VALUES ('SP_FALSE'); + END IF; +END| + +# Create a trigger that uses the @var for flow control + +CREATE TRIGGER test.insert_bi BEFORE INSERT + ON test.t20 FOR EACH ROW + BEGIN + IF (@VAR) + THEN + INSERT INTO test.t21 VALUES ('TRIG_TRUE'); + ELSE + INSERT INTO test.t21 VALUES ('TRIG_FALSE'); + END IF; + END| +DELIMITER ;| + +sync_slave_with_master; +connection master; + +# Set @var and call the procedure, repeat with different values + +SET @VAR=0; +CALL test.insert(); +SET @VAR=1; +CALL test.insert(); + +--echo On master: Check the tables for correct data + +SELECT * FROM t20; +SELECT * FROM t21; + +sync_slave_with_master; + +--echo On slave: Check the tables for correct data and it matches master + +SELECT * FROM t20; +SELECT * FROM t21; +connection master; + +# Cleanup + +DROP TABLE t20; +DROP TABLE t21; +DROP PROCEDURE test.insert; + +# This test uses a stored function that uses user-defined variables to return data +# This test was constructed for BUG#20141 + +--disable_warnings +DROP TABLE IF EXISTS t1; +DROP FUNCTION IF EXISTS test.square; +--enable_warnings + +CREATE TABLE t1 (i INT); + +# Create function that returns a value from @var. In this case, the square function + +CREATE FUNCTION test.square() RETURNS INTEGER DETERMINISTIC RETURN (@var * @var); + +sync_slave_with_master; +connection master; + +# Set the @var to different values and insert them into a table + +SET @var = 1; +INSERT INTO t1 VALUES (square()); +SET @var = 2; +INSERT INTO t1 VALUES (square()); +SET @var = 3; +INSERT INTO t1 VALUES (square()); +SET @var = 4; +INSERT INTO t1 VALUES (square()); +SET @var = 5; +INSERT INTO t1 VALUES (square()); + +--echo On master: Retrieve the values from the table + +SELECT * FROM t1; + +sync_slave_with_master; + +--echo On slave: Retrieve the values from the table and verify they are the same as on master + +SELECT * FROM t1; + +connection master; + +# Cleanup + +DROP TABLE t1; +DROP FUNCTION test.square; + +# This test uses stored functions that uses user-defined variables to return data +# based on the use of @vars inside a function body. +# This test was constructed for BUG#14914 + +--disable_warnings +DROP TABLE IF EXISTS t1; +DROP FUNCTION IF EXISTS f1; +DROP FUNCTION IF EXISTS f2; +--enable_warnings + +CREATE TABLE t1(a int); +DELIMITER |; + +# Create a function that simply returns the value of an @var. +# Create a function that uses an @var for flow control, creates and uses another +# @var and sets its value to a value based on another @var. + +CREATE FUNCTION f1() returns int deterministic +BEGIN + return @a; +END | + +CREATE FUNCTION f2() returns int deterministic +BEGIN + IF (@b > 0) then + SET @c = (@a + @b); + else + SET @c = (@a - 1); + END if; + return @c; +END | +DELIMITER ;| + +sync_slave_with_master; +connection master; + +# Set an @var to a value and insert data into a table using the first function. +# Set two more @vars to some values and insert data into a table using the second function. + +SET @a=500; +INSERT INTO t1 values(f1()); +SET @b = 125; +SET @c = 1; +INSERT INTO t1 values(f2()); + +sync_slave_with_master; + +--echo On master: Retrieve the values from the table + +SELECT * from t1; + +--echo On slave: Check the tables for correct data and it matches master + +SELECT * from t1; + +connection master; + +# Cleanup + +DROP TABLE t1; +DROP FUNCTION f1; +DROP FUNCTION f2; + +# This test uses a function that changes a user-defined variable in its body. This test +# will ensure the @vars are replicated when needed and not interrupt the normal execution +# of the function on the slave. This also applies to triggers. +# +# This test was constructed for BUG#25167 + +--disable_warnings +DROP TABLE IF EXISTS t1; +DROP TABLE IF EXISTS t2; +--enable_warnings +CREATE TABLE t1 (i int); +CREATE TABLE t2 (k int); +DELIMITER |; + +# Create a trigger that inserts data into another table, changes the @var then inserts +# another row with the modified value. + +CREATE trigger t1_bi before INSERT on t1 for each row +BEGIN + INSERT INTO t2 values (@a); + SET @a:=42; + INSERT INTO t2 values (@a); +END | +DELIMITER ;| + +sync_slave_with_master; +connection master; + +# Set the @var to a value then insert data into first table. + +SET @a:=100; +INSERT INTO t1 values (5); + +--echo On master: Check to see that data was inserted correctly in both tables + +SELECT * from t1; +SELECT * from t2; + +sync_slave_with_master; + +--echo On slave: Check the tables for correct data and it matches master + +SELECT * from t1; +SELECT * from t2; + +connection master; + +--echo End of 5.0 tests. + +# Cleanup + +DROP TABLE t1; +DROP TABLE t2; + +# This test uses a stored function that uses user-defined variables to return data +# The test ensures the value of the user-defined variable is replicated correctly +# and in the correct order of assignment. +# This test was constructed for BUG#20141 + +--disable_warnings +DROP TABLE IF EXISTS t1; +DROP FUNCTION IF EXISTS f1; +DROP FUNCTION IF EXISTS f2; +--enable_warnings + +CREATE TABLE t1 (i INT); + +# Create two functions. One simply returns the user-defined variable. The other +# returns a value based on the user-defined variable. + +CREATE FUNCTION f1() RETURNS INT RETURN @a; +DELIMITER |; +CREATE FUNCTION f2() RETURNS INT +BEGIN + INSERT INTO t1 VALUES (10 + @a); + RETURN 0; +END| +DELIMITER ;| + +sync_slave_with_master; +connection master; + +# Set the variable and execute the functions. + +SET @a:=123; +SELECT f1(), f2(); + +--echo On master: Check to see that data was inserted correctly + +INSERT INTO t1 VALUES(f1()); +SELECT * FROM t1; + +sync_slave_with_master; + +--echo On slave: Check the table for correct data and it matches master + +SELECT * FROM t1; + +connection master; + +# Cleanup + +DROP FUNCTION f1; +DROP FUNCTION f2; +DROP TABLE t1; + +sync_slave_with_master; stop slave; -# End of 4.1 tests diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test index c4737814137..ea5fadb2e1b 100644 --- a/mysql-test/t/select.test +++ b/mysql-test/t/select.test @@ -3253,4 +3253,50 @@ select case when 1 then cast(1111111111111111111 as unsigned) else 1 end c, coalesce(cast(1111111111111111111 as unsigned), 1) co; +# +# Bug #22971: indexes on text columns are ignored for ref accesses +# + +CREATE TABLE t1 (name varchar(255)); +CREATE TABLE t2 (name varchar(255), n int, KEY (name(3))); +INSERT INTO t1 VALUES ('ccc'), ('bb'), ('cc '), ('aa '), ('aa'); +INSERT INTO t2 VALUES ('bb',1), ('aa',2), ('cc ',3); +INSERT INTO t2 VALUES (concat('cc ', 0x06), 4); +INSERT INTO t2 VALUES ('cc',5), ('bb ',6), ('cc ',7); +SELECT * FROM t2; +SELECT * FROM t2 ORDER BY name; +SELECT name, LENGTH(name), n FROM t2 ORDER BY name; + +EXPLAIN SELECT name, LENGTH(name), n FROM t2 WHERE name='cc '; +SELECT name, LENGTH(name), n FROM t2 WHERE name='cc '; +EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%'; +SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%'; +EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name; +SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name; +EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name; +SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name; + +DROP TABLE t1,t2; + +CREATE TABLE t1 (name text); +CREATE TABLE t2 (name text, n int, KEY (name(3))); +INSERT INTO t1 VALUES ('ccc'), ('bb'), ('cc '), ('aa '), ('aa'); +INSERT INTO t2 VALUES ('bb',1), ('aa',2), ('cc ',3); +INSERT INTO t2 VALUES (concat('cc ', 0x06), 4); +INSERT INTO t2 VALUES ('cc',5), ('bb ',6), ('cc ',7); +SELECT * FROM t2; +SELECT * FROM t2 ORDER BY name; +SELECT name, LENGTH(name), n FROM t2 ORDER BY name; + +EXPLAIN SELECT name, LENGTH(name), n FROM t2 WHERE name='cc '; +SELECT name, LENGTH(name), n FROM t2 WHERE name='cc '; +EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%'; +SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%'; +EXPLAIN SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name; +SELECT name , LENGTH(name), n FROM t2 WHERE name LIKE 'cc%' ORDER BY name; +EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name; +SELECT * FROM t1 LEFT JOIN t2 ON t1.name=t2.name; + +DROP TABLE t1,t2; + --echo End of 5.0 tests diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index d8478bae258..11cec291d8c 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -2600,4 +2600,31 @@ SELECT * FROM t1 UNION SELECT c from t2 WHERE c=t1.c); -DROP TABLE t1,t2,t3; +DROP TABLE t1,t2,t3; +# +# Bug#23800: Outer fields in correlated subqueries is used in a temporary +# table created for sorting. +# +CREATE TABLE t1(f1 int); +CREATE TABLE t2(f2 int, f21 int, f3 timestamp); +INSERT INTO t1 VALUES (1),(1),(2),(2); +INSERT INTO t2 VALUES (1,1,"2004-02-29 11:11:11"), (2,2,"2004-02-29 11:11:11"); +SELECT ((SELECT f2 FROM t2 WHERE f21=f1 LIMIT 1) * COUNT(f1)) AS sq FROM t1 GROUP BY f1; +SELECT (SELECT SUM(1) FROM t2 ttt GROUP BY t2.f3 LIMIT 1) AS tt FROM t2; +PREPARE stmt1 FROM 'SELECT ((SELECT f2 FROM t2 WHERE f21=f1 LIMIT 1) * COUNT(f1)) AS sq FROM t1 GROUP BY f1'; +EXECUTE stmt1; +EXECUTE stmt1; +DEALLOCATE PREPARE stmt1; +SELECT f2, AVG(f21), + (SELECT t.f3 FROM t2 AS t WHERE t2.f2=t.f2 AND t.f3=MAX(t2.f3)) AS test + FROM t2 GROUP BY f2; +DROP TABLE t1,t2; +CREATE TABLE t1 (a int, b INT, c CHAR(10) NOT NULL); +INSERT INTO t1 VALUES + (1,1,'a'), (1,2,'b'), (1,3,'c'), (1,4,'d'), (1,5,'e'), + (2,1,'f'), (2,2,'g'), (2,3,'h'), (3,4,'i'), (3,3,'j'), + (3,2,'k'), (3,1,'l'), (1,9,'m'); +SELECT a, MAX(b), + (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b)) AS test + FROM t1 GROUP BY a; +DROP TABLE t1; diff --git a/mysql-test/t/update.test b/mysql-test/t/update.test index 3ce7ef72670..23ee75d61ea 100644 --- a/mysql-test/t/update.test +++ b/mysql-test/t/update.test @@ -342,3 +342,29 @@ UPDATE t1 SET user_id=null WHERE request_id=999999999999999999999999999999; show status like '%Handler_read%'; DROP TABLE t1; + +# +# Bug #24010: INSERT INTO ... SELECT fails on unique constraint with data it +# doesn't select +# +CREATE TABLE t1 ( + + a INT(11), + quux decimal( 31, 30 ), + + UNIQUE KEY bar (a), + KEY quux (quux) +); + +INSERT INTO + t1 ( a, quux ) +VALUES + ( 1, 1 ), + ( 2, 0.1 ); + +INSERT INTO t1( a ) + SELECT @newA := 1 + a FROM t1 WHERE quux <= 0.1; + +SELECT * FROM t1; + +DROP TABLE t1; diff --git a/mysql-test/t/wait_timeout.test b/mysql-test/t/wait_timeout.test index 67ed8fedfeb..9b9813f9655 100644 --- a/mysql-test/t/wait_timeout.test +++ b/mysql-test/t/wait_timeout.test @@ -46,7 +46,8 @@ while (!`select @aborted_clients`) connection default; # When the connection is closed in this way, the error code should # be consistent see bug#2845 for an explanation ---error 2006 +# depending on platform/client, either errno 2006 or 2013 can occur below +--error 2006,2013 select 2; --enable_reconnect select 3; @@ -90,7 +91,8 @@ while (!`select @aborted_clients`) connection con1; # When the connection is closed in this way, the error code should # be consistent see bug#2845 for an explanation ---error 2006 +# depending on platform/client, either errno 2006 or 2013 can occur below +--error 2006,2013 select 2; --enable_reconnect select 3; diff --git a/mysql-test/valgrind.supp b/mysql-test/valgrind.supp index f7eb9baa4c7..e71f4541cfd 100644 --- a/mysql-test/valgrind.supp +++ b/mysql-test/valgrind.supp @@ -346,3 +346,20 @@ fun:_ZN19TransporterRegistry11performSendEv fun:_ZN19TransporterRegistry14forceSendCheckEi } + +# +# BUG#25463: Probable cause - slave thread doesn't have time to terminate properly +# because rest of the server shuts down before that. The server shutdown code has +# been fixed in 5.1 and there this leak should not happen. Suppression has been +# approved by Monty. +# +# Important: do not propagate this rule to 5.1 tree! +# + +{ + Slave thread shutdown (BUG#25463) + Memcheck:Leak + fun:calloc + fun:my_thread_init + fun:handle_slave_io +} |