Re: [sqlite] Critical issue

[Cyrille]

> Alternatively, you could recompile SQLite.Interop.dll to use the
> static CRT library (/MT).
Could you please let me know how to proceed to do this?

Thanks again,
Regards,
Cyrille


Le 22/06/2011 21:48, Random Coder a écrit :
> On Wed, Jun 22, 2011 at 10:57 AM, Cyrille  wrote:
>> As you said, I have been surprised by the result. One of my users copied
>> the missing DLL indicated by Depends (IEShims.dll) to the application
>> folder and Depends indicates now another missing DLL: ieframe.dll which
>> seems to be again linked to IE.
>> Well, I have the feeling that the only possible solution so far is to
>> follow your other advice: adding the IE folder to the environment path.
>> Could you please explain me how to proceed?
>>
>> Finally, I still very puzzled how the difference between the DLL error
>> indicated by Depends and the DLL which cannot be found according to the
>> .NET error message raised while launching the application.
>> If the .NET raised an error message: "System.DllNotFoundException:
>> Impossible to load the DLL SQLite.Interop.DLL", why this DLL file error
>> is not also indicated by Depends?
> IEShims and IEFrame are red-herrings.  They're delay-loaded by a
> Windows component, and that component knows how to find them, and can
> deal with them not being loaded if need be.
>
> The core error is the fact you can't load SQLite.Interop.dll.  If you
> open depends and open this DLL on the target machines, I'm betting
> you'll see three error messages from depends.  You'll see IEShims.dll
> and IEFrame.dll, which you can ignore.  You'll also probably see
> MSVCR100.dll, which is the crux of the problem.  This is a guess, but
> you probably just need to install the VS 2010 C++ Redistributable
> package on the target machines and your project will work.
> Alternatively, you could recompile SQLite.Interop.dll to use the
> static CRT library (/MT).
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Sqlite3 WAL (file is encrypted or is not a database, database table is locked, database disk image is malformed') at high load

[Anoop K]

I am using sqlite3(sqlite-3.7.0.1) with WAL enabled as storage for a
multiprocessing daemon(python). On doing a BULK insert of .5 million rows
each of size 230 bytes in batches of 500 where each batch is a transaction,
following errors happen in other processes which perform (<10) SELECTS and
INSERTS.

These errors does not happen always and database do function even after
these errors. SELECTS, INSERTS queries after the BULK insert operation do
succeed after the batch operation is completed.

   - file is encrypted or is not a database
   - database table is locked

Sqlite3 configuration

   - 'PRAGMA synchronous=OFF'
   - 'PRAGMA journal_mode=wal'
   - 'PRAGMA wal_checkpoint=FULL' Rest of the configuration parameters have
   have default values

If I continue BULK inserts with a sleep of 30 sec, after 10+ runs I
see *'database
disk image is malformed' . *After that the table to which BULK inserts were
made does not work. They fail with same error '*database disk image is
malformed'.*. Other tables do respond to queries.

Any clues on the root cause ? Is this a Sqlite3 WAL bug ?

Thanks

Anoop
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query with UNION on large table

[Simon Slavin]

On 23 Jun 2011, at 1:00am, Rense Corten wrote:

> @Simon: I don't have a problem per se with things running overnight,
> as long as I can be sure that things will complete at some point...the
> result of "PRAGMA integrity_check;", which by the way took less then
> an hour, is "ok".  Any suggestions?

Well, that is a major hurdle cleared.  Sorry I don't have any better ideas.  I 
hope someone else on the list does.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query with UNION on large table

[Rense Corten]

Thanks Simon, Jim and Tom for your replies!

@Simon: I don't have a problem per se with things running overnight,
as long as I can be sure that things will complete at some point...the
result of "PRAGMA integrity_check;", which by the way took less then
an hour, is "ok".  Any suggestions?

@Jim: I'm running your alternative query right now, but so far (after
a couple of hours) seems to have the same symptoms: cpu jumps at
first, a journal file is created, but then cpu usage drops to almost
nothing and keeps decreasing, and no observable changes to the
database file.

@Tom: you are right, the version I posted removes only the "reverse
copies" but leaves the rest in, which is another operation I need to
do, and I mixed up the code when posting. To get the result I
initially described, I would indeed use an "intersect"  as you
suggested. Sorry for the confusion.

Rense
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query with UNION on large table

[Rense Corten]

Thanks Simon, Jim and Tom for your replies!

@Simon: I don't have a problem per se with things running overnight,
as long as I can be sure that things will complete at some point...the
result of "PRAGMA integrity_check;", which by the way took less then
an hour, is "ok".  Any suggestions?

@Jim: I'm running your alternative query right now, but so far (after
a couple of hours) seems to have the same symptoms: cpu jumps at
first, a journal file is created, but then cpu usage drops to almost
nothing and keeps decreasing, and no observable changes to the
database file.

@Tom: you are right, the version I posted removes only the "reverse
copies" but leaves the rest in, which is another operation I need to
do, and I mixed up the code when posting. To get the result I
initially described, I would indeed use an "intersect"  as you
suggested. Sorry for the confusion.

Rense
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Bug with fts3 when using a restrictive authorizer.

[Scott Hess]

If the authorizer prevents PRAGMA, then fts3 fails with an SQLITE_AUTH
error from fts3DatabasePageSize().

http://crbug.com/85522 is the Chromium bug where this originated (I
upgraded the Chromium SQLite version a few weeks back).

http://codereview.chromium.org/7230021 is the short-term patch I'm
landing to fix it.  When it sees SQLITE_AUTH from preparing the PRAGMA
page_size call, it wires the returned page_size to 1024.  But if
there's a better way, I'd be happy to snag it from upstream.

Thanks,
scott
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Query performance issue

[Josh Gibbs]

Hi all.  We're trying to get the following query working at a better 
speed and
I'm wondering if anyone has any ideas on optimizations we might be able 
to do.

The query groups e-mail addresses and gives us the total number of each
address seen within a given time range of messages, ordering from the 
highest
to lowest count.

The first time we run the query it's very slow, but speeds up with 
subsequent
runs.  That doesn't help in production because by the time it comes 
around to
running the report the cached info is well expired.

Table structure:
CREATE TABLE email_addresses (email_address_ID INTEGER PRIMARY KEY 
AUTOINCREMENT, EMailAddress TEXT UNIQUE);
CREATE TABLE messages (message_ID INTEGER PRIMARY KEY AUTOINCREMENT, 
TimeStamp INTEGER);
CREATE INDEX messages_timestamp_index ON messages (TimeStamp);
CREATE TABLE recipients (recipient_ID INTEGER PRIMARY KEY AUTOINCREMENT, 
message_ID INTEGER, email_address_ID INTEGER, FOREIGN KEY(message_ID) 
REFERENCES messages(message_ID) ON DELETE CASCADE);
CREATE INDEX recipients_emailAddressID_index ON recipients 
(email_address_ID);
CREATE UNIQUE INDEX recipients_constraint_index ON recipients 
(message_ID, email_address_ID);

Query:
SELECT email_addresses.EMailAddress, COUNT(*) as OrderColumn
FROM email_addresses,recipients,messages
WHERE email_addresses.email_address_ID = recipients.email_address_ID AND 
messages.message_ID = recipients.message_ID
AND CAST (messages.TimeStamp AS INTEGER) BETWEEN 0 AND 99
GROUP BY email_addresses.EMailAddress
ORDER BY
  OrderColumn
DESC

Table stats (these are just the relevant tables):

Messages: 942,279
Recipients: 531,186
Email_addresses: 226,337

DB size is just over 1Gb


On a side note, the CAST in there was a leftover from when the timestamp 
was a date field.
Taking the cast out slows the query down?  Explain suggests that the 
query has 2 additional
instructions to perform without the CAST.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Critical issue

[Random Coder]

On Wed, Jun 22, 2011 at 1:11 PM, Cyrille  wrote:
> I confirm also that my project was set to "anycpu". If I change the
> setting to "x86", is the VS 2010 C++ Redistributable package still
> necessary?

Yes.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Critical issue

[Cyrille]

Thank you so much to all of you for your feedback. One of my users just 
informed me that he solved the issue by installing indeed the VS 2010 
C++ Redistributable package.
I confirm also that my project was set to "anycpu". If I change the 
setting to "x86", is the VS 2010 C++ Redistributable package still 
necessary?

Thanks again!
Regards,
Cyrille


Le 22/06/2011 21:56, J Decker a écrit :
> And the ieshims or whatever that doesn't show up in dependancy walker
> is standard behavior for any C# application.
>
> On Wed, Jun 22, 2011 at 12:55 PM, J Decker  wrote:
>> This is actually probably a very simple problem.  sqlite.net library
>> is built as x86.  Your project is probably built as 'anycpu' instead
>> of 'x86' which allows it to try and run as x64 as appropriate, but it
>> won't be able to use the x86 sqlite.net library.
>>
>> On Sat, Jun 18, 2011 at 5:47 AM, Cyrille  wrote:
>>> Dear all,
>>>
>>> Since I am using the new version of SQLite.NET, some of my users have
>>> the following error when launching my application:
>>>
>>> System.DllNotFoundException: Impossible de charger la DLL
>>> 'SQLite.Interop.DLL': Le module spécifié est introuvable. (Exception de
>>> HRESULT : 0x8007007E)
>>> à System.Data.SQLite.UnsafeNativeMethods.sqlite3_open_interop(Byte[]
>>> utf8Filename, Int32 flags, IntPtr&  db)
>>> à System.Data.SQLite.SQLite3.Open(String strFilename,
>>> SQLiteOpenFlagsEnum flags, Int32 maxPoolSize, Boolean usePool)
>>> à System.Data.SQLite.SQLiteConnection.Open()
>>>
>>> However, the SQLite.Interop is in the application folder. I specify that
>>> I am using VB 2010 Express with the framework 4.0.
>>>
>>> Sorry but I really have troubles: if I cannot solve this critical issue,
>>> I cannot see what to do but stopping the development of my application :-(
>>> Thank you very much in advance
>>> Best regards,
>>> Cyrille
>>> ___
>>> 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

Re: [sqlite] Critical issue

[J Decker]

And the ieshims or whatever that doesn't show up in dependancy walker
is standard behavior for any C# application.

On Wed, Jun 22, 2011 at 12:55 PM, J Decker  wrote:
> This is actually probably a very simple problem.  sqlite.net library
> is built as x86.  Your project is probably built as 'anycpu' instead
> of 'x86' which allows it to try and run as x64 as appropriate, but it
> won't be able to use the x86 sqlite.net library.
>
> On Sat, Jun 18, 2011 at 5:47 AM, Cyrille  wrote:
>> Dear all,
>>
>> Since I am using the new version of SQLite.NET, some of my users have
>> the following error when launching my application:
>>
>> System.DllNotFoundException: Impossible de charger la DLL
>> 'SQLite.Interop.DLL': Le module spécifié est introuvable. (Exception de
>> HRESULT : 0x8007007E)
>>    à System.Data.SQLite.UnsafeNativeMethods.sqlite3_open_interop(Byte[]
>> utf8Filename, Int32 flags, IntPtr& db)
>>    à System.Data.SQLite.SQLite3.Open(String strFilename,
>> SQLiteOpenFlagsEnum flags, Int32 maxPoolSize, Boolean usePool)
>>    à System.Data.SQLite.SQLiteConnection.Open()
>>
>> However, the SQLite.Interop is in the application folder. I specify that
>> I am using VB 2010 Express with the framework 4.0.
>>
>> Sorry but I really have troubles: if I cannot solve this critical issue,
>> I cannot see what to do but stopping the development of my application :-(
>> Thank you very much in advance
>> Best regards,
>> Cyrille
>> ___
>> 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] Critical issue

[J Decker]

This is actually probably a very simple problem.  sqlite.net library
is built as x86.  Your project is probably built as 'anycpu' instead
of 'x86' which allows it to try and run as x64 as appropriate, but it
won't be able to use the x86 sqlite.net library.

On Sat, Jun 18, 2011 at 5:47 AM, Cyrille  wrote:
> Dear all,
>
> Since I am using the new version of SQLite.NET, some of my users have
> the following error when launching my application:
>
> System.DllNotFoundException: Impossible de charger la DLL
> 'SQLite.Interop.DLL': Le module spécifié est introuvable. (Exception de
> HRESULT : 0x8007007E)
>    à System.Data.SQLite.UnsafeNativeMethods.sqlite3_open_interop(Byte[]
> utf8Filename, Int32 flags, IntPtr& db)
>    à System.Data.SQLite.SQLite3.Open(String strFilename,
> SQLiteOpenFlagsEnum flags, Int32 maxPoolSize, Boolean usePool)
>    à System.Data.SQLite.SQLiteConnection.Open()
>
> However, the SQLite.Interop is in the application folder. I specify that
> I am using VB 2010 Express with the framework 4.0.
>
> Sorry but I really have troubles: if I cannot solve this critical issue,
> I cannot see what to do but stopping the development of my application :-(
> Thank you very much in advance
> Best regards,
> Cyrille
> ___
> 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] Critical issue

[Random Coder]

On Wed, Jun 22, 2011 at 10:57 AM, Cyrille  wrote:
> As you said, I have been surprised by the result. One of my users copied
> the missing DLL indicated by Depends (IEShims.dll) to the application
> folder and Depends indicates now another missing DLL: ieframe.dll which
> seems to be again linked to IE.
> Well, I have the feeling that the only possible solution so far is to
> follow your other advice: adding the IE folder to the environment path.
> Could you please explain me how to proceed?
>
> Finally, I still very puzzled how the difference between the DLL error
> indicated by Depends and the DLL which cannot be found according to the
> .NET error message raised while launching the application.
> If the .NET raised an error message: "System.DllNotFoundException:
> Impossible to load the DLL SQLite.Interop.DLL", why this DLL file error
> is not also indicated by Depends?

IEShims and IEFrame are red-herrings.  They're delay-loaded by a
Windows component, and that component knows how to find them, and can
deal with them not being loaded if need be.

The core error is the fact you can't load SQLite.Interop.dll.  If you
open depends and open this DLL on the target machines, I'm betting
you'll see three error messages from depends.  You'll see IEShims.dll
and IEFrame.dll, which you can ignore.  You'll also probably see
MSVCR100.dll, which is the crux of the problem.  This is a guess, but
you probably just need to install the VS 2010 C++ Redistributable
package on the target machines and your project will work.
Alternatively, you could recompile SQLite.Interop.dll to use the
static CRT library (/MT).
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query with UNION on large table

[Tom Holden]

This is even faster, on a tiny table:

SELECT n1, n2 FROM table1
  INTERSECT
   SELECT n2, n1  FROM table1
WHERE n2  1

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query with UNION on large table

[Jim Morris]

Did you try to time a simpler select:

SELECT min(n1, n2) as new1, max(n1,n2) as new2 FROM table1 group by new1, new2 
having count(*)>  1


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Critical issue

[Cyrille]

Hi Michael,

As you said, I have been surprised by the result. One of my users copied 
the missing DLL indicated by Depends (IEShims.dll) to the application 
folder and Depends indicates now another missing DLL: ieframe.dll which 
seems to be again linked to IE.
Well, I have the feeling that the only possible solution so far is to 
follow your other advice: adding the IE folder to the environment path. 
Could you please explain me how to proceed?

Finally, I still very puzzled how the difference between the DLL error 
indicated by Depends and the DLL which cannot be found according to the 
.NET error message raised while launching the application.
If the .NET raised an error message: "System.DllNotFoundException: 
Impossible to load the DLL SQLite.Interop.DLL", why this DLL file error 
is not also indicated by Depends?

Sorry, I would like to understand :-//
Thank you very much again! :-)
Best regards,
Cyrille




Le 22/06/2011 03:29, Michael Stephenson a écrit :
> Hi Cyrille,
>
> You should probably use Dependency Walker on an end-user machine and profile
> your app with it to see what module(s) it cannot load.
>
> Since your app is 32bit, you'll need to get the 32bit version of Dependency
> Walker.  Then, load your exe into DW, click on the Profile menu, and start
> profiling.
>
> The log at the bottom of the DW window will show quite clearly which modules
> are loaded (you may be surprised :o) and will indicate in red any issues
> that are encountered.
>
> Note that not all failures of LoadLibrary or GetProcAddress are actual
> errors.  Usually the library that is executing these calls is ready to
> handle the case where the DLL may be missing or may be a different version
> that does not export a particular function.
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Cyrille
> Sent: Monday, June 20, 2011 2:20 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Critical issue
>
> Dear Michael,
>
> Thank you very much. I am currently working with my users to make some
> testing following your advice. It seems that so far, the users
> encountering the bug has IE 8 installed. However, I have on another
> computer IE9 and the desktop application is running perfectly.
>
> They ran Depends on their end-user machine and it reveals two DLLs issue
> (cf. screenshot attached). However, these DLLs are not the SQLite.NET
> one. Thus my question: do the error mentioned in the screenshot attached
> can lead, on the end-user machine, to an error like
> "System.DllNotFoundException: Impossible to load the DLL
> '_SQLite.Interop.DLL_'"? (i.e. an error linked to a different DLL than
> the one mentioned by Depends)
>
> Thanks again very much :)
> Best regards,
> Cyrille
>
>
> Le 19/06/2011 20:10, Michael Stephenson a écrit :
>> You might also ask them if their IE version is 32-bit or 64-bit.
>>
>> Yes, ideally you would try Depends on an end-user machine.  Then, you
> might
>> try tweaking that machine (manually adjust the path, copy ieshims.dll to
>> somewhere on the path, etc.) until you come up with a workable solution.
>>
>> -Original Message-
>> From: sqlite-users-boun...@sqlite.org
>> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Cyrille
>> Sent: Sunday, June 19, 2011 1:56 PM
>> To: General Discussion of SQLite Database
>> Subject: Re: [sqlite] Critical issue
>>
>> Dear Michael,
>>
>> Thanks for your message. I am going to ask my users for their IE version
> to
>> see if there is a link with the issue.
>>
>> Regards,
>> Cyrille
>>
>>
>>
>> Le 19/06/2011 05:20, Michael Stephenson a écrit :
>>> If the IE folder is not on the path, yes, you have to either copy it
>>> somewhere on the path, copy it to your application folder, or add the
>>> IE folder to the path.  I believe that not having the IE folder on the
>>> path is a mistake in the installer for the newer versions of IE.  Back
>>> in the day, I think that the IE folder used to be on the path
>>> automatically.  This is a good example of bad practices on Microsoft's
>>> part and also how they have tied IE into the operating system so that you
>> can't get rid of it.
>>> I think that this is a typical issue with just about any application
>>> (Depends will show a missing delay load) because shell32.dll on newer
>>> versions of Windows has this delay load dependency on ieshims.dll.
>>>
>>> However, usually this is not a problem because usually the delayed
>>> load isn't invoked because nothing in the DLL is used.
>>>
>>> You might want to use Dependency Walker to profile your app and get a
>>> good idea of what the exact issue is.  If your app is 32-bit, you'll
>>> need the 32-bit depends.exe to profile the app.
>>>
>>> I'm going to bet that if all of your users aren't having the problem,
>>> then the ones with IE7 or earlier are good and the ones with IE8 or
>>> later are the ones having the problem.
>>>
>>> -Original Message-
>>> From: 

Re: [sqlite] Query with UNION on large table

[Simon Slavin]

On 22 Jun 2011, at 6:36pm, Simon Slavin wrote:

> With a billion rows,

I forgot to say that there shouldn't really be a problem with a database of 
this size.  If you have a single-user single-process use for a database of this 
size there's no reason SQLite shouldn't be a good solution for you.  But I am 
interested to know what's causing your problem.

Simon.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query with UNION on large table

[Simon Slavin]

On 22 Jun 2011, at 6:25pm, Rense Corten wrote:

> This has the desired result on a small example, but when I try this on
> my actual table which has about 800 million rows, the query never
> seems to complete. It has been running for a couple of days now, and
> it doesn't seem sqlite is still doing anything (cpu usage dropped to
> almost zero), but I get no error messages. I should mention that
> table1 is not indexed, as indexing this large table also didn't work
> out yet

I'm actually more concerned about your problem with creating the index than 
about your SELECT.  In fact, it's possible that the SELECT optimiser inside 
SQLite has decided that the best way to perform your very complicated SELECT 
command is to make a temporary index.

Please run "PRAGMA integrity_check" on your database as in



With a billion rows, this will probably need at least an overnight run.  In 
fact the integrity check, creating the index, and your SELECT will all need an 
overnight run.  And I suspect that your are stressing some component of your 
setup, possibly your operating-system-level disk caching or your hard disk 
drive.

The integrity_check should be a good first check to see whether it's worth 
pursuing the other things.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Query with UNION on large table

[Rense Corten]

Hi list,
I am rather new to sqlite, and try to use it to manage a large
database. The problem I have is the following:
I Have a table "table1" that looks like this:

n1,n2
1,3
3,1
2,3
3,2
2,4

thus there exists "reverse copies" of (1,3) and (2,3), while there is
no such copy of (2,4) . I want to reduce this to:

n1,n2
1,3
2,3

that is, keep only the rows of which there exists a reverse copy, and
then keep only one of those copies.

I tried the following query to achieve this:

CREATE TABLE table2 AS SELECT n1,n2 FROM (SELECT n1, n2 FROM table1
UNION SELECT n2 AS n1, n1 AS n2 FROM table1) WHERE(n1

Re: [sqlite] openmpi parallel problem

[Pavel Ivanov]

> there are no statements open, as far as i can see.

Your eyes can miss something. Does sqlite3_close return SQLITE_OK or
something else?


Pavel


On Wed, Jun 22, 2011 at 12:36 PM, Thorsten Kersting
 wrote:
> there are no statements open, as far as i can see.
>
> On 06/22/2011 05:29 PM, Pavel Ivanov wrote:
>> Do you check result code of connection closing? Is it successful?
>> If it's not successful then some statements are still active and
>> reading transaction is still open. That could be the reason of error
>> in write process.
>>
>>
>> Pavel
>>
>>
>> On Wed, Jun 22, 2011 at 10:58 AM, Thorsten Kersting
>>   wrote:
>>> i use openmpi fo parallelization, an c++ as the interface to sqlite.
>>> in my program one process only writes into the database, and the other
>>> processes read at the beginning, and then never do anything with it. But
>>> even when i close the database-connection of the non-writing processes,
>>> my write process says, that the database is locked. And this lock never
>>> gets released.
>>> I don't understand, why there is a lock on the database, when no other
>>> connection exists. Is there a way, that i can see, which connection has
>>> the lock?
>>> i tried this in a testprogramm, and there it worked, but not in the
>>> programm i want to use.
>>>
>>> thanks for any help
>>> ___
>>> 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] openmpi parallel problem

[Thorsten Kersting]

there are no statements open, as far as i can see.

On 06/22/2011 05:29 PM, Pavel Ivanov wrote:
> Do you check result code of connection closing? Is it successful?
> If it's not successful then some statements are still active and
> reading transaction is still open. That could be the reason of error
> in write process.
>
>
> Pavel
>
>
> On Wed, Jun 22, 2011 at 10:58 AM, Thorsten Kersting
>   wrote:
>> i use openmpi fo parallelization, an c++ as the interface to sqlite.
>> in my program one process only writes into the database, and the other
>> processes read at the beginning, and then never do anything with it. But
>> even when i close the database-connection of the non-writing processes,
>> my write process says, that the database is locked. And this lock never
>> gets released.
>> I don't understand, why there is a lock on the database, when no other
>> connection exists. Is there a way, that i can see, which connection has
>> the lock?
>> i tried this in a testprogramm, and there it worked, but not in the
>> programm i want to use.
>>
>> thanks for any help
>> ___
>> 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

Re: [sqlite] openmpi parallel problem

[Pavel Ivanov]

Do you check result code of connection closing? Is it successful?
If it's not successful then some statements are still active and
reading transaction is still open. That could be the reason of error
in write process.


Pavel


On Wed, Jun 22, 2011 at 10:58 AM, Thorsten Kersting
 wrote:
> i use openmpi fo parallelization, an c++ as the interface to sqlite.
> in my program one process only writes into the database, and the other
> processes read at the beginning, and then never do anything with it. But
> even when i close the database-connection of the non-writing processes,
> my write process says, that the database is locked. And this lock never
> gets released.
> I don't understand, why there is a lock on the database, when no other
> connection exists. Is there a way, that i can see, which connection has
> the lock?
> i tried this in a testprogramm, and there it worked, but not in the
> programm i want to use.
>
> thanks for any help
> ___
> 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] openmpi parallel problem

[Thorsten Kersting]

i use openmpi fo parallelization, an c++ as the interface to sqlite.
in my program one process only writes into the database, and the other 
processes read at the beginning, and then never do anything with it. But 
even when i close the database-connection of the non-writing processes, 
my write process says, that the database is locked. And this lock never 
gets released.
I don't understand, why there is a lock on the database, when no other 
connection exists. Is there a way, that i can see, which connection has 
the lock?
i tried this in a testprogramm, and there it worked, but not in the 
programm i want to use.

thanks for any help
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Spatialite RTree index

[chris1287]

Hello,
I have a spatial table with about 1 million rows (extracted from the italian
ways shapefile).

I create a spatial index on that table. Then, I tried to perform the same
query; the first time I have not used the index, the second one I tried to
take full advantage from it. But, the execution time of the two queries is
quite the same. Everybody knows why? Here the two queries:

Without index:

SELECT name 
FROM 
italia_90 it, 
( 
SELECT Transform(GeomFromText('POINT(7.662359 45.062477)', 
4326), 32632)
as the_geom_32632
) as p 
WHERE 
Intersects(it.the_geom_32632, p.the_geom_32632);

With index:

SELECT name 
FROM 
italia_90 it, 
( 
SELECT Transform(GeomFromText('POINT(7.662359 45.062477)', 
4326), 32632)
as the_geom_32632
) as p 
WHERE 
Intersects(it.the_geom_32632, p.the_geom_32632)
AND it.pk_uid in 
( 
SELECT pkid 
FROM idx_italia_90_the_geom_32632 
WHERE 
MBRMinX(p.the_geom_32632) - 100 < xmax and 
MBRMaxX(p.the_geom_32632) + 100 > xmin and 
MBRMinY(p.the_geom_32632) - 100 < ymax and 
MBRMaxY(p.the_geom_32632) + 100 > ymin
);
-- 
View this message in context: 
http://old.nabble.com/Spatialite-RTree-index-tp31903692p31903692.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] unexpected effect of view nesting on type conversion

[Mark Brand]

> I've run into a phenomenon I don't understand where view nesting affects
> types.
> Seen in sqlite 3.7.6.3.
>

sqlite-3.6.23 does NOT show this behavior. I don't know though when the 
change happened.

Mark

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [PATCH] Won't compile on archaic OS

[Richard Hipp]

On Tue, Jun 21, 2011 at 7:29 AM, H.Merijn Brand  wrote:

> On Mon, 23 May 2011 18:45:14 +0200, "Bugs in DBD-SQLite via RT"
>  wrote:
>
> > See also
> > https://rt.cpan.org/Ticket/Display.html?id=68396
>
> This (rather clear) bug report neither got any acknowledgement, not
> does it appear in the RT trackers.
>

The problem was fixed in SQLite on 2011-04-15 with this check-in:
http://www.sqlite.org/src/ci/8775f159c1


>
> Meanwhile things got even worse.
>
> > Using gcc-3.4.6 on HP-UX 10.20:
>
> HP-UX 10.20 does not have pread at all. -UUSE_PREAD doesn't work
> (enough) as the test is (very) wrong. SQLITE_ENABLE_LOCKING_STYLE is
> overruled in this section when passed as -USQLITE_ENABLE_LOCKING_STYLE
>
> --8<--- line 23983
> #if !defined(SQLITE_ENABLE_LOCKING_STYLE)
> #  if defined(__APPLE__)
> #define SQLITE_ENABLE_LOCKING_STYLE 1
> #  else
> #define SQLITE_ENABLE_LOCKING_STYLE 0
> #  endif
> #endif
> -->8---
>
> And thus leaving
> --8<--- line 24453
> #if defined(USE_PREAD) || defined(SQLITE_ENABLE_LOCKING_STYLE)
>  { "pread",(sqlite3_syscall_ptr)pread,  0  },
> #else
>  { "pread",(sqlite3_syscall_ptr)0,  0  },
> #endif
> -->8---
>
> a useless and bogus test.
>
> That second test (also for pwrite) should either test for the *content*
> of SQLITE_ENABLE_LOCKING_STYLE or not test on it at all
>
> > > make test
> > gcc -c  -I. -I/opt/perl/lib/site_perl/5.14.0/PA-RISC2.0/auto/DBI -mpa-
> > risc-1-1 -DPERL_DONT_CREATE_GVSV -D_HPUX_SOURCE -fno-strict-aliasing -
> > pipe -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -O2   -DVERSION=
> > \"1.31\" -DXS_VERSION=\"1.31\" -fPIC "-I/opt/perl/lib/5.14.0/PA-RISC2.0/
> > CORE"  -DSQLITE_ENABLE_FTS3 -DSQLITE_ENABLE_FTS3_PARENTHESIS -
> > DSQLITE_ENABLE_COLUMN_METADATA -DNDEBUG=1 -DHAVE_USLEEP=1 -
> > DTHREADSAFE=0 -DSQLITE_OMIT_LOAD_EXTENSION sqlite3.c
> > sqlite3.c: In function `unixShmMap':
> > sqlite3.c:26184: error: `MAP_FAILED' undeclared (first use in this
> > function)
> > sqlite3.c:26184: error: (Each undeclared identifier is reported only
> > once
> > sqlite3.c:26184: error: for each function it appears in.)
> >
> > After applying this patch:
> > --8<---
> > --- sqlite3.c.org   2011-05-23 15:27:35 +0200
> > +++ sqlite3.c   2011-05-23 15:29:55 +0200
> > @@ -22661,6 +22661,10 @@ struct unixFile {
> >  */
> >  #define SQLITE_WHOLE_FILE_LOCKING  0x0001   /* Use whole-file locking */
> >
> > +#ifndef MAP_FAILED
> > +#define MAP_FAILED   ((void *) -1)
> > +#endif
> > +
> >  /*
> >  ** Include code that is common to all os_*.c files
> >  */
> > -->8---
> >
> > > make test
> > :
> > All tests successful.
> > Files=59, Tests=1308, 87 wallclock secs ( 4.61 usr  0.48 sys + 53.42
> > cusr  3.96 csys = 62.47 CPU)
> > Result: PASS
> > >
>
>
> --
> H.Merijn Brand  http://tux.nl  Perl Monger  http://amsterdam.pm.org/
> using 5.00307 through 5.14 and porting perl5.15.x on HP-UX 10.20, 11.00,
> 11.11, 11.23 and 11.31, OpenSuSE 10.1, 11.0 .. 11.4 and AIX 5.2 and 5.3.
> http://mirrors.develooper.com/hpux/   http://www.test-smoke.org/
> http://qa.perl.org  http://www.goldmark.org/jeff/stupid-disclaimers/
> ___
> 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

Re: [sqlite] [PATCH] Won't compile on archaic OS

[H.Merijn Brand]

On Mon, 23 May 2011 18:45:14 +0200, "Bugs in DBD-SQLite via RT"
 wrote:

> See also
> https://rt.cpan.org/Ticket/Display.html?id=68396

This (rather clear) bug report neither got any acknowledgement, not
does it appear in the RT trackers.

Meanwhile things got even worse.
 
> Using gcc-3.4.6 on HP-UX 10.20:

HP-UX 10.20 does not have pread at all. -UUSE_PREAD doesn't work
(enough) as the test is (very) wrong. SQLITE_ENABLE_LOCKING_STYLE is
overruled in this section when passed as -USQLITE_ENABLE_LOCKING_STYLE

--8<--- line 23983
#if !defined(SQLITE_ENABLE_LOCKING_STYLE)
#  if defined(__APPLE__)
#define SQLITE_ENABLE_LOCKING_STYLE 1
#  else
#define SQLITE_ENABLE_LOCKING_STYLE 0
#  endif
#endif
-->8---

And thus leaving
--8<--- line 24453
#if defined(USE_PREAD) || defined(SQLITE_ENABLE_LOCKING_STYLE)
  { "pread",(sqlite3_syscall_ptr)pread,  0  },
#else
  { "pread",(sqlite3_syscall_ptr)0,  0  },
#endif
-->8---

a useless and bogus test.

That second test (also for pwrite) should either test for the *content*
of SQLITE_ENABLE_LOCKING_STYLE or not test on it at all

> > make test
> gcc -c  -I. -I/opt/perl/lib/site_perl/5.14.0/PA-RISC2.0/auto/DBI -mpa-
> risc-1-1 -DPERL_DONT_CREATE_GVSV -D_HPUX_SOURCE -fno-strict-aliasing -
> pipe -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -O2   -DVERSION=
> \"1.31\" -DXS_VERSION=\"1.31\" -fPIC "-I/opt/perl/lib/5.14.0/PA-RISC2.0/
> CORE"  -DSQLITE_ENABLE_FTS3 -DSQLITE_ENABLE_FTS3_PARENTHESIS -
> DSQLITE_ENABLE_COLUMN_METADATA -DNDEBUG=1 -DHAVE_USLEEP=1 -
> DTHREADSAFE=0 -DSQLITE_OMIT_LOAD_EXTENSION sqlite3.c
> sqlite3.c: In function `unixShmMap':
> sqlite3.c:26184: error: `MAP_FAILED' undeclared (first use in this 
> function)
> sqlite3.c:26184: error: (Each undeclared identifier is reported only 
> once
> sqlite3.c:26184: error: for each function it appears in.)
> 
> After applying this patch:
> --8<---
> --- sqlite3.c.org   2011-05-23 15:27:35 +0200
> +++ sqlite3.c   2011-05-23 15:29:55 +0200
> @@ -22661,6 +22661,10 @@ struct unixFile {
>  */
>  #define SQLITE_WHOLE_FILE_LOCKING  0x0001   /* Use whole-file locking */
> 
> +#ifndef MAP_FAILED
> +#define MAP_FAILED   ((void *) -1)
> +#endif
> +
>  /*
>  ** Include code that is common to all os_*.c files
>  */
> -->8---
> 
> > make test
> :
> All tests successful.
> Files=59, Tests=1308, 87 wallclock secs ( 4.61 usr  0.48 sys + 53.42 
> cusr  3.96 csys = 62.47 CPU)
> Result: PASS
> >


-- 
H.Merijn Brand  http://tux.nl  Perl Monger  http://amsterdam.pm.org/
using 5.00307 through 5.14 and porting perl5.15.x on HP-UX 10.20, 11.00,
11.11, 11.23 and 11.31, OpenSuSE 10.1, 11.0 .. 11.4 and AIX 5.2 and 5.3.
http://mirrors.develooper.com/hpux/   http://www.test-smoke.org/
http://qa.perl.org  http://www.goldmark.org/jeff/stupid-disclaimers/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] unexpected effect of view nesting on type conversion

[Jean-Christophe Deschamps]

>I've run into a phenomenon I don't understand where view nesting affects
>types.

Give me a try:

Form what I understand, views don't have their own types, so default 
affinity applies.  12.0 gets converted to 12 as an integer in v2 when 
the value gets picked from v1.

Please someone correct me if I'm wrong.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] unexpected effect of view nesting on type conversion

[Mark Brand]

Hi,

I've run into a phenomenon I don't understand where view nesting affects 
types.
Seen in sqlite 3.7.6.3.

The following is my attempt at a minimal case that produces this 
behavior. Here a real is unexpectedly converted to int.

CREATE TABLE customer (
 id INT,
 PRIMARY KEY (id)
);

-- annual percentage points
CREATE TABLE apr (
 id INT,
 apr REAL,
 PRIMARY KEY (id)
);

CREATE VIEW v1 AS
SELECT c.id, i.apr
FROM customer c
LEFT JOIN apr i ON i.id=c.id
;

CREATE VIEW v2 AS
SELECT c.id, v1.apr
FROM customer c
LEFT JOIN v1 ON v1.id=c.id
;

INSERT INTO customer (id) VALUES (1);
INSERT INTO apr (id, apr) VALUES (1, 12);

INSERT INTO customer (id) VALUES (2);
INSERT INTO apr (id, apr) VALUES (2, 12.01);

--works as expected
SELECT id, (apr / 100), typeof(apr) apr_type  FROM v1;
/*
1|0.12|real
2|0.1201|real
*/

--fails expectation to agree with v1
SELECT id, (apr / 100), typeof(apr) apr_type  FROM v2;
/*
1|0|integer
2|0.1201|real
*/


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users