[sqlite] whish list for 2016

[Christian Schmitz]

> Am 20.12.2015 um 19:12 schrieb Big Stone :
> 
> Hi All,
> 
> To prepare for 2016 greetings moment, here is my personnal whish list

Unless I missed something, I may suggest

* moveprev
* movefirst
* movelast
* move to record in cursor with given index  


Looping over a recordset twice is often useful.

Sincerely
Christian

-- 
Read our blog about news on our plugins:

http://www.mbsplugins.de/

[sqlite] whish list for 2016

[Domingo Alvarez Duarte]

I think this is a very good feature to have, thumbs up !  

?  
>  Tue Jan 12 2016 9:50:33 pm CET CET from "James K. Lowden"
>  Subject: Re: [sqlite] whish list for 2016
>
>  On Fri, 8 Jan 2016 08:28:29 +0100
> Dominique Devienne  wrote:
> 
>  
>>  
>>>One way to do that would be to honor a special user-created table,
>>> say "PRAGMAS", with name-value pairs that are automatically applied
>>> when the database is opened.
>>> 
>>> 

>>  Interesting idea. A "special"-to-SQLite sqlite_pragmas table, similar
>> to sqlite_master. 
>> 

>  Similar in name, yes. sqlite_master is special in that it's built into
> the database file structure, though. You couldn't add another such
> table without changing the file structure, something OFL understandably
> doesn't want to do. 
> 
> I'm suggesting that the presence of a user-created 
> "configuration table" would govern the behavior of the database,
> analogous to a configuration file. 
> 
> --jkl
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
>
>  



?

[sqlite] whish list for 2016

[Keith Medcalf]

On Tuesday, 12 January, 2016 13:58, Christian Schmitz  said:

> Unless I missed something, I may suggest

> * moveprev
> * movefirst
> * movelast
> * move to record in cursor with given index

There are no relational database engines on the market that can do this.  Those 
that *appear* to be able to do this are using some sleight of hand, either 
entirely on the client side (most common), or using some "server side 
assistance" so that the result set does not have to be cached in its entirety 
on the client.

Server side assistance was developed because of the propensity, once the client 
side feature was made "built-in", of the great unwashed to do things like 
"select * from customers;" and use the resulting client side cursor to scroll 
through the 200 million customer records with their 6 line GUI.  Needless to 
say, performance was somewhat suboptimal to say the least.  If you have ever 
used DSA.MSC to browse an Active Directory domain containing a billion objects 
then you know exactly what I mean.

Generally speaking, you have the choice of "server side" or "client side", 
either "complete set" or "keyset".  Client-Side Complete Set works as per the 
above -- the result set is sent into a row cache on the client, and the 
application fiddles with this rowset to make it look like you are going forward 
and backward (though you are not).  There is no further involvement of the 
database server.  Server-Side Set works the same way but the result set is 
cache in a temp table on the server indexed by ordinal position of the result 
row.  Magically created queries against this temp table make it look like you 
are scrolling the result set.

Keyset driven scrollable cursors work in similar fashion, except that instead 
caching the entire result row, only the primary keys of the source data rows 
are saved in the cache, with the same ordinal position primary key.  This 
keyset is stored on either the client-side or the server-side (in a temp 
table).  By the client submitting the correct queries, it can be made to appear 
that you have a scrollable cursor.  The advantage of a keyset is twofold -- it 
is often smaller than the full result set, and since it contains the primary 
keys of the underlying tables, one can do an UPDATE sourcetable ...WHERE 
CURRENT OF SET.

By design a Relational DBMS only has a concept of a result set and is not 
navigable except through artifice and trickery.  If you want a navigable 
database they you want either a Hierarchical, Network, or Network Extended 
database model, not relational.

> Looping over a recordset twice is often useful.

Simply resubmit the original query.  If you need repeatable read, do both 
inside the same transaction.

[sqlite] whish list for 2016

[Keith Medcalf]

On Tuesday, 12 January, 2016 13:51, James K. Lowden  said:

> On Fri, 8 Jan 2016 08:28:29 +0100
> Dominique Devienne  wrote:

> > > One way to do that would be to honor a special user-created table,
> > > say "PRAGMAS", with name-value pairs that are automatically applied
> > > when the database is opened.

> > Interesting idea. A "special"-to-SQLite sqlite_pragmas table, similar
> > to sqlite_master.

> Similar in name, yes.  sqlite_master is special in that it's built into
> the database file structure, though.  You couldn't add another such
> table without changing the file structure, something OFL understandably
> doesn't want to do.

> I'm suggesting that the presence of a user-created
> "configuration table" would govern the behavior of the database,
> analogous to a configuration file.

I would suppose that one could implement this using an SQLITE_EXTRA_INIT linked 
function (a symbol attached to SQLITE_EXTRA_INIT is called at the end of the 
sqlite3_initialize routine to perform extra initialization).  Pointing this to 
a function which calls a bunch of sqlite3_auto_extension to add internally 
compiled extension init routines to the initialization of each connection thus 
making such extensions available to all connections by auto-magic.

I suppose one could write some code which looks for the desired table and, if 
found, reads and executes the statements contained therein.

The entry point of this routine can be added into the auto_extension list (even 
though it is not an extension) through the EXTRA_INIT hook.

>From my reading of the code, a pointer in the auto_extension list does not 
>*have* to actually be an extension -- it is just code that is called when a 
>connection object is created that receives the connection object as an 
>argument.  If it *is* an extension init routine, it just happens to make some 
>calls to add functions/aggregates/modules/etc., but I see nothing to indicate 
>that this is actually required.

The only thing you will NOT be able to do this way is anything that needs to be 
set *before* initialization.  Running a set of pragma's on each connection 
should work just peachy though, I would expect.

[sqlite] whish list for 2016

[Richard Hipp]

On 1/12/16, Christian Schmitz  wrote:
>
> Unless I missed something, I may suggest
>
> * moveprev
> * movefirst
> * movelast
> * move to record in cursor with given index

Not possible, at least not in general.  Something like this could be
done for the simpliest queries against a single real table.  But for
joins (especially LEFT JOINs) or queries containing subqueries or
queries against virtual tables or table-valued functions or queries
involving common-table-expressions, this is not possible.
-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] whish list for 2016

[Scott Hess]

On Tue, Jan 12, 2016 at 3:43 PM, Keith Medcalf  wrote:

> On Tuesday, 12 January, 2016 13:51, James K. Lowden <
> jklowden at schemamania.org> said:
> > On Fri, 8 Jan 2016 08:28:29 +0100
> > Dominique Devienne  wrote:
> > > > One way to do that would be to honor a special user-created table,
> > > > say "PRAGMAS", with name-value pairs that are automatically applied
> > > > when the database is opened.
> > > Interesting idea. A "special"-to-SQLite sqlite_pragmas table, similar
> > > to sqlite_master.
> > Similar in name, yes.  sqlite_master is special in that it's built into
> > the database file structure, though.  You couldn't add another such
> > table without changing the file structure, something OFL understandably
> > doesn't want to do.
>
> > I'm suggesting that the presence of a user-created
> > "configuration table" would govern the behavior of the database,
> > analogous to a configuration file.
>
> I would suppose that one could implement this using an SQLITE_EXTRA_INIT
> linked function (a symbol attached to SQLITE_EXTRA_INIT is called at the
> end of the sqlite3_initialize routine to perform extra initialization).
> Pointing this to a function which calls a bunch of sqlite3_auto_extension
> to add internally compiled extension init routines to the initialization of
> each connection thus making such extensions available to all connections by
> auto-magic.
>
> I suppose one could write some code which looks for the desired table and,
> if found, reads and executes the statements contained therein.
>
> The entry point of this routine can be added into the auto_extension list
> (even though it is not an extension) through the EXTRA_INIT hook.
>
> From my reading of the code, a pointer in the auto_extension list does not
> *have* to actually be an extension -- it is just code that is called when a
> connection object is created that receives the connection object as an
> argument.  If it *is* an extension init routine, it just happens to make
> some calls to add functions/aggregates/modules/etc., but I see nothing to
> indicate that this is actually required.
>
> The only thing you will NOT be able to do this way is anything that needs
> to be set *before* initialization.  Running a set of pragma's on each
> connection should work just peachy though, I would expect.


If you are writing a client that uses SQLite databases, then you probably
have a wrapper for sqlite3_open*(), and you could just put the code to
query and run the pragmas in there.

If I am writing a client that can read SQLite databases, then I probably
don't want your database to be injecting a bunch of arbitrary PRAGMA calls
into my client.

To be honest, I'm kind of pressed to imagine the set of pragmas which make
sense for the database to select, rather than the client, since most of
them affect client operation.  I'd be very annoyed if my client has strong
memory constraints and the database forced some how cache_size setting.  Or
if my client was written assuming temp_store = FILE and the database forced
temp_store = MEMORY.  Having the database run synchronous = OFF or
journal_mode = OFF would be pretty fun!  The only ones which seem more like
they could be part of the schema itself are foreign_keys and secure_delete,
so maybe it would make more sense to handle them differently in the first
place, more like page_size or encoding where once set they're sticky.

-scott

[sqlite] whish list for 2016

[James K. Lowden]

On Fri, 8 Jan 2016 08:28:29 +0100
Dominique Devienne  wrote:

> > One way to do that would be to honor a special user-created table,
> > say "PRAGMAS", with name-value pairs that are automatically applied
> > when the database is opened.
> >
> 
> Interesting idea. A "special"-to-SQLite sqlite_pragmas table, similar
> to sqlite_master. 

Similar in name, yes.  sqlite_master is special in that it's built into
the database file structure, though.  You couldn't add another such
table without changing the file structure, something OFL understandably
doesn't want to do.  

I'm suggesting that the presence of a user-created 
"configuration table" would govern the behavior of the database,
analogous to a configuration file.  

--jkl

[sqlite] analysis of a corrupt db

[Simon Slavin]

On 12 Jan 2016, at 2:52pm, Adam Devita  wrote:

> Under normal operation all access is controlled by 1 program that
> serializes requests from the rest of the system, and executes batches
> of statements in a transaction. Under normal operation only SELECT and
> UPDATE queries are run.

Does your program examine the codes returned by SQLite3 calls and check to see 
that they are all returning SQLITE_OK ?

> The db doesn't grow in number of records. There are (always at this
> firmware version) 1455 rows in a good db.
> 
> Under upgrade, the above db management program is shut down and the
> upgrade script runs commands through a shell tool.

Does the script shut down the program and wait for the program to quit before 
it starts running its own commands, or are the two things done independently ?

Are both programs running on the computer with the database stored on a hard 
disk, or is anything accessing the database across a network ?

> Under upgrade we do
> use INSERT OR REPLACE as well as update. Upgrades are normally
> executed by creating a new db with default values (and inserting the
> list of known ids)  and then attaching the new db to the old one and
> replacing records into the new db that have non-default values.

Does /this/ program examine the values returned by SQLite calls to see that 
they're all SQLITE_OK ?

Just as a closing comment, I note that almost all the cases of genuine database 
corruption I've seen are caused by faulty hardware.  You've obviously read the 
"How to corrupt" document and that's what's left: hardware.

Simon.

[sqlite] hard links and SQLite

[Rowan Worth]

On 12 January 2016 at 13:12, Felipe Gasper  wrote:

> Same code, just different processes.
>
> We?ve just noted over the years with race conditions that that ?if it can
> fail, it will?, so we try to be as bulletproof as we can.
>

Good policy :)

After you unlink the temp file, I presume the schema creating process opens
a new connection against the permanent file?

I can't see your algorithm causing corruption, unless your affected users
are using a filesystem which doesn't propagate POSIX file region locks
across hard links.

-Rowan

[sqlite] Performance of newer versions

[Sergej Jurečko]

Something I've recently noticed when using SQLite over multiple threads
(with SQLITE_THREADSAFE=2). You must disable memstatus (with
-DSQLITE_DEFAULT_MEMSTATUS=0) otherwise that mutex will cause a lot of
contention.

Sergej

[sqlite] hard links and SQLite

[Rowan Worth]

On 12 January 2016 at 13:26, Scott Hess  wrote:

> On Mon, Jan 11, 2016 at 9:12 PM, Felipe Gasper 
> wrote:
>
> > On 11 Jan 2016 9:06 PM, Rowan Worth wrote:
> >
> >> * if it returns SQLITE_OK and zero rows, the schema hasn't been created
> >> yet
> >>
> >
> > Sure; however, by the time you do the next action it?s possible that
> > something else will be creating the schema. This is the condition that
> I?m
> > trying to avoid.
>
>
> If something else created the schema between check and use, what's the
> problem?
>
> You can avoid this by doing:
>  - query sqlite_master.
>  - if the query returns rows, the schema exists, exit; else
>  - open a transaction.
>  - query sqlite_master.
>  - if the query returns rows, the schema exists, end transaction and exit;
> else
>  - create the schema.
>  - commit transaction.
>
> Or, just use CREATE IF NOT EXISTS style when creating the schema, and you
> will successfully not create it the second time.  Or just pay attention to
> your error codes in the create code.
>
> -scott
>

You can get away with querying sqlite_master only once, I think?

- open a transaciton
- query sqlite_master
- if the query returns rows, end transaction and exit
- create the schema
- on SQLITE_BUSY, retry transaction[1]
- commit transaction

[1] most likely this means another process is also creating the schema, and
the next transaction attempt will exit early after noticing sqlite_master
is populated

This way doesn't require any EXCLUSIVE transactions either.
-Rowan

[sqlite] analysis of a corrupt db

[Adam Devita]

Some more information:

A co-worker managed to get an copy of the db by as interpreted by
jffs2dump of the file system, that was extracted by the jffs2dump
python script (from git hub). It is interesting that it is also
corrupt but in a different way.

sqlite> select  * from config where id ='isp_de_mode';
(11) database corruption at line 70244 of [fd0a50f079]
(11) statement aborts at 9: [select  * from config where id
='isp_de_mode';] database disk image is malformed
Error: database disk image is malformed
sqlite> select  id from config where id ='isp_de_mode';
isp_de_mode
sqlite> select count(id) from config where id ='isp_de_mode';
1
sqlite> select count(1), id from config group by id order by 1 desc limit 2;
2|isp_de_mode
1|audio_bitrate_s0
sqlite> .schema
CREATE TABLE config( id varchar(255) PRIMARY KEY,file
varchar(255),xpath varchar (255), value varchar(255),
venc_switch_xpath varchar(255), apply_cmd varchar(255), cacheIsDirty
integer default -1  );
sqlite>
sqlite> pragma integrity_check;
row 1275 missing from index sqlite_autoindex_config_1
row 1276 missing from index sqlite_autoindex_config_1
row 1277 missing from index sqlite_autoindex_config_1
row 1346 missing from index sqlite_autoindex_config_1
row 1347 missing from index sqlite_autoindex_config_1
row 1348 missing from index sqlite_autoindex_config_1
row 1349 missing from index sqlite_autoindex_config_1
row 1350 missing from index sqlite_autoindex_config_1
row 1351 missing from index sqlite_autoindex_config_1
row 1352 missing from index sqlite_autoindex_config_1
row 1353 missing from index sqlite_autoindex_config_1
row 1354 missing from index sqlite_autoindex_config_1
row 1367 missing from index sqlite_autoindex_config_1
row 1372 missing from index sqlite_autoindex_config_1
wrong # of entries in index sqlite_autoindex_config_1

regards,
Adam



On Tue, Jan 12, 2016 at 12:01 PM, Adam Devita  wrote:
> Good day,
> Thank you for some avenues of investigation.
>
> Q: Does your program examine the codes returned by SQLite3 calls and
> check to see that they are all returning SQLITE_OK ?
>
> A1: The upgrade process is done by a script. It isn't error checking &
> executes queries via the shell tool.  I followed up with the script
> folks and they got  a pull of the file system log from the failed
> unit.  There are 2 queries that run to update new.db from data in
> old.db, and appear to execute and update multiple rows each.
>
> A2: From inspecting the normal op program (at the version of the
> firmware tag)  It looks fairly good.
> The only return code not checked is a on  commit/rollback that is
> after a failed prepare statement or one particular function that takes
> a checked input, performs bind, step, reset at one point.
> There may be a vulnerability at one point to sqlite bind text that has
> a null input for the string. The docs say such an oversight would
> return SQLITE_MISUSE so the step would not happen.
>
> Q: Does the script shut down the program and wait for the program to
> quit before it starts running its own commands, or are the two things
> done independently ?
>
> A: The script does not check for a response but it waits. It would be
> very unusual for their to be pending writes to the old db (or user
> requests) while new db is extracting the information. I suppose WAL
> mode would be safer here.
>
> The upgrade Script doesn't check values of the returned by the shell
> tool. That said. Since the alg is :update new.db from old.db, replace
> old.db file with new.db. New.db's default value for cache is "reload
> value from config files", the script failure would leave new.db in a
> state that, as long as not corrupt, would simply reload from the
> config files.
>
>
> Q: Are both programs running on the computer with the database stored
> on a hard disk, or is anything accessing the database across a network
> ?
> A: All operations happen on locally stored flash or memory only. (The
> new.db in an update is uncompressed to ram, once updates are complete,
> then it gets copied to the local flash.)
> The only network ops are "upload upgrade package to remote device,
> then tell it to use it". There are no sqlite operations over a
> network.
>
>
> --As per DRH's instruction--
> f:\Users\Adam>sqlite3.exe system.bad
> SQLite version 3.10.0 2016-01-06 11:01:07
> Enter ".help" for usage hints.
> sqlite> .log stdout
> sqlite> pragma integrity_check;
> (11) database corruption at line 58034 of [fd0a50f079]
> (11) database disk image is malformed
> Error: database disk image is malformed
> sqlite>
> -
>
> showdb: I assume this is a linux tool?  Where would I pull that from?
> Our device doesn't have all the utilities but I can put a copy of the
> bad db on a development linux environment for further tests.
>
> On Tue, Jan 12, 2016 at 10:55 AM, Richard Hipp  wrote:
>> On 1/12/16, Adam Devita  wrote:
>>>
>>> Shell Tool Observations:
>>> SQLite version 3.8.4.3 2014-04-03 16:53:12
>>> Enter ?.help? for usage hints.
>

[sqlite] analysis of a corrupt db

[Adam Devita]

Good day,
Thank you for some avenues of investigation.

Q: Does your program examine the codes returned by SQLite3 calls and
check to see that they are all returning SQLITE_OK ?

A1: The upgrade process is done by a script. It isn't error checking &
executes queries via the shell tool.  I followed up with the script
folks and they got  a pull of the file system log from the failed
unit.  There are 2 queries that run to update new.db from data in
old.db, and appear to execute and update multiple rows each.

A2: From inspecting the normal op program (at the version of the
firmware tag)  It looks fairly good.
The only return code not checked is a on  commit/rollback that is
after a failed prepare statement or one particular function that takes
a checked input, performs bind, step, reset at one point.
There may be a vulnerability at one point to sqlite bind text that has
a null input for the string. The docs say such an oversight would
return SQLITE_MISUSE so the step would not happen.

Q: Does the script shut down the program and wait for the program to
quit before it starts running its own commands, or are the two things
done independently ?

A: The script does not check for a response but it waits. It would be
very unusual for their to be pending writes to the old db (or user
requests) while new db is extracting the information. I suppose WAL
mode would be safer here.

The upgrade Script doesn't check values of the returned by the shell
tool. That said. Since the alg is :update new.db from old.db, replace
old.db file with new.db. New.db's default value for cache is "reload
value from config files", the script failure would leave new.db in a
state that, as long as not corrupt, would simply reload from the
config files.


Q: Are both programs running on the computer with the database stored
on a hard disk, or is anything accessing the database across a network
?
A: All operations happen on locally stored flash or memory only. (The
new.db in an update is uncompressed to ram, once updates are complete,
then it gets copied to the local flash.)
The only network ops are "upload upgrade package to remote device,
then tell it to use it". There are no sqlite operations over a
network.


--As per DRH's instruction--
f:\Users\Adam>sqlite3.exe system.bad
SQLite version 3.10.0 2016-01-06 11:01:07
Enter ".help" for usage hints.
sqlite> .log stdout
sqlite> pragma integrity_check;
(11) database corruption at line 58034 of [fd0a50f079]
(11) database disk image is malformed
Error: database disk image is malformed
sqlite>
-

showdb: I assume this is a linux tool?  Where would I pull that from?
Our device doesn't have all the utilities but I can put a copy of the
bad db on a development linux environment for further tests.

On Tue, Jan 12, 2016 at 10:55 AM, Richard Hipp  wrote:
> On 1/12/16, Adam Devita  wrote:
>>
>> Shell Tool Observations:
>> SQLite version 3.8.4.3 2014-04-03 16:53:12
>> Enter ?.help? for usage hints.
>
> If you first do:  ".log stdout" before doing the "PRAGMA
> integrity_check", you might get some better diagnostics.  Or maybe
> not.  In any event, it doesn't hurt to try.
>
>> sqlite> pragma integrity_check;
>> Error: database disk image is malformed
>>
>
> Other things to try:
>
> ./configure; make showdb;
> ./showdb your-corrupt-db-file.db dbheader
> ./showdb your-corrupt-db-file.db pgidx
>
> There is a lot of other things you can do with the showdb program.
> Type "./showdb" with no argument for a very terse summary.  I, for
> one, would be very interested in seeing the output of the above two
> commands.
>
> --
> D. Richard Hipp
> drh at sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



-- 
--
VerifEye Technologies Inc.
151 Whitehall Dr. Unit 2
Markham, ON
L3R 9T1

[sqlite] analysis of a corrupt db

[Richard Hipp]

On 1/12/16, Adam Devita  wrote:
>
> Shell Tool Observations:
> SQLite version 3.8.4.3 2014-04-03 16:53:12
> Enter ?.help? for usage hints.

If you first do:  ".log stdout" before doing the "PRAGMA
integrity_check", you might get some better diagnostics.  Or maybe
not.  In any event, it doesn't hurt to try.

> sqlite> pragma integrity_check;
> Error: database disk image is malformed
>

Other things to try:

./configure; make showdb;
./showdb your-corrupt-db-file.db dbheader
./showdb your-corrupt-db-file.db pgidx

There is a lot of other things you can do with the showdb program.
Type "./showdb" with no argument for a very terse summary.  I, for
one, would be very interested in seeing the output of the above two
commands.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] hard links and SQLite

[Rowan Worth]

On 12 January 2016 at 03:00, Felipe Gasper  wrote:

> On 11 Jan 2016 1:45 PM, Scott Hess wrote:
>
>>
>> As far as preventing the other process from using it before the schema
>> exists, do "SELECT count(*) FROM sqlite_master", and if the result is 0,
>> the schema does not exist.  If you create the schema as a transaction,
>> that
>> will be atomic.
>>
>
> But in order for that SELECT to avert TOCTTOU errors, we?d have to do
> BEGIN EXCLUSIVE LOCK at the beginning of every single DB handle creation.
>

No, only the connection which is creating the schema needs BEGIN EXCLUSIVE.
The other connections can determine the schema state based on a normal
"SELECT count(*) FROM sqlite_master":

* if it returns SQLITE_OK and at least one row, the schema has been created
and it can proceed
* if it returns SQLITE_OK and zero rows, the schema hasn't been created yet
* if it returns SQLITE_BUSY, the schema is in the process of being created
(or there's some other EXCLUSIVE transaction in progress, or a transaction
is being committed at this very moment, or an in-progress write transaction
has spilled sqlite's memory cache)


As Scott also hinted at, hard linking DB files is dangerous because
connections against each of the links will use different -journal files. In
the event that your schema creation process (or the machine its running on)
crashes halfway through COMMIT, connecting to the permanent database will
cause corruption to be observed (because it's not aware of the other
journal file and thus can't rollback the partial transaction).

This may also be possible if another process simply connects to the
permanent DB at just the wrong time (ie. halfway through the schema
creation COMMIT)? Or maybe not, in my experience POSIX locks are maintained
across hardlinks but I haven't checked if this is specified by the standard
or file-system dependent.


Do your different connections actually run different code? Or are you
potentially in the situation where two threads are trying to create the
same DB at the same time (each via their own .tmp file)?

-Rowan

[sqlite] analysis of a corrupt db

[Adam Devita]

Good day,

I've got a case of a corrupt file on some hardware of our own design,
a linux based IO controller in a harsh environment.

It was lately discovered that 1 controller in a field test group had a
corrupt db on it, so naturally we are attempting to figure out what
happened.

The hardware has the db on flash memory.

DB Description:
For the sake of documenting it, Db Size is about 370KB
It is used as a status scoreboard for various system configuration information.

1 table only:
CREATE TABLE config( id text PRIMARY KEY,file text,xpath text, value
text, venc_switch_xpath text, apply_cmd text, cacheIsDirty integer
default -1 );

Under normal operation all access is controlled by 1 program that
serializes requests from the rest of the system, and executes batches
of statements in a transaction. Under normal operation only SELECT and
UPDATE queries are run.

The db doesn't grow in number of records. There are (always at this
firmware version) 1455 rows in a good db.

Under upgrade, the above db management program is shut down and the
upgrade script runs commands through a shell tool. Under upgrade we do
use INSERT OR REPLACE as well as update. Upgrades are normally
executed by creating a new db with default values (and inserting the
list of known ids)  and then attaching the new db to the old one and
replacing records into the new db that have non-default values.

Shell Tool Observations:
SQLite version 3.8.4.3 2014-04-03 16:53:12
Enter ?.help? for usage hints.
sqlite> pragma integrity_check;
Error: database disk image is malformed
sqlite> .tables
sqlite> .schema
Error: database disk image is malformed
sqlite> select * from sqlite_master;
Error: database disk image is malformed
sqlite>.dump
...eventually
INSERT INTO "config" VALUES('gforce_orientation','good
_data','more_good_data','',NULL,NULL,0);
INSERT INTO "config"
VALUES('audio_input_gain','mygood_path1','alsogood_data','',NULL,'good
text data',0);
/ ERROR: (11) database disk image is malformed ***/
/** ERROR: (11) database disk image is malformed */
COMMIT;

Other hacks at it:
Inspecting the file and from the above with a comparison to a known
good file the headers appear ok. The table exists but our code returns
this db is corrupt.
Using a hex editor to manually inspect the file with a comparison to a
known good one shows that there is no data that isn't "db-ish" : This
is not a case of rogue data being written to the file, as far as I can
see.

Is there another utility I can use to help point at the problem?
How is .dump working to print out almost everything when .tables
returns the db is corrupt?

I'd like to attempt to figure out what the last bit of data written in was.

If I .dump into a text file, then open a new db and .read into it, I
get 1454 records (1 fewer than the 'good db')
Comparing to the good file, I know that  audio_output_gain is the
record that is not printed by the .dump.
Does it follow that it must be the corrupt record?
How would that prevent .table or .schema from getting read?

>From the values of the cacheIsDirty flag, I deduce that it was in the
process of an upgrade, not normal user interaction, when the
corruption occurred. (This does not conclusively point to if the error
happened during the upgrade, or immediately after it as the normal
mode works through the records with 'dirty' cache.  That said, all
~200 records of 'dirty' cache should be updated in 1 transaction, so 1
record being wrong seems to not fit. )


regards,
Adam DeVita


BTW: While testing this, I noticed that if I ftp the file to the
device from win 7 command prompt ftp to the linux box without setting
to bin (leaving in ascii mode), that will corrupt the db. That is a
simple move to corrupt that isn't listed on
(https://www.sqlite.org/howtocorrupt.html ).

--

[sqlite] Performance of newer versions

[Clemens Ladisch]

Simon Slavin wrote:
> More recent versions introduced another mode (WAL)
> in which any number of queries can take place without locking the database.

That would be file locking, not a critical section.

This appears to be a problem with using the SQLite C API.


Regards,
Clemens

[sqlite] Performance of newer versions

[Clemens Ladisch]

Gheorghe Marinca wrote:
> the library seemed to held, be contended on an internal lock (critical 
> section).

Which lock?

This probably happens when you are using the same database connection
from multiple threads, or multiple connections in shared-cache mode.
Neither one is something that you should be doing.

> Do the newer versions improve on this

Probably not; some kind of locking is always needed to make such accesses
thread safe.

But if the old version is 3.x, there should be no problem testing the
current version.


Regards,
Clemens

[sqlite] Performance of newer versions

[Simon Slavin]

On 12 Jan 2016, at 7:40am, Gheorghe Marinca  wrote:

> We have used in an older product a version of sqllite from 7-8-9 years ago. I 
> oserved that (this being used on a server) when doing sql queries the library 
> seemed to held, be contended on an internal lock (critical section).  Do the 
> newer versions improve on this or for reading there are always cases where 
> locks could be involved/limit performance/responsivness ?

More recent versions introduced another mode ...



in which any number of queries can take place without locking the database.  
Only while changes are committed is the database locked.  This was introduced 
in version 3.7.0 which was released on 2010-07-21.

The major disadvantages of this mode are that it does not work at all over 
network connections, and that if you have one transaction involving a very 
large number of modifications to the database it can (temporarily) use a lot of 
storage space.

Simon.

[sqlite] Performance of newer versions

[Gheorghe Marinca]

Hi,

We have used in an older product a version of sqllite from 7-8-9 years ago. I 
oserved that (this being used on a server) when doing sql queries the library 
seemed to held, be contended on an internal lock (critical section).  Do the 
newer versions improve on this or for reading there are always cases where 
locks could be involved/limit performance/responsivness ?

Regards
Ghita




DISCLAIMER The information contained in this electronic mail may be 
confidential or legally privileged. It is for the intended recipient(s) only. 
Should you receive this message in error, please notify the sender by replying 
to this mail. Please do not read, copy, forward or store this message unless 
you are an intended recipient of it - unauthorized use of contents is strictly 
prohibited. Unless expressly stated, opinions in this message are those of the 
individual sender and not of GFI. While all care has been taken, GFI is not 
responsible for the integrity or the contents of this electronic mail and any 
attachments included within. (GFI2016)

[sqlite] hard links and SQLite

[Zia Khatri]

Please unsubscribe my id zia_khatri at yahoo.com


On Tuesday, January 12, 2016 11:19 AM, Rowan Worth  
wrote:


 On 12 January 2016 at 13:12, Felipe Gasper  wrote:

> Same code, just different processes.
>
> We?ve just noted over the years with race conditions that that ?if it can
> fail, it will?, so we try to be as bulletproof as we can.
>

Good policy :)

After you unlink the temp file, I presume the schema creating process opens
a new connection against the permanent file?

I can't see your algorithm causing corruption, unless your affected users
are using a filesystem which doesn't propagate POSIX file region locks
across hard links.

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

[sqlite] hard links and SQLite

[Felipe Gasper]

On 11 Jan 2016 9:06 PM, Rowan Worth wrote:
> On 12 January 2016 at 03:00, Felipe Gasper  wrote:
>
>> On 11 Jan 2016 1:45 PM, Scott Hess wrote:
>>
>>>
>>> As far as preventing the other process from using it before the schema
>>> exists, do "SELECT count(*) FROM sqlite_master", and if the result is 0,
>>> the schema does not exist.  If you create the schema as a transaction,
>>> that
>>> will be atomic.
>>>
>>
>> But in order for that SELECT to avert TOCTTOU errors, we?d have to do
>> BEGIN EXCLUSIVE LOCK at the beginning of every single DB handle creation.
>>
>
> * if it returns SQLITE_OK and at least one row, the schema has been created
> and it can proceed

Agreed.

> * if it returns SQLITE_OK and zero rows, the schema hasn't been created yet

Sure; however, by the time you do the next action it?s possible that 
something else will be creating the schema. This is the condition that 
I?m trying to avoid.

> * if it returns SQLITE_BUSY, the schema is in the process of being created
> (or there's some other EXCLUSIVE transaction in progress, or a transaction
> is being committed at this very moment, or an in-progress write transaction
> has spilled sqlite's memory cache)

Agreed.

>
> As Scott also hinted at, hard linking DB files is dangerous because
> connections against each of the links will use different -journal files. In
> the event that your schema creation process (or the machine its running on)
> crashes halfway through COMMIT, connecting to the permanent database will
> cause corruption to be observed (because it's not aware of the other
> journal file and thus can't rollback the partial transaction).

I suppose we could have each connection:

BEGIN EXCLUSIVE LOCK
Check for schema: if OK, then ROLLBACK; else, create and COMMIT.

Just seems a bit funny to do a LOCK at the begin of each and every 
connection, but I suppose not too bad since every write does an 
exclusive lock in the first place.

> This may also be possible if another process simply connects to the
> permanent DB at just the wrong time (ie. halfway through the schema
> creation COMMIT)? Or maybe not, in my experience POSIX locks are maintained
> across hardlinks but I haven't checked if this is specified by the standard
> or file-system dependent.

Yeah, we had quite a few corruptions. I think only from the DBs that we 
create this way, but it was odd that none of our folks could reproduce 
the failure.

> Do your different connections actually run different code? Or are you
> potentially in the situation where two threads are trying to create the
> same DB at the same time (each via their own .tmp file)?

Same code, just different processes.

We?ve just noted over the years with race conditions that that ?if it 
can fail, it will?, so we try to be as bulletproof as we can.

-FG