Bug #40500 Synchronize failed due to erronous row_format being specified
Submitted: 4 Nov 2008 14:16 Modified: 23 Jan 2014 13:24
Reporter: Brian Zammit Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Workbench Severity:S2 (Serious)
Version:5.0.26, 5.2.35,5.2.39 OS:Windows (XP)
Assigned to: CPU Architecture:Any
Tags: error, fail, sync, synchronize

[4 Nov 2008 14:16] Brian Zammit
Description:
Attempting to synchronize databases to MySQL-server-community-5.1.24-0.rhel5, and "ROW_FORMAT=" keeps appearing, causing the synchronize to fail. Removing this option allows synchronize to continue normally.

---
Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 3

ALTER TABLE `x`.`ActiveNumber` CHANGE COLUMN `SendDiversion` `SendDiversion` TINYINT(3) UNSIGNED NOT NULL DEFAULT 0  
, DROP PRIMARY KEY 
, ADD PRIMARY KEY (`ActiveNumberID`) , ROW_FORMAT =  
Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

ALTER TABLE `y`.`TCB` CHANGE COLUMN `Scope` `Scope` TINYINT(3) UNSIGNED NOT NULL DEFAULT 0  , ROW_FORMAT =  
SQL script execution finished: statements: 6 succeeded, 2 failed
---

How to repeat:
Create one of the tables in MySQL. Create the same table in Workbench. Synchronize the tables. Watch syncronize fail due to an empty row_format= option being added.

CREATE TABLE `y` (                                                            
          `blahid` int(10) unsigned NOT NULL AUTO_INCREMENT,                             
          `blahnumber` varchar(40) DEFAULT NULL,                                      
          `blah2number` varchar(40) NOT NULL,                                             
          `blah` tinyint(1) unsigned DEFAULT NULL,                                      
          `blah2` tinyint(3) unsigned NOT NULL DEFAULT '0',                             
          PRIMARY KEY (`blahid`),                                                        
          UNIQUE KEY `ix_blah` (`blah2number`)                                             
        ) ENGINE=InnoDB AUTO_INCREMENT=93041 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC

Suggested fix:
Stop trying to update the database when there are no changes to be made. Why even attempt to change row_format=dynamic when this has not changed in workbench?
[17 Nov 2008 19:52] Brian Zammit
In some cases, this bug can break repliation. Since updates to the schema have to be pushed to all masters in a multi master setup, when the second and subsequent masters are updated the first master replication complains about making changes to the schema that already exist.
[1 Dec 2008 18:12] i guess
I got nearly the same problem ... changed my old database schema (MyISAM) to a new one with MySQL Workbench SE (InnoDB) ... now i get a synch error at every synchronization try .. he ALWAYS shows me all tables that are not synchron (cause it can't probably find the ROW_FORMAT matching in the diff tree ?).

I can't even disable the ROW_FORMAT setting in Workbench ... a fix would be nice, cause I never had a real overview for unsynchronized tables.
[11 Dec 2008 11:14] Sveta Smirnova
Thank you for the report.

I can not repeat described behavior with current version 5.0.28. Please upgrade, try with it and inform us if problem still exists in your environment.
[12 Jan 2009 0:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[11 Jan 2012 22:41] Matt Sawyer
This bug still exists as originally described. I'm using MySQL 5.5.15 with Workbench 5.2.35 on Mac OSX 10.7.2.
[12 Jan 2012 9:28] Valeriy Kravchuk
Please, check if the same problem still happens with a newer version, 5.2.37.
[13 Feb 2012 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[7 May 2012 19:29] Carl Downing
I'm still seeing this in versions as recent as 5.2.39
[8 May 2012 12:09] MySQL Verification Team
Verified on Windows 7 64-bits.
[16 May 2012 17:28] Carl Downing
Still in 5.2.40 OS X
[14 Sep 2012 17:57] Luca Longo
Same problem with workbench 5.2.43 (revision 9869) on OS X. I tried with Windows OS and got the same error.
[27 Nov 2012 18:55] Luke Deniston
Can confirm this bug is still present. Workbench 5.2.44, OSX 10.8.2. 

Comparing MySQL 5.5.25 to 5.5.14 if that matters.
[26 Jun 2013 21:15] Alfredo Kojima
Does this still happen with the latest version? If so, please attach a sample model and sample DB schema dump to repeat this. I created the sample table supplied in MySQL, reverse engineered it and then did a sync and it works fine in 5.2.47, no changes are reported.
[23 Jan 2014 13:24] Miguel Tadeu Mota
This is a duplicate of bug #70129