Support

Akeeba Backup for WordPress

#28015 – error #1273 on angie restoration

Posted in ‘Akeeba Backup for WordPress’
This is a public ticket. Everybody will be able to see its contents. Do not include usernames, passwords or any other sensitive information.
Sunday, 25 June 2017 09:31 CDT
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
Custom Fields
WordPress version (in x.y.z format) 4.6.6
PHP version (in x.y.z format) 5.6
Akeeba Backup version (x.y.z format) 2.04
mirit.tc
Monday, 26 June 2017 02:46 CDT
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



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



tampe125
Monday, 26 June 2017 15:16 CDT
Hi

Yes I did, it didnt help sadly, my server used UTF-8 UNICODE, thats not so rare, is it?
mirit.tc
Tuesday, 27 June 2017 01:37 CDT
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_520_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_520_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_520_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



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



nicholas
Thursday, 27 July 2017 17:17 CDT
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.
system
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.