Author Topic: Failed to import database construction. MySQL said: Index column size too large.  (Read 6221 times)

ChristophW

  • Newbie
  • *
  • Posts: 1
    • View Profile
    • Email
- Version 3.4.4.1
- MySql: 5.6.33
- InnoDb
« Last Edit: October 26, 2016, 07:09:06 AM by ChristophW »

koksalbasar

  • Newbie
  • *
  • Posts: 1
    • View Profile
Same problem.

The message came after two tables are created.
fo_administration_tools
fo_dimensions

I deleted the following indexes from ...fengoffice\public\install\installation\templates\sql\mysql_schema.php file; (for the next table : members)
KEY `by_dimension` (`dimension_id`,`parent_member_id`,`name`),
  KEY `by_object_id` (`object_id`),
  KEY `archived_on` (`archived_on`),
  KEY `name` (`name`)

setup is continued until another error message...

Failed to import initial data. MySQL said: Field 'description' doesn't have a default value

My experience is not enough yet to solve these problems...

Is any body have solution?
« Last Edit: November 09, 2016, 06:05:48 PM by koksalbasar »

lemrm

  • Newbie
  • *
  • Posts: 2
    • View Profile
public-install-installation-templates-sql-mysql_schema.php
change Line 46

from

 `name` varchar(511)

to

`name` varchar(200)

you could later change it back in db to latin1_general_cs varchar 511

davinci369

  • Newbie
  • *
  • Posts: 5
    • View Profile
    • Email
Lemrm, I also have the issue with fengoffice 3.7 and your solution solved the issue, thanks a lot!

lemrm
Newbie
*
Posts: 2
View Profile  Personal Message (Offline)

Re: Failed to import database construction. MySQL said: Index column size too large.
« Reply #2 on: August 16, 2017, 08:56:32 AM »
Quote
public-install-installation-templates-sql-mysql_schema.php
change Line 46

from

 `name` varchar(511)

to

`name` varchar(200)

you could later change it back in db to latin1_general_cs varchar 511

davinci369

  • Newbie
  • *
  • Posts: 5
    • View Profile
    • Email
The previos solution was wrong, it forced the installation but you should not work like that.

I found the reason of the error, you need to turn off:

disabled innodb_large_prefix by adding the following within /etc/my.cnf:

I turned off and the insatallation went with any error. the only issue, still the search feature is not working.

==============

innodb_large_prefix=0

These is my request fro my cpanel vps:

Thank you for your patience. 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.


Jayde1235

  • Newbie
  • *
  • Posts: 1
  • Jayden Live
    • View Profile
    • How To All Router Login
A little more background to the issue after investigating it.

the default collation of the ddev db is utf8mb4_bin; utf8mb4 uses 4 bytes per char, while "standard" utf8 collation uses 3 bytes per char.
If you run above query without having DEFAULT CHARSET=utf8 at the end, mysql will try to create table using default db collation which is utf8mb4.
in this table we have a KEY identifier (storage,identifier(199)). Doing little math 199 chars *4 bytes = 796 bytes, so already over the 767 limit.


barryallen1337

  • Newbie
  • *
  • Posts: 1
    • View Profile
public-install-installation-templates-sql-mysql_schema.php
change Line 46

from

 `name` varchar(511)

to

`name` varchar(200)

you could later change it back in db to latin1_general_cs varchar 511

 

anything
anything