从 v2.28.21离线升级至v3.10.4时报错

user-qqqfnq2zwep · 2024 年3 月 5 日 08:39

离线升级，从 v2.28.21升级至v3.10.4时，报错如下，请教各位，应该如何处理呢？

[root@ygxb_rrx_0_209 jumpserver-offline-installer-v3.10.4-amd64]# ./jmsctl.sh upgrade
Are you sure you want to update the current version to v3.10.4 ? (y/n) (default y):

Check Configuration File
Path to Configuration file: /opt/jumpserver/config
/opt/jumpserver/config/config.txt [ √ ]
/opt/jumpserver/config/mariadb/mariadb.cnf [ √ ]
/opt/jumpserver/config/mysql/my.cnf [ √ ]
/opt/jumpserver/config/nginx/lb_http_server.conf [ √ ]
/opt/jumpserver/config/redis/redis.conf [ √ ]
/opt/jumpserver/config/nginx/cert/server.crt [ √ ]
/opt/jumpserver/config/nginx/cert/server.key [ √ ]
complete
Loading Docker Image
jumpserver/redis:6.2 <= images/redis:6.2.tar
e1325a2db1db: Loading layer [==================================================>] 84.04MB/84.04MB
f5069bc2825b: Loading layer [==================================================>] 335.4kB/335.4kB
3857666e7fb3: Loading layer [==================================================>] 11.26kB/11.26kB
431d230678b4: Loading layer [==================================================>] 4.22MB/4.22MB
d39cb5c1d4a0: Loading layer [==================================================>] 27.83MB/27.83MB
0e75e7241945: Loading layer [==================================================>] 1.536kB/1.536kB
5f70bf18a086: Loading layer [==================================================>] 1.024kB/1.024kB
fb36292c5eb1: Loading layer [==================================================>] 4.096kB/4.096kB
The image jumpserver/redis:6.2 already exists, renaming the old one with ID sha256:b8ea687821dd6beab39cd819f4205f37bd863f189ec2df112814f66252084541 to empty string
Loaded image: jumpserver/redis:6.2
jumpserver/mariadb:10.6 <= images/mariadb:10.6.tar
cf31481a5ec5: Loading layer [==================================================>] 335.4kB/335.4kB
384df4cc9f59: Loading layer [==================================================>] 11.14MB/11.14MB
a48374c814e0: Loading layer [==================================================>] 1.536kB/1.536kB
74f9b132df54: Loading layer [==================================================>] 5.12kB/5.12kB
da40f2898326: Loading layer [==================================================>] 302.1MB/302.1MB
df45ae2b27e8: Loading layer [==================================================>] 12.8kB/12.8kB
fdabfcb0f882: Loading layer [==================================================>] 27.65kB/27.65kB
The image jumpserver/mariadb:10.6 already exists, renaming the old one with ID sha256:aac2cf878de917be8d8f9824756ec5850ba7ccbfc71eaa85346635a50bd7eb89 to empty string
Loaded image: jumpserver/mariadb:10.6
jumpserver/core-ce:v3.10.4 <= images/core-ce:v3.10.4.tar
0baf2321956a: Loading layer [==================================================>] 84.17MB/84.17MB
e00d2ab597e7: Loading layer [==================================================>] 3.402MB/3.402MB
79b6835e649d: Loading layer [==================================================>] 32.97MB/32.97MB
23b0fd121873: Loading layer [==================================================>] 5.12kB/5.12kB
fb2cf8973010: Loading layer [==================================================>] 13.12MB/13.12MB
9de4a4d2f184: Loading layer [==================================================>] 390.8MB/390.8MB
906312e5652a: Loading layer [==================================================>] 922MB/922MB
d385c444b0e3: Loading layer [==================================================>] 166.9MB/166.9MB
5f70bf18a086: Loading layer [==================================================>] 1.024kB/1.024kB
Loaded image: jumpserver/core-ce:v3.10.4
jumpserver/koko:v3.10.4 <= images/koko:v3.10.4.tar
8dc48dc115c5: Loading layer [==================================================>] 199.4MB/199.4MB
1958f194307a: Loading layer [==================================================>] 184.4MB/184.4MB
fb5dacad7e94: Loading layer [==================================================>] 6.689MB/6.689MB
eaf735cd9073: Loading layer [==================================================>] 2.048kB/2.048kB
be2db3f53862: Loading layer [==================================================>] 54.78kB/54.78kB
3ea52ca7bf92: Loading layer [==================================================>] 523.8MB/523.8MB
993ea1568c90: Loading layer [==================================================>] 71.68kB/71.68kB
cea7573977f9: Loading layer [==================================================>] 66.65MB/66.65MB
Loaded image: jumpserver/koko:v3.10.4
jumpserver/lion:v3.10.4 <= images/lion:v3.10.4.tar
c4fb9a7601a5: Loading layer [==================================================>] 12.06MB/12.06MB
d2b65888203a: Loading layer [==================================================>] 77.45MB/77.45MB
5d7dcfd9a563: Loading layer [==================================================>] 5.632kB/5.632kB
11a782ee4f13: Loading layer [==================================================>] 338.9kB/338.9kB
8e1df6a1422e: Loading layer [==================================================>] 56.98MB/56.98MB
fff0b76e2cb4: Loading layer [==================================================>] 2.048kB/2.048kB
ca9932f07052: Loading layer [==================================================>] 6.717MB/6.717MB
09a35b1c0e1b: Loading layer [==================================================>] 24.72MB/24.72MB
0af0d4f2f0de: Loading layer [==================================================>] 3.584kB/3.584kB
f43b2f1df635: Loading layer [==================================================>] 3.584kB/3.584kB
fa3088617e10: Loading layer [==================================================>] 3.584kB/3.584kB
Loaded image: jumpserver/lion:v3.10.4
jumpserver/magnus:v3.10.4 <= images/magnus:v3.10.4.tar
f91e77a6cc75: Loading layer [==================================================>] 27.08MB/27.08MB
5f70bf18a086: Loading layer [==================================================>] 1.024kB/1.024kB
18d5fecc6f94: Loading layer [==================================================>] 28.16MB/28.16MB
87fc538d024d: Loading layer [==================================================>] 2.048kB/2.048kB
d541db63157e: Loading layer [==================================================>] 3.072kB/3.072kB
96d53681918f: Loading layer [==================================================>] 12.44MB/12.44MB
450f58d88031: Loading layer [==================================================>] 3.072kB/3.072kB
Loaded image: jumpserver/magnus:v3.10.4
jumpserver/chen:v3.10.4 <= images/chen:v3.10.4.tar
19ef72dc9e9d: Loading layer [==================================================>] 4.385MB/4.385MB
3febddb8ef94: Loading layer [==================================================>] 330.9MB/330.9MB
e9ffd64bee64: Loading layer [==================================================>] 25.35MB/25.35MB
5f70bf18a086: Loading layer [==================================================>] 1.024kB/1.024kB
b1303f4026cb: Loading layer [==================================================>] 28.16MB/28.16MB
6c9d08cb6399: Loading layer [==================================================>] 2.048kB/2.048kB
38ee29779edd: Loading layer [==================================================>] 83.9MB/83.9MB
0a0f3334e26a: Loading layer [==================================================>] 3.584kB/3.584kB
036fffeb1e1d: Loading layer [==================================================>] 19.62MB/19.62MB
6cec3bcbd1a3: Loading layer [==================================================>] 3.584kB/3.584kB
Loaded image: jumpserver/chen:v3.10.4
jumpserver/kael:v3.10.4 <= images/kael:v3.10.4.tar
7c37a3952bfe: Loading layer [==================================================>] 137.2MB/137.2MB
5f70bf18a086: Loading layer [==================================================>] 1.024kB/1.024kB
a4e44db6ee74: Loading layer [==================================================>] 28.16MB/28.16MB
5c2c7db4db18: Loading layer [==================================================>] 2.048kB/2.048kB
169d9b8c3395: Loading layer [==================================================>] 3.868MB/3.868MB
6a8ed407d440: Loading layer [==================================================>] 23.01MB/23.01MB
82568b3b03af: Loading layer [==================================================>] 3.072kB/3.072kB
Loaded image: jumpserver/kael:v3.10.4
jumpserver/web:v3.10.4 <= images/web:v3.10.4.tar
8b2aad57b918: Loading layer [==================================================>] 62.52MB/62.52MB
5766d5e17013: Loading layer [==================================================>] 3.584kB/3.584kB
c872dee4ad47: Loading layer [==================================================>] 4.608kB/4.608kB
a3570acb8c4d: Loading layer [==================================================>] 3.584kB/3.584kB
f949422e3d7c: Loading layer [==================================================>] 7.168kB/7.168kB
9862cb4832cb: Loading layer [==================================================>] 59.58MB/59.58MB
5f70bf18a086: Loading layer [==================================================>] 1.024kB/1.024kB
eb9c13452d06: Loading layer [==================================================>] 1.266GB/1.266GB
f1b321d2d8e4: Loading layer [==================================================>] 29.65MB/29.65MB
c3914328bdd5: Loading layer [==================================================>] 5.272MB/5.272MB
120e30d66b7d: Loading layer [==================================================>] 18MB/18MB
ccfbf7299f84: Loading layer [==================================================>] 4.096kB/4.096kB
f24b1e27b265: Loading layer [==================================================>] 8.192kB/8.192kB
5139a23a746d: Loading layer [==================================================>] 4.096kB/4.096kB
6ac656332838: Loading layer [==================================================>] 4.096kB/4.096kB
8f563647c797: Loading layer [==================================================>] 6.656kB/6.656kB
Loaded image: jumpserver/web:v3.10.4
complete
Backup database
Backing up…
[SUCCESS] Backup succeeded! The backup file has been saved to: /opt/jumpserver/db_backup/jumpserver-v2.28.21-2024-03-05_16:16:21.sql
Backup Configuration File
Back up to /opt/jumpserver/db_backup/config-v2.28.21-2024-03-05_16:16:36.conf
Apply database changes
Changing database schema may take a while, please wait patiently
Detected that the JumpServer container is running. Do you want to close the container and continue to upgrade? (y/n) (default y):

[+] Running 9/9
⠿ Container jms_celery Removed 3.0s
⠿ Container jms_redis Removed 2.5s
⠿ Container jms_core Removed 2.2s
⠿ Container jms_lion Removed 12.3s
⠿ Container jms_magnus Removed 12.9s
⠿ Container jms_koko Removed 12.6s
⠿ Container jms_web Removed 11.9s
⠿ Container jms_mysql Removed 3.9s
⠿ Network jms_net Removed 0.2s

[+] Running 4/4
⠿ Network jms_net Created 0.1s
⠿ Container jms_redis Started 2.4s
⠿ Container jms_core Started 2.2s
⠿ Container jms_mysql Started 2.2s
2024-03-05 16:18:26 Collect static files
ALLOWED_HOSTS:

localhost
core:8080
127.0.0.1
127.0.0.1:8080
127.0.0.1:80
localhost:8080
localhost:80
core:8080
core:80
ALLOWED_HOSTS:
localhost
core:8080
127.0.0.1
127.0.0.1:8080
127.0.0.1:80
localhost:8080
localhost:80
core:8080
core:80
2024-03-05 16:18:26 Collect static files done
2024-03-05 16:18:26 Check database structure change …
2024-03-05 16:18:26 Migrate model change to database …
Operations to perform:
Apply all migrations: accounts, acls, admin, applications, assets, audits, auth, authentication, captcha, common, contenttypes, django_cas_ng, django_celery_beat, labels, notifications, ops, orgs, perms, rbac, sessions, settings, terminal, tickets, users
Running migrations:
Applying assets.0093_auto_20220403_1627…2024-03-05 16:18:31 Perform migrate failed, exit
Traceback (most recent call last):
File “/opt/py3/lib/python3.11/site-packages/django/db/backends/utils.py”, line 89, in _execute
return self.cursor.execute(sql, params)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File “/opt/py3/lib/python3.11/site-packages/django/db/backends/mysql/base.py”, line 75, in execute
return self.cursor.execute(query, args)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File “/opt/py3/lib/python3.11/site-packages/MySQLdb/cursors.py”, line 179, in execute
res = self._query(mogrified_query)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File “/opt/py3/lib/python3.11/site-packages/MySQLdb/cursors.py”, line 330, in _query
db.query(q)
File “/opt/py3/lib/python3.11/site-packages/MySQLdb/connections.py”, line 255, in query
_mysql.connection.query(self, query)
MySQLdb.OperationalError: (1005, ‘Can't create table jumpserver.assets_host (errno: 150 “Foreign key constraint is incorrectly formed”)’)

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
File “/opt/jumpserver/./jms”, line 88, in perform_db_migrate
management.call_command(‘migrate’)
File “/opt/py3/lib/python3.11/site-packages/django/core/management/init.py”, line 198, in call_command
return command.execute(*args, **defaults)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File “/opt/py3/lib/python3.11/site-packages/django/core/management/base.py”, line 448, in execute
output = self.handle(*args, **options)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File “/opt/py3/lib/python3.11/site-packages/django/core/management/base.py”, line 96, in wrapped
res = handle_func(*args, **kwargs)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File “/opt/py3/lib/python3.11/site-packages/django/core/management/commands/migrate.py”, line 349, in handle
post_migrate_state = executor.migrate(
^^^^^^^^^^^^^^^^^
File “/opt/py3/lib/python3.11/site-packages/django/db/migrations/executor.py”, line 135, in migrate
state = self._migrate_all_forwards(
^^^^^^^^^^^^^^^^^^^^^^^^^^^
File “/opt/py3/lib/python3.11/site-packages/django/db/migrations/executor.py”, line 167, in _migrate_all_forwards
state = self.apply_migration(
^^^^^^^^^^^^^^^^^^^^^
File “/opt/py3/lib/python3.11/site-packages/django/db/migrations/executor.py”, line 249, in apply_migration
with self.connection.schema_editor(
File “/opt/py3/lib/python3.11/site-packages/django/db/backends/base/schema.py”, line 164, in exit
self.execute(sql)
File “/opt/py3/lib/python3.11/site-packages/django/db/backends/base/schema.py”, line 199, in execute
cursor.execute(sql, params)
File “/opt/py3/lib/python3.11/site-packages/django/db/backends/utils.py”, line 67, in execute
return self._execute_with_wrappers(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File “/opt/py3/lib/python3.11/site-packages/django/db/backends/utils.py”, line 80, in _execute_with_wrappers
return executor(sql, params, many, context)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File “/opt/py3/lib/python3.11/site-packages/django/db/backends/utils.py”, line 84, in _execute
with self.db.wrap_database_errors:
File “/opt/py3/lib/python3.11/site-packages/django/db/utils.py”, line 91, in exit
raise dj_exc_value.with_traceback(traceback) from exc_value
File “/opt/py3/lib/python3.11/site-packages/django/db/backends/utils.py”, line 89, in _execute
return self.cursor.execute(sql, params)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File “/opt/py3/lib/python3.11/site-packages/django/db/backends/mysql/base.py”, line 75, in execute
return self.cursor.execute(query, args)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File “/opt/py3/lib/python3.11/site-packages/MySQLdb/cursors.py”, line 179, in execute
res = self._query(mogrified_query)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File “/opt/py3/lib/python3.11/site-packages/MySQLdb/cursors.py”, line 330, in _query
db.query(q)
File “/opt/py3/lib/python3.11/site-packages/MySQLdb/connections.py”, line 255, in query
_mysql.connection.query(self, query)
django.db.utils.OperationalError: (1005, ‘Can't create table jumpserver.assets_host (errno: 150 “Foreign key constraint is incorrectly formed”)’)
[ERROR] Failed to change the table structure!

菜鸟运维 · 2024 年3 月 5 日 09:41

根据错误信息，问题出现在数据库迁移过程中尝试创建名为"jumpserver.assets_host"的表时遇到了外键约束的错误，具体是"Foreign key constraint is incorrectly formed"。这种错误通常是由于外键约束引用的目标表或字段不存在，或者数据类型不匹配导致的。

解决这个问题的步骤如下：

检查数据库模型定义文件：确保在创建外键约束时引用的目标表和字段名称与数据库中的实际情况一致。您可以查看相关的模型定义文件，确认外键关系是否正确定义，并且目标表和字段是否存在。
检查迁移文件：查看数据库迁移文件，尤其是与创建"jumpserver.assets_host"表以及外键约束相关的部分。确认迁移文件中的代码是否正确引用了目标表和字段，并且数据类型是否匹配。
手动执行迁移失败的SQL语句：根据错误信息中提供的信息，可以尝试手动执行迁移失败时正在执行的SQL语句，以便查看更详细的错误信息。这样可以帮助您更准确地定位问题所在。
调试数据库连接和配置：检查数据库连接是否正确设置，并确保数据库权限和配置正确。有时，数据库连接问题也会导致数据库迁移失败。
查看数据库引擎支持情况：某些数据库引擎可能对外键约束的支持有所不同，特别是在涉及到数据类型或约束定义时。确保您使用的数据库引擎支持所需的约束类型和操作。
修复数据库结构：如果确定是数据库结构出现了问题，您可以尝试手动修改数据库结构，以符合外键约束的要求。这可能包括修改表结构、添加缺失的字段或表等操作。

通过以上步骤，您应该能够更准确地定位并解决数据库迁移过程中遇到的外键约束错误。

mengyanglin · 2024 年3 月 5 日 14:54

一般v2升级到v3都不能平滑升级，有些字段不兼容了，可以看对应的表结构变更细节。或者临时取消下外键约束试试看

user-qqqfnq2zwep · 2024 年3 月 6 日 01:16

这是gpt回复的吧跟我的简直一模一样

user-qqqfnq2zwep · 2024 年3 月 6 日 01:18

好的，我试试

xiangjiang · 2024 年3 月 6 日 03:40

升级过程数据库表结构报错，根据报错提示查看报错的表存在外键约束，去除外键，再升级下。或者备份的数据库拿出来，新部署jumpserver，数据库drop掉，按v2.28的数据库编码格式从新创建一个，恢复下数据。

user-qqqfnq2zwep · 2024 年3 月 6 日 05:50

备份的数据库拿出来，新部署jumpserver，数据库drop掉，按v2.28的数据库编码格式从新创建一个，恢复下数据。

这个试过了不行，新部署的v3.10.4，drop掉数据库，按v2.28的新建，恢复，能恢复，但是好几个服务都起不来

菜鸟运维 · 2024 年3 月 6 日 06:06

机器不多建议重新部署最新的，新版可以批量导入资产，解决不了不用在这一直耗时间/

user-qqqfnq2zwep · 2024 年3 月 6 日 06:12

估计只能这样了，建议官方出个可靠的升级路线图，从哪个版本升级至哪个版本，类似gitlab那种，一劳永逸，方便你们支持，也方便所有使用者了，我试了从2.28.21升级至最早的v3版本，也不行，错误一样，报错的是另一个表

user-qqqfnq2zwep · 2024 年3 月 6 日 06:16

MariaDB [jumpserver]> SELECT CONSTRAINT_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = ‘jumpserver’ AND TABLE_NAME = ‘assets_host’;
±----------------+
| CONSTRAINT_NAME |
±----------------+
| PRIMARY |
±----------------+
1 row in set (0.000 sec)

MariaDB [jumpserver]> show create table assets_host
→ ;
±------------±-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
±------------±-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| assets_host | CREATE TABLE assets_host (
asset_ptr_id char(32) NOT NULL,
PRIMARY KEY (asset_ptr_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci |
±------------±-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

报错的这个表，没查到有外键呢？

菜鸟运维 · 2024 年3 月 6 日 06:17

你不如暂时把这个问题放着，这台机器先留着，不动它，等你部署好了新版本的回头再慢慢看。

user-qqqfnq2zwep · 2024 年3 月 6 日 06:20

好的，我现在就是在测试机器上搭了v2.28.21，在测试升级呢，等测试完了，就动生产环境了

Onedream · 2024 年3 月 8 日 01:47

旧版本数据库的字符集和校对集检查一下到数据库里执行show create database jumpserver;来获取
新版本数据库的字符集和校对集依照上面的查询情况建立，两者要保持一致。
你们上面聊的外键约束只是表象

user-qqqfnq2zwep · 2024 年3 月 8 日 01:51

就是这个问题，我改了之后，能正常升级，不报错了。

user-qqqfnq2zwep · 2024 年3 月 8 日 01:53

但是，从v2.28.21升级至v3.0.0，正常；从v3.0.0升级至v3.10.5，能正常升级没报错，升级成功起来后，登陆时候，一直有问题，输入用户名密码和mfa验证码，又跳回到输入用户名和密码那，一直这样来回跳，登不进去

菜鸟运维 · 2024 年3 月 8 日 02:14

查看下服务器系统时间是不是正确的北京时间，将其修改为正确的时区。

user-qqqfnq2zwep · 2024 年3 月 8 日 02:26

机器时间是对的

user-qqqfnq2zwep · 2024 年3 月 8 日 02:48

从v2.28.20 重新直接升级至v3.10.5，一切顺利，最后也没问题了，衷心感谢楼上诸位的帮助！

菜鸟运维 · 2024 年3 月 8 日 02:53

谢谢章鱼哥就可以了。

user-qqqfnq2zwep · 2024 年3 月 8 日 02:55

章鱼哥威武