Query builder whereLike charset

[lehnihon]

Hi, has anyone had any charset issues with the whereLike query builder?
I noticed that it adds a COLLATE to the query by default "COLLATE utf8_bin".
How to change this COLLATE or remove this behavior?

select count(*) as total from products where name like '%Produto c%' COLLATE utf8_bin - COLLATION 'utf8_bin' is not valid for CHARACTER SET 'utf8mb4'

[raimai00]

https://medium.com/@neosun249/how-to-fix-collation-utf8-bin-is-not-valid-for-character-set-utf8mb4-in-adonis-js-d74338fb5fd8

this might help

[tomgobich]

Not sure if this will resolve your issue, but you can alter the charset used within config/database.ts within the connection object. If you need this picked up at the database level as well, you may need to re-run your migrations (or alter the charset via a migration for existing tables). It's been a while since I've altered this option so can't remember the exact flow.

mysql: {
    client: 'mysql',
    connection: {
        host: Env.get('MYSQL_HOST'),
        port: Env.get('MYSQL_PORT'),
        user: Env.get('MYSQL_USER'),
        password: Env.get('MYSQL_PASSWORD', ''),
        database: Env.get('MYSQL_DB_NAME'),
        charset: 'utf8mb4' // <- change chartset to utf8mb4
    },
    migrations: {
        naturalSort: true,
    },
    healthCheck: false,
    debug: false,
},