Description:
The documentation claims that mysqldbcompare ignores database name differences when comparing objects. This is true if the objects are two identical tables. However, if there is a difference in a table present in two compared databases, identical foreign key constraints (excepting the database name) are reported in the SQL output. For example, if I clone a database and compare all checks will pass. But if I add a column to a table in the cloned database, I'll see:
ALTER TABLE `db`.`Table`
DROP FOREIGN KEY Table_ibfk_1,
ADD CONSTRAINT Table_ibfk_1 FOREIGN KEY(old_column) REFERENCES `dbclone`.`Table2`(old_column),
ADD COLUMN new_column int(4) NULL AFTER old_column;
This is a problem for several reasons:
1) The ADD CONSTRAINT statement refers to the wrong database.
2) The ADD CONSTRAINT statement DROPs and ADDs the same key in a single query, resulting in a duplicate key error.
3) The ADD CONSTRAINT statement does not include ON UPDATE or ON DELETE conditions present in the original constraint.
4) THERE IS NO NEED TO MODIFY THIS FOREIGN KEY AT ALL. IT IS IDENTICAL BETWEEN THE DATABASES.
How to repeat:
Create a database with two tables that are connected by a foreign key. Clone this database and add a column to the table with the foreign key constraint. mysqldbcompare will include add and drop foreign key statements in a single ALTER TABLE query.
