Bug #64741 Can not invoke the 'alter table' GUI in Workbench
Submitted: 23 Mar 2012 0:03 Modified: 14 Jan 2014 2:48
Reporter: Greg S Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Workbench: SQL Editor Severity:S3 (Non-critical)
Version:5.2.38, 5.2.40 OS:Windows (Windows 7 Home Premium SP1)
Assigned to: CPU Architecture:Any
Tags: ALTER TABLE, workbench

[23 Mar 2012 0:03] Greg S
Description:
I'm connecting to a remote MySQL server, version 5.0.77. 

I can connect fine, I can expand the table view in the object browser, run queries and edit table data. One thing I can not do though is right click a table and choose 'Alter Table...' to, for example, add a new column. 

Whenever I do this I get the following error: 

"Error Parsing DDL for 'schemaname'.'tablename'. There was an error parsing the DDL retrieved from the DBMS." 

I don't think this is a permissions-based problem since I can alter tables to my heart's content using the same login/connection details on another PC running the old Query Browser. Similarly I can alter tables in Workbench by manually creating and executing the relevent script to add columns. 

How to repeat:
Launch Workbench.
Right click tables under my db in the object browser.
Choose 'Create Table...'

Table name is set to 'new_table' by default.
Choose MyISAM for the Engine.
Double click under 'Column Name' to add an id column, labelled 'idnew_table' by default.
Click 'apply' to create the new table.
The 'Apply SQL script to Database' window appears.
Click 'apply' again.

At this stage I get an error popup that says the following:

"Error Parsing DDL for 'schemaname'.'new_table'
There was an error parsing the DDL retrieved from the DBMS."

Click 'OK'.
Click 'finish' in the underlying 'Apply SQL script to Database' window.

Regardless of the error message, the new table does appear in the table tree in the object browser.

I can right click the new table and choose 'edit table data'.
I can not right click the table and choose 'alter table...'

When I try this I get the same error message and the alter table GUI is not invoked.

Suggested fix:
I have no idea how to fix this. It may be that something's awry in my Workbench preferences settings.
[23 Mar 2012 8:43] Valeriy Kravchuk
I do not see any of the problem mentioned while working with local MySQL server 5.0.91 on Windows XP.
[23 Mar 2012 15:57] Greg S
I appreciate that you can't recreate my problem but I'm not the only one that seems to have this issue. 

Here's a link to my original post regarding this:
http://forums.mysql.com/read.php?152,505199,505199

Is there anything you suggest I try? Or would it help if I provided more information?

Thanks,
Greg.
[23 Mar 2012 16:00] Alfredo Kojima
When you get the dialog, click on View DDL and paste it here.
[23 Mar 2012 16:11] Greg S
Thanks for the reply - when I get the error dialogue my only options are 'ok' and 'cancel'. How do I view the appropriate DDL?
[25 Mar 2012 16:47] Alfredo Kojima
What's the SQL_MODE in the server?
[26 Mar 2012 10:36] Greg S
When I run this query:

SELECT @@SESSION.sql_mode;

I get the following response:

'MYSQL40,HIGH_NOT_PRECEDENCE'

I have tried the following options for "Default SQL_MODE" in the SQL Editor Preferences:

'MYSQL40,HIGH_NOT_PRECEDENCE'
'MYSQL40'
'HIGH_NOT_PRECEDENCE'

I have also tried leaving this field blank in the preferences.

I get the same error message (as quoted in the initial bug report post) for all of these settings in the preferences...
[1 May 2012 8:40] Greg S
Does anyone else have any ideas on how I can fix this problem?
[14 May 2012 12:48] Rafael Antonio Bedoy Torres
Hi Greg,

Please try with the newest version and let us know what you get.

Thanks in advance!
[23 May 2012 14:34] Greg S
Hi Rafael,

I've just upgraded to 5.2.40 CE, Revision 8790 but I'm still having the same problem. This is the 3rd revision of Workbench that I've installed but none seems to address my issue. Any other suggestions would be appreciated!

Thanks,
Greg.
[23 May 2012 21:59] Gladys Tang
I have the same problem since the update of 5.2.38....win 7 professional
[28 May 2012 12:25] Alfredo Kojima
What does a SHOW CREATE TABLE yourtable give when you run it from WB?
What about SHOW GRANTS?
[26 Jun 2012 12:33] Greg S
I think I've already pointed out that this is not a permissions based issue.
When I enter the following:

SHOW CREATE TABLE tbl_name

This is the response:

CREATE TABLE `tbl_name` (
   `col_01` int(10) unsigned NOT NULL auto_increment,
   `col_02` varchar(195) NOT NULL default '',
   `col_03` varchar(95) NOT NULL default '',
   PRIMARY KEY  (`col_01`)
 ) TYPE=MyISAM AUTO_INCREMENT=7

And when I enter this:

SHOW GRANTS

I get the following:

GRANT CREATE USER ON *.* TO 'db_user'@'%' IDENTIFIED BY PASSWORD 'xxxxxxxxxxxxxxxxx'

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE ROUTINE, ALTER ROUTINE ON `db_name`.* TO 'db_user'@'%' WITH GRANT OPTION
[1 Oct 2012 23:01] MySQL Verification Team
Please try version 5.2.44. Thanks.
[2 Nov 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".
[15 Oct 2013 22:40] Mike Bergeron
I may have figured out why this is happening.  I'm running Workbench 6.0.6 now with MySQL 5.0.45 on the server, but this was happening in v.5 Workbench, too.

I believe the error in Workbench derives from the fact that SHOW CREATE TABLE creates DDL output containing "TYPE=MYISAM", while "TYPE=engine_type" is deprecated in favor of "ENGINE=engine_type", so Workbench's attempt to parse and syntax check the DDL is failing with a warning.

I find no way to influence how the server reports the engine type in SHOW CREATE TABLE, so I'm unable to use "Alter Table" in the GUI to change my table.

-Mike
[15 Oct 2013 22:58] Greg S
Mike, that sounds very plausible. Thanks for spending the time to shed some light on this. I got sick of chasing workbench updates on the off chance that this problem would miraculously vanish. Hopefully the powers that be might now be able to fix this. Here's hoping... : )
[9 Jan 2014 13:52] Alfredo Kojima
Does removing 'MYSQL40' from SQL_MODE solve the problem?
[9 Jan 2014 15:13] Alfredo Kojima
The problem is with MYSQL40 in SQL_MODE
[9 Jan 2014 18:55] Mike Bergeron
YES! Thank you! The issue is clearly MYSQL40. When I query the global value of this system variable, it says MYSQL40,HIGH_NOT_PRECEDENCE. When I set it (session or global) to nothing, I get ENGINE=myisam on SHOW CREATE TABLE, and Workbench is pleased.

Assuming you don't have rights to change system variables on your server -- or there is some external reason to retain MYSQL40 compatibility -- you can make this change for only your running of Workbench by going into Manage Server Connections, and for each connection, under the Advanced tab, enter something innocuous (like HIGH_NOT_PRECEDENCE) in the textbox for SQL_MODE.  Bingo!  Your Workbench will be pleased also.

My thanks to Alfredo Kengi Kojima for leading me to this. It has obviously confused quite a few people for quite a long time.

Cheers!
[14 Jan 2014 2:48] Philip Olson
Fixed as of the upcoming MySQL Workbench 6.1.1, and here is the changelog entry:

Having "SQL_MODE" set to "MYSQL40" would not allow the "ALTER TABLE"
action to create new columns in MySQL Workbench.

Thank you for the bug report.