Lecture
in the last chapter, we learned how to find a problem query.
We have already reviewed 2 examples of syntax errors and one logical example. Do these examples cover all the possibilities provided to us by analyzing a problem query? Of course not! Although strictly speaking all the errors in the request can be reduced to these two subtypes. In this chapter we will look at what else can be done with the problematic query.
Syntax errors are trivial, so we will not dwell on them further.
One common problem is that the SELECT query returns incorrect data. This can occur for a number of reasons.
Consider the simplest - in terms of elimination - an example.
mysql> select count(*) as b from t3 order by b,a;
+---+
| b |
+---+
| 2 |
| 2 |
+---+
mysql> select count(*) as b from t3;
+---+
| b |
+---+
| 2 |
+---+
1 row in set (0.01 sec)
mysql> show create table t3\G
************ 1. row ************
Table: t3
Create Table: CREATE TABLE `t3` (
`a` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec) Despite the unusual query, the behavior is obviously incorrect: count (*) cannot return more than one line, unless GROUP BY is used. The request is trivial and there is no place to simplify it. In this case, your path to http://bugs.mysql.com, in the search, where we find the corresponding bug: http://bugs.mysql.com/bug.php?id=47280 In the same place, we find out that A bug was found in version 5.1.38 and is now fixed. Accordingly, updating to version 5.1.41 or newer will fix the problem.
It goes without saying that if there is no bug like the one you discovered, send it to the same address http://bugs.mysql.com
But what if you are unable to upgrade the MySQL server at the moment? In this case, remove the order by. This example demonstrates not only that MySQL Server also contains bugs in the code, but also another method for dealing with a problematic query.
Admission number 3: after you have identified the request causing the problem, run it on the command line and analyze the result.
But there are also bugs with workaround that do not require SQL modification. As a rule, if the workaround is not obvious, it is described in the bug report.
Take the following example:
mysql> create table `a` (
-> `id` bigint(20) not null auto_increment,
-> primary key (`id`)
-> ) engine=myisam;
Query OK, 0 rows affected (0.04 sec)
mysql> create table `b` (
-> `id` bigint(20) not null auto_increment,
-> `a_id` bigint(20) default null,
-> primary key (`id`)
-> ) engine=myisam;
Query OK, 0 rows affected (0.04 sec)
mysql> insert into `a` values (1),(2),(3),(4),(5),(6),(7),(8);
Query OK, 8 rows affected (0.00 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql> insert into `b` values (1,1),(2,1),(3,1),(4,2),(5,2),(6,2),(7,3),(8,3);
Query OK, 8 rows affected (0.01 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql> select a.id as a_id, count(distinct b.id) as cnt from aa left join bb on a.id = b.a_id
-> where a.id = 1 group by a.id with rollup limit 100;
+------+-----+
| a_id | cnt |
+------+-----+
| 1 | 8 |
| NULL | 8 |
+------+-----+
2 rows in set (0.01 sec) Where do we have 8 lines in a_id = 1? It is clearly visible that we have added only 3 lines with a_id = 1:
mysql> insert into `b` values (1,1),(2,1),(3,1),
(4,2),(5,2),(6,2),(7,3),(8,3);
Query OK, 8 rows affected (0.01 sec)
Records: 8 Duplicates: 0 Warnings: 0 Which confirms the subsequent request without groupings.
mysql> select a.id as a_id, b.id from aa left join bb on a.id = b.a_id where a.id = 1;
+------+------+
| a_id | id |
+------+------+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
+------+------+
3 rows in set (0.00 sec)
This case is described at http://bugs.mysql.com/bug.php?id=47650. It was also found in version 5.1.38 and was not fixed at the time of writing this text.
But there is a workaround here:
mysql> alter table b add index(a_id);
Query OK, 8 rows affected (0.05 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql> select a.id as a_id, count(distinct b.id) as cnt from aa left join bb on a.id = b.a_id
-> where a.id = 1 group by a.id with rollup limit 100;
+------+-----+
| a_id | cnt |
+------+-----+
| 1 | 3 |
| NULL | 3 |
+------+-----+
2 rows in set (0.02 sec) As you can see the result is now correct.
Technique # 4: Try to change the SQL so that the result is correct. Use search engines to find workaround.
Cases with bugs in the MySQL code, although easy to eliminate, still occur much less frequently than bugs in the user's SQL code.
What to do to determine why the SELECT query does not work as expected?
Consider an example.
mysql> create table t1(f1 int);
Query OK, 0 rows affected (0.01 sec)
mysql> create table t2(f2 int);
Query OK, 0 rows affected (0.08 sec)
mysql> insert into t1 values(1);
Query OK, 1 row affected (0.01 sec)
mysql> select * from t1;
+------+
| f1 |
+------+
| 1 |
+------+
1 row in set (0.00 sec) But
mysql> select * from t1, t2; Empty set (0.00 sec) Why does a SELECT from two tables return an empty set, although the rows in table t1 exist?
EXPLAIN EXTENDED comes to the rescue:
mysql> \W
Show warnings enabled.
mysql> explain extended select * from t1, t2;
+----+-------------+-------+--------+---------------+------+---------+------+------+----------+---------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+--------+---------------+------+---------+------+------+----------+---------------------+
| 1 | SIMPLE | t1 | system | NULL | NULL | NULL | NULL | 1 | 100.00 | |
| 1 | SIMPLE | t2 | system | NULL | NULL | NULL | NULL | 0 | 0.00 | const row not found |
+----+-------------+-------+--------+---------------+------+---------+------+------+----------+---------------------+
2 rows in set, 1 warning (0.20 sec)
Note (Code 1003): select '1' AS `f1`,'0' AS `f2` from `test`.`t1` join `test`.`t2` As you can see, the query is converted to a query with JOIN, which is a synonym for INNER JOIN and which cannot return rows from table t1 if there are no corresponding rows in table t2. Since table t2 contains no records, the query does not return anything.
For more complex (long) queries, the algorithm is similar: run EXPLAIN EXTENDED, if no error is detected, then split or simplify the query, repeat.
EXPLAIN will also help if your query runs for a very long time, although it returns valid data. We will not dwell on this here, since this feature is well described in the official MySQL User Manual. See related chapters.
Method 5: Use EXPLAIN EXTENDED to understand how the SQL query is optimized (and therefore executed).
Comments
To leave a comment
Databases - Error detection methods in SQL application
Terms: Databases - Error detection methods in SQL application