3

I haven't specifically seen this issue before but

SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_unicode_ci,COERCIBLE) for operation '=' (SQL: 

select `id` from `data` where `thing` = خ@gmail.com limit 1

I'm pretty sure خ@gmail.com is not a valid email address but I am not too sure why Laravel's DB functions passed this to my server. Shouldn't it be aware of the character set and collation of the database it's using? Is the right solution to change the character set and collation to UTF-8 so that this doesn't cause general errors on my MySQL server?

Is there any consensus of what the correct character set and collation should be?

O. Jones
  • 103,626
  • 17
  • 118
  • 172
ZCT
  • 181
  • 1
  • 1
  • 13
  • 1
    Don't you need to encapsulate the value with quotations? `where 'thing' = 'خ@gmail.com'` – Mark Jan 06 '20 at 13:59
  • You should always use `utf8mb4` – Rain Jan 06 '20 at 14:03
  • Hi, I am using Laravel's DB:: and also that is just the output from the error message not the actual query that is being used. Laravel is handling the 'quotations'. – ZCT Jan 06 '20 at 14:03
  • 1
    When Laravel outputs failed queries using prepared statements, it does not show the quotes around the values. Makes it hard to troubleshoot by copy/pasting the query into the command line, since you'll have to manually quote things. – aynber Jan 06 '20 at 14:08
  • Check the definition of the table/column. It should be utf8_unicode_ci, but it's probably set to latin1_swedish_ci. – aynber Jan 06 '20 at 14:09
  • @aynber If it's `utf8` then `@gmail.com` raises the same error. – Rain Jan 06 '20 at 14:12
  • @Rain Ah, okay. Collations confuse me, I was just picking one from the error. :-D – aynber Jan 06 '20 at 14:20

1 Answers1

0

Both the connection and the column definition need to be CHARACTER SET utf8 (or utf8mb4). From the error message, I see that one of them is clearly latin1.

In Laravel's config:

In the file config/database.php: 'mysql' => [..., 'charset' => 'utf8', 'collation' => 'utf8_unicode_ci', ...]

Please provide SHOW CREATE TABLE to discuss the table.

If you end up with gibberish, see Trouble with UTF-8 characters; what I see is not what I stored

The clinking beer mugs () requires utf8mb4 instead of utf8. (utf8mb4 is a superset of utf8.)

Rick James
  • 135,179
  • 13
  • 127
  • 222