MySQL Bugs: #64741: Can not invoke the 'alter table' GUI in Workbench

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

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.

I do not see any of the problem mentioned while working with local MySQL server 5.0.91 on Windows XP.

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.

When you get the dialog, click on View DDL and paste it here.

Thanks for the reply - when I get the error dialogue my only options are 'ok' and 'cancel'. How do I view the appropriate DDL?

What's the SQL_MODE in the server?

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...

Does anyone else have any ideas on how I can fix this problem?

Hi Greg,

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

Thanks in advance!

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.

I have the same problem since the update of 5.2.38....win 7 professional

What does a SHOW CREATE TABLE yourtable give when you run it from WB?
What about SHOW GRANTS?

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

Please try version 5.2.44. Thanks.

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".

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

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... : )

Does removing 'MYSQL40' from SQL_MODE solve the problem?

The problem is with MYSQL40 in SQL_MODE

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!

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.