Akeeba Backup for WordPress

Wednesday, 03 February 2016 16:25 CST

DWDLicense

I'm attempting to migrate changes made on our Development server to our Test/Staging Server and I see the following error:

Database server error reply:

ErrNo #0
SQL=CREATE TABLE `wp_fileaway_downloads` (`id` int(11) NOT NULL auto_increment ,`timestamp` varchar(255) NULL ,`file` varchar(1000) NULL ,`uid` int(11) NULL ,`email` varchar(255) NULL ,`ip` varchar(255) NULL ,`agent` varchar(255) NULL ,`notified` bit(1) NULL DEFAULT 'b\'0\'' , PRIMARY KEY (`id`), KEY `uid` (`uid`) USING BTREE) ENGINE=InnoDB DEFAULT COLLATE utf8mb4_unicode_ci

Raw query text:
CREATE TABLE `#__fileaway_downloads` (`id` int(11) NOT NULL auto_increment ,`timestamp` varchar(255) NULL ,`file` varchar(1000) NULL ,`uid` int(11) NULL ,`email` varchar(255) NULL ,`ip` varchar(255) NULL ,`agent` varchar(255) NULL ,`notified` bit(1) NULL DEFAULT 'b\'0\'' , PRIMARY KEY (`id`), KEY `uid` (`uid`) USING BTREE) ENGINE=InnoDB DEFAULT COLLATE utf8mb4_unicode_ci

With existing tables is set to DROP and "Suppress foreign key checks", "No auto value on zero", and "Allow UTF8MB4 auto-detection" are selected

Looking at the tables in the DB it looks like the restore operation succeeds in dropping the table prior to the creation attempt.

After reading several articles regarding ErrNo #0 which mention permission issues I attempted running the following SQL with CLI as the wordpress user I'm entering in the ANGIE dialogs. No errors

This was the result of running
show create table wp_fileaway_downloads;
on the Dev server that we used to create the JPA

CREATE TABLE `wp_fileaway_downloads` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`timestamp` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`file` varchar(1000) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`uid` int(11) DEFAULT NULL,
`email` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`ip` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`agent` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`notified` bit(1) DEFAULT b'0',
PRIMARY KEY (`id`),
KEY `uid` (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

As the plugin that the table supports is no longer installed or in use I have deleted the table from the DEV server, created a new backup and was able to restore. This is more to alert you to a potential problem.

-Paul Bergman

Thursday, 04 February 2016 01:36 CST

nicholas

I have the suspicion that your target server does not support utf8mb4.

Please note that if you are transferring a site FROM a plain utf8 server TO a utf8mb4-capable server the transfer will work and you can use the "Allow UTF8MB4 auto-detection" option to upgrade your tables to utf8mb4. However if you are transferring FROM a utf8mb4-capable server TO a plain utf8 server it will NOT work.

This is not something we can work around, it's a limitation of MySQL: utf8mb4 data can represent (four byte) characters that plain old 3-byte utf8 cannot. That was the reason WordPress migrated to utf8mb4. If you try to insert a four byte character to a plain old 3-byte utf8 table MySQL will truncate the data without asking you and without an indication of error! Therefore if we allowed downgrading utf8mb4 to plain old utf8 you'd end up with truncated data, i.e. a broken site.

The only corrective course of action I can see is making sure your target server supports utf8mb4. Please note that even if you manually downgraded the tables on your source server to utf8, WordPress would automatically upgrade them back to utf8mb4 the next time it installs an update.

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!

Saturday, 05 March 2016 17:20 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 2016-03-05 17:20 CST

Support