Bug #13934 Silent truncation of table comments
Submitted: 11 Oct 2005 17:55 Modified: 3 Jul 2006 20:23
Reporter: Paul Dubois Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:any OS:Linux (Linux)
Assigned to: Sergey Glukhov CPU Architecture:Any

[11 Oct 2005 17:55] Paul Dubois
Description:
The manual states that table comments are limited to 60
characters.  This limit is in fact enforced, but silent truncation
occurs for too-long comments. The server should not accept too-long
comments and truncate them silently.  It should produce a warning,
or an error in strict mode. Currently the server does neither.

Another issue is that column comments are truncated in one context
but not another.  The example in the how-to-repeat section shows
that a column comment longer than 255 characters can be created and
is displayed in full by SHOW CREATE TABLE.  However, in the
INFORMATION_SCHEMA database, the data type of COLUMNS.COLUMN_COMMENT
is VARCHAR(255), so only 255 characters are displayed.  It would be
nice to know what the maximum column length is (so that it can be stated
in the manual) and to have it treated consistently.  (With warnings/errors
if the length is too long.)

How to repeat:
Demonstration script. The column comment is 280 characters, the table
comment is 70 characters.

The server truncates the table comment (silently) to 60 characters.
The server accepts the column comment, but truncates it in
INFORMATION_SCHEMA.COLUMNS to 255 characters.

set sql_mode='traditional';
use test;
drop table if exists t;
create table t (
i int
comment
'123456789*123456789*123456789*123456789*123456789*123456789*123456789*123456789*123456789*123456789*123456789*123456789*123456789*123456789*123456789*123456789*123456789*123456789*123456789*123456789*123456789*123456789*123456789*123456789*123456789*123456789*123456789*123456789*'
)
comment
'123456789*123456789*123456789*123456789*123456789*123456789*123456789*'
;
show create table t\G
use information_schema;
select column_name, column_comment from columns where
table_schema = 'test' and table_name = 't'\G

Output from script (MySQL 5.0.15):

--------------
set sql_mode='traditional'
--------------

Query OK, 0 rows affected (0.00 sec)

--------------
drop table if exists t
--------------

Query OK, 0 rows affected (0.01 sec)

--------------
create table t (
i int
comment
'123456789*123456789*123456789*123456789*123456789*123456789*123456789*123456789*123456789*123456789*123456789*123456789*123456789*123456789*123456789*123456789*123456789*123456789*123456789*123456789*123456789*123456789*123456789*123456789*123456789*123456789*123456789*123456789*'
)
comment
'123456789*123456789*123456789*123456789*123456789*123456789*123456789*'
--------------

Query OK, 0 rows affected (0.03 sec)

--------------
show create table t
--------------

*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `i` int(11) default NULL COMMENT '123456789*123456789*123456789*123456789*123456789*123456789*123456789*123456789*123456789*123456789*123456789*123456789*123456789*123456789*123456789*123456789*123456789*123456789*123456789*123456789*123456789*123456789*123456789*123456789*123456789*123456789*123456789*123456789*'
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='123456789*123456789*123456789*123456789*123456789*123456789*'
1 row in set (0.05 sec)

--------------
select column_name, column_comment from columns where
table_schema = 'test' and table_name = 't'
--------------

*************************** 1. row ***************************
   column_name: i
column_comment: 123456789*123456789*123456789*123456789*123456789*123456789*123456789*123456789*123456789*123456789*123456789*123456789*123456789*123456789*123456789*123456789*123456789*123456789*123456789*123456789*123456789*123456789*123456789*123456789*123456789*12345
1 row in set (0.03 sec)
[21 Oct 2005 6:39] Oleksandr Byelkin
OK, to push after fixing the problem mentioned in my review.
(I suppose that the problem is absent in the early versions of the server (4.1/4.0) or they do not fixed because it is not so important)
[3 Apr 2006 10:09] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/4406
[30 May 2006 8:47] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/7010
[29 Jun 2006 13:36] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/8484
[3 Jul 2006 11:32] Sergey Glukhov
Fixed in 5.0.24
[3 Jul 2006 20:23] Paul Dubois
Noted in 5.0.24 changelog.

Table comments longer than 60 characters and column comments longer
than 255 characters were truncated silently. Now a warning is issued,
or an error in strict mode.
[13 Jul 2006 3:25] Paul Dubois
Fix went to 5.0.25 instead.