Welcome to Centmin Mod Community
Register Now

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.

  1. pamamolf

    pamamolf Premium Member Premium Member

    4,068
    427
    83
    May 31, 2014
    Ratings:
    +832
    Local Time:
    12:06 AM
    Nginx-1.25.x
    MariaDB 10.3.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. hendry

    hendry Member

    59
    14
    8
    Sep 19, 2016
    Ratings:
    +18
    Local Time:
    4:06 AM
    nginx/1.11.4
    MariaDB 10
  3. eva2000

    eva2000 Administrator Staff Member

    53,142
    12,108
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,643
    Local Time:
    7:06 AM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    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.
     
  4. pamamolf

    pamamolf Premium Member Premium Member

    4,068
    427
    83
    May 31, 2014
    Ratings:
    +832
    Local Time:
    12:06 AM
    Nginx-1.25.x
    MariaDB 10.3.x
    Yes it is for utf8mb4....
     
  5. JoeDer

    JoeDer Member

    82
    19
    8
    Feb 22, 2015
    Ratings:
    +48
    Local Time:
    12:06 AM
    Nginx 1.21.x
    MariaDB 10.3.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. eva2000

    eva2000 Administrator Staff Member

    53,142
    12,108
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,643
    Local Time:
    7:06 AM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    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)
     
  7. the caretaker

    the caretaker New Member

    14
    3
    3
    Nov 11, 2015
    Ratings:
    +4
    Local Time:
    7:06 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
     
  8. the caretaker

    the caretaker New Member

    14
    3
    3
    Nov 11, 2015
    Ratings:
    +4
    Local Time:
    7:06 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. eva2000

    eva2000 Administrator Staff Member

    53,142
    12,108
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,643
    Local Time:
    7:06 AM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    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
  10. the caretaker

    the caretaker New Member

    14
    3
    3
    Nov 11, 2015
    Ratings:
    +4
    Local Time:
    7:06 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. eva2000

    eva2000 Administrator Staff Member

    53,142
    12,108
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,643
    Local Time:
    7:06 AM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    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 :)
     
  12. eva2000

    eva2000 Administrator Staff Member

    53,142
    12,108
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,643
    Local Time:
    7:06 AM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    For folks who run across this thread and error, just know for Centmin Mod 123.09beta01 installs after April 29, 2018 this InnoDB column index length issue and occurrence should be lessened as Centmin Mod 123.09beta01 has updated MariaDB 10.1/10.2 my.cnf templates to add new innodb_default_row_format variable and set it to default to DYNAMIC instead of COMPACT row formats as per Beta Branch - update MariaDB 10.1 & 10.2 default /etc/my.cnf default for innodb_default_row_format. So now all the required pieces are in place to support the larger 3072 bytes limit instead of 767 bytes limit with Barracuda file format, innodb_large_prefer and innodb_default_row_format=dynamic all configured :)