Update 3.2 to 3.6 fails due to BLOB/TEXT columns can’t have defaults?!


(Volker Benders) #1

Hi,

i’m currently trying to upgrade a piwik 3.2.0 instance to 3.6.0 using online update.
(MySQL is 5v5 on debian 8v11)

This fails with this message

/var/www/xxx.yyy.de/3.2.0/core/Updates/3.5.0-b2.php: Error trying to execute the migration 'ALTER TABLE `piwik_privacy_logdata_anonymizations` ADD INDEX index_job_start_date (`job_start_date`);'. The error was: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'piwik.piwik_privacy_logdata_anonymizations' doesn't exist

OK, table can not be altered. Why? Because a previous create step failed with this message

Error: BLOB/TEXT column 'unset_visit_columns' can't have a default value
SQLState:  42000
ErrorCode: 1101

OK, BLOB/TEXT columns can’t have defaults.
But why is nobody else blocked by this?

There seems to be a workaround using triggers (i’m not allowed to add the corresponding link due to the fact i’m a new user. (With this kind of policy it maybe difficult to state a usefull question without references what i already did)

The create statement:

CREATE TABLE `piwik_privacy_logdata_anonymizations` (`idlogdata_anonymization` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, `idsites` TEXT NULL DEFAULT NULL, `date_start` DATETIME NOT NULL, `date_end` DATETIME NOT NULL, `anonymize_ip` TINYINT(1) UNSIGNED NOT NULL DEFAULT 0, `anonymize_location` TINYINT(1) UNSIGNED NOT NULL DEFAULT 0, `anonymize_userid` TINYINT(1) UNSIGNED NOT NULL DEFAULT 0, `unset_visit_columns` TEXT NOT NULL DEFAULT '', `unset_link_visit_action_columns` TEXT NOT NULL DEFAULT '', `output` MEDIUMTEXT NULL DEFAULT NULL, `scheduled_date` DATETIME NULL, `job_start_date` DATETIME NULL, `job_finish_date` DATETIME NULL, `requester` VARCHAR(100) NOT NULL DEFAULT '', PRIMARY KEY ( `idlogdata_anonymization` )) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Altering sql_mode according to

didn’t change anything.

I couldn’t find a solution to this problem in this forum and the inet (using popular search engines)
What would be the next step? Did you encounter the same issue?

Thank you,
Volker