Re: [sqlite] [BUG] sqlite3_exec "BEGIN; ROLLBACK" corrupts statement already running

[Joey Adams]

On Fri, Aug 24, 2012 at 12:45 AM, Pavel Ivanov  wrote:
> This is a documented change. See http://www.sqlite.org/releaselog/3_7_11.html:
>
> "Pending statements no longer block ROLLBACK. Instead, the pending
> statement will return SQLITE_ABORT upon next access after the
> ROLLBACK."
>
> There was even some explanation of reasons for that somewhere on the list.

Thanks for the quick response.  This looks like the thread you are referring to:

http://osdir.com/ml/sqlite-users/2012-04/msg00638.html
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [BUG] sqlite3_exec "BEGIN; ROLLBACK" corrupts statement already running

[Pavel Ivanov]

This is a documented change. See http://www.sqlite.org/releaselog/3_7_11.html:

"Pending statements no longer block ROLLBACK. Instead, the pending
statement will return SQLITE_ABORT upon next access after the
ROLLBACK."

There was even some explanation of reasons for that somewhere on the list.


Pavel


On Thu, Aug 23, 2012 at 9:03 PM, Joey Adams  wrote:
> Consider the following operations (full test program attached):
>
> stmt <- prepare conn "SELECT * FROM foo"
> Row <- step stmt
> exec conn "BEGIN; ROLLBACK"
> Row <- step stmt
>
> Namely, we prepare a statement with sqlite3_prepare_v2, call
> sqlite3_step (giving us SQLITE_ROW).  While the statement is busy, we
> jump in and do this:
>
> rc = sqlite3_exec(conn, "BEGIN; ROLLBACK", NULL, NULL, NULL);
>
> On SQLite 3.6.22, this sqlite3_exec call returns SQLITE_BUSY, and the
> subsequent sqlite3_step returns SQLITE_ROW.
>
> On SQLite 3.7.13, this sqlite3_exec call returns SQLITE_OK, but the
> subsequent sqlite3_step returns SQLITE_ABORT.
>
> The latter result looks bogus to me.
>
> #define SQLITE_ABORT4   /* Callback routine requested an abort */
>
> We're not doing anything with a callback routine.  And according to
> the documentation for ROLLBACK:
>
> The ROLLBACK will fail with an error code SQLITE_BUSY if there are
> any pending queries.
>
> Does this include queries started before the transaction begin?  Should it?
>
> When the sequence above is performed, I would expect one of two
> results (preferably the first):
>
>  * The exec "BEGIN; ROLLBACK" has no effect on the prepared statement
> that has already started.
>
>  * The ROLLBACK fails with SQLITE_BUSY (the 3.6.22 behavior).
>
> The 3.7.13 behavior definitely looks wrong.  Is this a bug, or is it
> undefined behavior to BEGIN or ROLLBACK while a prepared statement is
> running on the same connection?
>
> Thanks,
> -Joey
>
> ___
> 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] [BUG] sqlite3_exec "BEGIN; ROLLBACK" corrupts statement already running

[Joey Adams]

Consider the following operations (full test program attached):

stmt <- prepare conn "SELECT * FROM foo"
Row <- step stmt
exec conn "BEGIN; ROLLBACK"
Row <- step stmt

Namely, we prepare a statement with sqlite3_prepare_v2, call
sqlite3_step (giving us SQLITE_ROW).  While the statement is busy, we
jump in and do this:

rc = sqlite3_exec(conn, "BEGIN; ROLLBACK", NULL, NULL, NULL);

On SQLite 3.6.22, this sqlite3_exec call returns SQLITE_BUSY, and the
subsequent sqlite3_step returns SQLITE_ROW.

On SQLite 3.7.13, this sqlite3_exec call returns SQLITE_OK, but the
subsequent sqlite3_step returns SQLITE_ABORT.

The latter result looks bogus to me.

#define SQLITE_ABORT4   /* Callback routine requested an abort */

We're not doing anything with a callback routine.  And according to
the documentation for ROLLBACK:

The ROLLBACK will fail with an error code SQLITE_BUSY if there are
any pending queries.

Does this include queries started before the transaction begin?  Should it?

When the sequence above is performed, I would expect one of two
results (preferably the first):

 * The exec "BEGIN; ROLLBACK" has no effect on the prepared statement
that has already started.

 * The ROLLBACK fails with SQLITE_BUSY (the 3.6.22 behavior).

The 3.7.13 behavior definitely looks wrong.  Is this a bug, or is it
undefined behavior to BEGIN or ROLLBACK while a prepared statement is
running on the same connection?

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

Re: [sqlite] [PATCH] Fix "Symbol not found: _OSAtomicCompareAndSwapPtrBarrier" on Mac OS X 10.4 (Tiger)

[Richard Hipp]

On Tue, Aug 21, 2012 at 8:54 PM, Dwayne Litzenberger wrote:

> Recent versions of SQLite fail to run on Mac OS X 10.4 (Tiger) with
> the following error message:
>

We don't test SQLite in MacOS 10.4, but we do test every release on MacOS
10.2.  For that platform, we add the compile-time option:

-DSQLITE_WITHOUT_ZONEMALLOC

Doing the same should clear the problem on MacOS 10.4 as well.  And using
that approach will save us from having to maintain a bunch of hard-to-test
preprocessor logic.


>
> $ DYLD_LIBRARY_PATH=. ./sqlite3
> dyld: lazy symbol binding failed: Symbol not found:
> _OSAtomicCompareAndSwapPtrBarrier
>   Referenced from: ./libsqlite3.0.dylib
>   Expected in: /usr/lib/libSystem.B.dylib
>
> dyld: Symbol not found: _OSAtomicCompareAndSwapPtrBarrier
>   Referenced from: ./libsqlite3.0.dylib
>   Expected in: /usr/lib/libSystem.B.dylib
>
> Trace/BPT trap
>
> This is because OSAtomicCompareAndSwapPtrBarrier was not introduced
> until Mac OS X 10.5 (Leopard).
>
> Affected versions:
>
> - Latest trunk (fossil 45cdc32f1e cloned from https://sqlite.org/src/)
> - SQLite 3.7.13
> - SQLite 3.7.12
>
> Not affected:
>
> - SQLite 3.7.11
> - SQLite 3.7.10
> - SQLite 3.7.9
>
> Build environment:
>
> Mac OS X SDK: MacOSX10.6.sdk
>
> $ xcodebuild -version
> Xcode 3.2.5
> Component versions: DevToolsCore-1763.0; DevToolsSupport-1758.0
> BuildVersion: 10M2423
>
> $ gcc --version
> i686-apple-darwin10-gcc-4.2.1 (GCC) 4.2.1 (Apple Inc. build 5664)
>
> How to reproduce:
>
> I built SQLite on a Mac OS X 10.5 (Leopard) machine, then copied
> .libs/ to a machine running 10.4 (Tiger) and ran sqlite3 from there:
>
> tar xzf sqlite-autoconf-3071300.tar.gz
> mkdir bld
> cd bld
> CFLAGS='-arch ppc -arch i386 -mmacosx-version-min=10.4' \
> LDFLAGS='-arch ppc -arch i386 -mmacosx-version-min=10.4' \
> ../sqlite-autoconf-3071300/configure --without-dependency-tracking
> make
> rsync --delete -a .libs/ tigerbox:~/sqlite-scratch/
> ssh -t tigerbox "cd ~/sqlite-scratch && DYLD_LIBRARY_PATH=. ./sqlite3"
>
> I should get a regular sqlite prompt.  What I actually got was the
> "Symbol not found" error above.
>
> It looks like other people have seen this issue before[1], but it does
> not look like it's actually been fixed.
>
> Please apply the attached patch against the latest trunk (45cdc32f1e),
> which fixes this bug.  (You can ignore the git commit id in the patch.
>  It wasn't obvious to me how to make fossil output a commit as a
> patch, so just I did a "git init" inside the fossil working directory
> and then worked from that.)
>
> Cheers,
> - Dwayne
>
> [1] http://sqlite.org:8080/pipermail/sqlite-users/2012-January/036184.html
>
> ___
> 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] to table update

[Igor Tandetnik]

yanhong.ye  wrote:
> update tb1 set col1=(select col1 from tb2 ) where tb1.co2=tb2.co2;

The closing parenthesis is in the wrong place:

update tb1 set col1=(select col1 from tb2 where tb1.co2=tb2.co2);

-- 
Igor Tandetnik

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

Re: [sqlite] to table update

[Rob Richardson]

Are you certain there exist rows in tb1 and tb2 that satisfy the condition?  
What happens when you try?  Is any error message or number returned?  Can you 
run the same query inside an SQLite management tool like SQLite Spy?  Does it 
work there?  Please provide us ALL of the relevant information when you post.

But in this case, it's fairly easy.  (Easy enough for me to get it right?  
We'll see.)  The subquery is returning more than one value.  That's illegal.  
And I hope you mean to update every single row in tb1, because that's what your 
query will do.

And your main query references tb2 without defining it.

RobR

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of yanhong.ye
Sent: Thursday, August 23, 2012 7:32 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] to table update

update tb1 set col1=(select col1 from tb2 ) where tb1.co2=tb2.co2;

it couldn't work

___
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] Logging only SQL commands which make changes, parsing PRAGMAs

[Kit]

2012/8/23 Simon Slavin :
> I'm trying to log SQL commands which might make changes to the database, but 
> not those which just read it...
> Simon.

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

[sqlite] Logging only SQL commands which make changes, parsing PRAGMAs

[Simon Slavin]

I'm trying to log SQL commands which might make changes to the database, but 
not those which just read it.  This routine does /not/ need to deal with 
arbitrary tricksy SQL commands generated by third parties, just SQL commands I 
myself have written or my program has generated, intended specifically for 
SQLite.  And I'm going to assume that I will be sensible when I program so my 
commands won't start with spaces or play mind games.

Since this routine introduces load on every SQL command, it's okay to have a 
few false positives (but preferably not false negatives), if this makes the 
test simpler and faster.  So as a first approximation it logged everything 
except commands which start with 'SELECT'.  Which is fine.

But I do PRAGMAs every so often and now I notice a few PRAGMA commands in my 
logs which don't need to be there because they just test modes and schema.  So 
I want to log PRAGMA commands which might change things, and ignore those that 
won't.  So does the list think that simply looking for commands which start 
with 'PRAGMA' and have an '=' in them is a good fast way to do this ?  Or is 
there a better way that's still pretty fast ?

The routine is written in PHP, so I don't have access to the low-level VM parts 
of SQLite to put traps in.

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

[sqlite] to table update

[yanhong.ye]

update tb1 set col1=(select col1 from tb2 ) where tb1.co2=tb2.co2;

it couldn't work

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

[sqlite] SQLite - Database disk size difference when tables copied

[Madhur Kashyap]

Hello Pavel,

> >* When I implement the same mechanism for saving the*> >* memory database 
> >back to disk, the size of disk file is 4x of the original*> >* disk file 
> >size.*
> What is "original disk file size" here? Is it an empty database,
> database with some data, database with exactly the same data you are
> restoring? If the latter then do you restore right over the existing
> data? If yes how?


The original database on disk was created from scratch in memory and saved
"backup" command in regular "tclsh". Afterwards it was restored using
CREATE TABLE in the EDA tool as mentioned below

dbcmd eval "CREATE TABLE '$tname' AS SELECT * FROM loadfrom.'$tname'"

Once it is restored in the EDA tool memory, it is saved back on disk using
the commands I mentioned in the first email.

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