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
--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
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
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
command, use an
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:
For a list of MySQL error codes, symbolic names, and SQLSTATE
You can also examine the
sql_state.h files in the
include directory of a MySQL source
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.
From MySQL 5.5.17, 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
$mysql_errno is 0), this variable is
an empty string.