A good test suite checks not only that operations succeed as they ought, but also that they fail as they ought.

For example, if a statement is illegal, the server should reject it with an error message. The test suite should verify that the statement fails and that it fails with the proper error message.

The test engine enables you to specify “expected failures.” Let's say that after we create t1, we try to create it again without dropping it first:

--disable_warnings
DROP TABLE IF EXISTS t1,t2;
--enable_warnings
CREATE TABLE t1 (
  Period SMALLINT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,
  Varor_period SMALLINT(4) UNSIGNED DEFAULT '0' NOT NULL
);
CREATE TABLE t2 (Period SMALLINT);

INSERT INTO t1 VALUES (9410,9412);
INSERT INTO t2 VALUES (9410),(9411),(9412),(9413);

SELECT period FROM t1;
SELECT * FROM t1;
SELECT t1.* FROM t1;
SELECT * FROM t1 INNER JOIN t2 USING (Period);

CREATE TABLE t1 (something SMALLINT(4));

The result is failure and an error:

At line 21: query 'CREATE TABLE t1 (something SMALLINT(4))'
failed: 1050: Table 't1' already exists

To handle this error and indicate that indeed we do expect it to occur, we can put an error command before the second create table statement. Either of the following commands test for this particular MySQL error:

--error 1050
--error ER_TABLE_EXISTS_ERROR

1050 is the numeric error code and ER_TABLE_EXISTS_ERROR is the symbolic name. Symbolic names are more stable than error numbers because the numbers sometimes change, particularly for those created during recent development. For such errors, use of numbers rather than the names in a test case will require test to be revised should the numbers change.

After we make a change to add an error command before the CREATE TABLE statement and run the test again, the end of the result will look like this:

CREATE TABLE t1 (something SMALLINT(4));
ERROR 42S01: Table 't1' already exists

In this case, the result shows the statement that causes the error, together with the resulting error message. The fact that mysqltest does not terminate and that the error message becomes part of the result indicates that the error was expected.

You can also test for errors by specifying an SQLSTATE value. For MySQL error number 1050, the corresponding SQLSTATE value is 42S01. To specify an SQLSTATE value in an error command, use an S prefix:

--error S42S01

A disadvantage of SQLSTATE values is that sometimes they correspond to more than one MySQL error code. Using the SQLSTATE value in this case might not be specific enough (it could let through an error that you do not actually expect).

If you want to test for multiple errors, the error command allows multiple arguments, separated by commas. For example:

--error ER_NO_SUCH_TABLE,ER_KEY_NOT_FOUND

For a list of MySQL error codes, symbolic names, and SQLSTATE values, see https://dev.mysql.com/doc/refman/8.0/en/error-messages-server.html. You can also examine the mysqld_error.h and sql_state.h files in the include directory of a MySQL source distribution.

It is also possible to use symbolic error names to refer to client errors:

--error CR_SERVER_GONE_ERROR

For a list of MySQL client error codes, see https://dev.mysql.com/doc/refman/8.0/en/error-messages-client.html. You can also examine the errmsg.h file in the include directory of a MySQL source distribution.

The built-in variable $mysql_errno contains the numeric error returned by the most recent SQL statement sent to the server, or 0 if the statement executed successfully. This may be useful after statements that may or may not fail, or fail in more than one way (more than one argument to the error command), in case you need to perform different actions. Note that this applies to SQL statements, not to other commands.

There is also a variable $mysql_errname which contains the symbolic name of the last error. In some cases the symbolic name is not available; in those cases the variable will contain the string "<Unknown>". For new test development we recommend testing against the name rather than the number, since the number may change in future versions. If the last statement succeeded ($mysql_errno is 0), this variable is an empty string.