数据库迁移到 mysql

# 前言

Jpom 服务端的数据默认存储到 H2database 中

部分用户有需求期望数据存储到 mysql 中

提醒

使用 mysql 模式，系统管理中的数据库备份功能将不可用，数据的备份管理需要自行维护

# 系统升级

使用jpom的在线升级功能将将服务端和插件端版本升级至 2.10.11

注意

升级前请仔细阅读版本更新日志，如果有特殊说明或者注意事项请仔细确认。升级前建议提前做好相关备份,避免出现意外造成数据丢失

# 如何创建 h2 数据备份

# 页面操作创建

# 命令行创建

bash ./bin/Server.sh restart -15 --backup-h2

# 修改配置

配置文件:conf/application.yml

修改 jpom.db.mode 为 MYSQL
修改 jpom.db.url 为你 mysql 的 jdbc 地址( jdbc:mysql://127.0.0.1: 3306/jpom?useUnicode=true&characterEncoding=UTF-8&useSSL=false)
修改 jpom.db.user-name 为对应 mysql 账户 -修改 jpom.db.user-pwd 为对应 mysql 密码

  db: 
    mode: MYSQL
    url:  jdbc:mysql://xx.xx.xx.xx:3306/jpom?useUnicode=true&characterEncoding=UTF-8&useSSL=false
    # 日志存储条数，将自动清理旧数据,配置小于等于零则不清理
    log-storage-count: 10000
    # 数据库账号 默认 jpom
    user-name: root
    # 数据库密码 默认 jpom 如果自行配置请保证密码强度
    user-pwd: 123456
    # h2 数据库缓存大小  kilobyte 1KB 1,024 megabyte 1MB 1,048,576
    cache-size: 50MB
    # 自动备份间隔天数 小于等于 0 不自动备份
    auto-backup-interval-day: 1
    # 自动备份保留天数 小于等于 0，不自动删除自动备份数据
    auto-backup-reserve-day: 5
    # 数据库连接池相关配置
    max-active: 100
    initial-size: 10
    max-wait: 10
    min-idle: 1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

# 执行命令

bash ./bin/Server.sh restart -15 --h2-migrate-mysql --h2-user=jpom --h2-pass=jpom

如果您修改过 h2 数据库的账号或者密码，请在命令行参数里面填写您修改过的

提醒

为了保证数据安全，在迁移前程序会自动创建一个数据库备份，注意查看日志输出相关信息
在所以数据都迁移成功后程序会自动删除 h2 中的数据，并且将 h2 数据相关文件移动到备份路径，注意查看日志输出相关信息

# 查看结果

2023-01-10 10:53:50.947 [main] INFO  i.j.s.d.InitDb [InitDb.java:310] - 开始等待数据迁移
2023-01-10 10:53:50.950 [main] INFO  i.j.s.d.InitDb [InitDb.java:109] - start load MYSQL db
2023-01-10 10:53:51.543 [main] INFO  i.j.s.d.InitDb [InitDb.java:239] - exec init SQL file: table.all.v1.0.csv complete, and affected rows is: 0
2023-01-10 10:53:51.605 [main] INFO  i.j.s.d.InitDb [InitDb.java:239] - exec init SQL file: execute.mysql.v1.0.sql complete, and affected rows is: 0
2023-01-10 10:53:51.625 [main] INFO  i.j.s.d.InitDb [InitDb.java:239] - exec init SQL file: alter.all.v1.0.csv complete, and affected rows is: 0
2023-01-10 10:53:51.703 [main] INFO  i.j.s.d.InitDb [InitDb.java:239] - exec init SQL file: index.all.v1.0.csv complete, and affected rows is: 0
2023-01-10 10:53:51.706 [main] INFO  i.j.s.d.InitDb [InitDb.java:155] - 开始执行数据库事件：迁移数据
2023-01-10 10:53:51.706 [main] INFO  t.j.d.StorageServiceFactory [StorageServiceFactory.java:85] - 开始迁移 h2 数据到 MYSQL
2023-01-10 10:53:52.097 [main] INFO  t.j.d.StorageServiceFactory [StorageServiceFactory.java:95] - 成功连接 H2 ,开始尝试自动备份
2023-01-10 10:53:52.495 [main] INFO  t.j.d.StorageServiceFactory [StorageServiceFactory.java:106] - H2 数据库备份成功：/opt/jpom_server/server-2-2/db/backup/20230110105352.sql
2023-01-10 10:53:52.527 [main] INFO  t.j.d.StorageServiceFactory [StorageServiceFactory.java:111] - 成功连接 MYSQL jdbc:mysql://192.168.1.174:3306/jpom?useUnicode=true&characterEncoding=UTF-8&useSSL=false
2023-01-10 10:53:52.537 [main] INFO  t.j.d.StorageServiceFactory [StorageServiceFactory.java:124] - 准备迁移数据
2023-01-10 10:53:52.537 [main] INFO  t.j.d.StorageServiceFactory [StorageServiceFactory.java:143] - 开始迁移 构建历史 BUILDHISTORYLOG
2023-01-10 10:53:55.069 [main] INFO  t.j.d.StorageServiceFactory [StorageServiceFactory.java:179] - 构建历史 迁移成功 428 条数据
2023-01-10 10:53:55.069 [main] INFO  t.j.d.StorageServiceFactory [StorageServiceFactory.java:143] - 开始迁移 构建信息 BUILD_INFO
2023-01-10 10:53:55.170 [main] INFO  t.j.d.StorageServiceFactory [StorageServiceFactory.java:179] - 构建信息 迁移成功 16 条数据
2023-01-10 10:53:55.170 [main] INFO  t.j.d.StorageServiceFactory [StorageServiceFactory.java:143] - 开始迁移 命令执行记录 COMMAND_EXEC_LOG
2023-01-10 10:53:55.171 [main] INFO  t.j.d.StorageServiceFactory [StorageServiceFactory.java:179] - 命令执行记录 迁移成功 0 条数据
2023-01-10 10:53:55.172 [main] INFO  t.j.d.StorageServiceFactory [StorageServiceFactory.java:143] - 开始迁移 命令管理 COMMAND_INFO
2023-01-10 10:53:55.173 [main] INFO  t.j.d.StorageServiceFactory [StorageServiceFactory.java:179] - 命令管理 迁移成功 0 条数据
2023-01-10 10:53:55.173 [main] INFO  t.j.d.StorageServiceFactory [StorageServiceFactory.java:143] - 开始迁移 docker 信息 DOCKER_INFO
2023-01-10 10:53:55.223 [main] INFO  t.j.d.StorageServiceFactory [StorageServiceFactory.java:179] - docker 信息 迁移成功 9 条数据
2023-01-10 10:53:55.224 [main] INFO  t.j.d.StorageServiceFactory [StorageServiceFactory.java:143] - 开始迁移 docker 集群信息 DOCKER_SWARM_INFO
2023-01-10 10:53:55.226 [main] INFO  t.j.d.StorageServiceFactory [StorageServiceFactory.java:179] - docker 集群信息 迁移成功 0 条数据
2023-01-10 10:53:55.226 [main] INFO  t.j.d.StorageServiceFactory [StorageServiceFactory.java:143] - 开始迁移 日志阅读 LOG_READ
2023-01-10 10:53:55.228 [main] INFO  t.j.d.StorageServiceFactory [StorageServiceFactory.java:179] - 日志阅读 迁移成功 0 条数据
2023-01-10 10:53:55.228 [main] INFO  t.j.d.StorageServiceFactory [StorageServiceFactory.java:143] - 开始迁移 监控信息 MONITOR_INFO
2023-01-10 10:53:55.383 [main] INFO  t.j.d.StorageServiceFactory [StorageServiceFactory.java:179] - 监控信息 迁移成功 2 条数据
2023-01-10 10:53:55.383 [main] INFO  t.j.d.StorageServiceFactory [StorageServiceFactory.java:143] - 开始迁移 监控通知 MONITORNOTIFYLOG
2023-01-10 10:53:55.399 [main] INFO  t.j.d.StorageServiceFactory [StorageServiceFactory.java:179] - 监控通知 迁移成功 2 条数据
2023-01-10 10:53:55.400 [main] INFO  t.j.d.StorageServiceFactory [StorageServiceFactory.java:143] - 开始迁移 监控用户操作 MONITOR_USER_OPT
2023-01-10 10:53:55.401 [main] INFO  t.j.d.StorageServiceFactory [StorageServiceFactory.java:179] - 监控用户操作 迁移成功 0 条数据
2023-01-10 10:53:55.401 [main] INFO  t.j.d.StorageServiceFactory [StorageServiceFactory.java:143] - 开始迁移 节点信息 NODE_INFO
2023-01-10 10:53:55.451 [main] INFO  t.j.d.StorageServiceFactory [StorageServiceFactory.java:179] - 节点信息 迁移成功 8 条数据
2023-01-10 10:53:55.451 [main] INFO  t.j.d.StorageServiceFactory [StorageServiceFactory.java:143] - 开始迁移 节点统计 NODE_STAT
2023-01-10 10:53:55.496 [main] INFO  t.j.d.StorageServiceFactory [StorageServiceFactory.java:179] - 节点统计 迁移成功 8 条数据
2023-01-10 10:53:55.496 [main] INFO  t.j.d.StorageServiceFactory [StorageServiceFactory.java:143] - 开始迁移 分发日志 OUTGIVINGLOG
2023-01-10 10:53:55.498 [main] INFO  t.j.d.StorageServiceFactory [StorageServiceFactory.java:179] - 分发日志 迁移成功 0 条数据
2023-01-10 10:53:55.498 [main] INFO  t.j.d.StorageServiceFactory [StorageServiceFactory.java:143] - 开始迁移 节点分发 OUT_GIVING
2023-01-10 10:53:55.499 [main] INFO  t.j.d.StorageServiceFactory [StorageServiceFactory.java:179] - 节点分发 迁移成功 0 条数据
2023-01-10 10:53:55.499 [main] INFO  t.j.d.StorageServiceFactory [StorageServiceFactory.java:143] - 开始迁移 项目信息 PROJECT_INFO
2023-01-10 10:53:55.516 [main] INFO  t.j.d.StorageServiceFactory [StorageServiceFactory.java:179] - 项目信息 迁移成功 2 条数据
2023-01-10 10:53:55.516 [main] INFO  t.j.d.StorageServiceFactory [StorageServiceFactory.java:143] - 开始迁移 仓库信息 REPOSITORY
2023-01-10 10:53:55.600 [main] INFO  t.j.d.StorageServiceFactory [StorageServiceFactory.java:179] - 仓库信息 迁移成功 15 条数据
2023-01-10 10:53:55.601 [main] INFO  t.j.d.StorageServiceFactory [StorageServiceFactory.java:143] - 开始迁移 节点脚本模版 SCRIPT_INFO
2023-01-10 10:53:55.694 [main] INFO  t.j.d.StorageServiceFactory [StorageServiceFactory.java:179] - 节点脚本模版 迁移成功 17 条数据
2023-01-10 10:53:55.694 [main] INFO  t.j.d.StorageServiceFactory [StorageServiceFactory.java:143] - 开始迁移 节点脚本模版执行记录 SCRIPT_EXECUTE_LOG
2023-01-10 10:54:47.038 [main] INFO  t.j.d.StorageServiceFactory [StorageServiceFactory.java:179] - 节点脚本模版执行记录 迁移成功 9760 条数据
2023-01-10 10:54:47.038 [main] INFO  t.j.d.StorageServiceFactory [StorageServiceFactory.java:143] - 开始迁移 脚本模版执行记录 SERVER_SCRIPT_EXECUTE_LOG
2023-01-10 10:54:47.046 [main] INFO  t.j.d.StorageServiceFactory [StorageServiceFactory.java:179] - 脚本模版执行记录 迁移成功 1 条数据
2023-01-10 10:54:47.046 [main] INFO  t.j.d.StorageServiceFactory [StorageServiceFactory.java:143] - 开始迁移 脚本模版 SERVER_SCRIPT_INFO
2023-01-10 10:54:47.061 [main] INFO  t.j.d.StorageServiceFactory [StorageServiceFactory.java:179] - 脚本模版 迁移成功 2 条数据
2023-01-10 10:54:47.061 [main] INFO  t.j.d.StorageServiceFactory [StorageServiceFactory.java:143] - 开始迁移 SSH 信息 SSH_INFO
2023-01-10 10:54:47.103 [main] INFO  t.j.d.StorageServiceFactory [StorageServiceFactory.java:179] - SSH 信息 迁移成功 7 条数据
2023-01-10 10:54:47.103 [main] INFO  t.j.d.StorageServiceFactory [StorageServiceFactory.java:143] - 开始迁移 ssh 终端执行日志 SSHTERMINALEXECUTELOG
2023-01-10 10:54:47.634 [main] INFO  t.j.d.StorageServiceFactory [StorageServiceFactory.java:179] - ssh 终端执行日志 迁移成功 102 条数据
2023-01-10 10:54:47.635 [main] INFO  t.j.d.StorageServiceFactory [StorageServiceFactory.java:143] - 开始迁移 节点监控记录 SYSTEMMONITORLOG
2023-01-10 10:55:40.990 [main] INFO  t.j.d.StorageServiceFactory [StorageServiceFactory.java:179] - 节点监控记录 迁移成功 10000 条数据
2023-01-10 10:55:40.990 [main] INFO  t.j.d.StorageServiceFactory [StorageServiceFactory.java:143] - 开始迁移 系统参数 SYSTEM_PARAMETERS
2023-01-10 10:55:41.017 [main] INFO  t.j.d.StorageServiceFactory [StorageServiceFactory.java:179] - 系统参数 迁移成功 5 条数据
2023-01-10 10:55:41.017 [main] INFO  t.j.d.StorageServiceFactory [StorageServiceFactory.java:143] - 开始迁移 触发器 token TRIGGER_TOKEN_LOG
2023-01-10 10:55:41.288 [main] INFO  t.j.d.StorageServiceFactory [StorageServiceFactory.java:179] - 触发器 token 迁移成功 52 条数据
2023-01-10 10:55:41.288 [main] INFO  t.j.d.StorageServiceFactory [StorageServiceFactory.java:143] - 开始迁移 用户(权限组)工作空间关系表 USER_BIND_WORKSPACE
2023-01-10 10:55:41.347 [main] INFO  t.j.d.StorageServiceFactory [StorageServiceFactory.java:179] - 用户(权限组)工作空间关系表 迁移成功 11 条数据
2023-01-10 10:55:41.348 [main] INFO  t.j.d.StorageServiceFactory [StorageServiceFactory.java:143] - 开始迁移 用户账号 USER_INFO
2023-01-10 10:55:41.376 [main] INFO  t.j.d.StorageServiceFactory [StorageServiceFactory.java:179] - 用户账号 迁移成功 5 条数据
2023-01-10 10:55:41.377 [main] INFO  t.j.d.StorageServiceFactory [StorageServiceFactory.java:143] - 开始迁移 用户操作日志 USEROPERATELOGV1
2023-01-10 10:55:59.010 [main] INFO  t.j.d.StorageServiceFactory [StorageServiceFactory.java:179] - 用户操作日志 迁移成功 3127 条数据
2023-01-10 10:55:59.010 [main] INFO  t.j.d.StorageServiceFactory [StorageServiceFactory.java:143] - 开始迁移 用户权限组 USER_PERMISSION_GROUP
2023-01-10 10:55:59.018 [main] INFO  t.j.d.StorageServiceFactory [StorageServiceFactory.java:179] - 用户权限组 迁移成功 1 条数据
2023-01-10 10:55:59.018 [main] INFO  t.j.d.StorageServiceFactory [StorageServiceFactory.java:143] - 开始迁移 工作空间环境变量 WORKSPACE_ENV_VAR
2023-01-10 10:55:59.019 [main] INFO  t.j.d.StorageServiceFactory [StorageServiceFactory.java:179] - 工作空间环境变量 迁移成功 0 条数据
2023-01-10 10:55:59.019 [main] INFO  t.j.d.StorageServiceFactory [StorageServiceFactory.java:143] - 开始迁移 工作空间 WORKSPACE
2023-01-10 10:55:59.030 [main] INFO  t.j.d.StorageServiceFactory [StorageServiceFactory.java:179] - 工作空间 迁移成功 2 条数据
2023-01-10 10:55:59.032 [main] INFO  t.j.d.StorageServiceFactory [StorageServiceFactory.java:130] - 迁移完成,累计迁移 23582 条数据,耗时：2分7秒305毫秒
2023-01-10 10:55:59.091 [main] INFO  t.j.d.StorageServiceFactory [StorageServiceFactory.java:133] - 准备删除当前数据库文件
2023-01-10 10:55:59.095 [main] INFO  t.j.d.StorageServiceFactory [StorageServiceFactory.java:135] - 自动备份 h2 数据库文件,备份文件位于：/opt/jpom_server/server-2-2/db/recover_backup/20230110105559
2023-01-10 10:55:59.096 [main] INFO  i.j.s.d.InitDb [InitDb.java:158] - 数据库 迁移数据 事件执行结束,结果：false
2023-01-10 10:55:59.096 [main] INFO  i.j.s.d.InitDb [InitDb.java:172] - MYSQL db Successfully loaded, url is 【jdbc:mysql://192.168.1.174:3306/jpom?useUnicode=true&characterEncoding=UTF-8&useSSL=false】
2023-01-10 10:55:59.222 [main] INFO  i.j.c.JpomApplicationEvent [JpomApplicationEvent.java:222] - Server Successfully started,Can use happily => http://172.17.0.1:2122 【The current address is for reference only】

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80

# 如果失败了，不要慌！！！

如果失败了，我们建议按照如下操作处理

删除 mysql 中的所有数据
删除服务端中的 db 目录下 h2 数据库相关的文件

查看上一次迁移失败操作时程序自动备份数据的 sql 文件

2023-01-10 10:53:52.097 [main] INFO  t.j.d.StorageServiceFactory [StorageServiceFactory.java:95] - 成功连接 H2 ,开始尝试自动备份
2023-01-10 10:53:52.495 [main] INFO  t.j.d.StorageServiceFactory [StorageServiceFactory.java:106] - H2 数据库备份成功：/opt/jpom_server/server-2-2/db/backup/20230110105352.sql

1
2

将对应的 sql 文件下载到您的本地电脑
确认上传 db 目录下 h2 数据库相关的文件
重新启动服务端
这时您进入服务端需要你重新初始化系统，不要慌正常操作即可
进入到系统管理中的数据库备份管理
导入您之前下载的 sql 数据文件
还原数据
报错You do not have the SUPER privilege and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable

2023-03-08 11:08:21.977 [main] INFO  i.j.JpomServerApplication [StartupInfoLogger.java:55] - Starting JpomServerApplication v2.10.26 using Java 1.8.0_121 on xxxxx with PID 12073 (/usr/local/jpom-server/lib/Server-2.10.26.jar started by root in /usr/local/jpom-server)
2023-03-08 11:08:21.979 [main] INFO  i.j.JpomServerApplication [SpringApplication.java:631] - No active profile set, falling back to 1 default profile: "default"
2023-03-08 11:08:24.160 [main] INFO  o.s.b.w.e.t.TomcatWebServer [TomcatWebServer.java:108] - Tomcat initialized with port(s): 2122 (http)
2023-03-08 11:08:24.174 [main] INFO  o.a.c.h.Http11NioProtocol [DirectJDKLog.java:173] - Initializing ProtocolHandler ["http-nio-2122"]
2023-03-08 11:08:24.175 [main] INFO  o.a.c.c.StandardService [DirectJDKLog.java:173] - Starting service [Tomcat]
2023-03-08 11:08:24.176 [main] INFO  o.a.c.c.StandardEngine [DirectJDKLog.java:173] - Starting Servlet engine: [Apache Tomcat/9.0.70]
2023-03-08 11:08:24.292 [main] INFO  o.a.c.c.C.[.[.[/] [DirectJDKLog.java:173] - Initializing Spring embedded WebApplicationContext
2023-03-08 11:08:24.293 [main] INFO  o.s.b.w.s.c.ServletWebServerApplicationContext [ServletWebServerApplicationContext.java:292] - Root WebApplicationContext: initialization completed in 2219 ms
2023-03-08 11:08:25.228 [main] INFO  i.j.c.JpomApplicationEvent [JpomApplicationEvent.java:124] - Jpom[2.10.26] Current data path：/usr/local/jpom-server External configuration file path：file:/usr/local/jpom-server/conf/application.yml
2023-03-08 11:08:25.358 [main] INFO  i.j.c.JpomApplicationEvent [JpomApplicationEvent.java:139] - 本机安装 ID 为：8bef720e928b46c5942exxxxxxxxxxx99
2023-03-08 11:08:25.364 [main] INFO  i.j.s.d.InitDb [InitDb.java:297] - 开始等待数据迁移
2023-03-08 11:08:25.369 [main] INFO  i.j.s.d.InitDb [InitDb.java:106] - start load MYSQL db
2023-03-08 11:08:32.999 [main] INFO  i.j.s.d.InitDb [InitDb.java:226] - exec init SQL file: table.all.v1.0.csv complete, and affected rows is: 0
2023-03-08 11:08:33.712 [main] INFO  i.j.s.d.InitDb [InitDb.java:226] - exec init SQL file: table.all.v1.1.csv complete, and affected rows is: 0
2023-03-08 11:08:33.776 [main] ERROR i.j.s.d.InitDb [InitDb.java:165] - 初始化数据库失败 execute.mysql.v1.0.sql
java.sql.SQLException: You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
        at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:916)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1061)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1009)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1320)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdate(ClientPreparedStatement.java:994)
        at cn.hutool.db.sql.SqlExecutor.execute(SqlExecutor.java:58)
        at cn.hutool.db.AbstractDb.execute(AbstractDb.java:233)
        at io.jpom.system.db.InitDb.lambda$null$10(InitDb.java:221)
        at java.util.stream.ReferencePipeline$4$1.accept(ReferencePipeline.java:210)
        at java.util.ArrayList$ArrayListSpliterator.forEachRemaining(ArrayList.java:1374)
        at java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:481)
        at java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:471)
        at java.util.stream.ReduceOps$ReduceOp.evaluateSequential(ReduceOps.java:708)
        at java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234)
        at java.util.stream.IntPipeline.reduce(IntPipeline.java:456)
        at java.util.stream.IntPipeline.sum(IntPipeline.java:414)
        at io.jpom.system.db.InitDb.lambda$executeSql$9eed2c2c$1(InitDb.java:225)
        at cn.hutool.db.Db.tx(Db.java:191)
        at cn.hutool.db.Db.tx(Db.java:155)
        at io.jpom.system.db.InitDb.executeSql(InitDb.java:215)
        at io.jpom.system.db.InitDb.lambda$tryInitSql$7(InitDb.java:185)
        at java.util.Optional.ifPresent(Optional.java:159)
        at io.jpom.system.db.InitDb.tryInitSql(InitDb.java:181)
        at io.jpom.system.db.InitDb.afterPropertiesSet(InitDb.java:144)
        at io.jpom.common.JpomApplicationEvent.lambda$setApplicationContext$10(JpomApplicationEvent.java:348)
        at java.util.stream.ForEachOps$ForEachOp$OfRef.accept(ForEachOps.java:184)
        at java.util.stream.SortedOps$SizedRefSortingSink.end(SortedOps.java:352)
        at java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:482)
        at java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:471)
        at java.util.stream.ForEachOps$ForEachOp.evaluateSequential(ForEachOps.java:151)
        at java.util.stream.ForEachOps$ForEachOp$OfRef.evaluateSequential(ForEachOps.java:174)
        at java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234)
        at java.util.stream.ReferencePipeline.forEach(ReferencePipeline.java:418)
        at io.jpom.common.JpomApplicationEvent.setApplicationContext(JpomApplicationEvent.java:346)
        at org.springframework.context.support.ApplicationContextAwareProcessor.invokeAwareInterfaces(ApplicationContextAwareProcessor.java:128)
        at org.springframework.context.support.ApplicationContextAwareProcessor.postProcessBeforeInitialization(ApplicationContextAwareProcessor.java:102)
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.applyBeanPostProcessorsBeforeInitialization(AbstractAutowireCapableBeanFactory.java:440)
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1796)
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:620)
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:542)
        at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:335)
        at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:234)
        at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:333)
        at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:208)
        at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:955)
        at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:918)
        at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:583)
        at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.refresh(ServletWebServerApplicationContext.java:147)
        at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:731)
        at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:408)
        at org.springframework.boot.SpringApplication.run(SpringApplication.java:307)
        at org.springframework.boot.builder.SpringApplicationBuilder.run(SpringApplicationBuilder.java:164)
        at io.jpom.JpomServerApplication.main(JpomServerApplication.java:82)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.springframework.boot.loader.MainMethodRunner.run(MainMethodRunner.java:49)
        at org.springframework.boot.loader.Launcher.launch(Launcher.java:108)
        at org.springframework.boot.loader.Launcher.launch(Launcher.java:58)
        at org.springframework.boot.loader.JarLauncher.main(JarLauncher.java:65)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79

解决办法

mysql -u root -p mysql>set global log_bin_trust_function_creators = 1;

永久解决方案如下:

改配置文件，在[mysqld]部分加上： log_bin_trust_function_creators=1 注：linux系统配置文件/etc/my.cnf windows系统配置文件my.ini

报错java.sql.SQLException: Encountered illegal format of dynamic column string

2023-03-21 16:37:00.604 [main] INFO  i.j.JpomServerApplication [StartupInfoLogger.java:55] - Starting JpomServerApplication v2.10.36 using Java 1.8.0_301 on manage with PID 2016 (/usr/local/jpom-server/lib/Server-2.10.36.jar started by root in /)
2023-03-21 16:37:00.605 [main] INFO  i.j.JpomServerApplication [SpringApplication.java:631] - No active profile set, falling back to 1 default profile: "default"
2023-03-21 16:37:02.831 [main] INFO  o.s.b.w.e.t.TomcatWebServer [TomcatWebServer.java:108] - Tomcat initialized with port(s): 2122 (http)
2023-03-21 16:37:02.846 [main] INFO  o.a.c.h.Http11NioProtocol [DirectJDKLog.java:173] - Initializing ProtocolHandler ["http-nio-2122"]
2023-03-21 16:37:02.847 [main] INFO  o.a.c.c.StandardService [DirectJDKLog.java:173] - Starting service [Tomcat]
2023-03-21 16:37:02.847 [main] INFO  o.a.c.c.StandardEngine [DirectJDKLog.java:173] - Starting Servlet engine: [Apache Tomcat/9.0.71]
2023-03-21 16:37:02.988 [main] INFO  o.a.c.c.C.[.[.[/] [DirectJDKLog.java:173] - Initializing Spring embedded WebApplicationContext
2023-03-21 16:37:02.988 [main] INFO  o.s.b.w.s.c.ServletWebServerApplicationContext [ServletWebServerApplicationContext.java:292] - Root WebApplicationContext: initialization completed in 2301 ms
2023-03-21 16:37:04.134 [main] INFO  i.j.c.JpomApplicationEvent [JpomApplicationEvent.java:124] - Jpom[2.10.36] Current data path：/usr/local/jpom-server External configuration file path：file:/usr/local/jpom-server/conf/application.yml
2023-03-21 16:37:04.310 [main] INFO  i.j.c.JpomApplicationEvent [JpomApplicationEvent.java:139] - 本机安装 ID 为：07a9dd79cba9410d98879f50dc968c99
2023-03-21 16:37:04.321 [main] INFO  i.j.s.d.InitDb [InitDb.java:107] - start load MYSQL db
2023-03-21 16:37:06.448 [main] INFO  i.j.s.d.InitDb [InitDb.java:227] - exec init SQL file: table.all.v1.0.csv complete, and affected rows is: 0
2023-03-21 16:37:06.754 [main] INFO  i.j.s.d.InitDb [InitDb.java:227] - exec init SQL file: table.all.v1.1.csv complete, and affected rows is: 0
2023-03-21 16:37:06.770 [main] INFO  i.j.s.d.InitDb [InitDb.java:227] - exec init SQL file: execute.mysql.v1.0.sql complete, and affected rows is: 0
2023-03-21 16:37:06.805 [main] ERROR i.j.s.d.InitDb [InitDb.java:166] - 初始化数据库失败 alter.all.v1.0.csv
java.sql.SQLException: Encountered illegal format of dynamic column string
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
        at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:916)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1061)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1009)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1320)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdate(ClientPreparedStatement.java:994)
        at cn.hutool.db.sql.SqlExecutor.execute(SqlExecutor.java:58)
        at cn.hutool.db.AbstractDb.execute(AbstractDb.java:233)
        at io.jpom.system.db.InitDb.lambda$null$10(InitDb.java:222)
        at java.util.stream.ReferencePipeline$4$1.accept(ReferencePipeline.java:210)
        at java.util.ArrayList$ArrayListSpliterator.forEachRemaining(ArrayList.java:1384)
        at java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:482)
        at java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:472)
        at java.util.stream.ReduceOps$ReduceOp.evaluateSequential(ReduceOps.java:708)
        at java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234)
        at java.util.stream.IntPipeline.reduce(IntPipeline.java:457)
        at java.util.stream.IntPipeline.sum(IntPipeline.java:415)
        at io.jpom.system.db.InitDb.lambda$executeSql$9eed2c2c$1(InitDb.java:226)
        at cn.hutool.db.Db.tx(Db.java:191)
        at cn.hutool.db.Db.tx(Db.java:155)
        at io.jpom.system.db.InitDb.executeSql(InitDb.java:216)
        at io.jpom.system.db.InitDb.lambda$tryInitSql$8(InitDb.java:196)
        at java.util.Optional.ifPresent(Optional.java:159)
        at io.jpom.system.db.InitDb.tryInitSql(InitDb.java:193)
        at io.jpom.system.db.InitDb.afterPropertiesSet(InitDb.java:145)
        at io.jpom.common.JpomApplicationEvent.lambda$setApplicationContext$10(JpomApplicationEvent.java:348)
        at java.util.stream.ForEachOps$ForEachOp$OfRef.accept(ForEachOps.java:184)
        at java.util.stream.SortedOps$SizedRefSortingSink.end(SortedOps.java:352)
        at java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:483)
        at java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:472)
        at java.util.stream.ForEachOps$ForEachOp.evaluateSequential(ForEachOps.java:151)
        at java.util.stream.ForEachOps$ForEachOp$OfRef.evaluateSequential(ForEachOps.java:174)
        at java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234)
        at java.util.stream.ReferencePipeline.forEach(ReferencePipeline.java:418)
        at io.jpom.common.JpomApplicationEvent.setApplicationContext(JpomApplicationEvent.java:346)
        at org.springframework.context.support.ApplicationContextAwareProcessor.invokeAwareInterfaces(ApplicationContextAwareProcessor.java:128)
        at org.springframework.context.support.ApplicationContextAwareProcessor.postProcessBeforeInitialization(ApplicationContextAwareProcessor.java:102)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55

问题原因

环境：出现问题的数据库为mariadb，版本为5.5.65

原因：mariadb版本过低，存储过程函数报错

解决办法

升级为mysql5.7

将上述步骤操作完您就还原到迁移数据之前的状态了，接下来您可以再次尝试迁移数据

如果多次操作仍然失败您可以联系官方协助您排查解决问题

还原数据您还可以通过命令行的方式操作

bash ./bin/Server.sh restart -15 --replace-import-h2-sql=/opt/jpom_server/server-2-2/db/backup/20230110105352.sql

帮助我们改善此文档

#mysql

上次更新: 2023/03/23, 10:48:04

← 升级 Jpom 版本如何将 2.9.x 升级到 2.10.x→