The utf8mb4 Is Not Working

[arshidkv12]

Hi I am connecting to a remote MYSQL server. I tried with the following code. Emoji is saving as ???.

Set the remote server as follows. https://i.imgur.com/Ktx8flD.png

const connection = mysql.createConnection({
    host:  config.DB_HOST,
    user:  config.DB_USERNAME,
    password: config.DB_PASSWORD, 
    database: config.DB_DATABASE,
    charset: 'utf8mb4'
  });
connection.query(
  'INSERT INTO emails ( `subject` )   VALUES (" subject 👋" );',
);

Environment

Node.js version: 16.14.2 Database & Version: 10.3.39-MariaDB-cll-lve Connector library & Version: MYSQL2 3.6.5

[arshidkv12]

It is working if I add the following code (each time).

connection.query("SET CHARACTER SET utf8mb4");
connection.query(
  'INSERT INTO emails ( `subject` )   VALUES (" subject 👋" );',
);

[sidorares]

@arshidkv12 can you try using UTF8MB4_GENERAL_CI as charset value? I suspect your charset is not recognised. If that's the case we should probably warn users

The reason your second example works is that we track state variables updates and is "CHARACTER SET" variable is updated connection knows that - see https://github.com/sidorares/node-mysql2/blob/489154f98224fad8036b87c63df8b736ede1c4aa/lib/packets/resultset_header.js#L65-L68

[arshidkv12]

I attempted the code below, but it didn't work.

var connection = mysql.createConnection({
    host:  config.DB_HOST,
    user:  config.DB_USERNAME,
    password: config.DB_PASSWORD, 
    database: config.DB_DATABASE,
    charset: 'UTF8MB4_GENERAL_CI'
});

OR

var connection = mysql.createConnection({
    host:  config.DB_HOST,
    user:  config.DB_USERNAME,
    password: config.DB_PASSWORD, 
    database: config.DB_DATABASE,
    charsetNumber:45
});

[arshidkv12]

How to fix it? Can I set charsetNumber:45 by hardcode?