hanko on mariadb doesn't work

[user529]

Checklist

• I could not find a solution in the existing issues or docs.
• I agree to follow this project's Code of Conduct.

Describe the bug

Hi!

Recently I tried to deploy a fresh and self-hosted (localhost) installation of hanko on mariadb and failed.

Logs of hanko-migrade repeatedly displays

2025/01/06 21:57:44 migrate up
2025/01/06 21:57:44 Using config file: /etc/config/config.yaml
[POP] 2025/01/06 21:57:44 info - 0.0363 seconds
2025/01/06 21:57:44 error executing migrations/20240717020707_change_flows.up.fizz, sql: ALTER TABLE `flows` ADD COLUMN `data` VARCHAR (65536) NOT NULL;
ALTER TABLE `flows` DROP COLUMN `stash_data`;
ALTER TABLE `flows` DROP COLUMN `current_state`;
ALTER TABLE `flows` DROP COLUMN `previous_state`;: Error 1074 (42000): Column length too big for column 'data' (max = 16383); use BLOB or TEXT instead

As we can see, it's not possible to apply some ALTER TABLE to flows table from backend/persistence/migrations/20240717020707_change_flows.up.fizz

add_column("flows", "data", "string", {"size": 65536})

OK. A quick googleing brought up some clarifications:

The reason for this is that the maximum number of bytes that MySQL can store in a VARCHAR column is 65,535, 
and that is 21845 characters for utf8mb3 character set and 16383 characters for the utf8mb4 character set.

This is confirmed by checking the table itself:

select * from information_schema.tables where table_name='flows'

TABLE_CATALOG   TABLE_SCHEMA    TABLE_NAME      TABLE_TYPE      ENGINE  VERSION ROW_FORMAT      TABLE_ROWS      AVG_ROW_LENGTH  DATA_LENGTH     MAX_DATA_LENGTH INDEX_LENGTH    DATA_FREE       AUTO_INCREMENT  CREATE_TIME     UPDATE_TIME     CHECK_TIME   TABLE_COLLATION  CHECKSUM        CREATE_OPTIONS  TABLE_COMMENT   MAX_INDEX_LENGTH        TEMPORARY
def     passkey flows   BASE TABLE      InnoDB  10      Dynamic 0       0       16384   0       0       0       NULL    2025-01-06 21:18:59     NULL    NULL    utf8mb4_uca1400_ai_ci   NULL                    0       N

By default, a new table was created with utf8mb4_uca1400_ai_ci. Which is reasonable, because it implements modern The Unicode Standard, Version 14.0.

Just as an experiment, I tried a dirty hack (the idea was that latin2 charset uses 1 byte instead of 4 for utf8mb4 charset)

ALTER TABLE flows CHARACTER SET latin2 COLLATE latin2_general_ci;

But it with no success

Column length too big for column 'data' (max = 65532); use BLOB or TEXT instead

As I learned, Mariadb have internal limitations: row limit is 65535 (sic! it's exactly 4 byte). But even more, on top of that it's require two bytes to store some length.
And in the bottom line mariadb allows allocate only 65532 per row.

But in this case 20240717020707_change_flows.up.fizz fix tries to set field length to 65536. Which is a bit of a strange choice, as you probably know in memory representation it's 4 byte + 1 bit. And it looks like there is no chance to set this length in Mariadb.

So, I'm on the fence now with full of questions.
Is it do supposed to store real 4-bytes-UTF-8 in the DATA field of the FLOWS table?
Does the DATA field really have to be that big (65536)?

Reproducing the bug

Run podman/docker compose using the following backend.yaml

backend.yaml

version: "3.9"
services:
    hanko-migrate:
        container_name: hanko-migrate
        build: ../../backend
        volumes:
            - type: bind
              source: ./custom.config.yaml
              target: /etc/config/config.yaml
        command: --config /etc/config/config.yaml migrate up
        restart: on-failure
        depends_on:
            mariadb:
                condition: service_healthy
        networks:
            - intranet
    hanko:
        container_name: hanko
        depends_on:
            hanko-migrate:
                condition: service_completed_successfully
        build:
            context: ../../backend
            dockerfile: Dockerfile
            # dockerfile: Dockerfile.debug
        security_opt:
            - "apparmor=unconfined"
        cap_add:
            - SYS_PTRACE
        ports:
            - "8000:8000" # public
            - "8001:8001" # admin
            # - "40000:40000" # debug
        restart: unless-stopped
        command: serve --config /etc/config/config.yaml all
        volumes:
            - type: bind
              source: ./custom.config.yaml
              target: /etc/config/config.yaml
        networks:
            - intranet
        environment:
            - PASSWORD_ENABLED
    mariadb:
        container_name: mariadb
        image: mariadb:11
        ports:
            - "3306:3306"
        environment:
            - MARIADB_USER=hanko
            - MARIADB_PASSWORD=hanko
            - MARIADB_DATABASE=hanko
            - MARIADB_RANDOM_ROOT_PASSWORD=true
        healthcheck:
            interval: 30s
            retries: 3
            test:
                [
                    "CMD",
                    "healthcheck.sh",
                    "--su-mysql",
                    "--connect",
                    "--innodb_initialized",
                ]
            timeout: 30s
            start_period: 30s
        networks:
            - intranet
networks:
    intranet:

then check logs with

 podman logs --follow hanko-migrate

Logs

No response

Configuration

No response

Hanko Version

fc80743

OS Hanko Backend

None

OS Version Hanko Backend

No response

OS

None

OS Version

No response

Browser Version

No response

Environment

Docker Compose

Additional Context

No response