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).
Instruction for migrating seafile from sqlite to mysql (linux)
Stop seafile and seahub
Make backup
Download sqlite2mysql_data6+.sh, to the top directory of your Seafile installation path. For example â/data/haiwenâ: sqlite2mysql_data_6+.sh
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).
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
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.
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
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.
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â,
}
}
}
Start seafile and seahub and test your new server (sync, upload, maybe run fsck tool,âŚ)
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
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.
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
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âŚ
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
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.
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:)