I attempted to load additional content from African Storybook today. It failed to download the selected content. Since then, I’ve not been able to access the online channels. I get a red exclamation point symbol after some time, then it never succeeds to present the content selection page again. I’ve tried this with other channels with the same results. I have also rebooted my Kolibri server, still to no avail.
Thank you for reporting this issue! Could you please show us the
kolibri.log file here so that we can find what might have gone wrong in the backend? I assume this might be something wrong with the database but I can’t be certain about the specific causes without checking the log file. Thank you so much!
Here’s a link to the zipped kolibri.log file:
I think I found the error that caused the issue:
sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) NOT NULL constraint failed: content_file.available [SQL: 'UPDATE content_file SET available=(SELECT content_localfile.available \nFROM content_localfile \nWHERE content_file.local_file_id = content_localfile.id\n LIMIT ? OFFSET ?)'] [parameters: (1, 0)] (Background on this error at: http://sqlalche.me/e/gkpj)
I will keep working on the error and keep you posted. Thank you!
@lingyi: Thank you for your efforts!
Do you mind sending your database file to my email (email@example.com). I’m not able to reproduce this error from my side, so your database file could help a lot. Thank you so much!
@lingyi: Did you get the email I sent with a link for downloading the file?
If not, this is it: https://www.dropbox.com/s/3jtvin6a44k3s6w/db.sqlite3.zip?dl=0
Sorry about not updating you the status! I’ve been focusing on this issue these days, but unfortunately I still haven’t found the cause to it yet. I will let you know as soon as I have some updates. I’m very sorry about that. Thank you!
It’s ok. Just wanted to be sure you got the file. I tried to add content again today. Same problem as originally reported. Seems the failed download of ASb content messed up the whole soup! I wonder if deleting ASb channel and then importing again would do the trick.
Unfortunately I don’t think deleting the content and importing it again would help resolve the issue (I also tested this on my side). From what I’ve found, this error might not come from ASB content but something wrong happened before you imported ASB content. I currently have a temporary solution to let the app work but I would love to double check with other developers to be sure it’s viable. Thanks!
After discussing with another developer Richard, we think that your database file has been corrupted. To make kolibri work, we suggest you remove the corrupted row in the database file.
Here are the steps to do so. You will need
sqlite3 installed on your machine and run the following commands in the terminal:
kolibri stopto make kolibri stop running
kolibri manage vacuumsqlitein case that there are still some data in the WAL file
- Make a copy of the
~/.kolibri/db.sqlite3file and store it somewhere else, in case anything wrong happens during the following steps
- sqlite3> .mode insert
- sqlite3> .output dump_all.sql
- sqlite3> .dump
- sqlite3> .exit
- Use a text editor to open dump_all.sql file
- change the last line in dump_all.sql file from
COMMIT;. After that, save the file.
- make sure there is no file named fixed.data existing in the current directory
- sqlite3> .read dump_all.sql
- sqlite3> SELECT id FROM content_file where preset = “M”;
- you will get result
6fbdd89a53597a37586843c25c51f5a7. If you do not get this as the result of the previous step, please stop here and let me know what you get. Thanks!
- sqlite3> DELETE FROM content_file where id = “6fbdd89a53597a37586843c25c51f5a7”;
- sqlite3> .exit
cp fixed.data ~/.kolibri/db.sqlite3
kolibri start. Now you should be able to log into kolibri and import content like before.
If things still don’t work, please feel free to let me know. Thank you!
@lingyi: Thank you for the detailed steps. I followed them as listed. The first problem I encountered is the dump_all.sql file’s last line read COMMIT; instead of ROLLBACK;
I decided to move forward to see what would happen. Following step 14, I got no result at all, so I stopped there.
I restarted Kolibri and tested to see if I can add additional content, but it is still the same problem.
I’m sorry that the steps don’t work for you. Could you please send me the fixed.data file you got? Thank you!
@lingyi : No problem. Thanks for continuing to work on this. I’ve not upgraded to a 0.12 version because I fear it won’t work unless we can sort out the db problem.
Here is a link to the fixed.data file: https://www.dropbox.com/s/dgr67z4nslog3vk/fixed.data.zip?dl=0
For step 14, do you get the error
Error: no such column: “M”? If so, could you please try typing the command
SELECT id FROM content_file where preset = "M"; instead of copying and pasting it? I realize that the quotes here don’t work.
Since you have the fixed.data file already, please try the following commands:
SELECT id FROM content_file where preset = "M";
thank you so much!
With step 14, I never got a response.
So, before I do this, should I start from the beginning again? When I followed the instructions, I did not copy/paste. I retyped the commands myself.
I wonder if I were to give you SSH access to my Kolibri server, if it would be better for you to simply go into my unit and work on it directly instead of our back and forth. If that will work for you, I can open access to you for 8 hrs using the remote.it account I created.
Yes having remote access to your Kolibri server would be helpful for me to debug your database issue. Thank you so much!
Here’s the results:
pi@raspberrypi:~/.kolibri $ sqlite3 fixed.data
SQLite version 3.16.2 2017-01-06 16:32:41
Enter “.help” for usage hints.
sqlite> SELECT id FROM content_file where present = “M”;
Error: no such column: present
I opened fixed.data with a text editor and searched for “6fbdd89a53597a37586843c25c51f5a7” and found it in the file. So it is here, but the sqlite query did not find it.
I’ll send you an email on how to access the Kolibri server here. We are 9 hrs ahead of CA. I believe that is where you are. The free account I have with remote.it allows for up to 8 hours of access without needing to refresh the account. So, at around 9 AM your time, I’ll open up the account and refresh the links.
BTW, another problem recurred yesterday. I had to add new students to Kolibri. I wanted to create a new group, which I did. However, I’m not able to add the new students to this group or any group because they are not listed in the “Ungrouped” section, nor anywhere on that list. This is a problem that occurred before but I worked around it by creating all new groups, etc. That was at the beginning of the workshop. At this point, it would not be easy at all to have to do the same. Maybe this is all associated with the corrupted db.
@lingyi: I’ve sent you an email with access details
This issue has been resolved by the 19 steps provided above. One thing to note is that step 10 did not work, as the sql file did not contain ROLLBACK but COMMIT. But the step did not affect the ones following it.