bug: Switched from SQLite to MySQL (Maria) and now crashes

[YouveGotMeowxy]

Describe the bug

Environment

Docker

Version

latest as of this post

Describe the problem

Per the title, fresh default install (sqlite) seemingly went fine (no errors); switched to MariaDB, and the log now looks like this:

Also, when navigating to any other page, I get this:

 Error [TRPCError]: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(select json_array(`groupMembers_group`.`id`, `groupMembers_group`.`name`, `g...' at line 1
    at E.query (.next/server/chunks/5143.js:164:51965)
    at I.execute (.next/server/chunks/5143.js:170:27462)
    at QueryPromise.execute (.next/server/chunks/5143.js:164:121934)
    at QueryPromise.then (.next/server/chunks/5143.js:170:55294) {
  code: 'INTERNAL_SERVER_ERROR',
  digest: '140570336',
  [cause]: Error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(select json_array(`groupMembers_group`.`id`, `groupMembers_group`.`name`, `g...' at line 1
      at E.query (.next/server/chunks/5143.js:164:51965)
      at I.execute (.next/server/chunks/5143.js:170:27462)
      at QueryPromise.execute (.next/server/chunks/5143.js:164:121934)
      at QueryPromise.then (.next/server/chunks/5143.js:170:55294) {
    code: 'ER_PARSE_ERROR',
    errno: 1064,
    sql: "select `groupMembers`.`group_id`, `groupMembers`.`user_id`, `groupMembers_group`.`data` as `group` from `groupMember` `groupMembers` left join lateral (select json_array(`groupMembers_group`.`id`, `groupMembers_group`.`name`, `groupMembers_group`.`owner_id`, `groupMembers_group_boardPermissions`.`data`) as `data` from (select * from `group` `groupMembers_group` where `groupMembers_group`.`id` = `groupMembers`.`group_id` limit 1) `groupMembers_group` left join lateral (select coalesce(json_arrayagg(json_array(`groupMembers_group_boardPermissions`.`board_id`, `groupMembers_group_boardPermissions`.`group_id`, `groupMembers_group_boardPermissions`.`permission`)), json_array()) as `data` from `boardGroupPermission` `groupMembers_group_boardPermissions` where `groupMembers_group_boardPermissions`.`group_id` = `groupMembers_group`.`id`) `groupMembers_group_boardPermissions` on true) `groupMembers_group` on true where `groupMembers`.`user_id` = 'ziim6'",
    sqlState: '42000',
    sqlMessage: "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(select json_array(`groupMembers_group`.`id`, `groupMembers_group`.`name`, `g...' at line 1"
  }
}
2025-02-10T03:27:36.815Z info: ? Connection (1) 1006 
Killed
Container stopped

The initial db migrate did seem to populate the MySQL db, so it seems the connection itself shouldn't be the problem.

Running this in docker swarm, here is my Compose:

Expand

homarr:
  hostname: 'homarr'
  image: 'ghcr.io/homarr-labs/homarr:latest'
  environment:
    TZ: 'America/Chicago'
    DB_DRIVER: 'mysql2'
    DB_DIALECT: 'mysql'
    DB_HOST: 'mariadb'
    DB_PORT: '3306'
    DB_NAME: 'homarr'
    DB_USER: 'oc_homarr'
    DB_PASSWORD: '<redacted>'
    SECRET_ENCRYPTION_KEY: 'redacted'
  volumes:
    - '/opt/docker/configs/homarr/appdata:/appdata:rw'
    - '/var/run/docker.sock:/var/run/docker.sock'
  deploy:
    mode: 'global'
    labels:
      swag.uptime-kuma.enabled: "true"
      swag.uptime-kuma.monitor.name: "Homarr"
      swag.uptime-kuma.monitor.url: "https://homarr.mydomain.com"
      swag.uptime-kuma.monitor.description: "Server Managment"
      swag.uptime-kuma.monitor.parent: "HOMEPAGES"
      com.ouroboros.enable: 'true'
    resources:
      limits:
        memory: '200M'
    restart_policy:
      condition: 'any'
  networks:
    - 'mariadb'

Logs

No response

Context

No response

Please tick the boxes

• I confirm that I attached the proper logs
• I've read the docs
• I've checked for duplicate issues
• I've tried to debug myself

Steps to reproduce

1. Install fresh as usual (w/out MySQL environment vars in compose).
2. Run it in the browser as usual, setup everything as usual.
3. Shut it down.
4. Add MySQL env vars (I use latest maria)
5. Restart container

It's wonky from there on. Sometimes it appears to load normally in the browser, but then you click things and it acts up. White pages, errors, etc.

Impact

Becomes unusable.

Additional information

Originally posted here: ajnart/homarr#2274 (comment)

MariaDB Info:

Homarr version:

Version

1.5.0

Installation method

Docker Compose

Browser

Brave

[Meierschlumpf]

MariaDB has not the same Syntax of commands like Mysql, so you can either use Mysql or wait until our ORM and later we release support for MariaDB.

[YouveGotMeowxy]

@Meierschlumpf ouch, ok thanks.

I was always under the impression that MariaDB was (mostly) a drop in replacement for mysql. I've never had any issues (thus far) with other software that uses MySQL. Any idea on a timeline that Maria might be supported? it's used by a ton of ppl, lol

[YouveGotMeowxy]

P.S. little teeny bug also shown in the Homarr version screenshot above in the version compare. It says there's a new version compared to the current version, but both show 1.5.0. ;)

[Meierschlumpf]

See the roadmap of the ORM we are using. Can't give you a specific estimate, but they say they are at 75%
https://orm.drizzle.team/roadmap

[YouveGotMeowxy]

Any way I can get some sort of notification when it's available? For now I'm just gonna put homarr on the backburner and forget about it until then.

[Meierschlumpf]

Probably best to create a new issue just for that as feature request and then you can subscribe to it

[Meierschlumpf]

Created the issue: #2305

[YouveGotMeowxy]

@Meierschlumpf ty!

[manuel-rw]

Can we close this issue as a duplicate of #2305 ? Or why did we create a new issue for this?