Support

Site Restoration

#26830 admin tools table in dbse causes error in restoration

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 on Sunday, 05 February 2017 17:17 CST

Wednesday, 04 January 2017 11:13 CST

nicol
Every backup of my site creates a MySQL error on restoration (below). Is there a way to do a manual MySQL restore, and skip the xmab_admintools_* tables?

Thanks
Nic

Database error processing line 73
Database server error reply:

ErrNo #1071
Specified key was too long; max key length is 1000 bytes SQL=CREATE TABLE `xmab_admintools_ipautoban` ( `ip` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `reason` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT 'other', `until` datetime DEFAULT NULL, UNIQUE KEY `ip` (`ip`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
Raw query text:

CREATE TABLE `#__admintools_ipautoban` ( `ip` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `reason` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT 'other', `until` datetime DEFAULT NULL, UNIQUE KEY `ip` (`ip`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Thursday, 05 January 2017 02:28 CST

nicholas
Some background information. It's OK if you don't understand it, it's admittedly too technical. This would have happened with any table. You see it with Admin Tools because it's simply the first table with a large index being restored. The problem has to do with your old site being hosted on an old server which doesn't support the UTF8MB4 encoding whereas the new one does and you're trying to upgrade the tables to the new encoding. This will only work if the extension tables support such a conversion. Admin Tools will only apply a workaround for this issue to its database tables if it runs on a server compatible with UTF8MB4. Since your old server wasn't compatible no workaround was applied. This wouldn't have been a problem if you were not trying to forcibly upgrade your tables to UTF8MB4 while restoring the site.

TL;DR: How to solve this problem. When you are restoring your site you reach the database restoration page. Enter your new database connection information. Before clicking Next please make sure that on the right hand side of the screen the following checkboxes are NOT checked:
  • Force UTF-8 collation on database
  • Force UTF-8 collation on tables
  • Allow UTF8MB4 auto-detection

By doing that you are instructing ANGIE (the restoration script) to NOT attempt to modify the encoding of your tables.

If that still fails, even though it shouldn't, change the checkboxes per the following recommendations:
  • Force UTF-8 collation on database: NOT checked
  • Force UTF-8 collation on tables: CHECKED
  • Allow UTF8MB4 auto-detection: NOT checked

This instructs ANGIE to forcibly change the encoding of the tables to plain old UTF-8.

In any case, after restoring your site please remember to update Admin Tools if necessary and definitely visit its control panel page to let it apply a workaround which will prevent any similar issues in the future (at least with Admin Tools' tables).

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!

Thursday, 05 January 2017 15:52 CST

nicol
Thanks Nicholas, forcing UTF-8 collation fixed it.

Friday, 06 January 2017 04:36 CST

nicholas
You're welcome!

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!

Sunday, 05 February 2017 17:17 CST

System Task
system
This ticket has been automatically closed. All tickets which have been inactive for a long time are automatically closed. If you believe that this ticket was closed in error, please contact us.
Edited by on 2017-02-05 17:17 CST

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!