sqlite2mysql.sh script generates broken SQL queries

[sandnabba]

Hello!

I'm on a locally installed Seafile 8.0.8 server, and I'm trying to migrate the SQLite DB to MySQL so that I can start using the Docker version of seafile server (which seem to require MySQL).

However, the generated SQL files does not seem to work. Getting this on the seahub-db.sql:

$> mysql -u emil -p seahub_db < seahub-db.sql 
Enter password: 
ERROR 1064 (42000) at line 2: 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 'NOT EXISTS `django_migrations` (`id` integer NOT NULL PRIMARY KEY AUTO_INCREMENT' at line 1

Generating the files seemed to work fine:

$> ./sqlite2mysql.sh 
sqlite3 /home/seafile/ccnet/PeerMgr/usermgr.db .dump | python sqlite2mysql.py > ccnet-db.sql
sqlite3 /home/seafile/ccnet/GroupMgr/groupmgr.db .dump | python sqlite2mysql.py >> ccnet-db.sql
sqlite3 /home/seafile/seafile-data/seafile.db .dump | python sqlite2mysql.py > seafile-db.sql
sqlite3 /home/seafile/seahub.db .dump | tr -d '
' | sed 's/;/;
/g' | python sqlite2mysql.py > seahub-db.sql

I'm on an updated Ubuntu 20.04.5 LTS.

[rea-ducks]

Because in Seafile 11, useage of SQlite will be deprecated, I'm trying to move from SQlite to MySQL according to https://manual.seafile.com/deploy/migrate_from_sqlite_to_mysql/
I'm on Seafile 10.0.1 on Debian running MariaDB 15.

Also for me generating was done without problem, applying seahub-db.sql fails me with several errors. I added the corresponding lines that were generated:

372 DROP TABLE IF EXISTS `auth_group_permissions`;
373 CREATE TABLE IF NOT EXISTS `auth_group_permissions` (`id` integer NOT NULL PRIMARY KEY AUTO_INCREMENT AUTO_INCREMENT, `group_id` integer NOT NULL REFERENCES `auth_group` (`id`), `permission_id` integer NOT NULL REFERENCES `auth_permission` (`id`)) ENGINE=INNODB;
 
ERROR 1005 (HY000) at line 373 in file: 'seahub-db.sql': Can't create table `seahub_db`.`auth_group_permissions` (errno: 150 "Foreign key constraint is incorrectly formed")


375 DROP TABLE IF EXISTS `auth_user_groups`;
376 CREATE TABLE IF NOT EXISTS `auth_user_groups` (`id` integer NOT NULL PRIMARY KEY AUTO_INCREMENT AUTO_INCREMENT, `user_id` integer NOT NULL REFERENCES `auth_user` (`id`), `group_id` integer NOT NULL REFERENCES `auth_group` (`id`)) ENGINE=INNODB;

ERROR 1005 (HY000) at line 376 in file: 'seahub-db.sql': Can't create table `seahub_db`.`auth_user_groups` (errno: 150 "Foreign key constraint is incorrectly formed")

 
378 DROP TABLE IF EXISTS `auth_user_user_permissions`;
379 CREATE TABLE IF NOT EXISTS `auth_user_user_permissions` (`id` integer NOT NULL PRIMARY KEY AUTO_INCREMENT AUTO_INCREMENT, `user_id` integer NOT NULL REFERENCES `auth_user` (`id`), `permission_id` integer NOT NULL REFERENCES `auth_permission` (`id`)) ENGINE=INNODB;

ERROR 1005 (HY000) at line 379 in file: 'seahub-db.sql': Can't create table `seahub_db`.`auth_user_user_permissions` (errno: 150 "Foreign key constraint is incorrectly formed")


1006 DROP TABLE IF EXISTS `post_office_attachment_emails`;
1007 CREATE TABLE IF NOT EXISTS `post_office_attachment_emails` (`id` integer NOT NULL PRIMARY KEY AUTO_INCREMENT AUTO_INCREMENT, `attachment_id` integer NOT NULL REFERENCES `post_office_attachment` (`id`), `email_id` integer NOT NULL REFERENCES `post_office_email` (`id`)) ENGINE=INNODB;

ERROR 1005 (HY000) at line 1007 in file: 'seahub-db.sql': Can't create table `seahub_db`.`post_office_attachment_emails` (errno: 150 "Foreign key constraint is incorrectly formed")


1009 DROP TABLE IF EXISTS `post_office_email`;
1010 CREATE TABLE IF NOT EXISTS `post_office_email` (`id` integer NOT NULL PRIMARY KEY AUTO_INCREMENT AUTO_INCREMENT, `from_email` varchar(254) NOT NULL, `to` text NOT NULL, `cc` text NOT NULL, `bcc` text NOT NULL, `subject` varchar(989) NOT NULL, `message` text NOT NULL, `html_message` text NOT NULL, `status` smallint unsigned NULL, `priority` smallint unsigned NULL, `created` datetime NOT NULL, `last_updated` datetime NOT NULL, `scheduled_time` datetime NULL, `headers` text NULL, `context` text NULL, `backend_alias` varchar(64) NOT NULL, `template_id` integer NULL REFERENCES `post_office_emailtemplate` (`id`)) ENGINE=INNODB;

ERROR 1005 (HY000) at line 1010 in file: 'seahub-db.sql': Can't create table `seahub_db`.`post_office_email` (errno: 150 "Foreign key constraint is incorrectly formed")


1015 DROP TABLE IF EXISTS `post_office_log`;
1016 CREATE TABLE IF NOT EXISTS `post_office_log` (`id` integer NOT NULL PRIMARY KEY AUTO_INCREMENT AUTO_INCREMENT, `date` datetime NOT NULL, `exception_type` varchar(255) NOT NULL, `message` text NOT NULL, `email_id` integer NOT NULL REFERENCES `post_office_email` (`id`), `status` smallint unsigned NOT NULL) ENGINE=INNODB;

ERROR 1005 (HY000) at line 1016 in file: 'seahub-db.sql': Can't create table `seahub_db`.`post_office_log` (errno: 150 "Foreign key constraint is incorrectly formed")


DROP TABLE IF EXISTS `custom_share_permission`;
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;

ERROR 1064 (42000) at line 1842 in file: 'seahub-db.sql': You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ' `reporter` text NOT NULL) ENGINE=INNODB' at line 1

[freeplant]

We will check the issue.

[imwhatiam]

ERROR 1005 (HY000) at line 373 in file: 'seahub-db.sql': Can't create table seahub_db.auth_group_permissions (errno: 150 "Foreign key constraint is incorrectly formed")

Hello, after some debugging, I found that this error (errno: 150 "Foreign key constraint is incorrectly formed") may be caused by the table creation sequence.

For example, the auth_group_permissions table has foreign keys.

CONSTRAINT `auth_group_permissio_permission_id_84c5c92e_fk_auth_perm` FOREIGN KEY (`permission_id`) REFERENCES `auth_permission` (`id`),
CONSTRAINT `auth_group_permissions_group_id_b120cbf9_fk_auth_group_id` FOREIGN KEY (`group_id`) REFERENCES `auth_group` (`id`)

When creating the auth_group_permissions table, the tables (auth_permission and auth_group) corresponding to these foreign keys need to be created beforehand, otherwise an error will occur.

So please check your SQL file first to ensure that the creation statement for the auth_group_permissions table comes before auth_permission and auth_group.

If indeed this is the issue, then we will work on updating the migration document from SQLite to MySQL, providing more points of attention to avoid such errors from happening again.

[rea-ducks]

Alright, after a bit of shuffling, the import now worked.

So the order is now

auth_user
auth_group
auth_permission
auth_group_permissions
auth_user_groups
auth_user_user_permissions

and

post_office_emailtemplate
post_office_email
post_office_attachment
post_office_attachment_emails

Also I modified the following CREATE, since the type of the "permission" field wasn't given in the original 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` longtext NOT NULL, `reporter` text NOT NULL) ENGINE=INNODB;

But that work shouldn't be necessary to be done by every user. Instead I expect that running "sqlite2mysql.sh" will spit out working SQL files.

Please also see to mentioning in the migration document, the admin should create a separate user for accessing the three databases like so:

create user 'seafile'@'localhost' identified by 'seafile_pw';

GRANT ALL PRIVILEGES ON `ccnet_db`.* to `seafile`@localhost;
GRANT ALL PRIVILEGES ON `seafile_db`.* to `seafile`@localhost;
GRANT ALL PRIVILEGES ON `seahub_db`.* to `seafile`@localhost;

[imwhatiam]

Alright, after a bit of shuffling, the import now worked.

Thank you for your testing and feedback. We will update the documentation shortly.

[freeplant]

The script should have been fixed already.