Migate from SQLite to MySQL - script for 6+ versions

Hi, not so long time ago i wrote a script for migrating form SQLite to mysql that fixes the problems with current official script (which did not work without heavy manual fixing + resulting migrated schema is not identical to fresh mysql schema):
https://forum.seafile.de/t/migrate-from-sqlite-to-mysql-improved-script/5303
Scipt worked very well i migrated my home server with it. Problem was that script was 5.1.x only.

With release 6 of server devs removed all the big schema differences between sqlite and mysql databases(well done) and i could write a new cleaner script (also with shorter instructions) that in theory should work with any future versions also (if no big differences and by big i mean fields missing, between sqlite in mysql databases are introduced).

So here are instructions on how to use this new SQLite to MySQL migration script for 6+ versions.
Maybe this should replace current official instructions: https://manual.seafile.com/deploy/migrate_from_sqlite_to_mysql.html ?

Instruction for migrating seafile from sqlite to mysql (linux)

  1. Stop seafile and seahub

  2. Make backup

  3. Download sqlite2mysql_data6+.sh, to the top directory of your Seafile installation path. For example “/data/haiwen”:
    sqlite2mysql_data_6+.sh

  4. Run sqlite2mysql_data6+.sh:
    a. chmod +x sqlite2mysql_data_seafile6+.sh
    b. ./sqlite2mysql_data6+.sh
    This script will produce three files: ccnet-db-data.sql, seafile-db-data.sql, seahub-db-data.sql).

  5. Next step is to make a temporary fresh installation of seafile server with mysql. Must be identical version as your current sqlite server! Just follow the instructions:
    https://manual.seafile.com/deploy/using_mysql.html

  6. Start this new fresh installation to ensure all databases are initialized. Make a quick test(one library, empty folder,…) than you can DELETE this fresh installation, since we only need the databases.

  7. Start mysql in a way that shows you errors(we want to see if any error happens):

    a. If you are using MARIA DB, use “abort on error” to not miss any error:
    mysql –-show-warnings –-abort-source-on-error -u seafile -p

    b. If using MySql, output to log and later check if any errors
    mysql –-show-warnings –-tem=seafile_migration.log -u seafile -p

  8. Load first schemas and then data to your MySQL databases. For example:

    mysql> use ccnet-db
    mysql> source ccnet-db-data.sql
    mysql> use seafile-db
    mysql> source seafile-db-data.sql
    mysql> use seahub-db
    mysql> source seahub-db-data.sql

    The last one can take a LONG time if you have a lot of data. Can be more than one hour per TB of data you have!

    IMPORTANT: If there is any error during importing data files do not continue.

  9. Modify configuration.

    Append following lines to ccnet.conf:
    [Database]
    ENGINE=mysql
    HOST=127.0.0.1
    USER=seafile
    PASSWD=seafile
    DB=ccnet-db
    CONNECTION_CHARSET=utf8

    Note: Use 127.0.0.1, don’t use localhost.

    Replace the database section in seafile.conf with following lines:
    [database]
    type=mysql
    host=127.0.0.1
    user=seafile
    password=seafile
    db_name=seafile-db
    CONNECTION_CHARSET=utf8

    Append following lines to seahub_settings.py:
    DATABASES = {
    ‘default’: {
    ‘ENGINE’: ‘django.db.backends.mysql’,
    ‘USER’ : ‘seafile’,
    ‘PASSWORD’ : ‘seafile’,
    ‘NAME’ : ‘seahub-db’,
    ‘HOST’ : ‘127.0.0.1’,
    # This is only needed for MySQL older than 5.5.5.
    # For MySQL newer than 5.5.5 INNODB is the default already.
    ‘OPTIONS’: {
    “init_command”: “SET storage_engine=INNODB”,
    }
    }
    }

  10. Start seafile and seahub and test your new server (sync, upload, maybe run fsck tool,…)

  11. Do not forget to modify your autostart script to wait for mysql service: http://manual.seafile.com/deploy/start_seafile_at_system_bootup.html

2 Likes

First of all, thank you for your post.

Honestly, the official documentation is actually a reference to your idea: first create clean databases and tables, then migrate the table data from sqlite。

But compared to your post, there are still some differences: user don’t need to start a new fresh installation of mysql, they just need to create the three mysql databases and the corresponding database tables (by the ce_*_db.sql scripts which will always be updated to the newest Seafile version).

This will be more convenient and easy to understand.

Hi, my mistake for not checking the current official manual, took a quick look and though it was stil the old version :slight_smile:

I see that it is already updated with my input and also much more streamlined by including scripts for generating databases, so kudos. Will take a look.

Hello,

I try to convert my Seafile v6 installation from sqlite to mysql, but either the official script or the one from this thread give me this error message :

Error: mode should be one of: column csv html insert line list tabs tcl
Resulting sql files doesn’t contains any INSERT statements, so something goes wrong :slight_smile:

Any idea to solve this?

Hi,

Gues you have found an error that only happens on some systems.

This error seems to be thrown by sqlite3 on this line:
echo “.mode insert\nselect * from ‘${TABLE}’;” | sqlite3 $1 |

Can you try modifying this line if it will help?(added one extra space after “.mode insert”)
echo “.mode insert \nselect * from ‘${TABLE}’;” | sqlite3 $1 |

I suspect that maybe not all linux systems handle this “piping via new lines” the same way. Maybe this extra space will help. No other idea at the moment, since i am pretty new to linux and shell…

Hi,

Thank you for the quick answer.

With the extra space after “.mode insert”, the error message disappear, but resulting sql files still contains only DELETE FROM and SET FOREIGN_KEY_CHECKS statements, so I think there is a problem somewhere.

I tried to use bash, or sh, to see if it’s a shell-relative problem, but no difference.

Yes it must be bash/shell problem. Because if i run this command directly in terminal i DO get your error. But if i run it via ./sqlite2mysql_data6+.sh it works ok.

Which linux distro do you have? Maybe your distro does not recognize “#!/bin/sh” in first line of script.

You could try forcing this script to be run as shell script by running it this way:
sh sqlite2mysql_data6+.sh

I’m using Debian stable, 32bits i386 flavor. My distro usually recognize “#!/bin/sh” very well :wink:

I tried running the script by “sh sqlite2mysql_data6+.sh”, same problem.

Hi, i did a little research and i think ubuntu uses dash for default interpreter while debian uses bash and it seems my script is dash specific.

To confirm my theory try running “dash sqlite2mysql_data6+.sh” if that works.

If this turns out to be true i would need to modify script as #!/bin/dash, but this could be a problem cause i think some debian flavors do not have dash installed by default.

Hum… I wanted to retry with dash, but the link to the shell script give now a 404 error at dropbox. (I deleted it on my server, unfortunately).

Can you post a new link to you script?

Thank you!

there is one small but important difference in the seafile documentation regarding
fresh installation vs upgrade.

Fresh installation creates a specific user for seafile, whereas upgrade just uses root.

While does not seem significant, as the both user have all privileges,
can you modify the documentation so it is identical and thus avoid confusion?

Yeah, dropbox moved public folder, needed to recreate link:

Hi,

I couldn’t get this running on my Archlinux-System.

Problem
Originally I came here because of the recurring error:

Error: mode should be one of: ascii column csv html insert line list quote tabs tcl

This has to do with the interpretion of the newline \n - for me it got fixed with the following.
On costed 3 hours :confused:

Solution: Please correct the code in row 28 (add -e)

was:

echo ".mode insert\nselect * from '${TABLE}';" | sqlite3 $1 |

to:

echo -e ".mode insert\nselect * from '${TABLE}';" | sqlite3 $1 |

Thanks @Lonsarg !!

fixed file can be found as gist here

Why are you using Arch on your Server ? I know pacman and yaourt are much better than apt etc.,but on my Server I always run Debian.

This script was already used to update official documentation as far as i saw:
https://manual.seafile.com/deploy/migrate_from_sqlite_to_mysql.html

As for your fix, it should be tested that it also works on other systems beside arch before updating official docs. I really do not have time at the moment:) This unconsistency in script language interpretation between different linux subsystems is very annoying, this was probalby my first and last script written in shell language, .net Core from now on hehe:)

1 Like