Bug #63476 ALTER TABLE ADD a NOT NULL column with silent default.

• Submitted: 29 Nov 2011 15:39
• Modified: 12 Dec 2011 19:47
• Reporter: Rolf Martin-Hoster
• Status: Duplicate
• Category: MySQL Server: General
• Severity: S3 (Non-critical)
• Version: 5.1.54 ent
• OS: Any
• CPU Architecture: Any
• Tags: not null silent defaults

[29 Nov 2011 15:39] Rolf Martin-Hoster

Description:
ALTER TABLE ADD a NOT NULL column does not modify the column definition with a silent default.

How to repeat:
mysql> show create table test;
+-------+------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                           |
+-------+------------------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `id` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into test values(1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values(2);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values(3);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values(4);
Query OK, 1 row affected (0.00 sec)
mysql> alter table test ADD COLUMN `location_id` int(10) unsigned NOT NULL;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> show create table test;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                      |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `id` int(11) NOT NULL DEFAULT '0',
  `location_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter table test drop primary key;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> alter table test ADD PRIMARY KEY(`id`,`location_id`);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from test;
+----+-------------+
| id | location_id |
+----+-------------+
|  1 |           0 |
|  2 |           0 |
|  3 |           0 |
|  4 |           0 |
+----+-------------+
4 rows in set (0.00 sec)

mysql> show create table test;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                    |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `id` int(11) NOT NULL DEFAULT '0',
  `location_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`,`location_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[29 Nov 2011 18:17] Peter Laursen

What is the sql_mode?

Peter
(not a MySQL person)

[29 Nov 2011 18:24] Rolf Martin-Hoster

NO_AUTO_CREATE_USER.

The problem is that the column specification is not altered to say its going to have a silent default. When you create a table this happens, but when you add a column it doesn't.

[12 Dec 2011 19:47] Sveta Smirnova

Please do not submit the same bug more than once. An existing bug report already describes this very problem. Even if you feel that your issue is somewhat different, the resolution is likely
to be the same. Because of this, we hope you add your comments to the original bug instead.

Thank you for your interest in MySQL.

Duplicate of bug #45669