There are options when the wrong conclusion is only a symptom that has previously been entered incorrectly.

For example, at some step in the script, you start to get incorrect data. After analyzing the query (or queries) of the SELECT, it turns out that the queries are correct and return exactly the data that is in the table (s).

This means that incorrect data was entered in the early stages of the script.

How to find out exactly when this was done?

Start with the script step immediately following the output, check all the queries as described in previous chapters. If everything works correctly again, check the earlier step and so on until you find an error.

You can consider an example with a list from the first chapter as an example of such incorrect behavior.

Let's look again at the conclusion.

We viewed the page modifying the list. But what if such a list was found on the page that provides the output? Here is the code responsible for displaying the list:

The SELECT query will be perfectly valid:

Let's see what's in the table:

Accordingly, we will have to go back a step earlier when we added a new list item in order to find the source of the problems, which we did earlier in chapter №1.

Reception number 7: check your script step by step in the reverse order until you find the problem query.

Long, isn't it? Is it possible to do something to identify the problem in a simpler way?

Yes. All queries, including DML, always return a result. Be sure to check it out! Also check for errors and warnings that return queries. As a rule, a problem request is erroneous, but an unverified result leads to the fact that it went unnoticed. Use the tools of your favorite programming language to check the results and errors.

MySQL returns the following data for DML queries:

Below I will explain in detail what this information is about and give instructions on how to get it not only on the command line, but also in the application. I will use C API syntax. In the case of alternative connectors, the functions will most likely be called similar to the C API variants. JDBC, which uses the Connector / J and ODBC have their own interfaces to obtain the necessary information. Use them!

Note the following:

Request completed, N rows updated.

C API:

Use the function

to get this information in your application.

Found M matching lines.

C API:

Function

returns additional information about the last query as a string.

P lines of them are changed. Note that M and P may be different: MySQL notices when you do not need to update anything and does not do any extra work.

C API:

Function

returns additional information about the last query as a string.

R warnings were received. You receive warnings if there are any problems, but the request has nonetheless been completed. Be sure to follow the warnings, as they inform about potential problems.

In your program you can use the following functions:

C API:

- returns additional information about the last query as a string.
- how many warnings the previous request returned
- last SQLSTATE. "0000" means 0 errors and warnings.

It is also useful to check the error. Use the following functions:

C API:

- MySQL error number
- error in text form

It is convenient to write all messages in a separate log, which can then be viewed in order to detect erroneous data.

Reception number 8: always check the result of the query! Use your connector tools or interactive client output.

Unfortunately, there are cases when it is an unnoticed logical error in a DML request that leads to the output of incorrect data. This can often be noticed by comparing the value of the affected rows with the expected, but sometimes it is not easy to predict. In this case, only reception number 7 will help.

Subtotals.

Always try to find a query that causes unwanted behavior.
Analyze what is wrong, correct the problem according to the result.
Use dedicated techniques for analyzing results.