CODEWITHSUNDEEP
mysqllaravelcollation
Rehan
Rehan

Reputation: 4013

Illegal mix of collation in laravel

I have been trying to implement emoji in table. I have changed the column collation to utf8mb4_unicode_ci and the rest of the columns collation are empty but when I try to query out that. I get an error like so

SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8_unicode_ci,COERCIBLE) for operation '=' (SQL: select count(*) as aggregate from qa_defect_comments where defect_id = 2957 and logged_user_id = 2 and comments = 😂😂)

Can we set the collation during querying out the data?

EDIT- SHOW CREATE TABLE qa_defect_comments

CREATE TABLE `qa_defect_comments` (
 `comments_id` int(11) NOT NULL AUTO_INCREMENT,
 `defect_id` int(11) NOT NULL,
 `project_id` int(11) NOT NULL,
 `comments` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL,
 `logged_user_id` int(11) NOT NULL,
 `assign_user_id` int(11) NOT NULL,
 `created_at` datetime NOT NULL,
 `updated_at` datetime NOT NULL,
 `created_time` datetime NOT NULL,
 PRIMARY KEY (`comments_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1793 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC

SHOW VARIABLES LIKE 'char%';

character_set_client - utf8mb4
character_set_connection - utf8mb4
character_set_database - latin1
character_set_filesystem - binary
character_set_results - utf8mb4
character_set_server - latin1
character_set_system - utf8
character_sets_dir - /usr/share/percona-server/charsets/

SHOW VARIABLES LIKE 'coll%';

collation_connection - utf8mb4_unicode_ci
collation_database - latin1_swedish_ci
collation_server - latin1_swedish_ci

Upvotes: 1

Views: 8389

Answers (2)

Prem Sagar
Prem Sagar

Reputation: 165

I also faced the same issue. I resolved it by:

  1. Navigating to phpMyadmin
  2. Select the database your application is using
  3. Click on Operations
  4. Select Collation: utf8_general_ci
  5. Click on Go

Flushed the laravel configuration cache:

php artisan config:cache

Upvotes: 0

Rick James
Rick James

Reputation: 142528

Check config/database.php. I think you will find a utf8 in one or two places, where you should have utf8mb4.

'mysql' => [..., 'charset' => 'utf8mb4', 'collation' => 'utf8mb4_unicode_ci', ...]

Upvotes: 3

Related Questions