[sqlite] Compiling on SCO OSR5
Hello: I have compiled sqlite3.2.5 on OSR5 using gcc 2.95 and run the fulltest set. This results in a large number of failures of the crash tests all of which have the following format crash-4.2.1036.1... Expected: [1 {child process exited abnormally}] Got: [1 /wk/BUILD/bld/.libs/lt-crashtest:\ disk\ I/O\ error\n\ \ \ \ while\ executing\n\"db\ eval\ \{\n\n\ \ \ \ \ \ \ ATTACH\ 'test2.db'\ AS\ aux\;\n\ \ \ \ \ \ \ BEGIN\;\n\ \ \ \ \ \ \ SELECT\ random()\ FROM\ abc\ LIM IT\ 1036\;\n\ \ \ \ \ \ \ INSERT\ INTO\ abc\ VALUES(randstr(10,10),\ 0,...\"\n\ \ \ \ (file\ \"crash.tcl\"\ line \ 6)] crash-4.2.1036.2... Ok crash-4.2.1036.3... Ok I have 2 questions - 1. Should I be worried about these failures? 2. Any suggestions as to how to make the tests pass? Thanks, Nigel Ball
[sqlite] bug in sqlite 3.2.5 compilation and ANALYZE
I tried the ANALYZE statement with sqlite 3.2.5 in dll form, (both the pre-compiled version downloaded from the site, AND a local compilation). in both cases, the statement fails with an ACCESS VIOLATION. however, the same statement, when run from the pre-compiled sqlite3.exe, works. further, having succesfully ANALYZED a database with the command-line program, I can no longer work with the database using the DLL api. I think there is something very wrong with the compilation defines of the DLL. DRH: can you pls check and fix ? ALL: can you verify that the DLL API crashes on ANALYZE ? (WINDOWS, obviously)
RE: [sqlite] bug in sqlite 3.2.5 compilation and ANALYZE
Perhaps you could provide the exact SQL that crashed. The code seems to be very well tested automatically, so it is very unlikely that all ANALYZE executions fail. --Ned. http://nedbatchelder.com -Original Message- From: Cariotoglou Mike [mailto:[EMAIL PROTECTED] Sent: Monday, 05 September, 2005 5:46 AM To: sqlite-users@sqlite.org Subject: [sqlite] bug in sqlite 3.2.5 compilation and ANALYZE I tried the ANALYZE statement with sqlite 3.2.5 in dll form, (both the pre-compiled version downloaded from the site, AND a local compilation). in both cases, the statement fails with an ACCESS VIOLATION. however, the same statement, when run from the pre-compiled sqlite3.exe, works. further, having succesfully ANALYZED a database with the command-line program, I can no longer work with the database using the DLL api. I think there is something very wrong with the compilation defines of the DLL. DRH: can you pls check and fix ? ALL: can you verify that the DLL API crashes on ANALYZE ? (WINDOWS, obviously)
RE: [sqlite] bug in sqlite 3.2.5 compilation and ANALYZE
ok. I use sqlite3Explorer (which should not matter), I create a new database, then I run : create table test1(id integer); analyze this crashes immediately, with dll 3.2.5. it works fine from the command-line. > -Original Message- > From: Ned Batchelder [mailto:[EMAIL PROTECTED] > Sent: Monday, September 05, 2005 3:10 PM > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] bug in sqlite 3.2.5 compilation and ANALYZE > > Perhaps you could provide the exact SQL that crashed. The > code seems to be very well tested automatically, so it is > very unlikely that all ANALYZE executions fail. > > --Ned. > http://nedbatchelder.com > > -Original Message- > From: Cariotoglou Mike [mailto:[EMAIL PROTECTED] > Sent: Monday, 05 September, 2005 5:46 AM > To: sqlite-users@sqlite.org > Subject: [sqlite] bug in sqlite 3.2.5 compilation and ANALYZE > > I tried the ANALYZE statement with sqlite 3.2.5 in dll form, > (both the pre-compiled version downloaded from the site, AND > a local compilation). > in both > cases, the statement fails with an ACCESS VIOLATION. however, > the same statement, when run from the pre-compiled > sqlite3.exe, works. > further, having succesfully ANALYZED a database with the > command-line program, I can no longer work with the database > using the DLL api. > I think there is something very wrong with the compilation > defines of the DLL. > > DRH: can you pls check and fix ? > ALL: can you verify that the DLL API crashes on ANALYZE ? (WINDOWS, > obviously) > > > > > > >
Re: [sqlite] Problems with threadsafe opt correction #2623
I do not see how such a major change can be justified in a minor point release. For instance, currently amaroK does not work when using a sqlite database on Debian Sid since they package it with sqlite 3.2.5. Shouldn't this have waited for 4.0? On Wed, 31 Aug 2005 05:38:05 -0700 D. Richard Hipp wrote: > On Wed, 2005-08-31 at 12:53 +0200, Guillaume Fougnies wrote: > > My code is sharing a pool of SQLite connections on > > multiple databases between a bunch of treatment > > threads. > > Each thread pops a connection from the pool safely > > and push it back once finished. > > > > This works on some systems but not on others. On some > versions of Linux, a thread is not able to override locks > created by a different thread in the same process. When > that happens, a database connection created on one thread > will not be usable by a different thread. > > Additional information: > > http://www.sqlite.org/cvstrac/tktview?tn=1272 > http://www.sqlite.org/cvstrac/chngview?cn=2521
RE: [sqlite] bug in sqlite 3.2.5 compila tion and ANALYZE
"Cariotoglou Mike" <[EMAIL PROTECTED]> je ob 5.9.2005 14:49:54 napisal(a): >ok. >I use sqlite3Explorer (which should not matter), I create a new >database, then I run : > >create table test1(id integer); >analyze > >this crashes immediately, with dll 3.2.5. it works fine from the >command-line. > I can confirm that. But it seems that the problem is in Sqlite explorer or your sqlite3.dll wrapper implementation. When I try that with my own Delphi wrapper implementation it works. But opening database with sqlite explorer results in crash. Regards, Miha
RE: [sqlite] Null values in Primary Key
Thanks, and sorry on a good day I knew that. My real issue is: CREATE TABLE "Test" ("A" VARCHAR(36) COLLATE NOCASE, "B" VARCHAR(36) COLLATE NOCASE, PRIMARY KEY("A", "B")) INSERT INTO Test("A", "B") VALUES ('1', '1') --> No Error INSERT INTO Test("A", "B") VALUES ('1', '1') --> Duplicate Error INSERT INTO Test("A", "B") VALUES ('2', NULL) --> No Error INSERT INTO Test("A", "B") VALUES ('2', NULL) --> No Error (It inserts another copy) I tried to see what MS SQL would do but it does not allow creation of the tables primary key with any NULL segments. Does that mean I need to do my own check for an existing record instead of using insert or replace? Thanks, Matt Froncek QODBC Development Support / FLEXquarters Solutions Consultant QODBC Driver for QuickBooks - Unleash your data at www.qodbc.com -Original Message- From: Dan Kennedy [mailto:[EMAIL PROTECTED] Sent: Friday, September 02, 2005 11:40 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Null values in Primary Key NULL = NULL is false. It's an SQL thing - go figure. Try: SELECT * FROM test WHERE b IS NULL; http://www.sqlite.org/nulls.html --- Matt Froncek <[EMAIL PROTECTED]> wrote: > I am using SQLCrypt and it returns SQLite Library number 3002002. I have > tried with Encryption on and off. > > Why can't I find a record saved with NULL in one of the fields of the > primary key? > > CREATE TABLE "Test" ("A" VARCHAR(36) COLLATE NOCASE, "B" VARCHAR(36) COLLATE > NOCASE, PRIMARY KEY("A", "B")) > > INSERT INTO Test("A", "B") VALUES ('1', '1') > INSERT INTO Test("A", "B") VALUES ('2', NULL) > > SELECT * From Test WHERE B = NULL --> Returns no records > SELECT * From Test WHERE B = '' --> Returns no records > SELECT * From Test--> Returns both records > SELECT * From Test WHERE A = '2' --> Returns the second record > > Matt Froncek > QODBC Development Support / FLEXquarters Solutions Consultant > QODBC Driver for QuickBooks - Unleash your data at www.qodbc.com > > > Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs
Re: [sqlite] Problems with threadsafe opt correction #2623
On Mon, 2005-09-05 at 10:57 -0500, Ian Monroe wrote: > I do not see how such a major change can be justified in a minor point > release. For instance, currently amaroK does not work when using a > sqlite database on Debian Sid since they package it with sqlite 3.2.5. > > Shouldn't this have waited for 4.0? > There are no plans at this time to ever release version 4.0. SQLite has *never* supported the ability of a handle to be used by more than one thread. By luck, such use would sometimes work on some operating systems. But it would fail on others. Such a situation is very dangerous since if a developer is working on a system where the misuse of SQLite just happened to work, they might not detect their design error and then ship non-working code to a customer where it would fail. A minor change in check-in [2517] detects the misuse of SQLite on all systems and prevents it from working even by chance. This allows the problem to be detected early and corrected before it reaches a customer. This is not a design change. This is not an API change. It does not break backwards compatibility. All that changed is that certain errors in the way SQLite is used are now detected sooner rather than later. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Problems with threadsafe opt correction #2623
On Mon, 2005-09-05 at 18:36 +0200, Tobias Rundström wrote: > How "expensive" is it to open a new sqlite3 handler? The schema has to be read and parsed. Usually that is very quick - SQLite has an efficient parser and can process tens of thousands of SQL statements per second. > This seems to occur when this happens: > > thread1: begin > thread2: begin > thread1: insert () > thread2: select () -> EBUSY > thread1: commit -> EBUSY > > to solve this you have to rollback thread2 before you can commit > thread1. It was a strange behaviour that didn't fit my code at all so I > reverted to not using transactions, this works well but probably hurt my > preformance. > > Is there a better way to handle this? > The BEGIN statement defers locks until they are needed. If you do BEGIN EXCLUSIVE instead of just BEGIN, it acquires the locks immediately and would thus cause the EBUSY error when thread2 did its BEGIN. This simplifies recovery at the price of some concurrency. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Problems with threadsafe opt correction #2623
Ian Monroe wrote: I do not see how such a major change can be justified in a minor point release. For instance, currently amaroK does not work when using a sqlite database on Debian Sid since they package it with sqlite 3.2.5. Just my two cents, but if this change is breaking things in amaroK, XMMS2 and others then more testing should've been done on _those_ products before they were released to the wild. Regression/ unit tests should be run on most any change but definately when you upgrade a major sub-component of your system. --K
Re: [sqlite] Problems with threadsafe opt correction #2623
D. Richard Hipp wrote: This is not a design change. This is not an API change. It does not break backwards compatibility. All that changed is that certain errors in the way SQLite is used are now detected sooner rather than later. May be you can add some pragma to disable this check? Also, if i read this source right: http://www.sqlite.org/cvstrac/chngview?cn=2517 this does only affect linux? regards, rene
Re: [sqlite] Problems with threadsafe opt correction #2623
On 9/5/05, D. Richard Hipp <[EMAIL PROTECTED]> wrote: > On Mon, 2005-09-05 at 10:57 -0500, Ian Monroe wrote: > > I do not see how such a major change can be justified in a minor point > > release. For instance, currently amaroK does not work when using a > > sqlite database on Debian Sid since they package it with sqlite 3.2.5. > > > > Shouldn't this have waited for 4.0? > > > > There are no plans at this time to ever release version 4.0. > > SQLite has *never* supported the ability of a handle to be used > by more than one thread. By luck, such use would sometimes work on > some operating systems. But it would fail on others. Such a > situation is very dangerous since if a developer is working on > a system where the misuse of SQLite just happened to work, they > might not detect their design error and then ship non-working code > to a customer where it would fail. A minor change in check-in [2517] > detects the misuse of SQLite on all systems and prevents it from > working even by chance. This allows the problem to be detected > early and corrected before it reaches a customer. But it has already reached our users. amaroK has never had a reported problem with this. In this case "early" is a year or so too late. What about an --incorrect-behavior configuration option? Or a warning to stderr instead? > This is not a design change. This is not an API change. > It does not break backwards compatibility. Um, yes it does. amaroK works with one version of sqlite but not the next. What is the actual definition of backwards compatable? This reminds me of the scene in Office Space where they lay off Milton by "correcting" the accounting error that lead to him continuing to get his checks. :P > All that changed > is that certain errors in the way SQLite is used are now > detected sooner rather than later. > > -- > D. Richard Hipp <[EMAIL PROTECTED]> > >
Re: [sqlite] Problems with threadsafe opt correction #2623
René Tegel wrote: this does only affect linux? Forgive me that i said linux where i ment unix...
Re: [sqlite] Problems with threadsafe opt correction #2623
Mon, Sep 05, 2005 at 12:45:42PM -0400: D. Richard Hipp wrote: > On Mon, 2005-09-05 at 10:57 -0500, Ian Monroe wrote: > > I do not see how such a major change can be justified in a minor point > > release. For instance, currently amaroK does not work when using a > > sqlite database on Debian Sid since they package it with sqlite 3.2.5. > > > > Shouldn't this have waited for 4.0? > > > > There are no plans at this time to ever release version 4.0. > > SQLite has *never* supported the ability of a handle to be used > by more than one thread. By luck, such use would sometimes work on > some operating systems. But it would fail on others. Such a > situation is very dangerous since if a developer is working on > a system where the misuse of SQLite just happened to work, they > might not detect their design error and then ship non-working code > to a customer where it would fail. A minor change in check-in [2517] > detects the misuse of SQLite on all systems and prevents it from > working even by chance. This allows the problem to be detected > early and corrected before it reaches a customer. In this case, the ability of a handle to be used by more than one thread should be always denied to avoid portability problems. -- Guillaume FOUGNIES Eulerian Technologies
Re: [sqlite] Problems with threadsafe opt correction #2623
Kiel W. wrote: Ian Monroe wrote: I do not see how such a major change can be justified in a minor point release. For instance, currently amaroK does not work when using a sqlite database on Debian Sid since they package it with sqlite 3.2.5. Just my two cents, but if this change is breaking things in amaroK, XMMS2 and others then more testing should've been done on _those_ products before they were released to the wild. Regression/ unit tests should be run on most any change but definately when you upgrade a major sub-component of your system. Well in this case it broke all the currently installed applications that was installed and where MISSUSING the API. We have corrected this now but it will take a while before we can make a release out of it. In the meantime distributions that ship a newer sqlite3 will not be able to use amarok and xmms2. But it was our fault for not reading the docs correctly. -- Tobias
Re: [sqlite] Problems with threadsafe opt correction #2623
"D. Richard Hipp" <[EMAIL PROTECTED]> je ob 5.9.2005 18:45:42 napisal(a): >SQLite has *never* supported the ability of a handle to be used >by more than one thread. By luck, such use would sometimes work on >some operating systems. But it would fail on others. Such a >situation is very dangerous since if a developer is working on >a system where the misuse of SQLite just happened to work, they >might not detect their design error and then ship non-working code >to a customer where it would fail. A minor change in check-in [2517] >detects the misuse of SQLite on all systems and prevents it from >working even by chance. This allows the problem to be detected >early and corrected before it reaches a customer. > >This is not a design change. This is not an API change. >It does not break backwards compatibility. All that changed >is that certain errors in the way SQLite is used are now >detected sooner rather than later. > So what does that mean. On windows I'm protecting database handle with MREWS. Should I rewrite about 10.000 lines of code. And Check other 30.000. To fix that? Regards, Miha
Re: [sqlite] Problems with threadsafe opt correction #2623
On 9/5/05, Kiel W. <[EMAIL PROTECTED]> wrote: > Ian Monroe wrote: > > >I do not see how such a major change can be justified in a minor point > >release. For instance, currently amaroK does not work when using a > >sqlite database on Debian Sid since they package it with sqlite 3.2.5. > > > > > Just my two cents, but if this change is breaking things in amaroK, > XMMS2 and others then more testing should've been done on _those_ > products before they were released to the wild. Regression/ unit tests > should be run on most any change but definately when you upgrade a major > sub-component of your system. I was going to make a scarcastic comment involving time machines, but I see your just ignorant of how open source software gets released. We didn't upgrade a major sub-component of our system. Debian Sid did (they're fixing this now). And regardless, it means amaroK can no longer use newer (unpatched) sqlite versions. Thats the actual issue. > --K >
Re: [sqlite] Problems with threadsafe opt correction #2623
On Mon, 2005-09-05 at 19:23 +0200, Tobias Rundström wrote: > Well in this case it broke all the currently installed applications that > was installed and where MISSUSING the API. We have corrected this now > but it will take a while before we can make a release out of it. In the > meantime distributions that ship a newer sqlite3 will not be able to use > amarok and xmms2. If you were using the same handle in multiple threads, then your applications never have worked on RedHat 9. They might seem to worked, but that would only be because you have not tested them sufficiently to find the subtle failures. Normally the problem only appears under high load. You can avoid these kinds of problems in the future by statically linking against SQLite so that you are not beholden to whatever version of SQLite is shipped with the distribution. > > But it was our fault for not reading the docs correctly. > Not really. The documentation did not bring this point out at all - it was ambiguously worded. (That has since been changed.) We did not realize how bad the situation was until people started reporting problems on RedHat 9. See http://www.sqlite.org/cvstrac/tktview?tn=1272 and http://www.sqlite.org/cvstrac/tktview?tn=1285. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Problems with threadsafe opt correction #2623
On 9/5/05, Tobias Rundström <[EMAIL PROTECTED]> wrote: > Kiel W. wrote: > > Ian Monroe wrote: > > > >> I do not see how such a major change can be justified in a minor point > >> release. For instance, currently amaroK does not work when using a > >> sqlite database on Debian Sid since they package it with sqlite 3.2.5. > >> > >> > > Just my two cents, but if this change is breaking things in amaroK, > > XMMS2 and others then more testing should've been done on _those_ > > products before they were released to the wild. Regression/ unit tests > > should be run on most any change but definately when you upgrade a major > > sub-component of your system. > > Well in this case it broke all the currently installed applications that > was installed and where MISSUSING the API. We have corrected this now > but it will take a while before we can make a release out of it. In the > meantime distributions that ship a newer sqlite3 will not be able to use > amarok and xmms2. Well, amaroK bundles sqlite. Its not that fatal. Its just that distributions can't use the system sqlite. > But it was our fault for not reading the docs correctly. > > -- Tobias >
Re: [sqlite] Problems with threadsafe opt correction #2623
Miha Vrhovnik wrote: So what does that mean. On windows I'm protecting database handle with MREWS. Should I rewrite about 10.000 lines of code. And Check other 30.000. To fix that? As far as i can tell now the windows version of 3.2.5 seems not affected. For other os maybe you can extend your wrapper (if you have one) and add class that implements a helper thread to open/close/query everything in a single thread, at least that is what i plan to do. Shouldn't be too hard and also not really affect the application, at the cost of some performance. regards, rene
[sqlite] What is this extraneous table?
Hi, I have a DB containing a table called MyTable which is about 3Gb in size currently. When my process is writing to it, it seems to create a table something likeMyTable-vmhyimo41pq82sw63ip0 with an associated journal table.I seem to have quite a few of these tables.I cannot use the Command Line tool to view this extraneous DB when the process is running because it is locked. Once the process dies, the file seems to have a large size, but doing a .table or .schema returns nothing. Please help.
Re: [sqlite] Problems with threadsafe opt correction #2623
On Mon, 2005-09-05 at 19:58 +0200, René Tegel wrote: > As far as i can tell now the windows version of 3.2.5 seems not affected. > The restriction that a DB handle can only be used from a single thread is currently only enforced on Unix, because Unix boxes are the only place where using DB handles in multiple threads is a problem. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Null values in Primary Key
> I tried to see what MS SQL would do but it does not allow creation of the > tables primary key with any NULL segments. This is the correct behavior; I thought SQLite enforced that. You can duplicate the correct behavior by adding to the spec for each PK column. > Does that mean I need to do my own check for an existing record instead of > using insert or replace? If you're going to allow nulls in primary keys, SQLite's behavior is correct; nulls are not equal to each other for the purpose of determining column uniqueness -- so the answer to your question is yes if you're going to allow nulls. Regards
[sqlite] Please test on Win95/98/ME
Can somebody with access to Win95/98/ME please test check-in [2656] for me to make sure it didn't break anything? http://www.sqlite.org/cvstrac/chngview?cn=2656 -- D. Richard Hipp <[EMAIL PROTECTED]>
[sqlite] sql help
Hey guys, I'm hoping to get some help with this: create table uses(userid integer, typeid integer); insert into uses values(1, 1); insert into uses values(1, 1); insert into uses values(2, 1); insert into uses values(2, 2); now i'm trying to build a select that gives the result: userid | percent 11.0 20.5 Where percent is the percent of typeids for that userid where typeid=1. I've been trying to do this unsuccessfully for a couple days, so any help would be greatly appreciated. -- Cory Nelson http://www.int64.org
Re: [sqlite] sql help
SELECT userid, SUM(CASE WHEN typeid=1 THEN 1 ELSE 0 END)/COUNT(*) AS percent FROM uses GROUP BY userid Cory Nelson wrote: Hey guys, I'm hoping to get some help with this: create table uses(userid integer, typeid integer); insert into uses values(1, 1); insert into uses values(1, 1); insert into uses values(2, 1); insert into uses values(2, 2); now i'm trying to build a select that gives the result: userid | percent 11.0 20.5 Where percent is the percent of typeids for that userid where typeid=1. I've been trying to do this unsuccessfully for a couple days, so any help would be greatly appreciated.
[sqlite] how to check the file is a sqlite database file?
Hello All, Is there any way to know whether a certain file is sqlite database file or not? I am using sqlite 2.8.16. My requirement is , I give user an option to select a file as a sqlite database file, so if user makes a mistake in selecting a proper sqlite database file,I want to prevent the further processing and prompt the user to select correct file. (I don't want to give any extension to the database file.) Is there any sqlite api /code snippet which tells me whether the file is of sqlite database type or not? Thanking you in advance.. Regards, Yogesh Joshi
Re: [sqlite] how to check the file is a sqlite database file?
Read the first 31 bytes of the file (or the first 15 bytes in V3). Regards
[sqlite] PHP 5.1 PDO Objects
I compiled one of the snapshots of PHP 5.1 available at http://snaps.php.net/ and I notice that phpinfo() recognizes that I have Sqlite 3.2.5 installed for PHP Data Objects (PDO) purposes. I have not experimented to see whether it actually works for Sqlite databases. Is anyone using PDOs with 3.2.5? Thanks Bob Cochran