Skip to content


DB migration to 1.2.1 fails on log_link_visit_action with STRICT mysql & NO_ZERO* options #2232

anonymous-piwik-user opened this Issue · 6 comments

2 participants



Database migration fails on the log_link_visit_action table if MySQL is configured with options in "STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE"


Steps to Reproduce

Create the log_link_visit_action table from 1.1.1:

CREATE TABLE IF NOT EXISTS "piwik_log_link_visit_action" (
  "idlink_va" int(11) NOT NULL AUTO_INCREMENT,
  "idvisit" int(10) unsigned NOT NULL,
  "idaction_url" int(10) unsigned NOT NULL,
  "idaction_url_ref" int(10) unsigned NOT NULL,
  "idaction_name" int(10) unsigned DEFAULT NULL,
  "time_spent_ref_action" int(10) unsigned NOT NULL,
  PRIMARY KEY ("idlink_va"),
  KEY "index_idvisit" ("idvisit")

Populate it with a few sample values:

INSERT INTO `piwik_log_link_visit_action` (`idlink_va`, `idvisit`, `idaction_url`, `idaction_url_ref`, `idaction_name`, `time_spent_ref_action`)
    (1, 1, 1, 0, NULL, 0),
    (2, 2, 1, 0, NULL, 0),
    (3, 3, 1, 0, NULL, 0),
    (4, 3, 2, 1, NULL, 10),
    (5, 3, 3, 2, NULL, 30),
    (6, 3, 4, 3, NULL, 42),
    (7, 4, 1, 0, NULL, 0),
    (8, 1, 1, 1, NULL, 539),
    (9, 5, 1, 0, NULL, 0),
    (10, 5, 5, 1, NULL, 276),
    (11, 5, 3, 5, NULL, 117);

Run the ALTER command that will update that table to 1.2.1:

ALTER TABLE `piwik_log_link_visit_action`
   ADD `idsite` INT( 10 ) UNSIGNED NOT NULL AFTER `idlink_va` , 
   ADD `server_time` DATETIME NOT NULL AFTER `idsite`,
   ADD `idvisitor` BINARY(8) NOT NULL AFTER `idsite`,
   ADD `idaction_name_ref` INT UNSIGNED NOT NULL AFTER `idaction_name`,
   ADD INDEX `index_idsite_servertime` ( `idsite` , `server_time` )

Expect to See

No errors, successful migration.

Actually Saw

Error message and upgrade aborted with tables in half-converted state:

"SQL Error (1292): Incorrect datetime value: '0000-00-00 00:00:00' for column 'server_time' at row 1"

Possible Solution

Let the column server_time be NULL.

MySQL details

The STRICT_ settings in MySQL disallow invalid date values to be entered. The NO_ZERO_ settings prevent dates in the form of 0000:00:00 00:00:00 from being in the table.

Versions of MySQL this appears on: 5.1.39 and 5.1.53


What if we added the IGNORE keyword to the ALTER statement(s)?


Ok, IGNORE didn't work.

Agree with removing the NOT NULL constraint in the initial ALTER, but after the UPDATE, we add the constraint.


(In [4186]) fixes #2232 - for anyone else upgrading from pre-1.2.1


I guess this is an inconsistency in MySQL, but despite sql_mode='STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE', this filled the server_time column with '0000-00-00 00:00:00':

ALTER TABLE `piwik_log_link_visit_action` ADD `server_time` DATETIME NOT NULL AFTER `idsite`;

ALTER IGNORE TABLE `piwik_log_link_visit_action` CHANGE `server_time` `server_time` DATETIME NOT NULL;

Thanks James!


(In [4187]) refs #2232 - typo

@anonymous-piwik-user anonymous-piwik-user added this to the Piwik 1.3 milestone
This issue was closed.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.