Fixing Prisma Migration for Umami v2 with MariaDB 11

While setting up Umami v2 with MariaDB 11, I encountered a problem during one of the Prisma migrations:

Error: FUNCTION BIN_TO_UUID not found

This issue stems from Umami using MySQL-specific functions (BIN_TO_UUID and RANDOM_BYTES) in its migration SQL. These functions are not available in MariaDB.

To resolve this and keep the migration compatible with future upgrades, I had to re-implement those functions manually in MariaDB.

The Problematic Migration

Here’s the original migration snippet that failed:

ALTER TABLE `website_event` ADD COLUMN `visit_id` VARCHAR(36) NULL;

UPDATE `website_event` we
JOIN (SELECT DISTINCT
        s.session_id,
        s.visit_time,
        BIN_TO_UUID(RANDOM_BYTES(16)) uuid
    FROM (SELECT DISTINCT session_id,
            DATE_FORMAT(created_at, '%Y-%m-%d %H:00:00') visit_time
        FROM `website_event`) s) a
    ON we.session_id = a.session_id and DATE_FORMAT(we.created_at, '%Y-%m-%d %H:00:00') = a.visit_time
SET we.visit_id = a.uuid
WHERE we.visit_id IS NULL;

MariaDB doesn't support RANDOM_BYTES() or BIN_TO_UUID(), which are used to generate UUIDv4 identifiers.

The Fix: Defining Custom Functions

MariaDB supports custom SQL functions, so I implemented equivalents:

1. random_bytes_hex(n)

Simulates RANDOM_BYTES(n):

CREATE FUNCTION random_bytes_hex(n INT) RETURNS BLOB
DETERMINISTIC
BEGIN
  DECLARE output BLOB DEFAULT '';
  DECLARE i INT DEFAULT 0;
  WHILE i < n DO
    SET output = CONCAT(output, CHAR(FLOOR(RAND() * 256)));
    SET i = i + 1;
  END WHILE;
  RETURN output;
END;

2. bin_to_uuid(blob)

Formats binary data as a UUID string:

CREATE FUNCTION bin_to_uuid(b BLOB) RETURNS VARCHAR(36)
DETERMINISTIC
BEGIN
  RETURN LOWER(CONCAT_WS('-',
    HEX(SUBSTRING(b, 1, 4)),
    HEX(SUBSTRING(b, 5, 2)),
    HEX(SUBSTRING(b, 7, 2)),
    HEX(SUBSTRING(b, 9, 2)),
    HEX(SUBSTRING(b, 11, 6))
  ));
END;

3. uuid_v4() (which I did not use)

ChatGPT also offered me the suggestion to override the uuid_v4 function, which already exists in MariaDB natively. So when I executed this I got a warning that there was already a native function available.

DROP FUNCTION IF EXISTS uuid_v4;

CREATE FUNCTION uuid_v4() RETURNS VARCHAR(36)
DETERMINISTIC
BEGIN
  DECLARE b BLOB;
  SET b = random_bytes_hex(16);
  SET b = INSERT(b, 7, 1, CHAR(ASCII(SUBSTRING(b,7,1)) & 0x0F | 0x40));
  SET b = INSERT(b, 9, 1, CHAR(ASCII(SUBSTRING(b,9,1)) & 0x3F | 0x80));
  RETURN bin_to_uuid(b);
END;

Run DROP FUNCTION IF EXISTS uuid_v4; if you want to remove the custom version you already created. I decided to take my chances with the native function, and encountered no issues.

The Updated Migration

After this I manually ran the failing queries from the migration again; all except the first one, the visit_id column was already created.

After this I updated the _prisma_migration table to mark the fifth migration as completed, and restarted umami. All other migrations ran without any problems.

Conclusion

This workaround allows Umami v2 to run on MariaDB 11 without switching to MySQL. It’s a one-time patch, and with these functions in place, any future use of the missing functions should work as expected.