#29172 – Database server error #1709

Posted in ‘Kickstart / Backup Restoration’
This is a public ticket. Everybody will be able to see its contents. Do not include usernames, passwords or any other sensitive information.
Friday, 09 February 2018 11:25 CST
I have update a Joomla website on my local host PC and made a backup with Akeeba.
This backup is placed on online server to install with kickstart-pro.
Local server is PHP7.1, local host is 5.6.25

I get different errors like:
Database server error reply: ErrNo #1071
Specified key was too long; max key length is 767 bytes

Because I tried different times and get several of these errors, I have used Admintools Pro on localhost to Change Database Collation and Repair & Optimise Tables. After that I made a backup and tried again with Kickstart.

The webhost is using mariaDB and has innodb_large_prefix=1
and also: innodb_file_format=BARRACUDA

After that :
During the proces I get a Database error processing line 9
Database server error reply: ErrNo #1709

Index column size too large. The maximum column size is 767 bytes.
SQL=CREATE TABLE `mvp_jd_store` ( `idJdStore` int(11) NOT NULL AUTO_INCREMENT, `idLang` tinyint(4) NOT NULL COMMENT 'language id #__languages', `idReference` int(11) NOT NULL COMMENT 'Primary key for translation', `referenceTable` varchar(255) NOT NULL COMMENT 'Table of the translation', `referenceOption` varchar(50) NOT NULL, `referenceView` varchar(50) NOT NULL, `referenceLayout` varchar(50) NOT NULL, `value` longtext NOT NULL COMMENT 'serialized table value', `modified` datetime NOT NULL, `modified_by` int(11) NOT NULL, `state` tinyint(4) NOT NULL, PRIMARY KEY (`idJdStore`), UNIQUE KEY `idLang` (`idLang`,`idReference`,`referenceTable`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

What can I do to get this website running?
And what is the mvp_jd_store in the database (I think I do not need that)
Custom Fields
Joomla! version (in x.y.z format)
3.8.5
PHP version (in x.y.z format)
7.1
Akeeba Backup version (x.y.z format)
6.0.0
Kickstart version (x.y.z format)
5.4.0
Edited by appletree on 2018-02-09 17:25
 
Monday, 12 February 2018 01:16 CST
Your old database is using plain old UTF8 instead of the modern UTF8MB4 encoding. One of the extensions you are using is either stuck at a version published before Joomla! 3.5.0 (that's be about two years now) or the developer didn't get the memo about UTF8MB4. As a result, the database structure is such that the indices are against columns which are too big to be indexed under the UTF8MB4 encoding*. Meanwhile, you are restoring your site using the options which tell ANGIE to convert all your database tables to UTF8MB4, thus causing this MySQL error.

During restoration you'll eventually reach the Database Restoration page. You need to uncheck "Allow UTF8MB4 auto-detection", "Force UTF-8 collation on database" and "Force UTF-8 collation on tables".

If that causes a problem on the restored site retry the restoration with "Allow UTF8MB4 auto-detection" unchecked BUT do check the "Force UTF-8 collation on database" and "Force UTF-8 collation on tables" options this time (they work around the database collation being other than utf8_general_ci).

Alternatively, if you are sure you don't need that table (e.g. you no longer use the extension) you can delete the table(s) from that extensions and then take a new backup. If that's not an option see above :)

* Sidebar / technical notes: You'll note that the index is against a VARCHAR column with a length of 255 characters. So where does 767 bytes come from in the error message? UTF8 uses up to 3 bytes to store a glyph. Therefore MySQL reserves 255 characters x 3 bytes / character = 765 bytes for each index record. With UTF8MB4 we can use up to 4 bytes to store a glyph. Therefore MySQL needs to reserve 255 characters x 4 bytes / character = 1020 bytes for each index record. However, indices in MySQL are implemented as binary trees with a maximum leaf node size of 767 bytes. This number has to do with the internal data structures and the efficiency of the algorithm. Anyway. 1020 is larger than the limit of 767, hence the problem. The solution is for the developer to either reduce the size of the column or tell MySQL to index up to the first 191 characters.




Nicholas K. Dionysopoulos


Lead Developer and Director






Greek: native


English: excellent


French: basic






Please keep in mind my timezone and cultural differences when reading my replies. Thank you!






Tuesday, 13 February 2018 04:17 CST
Thanks Nicholas,
The solution was the 2nd attempt: "Allow UTF8MB4 auto-detection" unchecked BUT do check the "Force UTF-8 collation on database" and "Force UTF-8 collation on tables" options this time.
 
This ticket is closed, therefore read-only. You can no longer reply to it. If you need to provide more information, please open a new ticket and mention this ticket's number.

Support Information

Working hours: Typically we work Monday to Friday, 9am to 7pm Cyprus timezone (EEST). Support is provided by the same developers writing the software, all of which live in Europe. You can still file tickets, but we cannot respond to them, outside of our working hours.

Support policy: We would like to kindly inform you that when using our support you have already agreed to the Support Policy which is part of our Terms of Service. Thank you for your understanding and for helping us help you!

Cookies Notification - Action required

This website uses cookies to provide user authentication and improve your user experience. Please indicate whether you consent to our site placing these cookies on your device. You can change your preference later, from the controls which will be made available to you at the bottom of every page of our site.