After getting the new xampp on my machine whenever I install the wordpress the database collation is defaulting to "utf8mb4_unicode_ci" and this database cannot be imported on server having mysql lower than 5.5.3. Had a lot of pain going on like changing the collation for each table and then any new table created by plugins are also defaulting to the same collation then convert them again. Looked on several place for a fix that doesn't hack the wordpress core, but couldn't find one. I Read some more and people are talking about hacking or upgrading mysql instead of a current server solution. And if you hack the core when you are going upgrade wordpress the core goes back to the default.
So had to figure out myself and it was really simple but kinda tricky. To get similar results, when you are installing a new wordpress you'll have to do some careful steps. Before installation, wordpress does not have the config.php. It creates it on the fly when you put db credentials on the form and submit that. The next screens sets up the admin credentials and installation starts. So when installing wordpress you will have the following screens one after other and you are going do as I did.
- Select the language. (url: http://localhost/project_name/wp-admin/setup-config.php)
- Hit the "let's go" button. (url: http://localhost/project_name/wp-admin/setup-config.php?step=0)
- Put the db credentials and hit submit. (http://localhost/project_name/wp-admin/setup-config.php?step=1)
- Now go to the file wp-config.php on your wordpress installtion's root directory
- look for "define('DB_CHARSET', 'utf8mb4');". It may also say utf8mb4. Delete the db_charset value. It should look like "define('DB_CHARSET', '');"
- Then right after that you should see "define('DB_COLLATE', '');". Put value utf8_general_ci in the value, like "define('DB_COLLATE', 'utf8_general_ci');".
- Now go back to browser where you left the wordpress installation and hit "Run the Installation" button. (http://localhost/project_name/wp-admin/setup-config.php?step=2)
- Put admin credentials and hit the "Install Wordpress" button. (http://localhost/project_name/wp-admin/install.php?language=en_US)
- Goto phpmyadmin and you should see tables are created with collation "utf8_general_ci".
This applies for all the servers with mysql version 5.5.3 or higher (including all 10.x versions of MariaDB).
Wordpress utf8mb4 db_chartset problem
Wordpress database to utf8_general_ci
Unknown collation: 'utf8mb4_unicode_ci'