Re: [sqlite] Sqlite error code 14 when using 3.31.0+

[Daniel Polski]

Den 2020-03-10 kl. 12:31, skrev Daniel Polski:

Den 2020-03-10 kl. 02:33, skrev Rowan Worth:
On Mon, 9 Mar 2020 at 23:22, Daniel Polski  
wrote:



Updated to 3.31.1 but my application started spitting out an error when
opening the database, so I tested some earlier sqlite versions to 
figure

out when the problem starts.
I don't get the message in versions <= 3.30.1.

(from the applications log)
SQLite Version: 3.31.0
INFO: Database opened: /tmp/database.sqlite
WARNING: SQLITE error code: 14 cannot open file at line 36982 of
[3bfa9cc97d]
WARNING: SQLITE error code: 14 os_unix.c:36982: (40) 
openDirectory(/tmp) -



errno 40 is ELOOP, "Too many symbolic links encountered". open(2) says:

    ELOOP  Too many symbolic links were encountered in resolving
  pathname,  or  O_NOFOLLOW  was
   specified but pathname was a symbolic link.

Is your /tmp/ a symlink? Sqlite seems to use O_NOFOLLOW 
unconditionally in

openDirectory() since this checkin:

https://www.sqlite.org/src/info/6a64fb6a2da6c98f


/tmp/ is a directly mounted ramdisk.



Sorry I was wrong.

/tmp/ actually IS a symlink to /var/volatile/tmp

Best regards,


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

Re: [sqlite] Sqlite error code 14 when using 3.31.0+

[Daniel Polski]

Den 2020-03-10 kl. 02:33, skrev Rowan Worth:

On Mon, 9 Mar 2020 at 23:22, Daniel Polski  wrote:


Updated to 3.31.1 but my application started spitting out an error when
opening the database, so I tested some earlier sqlite versions to figure
out when the problem starts.
I don't get the message in versions <= 3.30.1.

(from the applications log)
SQLite Version: 3.31.0
INFO: Database opened: /tmp/database.sqlite
WARNING: SQLITE error code: 14 cannot open file at line 36982 of
[3bfa9cc97d]
WARNING: SQLITE error code: 14 os_unix.c:36982: (40) openDirectory(/tmp) -


errno 40 is ELOOP, "Too many symbolic links encountered". open(2) says:

ELOOP  Too many symbolic links were encountered in resolving
  pathname,  or  O_NOFOLLOW  was
   specified but pathname was a symbolic link.

Is your /tmp/ a symlink? Sqlite seems to use O_NOFOLLOW unconditionally in
openDirectory() since this checkin:

https://www.sqlite.org/src/info/6a64fb6a2da6c98f


/tmp/ is a directly mounted ramdisk.


Probably a bug? The changelog for sqlite 3.31.0 include this which is
likely related:

- * Add the SQLITE_OPEN_NOFOLLOW
<https://www.sqlite.org/c3ref/open.html#opennofollow> option to
sqlite3_open_v2() <https://www.sqlite.org/c3ref/open.html> that prevents
SQLite from opening symbolic links.


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

Re: [sqlite] Sqlite error code 14 when using 3.31.0+

[Graham Holden]

Tuesday, March 10, 2020, 1:33:13 AM, Rowan Worth  wrote:

> On Mon, 9 Mar 2020 at 23:22, Daniel Polski  wrote:

>> Updated to 3.31.1 but my application started spitting out an error when
>> opening the database, so I tested some earlier sqlite versions to figure
>> out when the problem starts.
>> I don't get the message in versions <= 3.30.1.
>>
>> (from the applications log)
>> SQLite Version: 3.31.0
>> INFO: Database opened: /tmp/database.sqlite
>> WARNING: SQLITE error code: 14 cannot open file at line 36982 of
>> [3bfa9cc97d]
>> WARNING: SQLITE error code: 14 os_unix.c:36982: (40) openDirectory(/tmp) -
>>

> errno 40 is ELOOP, "Too many symbolic links encountered". open(2) says:

>ELOOP  Too many symbolic links were encountered in resolving
>  pathname,  or  O_NOFOLLOW  was
>   specified but pathname was a symbolic link.

> Is your /tmp/ a symlink? Sqlite seems to use O_NOFOLLOW unconditionally in
> openDirectory() since this checkin:

> https://www.sqlite.org/src/info/6a64fb6a2da6c98f

> Probably a bug? The changelog for sqlite 3.31.0 include this which is
> likely related:

> - * Add the SQLITE_OPEN_NOFOLLOW
> <https://www.sqlite.org/c3ref/open.html#opennofollow> option to
> sqlite3_open_v2() <https://www.sqlite.org/c3ref/open.html> that prevents
> SQLite from opening symbolic links.

Assuming /tmp IS a symlink, then since it's the parent directory of
the SQLite file that openDirectory fails on, I would hazard a wild
guess that this has something to do with SQLite's handling of journal
files, about which that above check-in comment notes "O_NOFOLLOW is
always included in open() system calls for journal files".

Graham Holden

(There were an earlier pair of emails to the list on 13th Feb this
year where use of O_NOFOLLOW was triggering ELOOP, but I suspect that
wasn't directly related as the problem there was "protection" code in
SQLite that was opening /dev/null was failing because /dev/null under
Solaris was itself a symbolic link (see
https://www.sqlite.org/src/timeline?c=0c683c43a62fe25c)


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

Re: [sqlite] Sqlite error code 14 when using 3.31.0+

[Rowan Worth]

On Mon, 9 Mar 2020 at 23:22, Daniel Polski  wrote:

> Updated to 3.31.1 but my application started spitting out an error when
> opening the database, so I tested some earlier sqlite versions to figure
> out when the problem starts.
> I don't get the message in versions <= 3.30.1.
>
> (from the applications log)
> SQLite Version: 3.31.0
> INFO: Database opened: /tmp/database.sqlite
> WARNING: SQLITE error code: 14 cannot open file at line 36982 of
> [3bfa9cc97d]
> WARNING: SQLITE error code: 14 os_unix.c:36982: (40) openDirectory(/tmp) -
>

errno 40 is ELOOP, "Too many symbolic links encountered". open(2) says:

   ELOOP  Too many symbolic links were encountered in resolving
 pathname,  or  O_NOFOLLOW  was
  specified but pathname was a symbolic link.

Is your /tmp/ a symlink? Sqlite seems to use O_NOFOLLOW unconditionally in
openDirectory() since this checkin:

https://www.sqlite.org/src/info/6a64fb6a2da6c98f

Probably a bug? The changelog for sqlite 3.31.0 include this which is
likely related:

- * Add the SQLITE_OPEN_NOFOLLOW
<https://www.sqlite.org/c3ref/open.html#opennofollow> option to
sqlite3_open_v2() <https://www.sqlite.org/c3ref/open.html> that prevents
SQLite from opening symbolic links.

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

[sqlite] Sqlite error code 14 when using 3.31.0+

[Daniel Polski]

Updated to 3.31.1 but my application started spitting out an error when 
opening the database, so I tested some earlier sqlite versions to figure 
out when the problem starts.

I don't get the message in versions <= 3.30.1.

(from the applications log)
SQLite Version: 3.31.0
INFO: Database opened: /tmp/database.sqlite
WARNING: SQLITE error code: 14 cannot open file at line 36982 of 
[3bfa9cc97d]

WARNING: SQLITE error code: 14 os_unix.c:36982: (40) openDirectory(/tmp) -

It seems related to opening the WAL file. Anything I should change when 
using sqlite >= 3.31.0 ?


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

Re: [sqlite] Error/Result code documentation

[Dominique Devienne]

On Wed, Feb 26, 2020 at 11:09 AM Dominique Devienne  wrote:
> Hi. In the doc excerpt below from https://www.sqlite.org/rescode.html
> are the $n*Code variables supposed to be expanded with a numeric
> value, and there's a little issue in the doc generation? Thanks, --DD

Thanks for the fix Richard. --DD

To anyone else, here's the fix:
https://www.sqlite.org/docsrc/info/d35032eb7ff2018b
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Error/Result code documentation

[Dominique Devienne]

Hi. In the doc excerpt below from https://www.sqlite.org/rescode.html
are the $n*Code variables supposed to be expanded with a numeric
value, and there's a little issue in the doc generation? Thanks, --DD

4. Primary Result Code List
The $nPrimCode result codes ...

5. Extended Result Code List
The $nExtCode extended result codes ...
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Error? ext/misc series.c and spellfix.c

[Keith Medcalf]

sqlite3_vtab_config(db, SQLITE_INNOCUOUS) should that not be 
sqlite3_vtab_config(db, SQLITE_VTAB_INNOCUOUS)?

Which explains why my quick patch in the pragma.c xConnect code to make all 
pragma vtabs innocuous didn't work (I copied from series.c) :)

  rc = sqlite3_declare_vtab(db, zBuf);
  if( rc==SQLITE_OK ){
pTab = (PragmaVtab*)sqlite3_malloc(sizeof(PragmaVtab));
if( pTab==0 ){
  rc = SQLITE_NOMEM;
}else{
  sqlite3_vtab_config(db, SQLITE_VTAB_INNOCUOUS);  // Make the vtab 
innocuous
  memset(pTab, 0, sizeof(PragmaVtab));
  pTab->pName = pPragma;
  pTab->db = db;
  pTab->iHidden = i;
  pTab->nHidden = j;
}
  }else{
*pzErr = sqlite3_mprintf("%s", sqlite3_errmsg(db));
  }

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.




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

Re: [sqlite] Error 19 with Caret ^ INSERT

[Keith Medcalf]

How do you know that the "error" is related to the use of a caret?

Perhaps your .net wrapper is diddling text strings that contain carets for some 
reason known only to the wrapper writer?  Have you checked the wrapper 
documentation to see if such diddling is documented, and if so, how to turn it 
off?

And where do you get the idea that a caret in a string is a "filter or so"?

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users  On
>Behalf Of André Borchert
>Sent: Saturday, 5 October, 2019 22:56
>To: sqlite-users@mailinglists.sqlite.org
>Subject: [sqlite] Error 19 with Caret ^ INSERT
>
>Hello,
>
>in my C# program which uses DllImport for sqlite.dll (3.29) I get Error
>19 (SQLITE_CONSTRAINT) with this statement:
>
>INSERT INTO CompanyDetails (RIC) VALUES('AMBEUR.IP^G19')
>
>The ^ symbol is part of the text I want to save, its not a filter or so,
>its just plain text.
>
>The odd thing is that the sql statement works just fine when I use DB
>Browser for SQLite.
>The row looks like expected, with the ^ character saved as text.
>
>Sent with [ProtonMail](https://protonmail.com) Secure Email.
>___
>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] Error 19 with Caret ^ INSERT

[Simon Slavin]

On 6 Oct 2019, at 5:56am, André Borchert  wrote:

> The odd thing is that the sql statement works just fine when I use DB Browser 
> for SQLite.

Try the SQLite command line tool.  That's written by the team which maintains 
SQLite itself, and it should give the connonically correct result.  If it 
objects to a constraint then the team can definitely tell you why.


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

[sqlite] Error 19 with Caret ^ INSERT

[André Borchert]

Hello,

in my C# program which uses DllImport for sqlite.dll (3.29) I get Error 19 
(SQLITE_CONSTRAINT) with this statement:

INSERT INTO CompanyDetails (RIC) VALUES('AMBEUR.IP^G19')

The ^ symbol is part of the text I want to save, its not a filter or so, its 
just plain text.

The odd thing is that the sql statement works just fine when I use DB Browser 
for SQLite.
The row looks like expected, with the ^ character saved as text.

Sent with [ProtonMail](https://protonmail.com) Secure Email.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] error: unable to read database header

[David Raymond]

When you run ".open test.db" if the database doesn't already exist, then the 
file is created. However *no data is written yet* as there are a few permanent 
things you could change at the very start (like page size, encoding, etc), so 
it holds off on writing the first page with the header until you issue some 
sort of statement that isn't setting those "new database options" So since the 
file is still 0 size at this point, there is no header for .dbinfo to read 
from. If you create a table first for example, then it will populate the header 
and actually write to the file, at which point there will be a header for 
.dbinfo to read.


-Original Message-
From: sqlite-users  On Behalf Of 
Alexander Gabriel
Sent: Friday, August 30, 2019 10:54 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] error: unable to read database header

Hi

I downloaded the files for v3.29.0 into a folder.
Then double clicked sqlite3.exe to open a command shell.
Then typed `.open test.db`
Then typed `.dbinfo`
And got: `unable to read database header`

What am I doing wrong?
Have I created a broken database?

Alex
___
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] error: unable to read database header. Also: My mails are being held back due to subscription to list not having worked

[Alexander Gabriel]

Hi

*error: unable to read database header*

Sorry for spamming this list but as it is an email list I cannot add to my
original issue as for instance in github.

As mentioned in my previous email I have donwloaded binaries for windows
10, run `.open test` followed by `.dbinfo` and got this error: `unable to
read database header`. There is an old issue from v3.20.1 that reported the
same error which seemed to be a build issue which later was corrected if I
understand correctly:
https://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg106549.html

I have tried:

   - installing sqlite3 in ubuntu on windows by copying the binaries and
   running `sqlite3` as per your documentation. Result: sqlite3 not installed
   - installing sqlite3 in ubuntu on windows by copying the binaries and
   running `./sqlite3`. Result: `bash: ./sqlite3: cannot execute binary file:
   Exec format error`
   - installing sqlite3 in ubuntu on windows by using `sudo apt-get update`
   then `sudo apt-get install sqlite3`. Result: v2.8.17 installed. I do not
   want to use such an old version
   - installing sqlite3 on macOS  by copying the binaries and running
   `sqlite3` as per your documentation. Result: sqlite3 not installed
   - installing sqlite3 on macOS  by copying the binaries and running
   `./sqlite3`. Works. But same error as in Windows 10 when running `.open
   test` followed by `.dbinfo`: `unable to read database header`

I have also tried to access binaries for older versions like 3.28.0. Maybe
this error does not occur there? After all it did not occur last year when
I worked with v3.27.2. But it seems binaries for older versions do not
exist.

I have also tried a few more things like searching google. Unfortunately it
seems that it is MUCH harder to find help for issues concerning sqlite3
than for any other library I have used so far.

So after a few hours I am left with a db file created that cannot show
.dbinfo and makes me feel real bad. Can I trust it and use it?

*Subscription to List not working*

I posted the following email to sqlite-us...@sqlite.org (this email was
explicitly shown somewhere on the sqlite.org site):
---
I signed in to
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users.
I got the confirmation email for
sqlite-users-requ...@mailinglists.sqlite.org and confirmed.

But every time I try to log in I get this error:

> *sqlite-users roster authentication failed.*

---
 but got this answer:

---
This is the mail system at host relay.mailchannels.net.

I'm sorry to have to inform you that your message could not
be delivered to one or more recipients. It's attached below.

For further assistance, please send mail to postmaster.

If you do so, please include this problem report. You can
delete your own text from the attached returned message.
---

I also sent a first error message to
sqlite-users-ow...@mailinglists.sqlite.org and got this answer:

---
Your mail to 'sqlite-users' with the subject

error: unable to read database header

Is being held until the list moderator can review it for approval.

The reason it is being held:

Post by non-member to a members-only list

Either the message will get posted to the list, or you will receive
notification of the moderator's decision.  If you would like to cancel
---

It seems that the fact that signing up to the list seems not to work (in my
case?) also may prevent me from mailing to the list :-(

I feel sqlite3 is making it REALLY hard to get a working version of sqlite3
if a published version happens to be faulty.
I realize this is open source and I have no right to complain. But maybe it
could be made easier to report issues in a timely fashion?

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

[sqlite] error: unable to read database header

[Alexander Gabriel]

Hi

I downloaded the files for v3.29.0 into a folder.
Then double clicked sqlite3.exe to open a command shell.
Then typed `.open test.db`
Then typed `.dbinfo`
And got: `unable to read database header`

What am I doing wrong?
Have I created a broken database?

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

Re: [sqlite] Error response for automatic transaction rollback

[Warren Young]

On Aug 20, 2019, at 9:09 AM, Matt Zand  wrote:
> 
> I wonder if version 3.29 does support error handling for sql transaction
> rollbacks. Also, does it support bypassing errors by forcing unconditional
> rollback yet.

It sounds like you’re looking for CREATE TRIGGER…BEFORE UPDATE…RAISE(ROLLBACK) 
and such:

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

Re: [sqlite] Error response for automatic transaction rollback

[Richard Hipp]

On 8/20/19, Matt Zand  wrote:
> I wonder if version 3.29 does support error handling for sql transaction
> rollbacks. Also, does it support bypassing errors by forcing unconditional
> rollback yet.

I don't understand what you are asking.

-- 
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

[sqlite] Error response for automatic transaction rollback

[Matt Zand]

Hey guys,

I wonder if version 3.29 does support error handling for sql transaction
rollbacks. Also, does it support bypassing errors by forcing unconditional
rollback yet.


-- 
Cheers,

Matt Zand
Cell: 202-420-9192
Work: 240-200-6131
High School Technology Services 
DC Web Makers 
Coding Bootcamps 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Error in recover sqlite3 database

[bhandari_nikhil]

My sqlite3 database keeps on increasing in memory (although it is limited by
memory). If I try to rebuild the database, it is giving the following error:

sqlite> INSERT INTO tbllog(tbllog) VALUES('rebuild');   
  

Error: database or disk is full

Is the solution only to remove the db file ? Or we can do something about it
? BTW, even after a reboot of the device, we land up in the same situation
after some time. Is there some problem with the journal ?



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Error when renaming a table when an invalid view exists in the schema

[Tom Bassel]

Thanks Keith. LEGACY_ALTER_TABLE=ON lets me change the table name without 
error. But the docs say "New applications should leave this flag turned off."

Is there any other way of checking if the schema is invalid besides attempting 
to change the name of a table?

I went through the pragmas and commands below and they did not report that the 
schema was invalid.

SQLite version 3.29.0 2019-04-27 20:30:19
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table t1(f1);
sqlite> create view v1 as select * from t1;
sqlite> drop table t1;
sqlite> create table t2(f1);
sqlite> .schema
CREATE VIEW v1 as select * from t1;
CREATE TABLE t2(f1);
sqlite> pragma integrity_check;
ok
sqlite> pragma quick_check;
ok
sqlite> pragma optimize;
sqlite> vacuum;
sqlite> pragma foreign_key_check;
sqlite> pragma database_list;
0|main|
1|temp|
sqlite> .lint
Usage lint sub-command ?switches...?
Where sub-commands are:
fkey-indexes
sqlite> .lint fkey-indexes
sqlite> alter table t2 rename to t20;
Error: error in view v1: no such table: main.t1


---
Date: Fri, 03 May 2019 13:27:18 -0600
From: "Keith Medcalf" 
To: "SQLite mailing list" 
Subject: Re: [sqlite] Error when renaming a table when an invalid view
exists in the schema
Message-ID: <802bb8bcf08af448bd5d9a4b9bf2f...@mail.dessus.com>
Content-Type: text/plain; charset="utf-8"


Use PRAGMA LEGACY_ALTER_TABLE=ON;

The "Legacy alter table" does not require the database to be "valid/consistent" 
after executing the "alter table" command.  The non-legacy (default) mode 
requires that the database be "transformed" in
to a "valid/consistent" state in order for the alter table command to be 
processed.

A side effect of this is that if the database was invalid BEFORE you issue the 
alter table command, and it is still invalid after, that in the "validity" 
checking mode (the default), the alter table c
ommand will not be processed (since it will throw an error that the 
transformation did not result in a "valid" schema).

Correctly, you should either (a) drop view v1, or (b) create table t1 so that 
the database schema is consistent before you attempt to use ALTER TABLE. (Or, 
if you know that your schema is invalid, you
 can turn off validity checking with pragma LEGACY_ALTER_TABLE).  Since 
creating a view is nothing more than storing a statement in the database, it is 
not checked when you create a view or drop a tab
le that the schema is still valid (otherwise you would get a message of the 
form "Cannot drop table t1 because it is referenced in a view" or mayhaps view 
v1 would be dropped automatically).

Perhaps a setting "ALLOW_INVALID_SCHEMA" needs to be added such that after each 
DDL statement the schema is checked for validity and if it is not valid then 
the DDL is tossed with an error (such as oc
curs in SQLFat databases)?

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

Re: [sqlite] Error when renaming a table when an invalid view exists in the schema

[Keith Medcalf]

Use PRAGMA LEGACY_ALTER_TABLE=ON;

The "Legacy alter table" does not require the database to be "valid/consistent" 
after executing the "alter table" command.  The non-legacy (default) mode 
requires that the database be "transformed" into a "valid/consistent" state in 
order for the alter table command to be processed.

A side effect of this is that if the database was invalid BEFORE you issue the 
alter table command, and it is still invalid after, that in the "validity" 
checking mode (the default), the alter table command will not be processed 
(since it will throw an error that the transformation did not result in a 
"valid" schema).

Correctly, you should either (a) drop view v1, or (b) create table t1 so that 
the database schema is consistent before you attempt to use ALTER TABLE. (Or, 
if you know that your schema is invalid, you can turn off validity checking 
with pragma LEGACY_ALTER_TABLE).  Since creating a view is nothing more than 
storing a statement in the database, it is not checked when you create a view 
or drop a table that the schema is still valid (otherwise you would get a 
message of the form "Cannot drop table t1 because it is referenced in a view" 
or mayhaps view v1 would be dropped automatically).

Perhaps a setting "ALLOW_INVALID_SCHEMA" needs to be added such that after each 
DDL statement the schema is checked for validity and if it is not valid then 
the DDL is tossed with an error (such as occurs in SQLFat databases)?

The only possible inconsistency that I see is that:

sqlite> create view v1 as select * from t1;
sqlite> create table t2(x);
sqlite> alter table t2 rename to t1;
Error: error in view v1: no such table: main.t1

which means that you must use legacy_alter_table in order to be able to fix 
typo's ... The rename is prohibited because the schema is inconsistent BEFORE 
the change, even though it would be consistent AFTER the change ...

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Tom Bassel
>Sent: Friday, 3 May, 2019 12:51
>To: sqlite-users@mailinglists.sqlite.org
>Subject: [sqlite] Error when renaming a table when an invalid view
>exists in the schema
>
>Hello again everyone,
>
>I went through the ALTER TABLE docs but could not find anything
>describing this behavior:
>
>
>SQLite version 3.29.0 2019-04-27 20:30:19
>Enter ".help" for usage hints.
>Connected to a transient in-memory database.
>Use ".open FILENAME" to reopen on a persistent database.
>sqlite> create table t1(f1);
>sqlite> create view v1 as select * from t1;
>sqlite> drop table t1;
>sqlite> create table t2(f1);
>sqlite> .schema
>CREATE VIEW v1 as select * from t1;
>CREATE TABLE t2(f1);
>sqlite> alter table t2 rename to t20;
>Error: error in view v1: no such table: main.t1
>
>
>That is, if an invalid view exists in the schema, then any attempt to
>alter the name of another unrelated table will fail with the error
>message that there is a view that references a different table that
>does not exist.
>
>It also occurs in 3.27 but I downloaded and tried it on 3.29 just in
>case something changed recently.
>
>Thanks
>Tom
>___
>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] Error when renaming a table when an invalid view exists in the schema

[Tom Bassel]

Hello again everyone,

I went through the ALTER TABLE docs but could not find anything describing this 
behavior:


SQLite version 3.29.0 2019-04-27 20:30:19
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table t1(f1);
sqlite> create view v1 as select * from t1;
sqlite> drop table t1;
sqlite> create table t2(f1);
sqlite> .schema
CREATE VIEW v1 as select * from t1;
CREATE TABLE t2(f1);
sqlite> alter table t2 rename to t20;
Error: error in view v1: no such table: main.t1


That is, if an invalid view exists in the schema, then any attempt to alter the 
name of another unrelated table will fail with the error message that there is 
a view that references a different table that does not exist.

It also occurs in 3.27 but I downloaded and tried it on 3.29 just in case 
something changed recently.

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

Re: [sqlite] SQLite error while fetching the data from a table

[Tommy Lane]

Hi All,

Hi Ananta


Need quick help to resolve one issue i am getting now.
I am a new user of SQLite.

my code:
connection =
DriverManager.getConnection("jdbc:sqlite:C:\\sqllite\\sqlite-tools-win32-x86-328\\Stories.db");
 Statement st = connection.createStatement();
 ResultSet b = st.executeQuery("select count(*) from stories;");

Have you tried running the query using the cli on Stories.db?

sqlite3 'path-to-Stories.db'

sqlite> select count(*) from stories;

What output do you get?



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

Re: [sqlite] SQLite error while fetching the data from a table

[Luuk]

On 22-4-2019 14:03, Ananta Jena wrote:

Hi All,

Need quick help to resolve one issue i am getting now.
I am a new user of SQLite.

my code:
connection =
DriverManager.getConnection("jdbc:sqlite:C:\\sqllite\\sqlite-tools-win32-x86-328\\Stories.db");
  Statement st = connection.createStatement();
  ResultSet b = st.executeQuery("select count(*) from stories;");

Note : Connection is established successfully and also table STORIES has 1
record as well.

while executing this ,i am geting below error:
java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (no
such table: stories)



The error says: "SQL error or missing database"

on the next line is: "no such table: stories"


Conclusion: there must be something wrong in the code you did not post.

(but i'm not a java programmer)

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

[sqlite] SQLite error while fetching the data from a table

[Ananta Jena]

Hi All,

Need quick help to resolve one issue i am getting now.
I am a new user of SQLite.

my code:
connection =
DriverManager.getConnection("jdbc:sqlite:C:\\sqllite\\sqlite-tools-win32-x86-328\\Stories.db");
 Statement st = connection.createStatement();
 ResultSet b = st.executeQuery("select count(*) from stories;");

Note : Connection is established successfully and also table STORIES has 1
record as well.

while executing this ,i am geting below error:
java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (no
such table: stories)
at org.sqlite.DB.newSQLException(DB.java:383) ~[sqlite-jdbc-3.7.2.jar:na]
at org.sqlite.DB.newSQLException(DB.java:387) ~[sqlite-jdbc-3.7.2.jar:na]
at org.sqlite.DB.throwex(DB.java:374) ~[sqlite-jdbc-3.7.2.jar:na]
at org.sqlite.NativeDB.prepare(Native Method) ~[sqlite-jdbc-3.7.2.jar:na]
at org.sqlite.DB.prepare(DB.java:123) ~[sqlite-jdbc-3.7.2.jar:na]
at org.sqlite.Stmt.executeQuery(Stmt.java:121) ~[sqlite-jdbc-3.7.2.jar:na]
at
com.sabre.ngp.devx.storyeditor.service.StoryEditorServiceImpl.getData(StoryEditorServiceImpl.java:49)
~[classes/:na]
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
~[na:1.8.0_91]
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
~[na:1.8.0_91]
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
~[na:1.8.0_91]
at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_91]
at
org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:221)
~[spring-web-4.3.3.RELEASE.jar:4.3.3.RELEASE]
at
org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:136)
~[spring-web-4.3.3.RELEASE.jar:4.3.3.RELEASE]
at
org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:114)
~[spring-webmvc-4.3.3.RELEASE.jar:4.3.3.RELEASE]
at
org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:827)
~[spring-webmvc-4.3.3.RELEASE.jar:4.3.3.RELEASE]
at
org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:738)
~[spring-webmvc-4.3.3.RELEASE.jar:4.3.3.RELEASE]
at
org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)
~[spring-webmvc-4.3.3.RELEASE.jar:4.3.3.RELEASE]
at
org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:963)
~[spring-webmvc-4.3.3.RELEASE.jar:4.3.3.RELEASE]
at
org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:897)
~[spring-webmvc-4.3.3.RELEASE.jar:4.3.3.RELEASE]
at
org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970)
~[spring-webmvc-4.3.3.RELEASE.jar:4.3.3.RELEASE]
at
org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:861)
~[spring-webmvc-4.3.3.RELEASE.jar:4.3.3.RELEASE]
at javax.servlet.http.HttpServlet.service(HttpServlet.java:622)
~[tomcat-embed-core-8.5.5.jar:8.5.5]
at
org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846)
~[spring-webmvc-4.3.3.RELEASE.jar:4.3.3.RELEASE]
at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
~[tomcat-embed-core-8.5.5.jar:8.5.5]
at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:230)
~[tomcat-embed-core-8.5.5.jar:8.5.5]
at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:165)
~[tomcat-embed-core-8.5.5.jar:8.5.5]
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
~[tomcat-embed-websocket-8.5.5.jar:8.5.5]
at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:192)
~[tomcat-embed-core-8.5.5.jar:8.5.5]
at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:165)
~[tomcat-embed-core-8.5.5.jar:8.5.5]
at
org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:99)
~[spring-web-4.3.3.RELEASE.jar:4.3.3.RELEASE]
at
org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
~[spring-web-4.3.3.RELEASE.jar:4.3.3.RELEASE]
at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:192)
~[tomcat-embed-core-8.5.5.jar:8.5.5]
at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:165)
~[tomcat-embed-core-8.5.5.jar:8.5.5]
at
org.springframework.web.filter.HttpPutFormContentFilter.doFilterInternal(HttpPutFormContentFilter.java:89)
~[spring-web-4.3.3.RELEASE.jar:4.3.3.RELEASE]
at
org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
~[spring-web-4.3.3.RELEASE.jar:4.3.3.RELEASE]
at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterCh

Re: [sqlite] Error in docs

[Dominique Devienne]

On Tue, Apr 9, 2019 at 9:41 AM Richard Hipp  wrote:

> On 4/9/19, Dominique Devienne  wrote:
> >>
> >> It defines the table and view:
> >> CREATE TABLE t1(a INT, b TEXT, c REAL);
> >> CREATE VIEW v1(x,y,z) AS SELECT b, a+c, 42 FROM t1 WHERE b!=11;
> >>
> >> It then states "The affinity of the v1.x column will be the same as the
> >> affinity of t1.b (INTEGER), since v1.x maps directly into t1.b.".
> >>
> >> However column t1.b is TEXT, not INTEGER.
> >>
> >
> > How to do determine that exactly? Via SQL? Debugging?
>
> If you compile with -DSQLITE_DEBUG, then there is a new undocumented
> function affinity().  After inserting a row into table t1:
>

Cool! Thanks for the heads up. But why debug only? It's as useful as
typeof() IMHO.
Obviously affinity-related-code is compiled-in in non-debug build, so I
don't see any
down side of unconditionally adding a function that if not explicitly used
as zero runtime
overhead, no? While SQLITE_DEBUG itself likely adds overhead.

Would be really useful for troubleshooting and teaching to always have it.
My $0.02. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Error in docs

[Richard Hipp]

On 4/9/19, Dominique Devienne  wrote:
>>
>> It defines the table and view:
>> CREATE TABLE t1(a INT, b TEXT, c REAL);
>> CREATE VIEW v1(x,y,z) AS SELECT b, a+c, 42 FROM t1 WHERE b!=11;
>>
>> It then states "The affinity of the v1.x column will be the same as the
>> affinity of t1.b (INTEGER), since v1.x maps directly into t1.b.".
>>
>> However column t1.b is TEXT, not INTEGER.
>>
>
> How to do determine that exactly? Via SQL? Debugging?

If you compile with -DSQLITE_DEBUG, then there is a new undocumented
function affinity().  After inserting a row into table t1:

INSERT INTO t1 values(1,2,3);

You can do:

SELECT affinity(x), affinity(y), affinity(z) FROM v1;

And get the answer:

'text','none','none'

-- 
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] Error in docs

[Dominique Devienne]

On Mon, Apr 8, 2019 at 7:58 PM Jim Dossey  wrote:

> I think I found an error in the documentation here:
> https://www.sqlite.org/datatype3.html#column_affinity_for_views_and_subqueries
> <
> https://www.sqlite.org/datatype3.html#column_affinity_for_views_and_subqueries
> >
>
> It defines the table and view:
> CREATE TABLE t1(a INT, b TEXT, c REAL);
> CREATE VIEW v1(x,y,z) AS SELECT b, a+c, 42 FROM t1 WHERE b!=11;
>
> It then states "The affinity of the v1.x column will be the same as the
> affinity of t1.b (INTEGER), since v1.x maps directly into t1.b.".
>
> However column t1.b is TEXT, not INTEGER.
>

How to do determine that exactly? Via SQL? Debugging?

There's no way to get "directly" the affinity of a column, that I know of,
you can only infer it from side-effects I believe. Thus my curiosity. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Error in docs

[Jim Dossey]

I think I found an error in the documentation here: 
https://www.sqlite.org/datatype3.html#column_affinity_for_views_and_subqueries 


It defines the table and view:
CREATE TABLE t1(a INT, b TEXT, c REAL);
CREATE VIEW v1(x,y,z) AS SELECT b, a+c, 42 FROM t1 WHERE b!=11;

It then states "The affinity of the v1.x column will be the same as the 
affinity of t1.b (INTEGER), since v1.x maps directly into t1.b.".

However column t1.b is TEXT, not INTEGER.

Do I get a check for $2.56 from DRH?  :-)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Error message "Attempt to write to read-only database"

[Beatrix Willius]

Hi,

first post here. Got a perplexing problem with SQLite and no, the database is 
perfectly writable.

In my app I use a different database as main database that unfortunately 
doesn't have an FTS. Therefore, I'm using an SQLite database as index. The 
SQLite database is very simple:

CREATE VIRTUAL TABLE bodyindex USING fts4(tokenize=unicode61,content='', 
messagebody);

I noticed for large messagebody fields that I see the beachball. Another SQLite 
library offers multithreaded methods to get rid of the beachball. Now, if I 
start writing to the database I get the above error. If I create the SQLite 
database with the old SQLite library and then start writing everything works 
fine.

Here is the code for writing:

  dim thePreparedStatement as SQLitePreparedStatement = 
SQLiteIndexDB.Prepare("INSERT INTO bodyindex(docid, messagebody) VALUES(" + 
str(theRecID) + ", ?)")
  thePreparedStatement.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
thePreparedStatement.SQLExecute(MessageBody)
  
  If SQLiteIndexDB.Error Then
globals.theErrorLog.LogItem(CurrentMethodName)
Globals.theErrorLog.DialogErrorProceed kErrorWrite + " " + 
SQLiteIndexDB.ErrorMessage
globals.StopArchiving = True
Return
  End If
  
  thePreparedStatement = Nil

I made an example and - of course - everything works fine there.

I think that the problem is thread-related. Does anyone have an idea what might 
cause this odd problem?

macOS 10.13 + 10.14. I'm using a language + IDE called Xojo.

And just because I like fun I get some SQLite related zombies on 10.14:

0   MBS_SQL_SQLite_Plugin_19846.dylib   0x00010d6e515c 
sqlite3LeaveMutexAndCloseZombie + 1596
1   MBS_SQL_SQLite_Plugin_19846.dylib   0x00010d6e454e sqlite3Close + 878
2   MBS_SQL_SQL_Plugin_19846.dylib  0x00010d460a1f 
Isl3Connection::Disconnect() + 31
3   MBS_SQL_SQL_Plugin_19846.dylib  0x00010d409307 
SAConnection::Disconnect() + 87
4   MBS_SQL_SQL_Plugin_19846.dylib  0x00010d48297a closeDB(dbDatabase*) 
+ 42
5   XojoFramework   0x000109b10206 databaseClose + 58
6   Mail Archiver X.debug   0x000105aa002b 
Database.Close%%o + 11
7   Mail Archiver X.debug   0x000106c949e4 
SQLiteIndex.CloseSQLite%%o + 580 (/SQLiteIndex:376)
8   Mail Archiver X.debug   0x00010767bf7f 
MaxModel.CloseArchive%%o + 1343 (/MaxModel:696)
9   Mail Archiver X.debug   0x000108088439 
MainWindowController.CloseArchive%%o + 329 
(/MainWindowController:358)
10  Mail Archiver X.debug   0x0001080d66ec 
MainWindow.MainWindow.CloseDB%%ob + 6476 
(/MainWindow:1253)
11  Mail Archiver X.debug   0x0001080be27f 
MainWindow.MainWindow.Event_CloseDatabase%%ob + 671 
(/MainWindow:714)
12  Mail Archiver X.debug   0x00010815935e 
DBWindow.CloseArchive%%o + 958 (/DBWindow:579)
13  Mail Archiver X.debug   0x00010814f611 
DBWindow._FileClose_Action%b%o + 337 (/DBWindow:235)
14  XojoFramework   0x000109b58979 
RuntimeMenuItemClick(RunMenuItem*, unsigned char, Window*, unsigned char*) + 
1063
15  XojoFramework   0x000109a5bef8 
CocoaMenu::_MenuItemAction(NSMenuItem*) + 72

10.13 just dies without crashlog. Sometimes and not always.

Mit freundlichen Grüßen/Regards

Beatrix Willius

http://www.mothsoftware.com 
Mail Archiver X: The email archiving solution for professionals

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

Re: [sqlite] SQLite error (5): database is locked

[Simon Slavin]

On 4 Feb 2019, at 3:15pm, Urs Wagner  wrote:

> SQLite error (5): database is locked occurs?

Can't answer your question, but …

If you're getting unexpected locks, have you set a timeout on every connection 
to that database ?  That gets rid of most locks.

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

[sqlite] SQLite error (5): database is locked

[Urs Wagner]

Hello

Is it possible to get a C# exception when the error

SQLite error (5): database is locked occurs?

I am using the entity framework with multiple thread and a global mutex.
I would like to know which call generates the locking error.

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

Re: [sqlite] SQLite error (5): database is locked

[Rowan Worth]

On Tue, 15 Jan 2019 at 02:54, Simon Slavin  wrote:

> The "just-in-time" idea mentioned in your question doesn't work in real
> life, since constantly checking mutex status keeps one core completely
> busy, using lots of power and generating lots of heat.
>

Technically "just-in-time" could be implemented fine; the normal file
locking primitives used on both windows and unix can operate in a blocking
mode, where the kernel wakes the process up once the lock has been
relinquished. sqlite just doesn't use that API.

It would be a pretty significant change for sqlite to invoke said API, and
may affect the ability to support other existing locking modes which don't
provide the same semantics and where polling _is_ required (eg. dotfile).

Also I'm not 100% sure whether it would be safe to drop in, or whether
blocking the process for lock acquisition within sqlite's locking protocol
would introduce deadlock scenarios.

FYI sqlite's backoff reaches a maximum sleep time of 100ms. Unless you're
on unix and compile without -DHAVE_USLEEP=1 in which case there's no
backoff and every sleep is 1000ms long.

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

Re: [sqlite] SQLite error (5): database is locked

[Simon Slavin]

On 14 Jan 2019, at 7:04pm, Tim Streater  wrote:

> Hmm, from my attempt at interpreting the source code I gained the impression 
> that the handler used exponential backoff for the first few attempts, but 
> then used a constant period for the rest (up to the set limit). Is this not 
> the case?

I'm sure your interpretation is correct.  I just knew that the default handler 
used exponential backoff, and what that means.  I didn't know that it used it 
for every retry.  It seems likely that the dev team set a maximum based on 
their understanding of what lock times were plausible.

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

Re: [sqlite] SQLite error (5): database is locked

[Tim Streater]

On 14 Jan 2019, at 18:54, Simon Slavin  wrote:

> SQLite's own busy_timeout routine (the one you get if you don't supply your
> own) uses exponential backoff. It first sleeps for a very small amount of
> time, then checks the lock. If access is still prevented it sleeps longer,
> then checks again. Then longer still, then longer still, until the amount of
> time /since the routine started/ exceeds the timeout you set.

Hmm, from my attempt at interpreting the source code I gained the impression 
that the handler used exponential backoff for the first few attempts, but then 
used a constant period for the rest (up to the set limit). Is this not the case?


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

Re: [sqlite] SQLite error (5): database is locked

[Simon Slavin]

On 14 Jan 2019, at 6:34pm, Thomas Kurz  wrote:

>> pragma_busy_timeout
> 
> Does setting the busy_timeout retry periodically (e.g. every x milliseconds), 
> or is there some automatism that ensures that the requested operation is done 
> just-in-time as soon as the previous/blocking operation is finished?

The busy_timeout setting is the maximum amount of time that the busy_timeout 
routine is allowed to run.  That routine is not expected to sleep for that 
amount of time you set in a single sleep.

SQLite's own busy_timeout routine (the one you get if you don't supply your 
own) uses exponential backoff.  It first sleeps for a very small amount of 
time, then checks the lock.  If access is still prevented it sleeps longer, 
then checks again.  Then longer still, then longer still, until the amount of 
time /since the routine started/ exceeds the timeout you set.

The "just-in-time" idea mentioned in your question doesn't work in real life, 
since constantly checking mutex status keeps one core completely busy, using 
lots of power and generating lots of heat.

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

Re: [sqlite] SQLite error (5): database is locked

[Thomas Kurz]

> pragma_busy_timeout

Does setting the busy_timeout retry periodically (e.g. every x milliseconds), 
or is there some automatism that ensures that the requested operation is done 
just-in-time as soon as the previous/blocking operation is finished?

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

Re: [sqlite] SQLite error (5): database is locked

[Keith Medcalf]

Are you getting as OS ERROR 5 or the SQLITE Return Code 5 (SQLITE_BUSY).  

The former means that the OS is indicating that the USERID you are using to run 
the task cannot open the file or the directory or the temporary files or the 
journal files required because it has not been granted the necessary 
permissions to do so; the latter indicates that the file has been opened but 
that you simply are trying to do something while the database is busy doing 
something else.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Urs Wagner
>Sent: Monday, 14 January, 2019 02:24
>To: SQLite mailing list
>Subject: [sqlite] SQLite error (5): database is locked
>
>Hallo
>
>I use several tasks in C# to call Sqlite queries.
>No I get the error SQLite error (5): database is locked.
>Is it not possible to use more than one tasks with Sqlite?
>
>Regards
>
>Urs
>___
>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] [EXTERNAL] SQLite error (5): database is locked

[Simon Slavin]

On 14 Jan 2019, at 11:14am, Urs Wagner  wrote:

>ProviderConnectionString = @"data source=" + _dataBase + 
> ";PRAGMA foreign_keys = ON;PRAGMA locking_mode = EXCLUSIVE;PRAGMA 
> schema.synchronous = NORMAL; PRAGMA schema.journal_mode = DELETE; PRAGMA 
> busy_timeout = 10"

I am suspicious about this, but I do not know entity framework well enough to 
know for sure.  Can you include arbitrary PRAGMA statements in am entity 
framework connection string like this ?

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

Re: [sqlite] [EXTERNAL] SQLite error (5): database is locked

[Urs Wagner]

Thank You for Your explanation.
We do not using explicit transactions, SaveChanges takes rather a lot of time.


-Original Message-
From: sqlite-users  On Behalf Of 
Barry Smith
Sent: Monday, January 14, 2019 1:50 PM
To: SQLite mailing list 
Subject: Re: [sqlite] [EXTERNAL] SQLite error (5): database is locked

That's not how you set a busy timeout using a connection string. That's not how 
you set any (pragma) options with a connection string. Check the 
System.Data.SQLite documentation (or google) to find out connection string 
parameters, or play around with the SQLiteConnectionStringBuilder.

The busy timeout pragma will not help if you end up in a deadlock. One way a 
deadlock can occur is as follows:
 1) Connection A starts a transaction and reads the database - it takes out a 
shared lock
 2) Connection B starts a transaction and reads the database - it too takes out 
a shared lock
 3) Connection A attempts to write to the database, so it upgrades to a 
reserved lock and waits for all readers to close (all shared locks to be 
released).
 4) Connection B attempts to write to the database. It keeps its shared lock 
and tries to upgrade to reserved. Because A already owns a reserved lock, 
connection B is denied its lock. No amount of waiting will solve this problem 
because A is waiting for B to release its lock so it can have an exclusive 
lock. SQLite knows this, so it returns SQLITE_BUSY immediately.

https://www.sqlite.org/lockingv3.html

Note that the above is true for databases with an old style (non-WAL) journal. 
I assume similar protections and situations exist in WAL, but can't be certain 
and they may use a different mechanism.

You can figure out whether you have a deadlock or simply the wait timed out by 
looking at how quickly the error was returned. You can also look at where other 
threads (or processes if your debugger can attach to multiple processes) are 
when the error occurs.

Are you using explicit transactions? Entity Franework shouldn't cause deadlocks 
unless you are manually taking control of the transactions. EF can also take a 
very long time to SaveChanges if you have a large number of entities...

> On 14 Jan 2019, at 10:14 pm, Urs Wagner  wrote:
> 
> We are using entity framework
> 
> The timeout pragma does not work. Is think the timeout is not set, see below
> 
>var esb = new EntityConnectionStringBuilder
>{
>Metadata = 
> "res://*/RadaxModel.csdl|res://*/RadaxModel.ssdl|res://*/RadaxModel.msl",
>Provider = "System.Data.SQLite.EF6",
>ProviderConnectionString = @"data source=" + _dataBase + 
> ";PRAGMA foreign_keys = ON;PRAGMA locking_mode = EXCLUSIVE;PRAGMA 
> schema.synchronous = NORMAL; PRAGMA schema.journal_mode = DELETE; PRAGMA 
> busy_timeout = 10"
>};
> 
> 
> -Original Message-
> From: sqlite-users  On Behalf 
> Of Hick Gunter
> Sent: Monday, January 14, 2019 11:28 AM
> To: 'SQLite mailing list' 
> Subject: Re: [sqlite] [EXTERNAL] SQLite error (5): database is locked
> 
> With journal mode, SQLite supports 1 writer OR n readers; with WAL mode, 
> SQLite supports 1 writer AND N readers.
> 
> In any case, connections need to indicate if or how long they are willing to 
> wait for the db file to be unlocked. Default is NO.
> 
> The easiest way is to specify a timeout on the connection. The value needs to 
> be longer than your longest write transaction is expected to run and shorter 
> than the latency required by your application.
> 
> -Ursprüngliche Nachricht-
> Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
> Auftrag von Urs Wagner
> Gesendet: Montag, 14. Jänner 2019 10:24
> An: SQLite mailing list 
> Betreff: [EXTERNAL] [sqlite] SQLite error (5): database is locked
> 
> Hallo
> 
> I use several tasks in C# to call Sqlite queries.
> No I get the error SQLite error (5): database is locked.
> Is it not possible to use more than one tasks with Sqlite?
> 
> Regards
> 
> Urs
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
> 
> ___
> Gunter Hick | Software Engineer | Scientific Games International GmbH | 
> Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) 
> +43 1 80100 - 0
> 
> May be privileged. May be confidential. Please delete if not the addressee.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

Re: [sqlite] [EXTERNAL] SQLite error (5): database is locked

[Barry Smith]

That's not how you set a busy timeout using a connection string. That's not how 
you set any (pragma) options with a connection string. Check the 
System.Data.SQLite documentation (or google) to find out connection string 
parameters, or play around with the SQLiteConnectionStringBuilder.

The busy timeout pragma will not help if you end up in a deadlock. One way a 
deadlock can occur is as follows:
 1) Connection A starts a transaction and reads the database - it takes out a 
shared lock
 2) Connection B starts a transaction and reads the database - it too takes out 
a shared lock
 3) Connection A attempts to write to the database, so it upgrades to a 
reserved lock and waits for all readers to close (all shared locks to be 
released).
 4) Connection B attempts to write to the database. It keeps its shared lock 
and tries to upgrade to reserved. Because A already owns a reserved lock, 
connection B is denied its lock. No amount of waiting will solve this problem 
because A is waiting for B to release its lock so it can have an exclusive 
lock. SQLite knows this, so it returns SQLITE_BUSY immediately.

https://www.sqlite.org/lockingv3.html

Note that the above is true for databases with an old style (non-WAL) journal. 
I assume similar protections and situations exist in WAL, but can't be certain 
and they may use a different mechanism.

You can figure out whether you have a deadlock or simply the wait timed out by 
looking at how quickly the error was returned. You can also look at where other 
threads (or processes if your debugger can attach to multiple processes) are 
when the error occurs.

Are you using explicit transactions? Entity Franework shouldn't cause deadlocks 
unless you are manually taking control of the transactions. EF can also take a 
very long time to SaveChanges if you have a large number of entities...

> On 14 Jan 2019, at 10:14 pm, Urs Wagner  wrote:
> 
> We are using entity framework
> 
> The timeout pragma does not work. Is think the timeout is not set, see below
> 
>var esb = new EntityConnectionStringBuilder
>{
>Metadata = 
> "res://*/RadaxModel.csdl|res://*/RadaxModel.ssdl|res://*/RadaxModel.msl",
>Provider = "System.Data.SQLite.EF6",
>ProviderConnectionString = @"data source=" + _dataBase + 
> ";PRAGMA foreign_keys = ON;PRAGMA locking_mode = EXCLUSIVE;PRAGMA 
> schema.synchronous = NORMAL; PRAGMA schema.journal_mode = DELETE; PRAGMA 
> busy_timeout = 10"
>};
> 
> 
> -Original Message-
> From: sqlite-users  On Behalf 
> Of Hick Gunter
> Sent: Monday, January 14, 2019 11:28 AM
> To: 'SQLite mailing list' 
> Subject: Re: [sqlite] [EXTERNAL] SQLite error (5): database is locked
> 
> With journal mode, SQLite supports 1 writer OR n readers; with WAL mode, 
> SQLite supports 1 writer AND N readers.
> 
> In any case, connections need to indicate if or how long they are willing to 
> wait for the db file to be unlocked. Default is NO.
> 
> The easiest way is to specify a timeout on the connection. The value needs to 
> be longer than your longest write transaction is expected to run and shorter 
> than the latency required by your application.
> 
> -Ursprüngliche Nachricht-
> Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
> Auftrag von Urs Wagner
> Gesendet: Montag, 14. Jänner 2019 10:24
> An: SQLite mailing list 
> Betreff: [EXTERNAL] [sqlite] SQLite error (5): database is locked
> 
> Hallo
> 
> I use several tasks in C# to call Sqlite queries.
> No I get the error SQLite error (5): database is locked.
> Is it not possible to use more than one tasks with Sqlite?
> 
> Regards
> 
> Urs
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
> 
> ___
> Gunter Hick | Software Engineer | Scientific Games International GmbH | 
> Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) 
> +43 1 80100 - 0
> 
> May be privileged. May be confidential. Please delete if not the addressee.
> ___
> 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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] SQLite error (5): database is locked

[Hick Gunter]

Please note the the semicolon at the end of an SQL Statement is required. 
"PRAGMA busy_timeout = 100" is not complete SQL.
Also, the schema prefix needs to be replaced with the attach name of the 
database whose properties you wish to query/change. It may be omitted if "main" 
is desired.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Urs Wagner
Gesendet: Montag, 14. Jänner 2019 12:14
An: SQLite mailing list 
Betreff: Re: [sqlite] [EXTERNAL] SQLite error (5): database is locked

We are using entity framework

The timeout pragma does not work. Is think the timeout is not set, see below

var esb = new EntityConnectionStringBuilder
{
Metadata = 
"res://*/RadaxModel.csdl|res://*/RadaxModel.ssdl|res://*/RadaxModel.msl",
Provider = "System.Data.SQLite.EF6",
ProviderConnectionString = @"data source=" + _dataBase + 
";PRAGMA foreign_keys = ON;PRAGMA locking_mode = EXCLUSIVE;PRAGMA 
schema.synchronous = NORMAL; PRAGMA schema.journal_mode = DELETE; PRAGMA 
busy_timeout = 10"
};


-Original Message-
From: sqlite-users  On Behalf Of 
Hick Gunter
Sent: Monday, January 14, 2019 11:28 AM
To: 'SQLite mailing list' 
Subject: Re: [sqlite] [EXTERNAL] SQLite error (5): database is locked

With journal mode, SQLite supports 1 writer OR n readers; with WAL mode, SQLite 
supports 1 writer AND N readers.

In any case, connections need to indicate if or how long they are willing to 
wait for the db file to be unlocked. Default is NO.

The easiest way is to specify a timeout on the connection. The value needs to 
be longer than your longest write transaction is expected to run and shorter 
than the latency required by your application.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Urs Wagner
Gesendet: Montag, 14. Jänner 2019 10:24
An: SQLite mailing list 
Betreff: [EXTERNAL] [sqlite] SQLite error (5): database is locked

Hallo

I use several tasks in C# to call Sqlite queries.
No I get the error SQLite error (5): database is locked.
Is it not possible to use more than one tasks with Sqlite?

Regards

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


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
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


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] SQLite error (5): database is locked

[Urs Wagner]

We are using entity framework

The timeout pragma does not work. Is think the timeout is not set, see below

var esb = new EntityConnectionStringBuilder
{
Metadata = 
"res://*/RadaxModel.csdl|res://*/RadaxModel.ssdl|res://*/RadaxModel.msl",
Provider = "System.Data.SQLite.EF6",
ProviderConnectionString = @"data source=" + _dataBase + 
";PRAGMA foreign_keys = ON;PRAGMA locking_mode = EXCLUSIVE;PRAGMA 
schema.synchronous = NORMAL; PRAGMA schema.journal_mode = DELETE; PRAGMA 
busy_timeout = 10"
};


-Original Message-
From: sqlite-users  On Behalf Of 
Hick Gunter
Sent: Monday, January 14, 2019 11:28 AM
To: 'SQLite mailing list' 
Subject: Re: [sqlite] [EXTERNAL] SQLite error (5): database is locked

With journal mode, SQLite supports 1 writer OR n readers; with WAL mode, SQLite 
supports 1 writer AND N readers.

In any case, connections need to indicate if or how long they are willing to 
wait for the db file to be unlocked. Default is NO.

The easiest way is to specify a timeout on the connection. The value needs to 
be longer than your longest write transaction is expected to run and shorter 
than the latency required by your application.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Urs Wagner
Gesendet: Montag, 14. Jänner 2019 10:24
An: SQLite mailing list 
Betreff: [EXTERNAL] [sqlite] SQLite error (5): database is locked

Hallo

I use several tasks in C# to call Sqlite queries.
No I get the error SQLite error (5): database is locked.
Is it not possible to use more than one tasks with Sqlite?

Regards

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


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
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] [EXTERNAL] SQLite error (5): database is locked

[Hick Gunter]

With journal mode, SQLite supports 1 writer OR n readers; with WAL mode, SQLite 
supports 1 writer AND N readers.

In any case, connections need to indicate if or how long they are willing to 
wait for the db file to be unlocked. Default is NO.

The easiest way is to specify a timeout on the connection. The value needs to 
be longer than your longest write transaction is expected to run and shorter 
than the latency required by your application.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Urs Wagner
Gesendet: Montag, 14. Jänner 2019 10:24
An: SQLite mailing list 
Betreff: [EXTERNAL] [sqlite] SQLite error (5): database is locked

Hallo

I use several tasks in C# to call Sqlite queries.
No I get the error SQLite error (5): database is locked.
Is it not possible to use more than one tasks with Sqlite?

Regards

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


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite error (5): database is locked

[Simon Slavin]

On 14 Jan 2019, at 9:23am, Urs Wagner  wrote:

> I use several tasks in C# to call Sqlite queries.
> No I get the error SQLite error (5): database is locked.
> Is it not possible to use more than one tasks with Sqlite?

Please set a timeout for all connections to your database:

<https://sqlite.org/c3ref/busy_timeout.html>

<https://sqlite.org/pragma.html#pragma_busy_timeout>

A good timeout value is at least 1 milliseconds.

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

[sqlite] SQLite error (5): database is locked

[Urs Wagner]

Hallo

I use several tasks in C# to call Sqlite queries.
No I get the error SQLite error (5): database is locked.
Is it not possible to use more than one tasks with Sqlite?

Regards

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

Re: [sqlite] Error: dlsym(0x7fa073e02c60, sqlite3_sqliteicu_init): symbol not found

[邱朗]

Hi,

Thanks for look into it. I checked its readme 
https://www.sqlite.org/cgi/src/dir?ci=6cb537bdce85e088&name=ext/icu

It does not mention tokenizer at all so I guess you are right, it probably does 
not support that at all.
Qiulang


At 2018-10-18 17:58:38, "Keith Medcalf"  wrote:
>
>The ICU extension (as in icu.c) does not contain the tokenizer ... it only 
>contains the extension functions ... (upper / lower / etc)
>
>Looking in the amalgamation it appears that the icu tokenizer module is 
>defined in fts3_icu.c
>
>I have no idea how you load a tokenizer module as part of a loadable 
>extension, or even if that is supported at all (or specifically in the case of 
>icu).
>
>Generally speaking I think you just compile the amalgamation (or the full 
>source tree) with SQLITE_ENABLE_ICU defined to use icu.
>
>---
>The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
>lot about anticipated traffic volume.
>
>>-Original Message-
>>From: sqlite-users [mailto:sqlite-users-
>>boun...@mailinglists.sqlite.org] On Behalf Of ??
>>Sent: Thursday, 18 October, 2018 02:56
>>To: SQLite mailing list
>>Subject: Re: [sqlite] Error: dlsym(0x7fa073e02c60,
>>sqlite3_sqliteicu_init): symbol not found
>>
>>Hi,
>>
>>Thanks for your reply. I change module name to libicu.so as you said
>>then got Error: unknown tokenizer: icu
>>
>>
>>sqlite> .load libicu.so
>>sqlite> CREATE VIRTUAL TABLE zh_text USING fts4(text, tokenize=icu
>>zh_CN);
>>Error: unknown tokenizer: icu
>>
>>
>>Why is that ? Is the whole point to build icu extension to get the
>>icu tokenizer ?
>>
>>
>>BTW I have build an icu sqlite version from amalgamation but this
>>time I need to icu extension only.
>>
>>
>>Thanks
>>
>>
>>Qiulang
>>
>>At 2018-10-18 12:48:51, "Keith Medcalf"  wrote:
>>>
>>>The symbol name is sqlite3_icu_init.  When you load module lib.so
>>the symbol sqlite3__init is called.  You need to either (a) rename
>>the shared library to the correct name (libicu.so) or pass the name
>>of the init function (sqlite3_icu_init) to the loader when you load
>>the module, or (c) change the name of the sqlite3_icu_init function
>>in the icu.c source so that it matches the name that the module
>>loader is looking for ...
>>>
>>>(If you are building your own library from the amalgamation source
>>you can just compile the amalgamation with the SQLITE_ENABLE_ICU
>>symbol defined ...)
>>>
>>>---
>>>The fact that there's a Highway to Hell but only a Stairway to
>>Heaven says a lot about anticipated traffic volume.
>>>
>>>>-Original Message-
>>>>From: sqlite-users [mailto:sqlite-users-
>>>>boun...@mailinglists.sqlite.org] On Behalf Of ??
>>>>Sent: Wednesday, 17 October, 2018 22:26
>>>>To: sqlite-users@mailinglists.sqlite.org
>>>>Subject: [sqlite] Error: dlsym(0x7fa073e02c60,
>>>>sqlite3_sqliteicu_init): symbol not found
>>>>
>>>>I had thought I succeeded in building ICU extension because running
>>>>gcc -shared icu.c `icu-config --ldflags` -o libSqliteIcu.so
>>succeeded
>>>>and libSqliteIcu.so was generated.
>>>>But when I load it I got error Error: dlsym(0x7fa073e02c60,
>>>>sqlite3_sqliteicu_init): symbol not found
>>>>
>>>>
>>>>How do I solve that ? I am using mac 10.13 & sqlite 3.24 btw.
>>>>
>>>>
>>>>Thanks
>>>>
>>>>
>>>>Qiulang
>>>>___
>>>>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-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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Error: dlsym(0x7fa073e02c60, sqlite3_sqliteicu_init): symbol not found

[Keith Medcalf]

The ICU extension (as in icu.c) does not contain the tokenizer ... it only 
contains the extension functions ... (upper / lower / etc)

Looking in the amalgamation it appears that the icu tokenizer module is defined 
in fts3_icu.c

I have no idea how you load a tokenizer module as part of a loadable extension, 
or even if that is supported at all (or specifically in the case of icu).

Generally speaking I think you just compile the amalgamation (or the full 
source tree) with SQLITE_ENABLE_ICU defined to use icu.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of ??
>Sent: Thursday, 18 October, 2018 02:56
>To: SQLite mailing list
>Subject: Re: [sqlite] Error: dlsym(0x7fa073e02c60,
>sqlite3_sqliteicu_init): symbol not found
>
>Hi,
>
>Thanks for your reply. I change module name to libicu.so as you said
>then got Error: unknown tokenizer: icu
>
>
>sqlite> .load libicu.so
>sqlite> CREATE VIRTUAL TABLE zh_text USING fts4(text, tokenize=icu
>zh_CN);
>Error: unknown tokenizer: icu
>
>
>Why is that ? Is the whole point to build icu extension to get the
>icu tokenizer ?
>
>
>BTW I have build an icu sqlite version from amalgamation but this
>time I need to icu extension only.
>
>
>Thanks
>
>
>Qiulang
>
>At 2018-10-18 12:48:51, "Keith Medcalf"  wrote:
>>
>>The symbol name is sqlite3_icu_init.  When you load module lib.so
>the symbol sqlite3__init is called.  You need to either (a) rename
>the shared library to the correct name (libicu.so) or pass the name
>of the init function (sqlite3_icu_init) to the loader when you load
>the module, or (c) change the name of the sqlite3_icu_init function
>in the icu.c source so that it matches the name that the module
>loader is looking for ...
>>
>>(If you are building your own library from the amalgamation source
>you can just compile the amalgamation with the SQLITE_ENABLE_ICU
>symbol defined ...)
>>
>>---
>>The fact that there's a Highway to Hell but only a Stairway to
>Heaven says a lot about anticipated traffic volume.
>>
>>>-Original Message-----
>>>From: sqlite-users [mailto:sqlite-users-
>>>boun...@mailinglists.sqlite.org] On Behalf Of ??
>>>Sent: Wednesday, 17 October, 2018 22:26
>>>To: sqlite-users@mailinglists.sqlite.org
>>>Subject: [sqlite] Error: dlsym(0x7fa073e02c60,
>>>sqlite3_sqliteicu_init): symbol not found
>>>
>>>I had thought I succeeded in building ICU extension because running
>>>gcc -shared icu.c `icu-config --ldflags` -o libSqliteIcu.so
>succeeded
>>>and libSqliteIcu.so was generated.
>>>But when I load it I got error Error: dlsym(0x7fa073e02c60,
>>>sqlite3_sqliteicu_init): symbol not found
>>>
>>>
>>>How do I solve that ? I am using mac 10.13 & sqlite 3.24 btw.
>>>
>>>
>>>Thanks
>>>
>>>
>>>Qiulang
>>>___
>>>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-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] Error: dlsym(0x7fa073e02c60, sqlite3_sqliteicu_init): symbol not found

[邱朗]

Hi,

Thanks for your reply. I change module name to libicu.so as you said then got 
Error: unknown tokenizer: icu


sqlite> .load libicu.so
sqlite> CREATE VIRTUAL TABLE zh_text USING fts4(text, tokenize=icu zh_CN);
Error: unknown tokenizer: icu


Why is that ? Is the whole point to build icu extension to get the icu 
tokenizer ?  


BTW I have build an icu sqlite version from amalgamation but this time I need 
to icu extension only.


Thanks


Qiulang

At 2018-10-18 12:48:51, "Keith Medcalf"  wrote:
>
>The symbol name is sqlite3_icu_init.  When you load module lib.so the 
>symbol sqlite3__init is called.  You need to either (a) rename the shared 
>library to the correct name (libicu.so) or pass the name of the init function 
>(sqlite3_icu_init) to the loader when you load the module, or (c) change the 
>name of the sqlite3_icu_init function in the icu.c source so that it matches 
>the name that the module loader is looking for ...
>
>(If you are building your own library from the amalgamation source you can 
>just compile the amalgamation with the SQLITE_ENABLE_ICU symbol defined ...)
>
>---
>The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
>lot about anticipated traffic volume.
>
>>-Original Message-
>>From: sqlite-users [mailto:sqlite-users-
>>boun...@mailinglists.sqlite.org] On Behalf Of ??
>>Sent: Wednesday, 17 October, 2018 22:26
>>To: sqlite-users@mailinglists.sqlite.org
>>Subject: [sqlite] Error: dlsym(0x7fa073e02c60,
>>sqlite3_sqliteicu_init): symbol not found
>>
>>I had thought I succeeded in building ICU extension because running
>>gcc -shared icu.c `icu-config --ldflags` -o libSqliteIcu.so succeeded
>>and libSqliteIcu.so was generated.
>>But when I load it I got error Error: dlsym(0x7fa073e02c60,
>>sqlite3_sqliteicu_init): symbol not found
>>
>>
>>How do I solve that ? I am using mac 10.13 & sqlite 3.24 btw.
>>
>>
>>Thanks
>>
>>
>>Qiulang
>>___
>>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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Error: dlsym(0x7fa073e02c60, sqlite3_sqliteicu_init): symbol not found

[Keith Medcalf]

The symbol name is sqlite3_icu_init.  When you load module lib.so the symbol 
sqlite3__init is called.  You need to either (a) rename the shared library 
to the correct name (libicu.so) or pass the name of the init function 
(sqlite3_icu_init) to the loader when you load the module, or (c) change the 
name of the sqlite3_icu_init function in the icu.c source so that it matches 
the name that the module loader is looking for ...

(If you are building your own library from the amalgamation source you can just 
compile the amalgamation with the SQLITE_ENABLE_ICU symbol defined ...)

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of ??
>Sent: Wednesday, 17 October, 2018 22:26
>To: sqlite-users@mailinglists.sqlite.org
>Subject: [sqlite] Error: dlsym(0x7fa073e02c60,
>sqlite3_sqliteicu_init): symbol not found
>
>I had thought I succeeded in building ICU extension because running
>gcc -shared icu.c `icu-config --ldflags` -o libSqliteIcu.so succeeded
>and libSqliteIcu.so was generated.
>But when I load it I got error Error: dlsym(0x7fa073e02c60,
>sqlite3_sqliteicu_init): symbol not found
>
>
>How do I solve that ? I am using mac 10.13 & sqlite 3.24 btw.
>
>
>Thanks
>
>
>Qiulang
>___
>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] Error: dlsym(0x7fa073e02c60, sqlite3_sqliteicu_init): symbol not found

[邱朗]

I had thought I succeeded in building ICU extension because running gcc -shared 
icu.c `icu-config --ldflags` -o libSqliteIcu.so succeeded and libSqliteIcu.so 
was generated.
But when I load it I got error Error: dlsym(0x7fa073e02c60, 
sqlite3_sqliteicu_init): symbol not found


How do I solve that ? I am using mac 10.13 & sqlite 3.24 btw.


Thanks


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

Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is locked

[Chris Locke]

> For the record, "delete the journal file" is terrible advice

Agreed.  In normal production environment, I wouldn't suggest that.  The
user was testing a database, and in my own developemtn cycle, its common
when developing for a database to be in all manners of chaos states.  It
was purely a 'gotcha' that has caught me out before - a journal file
lingers and locks the system.


On Mon, Oct 15, 2018 at 4:03 AM Rowan Worth  wrote:

> On Sat, 13 Oct 2018 at 00:21, Chris Locke 
> wrote:
>
> > > Database is locked
> >
> > Close your application.  Is there a xxx-journal file in the same
> directory
> > as the database? (where xxx is the name of the database)
> > Try deleting this file.
> >
>
> For the record, "delete the journal file" is terrible advice and a great
> way to corrupt a database. In the case where a program crashes
> mid-transaction, the journal contains information which is crucial for
> recovering to a correct database state. And in non-crash scenarios, the
> journal should be cleaned up¹. So when you can see a journal file it's
> likely that either:
>
> 1. some program is currently using the DB, or
> 2. there was a crash mid-transaction
>
> Either way, deleting the journal is a wrong move.
>
> ¹ unless the DB is configured with PRAGMA journal_mode set to TRUNCATE or
> PERSIST, in which case you've asked for the rollback journal to linger
> around.
>
> -Rowan
> ___
> 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] error message: [FireDAC][Phys][SQLite] Database is locked

[R Smith]

On 2018/10/15 5:05 AM, Rowan Worth wrote:

On Sat, 13 Oct 2018 at 02:20, Lars Frederiksen  wrote:


Any ideas?


Not sure if this got resolved off-list, but based on the code you've posted
I wonder if there is an "fdqGloser" as well as an "fdqGloser2", and if so
what state it is in.


Update: This was indeed resolved off-list.
Thanks for the added thoughts.

The problem turned out to be related to FireDAC automagic and moving 
some DB connecting/opening code out of the form's OnCreate and into a 
more sensible event call did the trick.


SQLite was innocent. :)


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

Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is locked

[Rowan Worth]

On Sat, 13 Oct 2018 at 02:20, Lars Frederiksen  wrote:

> I type info into some labeledits and by pressing "Append to DB" button
> this code is executed (fdqGoser2 is a FDQuery)
>
>   fdqGloser2.Open;
>   fdqGloser2.Append;
>   fdqGloser2.FieldByName('Graesk').AsString:= ledGræsk.Text;
> ...
>   fdqGloser2.FieldByName('Graesk_ren').AsString:=
> Trykløs_SmåBogstaver(fdqGloser2.FieldByName('Graesk').AsString);
>   fdqGloser2.Post; [ here the error is triggered]
>
> I wonder if some kind of property i fdqGloser2 is set wrong. I think the
> only thing I have set i SQL ('SELECT * FROM Gloser2').
>
> Any ideas?
>

Not sure if this got resolved off-list, but based on the code you've posted
I wonder if there is an "fdqGloser" as well as an "fdqGloser2", and if so
what state it is in.

It reads like you're trying to update the database with fdqGloser2 -
another reason this would fail with "database is locked" is if you're
holding a separate read transaction open.

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

Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is locked

[Rowan Worth]

On Sat, 13 Oct 2018 at 00:21, Chris Locke  wrote:

> > Database is locked
>
> Close your application.  Is there a xxx-journal file in the same directory
> as the database? (where xxx is the name of the database)
> Try deleting this file.
>

For the record, "delete the journal file" is terrible advice and a great
way to corrupt a database. In the case where a program crashes
mid-transaction, the journal contains information which is crucial for
recovering to a correct database state. And in non-crash scenarios, the
journal should be cleaned up¹. So when you can see a journal file it's
likely that either:

1. some program is currently using the DB, or
2. there was a crash mid-transaction

Either way, deleting the journal is a wrong move.

¹ unless the DB is configured with PRAGMA journal_mode set to TRUNCATE or
PERSIST, in which case you've asked for the rollback journal to linger
around.

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

Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is locked

[R Smith]

On 2018/10/12 8:19 PM, Lars Frederiksen wrote:

I type info into some labeledits and by pressing "Append to DB" button this 
code is executed (fdqGoser2 is a FDQuery)

   fdqGloser2.Open;
   fdqGloser2.Append;
   fdqGloser2.FieldByName('Graesk').AsString:= ledGræsk.Text;
   fdqGloser2.FieldByName('Dansk').AsString:= ledDansk.Text;
   fdqGloser2.FieldByName('Lektion').AsString:= ledLektion.Text;
   fdqGloser2.FieldByName('Note').AsString:= ledNote.Text;
   fdqGloser2.FieldByName('Kategori').AsString:= ledKategori.Text;
   fdqGloser2.FieldByName('Langenscheidt').AsString:= ledLangenscheidt.Text;
   fdqGloser2.FieldByName('Ordtype').AsString:= ledOrdtype.Text;
   fdqGloser2.FieldByName('Boejning').AsString:= ledBøjning.Text;
   fdqGloser2.FieldByName('Graesk_ren').AsString:= 
Trykløs_SmåBogstaver(fdqGloser2.FieldByName('Graesk').AsString);
   fdqGloser2.Post; [ here the error is triggered]

I wonder if some kind of property i fdqGloser2 is set wrong. I think the only 
thing I have set i SQL ('SELECT * FROM Gloser2').

Any ideas?


This is a kind of novice way to do it, but should work fine (I'm not 
judging, it's just that I'm not 100% sure if Append starts a transaction 
or not, or if there is another command to follow Append after the field 
values are set, like AddRecord or CloseRecord or some such, before Post 
can be called - Prefer doing these lower down and have some control over 
it, but happy to do a little trouble-shooting).


What is fdqGloser2?    I mean what is its Delphi Type? (T..something... )

I'm guessing fdq is for Fire-DAC-Query, which makes it seem like this is 
from some tutorial.


Actually, this question is veering further and further away from an 
SQLite question - perhaps we should not spam everyone and move off-list 
(along with whomever wish to join).
Send me off-list the entire Unit perhaps and the DB file (at least the 
schema if the content is sensitive), then I can have a look at what goes 
wrong.
If someone else is interested in contributing to this, send me a mail 
too to include in the reply chain.




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

Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is locked

[Lars Frederiksen]

I type info into some labeledits and by pressing "Append to DB" button this 
code is executed (fdqGoser2 is a FDQuery)

  fdqGloser2.Open;
  fdqGloser2.Append;
  fdqGloser2.FieldByName('Graesk').AsString:= ledGræsk.Text;
  fdqGloser2.FieldByName('Dansk').AsString:= ledDansk.Text;
  fdqGloser2.FieldByName('Lektion').AsString:= ledLektion.Text;
  fdqGloser2.FieldByName('Note').AsString:= ledNote.Text;
  fdqGloser2.FieldByName('Kategori').AsString:= ledKategori.Text;
  fdqGloser2.FieldByName('Langenscheidt').AsString:= ledLangenscheidt.Text;
  fdqGloser2.FieldByName('Ordtype').AsString:= ledOrdtype.Text;
  fdqGloser2.FieldByName('Boejning').AsString:= ledBøjning.Text;
  fdqGloser2.FieldByName('Graesk_ren').AsString:= 
Trykløs_SmåBogstaver(fdqGloser2.FieldByName('Graesk').AsString);
  fdqGloser2.Post; [ here the error is triggered]

I wonder if some kind of property i fdqGloser2 is set wrong. I think the only 
thing I have set i SQL ('SELECT * FROM Gloser2').

Any ideas?

/Lars

-Oprindelig meddelelse-
Fra: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] På 
vegne af Stephen Chrzanowski
Sendt: 12. oktober 2018 19:47
Til: General Discussion of SQLite Database
Emne: Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is locked

FSCK is File System ChecK, so the equivalent in Windows Scandisk.

Trace through the code to find out when exactly the SQLite is reporting the
lock.  Is it at a query, on opening, is it multi-thread or
multi-application access?  Is the query taking too long?  If you're
debugging and everything is working through the transactions, I'd look at
how you're setting the timeout and maybe it isn't being passed through
correctly.  As mentioned, depending on the size of the DB, your AV may be
holding on and doing a scan of the file that is much longer than the 5
seconds.  Set breakpoints at every point where you do something with SQL,
then first step-over the break point to validate that specific function
isn't causing the lock, then resume until the next break point.  When/if
you do hit a break point that causes a lock, restart the app, then
step-into the function and see where the lock is happening.

Transactions may also cause some grief, but I defer to those with more
experience on that.  I know that there are blocks in play for writes, not
100% sure if you open a transaction if you'll get a locked message from
another call.

Another thing you could do is, again depending on the size of your database
and memory constraints, is "backup" the database to memory, if you're only
using a single-thread & single-application, this should be valid.

For a short bit, turn off all your AV software, or, get an AV free OS up
and running to test your application on.  Disconnect your Ethernet if
you're truly paranoid about getting a rogue application hitting your
machine while you've got AV off.  (Been there with Melissa Virus)
Another option is start up a new application and just have it do some data
massaging on a scrap database.  SQLite Expert I think has a demo DB you can
play with, or, just start from scratch with a single file, single table
database.  If your new application can do work without your AV having a
problem, then I'd be looking at what exactly your code is doing.  I'm sure
there's a SQLite3 Northwind database here:
https://github.com/jpwhite3/northwind-SQLite3

I use the free version of Berlin and Tokyo, and I've never had a lock issue
that couldn't be explained by another process taking too long with the
data.  I typically write single-threaded applications, but sometimes there
are multiple applications touching the database.  (IE: SQLite Expert;
sqlite3.exe, my applications, etc).  I've only ever run into database
locked issues is when my app is writing the data pretty quickly to the DB
and SQLite Expert runs a query.  I'll get a timeout somewhere, but its
easily explained.  Expanding the timeout on both applications usually fixes
the locked issue.

On Fri, Oct 12, 2018 at 1:20 PM Lars Frederiksen  wrote:

> Thank you for your response. To answer your questions:
>


___
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] error message: [FireDAC][Phys][SQLite] Database is locked

[Keith Medcalf]

This would seem to imply that BitDefender is not the cause of your woes.  Do 
you have write permission on the database file?


---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Lars Frederiksen
>Sent: Friday, 12 October, 2018 06:26
>To: 'SQLite mailing list'
>Subject: Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database
>is locked
>
>That is: I have disabled ALL protecting parameters in BitDefender to
>find the culprit module - without luck.
>
>-Oprindelig meddelelse-
>Fra: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] På vegne af Radovan Antloga
>Sendt: 12. oktober 2018 10:10
>Til: sqlite-users@mailinglists.sqlite.org
>Emne: Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is
>locked
>
>Try to change database name (extension).
>
>For example:
>mydb.sqlite
>mydb.sdb
>mydb.db3
>
>I would use .sqlite extension and exclude that
>files from antivirus check.
>
>Regards
>
>Lars Frederiksen je 12.10.2018 ob 9:11 napisal:
>> Hello,
>>
>> I have made (in Delphi) a program that writes to a sqlite database.
>This is
>> a simple stand-alone solution. Now something is blocking for the
>programs
>> attempt to writing to the databse. There is a an error message:
>> [FireDAC][Phys][SQLite] Database is locked.
>>
>> A friend of mine has analysed the scenario and tells me that
>BitDefender is
>> the Problem.  In BitDefender "Application Access" I have given my
>program
>> permission to "Work".
>>
>> Any solution on how to "tell" BitDefender that it is OK to make
>changes in
>> my database? (I have tried BitDefender support - but no ticket
>answer!)
>>
>> Maybe also somebody knows an antivirus/anti-malware solution which
>are not
>> so "hard" as BitDefender? (and with a userfriendly GUI).
>>
>> Thank you so much for any help. I am rather "desperate" for some
>help!
>>
>> Best Regards
>>
>> Lars
>>
>>
>>
>>
>>
>> ___
>> 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-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] error message: [FireDAC][Phys][SQLite] Database is locked

[Stephen Chrzanowski]

FSCK is File System ChecK, so the equivalent in Windows Scandisk.

Trace through the code to find out when exactly the SQLite is reporting the
lock.  Is it at a query, on opening, is it multi-thread or
multi-application access?  Is the query taking too long?  If you're
debugging and everything is working through the transactions, I'd look at
how you're setting the timeout and maybe it isn't being passed through
correctly.  As mentioned, depending on the size of the DB, your AV may be
holding on and doing a scan of the file that is much longer than the 5
seconds.  Set breakpoints at every point where you do something with SQL,
then first step-over the break point to validate that specific function
isn't causing the lock, then resume until the next break point.  When/if
you do hit a break point that causes a lock, restart the app, then
step-into the function and see where the lock is happening.

Transactions may also cause some grief, but I defer to those with more
experience on that.  I know that there are blocks in play for writes, not
100% sure if you open a transaction if you'll get a locked message from
another call.

Another thing you could do is, again depending on the size of your database
and memory constraints, is "backup" the database to memory, if you're only
using a single-thread & single-application, this should be valid.

For a short bit, turn off all your AV software, or, get an AV free OS up
and running to test your application on.  Disconnect your Ethernet if
you're truly paranoid about getting a rogue application hitting your
machine while you've got AV off.  (Been there with Melissa Virus)
Another option is start up a new application and just have it do some data
massaging on a scrap database.  SQLite Expert I think has a demo DB you can
play with, or, just start from scratch with a single file, single table
database.  If your new application can do work without your AV having a
problem, then I'd be looking at what exactly your code is doing.  I'm sure
there's a SQLite3 Northwind database here:
https://github.com/jpwhite3/northwind-SQLite3

I use the free version of Berlin and Tokyo, and I've never had a lock issue
that couldn't be explained by another process taking too long with the
data.  I typically write single-threaded applications, but sometimes there
are multiple applications touching the database.  (IE: SQLite Expert;
sqlite3.exe, my applications, etc).  I've only ever run into database
locked issues is when my app is writing the data pretty quickly to the DB
and SQLite Expert runs a query.  I'll get a timeout somewhere, but its
easily explained.  Expanding the timeout on both applications usually fixes
the locked issue.

On Fri, Oct 12, 2018 at 1:20 PM Lars Frederiksen  wrote:

> Thank you for your response. To answer your questions:
>


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

Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is locked

[Lars Frederiksen]

Thank you for your response. To answer your questions:

Ryan: 
It's sad to see someone get dismayed with what is usually a flawless working 
system. It's not the database that's tricky, it's the reason the file is 
locked. This would be a problem with a string based file too.
- I agree Ryan, but I get SO totally frustrated [and locked myself :-)] not 
being able to go on with my favorite hobby!!!

Do you have it open in any DB manager or other CLI or such?
- I have just tried SQLite Manager and in fact I am able to edit in the db 
through this program

What's the actual path of the folder containing the file on your drive?
- My database (called flashcard.db) is placed in a subfolder to %programdata% 
(c:\programdata\my name\flashcard_data

This leaves other possible culprits as Windows Virtualization 
- Personally I do not know much about Windows Virtualization. I will try a 
search!!
and possible other programs/processes on your machine locking the DB file.
- I am totally empty about what kind of program that might be??

Thomas:
Could the problem arise due to filesystem corruption? Have you tried an fsck?
- no I have not tried an fsck [is this not a Linux prg that checks the files?]

Chris:
Close your application.  
Is there a xxx-journal file in the same directory as the database? (where xxx 
is the name of the database) Try deleting this file.
- there is no such file in the database directory


Best Regards
Lars



-Oprindelig meddelelse-
Fra: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] På 
vegne af Chris Locke
Sendt: 12. oktober 2018 18:22
Til: SQLite mailing list
Emne: Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is locked

> Database is locked

Close your application.  Is there a xxx-journal file in the same directory
as the database? (where xxx is the name of the database)
Try deleting this file.


Thanks,
Chris



On Fri, Oct 12, 2018 at 4:54 PM Thomas Kurz  wrote:
>
>> Could the problem arise due to filesystem corruption? Have you tried an
>> fsck?
>>
>>
>> - Original Message -
>> From: R Smith 
>> To: sqlite-users@mailinglists.sqlite.org <
>> sqlite-users@mailinglists.sqlite.org>
>> Sent: Friday, October 12, 2018, 17:35:28
>> Subject: [sqlite] error message: [FireDAC][Phys][SQLite] Database is
>> locked
>>
>> On 2018/10/12 2:44 PM, Lars Frederiksen wrote:
>> > I have tried that too! - I must admit that right now I am turning to a
>> KISS solution: String-based database with functions and procedures in a
>> class that handles the different jobs you do on a table. After all these
>> old-fashioned DB's are not so picky :-)
>>
>> It's sad to see someone get dismayed with what is usually a flawless
>> working system.
>> It's not the database that's tricky, it's the reason the file is locked.
>> This would be a problem with a string based file too.
>>
>> Reading the above, I'm no longer convinced it's your anti-virus' fault
>> either.
>>
>> Using Delphi FireDAC quite a bit, I can promise you that its SQLite
>> connectivity is working just fine, and while an Antivirus can lock a
>> file for the time it takes to check it, unless your database is
>> Gigabytes in size, that lock should be released within milliseconds,
>> unless found to be unsafe - but then the log should speak of it (which I
>> assume it doesn't).
>>
>> This leaves other possible culprits as Windows Virtualization and
>> possible other programs/processes on your machine locking the DB file.
>> Do you have it open in any DB manager or other CLI or such?
>> What's the actual path of the folder containing the file on your drive?
>>
>>
>> Cheers,
>> Ryan
>>
>> ___
>> 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-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] error message: [FireDAC][Phys][SQLite] Database is locked

[Chris Locke]

> Database is locked

Close your application.  Is there a xxx-journal file in the same directory
as the database? (where xxx is the name of the database)
Try deleting this file.


Thanks,
Chris



On Fri, Oct 12, 2018 at 4:54 PM Thomas Kurz  wrote:
>
>> Could the problem arise due to filesystem corruption? Have you tried an
>> fsck?
>>
>>
>> - Original Message -
>> From: R Smith 
>> To: sqlite-users@mailinglists.sqlite.org <
>> sqlite-users@mailinglists.sqlite.org>
>> Sent: Friday, October 12, 2018, 17:35:28
>> Subject: [sqlite] error message: [FireDAC][Phys][SQLite] Database is
>> locked
>>
>> On 2018/10/12 2:44 PM, Lars Frederiksen wrote:
>> > I have tried that too! - I must admit that right now I am turning to a
>> KISS solution: String-based database with functions and procedures in a
>> class that handles the different jobs you do on a table. After all these
>> old-fashioned DB's are not so picky :-)
>>
>> It's sad to see someone get dismayed with what is usually a flawless
>> working system.
>> It's not the database that's tricky, it's the reason the file is locked.
>> This would be a problem with a string based file too.
>>
>> Reading the above, I'm no longer convinced it's your anti-virus' fault
>> either.
>>
>> Using Delphi FireDAC quite a bit, I can promise you that its SQLite
>> connectivity is working just fine, and while an Antivirus can lock a
>> file for the time it takes to check it, unless your database is
>> Gigabytes in size, that lock should be released within milliseconds,
>> unless found to be unsafe - but then the log should speak of it (which I
>> assume it doesn't).
>>
>> This leaves other possible culprits as Windows Virtualization and
>> possible other programs/processes on your machine locking the DB file.
>> Do you have it open in any DB manager or other CLI or such?
>> What's the actual path of the folder containing the file on your drive?
>>
>>
>> Cheers,
>> Ryan
>>
>> ___
>> 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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is locked

[Thomas Kurz]

Could the problem arise due to filesystem corruption? Have you tried an fsck?


- Original Message - 
From: R Smith 
To: sqlite-users@mailinglists.sqlite.org 
Sent: Friday, October 12, 2018, 17:35:28
Subject: [sqlite] error message: [FireDAC][Phys][SQLite] Database is locked

On 2018/10/12 2:44 PM, Lars Frederiksen wrote:
> I have tried that too! - I must admit that right now I am turning to a KISS 
> solution: String-based database with functions and procedures in a class that 
> handles the different jobs you do on a table. After all these old-fashioned 
> DB's are not so picky :-)

It's sad to see someone get dismayed with what is usually a flawless 
working system.
It's not the database that's tricky, it's the reason the file is locked. 
This would be a problem with a string based file too.

Reading the above, I'm no longer convinced it's your anti-virus' fault 
either.

Using Delphi FireDAC quite a bit, I can promise you that its SQLite 
connectivity is working just fine, and while an Antivirus can lock a 
file for the time it takes to check it, unless your database is 
Gigabytes in size, that lock should be released within milliseconds, 
unless found to be unsafe - but then the log should speak of it (which I 
assume it doesn't).

This leaves other possible culprits as Windows Virtualization and 
possible other programs/processes on your machine locking the DB file. 
Do you have it open in any DB manager or other CLI or such?
What's the actual path of the folder containing the file on your drive?


Cheers,
Ryan

___
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] error message: [FireDAC][Phys][SQLite] Database is locked

[R Smith]

On 2018/10/12 2:44 PM, Lars Frederiksen wrote:

I have tried that too! - I must admit that right now I am turning to a KISS 
solution: String-based database with functions and procedures in a class that 
handles the different jobs you do on a table. After all these old-fashioned 
DB's are not so picky :-)


It's sad to see someone get dismayed with what is usually a flawless 
working system.
It's not the database that's tricky, it's the reason the file is locked. 
This would be a problem with a string based file too.


Reading the above, I'm no longer convinced it's your anti-virus' fault 
either.


Using Delphi FireDAC quite a bit, I can promise you that its SQLite 
connectivity is working just fine, and while an Antivirus can lock a 
file for the time it takes to check it, unless your database is 
Gigabytes in size, that lock should be released within milliseconds, 
unless found to be unsafe - but then the log should speak of it (which I 
assume it doesn't).


This leaves other possible culprits as Windows Virtualization and 
possible other programs/processes on your machine locking the DB file. 
Do you have it open in any DB manager or other CLI or such?

What's the actual path of the folder containing the file on your drive?


Cheers,
Ryan

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

Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is locked

[Radovan Antloga]

Try this:

1. check file atribute if it is not read only
  maybe is not BitDefender

2. copy your database to some test database in
  same folder. Try to edit with simple editor
  and save that file to see if is possible.

R.A.

Lars Frederiksen je 12.10.2018 ob 14:23 napisal:

I have tried this, unfortunately without any luck.
I think the problem might be in the sqlite DB-system. I have disabled ALL 
protecting parameters to find the culprit module - without luck.
My sqlite version is: SQLite version 3.19.3 2017-06-08 14:26:16
There might be a newer and better version without this error?

/Lars


-Oprindelig meddelelse-
Fra: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] På 
vegne af Radovan Antloga
Sendt: 12. oktober 2018 10:10
Til: sqlite-users@mailinglists.sqlite.org
Emne: Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is locked

Try to change database name (extension).

For example:
mydb.sqlite
mydb.sdb
mydb.db3

I would use .sqlite extension and exclude that
files from antivirus check.

Regards

Lars Frederiksen je 12.10.2018 ob 9:11 napisal:

Hello,

I have made (in Delphi) a program that writes to a sqlite database. This is
a simple stand-alone solution. Now something is blocking for the programs
attempt to writing to the databse. There is a an error message:
[FireDAC][Phys][SQLite] Database is locked.

A friend of mine has analysed the scenario and tells me that BitDefender is
the Problem.  In BitDefender "Application Access" I have given my program
permission to "Work".

Any solution on how to "tell" BitDefender that it is OK to make changes in
my database? (I have tried BitDefender support - but no ticket answer!)

Maybe also somebody knows an antivirus/anti-malware solution which are not
so "hard" as BitDefender? (and with a userfriendly GUI).

Thank you so much for any help. I am rather "desperate" for some help!

Best Regards

Lars

   

   


___
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-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] error message: [FireDAC][Phys][SQLite] Database is locked

[Lars Frederiksen]

I have tried that too! - I must admit that right now I am turning to a KISS 
solution: String-based database with functions and procedures in a class that 
handles the different jobs you do on a table. After all these old-fashioned 
DB's are not so picky :-)

-Oprindelig meddelelse-
Fra: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] På 
vegne af Stephen Chrzanowski
Sendt: 12. oktober 2018 14:37
Til: General Discussion of SQLite Database
Emne: Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is locked

How about moving the databases elsewhere?  Sometimes applications are picky
when it comes where files are being written to (IE: Program Files).

On Fri, Oct 12, 2018 at 8:30 AM Lars Frederiksen  wrote:

> Setting the Busy Timeout (in FireDAC) to 5000 ms does not solve the
> problem - unfortunately!
>
> -Oprindelig meddelelse-
> Fra: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> På vegne af Stephen Chrzanowski
> Sendt: 12. oktober 2018 14:25
> Til: General Discussion of SQLite Database
> Emne: Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is
> locked
>
> Change the timeout value on the connection to 5 seconds or so.  The DB will
> retry.  The AV has its grip on it, doing a scan, and won't let anything
> else touch it until its done.
>
> On Fri, Oct 12, 2018 at 3:11 AM Lars Frederiksen 
> wrote:
>
> > Hello,
> >
> > I have made (in Delphi) a program that writes to a sqlite database. This
> is
> > a simple stand-alone solution. Now something is blocking for the programs
> > attempt to writing to the databse. There is a an error message:
> > [FireDAC][Phys][SQLite] Database is locked.
> >
> > A friend of mine has analysed the scenario and tells me that BitDefender
> is
> > the Problem.  In BitDefender "Application Access" I have given my program
> > permission to "Work".
> >
> > Any solution on how to "tell" BitDefender that it is OK to make changes
> in
> > my database? (I have tried BitDefender support - but no ticket answer!)
> >
> > Maybe also somebody knows an antivirus/anti-malware solution which are
> not
> > so "hard" as BitDefender? (and with a userfriendly GUI).
> >
> > Thank you so much for any help. I am rather "desperate" for some help!
> >
> > Best Regards
> >
> > Lars
> >
> >
> >
> >
> >
> > ___
> > 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-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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is locked

[Stephen Chrzanowski]

How about moving the databases elsewhere?  Sometimes applications are picky
when it comes where files are being written to (IE: Program Files).

On Fri, Oct 12, 2018 at 8:30 AM Lars Frederiksen  wrote:

> Setting the Busy Timeout (in FireDAC) to 5000 ms does not solve the
> problem - unfortunately!
>
> -Oprindelig meddelelse-
> Fra: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> På vegne af Stephen Chrzanowski
> Sendt: 12. oktober 2018 14:25
> Til: General Discussion of SQLite Database
> Emne: Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is
> locked
>
> Change the timeout value on the connection to 5 seconds or so.  The DB will
> retry.  The AV has its grip on it, doing a scan, and won't let anything
> else touch it until its done.
>
> On Fri, Oct 12, 2018 at 3:11 AM Lars Frederiksen 
> wrote:
>
> > Hello,
> >
> > I have made (in Delphi) a program that writes to a sqlite database. This
> is
> > a simple stand-alone solution. Now something is blocking for the programs
> > attempt to writing to the databse. There is a an error message:
> > [FireDAC][Phys][SQLite] Database is locked.
> >
> > A friend of mine has analysed the scenario and tells me that BitDefender
> is
> > the Problem.  In BitDefender "Application Access" I have given my program
> > permission to "Work".
> >
> > Any solution on how to "tell" BitDefender that it is OK to make changes
> in
> > my database? (I have tried BitDefender support - but no ticket answer!)
> >
> > Maybe also somebody knows an antivirus/anti-malware solution which are
> not
> > so "hard" as BitDefender? (and with a userfriendly GUI).
> >
> > Thank you so much for any help. I am rather "desperate" for some help!
> >
> > Best Regards
> >
> > Lars
> >
> >
> >
> >
> >
> > ___
> > 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-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] error message: [FireDAC][Phys][SQLite] Database is locked

[Lars Frederiksen]

Setting the Busy Timeout (in FireDAC) to 5000 ms does not solve the problem - 
unfortunately!

-Oprindelig meddelelse-
Fra: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] På 
vegne af Stephen Chrzanowski
Sendt: 12. oktober 2018 14:25
Til: General Discussion of SQLite Database
Emne: Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is locked

Change the timeout value on the connection to 5 seconds or so.  The DB will
retry.  The AV has its grip on it, doing a scan, and won't let anything
else touch it until its done.

On Fri, Oct 12, 2018 at 3:11 AM Lars Frederiksen  wrote:

> Hello,
>
> I have made (in Delphi) a program that writes to a sqlite database. This is
> a simple stand-alone solution. Now something is blocking for the programs
> attempt to writing to the databse. There is a an error message:
> [FireDAC][Phys][SQLite] Database is locked.
>
> A friend of mine has analysed the scenario and tells me that BitDefender is
> the Problem.  In BitDefender "Application Access" I have given my program
> permission to "Work".
>
> Any solution on how to "tell" BitDefender that it is OK to make changes in
> my database? (I have tried BitDefender support - but no ticket answer!)
>
> Maybe also somebody knows an antivirus/anti-malware solution which are not
> so "hard" as BitDefender? (and with a userfriendly GUI).
>
> Thank you so much for any help. I am rather "desperate" for some help!
>
> Best Regards
>
> Lars
>
>
>
>
>
> ___
> 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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is locked

[Lars Frederiksen]

That is: I have disabled ALL protecting parameters in BitDefender to find the 
culprit module - without luck.

-Oprindelig meddelelse-
Fra: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] På 
vegne af Radovan Antloga
Sendt: 12. oktober 2018 10:10
Til: sqlite-users@mailinglists.sqlite.org
Emne: Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is locked

Try to change database name (extension).

For example:
mydb.sqlite
mydb.sdb
mydb.db3

I would use .sqlite extension and exclude that
files from antivirus check.

Regards

Lars Frederiksen je 12.10.2018 ob 9:11 napisal:
> Hello,
>
> I have made (in Delphi) a program that writes to a sqlite database. This is
> a simple stand-alone solution. Now something is blocking for the programs
> attempt to writing to the databse. There is a an error message:
> [FireDAC][Phys][SQLite] Database is locked.
>
> A friend of mine has analysed the scenario and tells me that BitDefender is
> the Problem.  In BitDefender "Application Access" I have given my program
> permission to "Work".
>
> Any solution on how to "tell" BitDefender that it is OK to make changes in
> my database? (I have tried BitDefender support - but no ticket answer!)
>
> Maybe also somebody knows an antivirus/anti-malware solution which are not
> so "hard" as BitDefender? (and with a userfriendly GUI).
>
> Thank you so much for any help. I am rather "desperate" for some help!
>
> Best Regards
>
> Lars
>
>   
>
>   
>
> ___
> 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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is locked

[Stephen Chrzanowski]

Change the timeout value on the connection to 5 seconds or so.  The DB will
retry.  The AV has its grip on it, doing a scan, and won't let anything
else touch it until its done.

On Fri, Oct 12, 2018 at 3:11 AM Lars Frederiksen  wrote:

> Hello,
>
> I have made (in Delphi) a program that writes to a sqlite database. This is
> a simple stand-alone solution. Now something is blocking for the programs
> attempt to writing to the databse. There is a an error message:
> [FireDAC][Phys][SQLite] Database is locked.
>
> A friend of mine has analysed the scenario and tells me that BitDefender is
> the Problem.  In BitDefender "Application Access" I have given my program
> permission to "Work".
>
> Any solution on how to "tell" BitDefender that it is OK to make changes in
> my database? (I have tried BitDefender support - but no ticket answer!)
>
> Maybe also somebody knows an antivirus/anti-malware solution which are not
> so "hard" as BitDefender? (and with a userfriendly GUI).
>
> Thank you so much for any help. I am rather "desperate" for some help!
>
> Best Regards
>
> Lars
>
>
>
>
>
> ___
> 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] error message: [FireDAC][Phys][SQLite] Database is locked

[Lars Frederiksen]

I have tried this, unfortunately without any luck. 
I think the problem might be in the sqlite DB-system. I have disabled ALL 
protecting parameters to find the culprit module - without luck.
My sqlite version is: SQLite version 3.19.3 2017-06-08 14:26:16
There might be a newer and better version without this error?

/Lars


-Oprindelig meddelelse-
Fra: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] På 
vegne af Radovan Antloga
Sendt: 12. oktober 2018 10:10
Til: sqlite-users@mailinglists.sqlite.org
Emne: Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is locked

Try to change database name (extension).

For example:
mydb.sqlite
mydb.sdb
mydb.db3

I would use .sqlite extension and exclude that
files from antivirus check.

Regards

Lars Frederiksen je 12.10.2018 ob 9:11 napisal:
> Hello,
>
> I have made (in Delphi) a program that writes to a sqlite database. This is
> a simple stand-alone solution. Now something is blocking for the programs
> attempt to writing to the databse. There is a an error message:
> [FireDAC][Phys][SQLite] Database is locked.
>
> A friend of mine has analysed the scenario and tells me that BitDefender is
> the Problem.  In BitDefender "Application Access" I have given my program
> permission to "Work".
>
> Any solution on how to "tell" BitDefender that it is OK to make changes in
> my database? (I have tried BitDefender support - but no ticket answer!)
>
> Maybe also somebody knows an antivirus/anti-malware solution which are not
> so "hard" as BitDefender? (and with a userfriendly GUI).
>
> Thank you so much for any help. I am rather "desperate" for some help!
>
> Best Regards
>
> Lars
>
>   
>
>   
>
> ___
> 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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is locked

[Radovan Antloga]

Try to change database name (extension).

For example:
mydb.sqlite
mydb.sdb
mydb.db3

I would use .sqlite extension and exclude that
files from antivirus check.

Regards

Lars Frederiksen je 12.10.2018 ob 9:11 napisal:

Hello,

I have made (in Delphi) a program that writes to a sqlite database. This is
a simple stand-alone solution. Now something is blocking for the programs
attempt to writing to the databse. There is a an error message:
[FireDAC][Phys][SQLite] Database is locked.

A friend of mine has analysed the scenario and tells me that BitDefender is
the Problem.  In BitDefender "Application Access" I have given my program
permission to "Work".

Any solution on how to "tell" BitDefender that it is OK to make changes in
my database? (I have tried BitDefender support - but no ticket answer!)

Maybe also somebody knows an antivirus/anti-malware solution which are not
so "hard" as BitDefender? (and with a userfriendly GUI).

Thank you so much for any help. I am rather "desperate" for some help!

Best Regards

Lars

  

  


___
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] error message: [FireDAC][Phys][SQLite] Database is locked

[Lars Frederiksen]

Hello, 

I have made (in Delphi) a program that writes to a sqlite database. This is
a simple stand-alone solution. Now something is blocking for the programs
attempt to writing to the databse. There is a an error message:
[FireDAC][Phys][SQLite] Database is locked. 

A friend of mine has analysed the scenario and tells me that BitDefender is
the Problem.  In BitDefender "Application Access" I have given my program
permission to "Work".  

Any solution on how to "tell" BitDefender that it is OK to make changes in
my database? (I have tried BitDefender support - but no ticket answer!)

Maybe also somebody knows an antivirus/anti-malware solution which are not
so "hard" as BitDefender? (and with a userfriendly GUI). 

Thank you so much for any help. I am rather "desperate" for some help!  

Best Regards 

Lars 

 

 

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

Re: [sqlite] Error when reading from pre-populated SQLite database in Ionic project

[Simon Slavin]

On 10 Sep 2018, at 12:30am, Robert Helmick  wrote:

> Thanks for your response. I'm using the node.js plugin. I've tried creating
> a table then closing the db, and the queries executed successfully, but I
> was not able to find the database file that it created. I searched the
> folder structure and was unable to find any .db file, even though the db
> was opened, a table was created, and the db was closed again.

Once you have found the file, you will understand where the default folder is.

Just as a test, after your program which creates the table and puts a row into 
it is finished, write another to check that the table you created is still 
there.  If it is, then the file definitely exists somewhere.

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

Re: [sqlite] Error when reading from pre-populated SQLite database in Ionic project

[Robert Helmick]

Hi Simon,

Thanks for your response. I'm using the node.js plugin. I've tried creating
a table then closing the db, and the queries executed successfully, but I
was not able to find the database file that it created. I searched the
folder structure and was unable to find any .db file, even though the db
was opened, a table was created, and the db was closed again.

Regards,
Robert

On Thu, Sep 6, 2018 at 12:32 PM Simon Slavin  wrote:

> On 6 Sep 2018, at 5:33pm, Robert Helmick 
> wrote:
>
> > by default, then creates an empty database when it doesn't find the
> > pre-populated mydb.db file. This is why it can't find the 'plant' table,
> > because the newly created blank database obviously doesn't contain a
> > 'plant' table. However I can confirm that the database *is* in the /www
> > folder, and that it contains the 'plant' table when I run `sqlite3
> mydb.db`
>
> What OS ?
> Are you using the C API or some other library ?
>
> If you write code to create a new database (open a filename, do at least a
> CREATE TABLE command, close the connection) what directory does it put the
> file in ?
>
> Simon.
> ___
> 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] Error when reading from pre-populated SQLite database in Ionic project

[Simon Slavin]

On 6 Sep 2018, at 5:33pm, Robert Helmick  wrote:

> by default, then creates an empty database when it doesn't find the
> pre-populated mydb.db file. This is why it can't find the 'plant' table,
> because the newly created blank database obviously doesn't contain a
> 'plant' table. However I can confirm that the database *is* in the /www
> folder, and that it contains the 'plant' table when I run `sqlite3 mydb.db`

What OS ?
Are you using the C API or some other library ?

If you write code to create a new database (open a filename, do at least a 
CREATE TABLE command, close the connection) what directory does it put the file 
in ?

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

[sqlite] Error when reading from pre-populated SQLite database in Ionic project

[Robert Helmick]

I'm receiving an error when I try to read from a pre-populated SQLite
database: `sqlite3_prepare_v2 failure: no such table 'plant'`

From what I understand SQLite looks for the mydb.db file in the /www folder
by default, then creates an empty database when it doesn't find the
pre-populated mydb.db file. This is why it can't find the 'plant' table,
because the newly created blank database obviously doesn't contain a
'plant' table. However I can confirm that the database *is* in the /www
folder, and that it contains the 'plant' table when I run `sqlite3 mydb.db`
then `.tables` in the terminal.

I can't figure out why it's not reading from the pre-populated mydb.db file.

Folder structure (from root):

/src
-/app
--/app.component.ts
/www
-/mydb.db

app.component.ts:

  constructor(public platform: Platform, private sqlite: SQLite ) {
platform.ready().then(() => {
  this.getData();
});
  }

  getData() {
this.sqlite.create({
  name: 'mydb.db',
  location: 'default'
}).then((db: SQLiteObject) => {
  db.executeSql('SELECT * FROM plant ORDER BY id ASC', [])
  .then(res => {
// Do Stuff
  }).catch(e => console.log("FAIL executeSql:", e));
})
  }

I've attempted many fixes that I've found on StackOverflow, like wiping the
app from my device, starting a new ionic project then copying app and
config files over, and setting a direct path in the database location, but
it still keeps trying to read from the empty database that it creates..

Thanks in advance for any help.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Error when reading from pre-populated SQLite database in Ionic project

[Chris Brody]

Check the results of cordova plugin ls

A common pitfall is that you have to use cordova-sqlite-ext plugin to get
pre-populated database functionality. Commonly used cordova-sqlite-storage
plugin does not support this feature. Second pitfall is if you have
multiple Cordova sqlite plugins installed.

On Wed, Sep 5, 2018 at 12:25 PM Chris Locke 
wrote:

> When SQLite creates an empty .db file, which directory is it in?  With all
> your tweaking, etc, is the new database always in the same directory?
>
> Thanks,
> Chris
>
> On Wed, Sep 5, 2018 at 3:23 PM Robert Helmick  >
> wrote:
>
> > I'm receiving an error when I try to read from a pre-populated SQLite
> > database: `sqlite3_prepare_v2 failure: no such table 'plant'`
> >
> > From what I understand SQLite looks for the mydb.db file in the /www
> folder
> > by default, then creates an empty database when it doesn't find the
> > pre-populated mydb.db file. This is why it can't find the 'plant' table,
> > because the newly created blank database obviously doesn't contain a
> > 'plant' table. However I can confirm that the database *is* in the /www
> > folder, and that it contains the 'plant' table when I run `sqlite3
> mydb.db`
> > then `.tables` in the terminal.
> >
> > I can't figure out why it's not reading from the pre-populated mydb.db
> > file.
> >
> > Folder structure (from root):
> >
> > /src
> > -/app
> > --/app.component.ts
> > /www
> > -/mydb.db
> >
> > app.component.ts:
> >
> >   constructor(public platform: Platform, private sqlite: SQLite ) {
> > platform.ready().then(() => {
> >   this.getData();
> > });
> >   }
> >
> >   getData() {
> > this.sqlite.create({
> >   name: 'mydb.db',
> >   location: 'default'
> > }).then((db: SQLiteObject) => {
> >   db.executeSql('SELECT * FROM plant ORDER BY id ASC', [])
> >   .then(res => {
> > // Do Stuff
> >   }).catch(e => console.log("FAIL executeSql:", e));
> > })
> >   }
> >
> > I've attempted many fixes that I've found on StackOverflow, like wiping
> the
> > app from my device, starting a new ionic project then copying app and
> > config files over, and setting a direct path in the database location,
> but
> > it still keeps trying to read from the empty database that it creates..
> >
> > Thanks in advance for any help.
> >
> > Robert
> > ___
> > 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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Error when reading from pre-populated SQLite database in Ionic project

[Chris Locke]

When SQLite creates an empty .db file, which directory is it in?  With all
your tweaking, etc, is the new database always in the same directory?

Thanks,
Chris

On Wed, Sep 5, 2018 at 3:23 PM Robert Helmick 
wrote:

> I'm receiving an error when I try to read from a pre-populated SQLite
> database: `sqlite3_prepare_v2 failure: no such table 'plant'`
>
> From what I understand SQLite looks for the mydb.db file in the /www folder
> by default, then creates an empty database when it doesn't find the
> pre-populated mydb.db file. This is why it can't find the 'plant' table,
> because the newly created blank database obviously doesn't contain a
> 'plant' table. However I can confirm that the database *is* in the /www
> folder, and that it contains the 'plant' table when I run `sqlite3 mydb.db`
> then `.tables` in the terminal.
>
> I can't figure out why it's not reading from the pre-populated mydb.db
> file.
>
> Folder structure (from root):
>
> /src
> -/app
> --/app.component.ts
> /www
> -/mydb.db
>
> app.component.ts:
>
>   constructor(public platform: Platform, private sqlite: SQLite ) {
> platform.ready().then(() => {
>   this.getData();
> });
>   }
>
>   getData() {
> this.sqlite.create({
>   name: 'mydb.db',
>   location: 'default'
> }).then((db: SQLiteObject) => {
>   db.executeSql('SELECT * FROM plant ORDER BY id ASC', [])
>   .then(res => {
> // Do Stuff
>   }).catch(e => console.log("FAIL executeSql:", e));
> })
>   }
>
> I've attempted many fixes that I've found on StackOverflow, like wiping the
> app from my device, starting a new ionic project then copying app and
> config files over, and setting a direct path in the database location, but
> it still keeps trying to read from the empty database that it creates..
>
> Thanks in advance for any help.
>
> Robert
> ___
> 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] Error when reading from pre-populated SQLite database in Ionic project

[Robert Helmick]

I'm receiving an error when I try to read from a pre-populated SQLite
database: `sqlite3_prepare_v2 failure: no such table 'plant'`

From what I understand SQLite looks for the mydb.db file in the /www folder
by default, then creates an empty database when it doesn't find the
pre-populated mydb.db file. This is why it can't find the 'plant' table,
because the newly created blank database obviously doesn't contain a
'plant' table. However I can confirm that the database *is* in the /www
folder, and that it contains the 'plant' table when I run `sqlite3 mydb.db`
then `.tables` in the terminal.

I can't figure out why it's not reading from the pre-populated mydb.db file.

Folder structure (from root):

/src
-/app
--/app.component.ts
/www
-/mydb.db

app.component.ts:

  constructor(public platform: Platform, private sqlite: SQLite ) {
platform.ready().then(() => {
  this.getData();
});
  }

  getData() {
this.sqlite.create({
  name: 'mydb.db',
  location: 'default'
}).then((db: SQLiteObject) => {
  db.executeSql('SELECT * FROM plant ORDER BY id ASC', [])
  .then(res => {
// Do Stuff
  }).catch(e => console.log("FAIL executeSql:", e));
})
  }

I've attempted many fixes that I've found on StackOverflow, like wiping the
app from my device, starting a new ionic project then copying app and
config files over, and setting a direct path in the database location, but
it still keeps trying to read from the empty database that it creates..

Thanks in advance for any help.

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

Re: [sqlite] Error: foreign key mismatch - "loan" referencing "user"

[Keith Medcalf]

Because the required unique index on copy(id_book, copy_number) exists (in the 
table definition).


---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Markos
>Sent: Wednesday, 1 August, 2018 16:23
>To: sqlite-users@mailinglists.sqlite.org
>Subject: Re: [sqlite] Error: foreign key mismatch - "loan"
>referencing "user"
>
>Hi Clemens,
>
>The table user stores info about all users.
>
>But some users have administrator privileges and can register a loan
>or
>a devolution in the system.
>
>On the line:
>> FOREIGN KEY  (id_admin_loan, id_admin_devolution) REFERENCES user
>(id_user, id_user)
>
>The id_admin_loan field is the id_user of a user who has adminstrator
>privileges and registered a loan of a book.
>
>The id_admin_devolution field is the id_user of a user with
>administrator privilege and who registered the devolution of a book.
>
>My intention is to register "who" did "what" in the system.
>
>I followed your suggestion and separated the double constraint into
>two
>separate single constraint:
>
>FOREIGN KEY(id_admin_loan) REFERENCES user(id_user),
>
>FOREIGN KEY(id_admin_devolution) REFERENCES user(id_user)
>
>and worked fine. :-)
>
>Thank you for the tip.
>
>But I'm curious to understand why the original (double) restriction
>was
>generating this error?
>
>Because the other double constraint:
>
>FOREIGN KEY(id_book, copy_number) REFERENCES copy(id_book,
>copy_number),
>
>do not generate any error.
>
>Thank you very much for your attention.
>
>Markos
>
>
>
>Markos wrote:
>
>> CREATE TABLE user (
>>  id_user integer PRIMARY KEY,
>>  ...
>> CREATE TABLE loan (
>>  ...
>>  FOREIGN KEY(id_admin_loan, id_admin_devolution) REFERENCES
>user(id_user, id_user)
>
>I do not understand what this is trying to accomplish.
>Why not two single-column FK constraints?
>
>> Error: foreign key mismatch - "loan" referencing "user"
>
>Because there is no unique index on (id_user,id_user).
>
>
>Regards,
>Clemens
>
>
>Em 30-07-2018 20:42, Markos escreveu:
>> Hi,
>>
>> I am a beginner in database and I'm developing a Database (with
>> Tcl/Tk) to control the loans of books in a reading room.
>>
>> I created a table to register the users:
>>
>> ...
>>
>> db1 eval {CREATE TABLE user (
>>                        id_user integer PRIMARY KEY,
>>                        name text NOT NULL,
>>
>>                         ...
>>
>>                         id_admin integer  NOT NULL
>>                        );}
>>
>>
>> Then create a table for the books:
>>
>> db1 eval {CREATE TABLE book (
>>                        id_book integer PRIMARY KEY,
>>                        title text NOT NULL,
>>
>>                     ...
>>                        subject text NOT NULL,
>>                        UNIQUE (id_book, title));}
>>
>>
>> And another table for each copy of the books, because we can have
>> different copies of a book:
>>
>> db1 eval {CREATE TABLE copy (
>>                        id_copy integer PRIMARY KEY,
>>                        id_book integer NOT NULL,
>>                        copy_number integer NOT NULL,
>>                        ...
>>                        UNIQUE (id_book, copy_number),
>>                        FOREIGN KEY(id_book) REFERENCES
>book(id_book)
>>                        );}
>>
>>
>> And finally a table to register the loans with many FOREIGN KEYS:
>>
>> db1 eval {CREATE TABLE loan (
>>                        id_loan integer PRIMARY KEY,
>>                        id_user integer NOT NULL,
>>                        id_book integer NOT NULL,
>>                        copy_number integer NOT NULL,
>>                        loan_date integer NOT NULL,
>>                        devolution_date integer NOT NULL,
>>                        ...
>>                        UNIQUE (id_user, id_book, copy_number,
>> loan_date),
>>                        FOREIGN KEY(id_book, copy_number)
>> REFERENCES copy(id_book, copy_number),
>>                        FOREIGN KEY(id_user) REFERENCES
>user(id_user),
>>                        FOREIGN K

Re: [sqlite] Error: foreign key mismatch - "loan" referencing "user"

[Markos]

Hi Clemens,

The table user stores info about all users.

But some users have administrator privileges and can register a loan or 
a devolution in the system.


On the line:

FOREIGN KEY  (id_admin_loan, id_admin_devolution) REFERENCES user (id_user, 
id_user)


The id_admin_loan field is the id_user of a user who has adminstrator 
privileges and registered a loan of a book.


The id_admin_devolution field is the id_user of a user with 
administrator privilege and who registered the devolution of a book.


My intention is to register "who" did "what" in the system.

I followed your suggestion and separated the double constraint into two 
separate single constraint:


FOREIGN KEY(id_admin_loan) REFERENCES user(id_user),

FOREIGN KEY(id_admin_devolution) REFERENCES user(id_user)

and worked fine. :-)

Thank you for the tip.

But I'm curious to understand why the original (double) restriction was 
generating this error?


Because the other double constraint:

FOREIGN KEY(id_book, copy_number) REFERENCES copy(id_book, copy_number),

do not generate any error.

Thank you very much for your attention.

Markos



Markos wrote:


CREATE TABLE user (
 id_user integer PRIMARY KEY,
 ...
CREATE TABLE loan (
 ...
 FOREIGN KEY(id_admin_loan, id_admin_devolution) REFERENCES user(id_user, 
id_user)


I do not understand what this is trying to accomplish.
Why not two single-column FK constraints?


Error: foreign key mismatch - "loan" referencing "user"


Because there is no unique index on (id_user,id_user).


Regards,
Clemens


Em 30-07-2018 20:42, Markos escreveu:

Hi,

I am a beginner in database and I'm developing a Database (with 
Tcl/Tk) to control the loans of books in a reading room.


I created a table to register the users:

...

db1 eval {CREATE TABLE user (
                       id_user integer PRIMARY KEY,
                       name text NOT NULL,

                        ...

                        id_admin integer  NOT NULL
                       );}


Then create a table for the books:

db1 eval {CREATE TABLE book (
                       id_book integer PRIMARY KEY,
                       title text NOT NULL,

                    ...
                       subject text NOT NULL,
                       UNIQUE (id_book, title));}


And another table for each copy of the books, because we can have 
different copies of a book:


db1 eval {CREATE TABLE copy (
                       id_copy integer PRIMARY KEY,
                       id_book integer NOT NULL,
                       copy_number integer NOT NULL,
                       ...
                       UNIQUE (id_book, copy_number),
                       FOREIGN KEY(id_book) REFERENCES book(id_book)
                       );}


And finally a table to register the loans with many FOREIGN KEYS:

db1 eval {CREATE TABLE loan (
                       id_loan integer PRIMARY KEY,
                       id_user integer NOT NULL,
                       id_book integer NOT NULL,
                       copy_number integer NOT NULL,
                       loan_date integer NOT NULL,
                       devolution_date integer NOT NULL,
                       ...
                       UNIQUE (id_user, id_book, copy_number, 
loan_date),
                       FOREIGN KEY(id_book, copy_number) 
REFERENCES copy(id_book, copy_number),

                       FOREIGN KEY(id_user) REFERENCES user(id_user),
                       FOREIGN KEY(id_admin_loan, 
id_admin_devolution) REFERENCES user(id_user, id_user));}


But the command to insert a new loan return an error.

The command:

db1 eval "INSERT INTO loan (id_user, id_book, copy_number, loan_date, 
devolution_date, ...) VALUES (1, 2, 1, 1532919600, 1514772000, ...)"


The error:

Error: foreign key mismatch - "loan" referencing "user"

foreign key mismatch - "loan" referencing "user"
foreign key mismatch - "loan" referencing "user"
    while executing
"db1 eval "INSERT INTO $table_name ($field_list) VALUES ($value_list)""

The insert command works well for insertion into the user, book, and 
copy tables.


I'm just having error to insert into the loan table.

I can't find the reason for the error.

Any tip?

Thank you,

Markos


___
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] Error: foreign key mismatch - "loan" referencing "user"

[Clemens Ladisch]

Markos wrote:
> CREATE TABLE user (
> id_user integer PRIMARY KEY,
> ...
> CREATE TABLE loan (
> ...
> FOREIGN KEY(id_admin_loan, id_admin_devolution) REFERENCES user(id_user, 
> id_user)

I do not understand what this is trying to accomplish.
Why not two single-column FK constraints?

> Error: foreign key mismatch - "loan" referencing "user"

Because there is no unique index on (id_user,id_user).


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

[sqlite] Error: foreign key mismatch - "loan" referencing "user"

[Markos]

Hi,

I am a beginner in database and I'm developing a Database (with Tcl/Tk) 
to control the loans of books in a reading room.


I created a table to register the users:

...

db1 eval {CREATE TABLE user (
                       id_user integer PRIMARY KEY,
                       name text NOT NULL,

                        ...

                        id_admin integer  NOT NULL
                       );}


Then create a table for the books:

db1 eval {CREATE TABLE book (
                       id_book integer PRIMARY KEY,
                       title text NOT NULL,

                    ...
                       subject text NOT NULL,
                       UNIQUE (id_book, title));}


And another table for each copy of the books, because we can have 
different copies of a book:


db1 eval {CREATE TABLE copy (
                       id_copy integer PRIMARY KEY,
                       id_book integer NOT NULL,
                       copy_number integer NOT NULL,
                       ...
                       UNIQUE (id_book, copy_number),
                       FOREIGN KEY(id_book) REFERENCES book(id_book)
                       );}


And finally a table to register the loans with many FOREIGN KEYS:

db1 eval {CREATE TABLE loan (
                       id_loan integer PRIMARY KEY,
                       id_user integer NOT NULL,
                       id_book integer NOT NULL,
                       copy_number integer NOT NULL,
                       loan_date integer NOT NULL,
                       devolution_date integer NOT NULL,
                       ...
                       UNIQUE (id_user, id_book, copy_number, 
loan_date),
                       FOREIGN KEY(id_book, copy_number) REFERENCES 
copy(id_book, copy_number),

                       FOREIGN KEY(id_user) REFERENCES user(id_user),
                       FOREIGN KEY(id_admin_loan, 
id_admin_devolution) REFERENCES user(id_user, id_user));}


But the command to insert a new loan return an error.

The command:

db1 eval "INSERT INTO loan (id_user, id_book, copy_number, loan_date, 
devolution_date, ...) VALUES (1, 2, 1, 1532919600, 1514772000, ...)"


The error:

Error: foreign key mismatch - "loan" referencing "user"

foreign key mismatch - "loan" referencing "user"
foreign key mismatch - "loan" referencing "user"
    while executing
"db1 eval "INSERT INTO $table_name ($field_list) VALUES ($value_list)""

The insert command works well for insertion into the user, book, and 
copy tables.


I'm just having error to insert into the loan table.

I can't find the reason for the error.

Any tip?

Thank you,

Markos


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

Re: [sqlite] error SQLite will not work correctly with the -ffast-math option of GCC.

[Jens Alfke]

> On Feb 23, 2018, at 4:25 AM, Ashley Collins-Richardson 
>  wrote:
> 
> I am trying to add sqlite amalgamation into xcode and I keep getting the
> error
> error SQLite will not work correctly with the -ffast-math option of GCC.

Why not just link with the copy of SQLite that’s already built into the OS? 
Unless you absolutely need the very latest version, this will be easier (and 
make your app about 1MB smaller.)

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

Re: [sqlite] error SQLite will not work correctly with the -ffast-math option of GCC.

[Ashley Collins-Richardson]

I'm still getting the error

---

Ashley Collins-Richardson
Technical Director | Scrapbook Development Limited |
www.scrapbookdev.com [1] 
Electric Works - Digital Campus, 3 Concourse Way, Sheffield, S1 2BJ 

On 2018-02-23 13:18, Simon Slavin wrote:

> On 23 Feb 2018, at 12:25pm, Ashley Collins-Richardson 
>  wrote:
> 
>> error SQLite will not work correctly with the -ffast-math option of GCC.
> 
> For now, turn off all optmization:
> 
> Select the Project folder
> Build Settings
> Compiler Options
> Apple LLVM 8.0 Code Generation
> Optimization Level
> 
> set it to None.
> 
> If that gets rid of the problem then you can worry about the specific option 
> the error message mentions.
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
 

Links:
--
[1] http://www.scrapbookdev.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] error SQLite will not work correctly with the -ffast-math option of GCC.

[Ashley Collins-Richardson]

got it working. thank you for your help

---

Ashley Collins-Richardson
Technical Director | Scrapbook Development Limited |
www.scrapbookdev.com [1] 
Electric Works - Digital Campus, 3 Concourse Way, Sheffield, S1 2BJ 

On 2018-02-23 13:40, Simon Slavin wrote:

> On 23 Feb 2018, at 1:38pm, Ashley Collins-Richardson 
>  wrote:
> 
>> now I have the error 'system' is unavailable: not available on iOS
> 
> Are you trying to compile the executable "sqlite3" program ?  That won't run 
> on iOS.  You need to be using just the amalgamation source code, which is one 
> .c file and one .h file.
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
 

Links:
--
[1] http://www.scrapbookdev.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] error SQLite will not work correctly with the -ffast-math option of GCC.

[Simon Slavin]

On 23 Feb 2018, at 1:38pm, Ashley Collins-Richardson 
 wrote:

> now I have the error 'system' is unavailable: not available on iOS

Are you trying to compile the executable "sqlite3" program ?  That won't run on 
iOS.  You need to be using just the amalgamation source code, which is one .c 
file and one .h file.

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

Re: [sqlite] error SQLite will not work correctly with the -ffast-math option of GCC.

[Ashley Collins-Richardson]

found that issue - you have to set the relax iee compliance to NO

now I have the error 'system' is unavailable: not available on iOS

---

Ashley Collins-Richardson
Technical Director | Scrapbook Development Limited |
www.scrapbookdev.com [1] 
Electric Works - Digital Campus, 3 Concourse Way, Sheffield, S1 2BJ 

On 2018-02-23 13:24, Simon Slavin wrote:

> On 23 Feb 2018, at 1:18pm, Simon Slavin  wrote:
> 
>> For now, turn off all optmization:
>> 
>> Select the Project folder
>> Build Settings
>> Compiler Options
>> Apple LLVM 8.0 Code Generation
>> Optimization Level
>> 
>> set it to None.
> 
> Whoops sorry you're using GCC not LLVM.  Look for an equivalent setting.
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
 

Links:
--
[1] http://www.scrapbookdev.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] error SQLite will not work correctly with the -ffast-math option of GCC.

[Simon Slavin]

On 23 Feb 2018, at 1:18pm, Simon Slavin  wrote:

> For now, turn off all optmization:
> 
> Select the Project folder
> Build Settings
> Compiler Options
> Apple LLVM 8.0 Code Generation
> Optimization Level
> 
> set it to None.

Whoops sorry you're using GCC not LLVM.  Look for an equivalent setting.

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

Re: [sqlite] error SQLite will not work correctly with the -ffast-math option of GCC.

[Simon Slavin]

On 23 Feb 2018, at 12:25pm, Ashley Collins-Richardson 
 wrote:

> error SQLite will not work correctly with the -ffast-math option of GCC.

For now, turn off all optmization:

Select the Project folder
Build Settings
Compiler Options
Apple LLVM 8.0 Code Generation
Optimization Level

set it to None.

If that gets rid of the problem then you can worry about the specific option 
the error message mentions.

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

Re: [sqlite] error SQLite will not work correctly with the -ffast-math option of GCC.

[Ashley Collins-Richardson]

how do you do it ? in the other linker flage setting?

---

Ashley Collins-Richardson
Technical Director | Scrapbook Development Limited |
www.scrapbookdev.com [1] 
Electric Works - Digital Campus, 3 Concourse Way, Sheffield, S1 2BJ 

On 2018-02-23 12:38, Richard Hipp wrote:

> On 2/23/18, Ashley Collins-Richardson  wrote: 
> 
>> Hi There,
>> 
>> I am trying to add sqlite amalgamation into xcode and I keep getting the
>> error
>> 
>> error SQLite will not work correctly with the -ffast-math option of GCC.
>> 
>> I have swapped  with #sqlite3.h and removed the dependencies
>> from the previous library but I think I'm missing something.
> 
> Disable the -ffast-math compile-time option since it is incompatible
> with SQLite.
 

Links:
--
[1] http://www.scrapbookdev.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] error SQLite will not work correctly with the -ffast-math option of GCC.

[Richard Hipp]

On 2/23/18, Ashley Collins-Richardson  wrote:
> Hi There,
>
> I am trying to add sqlite amalgamation into xcode and I keep getting the
> error
>
> error SQLite will not work correctly with the -ffast-math option of GCC.
>
> I have swapped  with #sqlite3.h and removed the dependencies
> from the previous library but I think I'm missing something.

Disable the -ffast-math compile-time option since it is incompatible
with SQLite.
-- 
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

[sqlite] error SQLite will not work correctly with the -ffast-math option of GCC.

[Ashley Collins-Richardson]

Hi There,

I am trying to add sqlite amalgamation into xcode and I keep getting the
error

error SQLite will not work correctly with the -ffast-math option of GCC.

I have swapped  with #sqlite3.h and removed the dependencies
from the previous library but I think I'm missing something.

Please advise

-- 

Ashley Collins-Richardson
Technical Director | Scrapbook Development Limited |
www.scrapbookdev.com [1] 
Electric Works - Digital Campus, 3 Concourse Way, Sheffield, S1 2BJ 

 

Links:
--
[1] http://www.scrapbookdev.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "Error: NOT NULL constraint failed ..." when no such constraint exists

[Simon Slavin]

On 19 Jan 2018, at 1:48pm, R Smith  wrote:

> On 2018/01/19 3:36 PM, Simon Slavin wrote:
> 
>> I hope one day to see SQLite4, in which everything done to avoid breaking 
>> backward compatibility is abandoned. 
> 
> That's an impossibility.
> 
> If you mean that SQLite4 itself will not have to honour backwards 
> compatibility to SQLite3, then yes - but if you intended that SQLite4 itself 
> will have a mechanism by which it will not fall prey to the perils of 
> maintaining backwards compatibility to its own generation, then no, that is 
> impossible. It too will have the same struggle.

Oh no, I get that.  SQLite4 will start its own legacy of foibles.  But it would 
be nice to get rid of the "" quoted variable names, the NULL keys, and a few 
other things.

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

Re: [sqlite] "Error: NOT NULL constraint failed ..." when no such constraint exists

[R Smith]

On 2018/01/19 3:36 PM, Simon Slavin wrote:

On 19 Jan 2018, at 12:43pm, Richard Hipp  wrote:

I hope one day to see SQLite4, in which everything done to avoid 
breaking backward compatibility is abandoned. 


That's an impossibility.

If you mean that SQLite4 itself will not have to honour backwards 
compatibility to SQLite3, then yes - but if you intended that SQLite4 
itself will have a mechanism by which it will not fall prey to the 
perils of maintaining backwards compatibility to its own generation, 
then no, that is impossible. It too will have the same struggle.


One can hope though that, by that time, so many SQL-standard things have 
been fixed that it won't matter much anymore.



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

Re: [sqlite] "Error: NOT NULL constraint failed ..." when no such constraint exists

[Simon Slavin]

On 19 Jan 2018, at 12:43pm, Richard Hipp  wrote:

> Sometimes one has to compromise the simplicity of the
> design, or to document bugs rather than fix them, in order to avoid
> breaking legacy applications.

I hope one day to see SQLite4, in which everything done to avoid breaking 
backward compatibility is abandoned.

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

Re: [sqlite] "Error: NOT NULL constraint failed ..." when no such constraint exists

[Richard Hipp]

On 1/19/18, Shane Dev  wrote:
> I missed that part of the documentation, thanks

I apologize for this goofy exception to the rules.  Maintaining a
widely-used library like SQLite in a way that is backwards compatible
is difficult.  Sometimes one has to compromise the simplicity of the
design, or to document bugs rather than fix them, in order to avoid
breaking legacy applications.  This is such an instance.  There are
others, which you will no doubt encounter from time to time if you use
SQLite intensely.
-- 
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] "Error: NOT NULL constraint failed ..." when no such constraint exists

[Keith Medcalf]

A Primary Key cannot be null.  Except in cases of bug-for-bug backwards 
compatibility.  WITHOUT ROWID tables comply with the SQL specification, not the 
bug-for-bug compatibility mode of rowid tables where due to an age old error 
nulls are allowed in primary keys although they should not be.


---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Shane Dev
>Sent: Friday, 19 January, 2018 01:32
>To: SQLite mailing list
>Subject: [sqlite] "Error: NOT NULL constraint failed ..." when no
>such constraint exists
>
>Hello,
>
>The following SQL works as I expect -
>
>sqlite> CREATE TABLE edges(parent int, child int, primary key(parent,
>child));
>sqlite> insert into edges select null, 1;
>sqlite> select * from edges;
>parent  child
>1
>sqlite>
>
>but if I remove the superfluous rowid column from the table
>definition -
>
>sqlite> drop table edges;
>sqlite> CREATE TABLE edges(parent int, child int, primary key(parent,
>child)) without rowid;
>sqlite> insert into edges select null, 1;
>Error: NOT NULL constraint failed: edges.parent
>sqlite>
>
>Why do I get this error?
>___
>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] "Error: NOT NULL constraint failed ..." when no such constraint exists

[Shane Dev]

I missed that part of the documentation, thanks

On 19 January 2018 at 07:51, Rowan Worth  wrote:

> https://www.sqlite.org/withoutrowid.html
>
> "NOT NULL is enforced on every column of the PRIMARY KEY in a WITHOUT ROWID
> table."
>
> It goes on to say that NOT NULL is supposed to be enforced on all PRIMARY
> KEY columns of _every_ table according to the SQL standard, but an early
> version of sqlite included a bug which allowed NULLs and as a result sqlite
> does not enforce this for ROWID tables.
>
> -Rowan
>
> On 19 January 2018 at 14:32, Shane Dev  wrote:
>
> > Hello,
> >
> > The following SQL works as I expect -
> >
> > sqlite> CREATE TABLE edges(parent int, child int, primary key(parent,
> > child));
> > sqlite> insert into edges select null, 1;
> > sqlite> select * from edges;
> > parent  child
> > 1
> > sqlite>
> >
> > but if I remove the superfluous rowid column from the table definition -
> >
> > sqlite> drop table edges;
> > sqlite> CREATE TABLE edges(parent int, child int, primary key(parent,
> > child)) without rowid;
> > sqlite> insert into edges select null, 1;
> > Error: NOT NULL constraint failed: edges.parent
> > sqlite>
> >
> > Why do I get this error?
> > ___
> > 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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "Error: NOT NULL constraint failed ..." when no such constraint exists

[Rowan Worth]

https://www.sqlite.org/withoutrowid.html

"NOT NULL is enforced on every column of the PRIMARY KEY in a WITHOUT ROWID
table."

It goes on to say that NOT NULL is supposed to be enforced on all PRIMARY
KEY columns of _every_ table according to the SQL standard, but an early
version of sqlite included a bug which allowed NULLs and as a result sqlite
does not enforce this for ROWID tables.

-Rowan

On 19 January 2018 at 14:32, Shane Dev  wrote:

> Hello,
>
> The following SQL works as I expect -
>
> sqlite> CREATE TABLE edges(parent int, child int, primary key(parent,
> child));
> sqlite> insert into edges select null, 1;
> sqlite> select * from edges;
> parent  child
> 1
> sqlite>
>
> but if I remove the superfluous rowid column from the table definition -
>
> sqlite> drop table edges;
> sqlite> CREATE TABLE edges(parent int, child int, primary key(parent,
> child)) without rowid;
> sqlite> insert into edges select null, 1;
> Error: NOT NULL constraint failed: edges.parent
> sqlite>
>
> Why do I get this error?
> ___
> 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] "Error: NOT NULL constraint failed ..." when no such constraint exists

[Shane Dev]

Hello,

The following SQL works as I expect -

sqlite> CREATE TABLE edges(parent int, child int, primary key(parent,
child));
sqlite> insert into edges select null, 1;
sqlite> select * from edges;
parent  child
1
sqlite>

but if I remove the superfluous rowid column from the table definition -

sqlite> drop table edges;
sqlite> CREATE TABLE edges(parent int, child int, primary key(parent,
child)) without rowid;
sqlite> insert into edges select null, 1;
Error: NOT NULL constraint failed: edges.parent
sqlite>

Why do I get this error?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users