Seafile CE 11.0.5 Upgrade - MySQL Warnings - Syntax Error

Seafile Server
1

Seafile Server CE 10.0.1 upgrade to Seafile CE 11.0.5:

Updating seafile/seahub database …

[INFO] You are using MySQL
[INFO] updating seahub database…
[WARNING] Failed to execute sql: (1064, “You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘IF NOT EXISTS share_uploadlinkshare_expire_date (expire_date)’ at line 1”)
[WARNING] Failed to execute sql: (1054, “Unknown column ‘domain’ in ‘org_saml_config’”)
[WARNING] Failed to execute sql: (1064, “You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘IF NOT EXISTS dns_txt varchar(64) NULL’ at line 1”)
[WARNING] Failed to execute sql: (1064, “You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘IF NOT EXISTS domain_verified TINYINT(1) NOT NULL DEFAULT 0’ at line 1”)
[WARNING] Failed to execute sql: (1064, “You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘IF NOT EXISTS org_saml_config_domain_verified_398065b9 (domain_verified)’ at line 1”)
[WARNING] Failed to execute sql: (1064, “You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘IF NOT EXISTS idp_certificate longtext DEFAULT NULL’ at line 1”)
[WARNING] Failed to execute sql: (1054, “Unknown column ‘domain_verified’ in ‘where clause’”)
Done

migrating avatars …

Done

Ubuntu 22.04.3 LTS
mysql Ver 8.0.36-0ubuntu0.22.04.1 for Linux on x86_64 ((Ubuntu))

The script reported that the upgrade was successful. The server is operational but I suspect a few things referenced in the warnings above are broken (still checking).

Can anyone advise on how to verify or fix the issues related to the MySQL errors? My guess is that these errors will be encountered by others.

-Thank you

2

So generally… It looks like that mysql version does not fully support the syntax of the upgrade sql scripts - all those 1064 errors seem like that version 8.0.x of yours does not recognize the IF NOT EXISTS part of the SQL statements.

The reason for this looks like the same as mentioned in MySQL warning on upgrade 9 to 10

Having this in mind, we can work around those errors. Connect to the seahub database with e.g.
mysql --host localhost --protocol=tcp --port=3306 --user root -p dbseahub
(the last parameter is the database name, adapt to what you have in your seahub_settings.py), then enter the mysql root password.

Afterwards, enter the following statements:

ALTER TABLE `share_uploadlinkshare` ADD INDEX `share_uploadlinkshare_expire_date` (`expire_date`);
ALTER TABLE `org_saml_config` ADD COLUMN `domain` varchar(255) NOT NULL;
ALTER TABLE `org_saml_config` CHANGE domain domain varchar(255) DEFAULT NULL;
ALTER TABLE `org_saml_config` ADD COLUMN `dns_txt` varchar(64) NULL;
ALTER TABLE `org_saml_config` ADD COLUMN `domain_verified` TINYINT(1) NOT NULL DEFAULT 0;
ALTER TABLE `org_saml_config` ADD INDEX `org_saml_config_domain_verified_398065b9` (`domain_verified`);
ALTER TABLE `org_saml_config` ADD COLUMN `idp_certificate` longtext DEFAULT NULL;
UPDATE `org_saml_config` SET domain_verified=1 WHERE domain_verified=0;
COMMIT;

Last, exit from the mysql shell with
\q

1 Like
3

Seems to have worked like a charm. Many thanks for the tip.

4

Copy of additional information from Upgrade from 10.0.1 to 11.0.5 - Issues with MYSQL - #26 by Nightshade on this topic, too (for completeness):

An unique key also needs to be added manually by:

ALTER TABLE `org_saml_config` ADD UNIQUE KEY `domain` (`domain`);

1 Like