MySQL warning on upgrade 9 to 10

Seafile Server
#1

Just updated CE version from 9 to 10 and got

[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 `repo_id` (`repo_id`)' at line 1")

That’s obviously caused by this statement from seafile-server-10.0.1/upgrade/sql/10.0.0/mysql/seafile.sql:

ALTER TABLE `WebUploadTempFiles` ADD INDEX IF NOT EXISTS `repo_id` (`repo_id`);

MySQL has not support for IF NOT EXISTS for indexes. (only MariaDB has, afaik)

#2

I’m seeing the same error during the upgrade from CE 9 to CE 10.

Updating seafile/seahub database …

[INFO] You are using MySQL
[INFO] updating ccnet database…
[INFO] updating seafile 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 repo_id (repo_id)’ at line 1”)
[INFO] updating seahub database…
Done

migrating avatars …

Done

updating /home/seafuser/seafserver/seafile-server-latest symbolic link to /home/seafuser/seafserver/seafile-server-10.0.1 …

Upgraded your seafile server successfully.

The server appears to work but is throwing a sync error with Desktop Drive Client (macOS, 2.0.26).

[05/04/23 12:36:25] sync-mgr.c(760): Repo ‘projects’ sync state transition from ‘synchronized’ to ‘uploading’.
[05/04/23 12:36:25] http-tx-mgr.c(4292): Upload with HTTP sync protocol version 2.
[05/04/23 12:36:25] http-tx-mgr.c(1331): Transfer repo ‘0e2d3f0d’: (‘normal’, ‘init’) → (‘normal’, ‘check’)
[05/04/23 12:36:25] http-tx-mgr.c(1331): Transfer repo ‘0e2d3f0d’: (‘normal’, ‘check’) → (‘normal’, ‘commit’)
[05/04/23 12:36:26] http-tx-mgr.c(1331): Transfer repo ‘0e2d3f0d’: (‘normal’, ‘commit’) → (‘normal’, ‘fs’)
[05/04/23 12:36:26] http-tx-mgr.c(1331): Transfer repo ‘0e2d3f0d’: (‘normal’, ‘fs’) → (‘normal’, ‘data’)
[05/04/23 12:36:26] http-tx-mgr.c(1331): Transfer repo ‘0e2d3f0d’: (‘normal’, ‘data’) → (‘normal’, ‘update-branch’)
[05/04/23 12:36:26] http-tx-mgr.c(4127): Bad response code for PUT https://my.domain.net/seafhttp/repo/0e2d3f0d-b72f-4127-bfad-7af0e7b59cf6/commit/HEAD/?head=5a4aba5e7094dc455a9409ebad0cddf26e1dcc71: 500.
[05/04/23 12:36:26] http-tx-mgr.c(4384): Failed to update branch of repo 0e2d3f0d.
[05/04/23 12:36:26] http-tx-mgr.c(1331): Transfer repo ‘0e2d3f0d’: (‘normal’, ‘update-branch’) → (‘error’, ‘finished’)
[05/04/23 12:36:26] sync-mgr.c(794): Repo ‘projects’ sync state transition from uploading to ‘error’: ‘Error occured in upload.’.

Is there a fix for this?

-Thanks

Ubuntu 20.04.6 LTS | mysql Ver 8.0.32-0ubuntu0.20.04.2 for Linux on x86_64 ((Ubuntu))

#3

The fix is to change the line

ALTER TABLE `WebUploadTempFiles` ADD INDEX IF NOT EXISTS `repo_id` (`repo_id`);

to

ALTER TABLE `WebUploadTempFiles` ADD INDEX `repo_id` (`repo_id`);

in

seafile-server-10.0.1/upgrade/sql/10.0.0/mysql/seafile.sql

If it still fails, you need to remove that line. (because the index exists already then)

#4

Thanks, @liayn for the quick response.

I checked the indexes using

mysql> SHOW INDEX FROM WebUploadTempFiles;

and did not see the index on repo_id so I entered it manually.

mysql> ALTER TABLE WebUploadTempFiles ADD INDEX repo_id (repo_id);

No errors reported. It appears to have worked.

As to the sync error, on a whim I quit SeaDrive, renamed (disabled) the directory “.seadrive” at the root level of the user directory and restarted the Seadrive app, creating a fresh login and allowing the files to re-sync. All is well and no sync error is reported.

Just to be clear for future readers, I rebuilt the .seadive folder before adding the index mentioned above. It appears that the sync issue was a separate problem and not directly related to the missing index.

Thank you again for your assistance with the MySQL index problem. I appreciate your help.