Akeeba Backup for WordPress

Sunday, 25 June 2017 09:31 CDT

mirit.tc

Hi

Im trying to resterize a website to another host but i get the error # 1273

Ive tried your advice here:

https://www.akeebabackup.com/support/akeeba-backup-3x/Ticket/26278-error-1273-during-a-restore-of-database.html

but it didnt work

this is the error i get:

Database error processing line 11
Database server error reply:

ErrNo #1273
Unknown collation: 'utf8_unicode_520_ci'
SQL=CREATE TABLE `pz_comments` ( `comment_ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `comment_post_ID` bigint(20) unsigned NOT NULL DEFAULT '0', `comment_author` tinytext CHARACTER SET utf8 COLLATE utf8_unicode_520_ci NOT NULL, `comment_author_email` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_520_ci NOT NULL, `comment_author_url` varchar(200) CHARACTER SET utf8 COLLATE utf8_unicode_520_ci NOT NULL, `comment_author_IP` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_520_ci NOT NULL, `comment_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `comment_date_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `comment_content` text CHARACTER SET utf8 COLLATE utf8_unicode_520_ci NOT NULL, `comment_karma` int(11) NOT NULL DEFAULT '0', `comment_approved` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_520_ci NOT NULL DEFAULT '1', `comment_agent` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_520_ci NOT NULL, `comment_type` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_520_ci NOT NULL, `comment_parent` bigint(20) unsigned NOT NULL DEFAULT '0', `user_id` bigint(20) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`comment_ID`), KEY `comment_approved_date_gmt` (`comment_approved`,`comment_date_gmt`) , KEY `comment_author_email` (`comment_author_email`(10)) , KEY `comment_date_gmt` (`comment_date_gmt`) , KEY `comment_parent` (`comment_parent`) , KEY `comment_post_ID` (`comment_post_ID`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8
Raw query text:

CREATE TABLE `#__comments` ( `comment_ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `comment_post_ID` bigint(20) unsigned NOT NULL DEFAULT '0', `comment_author` tinytext CHARACTER SET utf8 COLLATE utf8_unicode_520_ci NOT NULL, `comment_author_email` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_520_ci NOT NULL, `comment_author_url` varchar(200) CHARACTER SET utf8 COLLATE utf8_unicode_520_ci NOT NULL, `comment_author_IP` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_520_ci NOT NULL, `comment_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `comment_date_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `comment_content` text CHARACTER SET utf8 COLLATE utf8_unicode_520_ci NOT NULL, `comment_karma` int(11) NOT NULL DEFAULT '0', `comment_approved` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_520_ci NOT NULL DEFAULT '1', `comment_agent` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_520_ci NOT NULL, `comment_type` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_520_ci NOT NULL, `comment_parent` bigint(20) unsigned NOT NULL DEFAULT '0', `user_id` bigint(20) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`comment_ID`), KEY `comment_approved_date_gmt` (`comment_approved`,`comment_date_gmt`) , KEY `comment_author_email` (`comment_author_email`(10)) , KEY `comment_date_gmt` (`comment_date_gmt`) , KEY `comment_parent` (`comment_parent`) , KEY `comment_post_ID` (`comment_post_ID`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8

PLEASE HELP ME :)
Mirit

Monday, 26 June 2017 02:46 CDT

tampe125

Hello,

it seems that your original server was using a specific collation on your tables.
Did you already tried to follow this advice?

Please check the Force UTF-8 collation on database and Force UTF-8 collation on tables options in ANGIE and uncheck the Allow UTF8MB4 auto-detection option.

Davide Tampellini

Developer and Support Staff

🇮🇹Italian: native 🇬🇧English: good • 🕐 My time zone is Europe / Rome (UTC +1)
Please keep in mind my timezone and cultural differences when reading my replies. Thank you!

Monday, 26 June 2017 15:16 CDT

mirit.tc

Hi

Yes I did, it didnt help sadly, my server used UTF-8 UNICODE, thats not so rare, is it?

Tuesday, 27 June 2017 01:37 CDT

nicholas

I will be responding to your ticket since Davide is not in the office today.

This issue boils down to the way WordPress deals with database data and more specifically with the four byte Unicode character support, commonly referred to as UTF8MB4. This was marketed as Emoji support but in fact it's something far more important: it makes your site safer by fending off an entire class of attacks designed to confuse the database into doing something it's not supposed to.

While it's common for sites to be on modern hosts that support UTF8MB4 it's not common (or prudent) to transfer them to servers which do not support this feature for two reasons. First, converting from UTF8MB4 to plain old three byte UTF8 causes data loss (Emoji and some non-Latin characters would be rendered as question marks or empty boxes). Second, it decreases the site's security. The default option is to let the restoration fail to let you rectify this error, typically by asking your host to upgrade their servers. In case you don't want to do that you can take responsibility for downgrading the data to three byte UTF8. This is what you tried to do but I'm not convinced that it's the reason of your problems. It sounds like you either have an old version of Akeeba Backup or a server doing something strange.

Recent versions of WordPress (late August 2016 onwards) support an even more advanced version of UTF8MB4, the collation utf8mb4_unicode_ci. This is a MySQL 5.6 or later feature only. Versions of Akeeba Backup released before late September 2016 didn't have support for them, meaning that the conversion will fail when you are restoring to MySQL 5.5 or earlier. We added support for that by asking MySQL to report its version. If it's 5.5 or earlier we convert utf8mb4_unicode_ci to utf8mb4_unicode_ci automatically (no data is lost with this procedure). Make sure that you do use the latest version of Akeeba Backup for WordPress, i.e. version 2.2.0 at the time of this writing. You can check which version of Akeeba Backup was used to take the backup of the site in the first page of the restoration script, at the bottom.

If you are already using Akeeba Backup 2.2.0 to take the backup we have two possibilities: either your server doesn't report the correct MySQL version OR or some reason your database server doesn't support utf8mb4_unicode_ci. For what it's worth, the official MySQL changelog claims that support for this feature was added in MySQL 5.6.0. I can't see a way to have MySQL 5.6 and not support that feature. Then again I've seen hosts doing more nonsensical things than that...

I need you to do a few things to help me help you. Basically, I need to understand which of these two issues might be at play here.

1. When the restoration starts (first page in ANGIE) scroll to the bottom and look under Backup Information and Site Information. Please copy this information here.

2. Please uncheck the Force UTF-8 collation on database and Force UTF-8 collation on tables options in ANGIE and check the Allow UTF8MB4 auto-detection option. This is the opposite of what you previously did. If you get a database error please paste it verbatim. It will give me very useful information.

Thank you in advance for your patience and helping us help you.

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, 27 July 2017 17:17 CDT

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-07-27 17:17 CDT

Support