/ Register

Learn about Centmin Mod LEMP Stack today
Become a Member

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

Discussion in 'System Administration' started by Jon Snow, Oct 6, 2022.

Previous Thread Next Thread
Loading...
  1. Oct 6, 2022 #1
    Jon Snow

    Jon Snow Active Member

    766
    157
    43
    Jun 30, 2017
    Ratings:
    +225
    Local Time:
    7:08 PM
    Nginx 1.13.9
    MariaDB 10.1.31
    Query Monitor is giving me the following error when trying to use a Wordpress plugin's optimization feature to remove unused CSS. The site functions normally, it's just that I don't believe the feature is working and this is logged in Query Monitor.

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

    This is a $5/$10 Linode VPS.

    How can I fix this?
     
  2. Oct 6, 2022 #2
    eva2000

    eva2000 Administrator Staff Member

    53,209
    12,113
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,649
    Local Time:
    8:08 AM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    What's your MariaDB database's default characterset and collation? Must be that the Wordpress plugin database schema doesn't account for InnoDB has a maximum index length of 767 bytes when using utf8mb4 characterset and/or is using COMPACT/REDUNDANT row format for InnoDB table

    see
     
  3. Oct 7, 2022 #3
    Jon Snow

    Jon Snow Active Member

    766
    157
    43
    Jun 30, 2017
    Ratings:
    +225
    Local Time:
    7:08 PM
    Nginx 1.13.9
    MariaDB 10.1.31
    It's showing the same result for me from the post:
    Code (Text):
    egrep 'format|large' /etc/my.cnf | uniq
innodb_large_prefix=1
innodb_file_format = Barracuda
# binlog_format=ROW

    What's the command to check this if I don't have phpMyAdmin installed?
     
  4. Oct 7, 2022 #4
    eva2000

    eva2000 Administrator Staff Member

    53,209
    12,113
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,649
    Local Time:
    8:08 AM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    SSH command to check and filter MariaDB MySQL relevant variables
    Code (Text):
    mysqladmin var | egrep -i '\| character_|\| collation|file_format|large_prefix|row_format' | tr -s ' '
     
  5. Oct 7, 2022 #5
    Jon Snow

    Jon Snow Active Member

    766
    157
    43
    Jun 30, 2017
    Ratings:
    +225
    Local Time:
    7:08 PM
    Nginx 1.13.9
    MariaDB 10.1.31
    This is the results:

    Code (Text):
    mysqladmin var | egrep -i '\| character_|\| collation|file_format|large_prefix|row_format' | tr -s ' '
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
| collation_connection | utf8_general_ci |
| collation_database | utf8_general_ci |
| collation_server | utf8_general_ci |
| innodb_default_row_format | compact |
| innodb_file_format | Barracuda |
| innodb_file_format_check | ON |
| innodb_file_format_max | Antelope |
| innodb_large_prefix | ON |
     
  6. Oct 7, 2022 #6
    eva2000

    eva2000 Administrator Staff Member

    53,209
    12,113
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,649
    Local Time:
    8:08 AM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    So your MariaDB defaults use utf8 character set so shouldn't be subject to InnoDB maximum index length of 767 bytes. However, individual databases/tables or web app scripts can choose to create database/tables with utf8mb4 character set which will trigger/hit InnoDB maximum index length of 767 bytes. So you need to check with Wordpress plugin author/developer to see if their created tables are utf8mb4 or utf8 based and adjust table InnoDB lengths appropriately. My 1st reply in this thread has 2 links you can pass onto the Wordpress plugin author/developer.

    edit: though looks like author is dismissing it as a problem on his side [Index column size too large. The maximum column size is 767 bytes.]

    When did you install Query Monitor plugin? If it was ages ago, you can try uninstalling plugin and re-installing to see if the database table for it gets updated.

    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 with 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 :)
     
    Last edited: Oct 7, 2022
  7. Oct 11, 2022 #7
    Jon Snow

    Jon Snow Active Member

    766
    157
    43
    Jun 30, 2017
    Ratings:
    +225
    Local Time:
    7:08 PM
    Nginx 1.13.9
    MariaDB 10.1.31
    In Health Check via Wordpress, it says:

    Code (Text):
    Database charset    utf8mb4

    So there is a limitation according to your post.

    How do you suggest I increase the limit on CMM and is it safe to do? Or do you have another recommendation?
     
  8. Oct 11, 2022 #8
    eva2000

    eva2000 Administrator Staff Member

    53,209
    12,113
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,649
    Local Time:
    8:08 AM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    The solution involves modifying your database table structure for InnoDB indexes with >767 bytes or changing database table's row format. The links I provided above also link to official documentation for such as well. If you aren't sure how to do it, I'd recommend hiring someone to do it as that person would be able to access your server to have a better idea of the scope of changes needed.

    I'd be wary of forum/online suggested commands/changes as they won't know the full scope of your database structure and it's potential impact. If you did have to test such changes, you can do a full MySQL database backup first and/or do changes on a test copy of your MySQL database.
     
  9. Oct 12, 2022 #9
    Jon Snow

    Jon Snow Active Member

    766
    157
    43
    Jun 30, 2017
    Ratings:
    +225
    Local Time:
    7:08 PM
    Nginx 1.13.9
    MariaDB 10.1.31
    I just have to set this:
    Code (Text):
    innodb_file_format=BARRACUDA
innodb-file-per-table=ON
innodb-large-prefix=ON
innodb_default_row_format = 'DYNAMIC'

    In etc/my.cnf, I already see
    Code (Text):
    innodb_file_format=BARRACUDA

    These two have a dash in the suggested solution instead of an underscore in the Centminmod My CNF file:
    Code (Text):
    # innodb settings
innodb_large_prefix=1
innodb_file_per_table = 1

    And there's no:
    Code (Text):
    innodb_default_row_format = 'DYNAMIC'

    Would I just have to add the last 3 lines from the suggested solution and restart MariaDB?

    Last three lines:
    Code (Text):
    innodb-file-per-table=ON
innodb-large-prefix=ON
innodb_default_row_format = 'DYNAMIC'

    Would these changes be reverted with updates?

    I'm assuming innodb_large_prefix is a different variable to innodb-large-prefix.
     
  10. Oct 13, 2022 #10
    eva2000

    eva2000 Administrator Staff Member

    53,209
    12,113
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,649
    Local Time:
    8:08 AM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    The /etc/my.cnf changes only work for newly created MySQL database tables not existing - hence why I said you need to modify your database tables affected by this.

    What's output for command
    Code (Text):
    grep -C3 -n innodb_default_row_format  /etc/my.cnf

    as each version section should have that by default already
    Code (Text):
    grep -C3 -n innodb_default_row_format  /etc/my.cnf

165-log_slow_filter =admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk
166-
167-[mariadb-10.1]
168:innodb_default_row_format = dynamic
169-innodb_file_per_table = 1
170-
171-## wsrep specific
--
206-
207-[mariadb-10.2]
208-#innodb_file_format = Barracuda
209:innodb_default_row_format = dynamic
210-innodb_file_per_table = 1
211-
212-## wsrep specific
--
253-
254-[mariadb-10.3]
255-#innodb_file_format = Barracuda
256:innodb_default_row_format = dynamic
257-innodb_file_per_table = 1
258-
259-## wsrep specific
     
  11. Oct 13, 2022 #11
    Jon Snow

    Jon Snow Active Member

    766
    157
    43
    Jun 30, 2017
    Ratings:
    +225
    Local Time:
    7:08 PM
    Nginx 1.13.9
    MariaDB 10.1.31
    It's for a new table so that's the aim.
    It's blank.
     
  12. Oct 14, 2022 #12
    Jon Snow

    Jon Snow Active Member

    766
    157
    43
    Jun 30, 2017
    Ratings:
    +225
    Local Time:
    7:08 PM
    Nginx 1.13.9
    MariaDB 10.1.31
    On one of my newer servers:
    Code (Text):
    grep -C3 -n innodb_default_row_format  /etc/my.cnf
166-
167-[mariadb-10.3]
168-#innodb_file_format = Barracuda
169:innodb_default_row_format = dynamic
170-innodb_file_per_table = 1
171-
172-## wsrep specific

    Barracuda is commented out.

    Is there a difference between innodb-file-per-table and innodb_file_per_table?
     
  13. Oct 15, 2022 #13
    eva2000

    eva2000 Administrator Staff Member

    53,209
    12,113
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,649
    Local Time:
    8:08 AM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    variables in my.cnf should use underscores

    10.3+ higher deprecate that variable for innodb_file_format as new versions of MariaDB 10.3+ are by default using Barracuda already

    I suggest you compare newer Centmin Mod server's /etc/my.cnf to the one with blank output for that command and see what you need to re-add to [mariadb-10.3] section. Again that only helps newly created MySQL database tables not your existing ones.
     
  14. Oct 15, 2022 #14
    Jon Snow

    Jon Snow Active Member

    766
    157
    43
    Jun 30, 2017
    Ratings:
    +225
    Local Time:
    7:08 PM
    Nginx 1.13.9
    MariaDB 10.1.31
    What if I'm still on 10.1? Should I upgrade to 10.3?

    It's blank for 10.1.
     
  15. Oct 15, 2022 #15
    eva2000

    eva2000 Administrator Staff Member

    53,209
    12,113
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,649
    Local Time:
    8:08 AM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    I'd upgrade to MariaDB 10.3 via centmin.sh menu option 11
     
Previous Thread Next Thread
Loading...

.