| Bug #35726 | Can't modify column with NOT NULL | ||
|---|---|---|---|
| Submitted: | 31 Mar 2008 22:53 | Modified: | 1 Apr 2008 3:03 |
| Reporter: | Jared S (Silver Quality Contributor) | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S2 (Serious) |
| Version: | 5.1.23 | OS: | Windows (Vista) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | default value, enum | ||
[1 Apr 2008 0:29]
MySQL Verification Team
Thank you for the bug report. Notice you specified NOT NULL and also a
DEFAULT NULL:
Microsoft Windows [versão 6.0.6000]
Copyright (c) 2006 Microsoft Corporation. Todos os direitos reservados.
c:\dbs>5.0\bin\mysql -uroot test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.0.60-nt Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> DROP TABLE IF EXISTS `test`.`new table`;
Query OK, 0 rows affected, 1 warning (0.04 sec)
mysql> CREATE TABLE `test`.`new table` (
-> `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
-> `myenum` enum('One','Two','Three') NOT NULL,
-> PRIMARY KEY (`id`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.61 sec)
mysql> ALTER TABLE `test`.`new table` MODIFY COLUMN `myenum` INTEGER UNSIGNED NOT NULL DEFAULT
-> NULL;
ERROR 1067 (42000): Invalid default value for 'myenum'
mysql> ALTER TABLE `test`.`new table` MODIFY COLUMN `myenum` INTEGER UNSIGNED NOT NULL;
Query OK, 0 rows affected (0.31 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE `test`.`new table` MODIFY COLUMN `myenum` INTEGER UNSIGNED NOT NULL
-> DEFAULT 1;
Query OK, 0 rows affected (0.24 sec)
Records: 0 Duplicates: 0 Warnings: 0
[1 Apr 2008 2:37]
Jared S
-changed version
[1 Apr 2008 2:40]
Jared S
MySQL actually support NOT NULL on enum columns, but I just appear to have trouble changing enum to integer.
[1 Apr 2008 2:50]
Jared S
REPO STEPS...
- (Change the following)
- ENUM('One', 'Two', 'Three')
- ENUM('One', 'Two', 'Three', 'Four')
WORKAROUND...
- decheck NOT NULL
- ENUM('One', 'Two', 'Three', 'Four')
- check NOT NULL
[1 Apr 2008 2:52]
Jared S
-update bug info
[1 Apr 2008 3:03]
Jared S
This bug also effects changing DECIMAL(9,2) to DECIMAL(7,2) when NOT NULL is selected.
Description: Hi, Cant change an enum field to integer even when the table is empty. How to repeat: DROP TABLE IF EXISTS `test`.`new table`; CREATE TABLE `test`.`new table` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `myenum` enum('One','Two','Three') NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- Change column 'myenum' to integer ALTER TABLE `test`.`new table` MODIFY COLUMN `myenum` INTEGER UNSIGNED NOT NULL DEFAULT NULL;