Not the answer you need?
Register and ask your own question!

The problem of migration from MySQL 5.5 to Percona 5.6

Ivan_DIvan_D EntrantInactive User Role Participant
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

Comments

  • scott.nemesscott.nemes MySQL Sage Current User Role Advisor
    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:
    http://dev.mysql.com/doc/refman/5.6/...t-dynamic.html

    Limits (see third bullet under maximums and minimums):
    http://dev.mysql.com/doc/refman/5.6/...trictions.html

    -Scott
  • Ivan_DIvan_D Entrant Inactive User Role Participant
    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
  • scott.nemesscott.nemes MySQL Sage Current User Role Advisor
    Hi Ivan_D;

    Run:

    ALTER TABLE <your 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
  • Ivan_DIvan_D Entrant Inactive User Role Participant
    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 ? Attachment not found.
    P.S. I tried to create base in UTF8 and LATIN1 character
  • scott.nemesscott.nemes MySQL Sage Current User Role Advisor
    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
  • Ivan_DIvan_D Entrant Inactive User Role Participant
    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 :(
  • scott.nemesscott.nemes MySQL Sage Current User Role Advisor
    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
  • Ivan_DIvan_D Entrant Inactive User Role Participant
    Thanks Scott for your answers,
    Installation Percona version 5.5 has solved the problem about an error.
    Good luck!
  • scott.nemesscott.nemes MySQL Sage Current User Role Advisor
    Hi Ivan_D;

    Glad you got it working. =)

    -Scott
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.