MySQL Bugs: #50111: Longtext fields in SQL Editor are not correctly updated owing to no quotes

Bug #50111	Longtext fields in SQL Editor are not correctly updated owing to no quotes
Submitted:	6 Jan 2010 19:04	Modified:	5 Feb 2010 15:23
Reporter:	Martin Eve	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench	Severity:	S3 (Non-critical)
Version:	5.2.11 Beta 3	OS:	Linux (Arch)
Assigned to:	Sergei Tkachenko	CPU Architecture:	Any
Tags:	longtext, workbench

Description:
When editing a table by using the SQL Editor -> double click on the desired table.

Add a new row with a longtext field that spans multiple lines.

Expected result: Entry is aded to the database

Actual result: Attempted insert produces an error. This appears to be because the field is not being placed in single quotes. The log file reads:

1 error(s) saving changes to table `blogs`.`discogLyrics`:

insert into `blogs`.`discogLyrics` (`id`, `trackName`, `lyric`, `Comments`) values ('', 'Bubble', <p>Quelle rumble,</br>
sounds like trouble</br>
sounds like hell</br>
sounds like trouble</br>
sounds like hell</br>
there is a trouble</br></p>

<p>sounds like hell quelle rumble</br>
sounds like hell quelle rumble</br>
rumble round dig your dreams</br>
goes on and round unstoppable</br>
goes on and round unstoppable</br>
goes on and round and round and round</p>

<p>sounds like trouble</br>
quelle rumble</br>
sounds like hell</br>
quelle rumble</br>
sounds like hell</br>
sounds like trouble quelle rumble bust in a bubble</br>
sounds like trouble bust in a bubble like a chime of seven bells.</p, Partially verified by Mike Tournier)
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 '<p>Quelle rumble,</br>
sounds like trouble</br>
sounds like hell</br>
sounds lik' at line 1

Rollback complete

How to repeat:
When editing a table by using the SQL Editor -> double click on the desired table.

Add a new row with a longtext field that spans multiple lines.

Suggested fix:
Encapsulate the longtext field in quotation marks or sanitize the string.

Sergei, can we detect the column type and warn the user about the missing " before executing the query?

Also, I think it would be a good idea to display a confirmation dialog that shows the SQL we are going to execute for the INSERTs/UPDATEs (like we have the Apply Changes dialog for the live Editors)

In reference to Michael's comment; shouldn't these quotes be added *automatically* for longtext? (it is, after all, a field into which only a string of chars can validly be entered)

Fix typo

Just to answer your question:

The answer is no.

insert into `blogs`.`discogLyrics` (`id`, `trackName`, `lyric`, `Comments`) values ('',
'Bubble', ..., ...);

id is something string related like char, varchar, binary, varbinary, text, blob and same with trackName.

You used quotes here already.

The other two columns also just string values.

SQL says that string values always need to get quoted.

In other data types sometimes the quotes occur a specific bahviour. A decimal with quotes behave different then without quotes. Same for some date or time values.

Also SQL standard says here for these data types that you need to quote on globalisation.

Additionally, data type of the column is not known when you write the insert statement.

Imagine your fridge is empty ... you write a list what you need to buy.

This is similar to writing the statement:

INSERT INTO fridge VALUES(apple, butter, schnitzel);

With this list you will walk to the next supermarket or you will open the online shop.

Your statement know will be transfered to the server.

In the shop itself you will look if you can get apples. Apples are fruits and so you look for the fruit booth.

The server is the shop. And fruits, cow products and meat are the data types.

So server looks if column is data type fruit.

Finally you figure out there is no fruit booth and so you aren't able to buy apples.

Same happens on server ... if column expect meat instead of fruits then you just will get a message that meat is not fruit.

Consider, you were a vegetarien and somebody just would buy schnitzel for you instead of brocoli.

Susanne: if that was a reply to my comment, then I'm afraid that the context of my question was mis-interpreted. I intended it to mean: shouldn't *workbench* automatically add the quotes.

I am aware of the sql spec and the importance of type restraints on input, I was asking if workbench should not do the detection of the destination column type and put quotes around the user's input (which is specified visually, via a table of input.)

We reconsidered processing of field types like TINYTEXT/TEXT/MEDIUMTEXT/LONGTEXT. From now on fields of these types are represented as BLOB values to avoid UI performance hit caused by potentially large values. Fields of these types now  can only be updated using SQL.
This change affects all platforms.

editing BLOB/TEXT columns is disabled using the grid .
will be included in 5.2.13

An entry has been added to the 5.2.13 changelog:

In the SQL Editor, if a LONGTEXT field was being edited in the table data live editor, when the changes were applied an error was generated.

MySQL Workbench has been changed so that fields with types such as TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT have editing in the table data editor disabled. They are now shown as BLOB values. These fields can now only be changed with SQL code. This change was made to prevent potentially large values for these field types impacting performance.

I was pointed at this bug on IRC, but I am only trying to view longtext fields, not edit them.  How do I view the values in longtext fields as it makes workbench pretty much unusable for me otherwise.

Please see bug report #50692 for our plans on how to resolve this issue.

Hi,

Thanks for all the responses.

I would like to add, though, that removing editing of text fields is a serious, indeed in my case, near-blocking feature loss.

Would it not be possible to allow the field to be displayed as blob in table layout and, when an edit is requested, retrieve the specific instance of longtext to be edited? (akin to the proposal for viewing in #50692?

Many thanks,

Martin

Actually fetching field values (including BLOBs) is occurring anyway, according to MySQL client/server protocol client don't have option to skip fetching values. My concern was related to large values affecting UI part, namely scrolling recordset. In addition some other DB tools that don't visualize TEXT fields influenced my decision to hide TEXT fields' contents. Now I see the need to have this reverted and address performance issue in other way, not so radically.
As for ability to edit TEXT family type fields, they will be editable again and the bug this report initially referred to will be fixed.

Sergei: my sincere thanks for this reconsideration - I truly appreciate it.

I see the problems that could result in UI performance... could I suggest that each (long)text-field could have a "..." (expand) button embedded which would then open an editor for that specific field in a new window? This way, we would avoid displaying all the large elements and any performance hit.

Martin

Re-opened (hope that's ok!)

Field value text editor is planned only for next major version (v6.0). Sorry.
For now large text values are supposed to be edited in external editor and then pasted into the grid cell.

fix in repository confirmed

The changelog entry for Bug #50111 has been moved from the 5.2.13 changelog to the 5.2.16 changelog and edited to be as follows:

In the SQL Editor, if a LONGTEXT field was being edited in the table data live editor, when the changes were applied an error was generated.

MySQL Workbench has also been changed so that large text values need to be edited in an external editor and then pasted into the grid cell.