Re: [sqlite] database is locked

Use the following code snippet (add error checking, set v_file to the full path name of your SQLite db file) to check. It attempts to take the same locks as SQLite would, but prints the pid of the blocking process. It also prints the journal mode. #include #include #ifdef AIX64 #include

Re: [sqlite] Best Practice: Storing Dates

Thank you for all the responses to my original post. They were all very helpful in formulating my position/solution: Unless you deal with financial services, Facebook, Twitter type systems processing multi-million / billions of records or high volumes / TPS that would be hampered by text/string

Re: [sqlite] using a hex integer as input to DateTime

If you're accessing SQLite from your own program, adding a hextoint function would be pretty easy. Even if you were using sqlite3, defining your own extension isn't that hard (and is documented on the SQLite website to some extent). That would allow you to do most of the things you've been

Re: [sqlite] using a hex integer as input to DateTime

On 1/14/15, Paul Sanderson wrote: > Thanks all > > I am running the latest version :) > > I am just getting back to this and have a related problem > > I have a table > > create table (base int, hex text) > > and I want to create a trigger such that if hex is updated

Re: [sqlite] using a hex integer as input to DateTime

Thanks all I am running the latest version :) I am just getting back to this and have a related problem I have a table create table (base int, hex text) and I want to create a trigger such that if hex is updated (or a new row inserted) with a text string in the form 0x12345abcd this value is

Re: [sqlite] Best Practice: Storing Dates

On 14 Jan 2015, at 10:40pm, Baruch Burstein wrote: > Of course, this is just at the theoretical level. As yo said, your app > probably wouldn't need to worry about this. I think a previous poster had it right. If you need to do lots of maths with the timestamps store

Re: [sqlite] Best Practice: Storing Dates

On Wed, Jan 14, 2015 at 3:09 PM, Chris Keilitz wrote: > Since sqlite and most RDMS implementations have functions to convert to and > from both options and using a LONG should allow the date/time to function > way past 2038, it seems it comes down to how many bytes it takes to

Re: [sqlite] Best Practice: Storing Dates

For the data collection systems we use we store only UTC in the database. The application can translate times to the appropriate time zone and format for the user as required. This variable complexity needs to be controlled into one layer of your program. Since governments, even some city ones,

Re: [sqlite] Best Practice: Storing Dates

Whatever format you choose to store it in, I highly recommend storing the UTC time. It might be a little more work, but: 1. your program can display the correct local time, even if the database/app/user is in/changes to another timezone 2. you won't have to deal with seeing two 1:30am on the

Re: [sqlite] database is locked

Thank you!! Roman From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Richard Hipp [d...@sqlite.org] Sent: Wednesday, January 14, 2015 1:31 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] database is

Re: [sqlite] database is locked

On 1/14/15, Roman Fleysher wrote: > Thank you, Richard. > > You are correct, I made a typo: we have NFS not NTFS and I know they are > buggy. I always use the same node on our compute cluster to minimize > buffering issue. So, are you saying I can not clear the

Re: [sqlite] database is locked

Thank you, Richard. You are correct, I made a typo: we have NFS not NTFS and I know they are buggy. I always use the same node on our compute cluster to minimize buffering issue. So, are you saying I can not clear the database lock and must rebuild the database? Roman

Re: [sqlite] database is locked

On 1/14/15, Roman Fleysher wrote: > SQLite shell version 3.7.2 > on Linux 2.6.18 > NTFS On Linux, SQLite uses posix advisory locks. All locks are automatically released when the process dies (if they haven't been already). If you have stuck locks, that indicates

Re: [sqlite] Best Practice: Storing Dates

On 14 Jan 2015, at 5:53pm, Nigel Verity wrote: > I generally just use a fixed-length 14-character string to store the date and > time in MMDDHHMMSS format. It accommodates any time stamp across a 10,000 > year timespan and also supports simple date/time

Re: [sqlite] database is locked

On 14 Jan 2015, at 5:30pm, Roman Fleysher wrote: > Is there a way to figure out what is happening? Clear the lock? We would need specifics of your system to answer this absolutely correctly. But you can try these things in turn until one of them works:

Re: [sqlite] database is locked

SQLite shell version 3.7.2 on Linux 2.6.18 NTFS From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Richard Hipp [d...@sqlite.org] Sent: Wednesday, January 14, 2015 12:50 PM To: General Discussion of SQLite Database

Re: [sqlite] Best Practice: Storing Dates

Hi Interesting discussion on the best way to store date/time fields. Unix time certainly has advantages for portability but, I agree, it can be a pain to convert, depending on which applications and tools you are interfacing with. ISO8601 is fine if you just need a local time stamp, but even

Re: [sqlite] database is locked

On 1/14/15, Roman Fleysher wrote: > Dear SQLiters, > > There has been a lot of discussion, I remember, on this subject by others. > Please forgive me for asking this for a millionth time. > > I somehow got my database in a locked state. I updated a table yesterday

Re: [sqlite] Huge WAL log

On 14 Jan 2015, at 3:36pm, Jan Slodicka wrote: > - WAL log size 7.490 GB Please repeat your tests but as the first command after opening your database file issue PRAGMA journal_size_limit = 100 With this change the WAL file may still grow to 7 GB while that particular

Re: [sqlite] Huge WAL log

On 1/14/15, Jan Slodicka wrote: > Richard Hipp-3 wrote >>> No other active readers or writers. >> >> Are you sure? > > Writers for sure. > > As far readers are concerned, the things are too complex to make an > absolute > statement. (I shall check once more.) However, I can add a

[sqlite] database is locked

Dear SQLiters, There has been a lot of discussion, I remember, on this subject by others. Please forgive me for asking this for a millionth time. I somehow got my database in a locked state. I updated a table yesterday and I am rather sure that no one on our multi-user system is updating it

Re: [sqlite] Huge WAL log

Richard Hipp-3 wrote >> No other active readers or writers. > > Are you sure? Writers for sure. As far readers are concerned, the things are too complex to make an absolute statement. (I shall check once more.) However, I can add a few observations I made: WAL file size was about 70 MB (as

Re: [sqlite] Huge WAL log

On 1/14/15, Jan Slodicka wrote: > I understand that the WAL log uses less efficient storage rules than the > real > database, but this case was a real surprise for me. Here is the brief > description. > > We start from an empty database, create a few tables (each having a few >

[sqlite] Huge WAL log

I understand that the WAL log uses less efficient storage rules than the real database, but this case was a real surprise for me. Here is the brief description. We start from an empty database, create a few tables (each having a few indexes), then begin a transaction, do a couple of inserts into

Re: [sqlite] Best Practice: Storing Dates

On 1/14/15, Stephan Beal wrote: > On Wed, Jan 14, 2015 at 2:09 PM, Chris Keilitz wrote: > >> Since sqlite and most RDMS implementations have functions to convert to >> and >> from both options and using a LONG should allow the date/time to function >>

Re: [sqlite] Best Practice: Storing Dates

On Wed, Jan 14, 2015 at 2:09 PM, Chris Keilitz wrote: > Since sqlite and most RDMS implementations have functions to convert to and > from both options and using a LONG should allow the date/time to function > way past 2038, In my experience, having the timestamp in Unix

[sqlite] Best Practice: Storing Dates

I'm a new sqlite user and new to this mailing list. I hope this question is appropriate. I am writing an application that needs to track a timestamp - date + time down to the seconds. I'd like to store the date/time in a standard, efficient, usable, portable format. I have looked over the sqlite

Re: [sqlite] .NET System.Data.SQLite, how trap SQLITE_BUSY_SNAPSHOT (WAL) and retry

On 13 Jan 2015, at 10:53pm, Andreas Hofmann wrote: > Ideally, SQLite should retry itself, but I am not sure if this is supported. SQLite supports it just fine.

[sqlite] .NET System.Data.SQLite, how trap SQLITE_BUSY_SNAPSHOT (WAL) and retry

Hi, I am using WAL. If I use a new db connection for every write (as usually suggested practice) and connection pooling is enabled, my writes are quite a bit faster. Good so far. However, I also see some db locks occurring, specifically: (517) SQLITE_BUSY_SNAPSHOT For some reason, the

Re: [sqlite] help with query

Maybe something like this would work for you: SELECT * FROM table WHERE data1 IN (SELECT data1 FROM table GROUP BY data1 HAVING count(*)>=3); ~snowbiwan -- View this message in context: