Conversion from collation utf8mb4_unicode_ci into utf8mb3_general_ci impossible for parameter

[mondalaci]

I'm running mysqld 8.0.32, node-mysql2 2.1.0, and the following code triggers the error of the subject:

await db.execute(`
    UPDATE orders
    SET leadSource = ?
    WHERE id = ?
`, ['😅', 1]);

The collation of the leadSource column is utf8mb3_general_ci.

[sidorares]

can you try to prepare a small minimal example I can try running locally ( creates temporarily table, populates with required data and then runs UPDATE statement )? That would make debugging much easier for me

[mondalaci]

The following self-contained example reproduces the issue:

const conn = await mysql.createConnection(config.mysql);
await conn.execute("CREATE TABLE test ( id INT NOT NULL , text VARCHAR(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL ) ENGINE = InnoDB;");
await conn.execute(`UPDATE test SET text=? WHERE id=?`, ['😅', 1]);
conn.end();

But then I googled for "utf8mb3 vs utf8mb4" which yield:

utf8mb3 supports only characters in the Basic Multilingual Plane (BMP). utf8mb4 additionally supports supplementary characters that lie outside the BMP. utf8mb3 uses a maximum of three bytes per character. utf8mb4 uses a maximum of four bytes per character.

And indeed, using utf8mb4 collation and charset instead of utf8mb3 makes this a non-issue. Feel free to close this issue if you think there's nothing to be done on your side.

[sidorares]

yeah, in general its best to always use utf8mb4 as this is the real "proper modern" utf8. utf8 and utf8mb3 existed in mysql before / during utf8 was developed and are historical artifacts in a way

see some discussion in #374 (comment)

There might be a way to insert non-bmp characters using surrogates with utf8mb3 ( takes 6 bytes ), but I might be wrong here ( #1240 (comment) )