[Bug]: 'utf8_bin' is not valid for CHARACTER SET 'utf8mb4' Changing charset in ConnectionOptions doesn't make a difference

[k3rne1pan1c]

Contact Details

No response

What happened?

I want to filter for anything. I am connected to Mysql Database. List, Show, Delete works. Sorting works, but when trying to filter, anything is failing.

Error: COLLATION 'utf8_bin' is not valid for CHARACTER SET 'utf8mb4'
    at Packet.asError (/app/node_modules/mysql2/lib/packets/packet.js:740:17)
    at Query.execute (/app/node_modules/mysql2/lib/commands/command.js:29:26)
    at Connection.handlePacket (/app/node_modules/mysql2/lib/base/connection.js:475:34)
    at PacketParser.onPacket (/app/node_modules/mysql2/lib/base/connection.js:93:12)
    at PacketParser.executeStart (/app/node_modules/mysql2/lib/packet_parser.js:75:16)
    at Socket.<anonymous> (/app/node_modules/mysql2/lib/base/connection.js:100:25)
    at Socket.emit (node:events:517:28)
    at addChunk (node:internal/streams/readable:368:12)
    at readableAddChunk (node:internal/streams/readable:341:9)
    at Readable.push (node:internal/streams/readable:278:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23)

Then I was trying to change the ConnectionOptions:

export const connectionOptions: ConnectionOptions = {
  ... Other options ...
  charset: 'utf8mb4', // Ensure charset matches your DB
};

This doesn't make any difference and I still receive an error. utf8mb4 is the standard for Mysql. So what can I do to change the underlying charset?

This only happens when filtering after string based queries: String, UUID, but textarea works again. Boolean, other values working fine.

Bug prevalence

Everytime, I am trying to filter for string based queries, but textarea

AdminJS dependencies version

"dependencies": {
    "@adminjs/express": "^6.1.0",
    "@adminjs/mongoose": "^4.1.0",
    "@adminjs/sql": "^2.2.1",
    "@adminjs/themes": "^1.0.1",
    "@emotion/react": "^11.14.0",
    "@emotion/styled": "^11.14.0",
    "@fontsource/roboto": "^5.2.5",
    "@mui/material": "^6.4.7",
    "adminjs": "^7.7.2",
    "axios": "^1.8.2",
    "express": "^4.18.2",
    "express-formidable": "^1.2.0",
    "express-session": "^1.17.3",
    "mongoose": "^8.12.1",
    "mysql2": "latest",
    "react-simple-maps": "^3.0.0"
  },

What browsers do you see the problem on?

Firefox

Relevant log output

Error: select * from `ReportPost` where `reason` like '%Spam oder unerwünschte Inhalte%' COLLATE utf8_bin order by `id` asc limit 10 - COLLATION 'utf8_bin' is not valid for CHARACTER SET 'utf8mb4'
    at Packet.asError (/app/node_modules/mysql2/lib/packets/packet.js:740:17)
    at Query.execute (/app/node_modules/mysql2/lib/commands/command.js:29:26)
    at Connection.handlePacket (/app/node_modules/mysql2/lib/base/connection.js:475:34)
    at PacketParser.onPacket (/app/node_modules/mysql2/lib/base/connection.js:93:12)
    at PacketParser.executeStart (/app/node_modules/mysql2/lib/packet_parser.js:75:16)
    at Socket.<anonymous> (/app/node_modules/mysql2/lib/base/connection.js:100:25)
    at Socket.emit (node:events:517:28)
    at addChunk (node:internal/streams/readable:368:12)
    at readableAddChunk (node:internal/streams/readable:341:9)
    at Readable.push (node:internal/streams/readable:278:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23)

Relevant code that's giving you issues

resource: db.table('ReportPost'),
    options: {
      navigation: { name: 'Reports', icon: 'Alert' },
      properties: {
        id: {
          type: 'uuid',
          isVisible: { list: true, show: true, edit: false, filter: true },
        },
        reporter_id: {
          type: 'uuid',
          isVisible: { list: true, show: true, edit: false, filter: true },
        },
        user_id: {
          type: 'uuid',
          isVisible: false
        },
        post_id: {
          type: 'uuid',
          isVisible: { list: true, show: true, edit: false, filter: true },
        },
        reason: {
          type: 'string',
          isVisible: { list: true, show: true, edit: true, filter: true },
        },
        description: {
          type: 'textarea',
          isVisible: { list: true, show: true, edit: true, filter: true },
        },
        archived: {
          type: 'boolean',
          isVisible: { list: true, show: true, edit: true, filter: true },
        },
        created_at: {
          type: 'datetime',
          isVisible: { list: true, show: true, edit: false, filter: true },
        },
        updated_at: {
          type: 'datetime',
          isVisible: { list: true, show: true, edit: false, filter: true },
        },
      },
    },
  },

[x-46]

Setting the value to 'utf8_bin' worked for me

export const connectionOptions: ConnectionOptions = {
  ... Other options ...
  charset: 'utf8_bin', // <--- !
};