#26 Database creation fails with Error (1067)

[Janek Bogucki]

htcheck -i fails for me with this output,

$ htcheck -i
Error (1067): Invalid default value for 'StartTime'
! htcheck: Database error

This is a shortened version of the table creation statement that demonstrates the problem,

mysql> CREATE TABLE htcheck.htCheck (
-> StartTime DATETIME DEFAULT '0000-00-00 00:00:00' NOT NULL);
ERROR 1067 (42000): Invalid default value for 'StartTime'

This my MySQL version and sql_mode,

mysql> select version(), @@sql_mode\G
*************************** 1. row ***************************
version(): 5.0.51a-3ubuntu5.4-log
@@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)

The statement succeeds when sql_mode is set to '',

mysql> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE htcheck.htCheck ( StartTime DATETIME DEFAULT '0000-00-00 00:00:00' NOT NULL);
Query OK, 0 rows affected (0.01 sec)

If htcheck relies on a particular sql_mode it should set it before starting work.

(I tried adding sql_mode= in ~/.my.cnf under the [htcheck] group but it did not work for me. See http://rpbouman.blogspot.com/2007_04_01_archive.html\)