Bug: Structure Synchr. Disregards Field Order!

[motin]

When using the structure synzhronization, sqlyog disregards if the fields/columns are in different orders!

This had me up for a strange experience where I first synchronied the structure, but then tried synchronizing the data and got:

Column NAME mismatch for '`members`' table

Column NAME mismatch for '`pics`' table

The difference is shown below between the tow, and remember, this is AFTER structure synchronization:

pics CREATE TABLE `pics` (

`picID` int(11) NOT NULL auto_increment,

`type` varchar(50) default NULL,

`linked_id` int(11) default NULL,

`verified` tinyint(11) default NULL,

`width` int(11) NOT NULL default '0',

`height` int(11) NOT NULL default '0',

`thumb` varchar(100) NOT NULL default '0',

`path_original` varchar(255) NOT NULL default '',

`path_thumb` varchar(100) NOT NULL default '',

`filetype` varchar(50) default NULL,

PRIMARY KEY (`picID`),

KEY `band` (`linked_id`,`type`),

KEY `ver` (`verified`,`linked_id`,`type`)

) TYPE=MyISAM

pics CREATE TABLE `pics` (

`picID` int(11) NOT NULL auto_increment,

`type` varchar(50) default NULL,

`linked_id` int(11) default NULL,

`verified` tinyint(11) default NULL,

`width` int(11) NOT NULL default '0',

`height` int(11) NOT NULL default '0',

`thumb` varchar(100) NOT NULL default '0',

`path_thumb` varchar(100) NOT NULL default '',

`path_original` varchar(255) NOT NULL default '',

`filetype` varchar(50) default NULL,

PRIMARY KEY (`picID`),

KEY `band` (`linked_id`,`type`),

KEY `ver` (`verified`,`linked_id`,`type`)

) TYPE=MyISAM

path_thumb and path_original has different places.

I am using v4.1.

[motin]

For help fixing this, this is how to alter field-orders:

To reorder the columns of your table, you can use the ALTER TABLE syntax, since as of MySQL 4.0.1, the keywords FIRST and AFTER can be used in a CHANGE or MODIFY command.

Example:

mysql> describe example_table;

Code:

+————-+————+——+—–+———+—————-+
| Field       | Type       | Null | Key | Default | Extra          |
+————-+————+——+—–+———+—————-+
| id          | int(11)    |      | PRI | NULL    | auto_increment |
| columnA     | bigint(20) |      |     | 0       |                |
| columnB     | text       |      |     | 0       |                |
+————-+————+——+—–+———+—————-+

3 rows in set (0.00 sec)

mysql> ALTER TABLE example_table CHANGE COLUMN columnB columnB TEXT NOT NULL AFTER id;

Query OK, 3 rows affected (0.01 sec)

Records: 3 Duplicates: 0 Warnings: 0

mysql> describe example_table;

Code:

+————-+————+——+—–+———+—————-+
| Field       | Type       | Null | Key | Default | Extra          |
+————-+————+——+—–+———+—————-+
| id          | int(11)    |      | PRI | NULL    | auto_increment |
| columnB     | text       |      |     | 0       |                |
| columnA     | bigint(20) |      |     | 0       |                |
+————-+————+——+—–+———+—————-+

[Ritesh]

http://www.webyog.com/forums/index.php?sho…52&st=0&p=7625&

[Author]

Posts