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 /tmp/data01;
line one for the file
line two for the file
EOF
append_file /tmp/data01;
line three for the file
EOF

write_file /tmp/data02 END_OF_FILE;
line one for the file
line two for the file
END_OF_FILE
append_file /tmp/data02 END_OF_FILE;
line three for the file
END_OF_FILE

append_file was added in MySQL 4.1.23/5.0.41/5.1.17.

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 /tmp/data01;

cat_file was added in MySQL 4.1.23/5.0.41/5.1.17.

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

change_user was added in MySQL 5.1.23.

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 /tmp/data_xxx01;

chmod was added in MySQL 4.1.23/5.0.32/5.1.15.

connect (name, host_name, user_name, password, db_name [,port_num [,socket [,options]]])

Open a connection to the server and make the connection the current connection. (Syntax oddities: There must be whitespace between connect and the opening parenthesis, and no whitespace after the opening parenthesis.)

The arguments to connect are:

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;

As of MySQL 5.1.32 and 6.0.10, a variable can be used to specify the connection_name value.

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.

copy_file was added in MySQL 4.1.23/5.0.30/5.1.13.

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

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

die was added in MySQL 4.1.23/5.0.30/5.1.12.

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.

diff_files was added in MySQL 4.1.23/5.0.41/5.1.17.

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.

As of MySQL 5.1.32 and 6.0.10, 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_info, enable_info

Disable or enable additional information about SQL statement results. This setting 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. This setting 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.

--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_ps_warnings, enable_ps_warnings

Disable or enable prepared-statement warnings. This setting is enabled by default.

--disable_ps_warnings
--enable_ps_warnings

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 applies to connections made afterward.

--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. (MySQL 4.0 and up only.) The default is whatever the default is for the C API library.

--disable_rpl_parse
--enable_rpl_parse

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.

--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.

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

--error ER_TABLE_EXISTS_ERROR

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.

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, these two commands have different effects:

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

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

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;

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.

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;

file_exists was added in MySQL 4.1.23/5.0.30/5.1.13.

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 true. 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 greater than 0, (counter=0);
}
if (!$counter)
{
  echo Counter is not 0, (counter=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. mysqltest aborts with an error if the value is erroneous.

As of MySQL 5.0.26/5.1.12, 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;

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'};

As of MySQL 4.1.24/5.0.44/5.1.20, let syntax is extended to allow 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

mkdir was added in MySQL 5.0.58/5.1.24/6.0.5.

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 /tmp/data01 /tmp/test.out;

move_file was added in MySQL 5.1.34/6.0.12.

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

perl was added in MySQL 4.1.23/5.0.30/5.1.13.

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)

As of MySQL 4.1.24/5.0.44/5.1.20, the query_get_value() function can be used on 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

let $value= query_get_value(SHOW COLUMNS FROM t1, Type, 1);
echo $value;
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 /tmp/data01;

remove_file was added in MySQL 4.1.23/5.0.30/5.1.13.

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.

The replace_regex command was added in MySQL 5.1.6.

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 quotes or double quotes. 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

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 ew test result to it.

rmdir dir_name

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

--rmdir testdir

rmdir was added in MySQL 5.0.58/5.1.24/6.0.5.

rpl_probe

Unknown.

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.

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;

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;

This command was added in MySQL 5.1.26/6.0.6.

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:

if ( 1 != 0 )
{
  skip One not equal to 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 3
reason: One not equal to zero, skipping test
skipped

skip was added in MySQL 4.1.23/5.0.32/5.1.18.

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.

--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.

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:

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.

sorted_result was added in MySQL 4.1.23/5.0.32/5.1.18.

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;

As of MySQL 4.1.24, 5.0.50, and 5.1.21, the file name can include variable references. Variables are expanded including any quotes in the values, so normally the values should not include quotes. 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.

As of MySQL 5.1.32 and 6.0.10, 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.

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

--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;

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 /tmp/data01;
line one for the file
line two for the file
EOF

write_file /tmp/data02 END_OF_FILE;
line one for the file
line two for the file
END_OF_FILE

write_file was added in MySQL 4.1.23/5.0.30/5.1.13.