MySQL errors after upgrade from 6.3 to 7.0

The webinterface and the sync of the clients is working fine. Browsing in the seafile client does not word, neither does the android app.

I see this error:

ProgrammingError: (1146, “Table ‘seahub-db.file_tags_filetags’ doesn’t exist”)
2019-06-26 16:56:30,047 [ERROR] django.request:135 handle_uncaught_exception Internal Server Error: /api2/repos/my-repo-uuid/dir/
Traceback (most recent call last):
File “/opt/seafile/seafile-server-7.0.2/seahub/thirdpart/django/core/handlers/exception.py”, line 41, in inner
response = get_response(request)
File “/opt/seafile/seafile-server-7.0.2/seahub/thirdpart/django/core/handlers/base.py”, line 249, in _legacy_get_response
response = self._get_response(request)

Looks like I’m missing a table. I had no errors in the update though. Creating the table manually (Code from here: https://github.com/haiwen/seahub/blob/master/sql/mysql.sql) does not work either. And I don’t understand why:

mysql> CREATE TABLE file_tags_filetags (
-> id int(11) NOT NULL AUTO_INCREMENT,
-> file_uuid_id char(32) NOT NULL,
-> repo_tag_id int(11) NOT NULL,
-> PRIMARY KEY (id),
-> KEY file_tags_filetags_file_uuid_id_e30f0ec8_fk_tags_file (file_uuid_id),
-> KEY file_tags_filetags_repo_tag_id_c39660cb_fk_repo_tags_repotags_id (repo_tag_id),
-> CONSTRAINT file_tags_filetags_file_uuid_id_e30f0ec8_fk_tags_file FOREIGN KEY (file_uuid_id) REFERENCES tags_fileuuidmap (uuid),
-> CONSTRAINT file_tags_filetags_repo_tag_id_c39660cb_fk_repo_tags_repotags_id FOREIGN KEY (repo_tag_id) REFERENCES repo_tags_repotags (id)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ERROR 3780 (HY000): Referencing column ‘file_uuid_id’ and referenced column ‘uuid’ in foreign key constraint ‘file_tags_filetags_file_uuid_id_e30f0ec8_fk_tags_file’ are incompatible.

Here is the referenced table:

mysql> describe tags_fileuuidmap;
+-------------------------+---------------+------+-----+---------+-------+
| Field                   | Type          | Null | Key | Default | Extra |
+-------------------------+---------------+------+-----+---------+-------+
| uuid                    | char(32)      | NO   | PRI | NULL    |       |
| repo_id                 | varchar(36)   | NO   | MUL | NULL    |       |
| repo_id_parent_path_md5 | varchar(100)  | NO   | MUL | NULL    |       |
| parent_path             | longtext      | NO   |     | NULL    |       |
| filename                | varchar(1024) | NO   |     | NULL    |       |
| is_dir                  | tinyint(1)    | NO   |     | NULL    |       |
+-------------------------+---------------+------+-----+---------+-------+
6 rows in set (0.01 sec)

Type is char(32) in both cases!?

@daniel.pan: Any ideas? Any help is much appreciated!

Hi there, grasshide!

Could you please check out the engines and the charsets used by both tables?

You can do so by executing SHOW CREATE TABLE [tablename]; in your mysql CLI.

EDIT: Please also execute SHOW FULL COLUMNS FROM [tablename] for both tables;

As I’m unable to create the missing table I can only describe the existing one:

SHOW CREATE TABLE tags_fileuuidmap;
±-----------------±----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
±-----------------±----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tags_fileuuidmap | CREATE TABLE tags_fileuuidmap (
uuid char(32) COLLATE utf8mb4_unicode_ci NOT NULL,
repo_id varchar(36) COLLATE utf8mb4_unicode_ci NOT NULL,
repo_id_parent_path_md5 varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
parent_path longtext COLLATE utf8mb4_unicode_ci NOT NULL,
filename varchar(1024) COLLATE utf8mb4_unicode_ci NOT NULL,
is_dir tinyint(1) NOT NULL,
PRIMARY KEY (uuid),
KEY tags_fileuuidmap_9a8c79bf (repo_id),
KEY tags_fileuuidmap_c5bf47d4 (repo_id_parent_path_md5)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
±-----------------±----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

SHOW FULL COLUMNS FROM tags_fileuuidmap;
±------------------------±--------------±-------------------±-----±----±--------±------±--------------------------------±--------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
±------------------------±--------------±-------------------±-----±----±--------±------±--------------------------------±--------+
| uuid | char(32) | utf8mb4_unicode_ci | NO | PRI | NULL | | select,insert,update,references | |
| repo_id | varchar(36) | utf8mb4_unicode_ci | NO | MUL | NULL | | select,insert,update,references | |
| repo_id_parent_path_md5 | varchar(100) | utf8mb4_unicode_ci | NO | MUL | NULL | | select,insert,update,references | |
| parent_path | longtext | utf8mb4_unicode_ci | NO | | NULL | | select,insert,update,references | |
| filename | varchar(1024) | utf8mb4_unicode_ci | NO | | NULL | | select,insert,update,references | |
| is_dir | tinyint(1) | NULL | NO | | NULL | | select,insert,update,references | |
±------------------------±--------------±-------------------±-----±----±--------±------±--------------------------------±--------+
6 rows in set (0.01 sec)

I guess I have a mix of utf8mb4_unicode_ci and utf8 for whatever reason.
What is the best fix for my problem? Creating the missing table in utf8mb4 would lead to the same problems with the next db update. So how do I get from utf8mb4 to the standard utf8 used by seafile? And are there any risks of loosing data?

Thanks for the help btw. :slight_smile:

Like this?
ALTER DATABASE seafile-db CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER DATABASE seahub-db CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Since I got no errors during the upgrade from 6.3 to 7.0 … how do I find out what other databases are missing? should I run the mysql upgrade again? Or does that result in disaster?

As always, it depends on various things :wink:

Let’s assume, that all your tables are using the utf8mb4_unicode_ci collation and utf8mb4 as default charset.
Is that true?
(My first question to you/the developers would be “why?”, because the install scripts use UTF8 as default)

Your question implies that your seafile instance worked before the update?
If so and if all other tables in your database tables uses these parameters, I would try to create the missing table in your seahub-db with these parameters, too:

CREATE TABLE `file_tags_filetags` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `file_uuid_id` char(32) NOT NULL,
  `repo_tag_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `file_tags_filetags_file_uuid_id_e30f0ec8_fk_tags_file` (`file_uuid_id`),
  KEY `file_tags_filetags_repo_tag_id_c39660cb_fk_repo_tags_repotags_id` (`repo_tag_id`),
  CONSTRAINT `file_tags_filetags_file_uuid_id_e30f0ec8_fk_tags_file` FOREIGN KEY (`file_uuid_id`) REFERENCES `tags_fileuuidmap` (`uuid`),
  CONSTRAINT `file_tags_filetags_repo_tag_id_c39660cb_fk_repo_tags_repotags_id` FOREIGN KEY (`repo_tag_id`) REFERENCES `repo_tags_repotags` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

But, keep in mind, that this problem will most likely appear again with any minor/major Seafile update adding additional tables to your DB, because they always use other settings. This will even be the case when you altered your Database-Setting the way you noted.
(ALTER DATABASE yourdatabasename CHARACTER SET utf8 COLLATE utf8_unicode_ci)
This will only set the default when creating new tables WITHOUT further parameters in the SQL-statement. But, as you can see in their file on GitHub, they ALWAYS use these additional parameters. And this would override your Database-level setting.

Let’s hope that this was the issue, because reference errors in MySQL can be very frustrating :wink:

I don’t think that whole databases are missing.
Maybe there are some tables missing.
You will surely notice. Either in unexpected behaviour or in your logs.
But be aware! Log file entries are not always a real error. Some tables are not needed in community edition!
So, just keep an eye on your app.

Any success?

Yes, kinda…

I went with your proposal to add COLLATE=utf8mb4_unicode_ci when creating the table file_tags_filetags. That worked. Seafile worked as expected after that.

So I made a backup of this db version. I currently trying to go back to the seafile utf8 standard. I used phpmyadmin to convert databases and columns to COLLATE=utf8_unicode_ci. I had to drop two databases (tags_filetag and base_filecomment) because of foreign key problems. They’ve been empty anyways so I thought no problem. But as I try to create them I get the error I got before:

show create table tags_fileuuidmap;
±-----------------±------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
±-----------------±------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tags_fileuuidmap | CREATE TABLE tags_fileuuidmap (
uuid char(32) COLLATE utf8_unicode_ci NOT NULL,
repo_id varchar(36) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
repo_id_parent_path_md5 varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
parent_path longtext CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
filename varchar(1024) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
is_dir tinyint(1) NOT NULL,
PRIMARY KEY (uuid),
KEY tags_fileuuidmap_9a8c79bf (repo_id),
KEY tags_fileuuidmap_c5bf47d4 (repo_id_parent_path_md5)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=COMPACT |
±-----------------±------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

CREATE TABLE file_tags_filetags (
-> id int(11) NOT NULL AUTO_INCREMENT,
-> file_uuid_id char(32) NOT NULL,
-> repo_tag_id int(11) NOT NULL,
-> PRIMARY KEY (id),
-> KEY file_tags_filetags_file_uuid_id_e30f0ec8_fk_tags_file (file_uuid_id),
-> KEY file_tags_filetags_repo_tag_id_c39660cb_fk_repo_tags_repotags_id (repo_tag_id),
-> CONSTRAINT file_tags_filetags_file_uuid_id_e30f0ec8_fk_tags_file FOREIGN KEY (file_uuid_id) REFERENCES tags_fileuuidmap (uuid),
-> CONSTRAINT file_tags_filetags_repo_tag_id_c39660cb_fk_repo_tags_repotags_id FOREIGN KEY (repo_tag_id) REFERENCES repo_tags_repotags (id)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ERROR 3780 (HY000): Referencing column ‘file_uuid_id’ and referenced column ‘uuid’ in foreign key constraint ‘file_tags_filetags_file_uuid_id_e30f0ec8_fk_tags_file’ are incompatible.

What else do I have to change on my databases so that they are back to “normal”?

Removing the last part of the DB statement “DEFAULT CHARSET=utf8;” creates the tables without error. But of course with the next db-update I’ll run into trouble again. :frowning:

  1. So, at least Your Seafile instance is up and running now?
  2. Regarding trouble @ next update:
    You could try to backup your whole DB and then try to convert them all via script or PHPMyAdmin.
    But be aware! I am currently not sure, how the different implementations of this charset affect your table’s contents! I once transformed a database the other way and had to take care of the maximum length of columns and index keys before converting.
    Maybe you are lucky, and new tables are not frequently implemented in Seafile updates/upgrades. :wink:

You could also export your current databases (Dbs, tables and content) after stopping seafile and seahub via PHPMyAdmin and edit the resulting SQL statements to your needs and then create a new DB with file.