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 http://dev.mysql.com/doc/mysql/en/error-messages-server. You can also examine the mysqld_error.h and sql_state.h files in the include directory of a MySQL source distribution.