Bug #32252 information_schema.columns column_default truncates at 64 characters

• Submitted: 9 Nov 2007 23:53
• Modified: 10 Nov 2007 0:57
• Reporter: Farid Zidan (Candidate Quality Contributor)
• Status: Can't repeat
• Category: MySQL Server: Information schema
• Severity: S2 (Serious)
• Version: 5.0.27-community-nt
• OS: Windows (XP SP2)
• CPU Architecture: Any

[9 Nov 2007 23:53] Farid Zidan

Description:
created a table with a column with long text default. When interrogate information schema columns view about the table column defaults I get only the first 64 characters of the long default.

( the column long default gets correctly used when inserting into the table)

How to repeat:
1. create test table:

create table test_col_default(
tran_id int primary key,
tran_type char(8),
tran_name varchar(20) default 'string default',
amount float default 1000.25,
active_ind tinyint default 1,
paid_date timestamp default current_timestamp,
long_name varchar(2000) default 'The default value of the column. The value in this column should be interpreted as a string if it is enclosed in quotation marks.
If NULL was specified as the default value, then this column is the word NULL, not enclosed in quotation marks. If the default value cannot be represented without truncation, then this column contains TRUNCATED, with no enclosing single quotation marks. If no default value was specified, then this column is NULL.

The value of COLUMN_DEF can be used in generating a new column definition, except when it contains the value TRUNCATED.');

2. interrogate information schema:
select
column_name,
column_default
from
information_schema.columns
where
table_schema = <my database> and
table_name = 'test_col_default' and
column_default is not null
order by
ordinal_position;

you get ( long_name column default is truncated ):
column_name	column_default
-----------     --------------
tran_name	string default
amount	1000.25
active_ind	1
paid_date	CURRENT_TIMESTAMP
long_name	The default value of the column. The value in this column should

3. The default for column long_name is getting used correctly:
insert into test_col_default ( tran_id ) values( 1);
select * from test_col_default;

[10 Nov 2007 0:19] MySQL Verification Team

Thank you for the bug report. I can't repeat with released version 5.0.45,
could you please upgrade. Thanks in advance.

mysql> select
    -> column_name,
    -> column_default
    -> from
    -> information_schema.columns
    -> where
    -> table_schema="test" and
    -> table_name = "test_col_default" and
    -> column_default is not null
    -> order by
    -> ordinal_position\G
*************************** 1. row ***************************
   column_name: tran_name
column_default: string default
*************************** 2. row ***************************
   column_name: amount
column_default: 1000.25
*************************** 3. row ***************************
   column_name: active_ind
column_default: 1
*************************** 4. row ***************************
   column_name: paid_date
column_default: CURRENT_TIMESTAMP
*************************** 5. row ***************************
   column_name: long_name
column_default: The default value of the column. The value in this column
should be interpreted as a string if it is enclosed in quotation marks.
If NULL was specified as the default value, then this column is the word NULL, not
enclosed in quotation marks. If the default value cannot be represented without
truncation, then this column contains TRUNCATED, with no enclosing single quotation marks.
If no default value was specified, then this column is NULL.
The value of COLUMN_DEF can be used in generating a new column definition, except when it
contains the value TRUNCATED.
5 rows in set (0.01 sec)

mysql>

[10 Nov 2007 0:57] Farid Zidan

Updated to server 5.0.45 and now it works correctly. Thanks for the quick response.