Russian Version

Chapter 3. Wrong data in database or what to do with problem DML query.

Problems with wrong data happens not only with SELECT queries like cases we discussed in chapter 2, but in cases of querires which modify data: DML queries.

Lets discuss example below

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)

mysql> delete from t1, t2 using t1, t2;
Query OK, 0 rows affected (0.00 sec)

User can expect what DELETE query will remove all rows from tables t1 and t2: "using t1, t2;" used and there is no WHERE clause. But as you see this is not true.

Please pay attentsion for a row which says "0 rows affected". This means what 0 rows were removed! But why?

Lets see which data we have in the tables:

mysql> select * from t1;
+------+
| f1   |
+------+
|    1 | 
+------+
1 row in set (0.00 sec)

What happened?

In case of SELECT we could use method described before and examine output of EXPLAIN EXTENDED. But MySQL doesn't support EXPLAIN DELETE/UPDATE/INSERT. This feature was planned, but not made yet. What can we do right now?

Best method to examine how delete/insert/update queries will be executed is convert them to corresponding select queries:

mysql> select * from t1, t2;
Empty set (0.00 sec)

We got very same query like we described in the last chapter. Therefore we can make same conclusion: in this case expression t1, t2 means t1 INNER JOIN t2. And as t2 has no data there is no filed which can be used for creating result table. Therefore no row was removed.

Same methodes can be used for problematic UPDATE querires.

Method #6: convert DML queries to corresponding SELECT to examine which rows will be modified.

Back Content Forward



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