System.Data.SQLite

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 10000 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,

I think this sounds like an issue specific to the SpatiaLite
("http://www.gaia-gis.it/spatialite") extension.  Can you confirm this?

It's possible, that the issue is link to the underlying geospatial library. I am merely a "user". Should I do something specific to help pinpointing the issue?

Could you ask the developer of the software package you are using about this issue, pointing them to this ticket?

Sorry, I did not express myself properly. I am the developper of the routing application that has this exception issue. However, the spatial functions I am using are provided to be by the sqlite lib. That's why I said I am a "user" of the system.data.sqlite dll.

However, if I can take action to help pinpointing where the issue is, just let me know.

You said : mistachkin added on 2013-01-10 16:09:45: 
I think this sounds like an issue specific to the SpatiaLite
("http://www.gaia-gis.it/spatialite") extension.  Can you confirm this?

A quick look at the web site, let me think the issue is likely to be in this package, since I am using the spatial functions of sqlite.

Which extensions are you using, exactly?  Could you provide some example C# code and a schema that demonstrates the issue you are seeing?

Hi,

Which extensions are you using, exactly?
My code references system.data.sqlite.dll V1.0.84. There are no other database related references in my project. I use the spatial function available in this dll.

Could you provide some example C# code and a schema that demonstrates the issue you are seeing?
The full code is available on googlecode project name sbsrouteur.

The code that produce the issue is can be found in http://code.google.com/p/sbsrouteur/source/browse/Routeur/Classes/SQLLite/DBWrapper.vb starting at line 200

The database is very simple : 1 table with segments, 5 range indexes on the segment table (1 for each segment precision level)

Could you enable extra SQLite error logging and see if any other messages are
produced?

If you can rebuild the System.Data.SQLite assemblies in the "Debug" 
configuration, you should be able to see any log messages produced using a tool 
such as "DbgView", "dbmon", or Visual Studio itself (when "enable unmanaged code 
debugging" is enabled).

Could you try building and running the code on the [/info/tkt-d1fb769a8e|ticket branch]?  The name of the branch is "tkt-d1fb769a8e".

It has improved error messages for RTree that should help narrow down the cause of the error message you are seeing.

Hi,

I am retrieving the code, and will try building the lib and reference it from my project to see if I can get you more detailed information. What flag should I set to enable detailed logging in sqlite?

When creating the connection, you can set the connection flags by adding the
following to the connection string:

       "Flags=LogAll;"

This extra step might not be strictly necessary in this case; however, it could
prove to be useful.

When building System.Data.SQLite for testing, please be sure to use the "Debug"
build configuration as this will enable extra diagnostics and debugging code.

Hi,

I have tried the flags=all, but it seems that the flow of trace changes the flow of excecution, and the exception does not show up. I have rebuild the solution and run my app against your branch, I Can produce the exception the same. The exception comes in the middle of some DB locked exceptions.

I have run another test today : started the debugger on the dll using my app as a startup point. I produced the excpetion again. Here is the stack trace :

   à System.Data.SQLite.SQLite3.Prepare(SQLiteConnection cnn, String strSql, SQLiteStatement previous, UInt32 timeoutMS, String& strRemain) dans c:\Projets\01_Perso\HG_Routeur-google_code\SQLite\System.Data.SQLite\SQLite3.cs:ligne 644

I can't seem to see any more information than running the release app.

What should I do to enable more debug information? What more info do you need?

For the record a sample of the queries I am running. 
Select * from mapssegments inner join (  select id from MapLevel_Idx3 where  (MaxX  >= -13.359375 and MinX <=-13.0078125) and ( MaxY >=35.5078125 and MinY <=35.68359375) ) As T on IdSegment = id

From the looks of it, the behavior you are seeing is correct, though undesirable.  Some other threads are performing queries against the database, causing it to be locked.  When the CREATE VIRTUAL TABLE or other DDL queries are then executed on another thread, database locking issues result.

For more information on how SQLite handles locking, see:

[https://www.sqlite.org/lockingv3.html]