php - Cannot store emoji in database

Question

THE SITUATION:

Sorry in advance if this question has already been asked, but the solutions aren't working for me.

No matter what I try, I cannot store emoji in my database. They are saved as ????.
The only emojis that are properly saved are the ones that require only 3 bytes, like the shy face or the sun.

The actual utf8mb4 is not working.

It has been tested on both Android and Ios. With same results.

VERSIONS:

Mysql: 5.5.49
CodeIgniter: 3.0.0

THE STEPS:

1. I have modified database character set and collation properties.

   ALTER DATABASE my_database CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci

2. I have modified table character set and collation properties.

   ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci

3. I have set each field of the table, where possible, as Encoding: UTF-8(ut8mb4) and Collation: utf8mb4_unicode_ci

4. I have modified the database connection in the CodeIgniter app.

5. I have run the following: SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci

6. Lastly I have also tried this: REPAIR TABLE table_name; OPTIMIZE TABLE table_name;

Everything should have been setup properly but yet it doesn't work.

DATABASE SETTINGS:

This is the outcome running the following command:

`SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';`

TABLE SETTINGS:

A screeshot of the table structure:

DATABASE CONNECTION:

These are the database connection settings inside database.php (note this is not the only database, there are also others that connect using utf8)

$db['my_database'] = array(
        'dsn'           => '',
        'hostname'      => PROJECT_DATABASE_HOSTNAME,
        'username'      => PROJECT_DATABASE_USERNAME,
        'password'      => PROJECT_DATABASE_PASSWORD,
        'database'      => PROJECT_DATABASE_NAME,
        'dbdriver'      => 'mysqli',
        'dbprefix'      => '',
        'pconnect'      => FALSE,
        'db_debug'      => TRUE,
        'cache_on'      => FALSE,
        'cachedir'      => '',
        'char_set'      => 'utf8mb4',
        'dbcollat'      => 'utf8mb4_unicode_ci',
        'swap_pre'      => '',
        'encrypt'       => FALSE,
        'compress'      => FALSE,
        'stricton'      => FALSE,
        'failover'      => array(),
        'save_queries'  => TRUE
    );

MY.CNF SETTINGS:

This is the whole content of the file my.cnf:

[mysqld]
default-storage-engine=MyISAM
innodb_file_per_table=1
max_allowed_packet=268435456
open_files_limit=10000
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

THE QUESTION:

Do you know why is not working? Am I missing something?

HYPHOTESIS 1:

I am not sure, but the cause of the problem may be this:

As you can see in my.cnf character-set-server is clearly set as utf8mb4:

But after running the query in the database:

SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';

The outcome is that character-set-server = latin1

Do you know why is that? Why is not actually updating?

HYPHOTESIS 2:

The application use several different databases. This one is set to utf8mb4 but all the others are set to utf8. It may be a problem even if they are separated databases?

Thank you!

EDIT:

This is the outcome of SHOW CREATE TABLE app_messages;

CREATE TABLE `app_messages` (
  `message_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `project_id` bigint(20) NOT NULL,
  `sender_id` bigint(20) NOT NULL,
  `receiver_id` bigint(20) NOT NULL,
  `message` text COLLATE utf8mb4_unicode_ci,
  `timestamp` bigint(20) DEFAULT NULL,
  `is_read` enum('x','') COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`message_id`)
) ENGINE=InnoDB AUTO_INCREMENT=496 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

EDIT 2:

I have run the following command:

INSERT INTO app_messages (message_id, project_id, sender_id, receiver_id, message, timestamp, is_read)
VALUES ('496','322','77','188', '??' ,'1473413606','x');

And other two similar with ?? and ??

They were inserted in the table without problems:

But in the actual app what i really see is: ? (this time only one ? and not 4)

Answer 1

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.