MySQL Bugs: #14693: Cannot create a new column default after column created

Bug #14693	Cannot create a new column default after column created
Submitted:	7 Nov 2005 8:04	Modified:	6 Dec 2005 21:44
Reporter:	[ name withheld ]	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.16-BK, 5.0.15-nt	OS:	Linux (Linux, Windows)
Assigned to:	Alexey Botchkov	CPU Architecture:	Any

Description:
Hi,

In earlier versions of MySQL I've been able to create a table and then set default values for columns e.g.

create table mytable (mycol int(10) not null);
alter table mytable alter column mycol set default 0;

In MySQL 5.0, the alter table returns 'Query OK, 0 rows affected (0.05 sec)' and if I desc the table it shows the default as not set. I can set defaults at the time I create tables, but my scripts (ported from MSSQL and Oracle) do it as a second stage.

This appears to be a fault.

How to repeat:
Key the following...

create table mytable (mycol int(10) not null);
alter table mytable alter column mycol set default 0;

Thank you for a bug report. I was able to repeat it with both 5.0.15 on Windows and with 5.0.16-BK (ChangeSet@1.1957.1.18, 2005-11-03 20:29:21+02:00, jani@ua141d10.elisa.omakaista.fi) on Linux:

mysql> use test;
Database changed
mysql> create table mytable (mycol int(10) not null);
Query OK, 0 rows affected (0.25 sec)

mysql> alter table mytable alter column mycol set default 0;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc mytable;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| mycol | int(10) | NO   |     |         |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.15 sec)

mysql> alter table mytable alter column mycol set default 1;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc mytable;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| mycol | int(10) | NO   |     |         |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.01 sec)

mysql> insert into mytable values();
ERROR 1364 (HY000): Field 'mycol' doesn't have a default value

As a workaround, I recommend you to use more standard way (same as in Oracle, for example) to modify column properties:

mysql> alter table mytable modify mycol int(10) not null default 1;
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc mytable;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| mycol | int(10) | NO   |     | 1       |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.01 sec)

It just works, as in any database.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/32685

fixed in 5.0.17

Noted in 5.0.17 changelog.