Bug #27507 | Out of range value accepted for new NOT NULL datetime column in existing table | ||
---|---|---|---|
Submitted: | 28 Mar 2007 22:15 | Modified: | 18 Jun 2007 17:50 |
Reporter: | Ed Dawley | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: Data Types | Severity: | S3 (Non-critical) |
Version: | 5.0.40-BK, 5.0.33 | OS: | Linux (Linux) |
Assigned to: | Evgeny Potemkin | CPU Architecture: | Any |
[28 Mar 2007 22:15]
Ed Dawley
[29 Mar 2007 5:52]
Valeriy Kravchuk
Thank you for a bug report. Verified just as described: openxs@suse:~/dbs/5.0> bin/mysql -uroot test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.0.40 Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> set sql_mode="no_zero_in_date,strict_all_tables"; Query OK, 0 rows affected (0.00 sec) mysql> select @@sql_mode; +-----------------------------------+ | @@sql_mode | +-----------------------------------+ | STRICT_ALL_TABLES,NO_ZERO_IN_DATE | +-----------------------------------+ 1 row in set (0.01 sec) mysql> create table ed (id int unsigned) engine=innodb; Query OK, 0 rows affected (0.01 sec) mysql> insert into ed set id=1; Query OK, 1 row affected (0.00 sec) mysql> alter table ed add column blah datetime not null; Query OK, 1 row affected (0.02 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> select * from ed; +------+---------------------+ | id | blah | +------+---------------------+ | 1 | 0000-00-00 00:00:00 | +------+---------------------+ 1 row in set (0.00 sec) mysql> alter table ed engine=MyISAM; Query OK, 1 row affected (0.02 sec) Records: 1 Duplicates: 0 Warnings: 0 So, ALTER is not giving errors even with STRICT_ALL_TABLES in this case, but still, with NO_ZERO_IN_DATE first ALTER TABLE should NOT be allowed.
[21 May 2007 13:24]
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/commits/27069 ChangeSet@1.2493, 2007-05-21 17:20:58+04:00, evgen@moonbone.local +3 -0 Bug#27507: Wrong DATETIME value was allowed by ALTER TABLE in the NO_ZERO_DATE mode. When new DATE/DATETIME field without default value is being added by the ALTER TABLE the '0000-00-00' value is used as the default one. But it wasn't checked whether such value is allowed by the current sql mode. Due to this '0000-00-00' values was allowed for DATE/DATETIME fields even in the NO_ZERO_DATE mode. Now the mysql_alter_table() function checks whether the '0000-00-00' value is allowed for DATE/DATETIME fields by the current sql mode. The new error_if_not_empty flag is used in the mysql_alter_table() function to indicate that it should abort if the table being altered isn't empty. The new new_datetime_field field is used in the mysql_alter_table() function for error throwing purposes. The new error_if_not_empty parameter is added to the copy_data_between_tables() function to indicate the it should return error if the source table isn't empty.
[21 May 2007 20:25]
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/commits/27100 ChangeSet@1.2496, 2007-05-22 00:22:53+04:00, evgen@moonbone.local +3 -0 Bug#27507: Wrong DATETIME value was allowed by ALTER TABLE in the NO_ZERO_DATE mode. When a new DATE/DATETIME field without default value is being added by the ALTER TABLE the '0000-00-00' value is used as the default one. But it wasn't checked whether such value was allowed by the set sql mode. Due to this '0000-00-00' values was allowed for DATE/DATETIME fields even in the NO_ZERO_DATE mode. Now the mysql_alter_table() function checks whether the '0000-00-00' value is allowed for DATE/DATETIME fields by the set sql mode. The new error_if_not_empty flag is used in the mysql_alter_table() function to indicate that it should abort if the table being altered isn't empty. The new new_datetime_field field is used in the mysql_alter_table() function for error throwing purposes. The new error_if_not_empty parameter is added to the copy_data_between_tables() function to indicate the it should return error if the source table isn't empty.
[4 Jun 2007 21:20]
Bugs System
Pushed into 5.1.20-beta
[4 Jun 2007 21:22]
Bugs System
Pushed into 5.0.44
[18 Jun 2007 17:50]
Paul DuBois
Noted in 5.0.44, 5.1.20 changelogs. When ALTER TABLE was used to add a new DATE column with no explicit default value, '0000-00-00' was used as the default even if the SQL mode included the NO_ZERO_DATE mode to prohibit that value. A similar problem occurred for DATETIME columns.