seafile6.2.3 sqlite如何迁移至mysql

jialan · 2022 年8 月 1 日 13:27

数据库迁移原因：seafile6.2.3通过一键式脚本部署，默认sqlite数据库，前期使用量较少的时候无问题，但使用率上来后（600注册，200设备连接），经常出现服务卡死，无法访问web以及客户端无法刷新的问题，每次都需要重启seafile.sh 以及seahub.sh 之后才能恢复，查看服务器的内存以及cpu占用率并无问题，判断应该是sqlite性能问题导致，现需要将数据库迁移至MYSQL进行进一步判断。

sqlite数据库文件路径：
/home/MyCloud/ccent/GroupMgr/groupmgr.db
/home/MyCloud/ccent/OrgMgr/orgmgr.db
/home/MyCloud/ccent/PreeMgr/usermgr.db
/home/MyCloud/seahub.db
/home/seafile/seafile.db

请问如何可以将数据库迁移到mysql中，修改seafile业务配置指向mysql，转移前应该做好哪些数据库以及配置文件备份，请各位支持，谢谢。

han-seafile · 2022 年8 月 2 日 01:26

migrate_from_sqlite_to_mysql - Seafile Cloud 参考这个文档

jialan · 2022 年8 月 2 日 02:12

您好管理员，sqlite2mysql.sh和sqlite2mysql.py这两个文件下载失效了，所以无法参考，能否更新下下载呢，谢谢。

jialan · 2022 年8 月 2 日 02:30

附上seafile.log最新的报错信息
MODE: sqlite3_prepare_v2 failed: near “LOCK”: syntax error.
[08/02/2022 10:29:28 AM] …/common/seaf-db.c(425): Error prepare statement SELECT repo_id, commit_id FROM Branch WHERE name=‘master’ AND repo_id IN (‘4a52eb57-fb74-4d68-b4b8-a20dae536316’) LOCK IN SHARE MODE: sqlite3_prepare_v2 failed: near “LOCK”: syntax error.
[08/02/2022 10:29:28 AM] …/common/seaf-db.c(425): Error prepare statement SELECT repo_id, commit_id FROM Branch WHERE name=‘master’ AND repo_id IN (‘bdabb2d3-fdc7-4062-a40e-aa936d973d01’,‘0641d7e4-58e1-4318-9b11-4df47638d681’) LOCK IN SHARE MODE: sqlite3_prepare_v2 failed: near “LOCK”: syntax error.
[08/02/2022 10:29:28 AM] …/common/seaf-db.c(425): Error prepare statement SELECT repo_id, commit_id FROM Branch WHERE name=‘master’ AND repo_id IN (‘64775600-9583-4df6-89e4-745dfe7a5950’,‘dc5a2427-595d-4de4-95d3-e8da17344dfe’,‘6087a6ac-e359-449e-8b47-d4bc303daef4’) LOCK IN SHARE MODE: sqlite3_prepare_v2 failed: near “LOCK”: syntax error.
[08/02/2022 10:29:28 AM] …/common/seaf-db.c(425): Error prepare statement SELECT repo_id, commit_id FROM Branch WHERE name=‘master’ AND repo_id IN (‘6566ce99-0e66-461d-8a37-61b217b07739’) LOCK IN SHARE MODE: sqlite3_prepare_v2 failed: near “LOCK”: syntax error.
[08/02/2022 10:29:29 AM] …/common/seaf-db.c(425): Error prepare statement SELECT repo_id, commit_id FROM Branch WHERE name=‘master’ AND repo_id IN (‘269d16b9-9f82-4e12-bac4-62a693aaa3d6’) LOCK IN SHARE MODE: sqlite3_prepare_v2 failed: near “LOCK”: syntax error.
[08/02/2022 10:29:29 AM] …/common/seaf-db.c(425): Error prepare statement SELECT repo_id, commit_id FROM Branch WHERE name=‘master’ AND repo_id IN (‘bbf3b784-debb-4c1c-95d1-0e80b50a7032’) LOCK IN SHARE MODE: sqlite3_prepare_v2 failed: near “LOCK”: syntax error.
[08/02/2022 10:29:29 AM] …/common/seaf-db.c(425): Error prepare statement SELECT repo_id, commit_id FROM Branch WHERE name=‘master’ AND repo_id IN (‘afecd827-305e-4770-9816-378ec8fa39d5’) LOCK IN SHARE MODE: sqlite3_prepare_v2 failed: near “LOCK”: syntax error.
[08/02/2022 10:29:29 AM] …/common/seaf-db.c(425): Error prepare statement SELECT repo_id, commit_id FROM Branch WHERE name=‘master’ AND repo_id IN (‘bb573b64-472e-4736-9e77-156b46b4bab7’) LOCK IN SHARE MODE: sqlite3_prepare_v2 failed: near “LOCK”: syntax error.
[08/02/2022 10:29:30 AM] …/common/seaf-db.c(425): Error prepare statement SELECT repo_id, commit_id FROM Branch WHERE name=‘master’ AND repo_id IN (‘0f434996-ad3a-43cd-b064-78229d6e946f’) LOCK IN SHARE MODE: sqlite3_prepare_v2 failed: near “LOCK”: syntax error.
[08/02/2022 10:29:30 AM] …/common/seaf-db.c(425): Error prepare statement SELECT repo_id, commit_id FROM Branch WHERE name=‘master’ AND repo_id IN (‘bdabb2d3-fdc7-4062-a40e-aa936d973d01’,‘49dc9079-5404-4a62-999f-ab16521b00ca’) LOCK IN SHARE MODE: sqlite3_prepare_v2 failed: near “LOCK”: syntax error.
[08/02/2022 10:29:30 AM] …/common/seaf-db.c(425): Error prepare statement SELECT repo_id, commit_id FROM Branch WHERE name=‘master’ AND repo_id IN (‘f558c8d0-b6a5-41ce-9d9e-a774811768f5’) LOCK IN SHARE MODE: sqlite3_prepare_v2 failed: near “LOCK”: syntax error.
[08/02/2022 10:29:30 AM] …/common/seaf-db.c(425): Error prepare statement SELECT repo_id, commit_id FROM Branch WHERE name=‘master’ AND repo_id IN (‘b458efe7-38a4-43db-8dbf-e5c9b08320c8’) LOCK IN SHARE MODE: sqlite3_prepare_v2 failed: near “LOCK”: syntax error.
[08/02/2022 10:29:31 AM] …/common/seaf-db.c(425): Error prepare statement SELECT repo_id, commit_id FROM Branch WHERE name=‘master’ AND repo_id IN (‘4ac3d985-8a66-4e4e-be03-ca1c91098c6e’) LOCK IN SHARE MODE: sqlite3_prepare_v2 failed: near “LOCK”: syntax error.
[08/02/2022 10:29:32 AM] …/common/seaf-db.c(425): Error prepare statement SELECT repo_id, commit_id FROM Branch WHERE name=‘master’ AND repo_id IN (‘4d57f9ca-6502-4225-a8f7-17e6f82fb1a1’) LOCK IN SHARE MODE: sqlite3_prepare_v2 failed: near “LOCK”: syntax error.
[08/02/2022 10:29:33 AM] …/common/seaf-db.c(425): Error prepare statement SELECT repo_id, commit_id FROM Branch WHERE name=‘master’ AND repo_id IN (‘0ef47861-8c6c-456a-8af9-db129959668a’,‘c1abf8ec-501b-4ced-a23b-7d0e9317cf18’,‘1018f710-e6b8-415a-9058-98c1073eefcc’,‘81960fe7-7f69-43db-9fe2-8a537ffa4831’) LOCK IN SHARE MODE: sqlite3_prepare_v2 failed: near “LOCK”: syntax error.
[08/02/2022 10:29:33 AM] …/common/seaf-db.c(425): Error prepare statement SELECT repo_id, commit_id FROM Branch WHERE name=‘master’ AND repo_id IN (‘f5eedb48-400a-4f27-8baf-5197d2a52c0f’,‘02daaebe-ac81-46ba-81cc-a4f145648d95’,‘bdabb2d3-fdc7-4062-a40e-aa936d973d01’) LOCK IN SHARE MODE: sqlite3_prepare_v2 failed: near “LOCK”: syntax error.
[08/02/2022 10:29:33 AM] …/common/seaf-db.c(425): Error prepare statement SELECT repo_id, commit_id FROM Branch WHERE name=‘master’ AND repo_id IN (‘c19e4dd6-fe1f-44ff-b2ec-4bb4a8e65b7f’) LOCK IN SHARE MODE: sqlite3_prepare_v2 failed: near “LOCK”: syntax error.
[08/02/2022 10:29:33 AM] …/common/seaf-db.c(425): Error prepare statement SELECT repo_id, commit_id FROM Branch WHERE name=‘master’ AND repo_id IN (‘d7673ddf-4916-4dc7-bb8d-9f2173b9b605’,‘58d99038-1969-4d97-b178-dfc83ae09d69’) LOCK IN SHARE MODE: sqlite3_prepare_v2 failed: near “LOCK”: syntax error.
[08/02/2022 10:29:34 AM] …/common/seaf-db.c(425): Error prepare statement SELECT repo_id, commit_id FROM Branch WHERE name=‘master’ AND repo_id IN (‘7ff74037-7ac0-4aad-8c13-62af03b9012e’) LOCK IN SHARE MODE: sqlite3_prepare_v2 failed: near “LOCK”: syntax error.
[08/02/2022 10:29:35 AM] …/common/seaf-db.c(425): Error prepare statement SELECT repo_id, commit_id FROM Branch WHERE name=‘master’ AND repo_id IN (‘1c07f3f6-f8f3-4edf-b000-d0d166b8e656’) LOCK IN SHARE MODE: sqlite3_prepare_v2 failed: near “LOCK”: syntax error.

jialan · 2022 年8 月 2 日 02:53

查看到脚本文本已可以访问，我这里再试下迁移，感谢

jialan · 2022 年8 月 2 日 09:50

—修改数据库路径后执行sqlite2mysql.sh成功
–修改 /etc/my.conf，在centos里面是my.cnf，添加语句保存无问题
–导入ccnet-db.sql，无报错
–修改ccnet.conf、seafile.conf、seahub_settings.py正常保存

（报错）导入seafile-db，seahub-db中报错，提示值重复，查询数据库表中发现账户很多都有大写账户以及小写账户，日常分享文件的时候也能看见。

部分报错信息：
Query:
INSERT INTO UserQuota VALUES(‘FL00001677@FL.COM’,10000000000)

Error occured at:2022-08-02 13:43:05
Line no.:15791
Error Code: 1062 - Duplicate entry ‘FL00001677@FL.COM’ for key ‘PRIMARY’

Query:
INSERT INTO profile_profile VALUES(799,‘fl00000133@fl.com’,‘杨平’,‘’,NULL,NULL,NULL,‘’,0)

Error occured at:2022-08-02 13:43:44
Line no.:8079
Error Code: 1062 - Duplicate entry ‘fl00000133@fl.com’ for key ‘user’

Query:
INSERT INTO profile_profile VALUES(799,‘fl00000133@fl.com’,‘杨平’,‘’,NULL,NULL,NULL,‘’,0)

Error occured at:2022-08-02 13:45:13
Line no.:8079
Error Code: 1062 - Duplicate entry ‘fl00000133@fl.com’ for key ‘user’

Query:
INSERT INTO profile_profile VALUES(799,‘fl00000133@fl.com’,‘杨平’,‘’,NULL,NULL,NULL,‘’,0)

Error occured at:2022-08-02 13:45:58
Line no.:8079
Error Code: 1062 - Duplicate entry ‘fl00000133@fl.com’ for key ‘user’

Query:
INSERT INTO profile_profile VALUES(800,‘fl00023781@fl.com’,‘陈可’,‘’,NULL,NULL,NULL,‘’,0)

Error occured at:2022-08-02 13:45:58
Line no.:8081
Error Code: 1062 - Duplicate entry ‘fl00023781@fl.com’ for key ‘user’

忽略报错后，启动seafile。
./seafile.sh start 正常
./seahub.sh start报错（ Seahub failed to start，提示重新运行./seahub.sh start，重复运行无效）

han-seafile · 2022 年8 月 3 日 04:59

seahub 启动不了可以改 gunicorn.conf 的daemon = True 为 False 看一下启动报错

jialan · 2022 年8 月 3 日 05:59

我这里目录下 /home/MyCloud/seafile-server/seahub/thirdpart/ 的gunicorn文件为可执行文件，内容如下：
[root@localhost thirdpart]# cat gunicorn
#!/usr/bin/python

EASY-INSTALL-ENTRY-SCRIPT: ‘gunicorn==19.4.5’,‘console_scripts’,‘gunicorn’

requires = ‘gunicorn==19.4.5’
import sys
from pkg_resources import load_entry_point

if name == ‘main’:
sys.exit(
load_entry_point(‘gunicorn==19.4.5’, ‘console_scripts’, ‘gunicorn’)()
)

未查到daemon参数设置信息，也未找到gunicorn.conf文件，目前准备进行第二次迁移

jialan · 2022 年8 月 8 日 04:17

\依旧在迁移后无法启动seahub，且找不到gunicorn.conf文件

jialan · 2022 年8 月 10 日 08:24

你好管理员，在添加了daemon = True 为 False后发现的启动报错，MYSQL是自行找的离线包安装，是否是缺少模块，需要怎样处理呢，希望回复，谢谢。

django.core.exceptions.ImproperlyConfigured: Error loading MySQLdb module: libmysqlclient.so.18: cannot open shared object file: No such file or directory

Ffeng · 2022 年8 月 11 日 00:42

./seahub.sh start-fastcgi 就可以看到启动报错。

jialan · 2022 年8 月 11 日 02:08

你好：昨日提示django.core.exceptions.ImproperlyConfigured: Error loading MySQLdb module: libmysqlclient.so.18: cannot open shared object file: No such file or directory报错之后，我单独下载了libmysqlclient.so.18并安装，启动的时候seahub能够正常启动，但开始报页面错误，请问这属于哪方面问题，感谢回复。

han-seafile · 2022 年8 月 11 日 02:10

看一下seahub日志

jialan · 2022 年8 月 11 日 02:17

请问是这个日志嘛，今日内容如下：
2022-08-11 02:02:21,253 [ERROR] seahub.api2.views:3106 get Cannot create sub-library from a sub-library
2022-08-11 02:03:17,259 [ERROR] seahub.api2.views:3106 get Cannot create sub-library from a sub-library
2022-08-11 02:04:16,652 [ERROR] seahub.api2.views:3106 get Cannot create sub-library from a sub-library
2022-08-11 02:07:47,302 [ERROR] seahub.api2.views:3106 get Cannot create sub-library from a sub-library
2022-08-11 02:08:14,947 [ERROR] seahub.api2.views:3106 get Cannot create sub-library from a sub-library

没有有效信息

jialan · 2022 年8 月 11 日 02:22

seahub_django_request.log也无最新日志信息，均为数据库切换前的信息
OperationalError: (1193, “Unknown system variable ‘storage_engine’”)
2022-08-11 02:01:11,843 [WARNING] django.request:170 get_response Not Found: /api/v2.1/smart-link/
2022-08-11 02:02:32,359 [WARNING] django.request:170 get_response Not Found: /api/v2.1/smart-link/
2022-08-11 02:07:06,944 [WARNING] django.request:170 get_response Not Found: /seafhttp/protocol-version

han-seafile · 2022 年8 月 11 日 02:50

没有最新的日志吗，这个时间还是两点的

jialan · 2022 年8 月 11 日 03:03

这两个日志文件都没有最新的日志，我中午尝试先清理缓存后试试

jialan · 2022 年8 月 11 日 03:34

执行./seahub.sh start-fastcgi 启动后的日志，提示启动失败。

[root@localhost seafile-server]# ./seafile.sh start

[08/11/22 11:30:51] …/common/session.c(132): using config file /home/MyCloud/conf/ccnet.conf
Starting seafile server, please wait …
Seafile server started

Done.
[root@localhost seafile-server]# ./seahub.sh start-fastcgi

LC_ALL is not set in ENV, set to en_US.UTF-8
Starting seahub (fastcgi) at 127.0.0.1:8000 …
Traceback (most recent call last):
File “/home/MyCloud/seafile-server/seahub/manage.py”, line 10, in
execute_from_command_line(sys.argv)
File “/home/MyCloud/seafile-server/seahub/thirdpart/Django-1.8.18-py2.7.egg/django/core/management/init.py”, line 354, in execute_from_command_line
utility.execute()
File “/home/MyCloud/seafile-server/seahub/thirdpart/Django-1.8.18-py2.7.egg/django/core/management/init.py”, line 346, in execute
self.fetch_command(subcommand).run_from_argv(self.argv)
File “/home/MyCloud/seafile-server/seahub/thirdpart/Django-1.8.18-py2.7.egg/django/core/management/base.py”, line 394, in run_from_argv
self.execute(args, cmd_options)
File “/home/MyCloud/seafile-server/seahub/thirdpart/Django-1.8.18-py2.7.egg/django/core/management/base.py”, line 444, in execute
self.check()
File “/home/MyCloud/seafile-server/seahub/thirdpart/Django-1.8.18-py2.7.egg/django/core/management/base.py”, line 482, in check
include_deployment_checks=include_deployment_checks,
File “/home/MyCloud/seafile-server/seahub/thirdpart/Django-1.8.18-py2.7.egg/django/core/checks/registry.py”, line 72, in run_checks
new_errors = check(app_configs=app_configs)
File “/home/MyCloud/seafile-server/seahub/thirdpart/Django-1.8.18-py2.7.egg/django/core/checks/model_checks.py”, line 28, in check_all_models
errors.extend(model.check(kwargs))
File “/home/MyCloud/seafile-server/seahub/thirdpart/Django-1.8.18-py2.7.egg/django/db/models/base.py”, line 1205, in check
errors.extend(cls._check_fields(kwargs))
File “/home/MyCloud/seafile-server/seahub/thirdpart/Django-1.8.18-py2.7.egg/django/db/models/base.py”, line 1282, in _check_fields
errors.extend(field.check(kwargs))
File “/home/MyCloud/seafile-server/seahub/thirdpart/Django-1.8.18-py2.7.egg/django/db/models/fields/init.py”, line 934, in check
errors = super(AutoField, self).check(kwargs)
File “/home/MyCloud/seafile-server/seahub/thirdpart/Django-1.8.18-py2.7.egg/django/db/models/fields/init.py”, line 207, in check
errors.extend(self._check_backend_specific_checks(kwargs))
File “/home/MyCloud/seafile-server/seahub/thirdpart/Django-1.8.18-py2.7.egg/django/db/models/fields/init.py”, line 306, in _check_backend_specific_checks
return connection.validation.check_field(self, kwargs)
File “/home/MyCloud/seafile-server/seahub/thirdpart/Django-1.8.18-py2.7.egg/django/db/backends/mysql/validation.py”, line 18, in check_field
field_type = field.db_type(connection)
File “/home/MyCloud/seafile-server/seahub/thirdpart/Django-1.8.18-py2.7.egg/django/db/models/fields/init.py”, line 614, in db_type
return connection.data_types[self.get_internal_type()] % data
File “/home/MyCloud/seafile-server/seahub/thirdpart/Django-1.8.18-py2.7.egg/django/db/init.py”, line 36, in getattr
return getattr(connections[DEFAULT_DB_ALIAS], item)
File “/home/MyCloud/seafile-server/seahub/thirdpart/Django-1.8.18-py2.7.egg/django/utils/functional.py”, line 59, in get
res = instance.dict[self.name] = self.func(instance)
File “/home/MyCloud/seafile-server/seahub/thirdpart/Django-1.8.18-py2.7.egg/django/db/backends/mysql/base.py”, line 196, in data_types
if self.features.supports_microsecond_precision:
File “/home/MyCloud/seafile-server/seahub/thirdpart/Django-1.8.18-py2.7.egg/django/utils/functional.py”, line 59, in get
res = instance.dict[self.name] = self.func(instance)
File “/home/MyCloud/seafile-server/seahub/thirdpart/Django-1.8.18-py2.7.egg/django/db/backends/mysql/features.py”, line 52, in supports_microsecond_precision
return self.connection.mysql_version >= (5, 6, 4) and Database.version_info >= (1, 2, 5)
File “/home/MyCloud/seafile-server/seahub/thirdpart/Django-1.8.18-py2.7.egg/django/utils/functional.py”, line 59, in get
res = instance.dict[self.name] = self.func(instance)
File “/home/MyCloud/seafile-server/seahub/thirdpart/Django-1.8.18-py2.7.egg/django/db/backends/mysql/base.py”, line 371, in mysql_version
with self.temporary_connection():
File “/usr/lib64/python2.7/contextlib.py”, line 17, in enter
return self.gen.next()
File “/home/MyCloud/seafile-server/seahub/thirdpart/Django-1.8.18-py2.7.egg/django/db/backends/base/base.py”, line 462, in temporary_connection
cursor = self.cursor()
File “/home/MyCloud/seafile-server/seahub/thirdpart/Django-1.8.18-py2.7.egg/django/db/backends/base/base.py”, line 164, in cursor
cursor = self.make_cursor(self._cursor())
File “/home/MyCloud/seafile-server/seahub/thirdpart/Django-1.8.18-py2.7.egg/django/db/backends/base/base.py”, line 135, in _cursor
self.ensure_connection()
File “/home/MyCloud/seafile-server/seahub/thirdpart/Django-1.8.18-py2.7.egg/django/db/backends/base/base.py”, line 130, in ensure_connection
self.connect()
File “/home/MyCloud/seafile-server/seahub/thirdpart/Django-1.8.18-py2.7.egg/django/db/utils.py”, line 98, in exit
six.reraise(dj_exc_type, dj_exc_value, traceback)
File “/home/MyCloud/seafile-server/seahub/thirdpart/Django-1.8.18-py2.7.egg/django/db/backends/base/base.py”, line 130, in ensure_connection
self.connect()
File “/home/MyCloud/seafile-server/seahub/thirdpart/Django-1.8.18-py2.7.egg/django/db/backends/base/base.py”, line 119, in connect
self.connection = self.get_new_connection(conn_params)
File “/home/MyCloud/seafile-server/seahub/thirdpart/Django-1.8.18-py2.7.egg/django/db/backends/mysql/base.py”, line 276, in get_new_connection
conn = Database.connect(conn_params)
File “/usr/lib64/python2.7/site-packages/MySQLdb/init.py”, line 81, in Connect
return Connection(args, kwargs)
File “/usr/lib64/python2.7/site-packages/MySQLdb/connections.py”, line 193, in init**
super(Connection, self).init(*args, **kwargs2)
django.db.utils.OperationalError: (1193, “Unknown system variable ‘storage_engine’”)
Error:Seahub failed to start.

执行。/seahub.sh start 如下，能成功但是打开页面还是page unavailable
[root@localhost seafile-server]# ./seahub.sh start

LC_ALL is not set in ENV, set to en_US.UTF-8
Starting seahub at port 8000 …

Seahub is started

Done.

jialan · 2022 年8 月 11 日 03:45

·······保留编辑
清理缓存后依旧启动页面错误

Ffeng · 2022 年8 月 11 日 06:33

把conf/seahub_settings.py 发出来看一下。

seafile6.2.3 sqlite如何迁移至mysql

您好管理员，sqlite2mysql.sh和sqlite2mysql.py这两个文件下载失效了，所以无法参考，能否更新下下载呢，谢谢。

—修改数据库路径后执行sqlite2mysql.sh成功 –修改 /etc/my.conf，在centos里面是my.cnf，添加语句保存无问题 –导入ccnet-db.sql，无报错 –修改ccnet.conf、seafile.conf、seahub_settings.py正常保存

Error occured at:2022-08-02 13:45:58 Line no.:8081 Error Code: 1062 - Duplicate entry ‘fl00023781@fl.com’ for key ‘user’

EASY-INSTALL-ENTRY-SCRIPT: ‘gunicorn==19.4.5’,‘console_scripts’,‘gunicorn’

执行./seahub.sh start-fastcgi 启动后的日志，提示启动失败。

—修改数据库路径后执行sqlite2mysql.sh成功
–修改 /etc/my.conf，在centos里面是my.cnf，添加语句保存无问题
–导入ccnet-db.sql，无报错
–修改ccnet.conf、seafile.conf、seahub_settings.py正常保存

Error occured at:2022-08-02 13:45:58
Line no.:8081
Error Code: 1062 - Duplicate entry ‘fl00023781@fl.com’ for key ‘user’