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

I am trying to query a view created on my database but get this error i have tried changing the view to a new view but still getting the same error my whole database is set up to

  'charset' => 'utf8',
            'collation' => 'utf8_unicode_ci',

so this is what I have tried

$onformance= DB::table('fullCompleted')
                     ->where('result','=','Success COLLATE utf8_unicode_ci')

but it still not showing anything please help or just point me to read about eloquent, mysql I have tried all

i would like the eloquent query to be like so

DB::statement("SELECT count(*) FROM fullCompleted WHERE result = 'Success COLLATE utf8_unicode_ci'") ;

tried this it returns one no errors just to say the query return a line would like it to actual return a value

tim3011

9 years ago

Best Answer

Level 2

The sad solution was to both change the db collation and recreate the views/functions to force them to use the current collation. source:http://stackoverflow.com/questions/3029321/troubleshooting-illegal-mix-of-collations-error-in-mysql

paresh

7 years ago

Level 1

When we will compare two string column and column collation is different then we will fetch this issue.

If you don't want change collation in a database table then you can use BINARY operator in where clause like following.

SELECT count(users.id) FROM users inner join phone on BINARY users.name = BINARY phone.name;

$same_users = DB::table('users as u')
          ->join('phone as p', DB::raw('BINARY u.name'), '=', DB::raw('BINARY p.name'))
          ->select('u.id','u.name')
          ->get();

1 like

Please or to participate in this conversation.