Support

Site Restoration

#29172 Database server error #1709

Posted in ‘Site restoration’
This is a public ticket

Everybody will be able to see its contents. Do not include usernames, passwords or any other sensitive information.

Environment Information

PHP version
n/a
CMS Type
Other
CMS Version
n/a
Backup Tool Version
n/a
Kickstart version
n/a

Latest post by appletree on Tuesday, 13 February 2018 04:17 CST

appletree
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)

nicholas
Akeeba Staff
Manager
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 • 🕐 My time zone is Europe / Athens
Please keep in mind my timezone and cultural differences when reading my replies. Thank you!

appletree
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.

Support Information

Working hours: We are open Monday to Friday, 9am to 7pm Cyprus timezone (EET / EEST). Support is provided by the same developers writing the software, all of which live in Europe. You can still file tickets outside of our working hours, but we cannot respond to them until we're back at the office.

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!