Upgrade impossible due to locked SQLIte

[jonschneider]

Hi,

I currently have a working 1.8.17 MyBB instance which uses a SQLite file as database.

I am trying to upgrade to 1.8.18 as a first step towards 1.8.19 but the install/upgrade.php script fails and complains about the SQLite database being locked. I tried the two upgrade methods (changed files and full install with edited config.php file) but I don't even get to the point where I can select the version to upgrade from, I get the error message immediately:

SQLSTATE[HY000]: General error: 6 database table is locked

I searched for similar cases on the Internet and tried different workarounds (creating copies of the database with cp or with the .backup SQLite command, creating an empty database and populating it with the output of the .dump SQLite command), but none of them worked and I systematically get the database locked error.

I checked with lsof and fuser that no other process was accessing the database, so my guess is that the upgrade.php is trying to execute several exclusive queries concurrently.

Here are the versions of the libraries installed on my machine (ArchLinux) which seem relevant for this issue:

• php 7.2.10-1
  
• php-fpm 7.2.10-1
  
• php-sqlite 7.2.10-1
  
• sqlite 3.25.2-1
  

Do you have an idea what is causing this error and how I could fix it (or bypass the error to proceed with the upgrade, since it has probably not even started as the version selection dialog didn't show)?

[Crazycat]

Set your forum offline, to stop the writing/reading operations on your database file.
If you can, kill all processes accessing to the sqlite (ps -aux | grep sqlite <-- to find them)

[jonschneider]

Sorry, forgot to mention that I set the board offline before trying to upgrade.

There is no sqlite process running and I still get the error.

[Crazycat]

Ok, so try:

sudo fuser /path/to/your/database.db[/code)
If it gives you something like:
[code]/path/to/your/database.db: 18645

kill the process n° (kill -9 18645)

If it don't work, your database is probably in a weird state because of a trouble, you can try to recreate it (not copy, recreate), using:

echo ".dump" | sqlite mybb.db | sqlite new.db

And then rename the two (the initial mybb as old, the new as mybb, or whatever is the name of your original file).

You can have a look @ https://random.kakaopor.hu/how-to-repair...-database/

Notice: peharps is it sqlite3 and not sqlite

[jonschneider]

Thank you, but as I said in the first post I already tried to detect the processes reading the database using fuser and lsof and there is none.

I also created clean copies of the database using the .dump trick and the result is still the same.

I see no external factor (processes, db state, etc...) that could explain the problem, which is why I suspect there is an issue with the upgrade script (maybe this is an edge case where it opens multiple connections to the database).

I will try again on another machine to see if I can replicate the problem.

[Crazycat]

Sorry, I read your post too quickly and didn't see you already try the dump trick.
Peharps is it the upgrade process itself which lock the database

[jonschneider]

Sorry for necrobumping this thread, but I found a workaround for the issue and forgot to post it here.

1. Close the board
   
2. Make a backup of the database
   

   sqlite3 forum.sqlite3 ".backup forum_backup.sqlite3"

3. Remove the mybb_upgrade_data table
   

   sqlite3 forum.sqlite3 "drop table mybb_upgrade_data"

4. Recreate the database from an SQL dump (just to be on the safe side wrt open connections)
   

   sqlite3 forum.sqlite3 ".dump" > forum.sql
   mv forum.sqlite3 forum_original.sqlite3
   sqlite3 forum.sqlite3 ".read forum.sql"
   chown http:http forum.sqlite3

5. Run the MyBB upgrade script
   
6. Reopen the board
   

I only remembered this morning because I had the same issue when attempting to upgrade from 1.8.19 to 1.8.20
I don't know if removing the mybb_upgrade_data table prior to the upgrade has negative side-effects, but doing so allows the script to run without error.

[User 2877]

How big is your forum? I've not been able to use the upgrade script if it has certain queries for years now and I run MySQL (MariaDB). Depending on the size of the forum, if you have a lot of posts, and the upgrade script does a change like ALTER TABLE or whatever, it can die. Unfortunately this creates a huge issue because you can't recover or continue from that point. I've had to jump through hoops to do upgrades by manually tearing apart the upgrade scripts and running each process manually in CLI. I rarely use upgrade script now because it breaks things.

[jonschneider]

The forum is very small (a dozen of users, less than fifty posts), I don't think the upgrade operations on the database take very long. The server load is also quite low, with plenty of free memory, so I doubt the upgrade process dies from resources starvation (there is nothing in the system logs pointing to this direction).

[SimonLewis]

My advice would to be to go through this and follow this tutorial.
https://docs.mybb.com/1.8/install/upgrade/

Also install folder you can remove it once your board is fully installed.