| 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: | |
| 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: | Victor Vagin | CPU Architecture: | Any |
[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
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.