MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:Konstantin Osipov Date:February 6 2008 2:31pm
Subject:Re: bk commit into 5.1 tree (anozdrin:1.2663) BUG#18834
View as plain text  
* 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

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

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
bk commit into 5.1 tree (anozdrin:1.2663) BUG#18834Alexander Nozdrin4 Feb
  • Re: bk commit into 5.1 tree (anozdrin:1.2663) BUG#18834Konstantin Osipov6 Feb