SQLite to MySQL script fails to create working SQL scripts

I currently have Seafile 10.0.1 on Debian 12 and with the SQLite now deprecated, I tried the script linked in the official documentation. The scripts generated what at a quick glance looked right, but the result does not import into MariaDB as expected. The script produces .sql files that need to be edited by hand so the tables are created in specific order. This does not appear to be enough, as the table custom_share_permission has an invalid create statement:

CREATE TABLE IF NOT EXISTS `custom_share_permission` (`id` integer NOT NULL PRIMARY KEY AUTO_INCREMENT AUTO_INCREMENT, `repo_id` varchar(36) NOT NULL, `name` varchar(255) NOT NULL, `description` varchar(500) NOT NULL, `permission` , `reporter` text NOT NULL) ENGINE=INNODB;

and drafts_draftreviewer table creation refers to a table that is not defined anywhere in the resulting scripts:

CREATE TABLE IF NOT EXISTS `drafts_draftreviewer` (`id` integer NOT NULL PRIMARY KEY AUTO_INCREMENT AUTO_INCREMENT, `reviewer` varchar(255) NOT NULL, `draft_id` integer NOT NULL REFERENCES `drafts_draft_old` (`id`)) ENGINE=INNODB;

I had for custom_share_permission excactly the same issue (and another issue),

so it seems a not just a one-off issue, unfortunately have not gotten answer either

1 Like

I have this problem too, and have no idea how to fix this problem

For the people who end up here from google searches:

It’d appear this was originally caused by an upgrade script, that should have either dropped this table or recreate a new one in its place.

I edited the sql dump file and renamed “drafts_draft_old” to “drafts_draft”, after which it worked.

In my case it also had several other table creations in wrong order than what was in the official migration FAQ, but those were fixed the same way as the ones listed there; just find the query that croaks, then locate the table it tries to reference and move that table’s creative above the problematic one.

In the other case where the type is missing for permission column, it should be:

permission longtext NOT NULL

Some more hints are here:

Guess we have to collect them ourselves, hope that the conversion script will be updated eventually.
But please keep trying! When you backup the sqlite db files before experimenting with the update, there is no risk involved.
I recommend to use adminer so that you can clear MariaDB easily when the import failed and you can also inspect the data there.
You can also use sqliteadmin to look into the sqlite files before the conversion takes place.
It took me about ten runs to figure out all issues, but now my seafile is running smoothly with mariadb and it is indeed faster than before :slight_smile:

files to backup before experimenting with conversions are:
ccnet folder