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)
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)