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


(Matthieu Aubry) #2

Hi, if anybody experiences this error again please feel free to create a bug report on: https://github.com/piwik/piwik/issues
as we didn’t hear other reports so far?