[#9815] - Bugfix for utf8mb4 conversion issue (Specified key was too long; max key length is 1000 bytes)
- Closed
- 9 Apr 2016
- Critical
- Build: master
- # 9815
Hi,
I have encountered the following issue when updating a joomla website from 3.4.8 to 3.5.0 or to 3.5.1
The solution explained below may help those who faced with utf8mb4 conversion issue related to index.
Steps to reproduce the issue
Update a 3.x joomla website to 3.5.0 or 3.5.1
Expected result
Successfull Upgrade. No Database error
Actual result
Error
Specified key was too long; max key length is 1000 bytes
SQL=ALTER TABLE #__content CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
System information (as much as possible)
Joomla : 3.4.8
PHP Version : 5.4.43
Web Server : Apache
Database Version : MySQL 5.6.29
Reason of this issue
This error is triggered because of index "idx_id_title" in core table #__content
This index have 2 fields :
id int(10)
title varchar(255)
We have 4 byte per character in the new encoding utf8mb4, so 255*4 = 1020, hence it exceed the max size of 1000 for a mysql table index. (See : http://dev.mysql.com/doc/refman/5.1/en/error-messages-server.html#error_er_too_long_key)
Solution
Below is the way i have fixed this issue :
1) Change size of column title to VARCHAR(200) instead of VARCHAR(255) for core table #__content
2) In PhpMyAdmin : Execute ALTER TABLE #__content CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
(replace #__content with the real name of your content table)
3) In Joomla back-end, navigate to Extension > Manage > Database and click Fix.
After the Fix button click, joomla display : Database table structure is up to date.
I also could not find this index in 1.5.0 and 1.5.1 even, checked all scripts. So I am pretty sure this index does not come from Joomla! Core.
This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/9815.
My joomla installation exist since 1.7 and i did all upgrade until 3.4.8.
I just checked a full install of 3.5.1, 3.4.8; 2.5.28 and even 1.7 and this index is not present.
Let's close this issue then. If i find the extension which create it, then will update this thread with it.
| Status | New | ⇒ | Closed |
| Closed_Date | 0000-00-00 00:00:00 | ⇒ | 2016-04-09 17:22:28 |
| Closed_By | ⇒ | montellsp |
@montellsp Ok, thanks for the info then.
You're welcome
Did you ever find out if it was an extension that caused this error? I am encountering the exact same problem. I will attempt to apply your solution above first.
Error
Specified key was too long; max key length is 1000 bytes SQL=ALTER TABLE #__content CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
× Warning: Database is not up to date!
Database schema version (in #__schemas): 3.6.0-2016-06-05.
Update version (in #__extensions): 3.6.0.
Database driver: mysqli.
97 database changes were checked successfully.
158 database changes did not alter table structure and were skipped.
1 Database Problem Found.
The Joomla! Core database tables have not been converted yet to UTF-8 Multibyte (utf8mb4).
Hi,
I think the index was added manually for performance issue (which is no more) i had a long time ago.
I did not find any of my actual extensions that have made this change.
In order to be safe for future Joomla upgrade, i have removed this index from the table and all works fines now.
It seems to me that this index "idx_id_title" does not come from Joomla! core, at least it is not used in joomla.sql or any update script. I checked Joomla! full install zips for 2.5.28, 3.2.7, 3.4.7, 3.4.8, 3.5.0 and 3.5.1.
Can it be it has been created by some extension?
In this case there is nothing we can correct in Joomla! core.
Or it comes from times older than 2.5.28, e.g. 1.5, and there was never any update script to remove it? @wilsonge can you check that or ask someone to check? In this case we would have to add it to some old update script.
This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/9815.