How to Migrate sqlite to mysql

Installation Issues
1

I’ve got this installed and I love it but I can’t seem to figure out how to migrate sqlite to mysql. I’ve updated my config.php and then I’ve run php cli db:migrate but nothing changes in mysql when I go look via PHPmyadmin. Any tips would be helpful!

2

what is your expectation running php cli db:migrate? Are you expecting your old Sqlite data to Migrate over to Mysql? That’s only going to migrate data to newer schema versions within your choosen DB driver. If you are looking to transfer data from Sqlite DB to your Mysql DB, you will need to do that manually. Simplest way, might be to just download your Sqlite DB, and then use some type of converter, never actually done it…maybe someone else knows more than I do!

3

I just figured that out- I just finished a lot of copy and pasting :+1:

4

Greetings,

In case it is helpful, here is some reference:

1 Like
5

This worked for me. let me know if anyone has trouble. Worked on 1.2.7

6

The Kanboard migration path from SQLite to MySQL isn’t terribly clear (it’s not documented in Kanboard’s docs about MySQL nor Kanboard’s docs about SQLite) and I wish it was an officially supported procedure with a built-in tool for it.

I tried out that third-party script linked above and was unable to migrate, it fails on the first line of the resulting SQL dump: https://github.com/oliviermaridat/kanboard-sqlite2mysql/issues/21
…John, didn’t you run into this problem?

I’d appreciate any help in troubleshooting/solving this. The author of that migration script hasn’t made commits since over a year and so I’m not sure if it’s still maintained and if he will be around to answer this problem; that’s another reason why I would hope for this to be an “officially supported and documented” tooling part of the core of Kanboard (since SQLite performance is so limited, and even recommended against in the official documentation)…

7

No problems here. I looked over your bug and checked back at my notes and the only thing I didn’t do was :

Then I grabbed the db-mysql.sql file and, as per your instructions, 
added this at the top:

SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;
SET CHARACTER SET 'utf8mb4';
SET SESSION collation_connection = 'utf8mb4_general_ci';

Also, I had direct access to the server so I didnt do this part:

Then, in phpmyadmin, inside the target database (foo_kanboard-perso), 
in phpMyAdmin/import.php, I loaded the .sql file; I told it it was an 
uncompressed sql file, that it was utf8 (it didn't give me a choice for
utf8mb4, whatever that is) and I ran it. Then phpMyAdmin gave me this error:

and instead i did:

image
image.png1457×637 170 KB

Can you install MySQL locally to see if the import works? That way you can rule out any phpmyadmin / character set issues. This method worked for me in 1.2.7 and 1.2.8 .

John

8

OK, I think I finally found/understood part of the issue now, and have updated the bug report here accordingly.

In short, on my end it only works if I use the script to reimport the mysql db, it won’t work with either phpmyadmin or the “mysql” command locally, because of missing kanboard database schemas.

So in a way I at least found a way to get the db migrated… but the resulting data encoding is wrong, and that is kind of another showstopper :face_with_raised_eyebrow: