Migration error P3018 with MySQL migration 05

[MiK546]

Describe the Bug

I'm trying to upgrade my Docker Umami installation to the latest version. However the database upgrade fails to the migration 05_add_visit_id with the attached error. I am running MariaDB 11.2.2, in Docker as well. The setup was functional before the Umami upgrade. I attempted to roll back the database upgrade, mark the step as rolled back and to try again, but the same error repeated. This seems to be a bug in Umami migration code or am I doing something wrong?

Database

MySQL

Relevant log output

Error: P3018

A migration failed to apply. New migrations cannot be applied before the error is recovered from. Read more about how to resolve migration issues in a production database: https://pris.ly/d/migrate-resolve

Migration name: 05_add_visit_id

Database error code: 1305

Database error:
FUNCTION umami.BIN_TO_UUID does not exist

Please check the query number 2 from the migration file.

Which Umami version are you using? (if relevant)

2.11.2

Which browser are you using? (if relevant)

No response

How are you deploying your application? (if relevant)

Docker

[Gouttfi]

Hello,
I have exactly the same problem.
I tried to install latest version of Umami today version 2.11.2
I have mariadb database version 10.11.6
Node version 18.19.0, NPM version 9.2.0
I dont use docker

EDIT : tested with 2.11.0 and having same problem

[MiK546]

EDIT: See a better way to run the failing migration on MariaDB in this issue.

Seems MariaDB just doesn't implement BIN_TO_UUID right now whereas it is a built-in function in MySQL. There are polyfills for this but for some reason they produced a new error for me, so they are not completely 1-to-1 with the MySQL implementation I suppose. In the end I replaced the

BIN_TO_UUID(RANDOM_BYTES(16) & 0xffffffffffff0fff3fffffffffffffff | 0x00000000000040008000000000000000) uuid

line with

BIN_TO_UUID(UUID_TO_BIN(UUID())) uuid

with the polyfills for the two utility functions already created.

Not sure if this was a bit silly and maybe just UUID() would have sufficed, but I wanted to make sure the formatting of the VARCHAR(32) was more likely to be the same as the original MySQL code. I do not in any way guarantee this works the same way as the original code, but it seemed to work for me. After I had manually run the migration code with this change I marked the 05 migration done as per the Prisma documentation linked in the original error. Then I re-started Umami which did the rest of the migrations successfully and now seems to work fine.

I guess if Umami wanted to support MariaDB as well as MySQL they would need to replace using BIN_TO_UUID in the migration code.

[franciscao633]

check out this comment. I believe its the same exact issue.

[Gouttfi]

check out this comment. I believe its the same exact issue.

I ran this command npx prisma migrate resolve --applied "05_add_visit_id" and the installation then worked correctly. I specify that my data tables were empty during the installation and I repatriated my data once the installation was completed

[MiK546]

check out this comment. I believe its the same exact issue.

Indeed it is! The author of that issue has just used the wrong error code in the title. They used the "unfinished migrations" error code of P3009 even though the body describes the same error P3018 I had. I didn't manage to find the issue because of that. This issue could be marked as a duplicate I think.

[acagastya]

npx prisma migrate resolve --applied "05_add_visit_id"

This seems to have worked for me.