Re: [sqlite] Exception when querying a range index
Ok, I have updated my database with analyze so that stat1 and stat4 tables have been created. I also have added an index to a table, in order to get rid a an auto index warning after each query. It seems that now out of some "Database locked" errors, I am not getting the original error message anymore. I'll be monitoring that, and come back here if I find something weird again. Thanks all for your time and your input. Regards, -- Stéphane On Wed, Mar 12, 2014 at 12:36 AM, Richard Hipp wrote: > On Tue, Mar 11, 2014 at 6:59 PM, St. B. wrote: > > > HI, > > > > So I updated to V1.0.91 of SDS, but now I get the following error at each > > query run against the database : > > SQLite error (1): no such table: sqlite_stat1 > > > > What is the text of the query you are running? > > > > > > What should I do to get the table properly created? > > > > > > > > > > On Tue, Mar 11, 2014 at 12:50 PM, Simon Slavin > > wrote: > > > > > > > > On 11 Mar 2014, at 11:06am, St. B. wrote: > > > > > > > There is one writing thread (every 5 minutes). The one writing thread > > > > writes in the same database, but it is on a different table. > > > > > > Have you set a timeout for all your database handles (or just the one > > > handle if they're all using the same handle): > > > > > > <http://www.sqlite.org/c3ref/busy_timeout.html> > > > > > > Many problems disappear if you set a timeout of ten seconds. > > > > > > Simon. > > > ___ > > > sqlite-users mailing list > > > sqlite-users@sqlite.org > > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > ___ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > -- > D. Richard Hipp > d...@sqlite.org > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Exception when querying a range index
HI, So I updated to V1.0.91 of SDS, but now I get the following error at each query run against the database : SQLite error (1): no such table: sqlite_stat1 What should I do to get the table properly created? On Tue, Mar 11, 2014 at 12:50 PM, Simon Slavin wrote: > > On 11 Mar 2014, at 11:06am, St. B. wrote: > > > There is one writing thread (every 5 minutes). The one writing thread > > writes in the same database, but it is on a different table. > > Have you set a timeout for all your database handles (or just the one > handle if they're all using the same handle): > > <http://www.sqlite.org/c3ref/busy_timeout.html> > > Many problems disappear if you set a timeout of ten seconds. > > Simon. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Fwd: Exception when querying a range index
On Tue, Mar 11, 2014 at 11:13 AM, RSmith wrote: > > On 2014/03/11 11:58, St. B. wrote: > >> I still have a question. Since I have many threads (between 100 and 200) >> that do reading on the table that has the R Tree, and 1 thread that will >> write to another table once every five minutes, is it normal that I get >> database is locked error on a regular basis? I was under the impression >> that read only is lock free in when doing multiple access, and that locks >> should only occur during the writes. >> > > So to be sure - You have no threads at all writing to this DB? Just 200 > that read often and 1 that reads every 5 minutes (and then writes to a > completely different DB)? > There is one writing thread (every 5 minutes). The one writing thread writes in the same database, but it is on a different table. > > You cannot get locked errors then. Unless maybe some of these reads happen > inside a transaction that is exclusive. Or a pragma that locks or does some > function that requires a lock is called. > > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Fwd: Exception when querying a range index
Hi Dan, Thanks for this answer, I will update the SDS to the latest version to see how it goes, and if we get more explicit. I still have a question. Since I have many threads (between 100 and 200) that do reading on the table that has the R Tree, and 1 thread that will write to another table once every five minutes, is it normal that I get database is locked error on a regular basis? I was under the impression that read only is lock free in when doing multiple access, and that locks should only occur during the writes. Regards, On Tue, Mar 11, 2014 at 10:30 AM, Dan Kennedy wrote: > On 03/11/2014 04:17 AM, St. B. wrote: > >> Hi, >> >> >> On Mon, Mar 10, 2014 at 6:37 PM, Dan Kennedy >> wrote: >> >> On 03/10/2014 06:49 PM, St. B. wrote: >>> >>> Hi, >>>> >>>> I posted this question last year, but did not get any feed back. Is this >>>> something that can be handled on the ML, or should I ask in another >>>> place? >>>> >>>> Regards, >>>> >>>> -- Forwarded message -- >>>> From: St. B. >>>> Date: Sun, Jan 6, 2013 at 11:45 PM >>>> Subject: Exception when querying a range index >>>> To: sqlite-users@sqlite.org >>>> >>>> >>>> Hi All, >>>> >>>> I have an application using sqlite to store coast lines (application >>>> runs >>>> on windows and uses the .Net version of sqlite v1.0.83). I use range >>>> indexes to look-up coast line within range of a given point. >>>> >>>> During run time I find that the following exception occurs. >>>> >>>> vtable constructor failed: MapLevel_Idx3 >>>> >>>> How is virtual table MapLevel_Idx3 created? Is it an rtree table? >>> >>> The virtual table is created with the following sql statement: >> >> CREATE VIRTUAL TABLE MapLevel_Idx4 USING rtree( >> id, -- Integer primary key >> minX, maxX, -- Minimum and maximum X coordinate >> minY, maxY -- Minimum and maximum Y coordinate >> ); >> >> Yes it is an rtree table >> >> SQLite version? >>> >>> I am using system.data.SQLite v 1.0.84.0. I guess it includes sqlites >> in v >> 3.7.6.3 (but am not sure at all) >> > > If it's possible, you should upgrade to a newer version of SDS. 3.7.6.3 > is almost three years old now. > > The "vtable constructor failed" message tells us that an error occurred > while trying to initialize the virtual table instance, but it doesn't > tell us any more than that. However, the error messages returned by the > rtree module have improved a lot since 3.7.6, so I fancy that if you > upgrade the new version will spit out a more specific complaint. > > For example, I just tested the effects of encountering an SQLITE_BUSY > while initializing the rtree instance. With 3.8.4 the error message is > "database is locked". But with 3.7.6 all you get is "vtable constructor > failed". > > There is a good chance that this is what is happening in your environment > as well - the db is locked and the error message is deceptive. In which > case you should just retry the operation or add a busy-handler to the > database connection. But upgrading first is the safer option. > > Dan. > > > > > > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Fwd: Exception when querying a range index
Hi, On Mon, Mar 10, 2014 at 6:37 PM, Dan Kennedy wrote: > On 03/10/2014 06:49 PM, St. B. wrote: > >> Hi, >> >> I posted this question last year, but did not get any feed back. Is this >> something that can be handled on the ML, or should I ask in another place? >> >> Regards, >> >> -- Forwarded message -- >> From: St. B. >> Date: Sun, Jan 6, 2013 at 11:45 PM >> Subject: Exception when querying a range index >> To: sqlite-users@sqlite.org >> >> >> Hi All, >> >> I have an application using sqlite to store coast lines (application runs >> on windows and uses the .Net version of sqlite v1.0.83). I use range >> indexes to look-up coast line within range of a given point. >> >> During run time I find that the following exception occurs. >> >> vtable constructor failed: MapLevel_Idx3 >> > > How is virtual table MapLevel_Idx3 created? Is it an rtree table? > The virtual table is created with the following sql statement: CREATE VIRTUAL TABLE MapLevel_Idx4 USING rtree( id, -- Integer primary key minX, maxX, -- Minimum and maximum X coordinate minY, maxY -- Minimum and maximum Y coordinate ); Yes it is an rtree table > > SQLite version? > I am using system.data.SQLite v 1.0.84.0. I guess it includes sqlites in v 3.7.6.3 (but am not sure at all) > > Dan. > > > > > > >> Occurence ratio is about 1 in 1 queries on the range index. Am I doing >> something wrong, or is this supposed to be so? When the exception occurs, >> I >> just drop the resultset and assume that no data was to be found. Should I >> restart the query? >> >> Regards, >> >> -- >> >> S. Barthes >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Fwd: Exception when querying a range index
Hi, I posted this question last year, but did not get any feed back. Is this something that can be handled on the ML, or should I ask in another place? Regards, -- Forwarded message -- From: St. B. Date: Sun, Jan 6, 2013 at 11:45 PM Subject: Exception when querying a range index To: sqlite-users@sqlite.org Hi All, I have an application using sqlite to store coast lines (application runs on windows and uses the .Net version of sqlite v1.0.83). I use range indexes to look-up coast line within range of a given point. During run time I find that the following exception occurs. vtable constructor failed: MapLevel_Idx3 Occurence ratio is about 1 in 1 queries on the range index. Am I doing something wrong, or is this supposed to be so? When the exception occurs, I just drop the resultset and assume that no data was to be found. Should I restart the query? Regards, -- S. Barthes ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Need pointers for better build integration and support of schema updates
Hi, I have run into a similar issue for a hobby application of mine. If you are interested, you can get a look at the code. -Database SQL DDL is included in the source as string resources (fits the scource control & install requirement) -Build (don't know what you expect there, you may need to add some stuff) - Database has a "version table" to handle installing the proper htings in the proper order - Each update change dbversion. Upgrades are run one after another in the proper sequential order. Should be transacted and proeprly aborted, i do not remember if I handle that in the code - version detection : see above Regards, -- Stephane On Fri, Jan 31, 2014 at 5:07 PM, Andreas Hofmann wrote: > Sorry if some of these are no brainers, I am a sqlite newbie with some MS > SQL experience. > > > > Our application uses 3 Sqlite dbs and System.Data.Sqlite.dll (dropped by > installer). Functionality allows users to start a new instance of one of > the > databases at run time. Hence we ship empty databases in a binary format, > created by one of our developers. The process of making changes to the DDL > is basically using a sqlite admin tool, make the changes, then update the > installer with the new db file. I am looking for a better way how to > automate this and have better source control integration. At my other job, > we got MS SQL ddl source files as text files in source control, verify them > at build time, and then ship the scripts. > > > > I would like accomplish these (or at least some of these) for the Sqlite > dbs: > > > > - text files for sql in source control > > - build them from command line during msbuild for syntax verification > > - ship text files in the installer (I know how to do that) > > - run the text files against a newly created db at run time in order > "install" the schema and data (I think this is just a simple execute the > sql > ddl agains the db, right?) > > - support schema updates. If V2 of the product ships, all dbs that are > older should automatically be migrated to V2 when loaded. Is this as > simple > as writing the ddl as IF COLUMN EXISTS etc. statements? > > - version detection. At run time need to detect that a given db is of > version v1. simple column in dbversion table? > > > > Any pointers would be appreciated. > > > > Thanks > > Andy > > > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SELECTing from another SELECT
SELECT * FROM A WHERE projid in (SELECT projid FROM B WHERE ptask = 'a'); will probably fill the bill. If I where to run your query, I would do a select A.* from A inner join B on A.a = b.ProjId where b.ptask='a' the join may optimize better than the in (select ...) On Thu, Jan 23, 2014 at 8:11 PM, jose isaias cabrera wrote: > > Greetings! > > I have these tables A and B: > A > id,projid,a,b > 1,1,'a','h' > 2,2,'b','i' > 3,3,'c','j' > 4,4,'d','k' > 5,5,'e','l' > ... > ... > > > B > id,projid,ptask > 101,1,'a' > 102,2,'b' > 103,3,'a' > 104,4,'b' > 105,5,'a' > ... > ... > > When I do this SELECT, > > SELECT * FROM A WHERE projid = (SELECT projid FROM B WHERE ptask = 'a'); > > This only returns one record (record 1) where it should return all the > records with ptask = 'a'. Will someone point me to the right syntax to get > this correct? Thanks for the help. > > josé > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Exception when querying a range index
Hi All, I have an application using sqlite to store coast lines (application runs on windows and uses the .Net version of sqlite v1.0.83). I use range indexes to look-up coast line within range of a given point. During run time I find that the following exception occurs. vtable constructor failed: MapLevel_Idx3 Occurence ratio is about 1 in 1 queries on the range index. Am I doing something wrong, or is this supposed to be so? When the exception occurs, I just drop the resultset and assume that no data was to be found. Should I restart the query? Regards, -- S. Barthes ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
