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 啊!

☆版权☆

* 网站名称:obaby@mars
* 网址:https://obaby.org.cn/
* 个性:https://oba.by/
* 本文标题: 《umami 升级记 — 能跑千万别折腾》
* 本文链接:https://obaby.org.cn/2024/04/16742
* 短链接:https://oba.by/?p=16742
* 转载文章请标明文章来源,原文标题以及原文链接。请遵从 《署名-非商业性使用-相同方式共享 2.5 中国大陆 (CC BY-NC-SA 2.5 CN) 》许可协议。


You may also like

46 comments

  1.   Level 7
    Google Chrome 124 Google Chrome 124 Mac OS X 10.15 Mac OS X 10.15 cn中国–浙江–杭州 电信

    为了避免升级带来问题,我用虚拟机构建了跟云服务器相仿的环境(dpkg -l 完全一致)。在虚拟机跑一段时间没问题,再去云服务器上升级。

  2.   Level 7
    Google Chrome 124 Google Chrome 124 Mac OS X 10.15 Mac OS X 10.15 cn中国–浙江–杭州 电信/数据中心

    我看了一下题图,然后到【那个网站】搜索杨晨晨。你别说,你还真别说~~

  3. Level 4
    Google Chrome 124 Google Chrome 124 Mac OS X 10.15 Mac OS X 10.15 cn中国 中国电信

    还好我是使用docker来部署的,升级与部署不用理会环境

  4. Level 3
    Google Chrome 124 Google Chrome 124 Windows 11 Windows 11 cn中国–四川–自贡 移动

    umami我记得有个大版本升级需要先升级数据库结构。

    1. 公主 Queen 
      Google Chrome 122 Google Chrome 122 Android 10 Android 10 cn中国 中国联通

      这个就是升级数据库的时候出的问题,他们的安装脚本也有问题。

      1. Level 3
        Google Chrome 124 Google Chrome 124 Windows 11 Windows 11 cn中国–四川–自贡 移动

        我当时好像一次性就成功了,不过我是用的docker,应该简单一些。

  5. Level 5
    Google Chrome 124 Google Chrome 124 Windows 11 Windows 11 cn中国–湖北–武汉 联通

    前天死活没安装成功,没办法找了个丐版,顺带着水了一篇文章

  6.  Level 6
    Microsoft Edge 124 Microsoft Edge 124 Windows 11 Windows 11 cn中国–陕西–西安 电信

    我那个聊天广场数据库,一直想要升级,每次升级都会丢失数据,我也是服气了,不折腾了~

    1. 公主 Queen 
      Google Chrome 122 Google Chrome 122 Android 10 Android 10 cn中国–山东–青岛 联通

      很多系统数据库升级脚本兼容性可靠性很烂 升级各种问题

    1. 公主 Queen 
      Google Chrome 118 Google Chrome 118 Mac OS X 10.15 Mac OS X 10.15 cn中国–山东–青岛 联通

      关键是我没看到从哪里关闭升级提示,😂

  7. Level 5
    Google Chrome 124 Google Chrome 124 Windows 10 Windows 10 se瑞典 Oracle_Corporation

    除非不能用,不然不升级,我在pc上用一些破解软件的态度。

  8. Level 5
    Internet Explorer 8 Internet Explorer 8 iPad iOS 4.3.3 iPad iOS 4.3.3 us美国–加利福尼亚州–洛杉矶–洛杉矶

    图是漂亮,但是还是爱看灵妹妹

  9. Level 2
    Microsoft Edge 123 Microsoft Edge 123 Android 10 Android 10 cn中国–陕西 移动/全省通用

    dance能不动就不动 我搬了一个图库 现在一直http500 到现在还没解决……

  10. Level 4
    Microsoft Edge 120 Microsoft Edge 120 Windows 11 Windows 11 cn中国–广东–广州 联通

    就是这个统计?试用过,很一般,就没再用。不过不要随便升级是真理

    1. 公主 Queen 
      Google Chrome 118 Google Chrome 118 Mac OS X 10.15 Mac OS X 10.15 cn中国–山东–青岛 联通

      就是个统计,功能不是很强大,看个数够用了。其他的也用不到

    1. 公主 Queen 
      Google Chrome 122 Google Chrome 122 Android 10 Android 10 cn中国–山东–青岛 联通

      是的 如果不提示还好 提示了就有点烦人了

    1. 公主 Queen 
      Google Chrome 122 Google Chrome 122 Android 10 Android 10 cn中国–山东–青岛 联通

      感觉这个是回滚了,visitor_id列还是有问题的

    2. 公主 Queen 
      Google Chrome 122 Google Chrome 122 Android 10 Android 10 cn中国–山东–青岛 联通

      贵站邮件里能打开 我从评论点击链接提示
      无法访问此网站检查 xingpingnc.top 中是否有拼写错误。
      DNS_PROBE_FINISHED_NXDOMAIN

      1.  Level 4
        Google Chrome 124 Google Chrome 124 Windows 10 Windows 10 cn中国–江苏–盐城 移动

        他的评论是xingpingcn,而留的地址却是xingpingnc,哈哈哈 smile

  11. Level 1
    Google Chrome 128 Google Chrome 128 Windows 10 Windows 10 cn中国–广东–深圳 电信

    还真别说,之前也是想升级。后面就感觉没必要哈哈,一个看看数据的工具罢了。

    1. 公主 Queen 
      Google Chrome 126 Google Chrome 126 Mac OS X 10.15 Mac OS X 10.15 cn中国–山东–青岛 联通

      又提示新版本,这次我忍住了,没鸟它。哈哈哈

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注