DB Comparison/synchronization issue

MarkBrock · Post by **MarkBrock** » Tue 11 Aug 2009 11:31

HI,

in the new version of dbForge Studio 3.50.310, there seems to be a bug in the comparison/synchronisation feature.

Comparing the database with the project or two db's, it shows many differences in the table creation DDL statements which shows that table option AVG_ROW_LENGTH = x as different. The value of this option is not relevant for any comparison, because it is a calculated value from the database (depends on the stored data) and in most cases different. It would be a good idea to ignore this value/option like it is done with the AUTO_INCREMENT = x option.

Alexz · Post by **Alexz** » Tue 11 Aug 2009 11:52

We fixed this problem in dbForge Studio for MySQL v3.60. It will be available in a fews days for download. We'll inform you.

MarkBrock · Post by **MarkBrock** » Tue 11 Aug 2009 11:55

Thx very much for your quick support

Alexz · Post by **Alexz** » Wed 12 Aug 2009 14:46

The new release of dbForge Studio for MySQL v 3.60 is available for download.

MarkBrock · Post by **MarkBrock** » Thu 13 Aug 2009 11:27

thx have installed the new version.

in some cases it is still shown as different, in others it is shown as equal now!

But in any cases it is shown in the code diff viewer as different.

MySQL says, that this option (AVG_ROW_LENGTH) should only be set for large tables with different record length. But I'm not sure, I think it depends on the used db engine, if it is set automatically by the db or not. I never set this value manually when developing a db project and in all of my db projects this value is set automatically (in most cases I only use InnoDB). And also if you have a developer db and a production db, this value is always different.

I prefer this solution:
if it is not explicitly set in the table definition in the project it should be completely ignored on synchronization/comparison (also in the code diff viewer, this option should completely disapear in the code, same as AUTO_INCREMENT)
It is ok to show/set this value with the Table Editor when editing or maintenance the real db table.

Alexz · Post by **Alexz** » Thu 13 Aug 2009 14:55

What do you mean by saying "in some cases it is still shown as different, in others it is shown as equal now!"? Do you want to say that it has differences in the code viewer or it has "Different" status in the grid?

Schema Compare ignores marginal differences when comparing and synchronizing objects, but shows them in the code difference viewer.

MarkBrock · Post by **MarkBrock** » Fri 14 Aug 2009 11:08

in the code viewer it is always shown as different and in the grid (top window) some tables are shown as status "equal" (in the code of this tables it is still shown as different) and some are shown as state "different" and different in the code viewer.

Duke · Post by **Duke** » Mon 17 Aug 2009 07:43

It was intentionally made so that the text viewer shows original object DDL even if objects considered equal. If this behaviour is puzzling we consider changing it.

MarkBrock · Post by **MarkBrock** » Mon 17 Aug 2009 10:50

I will give you an example of what I mean:

My original Project source DDL is like this:

Code: Select all

CREATE TABLE IF NOT EXISTS auth_application (
  APPID INT(11) AUTO_INCREMENT COMMENT 'application ID',
  TS_CREATE DATETIME NOT NULL COMMENT 'create timestamp',
  TS_UPDATE DATETIME DEFAULT NULL COMMENT 'update timestamp',
  NAME VARCHAR(50) NOT NULL DEFAULT '' COMMENT 'application name',
  DESCRIPTION VARCHAR(255) DEFAULT NULL COMMENT 'application description',
  PRIMARY KEY (APPID),
  UNIQUE INDEX idx_auth_application_name USING BTREE (NAME)
)
ENGINE = INNODB
AUTO_INCREMENT = 1
CHARACTER SET utf8 COLLATE utf8_general_ci;

the next one is what the synchronization generates for verification and shows as source object DDL in the source viewer:

Code: Select all

CREATE TABLE auth_application (
  APPID INT(11) NOT NULL AUTO_INCREMENT COMMENT 'application ID',
  TS_CREATE DATETIME NOT NULL COMMENT 'create timestamp',
  TS_UPDATE DATETIME DEFAULT NULL COMMENT 'update timestamp',
  NAME VARCHAR(50) NOT NULL COMMENT 'application name',
  DESCRIPTION VARCHAR(255) DEFAULT NULL COMMENT 'application description',
  PRIMARY KEY (APPID),
  UNIQUE INDEX idx_auth_application_name USING BTREE (NAME)
)
ENGINE = INNODB
CHARACTER SET utf8
COLLATE utf8_general_ci

It correctly removes the AUTO_INCREMENT option for verification

And this is what is generated from the target DB and shown in the target source viewer:

Code: Select all

CREATE TABLE auth_application (
  APPID INT(11) NOT NULL AUTO_INCREMENT COMMENT 'application ID',
  TS_CREATE DATETIME NOT NULL COMMENT 'create timestamp',
  TS_UPDATE DATETIME DEFAULT NULL COMMENT 'update timestamp',
  NAME VARCHAR(50) NOT NULL COMMENT 'application name',
  DESCRIPTION VARCHAR(255) DEFAULT NULL COMMENT 'application description',
  PRIMARY KEY (APPID),
  UNIQUE INDEX idx_auth_application_name USING BTREE (NAME)
)
ENGINE = INNODB
AVG_ROW_LENGTH = 8192
CHARACTER SET utf8
COLLATE utf8_general_ci

Also correctly removes the AUTO_INCREMENT option but leaves the AVG_ROW_LENGTH option, this also have to be removed.

In the top window some of my tables shown as status "equal" but the source viewer marks the different part with the AVG_ROW_LENGTH.
Some other tables shown as status "different" and also marks the different parts with the AVG_ROW_LENGTH in the source viewer.

I think the AVG_ROW_LENGTH option must be handled in the same way like the AUTO_INCREMENT option.

Duke · Post by **Duke** » Thu 20 Aug 2009 07:48

We'll fix this problem in the next build.

MarkBrock · Post by **MarkBrock** » Thu 20 Aug 2009 07:51