Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

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

Closed
anonymous-matomo-user opened this issue Mar 25, 2011 · 6 comments
Closed

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

anonymous-matomo-user opened this issue Mar 25, 2011 · 6 comments
Labels
Bug For errors / faults / flaws / inconsistencies etc.
Milestone
1.3

Comments

@anonymous-matomo-user
Copy link

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
@robocoder
Copy link
Contributor

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

@robocoder
Copy link
Contributor

Ok, IGNORE didn't work.

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

@robocoder
Copy link
Contributor

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

@robocoder
Copy link
Contributor

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;

@robocoder
Copy link
Contributor

Thanks James!

@robocoder
Copy link
Contributor

(In [4187]) refs #2232 - typo

@anonymous-matomo-user anonymous-matomo-user added this to the Piwik 1.3 milestone Jul 8, 2014
@mattab mattab mentioned this issue Sep 9, 2014
Open
This issue was closed.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
Bug For errors / faults / flaws / inconsistencies etc.
Projects
None yet
Milestone
1.3
Development

No branches or pull requests
2 participants
@robocoder @anonymous-matomo-user