MySQL data comparison of two databases

I’m trying to compare two databases using dbForge MySQL. I managed to sync two database schemas with dbForge which went well. So then then next step I tried to do was sync the data of two databases. Here is a snapshot of the data that was compared. Notice the table ‘ratings’ needs 3 inserts from the source table to the target table:

dbforge mysql data comparison results

When I create the sql statements, this is what was created by the “Show Synchronization Wizard” button:

INSERT INTO ratings(id, rating) VALUES (62, 2);
INSERT INTO ratings(id, rating) VALUES (61, 4);
INSERT INTO ratings(id, rating) VALUES (63, 3);

an error occured with dbForge MySQL data synchronization.

Here is the snapshot to the ‘ratings’ table:

dbforge mysql ratings table schema.

And here is the text MySQL statement for creating the ‘ratings’ table:

CREATE TABLE skincar3_cosmetic.ratings(
  id INT(11) NOT NULL AUTO_INCREMENT,
  rating TINYINT(4) NOT NULL,
  div_id TEXT NOT NULL,
  ip TEXT NOT NULL,
  PRIMARY KEY (id)
)
ENGINE = MYISAM
AUTO_INCREMENT = 1
CHARACTER SET latin1
COLLATE latin1_swedish_ci;

So why does it give me this error when in the first step I synced the two database schemas using dbForge MySQL? I know the workaround is to simply allow null values for the field ‘ip’ and then the errors don’t occur but this is an extra step.

The other option is to change the MySQL statement as follows:

INSERT INTO ratings(id, rating, div_id, ip) VALUES (62, 2, '', '');
INSERT INTO ratings (id, rating, div_id, ip) VALUES (61, 4, '', '');
INSERT INTO ratings (id, rating, div_id, ip) VALUES (63, 3, '', '');
Share and Enjoy:
  • Digg
  • Sphinn
  • del.icio.us
  • Facebook
  • Mixx
  • Google Bookmarks
  • Share

You must be logged in to post a comment.