Database upgrade Seafile CE 11 to 12

hannes · March 2, 2025, 3:58pm

Hi!

when upgrading my Seafile CE server from 11.0.9 to 12.0.10, I get some warnings from the DB upgrade script:

-------------------------------------------------------------
This script would upgrade your seafile server from 11.0 to 12.0
Press [ENTER] to contiune
-------------------------------------------------------------



Updating seafile/seahub database ...

[INFO] You are using MySQL
[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 type varchar(10)' 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 RepoInfoTypeIndex on RepoInfo (type)' at line 1")
[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 `user_scope` varchar(225) DEFAULT 'all_users'' 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 `authed_details` LONGTEXT DEFAULT 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 `idx_ctime` (`ctime`)' 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 `idx_view_cnt` (`view_cnt`)' 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 `is_manually_set_contact_email` tinyint(1) DEFAULT 0' at line 1")
[INFO] updating seafevents database...
Done

migrating avatars ...

ln: Die symbolische Verknüpfung '/opt/seafile/seafile-server-12.0.10/seahub/media/avatars' konnte nicht angelegt werden: Die Datei existiert bereits
Done

updating /opt/seafile/seafile-server-latest symbolic link to /opt/seafile/seafile-server-12.0.10 ...



-----------------------------------------------------------------
Upgraded your seafile server successfully.
-----------------------------------------------------------------

The script says, the update is successful.
But when I launch the upgraded server, the login does not work. In the seahub.log, I find errors related to missing database fields:

[2025-03-02 15:29:20] [ERROR] django.request:241 log_response Internal Server Error: /accounts/login/
Traceback (most recent call last):
  File "/opt/seafile/seafile-server-12.0.10/seahub/thirdpart/django/db/backends/utils.py", line 89, in _execute
    return self.cursor.execute(sql, params)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/seafile/seafile-server-12.0.10/seahub/thirdpart/django/db/backends/mysql/base.py", line 75, in execute
    return self.cursor.execute(query, args)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/seafile/python-venv/lib/python3.12/site-packages/MySQLdb/cursors.py", line 179, in execute
    res = self._query(mogrified_query)
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/seafile/python-venv/lib/python3.12/site-packages/MySQLdb/cursors.py", line 330, in _query
    db.query(q)
  File "/opt/seafile/python-venv/lib/python3.12/site-packages/MySQLdb/connections.py", line 280, in query
    _mysql.connection.query(self, query)
MySQLdb.OperationalError: (1054, "Unknown column 'profile_profile.is_manually_set_contact_email' in 'field list'")

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

Traceback (most recent call last):
  File "/opt/seafile/seafile-server-12.0.10/seahub/thirdpart/django/core/handlers/exception.py", line 55, in inner
    response = get_response(request)
               ^^^^^^^^^^^^^^^^^^^^^
  File "/opt/seafile/seafile-server-12.0.10/seahub/thirdpart/django/core/handlers/base.py", line 197, in _get_response
    response = wrapped_callback(request, *callback_args, **callback_kwargs)
               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/seafile/seafile-server-12.0.10/seahub/thirdpart/django/utils/decorators.py", line 134, in _wrapper_view
    response = view_func(request, *args, **kwargs)
               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/seafile/seafile-server-12.0.10/seahub/thirdpart/django/views/decorators/cache.py", line 62, in _wrapper_view_func
    response = view_func(request, *args, **kwargs)
               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/seafile/seafile-server-12.0.10/seahub/seahub/auth/views.py", line 128, in login
    if form.is_valid():
       ^^^^^^^^^^^^^^^
  File "/opt/seafile/seafile-server-12.0.10/seahub/thirdpart/django/forms/forms.py", line 201, in is_valid
    return self.is_bound and not self.errors
                                 ^^^^^^^^^^^
  File "/opt/seafile/seafile-server-12.0.10/seahub/thirdpart/django/forms/forms.py", line 196, in errors
    self.full_clean()
  File "/opt/seafile/seafile-server-12.0.10/seahub/thirdpart/django/forms/forms.py", line 434, in full_clean
    self._clean_form()
  File "/opt/seafile/seafile-server-12.0.10/seahub/thirdpart/django/forms/forms.py", line 455, in _clean_form
    cleaned_data = self.clean()
                   ^^^^^^^^^^^^
  File "/opt/seafile/seafile-server-12.0.10/seahub/seahub/auth/forms.py", line 62, in clean
    email = Profile.objects.convert_login_str_to_username(username)
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/seafile/seafile-server-12.0.10/seahub/seahub/profile/models.py", line 129, in convert_login_str_to_username
    username = self.get_username_by_login_id(login_str)
               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/seafile/seafile-server-12.0.10/seahub/seahub/profile/models.py", line 109, in get_username_by_login_id
    return super(ProfileManager, self).get(login_id=login_id).user
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/seafile/seafile-server-12.0.10/seahub/thirdpart/django/db/models/manager.py", line 87, in manager_method
    return getattr(self.get_queryset(), name)(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/seafile/seafile-server-12.0.10/seahub/thirdpart/django/db/models/query.py", line 633, in get
    num = len(clone)
          ^^^^^^^^^^
  File "/opt/seafile/seafile-server-12.0.10/seahub/thirdpart/django/db/models/query.py", line 380, in __len__
    self._fetch_all()
  File "/opt/seafile/seafile-server-12.0.10/seahub/thirdpart/django/db/models/query.py", line 1881, in _fetch_all
    self._result_cache = list(self._iterable_class(self))
                         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/seafile/seafile-server-12.0.10/seahub/thirdpart/django/db/models/query.py", line 91, in __iter__
    results = compiler.execute_sql(
              ^^^^^^^^^^^^^^^^^^^^^
  File "/opt/seafile/seafile-server-12.0.10/seahub/thirdpart/django/db/models/sql/compiler.py", line 1562, in execute_sql
    cursor.execute(sql, params)
  File "/opt/seafile/seafile-server-12.0.10/seahub/thirdpart/django/db/backends/utils.py", line 67, in execute
    return self._execute_with_wrappers(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/seafile/seafile-server-12.0.10/seahub/thirdpart/django/db/backends/utils.py", line 80, in _execute_with_wrappers
    return executor(sql, params, many, context)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/seafile/seafile-server-12.0.10/seahub/thirdpart/django/db/backends/utils.py", line 84, in _execute
    with self.db.wrap_database_errors:
  File "/opt/seafile/seafile-server-12.0.10/seahub/thirdpart/django/db/utils.py", line 91, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/opt/seafile/seafile-server-12.0.10/seahub/thirdpart/django/db/backends/utils.py", line 89, in _execute
    return self.cursor.execute(sql, params)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/seafile/seafile-server-12.0.10/seahub/thirdpart/django/db/backends/mysql/base.py", line 75, in execute
    return self.cursor.execute(query, args)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/seafile/python-venv/lib/python3.12/site-packages/MySQLdb/cursors.py", line 179, in execute
    res = self._query(mogrified_query)
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/seafile/python-venv/lib/python3.12/site-packages/MySQLdb/cursors.py", line 330, in _query
    db.query(q)
  File "/opt/seafile/python-venv/lib/python3.12/site-packages/MySQLdb/connections.py", line 280, in query
    _mysql.connection.query(self, query)
django.db.utils.OperationalError: (1054, "Unknown column 'profile_profile.is_manually_set_contact_email' in 'field list'")

The server is running on Ubuntu 22.04 (not in a docker container).
MySQL is 8.0.41

What can I do to rectify things?

Thanks!

mercury · March 3, 2025, 1:16am

I believe that error has been seen before in upgrade scripts. Please see here and here for reference.

Previously the solution has been to issue the SQL statements manually and check the results.

I am sorry for your trouble. In my opinion the developers should test the scripts with MySQL 8 too.

dzmbbs · March 3, 2025, 6:16am

The seafile 12.0 upgrade sql is here, /opt/seafile/seafile-server-latest/upgrade/sql/12.0.0/mysql/seafile.sql, /opt/seafile/seafile-server-latest/upgrade/sql/12.0.0/mysql/seahub.sql, /opt/seafile/seafile-server-latest/upgrade/sql/12.0.0/mysql/seafevents.sql, adapted to MariaDB 10/11.

Mysql 8 may not support CREATE INDEX IF NOT EXISTS, ADD INDEX IF NOT EXISTS, ADD COLUMN IF NOT EXISTS, you can modify the sql manually and execute the upgrade sql in Mysql.

hannes · March 3, 2025, 6:30am

Thanks for the hint!

Is it safe to just modify the SQL statements and then reexecute the update script after a previous attempt?
Or should I figure out the affected SQL statements and only execute these?

dzmbbs · March 3, 2025, 6:40am

You can refer to this PR https://github.com/haiwen/seahub/pull/7543/files, which only requires two lines of modification in seafile_db and five lines of modification in seahub_db.

We will fix this in 12.0.11.

hannes · March 3, 2025, 7:32am

Dear dzmbbs,

thanks for pointing to the SQL statements!

I have execute only the affected SQL statements through the mysql client manually (not rerun the entire update script). Now it seems to work fine.

dzmbbs · March 3, 2025, 8:02am

You’re welcome

chopperpl · March 14, 2025, 4:07pm

Is this mysql script executed automatically when upgrading or you have to run it manually? There is no word in upgrade document (11>12) about upgrading database.

mercury · March 15, 2025, 4:46pm

If you have already run the upgrade script and encountered the errors, go back and run the SQL statements manually from the terminal to complete the upgrade. Otherwise it might be easier to wait for v.12.0.11 to be released which will include the fixes.