Search filtering broken on MySQL

[rnayabed]

We self host kutt with mysql2 with the following environment variables:

• JWT_SECRET
• DB_CLIENT
• DB_HOST
• DB_PORT
• DB_NAME
• DB_USER
• DB_PASSWORD
• DEFAULT_DOMAIN
• CUSTOM_DOMAIN_USE_HTTPS

so far everything works perfectly. links get saved, API works, very grateful for the work.

however, search seems to be broken. going to admin panel and searching for any links gets stuck on "Loading Links" forever. I then ran it locally with same creds using npm run dev and seems like there is a collation issue:

> Ready on http://localhost:3000
Error: select count(*) as `count` from `links` left join `domains` on `links`.`domain_id` = `domains`.`id` where `links`.`user_id` = 1 and concat_ws(' ', description, links.address, target, domains.address) like '%elixir%' COLLATE utf8_bin - COLLATION 'utf8_bin' is not valid for CHARACTER SET 'utf8mb4'
    at Packet.asError (/run/media/rnayabed/HDD/code/thedevs-network/kutt/node_modules/mysql2/lib/packets/packet.js:740:17)
    at Query.execute (/run/media/rnayabed/HDD/code/thedevs-network/kutt/node_modules/mysql2/lib/commands/command.js:29:26)
    at Connection.handlePacket (/run/media/rnayabed/HDD/code/thedevs-network/kutt/node_modules/mysql2/lib/base/connection.js:475:34)
    at PacketParser.onPacket (/run/media/rnayabed/HDD/code/thedevs-network/kutt/node_modules/mysql2/lib/base/connection.js:93:12)
    at PacketParser.executeStart (/run/media/rnayabed/HDD/code/thedevs-network/kutt/node_modules/mysql2/lib/packet_parser.js:75:16)
    at Socket.<anonymous> (/run/media/rnayabed/HDD/code/thedevs-network/kutt/node_modules/mysql2/lib/base/connection.js:100:25)
    at Socket.emit (node:events:507:28)
    at addChunk (node:internal/streams/readable:559:12)
    at readableAddChunkPushByteMode (node:internal/streams/readable:510:3)
    at Readable.push (node:internal/streams/readable:390:5) {
  code: 'ER_COLLATION_CHARSET_MISMATCH',
  errno: 1253,
  sqlState: '42000',
  sqlMessage: "COLLATION 'utf8_bin' is not valid for CHARACTER SET 'utf8mb4'",
  sql: "select count(*) as `count` from `links` left join `domains` on `links`.`domain_id` = `domains`.`id` where `links`.`user_id` = 1 and concat_ws(' ', description, links.address, target, domains.address) like '%elixir%' COLLATE utf8_bin"
}

Our db uses utf8mb4 with collation utf8mb4_0900_ai_ci

this seems to be calling the following

kutt/server/queries/link.queries.js

Line 141 in c2f6c7f

async function getAdmin(match, params) {

which relies on

kutt/server/knex.js

Line 31 in c2f6c7f

db.compatibleILIKE = isPostgres ? "andWhereILike" : "andWhereLike";

when I change the andWhereLike to andWhereILike the search begins to work and queries no longer seem to add COLLATE utf8_bin

this seems to be a hardcoded thing within knex:

https://github.com/knex/knex/blob/01b177c485d696f1b72858dee728ba143c4fad76/lib/dialects/mysql/query/mysql-querycompiler.js#L203-L215

  whereLike(statement) {
    return `${this._columnClause(statement)} ${this._not(
      statement,
      'like '
    )}${this._valueClause(statement)} COLLATE utf8_bin`;
  }

  whereILike(statement) {
    return `${this._columnClause(statement)} ${this._not(
      statement,
      'like '
    )}${this._valueClause(statement)}`;
  }

I wanted to make a PR but looks like this query[knex.compatibleILIKE] code was added as part of a PR #794 to fix supposedly broken filtering table rows on sqllite and mysql