@Geddo opened this Issue on February 2nd 2022

Expected Behavior

Updating to 4.7.0-b2 via Webupdater shouldnt be a thing

Current Behavior

Fatal error:
matomo/core/Updates/4.7.0-b2.php: Error trying to execute the migration 'ALTER TABLEmatomo_changesADD UNIQUE KEY unique_plugin_version_title (plugin_name,version,title);'. The error was: SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes

Possible Solution

I guess it works with MySQL 5.7, but as 5.5 is still supported in general, it should work

@MatomoForumNotifications commented on February 2nd 2022

This issue has been mentioned on Matomo forums. There might be relevant details there:

https://forum.matomo.org/t/4-7-0-b2-update-fails-with-mysql-5-5/44603/1

@prbt2016 commented on February 2nd 2022

Hello ,

I am also facing issue when I try a fresh install of Matomo 4.7.0 on PHP 7.2, MYSQL 5.5, Apache 2.2.

An error occurred :

image

The same issue occurs when I tested on another server with MYSQL 5.6.

However , script works fine on MYSQL 5.7.

On further inspection found that {$tableprefix}changes table is not getting created on MYSQL 5.5 and MYSQL 5.6 due to MYSQL index size limit exceeding 767 bytes :

https://dev.mysql.com/doc/refman/5.6/en/innodb-limits.html

Could you please replicate this issue at your end and fix this?

@EmbDclic commented on February 2nd 2022

I also confirm this problem with the last update with MySQL 5.6:

/home/[user]/matomo/core/Updates/4.7.0-b2.php: Error trying to execute the migration 'ALTER TABLE matomo_changes ADD UNIQUE KEY unique_plugin_version_title (plugin_name, version, title);'. The error was: SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes

I can't change the value of innodb_large_prefix to 1 (I don't have super privileges on this database) and I can't update to version 5.7 either.

@sgiehl commented on February 2nd 2022 Member

Thanks for the report. We will try to create a fix for this soon.

@MatomoForumNotifications commented on February 2nd 2022

This issue has been mentioned on Matomo forums. There might be relevant details there:

https://forum.matomo.org/t/php-version-supported/44616/4

@MatomoForumNotifications commented on February 2nd 2022

This issue has been mentioned on Matomo forums. There might be relevant details there:

https://forum.matomo.org/t/error-4-6-2-4-7-1071-specified-key-was-too-long-max-key-length-is-767-bytes/44608/11

@sgiehl commented on February 2nd 2022 Member

The not working index can be ignored, if Matomo still works after the error

@MatomoForumNotifications commented on February 2nd 2022

This issue has been mentioned on Matomo forums. There might be relevant details there:

https://forum.matomo.org/t/critical-error-while-upgrading-to-4-7-0/44607/8

@EmbDclic commented on February 2nd 2022

The not working index can be ignored, if Matomo still works after the error

Thanks for your feedback. It doesn't, it asks to update again. Should I execute the MySQL commands of this update (with the exception of the index command)?

@sgiehl commented on February 2nd 2022 Member

@EmbDclic Yes. Makes sense. We will fix that with another update later.

@mcguffin commented on February 2nd 2022

As a temporary workaround for MySQL 5.5 I executed (reference):

ALTER TABLE `piwik_changes` ADD UNIQUE KEY unique_plugin_version_title (plugin_name(128), `version`, title(128));
@sgiehl commented on February 2nd 2022 Member

@mcguffin That will only work when not using utf8mb4.
We will most likely change the plugin_name to a length of 40 and limit the title in the key to 131 or something similar to make that work on all systems.

@Razique commented on February 3rd 2022

Chiming here in case you encountered the error.
This is what I did to solve the issue (as root into my DB instance)

SET @<a class='mention' href='https://github.com/global'>@global</a>.innodb_large_prefix = 1;

This enable large prefixes as a global setting:

When this option is enabled, index key prefixes longer than 767 bytes (up to 3072 bytes) are allowed for InnoDB tables that use DYNAMIC or COMPRESSED row format. See Section 14.23, “InnoDB Limits” for maximums associated with index key prefixes under various settings.

@NicoHood commented on April 5th 2022

Hey guys,
I also ran into this issue when 4.7.0 was released. However I am stuck now at this point, as the upgrade will not work anymore. It looks like 4.7.1 fixes the issue, but matomo wants to upgrade the database first. So I am stuck now.

What is the best way to upgrade now from this point?

@Razique commented on April 6th 2022

@NicoHood did you try to enable support for large prefixes?

@NicoHood commented on April 6th 2022

I am on a shared hosting, I am not sure if I am allowed to do that. At least I don't know how to do that.

@sgiehl commented on April 6th 2022 Member

@NicoHood Doesn't the database update work now? The files should have been adjusted so the incorrect db update shouldn't be included anymore. So if you update to Matomo 4.7.1 or 4.8.0 it should work without problems.

@NicoHood commented on April 6th 2022

This might be true, but I am stuck in the updater. The updater is in the process to update the db with 4.7.0 files. I am unable to access anything of the ui, it is a deadlock.

@sgiehl commented on April 6th 2022 Member

In that case you can follow the steps to do a manual update https://matomo.org/faq/on-premise/update-matomo/#the-manual-three-step-update

Or if you still want to use the automatic update you can remove this line from core/Updates/4.7.0-b2.php:

$migrations[] = $this->migration->db->addUniqueKey('changes', ['plugin_name', 'version', 'title'], 'unique_plugin_version_title');
@NicoHood commented on April 6th 2022

Thanks! I've edited the file and it worked!

This Issue was closed on February 3rd 2022
Powered by GitHub Issue Mirror