DB Comparison/synchronization issue
DB Comparison/synchronization issue
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.
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.
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.
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.
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.
Schema Compare ignores marginal differences when comparing and synchronizing objects, but shows them in the code difference viewer.
I will give you an example of what I mean:
My original Project source DDL is like this:
the next one is what the synchronization generates for verification and shows as source object DDL in the source viewer:
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:
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.
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;
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
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
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.