switch to a real database

[reven]

getting sick of sqlite db file being locked.

opening "unlocker" it shows i have about 15 access from NRecord on the db, around 8 for NDigitalHost and thats it. no webserver or anything (I stopped IIS a few days ago as I thought this was causing the issue).

its just a stupid database, which locks itself if theres a journal open, if one thing tries to write to the database and then that process craps out and doesnt close it off, then the db is locked and you have to kill all processes to release it.

pita db,

Code:

2012-11-25 15:49:22.674    [ERROR][56]    Unexpected error in Stream.Channel(): System.Data.SQLite.SQLiteException: The database file is locked
database is locked
   at System.Data.SQLite.SQLite3.Prepare(SQLiteConnection cnn, String strSql, SQLiteStatement previous, UInt32 timeoutMS, String& strRemain)
   at System.Data.SQLite.SQLiteCommand.BuildNextCommand()
   at System.Data.SQLite.SQLiteCommand.GetStatement(Int32 index)
   at System.Data.SQLite.SQLiteDataReader.NextResult()
   at System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd, CommandBehavior behave)
   at System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.SQLite.SQLiteCommand.ExecuteNonQuery()
   at System.Data.SQLite.SQLiteConnection.Open()
   at NUtility.DatabaseHelper.GetConnection()
   at NUtility.Channel.LoadForCaptureSource(Int32 forCaptureSourceOID, Int32 channelOID)
   at NUtility.Channel.LoadAll()
   at NShared.Extend.ChannelRequestHandler.GetListings(Dictionary`2 parameters)
2012-11-25 15:49:22.674    [DEBUG][56]    <?xml version="1.0" encoding="utf-8" ?>

using sqlce or localdb or something that doesnt need the user to install anything, but is still a more real database and can handle multiple connections correctly.

[sub]

I hate always saying no, but sometimes you guys ask for big changes. ...

It's unlikely we'd move to something else. SQLite has served us very well up until now, and it'd be a major exercise to move away from it.

That said we already have several processes sharing the database without locking problems. The only times we've had locking problems in the past have been when we've had buggy code that has left the database resources in use. Is there a sequence of events that you're seeing that causes this problem?

[reven]

no real sequence, just happens at random points during the day. thought it was my website polling for device status, but as I said, I've stopped that a couple of days ago and still happening.

just seems a busy server with lots of requests, when something goes wrong ( I was rescanning the EPG during the last instance, never during any others) and db file locked, entire npvr goes down, no clients can talk to npvr, nothing responds and I have to kill everything.

the problem is something crashed while making a journal file, then just left the db file in a locked state.

I did put this in the "wishlist" section just would make this a lot more robust and failsafe if it was using a real db that didnt lock the db when writing to it and could handle connections/transactions that went pear shaped.

if you give me access to the db layer code, i could do the work for you

and with a real db you could move a lot of the stuff to stored procedures

[sub]

if you give me access to the db layer code, i could do the work for you

Sorry, I'm really not interested in moving away from SQLite. I like the simplicity. Also the database code couldn't be easily supplied independently of the rest of the code.

and with a real db you could move a lot of the stuff to stored procedures

God...never going back there. I've done years of development with stored procedures on Sybase and SQL Server (and a to a lesser degree with Oracle). I'm ok with doing it for with my day job, but I don't want to do it on a project I'm doing for fun.

As I said above though, I'm sure it's probably something small tripping you up though, rather than SQLite just being a steaming pile of crap. I've had no SQLite locking issues with multiple processes. If you can produce a simple c# project which does this same type of polling, and reproduces the locking, send me the code and I'll run it up here to find what is happening.

[mvallevand]

I've never had any of these locking issues and I make NextPVR database calls all the time.

I think the reasons for the calls for the big stuff is my change in how I see NextPVR being used. The PC host client is great, the NMT's still work well but they are really are PC hosts too. Now that I've gone headless I don't have the luxury of running direct so the database seems really slow, and live TV just doesn't work with the PC remote client so I am stuck with XBMC as a client. For the most part it is ok but I want it to be as great as the PC host client I used to use.

Martin

[sub]

Now that I've gone headless I don't have the luxury of running direct so the database seems really slow

What database related activities are you finding slow?

The only database activity that I'm anticipating would be slow from a client PC is loading guide info. I could probably do something about that one. Is there any other areas you suspect are slow? I could add some logging around them to check how long is taken by the database queries.

[reven]

well it would be something inside npvr itself, since nothing im doing is talking to the database directly (except for some unit tests which just setup a dummy database for testing).

I rather use stored procedures any day than hardcoded sql strings. i find them easier to maintain and detect issues like column name change/removal etc, but thats my personal preference.

all i know something is crashing, leaving the db file locked and its something inside npvr, my reason for wanting a real db, is if something does crash, it should be able to fail safely and not take the entire app down. just really dont like how something can lock the db and you're stuffed. thats just a terrible idea IMO.

but end of the day its your decision, in the wishlist for future thought. *cough*localdb*cough*

[sub]

I rather use stored procedures any day than hardcoded sql strings. i find them easier to maintain and detect issues like column name change/removal etc, but thats my personal preference.

Ok, we'll put that one down to personal preference. I know they're good for somethings, but they also really complicate some stuff - I don't want to give people database upgrade scripts when I provide patches where I want to change queries etc. I'd rather have it all self contained and simple.

all i know something is crashing, leaving the db file locked and its something inside npvr, my reason for wanting a real db, is if something does crash, it should be able to fail safely and not take the entire app down. just really dont like how something can lock the db and you're stuffed. thats just a terrible idea IMO.

If you can give me something I can run that'll eventually produces the problem, I'm happy to look into it for you. We're not seeing the problem elsewhere in NextPVR, so there is no reason you should have to grapple with it.

[reven]

yeah im trying to narrow it down, not sure what the exact steps are, my setup is basically
1 nextpvr win7 server with quad DVB-S2 tuner
3 raspberry pis running xbmc using xbmc addon (from raspbmc)
2 openelec machines with xbmc addon

tv is pretty much always streaming to 1 or 2 clients when people are home. recordings are happening very frequently. last time i got the lock was someone was watching live tv, and i did a epg empty/update because my epg was missing for all no xmltv channels for some reason.

[jcjefferies]

Its been many months since I last saw a "Database Locked" error but I expect my overnight reboot clears some problems. However I don't know if its my setup but opening "Recordings" on my server seems slow. If it hasn't been opened recently it takes about 17 seconds to display "What's New". Doing the same thing on the client always seems to take about 2 seconds.