MySQL Testing Framework Simplified: Comprehensive Aspects

MySQL is one of the majorly used Databases that companies use to manage their daily business activities. Data consumption is increasing rapidly, and companies always need to maintain their Databases in the optimal condition for faster query processing and transactions.

Any failure to Server can disrupt the business activities. To avoid any Database failure or data disintegration problems, it is a good practice to regularly run MySQL Testing on your servers and keep a check on all the Databases. There are various techniques to perform MySQL Testing, and developers regularly run these tests to maintain the data integrity in MySQL Servers.

MySQL Testing Framework is a suite that helps developers test Database Connection and test MySQL Database with ease and automates some of the processes. In this article, you will have a brief introduction to MySQL and MySQL Testing Framework. You will also learn about the steps to test Database Connection and perform MySQL Testing on a Database. 

Table of Contents

1. Prerequisites
2. What is MySQL?
   • Key Features of MySQL
3. What is MySQL Testing Framework?
   • MySQL Testing Framework System Requirements
   • How to Report Bugs from MySQL Testing Suite?
4. What are the Steps to Test Your Database Connection?
5. How to Write MySQL Test Cases?
6. How to Run MySQL Testing Cases?
7. Cleaning Up from a Previous MySQL Testing Run
8. How to Generate a MySQL Testing Case Result File?
9. Specifying MySQL Testing Case-Specific through
   • Bootstrap Options
   • Client Options
   • Server Options
10. Checking for Expected Errors
11. Conclusion

Prerequisites

• MySQL Server installed on your local system.
• Some basic knowledge of SQL.

What is MySQL?

MySQL is a fully-managed open-source Relational Database Management System (RDBMS). It organizes data into one or more tables that are related to each other. With the help of MySQL, companies manage their business activities and store valuable data in a structured manner. MySQL was acquired by Sun Microsystems in 2008 and then owned by Oracle in 2010. MySQL provides high security, reliability that allows multiple users to access your Databases and runs queries using the SQL (Structured Query Language) language. 

MySQL is one of the widely used RDBMS in top websites, servers, etc., and comes with many versions that run on a variety of operating systems, including Linux, Mac OS X, Windows, Free BSD, Solaris, and others.

Key Features of MySQL

MySQL is an easy-to-use Database and allows companies to run their business operations effectively. A few features of MySQL are listed below:

• Highly Scalable: MySQL supports multi-threading that can handle as much as 50 million rows or more which makes it a highly scalable Database solution.
• GUI Support: MySQL comes with a user-friendly graphical user interface tool MySQL Workbench that allows Database Architects, Developers, Database Administrators to manage MySQL Database easily.
• Secure: MySQL is designed with a solid data security layer that protects sensitive data from getting leaked by intruders. It also comes with password encryption on MySQL servers.

To know more about MySQL, here.

What is MySQL Testing Framework?

MySQL Test Framework is a testing suite that includes a set of test cases and programs for MySQL Testing and running. It consists of tools that are responsible to verify the proper functioning of the MySQL Server and its client programs. These test cases mainly use SQL statements and can use test language constructs that control how to run and verify tests. The framework also comes with facilities for running unit tests and creating new unit tests. The “mysql-test-run.pl” is an application that runs the test suite located in “mysql-test” directory. 

MySQL Testing Framework System Requirements

The MySQL test and MySQL client test programs are written in C++ and are available on any machine that can compile MySQL or that has a binary MySQL distribution.

Other components of the test framework, such as mysql-test-run.pl, are Perl scripts that should be run on systems that have Perl installed.

To compare expected and actual test results, mysqltest use the diff software. If no diff is identified, mysqltest generates an error message and dumps the whole contents of the.result and.reject files so that you may try to figure out why a test failed. If your system lacks diff, you may be able to download it from one of the following websites:

• http://www.gnu.org/software/diffutils/diffutils.html
• http://gnuwin32.sourceforge.net/packages/diffutils.htm

Due to the difficulties of handling this effectively in numerous situations, mysql-test-run.pl cannot operate properly if started from within a directory whose complete path includes a space character.

How to Report Bugs from the MySQL Testing Suite?

If any of the test cases in the test suite fail, you should take the following actions:

• Do not submit a bug report until you have determined as much as possible about what went wrong. See here for details.
• Include the output of mysql-test-run.pl, as well as the contents of all.reject files in the mysql-test/var/log directory, or the (often considerably shorter) diff supplied by mysql-test-run.pl. Check to see if a single test in the test suite fails when performed on its own:

cd mysql-test
./mysql-test-run.pl test_name

If this fails and you’re constructing MySQL yourself, set up MySQL with —with-debug and execute mysql-test-run.pl with the —debug option. If this also fails, create a bug report and attach the trace file mysql-test/var/tmp/master.trace to it so that we may look into it. See How to Report Bugs or Problems for further information. Please offer a detailed description of your system, the version of the mysqld binary, and how you compiled it.

• Execute mysql-test-run. pl with the —force flag to see if any further tests fail.
• If you compiled MySQL yourself, consult the MySQL Reference Manual to discover if your system has any platform-specific concerns. There could be configuration workarounds to deal with the issues you’re seeing. Consider utilizing one of the binaries we’ve put together for you at https://dev.mysql.com/downloads/. The test suite should pass all of our standard binaries!

• If you see an error message such as Result length mismatch or Result content mismatch, it signifies that the test’s output did not match the intended output exactly. This could be a MySQL problem, or it could be that your version of mysqld delivers slightly different results under certain conditions. This mysql-test-run.pl output should include a comparison of the expected and actual results. If it is unable to generate a diff, it will instead print both in full. Furthermore, the real result is saved in a file with the.result extension in the r directory.

• If a test fails altogether, look through the logs file in the mysql-test/var/log directory for clues as to what went wrong.

• If you have debugging enabled in MySQL, you can try to debug test failures by running mysql-test-run.pl with one or both of the —gdb and —debug options. If you haven’t already, you should probably compile MySQL for debugging by using the -DWITH DEBUG option when calling CMake.

What are the Steps to Test Your Database Connection?

In this section, you will learn a simple technique that you can use to test Database Connection. Open your MySQL installation directory having “bin” subdirectory and containing the MySQL programs. The steps to test Database Connection for MySQL Testing are listed below:

• Open your terminal or shell window.
• You can use the “mysqladmin” command to verify whether the server is running or not. The following sample code given below will provide the test to check whether the server is up and responding to connections.

> bin/mysqladmin version 
> bin/mysqladmin variables

• In case you are unable to connect to the server, you can add the parameters “-u root” to connect with the server as a root. 
• Also, if you have already assigned some passwords to the root user then use the “-p” parameter to provide the valid password. The following sample code is given below for MySQL Testing Connection.

bin/mysqladmin -u root -p version 
Enter password: (enter root password here)

• After entering all the details correctly, the output for the “mysqladmin version” is similar as shown below.

bin/mysqladmin version
mysqladmin Ver 14.12 Distrib 8.0.27, for pc-linux-gnu on i686
...
Server version 8.0.27
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 14 days 5 hours 5 min 21 sec
Threads: 1 Questions: 366 Slow queries: 0
Opens: 0 Flush tables: 1 Open tables: 19
Queries per second avg: 0.000

• If your Database connectivity is correct, it will how the similar output else will throw some error.
• You can also use the “mysqlshow” command to retrieve some general information from the Database and verify if your Database Connection working correctly. A sample code is given below for this MySQL Testing.

bin/mysqlshow
+--------------------+
| Databases 	     |
+--------------------+
| information_schema |
| mysql 	     |
| performance_schema |
| sys 		     |
+--------------------+

The list of Databases except for at least “mysql” and “information_schema” may vary according to the data you have in your MySQL server.

How to Write MySQL Test Cases?

Now, you will learn how to write a simple test case for MySQL Testing and how you can perform the MySQL Testing on Database. The basic principle of test case evaluation is the comparison between the resulting output and the expected output. The test writer provides the expected result file so that it can be used further by “diff” comparison with the output file. The following steps to perform MySQL Testing are listed below:

• First, go to the test directory “mysql-version/mysql-test”, where “mysql-version” is the root directory of your source tree. The command is given below:

cd mysql-version/mysql-test

• Here, you have to create the test case in a file “t/test_name.test”, where test_name.test is the name of the test case file. 
• Now, create an empty file that will work as a result file with the command given below.

touch r/test_name.result

• Now, you can run the test with the command given below.

./mysql-test-run.pl test_name

• Let’s assume that the resulting output doesn’t match with the expected out and will create a reject file with the output as its content. The file will be saved with the name “r/test_name.reject”.
• Open the file and examine it. If the content of the output matches with the expected output then, copy its content to the result file which is empty at the moment. The command is given below.

cp r/test_name.reject r/test_name.result

• You can also create the result file directly by invoking the “mysql-test-run.pl” with the –record option to record the test output in the result file, as given in the command below.

./mysql-test-run.pl test_name

That’s it! You have completed MySQL Testing. It is a simple test, and the complexity of the MySQL Testing increases as the number of Databases, tables increases.

How to Run MySQL Testing Cases?

The test suite is often executed from within a source tree (after MySQL has been produced) or on a host where the MySQL server distribution is installed.

Your current working directory should be the mysql-test directory of your source tree or installed distribution if you want to run tests. Mysql-test is located at the base of the source tree in a source distribution. The location of mysql-test in a binary distribution is determined by the distribution layout. Mysql-test-run.pl, the software that performs the test suite, will determine if you are in a source tree or an installed directory tree.

To run the test suite, change location into your mysql-test directory and invoke the mysql-test-run.pl script:

cd mysql-test
./mysql-test-run.pl

mysql-test-run.pl accepts options on the command line. For example:

 ./mysql-test-run.pl --force --suite=binlog

By default, mysql-test-run is used —force causes execution to continue even if a test case fails.

See mysql-test-run.pl — Run MySQL Test Suite for a complete list of supported options.

To execute one or more particular test cases, use the mysql-test-run.pl command line and name them. The names of test case files are t/test name.test, where test name is the name of the test case, however, each name given on the command line should be the test case name, not the complete test case file name. The command below executes the test case rpl abcd, which has the test file t/rpl abcd.test:

./mysql-test-run.pl rpl_abcd

Use the — do-test option to run a family of test cases whose names share a similar prefix:

./mysql-test-run.pl --do-test=prefix

For example, the following command executes the events tests (test cases with names that begin with events):

./mysql-test-run.pl --suite=suite_name

For instance, the following program executes the replication tests in the rpl suite:

./mysql-test-run.pl --suite=rpl

mysql-test-run.pl starts the MySQL server, configures the environment for executing the mysqltest program, then invokes mysqltest to perform the test case. Mysqltest handles actions such as reading input from the test case file, initiating server connections, and sending SQL statements to servers for each test case that is run.

The language used in test case files is a combination of commands that the mysqltest tool understands and SQL statements. Input that mysqltest does not understand is considered to be SQL statements to be submitted to the database server. This makes the test case language familiar to individuals who know how to write SQL and powerful enough to add the control required to construct test cases.

You do not need to start a MySQL server before conducting tests. Instead, the mysql-test-run.pl script will launch the server or servers as needed. By default, any servers established for the test run utilise ports beginning with 13000.

Cleaning Up from a Previous MySQL Testing Run

Because the mysqltest test engine does not start with a clean new database for each test case in order to save time, each test case typically begins with a “cleaning up section.”

Assume that a test case will make use of two tables, t1 and t2. The test case should begin by ensuring that no previous tables with such names exist:

--disable_warnings
DROP TABLE IF EXISTS t1,t2;
--enable_warnings

The disable warnings command instructs the test engine not to log any warnings until either the enable warnings command or the test case is terminated. (If the table t1 or t2 does not exist, MySQL issues a warning.) When this section of the test case is surrounded by commands to deactivate and enable warnings, the output is the same regardless of whether the tables exist before the test begins.

After confirming that the tables do not exist, we can insert any SQL queries that create and use the tables t1 and t2. At the end of the test, the test case should also clean up by deleting any tables that it generates.

Let’s put in some SQL code into this test case:

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

DROP TABLE t1, t2;

If a test case creates other objects, such as stored applications or user accounts, it should make sure to clear them away at the start and end of the test. Temporary files should also be deleted, either at the end of the process or shortly after they have been used.

How to Generate a MySQL Testing Case Result File?

There are no checks on the result in the test code we have just written.

The failure of the test will be reported for one of two reasons:

1. A single SQL statement fails with an error.
2. The overall test case result differs from what was expected.

Please keep in mind that these are the reasons why mysqltest might fail; if the test is run from mysql-test-run.pl, the test may fail for other reasons.

Mysqltest aborts with an error in the first example. The second instance necessitates the keeping of a record of the expected result, which can then be compared to the actual result. To create a file with the test results, run the test with the —record option, as shown below:

cd mysql-test
./mysql-test-run.pl --record foo

When the test is run as shown, a result file named mysql-test/r/foo.result is created with the following content:

DROP TABLE IF EXISTS t1,t2;
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;
period
9410
SELECT * FROM t1;
Period  Varor_period
9410    9412
SELECT t1.* FROM t1;
Period  Varor_period
9410    9412
SELECT * FROM t1 INNER JOIN t2 USING (Period);
Period  Varor_period
9410    9412
DROP TABLE t1, t2;
ok

When we look at this result file, we can see that it contains the statements from the foo.test file as well as the output from the SELECT statements. Each statement’s output provides a row of column titles followed by data rows. Columns are separated by Tab characters in rows.

At this point, you should review the result file to ensure that its contents are correct. If so, include it in your test case. If the outcome is not as expected, you have discovered an issue with the server or the test. Determine the source of the problem and resolve it. For example, the output of the test may differ from run to run.

Specifying MySQL Testing Case-Specific Through

Bootstrap Options

If a test must run with a specific value of a bootstrap variable, such as —innodb-page-size or —innodb-data-file-path, the option can be specified on the command line when running mysql-test-run. pl.

Consider a test that can only be run with —innodb-page-size=8k. The test can be performed as follows:

./mysql-test-run.pl test_name_8k --initialize=--innodb-page-size=8k

This will set the data directory’s page size to 8k and start the server with that value.

It is also possible to pass bootstrap options in the test’s master.opt file, allowing the test to execute with the specified bootstrap options value without needing any command-line arguments. This is how it’s done:

--initialize --innodb-page-size=8k

The preferred technique is to specify bootstrap variables in the opt file.

To assure mysql-test-run, each bootstrap variable must be supplied as the value of a —initialize option in the opt file.

pl understands that the variable must be utilized during server startup. If the file contains bootstrap options, the current data directory is wiped and re-initialized using the options specified in the file. The bootstrap parameters are also passed together with the other options in the opt file when the server is launched.

Bootstrap settings are similarly sent to the slave. In replication scenarios, opt will be used to reinitialize the slave server.

The opt file’s bootstrap options are provided to the server first, followed by the server options. So, regardless of the order in which they are placed in the opt file, server start-up options take precedence over bootstrap options.

It should be noted that because the bootstrap options are supplied in the opt files, they take precedence over the command line bootstrap options. So, if a test has a master.opt file that has —innodb-page-size=8k, and —innodb-page-size=4k is supplied on the command line while the test is running, the test will run with an 8k page size.

Some tests necessitate restarting the server numerous times with different server parameters and bootstrap options. In such circumstances, the old data directory within the test is removed, and a new data directory is created using the bootstrap parameters. Following this, when the server is launched with the bootstrap options, the SQL queries will run with the supplied options.

--exec $MYSQLD --no-defaults --initialize-insecure
--lc_messages_dir=$MYSQL_SHAREDIR --innodb_page_size=8K
--basedir=$MYSQLD_BASEDIR --datadir=$MYSQL_TMP_DIR/datadir1/data
--init-file=$BOOTSTRAP_SQL --secure-file-priv=""

Client Options

If a test case requires a specific client option, such options can be specified in a file called testname-client.opt.

When a test case has its own -client.opt file, mysql-test-run.pl looks through it for any additional options that mysqltest should use while running the test case.

Consider the following example of a test case.

If the test requires latin1 as the default character set for client and connection, this can be provided in the example-client.opt file.

--default-character-set=latin1

In the option file, you can use the $VAR NAME syntax to refer to environment variables. Optional variables can also be referred to using the syntax $?VAR NAME. If the variable is not set, this will be replaced with an empty string.

Checking for Expected Errors

A good test suite ensures that activities not only succeed as expected but also fail as expected.

If a statement is illegal, for example, the server should reject it with an error message. The test suite should ensure that the statement fails and that the appropriate error message is returned.

You can specify “anticipated failures” in the test engine. Assume that after creating t1, we try to construct it again without first dropping it:

--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 a failure and an error:

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

To handle this problem and communicate that it is expected, we can place an error command before the second create table statement. Either of the following commands will look for this specific MySQL error:

--error 1050
--error ER_TABLE_EXISTS_ERROR

The numeric error code is 1050, and the symbolic name is ER TABLE EXISTS ERROR. Symbolic names are more stable than error numbers because the numbers can change, especially for those created recently. For such problems, using numbers rather than names in a test case necessitates rewriting the test if the numbers change.

After we change the code to include an error command before the CREATE TABLE statement and rerun the test, the output will look like this:

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

In this scenario, the result displays the sentence that caused the mistake, as well as the error message that resulted. The fact that mysqltest does not terminate and the error notice is included in the output implies that the error was anticipated.

By specifying a SQLSTATE value, you may additionally test for errors. SQLSTATE value 42S01 corresponds to MySQL error code 1050. Use a S prefix to provide a SQLSTATE value in an error command:

--error S42S01

SQLSTATE values have the problem of sometimes corresponding to more than one MySQL error code. Using the SQLSTATE value in this scenario may be insufficiently specific (it could let through an error that you do not actually expect).

The error command accepts several arguments separated by commas if you wish to test for numerous faults. As an example:

--error ER_NO_SUCH_TABLE,ER_KEY_NOT_FOUND

The built-in variable $mysql_errno stores the numeric error returned by the most recent SQL statement delivered to the server, or 0 if the statement was successfully executed. This could be handy following statements that may or may not fail or fail in more than one way (more than one parameter to the error command) if you need to do different actions. It should be noted that this only applies to SQL statements and not to other commands.

Server Options

Many system variables can be set within a test case using phrases like these:

SET sql_warnings= 1;
SET sql_mode= 'NO_AUTO_VALUE_ON_ZERO';

However, in order to apply command-line arguments related to a given test case, you may need to restart the server. These options can be specified in a file called mysql-test/t/test name-master.opt. When a file named t/test name-master.opt is found, mysql-test-run.pl analyses it for additional options that the server must be run with when the test name test case is executed. If no server has been started yet, or if the current server is running with different options, mysql-test-run.pl restarts it with the new options.

In the option file, you can use the $VAR NAME syntax to refer to environment variables. Optional variables can also be referred to using the syntax $?VAR NAME. If the variable is not set, this will be replaced with an empty string.

The option —skip-core-file will be evaluated by mysql-test-run.pl, which will thus prevent the server from producing any core files or crash dumps during this test. This could be useful for testing that purposefully crash the server.

Another option is —testcase-delay=minutes, which allows you to provide a different, longer timeout for a specific test case. If the supplied timeout in minutes is longer than the default, it will be used for this test.

The option —skip-core-file will be evaluated by mysql-test-run.pl, which will thus prevent the server from producing any core files or crash dumps during this test. This could be useful for testing that purposefully crash the server.

Another option is —testcase-delay=minutes, which allows you to provide a different, longer timeout for a specific test case. If the supplied timeout in minutes is longer than the default, it will be used for this test.

Because the.sh file is executed via /bin/sh, it cannot be used on Windows, and any tests that use such a file will be skipped if performed on Windows. As a result, in a future release of MySQL, this mechanism may be replaced with a more portable one.

Conclusion 

In this article, you learned about MySQL, MySQL Testing Framework, and the steps to test Database Connection. You also went through the process to create test case files and perform MySQL Testing on Database. MySQL Testing is a good practice that helps avoid any data failures, maintain the overall integrity of the data, and keep the server health in check. 

Visit our Website to Explore Hevo

MySQL Server stores valuable business data that can be used to generate insights. Companies need to analyze their business data stored in multiple data sources. The data needs to be loaded to the Data Warehouse to get a holistic view of the data. Hevo Data is a No-code Data Pipeline solution that helps to transfer data from 100+ sources to desired Data Warehouse. It fully automates the process of transforming and transferring data to a destination without writing a single line of code.

Want to take Hevo for a spin? Sign Up here for a 14-day free trial and experience the feature-rich Hevo suite first hand.

Share your experience of learning about MySQL Testing Framework in the comments section below!