This is caused by a third party plugin which made changes to the core WordPress database tables. That's a big no-no, it's called a core hack. As every core hack ever made, it causes problems its developer couldn't think of — if they could think of these problems they wouldn't be stupid enough to go on with a core hack to begin with. You would have this problem even if you didn't use our software and tried the “traditional” site transfer of ZIPping your files and using phpMyAdmin to dump and restore your database. The root cause is that the third party developer does not understand how MySQL works and did some really stupid things for no good reason, causing problems for everybody using a local server, or at least a server which doesn't use the DYNAMIC or COMPRESSED InnoDB row format by default. But I'm getting ahead of myself.
The third party plugin added several indices to the wp_posts table: anta_post_modified_gmt, anta_post_date, anta_post_status, anta_post_type, anta_post_modified, anta_guid. This results in the total size of the index data per record to exceed the maximum allowed by MySQL (767 bytes in the default configuration of MySQL versions before 5.7.9, which is the configuration WAMPServer is still using as they have no idea how to set up the servers they ship correctly, long story…). As a result, the database became impossible to restore unless you EITHER stop using UTF8MB4 (which means that Emoji and some other Unicode characters such as some extended Chinese and Japanese characters will be lost or replaced with empty boxes or question marks, depending on the MySQL version) OR if you change the MySQL configuration to allow for larger index sizes (see https://dev.mysql.com/doc/refman/8.0/en/innodb-limits.html).
In the former case, during restoration in the Database Restoration page you will need to set the following options:
- Force UTF-8 collation on tables: checked
- Allow UTF8MB4 auto-detection: NOT checked
This will try to convert all tables from UTF8MB4 to plain old UTF8. Please remember that this may very well result in loss of data .
In the latter case you will need to set the default InnoDB table format to DYNAMIC, i.e. edit your MySQL configuration file (
my.cnf) and set
innodb_default_row_format=DYNAMIC and restart MySQL. This extends the maximum MySQL index data size from 767 bytes to 3072 bytes which should be enough for these errant indices. Also please note that newer MySQL versions (5.7.9 and later) already use
innodb_default_row_format=DYNAMIC by default — that's why this worked on your live site but NOT your local server.
Of course the best approach is to reach out to the developer of the plugin which added these indices and let them know that the indices they added do cause problems. In fact, the index that causes the most problems is anta_guid since it tries to index the entire guid which is a VΑRCHΑR(255) meaning that its index size is 255 maximum characters times 4 maximum bytes per UTF8ΜB4 character = 1020 bytes. This will never work on any MySQL/MariaDB/Percona configuration which does not use DΥNAΜIC or CΟMRΡΕSSED InnoDB tables, and there is absolutely no benefit creating an full index on the GUID as the index search does not speed up SELECT queries unless the only WΗΕRΕ clause is a guid column equality (all other indices will be considered more specific by the MySQL query parser and optimiser, also noting that MySQL can only use one index per table per subquery). In other words, the developer of the plugin added indices left and right without understanding that they do, how to put it politely, absolutely sod all to speed up SΕLΕCΤ queries because said developer has not bothered reading the MySQL documentation or even running an EΧΡLΑΙΝ on their slow queries to understand exactly how they work. Tell them that, for the love of Cthulhu, they need to understand how things work before they start doing random stupid things left and right with reckless abandon.
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!