Order of columns with schema comparison

[Patrick8639]

Hello,
if I change the order of the columns in a table, make a schema comparison with a copy of the table and synchronize the schemas, the order of the columns are not changed in the destination table. So we can't really say that the schemas are the same.
If you don't want to make this automatically, at least provide it as an option to the comparison.
Patrick

[alexa]

We will review this suggestion when developing next versions of the product.

[alexa]

The functionality you requested is implemented in the latest builds of the following products:

http://www.devart.com/dbforge/mysql/stu ... nload.html
http://www.devart.com/dbforge/mysql/sch ... nload.html

[Patrick8639]

Thanks Alexa.

[Patrick8639]

Alexa,
it's working... but not always.
I just change the fields order for about 10 tables. When synchronizing between the distant server and my one server, I had to run synchronization multiple times to have the destination tables with the correct field order.
At the end, I still have a table that don't want to be synchronized!!!!
Patrick

[Patrick8639]

Alexa,
the source table schema is:

Code: Select all

CREATE TABLE asloca_intervention (
  CreationDT DATETIME NOT NULL DEFAULT '2013-01-01 00:00:00',
  CreationUser INT(11) DEFAULT 1,
  IsDeleted TINYINT(3) UNSIGNED NOT NULL DEFAULT 0,
  No INT(11) NOT NULL AUTO_INCREMENT,
  LastChangeDT DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
  LastChangeUser INT(11) NOT NULL DEFAULT 1,
  Version INT(11) NOT NULL DEFAULT 1,
  Address VARCHAR(100) DEFAULT NULL,
  AddressNo SMALLINT(5) UNSIGNED DEFAULT NULL,
  AddressNoExt VARCHAR(10) DEFAULT NULL,
  City VARCHAR(100) DEFAULT NULL,
  Date DATE NOT NULL,
  Gerance VARCHAR(50) DEFAULT NULL,
  Location TINYINT(3) UNSIGNED NOT NULL DEFAULT 0,
  NoConsultant INT(11) NOT NULL,
  NoMember INT(11) NOT NULL,
  Text TEXT DEFAULT NULL,
  Type TINYINT(3) UNSIGNED NOT NULL,
  ZipNo INT(11) NOT NULL DEFAULT 0,
  LastChangeWho INT(11) NOT NULL,
  PRIMARY KEY (No),
  INDEX ByConsultant (NoConsultant),
  INDEX ByNoMember (NoMember)
)
ENGINE = INNODB
CHARACTER SET utf8
COLLATE utf8_general_ci
COMMENT = 'Liste des interventions'

The destination table is:

Code: Select all

CREATE TABLE asloca_intervention (
  CreationDT DATETIME NOT NULL DEFAULT '2013-01-01 00:00:00',
  CreationUser INT(11) DEFAULT 1,
  IsDeleted TINYINT(3) UNSIGNED NOT NULL DEFAULT 0,
  No INT(11) NOT NULL AUTO_INCREMENT,
  LastChangeDT DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
  LastChangeUser INT(11) NOT NULL DEFAULT 1,
  Version INT(11) NOT NULL DEFAULT 1,
  Address VARCHAR(100) DEFAULT NULL,
  AddressNo SMALLINT(5) UNSIGNED DEFAULT NULL,
  AddressNoExt VARCHAR(10) DEFAULT NULL,
  City VARCHAR(100) DEFAULT NULL,
  Date DATE NOT NULL,
  Gerance VARCHAR(50) DEFAULT NULL,
  Text TEXT DEFAULT NULL,
  Type TINYINT(3) UNSIGNED NOT NULL,
  ZipNo INT(11) NOT NULL DEFAULT 0,
  LastChangeWho INT(11) NOT NULL,
  Location TINYINT(3) UNSIGNED NOT NULL DEFAULT 0,
  NoConsultant INT(11) NOT NULL,
  NoMember INT(11) NOT NULL,
  PRIMARY KEY (No),
  INDEX ByConsultant (NoConsultant),
  INDEX ByNoMember (NoMember)
)
ENGINE = INNODB
CHARACTER SET utf8
COLLATE utf8_general_ci
COMMENT = 'Liste des interventions'

The synchronization script is:

Code: Select all

USE asloca;


--
-- Alter table "asloca_intervention"
--
ALTER TABLE asloca_intervention
  CHANGE COLUMN Location Location TINYINT(3) UNSIGNED NOT NULL DEFAULT 0 AFTER Gerance,
  CHANGE COLUMN Text Text TEXT DEFAULT NULL AFTER NoMember;

It seems that something is missing in the script.
The next time I run the synchronization, there are still other changes and it seems to never ends...
Patrick

[alexa]

We are currently investigating this issue and will answer you as soon as possible.

[Patrick8639]

Alexa,
with version 6.0.399, the schema comparison tool says that the tables are equal, even if the order of the columns are not the same!
Patrick

[alexa]

Could you please provide us the versions of the compared MySQL servers and specify the options you have selected on the Options page of the New Schema Comparison wizard?

[Patrick8639]

Alexa,
it's OK now: the Force column order setting was not checked in this comparison.
Sorry.
Patrick

[alexa]

Thank you for updating us on this issue.

Please let us know if you have any questions.