Upgrade from 10.0.1 to 11.0.5 - Issues with MYSQL

Seafile Server
21

I can tell you where that 1054 comes from…

(all related to the MySQL scripts of the Community Edition only, leaving out the beta versions!)

The table org_saml_config was newly introduced with the 10.x version.

seafile-server-10.0.1/upgrade/sql/10.0.0/mysql/seahub.sql (the sql upgrade statements for v10 contained with the 10.0.1 package) contains this:

CREATE TABLE IF NOT EXISTS `org_saml_config` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`org_id` int(11) NOT NULL,
`metadata_url` longtext NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `org_id` (`org_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

seafile-server-11.0.3/upgrade/sql/10.0.0/mysql/seahub.sql (the sql upgrade statements for v10 contained with the 11.0.3 package; also identical for 11.0.4 and 11.0.5) contains this:

CREATE TABLE IF NOT EXISTS `org_saml_config` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`org_id` int(11) NOT NULL,
`metadata_url` longtext NOT NULL,
`domain` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `org_id` (`org_id`),
UNIQUE KEY `domain` (`domain`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

This means that any operator who upgraded from 9.x (or earlier) to 10.0.1 (or did an initial install on 10.0.1) will have that table WITHOUT the domain column.
Whereas any operator upgrading from 9.x (or earlier) to 11.0.x will have that table WITH the domain column.

seafile-server-11.0.3/upgrade/sql/11.0.0/mysql/seahub.sql (the sql upgrade statements for v11 contained with the 11.0.3 package; also identical for 11.0.4 and 11.0.5) contains this:

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

This assumes that the domain column is already present, which is NOT the case for operators updating from 10.0.1 - all of them will fall into this pit!

Only solution for this is to manually create the column by

ALTER TABLE `org_saml_config` ADD COLUMN `domain` varchar(255) NOT NULL;

and then repeating

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

Regarding the IF NOT EXISTS issue: I remember to have read somewhere that the original MySQL does not support this everywhere for DDL statements, whereas the MariaDB fork supports this for more statements.
I assume that the SeaFile developers only use and test their software on MariaDB.

Sorry for not putting the SQL statements into code blocks, but I’m failing to get the formatting for the apostrophes correct inside this answer form…

4 Likes
22

Thank you for posting. Makes perfect sense.

1 Like
24

Hi @mercury

I am trying out the mysql dumb command and am struggling with it.

Appearently Seafile installer names the databases seafile-db and NOT seafile_db, which should be corrected in the manual, I think. It’s a bit confusing :slight_smile:

ck@server ~/seafile> mysqldump -u root -p --opt --opt seafile-db > backup/databases/seafile-db.sql.date +"%Y-%m-%d-%H-%M-%S"
Enter password:
mysqldump: Couldn’t find table: “+%Y-%m-%d-%H-%M-%S`”

There seems to be another iiregularity in the template and I ca’;t get it solved to create files with the automatic date in the name?

25

Be careful with the quotation marks. The sample in the Manual is:

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

Notice the two sets of quotation marks, one set of back-ticks around date and one set of double-quotes around the timestamp.

For example, for seahub-db:

mysqldump -h localhost -u$DB_USER --opt seahub-db > $DB_BACKUP/seahub-db.sql.date +"%Y-%m-%d-%H-%M-%S"

You need to specify $DB_USER and $DB_BACKUP for your particular situation.

Does this help?

1 Like
26

Wooops, looking over this issue once again, I also noticed that I missed the unique key defined from the v10 script of the 11.0.3 package…

So… This statement also needs to be done manually:

ALTER TABLE `org_saml_config` ADD UNIQUE KEY `domain` (`domain`);

3 Likes
Seafile CE 11.0.5 Upgrade - MySQL Warnings - Syntax Error
28

Thanks @Nightshade and @mercury

I will try and get back on this today

29

I ran partially into the same issue today during upgrade. I ran mariab 10.6 before the upgrade and upgrade to 10.11

I am running the docker version of seafile CE.

the upgrade script gave the following warning:

[WARNING] Failed to execute sql: (1054, "Unknown column 'domain' in 'org_saml_config'")

The update still seems to have completely (partially?) successfully …

help would be greatly appreciated.

1 Like
30

Sorry it took so long to come back to this and thanks again for all the answers

On a side note, this automatic date naming doesn’t work for me.

If I use the exact same line as below:

mysqldump -u root -p --opt ccnet-db > backup/databases/ccnet-db.sql.date +“%Y-%m-%d-%H-%M-%S”

I get this result

mysqldump  -u root -p --opt ccnet-db > backup/databases/ccnet-db.sql.date+"%Y-%m-%d-%H-%M-%S"
Enter password: *****
ls backup/databases/
ccnet-db.sql.date+%Y-%m-%d-%H-%M-%S

And when I delete the whitespace behind the “date”, then I get this:

mysqldump  -u root -p --opt ccnet-db > backup/databases/ccnet-db.sql.date +"%Y-%m-%d-%H-%M-%S"
Enter password: ****
mysqldump: Couldn't find table: "+%Y-%m-%d-%H-%M-%S"
ls  backup/databases/
ccnet-db.sql.date

But in any case, it seems that the mysql dumb worked and I backed up my databses. :+1:

I now have:

ccnet-db.sql.bck
seafile-db.sql.bck
seahub-db.sql.bck

I will now go into the topic of fixing the MSQL Databases now and will read the relevant post.

31

Thanks @Nightshade , this does make perfect sense as @mercury already said.
And really really thanks again for all your help.

I would like to add the missing fields individually. It feels safer from my perspective :slight_smile:

As I checked above, I am missing the following fields within ‘org_saml_config’

domain
dns_txt
domain_verfified
org_saml_config_domain_verified_398065b9
idp_certificate

I would like to understand the exact code that I need to type now, that would be really helpful if you could help me there.
This can then also be a blueprint for others who might run into the same problem.

This is the code block that I need to relate to, copy and paste from this link.

CREATE 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;

So, for adding the field domain I think it should be done as below?

ALTER TABLE `org_saml_config` ADD COLUMN `domain` varchar(255) DEFAULT NULL;
ALTER TABLE `org_saml_config` ADD UNIQUE KEY `domain` (`domain`);

How do I add the other fields? Is it as below?

For dns-txt:

ALTER TABLE `org_saml_config` ADD COLUMN `dns_txt` varchar(64) DEFAULT NULL;

For domain_verified:

ALTER TABLE `org_saml_config` ADD COLUMN `domain_verified` tinyint(1) NOT NULL DEFAULT 0;

For idp_certificate:

ALTER TABLE `org_saml_config` ADD COLUMN `idp_certificate` longtext DEFAULT NULL;

For org_saml_config_domain_verified_398065b9 I don’t know what to do?

Thanks again in advance for all your help.

32

Yes, the statements that you picked are correct.

As for the org_saml_config_domain_verified_398065b9:

ALTER TABLE `org_saml_config` ADD KEY `org_saml_config_domain_verified_398065b9` (`domain_verified`);

Generally, if you would try to add a column or key that already exists with the same name, the MySQL server will simply return an error without changing anything - so you cannot really break your database (at least not with the above commands :slight_smile:)

1 Like
33

Thanks so much!

I will do this now and see what will happen :slight_smile:

34

Thanks so so much again.
I did all the changes to the table and it all went smooth.
Thanks for giving me the confidence to do this.

Could I ask if someone can send me the output of the org_saml_config, so I can compare my results?

> DESCRIBE org_saml_config;

# Note: that's the new output:
+-----------------+--------------+------+-----+---------+----------------+
| Field           | Type         | Null | Key | Default | Extra          |
+-----------------+--------------+------+-----+---------+----------------+
| id              | int          | NO   | PRI | NULL    | auto_increment |
| org_id          | int          | NO   | UNI | NULL    |                |
| metadata_url    | longtext     | NO   |     | NULL    |                |
| domain          | varchar(255) | YES  | UNI | NULL    |                |
| dns_txt         | varchar(64)  | YES  |     | NULL    |                |
| domain_verified | tinyint(1)   | NO   | MUL | 0       |                |
| idp_certificate | longtext     | YES  |     | NULL    |                |
+-----------------+--------------+------+-----+---------+----------------+
35

This is what I have:

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    |                |
| domain          | varchar(255) | YES  | UNI | NULL    |                |
| dns_txt         | varchar(64)  | YES  |     | NULL    |                |
| domain_verified | tinyint(1)   | NO   | MUL | 0       |                |
| idp_certificate | longtext     | YES  |     | NULL    |                |
+-----------------+--------------+------+-----+---------+----------------+
1 Like
36

Perfect, thank you!

All should be good then.

I will summarise the whole thread tomorrow and the steps I did to fix the issue and will mark it as “Solution”, if that’s ok for everybody.

I will highlight of course that it wasn’t me who solved it.

Thanks again!!

37

To be able solve this issue, the following steps had to be taken.
I am summarising this to be able to give this thread a “solution”.

Of course it wasn’t me though who solved this issue but it was @mercury , @nightshade and @daniel.pan .

Thanks a lot for your super nice and outstandig support !!!

For other users, please read all the details in the comments above. I learned a lot during this process.

What solved my issue was doing the steps below:

SSH into Seafile Server and backup the databases as explained above

Then:

mysql -u root -p #Note: enter MYSQL with your user (doesn’t need to be root) password

Note: now the command line is within MYSQL

USE seahub-db; #Note: this selects the seahub-db database

SHOW TABLES; #Note: this should show the org_saml_config table

DESCRIBE org_saml_config;

Note: that’s the output:

±-------------±---------±-----±----±--------±---------------+
| Field | Type | Null | Key | Default | Extra |
±-------------±---------±-----±----±--------±---------------+
| id | int | NO | PRI | NULL | auto_increment |
| org_id | int | NO | UNI | NULL | |
| metadata_url | longtext | NO | | NULL | |
±-------------±---------±-----±----±--------±---------------+

Note: Compared to the linked source code, the following fields should be there:

CREATE 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;

Note: So the following fields are missing:

  • domain
  • dns_txt
  • domain_verfified
  • org_saml_config_domain_verified_398065b9
  • idp_certificate

Note: They will be added with the following commands now:

ALTER TABLE `org_saml_config` ADD COLUMN `domain` varchar(255) DEFAULT NULL;

ALTER TABLE `org_saml_config` ADD UNIQUE KEY `domain` (`domain`);

ALTER TABLE `org_saml_config` ADD COLUMN `dns_txt` varchar(64) DEFAULT NULL;

ALTER TABLE `org_saml_config` ADD COLUMN `domain_verified` tinyint(1) NOT NULL DEFAULT 0;

ALTER TABLE `org_saml_config` ADD COLUMN `idp_certificate` longtext DEFAULT NULL;

ALTER TABLE `org_saml_config` ADD KEY `org_saml_config_domain_verified_398065b9` (`domain_verified`);

Note: Checking the updated table

DESCRIBE org_saml_config;

Note: that’s the new output:

±----------------±-------------±-----±----±--------±---------------+
| Field | Type | Null | Key | Default | Extra |
±----------------±-------------±-----±----±--------±---------------+
| id | int | NO | PRI | NULL | auto_increment |
| org_id | int | NO | UNI | NULL | |
| metadata_url | longtext | NO | | NULL | |
| domain | varchar(255) | YES | UNI | NULL | |
| dns_txt | varchar(64) | YES | | NULL | |
| domain_verified | tinyint(1) | NO | MUL | 0 | |
| idp_certificate | longtext | YES | | NULL | |
±----------------±-------------±-----±----±--------±---------------+

Note: Then I also had to add one more line based on my error message

ALTER TABLE `share_uploadlinkshare` ADD INDEX `share_uploadlinkshare_expire_date` (`expire_date` );

Note: Quit mysql with CTRL-D

Note: I rebooted the server - That was it

2 Likes