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

Updating $PATH for Laravel in Ubuntu 20.04

In order to get #laravel command to work make sure its included in the $PATH $ composer global require laravel/installer…

MySQL: Automate MySQL Dump

Need to backup all the databases daily mysqldump --all-databases --single-transaction --quick --lock-tables=false > full-backup-$(date +%F).sql -u root -p Then edit…

Satellite Server: katello-service

Time for some upgrades and backups of the satellite server, first inspect the current services running and enabled [root@rhel75lab ~]#…

UFW: Permit multiple ports

Allow the multiple ports in entire range by ufw (tcp/udp) kewrunner@serv01:~$ sudo ufw allow 82301:82320/tcp [sudo] password for kewrunner: Rule…