SQLite to MySQL script goes wrong

Seafile Server
1

Hi everyone
After run the “sqlite2mysql.sh” script to migrate sqlite to mysql, the script published here (Migrate From SQLite to MySQL - Seafile admin manual), I get some incorrect sql statements:

output of the “seahub_db.sql” file

`DROP TABLE IF EXISTS `IF`;

CREATE TABLE IF NOT EXISTS `IF` NOT EXISTS `django_migrations` (`id` integer NOT NULL PRIMARY KEY AUTO_INCREMENT AUTO_INCREMENT, `app` varchar(255) NOT NULL, `name` varchar(255) NOT NULL, `applied` datetime NOT NULL) ENGINE=INNODB;
INSERT INTO `django_migrations` VALUES(1,'admin_log','0001_initial','2017-09-25 05:15:11.274464');

DROP TABLE IF EXISTS `IF`;
CREATE TABLE IF NOT EXISTS `IF` NOT EXISTS `registration_registrationprofile` (`id` integer NOT NULL PRIMARY KEY AUTO_INCREMENT AUTO_INCREMENT, `emailuser_id` integer NOT NULL, `activation_key` varchar(40) NOT NULL) ENGINE=INNODB;

DROP TABLE IF EXISTS `IF`;
CREATE TABLE IF NOT EXISTS `IF` NOT EXISTS `api2_token` (`key` varchar(40) NOT NULL PRIMARY KEY, `user` varchar(255) NOT NULL UNIQUE, `created` datetime NOT NULL) ENGINE=INNODB;

INSERT INTO `api2_token` VALUES('ebe5cb6763************************a9a2','asdasdasd@posteo.ch','2020-05-08 07:46:57.069152');`

How can I fix this?

2

Hi all

any suggestions, more information, ambiguities …

it is not possible to migrate my seafile and it can not be used currently

3

Which version of Seafile are you using?

4

I’ve upgraded from version 7.x to 8.x. Now its 8.0.3 for Raspberry Pi

5

Hello, I think the problem is caused by such sql statement.

Can you have a check why this sentence IF NOT EXISTS is repeated on Raspberry Pi?

6

Hi, I dont know why this happen. I run the “sqlite2mysql.sh” script and what I get as a result is just what I wrote above.

But the topic is now a month old, so I can not migrate my system and will find a way to export my data and look for another service.

7

Sorry for the late reply, as you are using Raspberry Pi, we don’t have such test environment to reproduce this problem.

You can try to manually change the sql statement to make them conform to sql syntax.

8

i wanted to do that too, but every statement is wrong. not only the one mentioned here. the effort is too big.

9

I am also having this problem with seahub_db.sql.
Server is Ubuntu 18.04LTE
All of the drop-/create table commands have a duplicated IF NOT EXISTS section

image
image896×66 3.12 KB

10

i’m just now trying the migration as explained in the docs, and it seems the scripts are still broken. i got only one error for ccnet_db:

ERROR 1170 (42000): BLOB/TEXT column 'email' used in key specification without a key length
Query OK, 0 rows affected, 1 warning (0,02 sec)

and none for seafile_db, but in seahub-db.sql all DROP TABLE/CREATE TABLE queries are created wrong and must be fixed manually. for example:

DROP TABLE IF EXISTS `IF`;
CREATE TABLE IF NOT EXISTS `IF` NOT EXISTS `api2_tokenv2`

there’s about 100 of them that need fixing. but there’s also some other broken code it seems, at least this is what i see on several queries:

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 '`view_download`) ENGINE=INNODB' at line 1
ERROR 1146 (42S02): Table 'seahub_db.share_fileshare' doesn't exist
ERROR 1146 (42S02): Table 'seahub_db.share_fileshare' doesn't exist
ERROR 1146 (42S02): Table 'seahub_db.share_fileshare' doesn't exist
ERROR 1146 (42S02): Table 'seahub_db.share_fileshare' doesn't exist
...

several tables are not being created because of these incomplete(?) queries.

btw, the docs seem to omit some code for step “Create 3 databases ccnet_db, seafile_db, seahub_db and seafile user.” it would be more straight forward if that section would read:

mysql -u root -p
mysql> create database `ccnet_db` character set = 'utf8';
mysql> create database `seafile_db` character set = 'utf8';
mysql> create database `seahub_db` character set = 'utf8';

mysql> create user 'seafile'@'localhost' identified by '<safe password here>';

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

further config file modifications should then be changed accordingly instead of using root with password root.

11

here’s some more manual fixes i had to make to the seahub-db.sql script for it to run without errors.

api2_tokenv2

# wrong:
CREATE TABLE IF NOT EXISTS `api2_tokenv2` (    `key` varchar(40) NOT NULL PRIMARY KEY,    `user` varchar(255) NOT NULL,    `platform` varchar(32) NOT NULL,    `device_id` varchar(40) NOT NULL,    `device_name` varchar(40) NOT NULL,    `platform_version` varchar(16) NOT NULL,    `client_version` varchar(16) NOT NULL,    `last_accessed` datetime NOT NULL,    `last_login_ip` char(39), wiped_at datetime DEFAULT NULL, created_at datetime NOT NULL DEFAULT `1970-01-01 00:00:00`,    UNIQUE (`user`, `platform`, `device_id`)) ENGINE=INNODB;

this was missing backticks for wiped_at and created_at, but backticks around 1970-01-01 00:00:00 should be single quotes:

# ok:
CREATE TABLE IF NOT EXISTS `api2_tokenv2` (    `key` varchar(40) NOT NULL PRIMARY KEY,    `user` varchar(255) NOT NULL,    `platform` varchar(32) NOT NULL,    `device_id` varchar(40) NOT NULL,    `device_name` varchar(40) NOT NULL,    `platform_version` varchar(16) NOT NULL,    `client_version` varchar(16) NOT NULL,    `last_accessed` datetime NOT NULL,    `last_login_ip` char(39), `wiped_at` datetime DEFAULT NULL, `created_at` datetime NOT NULL DEFAULT '1970-01-01 00:00:00',    UNIQUE (`user`, `platform`, `device_id`)) ENGINE=INNODB;

custom_share_permission

# wrong:
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;

looking at seahub/sql/mysql.sql from the server sources, permission should be longtext NOT NULL:

# ok:
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;

share_fileshare

# wrong:
CREATE TABLE IF NOT EXISTS `share_fileshare` (    `id` integer NOT NULL PRIMARY KEY AUTO_INCREMENT,    `username` varchar(255) NOT NULL,    `repo_id` varchar(36) NOT NULL,    `path` text NOT NULL,    `token` varchar(10) NOT NULL UNIQUE,    `ctime` datetime NOT NULL,    `view_cnt` integer NOT NULL,    `s_type` varchar(2) NOT NULL,    `password` varchar(128),    `expire_date` datetime, permission varchar(50) not null default `view_download`) ENGINE=INNODB;

works with added backticks around permission and view_download in single quotes. however, when importing my data into it, i received Data too long for column 'token' errors and had to replace varchar(10) with varchar(100) as in seahub/sql/mysql.sql:

# ok:
CREATE TABLE IF NOT EXISTS `share_fileshare` (    `id` integer NOT NULL PRIMARY KEY AUTO_INCREMENT,    `username` varchar(255) NOT NULL,    `repo_id` varchar(36) NOT NULL,    `path` text NOT NULL,    `token` varchar(100) NOT NULL UNIQUE,    `ctime` datetime NOT NULL,    `view_cnt` integer NOT NULL,    `s_type` varchar(2) NOT NULL,    `password` varchar(128),    `expire_date` datetime, `permission` varchar(50) not null default 'view_download') ENGINE=INNODB;

share_uploadlinkshare

# wrong:
CREATE TABLE IF NOT EXISTS `share_uploadlinkshare` (    `id` integer NOT NULL PRIMARY KEY AUTO_INCREMENT,    `username` varchar(255) NOT NULL,    `repo_id` varchar(36) NOT NULL,    `path` text NOT NULL,    `token` varchar(10) NOT NULL UNIQUE,    `ctime` datetime NOT NULL,    `view_cnt` integer NOT NULL,    `password` varchar(128),    `expire_date` datetime) ENGINE=INNODB;

same here, replaced varchar(10) with varchar(100) for token:

# ok:
CREATE TABLE IF NOT EXISTS `share_uploadlinkshare` (    `id` integer NOT NULL PRIMARY KEY AUTO_INCREMENT,    `username` varchar(255) NOT NULL,    `repo_id` varchar(36) NOT NULL,    `path` text NOT NULL,    `token` varchar(100) NOT NULL UNIQUE,    `ctime` datetime NOT NULL,    `view_cnt` integer NOT NULL,    `password` varchar(128),    `expire_date` datetime) ENGINE=INNODB;
12

Thank you m.eik! I’m on Ubuntu 22.04 LTS and just tried doing the SQLite to MySQL migration and ended up with exactly the same issues as the others in this thread. Your guide was valid for me as well, with not just the wrong DROP and duplicated iF NOT EXISTS but also all the other errors with missing statements, backticks or even wrong backticks.

Clearly this script cannot be recommended at the moment.