Bug #15120 Documentation on Silent Changes has not been updated for 5.0
Submitted: 21 Nov 2005 23:38 Modified: 27 Sep 2006 17:57
Reporter: Nick Gaugler Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.0 OS:N/A
Assigned to: Paul DuBois CPU Architecture:Any

[21 Nov 2005 23:38] Nick Gaugler
Description:
http://dev.mysql.com/doc/refman/5.0/en/silent-column-changes.html

This page still states that a silent change from char to varchar will be performed in MySQL 5.0 if the column size is 3+ and there are other dynamic columns within the table.  This no longer happens.

How to repeat:
CREATE TABLE t1 (
c1 char(5),
c2 varchar(5));

This will remain char(5) pre 5.0.x it would be converted to varchar(5).

Suggested fix:
Please remove this section from the docs.
[21 Nov 2005 23:43] Nick Gaugler
fix the category
[22 Nov 2005 0:20] Paul DuBois
The section indicates that the change occurs only prior to 5.0.3,
which is true:

Possible data type changes are given in the following list. These occur prior to MySQL 5.0.3. As of 5.0.3, an error occurs if a column cannot be created using the specified data type.
[18 Sep 2006 10:59] Tonci Grgin
Paul, can you recheck this paragraph:
http://dev.mysql.com/doc/refman/5.0/en/silent-column-changes.html
If any column in a table has a variable length, the entire row becomes variable-length as a result. Therefore, if a table contains any variable-length columns (VARCHAR, TEXT, or BLOB), all CHAR  columns longer than three characters are changed to VARCHAR columns. This does not affect how you use the columns in any way; in MySQL, VARCHAR is just a different way to store characters. MySQL performs this conversion because it saves space and makes table operations faster. See Chapter 14, Storage Engines and Table Types.

In my example I am not using:
Starting with MySQL 5.0.3, a CHAR or BINARY column with a *length specification greater than 255* is not silently converted. Instead, an error occurs. From MySQL 5.0.6 on, silent conversion of VARCHAR and VARBINARY columns with a *length specification greater than 65,535* does not occur if strict SQL mode is enabled. Instead, an error occurs.

So please either add that only *before* 5.0.3 "If any column in a table has a variable length, the entire row becomes variable-length as a result. Therefore, if a table contains any variable-length columns (VARCHAR, TEXT, or BLOB), all CHAR  columns longer than three characters are changed to VARCHAR columns.", or I should change category to server bug.

5.0.26 and 5.1.12 BK
USE test;
DROP TABLE IF EXISTS `bug21770`;
CREATE TABLE `bug21770` (`data_type_id` int(10) unsigned NOT NULL auto_increment,
`char_type` char(30) default NULL,
`varchar_type` varchar(30) default NULL,
`tinyBlob_type` tinyblob,
`blob_type` blob,
`mediumBlob_type` mediumblob,
`longBlob_type` longblob,
`tinyText_type` tinytext,
`text_type` text,
`mediumText_type` mediumtext,
`longText_type` longtext,
`enum_type` enum('New','Used') NOT NULL default 'New',
`set_type` set('a','b','c') default NULL,
`tinyInt_type` tinyint(4) default NULL,
`smallInt_type` smallint(5) unsigned default NULL,
`mediumInt_type` mediumint(9) default NULL,
`int_type` int(11) default NULL,
`bigInt_type` bigint(20) default NULL,
`float_type` float default NULL,
`double_type` double default NULL,
`decimal_type` decimal(16,2) default NULL,
`date_type` date default NULL,
`time_type` time default NULL,
`dateTime_type` datetime default NULL,
`timeStamp_type` timestamp,
`year_type` year(4) default NULL,
PRIMARY KEY  (`data_type_id`)
) ENGINE=InnoDB;
show create table bug21770\G
insert into bug21770 (`data_type_id`, `text_type`) VALUES (NULL,"TONCI");
show create table bug21770\G
--
mysql> USE test;
Database changed
mysql> DROP TABLE IF EXISTS `bug21770`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE `bug21770` (`data_type_id` int(10) unsigned NOT NULL auto_in
crement,
    -> `char_type` char(30) default NULL,
    -> `varchar_type` varchar(30) default NULL,
    -> `tinyBlob_type` tinyblob,
    -> `blob_type` blob,
    -> `mediumBlob_type` mediumblob,
    -> `longBlob_type` longblob,
    -> `tinyText_type` tinytext,
    -> `text_type` text,
    -> `mediumText_type` mediumtext,
    -> `longText_type` longtext,
    -> `enum_type` enum('New','Used') NOT NULL default 'New',
    -> `set_type` set('a','b','c') default NULL,
    -> `tinyInt_type` tinyint(4) default NULL,
    -> `smallInt_type` smallint(5) unsigned default NULL,
    -> `mediumInt_type` mediumint(9) default NULL,
    -> `int_type` int(11) default NULL,
    -> `bigInt_type` bigint(20) default NULL,
    -> `float_type` float default NULL,
    -> `double_type` double default NULL,
    -> `decimal_type` decimal(16,2) default NULL,
    -> `date_type` date default NULL,
    -> `time_type` time default NULL,
    -> `dateTime_type` datetime default NULL,
    -> `timeStamp_type` timestamp,
    -> `year_type` year(4) default NULL,
    -> PRIMARY KEY  (`data_type_id`)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.05 sec)

mysql> show create table bug21770\G
*************************** 1. row ***************************
       Table: bug21770
Create Table: CREATE TABLE `bug21770` (
  `data_type_id` int(10) unsigned NOT NULL auto_increment,
  `char_type` char(30) default NULL,
  `varchar_type` varchar(30) default NULL,
  `tinyBlob_type` tinyblob,
  `blob_type` blob,
  `mediumBlob_type` mediumblob,
  `longBlob_type` longblob,
  `tinyText_type` tinytext,
  `text_type` text,
  `mediumText_type` mediumtext,
  `longText_type` longtext,
  `enum_type` enum('New','Used') NOT NULL default 'New',
  `set_type` set('a','b','c') default NULL,
  `tinyInt_type` tinyint(4) default NULL,
  `smallInt_type` smallint(5) unsigned default NULL,
  `mediumInt_type` mediumint(9) default NULL,
  `int_type` int(11) default NULL,
  `bigInt_type` bigint(20) default NULL,
  `float_type` float default NULL,
  `double_type` double default NULL,
  `decimal_type` decimal(16,2) default NULL,
  `date_type` date default NULL,
  `time_type` time default NULL,
  `dateTime_type` datetime default NULL,
  `timeStamp_type` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURREN
T_TIMESTAMP,
  `year_type` year(4) default NULL,
  PRIMARY KEY  (`data_type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> insert into bug21770 (`data_type_id`, `text_type`) VALUES (NULL,"TONCI");

Query OK, 1 row affected (0.00 sec)

mysql> show create table bug21770\G
*************************** 1. row ***************************
       Table: bug21770
Create Table: CREATE TABLE `bug21770` (
  `data_type_id` int(10) unsigned NOT NULL auto_increment,
  `char_type` char(30) default NULL,
  `varchar_type` varchar(30) default NULL,
  `tinyBlob_type` tinyblob,
  `blob_type` blob,
  `mediumBlob_type` mediumblob,
  `longBlob_type` longblob,
  `tinyText_type` tinytext,
  `text_type` text,
  `mediumText_type` mediumtext,
  `longText_type` longtext,
  `enum_type` enum('New','Used') NOT NULL default 'New',
  `set_type` set('a','b','c') default NULL,
  `tinyInt_type` tinyint(4) default NULL,
  `smallInt_type` smallint(5) unsigned default NULL,
  `mediumInt_type` mediumint(9) default NULL,
  `int_type` int(11) default NULL,
  `bigInt_type` bigint(20) default NULL,
  `float_type` float default NULL,
  `double_type` double default NULL,
  `decimal_type` decimal(16,2) default NULL,
  `date_type` date default NULL,
  `time_type` time default NULL,
  `dateTime_type` datetime default NULL,
  `timeStamp_type` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURREN
T_TIMESTAMP,
  `year_type` year(4) default NULL,
  PRIMARY KEY  (`data_type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql>
[27 Sep 2006 17:58] Paul DuBois
The second paragraph of the page in question covers this issue.