Russian Version

Chapter 4. Miscellaneous.

There are cases when wrong output is just symptom of wrong input made before.

For example, you start to receive wrong data at particular step of the scenario. After analysis of the SELECT query (or queries) is clear what queries are correct and return exactly same data which exists in the table (or tables).

This means wrong data was inserted at the earlier step.

How to know when it happened?

Start from the step of the scenario which exists just before step which does output, check every query as was described in earlier chapters. If everything works correctly, examine earlier step, repeat until you find the error.

We can examine example with list from Chapter 1 as example of such behavior.

Lets see at output one more time.

Existent systems

* System 1
* Test
* test2
* test2
* test2
* test2
* test2

Enter name of new system:
<>
Description:
<>

<Go!>

In Chapter 1 we discussed scenario which modifies the table. But what if this code firstly found at the step which outputs data? Here is the code which outputs the data:

return $this->addParameters(array(Field::ITEMS => DAO::system()->getPlainList()));

SELECT query will be absolutely correct:

SELECT `system`.`id`, `system`.`name`, `system`.`description` FROM `system`

Lets see what is in the table:

mysql> select * from system;
+----+----------+-------------------------------------------------+
| id | name     | description                                     |
+----+----------+-------------------------------------------------+
|  1 | System 1 | Man and woman clothing construction             |
|  2 | Test     | Testing Geometric set                           |
|  3 | test2    | New test                                        |
|  4 | test2    | foobar                                          |
|  8 | test2    |                                                 |
+----+----------+-------------------------------------------------+
5 rows in set (0.00 sec)

This means we should return to step before when we added element of the list to find cause of the problem and what we successfully did in Chapter 1.

Method #7: repeat your scenario backward step-by-step until you found the problem query.

Not fast, isn't it? Is there any way to find cause of the problem in faster way?

Yes. Every query, including DML queries, returns result set. Sure check it! Also check errors and warnings which queries return. Usually problem query returns information what is wrong, but if user doesn't check this information she misses it. Use means of your favorite programming language for checking results and errors of the query.

For DML queries MySQL returns following data:

mysql> update system set name='test3' where id=8;
Query OK, 1 row affected (0.55 sec)
Rows matched: 1 Changed: 1 Warnings: 0

Below I describe in details what this information means and how to get it not only in command line client, but in the user application too. I will use syntax of C API. In case of alternate connectors function most likely will be named similar to C API names. JDBC, which is used by Connector/J and ODBC have own interfaces for getting this information. Use them!

Pay attention for following information:

Query OK, N row affected

Query executed, N rows changed.

C API:

Use function

mysql_affected_rows()
to get same information in your aplication.

Rows matched: M

M corresponding rows found.

C API:

Function

mysql_info()
returns additional information of the last query in the string format.

Changed: P

P changed. Notice M and P can be different: MySQL checks if corresponding row contains same data as you are trying to insert there and doesn't update such a row.

C API:

Function

mysql_info()
returns additional information of the last query in the string format.

Warnings: R

Last query returned R warnings. You get warnings if there were some issues with the query, but it was executed and rows modified. Sure check warnings, because they inform about potential problems.

In your program you can use functions following:

C API:

mysql_info()
- returns additional information of the last query in the string format.
mysql_warning_count()
- how many warnings last query returned
mysql_sqlstate()
- last SQLSTATE. "0000" means 0 errors and 0 warnings

Also is useful to check error. Use functions following:

C API:

mysql_errno()
- MySQL number of the error
mysql_error()
- text of the error

It is convinient to write all messages in the separate log file which you can examine later to find wrong data.

Method #8: always check result of the query! Use means of your connector or interface of interactive client.

Unfortunately there are situations when missed logic error in DML query leads to having wrong data. Often you can notice this if you compare number of affected rows with number which you expect, but this number not always can be predicted. In this case you can only use method #7.

Back Content Forward



Author 2010 Sveta Smirnova
COPYRIGHT © 2010 S.Smirnova and S. Lasunov
sveta_at_js-client_dot_com