SQL problem

  • Resolved jkacher

    (@jkacher)


    8 months, 2 weeks ago

    Clicking Global Filter gets this error:
    Illegal mix of collations (utf8mb4_unicode_ci,COERCIBLE) and (utf8mb4_unicode_520_ci,COERCIBLE) for operation ‘like’

    Perhaps I have some foreign characters in the data, how can I check?

Viewing 4 replies - 1 through 4 (of 4 total)
  • Thread Starter jkacher

    (@jkacher)

    8 months, 2 weeks ago

    This happened after I replaced the source from table to view consisting of two tables, and used with one function. The only utf8mb4_unicode_520_ci tables are those used by wordpress. My tables use utf8mb4_unicode_ci

    Plugin Contributor Kim L

    (@kimmyx)

    8 months, 1 week ago

    Hi @jkacher,

    Thanks for the post.

    Could you send us a ticket regarding this issue? We might need to take a closer look at your data source.

    We’ll wait for your response!

    Thread Starter jkacher

    (@jkacher)

    8 months, 1 week ago

    I did respond in the email. I traced the problem all the way to the Group_concat() function in MariaDB, it adds a Separator that is of a different collation. Your Global Filter uses a simple like ‘%x%’ , and it went all the way down through View to my function. I had to materialize the View first, so your Global Filter worked on the result. To do this I set the Algorithm=Temptable in the create view, that solved the problem. So no need for a ticket, thanks for all the support you give us.

    Plugin Contributor Kim L

    (@kimmyx)

    8 months, 1 week ago

    Hi @jkacher,

    Thanks a lot for the update.

    We’re glad you were able to narrow down the cause. 🙏

    Feel free to create a new topic if you have new questions.

Viewing 4 replies - 1 through 4 (of 4 total)

The topic ‘SQL problem’ is closed to new replies.