Bug #4788 show create table provides incorrect statement
Submitted: 28 Jul 2004 15:20 Modified: 11 Sep 2004 12:10
Reporter: Matthias Leich Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1 OS:
Assigned to: Ingo Strüwing CPU Architecture:Any

[28 Jul 2004 15:20] Matthias Leich
Description:
I start with the following two statements 

   set @arg00= 8 ;
   create table tx as select @arg00 as a1 ;

MySQL delivers on 'show create table tx'
   Table	Create Table
   tx	CREATE TABLE `tx` (`a1` bigint(8192) default NULL
         ) ENGINE=MyISAM DEFAULT CHARSET=latin1

This CREATE TABLE statement is not correct, because
'CREATE TABLE `tx_dupl` ( `a1` bigint(8192) default NULL
       ) ENGINE=MyISAM DEFAULT CHARSET=latin1 '
gives error 1074: Column length too big for column 'a1' (max = 255); use BLOB instead

BTW: - CREATE TABLE tx_dupl LIKE tx     works successful
         - I am able to perform DML statements on the table tx.
         - The error message contains a useless hint, because a BLOB is a non numeric type.

My environment: Intel PC with Linux(SuSE 9.0)
                          MySQL 4.1 source distrib. (last bk pull 2004-07-28)

How to repeat:
please use the statements above

Suggested fix:
What about bigint instead of the incorrect bigint(8192) ?

The MySQL manual says in chapter "12 Column Types":
... 
Several of the column type descriptions use these conventions:
M Indicates the maximum display size. The maximum legal display size is 255.
[10 Sep 2004 17:01] Ingo Strüwing
Added code to adjust the field_length of user variables in dependence on the field type.
[11 Sep 2004 12:10] Ingo Strüwing
Fixed in 4.1.5