Opened 3 years ago

Closed 3 years ago

Last modified 3 years ago

#2232 closed Bug (fixed)

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

Reported by: jamesvl011 Owned by:
Priority: normal Milestone: Piwik 1.3
Component: Core Keywords:
Cc: Sensitive: no

Description

Summary

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"

Details

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`)
VALUES
	(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

Change History (8)

comment:1 Changed 3 years ago by vipsoft (robocoder)

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

comment:2 Changed 3 years ago by vipsoft (robocoder)

Ok, IGNORE didn't work.

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

comment:3 Changed 3 years ago by vipsoft (robocoder)

  • Resolution set to fixed
  • Status changed from new to closed

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

comment:4 Changed 3 years ago by vipsoft (robocoder)

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;

comment:5 Changed 3 years ago by vipsoft (robocoder)

  • Milestone changed from 1.x - Piwik 1.x to 1.3 - Piwik 1.3

comment:6 Changed 3 years ago by vipsoft (robocoder)

Thanks James!

comment:7 Changed 3 years ago by matt (mattab)

  • Summary changed from DB migration to 1.2.1 fails on log_link_visit_action to DB migration to 1.2.1 fails on log_link_visit_action with STRICT mysql & NO_ZERO* options

comment:8 Changed 3 years ago by vipsoft (robocoder)

(In [4187]) refs #2232 - typo

Note: See TracTickets for help on using tickets.