MySQL Indexing – Fixing Invalid Default Value for Column

datatypesindexMySQL

I am working on a 10 year old web-app (!!!)
& currently running mysql locally, version 5.7.

This is the table I am currently working on:

CREATE TABLE `processes_history` (
  `p_id` bigint(20) UNSIGNED NOT NULL DEFAULT '0',
  `exec_id` bigint(20) UNSIGNED NOT NULL DEFAULT '0',
  `feature` varchar(100) NOT NULL DEFAULT '',
  `macro` tinyint(1) UNSIGNED NOT NULL DEFAULT '0',
  `ts` date NOT NULL DEFAULT '0000-00-00',
  `seen` int(10) UNSIGNED NOT NULL DEFAULT '1',
  `seen_time` bigint(20) UNSIGNED NOT NULL DEFAULT '0',
  `focus` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `focus_time` bigint(20) UNSIGNED NOT NULL DEFAULT '0',
  `mouse` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `keyboard` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `interactive` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `interactive_time` bigint(20) UNSIGNED NOT NULL DEFAULT '0',
  `last_seen` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=MyISAM DEFAULT CHARSET=utf8
PARTITION BY RANGE (TO_DAYS(`ts`))
(
PARTITION p0 VALUES LESS THAN (736695) ENGINE=MyISAM,
PARTITION p201701 VALUES LESS THAN (736726) ENGINE=MyISAM,
PARTITION p201702 VALUES LESS THAN (736754) ENGINE=MyISAM,
PARTITION p201703 VALUES LESS THAN (736785) ENGINE=MyISAM,
PARTITION p201704 VALUES LESS THAN (736815) ENGINE=MyISAM,
PARTITION p201705 VALUES LESS THAN (736846) ENGINE=MyISAM,
PARTITION p201706 VALUES LESS THAN (736876) ENGINE=MyISAM,
PARTITION p201707 VALUES LESS THAN (736907) ENGINE=MyISAM,
PARTITION p201708 VALUES LESS THAN (736938) ENGINE=MyISAM,
PARTITION p201709 VALUES LESS THAN (736968) ENGINE=MyISAM,
PARTITION p201710 VALUES LESS THAN (736999) ENGINE=MyISAM,
PARTITION p201711 VALUES LESS THAN (737029) ENGINE=MyISAM,
PARTITION p201712 VALUES LESS THAN (737060) ENGINE=MyISAM,
PARTITION p201801 VALUES LESS THAN (737091) ENGINE=MyISAM,
PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE=MyISAM
);

--
-- Indexes for dumped tables
--

--
-- Indexes for table `processes_history`
--
ALTER TABLE `processes_history`
  ADD PRIMARY KEY (`p_id`,`exec_id`,`feature`,`ts`),
  ADD KEY `ts` (`ts`),
  ADD KEY `exec_ts` (`exec_id`,`ts`),
  ADD KEY `last_seen` (`last_seen`);

I keep getting an error when adding an index to p_id, exec_id, ts:

ALTER TABLE `dbname`.`processes_history` ADD INDEX `p_id,exec_id,ts` (`p_id`, `exec_id`, `ts`);

Error SQL query:

ALTER TABLE dbname.processes_history ADD INDEX p_id,exec_id,ts
(p_id, exec_id, ts) MySQL said: Documentation

1067 – Invalid default value for 'ts'

Following this post: On Create Index, Invalid default value

From what I understood, using 0000-00-00 as a default value breaks the 'date' type, and that's why it's not working.

But I just couldn't understand what is the solution for this situation. Using TIMESTAMP type instead?

Is there a way to solve this problem without breaking the structure (for now at least) unit I finish the whole web-app? Many things are dependent on that table and i reeeealy don't want to do something risky to index it the way I want.

Best Answer

@jerichorivera is on point with the problem, I will just expand on what he is meaning.

From MySQL 5.6 to MySQL 5.7, MySQL changed the default SQL_Mode from a "loose" one to a stricter, standards-complient "MySQL strict mode". On it, dates like '0000-00-00' are not allowed, the first allowed data is '1000-01-01'. Other examples can be seen here: https://www.slideshare.net/jynus/query-optimization-with-mysql-57-and-mariadb-10-even-newer-tricks/45

If you want a quick fix now so the application unbreaks, you can reset the sql mode by setting it to NO_ENGINE_SUBSTITUTION, nothing will break and you will get the 5.6 behaviour.

However, you may want to eventually "fix" your schema- using '0000-00-00' for an invalid date is as bad as using the empty string ('') for an invalid string, or a variable with an uninitialized value in C. SQL has a value to represent invalid/unknown states, and that is NULL- my suggestion is to use it to represent invalid dates (could be done implicitly with the property NULL), however, you will have to work for the application to have into account this new "state" for the column, plus any other design supposition regarding ordering and comparison both on application and on database levels (e.g. supposing any comparison will always return true or false, and not a third state NULL).

If using NULL is not possible or desirable, you can use a special value within the range allowed, it is not best practice, but at least it is not mixed with invalid inputs.

Maybe you can drop the column if it is no longer in use?

Once you have fixed all database issues, the TRADITIONAL/Strict modes will give you less headaches in data consistency.

Related Question