Upgrade from 0.2.7 flat to 0.4.4 - database table is locked

[MacJudge]

Hi.

I'm using Baïkal 0.2.7 flat package for two years. Now I try to upgrade to 0.4.4, but always get an error:

Uncaught exception during upgrade: exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 6 database table is locked' in /baikal/Core/Frameworks/BaikalAdmin/Controller/Install/VersionUpgrade.php:337 Stack trace:

0 /baikal/Core/Frameworks/BaikalAdmin/Controller/Install/VersionUpgrade.php(337): PDOStatement->execute(Array)

1 /baikal/Core/Frameworks/BaikalAdmin/Controller/Install/VersionUpgrade.php(61): BaikalAdmin\Controller\Install\VersionUpgrade->upgrade('0.2.7', '0.4.4')

2 /baikal/Core/Frameworks/Flake/Core/Render/Container.php(71): BaikalAdmin\Controller\Install\VersionUpgrade->render()

3 /baikal/Core/Frameworks/Flake/Controller/Page.php(85): Flake\Core\Render\Container->renderBlocks()

4 /baikal/html/admin/install/index.php(83): Flake\Controller\Page->render()

5 {main}

Successful operations:

synctoken was added to calendars calendarchanges was created synctoken was added to addressbooks addressbookchanges was created calendarsubscriptions was created etag and size were added to cards uid was added to calendarobjects schedulingobjects was created propertystorage was created

If I retry the action (reload the page), I get an other error because of the unfinished upgrade process:

Uncaught exception during upgrade: exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 1 duplicate column name: synctoken' in /baikal/Core/Frameworks/BaikalAdmin/Controller/Install/VersionUpgrade.php:255 Stack trace:

0 /baikal/Core/Frameworks/BaikalAdmin/Controller/Install/VersionUpgrade.php(255): PDO->exec('ALTER TABLE cal...')

1 /baikal/Core/Frameworks/BaikalAdmin/Controller/Install/VersionUpgrade.php(61): BaikalAdmin\Controller\Install\VersionUpgrade->upgrade('0.2.7', '0.4.4')

2 /baikal/Core/Frameworks/Flake/Core/Render/Container.php(71): BaikalAdmin\Controller\Install\VersionUpgrade->render()

3 /baikal/Core/Frameworks/Flake/Controller/Page.php(85): Flake\Core\Render\Container->renderBlocks()

4 /baikal/html/admin/install/index.php(83): Flake\Controller\Page->render()

5 {main}

I tried to do this several times in the last days with different states of my SQLite database, but nothing changed. I also tried to upgrade to 0.3.5 first, as some user suggested in another thread, but got the same results.

My last try was to make a clean copy of the database by doing

echo ".dump" | sqlite3 olddb.sqlite | sqlite3 db.sqlite

but I get the same results with this.

Any other suggestions?

[evert]

I think the most likely is that a client is accessing your server, while you are simultaneously trying to upgrade. You might want to try making sure that only you can access the server (maybe with a firewall rule, or apache access rule).

Let me know if that works!

[MacJudge]

I don't think there was some other client accessing the server, because I used a new test-domain, that no client knew about. The SQLite-database file was copied over several computers, so no file-system-lock or something similar could have been there. There was only my Firefox accessing the web-page to perform the upgrade-steps.

Finally, I just ignored the error and used the (partially?) converted database with the config-files from a fresh install and the admin interface now shows me almost the same information like the old version. Therefor, I tried to access the test-server with my Thunderbird/Lightning as I do with the old version, but it doesn't work. Unfortunately, it also doesn't work with a fresh database, although I'm quite sure I did the same configuration steps and hacks as I did with the old version. So I can't test, whether my incomplete upgrade might work.

If I find the time, I will try to access the server with some other client or do some research on how to configure the new server to work with Thunderbird/Lightning.

[evert]

The weird part is basically that that error is a SQLite specific error, indicating that something else is messing with the data. Copying from server to server doesn't really change this though. File-system locks are (as far as I know) mostly a thing on Windows. SQLite would have its own mechanism for this.

You could try to dump your entire sqlite database and re-create it. That should get rid of the locks for sure.

[MacJudge]

I think I already dumped and re-created the database by doing

echo ".dump" | sqlite3 olddb.sqlite | sqlite3 db.sqlite

on a Linux-machine but this didn't help as I wrote in my first post.

[evert]

Ah I'm sorry, I was certain that that would have actually solved the problem. I'm not really sure then where else to look :/

[c-mauderer]

I have the same problem. I tried upgrading from 0.2.7 to 0.4.5 directly or alternatively upgrading from 0.2.7 to 0.3.5.

I had to change the PHP-Version. Baikal 0.2.7 worked fine with PHP 5.3. For Baikal 0.4.x the minimum version is PHP 5.5 according to http://sabre.io/baikal/install/. So I changed the version. Can that cause the trouble?

Have you found a solution?

Edit: I stopped all clients before I tried to upgrade. The database dump and re-import also didn't work.

[evert]

I personally have no clue what could cause this. I've never seen this and as far as I know, the only situation where this can happen is if something has the sqlite database open while you are attempting to do the upgrade.

If you're absolutely sure there is no DAV client at all, and no browser open accessing baikal, it should work, but if it doesn't... I don't have a clue how to debug this :/

[c-mauderer]

OK. Thanks for the answer.

I searched for some time and found this site: http://beets.io/blog/sqlite-nightmare.html

It sounds like a similar problem even if I'm quite sure that the message does not need 5 seconds to appear. Despite that I would like to test it with an increased timeout value. Do you have any Idea how that could be added without to much effort? If it would be a lot of effort for you, I'll try to find that out for myself. But I think that you have a lot more experience with SQLite and PHP.

[c-mauderer]

I found a more likely explanation:

In an older version of the documentation of SQLite there is an explanation for the Error Code SQLITE_LOCKED (6): http://www.sqlite.org/cvstrac/wiki?p=DatabaseIsLocked

According to this, the error can occur when "Trying to write to a table while a SELECT is active on that same table." This seems to be fixed since 2006. I'm not sure, what version my webhoster is using, but about two years ago, the machine was quite likely based on a CentOS 5.10 (or probably Red Hat Enterprise with the fitting version). Even if CentOS 5.0 is from 2007, the support ends 2017. So It's quite likely that they still use the CentOS 5 branch with an old SQLite version.

The error appears in the following line: https://github.com/fruux/Baikal/blob/0.4.5/Core/Frameworks/BaikalAdmin/Controller/Install/VersionUpgrade.php#L337

It seems to me that at this line a change in the table "cards" is made while a query in "cards" is still active. That fits to the error described in the old sqlite manual.

I'll try to find out what version of SQLite is used on my webspace.

Is there any way to upgrade the database without using the VersionUpgrade.php on the server?

[evert]

The core point here is, and both your threads point to this @c-mauderer , is that this problem occurs when we're doing modifications in the sqlite database while something else is as well (or is doing a select).

PHP only does one thing at a time, so in theory this can only happen if something else also interfaces with baikal at the same time.

Perhaps you could share some of your apache error logs from your server during the time you are doing the upgrade?

[c-mauderer]

I checked the logs: It seems that there is nothing useful in it. I only get some messages that my favicon.ico could not be found. The access log shows that about 20 minutes before I accessed "/admin/" nothing has been accessed.

The disadvantage when just booking web space and not a dedicated server is that I don't have much influence on the logging level. So I probably can't get any more messages.

Are you sure that the query in line 330 is finished before the one in line 333 will be executed?

[evert]

@c-mauderer , yes absolutely. In PDO queries everything is synchronous, so the first query has to fully complete before the next one starts.

[c-mauderer]

I've found out the version of SQLite that is installed on the server of my webhoster: 3.3.6 So the paragraph in the linked SQLite documentation is relevant.

For the query in PDO: I'm sorry but I'm still not convinced: The PDO documentation says the following (http://php.net/manual/de/pdo.query.php):

If you do not fetch all of the data in a result set before issuing your next call to PDO::query(), your call may fail. Call PDOStatement::closeCursor() to release the database resources associated with the PDOStatement object before issuing your next call to PDO::query().

It seems to me that it only returns a handle to some internal construct. Otherwise you wouldn't have to use the $result->fetch() function to access the next row but just use an array index. So I'm not sure if the SELECT is finished.

If it is the problem I should be able to work around it by just fetching all results into an array right before the while loop in line 332 and iterate over the array instead of the PDO result handle. If this works, it would support my theory. If it doesn't change anything, I'll believe you ;-)

I'll report back as soon as I've tried it.

[evert]

alright, I'm very curious if that solves it then!

[c-mauderer]

I changed the query. You can find my new version here: https://gist.github.com/c-mauderer/fe24d58980477e344a907e85ac81d683#file-versionupgrade-php-L331 This solved at least this problem. But the same error message appears on line 360 of the new file.

I'm not sure how much Baikal changed between 0.2.7 and 0.4.5 but I would assume that a similar construction is used at further places in the code? That would mean that The current version cant be used with the ancient SQLite version that my hoster offers. If that is so, you should think about adding a version check and / or a line to the requirements that SQLite >= 3.3.7 is necessary.

As a fallback solution for my special case: Is there any simple way to migrate an existing SQLite database to a MySQL database?

[c-mauderer]

I found the same construct two additional times in VersionUpgrade.php. I changed them too in the gist. Now at least the update works: https://gist.github.com/c-mauderer/fe24d58980477e344a907e85ac81d683

I'll try to find out if there are any other problems with 0.4.5 and my unfortunately ancient SQLite.

[c-mauderer]

Seems that there are some more problems with the update. But I'm quite sure that they are not related to this one. Now I get a lot of errors 400 or 401 in my access_log along the line of "PROPFIND /cal.php/calendars/christian/default/". Even if I try to use the new "dav.php" URL. But like I said: I don't think that is related to the problem with the old SQLite version so I'll try to not flood this thread with it any further.

[MacJudge]

Thanks, Christian, for your investigation. I found out, that my webhoster is using the same SQLite-version like you: 3.3.6 As I already wrote, I have problems accessing the new system, too. So if you find a solution, that works for you, just let me know. Perhaps we are using the same hoster.

Edit: I just checked it and we are in fact using the same webhoster. :)

[c-mauderer]

@MacJudge: Yes, Daniel, I agree. It is the same webhoster: celeros.de

After you have the same problem, I might use the thread for further discussion. I hope that is OK for you both @evert and @MacJudge?

I tested a lot of different combinations and configurations with fresh installations: On a 0.2.7 flat installations, everything works fine. When I use some of the newer versions (I tested 0.3.3, 0.3.5, 0.4.5) than I get an error. I used exactly the same server configuration (I just renamed the directory without touching the config). Both times it has been an PHP 5.5.4

The problem appears when my client (Thunderbird with CardBook) sends out the following request: PROPFIND /card.php/addressbooks/test/default/ HTTP/1.1

For the 0.2.7 version, the server reacts with an HTTP/1.1 207 Multi-Status while on the newer versions I get an HTTP/1.1 400 Bad Request

I'm running out of ideas what can cause such a behavior. Any hints where to search?

Celeros is using an nginx / Apache combination. nginx should serve static request, Apache should do the rest. I get the following Apache messages in the access-log: [04/Jun/2016:12:14:56 +0200] "PROPFIND /card.php/addressbooks/test/default/ HTTP/1.0" 400 679 "-" "Thunderbird Lightning/4.7.1" I can't find any more error messages in the logs regarding this problem.