# ============================================================================ # # Test of mysqltest itself # # There are three rules that determines what belong to each command # 1. A normal command is delimited by the <delimiter> which by default is # set to ';' # # ex: | select * # | from t1; # | # Command: "select * from t1" # # 2. Special case is a line that starts with "--", this is a comment # ended when the new line character is reached. But the first word # in the comment may contain a valid command, which then will be # executed. This can be useful when sending commands that # contains <delimiter> # # 3. Special case is also a line that starts with '#' which is treated # as a comment and will be ended by new line character # # ============================================================================ # ---------------------------------------------------------------------------- # $mysql_errno contains the return code of the last command # send to the server. # ---------------------------------------------------------------------------- # get $mysql_errno before the first statement # $mysql_errno should be -1 eval select $mysql_errno as "before_use_test" ; # ---------------------------------------------------------------------------- # Positive case(statement) # ---------------------------------------------------------------------------- select otto from (select 1 as otto) as t1; # expectation = response --error 0 select otto from (select 1 as otto) as t1; # expectation <> response -- // --error 1054 -- // select otto from (select 1 as otto) as t1; # ---------------------------------------------------------------------------- # Negative case(statement): # The dervied table t1 does not contain a column named 'friedrich' . # --> ERROR 42S22: Unknown column 'friedrich' in 'field list and # --> 1054: Unknown column 'friedrich' in 'field list' # ---------------------------------------------------------------------------- # expectation <> response #--error 0 #select friedrich from (select 1 as otto) as t1 --error 1 --exec echo "select friedrich from (select 1 as otto) as t1;" | $MYSQL_TEST 2>&1 # expectation = response --error 1054 select friedrich from (select 1 as otto) as t1; # The following unmasked unsuccessful statement must give # 1. mysqltest gives a 'failed' # 2. does not produce a r/<test case>.reject file !!! # PLEASE uncomment it and check it's effect #select friedrich from (select 1 as otto) as t1; # ---------------------------------------------------------------------------- # Tests for the new feature - SQLSTATE error code matching # Positive case(statement) # ---------------------------------------------------------------------------- # This syntax not allowed anymore, use --error S00000, see below # expectation = response #!S00000 select otto from (select 1 as otto) as t1; --error S00000 select otto from (select 1 as otto) as t1; # expectation <> response #!S42S22 select otto from (select 1 as otto) as t1; #--error S42S22 #select otto from (select 1 as otto) as t1; --error 1 --exec echo "error S42S22; select otto from (select 1 as otto) as t1;" | $MYSQL_TEST 2>&1 # ---------------------------------------------------------------------------- # Negative case(statement) # ---------------------------------------------------------------------------- # This syntax not allowed anymore, use --error S42S22, see below # expectation = response #!S42S22 select friedrich from (select 1 as otto) as t1; --error S42S22 select friedrich from (select 1 as otto) as t1; # expectation !=response #!S00000 select friedrich from (select 1 as otto) as t1; #--error S00000 #select friedrich from (select 1 as otto) as t1; --error 1 --exec echo "error S00000; select friedrich from (select 1 as otto) as t1;" | $MYSQL_TEST 2>&1 # ---------------------------------------------------------------------------- # test cases for $mysql_errno # # $mysql_errno is a builtin variable of mysqltest and contains the return code # of the last command send to the server. # # The following test cases often initialize $mysql_errno to 1064 by # a command with wrong syntax. # Example: --error 1064 To prevent the abort after the error. # garbage ; # ---------------------------------------------------------------------------- # ---------------------------------------------------------------------------- # check mysql_errno = 0 after successful statement # ---------------------------------------------------------------------------- select otto from (select 1 as otto) as t1; eval select $mysql_errno as "after_successful_stmt_errno" ; #---------------------------------------------------------------------------- # check mysql_errno = 1064 after statement with wrong syntax # ---------------------------------------------------------------------------- --error 1064 garbage ; eval select $mysql_errno as "after_wrong_syntax_errno" ; # ---------------------------------------------------------------------------- # check if let $my_var= 'abc' ; affects $mysql_errno # ---------------------------------------------------------------------------- --error 1064 garbage ; let $my_var= 'abc' ; eval select $mysql_errno as "after_let_var_equal_value" ; # ---------------------------------------------------------------------------- # check if set @my_var= 'abc' ; affects $mysql_errno # ---------------------------------------------------------------------------- --error 1064 garbage ; set @my_var= 'abc' ; eval select $mysql_errno as "after_set_var_equal_value" ; # ---------------------------------------------------------------------------- # check if the setting of --disable-warnings itself affects $mysql_errno # (May be --<whatever> modifies $mysql_errno.) # ---------------------------------------------------------------------------- --error 1064 garbage ; --disable_warnings eval select $mysql_errno as "after_disable_warnings_command" ; # ---------------------------------------------------------------------------- # check if --disable-warnings + command with warning affects the errno # stored within $mysql_errno # (May be disabled warnings affect $mysql_errno.) # ---------------------------------------------------------------------------- drop table if exists t1 ; --error 1064 garbage ; drop table if exists t1 ; eval select $mysql_errno as "after_disable_warnings" ; --enable_warnings # ---------------------------------------------------------------------------- # check if masked errors affect $mysql_errno # ---------------------------------------------------------------------------- --error 1064 garbage ; --error 1146 select 3 from t1 ; eval select $mysql_errno as "after_minus_masked" ; --error 1064 garbage ; --error 1146 select 3 from t1 ; eval select $mysql_errno as "after_!_masked" ; # ---------------------------------------------------------------------------- # Will manipulations of $mysql_errno be possible and visible ? # ---------------------------------------------------------------------------- --error 1064 garbage ; let $mysql_errno= -1; eval select $mysql_errno as "after_let_errno_equal_value" ; # ---------------------------------------------------------------------------- # How affect actions on prepared statements $mysql_errno ? # ---------------------------------------------------------------------------- # failing prepare --error 1064 garbage ; --error 1146 prepare stmt from "select 3 from t1" ; eval select $mysql_errno as "after_failing_prepare" ; create table t1 ( f1 char(10)); # successful prepare --error 1064 garbage ; prepare stmt from "select 3 from t1" ; eval select $mysql_errno as "after_successful_prepare" ; # successful execute --error 1064 garbage ; execute stmt; eval select $mysql_errno as "after_successful_execute" ; # failing execute (table dropped) drop table t1; --error 1064 garbage ; --error 1146 execute stmt; eval select $mysql_errno as "after_failing_execute" ; # failing execute (unknown statement) --error 1064 garbage ; --error 1243 execute __stmt_; eval select $mysql_errno as "after_failing_execute" ; # successful deallocate --error 1064 garbage ; deallocate prepare stmt; eval select $mysql_errno as "after_successful_deallocate" ; # failing deallocate ( statement handle does not exist ) --error 1064 garbage ; --error 1243 deallocate prepare __stmt_; eval select $mysql_errno as "after_failing_deallocate" ; # ---------------------------------------------------------------------------- # test cases for "--disable_abort_on_error" # # "--disable_abort_on_error" switches the abort of mysqltest # after "unmasked" failing statements off. # # The default is "--enable_abort_on_error". # # "Maskings" are # --error <error number> and --error <error number> # in the line before the failing statement. # # There are some additional test case for $mysql_errno # because "--disable_abort_on_error" enables a new situation. # Example: "unmasked" statement fails + analysis of $mysql_errno # ---------------------------------------------------------------------------- # ---------------------------------------------------------------------------- # Switch the abort on error off and check the effect on $mysql_errno # ---------------------------------------------------------------------------- --error 1064 garbage ; --disable_abort_on_error eval select $mysql_errno as "after_--disable_abort_on_error" ; # ---------------------------------------------------------------------------- # "unmasked" failing statement should not cause an abort # ---------------------------------------------------------------------------- select 3 from t1 ; # ---------------------------------------------------------------------------- # masked failing statements # ---------------------------------------------------------------------------- # expected error = response --error 1146 select 3 from t1 ; --error 1146 select 3 from t1 ; eval select $mysql_errno as "after_!errno_masked_error" ; # expected error <> response # --error 1000 # select 3 from t1 ; # --error 1000 # select 3 from t1 ; --error 1 --exec echo "disable_abort_on_error; error 1000; select 3 from t1; error 1000; select 3 from t1;" | $MYSQL_TEST 2>&1 # ---------------------------------------------------------------------------- # Switch the abort on error on and check the effect on $mysql_errno # ---------------------------------------------------------------------------- --error 1064 garbage ; --enable_abort_on_error eval select $mysql_errno as "after_--enable_abort_on_error" ; # ---------------------------------------------------------------------------- # masked failing statements # ---------------------------------------------------------------------------- # expected error = response --error 1146 select 3 from t1 ; # ---------------------------------------------------------------------------- # check that the old default behaviour is not changed # Please remove the '#' to get the abort on error # ---------------------------------------------------------------------------- #--error 1064 #select 3 from t1 ; # #select 3 from t1 ; # End of 4.1 tests --error 1 --exec echo "disable_abort_on_error; enable_abort_on_error; error 1064; select 3 from t1; select 3 from t1;" | $MYSQL_TEST 2>&1 # ---------------------------------------------------------------------------- # Test comments # ---------------------------------------------------------------------------- # This is a comment # This is a ; comment # This is a -- comment -- This is also a comment -- # This is also a comment -- This is also a ; comment # ---------------------------------------------------------------------------- # Test comments with embedded command # ---------------------------------------------------------------------------- --echo hello -- echo hello -- echo ;;;;;;;; --echo # MySQL: -- The # ---------------------------------------------------------------------------- # Test detect end of line "junk" # Most likely causes by a missing delimiter # ---------------------------------------------------------------------------- # Too many parameters to function --error 1 --exec echo "sleep 5 6;" | $MYSQL_TEST 2>&1 # Too many parameters to function --error 1 --exec echo "--sleep 5 6" | $MYSQL_TEST 2>&1 # # Missing delimiter # The comment will be "sucked into" the sleep command since # delimiter is missing until after "show status" --system echo "sleep 4" > var/log/mysqltest.sql --system echo "# A comment" >> var/log/mysqltest.sql --system echo "show status;" >> var/log/mysqltest.sql --error 1 --exec $MYSQL_TEST < var/log/mysqltest.sql 2>&1 # # Extra delimiter # --error 1 --exec echo "--sleep 4;" | $MYSQL_TEST 2>&1 # Allow trailing # comment --sleep 1 # Wait for insert delayed to be executed. --sleep 1 # Wait for insert delayed to be executed. # ---------------------------------------------------------------------------- # Test echo command # ---------------------------------------------------------------------------- echo MySQL; echo "MySQL"; echo MySQL: The world''s most popular open source database; echo "MySQL: The world's most popular open source database"; echo MySQL: The world''s most popular open source database; echo # MySQL: The world''s # most popular open # source database; echo - MySQL: The world''s - most popular open - source database; echo - MySQL: The world''s -- most popular open -- source database; echo # MySQL: The --world''s # most popular -- open - source database; echo "MySQL: The world's most popular; open source database"; echo "MySQL: The world's most popular ; open source database"; echo "MySQL: The world's most popular ;open source database"; echo echo message echo message; echo ; # Illegal use of echo --error 1 --exec echo "echo $;" | $MYSQL_TEST 2>&1 # ---------------------------------------------------------------------------- # Test exec command # ---------------------------------------------------------------------------- # Illegal use of exec --error 1 --exec echo "--exec false" | $MYSQL_TEST 2>&1 --error 1 --exec echo "--exec " | $MYSQL_TEST 2>&1 # ---------------------------------------------------------------------------- # Test let command # ---------------------------------------------------------------------------- let $message=MySQL; echo $message; let $message="MySQL"; echo $message; let $message= MySQL: The world''s most popular open source database; echo $message; let $message= # MySQL: The # world''s most # popular open # source database; echo $message; let $message= -- MySQL: The -- world''s most -- popular open -- source database; echo $message; let $message= # MySQL: The - world''s most -- popular open # source database; echo $message; echo '$message'; echo "$message"; let $1=hej; echo $1; let $1 =hej ; echo $1; let $1 = hej; echo $1; let $1=1; let $2=$1; echo $2; let $5=$6; echo $5; echo $6; let $where=a long variable content; echo $where; let $where2= $where; echo $where2; let $where3=a long $where variable content; echo $where3; let $novar1= $novar2; echo $novar1; # Test illegal uses of let --error 1 --exec echo "let ;" | $MYSQL_TEST 2>&1 --error 1 --exec echo "let $=hi;" | $MYSQL_TEST 2>&1 --error 1 --exec echo "let hi=hi;" | $MYSQL_TEST 2>&1 --error 1 --exec echo "let $1 hi;" | $MYSQL_TEST 2>&1 --error 1 --exec echo "let $m hi;" | $MYSQL_TEST 2>&1 --error 1 --exec echo "let $hi;" | $MYSQL_TEST 2>&1 --error 1 --exec echo "let $ hi;" | $MYSQL_TEST 2>&1 --error 1 --exec echo "let =hi;" | $MYSQL_TEST 2>&1 --error 1 --exec echo "let hi;" | $MYSQL_TEST 2>&1 # ---------------------------------------------------------------------------- # Test source command # ---------------------------------------------------------------------------- # Test illegal uses of source --error 1 --exec echo "source ;" | $MYSQL_TEST 2>&1 --error 1 --exec echo "source non_existingFile;" | $MYSQL_TEST 2>&1 # Too many source --exec echo "source var/tmp/recursive.sql;" > var/tmp/recursive.sql --error 1 --exec echo "source var/tmp/recursive.sql;" | $MYSQL_TEST 2>&1 # Source a file with error --exec echo "garbage ;" > var/tmp/error.sql --error 1 --exec echo "source var/tmp/error.sql;" | $MYSQL_TEST 2>&1 # Test execution of source in a while loop --exec echo "echo here is the sourced script;" > var/tmp/sourced.sql --disable_query_log let $outer= 2; # Number of outer loops while ($outer) { eval SELECT '$outer = outer loop variable after while' AS ""; --source var/tmp/sourced.sql eval SELECT '$outer = outer loop variable before dec' AS ""; dec $outer; eval SELECT '$outer = outer loop variable after dec' AS ""; } let $outer= 2; # Number of outer loops while ($outer) { eval SELECT '$outer = outer loop variable after while' AS ""; echo here is the sourced script; eval SELECT '$outer = outer loop variable before dec' AS ""; dec $outer; eval SELECT '$outer = outer loop variable after dec' AS ""; } # Test execution of source in a while loop --exec echo "--source var/tmp/sourced.sql" > var/tmp/sourced1.sql --disable_abort_on_error # Sourcing of a file within while loop, sourced file will # source other file let $num= 9; while ($num) { SELECT 'In loop' AS ""; --source var/tmp/sourced1.sql dec $num; } --enable_abort_on_error --enable_query_log # ---------------------------------------------------------------------------- # Test sleep command # ---------------------------------------------------------------------------- sleep 0.5; sleep 1; real_sleep 1; # Missing parameter --error 1 --exec echo "sleep ;" | $MYSQL_TEST 2>&1 # Illegal parameter --error 1 --exec echo "sleep abc;" | $MYSQL_TEST 2>&1 # ---------------------------------------------------------------------------- # Test inc # ---------------------------------------------------------------------------- inc $i; echo $i; inc $i; echo $i; let $i=100; inc $i; echo $i; let $i=hej; echo $i; inc $i; echo $i; --error 1 --exec echo "inc;" | $MYSQL_TEST 2>&1 --error 1 --exec echo "inc i;" | $MYSQL_TEST 2>&1 --error 1 --exec echo "let \$i=100; inc \$i 1000; echo \$i;" | $MYSQL_TEST 2>&1 inc $i; inc $i; inc $i; --echo $i echo $i; # ---------------------------------------------------------------------------- # Test dec # ---------------------------------------------------------------------------- dec $d; echo $d; dec $d; echo $d; let $d=100; dec $d; echo $d; let $d=hej; echo $d; dec $d; echo $d; --error 1 --exec echo "dec;" | $MYSQL_TEST 2>&1 --error 1 --exec echo "dec i;" | $MYSQL_TEST 2>&1 --error 1 --exec echo "let \$i=100; dec \$i 1000; echo \$i;" | $MYSQL_TEST 2>&1 # ---------------------------------------------------------------------------- # Test system # ---------------------------------------------------------------------------- system ls > /dev/null; system echo "hej" > /dev/null; --system ls > /dev/null --system echo "hej" > /dev/null; --error 1 --exec echo "system;" | $MYSQL_TEST 2>&1 --error 1 --exec echo "system $NONEXISTSINFVAREABLI;" | $MYSQL_TEST 2>&1 --error 1 --exec echo "system false;" | $MYSQL_TEST 2>&1 --disable_abort_on_error system NonExistsinfComamdn; --enable_abort_on_error # ---------------------------------------------------------------------------- # Test delimiter # ---------------------------------------------------------------------------- delimiter stop; echo teststop delimiter ;stop echo test2; --delimiter stop echo test3stop --delimiter ; echo test4; # ---------------------------------------------------------------------------- # Test while, { and } # ---------------------------------------------------------------------------- let $i=1; while ($i) { echo $i; dec $i; } # One liner #let $i=1;while ($i){echo $i;dec $i;} # Exceed max nesting level --error 1 --exec echo "source include/mysqltest_while.inc;" | $MYSQL_TEST 2>&1 --error 1 --exec echo "while \$i;" | $MYSQL_TEST 2>&1 --error 1 --exec echo "while (\$i;" | $MYSQL_TEST 2>&1 --error 1 --exec echo "let \$i=1; while (\$i) dec \$i;" | $MYSQL_TEST 2>&1 --error 1 --exec echo "};" | $MYSQL_TEST 2>&1 --error 1 --exec echo "end;" | $MYSQL_TEST 2>&1 --error 1 --exec echo "{;" | $MYSQL_TEST 2>&1 --system echo "while (0)" > var/log/mysqltest.sql --system echo "echo hej;" >> var/log/mysqltest.sql --error 1 --exec $MYSQL_TEST < var/log/mysqltest.sql 2>&1 --system echo "while (0)" > var/log/mysqltest.sql --system echo "{echo hej;" >> var/log/mysqltest.sql --error 1 --exec $MYSQL_TEST < var/log/mysqltest.sql 2>&1 --system echo "while (0){" > var/log/mysqltest.sql --system echo "echo hej;" >> var/log/mysqltest.sql --error 1 --exec $MYSQL_TEST < var/log/mysqltest.sql 2>&1 # ---------------------------------------------------------------------------- # Test error messages returned from comments starting with a command # ---------------------------------------------------------------------------- --error 1 --exec echo "--if the other server is down" | $MYSQL_TEST 2>&1 --error 1 --exec echo "-- end when ..." | $MYSQL_TEST 2>&1 # ---------------------------------------------------------------------------- # Test replace # ---------------------------------------------------------------------------- --replace_result a b select "a" as col1, "c" as col2; --replace_result a b c d select "a" as col1, "c" as col2; --error 1 --exec echo "--replace_result a" | $MYSQL_TEST 2>&1 --error 1 --exec echo "--replace_result a;" | $MYSQL_TEST 2>&1 --error 1 --exec echo "replace_result a;" | $MYSQL_TEST 2>&1 --error 1 --exec echo "replace_result a ;" | $MYSQL_TEST 2>&1 --exec echo "replace_result a b;" | $MYSQL_TEST 2>&1 --error 1 --exec echo "--replace_result a b c" | $MYSQL_TEST 2>&1 --error 1 --exec echo "replace_result a b c ;" | $MYSQL_TEST 2>&1 --replace_column 1 b select "a" as col1, "c" as col2; --replace_column 1 b 2 d select "a" as col1, "c" as col2; --error 1 --exec echo "--replace_column a" | $MYSQL_TEST 2>&1 --error 1 --exec echo "--replace_column 1" | $MYSQL_TEST 2>&1 --error 1 --exec echo "--replace_column a b" | $MYSQL_TEST 2>&1 --error 1 --exec echo "--replace_column a 1" | $MYSQL_TEST 2>&1 --error 1 --exec echo "--replace_column 1 b c " | $MYSQL_TEST 2>&1 # ---------------------------------------------------------------------------- # Test sync_with_master # ---------------------------------------------------------------------------- --error 1 --exec echo "save_master_pos; sync_with_master 10!;" | $MYSQL_TEST 2>&1 --error 1 --exec echo "save_master_pos; sync_with_master 10 !;" | $MYSQL_TEST 2>&1 --error 1 --exec echo "save_master_pos; sync_with_master a;" | $MYSQL_TEST 2>&1 # ---------------------------------------------------------------------------- # Test mysqltest arguments # ---------------------------------------------------------------------------- # -x <file_name>, use the file specified after -x as the test file #--exec $MYSQL_TEST < $MYSQL_TEST_DIR/include/mysqltest-x.inc 2>&1 #--exec $MYSQL_TEST -x $MYSQL_TEST_DIR/include/mysqltest-x.inc 2>&1 #--exec $MYSQL_TEST --result_file=$MYSQL_TEST_DIR/include/mysqltest-x.inc 2>&1 #--error 1 #--exec $MYSQL_TEST -x non_existing_file.inc 2>&1 # ---------------------------------------------------------------------------- # TODO Test queries, especially their errormessages... so it's easy to debug # new scripts and diagnose errors # ---------------------------------------------------------------------------- # ---------------------------------------------------------------------------- # Test bug#12386 # ---------------------------------------------------------------------------- let $num= 2; while ($num) { --error 1064 failing_statement; dec $num; } SELECT 1 as a;