Lecture
Probably many of you know, at least from school mathematics, that any complex task can be reduced to a set of simple ones. The same technique can be applied to SQL application problems.
First, let's look at an example with a simple query. The error in this case is very simple.
select * fro t1 where f1 in (1,2,1); I think most readers have already noticed what the problem is. If you run this query in mysql cli, the error becomes even more noticeable:
mysql> select * fro t1 where f1 in (1,2,1);
ERROR 1064 (42000): You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version
for the right syntax to use near 'fro t1 where f1 in (1,2,1)' at line 1 That is, this is a syntax error: the last letter in the from predicate is omitted. Simple, isn't it?
Take an example more complicated. This is PHP code. (For holivor lovers, I repeat that the code was prepared for the PHP conference in Moscow. You can write similar confusion in any other favorite programming language.)
$query = 'SELECT * FROM t4 WHERE f1 IN(';
for ($i = 1; $i < 101; $i ++)
$query .= "'row$i,";
$query = rtrim($query, ',');
$query .= ')';
$result = mysql_query($query);
In this case, isolating the error is more difficult. And what will happen in the case of an even more complex query?
In the case of PHP, a simple echo statement will help us, which provides output:
$query = 'SELECT * FROM t4 WHERE f1 IN(';
for ($i = 1; $i < 101; $i ++)
$query .= "'row$i,";
$query = rtrim($query, ',');
$query .= ')';
echo $query;
//$result = mysql_query($query); Run the script:
$php phpconf2009_1.php
SELECT * FROM t4 WHERE f1 IN('row1,'row2,'row3,'row4,'row5,'row6,'row7,'row8,'row9,'row10,'row11,
'row12,'row13,'row14,'row15,'row16,'row17,'row18,'row19,'row20) And here the error becomes more obvious to us: the closing apostrophe in the expression is missing
$query .= "'row$i,"; It is enough to replace it with the expression
$query .= "'row$i',"; and the request will be executed correctly.
Please note that we displayed the query exactly in the form in which the DBMS receives it. It is much easier to find an error in a ready-made query than in a string that is collected from different parts or contains variables.
Using the inference operator to identify a problem query is the simplest but effective technique.
Reception # 1: use the inference operator to output the query in the form in which the DBMS receives it.
Unfortunately we can not always use echo. For example, in the case of support for an existing complex application.
Let's look at the following example.
The problem occurred on a web page with the following interface:
Имеющиеся системы
* Учебник
* Тест
* test2
* test2
* test2
* test2
* test2
Введите название новой системы:
<>
Описание:
<>
<Go!> The problem is that we somehow formed several systems with the same name.
Let's look at the code that is responsible for this:
$system = System::factory()
->setName($this->form->get(Field::NAME))
->setDescription(
$this->form->get(Field::DESCRIPTION)
);
DAO::system()->take($system); Do you understand anything? I think an experienced person can guess what the problem is, but in any case, this is only a hypothesis that needs to be confirmed or refuted. It is even less clear where in this example we add the conclusion and the conclusion of what it is: we have neither a call to the database nor a request.
In PHP, it is easy enough to change the library code, which is responsible for compiling the request to print it (request) to a file or to stderr before sending it to the database, but in the case of compiled languages, for example, with Java, the library will have to be recompiled. And not always the library code is open.
What to do? In the case of MySQL, we can apply the general query log:
mysql> set global log_output='table';
Query OK, 0 rows affected (0.00 sec) mysql> select * from mysql.general_log;
Empty set (0.09 sec) mysql> set global general_log='on';
Query OK, 0 rows affected (0.00 sec) Run the application.
mysql> select * from mysql.general_log order by event_time desc limit 25\G
*************************** 1. row ***************************
event_time: 2009-10-19 13:00:00
user_host: root[root] @ localhost []
thread_id: 10323
server_id: 60
command_type: Query
argument: select * from mysql.general_log order by event_time desc limit 25
...
*************************** 22. row ***************************
event_time: 2009-10-19 12:58:20
user_host: root[root] @ localhost [127.0.0.1]
thread_id: 10332
server_id: 60
command_type: Query
argument: INSERT INTO `system` (`id`, `name`, `description`) VALUES ('', 'test2', '')
...
mysql> set global general_log='off';
Query OK, 0 rows affected (0.08 sec) In line 22, we see our query. It does not look problematic: the usual INSERT.
Let's see what we have in the system table:
mysql> select * from system;
+----+---------+------------------------------------------+
| id | name | description |
+----+---------+------------------------------------------+
| 1 | Учебник | Конструирование мужской и женской одежды |
| 2 | Тест | Тестовый геометрический набор |
| 3 | test2 | New test |
| 4 | test2 | foobar |
| 8 | test2 | |
+----+---------+------------------------------------------+
5 rows in set (0.00 sec) Let's see its definition:
mysql> show create table system\G
*************************** 1. row ***************************
Table: system
Create Table: CREATE TABLE `system` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`description` tinytext NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8
1 row in set (0.09 sec) The name field is not unique. Therefore, we can solve the duplicate problem in two ways: you need to either make this field unique
(alter table system add unique(name)) or modify the application so that this problem is not handled at the SQL level.
We just looked at the following technique: use general query log to identify the query that causes the wrong behavior.
Method # 2: use the general query log if you need to determine which particular query causes the wrong behavior of your application.
Note the following feature of general query log: the query is written there immediately after mysqld received it, but before it began to execute it. Therefore, you will not see the return code and error messages.
We were convinced that it is almost always possible to isolate the request that caused the problem in the application. In most cases, this is enough to find and fix the error. This explains why I took away most of this text to search for a problem query. Of course, there are situations where finding a single request does not get off. These situations will be considered separately.
I would like to draw your attention to the following requests:
mysql> select * from mysql.general_log;
Empty set (0.09 sec) mysql> set global general_log='on';
Query OK, 0 rows affected (0.00 sec)
...
mysql> set global general_log='off';
Query OK, 0 rows affected (0.08 sec) Why do we need to change the value of a global variable if we can enable the general query log in the configuration file?
The fact is that the general query log itself is very expensive: it increases the load on the server and since it contains all the requests, you also have to keep track of disk space. Starting from version 5.1, it can be turned on and off in real time, thereby reducing the additional load on the MySQL server to a minimum.
The following query does not include output to a file, but to a table. The output in the table is good for identifying such problematic queries as in the last 2 examples, since the table is easy to sort: you refer to it as you would any other table.
mysql> set global log_output='table';
Query OK, 0 rows affected (0.00 sec) 
Comments
To leave a comment
Error detection methods in SQL application
Terms: Error detection methods in SQL application