#1253 - COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'utf8mb4'

Asked

Viewed 425 times

0

Why if there is an INNER JOIN the QUERY returns the error #1253, but without INNER JOIN the QUERY returns the records correctly, both are ?

The query is simple, so it works:

SELECT 
    a.* 
FROM 
    `produtos` a
WHERE 
    a.`titulo` LIKE '%".$termo_pesquisa."%' collate utf8_general_ci 
OR 
    a.`descricao` LIKE '%".$termo_pesquisa."%' collate utf8_general_ci

Increment returns the error:

SELECT 
    a.* 
FROM 
    `produtos` a
INNER JOIN
    `categorias` c
ON 
    c.`cat_id` = a.`categoria`
WHERE 
    a.`titulo` LIKE '%".$termo_pesquisa."%' collate utf8_general_ci 
OR 
    a.`descricao` LIKE '%".$termo_pesquisa."%' collate utf8_general_ci

1253 - COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'utf8mb4'

1 - Both tables are Myisam

2 - I inserted the collate to improve the search of keywords because there was some occultation of records due to the high box of characters.

mysql
  • 1

    Dear Eliseu, if you created the tables in utf8mb4 because you are trying force the collate in the LIKE? If the intention is to make CASE-INSENTIVE and accent comparisons with accents maybe you should try utf8mb4_general_ci, or better, check if the tables are no longer utf8mb4_general_ci or utf8mb4_unicode_ci, pq if the tables are, using collate with this would be "redundancy" (or almost, depends on my.ini and client). I won’t go into detail about the general and Unicode and their differences, but it would be interesting to post the PHP part [...]

  • [...] because your attempt to use collate may be to fix a problem that would actually be in PHP

  • @Guilhermenascimento thank you, the redundancy was eliminated and problem solved, it is as utf8_general_ci yes, and forcing the collate was giving error, without it worked, I do not remember what situation I used collate and now does not make sense rs, obrg

  • Caro @Eliseub some of your tables should be as utf8mb4, even if the bank does not, or even can be a configuration on my.ini, But without details, I can’t help. Anyway you have to analyze your php (if it is php) or connection, this well adjusted to work the data entries in SELECT (the variables), it would be even more interesting prepareuse d_statment (https://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php) instead of passing the variables directly , to avoid sqlinjection or simply avoid syntax error triggered by values containing apostrophe

  • @Guilhermenascimento thanks for the tips, the two tables are uft8. is that the hosting arrow the bank as utf8mb4 and I can not change, soon I will migrate, and the variables are treated before being passed, and use mysqli_real_escape_string() inside, is that I simplified there, I have function that makes a beautiful treatment of each type of variable, thanks for everything.

  • who creates the bank can be up to the hosting panel, but the tables and what mysqli_connect() is you, so you are responsible for such an adjustment and not the hosting.... On the other subject, yes the mysqli_real_escape_string already helps, but eventually you can forget about it, if you are very careful until you go :) ... But I think that a beautiful treatment in variables is the kind of redundancy that only causes problems in the future, it’s like a series of repetitive operations and that back and forth we forget, use what is ready, as Prepared, would help and would simplify, but it’s just tips :)

  • @Guilhermenascimento even inside phpmyadmin. They recognized the problem but asked it to adapt the code.

  • But then adapt to work in Rra is gambiarra, which today can work, but in the future will be problem :) .. ps: I will delete almost all comments except the first one. The rest here was just to guide you

Show 3 more comments
No answers

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.