/ Register

Learn about Centmin Mod LEMP Stack today
Become a Member

MariaDB Index column size too large. The maximum column size is 767 bytes.

Discussion in 'Nginx, PHP-FPM & MariaDB MySQL' started by pamamolf, Dec 3, 2016.

Tags:
Previous Thread Next Thread
Loading...
  1. Dec 3, 2016 #1
    pamamolf

    pamamolf Well-Known Member

    3,076
    293
    83
    May 31, 2014
    Ratings:
    +524
    Local Time:
    11:17 PM
    Nginx-1.13.x
    MariaDB 10.1.x
    Hello :)

    Is there a way to have a permanent entry on my.cnf file to avoid this error when i try to convert from MyISAM to InnoDB ?

    Code:
    ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.
    Thank you :)
     
  2. Dec 3, 2016 #2
    hendry

    hendry Member

    59
    13
    8
    Sep 19, 2016
    Ratings:
    +17
    Local Time:
    3:17 AM
    nginx/1.11.4
    MariaDB 10
  3. Dec 3, 2016 #3
    eva2000

    eva2000 Administrator Staff Member

    33,705
    7,465
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +11,476
    Local Time:
    6:17 AM
    Nginx 1.13.x
    MariaDB 5.5
    Centmin Mod is provide as is, so short of scripted related bugs or issues, any further optimisation to the web stack components - nginx, php-fpm, mariadb mysql, csf firewall etc or web app specific configurations are left to the Centmin Mod user to deal with. So I do not provide any free support for such.

    However, Centmin Mod users are free to help each other out and ask questions or give answers on this community forum. My hopes are that this community forum evolves so that more veteran long time Centmin Mod users help new Centmin Mod users out :)

    Are you using latin1, utf8 or utf8mb4 charset for MariaDB MySQL. If using utf8mb4 read official documentation for why you need to carefully go through all databases and ensure the max key prefix index sizes are within InnoDB limits outlined at MySQL :: MySQL 5.6 Reference Manual :: 14.8.8 Limits on InnoDB Tables

    Latest 123.09beta01 already adds to /etc/my.cnf the relevant settings to support larger InnoDB index prefixes as outlined at MySQL :: MySQL 5.6 Reference Manual :: 10.1.9.8 Converting Between 3-Byte and 4-Byte Unicode Character Sets
    Code (Text):
    egrep 'format|large' /etc/my.cnf | uniq  
innodb_large_prefix=1
innodb_file_format = Barracuda


    Again, only do this if you're know what you're doing and always backup all mysql databases on server before hand.
     
    • Informative Informative x 1
  4. Dec 3, 2016 #4
    pamamolf

    pamamolf Well-Known Member

    3,076
    293
    83
    May 31, 2014
    Ratings:
    +524
    Local Time:
    11:17 PM
    Nginx-1.13.x
    MariaDB 10.1.x
    Yes it is for utf8mb4....
     
  5. Dec 25, 2016 #5
    JoeDer

    JoeDer New Member

    15
    5
    3
    Feb 22, 2015
    Ratings:
    +11
    Local Time:
    11:17 PM
    Nginx 1.13.x
    MariaDB 10.1.x
    I have the same error when I try to install vBulletin 5.2.5 after all the necessary steps on a fresh 09beta01 installation.
    Screen Shot 2016-12-25 at 15.24.04.png Screen Shot 2016-12-25 at 15.29.59.png
     
    Last edited: Dec 25, 2016
  6. Dec 25, 2016 #6
    eva2000

    eva2000 Administrator Staff Member

    33,705
    7,465
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +11,476
    Local Time:
    6:17 AM
    Nginx 1.13.x
    MariaDB 5.5
    best to follow the instructions outlined to 'contact vBulletin support for assistance' as they would best know the index adjustments for utf8mb4 support (if they support it)
     
    • Like Like x 1
  7. Mar 9, 2017 #7
    the caretaker

    the caretaker New Member

    14
    3
    3
    Nov 11, 2015
    Ratings:
    +4
    Local Time:
    6:17 AM
    1.9.6
    MariaDB 10
    Hi Open the file upload / core / install mysql-schema.php

    And change the value ENGINE = $innodb in $MyISAM
    Search then worth $innodb and replace all in $MyISAM
     
    • Like Like x 2
  8. Mar 20, 2017 #8
    the caretaker

    the caretaker New Member

    14
    3
    3
    Nov 11, 2015
    Ratings:
    +4
    Local Time:
    6:17 AM
    1.9.6
    MariaDB 10
    Hi eva2000 the problem is still available!
    Grade on fresh install without mod tested
    Server version: 5.5.52-MariaDB MariaDB Server
    There is no error

    With mod on another test server
    Again the message
    ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.

    Think it has to do with your cnf of mysql
     
  9. Mar 20, 2017 #9
    eva2000

    eva2000 Administrator Staff Member

    33,705
    7,465
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +11,476
    Local Time:
    6:17 AM
    Nginx 1.13.x
    MariaDB 5.5
    nothing wrong with MariaDB it's your web app's mysql data structure when using innodb + utf8mb4

    and MySQL :: MySQL 5.6 Reference Manual :: 10.1.9.8 Converting Between 3-Byte and 4-Byte Unicode Character Sets
     
    Last edited: Mar 20, 2017
    • Like Like x 1
  10. Mar 21, 2017 #10
    the caretaker

    the caretaker New Member

    14
    3
    3
    Nov 11, 2015
    Ratings:
    +4
    Local Time:
    6:17 AM
    1.9.6
    MariaDB 10
    There must be a difference Yes in the cnf (clearly there's a difference logical white in de cnf) or setting! If a reinstallation of the server and 5.5.52-MariaDB MariaDB server of error (ERROR 1709 (HY000) not thrown. Then it must be somewhere on a setting. Also change the database structure bring nothing
     
  11. Mar 21, 2017 #11
    eva2000

    eva2000 Administrator Staff Member

    33,705
    7,465
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +11,476
    Local Time:
    6:17 AM
    Nginx 1.13.x
    MariaDB 5.5
    AFAIK, MySQL before 5.6 silently failed on that error but after 5.6+ it reports the error now. That's the difference. The solution is listed in my above linked documentation and will require you contacting vB tech support as I believe there's a few bugs still in vB5 installer's mysql-schema.php for a few tables to address innodb + utf8mb4 + max key index prefix limits issues.

    Centmin Mod is provide as is, so short of scripted related bugs or issues, any further optimisation to the web stack components - nginx, php-fpm, mariadb mysql, csf firewall etc or web app specific configurations are left to the Centmin Mod user to deal with. So I do not provide any free support for such - this is clearly a problem with vB5's mysql schema and how it's dealing with utf8/utf8mb4 and max key index prefix limits.

    However, Centmin Mod users are free to help each other out and ask questions or give answers on this community forum. My hopes are that this community forum evolves so that more veteran long time Centmin Mod users help new Centmin Mod users out :)
     
    • Informative Informative x 1
Previous Thread Next Thread
Loading...
..

.