[sqlite] Primary key values can be NULL
"INTEGER PRIMARY KEY" declares an alias for the rowid. It is always NOT NULL (it is the record number) and it is always and integer (it is not ducky-typed). Specifying NULL for the rowid causes the generation of the "next" rowid/record number. AUTOINCREMENT modifies "next" to mean "one greater than any rowid/record number that has ever been used in the table" rather than the normal "one greater than the current max rowid/record number". You CANNOT insert a null into an INTEGER PRIMARY KEY (that is, obtain an actual NULL value in the table for that column), nor can you insert any value that is not an integer (that is, have the value of the rowid/record number for an inserted record be anything other than an integer). > -Original Message- > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- > bounces at mailinglists.sqlite.org] On Behalf Of Adam Devita > Sent: Monday, 18 April, 2016 13:45 > To: SQLite mailing list > Subject: Re: [sqlite] Primary key values can be NULL > > I had a similar thought, until I imagined > > Program uses sqlite DLL > The program creates dbs, and sometimes does inserts with null primary > keys. (Why is beyond the scope, it just does sometimes.) > > Upgrading the DLL would start making files in the new format, but the > program using the dll doesn't know that. It just starts failing. > > regards, > Adam > > On Mon, Apr 18, 2016 at 10:29 AM, David Raymond > wrote: > > I don't mean to poke a busy thread with a possibly stupid newbie > question, but here goes. > > > > How is this that much different than say, the SQLITE_DEFAULT_FILE_FORMAT > compile option? (Pasting it here then continuing comment below) > > > > Text pasted here > > SQLITE_DEFAULT_FILE_FORMAT=<1 or 4> > > > > The default schema format number used by SQLite when creating new > database files is set by this macro. The schema formats are all very > similar. The difference between formats 1 and 4 is that format 4 > understands descending indices and has a tighter encoding for boolean > values. > > > > All versions of SQLite since 3.3.0 (2006-01-10) can read and write > any schema format between 1 and 4. But older versions of SQLite might not > be able to read formats greater than 1. So that older versions of SQLite > will be able to read and write database files created by newer versions of > SQLite, the default schema format was set to 1 for SQLite versions through > 3.7.9 (2011-11-01). Beginning with version 3.7.10, the default schema > format is 4. > > > > The schema format number for a new database can be set at runtime > using the PRAGMA legacy_file_format command. > > End quoted section > > > > The key point when introducing something new seems to be "as long as old > versions will know they shouldn't mess with it, then it's ok." So things > like CTE's can be added to the language as the old parser will gag on them > and not try to do something wrong with them and fail. But just straight up > changing the enforcement here would be bad, because the old version > wouldn't know that something new is going on. So although the above file > format option is intended for the physical structure of the file, could > for example we call file format 5 to be "same format, but will not null > primary key enforced." Then old versions would open it up, see file format > 5, and throw their hands up saying "I can't deal with this." And with new > versions it wouldn't be a changeable option, it would be "hey, once you > create this database file with this option, then you're stuck with that > enforcement forever." Looking at the dates above, format 4 was optional > for 5 years before it got a promotion to default, > si > > milarly a new value would have to be explicitly specified for n years > before anyone would have to worry about there being a "default build" that > would make something that could not be read by old versions. > > > > I know that actually using SQLITE_DEFAULT_FILE_FORMAT for this would be > very bad and not what it's intended for. But for example, there are 20 > bytes of "Reserved for expansion. Must be zero." in the header at the > moment. Do past or current versions throw up an error if those aren't zero > at the moment? Might it be time to appropriate a byte of reserved space > for new flags or an additional number? Or would that be the start of a > slippery slope? > > > > (As a newbie I apologize if this is just plain wrong, if I just created > the sound of hundreds of foreheads smacking into their desks in unison, or > if I just re-stirred a hornets' nest) > > ___ > > sqlite-users mailing list > > sqlite-users at mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > -- > -- > VerifEye Technologies Inc. > 151 Whitehall Dr. Unit 2 > Markham, ON > L3R 9T1 > ___ > sqlite-users
[sqlite] UPDATE/open/close blocked while executing SELECT
On Monday, 18 April, 2016 08:47 Olivier Mascia wrote: > Just to clarify: you mean compiled using SQLITE_CONFIG_MULTITHREAD (and > not SQLITE_CONFIG_SERIALIZED), is that it? > Then, if using threads in the application, each thread has its own > connection (or multiple connections) but no thread share any connection > with another one? (This is required for SQLITE_CONFIG_MULTITHREAD mode, > else you would need SQLITE_CONFIG_SERIALIZED, and that would add a lot of > contention). You do not have to restrict a connection to a thread, and you can use the same connection from multiple threads. What you cannot do (and what SERIALIZED enforces) is that only one thread per connection can be executing inside (entered) the SQLite engine/library at a time. That is, a connection (and anything derived from that connection) is fully serially entrant across threads. It is not, however, multiply entrant. The only difference between MULTITHREADED and SERIALIZED is that in the latter case SQLite enforces single entrance per connection -- in MULTITHREAD you have to enforce that yourself. Dedicating a connection per thread and having a given thread only use its connection is one way of enforcing the single entrance requirement, but not the only way.
[sqlite] SQLite 3.12.1 reports "database corruption" with existing databases created with SQLite 3.9.2
t18970237136 at web.de wrote: >>> after updating to 1.0.100.0 which uses SQLite 3.12.1, we are getting the >>> following error >>> >>> SQLite error (1): AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY > > The problem is ... the "[" and "]" in this syntax: > CREATE TABLE Customer ( > [ID] [INTEGER] NOT NULL PRIMARY KEY AUTOINCREMENT > ); Quoting an indentifier prevents the database from interpreting it as a keyword. Arguably, the old SQLite version did this wrong in this case. Regards, Clemens
[sqlite] Is it possible that dropping a big table takes very long
On 04/16/2016 04:59 PM, Cecil Westerhof wrote:
> I am playing a bit with SQLite. I first had a table with 1E8 elements. When
> trying to drop this it looked like SQLite got hung. I tried it from DB
> Browser and a Java program.
> I just tried it with a table of 1E7 elements. That was dropped in about 13
> seconds.
> I will try it again with 1E8 elements, but it takes 4? hours to generated.
> Is it possible that SQLite has trouble dropping very large tables? It was
> 5.2 GB. With 1E7 elements the table is 512 MB.
>
> The definition of the table:
> CREATE TABLE testUniqueUUID (
> UUIDblob,
>
> PRIMARY KEY(UUID)
> CHECK(TYPEOF(UUID) = 'blob' AND
>LENGTH(UUID) = 16 AND
>SUBSTR(HEX(UUID), 13, 1) == '4' AND
>SUBSTR(HEX(UUID), 17, 1) IN ('8', '9', 'A', 'B')
> )
> );
>
Are there any other tables in the db with foreign keys that refer to the
table being dropped?
If so, try with foreign keys disabled:
PRAGMA foreign_keys = 0;
Dan.
[sqlite] SQLite 3.12.1 reports "database corruption" with existing databases created with SQLite 3.9.2
Hi Clemens,? > Gesendet:?Montag, 18. April 2016 um 16:07 Uhr > Von:?"Clemens Ladisch" > t18970237136 at web.de wrote: > > after updating to 1.0.100.0 which uses SQLite 3.12.1, we are getting the > > following error > > > > SQLite error (1): AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY > > > > The problem seems to be that the tables have been created using the > > following SQL: > > CREATE TABLE DeviceEndpoint ( > > ID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, > > ... > > ); > > > > It seems the new version does not like the "NOT NULL" be placed between > > "INTEGER" and "PRIMARY KEY" > > sqlite> CREATE TABLE DeviceEndpoint ( > ...> ID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, > ...> [...] > ...> ); > sqlite> > > Might this error come from another table? Are you using AUTOINCREMENT > elsewhere? Sorry, I have rechecked this. The problem is not the order of the NOT NULL clause, but the "[" and "]" in this syntax: CREATE TABLE Customer ( [ID] [INTEGER] NOT NULL PRIMARY KEY AUTOINCREMENT ); We have used this syntax to create the tables, which worked in SQLite up to 3.9.2. However, since 3.12.1 I'm getting the error with the autoincrement when opening an existing DB with SQLite 3.12.1. Thanks! Regards, Konstantin Prei?er
[sqlite] SQLite 3.12.1 reports "database corruption" with existing databases created with SQLite 3.9.2
t18970237136 at web.de wrote: > after updating to 1.0.100.0 which uses SQLite 3.12.1, we are getting the > following error > > SQLite error (1): AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY > > The problem seems to be that the tables have been created using the following > SQL: > CREATE TABLE DeviceEndpoint ( > ID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, > ... > ); > > It seems the new version does not like the "NOT NULL" be placed between > "INTEGER" and "PRIMARY KEY" sqlite> CREATE TABLE DeviceEndpoint ( ...> ID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, ...> [...] ...> ); sqlite> Might this error come from another table? Are you using AUTOINCREMENT elsewhere? Regards, Clemens
[sqlite] SQLite 3.12.1 reports "database corruption" with existing databases created with SQLite 3.9.2
[Sorry, I sent an HTML email which doesn't seem do be allowed by the mailing list. I'm sending the message again as plain text] Hi, we are using SQLite in a C# (.Net 4.6, x86 and x64) Application on Windows 10/7 using the System.Data.Sqlite wrapper from https://system.data.sqlite.org. We use these properties on the SQLiteConnectionStringBuilder: UseUTF16Encoding=true, ForeignKeys=true, FailIfMissing=true, RecursiveTriggers=false. Previously we were using System.Data.SQLite Version 1.0.99.0, which was using SQlite 3.9.2. However, after updating to 1.0.100.0 which uses SQLite 3.12.1, we are getting the following error as soon as we try to do e.g. a SELECT statement on any table from an existing DB that has been created with the older version: SQLite error (1): AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY SQLite error (11): database corruption at line 109680 of [fe7d3b75fe] ... (other similar errors) SQLite error (1): no such table: main.DeviceEndpoint SQLite error (11): database corruption at line 109680 of [fe7d3b75fe] SQLite error (11): malformed database schema (IDX_DeviceEndpoint_CustomerIDXName) - no such table: main.DeviceEndpoint The problem seems to be that the tables have been created using the following SQL: CREATE TABLE DeviceEndpoint ( ID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, ... ); It seems the new version does not like the "NOT NULL" be placed between "INTEGER" and "PRIMARY KEY", and instead I would have needed to write "ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL". While I can fix this for new DBs, I'm not sure if there's a way to change the declaration of the DBs that have been created using 3.9.2 and are now accessed using 3.12.1. Is this behavior expected? Also, when looking at https://www.sqlite.org/lang_createtable.html (column-def and column-constraint) , the syntax tree doesn't seem to disallow a "NOT NULL" between the type and the primary key constraint. Any idea how to fix this? Thanks! Regards, Konstantin Prei?er
[sqlite] UPDATE/open/close blocked while executing SELECT
> Le 18 avr. 2016 ? 15:10, Detlef Golze a ?crit : > > thank you for the hints. I think I do it correctly, I compiled with > SQLITE_CONFIG_MULTITHREAD and attach the database with the URI option > cache=shared. Do you really *need* to use shared cache in favor of private cache? There are more contentions (on the cache) implied by locks on table b-trees, when you use shared-cache. We quickly had to rule it out, for better concurrency (of readers). Also when using shared-cache, taking into account the large number of SQLITE_LOCKED_SHAREDCACHE extended error code you will get, it is probably better to use sqlite3_unlock_notify() properly in those cases, so that each thread is awaken properly as soon as it can retry and proceed. But that implies some more programming and is probably more fitted for an intermediate layer of code between you application code and sqlite library code (that's how we tested it). Anyway, unless you have severe memory constraints, I would insist you try the private cache for each of your connections, you should see direct benefits. > Also, each connection is used by one thread at a time. But another thread may > reuse a connection - I hope that is allowed. I think so, because sqlite does not keep per-thread data but per connection data. So if you're absolutely sure this can't lead to situations of two threads sharing the connection, it should be okay, though I'd refrain to do that, it probably is useless. Here our experience has been that "re-using" connections is useless, at least for us. Opening a connection when needed is very cheap with sqlite. So each worker thread in our app server, opens a new connection when the thread is scheduled fo working for a client and closes it when work done (client disconnect). > Are you also opening/closing database connections and prepare statements > while another SELECT is running? We can have any number of connections opened (generally less than 30), and any of them can run simultaneous select statements without issue, as far as our short experience shows. You can even have one writer do a short-lived transaction while you have a number of readers right in the middle of select(s), that won't be a problem, wal is your friend for this. -- Meilleures salutations, Met vriendelijke groeten, Best Regards, Olivier Mascia, integral.be/om
[sqlite] UPDATE/open/close blocked while executing SELECT
Detlef Golze wrote: > I attach the database with the URI option cache=shared. This makes all threads share a single connection. Don't do it. Regards, Clemens
[sqlite] UPDATE/open/close blocked while executing SELECT
On 18 Apr 2016, at 2:10pm, Detlef Golze wrote: > I compiled with SQLITE_CONFIG_MULTITHREAD That bit's okay. > and attach the database with the URI option cache=shared Try turning that bit off and see if it results in an overall speed-up. Simon.
[sqlite] UPDATE/open/close blocked while executing SELECT
> Le 18 avr. 2016 ? 14:17, Detlef Golze a ?crit : > > Hi, > > I am using SQLite C amalgamation version 3.08.11.01 compiled for Multi > Threading Mode and using WAL Journal Mode. > > Sometimes I need to execute a SELECT statement which takes a very long time > to complete. I realized that during that time other functions are blocked for > a significant amount of time, eventually they get through after few minutes, > but then, another statement/function hangs. I have seen UPDATE statements > blocking and also functions like sqlite3_prepare16_v2(), open/close calls for > the same database. > > It is usually blocking at btreeLockCarefully(). > > Is there something I can do to avoid such long starvations or is that by > design? > > Thank you very much, > Detlef. I'm not an old-timer using SQLite and participating here, but I'm using it in WAL journal mode only, especially for its feature of 'multiple readers do not block writer and see a stable view of the db based on the instant they started their transaction'. Just to clarify: you mean compiled using SQLITE_CONFIG_MULTITHREAD (and not SQLITE_CONFIG_SERIALIZED), is that it? Then, if using threads in the application, each thread has its own connection (or multiple connections) but no thread share any connection with another one? (This is required for SQLITE_CONFIG_MULTITHREAD mode, else you would need SQLITE_CONFIG_SERIALIZED, and that would add a lot of contention). Also, are your connections using private (SQLITE_OPEN_PRIVATECACHE) or shared cache (SQLITE_OPEN_SHAREDCACHE)? When using SQLITE_CONFIG_MULTITHREAD, taking care no application thread share a connection with another, and using SQLITE_OPEN_PRIVATECACHE, along with WAL journal mode, I see no issue similar to what you report in our own programming. -- Meilleures salutations, Met vriendelijke groeten, Best Regards, Olivier Mascia, integral.be/om
[sqlite] Primary key values can be NULL
I don't mean to poke a busy thread with a possibly stupid newbie question, but here goes. How is this that much different than say, the SQLITE_DEFAULT_FILE_FORMAT compile option? (Pasting it here then continuing comment below) Text pasted here SQLITE_DEFAULT_FILE_FORMAT=<1 or 4> The default schema format number used by SQLite when creating new database files is set by this macro. The schema formats are all very similar. The difference between formats 1 and 4 is that format 4 understands descending indices and has a tighter encoding for boolean values. All versions of SQLite since 3.3.0 (2006-01-10) can read and write any schema format between 1 and 4. But older versions of SQLite might not be able to read formats greater than 1. So that older versions of SQLite will be able to read and write database files created by newer versions of SQLite, the default schema format was set to 1 for SQLite versions through 3.7.9 (2011-11-01). Beginning with version 3.7.10, the default schema format is 4. The schema format number for a new database can be set at runtime using the PRAGMA legacy_file_format command. End quoted section The key point when introducing something new seems to be "as long as old versions will know they shouldn't mess with it, then it's ok." So things like CTE's can be added to the language as the old parser will gag on them and not try to do something wrong with them and fail. But just straight up changing the enforcement here would be bad, because the old version wouldn't know that something new is going on. So although the above file format option is intended for the physical structure of the file, could for example we call file format 5 to be "same format, but will not null primary key enforced." Then old versions would open it up, see file format 5, and throw their hands up saying "I can't deal with this." And with new versions it wouldn't be a changeable option, it would be "hey, once you create this database file with this option, then you're stuck with that enforcement forever." Looking at the dates above, format 4 was optional for 5 years before it got a promotion to default, similarly a new value would have to be explicitly specified for n years before anyone would have to worry about there being a "default build" that would make something that could not be read by old versions. I know that actually using SQLITE_DEFAULT_FILE_FORMAT for this would be very bad and not what it's intended for. But for example, there are 20 bytes of "Reserved for expansion. Must be zero." in the header at the moment. Do past or current versions throw up an error if those aren't zero at the moment? Might it be time to appropriate a byte of reserved space for new flags or an additional number? Or would that be the start of a slippery slope? (As a newbie I apologize if this is just plain wrong, if I just created the sound of hundreds of foreheads smacking into their desks in unison, or if I just re-stirred a hornets' nest)
[sqlite] sqlite3 update hook data
Hi, Do you know if there is a way how to get n-th column of row during sqlite3_update_hook callback ? I saw there are (in experimental version) some functions like sqlite3_preupdate_new that are able to get this information during sqlite3_preupdate_hook but this is not in stable version. A by the way do anybody have any info when sqlite3_preupdate_hook will be available in the stable version cause this feature seems to be in the code since 2011 ;) ? With regards Martin.
[sqlite] UPDATE/open/close blocked while executing SELECT
> Detlef Golze wrote: > > I attach the database with the URI option cache=shared. > > This makes all threads share a single connection. Don't do it. > > > Regards, > Clemens That was indeed the problem. During my initial tests this option provided the best performance. I probably need to re-think that. Thanks, Detlef.
[sqlite] Primary key values can be NULL
I had a similar thought, until I imagined Program uses sqlite DLL The program creates dbs, and sometimes does inserts with null primary keys. (Why is beyond the scope, it just does sometimes.) Upgrading the DLL would start making files in the new format, but the program using the dll doesn't know that. It just starts failing. regards, Adam On Mon, Apr 18, 2016 at 10:29 AM, David Raymond wrote: > I don't mean to poke a busy thread with a possibly stupid newbie question, > but here goes. > > How is this that much different than say, the SQLITE_DEFAULT_FILE_FORMAT > compile option? (Pasting it here then continuing comment below) > > Text pasted here > SQLITE_DEFAULT_FILE_FORMAT=<1 or 4> > > The default schema format number used by SQLite when creating new > database files is set by this macro. The schema formats are all very similar. > The difference between formats 1 and 4 is that format 4 understands > descending indices and has a tighter encoding for boolean values. > > All versions of SQLite since 3.3.0 (2006-01-10) can read and write any > schema format between 1 and 4. But older versions of SQLite might not be able > to read formats greater than 1. So that older versions of SQLite will be able > to read and write database files created by newer versions of SQLite, the > default schema format was set to 1 for SQLite versions through 3.7.9 > (2011-11-01). Beginning with version 3.7.10, the default schema format is 4. > > The schema format number for a new database can be set at runtime using > the PRAGMA legacy_file_format command. > End quoted section > > The key point when introducing something new seems to be "as long as old > versions will know they shouldn't mess with it, then it's ok." So things like > CTE's can be added to the language as the old parser will gag on them and not > try to do something wrong with them and fail. But just straight up changing > the enforcement here would be bad, because the old version wouldn't know that > something new is going on. So although the above file format option is > intended for the physical structure of the file, could for example we call > file format 5 to be "same format, but will not null primary key enforced." > Then old versions would open it up, see file format 5, and throw their hands > up saying "I can't deal with this." And with new versions it wouldn't be a > changeable option, it would be "hey, once you create this database file with > this option, then you're stuck with that enforcement forever." Looking at the > dates above, format 4 was optional for 5 years before it got a promotion to > default, si > milarly a new value would have to be explicitly specified for n years before > anyone would have to worry about there being a "default build" that would > make something that could not be read by old versions. > > I know that actually using SQLITE_DEFAULT_FILE_FORMAT for this would be very > bad and not what it's intended for. But for example, there are 20 bytes of > "Reserved for expansion. Must be zero." in the header at the moment. Do past > or current versions throw up an error if those aren't zero at the moment? > Might it be time to appropriate a byte of reserved space for new flags or an > additional number? Or would that be the start of a slippery slope? > > (As a newbie I apologize if this is just plain wrong, if I just created the > sound of hundreds of foreheads smacking into their desks in unison, or if I > just re-stirred a hornets' nest) > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users -- -- VerifEye Technologies Inc. 151 Whitehall Dr. Unit 2 Markham, ON L3R 9T1
[sqlite] SQLite 3.12.1 reports "database corruption" with existing databases created with SQLite 3.9.2
On 4/18/16, Clemens Ladisch wrote: > t18970237136 at web.de wrote: after updating to 1.0.100.0 which uses SQLite 3.12.1, we are getting the following error SQLite error (1): AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY >> >> The problem is ... the "[" and "]" in this syntax: >> CREATE TABLE Customer ( >> [ID] [INTEGER] NOT NULL PRIMARY KEY AUTOINCREMENT >> ); > > Quoting an indentifier prevents the database from interpreting it as a > keyword. > > Arguably, the old SQLite version did this wrong in this case. Backwards compatibility is very important and so this is definitely a (serious) bug in 3.12.0 and 3.12.1. The current 3.12.2 release candidate (https://www.sqlite.org/src/timeline?c=92dc59fd) fixes the problem (https://www.sqlite.org/src/info/7d7525cb01b68) and is in testing now. There will be a release soon. Even though SQLite has always treated datatype names as identifiers, this is not something that was documented or tested (though it is tested now!) and not something that the developers were aware of prior to today. Hence this behavior change managed to slip through our release process unnoticed. -- D. Richard Hipp drh at sqlite.org
[sqlite] UPDATE/open/close blocked while executing SELECT
Hello Olivier, thank you for the hints. I think I do it correctly, I compiled with SQLITE_CONFIG_MULTITHREAD and attach the database with the URI option cache=shared. Also, each connection is used by one thread at a time. But another thread may reuse a connection - I hope that is allowed. Are you also opening/closing database connections and prepare statements while another SELECT is running? Thanks, Detlef. -Original Message- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Olivier Mascia Sent: Monday, April 18, 2016 2:47 PM To: SQLite mailing list Subject: Re: [sqlite] UPDATE/open/close blocked while executing SELECT > Le 18 avr. 2016 ? 14:17, Detlef Golze a ?crit : > > Hi, > > I am using SQLite C amalgamation version 3.08.11.01 compiled for Multi > Threading Mode and using WAL Journal Mode. > > Sometimes I need to execute a SELECT statement which takes a very long time > to complete. I realized that during that time other functions are blocked for > a significant amount of time, eventually they get through after few minutes, > but then, another statement/function hangs. I have seen UPDATE statements > blocking and also functions like sqlite3_prepare16_v2(), open/close calls for > the same database. > > It is usually blocking at btreeLockCarefully(). > > Is there something I can do to avoid such long starvations or is that by > design? > > Thank you very much, > Detlef. I'm not an old-timer using SQLite and participating here, but I'm using it in WAL journal mode only, especially for its feature of 'multiple readers do not block writer and see a stable view of the db based on the instant they started their transaction'. Just to clarify: you mean compiled using SQLITE_CONFIG_MULTITHREAD (and not SQLITE_CONFIG_SERIALIZED), is that it? Then, if using threads in the application, each thread has its own connection (or multiple connections) but no thread share any connection with another one? (This is required for SQLITE_CONFIG_MULTITHREAD mode, else you would need SQLITE_CONFIG_SERIALIZED, and that would add a lot of contention). Also, are your connections using private (SQLITE_OPEN_PRIVATECACHE) or shared cache (SQLITE_OPEN_SHAREDCACHE)? When using SQLITE_CONFIG_MULTITHREAD, taking care no application thread share a connection with another, and using SQLITE_OPEN_PRIVATECACHE, along with WAL journal mode, I see no issue similar to what you report in our own programming. -- Meilleures salutations, Met vriendelijke groeten, Best Regards, Olivier Mascia, integral.be/om ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Is it possible that dropping a big table takes very long
> Le 18 avr. 2016 ? 12:30, Dan Kennedy a ?crit :
>
>> I am playing a bit with SQLite. I first had a table with 1E8 elements. When
>> trying to drop this it looked like SQLite got hung. I tried it from DB
>> Browser and a Java program.
>> I just tried it with a table of 1E7 elements. That was dropped in about 13
>> seconds.
>> I will try it again with 1E8 elements, but it takes 4? hours to generated.
>> Is it possible that SQLite has trouble dropping very large tables? It was
>> 5.2 GB. With 1E7 elements the table is 512 MB.
>>
>> The definition of the table:
>> CREATE TABLE testUniqueUUID (
>> UUIDblob,
>>
>> PRIMARY KEY(UUID)
>> CHECK(TYPEOF(UUID) = 'blob' AND
>> LENGTH(UUID) = 16 AND
>> SUBSTR(HEX(UUID), 13, 1) == '4' AND
>> SUBSTR(HEX(UUID), 17, 1) IN ('8', '9', 'A', 'B')
>> )
>> );
>>
>
> Are there any other tables in the db with foreign keys that refer to the
> table being dropped?
>
> If so, try with foreign keys disabled:
>
> PRAGMA foreign_keys = 0;
>
> Dan.
>
Not sure if this was already discussed here this last days (I don't think so
for having quickly browsed this discussion), I would suggest to check these on
the filled-in DB before delete:
PRAGMA compile_options; (has SQLITE_SECURE_DELETE been set for compiling the
library?)
PRAGMA auto_vacuum; (is it set to 0 or else?)
--
Meilleures salutations, Met vriendelijke groeten, Best Regards,
Olivier Mascia, integral.be/om
[sqlite] Crash in shell on Windows with redirected stdout
Hi,
I'm getting a crash in the sqlite shell on Windows 7 in sqlite3 version 3.12.1.
I can reprduce it with the console commands
echo "SELECT 1;" > tmp.sql
sqlite3 :memory: -init tmp.sql >log
What seems to happen is that sqlite3_initialise is never called and
consequently the malloc function pointers (specifically
sqlite3GlobalConfig.m.xRoundup) are null.
Call stack for the crash:-
00 0044f0e4 00275700 0x0
01 0044f108 00241699 sqlite3!mallocWithAlarm(int n = 0n164, void ** pp =
0x0044f118)+0x10
[c:\pf\swgraphics\tools\extern_con\main\sqlite\src-split\sqlite3-1.c @ 6]
02 0044f11c 002416e1 sqlite3!sqlite3Malloc(unsigned int64 n = 0xa4)+0x49
[c:\pf\swgraphics\tools\extern_con\main\sqlite\src-split\sqlite3-1.c @ 22268]
03 0044f130 00276839 sqlite3!sqlite3MallocZero(unsigned int64 n = 0xa4)+0x11
[c:\pf\swgraphics\tools\extern_con\main\sqlite\src-split\sqlite3-1.c @ 22568]
04 0044f150 0027696f sqlite3!winMbcsToUnicode(char * zFilename = 0x006d6b50
"CREATE TABLE IF NOT EXISTS test_results ")+0x69
[c:\pf\swgraphics\tools\extern_con\main\sqlite\src-split\sqlite3-2.c @ 9682]
05 0044f164 00221ef3 sqlite3!sqlite3_win32_mbcs_to_utf8(char * zFilename =
0x006d6b50 "CREATE TABLE IF NOT EXISTS test_results ")+0xf
[c:\pf\swgraphics\tools\extern_con\main\sqlite\src-split\sqlite3-2.c @ 9732]
06 0044f184 00221f89 sqlite3!local_getline(char * zLine = 0x006d6b50 "CREATE
TABLE IF NOT EXISTS test_results ", struct _iobuf * in = 0x00325410)+0x123
[c:\pf\swgraphics\tools\extern_con\main\sqlite\src-split\shell.c @ 490]
07 0044f1a0 0022528c sqlite3!one_input_line(struct _iobuf * in = 0x00325410,
char * zPrior = 0x "", int isContinuation = 0n0)+0x19
[c:\pf\swgraphics\tools\extern_con\main\sqlite\src-split\shell.c @ 526]
08 0044f254 0022c715 sqlite3!process_input(struct ShellState * p = 0x0044f2f4,
struct _iobuf * in = 0x00325410)+0xbc
[c:\pf\swgraphics\tools\extern_con\main\sqlite\src-split\shell.c @ 4399]
09 0044f274 0022d167 sqlite3!process_sqliterc(struct ShellState * p =
0x0044f2f4, char * sqliterc_override = 0x006d7e88
"C:\pf\swgraphics\rogueddk\MAIN\tools\intern\usc2\perf.db.sql")+0xc5
[c:\pf\swgraphics\tools\extern_con\main\sqlite\src-split\shell.c @ 4593]
0a 0044f7c8 002e454d sqlite3!main(int argc = 0n6, char ** argv =
0x006d7e18)+0x7f7
[c:\pf\swgraphics\tools\extern_con\main\sqlite\src-split\shell.c @ 4875]
The crash only happens if the shell output is redirected to a file otherwise
the call sequence
process_sqliterc
->utf8_printf(stderr,"-- Loading resources from %s\n",sqliterc)
->sqlite3_vmprintf
->sqlite3_initialize
causes the malloc functions to be set up.
I fixed it with this patch
--- orig-src/sqlite-src-3120100/src/os_win.c2016-04-18 13:40:00.295629200
+0100
+++ sqlite-src-3120100/src/os_win.c 2016-04-18 13:35:52.358129200 +0100
@@ -1693,6 +1693,10 @@
LPWSTR zMbcsFilename;
int codepage = osAreFileApisANSI() ? CP_ACP : CP_OEMCP;
+ #ifndef SQLITE_OMIT_AUTOINIT
+ if( sqlite3_initialize() ) return 0;
+ #endif
+
nByte = osMultiByteToWideChar(codepage, 0, zFilename, -1, NULL,
0)*sizeof(WCHAR);
if( nByte==0 ){
[sqlite] UPDATE/open/close blocked while executing SELECT
Hi, I am using SQLite C amalgamation version 3.08.11.01 compiled for Multi Threading Mode and using WAL Journal Mode. Sometimes I need to execute a SELECT statement which takes a very long time to complete. I realized that during that time other functions are blocked for a significant amount of time, eventually they get through after few minutes, but then, another statement/function hangs. I have seen UPDATE statements blocking and also functions like sqlite3_prepare16_v2(), open/close calls for the same database. It is usually blocking at btreeLockCarefully(). Is there something I can do to avoid such long starvations or is that by design? Thank you very much, Detlef.
[sqlite] Pascal (almost) style BLOBs
You could use a binary PList or some other form of structured data - the BPList blob could then contain, ints, floats, strings byte arrays or even embedded binary plists. As it is a structured format then to an extent it is self documenting (in terms of the stored data types) Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite email from a work address for a fully functional demo licence On 17 April 2016 at 16:27, William Drago wrote: > On 4/17/2016 9:45 AM, Simon Slavin wrote: >> >> On 17 Apr 2016, at 1:02pm, William Drago wrote: >> >>> I am forced to consider this approach because my function will be >>> receiving blobs but will have no idea what those blobs are. >> >> Why do you care ? A BLOB is a BLOB. It doesn't matter whether it's an >> integer, a string, a car, a peace treaty or the smell of newly cut grass. >> You store it when told to and retrieve it when told to. > > > My job is to find all the blob columns and unblob them into arrays of > whatever type they originally were before they were blob'd. > >>> if this "feature" is not clearly documented someone in the future will >>> have a very hard time figuring out why the blobs don't make sense. >> >> BLOBs don't make sense. They're opaque. You are not meant to understand >> anything about what's in a BLOB. That's for the layer of your software >> which packs the information into a blob or unpacks the blob back into >> information. > > > Understood. I'm writing the code that does the packing and unpacking. The > users are designing their own databases. When they store for example, an > array of 16 bit integers, or an array of 64 bit floats, I want to give them > the same thing back when they query the database not just an array of bytes > that they have to decode themselves. > > Since I have control over how the blobs are generated, I can easily add a > type identifier to each blob so that I know how to unblob it. > > -Bill > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Is it possible that dropping a big table takes very long
On 18 April 2016 at 06:55, Cecil Westerhof wrote: > ?I put a strace on it. This was what I got: > Process 26455 attached with 20 threads > % time seconds usecs/call callserrors syscall > -- --- --- - - > 99.80 11245.498406 42527264435130887 futex > 0.09 10.48 349 3 fsync > 0.088.886784 0 39275508 read > 0.022.552284 0 16397440 write > 0.000.367716 0 50708676 lseek > -- --- --- - - > 100.00 11267.833632 106646970130895 total > ? > > ?Pretty damaging for futex: almost half of the calls went wrong and 99.8% > of the time is spend there?. > > ?I have some finding out to do.? I'm not sure this is damning for futex. You said you are not creating threads yourself, which means you probably only have one I/O bound thread actually doing anything. The other 19 threads are presumably spawned by the APIs you are using and are likely just waiting for something to do. jstack will give you details on each thread in the JVM so you can see what they are up to. It's not like the futex calls are wasting CPU (in this case) - the threads are idle until the kernel wakes them up. -Rowan
[sqlite] sqlite3 update hook data
On 4/18/16, Martin Trnovec wrote: > > Do you know if there is a way how to get n-th column of row during > sqlite3_update_hook callback ? I saw there are (in experimental version) > some functions like sqlite3_preupdate_new that are able to get this > information during sqlite3_preupdate_hook but this is not in stable > version. That is only available in the preupdate hook, which is only available when compiling with the SQLITE_ENABLE_PREUPDATE_HOOK compile time option, and then only on the "sessions" branch, which as you have observed, was recently merged into trunk. The next schedule release is 3.13.0 which is due out in May sometime. See https://www.sqlite.org/draft/releaselog/3_13_0.html > > A by the way do anybody have any info when sqlite3_preupdate_hook will > be available in the stable version cause this feature seems to be in the > code since 2011 ;) ? > -- D. Richard Hipp drh at sqlite.org
[sqlite] Is it possible that dropping a big table takes very long
2016-04-17 18:23 GMT+02:00 Simon Slavin : > > On 17 Apr 2016, at 5:13pm, Cecil Westerhof wrote: > > > Or is this normal > > in Java programs? > > Yes. You're discovering that Java is rubbish. 'futex' for Java is what > most environments call 'mutex' and Java doesn't do locking well. It's this > which is causing your delays, not SQLite or anything around it. > > Google 'java futex' if you want details, but there's little point since > you can't do much about it. Just stay updated to the latest OS and the > latest Java available to you and hope someone figured it out. > ?I put a strace on it. This was what I got: Process 26455 attached with 20 threads % time seconds usecs/call callserrors syscall -- --- --- - - 99.80 11245.498406 42527264435130887 futex 0.09 10.48 349 3 fsync 0.088.886784 0 39275508 read 0.022.552284 0 16397440 write 0.000.367716 0 50708676 lseek 0.000.0480008000 6 6 restart_syscall 0.000.000442 1 737 sched_yield 0.000.00 0 2 close 0.000.00 0 1 stat 0.000.00 0 4 fstat 0.000.00 0 4 mmap 0.000.00 0 128 mprotect 0.000.00 0 6 rt_sigprocmask 0.000.00 0 1 rt_sigreturn 0.000.00 0 2 2 access 0.000.00 0 4 madvise 0.000.00 0 7 fcntl 0.000.00 0 3 unlink 0.000.00 0 1 gettid 0.000.00 0 2 sched_getaffinity -- --- --- - - 100.00 11267.833632 106646970130895 total ? ?Pretty damaging for futex: almost half of the calls went wrong and 99.8% of the time is spend there?. ?I have some finding out to do.? -- Cecil Westerhof
[sqlite] Is it possible that dropping a big table takes very long
2016-04-17 21:59 GMT+02:00 Scott Robison : > On Sun, Apr 17, 2016 at 1:44 PM, Cecil Westerhof > wrote: > > > 2016-04-17 18:13 GMT+02:00 Cecil Westerhof : > > > > > 2016-04-17 17:13 GMT+02:00 Keith Medcalf : > > > The strange thing is that the blob variant takes a lot of time now > also. > > > First it took only 4? hour, now it is already busy for eight hours and > > only > > > has come to 8.9E7. > > > > > > 14:36:01: Inserted8.40e+07 UUID's > > > 14:54:47: Inserted8.50e+07 UUID's > > > 15:30:19: Inserted8.60e+07 UUID's > > > 15:54:02: Inserted8.70e+07 UUID's > > > 16:17:01: Inserted8.80e+07 UUID's > > > 17:24:20: Inserted8.90e+07 UUID's > > > > > > > ?It turned out that it is a combination of factors. Firefox sometimes > takes > > a lot of resources, so I decided to quit Firefox. And voila:? > > 17:24:20: Inserted8.90e+07 UUID's > > 18:28:02: Inserted9.00e+07 UUID's > > 19:24:13: Inserted9.10e+07 UUID's > > 19:36:41: Inserted9.20e+07 UUID's > > 19:42:18: Inserted9.30e+07 UUID's > > 19:47:46: Inserted9.40e+07 UUID's > > 19:52:43: Inserted9.50e+07 UUID's > > 19:57:50: Inserted9.60e+07 UUID's > > 20:02:36: Inserted9.70e+07 UUID's > > 20:07:29: Inserted9.80e+07 UUID's > > 20:12:17: Inserted9.90e+07 UUID's > > 20:16:59: Inserted1.00e+08 UUID's > > > > I should continue this on a Linux/Java newsgroup. But I thought it was > > interesting to know. > > > > It can be hard to accurately measure performance on a modern multitasking > multiuser system. As you've observed, external processes can have a huge > impact of the "independent and isolated" process. Whenever I'm trying to > measure performance, I close all other applications, maybe disconnect from > the network, turn off services. Depending on just how accurate I want to be > with the measurement (not all measurements are as important / picky). > ?That I know, but I was only going for big O. I really did not expect differences like I am getting now. Well, I learned something.? > It's entirely possible that the table drop is related to something similar > to this. > ?I can give positive news about this. In DB Browser it now took 20 seconds. The strange thing is that Revert Changes took much longer. I did not expect this, so sadly I do not have timing for this. The Java program took 2 minutes and 5 seconds. But this was with Firefox running. (I have some things to do.) Inserting goes a lot faster: 21:56:59: Going to create 1.00e+08 random UUID's 21:57:26: Inserted1.00e+06 UUID's 21:58:06: Inserted2.00e+06 UUID's 21:58:51: Inserted3.00e+06 UUID's 21:59:40: Inserted4.00e+06 UUID's 22:00:31: Inserted5.00e+06 UUID's 22:01:32: Inserted6.00e+06 UUID's 22:02:37: Inserted7.00e+06 UUID's 22:03:47: Inserted8.00e+06 UUID's 22:04:52: Inserted9.00e+06 UUID's 22:06:20: Inserted1.00e+07 UUID's 22:07:58: Inserted1.10e+07 UUID's 22:09:35: Inserted1.20e+07 UUID's 22:11:18: Inserted1.30e+07 UUID's But it is possible that this has to do with the commits I do. Just before displaying I do a commit, instead of after all the work is done. Using the sqlite3 executable took 2 minutes. That makes the 20 seconds of DB Browser a little strange. Well, I have to do everything over this week on a system that is not doing anything else. -- Cecil Westerhof
