MySQL Bugs: #54283: Unable to alter table

Bug #54283	Unable to alter table
Submitted:	7 Jun 2010 10:17	Modified:	15 Jun 2010 14:51
Reporter:	Jeremy Parker	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench	Severity:	S2 (Serious)
Version:	5.2.22 OSS RC 6091	OS:	Any (7 Home Premium, Ubuntu)
Assigned to:	Sergei Tkachenko	CPU Architecture:	Any
Tags:	ALTER TABLE

Description:
Unable to alter table in 5.2.22 OSS RC 6091. Previous versions worked fine.

How to repeat:
Connect to database (so far, any), expand database, expand tables, right click on table, click Alter Table, nothing happens.

Suggested fix:
None, other than manually altering table using SQL.

Hi Jeremy,
what server are you connecting to? i cant reproduce the problem on my win systems here.
thanks in advance

Hi,

Both systems run Linux (one of them is SuSE and the other is CentOS).

MySQL Server versions: 5.0.15-standard-log and 5.0.45 Source distribution

I was previously using earlier beta of WB and it worked fine. I did an upgrade on my laptop, and it started having this issue. I did a refresh install on one of my workstations here and it has the same issue.

I could try upgrading one of my other workstation, but at that point, I'd lose any functional WB.

Thanks for looking into this,
Jeremy

Ok, I did some more testing, and upgraded my other workstation which was using 5.2.16 OSS Beta 5249 (and Alter Table appeared to work all around).

After more testing, when connecting to the CentOS Server (5.0.45 Source distribution), the alter table works fine. When connecting to the SuSE server (5.0.15-standard-log), the alter table doesn't open when you click on the menu.

I can reproduce this one 3 systems here, one running Windows 7 Professional, and two running Windows 7 Home Premium.

If needed, I can provide access to the server, or perhaps setup a vmware to test this. Although I'm not sure if 5.0.15 is still supported? I haven't checked EOL.

Thanks,
Jeremy

I am able to repeat this on my Ubuntu systems when mysql server is on remote Ubuntu system.

I took world database as example database which you can download here:
http://dev.mysql.com/doc/index-other.html
(example databases)

First I tried localhost ....
it needs a while but then a popup window comes up where you will be able to alter a table.

I installed mysql server 5.1 on my other laptop and of course I first tried if connection is working (connect via CLI by using -hIPADDRESS).

Then I used workbench and opened connection to the remote database.
I tried to alter a table ... but after right click ... also after waiting 5 minutes ... nothing happened.

After closing Workbench and restarting it ... I connected to localhost server again and tried it again and also now here it is not working anymore.

I figured out that it is related to settings of sql-mode.

I usually use sql-mode = ansi_quotes.

I won't get a pop up window when this mode is set.

When there is no sql-mode set then I get a pop up window.

Query Editor made honor SQL mode taken from DBMS connection.

It's possible to workaround this problem by adding ANSI_QUOTES into SQL_MODE parameter in preferences form. For details see bug #46185.

fix confirmed in repository.

theres one problem left, which originates in how server handles procedure code and we can't completely prevent in workbench:
if a routine is created/altered using ansi_quotes and altered later using a connection without that setting, server still hands out procedure code containing ansi-quote-chars. when using that code (with unchanged quotes) to alter the routine, the alter operation will fail.

workaround deals with that problem in following manner: 
If parsing of the object's DDL fails (on opening of object-editor and initial retrieval of procedure code) WB tries to parse once more using alternate setting of ANSI_QUOTES flag and if it succeeds, user is notified with warning message, informing of inconsistency between the DDL statement and currently set value of SQL_MODE variable.

An entry has been added to the 5.2.23 changelog:

In the SQL Editor, selecting Alter Table from the context-sensitive menu had no effect.