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?

• Jeffrey L. Roberts: It was imported, I am asking now what version created the dump...
• Jeffrey L. Roberts: it has something to do with sql_modes...
• Jeffrey L. Roberts: I was able to run the statement after executing SET sql_mode = '';
• Vérace: @JeffreyL.Roberts - setting sql_mode='' with MySQL is nothing short of madness - you'll get non-standard (i.e. wrong) behaviour (with GROUP BY for example).

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 @ypercubeTM 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 @ypercubeTM has a better suggestion! I found this helpful.

• Vérace: @ypercubeTM - I looked at these issues (thanks for the heads up!) and you are correct in the first instance - the documentation seems to indicate that what the OP was trying to do should work, but it doesn't! I think there's some problem with 0000-00-00 00:00:00 being taken for NULL? As for the INDEX not being created, this works no problem if the table can be created. Difficult to put an INDEX on a table that's not been created? :-)
• Vérace: If you use sql_mod = '', I'm sure that the server could get the sun to rise in the west! I used to get caught on the GROUP BY nonsense! Unfortunately, in biology (my area), many people have used MySQL when they should have used PostgreSQL and are now paying the price - it's a bit like a dysfunctional relationship - easy to fall into, hard to get out of! Lesson learnt! :-(

Answer 2

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

• Bimal Poudel: First, the issue here is in the same column - activity_time. Ideally, you should have have only one timestamp per table auto initialized, because that records when your record was added or updated. dev.mysql.com/doc/refman/5.5/en/timestamp-initialization.html and dev.mysql.com/doc/refman/5.5/en/datetime.html will let you know that the default value provided is out of range.