MySQL Bugs: #86796: Long table comments yields no warning when truncated

Bug #86796	Long table comments yields no warning when truncated
Submitted:	22 Jun 2017 14:59	Modified:	23 Jun 2017 8:26
Reporter:	Peter Shillito	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Workbench	Severity:	S3 (Non-critical)
Version:	6.3.9	OS:	Windows (Windows 7)
Assigned to:		CPU Architecture:	Any
Tags:	comments, table editor, truncation

Description:
When modifying a table in Workbench, there is the option to add comments for the table as a whole (not the column comments). The box has no noticeable restriction on the amount of text that can be added. Upon saving changes to the table, the displayed SQL script comments out the comment past 60 characters but the comment begins near or past the edge of the script's box, meaning the warning isn't necessarily visible.

The script can then still be executed and no further warnings occur.

Following execution, the comments box itself truncates to 60 characters and the text previously entered is lost.

How to repeat:
1) In Workbench, right click a table and click "Alter Table..."
2) Type into the "comments" box at the top (not the column's comments box) a comment longer than 60 characters.
3) Click "Apply".
4) Note the SQL script displayed will have /* comment truncated */ /*(rest of text here)*/ after the first 60 characters. This may or may not be off the side of the box depending on resolution.
5) Click "Apply"
6) Note "SQL Script was successfully applied to the database" in the "Apply SQL Script to Database" box, and also the "Changes applied to (table name)" in the "Output" section of the main window.

Suggested fix:
Add a limit to the number of characters that can be typed into the comments field in the first place. Therefore it is clear that more than 60 characters are not possible. Keypresses into the comments field after the 60th should be ignored.

This can be augmented by a tooltip or similar message stating the fact that MySQL does not accept more than 60 characters for table comments.

I've reconsidered the severity and, considering it has hindered my work as I didn't notice the message in the "Apply SQL Script to Database" window, I feel like moving it to "non-critical" rather than "feature request" is more appropriate. Apologies for the quick change.

Just a comment :-)

https://dev.mysql.com/doc/refman/5.7/en/create-table.html
"A comment for the table .. up to 2048 characters long."

-- Peter
-- not a MySQL/Oracle person

Hello Peter Shillito,

Thank you for the report.
Imho this was fixed post Bug #62207/Bug #72904, and I'm not able to reproduce with WB 6.3.9 on Win7. Could you please provide exact MySQL version details, CREATE TABLE <table_name>\G output along with the exact comment that you are trying to update to reproduce the issue at our end? You may want to mark it as private after posting table details. 
Also, is this happening in SQL Editor or Modeling? For modeling we have one open report already Bug #76514

Thanks,
Umesh

Unless I'm misunderstanding the distinction between the two, I am not using the modelling functions of workbench, only the SQL Editor side, though letting workbench generate my SQL rather than writing it myself.

Full Workbench version: 6.3.9 build 10690321 CE (64-bits)
MySQL Version: 5.1.63
Comment attempting to use: "in the event of partial shipments, not all unique numbers for that order will be ticked for inclusion, or even available on the tablet system."
Generated script by Workbench: 
ALTER TABLE `tek`.`coc_unqnos` 

COMMENT = 'in the event of partial shipments, not all unique numbers fo' /* comment truncated */ /*r that order will be ticked for inclusion, or even available on the tablet system.*/ ;

My suggested fix of limiting the text box to the 60 characters it automatically truncates to may no longer be appropriate following Peter Laursen's link regarding table comments supporting up to 2048 characters long. Also, #76514 may indeed be the same workbench functionality being called here in SQL Editor too.

Thank you for providing the requested details.
Imho, looking at the manual page - "A comment for the table, up to 2048 characters long (60 characters before MySQL 5.5.3).". As you indicated, you are using MySQL Version: 5.1.63 and max table comment length before 5.5.3 is 60 chars.  

From the fixed bug's change log:

[22 Jun 2012 0:39] Philip Olson
Fixed as of 5.2.41 , and here's the changelog entry:

MySQL Workbench would sometimes be unable to save changes that were only to
comments. It used a hard limit, but now uses the MySQL Server limit for
maximum comment lengths. For example, it would compare the first 60
characters of a table comment change, and generate an error if the
change did not affect the first 60 characters. 

From the MySQL
Server 5.5.3 changelog: the maximum length of table comments was
extended from 60 to 2048 characters. The maximum length of column
comments was extended from 255 to 1024 characters. Index definitions now
can include a comment of up to 1024 characters.

With the above explanation from the change log, I conclude that it won't be fixed in WB as this is reaching MySQL 5.1.63 hard limit.

Thanks,
Umesh

Ah, I see. Thank you Umesh. In an ideal world, I would argue that the maximum length of table comments should be checked beforehand and apply that limit to the textbox (so I can't type more than 60 or whatever that version of MySQL has as a limit). However, I appreciate that the server I am connecting to is several versions behind, so I will instead recommend to my manager that we try to update MySQL to a more recent version.

Thank you all for your quick responses.