Bug #68642 Create EER Model From Existing Database not working anymore
Submitted: 11 Mar 2013 23:44 Modified: 6 Jan 2014 22:59
Reporter: Dimitriy A Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Workbench: Modeling Severity:S2 (Serious)
Version:5.2.47 CE OS:Windows (Win7 Ent sp1 32-bit)
Assigned to: CPU Architecture:Any
Tags: EER model, model

[11 Mar 2013 23:44] Dimitriy A
Description:
Create EER Model From Existing Database is not working in the new versions of MySQL Workbench. I do not recall, what the last version of Workbench was, where it worked. It failed for me in 5.2.45 CE and I tried to upgrade to the latest version to see if that would resolve the issue.

How to repeat:
Open WorkBench.
Click on Create EER Model From Existing Database.
Select an existing connection from Stored Connection drop down box.
Click Next twice.
Select "mysql" schema and click Next twice.
Click Execute.
Message log shows the following errors:
ERROR: Line 1: syntax error, unexpected TEXT_STRING. Statement skipped. 
ERROR: Line 4: syntax error, unexpected TEXT_STRING. Statement skipped. 
ERROR: Line 16: syntax error, unexpected TEXT_STRING. Statement skipped. 
ERROR: Line 42: syntax error, unexpected TEXT_STRING. Statement skipped. 
ERROR: Line 51: syntax error, unexpected TEXT_STRING. Statement skipped. 
ERROR: Line 61: syntax error, unexpected TEXT_STRING. Statement skipped. 
ERROR: Line 69: syntax error, unexpected TEXT_STRING. Statement skipped. 
ERROR: Line 76: syntax error, unexpected TEXT_STRING. Statement skipped. 
ERROR: Line 88: syntax error, unexpected TEXT_STRING. Statement skipped. 
ERROR: Line 112: syntax error, unexpected TEXT_STRING. Statement skipped. 
ERROR: Line 133: syntax error, unexpected TEXT_STRING. Statement skipped. 
ERROR: Line 147: syntax error, unexpected TEXT_STRING. Statement skipped. 
ERROR: Line 161: syntax error, unexpected TEXT_STRING. Statement skipped. 
ERROR: Line 168: syntax error, unexpected TEXT_STRING. Statement skipped. 
ERROR: Line 175: syntax error, unexpected TEXT_STRING. Statement skipped. 
ERROR: Line 182: syntax error, unexpected TEXT_STRING. Statement skipped. 
ERROR: Line 190: syntax error, unexpected TEXT_STRING. Statement skipped. 
ERROR: Line 200: syntax error, unexpected TEXT_STRING. Statement skipped. 
ERROR: Line 242: syntax error, unexpected TEXT_STRING. Statement skipped.

Suggested fix:
Fix creation of EER diagrams from existing databases.
[12 Mar 2013 2:32] MySQL Verification Team
I couldn't repeat on MacBook Pro Mountain Lion, Windows 2003 Server and Windows 7 Ultimate 64-bits.
[26 Apr 2013 14:29] Nicholas Straguzzi
I'm having the same issue on both Windows and Apple (I tried it on both platforms just in case.)  We have a valid database but Workbench is choking on the SQL.  The two platforms give different log messages, though the SQL line numbers line up exactly.  Here's the first few lines of the Apple log, if it helps:

ERROR: Line 1: SQL syntax error near '"name_of_database_redacted" /*!40100 DEFAULT CHARACTER SET latin1 */'. Statement skipped.
ERROR: Line 4: SQL syntax error near '"DVD" (
  "DV_ID" varchar(7) DEFAULT NULL,
  "TITLE" varchar(99) DEFAULT NULL,
 '. Statement skipped.
ERROR: Line 59: SQL syntax error near '"DVD_DESC" (
  "DV_ID" varchar(10) DEFAULT NULL,
  "D_ID" varchar(7) DEFAULT NUL'. Statement skipped.
ERROR: Line 68: SQL syntax error near '"NAMELINK" (
  "P_ID_VIDEO" varchar(7) DEFAULT NULL,
  "P_ID_POP" varchar(10) DE'. Statement skipped.

Really need a patch or fix for this ASAP, as I have a bunch of big DBs to RE.  Thanks - Nick
[26 Apr 2013 14:59] Nicholas Straguzzi
One more piece of hopefully useful information:  the MySQL database I'm trying to RE is running on 5.1.63-community.
[26 Apr 2013 17:37] Dimitriy A
Maybe someone could test on 32-bit platform?
[26 Apr 2013 18:03] Nicholas Straguzzi
Can't help with a 32-bit test.  However, I did do the following:

(1) Downgraded the Apple machine to use Workbench 5.1.18a

(2) Tried RE on the same schema.

It worked successfully.  So, the problem appears to have something to do with RE'ing a 5.1 schema in 5.2.

Regards,
Nick
[27 May 2013 5:53] Roman Syroeshko
I experience the same issue too.

Windows 7 Home Basic 64-bit.
MySQL Workbench 5.2.47 CE.
[27 May 2013 6:52] Roman Syroeshko
Each time when I try to reverse engineer "mysql" schema in the way Dimitriy mentioned, Workbench puts in its log the following text.

10:49:19 [INF][            grt]: Fetching schema list.	
10:49:19 [INF][            grt]: OK	
10:49:24 [INF][            grt]: Fetching table list.	
10:49:24 [INF][            grt]:     28 items from mysql	
10:49:24 [INF][            grt]: OK	
10:49:24 [INF][            grt]: Fetching view list.	
10:49:24 [INF][            grt]:     0 items from mysql	
10:49:24 [INF][            grt]: OK	
10:49:24 [INF][            grt]: Fetching routine list.	
10:49:24 [INF][            grt]:     0 items from mysql	
10:49:24 [INF][            grt]: OK	
10:49:24 [INF][            grt]: Fetching trigger list.	
10:49:24 [INF][            grt]:     0 items from mysql	
10:49:24 [INF][            grt]: OK	
10:49:28 [ERR][            grt]: Line 1: syntax error, unexpected TEXT_STRING. Statement skipped.	
10:49:28 [ERR][            grt]: Line 4: syntax error, unexpected TEXT_STRING. Statement skipped.	
10:49:28 [ERR][            grt]: Line 16: syntax error, unexpected TEXT_STRING. Statement skipped.	
10:49:28 [ERR][            grt]: Line 44: syntax error, unexpected TEXT_STRING. Statement skipped.	
10:49:28 [ERR][            grt]: Line 71: syntax error, unexpected TEXT_STRING. Statement skipped.	
10:49:28 [ERR][            grt]: Line 80: syntax error, unexpected TEXT_STRING. Statement skipped.	
10:49:28 [ERR][            grt]: Line 90: syntax error, unexpected TEXT_STRING. Statement skipped.	
10:49:28 [ERR][            grt]: Line 100: syntax error, unexpected TEXT_STRING. Statement skipped.	
10:49:28 [ERR][            grt]: Line 108: syntax error, unexpected TEXT_STRING. Statement skipped.	
10:49:28 [ERR][            grt]: Line 115: syntax error, unexpected TEXT_STRING. Statement skipped.	
10:49:28 [ERR][            grt]: Line 127: syntax error, unexpected TEXT_STRING. Statement skipped.	
10:49:28 [ERR][            grt]: Line 140: syntax error, unexpected TEXT_STRING. Statement skipped.	
10:49:28 [ERR][            grt]: Line 151: syntax error, unexpected TEXT_STRING. Statement skipped.	
10:49:28 [ERR][            grt]: Line 166: syntax error, unexpected TEXT_STRING. Statement skipped.	
10:49:28 [ERR][            grt]: Line 173: syntax error, unexpected TEXT_STRING. Statement skipped.	
10:49:28 [ERR][            grt]: Line 198: syntax error, unexpected TEXT_STRING. Statement skipped.	
10:49:28 [ERR][            grt]: Line 212: syntax error, unexpected TEXT_STRING. Statement skipped.	
10:49:28 [ERR][            grt]: Line 225: syntax error, unexpected TEXT_STRING. Statement skipped.	
10:49:28 [ERR][            grt]: Line 239: syntax error, unexpected TEXT_STRING. Statement skipped.	
10:49:28 [ERR][            grt]: Line 267: syntax error, unexpected TEXT_STRING. Statement skipped.	
10:49:28 [ERR][            grt]: Line 280: syntax error, unexpected TEXT_STRING. Statement skipped.	
10:49:28 [ERR][            grt]: Line 297: syntax error, unexpected TEXT_STRING. Statement skipped.	
10:49:28 [ERR][            grt]: Line 313: syntax error, unexpected TEXT_STRING. Statement skipped.	
10:49:28 [ERR][            grt]: Line 327: syntax error, unexpected TEXT_STRING. Statement skipped.	
10:49:28 [ERR][            grt]: Line 334: syntax error, unexpected TEXT_STRING. Statement skipped.	
10:49:28 [ERR][            grt]: Line 341: syntax error, unexpected TEXT_STRING. Statement skipped.	
10:49:28 [ERR][            grt]: Line 348: syntax error, unexpected TEXT_STRING. Statement skipped.	
10:49:28 [ERR][            grt]: Line 356: syntax error, unexpected TEXT_STRING. Statement skipped.	
10:49:28 [ERR][            grt]: Line 366: syntax error, unexpected TEXT_STRING. Statement skipped.	
10:49:29 [INF][ Canvas backend]: Found OpenGL version for this view: 3.1.0 - Build 9.17.10.2932
[27 May 2013 7:25] Roman Syroeshko
Workbench log-file with DEBUG3 level turned on.

Attachment: wb.log.debug3 (application/octet-stream, text), 9.50 KiB.

[18 Aug 2013 23:36] John Bentley
This bug occurs if either of the following options are set:

* ansi: true
* sql-mode: "[Other options], ANSI_QUOTES".

Removing both of those options not only fixes the current bug, when using "Create EER Model from Database" or "Database > Reverse Engineer ..." but fixes problems when using "Database > Synchronize Model ..."

MY SYSTEM

* MySQL Workbench Community (GPL) for Windows version 6.0.6  revision 11184.
* MySQL Community Server, 5.6.13
* OS: Microsoft 8 Pro, 64-bit

STEPS TO REPRODUCE THE "Create EER Model from Database" BUG

* In MySQL Workbench open a connection. You should see a window with a "localhost (defaultdatabase)".
* Management > Options File > General [Tab] > SQL [Options Group] >

Either:
ansi: tick

or

sql-mode: ticked and "STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION, ANSI_QUOTES" [Note "ANSI_QUOTES" is added to the default options]

* Click Apply!
* Restart your SQL server to ensure option changes take effect. E.g. MySQL Notifier > Right Click > MySQL56 - Running > Restart.

* Click the "Create Schema" button. Create schema with "test01".
* Create a "person" table with at least one varchar column (I'm presuming a text type column is causing the bug under the ansi options). I use the interface to generate something like the following:
CREATE TABLE `test01`.`person` (
  `PersonID` INT NOT NULL AUTO_INCREMENT,
  `PersonName` VARCHAR(45) NULL,
  PRIMARY KEY (`PersonID`));

* From the home tab, "Models", click on the > button to execute "Create ER Model from Database" (This seems just to fire the "Database > Reverse Engineer" command that is accessible from the "localhost (defaultdatabase)" tab).
* Click next a few times (assuming you've previously setup the right connection information) until the "Select Schemas to Reverse Engineer". Select "test01".
* Click next, then next again. At "Select Objects to Reverse Engineer" leave your table Selected. Click "Execute".
* On the "Reverse Engineering Progress" screen I get:
"Operation has completed with errors. Please see logs for details."

Message Log:
ERROR: Line 1: syntax error, unexpected TEXT_STRING. Statement skipped.
ERROR: Line 4: syntax error, unexpected TEXT_STRING. Statement skipped.

STEPS TO MAKE THE "Create EER Model from Database" BUG DISAPPEAR

* In MySQL Workbench open a connection. You should see a window with a "localhost (defaultdatabase)".
* Management > Options File > General [Tab] > SQL [Options Group] >

Ensure both:
ansi: unticked

and

sql-mode: ticked and nowhere contains "ANSI_QUOTES"

* Click Apply!
* Restart your SQL server to ensure option changes take effect. E.g. MySQL Notifier > Right Click > MySQL56 - Running > Restart.
* From the home tab, "Models", click on the > button to execute "Create ER Model from Database" (This seems just to fire the "Database > Reverse Engineer" command that is accessible from the "localhost (defaultdatabase)" tab).
* Click next a few times (assuming you've previously setup the right connection information) until the "Select Schemas to Reverse Engineer". Select "test01".
* Click next, then next again. At "Select Objects to Reverse Engineer" leave your table Selected. Click "Execute".
* On the "Reverse Engineering Progress" screen I get:

"Operation Completed Successfully".

* Click Next, Click Finish. You'll see your table on the EER Diagram.
[4 Mar 2014 17:44] Allan Rofer
I was beset with the same problem.
Seems like the wizard is not very wizard-y when it comes to sql_mode settings.
sql_mode = ansi breaks the reverse engineering.
Thanks for the tip!!