Unable to import the sql dump through phpmyadmin

[nekohayo]

I'm hosted on a shared host, so I manage my mysql databases through the provided phpmyadmin web interface. The web host is likely running Centos (presumably version 7) and they provide the choice of PHP 5.4, 5.6, 7.x, etc. I had no issue running a sqlite-based kanboard on it. I'm running the latest Kanboard, 1.2.10

I followed your instructions and ran this:

$ cp --archive kanboard_perf_test/ kanboard_perf_test_mysql
$ git clone https://github.com/oliviermaridat/kanboard-sqlite2mysql
$ cd kanboard-sqlite2mysql && chmod u+x kanboard-sqlite2mysql.sh kanboard-backup.sh
$ ./kanboard-backup.sh ../kanboard_perf_test_mysql/
# Kanboard backup in progress...
done
$ ./kanboard-sqlite2mysql.sh ../kanboard_perf_test_mysql/ -o db-mysql.sql
# Create MySQL data dump from SQLite database
done

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';

I did that without really knowing if I had to do it, but presumably so because utf8mb4 is mentioned in https://docs.kanboard.org/en/latest/admin_guide/mysql.html ; I dunno, this is all quite a bit confusing and I wish your script was an official part of Kanboard itself ;)

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:

Erreur
Requête SQL : ALTER TABLE users ADD COLUMN is_admin INT DEFAULT 0
MySQL a répondu :
#1146 - La table 'foo_kanboard-perso.users' n'existe pas

As you know (but maybe not the casual reader), this says it fails on the first SQL instruction of the file, because somehow the "users" table in that db doesn't exist. The same error occurs if I go to the sql command page in phpmyadmin and paste the contents of the sql file there.

I don't know what to do with this, any help is appreciated!

[nekohayo]

Update: the prepended SET NAMES, SET CHARACTER SET, SET SESSION commands are not the cause of the problem. Trying to import the database without those three instructions still fails in the same way.

The server runs SQLite 3.6.20 and mysql "14.14 distrib 5.6.41-84.1" for Linux "using 6.0", whatever that means...

Apparently, the .sql file generated by this script isn't a totally standard sql instructions/dump file, but rather something that works only specifically by using the script to do the mysql import?

[nekohayo]

Ah, I think I vaguely understand: that db really really wants to use this script for importing because otherwise with an empty db there are no kanboard mysql "schemas" in place, which is something that this script puts in place when:

• data/db.sqlite is present (otherwise it will fail, it seems)
• config.php is present and pre-configured (otherwise it will fail)

Importing straight from phpmyadmin or the "mysql" command will fail otherwise. But then I'm also stuck with problems like #23.

The documentation in https://github.com/oliviermaridat/kanboard-sqlite2mysql/blob/master/README.md isn't totally clear to me, because at the end of the day this single command does "everything" in one go:

Or you can also directly apply it to the MySQL database of your choice:

./kanboard-sqlite2mysql.sh <Kanboard instance physical path> <MySQL DB name> [ -h <MySQL DB host> -u <MySQL DB user> -p ]

...but when you read it, with the "Or you can also directly apply it to the MySQL database of your choice", it isn't totally clear that this command replaces most of the commands above it. It seems the instructions could be simplified quite a bit, and/or account for the cases where someone might need to import the .sql file manually into a separate mysql instance/server...

[hergl75]

Hi nekohayo,

I'm trying the same thing as you and as I could find out, you managed at least to get the db moved from sqlite to mysql. I tried several times and it never worked for me.
I'm wondering: Did you have any plugins installed when you succeeded? I have some plugins installed, which change/add stuff to the database, so maybe that's why it doesn't work for me.

Hopefully you still read this. Thanks for your reply in advance.

Best regards

[nekohayo]

I don't think I actually succeeded (well, I certainly didn't proceed to switch over my DBs, because of this here and also issue #23). I was hoping that @oliviermaridat could help with this, or turn this script into something that generates a standard utf8mb4 SQL dump (including schemas, I think?) that phpmyadmin (or other standard tools) can use to do clean migrations even if there are accents. Otherwise we're kind of stuck in a dead end.

I did/do have 2-3 plugins installed, yes, it's inevitable. Expecting users to not ever have any plugins installed wouldn't really be realistic, given kanboard's general hostility towards new features or major fixes (for the last few years the stance has basically been "go to the forum and beg for plugins from charitable people who may pity you enough"). You end up having to install a couple of plugins to be able to fix certain things that have gone long unadressed (like search operators or the board view's performance or the sqlite 999 query items limitation workaround), and sometimes the fixes are plugins just because the authors don't feel confident enough to contribute the improvements to the core of kanboard...

[oliviermaridat]

I am sorry @nekohayo , I do not have time to spend on this at the moment... Good luck! I hope you find a solution.

[bwaikel]

Has anyone figured this out? I see it's been over a year since the last comment. KanBoard is such a wonderful tool, yet I don't care for using it with SQLite. I successfully migrated the DB to MySQL and limited testing (logging in, moving, updating) worked just fine but it refuses to add new tasks. There were several hiccups, like a few columns with the TEXT data type, yet had KEYS assigned to them in SQLite which MySQL does not allow. MySQL also would not stand for the NOCASE instances but I have it set to utf8mb4_ci, which I thought would solve those issues, but no. I thought I would be able to post an SQL starter file for anyone needing it, but alas, it doesn't work completely. :-( I'm using KanBoard-1.2.22.

[SunnnyGohil]

I solved this by first just change the database configuration from config.php file then run the webserve so i found the all the table ceated in mysql database then i add the above dump in phpmyadmin its work for me