Documentation Home
The MySQL Test Framework, Version 2.0
Download this Manual
PDF (US Ltr) - 0.5Mb
PDF (A4) - 0.5Mb
EPUB - 129.0Kb


6.2 mysqltest Commands

mysqltest supports the commands described in this section. Command names are not case sensitive.

Some examples of command use are given, but you can find many more by searching the test case files in the mysql-test/t directory.

  • append_file file_name [terminator]

    append_file is like write_file except that the lines up to the terminator are added to the end of the file. The file is created if it does not exist. The file name argument is subject to variable substitution.

    write_file $MYSQL_TMP_DIR/data01;
    line one for the file
    line two for the file
    EOF
    append_file $MYSQL_TMP_DIR/data01;
    line three for the file
    EOF
    
    write_file $MYSQL_TMP_DIR/data02 END_OF_FILE;
    line one for the file
    line two for the file
    END_OF_FILE
    append_file $MYSQL_TMP_DIR/data02 END_OF_FILE;
    line three for the file
    END_OF_FILE
    
  • cat_file file_name

    cat_file writes the contents of the file to the output. The file name argument is subject to variable substitution.

    cat_file $MYSQL_TMP_DIR/data01;
    
  • change_user [user_name], [password], [db_name]

    Changes the current user and causes the database specified by db_name to become the default database for the current connection.

    change_user root;
    --change_user root,,test
    
  • character_set charset_name

    Set the default character set to charset_name. Initially, the character set is latin1.

    character_set utf8;
    --character_set sjis
    
  • chmod octal_mode file_name

    Change the mode of the given file. The file mode must be given as a four-digit octal number. The file name argument is subject to variable substitution, but must evaluate to a literal file name, not a file name pattern.

    chmod 0644 $MYSQL_TMP_DIR/data_xxx01;
    
  • connect (name, host_name, user_name, password, db_name [,port_num [,socket [,options [,default_auth]]]])

    Open a connection to the server and make the connection the current connection.

    The arguments to connect are:

    • name is the name for the connection (for use with the connection, disconnect, and dirty_close commands). This name must not already be in use by an open connection.

    • host_name indicates the host where the server is running.

    • user_name and password are the user name and password of the MySQL account to use.

    • db_name is the default database to use. As a special case, *NO-ONE* means that no default database should be selected. You can also leave db_name blank to select no database.

    • port_num, if given, is the TCP/IP port number to use for the connection. This parameter can be given by using a variable.

    • socket, if given, is the socket file to use for connections to localhost. This parameter can be given by using a variable.

    • options can be one or more of the words SSL and COMPRESS, separated by spaces. These specify the use of SSL and the compressed client/server protocol, respectively. As of MySQL 5.5.27, it is also possible to use CLEARTEXT to enable use of the cleartext authentication plugin.

    • default_auth is the name of an authentication plugin. It is passed to the mysql_options() C API function using the MYSQL_DEFAULT_AUTH option. If mysqltest does not find the plugin, use the --plugin-dir option to specify the directory where the plugin is located.

      This argument can be used as of MySQL 5.5.7.

    To omit an argument, just leave it blank. For an omitted argument, mysqltest uses an empty string for the first five arguments and the options argument. For omitted port or socket options, mysqltest uses the default port or socket.

    connect (conn1,localhost,root,,);
    connect (conn2,localhost,root,mypass,test);
    connect (conn1,127.0.0.1,root,,test,$MASTER_MYPORT);
    

    The last example assumes that the $MASTER_MYPORT variable has already been set (perhaps as an environment variable).

    If a connection attempt fails initially, mysqltest retries five times if the abort-on-error setting is enabled.

  • connection connection_name

    Select connection_name as the current connection. To select the connection that mysqltest opens when it starts, use the name default.

    connection master;
    connection conn2;
    connection default;
    

    A variable can be used to specify the connection_name value.

  • let $var= convert_error(error)

    This is not a command as such but rather a function that can be used in let statements. If the argument is a number, it returns the name of the corresponding error, or <Unknown> if no such error exists. If the argument is an error name, it returns the corresponding number, or fails if the error name is unknown. If the argument is 0 or an empty string, it returns 0. The function can also take a variable as argument.

    let $errvar1=convert_error(ER_UNKNOWN_ERROR);
    let $errvar2=convert_error(1450);
    let $errvar3=convert_error($errvar1);
    

    The convert_error function was added in MySQL 5.6.

  • copy_file from_file to_file

    Copy the file from_file to the file to_file. The command fails if to_file already exists. The file name arguments are subject to variable substitution.

  • dec $var_name

    Decrement a numeric variable. If the variable does not have a numeric value, the result is undefined.

    dec $count;
    dec $2;
    
  • delimiter str

    Set the command delimiter to str, which may consist of 1 to 15 characters. The default delimiter is the semicolon character (;).

    delimiter /;
    --delimiter stop
    

    This is useful or needed when you want to include long SQL statements like CREATE PROCEDURE which include semicolon delimited statements but need to be interpreted as a single statement by mysqltest. If you have set the delimiter to / as in the previous example, you can set it back to the default like this:

    delimiter ;|
    
  • die [message]

    Aborts the test with an error code after printing the given message as the reason. Suppose that a test file contains the following line:

    die Cannot continue;
    

    When mysqltest encounters that line, it produces the following result and exits:

    mysqltest: At line 1: Cannot continue
    not ok
    
  • diff_files file_name1 file_name2

    Compare the two files. The command succeeds if the files are the same, and fails if they are different or either file does not exist. The file name arguments are subject to variable substitution.

  • dirty_close connection_name

    Close the named connection. This is like disconnect except that it calls vio_delete() before it closes the connection. If the connection is the current connection, you should use the connection command to switch to a different connection before executing further SQL statements.

    A variable can be used to specify the connection_name value.

  • disable_abort_on_error, enable_abort_on_error

    Disable or enable abort-on-error behavior. This setting is enabled by default. With this setting enabled, mysqltest aborts the test when a statement sent to the server results in an unexpected error, and does not generate the .reject file. For discussion of reasons why it can be useful to disable this behavior, see Section 6.5, “Error Handling”.

    --disable_abort_on_error
    --enable_abort_on_error
    
  • disable_connect_log, enable_connect_log

    Disable or enable logging of creation or switch of connections. Connection logging is disabled by default. With this setting enabled, mysqltest enters lines in the test results to show when connections are created, switched or disconnected.

    If query logging is turned off using disable_query_log, connection logging is also turned off, until query log is re-enabled.

    --disable_connect_log
    --enable_connect_log
    
  • disable_info, enable_info

    Disable or enable additional information about SQL statement results. Information display is disabled by default. With this setting enabled, mysqltest displays the affected-rows count and the output from the mysql_info() C API function. The affected-rows value is rows selected for statements such as SELECT and rows modified for statements that change data.

    --disable_info
    --enable_info
    
  • disable_metadata, enable_metadata

    Disable or enable query metadata display. Metadata display is disabled by default. With this setting enabled, mysqltest adds query metadata to the result. This information consists of the values corresponding to the members of the MYSQL_FIELD C API data structure, for each column of the result.

    --disable_metadata
    --enable_metadata
    
  • disable_parsing, enable_parsing

    Disable or enable query parsing. This setting is enabled by default. When disabled, mysqltest ignores everything until enable_parsing. These commands are useful for "commenting out" a section from a test case without having to add a comment marker to every single line.

    --disable_parsing
    --enable_parsing
    
  • disable_ps_protocol, enable_ps_protocol

    Disable or enable prepared-statement protocol. This setting is disabled by default unless the --ps-protocol option is given.

    --disable_ps_protocol
    --enable_ps_protocol
    
  • disable_query_log, enable_query_log

    Disable or enable query logging. This setting is enabled by default. With this setting enabled, mysqltest echoes input SQL statements to the test result.

    One reason to disable query logging is to reduce the amount of test output produced, which also makes comparison of actual and expected results more efficient.

    --disable_query_log
    --enable_query_log
    
  • disable_reconnect, enable_reconnect

    Disable or enable automatic reconnect for dropped connections. (The default depends on the client library version.) This command only applies to the current connection.

    --disable_reconnect
    --enable_reconnect
    
  • disable_result_log, enable_result_log

    Disable or enable the result log. This setting is enabled by default. With this setting enabled, mysqltest displays query results (and results from commands such as echo and exec).

    --disable_result_log
    --enable_result_log
    
  • disable_rpl_parse, enable_rpl_parse

    Disable or enable parsing of statements to determine whether they go to the master or slave. The default is whatever the default is for the C API library.

    --disable_rpl_parse
    --enable_rpl_parse
    
  • disable_session_track_info, enable_session_track_info

    Disable or enable display of session tracking information. These commands were added in MySQL 5.7. Session-tracking display disabled by default.

    --disable_session_track_info
    --enable_session_track_info
    
  • disable_warnings, enable_warnings

    Disable or enable warnings. This setting is enabled by default. With this setting enabled, mysqltest uses SHOW WARNINGS to display any warnings produced by SQL statements.

    --disable_warnings
    --enable_warnings
    
  • disconnect connection_name

    Close the named connection. If the connection is the current connection, you should use the connection command to switch to a different connection before executing further SQL statements.

    disconnect conn2;
    disconnect slave;
    
  • echo text

    Echo the text to the test result. References to variables within the text are replaced with the corresponding values. The text does not need to be enclosed in quotation marks; if it is, the quotation marks will be included in the output.

    --echo Another sql_mode test
    echo should return only 1 row;
    
  • end

    End an if or while block. If there is no such block open, mysqltest exits with an error. See Section 6.4, “mysqltest Flow Control Constructs”, for information on flow-control constructs.

    mysqltest considers } and end the same: Both end the current block.

  • end_timer

    Stop the timer. By default, the timer does not stop until just before mysqltest exits.

  • error error_code [, error_code] ...

    Specify one or more comma-separated error values that the next command is expected to return. Each error_code value is a MySQL-specific error number or an SQLSTATE value. (These are the kinds of values returned by the mysql_errno() and mysql_sqlstate() C API functions, respectively.)

    If you specify an SQLSTATE value, it should begin with an S to enable mysqltest to distinguish it from a MySQL error number. For example, the error number 1050 and the SQLSTATE value 42S01 are equivalent, so the following commands specify the same expected error:

    --error 1050
    --error S42S01
    

    SQLSTATE values should be five characters long and may contain only digits and uppercase letters.

    It is also possible to use the symbolic error name from mysqld_error.h:

    --error ER_TABLE_EXISTS_ERROR
    

    Finally, you can assign either a numerical code or a symbolic error name to a variable and refer to that in the error command. This feature was added in MySQL 5.5.18. Numbers, symbolic names and variables may be freely mixed.

    If a statement fails with an error that has not been specified as expected by means of a error command, mysqltest aborts and reports the error message returned by the MySQL server.

    If a statement fails with an error that has been specified as expected by means of a error command, mysqltest does not abort. Instead, it continues and writes a message to the result output.

    • If an error command is given with a single error value and the statement fails with that error, mysqltest reports the error message returned by the MySQL server.

      Input:

      --error S42S02
      DROP TABLE t;
      

      mysqltest reports:

      ERROR 42S02: Unknown table 't'
      
    • If an error command is given with multiple error values and the statement fails with any of those errors, mysqltest reports a generic message. (This is true even if the error values are all the same, a fact that can be used if you want a message that does not contain varying information such as table names.)

      Input:

      --error S41S01,S42S02
      DROP TABLE t;
      

      mysqltest reports:

      Got one of the listed errors
      

    An error value of 0 or S00000 means no error, so using either for an error command is the same as saying explicitly, no error is expected, the statement must succeed..

    To indicate that you expect success or a given error or errors, specify 0 or S00000 first in the error list. If you put the no-error value later in the list, the test will abort if the statement is successful. That is, the following two commands have different effects: The second form literally means the next command may fail with error code 0, (rather than succeed) which in practice never happens:

    --error 0,1051
    --error 1051,0
    

    You can use error to specify shell status values for testing the value of shell commands executed using the exec command. This does not apply to system, for which the command status is ignored.

    If you use error in combination with send and reap, the error should be used just before the reap, as this is the command that actually gives the result and the potential error.

    From MySQL 5.5.18, variables may also be used as arguments to the error command; these may contain a number (including 0), an SQLSTATE or a symbolic error name. Variables and constant values may be freely combined.

  • eval statement

    Evaluate the statement by replacing references to variables within the text with the corresponding values. Then send the resulting statement to the server to be executed. Use \$ to specify a literal $ character.

    The advantage of using eval statement versus just statement is that eval provides variable expansion.

    eval USE $DB;
    eval CHANGE MASTER TO MASTER_PORT=$SLAVE_MYPORT;
    eval PREPARE STMT1 FROM "$my_stmt";
    
  • exec command [arg] ...

    Execute the shell command using the popen() library call. References to variables within the command are replaced with the corresponding values. Use \$ to specify a literal $ character.

    On Cygwin, the command is executed from cmd.exe, so commands such as rm cannot be executed with exec. Use system instead.

    --exec $MYSQL_DUMP --xml --skip-create test
    --exec rm $MYSQLTEST_VARDIR/tmp/t1
    exec $MYSQL_SHOW test -v -v;
    
    Note

    exec or system are sometimes used to perform file system operations, but the command for doing so tend to be operating system specific, which reduces test portability. mysqltest now has several commands to perform these operations portably, so they should be used instead: remove_file, chmod, mkdir, and so forth.

  • execw command [arg] ...

    This is a variant of the exec command which is needed on Windows if the command line contains non-ASCII characters. Otherwise it works exactly the same. On platforms other than Windows there is no difference, but on Windows it uses a different version of the popen() library call. So if your command line contains non-ASCII characters, it is recommended to use execw instead of exec.

    The execw command is available from MySQL 5.6.

  • exit

    Terminate the test case. This is considered a normal termination. That is, using exit does not result in evaluation of the test case as having failed. It is not necessary to use exit at the end of a test case, as the test case will terminate normally when reaching the end without failure.

  • file_exists file_name

    file_exists succeeds if the named file exists and fails otherwise. The file name argument is subject to variable substitution.

    file_exists /etc/passwd;
    
  • horizontal_results

    Set the default query result display format to horizontal. Initially, the default is to display results horizontally.

    --horizontal_results
    
  • if (expr)

    Begin an if block, which continues until an end or } line. mysqltest executes the block if the expression is non-zero. There is no provision for else with if. See Section 6.4, “mysqltest Flow Control Constructs”, for further information about if statements.

    let $counter= 0;
    if ($counter)
    {
      echo Counter is not 0;
    }
    if (!$counter)
    {
      echo Counter is 0;
    }
    
  • inc $var_name

    Increment a numeric variable. If the variable does not have a numeric value, the result is undefined.

    inc $i;
    inc $3;
    
  • let $var_name = value

    let $var_name = query_get_value(query, col_name, row_num)

    Assign a value to a variable. The variable name cannot contain whitespace or the = character. Except for the one-digit $0 to $9, it cannot begin with a number. mysqltest aborts with an error if the value is erroneous.

    References to variables within value are replaced with their corresponding values.

    If the let command is specified as a normal command (that is, not beginning with --), value includes everything up to the command delimiter, and thus can span multiple lines.

    --let $1= 0
    let $count= 10;
    

    When assigning a literal string to a variable, no quoting is required even if the string contains spaces. If the string does include quotation marks, they will be trated like any other characters and be included in the string value. This is important to be aware of when using the variable in an SQL statement.

    The result from executing a query can be assigned to a variable by enclosing the query within backtick (`) characters:

    let $q= `SELECT VERSION()`;
    

    The let command can set environment variables, not just mysqltest test language variables. To assign a value to an environment variable rather than a test language variable, just omit the dollar sign:

    let $mysqltest_variable= foo;
    let ENV_VARIABLE= bar;
    

    This is useful in interaction with external tools. In particular, when using the perl command, the Perl code cannot access test language variables, but it can access environment variables. For example, the following statement can access the ENV_VARIABLE value:

    print $ENV{'ENV_VARIABLE'};
    

    The let syntax also allows the retrieval of a value from a query result set produced by a statement such as SELECT or SHOW. See the description of query_get_value() for more information.

  • mkdir dir_name

    Create a directory named dir_name. Returns 0 for success and 1 for failure.

    --mkdir testdir
    
  • list_files dir_name [pattern]

    list_files lists the files in the named directory. If a pattern is given, lists only file(s) matching the pattern, which may contain wild cards.

    --list_files $MYSQLD_DATADIR/test t1*
    
  • list_files_append_file file_name dir_name [pattern]

    list_files_append_file works like list_files, but rather than outputting the file list, it is appended to the file named in the first argument. If the file does not exist, it is created.

    --list_files_append_file $MYSQL_TMP_DIR/filelist $MYSQL_TMP_DIR/testdir *.txt;
    
  • list_files_write_file file_name dir_name [pattern]

    list_files_write_file works like list_files_append_file, but creates a new file to write the file list to. If the file already exists, it will be replaced.

    --list_files_write_file $MYSQL_TMP_DIR/filelist $MYSQL_TMP_DIR/testdir *.txt;
    
  • lowercase_result

    Output from the following SQL statement will be converted to lowercase. This is sometimes needed to ensure consistent result across different platforms. If this is combined with one of the replace commands or with sorted_result, both will take effect on the output, with conversion to lowercase being applied first.

    --lowercase_result
    
  • move_file from_name to_name

    move_file renames from_name to to_name. The file name arguments are subject to variable substitution, but must evaluate to a literal file name, not a file name pattern.

    move_file $MYSQL_TMP_DIR/data01 $MYSQL_TMP_DIR/test.out;
    
  • output file_name

    Direct output from the next SQL statement to the named file rather than to the test output. If the file already exists, it will be overwritten. Only the next SQL statement will have its output redirected. This command is available from MySQL 5.7.

    output $MYSQL_TMP_DIR/out-file
    
  • perl [terminator]

    Use Perl to execute the following lines of the test file. The lines end when a line containing the terminator is encountered. The default terminator is EOF, but a different terminator can be provided.

    perl;
    print "This is a test\n";
    EOF
    
    perl END_OF_FILE;
    print "This is another test\n";
    END_OF_FILE
    
  • ping

    Ping the server. This executes the mysql_ping() C API function. The function result is discarded. The effect is that if the connection has dropped and reconnect is enabled, pinging the server causes a reconnect.

  • query [statement]

    Send the statement to the server to be executed. The query command can be used to force mysqltest to send a statement to the server even if it begins with a keyword that is a mysqltest command.

  • query_get_value(query, col_name, row_num)

    The query_get_value() function can be used only on the right hand side of a variable assigment in a let statement.

    query_get_value() enables retrieval of a value from a query result set produced by a statement such as SELECT or SHOW. The first argument indicates the query to execute. The second and third arguments indicate the column name and row number that specify which value to extract from the result set. The column name is case sensitive. Row numbers begin with 1. The arguments can be given literally or supplied using variables.

    Suppose that the test file contains this input:

    CREATE TABLE t1(a INT, b VARCHAR(255), c DATETIME);
    SHOW COLUMNS FROM t1;
    let $value= query_get_value(SHOW COLUMNS FROM t1, Type, 1);
    echo $value;
    

    The result will be:

    CREATE TABLE t1(a INT, b VARCHAR(255), c DATETIME);
    SHOW COLUMNS FROM t1;
    Field   Type    Null    Key     Default Extra
    a       int(11) YES             NULL    
    b       varchar(255)    YES             NULL    
    c       datetime        YES             NULL    
    int(11)
    

    If the query fails, an error message occurs and the test fails.

  • query_horizontal statement

    Execute the statement and display its result horizontally.

    query_horizontal SELECT PI();
    
  • query_vertical statement

    Execute the statement and display its result vertically.

    query_vertical SELECT PI();
    
  • real_sleep num

    Sleep num seconds. num can have a fractional part. Unlike the sleep command, real_sleep is not affected by the --sleep command-line option.

    --real_sleep 10
    real_sleep 5;
    

    Try not to use sleep or real_sleep commands more than necessary. The more of them there are, the slower the test suite becomes.

  • reap

    Receive the result of the statement sent with the send command within the current session. You should not use reap unless a statement has been sent with send, and you should not use send again if there is an outstanding send that has not been processed with reap.

  • remove_file file_name

    remove_file removes the file. It fails with an error if the file does not exist. The file name argument is subject to variable substitution, but must evaluate to a literal file name, not a file name pattern.

    remove_file $MYSQL_TMP_DIR/data01;
    
  • remove_files_wildcard dir_name [pattern]

    Remove all files in the named directory matching the pattern. Directories matching the pattern will not be removed, neither will matching files in subdirectories. Patterns can use ? to represent any single character, or * for any sequence of 0 or more characters. The . character is treated like any other. The pattern may not include /.

    If no pattern argument is given, all files in the directory will be removed, but not the directory itself.

    remove_files_wildcard $MYSQL_TMP_DIR file*.txt;
    
  • replace_column col_num value [col_num value] ...

    Replace strings in the output from the next statement. The value in col_num is replaced by the corresponding value. There can be more than one col_num/value pair. Column numbers start with 1.

    A replacement value can be double-quoted. (Use \" to specify a double quote within a replacement string.) Variables can be used in a replacement value if it is not double-quoted.

    If mixed replace_xxx commands are given, only the final one applies.

    Note: Although replace_regex and replace_result affect the output from exec, replace_column does not because exec output is not necessarily columnar.

    --replace_column 9 #
    replace_column 1 b 2 d;
    
  • replace_regex /pattern/replacement/[i] ...

    In the output from the next statement, find strings within columns of the result set that match pattern (a regular expression) and replace them with replacement. Each instance of a string in a column that matches the pattern is replaced. Matching is case sensitive by default. Specify the optional i modifier to cause matching to be case insensitive.

    The syntax for allowable patterns is the same as for the REGEXP SQL operator. In addition, the pattern can contain parentheses to mark substrings matched by parts of the pattern. These substrings can be referenced in the replacement string: An instance of \N in the replacement string causes insertion of the N-th substring matched by the pattern. For example, the following command matches strawberry and replaces it with raspberry and strawberry:

    --replace_regex /(strawberry)/raspberry and \1/
    

    Multiple pattern/replacement pairs may be given. The following command replaces instances of A with C (the first pattern replaces A with B, the second replaces B with C):

    --replace_regex /A/B/ /B/C/
    

    If a given pattern is not found, no error occurs and the input is unchanged.

  • replace_result from_val to_val [from_val to_val] ...

    Replace strings in the result. Each occurrence of from_val is replaced by the corresponding to_val. There can be more than from_val/to_val pair. Arguments can be quoted with single quotation marks or double quotation marks. Variable references within the arguments are expanded before replacement occurs. Values are matched literally. To use patterns, use the replace_regex command.

    --replace_result 1024 MAX_KEY_LENGTH 3072 MAX_KEY_LENGTH
    replace_result $MASTER_MYPORT MASTER_PORT;
    
  • require file_name

    This command specifies a file to be used for comparison against the results of the next query. If the contents of the file do not match or there is some other error, the test aborts with a this test is not supported error message.

    --require r/slave-stopped.result
    --require r/have_moscow_leap_timezone.require
    
  • reset_connection

    Reset the connection state by calling mysql_reset_connection(). This command is available from MySQL 5.7.

  • result file_name

    This command specifies a file to be used for comparison when the test case completes. If the content does not match or there is some other error, write the result to r/file_name.reject.

    If the --record command-line option is given, the result command changes the file by writing the new test result to it.

  • result_format version

    Set the format to the specified version, which is either 1 for the current, default behavior, or to 2 which is an extended alternative format. The setting is in effect until it is changed or until the end of the test.

    In format version 2, empty lines and indentation in the test file are preserveded in the result. Also, comments indicated by a double ## are copied verbatim to the result. Comments using a single # are not copied. Format version 2 makes it easier for humans to read the result output, but at the cost of somewhat larger files due to the white space and comments.

    --result_format 2
    
  • rmdir dir_name

    Remove a directory named dir_name. Returns 0 for success and 1 for failure.

    --rmdir testdir
    
  • save_master_pos

    For a master replication server, save the current binary log file name and position. These values can be used for subsequent sync_with_master or sync_slave_with_master commands.

  • send [statement]

    Send a statement to the server but do not wait for the result. The result must be received with the reap command. You cannot execute another SQL statement on the same connection between send and reap.

    If statement is omitted, the send command applies to the next statement executed. This means that send can be used on a line by itself before a statement. Thus, this command:

    send SELECT 1;
    

    Is equivalent to these commands:

    send;
    SELECT 1;
    
  • send_eval [statement]

    Evaluate the command, then send it to the server. This is a combination of the send and eval commands, giving the functionality of both. After variable replacement has been done, it behaves like the send command.

    --send_eval $my_stmt
    
  • send_quit connection

    Sends a COM_QUIT command to the server on the named connection.

    send_quit con;
    
  • send_shutdown

    Sends a shutdown command to the server but does not wait for it to complete the shutdown. Test execution proceeds as soon as the shutdown command is sent.

  • shutdown_server [timeout]

    Stops the server. This command waits for the server to shut down by monitoring its process ID (PID) file. If the server's process ID file is not gone after timeout seconds, the process will be killed. If timeout is omitted, the default is 60 seconds.

    shutdown_server;
    shutdown_server 30;
    
  • skip [message]

    Skips the rest of the test file after printing the given message as the reason. This can be used after checking a condition that must be satisfied, as a way of performing an exit that displays a reason. Suppose that the test file mytest has these contents:

    let $v= 0;
    if (!$v)
    {
      skip value is zero, skipping test;
    }
    echo This command is never reached;
    

    Executing mysqltest -x mytest yields these results:

    The test './mytest' is not supported by this installation
    Detected in file ./mytest at line 4
    reason: value is zero, skipping test
    

    If the test is run from mysql-test-run.pl, you will instead see the test result as [ skipped ] followed by the message.

  • sleep num

    Sleep num seconds. num can have a fractional part. If the --sleep command-line option was given, the option value overrides the value given in the sleep command. For example, if mysqltest is started with --sleep=10, the command sleep 15 sleeps 10 seconds, not 15.

    --sleep 10
    sleep 0.5;
    

    Try not to use sleep or real_sleep commands more than necessary. The more of them there are, the slower the test suite becomes.

  • sorted_result

    Sort the output from the next statement if it produces a result set. sorted_result is applied just before displaying the result, after any other result modifiers that might have been specified, such as replace_result or replace_column. If the next statement produces no result set, sorted_result has no effect because there is nothing to sort.

    sorted_result;
    SELECT 2 AS "my_col" UNION SELECT 1;
    let $my_stmt=SELECT 2 AS "my_col" UNION SELECT 1;
    --sorted_result
    eval $my_stmt;
    --sorted_result
    --replace_column 1 #
    SELECT '1' AS "my_col1",2 AS "my_col2"
    UNION
    SELECT '2',1;
    

    sorted_result sorts the entire result of the next query. If this involves constructs such as UNION, stored procedures, or multi-statements, the output will be in a fixed order, but all the results will be sorted together and might appear somewhat strange.

    The purpose of the sorted_result command is to produce output with a deterministic order for a given set of result rows. It is possible to use ORDER BY to sort query results, but that can sometimes present its own problems. For example, if the optimizer is being investigated for some bug, ORDER BY might order the result but return an incorrect set of rows. sorted_result can be used to produce sorted output even in the absence of ORDER BY.

    sorted_result is useful for eliminating differences between test runs that may otherwise be difficult to compensate for. Results without ORDER BY are not guaranteed to be returned in any given order, so the result for a given query might differ between test runs. For example, the order might vary between different server versions, so a result file created by one server might fail when compared to the result created by another server. The same is true for different storage engines. sorted_result eliminates these order differences by producing a deterministic row order.

    Other ways to eliminate differences from results without use of sorted_result include:

    • Remove columns from the select list to reduce variability in the output

    • Use aggregate functions such as AVG() on all columns of the select list

    • Use ORDER BY

    The use of aggregate functions or ORDER BY may also have the advantage of exposing other bugs by introducing additional stress on the server. The choice of whether to use sorted_result or ORDER BY (or perhaps both) may be dictated by whether you are trying to expose bugs, or avoid having them affect results. This means that care should be taken with sorted_result because it has the potential of hiding server bugs that result in true problems with result order.

  • source file_name

    Read test input from the named file.

    If you find that several test case files contain a common section of commands (for example, statements that create a standard set of tables), you can put those commands in another file and those test cases that need the file can include it by means of a source file_name command. This enables you to write the code just once rather than in multiple test cases.

    Normally, the file name in the source command is relative to the mysql-test directory because mysqltest usually is invoked in that directory.

    A sourced file can use source to read other files, but take care to avoid a loop. The maximum nesting level is 16.

    --source include/have_csv.inc
    source include/varchar.inc;
    

    The file name can include variable references. Variables are expanded including any quotation marks in the values, so normally the values should not include quotation marks. Suppose that /tmp/junk contains this line:

    SELECT 'I am a query';
    

    The following example shows one way in which variable references could be used to specify the file name:

    let $dir= /tmp;
    let $file= junk;
    source $dir/$file;
    
  • start_timer

    Restart the timer, overriding any timer start that occurred earlier. By default, the timer starts when mysqltest begins execution.

  • sync_slave_with_master [connection_name]

    Executing this command is equivalent to executing the following commands:

    save_master_pos;
    connection connection_name;
    sync_with_master 0;
    

    If connection_name is not specified, the connection named slave is used.

    The effect is to save the replication coordinates (binary log file name and position) for the server on the current connection (which is assumed to be a master replication server), and then switch to a slave server and wait until it catches up with the saved coordinates. Note that this command implicitly changes the current connection.

    A variable can be used to specify the connection_name value.

  • sync_with_master offset

    For a slave replication server, wait until it has caught up with the master. The position to synchronize to is the position saved by the most recent save_master_pos command plus offset.

    To use this command, save_master_pos must have been executed at some point earlier in the test case to cause mysqltest to save the master's replication coordinates.

  • system command [arg] ...

    Execute the shell command using the system() library call. References to variables within the command are replaced with the corresponding values. Use \$ to specify a literal $ character.

    --system echo '[mysqltest1]' > $MYSQLTEST_VARDIR/tmp/tmp.cnf
    --system echo 'port=1234' >> $MYSQLTEST_VARDIR/tmp/tmp.cnf
    system rm $MYSQLTEST_VARDIR/master-data/test/t1.MYI;
    
    Note

    exec or system are sometimes used to perform file system operations, but the command for doing so tend to be operating system specific, which reduces test portability. mysqltest now has several commands to perform these operations portably, so they should be used instead: remove_file, chmod, mkdir, and so forth.

  • vertical_results

    Set the default query result display format to vertical. Initially, the default is to display results horizontally.

    --vertical_results
    
  • wait_for_slave_to_stop

    Poll the current connection, which is assumed to be a connection to a slave replication server, by executing SHOW STATUS LIKE 'Slave_running' statements until the result is OFF.

    For information about alternative means of slave server control, see Section 4.13, “Writing Replication Tests”.

  • while (expr)

    Begin a while loop block, which continues until an end line. mysqltest executes the block repeatedly as long as the expression is true (non-zero). See flow-control constructs. Section 6.4, “mysqltest Flow Control Constructs”, for further information about while statements.

    Make sure that the loop includes some exit condition that eventually occurs. This can be done by writing expr so that it becomes false at some point.

    let $i=5;
    while ($i)
    {
      echo $i;
      dec $i;
    }
    
  • write_file file_name [terminator]

    Write the following lines of the test file to the given file, until a line containing the terminator is encountered. The default terminator is EOF, but a different terminator can be provided. The file name argument is subject to variable substitution. An error occurs if the file already exists.

    write_file $MYSQL_TMP_DIR/data01;
    line one for the file
    line two for the file
    EOF
    
    write_file $MYSQL_TMP_DIR/data02 END_OF_FILE;
    line one for the file
    line two for the file
    END_OF_FILE