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


You may also like

43 comments

  1.   Level 6
    Google Chrome 124 Google Chrome 124 Mac OS X 10.15 Mac OS X 10.15 cn浙江省杭州市 电信

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

  2.   Level 6
    Google Chrome 124 Google Chrome 124 Mac OS X 10.15 Mac OS X 10.15 cn浙江省杭州市 电信/数据中心

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

  3. Level 2
    Google Chrome 124 Google Chrome 124 Windows 11 Windows 11 cn四川省自贡市 移动

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

    1. 公主 Queen 
      Google Chrome 122 Google Chrome 122 Android 10 Android 10 cnAsia/Shanghai

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

      1. Level 2
        Google Chrome 124 Google Chrome 124 Windows 11 Windows 11 cn四川省自贡市 移动

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

  4.  Level 3
    Google Chrome 124 Google Chrome 124 Windows 11 Windows 11 cn湖北省武汉市 联通

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

  5.  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山东省青岛市 联通

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

  6. Level 5
    Google Chrome 124 Google Chrome 124 Windows 10 Windows 10 se瑞典 Oracle Corporation

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

  7. Level 4
    Internet Explorer 8 Internet Explorer 8 iPad iOS 4.3.3 iPad iOS 4.3.3 us美国加利福利亚州洛杉矶

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

  8. Level 1
    Microsoft Edge 123 Microsoft Edge 123 Android 10 Android 10 cn陕西省 移动/全省通用

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

  9. 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山东省青岛市 联通

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

      1.  Level 4
        Google Chrome 124 Google Chrome 124 Windows 10 Windows 10 cn江苏省盐城市 移动

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

发表回复

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