[GH-ISSUE #19] Wrong DELETE on non existent table #149

New issue

Open

opened 2026-03-07 20:41:34 +03:00 by kerem · 2 comments

kerem commented

2026-03-07 20:41:34 +03:00

Owner

Originally created by @amontalban on GitHub (Aug 11, 2016).
Original GitHub issue: https://github.com/DBDiff/DBDiff/issues/19

Hey guys,

I'm doing some tests with this great tool and I found this issue:

Server 1 (Source):

mysql> show tables like '%all_data_20160803132410%';
Empty set (0.02 sec)

mysql> select count(*) from all_data_20160803132410;
ERROR 1146 (42S02): Table 'ales68.all_data_20160803132410' doesn't exist

Server 2 (Destination):

mysql> show tables like '%all_data_20160803132410%';
+----------------------------------------------+
| Tables_in_ales68 (%all_data_20160803132410%) |
+----------------------------------------------+
| all_data_20160803132410                      |
+----------------------------------------------+
1 row in set (0.01 sec)

mysql> select count(*) from all_data_20160803132410;
+----------+
| count(*) |
+----------+
|        6 |
+----------+
1 row in set (0.01 sec)

I run the following command:

/usr/local/dbdiff/dbdiff --server1=amontalban@127.0.0.1:3306 --server2=amontalban@127.0.0.1:3306 --type=all server1.DB1:server2.DB2 --output=diff_test.sql

And it generates the following wrong output:

DELETE FROM `all_data_20160803132410` WHERE ;
DELETE FROM `all_data_20160803132410` WHERE ;
DELETE FROM `all_data_20160803132410` WHERE ;
DELETE FROM `all_data_20160803132410` WHERE ;
DELETE FROM `all_data_20160809105308` WHERE ;
DELETE FROM `all_data_20160803132410` WHERE ;
DELETE FROM `all_data_20160803132410` WHERE ;
DROP TABLE `all_data_20160803132410`;

As you can see it generates a line with the following code for EACH row on the table:

DELETE FROM `all_data_20160803132410` WHERE ;

Which is not valid and then it generates a DROP TABLE (Which should be the one that needs to be executed).

Unfortunately I can't share a dump of this data as it is confidential but have cases with tables with 44k rows and generates 44k lines of same DELETE.

Additionally I detected that when you do a simple UPDATE of a field DBDiff generates a DELETE line and an INSERT instead of doing an UPDATE, should I open a new issue for this?

Thank you very much for this great tool and let me know if more info is needed!

Originally created by @amontalban on GitHub (Aug 11, 2016). Original GitHub issue: https://github.com/DBDiff/DBDiff/issues/19 Hey guys, I'm doing some tests with this great tool and I found this issue: **Server 1 (Source):** ``` sql mysql> show tables like '%all_data_20160803132410%'; Empty set (0.02 sec) mysql> select count(*) from all_data_20160803132410; ERROR 1146 (42S02): Table 'ales68.all_data_20160803132410' doesn't exist ``` **Server 2 (Destination):** ``` sql mysql> show tables like '%all_data_20160803132410%'; +----------------------------------------------+ | Tables_in_ales68 (%all_data_20160803132410%) | +----------------------------------------------+ | all_data_20160803132410 | +----------------------------------------------+ 1 row in set (0.01 sec) mysql> select count(*) from all_data_20160803132410; +----------+ | count(*) | +----------+ | 6 | +----------+ 1 row in set (0.01 sec) ``` I run the following command: ``` sh /usr/local/dbdiff/dbdiff --server1=amontalban@127.0.0.1:3306 --server2=amontalban@127.0.0.1:3306 --type=all server1.DB1:server2.DB2 --output=diff_test.sql ``` And it generates the following **wrong** output: ``` sql DELETE FROM `all_data_20160803132410` WHERE ; DELETE FROM `all_data_20160803132410` WHERE ; DELETE FROM `all_data_20160803132410` WHERE ; DELETE FROM `all_data_20160803132410` WHERE ; DELETE FROM `all_data_20160809105308` WHERE ; DELETE FROM `all_data_20160803132410` WHERE ; DELETE FROM `all_data_20160803132410` WHERE ; DROP TABLE `all_data_20160803132410`; ``` As you can see it generates a line with the following code for EACH row on the table: ``` sql DELETE FROM `all_data_20160803132410` WHERE ; ``` Which is not valid and then it generates a `DROP TABLE` (Which should be the one that needs to be executed). Unfortunately I can't share a dump of this data as it is confidential but have cases with tables with 44k rows and generates 44k lines of same `DELETE`. Additionally I detected that when you do a simple `UPDATE` of a field DBDiff generates a `DELETE` line and an `INSERT` instead of doing an `UPDATE`, should I open a new issue for this? Thank you very much for this great tool and let me know if more info is needed!

kerem commented

2026-03-07 20:41:35 +03:00

Author

Owner

@jasdeepkhalsa commented on GitHub (Aug 25, 2016):

Hi @amontalban ! Thanks for the info!

Are you able to provide some fake data so we can re-create this scenario?

Also regarding your point:

Additionally I detected that when you do a simple UPDATE of a field DBDiff generates a DELETE line and an INSERT instead of doing an UPDATE, should I open a new issue for this?

Yes, please open a new issue for this as I'd like to explore how this could be done in a cross-database way as I believe MySQL only has limited UPDATE functionality compared to certain DBs like Postgres for example?

@jasdeepkhalsa commented on GitHub (Aug 25, 2016): Hi @amontalban ! Thanks for the info! Are you able to provide some fake data so we can re-create this scenario? Also regarding your point: ``` Additionally I detected that when you do a simple UPDATE of a field DBDiff generates a DELETE line and an INSERT instead of doing an UPDATE, should I open a new issue for this? ``` Yes, please open a new issue for this as I'd like to explore how this could be done in a cross-database way as I believe MySQL only has limited UPDATE functionality compared to certain DBs like Postgres for example?

kerem commented

2026-03-07 20:41:35 +03:00

Author

Owner

@tinwonda commented on GitHub (Mar 23, 2018):

+1 I had the same problem as above

And

DELETE FROM `all_data_20160809105308` WHERE ;

This command is invalid for mysql 5.6.22

@tinwonda commented on GitHub (Mar 23, 2018): +1 I had the same problem as above And ```sql DELETE FROM `all_data_20160809105308` WHERE ; ``` This command is invalid for mysql 5.6.22