SQLite->MySQL conversion problem


#1

Seafile 6.3.4 CE on Ubuntu 18.04 64bit x86 throws error messages during conversion from SQLite to MySQL accoding to the server manual. The server was previously running 6.2.5 CE with SQLite, and the upgrade to 6.3.4 CE with SQLite worked fine.
Here’s the log of the failed conversion from SQLite to MySQL:

user@myhost:~$ ./sqlite_to_mysql.sh
Start export ccnet data from user
Start export ccnet data from group
Done export ccnet data
Start export seafile data
Done export seafile data
Start export seahub data
syncDone export seahub data
user@myhost:~$ mysql -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 5.7.25-0ubuntu0.18.04.2 (Ubuntu)
mysql> create database ccnet_db character set = ‘utf8’;
Query OK, 1 row affected (0.00 sec)
mysql> create database seafile_db character set = ‘utf8’;
Query OK, 1 row affected (0.00 sec)
mysql> create database seahub_db character set = ‘utf8’;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
±-------------------+
| Database |
±-------------------+
| information_schema |
| ccnet_db |
| seafile_db |
| seahub_db |
±-------------------+
4 rows in set (0.01 sec)
mysql> use ccnet_db;
Database changed
mysql> source ce_ccnet_db.sql;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.03 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.04 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.02 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.03 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.03 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.03 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.03 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.03 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.03 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.03 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> source ccnet_db_data.sql;
Query OK, 0 rows affected (0.00 sec)
ERROR 1054 (42S22): Unknown column ‘reference_id’ in ‘field list’
ERROR 1054 (42S22): Unknown column ‘reference_id’ in ‘field list’
ERROR 1054 (42S22): Unknown column ‘reference_id’ in ‘field list’
Query OK, 1 row affected (0.02 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
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 ‘Group(group_id, group_name, creator_name, timestamp, type, `parent_gro’ at line 1
Query OK, 1 row affected (0.01 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
mysql> ^DBye

Any ideas?


#2

Same here.

@hailfinger did you find a solution?


#3

No. I tried for a while, and then I decided to move on. Seafile is still running with SQLite.

AFAICS the databases don’t contain that much info anyway, and restoring files in case of a crash/corruption can be done without the databases. You’ll lose owner information, though, but I don’t really care about that.

It would be interesting to check if a new installation of older+current Seafile versions can do the conversion, i.e. whether this problem is a regression for clean installs, or related to previous upgrades.


#4

I found it. The table “Group” is a keyword - GROUP. One has to modify the *.sql-file or change the statement by hand do use `Group` instead of Group. Then it magically works…


#5

Good find. Can someone try this fixed script that automatically inserts `` around table names. I do not have a working SQLite seafile installation nearby to test:
https://1drv.ms/u/s!Auorgwg45aTNwtkIAZPbbsxIVJwG3w