Support

Site Restoration

#35090 Angie Restoration Error (Wordpress) - Error 1406

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

itsonlycomputers

I am attempting to restore a Wordpress website from my old host to Siteground.  I've done this many times without any issues until today for some unknown reason.

1.  Ran Kickstart *Successfully unarchived all files.*

2.  Start Angie MySQL restore which results in the Error 1416.  (Error Attached).

 

Things I've tried

1. Verified that I'm using the latest versions of Wordpress, Akeeba and Kickstart.

2. Verified both hosts are using PHP 7.4 (Latest version available).

3. Changed MySQL passwords to not include special characters in the Username.

4.  Reviewed support forum to see if anyone has experienced this problem.

At a loss and looking for guidance.   

Thanks!

David

 

 

 

nicholas
Akeeba Staff
Manager

This error means that your original site is using the GEOMETRY extension of MySQL. Geometry data (such as points) cannot be exported correctly. As far as I can tell, the only way to export them is using MySQL Workbench. You can take another backup excluding the table with the geometry data and back it externally. To be fair, this is the first time I've seen geometry data being used on a site. Which plugin generates this kind of table content?

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!

nicholas
Akeeba Staff
Manager

I may have spoken too soon — it's been nine years since I last played around the geometry extensions in MySQL. I remembered having had problems with it, I didn't remember that I had in fact fixed those problems back in 2012. It only dawned on me when I realised that I had a backup of a test site using MySQL's spatial extensions from that time which wouldn't have been able to get backed up and restored if I hadn't addressed the issue.

Backing up and restoring spatial data such as points, polygons etc works fine. MySQL reports them as binary BLOB objects which do get backed up as such and restored correctly. I ran a few tests on MySQL 8. However, this hinges on the assumption that both versions of MySQL (source and target server) support the MySQL spatial (geometry) extension. Looking at your error, it appears that it happens when creating the table which makes me wonder if the target server supports spatial data in MySQL.

Can you please first ask your host if this is indeed the case?

Second, can you please attach the plugin which generates this data? There is a small chance that the default value they are using for the spatial data is wrong under some versions of MySQL, e.g. if it's using a reference coordinate system other than the default. I need to have the actual plugin generating the spatial data to understand this problem better.

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!

itsonlycomputers

Hi Nicholas!  Thanks so much for pointing me in the right direction.

Two plugins were causing the problem on my WordPress site.

.  WP Google Maps - WP Google Maps – WordPress plugin | WordPress.org

-  Wordfence - WordPress Security Plugin | Wordfence

Neither Plugin was being used, so I deleted the plugins, manually removed the associated MYSQL tables, and I was then able to backup and restore the website without error.

Here is an example of the offending data in one of the tables.

DROP TABLE IF EXISTS `wpac_wpgmza`;

CREATE TABLE `wpac_wpgmza` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`map_id` int(11) NOT NULL,
`address` varchar(700) NOT NULL,
`description` mediumtext NOT NULL,
`pic` varchar(700) NOT NULL,
`link` varchar(2083) NOT NULL,
`icon` varchar(700) NOT NULL,
`lat` varchar(100) NOT NULL,
`lng` varchar(100) NOT NULL,
`anim` varchar(3) NOT NULL,
`title` varchar(700) NOT NULL,
`infoopen` varchar(3) NOT NULL,
`category` varchar(500) NOT NULL,
`approved` tinyint(1) DEFAULT 1,
`retina` tinyint(1) DEFAULT 0,
`type` tinyint(1) DEFAULT 0,
`did` varchar(500) NOT NULL,
`other_data` longtext NOT NULL,
`latlng` point DEFAULT NULL,
`sticky` tinyint(1) DEFAULT 0,
`lnglat` point NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

INSERT INTO `wpac_wpgmza` VALUES (1,1,"12345 E High Road, Suite 50, Denver, CO 99999","","","","","39.111111","-104.231111",0,"",0,0,1,0,0,"","","\0\0\0\0\0\0\0×ÜÑÿr‡C@…—àÔ6ZÀ",0,"");

David

 

 

nicholas
Akeeba Staff
Manager

Ah, I see the problem. The table is defined with the UTF8 character set but the restoration happens in UTF8MB4. As a result the raw data gets restored in the wrong character set. This is not a problem with text data but it is with string-encoded binary data, causing your error.

I did a test like that and could reproduce the issue. Now that I know what it is I can fix it :)

I am going to make a change for the next version, dumping the spatial data as hex-encoded strings. I tried that on my local site where I had reproduced your issue and this fixes it.

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!

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.

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!

Summer vacations: Our support will be closed for replies and new tickets from August 6th to August 21st, 2022 due to summer vacations.