Dari log error, pesan yang ditampilkan
[2022-05-06 01:28:15] local.ERROR: SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8mb4_unicode_ci,COERCIBLE) for operation 'like' (SQL: select count(*) as aggregate from `clients`
Buat yang belum tau arti collation, collaction adalah aturan dan cara untuk mengurutkan string di MySQL/MariaDB.
Login sebagai root MySQL
sudo mysql DATABASE # contoh sudo mysql client_pertamina
Cek collation dari database
MariaDB [kamuskaro]> SHOW VARIABLES LIKE "collation_database"; +--------------------+--------------------+ | Variable_name | Value | +--------------------+--------------------+ | collation_database | latin1_swedish_ci | +--------------------+--------------------+ 1 row in set (0.003 sec)
Cek tabel yang menggunakan latin1_swedish_ci
MariaDB [client_pertamina]> SELECT TABLE_NAME, DATA_LENGTH,TABLE_COLLATION FROM information_schema.tables WHERE table_schema = DATABASE(); +------------+-------------+-------------------+ | TABLE_NAME | DATA_LENGTH | TABLE_COLLATION | +------------+-------------+-------------------+ | users | 16384 | latin1_swedish_ci | | fdata | 1135084 | utf8_general_ci | | flogs | 16384 | latin1_swedish_ci | +------------+-------------+-------------------+
Cek kolom yang menggunakan latin1_swedish_ci
MariaDB [client_pertamina]> SHOW FULL COLUMNS FROM users; +-------------------+-----------+-------------------+------+-----+---------+----------------+---------------------------------+---------+ | Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment | +-------------------+-----------+-------------------+------+-----+---------+----------------+---------------------------------+---------+ | id | int(11) | NULL | NO | PRI | NULL | auto_increment | select,insert,update,references | | | user | text | latin1_swedish_ci | NO | | NULL | | select,insert,update,references | | | role | text | latin1_swedish_ci | NO | | NULL | | select,insert,update,references | | | created_at | timestamp | NULL | YES | | NULL | | select,insert,update,references | | | updated_at | timestamp | NULL | YES | | NULL | | select,insert,update,references | | +-------------------+-----------+-------------------+------+-----+---------+----------------+---------------------------------+---------+ 5 rows in set (0.001 sec)
setelah mendapatkan informasi database, table dan kolom yang bermasalah mari kita fix satu per satu. Untuk fix-nya kita akan menggunakan utf8_unicode_ci
Set database collation jadi utf8_unicode_ci
ALTER DATABASE client_pertamina DEFAULT COLLATE utf8_unicode_ci;
Set tabel collation jadi utf8_unicode_ci
ALTER TABLE users CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE fdata CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE flogs CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;setelah tabel collation di-fix, kolom didalam tersebut juga otomatis di-fix. Tetapi bila masih belum selesai setelah di cek dengan SHOW FULL COLUMNS FROM users;, selesaikan issuenya dengan, cek terlebih dahulu type kolom
MariaDB [client_pertamina]> describe user; +-------------------+------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------------+------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | user | mediumtext | YES | | NULL | | | role | mediumtext | NO | | NULL | | | created_at | timestamp | YES | | NULL | | | updated_at | timestamp | YES | | NULL | | +-------------------+------------+------+-----+---------+----------------+
ALTER TABLE USER_TABLE MODIFY USER_COLUMN DATA_TYPE collate DATA_COALITION;
# contoh
ALTER TABLE user MODIFY role mediumtext collate utf8_unicode_ci;