Illegal mix of collations for operation 'like' while searching with Ignited-Datatables

Question

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?

One option is to use `convert(datefield using utf-8)`... – bbonev Jun 06 '17 at 07:47 — bbonev, Jun 06 '17 at 07:47

score 20 · Accepted Answer · answered Sep 06 '13 at 06:10

20

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.

answered Sep 06 '13 at 06:10

YahyaE

1,057
1
9
24

1

Merhaba. did you fix this issue without removing DATETIME? – Rashad Sep 04 '14 at 08:23
3

No, LIKE and DATETIME incompitablity is a MySQL problem. Hope to be fixed in later versions. – YahyaE Sep 04 '14 at 11:37
As I understand, the problem is on JSON UNICODE DECODES. So, in PHP 5.4 it should work. Did you try it at the latest PHP version? – Rashad Sep 05 '14 at 11:42
@RiKo You may be right. The question is one year old, so I did not try with the latest version. Let me and community know if you try and make it work. Thanks – YahyaE Sep 06 '14 at 08:33
@RiKo Did you try with the latest version? – YahyaE Oct 06 '14 at 17:38
here's a documented mysql bug, for reference: https://bugs.mysql.com/bug.php?id=61034 – Scadge Aug 23 '17 at 15:43

doitlikejustin · Answer 2 · 2013-09-05T06:46:32.910

4

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.

edited Sep 05 '13 at 06:46

answered Sep 05 '13 at 06:39

doitlikejustin

6,293
2
40
68

Thanks for the knowledge but it didn't work. I think it is something about how the library handles the post data. – YahyaE Sep 05 '13 at 08:18
Why don't you try `var_dump($this->input->post());` in your controller to see exactly what CI is doing to those chars. – doitlikejustin Sep 05 '13 at 14:57
I did. It is where the problem is. I am stacked with this, how come post is not utf-8? – YahyaE Sep 06 '13 at 03:16
The POST seems UTF-8, it is okay. Something wrong with Ignited-Datatables_ – YahyaE Sep 06 '13 at 05:03
Further reference [link] (https://github.com/IgnitedDatatables/Ignited-Datatables/issues/38) – YahyaE Sep 06 '13 at 05:05

score 3 · Answer 3 · answered Mar 22 '18 at 10:31

3

The question is a little bit old. Finally I find a solution change "LIKE " TO "LIKE binary "

answered Mar 22 '18 at 10:31

Tse Ka Leong

408
4
20

score 3 · Answer 4 · answered Mar 10 '21 at 13:33

I was having the same problem in Datatable search ssp.class.php i solved by converting to UTF8 like :

CONVERT(`user_datetime` USING utf8)

fix in ssp.class.php:

$globalSearch[] = "CONVERT(`".$column['db']."` USING utf8) LIKE ".$binding;

My final code was :

static function filter ( $request, $columns, &$bindings )
{
    $globalSearch = array();
    $columnSearch = array();
    $dtColumns = self::pluck( $columns, 'dt' );
 
    if ( isset($request['search']) && $request['search']['value'] != '' ) {
        $str = $request['search']['value'];
 
        for ( $i=0, $ien=count($request['columns']) ; $i<$ien ; $i++ ) {
            $requestColumn = $request['columns'][$i];
            $columnIdx = array_search( $requestColumn['data'], $dtColumns );
            $column = $columns[ $columnIdx ];
 
            if ( $requestColumn['searchable'] == 'true' ) {
                $binding = self::bind( $bindings, '%'.$str.'%', PDO::PARAM_STR );
                $globalSearch[] = "CONVERT(`".$column['db']."` USING utf8) LIKE ".$binding;
            }
        }
    }

score 1 · Answer 5 · answered Aug 17 '16 at 04:15

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.

Not in a position to test it but I liked the idea. – YahyaE Aug 18 '16 at 21:12 — YahyaE, Aug 18 '16 at 21:12

score 1 · Answer 6 · answered Feb 14 '19 at 09:11

1

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.

answered Feb 14 '19 at 09:11

Andron

6,413
4
43
56

Illegal mix of collations for operation 'like' while searching with Ignited-Datatables

6 Answers6

Linked