[sqlite] bug in transactions implementation ?
> On Mar 18, 2015, at 10:24 PM, Jason Vas Dias > wrote: > > This seems very buggy to me. Correct. http://www.styleite.com/wp-content/uploads/2014/11/legallyblonde.gif
[sqlite] how to determine the status of last statement in sqlite3 shell ?
> On Mar 18, 2015, at 9:51 PM, Jason Vas Dias > wrote: > > I don't see how anything like that is possible in the sqlite3 shell . Not in the shell per se, no (.bail on|off may or may not help in your case). Perhaps ON CONFLICT clause might help you: https://www.sqlite.org/lang_conflict.html Or you can trail all your transactions with a commit; rollback; pair.
[sqlite] bug in transactions implementation ?
> On Mar 18, 2015, at 9:45 PM, Jason Vas Dias > wrote: > > Would you care to expand on that ? As it says on the tin [1]: you cannot start a transactions inside another transaction (use savepoint if you want that), so? create table foo( value text, constraint uk unique( value ) ); begin transaction; insert into foo( value ) values( 'bar' ); commit; ? ok begin transaction; insert into foo( value ) values( 'bar' ); commit; ? Error: UNIQUE constraint failed: foo.value begin transaction; insert into foo( value ) values( 'baz' ); commit; ? Error: cannot start a transaction within a transaction rollback; ? ok begin transaction; insert into foo( value ) values( 'baz' ); commit; ? ok [1] https://www.sqlite.org/lang_transaction.html
[sqlite] bug in transactions implementation ?
On 18 Mar 2015, at 9:24pm, Jason Vas Dias wrote: > But now I get another error after the constraint violation : > 'Error: cannot commit - no transaction is active' > which gets back to the original point of this post, > which is that SQLite is evidently not considering > all text within 'BEGIN TRANSACTION; ... ; $EOT;' As I explained to you, the way you are passing your commands to the shell tool is messing them up. You are packaging together multiple commands in a pipe and your Unix shell is not passing them to sqlite3 correctly. I don't know which Unix shell you're using, or whether it is processing single and double-quotes as you expect, but something somewhere is messing up your stream of commands before your commands are seen by the sqlite3 program. In my earlier post I showed you two different ways of executing your own series of SQL commands and getting the right results in the right places. If you use either of the methods I showed you with this new INSERT OR ROLLBACK command you will get the right result in the right place. While I'm here, I noticed that you are quoting a text string in your command using double-quotes. This is not correct for SQL, which uses single quotes (non-directional apostrophes) around text strings. Double-quotes have a different meaning in SQL. However, this is not (as far as I can tell) causing the problem you are reporting. Simon.
[sqlite] bug in transactions implementation ?
> On Mar 18, 2015, at 9:11 PM, Jason Vas Dias > wrote: > > am I missing something? rollback?
[sqlite] bug in transactions implementation ?
OK, I discovered the OR clause of the INSERT statement, so I'm trying:
BEGIN TRANSACTION;
INSERT OR ROLLBACK INTO db VALUES("This breaks a constraint");
COMMIT;
But now I get another error after the constraint violation :
'Error: cannot commit - no transaction is active'
which gets back to the original point of this post,
which is that SQLite is evidently not considering
all text within 'BEGIN TRANSACTION; ... ; $EOT;'
to be part of the same transaction (for $EOT in COMMIT, ROLLBACK, END
TRANSACTION etc.) ; or if a transaction fails, it considers the next
transaction to be part of the failed transaction (and so disallows
another BEGIN TRANSACTION ).
This seems very buggy to me.
On 18/03/2015, Simon Slavin wrote:
>
> On 18 Mar 2015, at 8:11pm, Jason Vas Dias wrote:
>
>> The problem is, if this transaction runs in a session, then
>> NO transactions can ever run again in that session - eg.
>> if I try to run the same transaction twice :
>> On linux command line:
>> $ echo '
>> BEGIN TRANSACTION;
>> INSERT INTO db VALUES("This breaks a constraint");
>> COMMIT;
>> BEGIN TRANSACTION;
>> INSERT INTO db VALUES("This breaks a constraint");
>> COMMIT;
>> ' | sqlite3 my_db_file.db
>> Error: near line 1: UNIQUE constraint failed: db.some_field
>> Error: near line 2: cannot start a transaction within a tranaction
>>
>> It is the second error that worries me, since it shows that the
>> first failed transaction was not closed, even though I had clearly
>> written "COMMIT;" at the end of it, and no transactions
>> can ever run again in the same session.
>
> Which Unix/Linux command shell are you using ? I'm guessing that what
> you're seeing makes no sense because of the way the sqlite3 is receiving the
> lines from the shell. If I execute the commands singly I get this:
>
> 164:Desktop simon$ sqlite3 ~/Desktop/test.sdb
> SQLite version 3.8.5 2014-08-15 22:37:57
> Enter ".help" for usage hints.
> sqlite> CREATE TABLE myTable (myCol TEXT UNIQUE);
> sqlite> INSERT INTO myTable VALUES ('This breaks a constraint.');
> sqlite> BEGIN;
> sqlite> INSERT INTO myTable VALUES ('This breaks a constraint.');
> Error: UNIQUE constraint failed: myTable.myCol
> sqlite> COMMIT;
> sqlite> BEGIN;
> sqlite> INSERT INTO myTable VALUES ('This breaks a constraint.');
> Error: UNIQUE constraint failed: myTable.myCol
> sqlite> COMMIT;
> sqlite>
>
> which is fine. If I then use your command in bash I get this:
>
> 164:Desktop simon$ echo '
>> BEGIN TRANSACTION;
>> INSERT INTO myTable VALUES("This breaks a constraint.");
>> COMMIT;
>> BEGIN TRANSACTION;
>> INSERT INTO myTable VALUES("This breaks a constraint.");
>> COMMIT;
>> ' | sqlite3 ~/Desktop/test.sdb
> Error: near line 3: UNIQUE constraint failed: myTable.myCol
> Error: near line 6: UNIQUE constraint failed: myTable.myCol
> 164:Desktop simon$
>
> which is again fine.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
[sqlite] how to determine the status of last statement in sqlite3 shell ?
On 18 Mar 2015, at 8:51pm, Jason Vas Dias wrote: > When the sqlite3 shell is used to run a long stream of commands , eg. from > a script file or input pipe from another process, how can the success / > failure > status of the last statement be determined ? > IE. if the shell has just run an insert statment : > BEGIN TRANSACTION; INSERT INTO db VALUES(...); COMMIT; > how can the next statement determine if the previous statement failed ? It cannot. There's no way to do this inside the SQL command-stream apart from reading what you just tried to write and seeing if it's there. > Or, can the insert statement transaction determine if it has failed or not, > ie. > is it possible to do something like: > BEGIN TRANSACTION; INSERT INTO db VALUES(...); >ON SUCCESS: COMMIT; >ON FAILURE: ROLLBACK; > I don't see how anything like that is possible in the sqlite3 shell . > Any ideas ? If the only thing you're using the error for is to decide whether to COMMIT or ROLLBACK then you may have misunderstood how transactions work. A transaction is ended when you issue the COMMIT command. A transaction fails if any command which changes the database in it fails due to violating the schema. If a transaction fails then all commands in it are automatically ignored. There's no need to use ROLLBACK. You correctly grouped commands together into a transaction and SQL knows that if any of them fail none of them must be executed. A program would use ROLLBACK only if it decides that the whole transaction was a bad idea itself -- not because a command violated the schema and resulted in an error, but perhaps because the user hit an 'abort' button or because a long transaction failed to finish before end-of-day. Simon.
[sqlite] bug in transactions implementation ?
On 18 Mar 2015, at 8:11pm, Jason Vas Dias wrote:
> The problem is, if this transaction runs in a session, then
> NO transactions can ever run again in that session - eg.
> if I try to run the same transaction twice :
> On linux command line:
> $ echo '
> BEGIN TRANSACTION;
> INSERT INTO db VALUES("This breaks a constraint");
> COMMIT;
> BEGIN TRANSACTION;
> INSERT INTO db VALUES("This breaks a constraint");
> COMMIT;
> ' | sqlite3 my_db_file.db
> Error: near line 1: UNIQUE constraint failed: db.some_field
> Error: near line 2: cannot start a transaction within a tranaction
>
> It is the second error that worries me, since it shows that the
> first failed transaction was not closed, even though I had clearly
> written "COMMIT;" at the end of it, and no transactions
> can ever run again in the same session.
Which Unix/Linux command shell are you using ? I'm guessing that what you're
seeing makes no sense because of the way the sqlite3 is receiving the lines
from the shell. If I execute the commands singly I get this:
164:Desktop simon$ sqlite3 ~/Desktop/test.sdb
SQLite version 3.8.5 2014-08-15 22:37:57
Enter ".help" for usage hints.
sqlite> CREATE TABLE myTable (myCol TEXT UNIQUE);
sqlite> INSERT INTO myTable VALUES ('This breaks a constraint.');
sqlite> BEGIN;
sqlite> INSERT INTO myTable VALUES ('This breaks a constraint.');
Error: UNIQUE constraint failed: myTable.myCol
sqlite> COMMIT;
sqlite> BEGIN;
sqlite> INSERT INTO myTable VALUES ('This breaks a constraint.');
Error: UNIQUE constraint failed: myTable.myCol
sqlite> COMMIT;
sqlite>
which is fine. If I then use your command in bash I get this:
164:Desktop simon$ echo '
> BEGIN TRANSACTION;
> INSERT INTO myTable VALUES("This breaks a constraint.");
> COMMIT;
> BEGIN TRANSACTION;
> INSERT INTO myTable VALUES("This breaks a constraint.");
> COMMIT;
> ' | sqlite3 ~/Desktop/test.sdb
Error: near line 3: UNIQUE constraint failed: myTable.myCol
Error: near line 6: UNIQUE constraint failed: myTable.myCol
164:Desktop simon$
which is again fine.
Simon.
[sqlite] how to determine the status of last statement in sqlite3 shell ?
When the sqlite3 shell is used to run a long stream of commands , eg. from a script file or input pipe from another process, how can the success / failure status of the last statement be determined ? IE. if the shell has just run an insert statment : BEGIN TRANSACTION; INSERT INTO db VALUES(...); COMMIT; how can the next statement determine if the previous statement failed ? Or, can the insert statement transaction determine if it has failed or not, ie. is it possible to do something like: BEGIN TRANSACTION; INSERT INTO db VALUES(...); ON SUCCESS: COMMIT; ON FAILURE: ROLLBACK; I don't see how anything like that is possible in the sqlite3 shell . Any ideas ?
[sqlite] bug in transactions implementation ?
On 18/03/2015, Petite Abeille wrote: > >> On Mar 18, 2015, at 9:11 PM, Jason Vas Dias >> wrote: >> >> am I missing something? > > rollback? > Would you care to expand on that ? How is the script consisting of just the two insert statements shown meant to determine if the first transaction has failed or not ? I'm just using the sqlite3 shell to run sql scripts . How can one determine the status of the previous transaction in the shell, or if the current transaction needs to be rolled back or not ? Thanks & Regards, Jason > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] bug in transactions implementation ?
Good day -
There appears to be a bug in the way SQLite implements transactions :
I have an insert transaction which breaks a uniqueness constraint on an index:
BEGIN TRANSACTION;
INSERT INTO db VALUES("This breaks a constraint");
COMMIT;
The problem is, if this transaction runs in a session, then
NO transactions can ever run again in that session - eg.
if I try to run the same transaction twice :
On linux command line:
$ echo '
BEGIN TRANSACTION;
INSERT INTO db VALUES("This breaks a constraint");
COMMIT;
BEGIN TRANSACTION;
INSERT INTO db VALUES("This breaks a constraint");
COMMIT;
' | sqlite3 my_db_file.db
Error: near line 1: UNIQUE constraint failed: db.some_field
Error: near line 2: cannot start a transaction within a tranaction
It is the second error that worries me, since it shows that the
first failed transaction was not closed, even though I had clearly
written "COMMIT;" at the end of it, and no transactions
can ever run again in the same session.
I thought that if a transaction fails, its effects are meant to be
rolled back, and subsequent further transactions can proceed.
Is this bug in the sqlite transaction implementation or am I missing something?
Thanks for any responses,
Regards,
Jason
[sqlite] system.data.sqlite.dll and Widows 10
fuslogvw ( https://msdn.microsoft.com/en-us/library/e74a18c4%28v=vs.110%29.aspx ) might help you understand the DLL binding issue. On 18 March 2015 at 20:04, Jeff Steinkamp wrote: > One of my users reported that he was unable to get a piece of software > that uses this .NET assembly (x86 version 1.0.9.6) to run on windows 10. > It is throwing an error saying that it is unable to located > system.data.sqlite.dll even though it is included in the same folder as the > program executable. > > This software is compiled as x86 because of some other included assemblies > are complied as x86. This has been running just fine on both Win7 and Win8 > both 32 and 64 bits this way for quite some time. > > So, I setup a 64 bit Win10 technical preview in a VM and tried it myself > and I got the same error. I tired forcing the loading of the assembly, but > it would puke all over itself with the "your application quit and windows > will get back with you when hell freezes over" > > So I loaded up the SQLite setup bundle for both the X86 and X64 and had > them install into the GAC (Global Assembly Cache) and now the software > works. So this is telling me that Windows 10 is only looking in the GAC > for this assembly. Any idea why this might be as I have two other .NET > assemblies that are used in this software that it does not have trouble > finding and they are not installed in the GAC? > > I suspect this may be a Mico$oft Issue, but wanted to check here first to > make sure I have not got something setup incorrectly. > > -- > Jeff K. Steinkamp (N7YG) > Tucson, AZ > Scud Missile Coordinates > N32.2319 W110.8477 > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] how to determine the status of last statement in sqlite3 shell ?
>A transaction is ended when you issue the COMMIT command. A transaction >fails if any command which changes the database in it fails due to >violating the schema. If a transaction fails then all commands in it are >automatically ignored. There's no need to use ROLLBACK. You correctly >grouped commands together into a transaction and SQL knows that if any of >them fail none of them must be executed. This is incorrect. A statement may fail, however that does not affect other statements within the transaction. You still have to end a transaction with either a commit (to commit the changes made by statements WHICH DID NOT FAIL) or rollback to discard the changes made by the statements which did not fail. If you however submit a statement BATCH (that is, say 400 INSERT statements as a single line of text) and ONE of them ABORTS, then the batch is aborted (that is, no further statements in the batch are executed, from that point on -- statements which have executed successfully are NOT rolled back by magic). COMMIT will result in committing the database operations in the batch prior to the ABORT, and ROLLBACK will roll them all back. The default conflict resolution method for INSERT is ABORT. That means that the current statement is aborted and no further statements in the same batch (text string submitted for execution) are executed. It has no effect whatsoever on the state of the transaction, which is still open. This is why there is a conflict resolution method IGNORE: >sqlite < abort2.sql Error: near line 2: no such table: foo create table foo( value text, constraint uk unique( value ) ); begin transaction; insert or ignore into foo( value ) values( 'bar' ); commit; begin transaction; insert or ignore into foo( value ) values( 'bar' ); commit; begin transaction; insert or ignore into foo( value ) values( 'baz' ); commit; begin transaction; insert or ignore into foo( value ) values( 'baz' ); commit; ** In my original example, abort1.sql contains: .echo on drop table foo; create table foo( value text, constraint uk unique( value ) ); begin transaction; insert into foo( value ) values( 'bar' ); commit; begin transaction; insert into foo( value ) values( 'bar' ); commit; begin transaction; insert into foo( value ) values( 'baz' ); commit; begin transaction; insert into foo( value ) values( 'baz' ); commit; ** abort2.sql now contains (INSERT changed to INSERT OR IGNORE): .echo on drop table foo; create table foo( value text, constraint uk unique( value ) ); begin transaction; insert or ignore into foo( value ) values( 'bar' ); commit; begin transaction; insert or ignore into foo( value ) values( 'bar' ); commit; begin transaction; insert or ignore into foo( value ) values( 'baz' ); commit; begin transaction; insert or ignore into foo( value ) values( 'baz' ); commit; --- Theory is when you know everything but nothing works. Practice is when everything works but no one knows why. Sometimes theory and practice are combined: nothing works and no one knows why.
[sqlite] bug in transactions implementation ?
Works perfectly fine for me. Note that if you are submitting the commands in a single batch, then the abort action of INSERT OR ABORT aborts the batch. Submitting individual statements works just fine. You are submitting the script as a single sql statement rather than multiple statements (ie, save the script into a file with one command per line and it will work just peachy ... 2015-03-18 18:13:30 [D:\Temp] >sqlite < abort1.sql Error: near line 2: no such table: foo create table foo( value text, constraint uk unique( value ) ); begin transaction; insert into foo( value ) values( 'bar' ); commit; begin transaction; insert into foo( value ) values( 'bar' ); Error: near line 8: UNIQUE constraint failed: foo.value commit; begin transaction; insert into foo( value ) values( 'baz' ); commit; begin transaction; insert into foo( value ) values( 'baz' ); Error: near line 14: UNIQUE constraint failed: foo.value commit; 2015-03-18 18:13:53 [D:\Temp] >sqlite < abort2.sql Error: near line 2: no such table: foo create table foo( value text, constraint uk unique( value ) ); begin transaction; insert into foo( value ) values( 'bar' ); commit; begin transaction; insert into foo( value ) values( 'bar' ); Error: near line 5: UNIQUE constraint failed: foo.value begin transaction; Error: near line 6: cannot start a transaction within a transaction begin transaction; Error: near line 7: cannot start a transaction within a transaction --- Theory is when you know everything but nothing works. Practice is when everything works but no one knows why. Sometimes theory and practice are combined: nothing works and no one knows why. >-Original Message- >From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- >bounces at mailinglists.sqlite.org] On Behalf Of Petite Abeille >Sent: Wednesday, 18 March, 2015 15:02 >To: General Discussion of SQLite Database >Subject: Re: [sqlite] bug in transactions implementation ? > > >> On Mar 18, 2015, at 9:45 PM, Jason Vas Dias >wrote: >> >> Would you care to expand on that ? > >As it says on the tin [1]: you cannot start a transactions inside another >transaction (use savepoint if you want that), so? > >create table foo( value text, constraint uk unique( value ) ); >begin transaction; insert into foo( value ) values( 'bar' ); commit; ? ok >begin transaction; insert into foo( value ) values( 'bar' ); commit; ? >Error: UNIQUE constraint failed: foo.value >begin transaction; insert into foo( value ) values( 'baz' ); commit; ? >Error: cannot start a transaction within a transaction >rollback; ? ok >begin transaction; insert into foo( value ) values( 'baz' ); commit; ? ok > > >[1] https://www.sqlite.org/lang_transaction.html > > >___ >sqlite-users mailing list >sqlite-users at mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] It might be nice to have sqlite3_attach() and sqlite3_detach().
I'm thinking I could use something like: SQLITE_API int sqlite3_attach(sqlite3* db, const char* zPath, const char* dbname); SQLITE_API int sqlite3_detach(sqlite3* db, const char* dbname); Right now, I have a helper in Chromium which does "ATTACH DATABASE ? AS ?". This works, but AFAICT this page: https://www.sqlite.org/lang_keywords.html implies that the dbname _probably_ should not be a string literal but instead an identifier. So it should be "dbname" with embedded " characters doubled (nobody should ever do that, but *shrug*). WDYT? Mostly this just came up because I saw someone writing code which constructed things sprintf-style, without quoting, which was clearly error-prone. But in describing the right way to do it, I had to do notable hand-waving. Thanks, scott
[sqlite] Query times vary between 0.2 s and 30 s for very similar queries - how to trick the optimizer?
I?m using 3.8.8.1 on Windows via the ?C? interface. I work with SQLite for several years with good success. And I know that no optimizer ever will be perfect. But I?ve just stumbled upon a case where a very similar query requires between 0.2 seconds and a whopping 30 seconds. I?ve simplified things as much as possible and included the create instructions and queries below. 1. My database has a table "art_data" which holds information about an article. A second table "attr" holds all available attributes. art_data may contain any number of attribues per article, typically between 50 and 200. The art_data table in the production database has about 22 million rows, the attr table 20,000. art_data oid INTEGER PRIMARY KEY, art_oid INTEGER, attr_oid INTEGER, tdata TEXT the attr_oid refers to the table which defines the available attributes and tdata is the value for that attribute. The attr table is defined as: attr oid INTEGER PRIMARY KEY class INTEGER tag TEXT For the indices created, please see below. 2. My application needs to select all the data for a specific article and specific attributes. The number of attributes to select is usually between 1 and 30 so I use an IN clause and provide the attribute ids directly in the SELECT query. My application has these 20,000 ids cached and always available. If the number of articles is < 500, my application supplies a list of articles for the d.oid IN (...) as well. SELECT d.oid, d.tdata FROM art_data d WHERE d.oid IN (1,890,222,...) AND d.attr_oid IN (2188,2191,2251,2272,...) ORDER BY d.oid ASC, d.attr_oid ASC, d.rowid ASC This query takes between 0.2 and 0.5 seconds, even if 500 article numbers are in the first WHERE d.oid IN clause! If more than 500 articles are needed, I estimated that this would probably break the IN (is there a limit for IN?) and thus my application puts the article into a temporary table and JOINs with this table: SELECT d.oid, d.attr_oid, d.tdata FROM art_data d INNER JOIN _temp _t ON d.oid = _t.oid AND d.attr_oid IN (2188,2191,2251,2272,...) ORDER BY d.oid ASC, d.attr_oid ASC, d.rowid ASC This query takes between 17 and 30 seconds (!) even if the temporary table only has 501 article numbers (one more than the threshold for the IN clause). The only difference between 0.2 and 17 seconds is replacing an IN clause with 500 numbers with a JOIN with a temporary table containing 501 numbers. While playing with that, I used a SQLite GUI tool and created the temporary table _temp (oid INTEGER PRIMARY KEY) as a regular table and filled it with 500 article numbers. This also resulted in the 17 to 30s query times. For a test, I ran ANALYZE and the query time dropped down to 0.5 seconds. AMAZING. Apparently the query analyzer now had the info about the (no longer) temporary table and was able to use it efficiently. My SOLUTION for now was to change the query with the temporary table to SELECT d.oid, d.attr_oid, d.tdata FROM art_data d WHERE d.attr_oid IN (2188,2191,2251,2272,...) AND d.oid IN (SELECT oid FROM _temp) Instead of a JOIN for the temporary table I use an IN clause with a SELECT. This brought the query time down to 0.5 seconds as well. May also be the optimizer. The question is: When JOINing large tables with a temporary table, how to ensure that the optimizer can work optimal? Running ANALYZE with a temporary table probably does not work, and ANALYZE takes about 1 minute on this database so this is not feasible for each query. I'm glad to have found an apparently working solution (IN instead of JOIN) but I wonder if this could be somehow automated by the optimizer? Or maybe this is a worst-case for the optimizer? If you want to try this out yourself, here is the complete CREATE schema and queries: -- BEGIN - DROP TABLE IF EXISTS art_data; DROP TABLE IF EXISTS attr; CREATE TABLE art_data (oid INTEGER, attr_oid INTEGER, tdata TEXT, FOREIGN KEY(attr_oid) REFERENCES attr(oid) ON DELETE CASCADE); CREATE INDEX idx_art_data_oid ON art_data(oid); CREATE INDEX idx_art_data_oid_tag_oid ON art_data(oid,attr_oid); CREATE INDEX idx_art_data_attr_oid ON art_data(attr_oid); CREATE TABLE attr (oid INTEGER PRIMARY KEY, class INTEGER, tag TEXT); CREATE INDEX idx_attr_tag ON attr(tag); DROP TABLE IF EXISTS _temp; CREATE TABLE _temp (OID INTEGER PRIMARY KEY); --insert into _temp select ... -- Fast: 0.2 seconds -- explain query plan SELECT d.oid, d.tdata FROM art_data d -- Only for specific articles WHERE d.oid IN
[sqlite] system.data.sqlite.dll and Widows 10
It might also be the runtime; was trying to install a debug version at one point and msvcr110d.dll was missing... maybe it's just that the required runtime is missing yet? It also just threw an error 'cannot locate' On Wed, Mar 18, 2015 at 1:09 PM, Nicholas Smit wrote: > fuslogvw ( > https://msdn.microsoft.com/en-us/library/e74a18c4%28v=vs.110%29.aspx ) > might help you understand the DLL binding issue. > > On 18 March 2015 at 20:04, Jeff Steinkamp wrote: > > > One of my users reported that he was unable to get a piece of software > > that uses this .NET assembly (x86 version 1.0.9.6) to run on windows 10. > > It is throwing an error saying that it is unable to located > > system.data.sqlite.dll even though it is included in the same folder as > the > > program executable. > > > > This software is compiled as x86 because of some other included > assemblies > > are complied as x86. This has been running just fine on both Win7 and > Win8 > > both 32 and 64 bits this way for quite some time. > > > > So, I setup a 64 bit Win10 technical preview in a VM and tried it myself > > and I got the same error. I tired forcing the loading of the assembly, > but > > it would puke all over itself with the "your application quit and windows > > will get back with you when hell freezes over" > > > > So I loaded up the SQLite setup bundle for both the X86 and X64 and had > > them install into the GAC (Global Assembly Cache) and now the software > > works. So this is telling me that Windows 10 is only looking in the GAC > > for this assembly. Any idea why this might be as I have two other .NET > > assemblies that are used in this software that it does not have trouble > > finding and they are not installed in the GAC? > > > > I suspect this may be a Mico$oft Issue, but wanted to check here first to > > make sure I have not got something setup incorrectly. > > > > -- > > Jeff K. Steinkamp (N7YG) > > Tucson, AZ > > Scud Missile Coordinates > > N32.2319 W110.8477 > > > > ___ > > sqlite-users mailing list > > sqlite-users at mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] system.data.sqlite.dll and Widows 10
One of my users reported that he was unable to get a piece of software that uses this .NET assembly (x86 version 1.0.9.6) to run on windows 10. It is throwing an error saying that it is unable to located system.data.sqlite.dll even though it is included in the same folder as the program executable. This software is compiled as x86 because of some other included assemblies are complied as x86. This has been running just fine on both Win7 and Win8 both 32 and 64 bits this way for quite some time. So, I setup a 64 bit Win10 technical preview in a VM and tried it myself and I got the same error. I tired forcing the loading of the assembly, but it would puke all over itself with the "your application quit and windows will get back with you when hell freezes over" So I loaded up the SQLite setup bundle for both the X86 and X64 and had them install into the GAC (Global Assembly Cache) and now the software works. So this is telling me that Windows 10 is only looking in the GAC for this assembly. Any idea why this might be as I have two other .NET assemblies that are used in this software that it does not have trouble finding and they are not installed in the GAC? I suspect this may be a Mico$oft Issue, but wanted to check here first to make sure I have not got something setup incorrectly. -- Jeff K. Steinkamp (N7YG) Tucson, AZ Scud Missile Coordinates N32.2319 W110.8477
[sqlite] Understanding Sqlite IO on Windows CE
Thanks very much for that. Unfortunately WAL mode is not supported on CE - lacks certain structures apparently. On 18 March 2015 at 12:26, Richard Hipp wrote: > On 3/17/15, Nicholas Smit wrote: > > Hello. > > > > We have an app on CE 5, and CE 7. > > > > We are keen to move from using SQL CE, to Sqlite, for our main data, as > > Sqlite is superior in many ways. > > > > The app runs on a mobile device, where power can be removed at any time. > > The data is stored on SD cards, with FAT filesystem. > > > > Sometimes the IO sub-system is known to be slow. As such, corruption and > > data loss is our number one fear. Our main aim is to keep the IO to a > > minimum, thus reducing the risk of corruption anywhere. Corruption of > the > > file itself, but also the filesystem, leading to problems accessing the > > file. > > > > Of course this fear has nothing to do with Sqlite per se, but we're > trying > > to understand how the risk might *change*, as we move from SQL CE to > > Sqlite. > > > > With Sqlite, we're using Synchronous=FULL (as we don't want to lose > data), > > Journal Mode=Persist (to inhibit unnecessary IO on the journal file). > > > > In SQL CE, the minimum interval for flushes to disk is 1 second, which is > > what we've been using. > > > > My question is (finally I get to it!) with these settings, it sounds like > > if we do (say) 3 transactions in a particular second, we will end up > doing: > > > > - 3 updates to the FAT to obtain the exclusive lock > > - 3 writes to the sqlite journal > > - 3 writes to the sqlite main db file > > - 3 updates to the FAT to release the exclusive lock, update the last > > modified file date, etc. > > > > > > So a total of 12 writes to the file system. (excluding any reads etc > > required in the actual transaction). > > > > Whereas in SQL CE this would have been about 1 (to just append the new > > data for all 3, at the end of the flush interval. It presumably keeps the > > file open, so no FAT updates.). > > > > a) Is my understanding roughly correct here in terms of the IO? > > I do not think file locking and unlocking involves any I/O. The locks > are in-memory data structures that do not persist to disk in any way. > So there would only be 6 I/Os (the way you are counting them). > > Really, though, each of the remaining 6 I/Os consists of multiple > WriteFile() calls. > > > b) If so, is there any way to reduce the IO effort? > > (1) Combine the writes into a single transaction. > (2) Use WAL mode. > (3) Both of the above. > > > c) Can anyone comment on whether Windows CE VFS does actually honour the > > fsync commands of sqlite, or does the OS perhaps buffer them anyway? > > > I dunno. > > But even if WinCE does handle the FlushFileBuffers() calls correctly, > there is no guarantee that the hardware will. You can reduce your risk > by using WAL mode, which is much less vulnerable to dodgy hardware > than is the default rollback mode. > > > > -- > D. Richard Hipp > drh at sqlite.org >
[sqlite] Understanding Sqlite IO on Windows CE
On 3/17/15, Nicholas Smit wrote: > Hello. > > We have an app on CE 5, and CE 7. > > We are keen to move from using SQL CE, to Sqlite, for our main data, as > Sqlite is superior in many ways. > > The app runs on a mobile device, where power can be removed at any time. > The data is stored on SD cards, with FAT filesystem. > > Sometimes the IO sub-system is known to be slow. As such, corruption and > data loss is our number one fear. Our main aim is to keep the IO to a > minimum, thus reducing the risk of corruption anywhere. Corruption of the > file itself, but also the filesystem, leading to problems accessing the > file. > > Of course this fear has nothing to do with Sqlite per se, but we're trying > to understand how the risk might *change*, as we move from SQL CE to > Sqlite. > > With Sqlite, we're using Synchronous=FULL (as we don't want to lose data), > Journal Mode=Persist (to inhibit unnecessary IO on the journal file). > > In SQL CE, the minimum interval for flushes to disk is 1 second, which is > what we've been using. > > My question is (finally I get to it!) with these settings, it sounds like > if we do (say) 3 transactions in a particular second, we will end up doing: > > - 3 updates to the FAT to obtain the exclusive lock > - 3 writes to the sqlite journal > - 3 writes to the sqlite main db file > - 3 updates to the FAT to release the exclusive lock, update the last > modified file date, etc. > > > So a total of 12 writes to the file system. (excluding any reads etc > required in the actual transaction). > > Whereas in SQL CE this would have been about 1 (to just append the new > data for all 3, at the end of the flush interval. It presumably keeps the > file open, so no FAT updates.). > > a) Is my understanding roughly correct here in terms of the IO? I do not think file locking and unlocking involves any I/O. The locks are in-memory data structures that do not persist to disk in any way. So there would only be 6 I/Os (the way you are counting them). Really, though, each of the remaining 6 I/Os consists of multiple WriteFile() calls. > b) If so, is there any way to reduce the IO effort? (1) Combine the writes into a single transaction. (2) Use WAL mode. (3) Both of the above. > c) Can anyone comment on whether Windows CE VFS does actually honour the > fsync commands of sqlite, or does the OS perhaps buffer them anyway? > I dunno. But even if WinCE does handle the FlushFileBuffers() calls correctly, there is no guarantee that the hardware will. You can reduce your risk by using WAL mode, which is much less vulnerable to dodgy hardware than is the default rollback mode. -- D. Richard Hipp drh at sqlite.org
