Bug #49880 Unable to commit changes to TEXT fields when using "EDIT" mode
Submitted: 22 Dec 2009 16:20 Modified: 15 Jan 2010 18:41
Reporter: Edward Rudd Email Updates:
Status: Unsupported Impact on me:
None 
Category:MySQL Workbench Severity:S3 (Non-critical)
Version:5.2.11 OS:Linux (Fedora 12 i386)
Assigned to: CPU Architecture:Any

[22 Dec 2009 16:20] Edward Rudd
Description:
Edit editing the date of a table and changing the contents of a TEXT field, saving the data fails.  Th error looks as though Workbench has failed to quote the text.

Error Message: 
1 error(s) saving changes to table `permissions_main`.`preferences_blob`:

update `myDB`.`MyTable` set `value`=a:2:{i:0;s:4:"TEST";i:1;s:3:"UPS";} where `pref_id`='1'
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 ':2:{i:0;s:4:"TEST";i:1;s:3:"UPS";} where `pref_id`='1'' at line 1

Rollback complete

How to repeat:
CREATE TABLE MyTable (
  ID INT NOT NULL PRIMARY KEY,
  value LONGTEXT NOT NULL
);

Insert into MyTable VALUES (1,'A:1:{i:0;s:4:"TEST";}')

Then open the table in Workbench via

EDIT myDB.MyTable

Alter the value column to 'a:2:{i:0;s:4:"TEST";i:1;s:3:"UPS";}' and click the "Save"/Checkbox button.

IF I add single quotes around the text in the editor and click save it correctly updates (as I'm quoting the text for workbench)

Suggested fix:
Correctly quote the TEXT fields when updating.
[4 Jan 2010 12:55] Susanne Ebrecht
This works fine here.

Is there something else what you did and not let us know in the How To Repeat section?
[4 Jan 2010 15:10] Edward Rudd
That is very strange. As those are the exact steps I am doing.. Basically standard "basic" editing..

Well.. the only difference MIGHT be that I am doing

EDIT myDB.MyTable WHERE ID = 1

However, I can recreate this on my Mac OS X system as well running 5.2.11 as well. (I had to edit that table about 3 times yesterday and ran into it every time)

Now on the server side I am running MySQL 5.0.45 (RHEL/CentOS 5.3 Package)
I have not yet tested on other mysql servers. But I will do so.
[6 Jan 2010 17:23] Valeriy Kravchuk
Works OK here on Mac OS X. Try to check with a newer version of MySQL server, 5.0.89 or 5.1.42, please.
[6 Jan 2010 19:19] Edward Rudd
I just tested several DB servers following my exact directions in this bug report.

CentOS/RHEL 5.3 running MySQL 5.0.45  Bug Exists
CentOS 5.4 running MySQL 5.0.77  Bug Exists
Zend Server (Mac OS X) running MySQL 5.1.40  Bug does NOT exist.

So I take it you are using the "server side" escape function instead of the "client side" escape function which would account for different server versions acting differently.    Unfortunately as we are using RHEL/CentOS (we have both) I want to keep with the Redhat maintained releases for security updates and consistency.   Is there a check that can be added INTO workbench to "workaround" old buggy server versions?? as many others will be in the same situation I am in with RHEL.

If you can provide WHICH release of MySQL 5.0.x fixes the issue I will open an bug with RHEL on my account to have them incorporate a fix in a future release.
[14 Jan 2010 6:58] Susanne Ebrecht
Many thanks for all your feedback.

Unfortunately, I have to let you know that server 5.0 isn't supported anymore.

Server 5.0 has had the end of life last year.

http://www.mysql.com/about/legal/lifecycle/

MySQL Workbench is a new tool and only supports actual server which means server 5.1.

We won't make it supportable of older server versions.
[15 Jan 2010 18:41] Edward Rudd
I don't mean to "stir the pot", but why is there an option for "Operating System" of "Windows (MySQL 5.0 Installer Package) if MySQL 5.0 is not supported??
[28 Jan 2010 10:46] Sergei Tkachenko
See also related bug #50111