MySQL – Using Mysqldiff.exe to Sync Database2 with Database1

data synchronizationMySQLschema

This question is related to another I had asked earlier here: Passing passwords more securely to mysql utilities on the command line.

I have recently been trying to use mysqldiff.exe, part of MySQL Utilities group of tools. My goal is to spot any schema differences between database1 and database2, then going by what it says "on the label", I should be able to obtain statements that can transform either of the databases to match the other.

Here's example output I obtained by comparing database1.clients and database2.clients.

# WARNING: Using a password on the command line interface can be insecure.
# server1 on localhost: ... connected.
# server2 on localhost: ... connected.
# Comparing database1.clients to database2.clients            [FAIL]
# Object definitions differ. (--changes-for=server1)
#

--- database1.clients
+++ database2.clients
@@ -61,4 +61,4 @@
   CONSTRAINT `clients_ibfk_6` FOREIGN KEY (`state_code`) REFERENCES `states` (`state_code`) ON DELETE
SET NULL ON UPDATE CASCADE,
   CONSTRAINT `clients_ibfk_7` FOREIGN KEY (`rel_id`) REFERENCES `religions` (`rel_id`) ON DELETE SET NULL ON
UPDATE CASCADE,
   CONSTRAINT `clients_ibfk_9` FOREIGN KEY (`nationality_id`) REFERENCES `country` (`Code`) ON DELETE SET NULL
 ON UPDATE CASCADE
-) ENGINE=InnoDB AUTO_INCREMENT=8041 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT
+) ENGINE=InnoDB AUTO_INCREMENT=691802 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT
Compare failed. One or more differences found.

Problem is, I was expecting the output to be usable SQL statements, so I can then run this on the relevant side of the compare. How can I use this tool to perform database schema synchronization as I described?

Best Answer

As it appears on the manual, use the option --difftype=sql in order to obtain the results as ALTER TABLEs.

You have an example on a recent MySQL Performance Blog post:

$ mysqldiff --force --difftype=sql \
--server1=root:msandbox@127.0.0.1:21489 \
--server2=root:msandbox@127.0.0.1:21490 \
employees:employees

Related Solutions

MySQL Replication question

On the slave, you can configure MySQL to skip certain errors by using the slave-skip-errors configuration item.

In the case above, you'd add the following to your my.cnf;

slave-skip-errors = 1452

But I'd be extremely wary about doing this, as the referential integrity is there for a reason, and without it, your slave could not be satisfactorily promoted to take the place of the master in the case of something going wrong with your master hardware...

http://dev.mysql.com/doc/refman/5.5/en/replication-options-slave.html#option_mysqld_slave-skip-errors

Mysql – “Cannot add or update a child row” when deleting a record

On the face of it, that does seem impossible.

The thing is, your error suggests it's not that you're trying to delete at all.

The message you're getting suggests you're trying to insert or update a row in the child table, not delete a row from the parent table. If the foreign key you posted was causing the problem relative to a delete, you should see this message, instead:

Cannot delete or update a parent row: a foreign key constraint fails (`test`.`bar`, CONSTRAINT `bar_ibfk_1` FOREIGN KEY (`foo_id`) REFERENCES `foo` (`id`))

It's also possible you have some BEFORE DELETE trigger magic on survey_main that's doing something unexpected.

Right after this error occurs, try this:

SHOW ENGINE INNODB STATUS;

The LATEST FOREIGN KEY ERROR section should give you something more to go on. Failing that, you could enable the general log, which will show queries executed by triggers and other stored programs, as well as the queries you're directly executing, to shed light on what might be going on behind the scenes.

Update (#1) Things are definitely not as they seem and the full table definitions are going to be pretty critical, here.

Also, the version of MySQL you're using may also be relevant, so please mention it.

With nothing more to go on at the moment, I'm speculating that you have invalid data in the survey_id column of the survey_answers table. To test that theory:

SELECT *
  FROM survey_answers sa
  LEFT JOIN survey_main sm ON sm.id = sa.survey_id
 WHERE sm.id IS NULL;

If I understand your schema correctly, then this query will return zero rows if I am wrong. :) If you get rows returned, then those rows have survey_answers records that contain an survey_id value that doesn't exist in the id column of survey_main.

Best Answer

Related Solutions

MySQL Replication question

Mysql – “Cannot add or update a child row” when deleting a record

Related Question