Convert MySQL db character encoding set

Before you begin be sure to take a database dump in case something breaks. Then proceed with changing the character set as follows:

Inspect the current character encoding set

MariaDB [(none)]> SELECT default_character_set_name FROM information_schema.SCHEMATA S WHERE schema_name = "webdbprod";
+----------------------------+
| default_character_set_name |
+----------------------------+
| utf8                       |
+----------------------------+
1 row in set (0.001 sec)

In this session we need to change the type from utf8 to utf8mb4

MariaDB [(none)]> ALTER DATABASE webdbprod CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
Query OK, 1 row affected (0.001 sec)

You can then verify the changes all the way into the table

MariaDB [(none)]> SELECT CCSA.character_set_name FROM information_schema.`TABLES` T,information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA WHERE CCSA.collation_name = T.table_collation AND T.table_schema = "webdbprod" AND T.table_name = "wp_posts";
+--------------------+
| character_set_name |
+--------------------+
| utf8mb4            |
+--------------------+
1 row in set (0.002 sec)

If you run into errors check the WARNINGS, remember to match the COLLATION type with the correct CHARACTER SET

MariaDB [(none)]> SHOW WARNINGS;
+-------+------+----------------------------------------------------------------------+
| Level | Code | Message                                                              |
+-------+------+----------------------------------------------------------------------+
| Error | 1253 | COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'utf8mb4' |
+-------+------+----------------------------------------------------------------------+
1 row in set (0.000 sec)

RELATED POST

Virtual Machine Manager: Error starting domain

Starting up the KVM error occurred Error starting domain: Requested operation is not valid: network 'default' is not active Locate…

Git Commands

How to initialize a Git repo: Everything starts from here. The first step is to initialize a new Git repo…

Lab Hack: Raspberry Pi running VMWare ESXi

As strange as the title sounds, yes I am running VMWare ESXi on a Raspberry Pi 4 Model B (4GB)…

Lab Hack: Ubuntu openssh-server and passwordless authentication

While setting up the lab for a docker swarm training module I decided to spin up a few vms in…