title: Indexing: Invalid default value for '[column]'
body: <p>I am working on a 10 year old web-app (!!!)
  &amp; currently running mysql locally, version 5.7.</p>
  <p>This is the table I am currently working on:</p>
  <pre><code>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`);
  </code></pre>
  <p>I keep getting an error when adding an index to <code>p_id, exec_id, ts</code>:</p>
  <pre><code>ALTER TABLE `dbname`.`processes_history` ADD INDEX `p_id,exec_id,ts` (`p_id`, `exec_id`, `ts`);
  </code></pre>
  <blockquote>
  <p>Error SQL query:</p>
  <p>ALTER TABLE <code>dbname</code>.<code>processes_history</code> ADD INDEX <code>p_id,exec_id,ts</code>
  (<code>p_id</code>, <code>exec_id</code>, <code>ts</code>) MySQL said: Documentation</p>
  <h1>1067 - Invalid default value for 'ts'</h1>
  </blockquote>
  <p>Following this post: <a href="https://dba.stackexchange.com/questions/192186/on-create-index-invalid-default-value">https://dba.stackexchange.com/questions/192186/on-create-index-invalid-default-value</a></p>
  <p>From what I understood, using <code>0000-00-00</code> as a default value breaks the 'date' type, and that's why it's not working.</p>
  <p>But I just couldn't understand what is the solution for this situation. Using TIMESTAMP type instead?</p>
  <p>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.</p>