Incorrect string value - MySql

Question

I have a problem with MySql. My version of MYSql is : 5.7.33 - MySQL Community Server (GPL) I have create a discord Bot in node.js, and i have a mistake when a new user with pseudo like this : legoshi

So i have try to follow this topic : How to fix "Incorrect string value" errors?

So i convert my Database in : utf8mb4_unicode_ci

And my error is still here.

At the begin my database was in utf8 and i have the error too.

 code: 'ER_TRUNCATED_WRONG_VALUE_FOR_FIELD',
  errno: 1366,
  sqlMessage: "Incorrect string value: '\\xF0\\x9F\\x8C\\x8C\\xF0\\x9F...' for column 'user' at row 1",
  sqlState: 'HY000',
  index: 0,
  sql: 'INSERT INTO registre (id, user, autohit, ultimate, platinium, `Date Inscription`) VALUES (210490816542670849, "legoshi", 0, 0, 0, CURRENT_TIMESTAMP())'
}

So i don't no how to change this. I have see a lot of topic and all seems to be fix with utf8mb4_unicode_ci but not in my case.

Thanks for you're help.

You should make sure that your connection is also utf8mb4_unicode_ci, because otherwise the byte-sequence in your packet might be unparseable according to the utf8mb4_unicode_ci character set, yielding in an invalid encoding and therefore this error messaage. — Psi, Feb 17 '21 at 17:41
Oh okay i see, so fo you my error is caused because connection is not in utf8 ? I will try it now — Akyna, Feb 17 '21 at 18:04

Psi · Answer 1 · 2021-02-17T17:50:49.397

In MySQL, there are several places where you can set up a character set:

On the server level
On the database level
On the table level (for each table)
On the field level for all character-based fields
On your connection (telling the server what charset will be used in packets you send to the server)

Basically, server-level, database-level and table-level are just defaults for newly created items: New databases are generated with the server's default. New tables are created with the database's default, new fields are created with the table's default. However, only the field-level charset is what actually counts.

So first, you should make sure that the fields you want to store the data in actually are set up to utf8mb4_unicode_ci. Then, you need to connect to the server using exactly the same charset. Be aware that also the collation should match.

You can find out what character set is in use by issuing the following query:

SHOW VARIABLES LIKE 'character_set_%'

You'll see several variables indicating which default is set for various scopes. Have a look especially to the variables character_set_client and character_set_connection. If the connection does not have the correct character set specified, you need to set it up on connection.

It's a good practice to have all character sets match identically. Mixed values will sooner or later cause trouble.

To check the character set which is set up for the field, have it displayed with the command

SHOW CREATE TABLE registre

Okay i see, i have checked all this things before posting. I have try a lot things. Maybe i need to add somethings on my js code for sql like : INSERT INTO registre (id, user, autohit, ultimate, platinium, \`Date Inscription\`) VALUES (${message.author.id}, "${message.author.username}", 0, 0, 0, CURRENT_TIMESTAMP()) I think i maybe need to update this code and force to set in utf8mb4_unicode_ci — Akyna, Feb 17 '21 at 17:50
You need to specify the character set when connecting to the database. Also, you now say something about your "js code". The question is tagged "Java". What is correct here? — Psi, Feb 17 '21 at 17:56
Yes code is write full in js, but for connect JS into MySql i need to do MySQL requests of course. So i think i need to specify the encoding at the connection — Akyna, Feb 17 '21 at 18:08

Incorrect string value - MySql

1 Answers1