/ Register

Learn about Centmin Mod LEMP Stack today
Become a Member

Downgrade mariadb 10.1.X to mysql 5.7.X

Discussion in 'Nginx, PHP-FPM & MariaDB MySQL' started by uzey, May 29, 2016.

Tags:
Previous Thread Next Thread
Loading...
  1. May 29, 2016 #1
    uzey

    uzey New Member

    16
    3
    3
    May 15, 2016
    Ratings:
    +5
    Local Time:
    6:59 PM
    1.9.15
    5.5
    Hi all,

    Just need simple/step by step on how to downgrade mariadb 10.1.X to mysql 5.7.x. Currently having trouble restoring joomla site using akeeba restore. Tested/restoring with the same version of mysql ok. FYI, i am testing it on my vbox using centos 7 + centminmod .09beta. Please advise. Thanks!
     
  2. May 29, 2016 #2
    pamamolf

    pamamolf Well-Known Member

    3,119
    295
    83
    May 31, 2014
    Ratings:
    +531
    Local Time:
    12:59 PM
    Nginx-1.13.x
    MariaDB 10.1.x
    Providing more details about "having trouble" will be more easy to help you :)

    For downgrading to mysql 7 don't know as Centminmod support MariaDB and at least for me never had any issues....
     
  3. May 29, 2016 #3
    eva2000

    eva2000 Administrator Staff Member

    37,351
    8,161
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +12,562
    Local Time:
    8:59 PM
    Nginx 1.15.x
    MariaDB 5.5/10.x
    how did you restore and backup the data ? mysqldump to sql and restore is only valid way between major mysql versions

    specific error messages or symptoms would help also check mysql error logs - see FAQ 19

    Currently Centmin Mod is MariaDB only so downgrading to MySQL 5.7 would introduce issues if you did not downgrade properly. Best to figure out how to get MariaDB 10.1 to work
     
  4. May 29, 2016 #4
    uzey

    uzey New Member

    16
    3
    3
    May 15, 2016
    Ratings:
    +5
    Local Time:
    6:59 PM
    1.9.15
    5.5
    This is the error I get when I want it to restore the joomla site via akeeba wizard.

    Database server error reply:

    ErrNo #0
    SQL=CREATE TABLE `dgcex_akeeba_common` ( `key` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `value` longtext COLLATE utf8mb4_unicode_ci NOT NULL, PRIMARY KEY (`key`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    Raw query text:

    CREATE TABLE `#__akeeba_common` ( `key` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `value` longtext COLLATE utf8mb4_unicode_ci NOT NULL, PRIMARY KEY (`key`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

    I have set the collation type to utfmb4. What else could it be wrong? The original site is using Mysql 5.7.x.

    Failed to execute SQL : SQL CREATE TABLE `#__akeeba_common` ( `key` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `value` longtext COLLATE utf8mb4_unicode_ci NOT NULL, PRIMARY KEY (`key`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci failed : Index column size too large. The maximum column size is 767 bytes.

    Can i change the max col size?
     
    Last edited: May 29, 2016
  5. May 29, 2016 #5
    eva2000

    eva2000 Administrator Staff Member

    37,351
    8,161
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +12,562
    Local Time:
    8:59 PM
    Nginx 1.15.x
    MariaDB 5.5/10.x
  6. May 29, 2016 #6
    eva2000

    eva2000 Administrator Staff Member

    37,351
    8,161
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +12,562
    Local Time:
    8:59 PM
    Nginx 1.15.x
    MariaDB 5.5/10.x
    maybe Support

    and Support

     
  7. May 29, 2016 #7
    eva2000

    eva2000 Administrator Staff Member

    37,351
    8,161
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +12,562
    Local Time:
    8:59 PM
    Nginx 1.15.x
    MariaDB 5.5/10.x
    check you have innodb_large_prefix enabled and file format = Barracuda which is default enabled in Centmin Mod 123.09beta01's MariaDB 10.1 config
    Code (Text):
    mysqladmin var | egrep 'innodb_large_prefix|innodb_file_format ' | tr -s ' '
| innodb_file_format | Barracuda |
| innodb_large_prefix | ON |

    then edit the sql backup file's table create lines to add
    Code (Text):
    ROW_FORMAT=DYNAMIC

    see Using innodb_large_prefix to avoid ERROR 1071 - Mechanics of Flite

    Basically, the akeeba wizard wasn't smart enough to backup to sql the tables with ROW_FORMAT=DYNAMIC when charset/collation is set to utf8mb4. Strange though MySQL 5.7 also requires DYNAMIC row_format set so should of been part of your table create backup anyway if original server also used utf8mb4 and was working.
     
    Last edited: May 29, 2016
Previous Thread Next Thread
Loading...
..

.