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 :
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 TIMESTAMP
s – 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.
If possible, replace timestamp
with datetime
first. Then reindex.