Cannot store emoji in database

Okay I finally managed to make it working!Thanks to everybody that tried to help me, especially @Rick James and @Gerard Roche.

SUGGESTION:

If you need to work with emoji first of all make simple tests on localhost. Create a new database and make a fresh app for testing purpose.

If you follow the steps I wrote in the question or if you follow this tutorial: https://mathiasbynens.be/notes/mysql-utf8mb4#utf8-to-utf8mb4 it must work.

Working locally on a fresh basic app you will have more control and more room to make all the test you need.

SOLUTION:

In my case the problem was in the configuration of the database in CodeIgniter. It was not properly setting up the char_set and the collation for a stupid overlooking: I was overriding the database settings in the function that save messages to be sure it was working with the mobile database.

BEFORE:

function message_save ( $data = FALSE ){       $project_db_config                  = array();    $project_db_config['hostname']      = 'MY_HOST';    $project_db_config['username']      = 'MY_USERNAME';    $project_db_config['password']      = 'MY_PASSWORD';    $project_db_config['database']      = 'MY_DATABASE';    $mobile_db                          = $this->load->database( $project_db_config, TRUE );    // other code to save message       }

AFTER:

function message_save ( $data = FALSE ){    $mobile_db_connection = $this->load->database('admin_mobile_mh', TRUE);    // other code to save message}

CONCLUSION:

The app must set the connection to the database properly. If you have the database properly setup but you don't make the proper connection with your app, it won't work.

So if you encounter similar problems make sure the api properly setup the char_set as utf8mb4 and db_collat as utf8mb4_unicode_ci.

The only way I know of to get ???? for an Emoji is to not have the column declared utf8mb4. I understand that you have apparently determined that the column is declared that way, but please run SHOW CREATE TABLE table_name; to further confirm it.

The system default, the database default, and the table default are irrelevant if the column overrides the CHARACTER SET.

A note to all the other attempted answers: The COLLATION is irrelevant, only the CHARACTER SET is relevant for this question.

my.cnf is loaded first, then conf.d/*.cnf.

Instead of modifying my.cnf *(which may be overridden by configurations in conf.d/*.cnf), create a custom override configuration e.g. conf.d/90-my.cnf.

Prefixing 90 ensures the custom settings are loaded last which means they overwrite any earlier set settings.

To ensure the new configuration is reloaded, see Reload Without Restarting the MySQL service.

Example Configuration Structure (Linux)

.├── conf.d│   ├── 90-my.cnf│   ├── conn.cnf│   ├── my5.6.cnf│   └── mysqld_safe_syslog.cnf├── debian.cnf├── debian-start└── my.cnf

conf.d/90-my.cnf

# https://mathiasbynens.be/notes/mysql-utf8mb4# http://stackoverflow.com/q/3513773/934739[client]default-character-set = utf8mb4[mysql]default-character-set = utf8mb4[mysqld]character-set-client-handshake = FALSE# The server character set and collation are used as default values if the# database character set and collation are not specified in CREATE DATABASE# statements. They have no other purpose.character-set-server = utf8mb4collation-server = utf8mb4_unicode_ci