Re: [sqlite] Crash Report

[Richard Hipp]

On 9/18/16, Keith Medcalf  wrote:
>
> Somewhere between:
>
> 2016-09-03 16:23:42 672c21bcf09c5bfb67e061456a56be45409c4f34
>
> 2016-09-09 20:23:59 19e2e5950541f1a93eed994cc2b1eaf64b68e858
>
> where the former works and the later crashes (with a wunderful new fangled
> Windows 10 content free  error message) ((I do not have all versions from
> the fossil repository in my private repository)
>
> also fails on head of trunk
>
> 2016-09-18 16:08:10 6bf5ba10d28f1b0a32aa9a560ae3143a1235eadb

I cannot get it to fail - probably because I do not have data.

Can you please recompile with -DSQLITE_DEBUG than add "PRAGMA
vdbe_debug=ON;" just before the UPDATE, and send me the output?
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Potential bug: Database on gvfs mount cannot be committed to

[Simon Slavin]

On 19 Sep 2016, at 2:52am, Keith Medcalf  wrote:

> That is to say there is no difference between a block device (such as a 
> physical hard disk) attached to the computer via a 1 foot SCSI cable and an 
> iSCSI LUN where the iSCSI block device is located on a different plant, other 
> than the latency of the command/response.  In both cases you are using a 
> "Local" filesystem.
> 
> This is vastly different from mounting a "remote shared filesystem" from 
> another computer, whether that computer is located in the next rack slot or 
> located on another planet.

And it's going to get worse since the next generation is 'cloud storage' which 
looks like what you've called "remote shared filesystem" but has no fixed 
hardware.  It can be addressed using a network file system (SMB2 / CIFS / NFS) 
but the programmer will have no idea whether their data ends up on a rotating 
disk next door or spread over six SSDs on four continents.  So on top of the 
current problems it exhibits unpredictable timing problems.

We just have to pray that the network file system implements locking properly.  
Which SMB2 does allow, but I have no idea whether any drivers actually do it 
correctly.

Hmm.  Earlier this year Apple announced it's working a new file system, 
designed for the modern age with some pretty neat abilities.  It's suited to 
everything from Smart Cards to snapshottable virtual partitions in the cloud.  
Apple said it will release open source drivers and make the whole thing 
unencumbered by patents.  Now if it could just do the same thing with an API 
which implements locking correctly ...

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

Re: [sqlite] Potential bug: Database on gvfs mount cannot be committed to

[Keith Medcalf]

No database server product of which I am aware will "work" properly when the 
database resides on a remote filesystem.  There is a *vast* difference between 
a "remote file system" and a "local file system on a remote block device".

There is no difference between a "remote block device" known as a Hard Disk 
that is remotely attached via a SCSI/SATA/PATA/IDE/MDM cable on which the Local 
Computer creates a local filesystem for its own exclusive use, and a "remote 
block device" that is attached via iSCSI or Fiber Channel on which the Local 
Computer creates a local filesystem for its own exclusive use, except for the 
length of the cable connecting the "Local Computer" to the block storage device.

That is to say there is no difference between a block device (such as a 
physical hard disk) attached to the computer via a 1 foot SCSI cable and an 
iSCSI LUN where the iSCSI block device is located on a different plant, other 
than the latency of the command/response.  In both cases you are using a 
"Local" filesystem.

This is vastly different from mounting a "remote shared filesystem" from 
another computer, whether that computer is located in the next rack slot or 
located on another planet.

The confusion stems from the ill-advised usage of the inaccurate term "Network 
Attached Storage" by the ill-educated to refer to both "Remote Block Storage 
with a Local Filesystem" and "Remote Shared Filesystem", and the propensity of 
(some) of those hearing (or reading) the terms to make inappropriate 
assumptions about the meaning of the terms without understanding the 
consequences of those assumptions.

Practically every device connected to the Internet qualifies as "Network 
Attached Storage" and from that perspective, the Internet is just a great big 
"Storage Area Network".

Neither of those terms have anything to do with whether the filesystem is local 
or remote.  And the ability to create a "snapshot" does not require a Remote 
File System, nor does it require a Local File System.  In fact, it is 
completely independent of where the Filesystem is locatedhow it is attached, or 
the physical storage back-end.

> * R. Smith:
 
> > Enterprise DBs have servers on the same machine as the Files they
> > access, they do not actually use the network file-system to access the
> > DB data-files over the network from multiple clients, or even servers
> > (unless the DBs are partitioned so and ONLY accessed by the single
> > process so locking is permanent and moot).
 
> Deployments vary considerably.  A lot of enterprises use snapshotable
> storage for databases as well, just as an option in case upgrades need
> to be rolled back.




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

Re: [sqlite] Potential bug: Database on gvfs mount cannot be committed to

[Stephen Chrzanowski]

"snapshotable" or not, DBs are accessed from the local file system, not
from a network where another OS has control of the file system.

On Sun, Sep 18, 2016 at 10:16 AM, Florian Weimer  wrote:

> * R. Smith:
>
> > Enterprise DBs have servers on the same machine as the Files they
> > access, they do not actually use the network file-system to access the
> > DB data-files over the network from multiple clients, or even servers
> > (unless the DBs are partitioned so and ONLY accessed by the single
> > process so locking is permanent and moot).
>
> Deployments vary considerably.  A lot of enterprises use snapshotable
> storage for databases as well, just as an option in case upgrades need
> to be rolled back.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Crash Report

[Keith Medcalf]

Somewhere between:

2016-09-03 16:23:42 672c21bcf09c5bfb67e061456a56be45409c4f34 

2016-09-09 20:23:59 19e2e5950541f1a93eed994cc2b1eaf64b68e858

where the former works and the later crashes (with a wunderful new fangled 
Windows 10 content free  error message) ((I do not have all versions from the 
fossil repository in my private repository)

also fails on head of trunk

2016-09-18 16:08:10 6bf5ba10d28f1b0a32aa9a560ae3143a1235eadb

(Compiled for Window 32-bit using MinGW gcc version 4.9.3 
(x86_64-win32-sjlj-rev1, Built by MinGW-W64 project)
compiler options -m32 -s -O2 -mwin32 -pipe -falign-functions=16 
-falign-loops=16 -flto

on a query of the following form:

update program
   set showType = 'Special'
 where showType in ('Series', 'Sports')
   and program in (select distinct program
 from genrlink, genre
where genrlink.genre = genre.genre
  and class in ('Special', 'Olympics'));

.schema -indent

CREATE TABLE advisory(
  advisory integer primary key,
  advisoryname text collate nocase not null default 'Unknown' unique
);
CREATE TABLE advlink(
  program text not null collate nocase,
  advisory integer not null,
  primary key(program, advisory)
) WITHOUT ROWID;
CREATE TABLE crew(
  crew integer primary key,
  surname text collate nocase not null default '',
  givenname text collate nocase not null default '',
  unique(surname, givenname)
);
CREATE TABLE crewlink(
  program text not null collate nocase,
  crew integer not null,
  role integer not null,
  primary key(program, role, crew)
) WITHOUT ROWID;
CREATE TABLE genre(
  genre integer primary key,
  class text collate nocase not null default 'Unknown' unique
);
CREATE TABLE genrlink(
  program text not null collate nocase,
  genre integer not null,
  relevance integer not null default 0,
  primary key(program, relevance, genre)
) WITHOUT ROWID;
CREATE TABLE lineup(
  lineup text not null collate nocase primary key,
  name text not null collate nocase default 'No Name Given',
  device text not null collate nocase default 'No Device Given',
  location text not null collate nocase default 'No Location Given',
  postalcode text not null collate nocase default '',
  type text not null collate nocase default ''
) WITHOUT ROWID;
CREATE TABLE map(
  lineup text not null collate nocase,
  station text not null collate nocase,
  channel integer not null,
  validfrom integer not null,
  validto integer not null,
  primary key(lineup, station, channel, validfrom, validto)
) WITHOUT ROWID;
CREATE TABLE program(
  program text not null collate nocase primary key,
  showType text not null collate nocase default '',
  series text collate nocase,
  title text collate nocase,
  subtitle text collate nocase,
  description text not null collate nocase default 'No Description Available',
  colorCode text not null collate nocase default 'Color',
  starRating text not null collate nocase default '',
  originalAirDate date,
  syndicatedEpisodeNumber text collate nocase,
  year integer,
  mpaaRating text default '' collate nocase,
  runTime integer
) WITHOUT ROWID;
CREATE TABLE role(
  role integer primary key,
  rolename text collate nocase not null default 'Unknown Role' unique
);
CREATE TABLE schedule(
  time integer not null,
  endtime integer not null,
  station text not null collate nocase,
  program text not null collate nocase,
  duration integer not null,
  tvRating text not null default '' collate nocase,
  stereo integer not null default 0,
  ei integer not null default 0,
  subtitled integer not null default 0,
  hdtv integer not null default 0,
  new integer not null default 0,
  dolby integer not null default 0,
  closeCaptioned integer not null default 0,
  part integer,
  parts integer,
  live_tape text collate nocase,
  primary key(time, station)
) WITHOUT ROWID;
CREATE TABLE station(
  station text not null collate nocase primary key,
  callsign text not null collate nocase,
  name text not null collate nocase default 'No Name Given',
  affiliate text not null collate nocase default 'Independant',
  fccChannelNumber integer
) WITHOUT ROWID;
CREATE TABLE mySeries(series text collate nocase primary key) WITHOUT ROWID;
CREATE TABLE myChannels(
  channel integer not null,
  lineup text not null,
  primary key(lineup, channel)
) WITHOUT ROWID;
CREATE TABLE myTitles(
  selected text collate nocase,
  title text collate nocase primary key
);
CREATE INDEX stationschedule on schedule(
  station,
  time,
  program,
  endtime,
  duration
);
CREATE INDEX stationcallsign on station(callsign, station);
CREATE INDEX programTitle on program(title);
CREATE UNIQUE INDEX advisoryinverse on advisory(advisoryname, advisory);
CREATE UNIQUE INDEX advisoryforward on advisory(advisory, advisoryname);
CREATE UNIQUE INDEX advlinkinverse on advlink(advisory, program);
CREATE UNIQUE INDEX crewforward on crew(crew, surname, givenname);
CREATE UNIQUE INDEX crewinverse on crew(surname, givenname, crew);
CREATE UNIQUE INDEX 

Re: [sqlite] Virtual table

[Eduardo Morras]

On Wed, 31 Aug 2016 10:34:05 -0300
Maria de Jesus Philadelpho  wrote:

> Hi,
> 
> I implement the SQLite extension, virtcsv, which allows attaching a
> CSV file as a virtual table.  At a command line everything works just
> fine, why I can't see the result of a select * from table using the
> SQLitestudio 3.0.7?

Beacuse Sqlite Studio must load the Sqlite csv virtual table before
use, or be compiled with it.

Read Sqlite Studio manual how to achive that, I don't use SqliteStudio.

Perhaps you can get better answers from Sqlite Studio developers.

> See the attached snapshot.

Sqlite maillist don't support attachment.

> regards,
> 
> Maria Azevedo
> --
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


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

Re: [sqlite] Virtual table

[John G]

1) Can't see the 'attachment' - this list does not accept them.
Can you copy/paste sample code into an email?

John G



On 31 August 2016 at 14:34, Maria de Jesus Philadelpho <
jesus.ph...@gmail.com> wrote:

> Hi,
>
> I implement the SQLite extension, virtcsv, which allows attaching a CSV
> file as a virtual table.  At a command line everything works just fine, why
> I can't see the result of a select * from table using the SQLitestudio
> 3.0.7?
> See the attached snapshot.
>
> regards,
>
> Maria Azevedo
> --
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] rowid changing....

[mikeegg1]

Thanks everyone. I was mis-equating REPLACE with UPDATE. I’ll change my code to 
“INSERT or IGNORE” and add an UPDATE. 

Mike

> On Sep 18, 2016, at 05:34, Keith Medcalf  wrote:
> 
> 
> On Saturday, 17 September, 2016 21:35, mikeeggl asked:
> 
>> In my DDL I have “integer primary key not null” on my tables. When I do
>> the first insert to these tables the rowid is assigned. The insert
>> statement is a “insert or replace into…” so I don’t have to deal with
>> insertion errors. However, I expected the rowid to not change when the row
>> has not changed (when the replace is part of the clause is used). Is there
>> a way to not have the rowid changed when “insert or replace into…” is
>> used?
> 
> Specify the integer primary key in the insert or replace?
> 
> "INSERT OR REPLACE"
> 
> INSERTs a row if it does not cause an integrity violation.
> if the attempt to INSERT the row causes an integrity violation, then REPLACE 
> deletes (all) the conflicting row(s) then does the INSERT.
> 
> If no integer primary key value is specified (ie, is null) when the INSERT 
> (either the initial INSERT, or the one after the conflicting rows have been 
> removed), then a new integer primary key is generated.
> 
> If you do not want the OR REPLACE then perhaps OR IGNORE is what you want.  
> Possibly preceded by one or more conditional UPDATE(s) to update any 
> pre-existing rows.
> 
> INTEGER PRIMARY KEY is always not null since the ROWID cannot be null.  Your 
> specification of not null is redundant.  The specification of NULL or NOT 
> NULL on integer primary key columns (even for WITHOUT ROWID tables) is 
> silently ignored.
> 
> 
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] Another unexpected source of SQLITE_BUSY_SNAPSHOT

[Florian Weimer]

* Dan Kennedy:

>> My concern is about sqlite3_step(UPDATE) without a following
>> sqlite3_reset(UPDATE).  Perhaps I should change my wrapper to
>> unconditionally call sqlite3_reset() after DML-related sqlite3_step(),
>> whether the stepping operation succeeded or not.
>
> For a DML statement, I usually just do:
>
>   sqlite3_step(pStmt);
>   rc = sqlite3_reset(pStmt);
>   if( rc!=SQLITE_OK ){
> /* Handle error */
>   }
>
> I don't see why you would want to call step() but not reset() for a
> DML operation. And since reset() always returns either the same or a
> more specific error code, there isn't too much reason to ever catch
> the return value of step(). IMO.

The reset operation acquires another mutex.  I have not investigated
this in detail yet, but even in the non-contended case, this mutex
operation showed up in profiles for me (with a heavy INSERT workload,
rows mostly in the order of the INTEGER NOT NULL PRIMARY KEY).
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Potential bug: Database on gvfs mount cannot be committed to

[Florian Weimer]

* R. Smith:

> Enterprise DBs have servers on the same machine as the Files they
> access, they do not actually use the network file-system to access the
> DB data-files over the network from multiple clients, or even servers
> (unless the DBs are partitioned so and ONLY accessed by the single
> process so locking is permanent and moot).

Deployments vary considerably.  A lot of enterprises use snapshotable
storage for databases as well, just as an option in case upgrades need
to be rolled back.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] rowid changing....

[Keith Medcalf]

On Saturday, 17 September, 2016 21:35, mikeeggl asked:

> In my DDL I have “integer primary key not null” on my tables. When I do
> the first insert to these tables the rowid is assigned. The insert
> statement is a “insert or replace into…” so I don’t have to deal with
> insertion errors. However, I expected the rowid to not change when the row
> has not changed (when the replace is part of the clause is used). Is there
> a way to not have the rowid changed when “insert or replace into…” is
> used?

Specify the integer primary key in the insert or replace?

"INSERT OR REPLACE"

INSERTs a row if it does not cause an integrity violation.
if the attempt to INSERT the row causes an integrity violation, then REPLACE 
deletes (all) the conflicting row(s) then does the INSERT.

If no integer primary key value is specified (ie, is null) when the INSERT 
(either the initial INSERT, or the one after the conflicting rows have been 
removed), then a new integer primary key is generated.

If you do not want the OR REPLACE then perhaps OR IGNORE is what you want.  
Possibly preceded by one or more conditional UPDATE(s) to update any 
pre-existing rows.

INTEGER PRIMARY KEY is always not null since the ROWID cannot be null.  Your 
specification of not null is redundant.  The specification of NULL or NOT NULL 
on integer primary key columns (even for WITHOUT ROWID tables) is silently 
ignored.





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

Re: [sqlite] rowid changing....

[Simon Slavin]

On 18 Sep 2016, at 7:21am, Clemens Ladisch  wrote:

> mikeegg1 wrote:
>> The insert statement is a “insert or replace into…” so I don’t have to
>> deal with insertion errors. However, I expected the rowid to not change
>> when the row has not changed.
> 
> The OR REPLACE clause just deletes any old row.
> 
> If you want to update the old row, use UPDATE.  (And there's no good way
> to do this with a single SQL statement.)

Right.  If you want the same functionality as INSERT OR REPLACE then do

INSERT OR IGNORE ...
UPDATE ...

If the existing row already exists then the new one won't be inserted, but the 
UPDATE will affect both new and old rows.

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

Re: [sqlite] rowid changing....

[Clemens Ladisch]

mikeegg1 wrote:
> The insert statement is a “insert or replace into…” so I don’t have to
> deal with insertion errors. However, I expected the rowid to not change
> when the row has not changed.

The OR REPLACE clause just deletes any old row.

If you want to update the old row, use UPDATE.  (And there's no good way
to do this with a single SQL statement.)


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users