这几天 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 啊！
