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 INDEXp_id,exec_id,ts
(p_id
,exec_id
,ts
) MySQL said: Documentation1067 - 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.
ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, and NO_ENGINE_SUBSTITUTION.
set. and on 5.6: justNO_ENGINE_SUBSTITUTION
- on the productions server (MySQL version 5.5) has nothing in sql_mode set!... if i set my local MySQL 5.7 to.. lets sayNO_ENGINE_SUBSTITUTION
as 5.6 is set, will this cause any problems? (mysql 5.6 manual dev.mysql.com/doc/refman/5.6/en/sql-mode.html#sql-mode-setting)