I have successfully implemented Ignited-Datatables. However, while searching with database when typing "non-latin" characters like "İ,ş,ğ,.."
POST http://vproject.dev/module/user/ign_listing 500 (Internal Server Error)
Details are:
Illegal mix of collations for operation 'like' while searching
... (u.id_user LIKE '%Ä°%' OR u.first_name LIKE '%Ä°%' OR u.last_name LIKE '%Ä°%' OR ue.email LIKE '%Ä°%' OR u.last_login LIKE '%Ä°%' ) ...
%Ä°% part changes according to the non-latin character you typed.
Any idea for solving this?
I figured out the problem. It seems it is DATETIME fields that causes the problem.
.. ue.last_login '%ayşenur%'
gives error for Illegal mix of collations for operation 'like'. When I remove LIKE partials DATETIME fields, there are no error any more. I hope this helps.
Try the following:
u.id_user LIKE '%Ä°%' OR ... OR ... '%Ä°%' COLLATE utf8_bin
Refer to MySQL Unicode Character Sets
Also you can refer to MySQL _bin and binary Collations for more information on utf8_bin:
Nonbinary strings (as stored in the CHAR, VARCHAR, and TEXT data
types) have a character set and collation. A given character set can
have several collations, each of which defines a particular sorting
and comparison order for the characters in the set. One of these is
the binary collation for the character set, indicated by a _bin suffix
in the collation name. For example, latin1 and utf8 have binary
collations named latin1_bin and utf8_bin.
The question is a little bit old.
Finally I find a solution change "LIKE " TO "LIKE binary "
i know that this is far too late, but, here my workaround.
SELECT * FROM (SELECT DATE_FORMAT(some_date,'%d/%m/%Y') AS some_date FROM some_table)tb1
WHERE some_date LIKE '% $some_variable %'
datetime/date column gives error for Illegal mix of collations for operation 'like', therefore, by converting it, as another table entity, previous column type will be replace with varchar type.
also, make sure to convert any column before convert it to temporary table, to make matching process more easier.
I met a similar error when LIKE was applied to the DateTime column.
So now, instead of using simple date_col LIKE '2019%' I use CAST(date_col AS CHAR) LIKE '2019%'.
The solution was found on the official MySQL bugs website.
