MySQL collation errors using WordPress

0 Flares Twitter 0 Facebook 0 Google+ 0 LinkedIn 0 Email -- Filament.io 0 Flares ×

During the last two working days I spent much time trying to solve a very insidious and never-seen-before issue in WordPress.

 

The problem came with impossibility of logging in to the admin site, due to a continuous redirecting loop to the wp-login.php page. At every login button press, WordPress answered with a bounce to the login page. And so on.

To me and to the IT experts it looked like a HTTP header problem: people already logged in were correctly working inside the backoffice. We tried to investigate the web server (Apache), the caching server (Varnish) and the VPN network. But it was useless.

 

Today I found on the PHP Log a strange MySQL error that helped me to solve the problem. The error line was the following:

WordPress database error Table '*************.wp_redirection_items' doesn't exist for query SELECT wp_redirection_items.*,wp_redirection_groups.tracking,wp_redirection_groups.position AS group_pos,wp_redirection_modules.id AS module_id FROM wp_redirection_items INNER JOIN wp_redirection_groups ON wp_redirection_groups.id=wp_redirection_items.group_id AND wp_redirection_groups.status='enabled' INNER JOIN wp_redirection_modules ON wp_redirection_modules.id=wp_redirection_groups.module_id AND wp_redirection_modules.type='wp' WHERE( wp_redirection_items.regex=1 OR wp_redirection_items.url='*************') done by Red_Item::get_for_url

 

This error changed my previous suspects, and made me search for wp_redirection_modules issues on Google.
The things I found were really unespected!

There is a problem in MySQL that prevents it to JOIN two or more tables if they… have different collations. Yes, it sounds incredible, but it is. My problem was caused by a bugged plugin that created a MySQL table with a different collation from WordPress standard tables.

 

  • WordPress tables have utf8_general_ci collation.
  • This plugin (wp_redirection) tables have latin_swedish_ci collation.

 

I personally use to create latin_swedish_ci InnoDB tables for my projects. Wordpress uses utf8_general_ci MyISAM. So it would be normal to add collation attribute at the end of the “CREATE” command on MySQL in installation files. But many developers don’t do this. So MySQL engine assigns the database collation by default to the new table. My database obviously had latin_swedish_ci

 

The solution for my problem so was simple: to change collation on the involved plugin tables.

Magically WordPress login came back to work!

I cannot stop asking to myself: why MySQL cannot do a simple JOIN between two tables if they have a different collation?
This is a mistery that I really won’t like to investigate!

 

0 Flares Twitter 0 Facebook 0 Google+ 0 LinkedIn 0 Email -- Filament.io 0 Flares ×

One Reply to “MySQL collation errors using WordPress”

  1. No mystery about it: the tables would be out of order in respect of each other, making a join either impossible or very prone to error. Safer to reject it until the problem is fixed, but the error message should be clearer.

Leave a Reply

Your email address will not be published. Required fields are marked *