On Create Index, Invalid default value

Question

I am attempting to create an index

create index userLoginInfo on user_login_info(created_at);

on the following table

+-----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table           | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
+-----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user_login_info | CREATE TABLE `user_login_info` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_id` int(11) NOT NULL,
  `event_type` int(11) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `activity_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=261727 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+-----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

however, I am getting the following error

ERROR 1067 (42000): Invalid default value for 'activity_time'

Using Server Version

5.7.20 Homebrew

Any ideas on how I can create this index?

Answer 1

Try looking here!

The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.

You are exceeding the permitted ranges! Also, should activity_time be a duration of some sort? Maybe a begin_timestamp and end_timestamp might be an idea?

You should prefer TIMESTAMP to DATETIME, from here (Date and Time Type Storage Requirements), a timestamp without fractional seconds (i.e. most of them) requires only 4 bytes (as opposed to 5, but hey!). Also, 99% of dates that we deal with on a day to day basis will be between 1970 and 2038.

This part of the manual should help with initialisations.

Following the small oversight so kindly pointed out by @ypercubeᵀᴹ I had a look at this issue - i.e. of '0000-00-00 00:00:00' being a special "zero" value.

The fruits of my efforts are available here.

DDL:

CREATE TABLE `user_login_info` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_id` int(11) NOT NULL,
  `event_type` int(11) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  -- `activity_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',  -- FAILS
  `test_time1` TIMESTAMP NULL DEFAULT NULL,  -- WORKS
  -- `test_time2` TIMESTAMP DEFAULT '0000-00-00 00:00:00', -- FAILS
  -- `test_time3` TIMESTAMP NOT NULL DEFAULT 0,  -- FAILS
  -- `test_time35` TIMESTAMP NULL DEFAULT 0,
  `test_time4` TIMESTAMP NULL DEFAULT NULL,
  `test_time5` TIMESTAMP NOT NULL DEFAULT '1970-01-01, 00:00:01', -- FAILS with time = 00:00:00
  -- `test_dtime6` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',  -- FAILS
  `test_time6` TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP,
  -- `test_time7` TIMESTAMP NULL DEFAULT '0000-00-00 00:00:00',
  -- 'test_time8` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',  -- FAILS
  PRIMARY KEY (`id`),
  INDEX my_index (created_at) -- if the TABLE is CREATEd, the INDEX is!
) ENGINE=MyISAM;

It appears to me that there is a problem/bug with MySQL - in that it appears to confuse this special "zero" with the NULL value! It won't work with DATETIME either! I looked at the documentation again and can't appear to find why many of the failing constructs in my DDL won't work. There was a time when MySQL didn't work with multiple TIMESTAMPs - maybe this is relic of that epoch? I even tried SQLFiddle - which will build the schema but then fails - not sure why! I hope this clarifies matters unless @ypercubeᵀᴹ has a better suggestion! I found this helpful.

Answer 2

If possible, replace timestamp with datetime first. Then reindex.