Bug #9881 ALTER TABLE gives wrong error message with sql-mode TRADITIONAL
Submitted: 13 Apr 2005 22:04 Modified: 17 Jul 2005 0:20
Reporter: Hakan Küçükyılmaz Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.0.9 OS:Linux (SuSE 9.2)
Assigned to: Ramil Kalimullin CPU Architecture:Any

[13 Apr 2005 22:04] Hakan Küçükyılmaz
Description:
ALTER TABLE gives wrong error message with sql-mode TRADITIONAL

How to repeat:
5.0.5-beta-log
[00:03] root@test>SHOW VARIABLES LIKE 'sql_mode';
+---------------+-------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                |
+---------------+-------------------------------------------------------------------------------------------------------------------------------+
| sql_mode      | STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER |
+---------------+-------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

5.0.5-beta-log
[00:03] root@test>DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected (0.00 sec)

5.0.5-beta-log
[00:04] root@test>CREATE TABLE t1 (a int);
Query OK, 0 rows affected (0.26 sec)

5.0.5-beta-log
[00:04] root@test>ALTER TABLE t1 ADD b DATE NOT NULL;
Query OK, 0 rows affected (0.21 sec)
Records: 0  Duplicates: 0  Warnings: 0

5.0.5-beta-log
[00:04] root@test>ALTER TABLE t1 MODIFY a BIGINT UNSIGNED NOT NULL;
ERROR 1067 (42000): Invalid default value for 'b'
5.0.5-beta-log
[00:04] root@test>
[15 Jul 2005 10:50] Bugs System
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/27143
[15 Jul 2005 12:05] Ramil Kalimullin
fixed in 5.0.10
[17 Jul 2005 0:20] Paul DuBois
Noted in 5.0.10 changelog.