When updating from 7.0.5 I ran into a few errors:
==================================
Updating seafile/seahub database ...
[INFO] You are using SQLite3
[INFO] updating seahub database...
Traceback (most recent call last):
File "/home/<user>/<seafilefolder>/seafile-server-7.1.0/upgrade/db_update_helper.py", line 384, in <module>
main()
File "/home/<user>/<seafilefolder>/seafile-server-7.1.0/upgrade/db_update_helper.py", line 379, in main
db_updater.update_db()
File "/home/<user>/<seafilefolder>/seafile-server-7.1.0/upgrade/db_update_helper.py", line 275, in update_db
super(SQLiteDBUpdater, self).update_db()
File "/home/<user>/<seafilefolder>/seafile-server-7.1.0/upgrade/db_update_helper.py", line 129, in update_db
self.update_seahub_sql(seahub_sql)
File "/home/<user>/<seafilefolder>/seafile-server-7.1.0/upgrade/db_update_helper.py", line 299, in update_seahub_sql
self.apply_sqls(self.seahub_db, sql_path)
File "/home/<user>/<seafilefolder>/seafile-server-7.1.0/upgrade/db_update_helper.py", line 289, in apply_sqls
conn.execute(line)
sqlite3.IntegrityError: UNIQUE constraint failed: constance_config.constance_key
Failed to upgrade your database
==================================
Checking my sqlite3 db I see the table is configured as such:
sqlite> select * from constance_config_old;
id|key|value
1|LOGIN_REMEMBER_DAYS|<value>
2|SITE_TITLE|<value>
3|FREEZE_USER_ON_LOGIN_FAILED|<value>
4|LOGIN_ATTEMPT_LIMIT|<value>
From the script for 7.1.0 upgrade:
ALTER TABLE "constance_config" RENAME TO "constance_config_old";
CREATE TABLE "constance_config" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "constance_key" varchar(255) NOT NULL UNIQUE, "value" text NULL);
INSERT INTO "constance_config" ("id", "constance_key", "value") SELECT "id", "constance_key", "value" FROM "constance_config_old";
DROP TABLE "constance_config_old";
Here it is trying to insert constance_key 4 times since the column name is “key” not “constance_key” and thus fails with the UNIQUE constraint.
I had to drop the empty constance_key table, alter the name of constance_key_old back to constance_key and edit the seahub.sql to change the column it is pulling from for the constance_key_old table.
This allows that step to proceed but then I get:
sqlite3.OperationalError: near “ON”: syntax error
The problematic line is:
DROP INDEX IF EXISTS "drafts_draft_origin_file_uuid_7c003c98_uniq" ON "drafts_draft";
I ended up removing ON "drafts_draft"
and that proceeded the upgrade again (having reverted prior to a backup db before upgrade).
then unfortunately ran into a third issue:
sqlite3.OperationalError: near “CONSTRAINT”: syntax error
This is right on the next line:
ALTER TABLE "drafts_draft" ADD CONSTRAINT "drafts_draft_origin_file_uuid_7c003c98_uniq" UNIQUE ("origin_file_uuid");
Where here, this is a limitation being hit on sqlite:
https://www.sqlite.org/omitted.html
“Only the RENAME TABLE, ADD COLUMN, and RENAME COLUMN variants of the ALTER TABLE command are supported. Other kinds of ALTER TABLE operations such as DROP COLUMN, ALTER COLUMN, ADD CONSTRAINT, and so forth are omitted.”
Seems like the same steps for the first table need to be done here to add this constraint on drafts_draft, aka rename current table to _old, create new table with this unique constraint and add back any data.
Can you please provide an updated sql script that fixes these issues?
For reference, my version of sqlite3 is:
sqlite> select sqlite_version();
3.27.2
edit, for the last issue this seems to be what is needed:
DROP INDEX IF EXISTS “drafts_draft_origin_file_uuid_7c003c98”;
ALTER TABLE “drafts_draft” RENAME TO “drafts_draft_old”;
CREATE TABLE IF NOT EXISTS “drafts_draft” (“id” integer NOT NULL PRIMARY KEY AUTOINCREMENT, “created_at” datetime NOT NULL, “updated_at” datetime NOT NULL, “username” varchar(255) NOT NULL, “origin_repo_id” varchar(36) NOT NULL UNIQUE, “origin_file_version” varchar(100) NOT NULL, “draft_file_path” varchar(1024) NOT NULL, “publish_file_version” varchar(100) NULL, “status” varchar(20) NOT NULL, “origin_file_uuid” char(32) NOT NULL);
INSERT INTO “drafts_draft” (“id”, “created_at”, “updated_at”, “username”,“origin_repo_id”,“origin_file_version”,“draft_file_path”,“publish_file_version”,“status”,“origin_file_uuid”) SELECT “id”, “created_at”, “updated_at”, “username”,“origin_repo_id”,“origin_file_version”,“draft_file_path”,“publish_file_version”,“status”,“origin_file_uuid” FROM “drafts_draft_old”;
DROP TABLE “drafts_draft_old”;
CREATE INDEX “drafts_draft_created_at_e9f4523f” ON “drafts_draft” (“created_at”);
CREATE INDEX “drafts_draft_updated_at_0a144b05” ON “drafts_draft” (“updated_at”);
CREATE INDEX “drafts_draft_username_73e6738b” ON “drafts_draft” (“username”);
CREATE INDEX IF NOT EXISTS “drafts_draft_origin_repo_id_8978ca2c” ON “drafts_draft” (“origin_repo_id”);
I unfortunately do not have data in drafts_draft to confirm.
Edit 2: the upgrade script completed after making the above changes. Unfortunately I hit more issues trying to start the server, seahub fails to start, the only indication of anything is:
!!!
!!! WARNING: configuration file should have a valid Python extension.
!!!
Here (seahub/thirdpart/gunicorn/app/base.py) it seems to be loading gunicorn.conf and then explicitly checking for it’s extension of .py or .pyc and seems to be failing since it obviously doesn’t match.
Not sure what is wrong here, but I’ve run into too many problems and will just revert to 7.0.5.
Edit 3: To provide at least some info, I had modified base.py and added .conf to the extension check, starting seahub again threw this error:
Failed to read config file: /home/user/server/conf/gunicorn.conf
Traceback (most recent call last):
File "/home/user/server/seafile-server-7.1.0/seahub/thirdpart/gunicorn/app/base.py", line 111, in get_config_from_filename
mod = importlib.util.module_from_spec(spec)
File "<frozen importlib._bootstrap>", line 580, in module_from_spec
AttributeError: 'NoneType' object has no attribute 'loader'
After this I successfully reverted back to 7.0.5 by restoring backup of the DB and running the minor upgrade script in the 7.0.5 upgrade folder.
final edit: I hadn’t read the other responses but the last issue I had is similar. I am on Debian Buster as well with python 3.7.3.