Translation of "Chapter 3. Wrong data in database or what to do with problem DML query." of "Methods for searching errors in SQL application" just published.


This is short chapter which discuss single method of dealing with mysterious DML query.



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?



....


Rest of the chapter is here


Comments and translation corrections are always welcome.






Read More about [Chapter 3 of "Methods for searching errors in SQL application" has been published...