The problem of migration from MySQL 5.5 to Percona 5.6

I read as how wonderful Percona server and decided to migrate to it.
From MySQL Server 5.5.43 to Percona Server 5.6.24-72.2 on CentOS 6.6 x86 (Linux 2.6.32-042stab108.2 on i686)
But osTicket and Piwik stopped working, showing error:
ERROR 1071 (42000) at line 49: Specified key was too long; max key length is 768 bytes
I read that the following parameters have to help, but in vain
innodb_large_prefix = 1 & innodb_file_format = Barracuda & innodb_file_per_table = 1
Full configuration file in the attachment.
Please help, I do not want to go back to MySQL.
Best regards, Ivan

my.cnf.zip (869 Bytes)

Hi Ivan_D;

The innodb_large_prefix option only applies to InnoDB Barracuda tables with a row_format of DYNAMIC or COMPRESSED, so that is probably why you are still getting the error. So if that is the case (you can verify by doing a SHOW TABLE STATUS on your table(s)), then you just need to run an ALTER TABLE on your table(s) to set the file format to Barracuda and row_format to either DYNAMIC or COMPRESSED.

See the below link for information about both row formats:
[URL=“http://dev.mysql.com/doc/refman/5.6/en/innodb-row-format-dynamic.html”]http://dev.mysql.com/doc/refman/5.6/...t-dynamic.html[/URL]

Limits (see third bullet under maximums and minimums):
[URL=“http://dev.mysql.com/doc/refman/5.6/en/innodb-restrictions.html”]http://dev.mysql.com/doc/refman/5.6/...trictions.html[/URL]

-Scott

Thanks for the answer, Scott
I’m not a programmer and do not fully understand what you mean.
Tell that to add in my.cnf that would work like MySQL, because there a problem after migrating to Percona

Hi Ivan_D;

Run:

ALTER TABLE ROW_FORMAT=COMPRESSED;

That will set the table’s row_format to compressed, and if you have Barracuda as the default innodb_file_format as listed above, then it should work. After that see if you get the same error.

-Scott

I’m trying to reinstall Piwik on clean database using the wizard. There is no table in the database. But I get an error.
Maybe I should set compressed behavior as default behavior of current database or server generally ? [ATTACH=CONFIG]n39457[/ATTACH]
P.S. I tried to create base in UTF8 and LATIN1 character

Hi Ivan_D;

Ah, did not realize this was a fresh installation. Doing some Google searching, it appears this is a common issue with that application and MySQL 5.6. What I would do is modify the schema config that comes with the application so that it will load the tables properly. To do that, it looks like you can edit the file:

/piwik/core/Db/Schema/Mysql.php

In there add ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED to each one of the CREATE TABLE statements before the ENGINE piece, like:[


$tables = array(
'user' => "CREATE TABLE {$prefixTables}user (
login VARCHAR(100) NOT NULL,
password CHAR(32) NOT NULL,
alias VARCHAR(45) NOT NULL,
email VARCHAR(100) NOT NULL,
token_auth CHAR(32) NOT NULL,
superuser_access TINYINT(2) unsigned NOT NULL DEFAULT '0',
date_registered TIMESTAMP NULL,
PRIMARY KEY(login),
UNIQUE KEY uniq_keytoken(token_auth)
) ROW_FORMAT=DYNAMIC ENGINE=$engine DEFAULT CHARSET=utf8

After that, assuming that’s the right file, the install should work if it uses your updated create table statements.

-Scott

I made changes in Mysql.php, tried both ROW_FORMAT=DYNAMIC and ROW_FORMAT=COMPRESSED, but received the same error message.
I think editing third-party code is not rational.
Soon I will try MariaDB :frowning:

Hi Ivan_D;

There is nothing wrong with MySQL/Percona, it’s just telling you what its limits are, and the application you are attempting to install is going over those limits by default.

From what I’ve read it sounds like your application should work on MySQL/Percona 5.5, so if you want a plug-and-play method, you might want to try that.

-Scott

Thanks Scott for your answers,
Installation Percona version 5.5 has solved the problem about an error.
Good luck!

Hi Ivan_D;

Glad you got it working. =)

-Scott