umami 升级记 — 能跑千万别折腾

这几天 umami 打开的时候一直提示新版本，这就看着有点蛋疼了。今天上午想升级一下，看官方文档写的贼啦简单：

git pull
yarn install
yarn build

实际操作起来也贼啦简单，除了第三步，就在第三步的时候卡住了。提示下面两种错误：

root@iZbp13wa1pf33ffruzk6lfZ:~/umami# yarn build
yarn run v1.22.21
$ npm-run-all check-env build-db check-db build-tracker build-geo build-app
$ node scripts/check-env.js
$ npm-run-all copy-db-files build-db-client
$ node scripts/copy-db-files.js
Database type detected: mysql
Copied /root/umami/db/mysql to /root/umami/prisma
$ prisma generate
Environment variables loaded from .env
Prisma schema loaded from prisma/schema.prisma

✔ Generated Prisma Client (v5.12.1) to ./node_modules/@prisma/client in 240ms

Start using Prisma Client in Node.js (See: https://pris.ly/d/client)
```
import { PrismaClient } from '@prisma/client'
const prisma = new PrismaClient()
```
or start using Prisma Client at the edge (See: https://pris.ly/d/accelerate)
```
import { PrismaClient } from '@prisma/client/edge'
const prisma = new PrismaClient()
```

See other ways of importing Prisma Client: http://pris.ly/d/importing-client

┌────────────────────────────────────────────────────────────────┐
│  Supercharge your Prisma Client with global database caching,  │
│  scalable connection pooling and real-time database events.    │
│  Explore Prisma Accelerate: https://pris.ly/cli/-accelerate    │
│  Explore Prisma Pulse: https://pris.ly/cli/-pulse              │
└────────────────────────────────────────────────────────────────┘

$ node scripts/check-db.js
✓ DATABASE_URL is defined.
✓ Database connection successful.
✓ Database version check successful.
Error: P3009

migrate found failed migrations in the target database, new migrations will not be applied. Read more about how to resolve migration issues in a production database: https://pris.ly/d/migrate-resolve
The `05_add_visit_id` migration started at 2024-04-25 02:05:04.888 UTC failed



error Command failed with exit code 1.
info Visit https://yarnpkg.com/en/docs/cli/run for documentation about this command.
ERROR: "check-db" exited with 1.
error Command failed with exit code 1.
info Visit https://yarnpkg.com/en/docs/cli/run for documentation about this command.



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 umami2.BIN_TO_UUID does not exist

Please check the query number 2 from the migration file.

以及：

✗ Command failed: prisma migrate deploy
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 umami2.BIN_TO_UUID does not exist

Please check the query number 2 from the migration file.



error Command failed with exit code 1.
info Visit https://yarnpkg.com/en/docs/cli/run for documentation about this command.
ERROR: "check-db" exited with 1.
error Command failed with exit code 1.
info Visit https://yarnpkg.com/en/docs/cli/run for documentation about this command.

针对这两种错误，第一种可以直接通过命令回滚：

npx prisma migrate resolve --rolled-back 05_add_visit_id

而第二个错误就蛋疼了，mysql8以下没有这个函数BIN_TO_UUID，所以就直接报错了。搜索一下，可以找到下面的解决方案：

https://github.com/umami-software/umami/issues/2645

https://gist.github.com/jamesgmarks/56502e46e29a9576b0f5afea3a0f595c

实际情况是我试了，没那么好使，还是直接修改db/mysql/migrations/05_add_visit_id/migration.sql文件更方便：

源文件：

-- AlterTable
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) & 0xffffffffffff0fff3fffffffffffffff | 0x00000000000040008000000000000000) 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;

ALTER TABLE `website_event` MODIFY `visit_id` VARCHAR(36) NOT NULL;

-- CreateIndex
CREATE INDEX `website_event_visit_id_idx` ON `website_event`(`visit_id`);

-- CreateIndex
CREATE INDEX `website_event_website_id_visit_id_created_at_idx` ON `website_event`(`website_id`, `visit_id`, `created_at`);

修改：

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

为：

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

UPDATE `website_event` we
JOIN (SELECT DISTINCT
        s.session_id,
        s.visit_time,
        UUIDv4() 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;

ALTER TABLE `website_event` MODIFY `visit_id` VARCHAR(36) NOT NULL;

-- CreateIndex
CREATE INDEX `website_event_visit_id_idx` ON `website_event`(`visit_id`);

-- CreateIndex
CREATE INDEX `website_event_website_id_visit_id_created_at_idx` ON `website_event`(`website_id`, `visit_id`, `created_at`);

在数据库添加函数，函数名称UUIDv4：

CREATE DEFINER=`root`@`localhost` FUNCTION `UUIDv4`() RETURNS char(36) CHARSET utf8mb4
BEGIN
   DECLARE hexStr CHAR(32);
   RETURN LOWER(CONCAT(
HEX(RANDOM_BYTES(4)), '-',
    HEX(RANDOM_BYTES(2)), '-4',
    SUBSTR(HEX(RANDOM_BYTES(2)), 2, 3), '-',
    CONCAT(HEX(FLOOR(ASCII(RANDOM_BYTES(1)) / 64)+8),SUBSTR(HEX(RANDOM_BYTES(2)), 2, 3)), '-',
    HEX(RANDOM_BYTES(6))
    ));

END

如果创建函数失败，提示：

This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled

修改 my.cnf 添加下面一行重启数据库：

log_bin_trust_function_creators = 1

此时可能会出现下面的错误，提示 visitor_id 已经存在，直接去数据库删除，如果删除失败将列改名重新执行。根据错误提示多修改就 OK 了。

最后可能会提示下面的错误：

Database error:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'COLUMN `event_key` TO `data_key`;

-- AlterTable
ALTER TABLE `session_data` RENA' at line 2

Please check the query number 3 from the migration file.


✗ Command failed: prisma migrate deploy
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: 06_session_data

Database error code: 1064

Database error:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'COLUMN `event_key` TO `data_key`;

-- AlterTable
ALTER TABLE `session_data` RENA' at line 2

Please check the query number 3 from the migration file.

做法依然是回滚：

npx prisma migrate resolve --rolled-back 06_session_data

对于改名失败的列，直接去数据库管理工具修改，修改完成之后将下面的部分删除掉：

 -- DropIndex
--DROP INDEX `event_data_website_id_created_at_event_key_idx` ON `event_data`;

-- DropIndex
--DROP INDEX `event_data_website_id_website_event_id_created_at_idx` ON `event_data`;

-- AlterTable
--ALTER TABLE `event_data` RENAME COLUMN `event_key` TO `data_key`;

-- AlterTable
--ALTER TABLE `session_data` RENAME COLUMN `event_key` TO `data_key`;

只保留创建索引部分（下面3行），重新执行：

-- CreateIndex
CREATE INDEX `event_data_website_id_created_at_data_key_idx` ON `event_data`(`website_id`, `created_at`, `data_key`);

-- CreateIndex
CREATE INDEX `session_data_session_id_created_at_idx` ON `session_data`(`session_id`, `created_at`);

-- CreateIndex
CREATE INDEX `session_data_website_id_created_at_data_key_idx` ON `session_data`(`website_id`, `created_at`, `data_key`);

最后就终于成功了，不过不得不说，这个使用新特性偷懒的做法真是让人抑郁啊。为了装个 umami 升级数据库，这个做法有点蛋疼，我也不想这么做，但是这个兼容性真是让人一言难尽。

还是那句话，代码能跑就千万别动，你管他怎么跑呢！就这点破事，折腾一上午！关键是真不会写 sql 啊！