MySQL/MariaDB Character Problems: latin1_ to utf8_

May 19, 2023

•

3 min read

Yesterday I’ve upgraded one of my servers that is running on Ubuntu 18.04; since it’s reaching EOL it’s better to perform timely updates.

While upgrading the OS, why not upgrade MariaDB, right? So, I’ve upgraded it to 10.6.12 and this morning realized that one of the sites has been ruined with messed up characters.

Typically, stored data as latin1 is actually utf8… Since this is an old WordPress database, we had to convert the data into the actual format:

	ALTER TABLE wp_posts CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
	UPDATE wp_posts SET post_content = CONVERT(CAST(CONVERT(post_content USING latin1) AS BINARY) USING utf8);
	UPDATE wp_posts SET post_title = CONVERT(CAST(CONVERT(post_title USING latin1) AS BINARY) USING utf8);
	UPDATE wp_posts SET post_excerpt = CONVERT(CAST(CONVERT(post_excerpt USING latin1) AS BINARY) USING utf8);
	UPDATE wp_posts SET post_status = CONVERT(CAST(CONVERT(post_status USING latin1) AS BINARY) USING utf8);
	UPDATE wp_posts SET comment_status = CONVERT(CAST(CONVERT(comment_status USING latin1) AS BINARY) USING utf8);
	UPDATE wp_posts SET ping_status = CONVERT(CAST(CONVERT(ping_status USING latin1) AS BINARY) USING utf8);
	UPDATE wp_posts SET post_password = CONVERT(CAST(CONVERT(post_password USING latin1) AS BINARY) USING utf8);
	UPDATE wp_posts SET post_name = CONVERT(CAST(CONVERT(post_name USING latin1) AS BINARY) USING utf8);
	UPDATE wp_posts SET to_ping = CONVERT(CAST(CONVERT(to_ping USING latin1) AS BINARY) USING utf8);
	UPDATE wp_posts SET pinged = CONVERT(CAST(CONVERT(pinged USING latin1) AS BINARY) USING utf8);
	UPDATE wp_posts SET post_content_filtered = CONVERT(CAST(CONVERT(post_content_filtered USING latin1) AS BINARY) USING utf8);
	UPDATE wp_posts SET guid = CONVERT(CAST(CONVERT(guid USING latin1) AS BINARY) USING utf8);
	UPDATE wp_posts SET post_type = CONVERT(CAST(CONVERT(post_type USING latin1) AS BINARY) USING utf8);
	UPDATE wp_posts SET post_mime_type = CONVERT(CAST(CONVERT(post_mime_type USING latin1) AS BINARY) USING utf8);

view raw update.sql hosted with ❤ by GitHub

And, it did the trick!

I’ve read a bunch of non-sense solutions since found this one, hope it helps.

If you are setting up a new server, make sure to use utf8mb4 and thank me later 😉

Cheers,

mysql

MySQL/MariaDB Character Problems: latin1_ to utf8_

Leave a Reply Cancel reply