MySQL Bugs: #61626: Unable to save modifications to column comments if this is all that is modified

Bug #61626	Unable to save modifications to column comments if this is all that is modified
Submitted:	24 Jun 2011 11:43	Modified:	23 Dec 2011 19:15
Reporter:	david pugh	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: SQL Editor	Severity:	S3 (Non-critical)
Version:	5.2.34	OS:	Any (Windows 7 64bit, Mac OS X)
Assigned to:		CPU Architecture:	Any

Description:
If make a modification to a column's comments only.  Then click save, you are unable to save with the message 'No changes to object were detected'

How to repeat:
repeatable

Suggested fix:
You need to modify an attribute of the column i.e. datatype in some way for workbench to save the modifications.

What server version, x.y.z, do you use? I thought this problem is solved since 5.2.33 (see bug #60500)...

Server is 5.5.10

It looks like it's come back again in 5.2.34, sorry I forgot I had logged this before.

I am confused, I have created a test table as per previous bug.  Adding/Editing comments seems to work and yet if try this on existing table it doesn't.

This is the create statement of the table I am trying to modify.

delimiter $$

CREATE TABLE `jobs_stages` (
  `Jobs_StageId` int(12) unsigned NOT NULL AUTO_INCREMENT,
  `Jobs_StageType` char(1) NOT NULL COMMENT 'U - Waiting to be readied\nV - IM Passed in waiting to be readied\nN - IM Passed in waiting distribution\nX - IM Passed in waiting members authorisation\nW - IM Passed out awaiting authorisation\nJ - IM passed in/out Rejections\nR - Readied for invoicing\n',
  `Jobs_StageJobid` int(12) unsigned NOT NULL,
  `Jobs_StageCompanyId` int(12) unsigned NOT NULL,
  PRIMARY KEY (`Jobs_StageId`),
  KEY `FK_jobsstages_1` (`Jobs_StageJobid`),
  KEY `FK_jobsstages_2` (`Jobs_StageCompanyId`),
  KEY `index_1` (`Jobs_StageCompanyId`,`Jobs_StageType`,`Jobs_StageJobid`),
  CONSTRAINT `FK_jobsstages_1` FOREIGN KEY (`Jobs_StageJobid`) REFERENCES `jobs` (`JobId`) ON DELETE CASCADE ON UPDATE NO ACTION,
  CONSTRAINT `FK_jobsstages_2` FOREIGN KEY (`Jobs_StageCompanyId`) REFERENCES `contacts` (`ContactId`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=312448 DEFAULT CHARSET=latin1$$

...

Playing, it seems to work ok if you modify the start of the comment, but not if you append something to the end.   This is quite a long comment.  Is there a limit somewhere on the size of comments in workbench and it is not comparing the full length?

Dave

By the looks of it, it is only comparing the first 60 characters

I found this old bug report:
http://bugs.mysql.com/bug.php?id=13934

Peter
(not a MySQL person)

We used to have the limit of 60 characters for table comment, but it does not apply to 5.5.10 (see http://dev.mysql.com/doc/refman/5.5/en/create-table.html):

"- COMMENT

A comment for the table, up to 2048 characters long (60 characters before MySQL 5.5.3)."

But Workbench 5.2.34 indeed does NOT allow to change comment to be longer than 60 characters if previous one was 60 characters, probably because it compares only first 60, even working with MySQL server 5.5.15.

Same seems true for MySQL 5.1.57. This (with a 70 character comment) succeeds in 5.5.57:

CREATE TABLE `comtest` (
  `id` int(11) NOT NULL COMMENT '1111111111111111111111111111111111111111111111111111111111111111111111',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

I meant it succeeds in 5.1.57 - obviously!

Fixed as of 5.2.37:

  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.