Upgrade from 10.0.1 to 11.0.5 - Issues with MYSQL

GITchristoph · February 25, 2024, 4:20pm

Hi

I would urgently require help, please.
I tried to upgrade from 10.0.1 to 11.0.5 and something seems to be messed up with my MYSQL now!

Thanks a lot in advance for any help.

This is what the install script plotted out:

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'")

Seahub doesn’t start

ck@Cloud ~/s/seafile-server-latest> ./seahub.sh start

LC_ALL is not set in ENV, set to en_US.UTF-8
Starting seahub at port 8000 ...
Error:Seahub failed to start.
Please try to run "./seahub.sh start" again

GITchristoph · February 25, 2024, 4:46pm

Ok, after installing (again) the python libraries from the manual

Seahub is now starting again and on a first check the sync seems to work.
Something must have gone wrong during my first installation of those libraries.

Can I please ask for advise if I should rerun the Upgrade script upgrade_10.0_11.0.sh again to fix previous installtion errors?

I really appreciate a bit of help on this, because I never had an Upgrade issue before and wouldn;t know what to do now.

At least the urgency is gone now, since the server generally seems to work again!!

mercury · February 25, 2024, 5:05pm

I’m glad you have met with some success in fixing your upgrade. Upgrade errors are always heartbreaking.

These errors are not uncommon, sadly. If you search the forum for previous samples like THIS you will get an idea of the fix.

You will need to update the databases manually. It is not difficult but you will need to use the MySQL command line interface or some other tool like MySQL Workbench or DBeaver.

You should check your databases and verify that all the fields are present and correctly defined so that moving forward there are no “holes” that might create problems in your server down the road.

Good luck and write back if you have further questions. Please document your solution here so others can benefit later.

daniel.pan · February 26, 2024, 2:44am

It is better if you can run the SQL upgrade statement manually to check what exactly causing the problem. For example, maybe your MySQL version is too old.

GITchristoph · February 26, 2024, 6:50am

Thanks @mercury

Can you help me and tell me what I should compare my fields to?

Thanks @daniel.pan

Can you please tell me the command how to do that.

daniel.pan · February 26, 2024, 8:16am

The SQL upgrade statements can be found at: seahub/scripts/upgrade/sql at master · haiwen/seahub · GitHub

The correct database tables can be found at: seahub/sql/mysql.sql at master · haiwen/seahub · GitHub

GITchristoph · February 26, 2024, 9:08am

Thank you @daniel.pan

Just trying to get this right now, I am a bit nervous

Can you double check with me that my steps below are correct?

Stop Seafile and Seahub Server ?

Execute the file seafile-server-latest/seahub/scripts/upgrade/sql/11.0.0/mysql/seahub.sql ?

I assume this will be done by doing cd seafile-server-latest/seahub/scripts/upgrade/sql/11.0.0/mysql and then typing the command ./seahub.sql ?

Is it possible to do a dry-run first?

Thank you so much for going through this with me together.

My MYSQL version is:
mysql Ver 8.0.36-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu))

mysql> SHOW databases;
+--------------------+
| Database           |
+--------------------+
| ccnet-db           |
| information_schema |
| mysql              |
| performance_schema |
| seafile-db         |
| seahub-db          |
| sys                |
+--------------------+

mercury · February 26, 2024, 5:25pm

@daniel.pan, what is the recommended procedure for handling cases where an “Unknown column” is found? For example:

[WARNING] Failed to execute sql: (1054, “Unknown column ‘domain’ in ‘org_saml_config’”)

[WARNING] Failed to execute sql: (1054, “Unknown column ‘domain_verified’ in ‘where clause’”)

mercury · February 26, 2024, 7:00pm

Are you familiar with the MySQL command line tool?

GITchristoph · February 26, 2024, 7:16pm

Hi @mercury

I did a bit of checking myself today already.

After login in to my MYSQL database I did

mysql> USE seahub-db;

and then

mysql> SHOW TABLES;

I then got a list of tables and compared this to the link from @daniel.pan :

Using the text Editor Sublime I extracted all lines from the code on that link that started with “CREATE TABLE”.

This gave me the list of required Tables and I compared them against the tables from the mysql output SHOW TABLES.

I have all TABLES that are required based on the website and then approximately 15 to 20 additional TABLES in the database seahub-db.

I hope that means that it should be ok.

I still hope that @daniel.pan will explain me how to use the seahub.sql .

I am a bit scared to run it and mess it up more than now

mercury · February 26, 2024, 7:54pm

Excellent work! You are on the right track.

I suspect that you already have all the tables that are required. Now you should use the seahub.sql file that @daniel.pan referenced and double-check that all the fields are present. For example, considering share_uploadlinkshare, do you have expire_date datetime DEFAULT NULL?

CREATE TABLE share_uploadlinkshare (
id int(11) NOT NULL AUTO_INCREMENT,
username varchar(255) NOT NULL,
repo_id varchar(36) NOT NULL,
path longtext NOT NULL,
token varchar(100) NOT NULL,
ctime datetime NOT NULL,
view_cnt int(11) NOT NULL,
password varchar(128) DEFAULT NULL,
expire_date datetime DEFAULT NULL,
PRIMARY KEY (id),
UNIQUE KEY token (token),
KEY share_uploadlinkshare_username_3203c243 (username),
KEY share_uploadlinkshare_repo_id_c519f857 (repo_id),
KEY share_uploadlinkshare_expire_date (expire_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Once you have verified that all the fields are present and correctly defined take a look at the upgrade script and see if any field(s) were modified. For example,

ALTER TABLE share_uploadlinkshare ADD INDEX IF NOT EXISTS share_uploadlinkshare_expire_date (expire_date);

The ‘IF NOT EXISTS’ clause is a constant problem for MySQL users. It might be best to check if there is an index already for the expire_date field.

SHOW INDEX FROM yourtable;
SHOW INDEX FROM share_uploadlinkshare;

There will be a table of indexed fields produced. Look for expire_date in the table.

If the field is not indexed you can add it using another command.

GITchristoph · February 26, 2024, 8:27pm

Thank you so much for taking the time and helping me!

This is the part where I am not sure what to do.
Can I run that file as a command?

Run the MySQL update again specifically?

Sorry, that’s where I am unsure what to do

mercury · February 26, 2024, 10:20pm

You have a pretty short list of Warnings so it is not a lot of work to run the lines individually and check for errors (or warnings) from the system.

It looks like all your Warnings are in the Seahub database and looking at the upgrade document for Seafile 11.0.0 (seahub.sql above) your upgrade faulted at line 100.

You probably handled the first Warning and the field ‘expire_date’ in share_uploadlinkshare is probably OK.

Next is org_saml_config. How is the field ‘domain’? Are its properties correct? Any need to change it based on the two reference documents?

ALTER TABLE org_saml_config CHANGE domain domain varchar(255) DEFAULT NULL;

By the way, you can run the command if you like. No changes affected.

mysql> ALTER TABLE org_saml_config CHANGE domain domain varchar(255) DEFAULT NULL;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0

If you try to run the offending ‘IF NOT EXISTS’ clause an error 1064 will result. No harm done though.

mysql> ALTER TABLE org_saml_config ADD COLUMN IF NOT EXISTS dns_txt varchar(64) NULL;
ERROR 1064 (42000): 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

You can run the DESCRIBE command to check the table. For example:

mysql> DESCRIBE org_saml_config;

The point is: Check the tables and fields first. You might not have to run some of the commands at all.

GITchristoph · February 27, 2024, 5:14am

Thanks again for all your help and understand it’s not much work to check the tables “by hand”. I am usually quite good in doing these things.
I managed to google my way through mysql so far, but now I don’t manage to show the fields in a table.

For example to show the fields in the table “share_uploadlinkshare” this doesn’t work:

mysql> show share_uploadlinkshare;

How can I show a table content in a terminal window?

(I appreciate this is not Seafile realted issue )

This sounds great, but what I don’t understand is what command to run and how I can run it. So sorry, I feel a bit stupid here.

Thanks again for going through this with me.

mercury · February 27, 2024, 6:31am

Try ‘DESCRIBE table_name;’ instead. For example:

mysql> describe share_uploadlinkshare;
±------------±-------------±-----±----±--------±---------------+
| Field | Type | Null | Key | Default | Extra |
±------------±-------------±-----±----±--------±---------------+
| id | int | NO | PRI | NULL | auto_increment |
| username | varchar(255) | NO | MUL | NULL | |
| repo_id | varchar(36) | NO | MUL | NULL | |
| path | longtext | NO | | NULL | |
| token | varchar(100) | NO | UNI | NULL | |
| ctime | datetime | NO | | NULL | |
| view_cnt | int | NO | | NULL | |
| password | varchar(128) | YES | | NULL | |
| expire_date | datetime | YES | MUL | NULL | |
±------------±-------------±-----±----±--------±---------------+
9 rows in set (0.01 sec)

If you look at the seahub.sql upgrade script for 11.0.0. that Daniel recommended, and go to line 100, I think you will find the first command that tripped you up.

ALTER TABLE share_uploadlinkshare ADD INDEX IF NOT EXISTS share_uploadlinkshare_expire_date (expire_date);

That is a basic SQL command that adds an index to the field expire_date in the table share_uploadlinkshare. If you copy that and paste it into the MySQL command line it will fail because of the dreaded ‘IF NOT EXISTS’ clause. Instead you could enter it without the clause or inspect the field for an index first (see SHOW INDEX FROM, above) and maybe you will find that it is already there and don’t have any work to do.

I think you can step through all the warnings this way and determine whether you need to run the rest of the commands in the seahub.sql upgrade file.

Does that make sense?

GITchristoph · February 27, 2024, 6:58am

Yes!

Thank you, now I understand.

And the DESCRIBE command is what I couldn’t find myself.

Thanks again - I will work my way through this

GITchristoph · February 27, 2024, 7:17am

Thank you - I can see now that “share_uploadlinkshare_expire_date” is missing in the Index.

So this should have been triggered by the command:

ALTER TABLE share_uploadlinkshare ADD INDEX IF NOT EXISTS share_uploadlinkshare_expire_date (expire_date );

So now I (EDIT:) ~~should~~ did run the command like this (without the “IF NOT EXISTS”)? :

ALTER TABLE share_uploadlinkshare ADD INDEX share_uploadlinkshare_expire_date (expire_date);

And that added the index line! (Thank you!)

Below the output of my current database.

mysql> describe share_uploadlinkshare;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int          | NO   | PRI | NULL    | auto_increment |
| username    | varchar(255) | NO   | MUL | NULL    |                |
| repo_id     | varchar(36)  | NO   | MUL | NULL    |                |
| path        | longtext     | NO   |     | NULL    |                |
| token       | varchar(100) | NO   | UNI | NULL    |                |
| ctime       | datetime     | NO   |     | NULL    |                |
| view_cnt    | int          | NO   |     | NULL    |                |
| password    | varchar(128) | YES  |     | NULL    |                |
| expire_date | datetime     | YES  |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)

mysql> SHOW INDEX FROM share_uploadlinkshare;
+-----------------------+------------+-----------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table                 | Non_unique | Key_name                                | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-----------------------+------------+-----------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| share_uploadlinkshare |          0 | PRIMARY                                 |            1 | id          | A         |           2 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| share_uploadlinkshare |          0 | token                                   |            1 | token       | A         |           2 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| share_uploadlinkshare |          1 | share_uploadlinkshare_username_3203c243 |            1 | username    | A         |           1 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| share_uploadlinkshare |          1 | share_uploadlinkshare_repo_id_c519f857  |            1 | repo_id     | A         |           2 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| share_uploadlinkshare |          1 | share_uploadlinkshare_expire_date       |            1 | expire_date | A         |           1 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-----------------------+------------+-----------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

GITchristoph · February 27, 2024, 9:59am

Yes!

I checked org_saml_config

mysql> describe org_saml_config;
+--------------+----------+------+-----+---------+----------------+
| Field        | Type     | Null | Key | Default | Extra          |
+--------------+----------+------+-----+---------+----------------+
| id           | int      | NO   | PRI | NULL    | auto_increment |
| org_id       | int      | NO   | UNI | NULL    |                |
| metadata_url | longtext | NO   |     | NULL    |                |
+--------------+----------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

That’s less fields compared to the script:

REATE TABLE `org_saml_config` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `org_id` int(11) NOT NULL,
  `metadata_url` longtext NOT NULL,
  `domain` varchar(255) DEFAULT NULL,
  `dns_txt` varchar(64) DEFAULT NULL,
  `domain_verified` tinyint(1) NOT NULL DEFAULT 0,
  `idp_certificate` longtext DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `org_id` (`org_id`),
  UNIQUE KEY `domain` (`domain`),
  KEY `org_saml_config_domain_verified_398065b9` (`domain_verified`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I tried to run the command and I got an error

mysql> ALTER TABLE org_saml_config CHANGE domain domain varchar(255) DEFAULT NULL;

ERROR 1054 (42S22): Unknown column ‘domain’ in ‘org_saml_config’

Now my MYSQL knowledge stops again.
I think I need to add these missing fields

domain
dns_txt
domain_verfified
org_saml_config_domain_verified_398065b9
idp_certificate

This also clauses pretty much all the Warnings from the Upgrade.

Not sure what to do now, but I need to go working now.

mercury · February 27, 2024, 4:42pm

The 1054 error confuses me but I sniffed around a bit and came up with this:

Single quotes missing while inserting varchar value.

It’s worth a try. I asked @daniel.pan earlier about it. Maybe he will get back with a better solution. In the meantime here is a general reference on error 1054 if you care to take a look.

It looks to me that you have two options at this point: 1) Enter the missing fields individually; or 2), delete the existing table and create a new one using the CREATE TABLE we have from the docs.

By the way, since the subject has not come up yet, have you done a backup of your database? The subject is covered in the Admin Manual.

mysqldump -h [mysqlhost] -u[username] -p[password] --opt ccnet_db > /backup/databases/ccnet-db.sql.date +"%Y-%m-%d-%H-%M-%S"

mysqldump -h [mysqlhost] -u[username] -p[password] --opt seafile_db > /backup/databases/seafile-db.sql.date +"%Y-%m-%d-%H-%M-%S"

mysqldump -h [mysqlhost] -u[username] -p[password] --opt seahub_db > /backup/databases/seahub-db.sql.date +"%Y-%m-%d-%H-%M-%S"

GITchristoph · March 4, 2024, 9:27am

Hello @mercury

I am sorry I didn’t answer yet again.
I didn’t manage to find the time to get back to this topic, yet.
But I will (have to) and really appreciate all your support!