Specified key was too long during installation

6 years 4 months ago #299248 by counterpoint
With a database configured as utf8mb4, installation fails with the message "Specified key was too long". Maximum is 1000. This may happen because the database will assume each character can take 4 bytes, and there is an index that contains character string(s) that take the total above 1000. What can be done?

Please Log in to join the conversation.

6 years 4 months ago #299291 by krileon
Be sure to be using InnoDB engine and ensure InnoDB is configured with innodb_large_prefix enabled. You should have no issues with CB or Joomla indexes at that point.


Kyle (Krileon)
Community Builder Team Member
Before posting on forums: Read FAQ thoroughly + Read our Documentation + Search the forums
CB links: Documentation - Localization - CB Quickstart - CB Paid Subscriptions - Add-Ons - Forge
--
If you are a Professional, Developer, or CB Paid Subscriptions subscriber and have a support issue please always post in your respective support forums for best results!
--
If I've missed your support post with a delay of 3 days or greater and are a Professional, Developer, or CBSubs subscriber please send me a private message with your thread and will reply when possible!
--
Please note I am available Monday - Friday from 8:00 AM CST to 4:00 PM CST. I am away on weekends (Saturday and Sunday) and if I've missed your post on or before a weekend after business hours please wait for the next following business day (Monday) and will get to your issue as soon as possible, thank you.
--
My role here is to provide guidance and assistance. I cannot provide custom code for each custom requirement. Please do not inquire me about custom development.

Please Log in to join the conversation.

6 years 4 months ago #299296 by counterpoint
Replied by counterpoint on topic Specified key was too long during installation
Thanks. I am working with data from an old site. I have converted all the tables from MyISAM to InnoDB and configured the database server with innodb_large_prefix = 1 and restarted. All the conversions succeeded. But installing the latest Community Builder gives a failure, with message:

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

Please Log in to join the conversation.

6 years 4 months ago #299302 by krileon
Ensure your tables also have ROW_FORMAT set to DYNAMIC. Example as follows.

ALTER TABLE table_name_here ROW_FORMAT=DYNAMIC;

This all should've been upgraded by Joomla of which CB would upgrade to as well since CB matches Joomlas usage.

If the above doesn't work as "Index column size too large. The maximum column size is 767 bytes." is the limit without innodb_large_prefix being enabled then you likely don't have innodb_file_format set to BARRACUDA, which is also necessary.


Kyle (Krileon)
Community Builder Team Member
Before posting on forums: Read FAQ thoroughly + Read our Documentation + Search the forums
CB links: Documentation - Localization - CB Quickstart - CB Paid Subscriptions - Add-Ons - Forge
--
If you are a Professional, Developer, or CB Paid Subscriptions subscriber and have a support issue please always post in your respective support forums for best results!
--
If I've missed your support post with a delay of 3 days or greater and are a Professional, Developer, or CBSubs subscriber please send me a private message with your thread and will reply when possible!
--
Please note I am available Monday - Friday from 8:00 AM CST to 4:00 PM CST. I am away on weekends (Saturday and Sunday) and if I've missed your post on or before a weekend after business hours please wait for the next following business day (Monday) and will get to your issue as soon as possible, thank you.
--
My role here is to provide guidance and assistance. I cannot provide custom code for each custom requirement. Please do not inquire me about custom development.

Please Log in to join the conversation.

6 years 4 months ago - 6 years 4 months ago #299306 by counterpoint
Replied by counterpoint on topic Specified key was too long during installation
Thanks. I have changed the database server my.cnf to set innodb_file_format = Barracuda, and restarted. Have checked that the running database server has innodb_file_format set correctly, and also that it has innodb_large_prefix set on. I've gone through all the jos_comprofiler tables running the command "ALTER TABLE table_name_here ROW_FORMAT=DYNAMIC;". shows the error .

Sample table looks like this in phpMyAdmin:
Attachments:

Please Log in to join the conversation.

6 years 4 months ago #299321 by krileon
Your ROW_FORMAT is set to COMPACT and not DYNAMIC. Suggest contacting your host if still having issues configuring InnoDB. See the below for additional information.

stackoverflow.com/questions/30761867/mysql-error-the-maximum-column-size-is-767-bytes


Kyle (Krileon)
Community Builder Team Member
Before posting on forums: Read FAQ thoroughly + Read our Documentation + Search the forums
CB links: Documentation - Localization - CB Quickstart - CB Paid Subscriptions - Add-Ons - Forge
--
If you are a Professional, Developer, or CB Paid Subscriptions subscriber and have a support issue please always post in your respective support forums for best results!
--
If I've missed your support post with a delay of 3 days or greater and are a Professional, Developer, or CBSubs subscriber please send me a private message with your thread and will reply when possible!
--
Please note I am available Monday - Friday from 8:00 AM CST to 4:00 PM CST. I am away on weekends (Saturday and Sunday) and if I've missed your post on or before a weekend after business hours please wait for the next following business day (Monday) and will get to your issue as soon as possible, thank you.
--
My role here is to provide guidance and assistance. I cannot provide custom code for each custom requirement. Please do not inquire me about custom development.

Please Log in to join the conversation.

Moderators: beatnantkrileon
Time to create page: 0.253 seconds

Facebook Twitter LinkedIn