Bug #1323 varchar fields becoming char fields regardless of length
Submitted: 17 Sep 2003 13:41 Modified: 7 Oct 2003 4:36
Reporter: Kevin Korb Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:S3 (Non-critical)
Version:mysql-4.0.15-standard (Official MySQL-st OS:Linux (Linux RedHat 7.3 2.4.21 kernel)
Assigned to: Bugs System CPU Architecture:Any

[17 Sep 2003 13:41] Kevin Korb
Description:
When duplicating a table all varchar fields become char fields even if the length is longer than 4 chars as stated in http://www.mysql.com/doc/en/Silent_column_changes.html

This was noticed when one of our clients duplicated part of a table with many varchar(255) fields.  The source table with the varchar fields was 34MB and the resulting table with the char fields was 1.3GB.

How to repeat:
create table test (vc varchar(255));
insert into test values ('some data');
insert into test values ('some more data');

> desc test;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| vc    | varchar(255) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+

create table test2 as select * from test;

> desc test2;
+-------+-----------+------+-----+---------+-------+
| Field | Type      | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------+-------+
| vc    | char(255) | YES  |     | NULL    |       |
+-------+-----------+------+-----+---------+-------+

As you can see the new table uses a char field instead of a varchar field.

Suggested fix:
A workaround is to create the second table in advance with the proper datatypes and then the data can be copied with 'insert into test2 select * from test' and the varchar fields will remain.
[18 Sep 2003 3:48] Indrek Siitan
Looks like MySQL prefers the fixed-length row format. I did a quick test
with a blob field, that forces the table to be dynamic-length row in any
case, and the result was:

mysql> create table test (vc varchar(255), bl mediumtext);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test values ('data','data');
Query OK, 1 row affected (0.00 sec)

mysql> create table test2 as select * from test;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> desc test2;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| vc    | varchar(255) | YES  |     | NULL    |       |
| bl    | mediumtext   | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

Peter Gulutzan's comment on the standard:
The standard is CREATE TABLE t1 LIKE t2, or CREATE TABLE t1 AS (SELECT ...) 
[ {WITH DATA | WITH NO DATA} ]. And the standard says you have to preserve 
the data type of the original.
[7 Oct 2003 4:36] Victor Vagin
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html