MySQL Bugs: #36076: NOT NULL flag interfering with renaming column

Bug #36076	NOT NULL flag interfering with renaming column
Submitted:	15 Apr 2008 6:26	Modified:	20 Oct 2008 22:59
Reporter:	Jared S (Silver Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Query Browser	Severity:	S2 (Serious)
Version:	1.2.12	OS:	Windows (Vista SP1)
Assigned to:	Mike Lischke	CPU Architecture:	Any
Tags:	NOT NULL, qc, renaming

Description:
NOT NULL flag interfering with renaming column.

How to repeat:
CREATE TABLE `test`.`test` (
  `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `text` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`)
)
ENGINE = InnoDB;

ALTER TABLE `test`.`test` CHANGE COLUMN `text` `text2` VARCHAR(45) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT NULL;

5.0.51a suffers from this bug also, so it may be that InnoDB has always functioned like this.  but it still doesn't make sense to me since a columns name should have no bearing on weather or not NULLS are allowed in table shema info.

I think your problem has nothing to do with renaming column. Look:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot test -P3308
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.58-enterprise-gpl-nt MySQL Enterprise Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table tnull (t varchar(45) not null default null);
ERROR 1067 (42000): Invalid default value for 't'
mysql>

Please, check.

Same problem on my computer.  Can we increase the severity.

As my example differs from your initial synopsis for the bug (and shows the real reason for the error message you got, I think), please, explain exactly what results do you consider a bug, and why.

It is very simple - I want a table that has field with NOT NULL and default value of NULL.  These type of tables work best for my Visual Basic INSERT code!

--
-- FAILS
---
create table tnull (t varchar(45) not null default null);

--
-- SUCEEDS
--
create table tnull (t varchar(45) not null default 'A');
ALTER TABLE `test`.`tnull` MODIFY COLUMN `t` VARCHAR(45) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT null;
ALTER TABLE `test`.`tnull` MODIFY COLUMN `t` VARCHAR(45) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL;

-- see how it currently takes 3 steps to perform 1 action!!

Your 3 steps procedure gives the same result:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot test -P3308
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.0.58-enterprise-gpl-nt MySQL Enterprise Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table tnull (t varchar(45) not null default 'A');
Query OK, 0 rows affected (0.11 sec)

mysql> ALTER TABLE `test`.`tnull` MODIFY COLUMN `t` VARCHAR(45) CHARACTER SET
    -> latin1 COLLATE latin1_swedish_ci DEFAULT null;
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE `test`.`tnull` MODIFY COLUMN `t` VARCHAR(45) CHARACTER SET
    -> latin1 COLLATE latin1_swedish_ci NOT NULL;
Query OK, 0 rows affected (0.23 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table tnull\G
*************************** 1. row ***************************
       Table: tnull
Create Table: CREATE TABLE `tnull` (
  `t` varchar(45) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.02 sec)

as the following one step:

mysql> create table tnull2 (t varchar(45) not null);
Query OK, 0 rows affected (0.13 sec)

mysql> show create table tnull2\G
*************************** 1. row ***************************
       Table: tnull2
Create Table: CREATE TABLE `tnull2` (
  `t` varchar(45) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

In any case, you can not get NOT NULL DEFAULT NULL column. Please, check.

-- script below creates a table with default of 'NULL'
CREATE TABLE `test`.`t1` (
  `id` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`)
)
ENGINE = InnoDB;

-- script below creates a table with default of 'A'
CREATE TABLE `test`.`New Table` (
  `c1` VARCHAR(45) NOT NULL DEFAULT 'A',
  PRIMARY KEY (`c1`)
)
ENGINE = InnoDB;

NOTE it is just not a obvious since DEFAULT of NULL is in fact default value, so you wont see much looking into "CREATE STATEMENTS".

Valeriy, please read bug synopsys again.

NOT NULL flag is still interfering with renaming column on MySQL Server 5.0.67

This is a bug because of inconsistency.

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3308 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 28
Server version: 5.0.66a-enterprise-gpl-nt MySQL Enterprise Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE `test`.`test` (
    ->   `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    ->   `text` VARCHAR(45) NOT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) engine=MyISAM;
Query OK, 0 rows affected (0.06 sec)

mysql> ALTER TABLE `test`.`test` CHANGE COLUMN `text` `text2` VARCHAR(45) CHARAC
TER SET latin1
    -> COLLATE latin1_swedish_ci NOT NULL DEFAULT NULL;
ERROR 1067 (42000): Invalid default value for 'text2'

The above does not work. But the following actions:

mysql> ALTER TABLE `test`.`test` CHANGE COLUMN `text` `text2` VARCHAR(45) CHARAC
TER SET latin1 default 'A';
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE `test`.`test` MODIFY COLUMN `text2` VARCHAR(45) CHARACTER SET
 latin1 default NULL;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE `test`.`test` MODIFY COLUMN `text2` VARCHAR(45) CHARACTER SET
 latin1 NOT NULL;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

produces the desired result (NOT NULL DEFAULT NULL column with different name):

mysql> desc test;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| text2 | varchar(45)      | NO   |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

It produces a NOT NULL column without a default value:

mysql> show create table test;
+-------+-----------------------
| Table | Create Table                                                                                                                                                        |
+-------+-----------------------
| test  | CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `text2` varchar(45) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 | 
+-------+-----------------------

which you could've easily created directly, without three ALTERs.

Where's a bug ?

--
-- STEP 1 create basic table with 2 fields
--

CREATE TABLE `test`.`New Table` (
  `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `desc1` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`)
)
ENGINE = InnoDB;

--
-- STEP 2 rename a column using latest QB, following script produces error
-- because, table is marked as 'NOT FLAG'

ALTER TABLE `test`.`New Table` CHANGE COLUMN `desc1` `desc2` VARCHAR(45) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT NULL;

The statement

ALTER TABLE `test`.`New Table` CHANGE COLUMN `desc1` `desc2` VARCHAR(45) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT NULL;

results in an error, it's correct. A column can not be "NOT NULL DEFAULT NULL".
To rename a column one should use

ALTER TABLE `test`.`New Table` CHANGE COLUMN `desc1` `desc2` VARCHAR(45) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL;

So, as far as I understand it, it's a bug in the QB that generates incorrect SQL when one tries to rename a column.

Indeed, I agree that the bug is in QB that generates the code server does NOT accept.

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3308 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 53
Server version: 5.0.66a-enterprise-gpl-nt MySQL Enterprise Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE `test`.`test` (
    ->   `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    ->   `text` VARCHAR(45) NOT NULL,
    ->   PRIMARY KEY (`id`)
    -> )
    -> ENGINE = InnoDB;
Query OK, 0 rows affected (1.14 sec)

mysql> show create table test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `text` varchar(45) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.11 sec)

mysql> desc test;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| text  | varchar(45)      | NO   |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.22 sec)

So, we have a kind of "not null default null" initially (not null without default value actually). Then we just do the rename:

mysql> alter table test change column text text2 varchar(45) character set latin
1 collate latin1_swedish_ci not null;
Query OK, 0 rows affected (1.45 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| text2 | varchar(45)      | NO   |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.02 sec)

mysql> show create table test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `text2` varchar(45) character set latin1 NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

and get a same kind of "not null default null" column with a new name.

In terms of resolution:

a) QB removes NOT NULL flag before column rename
b) SUN correct issue with server parser

I strongly recommend the later since the problem will be greatly complicated by turning a column rername into a 2 teired SQL statements.  

Can we please change back to Server : Parser.

Thank you for the feedback.

I agree problem is with Query Browser: having column NOT NULL DEFAULT NULL is not possible.

There are also similar bugs: bug #30403 and bug #37184 which are possible duplicates of each other.

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 bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Just downloaded R13, it's fixed, and will be using it today for db scripting. :)