Unknown Collation Error Migrating Newer MySQL DB to Older MySQL DB

Today I will explain how I was able to fix the Unknown collation error when importing a MySQL database.

I was moving a completed WordPress site this weekend from my hosting account to a client’s hosting account and was surprised to receive an error upon importing the database into the new empty database.

The error was:

Unknown collation: ‘utf8mb4_unicode_520_ci’

Upon Googling it, I discovered that this is a typical error when importing a database from a newer version of MySQL to an older version of MySQL.

The solution was pretty simple. Export the database in a format that is compatible to both, which turned out to be MYSQL40.  I also chose that format when importing the database. Worked fine and I was back on track again.

Troubleshooting Reasons for an Unusually Large WordPress Database

My client informed me that when she viewed her website, it was displaying the “Error Establishing Database Connection” message.

When I went to wp-admin, it was displaying another similar message about the database. I logged into the Control Panel and was able to view the database in PHPMYADMIN. I checked to make sure the database name, username, and password matched. Called GoDaddy and found out that the database was over the limit allowed by the hosting plan and so the database user was disabled. We had one week to resolve the issue either by reducing the size of the large WordPress database or moving to a hosting plan that had unlimited database space.

I  browsed the database using PHPMyAdmin, and eventually found that there were over 1 million records in wp_posts! Next, I asked the client to move to a higher level hosting plan and got the site back online. There were only about 1,000 actual posts when viewing Posts from the Dashboard. I continued to browse wp_posts and finally something caught my eye. There were lots of posts of Post-type Http, and a field contained ‘core_control_http_log_item’. I Googled the character string and came up with one match! Googled translated the page from French and I found the answer. It was the Core Control plugin, which had been active for several months and had been logging activity in the database. I had been using it to debug Paypal connection problems and had not disabled it.

Next, I had to remove all posts of post-type http. I used the query:

DELETE a,b,c FROM wp_posts a LEFT JOIN wp_term_relationships b ON (a.ID = b.object_id) LEFT JOIN wp_postmeta c ON (a.ID = c.post_id) WHERE a.post_type = ‘http’.

I hope this may help someone else who discovers that they have a large WordPress database that shouldn’t be so excessive in size and need to find the reason why.