Mysql – How to convert a MySQL database from utf8mb4 to utf8

You could try turning on the general query log, which logs every query the server receives, and then use that information to see what's being queried, and then issue those queries yourself and examine the data.

To see the current setting for the general log (location and whether it's enabled):

mysql> SHOW VARIABLES LIKE 'general_log%';  # shows the on/off status and file path

To turn it on:

mysql> SET GLOBAL general_log = on; # or off to disable

Remember to turn if off again when you're done, to avoid filling the disk and potentially degrading performance with excessive I/O.

There are also options for logging to a table instead of a file.

mysql> SHOW VARIABLES LIKE 'log_output';    # shows whether logging to FILE or TABLE

I had the same problem with an Ubuntu installation of MySQL 5.6.23.

I had to edit /etc/mysql/my.conf and add these entries to these sections:

[client]
default-character-set=utf8mb4

[mysqld]
character-set-server = utf8mb4

[mysql]
default-character-set=utf8mb4

Then as root execute: service mysql restart

Both my webserver connections and my local (shell) mysql connections worked with utf8mb4.

INSERT INTO some_table_name
(some_index_id,value_to_be_utf8mb4)
VALUES (55, x'F09F9A8A');

select * from some_table_name;
| some_index_id | value_to_be_utf8mb4 |
|            55 | ?                  |
1 rows in set (0.00 sec)

The following are from a fresh command line instantiation of mysql:

mysql> show variables like "%coll%";
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_general_ci |
| collation_database   | utf8mb4_general_ci |
| collation_server     | utf8mb4_general_ci |
+----------------------+--------------------+

mysql> show variables like "%char%";
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

As a final note, the "character_set_system" is always "utf8" according to the mysql 5.x specifications, so that is normal.