MySQL + utf-8 = A drama filled weekend

MySQL + utf-8 = A drama filled weekend

Exciting stuff for a Saturday, eh? The boss has been cracking the whip so have to put in some extra hours wherever I can

If you’re working on the weekend you actually want it to be something interesting!

There were a few things on the docket left over from the week – As I mentioned in a recent post, if you haven’t stumbled across utf-8 already, you soon will. I’ve come across two issues recently whereby chars weren’t rendering correctly an I needed to look under the hood.

The more you delve into CS the greater the likelihood that you will need to develop a deeper understanding of character sets and encoding schemes. Eventually you’ll just have to peel that layer of abstraction away. Sorry to be the one to tell you.

Can you see the problem? Fixed!

Puzzle Pieces

There were two issues I needed to address when sending data from MySQL through to the Slack API as far as utf-8 was concerned (See points 1 and 2). The harder issue to resolve, however, was correctly rendering utf-8 within a WordPress text widget which consumed the bulk of my weekend.

Such is the life of an IT Craftsman.

  1. MySQL db connection

    After establishing the db connection you need to ensure the pipe is using utf-8. Here’s some code I found which does the trick nicely!

  2. CURL call

    We also need to set the charset in cURL within the Content-Type as follows:

  3. Rendering output in the browser

    Here’s a widget that I use to display newsfeed items via Tiny Tiny RSS from a MySQL db. Can you see the problem here?

    In this case the fix was pretty simple somewhat difficult. I was rendering ad-hoc HTML into a table but had forgotten to output the <meta> tag <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    That’s an easy one to fix – then as the French say “pas de problème”

    Nope – that’s not it!

    Hold that thought .. I need to do something else, as I’m still having the issue even after implementing the above. I quick look on Stack Overflow (where I spend most of my day 555) and I note the following about those dreaded questionmarks enclosed in black diamonds.

    The test script prints everything OK but it is running outside of the WP environment. Boy this is getting frustrating !!

    Here’s another look at the problem. See the Content-Type and Encoding. They don’t look right to me.

Another thing that I’ve realised is that my db charsets seem all over the place

Post Mortem

Why won’t MySQL talk utf-8?

Thought i had MySQL driver issues at one stage but was barking up the wrong tree.

There’s an excellent article here about finding which my.cnf MySQL is currently using but I’ve had no success with it 🙁

Let’s take a look at the driver versions currently in use:

MySQL client info: mysqlnd 5.0.12-dev – 20150407 – $Id: b5c5906d452ec590732a93b051f3827e02749b83 $
Client library version: 50012

I’d suggest that you only use the wpdb class for WP-related db access. It abstracts away the complexity (and hence also the power) of the mysqli class, which when faced with charset and encoding issues doesn’t help you too much.

In my case there were two issues which took a considerable amount of time to resolve

  1. Switching from wpdb to mysqli

  2. For a general query you’ll need to use mysqli::query instead of wpdb::get_results(). Make sure you understand how to use the classes, especially the desired return-type of the function call.

  3. A typo

    There’s no ‘t’ in charset folks – but it had me chasing my tail and thinking that I had a strange driver issue. Argh!

    Can you spot it?

I wish I’d come across these debugging tips earlier, but it rarely works out that way 🙁

I’d rather be here!

Leave a Reply

Be the First to Comment!

Notify of