4.7.0-b2 Update fails with MySQL 5.5

Support & Bugs
1

Hi guys, I already posted this on github:

Webupdate failed for me from Matomo 4.6 to 4.7 as I’m using MySQL 5.5. I upgraded to MySQL 5.7 and now it works :slight_smile:

2

Hi, what can I do if I’m not able to upgrade my database (I’m facing this issue with MySQL 5.6) ?
For information, the request is slightly different:

Error trying to execute the migration 'ALTER TABLE `piwik_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

As far as I know, it’s related to MySQL versions prior to 5.7 (via StackOverflow)

3

Hi !

I found a solution by changing the limit length of the key by executing a simple SQL command.

You need to connect to your MYSQL using CLI

MYSQL -u <user> -p

Then select your database and execute this command :

USE matomo //here, matomo is the database name, change it if needed

Then execute this command line to increase limitation

SET @@global.innodb_large_prefix = 1;

And back to your Matomo installation and run the DB update !
It solved the problem :slight_smile:

Ronan HELLO

1 Like
4

Thanks for your reply.
Unfortunately, I don’t have the right to do that on my hosting:

#1227 - Access denied; you need (at least one of) the SUPER privilege(s) for this operation
5

So maybe you can download your database ?
Run it on a local environment with all privileges and try again to fix it ?
Then you could easily upload your updated database to your web host :wink:

It required a local environnement such as WAMP on windows or MAMP for mac users
Ronan

6

I’ll give that a try.
But I fear that such changes to database schema can’t be restored from a local backup. (The modification is restricted by my server, thus, if it’s included in a local backup, the server can reject it anyway…)

7

What I did… changed the varchar length of plugin_name & title fields to 100 instead of 255 and that worked, maybe not the nicest way but it does the job, then MySQL can create that key.

1 Like
8

Super it’s work.
I 'll never imagine to decrease these values

Thanks a lot

9

If you are stuck in a broken state until this gets patched and can’t roll back, it seems like you should be able to update the columns used in that key so the composite is less than 767 bytes because (in our installation) after the upgrade the table is empty. Assuming you are using utf8mb4, changing plugin_name and title to varchar(85) will get you under 767.

ALTER TABLE matomo_changes MODIFY plugin_name VARCHAR(85);

Maybe a motomo dev can advise on how that table is being used and if this quick fix would work.

10

@alexdryden
You can try out this potential fix here:

It does exactly what you are suggesting (decreasing the size of the plugin column).

An update that fixes this issue should be released soon.

1 Like
11

Thanks for the tip, @alexdryden , it did work nicely.
(Looks like the Matomo dev folks are suggesting varchar(60), but 85 sounds as good as 42 :wink:
Thanks again.