[SOLVED] A few SQL errors during migration from sqlite to mysql


#1

Hello everyone,

I experience errors trying to migrate from Sqlite to Mysql.

I follow the manual here : https://manual.seafile.com/deploy/migrate_from_sqlite_to_mysql.html

Everything is OK until step 7 :

  • during the import of ccnet_db_data.sql :
    ERROR 1054 (42S22): Unknown column ‘reference_id’ in ‘field list’

Indeed, the sql file “ce_ccnet_db.sql” that creates the tables in Mysql database “ccnet_db” omits the last field “reference_id” of the table

EmailUser : 
     CREATE TABLE `EmailUser` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `email` varchar(255) DEFAULT NULL,
      `passwd` varchar(256) DEFAULT NULL,
      `is_staff` tinyint(1) NOT NULL,
      `is_active` tinyint(1) NOT NULL,
      `ctime` bigint(20) DEFAULT NULL,
      PRIMARY KEY (`id`),
      UNIQUE KEY `email` (`email`)
    ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;   

Obviously, when trying to import backed up data with an additionnal field, it fails :
INSERT INTO EmailUser(id, email, passwd, is_staff, is_active, ctime, reference_id) VALUES(1,’…’,’…’,1,1,’…’,NULL);

I think the solution is to create the missing field in the table structure, but I don’t know which type it is.

  • during the import of ccnet_db_data.sql :
    ERROR 1146 (42S02) at line 223 in file: 'seafile_db_data.sql': Table 'seafile_db.RepoInfo' doesn't exist

Indeed, “ce_seafile_db.sql” did not create this table…

  • during the import of “seahub_db_data.sql” :

    ERROR 1451 (23000) at line 1 in file: ‘seahub_db_data.sql’: Cannot delete or update a parent row: a foreign key constraint fails

    (seahub_db.auth_permission, CONSTRAINT auth_permission_content_type_id_2f476e4b_fk_django_co FOREIGN KEY (content_type_id) REFERENCES

    django_content_type (id))

and also, some lines later :

ERROR 1062 (23000) at line 41 in file: 'seahub_db_data.sql': Duplicate entry '1' for key 'PRIMARY'
ERROR 1062 (23000) at line 42 in file: 'seahub_db_data.sql': Duplicate entry '2' for key 'PRIMARY'
ERROR 1062 (23000) at line 43 in file: 'seahub_db_data.sql': Duplicate entry '3' for key 'PRIMARY'
ERROR 1062 (23000) at line 44 in file: 'seahub_db_data.sql': Duplicate entry '4' for key 'PRIMARY'
ERROR 1062 (23000) at line 45 in file: 'seahub_db_data.sql': Duplicate entry '5' for key 'PRIMARY'
ERROR 1062 (23000) at line 46 in file: 'seahub_db_data.sql': Duplicate entry '6' for key 'PRIMARY'
ERROR 1062 (23000) at line 47 in file: 'seahub_db_data.sql': Duplicate entry '7' for key 'PRIMARY'
ERROR 1062 (23000) at line 48 in file: 'seahub_db_data.sql': Duplicate entry '8' for key 'PRIMARY'
ERROR 1062 (23000) at line 49 in file: 'seahub_db_data.sql': Duplicate entry '9' for key 'PRIMARY'
ERROR 1062 (23000) at line 50 in file: 'seahub_db_data.sql': Duplicate entry '10' for key 'PRIMARY'
ERROR 1062 (23000) at line 51 in file: 'seahub_db_data.sql': Duplicate entry '11' for key 'PRIMARY'
ERROR 1062 (23000) at line 52 in file: 'seahub_db_data.sql': Duplicate entry '12' for key 'PRIMARY'
ERROR 1062 (23000) at line 53 in file: 'seahub_db_data.sql': Duplicate entry '13' for key 'PRIMARY'
ERROR 1062 (23000) at line 54 in file: 'seahub_db_data.sql': Duplicate entry '14' for key 'PRIMARY'
ERROR 1062 (23000) at line 55 in file: 'seahub_db_data.sql': Duplicate entry '15' for key 'PRIMARY'
ERROR 1062 (23000) at line 56 in file: 'seahub_db_data.sql': Duplicate entry '16' for key 'PRIMARY'
ERROR 1062 (23000) at line 57 in file: 'seahub_db_data.sql': Duplicate entry '17' for key 'PRIMARY'
...

Do someone have an idea to resolve this ?

Thank you a lot.


#2

I see that the files are not up-to-date for 6.3.x version of Seafile server, also the migrate script needed some small fixing. I have all the fixes ready if you can please try it this way:

Please write back if it works, so I try update official migration manual (extra alert what versions are supported should be added also).


#3

Hello,
Thank you a lot for your help.
I’m using Seafile 6.2.2.

Unfortunalety your fix does not work with my version of Seafile. I still have errors when I try to import my data :

https://pastebin.com/kDCDcwv6

Thank you.


#4

Aha than probably pubished scripts are even older than I thought… Can you please first update to latest version and than try the new scripts?

Alternative is to install a new temp installation of seafile with mysql and the same old version and than using mysql database from there for your migration, but it is more work and you need to clean database data manually.


#5

Hello,
I updated to latest version (6.3.2) and I got similar errors.

Thank you.


#6

Hi, I think I found and corrected some error, I also did a export/import test for ccnet on some old installs I have. The first 3 files are ok from before, the script is new: msqlite_to_mysql_fix20190514


#7

Hello @Lonsarg

It worked like a charm :slight_smile:

Thank you for your help.


#8

Great to be able to help, time to put this fixes to official manual page.

This is the pull request for these fixes, any further discussion should be there: https://github.com/haiwen/seafile-docs/pull/568