* Alexander Nozdrin <alik@stripped> [08/02/04 17:48]:
> ChangeSet@stripped, 2008-02-04 17:39:14+03:00, anozdrin@quad. +3 -0
> A patch for Bug#18834: ALTER TABLE ADD INDEX on table with
> two timestamp fields.
>
> The actual problem here was that CREATE TABLE allowed zero
> date as a default value for a TIMESTAMP column in NO_ZERO_DATE mode.
>
> The thing is that for TIMESTAMP date type specific rule is applied:
> column_name TIMESTAMP == column_name TIMESTAMP DEFAULT 0
> whever for any other date data type
> column_name TYPE == column_name TYPE DEFAULT NULL
>
> The fix is to raise an error when we're in NO_ZERO_DATE mode and
> there is TIMESTAMP column w/o default value.
I think you're right in your analysis, however, your fix is not
full.
A table may be created in one SQL mode and altered in
another. As far as I understand, the user will see the same
obscure message as before.
A similar error can happen with other column types, but in a
slightly different scenario:
mysql> create table t1 (a datetime not null);
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t1 (a) values (0);
Query OK, 1 row affected (0.00 sec)
mysql> set sql_mode=traditional;
Query OK, 0 rows affected (0.00 sec)
mysql> alter table t1 add index (a);
ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column 'a' at row
1
I think you should try to move the added check to mysql_prepare_create_table.
That will ensure that the same error is returned by both CREATE and ALTER,
since mysql_prepare_create_table() is called for both functions.
--
-- Konstantin Osipov Software Developer, Moscow, Russia
-- MySQL AB, www.mysql.com The best DATABASE COMPANY in the GALAXY
