Author Topic: Cpanel VPS - PHP vers. 7.2 no search - error MySQL Index column size too large  (Read 59 times)

davinci369

  • Newbie
  • *
  • Posts: 5
    • View Profile
    • Email
Hi I have Cpanel VPS account and tried to install Feng Office: 3.7.0.5

And got the following error.

1)  Database connection has been established successfully
InnoDB storage engine is supported

#1 - Failed to import database construction. MySQL said: Index column size too large. The maximum column size is 767 bytes.

The solution was to disabled innodb_large_prefix this by adding the following within /etc/my.cnf:

innodb_large_prefix=0

To found out these, I first ask cpanel support if InnoDB was enable, they answer the following:

Yes, InnoDB is enabled by default when installing MySQL via cPanel.
We can check this with the following command as well:

[15:00:38 vps root@12461559 ~]cPs# mysql -e "SHOW ENGINES"
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MyISAM | DEFAULT | MyISAM storage engine | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| InnoDB | YES | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

Now, researching the error you reported, it seems that this has to do with the innodb_large_prefix option.
It is on (by default).

[15:00:51 vps root@12461559 ~]cPs# mysql -e "SHOW VARIABLES LIKE 'innodb_larg%'"
+---------------------+-------+
| Variable_name | Value |
+---------------------+-------+
| innodb_large_prefix | ON |
+---------------------+-------+

I incorrectly assumed that it being on is what it needs to be to handle large indexes. But reading the MySQL 5.6 documentation: https://dev.mysql.com/doc/refman/5.6/en/innodb-restrictions.html

By default, the index key prefix length limit is 767 bytes. See Section 13.1.13, “CREATE INDEX Syntax”. For example, you might hit this limit with a column prefix index of more than 255 characters on a TEXT or VARCHAR column, assuming a utf8mb3 character set and the maximum of 3 bytes for each character. When the innodb_large_prefix configuration option is enabled, the index key prefix length limit is raised to 3072 bytes for InnoDB tables that use DYNAMIC or COMPRESSED row format.

Attempting to use an index key prefix length that exceeds the limit returns an error. To avoid such errors in replication configurations, avoid enabling innodb_large_prefix on the master if it cannot also be enabled on slaves.

To avoid those errors, you should disable the innodb_large_prefix option.

Please try that and see if it solves your issue.

Regards,
Technical Analyst III
Migration Specialist
cPanel, L.L.C.

--------------------------

2) The other issue that I found out after installing Feng Office is that the search did not work.
I have for that directory PHP ea-7.2 so I went to ea-7.1 but got the same problem, nothing at all.

I turned Multi PHP Editor, display_errors On, and did not get any error when trying to search something.

Finally changing to PHP ea-7.1 and the search feature worked!  :)

 

anything