SQLite Rollback

[cbk1994]

When I restarted Era's NPC-server last night, the SQLite database seemed to roll back, causing a few problems, and a ton of people to get unjailed who were previously unjailed (it's searching WHERE release_on < timevar2).

Why did this happen, and how can we prevent it?

[fowlplay4]

Hope someone could shed some light on this, because it kinda makes me reconsider using it to store player and item data.

[cbk1994]

Originally Posted by fowlplay4

Hope someone could shed some light on this, because it kinda makes me reconsider using it to store player and item data.

Indeed, I was thinking the same thing. We store use SQL to store bank accounts on Era Dev, and this of course has to be completely flawless.

[Inverness]

Are you starting and committing your transactions?

[cbk1994]

Originally Posted by Inverness

Are you starting and committing your transactions?

I thought that using a requestsql(query, true) would automatically open then close a transaction, no?

I'm not opening any elsewhere.

[Pelikano]

I don't know, but in MYSQL you have to always start and end it

[fowlplay4]

Originally Posted by http://www.sqlite.org/lang_transaction.html

No changes can be made to the database except within a transaction. Any command that changes the database (basically, any SQL command other than SELECT) will automatically start a transaction if one is not already in effect. Automatically started transactions are committed when the last query finishes.

Would make believe that it begins, and commits automatically.

[Gambet]

Perhaps it's a problem with the timevar2 value? You should run some debug tests to isolate the problem (set up a similar test database where you track different actions and restart the server and see how that affects your database).

[salesman]

Originally Posted by Gambet

Perhaps it's a problem with the timevar2 value? You should run some debug tests to isolate the problem (set up a similar test database where you track different actions and restart the server and see how that affects your database).

No, because I have a system dealing with a 10 column table, but after the restart, the database rolled back to when the table only had 9 colums (I added a value) and was causing all kinds of problems. It was definitely a rollback and not just the jail system that experienced the problem.

[cbk1994]

For now I will add a system to automatically back up the SQLite database every hour or so, and have it automatically update the file on server restart, but this definitely needs to be fixed before we can start using it for more important things (bank accounts, for example).

[fowlplay4]

Hope we can get Stefan to shed some light on this situation.

[Admins]

Hmmm normally it should update the database file each time you insert, modify or delete data. We have disabled file locking because only one npcserver is accessing the database file at a time, and also because file-locking is not working with several file systems that we use. But may be that is causing problems with data not written back to the file, only kept in memory or so. Normally that can easily be checked by downloading the file and opening it with some sqlite database editor.

[Inverness]

Originally Posted by fowlplay4

Would make believe that it begins, and commits automatically.

Originally Posted by cbk1994

I thought that using a requestsql(query, true) would automatically open then close a transaction, no?

I'm not opening any elsewhere.

Maybe you people should learn how SQLite works before you start complaining about it on the forums.

http://www.sqlite.org/lang_transaction.html
http://www.sqlite.org/speed.html

I suggest you read those.