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: | |
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
[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.