Unfortunately, it is not always possible to catch an error at the testing stage. Often they manifest themselves only under real load.

How do you know about them?

One of the most important sources of information about problems is the error log file. There you will find information about problems such as server crashes, connection errors (if the log_warnings = 2 option is enabled), options that were specified in the configuration file, but were not included due to an error, and a number of others. The rule of work with the error log is this: if something incomprehensible is going on, first of all check the entries in the error log. Error log file also contains information about server errors that are not available to clients. Therefore, it is advisable to keep it always on even if you have application-level logging enabled.

Reception №25: if something incomprehensible is going on, first check the entries in the error log.

You can also configure InnoDB Monitor, which will write all information about InnoDB transactions in the error log file.

Reception №26: set up InnoDB Monitor to have in the error log information about all transactions using InnoDB tables.

Another important source of information is the slow query log. It contains all queries that are executed in more than long_query_time seconds. The default value of long_query_time is 10 seconds, but you can change it. Use slow query log to search for slow queries. It can also be configured to record all queries that do not use indexes.

Method # 27: use slow query log to detect slow queries.

After an error is found, it is necessary to test it on the command line. It is not always possible to do this on the production server. For example, if a query is found that causes the server to crash. Or a query that runs very slowly and needs a lot of resources: you need to simplify such a query or try to break it up into several simpler ones, since such a technique often leads to improved performance.

In this case, you need to create an environment as close as possible to the real one on the test machine.

In general, you need to run on a separate, for example, developer machine, MySQL server of the same version as on the production server. You also need to copy the settings from the configuration file and download the data. The easiest way is to dump using the mysqldump command, but this is not always convenient, as it can take too much time with a large amount of data. MySQL supports binary data compatibility between platforms, so you can simply copy the files of the desired tables. See the appendix on backup and data transfer methods between MySQL servers.

After a copy of the production server has been created, you can test without fear of consequences for the application.

Sometimes you need to test the query on different versions. This may be necessary if you encounter a bug in the MySQL code that was fixed later, and you want to check if the rest of your application requests will work on this new version. This may not be a bug, but a new feature.

Sometimes it is worth testing a few minor versions to choose the most suitable for you.

The easiest way to do this is with MySQL Sandbox. MySQL Sandbox is a cross-platform application written in Perl. You can download it from https://launchpad.net/mysql-sandbox

Download the * tar.gz package with the version you need, then install the MySQL Sandbox and run the command:

Replace mysql-5.4.2-beta-linux-x86_64-glibc23.tar.gz with the version you need.

Above, you saw that the sandbox server was installed in the $ HOME / sandboxes / msb_5_4_2 directory. It was also launched:

Stop the server using the command

Make the necessary changes to the configuration file, copy the data, then start the server:

The sandbox (and the sandbox is translated from English as a sandbox) is ready! You can start testing.

Admission # 28: use MySQL Sandbox to quickly and conveniently test your application on several versions of MySQL.

It is not always convenient to detect an error using all the data. For example, you get an incorrect result by querying several rows from a table with millions of rows. If for any reason this query is executed slowly, you will wait long enough for the result of each of the test queries.

Most often, incorrect results occur when using the WHERE predicate in conjunction with other predicates, such as LIMIT, ORDER BY, GROUP BY, HAVING, or if WHERE contains several conditions.

You can minimize the test case using only part of the data.

Create a table with the same fields as the original:

Then load only part of the data into this table:

Next, work with the test_problem table until you find the reason for the wrong behavior. Correct the original request.

The same technique can be used for queries that simultaneously use multiple tables.

Reception №29: use part of the data when working with queries that return incorrect results on large volumes.

Results

In the last part, we looked at how to test problems on a production server. Repeat them:

Reception №25: if something incomprehensible is going on, first check the entries in the error log.

Reception №26: set up InnoDB Monitor to have in the error log information about all transactions using InnoDB tables.

Method # 27: use slow query log to detect slow queries.

Admission # 28: use MySQL Sandbox to quickly and conveniently test your application on several versions of MySQL.

Reception №29: use part of the data when working with queries that return incorrect results on large volumes.