2

I'm trying to add a new record to my MySQL table but I'm getting the following error:

An exception occurred while executing 'INSERT INTO team_mate (id, first_name, cached_sessions_count, created_at, updated_at) VALUES (?, ?, ?, ?, ?)' with params ["\xb6\xc4\xcf\x4e\x19\xcc\x43\x04\x82\x63\x79\x31\xec\x21\x5e\x66", "\x3f\x89\x6c\x69\x73\x65", 0, "2019-10-28 20:11:42", "2019-10-28 20:11:42"]: SQLSTATE[HY000]: General error: 1366 Incorrect string value: '\x89lise' for column 'first_name' at row 1

What is very weird is that inserting a new record named Élise doesn't work (and produces this error), but inserting aÉlise does work, in fact the error only happens when the first byte or first character of the field contains an accentued character.

Setup :

  • I use MySQL 8.0.18
  • I connect to my database using charset utf8mb4
  • The table team_mate has collation utf8mb4_unicode_ci
  • The field first_name in my table has collation utf8mb4_unicode_ci

The result of show variables like '%colla%'; performed from the connection:

collation_connection = utf8mb4_unicode_ci
collation_server = utf8mb4_unicode_ci
collation_database = utf8mb4_unicode_ci

The result of show variables like '%charac%'; performed from the connection:

character_set_client = utf8mb4
character_set_connection = utf8mb4
character_set_database = utf8mb4
character_set_filesystem = binary
character_set_results = utf8mb4
character_set_server = utf8mb4
character_set_system = utf8

Thanks!

Arkandias
  • 383
  • 2
  • 14
  • You might need to check your table collation. Otherwise these articles may help you sort this all out. https://stackoverflow.com/a/11013986/5749464 (use utf8mb4 instead of utf8) and https://mathiasbynens.be/notes/mysql-utf8mb4 – J. Schmale Oct 28 '19 at 20:28
  • Hello, thanks, I have checked both articles and then my connection/database/table/field settings and everything seems in order, here is what I have: collation_connection = utf8mb4_unicode_ci collation_server = utf8mb4_unicode_ci collation_database = utf8mb4_unicode_ci character_set_client = utf8mb4 character_set_connection = utf8mb4 character_set_database = utf8mb4 character_set_filesystem = binary character_set_results = utf8mb4 character_set_server = utf8mb4 character_set_system = utf8 Also, both the database collation and table collation are utf8mb4_unicode_ci. – Arkandias Oct 29 '19 at 11:16
  • Maybe the problem isn't mysql, but the app that is executing your insert query for you. – Ayrton Oct 29 '19 at 11:21
  • That's an interesting point! When I try to do the same query using PhpMyAdmin: `INSERT INTO team_mate (id, first_name, cached_sessions_count, created_at, updated_at) VALUES (0xb6c4cf4e19cc430482637931ec215e66, 0x3f896c697365, 0, "2019-10-28 20:11:42", "2019-10-28 20:11:42")` It fails with the same error... but if I do this one using the string values and not the binary ones: `INSERT INTO team_mate (id, first_name, cached_sessions_count, created_at, updated_at) VALUES (0xb6c4cf4e19cc430482637931ec215e66, "Élise", 0, "2019-10-28 20:11:42", "2019-10-28 20:11:42")` It works normally – Arkandias Oct 29 '19 at 11:31
  • Indeed, you were right Ayrton. I'm using Symfony 4.2 with Doctrine standard implementation and it appears that when the first character of the string chain is accentuated, Doctrine considers the full field as binary and sends it as binary to MySQL. However if I try to send "eÉlise", Doctrine sends it as a string and it works. Why does it do that, I don't know yet, but it's not a MySQL question. – Arkandias Oct 29 '19 at 11:39

1 Answers1

1

Ok, nevermind, I shouldn't have blamed Symfony/Doctrine/MySQL... After some debugging, I discovered that I had this function in the code:

public function setFirstName(string $firstName): self
{
    $this->firstName = mb_strtoupper(substr($firstName, 0, 1)).substr($firstName, 1);
}

It was supposed to uppercase the first character, but because the first character extends over more than one byte, this creates wrong bytes that prevent the insertion in the database. I replaced substr by mb_substr and everything was working smoothly then!

Problem solved, thanks Ayrton!

Arkandias
  • 383
  • 2
  • 14