A Basic Guide to MySQL Tests Automation

In one of the previous articles, I described how to create test cases for MySQL, as well as display their results in a form suitable for further automation.

As a result, we created a script that can be automatically run on certain kinds of events, as well as interpret its results, basing the automation execution logic on them.

Let's look at how this can be done and how to check the test results.

Test script output

First, let's recall the output of our test script:

That is, the result of each text is represented as a string:

[TEST]                                      <Test name> [<Result>]

[TEST] - a sign that the line is the result of a test, it is needed to be able to filter test results from the output of other commands, we will consider this below in more detail.

<Test name> - name of the test

[<Result>] - the result of the test, OK or FAILED, you can set any other values to display passed or failed tests.

We need to run the script from the command line without asking any information from the user.

To do this, we will configure authentication credentials so that we do not have to enter a password every time we run the MySQL command and do not specify it in the run command itself.

This can be done with the following command:

mysql_config_editor set --login-path=local_test --host=localhost --port=3306 --user=test --password

When executing it, we will need to enter the password once, it will be saved in the obfuscated form in the .mylogin.cnf file, and then it will no longer be requested (more details here).

In this case, we will run the script with the command:

mysql --login-path=local_test --database=employees --raw -s < tests.sql

Actually, the output of the script will look like this:

Here you can note the first line containing the result of calling the get_employees function.

There may be other lines, the presence of which depends on the code of the functions being tested, the flags of the MySQL command with which the script is started, and the system environment in which MySQL is started.

It is possible to filter out unwanted lines, leaving only lines with test results.

In this case it can be done using the "[TEST]" substring in the examples above as a filter for grep command, later I will show the code for this.

Having lines with test results, we can analyze them and build further logic.

Handling results

For convenience, let's save the script output and the mysql return code to the RESULTS and SCRIPT_RESULT variables:

RESULTS=$(mysql --login-path=local_test --database=employees --raw -s < tests.sql)
SCRIPT_RESULT=$?

The SCRIPT_RESULT variable will be 0 if the SQL script was successfully executed (no errors in the code that runs the test script), and 1 in case of errors.

This result should not be confused with the result of the tests themselves.

To check the successful execution of the tests themselves, there should be no Failed tests, that is, there should be no test lines with the result [FAIL], this can be checked with the command:

TEST_RESULT=$(echo "$RESULTS" | grep -F "[TEST]" | grep -F "[FAIL]" | wc -l)

Here we take the results of the script, select only the lines with test results, and then count the number of lines containing "[FAIL]", that is, the number of failed tests.

0 will mean that all tests were successful, and the overall test result can be considered successful.

Test the testing script

Let's put the described commands into one script:

#!/bin/bash
RESULTS=$(mysql --login-path=local_test --database=employees --raw -s < tests.sql)

SCRIPT_RESULT=$?

TEST_RESULT=$(echo "$RESULTS" | grep -F "[TEST]" | grep -F "[FAIL]" | wc -l)

echo SCRIPT_RESULT: $SCRIPT_RESULT
echo TEST_RESULT: $TEST_RESULT

Let's run it and see the result:

$ ./tests.sh
SCRIPT_RESULT: 0
TEST_RESULT: 0

In this case, the mysql command ran without errors and all tests passed.

For clarity, let's call an error first in the mysql command, for example, by trying to execute a non-existent file with tests:

RESULTS=$(mysql --login-path=local_test --database=employees --raw -s < nonexisting_file.sql)

The result of the script:

$ ./tests.sh
./tests.sh: line 2: nonexisting_file.sql: No such file or directory
SCRIPT_RESULT: 1
TEST_RESULT: 0

Or specifying a non-existent database:

RESULTS=$(mysql --login-path=local_test --database=nonexisting_database --raw -s < tests.sql)

$ ./tests.sh
ERROR 1044 (42000): Access denied for user 'test'@'localhost' to database 'nonexisting_database'
SCRIPT_RESULT: 1
TEST_RESULT: 0

We see that when running the shell script, various errors are generated, but in both cases this leads to the fact that we have a number other than 0 in the SCRIPT_RESULT variable.

We can handle this condition and perform the appropriate actions.

Let's return the line back so that the script runs successfully:

RESULTS=$(mysql --login-path=local_test --database=employees --raw -s < tests.sql)

Now let's check the situation when there are failed tests.

Let's comment out one of the lines that perform the deletion in the "Cleanup" test:

-- DELETE FROM employees WHERE emp_no = @emp_no;

And run the script:

$ ./tests.sh
SCRIPT_RESULT: 0
TEST_RESULT: 1

We can see that the script was executed without errors, but the tests themselves were unsuccessful: in this case, not all test data was deleted, therefore the "Cleanup" test was failed and, in turn, the overall test result also failed.

By analyzing SCRIPT_RESULT and TEST_RESULT, you can control the processes of test execution, deployment, user alerts, etc.