kanboard: Unable to run SQL migrations v1.2.16 to v1.2.17 (sqlite)

Well, you first have to know which tables were altered when you installed your plugins, and how (which columns were added and what type of data do the columns contain). There are two ways of doing that.

First solution, you compare the app/Schema/Sqlite.php file of Kanboard (here) with the current schema of your database. You can open your database with DBeaver Community or with DB Browser for SQLite. In my case for instance, when I open my SQLite database and I go to the tasks table, I can see that there are two columns that do no appear in the app/Schema/Sqlite.php file of Kanboard: owner_gp and owner_ms. I’m lucky: only the tasks table was altered by my plugins.

Second solution, you go to the Github page of all of your plugins, you locate the file which contain the SQLite database modifications, and identify the requests that have the ALTER keyword in it. Using the Group_Assign module example, here is the file, and you can see on line 33 that it does a request to alter the tasks table to add a owner_gp column. In my case, the owner_ms I mentioned earlier must have come from another plugin. That’s why you should do this for each of your plugins.

Either way, once you know which tables were edited, the name of the columns that were added and their type, you should edit the app/Schema/Sqlite.php of the new release of Kanboard to add these two columns, just like I explained on my message from January 20th. Then replace all the files in the Kanboard folder on your server with the files from the new release, including the app/Schema/Sqlite.php file that you edited, and run the upgrade. Don’t forget to backup your DB file though.

Who performed your Kanboard install, and could they not help you with that?

The plugin “Group_Assign” adds 2 extra columns owner_ms and owner_gp: https://github.com/creecros/Group_assign/blob/master/Schema/Sqlite.php

If you want to keep this plugin, you will have to change the migration manually on your installation to add these 2 columns: https://github.com/kanboard/kanboard/blob/master/app/Schema/Sqlite.php#L13-L56

You can also choose to skip the migration 125. The only change here is to add a foreign key for swimlane_id. If that your choice, then update the field version in table schema_version to the value 125.

Note that v1.2.18 has been released to fix a different Sqlite issue. See https://github.com/kanboard/kanboard/releases/tag/v1.2.18

I can confirm that it works great and allowed us to upgrade our 1.2.16 instance to version 1.2.18 by manually editing the app/Schema/Sqlite.php as described. To be even more explicit, we edited the following lines (L49-L50) of the file, from this:

            external_uri         TEXT
        )

To this:

            external_uri         TEXT,
            owner_gp             INTEGER DEFAULT 0,
            owner_ms             INTEGER DEFAULT 0
        )

The upgrade succeeded, no error message, no data or functionalities lost. Thank you for your help!

Maybe this process could be described in a documentation, or at least in the Release message on Github? Like “If you use plugins that edit tables (e.g. Group_Assign), you have to manually apply the database changes to the Sqlite.php file”?

Ok, I’m assuming the issue is solved now according to your last update.

If you still have a problem, reach out the author of this plugin: https://github.com/creecros/Group_assign/issues

The plugin “Group_Assign” adds 2 extra columns owner_ms and owner_gp: https://github.com/creecros/Group_assign/blob/master/Schema/Sqlite.php

If you want to keep this plugin, you will have to change the migration manually on your installation to add these 2 columns: https://github.com/kanboard/kanboard/blob/master/app/Schema/Sqlite.php#L13-L56

You can also choose to skip the migration 125. The only change here is to add a foreign key for swimlane_id. If that your choice, then update the field version in table schema_version to the value 125.

Note that v1.2.18 has been released to fix a different Sqlite issue. See https://github.com/kanboard/kanboard/releases/tag/v1.2.18

@sebw Well, you should be careful about “getting rid” of columns, they may contain useful data created by the plugins that you may lose if you just delete them. Or maybe I misunderstood what you said.

@gerroon If you go with sebw’s solution, you should make a backup of your database file so that you can revert to it if anything goes wrong. One solution can be to go to your Kanboard’s plugin page, and for each plugin you have installed, go to their respective Github repository, and ask the developer if their plugin adds columns to the database. That way, you would be able to correct the app/Schema/Sqlite.php appropriately. But I understand that it’s a delicate process if you’re not tech-savvy.

Ideally, this particularity should be taken care of by Kanboard’s upgrade process, or at least documented…

@gerroon I used DBeaver Community to fix my sqlite DB as I only know MySQL. I was able to get rid of the conflicting columns with a few clicks. Feel free to share your DB with me, I can try to have a look at what is wrong in it.

Edit: I added the missing columns in a wrong order (needs to be owner_gp first then owner_ms. Migration worked properly now.

I’ve the same problem updating from 1.2.16 to 1.2.18. I modified Sqlite.php to add the missing columns and migration worked then. But tasks are loosing all already assigned groups.