Problem with shared links after migration from SQLite to MySQL

strannick-ru · December 30, 2020, 2:37pm

Hello. I was try to migrate DB from SQLite to MySQL with this github script techouse/sqlite3-to-mysql and all working well, but when I try to make share link, I see an error message and logs look like this:

logs/seahub.log

==> logs/seahub.log <==
2020-12-30 14:27:04,397 [ERROR] django.request:132 handle_uncaught_exception Internal Server Error: /api/v2.1/share-links/
Traceback (most recent call last):
  File "/data/haiwen/seafile-server-7.1.5/seahub/thirdpart/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
  File "/data/haiwen/seafile-server-7.1.5/seahub/thirdpart/django/db/backends/mysql/base.py", line 101, in execute
    return self.cursor.execute(query, args)
  File "/data/haiwen/seafile-server-7.1.5/seahub/thirdpart/pymysql/cursors.py", line 163, in execute
    result = self._query(query)
  File "/data/haiwen/seafile-server-7.1.5/seahub/thirdpart/pymysql/cursors.py", line 321, in _query
    conn.query(q)
  File "/data/haiwen/seafile-server-7.1.5/seahub/thirdpart/pymysql/connections.py", line 505, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/data/haiwen/seafile-server-7.1.5/seahub/thirdpart/pymysql/connections.py", line 724, in _read_query_result
    result.read()
  File "/data/haiwen/seafile-server-7.1.5/seahub/thirdpart/pymysql/connections.py", line 1069, in read
    first_packet = self.connection._read_packet()
  File "/data/haiwen/seafile-server-7.1.5/seahub/thirdpart/pymysql/connections.py", line 676, in _read_packet
    packet.raise_for_error()
  File "/data/haiwen/seafile-server-7.1.5/seahub/thirdpart/pymysql/protocol.py", line 223, in raise_for_error
    err.raise_mysql_exception(self._data)
  File "/data/haiwen/seafile-server-7.1.5/seahub/thirdpart/pymysql/err.py", line 107, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.OperationalError: (1364, "Field 'id' doesn't have a default value")

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

Traceback (most recent call last):
  File "/data/haiwen/seafile-server-7.1.5/seahub/thirdpart/django/core/handlers/exception.py", line 41, in inner
    response = get_response(request)
  File "/data/haiwen/seafile-server-7.1.5/seahub/thirdpart/django/core/handlers/base.py", line 249, in _legacy_get_response
    response = self._get_response(request)
  File "/data/haiwen/seafile-server-7.1.5/seahub/thirdpart/django/core/handlers/base.py", line 187, in _get_response
    response = self.process_exception_by_middleware(e, request)
  File "/data/haiwen/seafile-server-7.1.5/seahub/thirdpart/django/core/handlers/base.py", line 185, in _get_response
    response = wrapped_callback(request, *callback_args, **callback_kwargs)
  File "/data/haiwen/seafile-server-7.1.5/seahub/thirdpart/django/views/decorators/csrf.py", line 58, in wrapped_view
    return view_func(*args, **kwargs)
  File "/data/haiwen/seafile-server-7.1.5/seahub/thirdpart/django/views/generic/base.py", line 68, in view
    return self.dispatch(request, *args, **kwargs)
  File "/data/haiwen/seafile-server-7.1.5/seahub/thirdpart/rest_framework/views.py", line 505, in dispatch
    response = self.handle_exception(exc)
  File "/data/haiwen/seafile-server-7.1.5/seahub/thirdpart/rest_framework/views.py", line 465, in handle_exception
    self.raise_uncaught_exception(exc)
  File "/data/haiwen/seafile-server-7.1.5/seahub/thirdpart/rest_framework/views.py", line 476, in raise_uncaught_exception
    raise exc
  File "/data/haiwen/seafile-server-7.1.5/seahub/thirdpart/rest_framework/views.py", line 502, in dispatch
    response = handler(request, *args, **kwargs)
  File "/data/haiwen/seafile-server-7.1.5/seahub/seahub/api2/endpoints/share_links.py", line 384, in post
    fs = FileShare.objects.create_file_link(username, repo_id, path,
  File "/data/haiwen/seafile-server-7.1.5/seahub/seahub/share/models.py", line 140, in create_file_link
    return self._add_file_share(username, repo_id, path, 'f', password,
  File "/data/haiwen/seafile-server-7.1.5/seahub/seahub/share/models.py", line 98, in _add_file_share
    fs = super(FileShareManager, self).create(
  File "/data/haiwen/seafile-server-7.1.5/seahub/thirdpart/django/db/models/manager.py", line 85, in manager_method
    return getattr(self.get_queryset(), name)(*args, **kwargs)
  File "/data/haiwen/seafile-server-7.1.5/seahub/thirdpart/django/db/models/query.py", line 394, in create
    obj.save(force_insert=True, using=self.db)
  File "/data/haiwen/seafile-server-7.1.5/seahub/thirdpart/django/db/models/base.py", line 807, in save
    self.save_base(using=using, force_insert=force_insert,
  File "/data/haiwen/seafile-server-7.1.5/seahub/thirdpart/django/db/models/base.py", line 838, in save_base
    updated = self._save_table(raw, cls, force_insert, force_update, using, update_fields)
  File "/data/haiwen/seafile-server-7.1.5/seahub/thirdpart/django/db/models/base.py", line 924, in _save_table
    result = self._do_insert(cls._base_manager, using, fields, update_pk, raw)
  File "/data/haiwen/seafile-server-7.1.5/seahub/thirdpart/django/db/models/base.py", line 962, in _do_insert
    return manager._insert([self], fields=fields, return_id=update_pk,
  File "/data/haiwen/seafile-server-7.1.5/seahub/thirdpart/django/db/models/manager.py", line 85, in manager_method
    return getattr(self.get_queryset(), name)(*args, **kwargs)
  File "/data/haiwen/seafile-server-7.1.5/seahub/thirdpart/django/db/models/query.py", line 1079, in _insert
    return query.get_compiler(using=using).execute_sql(return_id)
  File "/data/haiwen/seafile-server-7.1.5/seahub/thirdpart/django/db/models/sql/compiler.py", line 1112, in execute_sql
    cursor.execute(sql, params)
  File "/data/haiwen/seafile-server-7.1.5/seahub/thirdpart/django/db/backends/utils.py", line 79, in execute
    return super(CursorDebugWrapper, self).execute(sql, params)
  File "/data/haiwen/seafile-server-7.1.5/seahub/thirdpart/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
  File "/data/haiwen/seafile-server-7.1.5/seahub/thirdpart/django/db/utils.py", line 94, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "/data/haiwen/seafile-server-7.1.5/seahub/thirdpart/django/utils/six.py", line 685, in reraise
    raise value.with_traceback(tb)
  File "/data/haiwen/seafile-server-7.1.5/seahub/thirdpart/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
  File "/data/haiwen/seafile-server-7.1.5/seahub/thirdpart/django/db/backends/mysql/base.py", line 101, in execute
    return self.cursor.execute(query, args)
  File "/data/haiwen/seafile-server-7.1.5/seahub/thirdpart/pymysql/cursors.py", line 163, in execute
    result = self._query(query)
  File "/data/haiwen/seafile-server-7.1.5/seahub/thirdpart/pymysql/cursors.py", line 321, in _query
    conn.query(q)
  File "/data/haiwen/seafile-server-7.1.5/seahub/thirdpart/pymysql/connections.py", line 505, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/data/haiwen/seafile-server-7.1.5/seahub/thirdpart/pymysql/connections.py", line 724, in _read_query_result
    result.read()
  File "/data/haiwen/seafile-server-7.1.5/seahub/thirdpart/pymysql/connections.py", line 1069, in read
    first_packet = self.connection._read_packet()
  File "/data/haiwen/seafile-server-7.1.5/seahub/thirdpart/pymysql/connections.py", line 676, in _read_packet
    packet.raise_for_error()
  File "/data/haiwen/seafile-server-7.1.5/seahub/thirdpart/pymysql/protocol.py", line 223, in raise_for_error
    err.raise_mysql_exception(self._data)
  File "/data/haiwen/seafile-server-7.1.5/seahub/thirdpart/pymysql/err.py", line 107, in raise_mysql_exception
    raise errorclass(errno, errval)
django.db.utils.OperationalError: (1364, "Field 'id' doesn't have a default value")

How I can fix DB? I don’t know — what table is problem?

strannick-ru · December 30, 2020, 2:41pm

And when I was running upgrade script, I was see it:
[INFO] You are using MySQL
[INFO] updating seahub database…
[WARNING] Failed to execute sql: (1054, “Unknown column ‘key’ in ‘constance_config’”)
[WARNING] Failed to execute sql: (1061, “Duplicate key name ‘drafts_draft_origin_repo_id_8978ca2c’”)
[WARNING] Failed to execute sql: (1060, “Duplicate column name ‘headers’”)

Lonsarg · December 31, 2020, 11:00am

I gues they returned the old script for migration in official documentation that always had issues (cased by the fact that sqlite and mysql schemas are not 100% the same).

Thare are 2 ways you can fiy your problem:

Make a fresh instalation of Mysql seafile and compare database schema and manually fix (you need to have SQL command knowledge) yours so that it will match with fresh one
Migrate again using a longer procedure with alternative script i wrote here (you need to scroll down for updated download link), it was not tested on 7.1.x so no guarantees.
Migate from SQLite to MySQL - script for 6+ versions - Seafile Server - Seafile Community Forum