Re: [sqlite] test/exists.test 2.$tn.1 fails if both DBs are in WAL mode

[Dan Kennedy]

 >> For me, this test from test/exists.test fails if both database
 >> connections are opened in WAL mode, that is if "PRAGMA journal_mode=WAL"
 >> is issued right after each DB is opened.
 >>
 >># TABLE objects.
 >>#
 >>do_test   {
 >>  sql1 { DROP TABLE IF EXISTS t1 }
 >>  sql2 { CREATE TABLE t1(x) }
 >>  sql1 { DROP TABLE IF EXISTS t1 }
 >>  sql2 { SELECT name FROM sqlite_master WHERE type = 'table' }
 >>} {}

On 04/10/2011 04:12 PM, Ralf Junker wrote:
> The test passes well if I recreate the database file anew just prior to
> running it so it acts upon an empty database.

The [do_multiclient_test] is supposed to delete the database file
before running the script passed as the third argument (of which
this [do_test] is a part). So you're right, it is supposed to run
starting with an empty database.

Dan.

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

Re: [sqlite] Can't load sqlite 3.75 under tcl 8.4.4.0

[Dan Kennedy]

On 04/10/2011 11:43 AM, thoselai...@shaw.ca wrote:
> % package require sqlite
> couldn't load library "C:/Tcl/lib/sqlite3/sqlite3/.dll": this library or a 
> dependent library could not be found in library path
>
> What is sqlite3/.dll ?
> Where did that "/" come from?
>
> The pkgindex file contains -
>
> package ifneeded sqlite 3.7.5 [list load [file join $dir \
>  sqlite3[info sharedlibextension]] sqlite3]

Maybe there is a space or tab or something between "sqlite3" and
"[info".

Does the command "load C:/Tcl/lib/sqlite3/sqlite3.dll" work if
you enter it directly?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite question

[Igor Tandetnik]

Guilherme  wrote:
> Well, I thought that if the execution followed a plan.. I could get for
> example, the cardinality from the results until a point, say a join.

I don't see how the conclusion follows from the premise. Why does "follows 
execution plan" have to imply "provides ready access to the data I happen to 
want"?

> Imagine a query with 15 joins.. the execution would make a join

What exactly does "make a join" mean? A join is a declarative construct - it 
specifies *what* data you want, not *how* to procur it. The query engine then 
does whatever it deems necessary to produce the data you've described with a 
join. 

> than, with
> the results from this join, make another join with results form other
> joins.. and so on..

That's not how database engines normally work. They don't explicitly produce 
intermediate resultsets.

Imagine that you have a phonebook, sorted by last name, first name. Suppose you 
want to find all people named John Smith. Would you first write down all 
Smiths, then go through that intermediate list looking for Johns? Or would you 
check for the first name as you go through the Smiths in the phonebook?

> I could get the cardinalities before the joins

You are engaging in wishful thinking.
-- 
Igor Tandetnik

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

Re: [sqlite] Effect of using WAL on technical support

[Simon Slavin]

On 11 Apr 2011, at 2:48am, Richard Hipp wrote:

> On Sun, Apr 10, 2011 at 9:36 PM, Samuel Neff  wrote:
> 
>> I'm sorry, my statement was misleading.  I'm referring to immediately after
>> our application is closed.
>> 
>> We're seeing that even if the application is gracefully shut down, the -wal
>> and -shm files are still there.  In order to clear them I need to open the
>> database files with sqlite3.exe and issue a "pragma wal_checkpoint".
> 
> The -wal and -shm are deleted when the last connection to the database
> closes.  If you are having -wal and -shm files left over, that implies that
> you are not closing all your database connections before you exit.

Yes !  If your application has exited and you still have a journal file, 
something is wrong.  Are you closing your connection correctly, however ASP.NET 
wants you to do it ?

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

Re: [sqlite] Effect of using WAL on technical support

[Richard Hipp]

On Sun, Apr 10, 2011 at 9:36 PM, Samuel Neff  wrote:

> I'm sorry, my statement was misleading.  I'm referring to immediately after
> our application is closed.
>
> We're seeing that even if the application is gracefully shut down, the -wal
> and -shm files are still there.  In order to clear them I need to open the
> database files with sqlite3.exe and issue a "pragma wal_checkpoint".
>

The -wal and -shm are deleted when the last connection to the database
closes.  If you are having -wal and -shm files left over, that implies that
you are not closing all your database connections before you exit.


>
> I'm testing on Windows 7 with ASP.NET applications.
>
> Thanks,
>
> Sam
>
>
>
> On Sun, Apr 10, 2011 at 9:10 PM, Simon Slavin 
> wrote:
>
> >
> > On 11 Apr 2011, at 2:04am, Samuel Neff wrote:
> >
> > > I'm interested in hearing anyone's experiences of using WAL journal
> mode
> > on
> > > technical support.  We often have to copy databases to attach to
> customer
> > > reports and if the someone were to copy the database file while there
> is
> > an
> > > active -wal file then we would very likely be missing the most
> up-to-date
> > > data in the copy.  I'm not sure if we can rely on support to issue a
> > pragma
> > > wal_checkpoint prior to doing the copy.
> >
> > My understanding is that no matter what journaling mode you're using, a
> > straight copy of the data file is not 'safe' while the database is open.
>  If
> > you want to backup the data, keep the database closed, or use the special
> > SQLite backup API.
> >
> > 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

Re: [sqlite] Effect of using WAL on technical support

[Samuel Neff]

I'm sorry, my statement was misleading.  I'm referring to immediately after
our application is closed.

We're seeing that even if the application is gracefully shut down, the -wal
and -shm files are still there.  In order to clear them I need to open the
database files with sqlite3.exe and issue a "pragma wal_checkpoint".

I'm testing on Windows 7 with ASP.NET applications.

Thanks,

Sam



On Sun, Apr 10, 2011 at 9:10 PM, Simon Slavin  wrote:

>
> On 11 Apr 2011, at 2:04am, Samuel Neff wrote:
>
> > I'm interested in hearing anyone's experiences of using WAL journal mode
> on
> > technical support.  We often have to copy databases to attach to customer
> > reports and if the someone were to copy the database file while there is
> an
> > active -wal file then we would very likely be missing the most up-to-date
> > data in the copy.  I'm not sure if we can rely on support to issue a
> pragma
> > wal_checkpoint prior to doing the copy.
>
> My understanding is that no matter what journaling mode you're using, a
> straight copy of the data file is not 'safe' while the database is open.  If
> you want to backup the data, keep the database closed, or use the special
> SQLite backup API.
>
> 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] Effect of using WAL on technical support

[Simon Slavin]

On 11 Apr 2011, at 2:04am, Samuel Neff wrote:

> I'm interested in hearing anyone's experiences of using WAL journal mode on
> technical support.  We often have to copy databases to attach to customer
> reports and if the someone were to copy the database file while there is an
> active -wal file then we would very likely be missing the most up-to-date
> data in the copy.  I'm not sure if we can rely on support to issue a pragma
> wal_checkpoint prior to doing the copy.

My understanding is that no matter what journaling mode you're using, a 
straight copy of the data file is not 'safe' while the database is open.  If 
you want to backup the data, keep the database closed, or use the special 
SQLite backup API.

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

[sqlite] Effect of using WAL on technical support

[Samuel Neff]

I'm interested in hearing anyone's experiences of using WAL journal mode on
technical support.  We often have to copy databases to attach to customer
reports and if the someone were to copy the database file while there is an
active -wal file then we would very likely be missing the most up-to-date
data in the copy.  I'm not sure if we can rely on support to issue a pragma
wal_checkpoint prior to doing the copy.

Thanks,

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

Re: [sqlite] SQLite question

[Igor Tandetnik]

Guilherme  wrote:
> I believed that databases in general evaluate their queries, following a
> execution plan...
> e.g. first do a table scan and apply a filter from the where clause.. than
> use the results to execute a join with other table.. than use the results to
> excute with another join..
> 
> but now, from what I've seen, sqlite just does a nested loop, and returns
> (if available) a row in each loop...

I don't understand the difference. How does "does a nested loop" differ from 
"follows an execution plan"? SQLite follows an execution plan that, often, 
involves a loop. How exactly do you think other databases "do a table scan", if 
not with a loop?

In SQLite, execute any SQL statement with the keyword EXPLAIN prepended, e.g. 
"explain select * from SomeTable;". The output is SQLite's execution plan for 
that query. See also:

http://sqlite.org/vdbe.html
http://sqlite.org/opcode.html

-- 
Igor Tandetnik

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

[sqlite] SQLite question

[Guilherme]

Hi!

I believed that databases in general evaluate their queries, following a
execution plan...
e.g. first do a table scan and apply a filter from the where clause.. than
use the results to execute a join with other table.. than use the results to
excute with another join..

but now, from what I've seen, sqlite just does a nested loop, and returns
(if available) a row in each loop...
This type of execution is specific from sqlite, or there are more databases
that use the same algorithm?

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

Re: [sqlite] Sqlite database corrupted

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 04/10/2011 07:58 AM, mcnamaragio wrote:
> Could this have caused database corruption? 

SQLite does not have ordinary commands that will corrupt the database.  How
to corrupt the database is listed in the doc:

  http://www.sqlite.org/lockingv3.html#how_to_corrupt

The chance of there being a bug in SQLite is extremely small.  First of all
some of the other billions of users would have noticed.  Secondly the
testing is extraordinary:

  http://www.sqlite.org/testing.html

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAk2h+vEACgkQmOOfHg372QRZtgCffHGOW9XxEiJC/r6A5Fw1l4b7
MNAAnj8mTgriEcxuLd5D6kaw8CCmYw/2
=nlG8
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite database corrupted

[mcnamaragio]

Thanks. 

I have found that the select statement was issued inside a transaction. 
Could this cause sqlite to make changes to the database? I also observed 
that if I mark the database as readonly and issue a select statement inside 
a transaction (what the application was doing) I get "attempt to write a 
readonly database" error so it looks like sqlite is trying to have write 
access to the database even thought it is not needed. 

Could this have caused database corruption? 


Simon Slavin-3 wrote:
> 
> On 9 Apr 2011, at 02:47 PM, giorgi giorgi  wrote:
> 
>> The application is only issuing select statements (no
>> insert/update/delete
>> or vacuum) so I cannot understand what could have caused database
>> corruption.
> 
> Almost definitely a problem with your hardware, or possibly an
> operating-system glitch.  I'm not going to pretend that SQLite is bug-free
> but if you're not executing write commands it has no reason to write
> anything at all.
> 
> Simon
> -- 
> Sent while away from my computer.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://old.nabble.com/Sqlite-database-corrupted-tp31359173p31364155.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

[sqlite] Sqlite database corrupted

[Giorgi D]

Thanks.

I have found that the select statement was issued inside a transaction.
Could this cause sqlite to make changes to the database? I also observed
that if I mark the database as readonly and issue a select statement inside
a transaction (what the application was doing) I get "attempt to write a
readonly database" error so it looks like sqlite is trying to have write
access to the database even thought it is not needed.

Could this have caused database corruption?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] test/exists.test 2.$tn.1 fails if both DBs are in WAL mode

[Ralf Junker]

For me, this test from test/exists.test fails if both database
connections are opened in WAL mode, that is if "PRAGMA journal_mode=WAL"
is issued right after each DB is opened.

  # TABLE objects.
  #
  do_test{
sql1 { DROP TABLE IF EXISTS t1 }
sql2 { CREATE TABLE t1(x) }
sql1 { DROP TABLE IF EXISTS t1 }
sql2 { SELECT name FROM sqlite_master WHERE type = 'table' }
  } {}

This is against http://www.sqlite.org/src/info/f346dae127.

I am running a ported version of the test instead of the original tcl so
my finding could wrong. Still, I believe it better to let you know in
case you want to check this last-minute change before you release 3.7.6.

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

Re: [sqlite] test/exists.test 2.$tn.1 fails if both DBs are in WAL mode

[Ralf Junker]

The test passes well if I recreate the database file anew just prior to
running it so it acts upon an empty database.

If this is what is intended, I apologize for the false alarm.

Ralf

On 10.04.2011 10:28, Ralf Junker wrote:

> For me, this test from test/exists.test fails if both database
> connections are opened in WAL mode, that is if "PRAGMA journal_mode=WAL"
> is issued right after each DB is opened.
> 
>   # TABLE objects.
>   #
>   do_test  {
> sql1 { DROP TABLE IF EXISTS t1 }
> sql2 { CREATE TABLE t1(x) }
> sql1 { DROP TABLE IF EXISTS t1 }
> sql2 { SELECT name FROM sqlite_master WHERE type = 'table' }
>   } {}
> 
> This is against http://www.sqlite.org/src/info/f346dae127.
> 
> I am running a ported version of the test instead of the original tcl so
> my finding could wrong. Still, I believe it better to let you know in
> case you want to check this last-minute change before you release 3.7.6.
> 
> Ralf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] test/exists.test 2.$tn.1 fails if both DBs are in WAL mode

[Ralf Junker]

For me, this test from test/exists.test fails if both database
connections are opened in WAL mode, that is if "PRAGMA journal_mode=WAL"
is issued right after each DB is opened.

  # TABLE objects.
  #
  do_test{
sql1 { DROP TABLE IF EXISTS t1 }
sql2 { CREATE TABLE t1(x) }
sql1 { DROP TABLE IF EXISTS t1 }
sql2 { SELECT name FROM sqlite_master WHERE type = 'table' }
  } {}

This is against http://www.sqlite.org/src/info/f346dae127.

I am running a ported version of the test instead of the original tcl so
my finding could wrong. Still, I believe it better to let you know in
case you want to check this last-minute change before you release 3.7.6.

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