数据库迁移到 mysql
# 前言
Jpom 服务端的数据默认存储到 H2database 中
部分用户有需求期望数据存储到 mysql 中
提醒
使用 mysql 模式,系统管理中的数据库备份功能将不可用,数据的备份管理需要自行维护
# 系统升级
使用jpom的在线升级功能将将服务端和插件端版本升级至 2.10.11
注意
升级前请仔细阅读版本更新日志,如果有特殊说明或者注意事项请仔细确认。升级前建议提前做好相关备份,避免出现意外造成数据丢失
# 如何创建 h2 数据备份
# 页面操作创建
# 命令行创建
bash ./bin/Server.sh restart -15 --backup-h2
1
# 修改配置
配置文件: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
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
1
如果您修改过 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
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
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 org.dromara.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 org.dromara.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 org.dromara.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
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 org.dromara.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 org.dromara.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
1
帮助我们改善此文档 (opens new window)
上次更新: 2023/09/20, 13:11:05