MySQL Bugs: #56542: Using SQL Editor to update longtext fields is missing quotes

Bug #56542	Using SQL Editor to update longtext fields is missing quotes
Submitted:	3 Sep 2010 15:39	Modified:	1 Feb 2011 13:04
Reporter:	Pim VAN DER WAL	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Workbench: SQL Editor	Severity:	S3 (Non-critical)
Version:	5.2.27 CE, 5.2.29	OS:	Windows (Windows 7 Ent 64 bit)
Assigned to:		CPU Architecture:	Any
Tags:	editor, logtext, quotes

Description:
When using the SQL Editor to update a longtext field the resulting insert or update query is missing quotes for the longtext field. There is no problem for text fields, only for longtext.

How to repeat:
1. Create table

CREATE TABLE `test_longtext` (
  `a` int(11) NOT NULL auto_increment,
  `b` longtext,
  PRIMARY KEY  (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

2. Start editing the table with the "EDIT test_longtext;" statement

3. Enter "test" in column b and click "Apply changes to data"

4. Generated SQL "INSERT INTO `test`.`test_longtext` (`b`) VALUES (test);" is missing quotes for string test

Strange. 5.2.27 for Mac OS X generates inserts properly:

INSERT INTO `mydb`.`table1` (`id`, `val`) VALUES ('1', 'abc');
INSERT INTO `mydb`.`table1` (`id`, `val`) VALUES ('2', '\'fffff\'');

Same on 32-bit XP. The following correct code is generated:

INSERT INTO `test`.`test_longtext` (`b`) VALUES ('test');

Thank you for the report.

Verified as described. Please note one should try to insert double-quotes, not single quotes.

I've tried repeating this and it works as expected. I thought it was a duplicate of bug #55851, but longtext works even before fix, although LONG VARCHAR does not.

cannot repeat the problem either. both .27 and .28 generate this code when trying to reproduce the problem:
INSERT INTO `test`.`test_longtext` (`b`) VALUES ('test');

also quotes or double-quotes used inside text are escaped correctly.

I have just installed 5.2.28 CE and I get the same results: no quotes around the value of the longtext field. I just verified that this is not due to using the no-install version vs. the msi install version. I have also verified the behavior on 3 separate pc's. All are running 64 bit Win7 though so I also ran the test in XP mode and it still gave the same result. Not sure why no one else can repeat this behavior.

Pim, what database version are you using while experiencing that behavior?

We have most of our servers on 5.1.48 community edition and a couple on 5.0.51a community edition. Both types of servers give the same results. They're all running on 64 bit RHEL5. All are installed from MySQL RPM's.

Bug #57812 was marked as a duplicate of this one.

The problem can be raised using the "Edit Table Data" interface, modifying an existing value for a LONGTEXT field. The generated UPDATE query will miss quotes in Workbench 5.2.31 (rev 7110).

Exactly the same 5.2.31 version on Mac OS X, server 5.1.55, and the following code is generated for UPDATE:

UPDATE `test`.`test_longtext` SET `b`='test_test' WHERE `a`='1';

Looks like I am still missing some details on how to repeat.

Please consider uploading a screen-capture video of how to repeat this, it usually helps in such cases.

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