[sqlite] Creating a stable database

[R.Smith]

On 2015-09-25 08:46 PM, Warren Young wrote:
> On Sep 25, 2015, at 11:59 AM, Richard Hipp  wrote:
>> On 9/25/15, Aaron Digulla  wrote:
>>> I was wondering if it was possible to create the same database (= no binary
>>> difference) twice with sqlite.
>> It works fine to create identical database files when I try it:
>>
>>drh at bella:~/sqlite/bld$ ./sqlite3 db1 >drh at bella:~/sqlite/bld$ ./sqlite3 db2  That suggests a good test for Aaron, then:
>
>  sqlite3 my.db .dump > dump.sql
>  sqlite3 a.db < dump.sql
>  sqlite3 b.db < dump.sql

Note that the above can only be expected to work if there are no inserts 
using formulas like Random() or Date-Time based functions relative to 
the current time etc.

What I have done in the past is use the backup api to write out two 
copies of the DB, though this will only work if there are no other 
connections altering the database in between.

HTH,
Ryan

[sqlite] Creating a stable database

[Simon Slavin]

On 25 Sep 2015, at 5:04pm, Aaron Digulla  wrote:

> I was wondering if it was possible to create the same database (= no binary 
> difference) twice with sqlite.

Everything in a SQLite database is either structure (which we tend to call 
'schema' here) or data.  (For the same of argument we'll consider triggers and 
foreign keys to be structure.)

You can guarantee that the databases have the same structure and data in.  A 
trivial way to do this is to use the SQLite shell tool to do a '.dump' and then 
a '.read'.  However you can't guarantee that the database files are 
byte-for-byte identical.  If you want identical files you have to use a file 
API not a database API.

SQLite does include an API called the backup API:



My understanding of this is that it copies the database file block by block, 
without any understanding of what is in the blocks.  If this is true then it's 
a file API and will result in an identical file.

Simon.

[sqlite] Creating a stable database

[Stephan Mueller]

> On 9/25/15, Aaron Digulla  wrote:
>> 
>> I was wondering if it was possible to create the same database (= no binary
>> difference) twice with sqlite.

I trust the output of sqldiff, usually with the --primarykey option, to tell me 
two dbs are equivalent (subject to the caveats listed in 
http://sqlite.org/sqldiff.html).  Great tool -- thanks DRH and other SQLiters!  
Understood this doesn't directly answer the original question, and I can easily 
imagine cases where identical dbs are needed, but this discussion reminded me 
of sqldiff, and this aside: it'd be greatly appreciated (and maybe increase 
uptake?) if sqldiff binaries were available on the 
http://sqlite.org/download.html page along with the other tools.

stephan($0.02);

[sqlite] Creating a stable database

[Aaron Digulla]

Hello,

I was wondering if it was possible to create the same database (= no binary 
difference) twice with sqlite.

I tried by creating all tables in the same order and then inserting all rows in 
order of the primary key. But if I create two database files with the same 
code, the files are different (I checked with cmp(1) on Linux).

Is there a way to create database files which only change when the data inside 
changes?

I'm using sqlite 3.8.10.1

-- 
Aaron "Optimizer" Digulla a.k.a. Philmann Dark
"It's not the universe that's limited, it's our imagination.
Follow me and I'll show you something beyond the limits." 
http://blog.pdark.de/

[sqlite] Query containing correlated subquery gets "stuck"

[Clemens Ladisch]

gunnar wrote:
> What do you exactly mean with "But in any case, as others have already
> said, it is not possible for a write transaction to lock out a read
> transaction _in the middle_."? I do see that records are being inserted
> while I made those stack traces.

The inserted records are appended to the write-ahead-log.  Any read-only
transaction started previously ignores them, and continues to read the
old data.

Once a transaction has started, it is guaranteed to be able to read
everything in the database.

> I have a fifteen minute window / 24hours, is it enough for VACUUM?

Try it with a copy.  (Anyway, VACUUM uses a proper transaction, so the
worst that can happen is that the other processes run into their
timeouts.)


Another possibility for the slowdown might be a huge WAL file.
Try "PRAGMA wal_checkpoint(TRUNCATE)", but this might be as slow as
a VACUUM.


Regards,
Clemens

[sqlite] Query containing correlated subquery gets "stuck"

[gunnar]

Hi Clemens,

We checked and there are no faulty sectors.

I do have some information on the disks, both are SSD's. Supposed that 
it is a disk problem, then the problematic and non problematic ones are 
as below (not sure if this then still has to be in the sqlite mailing 
list).
I'm also going to try the query tonight in the fifteen minutes that 
we're not inserting records into the table.

Thanks!
Gunnar

The problematic one:

/dev/sdb:

ATA device, with non-removable media
 Model Number:   INTEL SSDSC2BB240G4
 Serial Number:  BTWL342202LD240NGN
 Firmware Revision:  D2010355
 Media Serial Num:
 Media Manufacturer:
 Transport:  Serial, ATA8-AST, SATA 1.0a, SATA II 
Extensions, SATA Rev 2.5, SATA Rev 2.6
Standards:
 Used: unknown (minor revision code 0x0029)
 Supported: 8 7 6 5
 Likely used: 8
Configuration:
 Logicalmaxcurrent
 cylinders1638316383
 heads1616
 sectors/track6363
 --
 CHS current addressable sectors:   16514064
 LBAuser addressable sectors:  268435455
 LBA48  user addressable sectors:  468862128
 Logical  Sector size:   512 bytes
 Physical Sector size:   512 bytes
 Logical Sector-0 offset:  0 bytes
 device size with M = 1024*1024:  228936 MBytes
 device size with M = 1000*1000:  240057 MBytes (240 GB)
 cache/buffer size  = unknown
 Form Factor: 2.5 inch
 Nominal Media Rotation Rate: Solid State Device
Capabilities:
 LBA, IORDY(can be disabled)
 Queue depth: 32
 Standby timer values: spec'd by Standard, no device specific minimum
 R/W multiple sector transfer: Max = 1Current = 1
 DMA: mdma0 mdma1 mdma2 udma0 udma1 udma2 udma3 udma4 udma5 *udma6
  Cycle time: min=120ns recommended=120ns
 PIO: pio0 pio1 pio2 pio3 pio4
  Cycle time: no flow control=120ns  IORDY flow control=120ns
Commands/features:
 EnabledSupported:
*SMART feature set
 Security Mode feature set
*Power Management feature set
*Write cache
*Look-ahead
*Host Protected Area feature set
*WRITE_BUFFER command
*READ_BUFFER command
*NOP cmd
*DOWNLOAD_MICROCODE
 SET_MAX security extension
*48-bit Address feature set
*Mandatory FLUSH_CACHE
*FLUSH_CACHE_EXT
*SMART error logging
*SMART self-test
*General Purpose Logging feature set
*WRITE_{DMA|MULTIPLE}_FUA_EXT
*64-bit World wide name
*IDLE_IMMEDIATE with UNLOAD
*WRITE_UNCORRECTABLE_EXT command
*{READ,WRITE}_DMA_EXT_GPL commands
*Segmented DOWNLOAD_MICROCODE
 unknown 119[6]
*Gen1 signaling speed (1.5Gb/s)
*Gen2 signaling speed (3.0Gb/s)
*unknown 76[3]
*Native Command Queueing (NCQ)
*Phy event counters
*unknown 76[15]
*Software settings preservation
*SMART Command Transport (SCT) feature set
*SCT LBA Segment Access (AC2)
*SCT Error Recovery Control (AC3)
*SCT Features Control (AC4)
*SCT Data Tables (AC5)
*Data Set Management TRIM supported
*reserved 69[4]
*reserved 69[5]
*Deterministic read after TRIM
Security:
 Master password revision code = 65534
 supported
 notenabled
 notlocked
 notfrozen
 notexpired: security count
 supported: enhanced erase
 2min for SECURITY ERASE UNIT. 2min for ENHANCED SECURITY ERASE UNIT.
Logical Unit WWN Device Identifier: 55cd2e404b4ee650
 NAA: 5
 IEEE OUI: 5cd2e4
 Unique ID: 04b4ee650
Checksum: correct




And the not problematic one:

/dev/sdb:

ATA device, with non-removable media
 Model Number:   INTEL SSDSC2BB300G4
 Serial Number:  BTWL3315087F300PGN
 Firmware Revision:  D2010355
 Media Serial Num:
 Media Manufacturer:
 Transport:  Serial, ATA8-AST, SATA 1.0a, SATA II 
Extensions, SATA Rev 2.5, SATA Rev 2.6
Standards:
 Used: unknown (minor revision code 0x0029)
 Supported: 8 7 6 5
 Likely used: 8
Configuration:
 Logicalmaxcurrent
 cylinders1638316383
 heads1616
 sectors/track6363
 --
 CHS current addressable sectors:   16514064
 LBAuser addressable sectors:  268435455
 LBA48  user addressable sectors:  586072368
 Logical  Sector size:   512 bytes
 Physical Sector size:   512 bytes
 Logical Sector-0 offset:  0 bytes
 device size with M = 1024*1024:  286168 MBytes
 device size with M = 1000*1000:  300069 MBytes (300 GB)
 cache/buffer size  

[sqlite] Query containing correlated subquery gets "stuck"

[gunnar]

typo: should have been "around 700MB and 500MB"


  On 09/25/2015 03:58 PM, gunnar wrote:
> Hi Clemens,
>
> Here are some of the settings and the integrity check that we always 
> prints at start up of our process:
>
> [query:PRAGMA synchronous=OFF][changes:0][total changes:0]
> [query:PRAGMA foreign_keys=ON][changes:0][total changes:0]
> [query:PRAGMA cache_size=1][changes:0][total changes:0]
> [query:PRAGMA journal_mode=WAL][changes:0][total changes:0]
> OrderCallbackStorage::checkDatabaseIntegrity: row 1 [ok]
>
>
> No virtualization and also no network file system.
>
> What do you exactly mean with "But in any case, as others have already 
> said, it is not possible for a write transaction to lock out a read 
> transaction _in the middle_."? I do see that records are being 
> inserted while I made those stack traces.
>
> I have a fifteen minute window / 24hours, is it enough for VACUUM? the 
> database file and wal file are at the moment around 700KiB and 500KiB 
> resp. Probably that can't be answered and I should just try it.
>
> We'll check the disk for bad sector(s).
>
>
> Thanks for your help!
> Gunnar
>
>
>
> On 09/25/2015 03:40 PM, Clemens Ladisch wrote:
>> gunnar wrote:
>>> (select uuid from session where date = (select max(date) from session))
>> This can be optimized to
>>(select uuid from session order by date desc limit 1)
>> but the speed of this subquery does not matter.
>>
>>> (SELECT max(cb_seq_num) FROM ordercallback WHERE 
>>> server_order_id=cb.server_order_id AND sessionuuid=cb.sessionuuid 
>>> AND working=1)
>>> 3|0|0|SEARCH TABLE ordercallback USING INDEX ordercallback_index3 
>>> (server_order_id=? AND sessionuuid=? AND working=?)
>> You could try to speed this up with a covering index by adding the
>> cb_seq_num column to the index.
>>
>>
>> But in any case, as others have already said, it is not possible for
>> a write transaction to lock out a read transaction _in the middle_.
>>
>> Are you using WAL? Some network file system? Virtualization?
>>
>> If neither the CPU nor the disk are busy, but SQLite is not sleeping,
>> then what is it waiting for?  This sounds like a defective disk sector.
>>
>> Try running "PRAGMA integrity_check" on (a copy of) the DB.
>> Try VACUUM.
>>
>>
>> Regards,
>> Clemens
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Query containing correlated subquery gets "stuck"

[gunnar]

Hi Clemens,

Here are some of the settings and the integrity check that we always 
prints at start up of our process:

[query:PRAGMA synchronous=OFF][changes:0][total changes:0]
[query:PRAGMA foreign_keys=ON][changes:0][total changes:0]
[query:PRAGMA cache_size=1][changes:0][total changes:0]
[query:PRAGMA journal_mode=WAL][changes:0][total changes:0]
OrderCallbackStorage::checkDatabaseIntegrity: row 1 [ok]


No virtualization and also no network file system.

What do you exactly mean with "But in any case, as others have already 
said, it is not possible for a write transaction to lock out a read 
transaction _in the middle_."? I do see that records are being inserted 
while I made those stack traces.

I have a fifteen minute window / 24hours, is it enough for VACUUM? the 
database file and wal file are at the moment around 700KiB and 500KiB 
resp. Probably that can't be answered and I should just try it.

We'll check the disk for bad sector(s).


Thanks for your help!
Gunnar



On 09/25/2015 03:40 PM, Clemens Ladisch wrote:
> gunnar wrote:
>> (select uuid from session where date = (select max(date) from session))
> This can be optimized to
>(select uuid from session order by date desc limit 1)
> but the speed of this subquery does not matter.
>
>> (SELECT max(cb_seq_num) FROM ordercallback WHERE 
>> server_order_id=cb.server_order_id AND  sessionuuid=cb.sessionuuid AND 
>> working=1)
>> 3|0|0|SEARCH TABLE ordercallback USING INDEX ordercallback_index3 
>> (server_order_id=? AND sessionuuid=? AND working=?)
> You could try to speed this up with a covering index by adding the
> cb_seq_num column to the index.
>
>
> But in any case, as others have already said, it is not possible for
> a write transaction to lock out a read transaction _in the middle_.
>
> Are you using WAL? Some network file system? Virtualization?
>
> If neither the CPU nor the disk are busy, but SQLite is not sleeping,
> then what is it waiting for?  This sounds like a defective disk sector.
>
> Try running "PRAGMA integrity_check" on (a copy of) the DB.
> Try VACUUM.
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Query containing correlated subquery gets "stuck"

[Clemens Ladisch]

gunnar wrote:
> (select uuid from session where date = (select max(date) from session))

This can be optimized to
  (select uuid from session order by date desc limit 1)
but the speed of this subquery does not matter.

> (SELECT max(cb_seq_num) FROM ordercallback WHERE 
> server_order_id=cb.server_order_id AND  sessionuuid=cb.sessionuuid AND 
> working=1)
> 3|0|0|SEARCH TABLE ordercallback USING INDEX ordercallback_index3 
> (server_order_id=? AND sessionuuid=? AND working=?)

You could try to speed this up with a covering index by adding the
cb_seq_num column to the index.


But in any case, as others have already said, it is not possible for
a write transaction to lock out a read transaction _in the middle_.

Are you using WAL? Some network file system? Virtualization?

If neither the CPU nor the disk are busy, but SQLite is not sleeping,
then what is it waiting for?  This sounds like a defective disk sector.

Try running "PRAGMA integrity_check" on (a copy of) the DB.
Try VACUUM.


Regards,
Clemens

[sqlite] Query containing correlated subquery gets "stuck"

[gunnar]

Hi Clemens,

See below..



sqlite> EXPLAIN QUERY PLAN SELECT count(*) FROM ordercallback cb WHERE 
sessionuuid=(select uuid from session where date = (select max(date) 
from session)) AND endstate=0 AND working=1 AND cb_seq_num = (SELECT 
max(cb_seq_num) FROM ordercallback WHERE 
server_order_id=cb.server_order_id AND  sessionuuid=cb.sessionuuid AND 
working=1);
0|0|0|SEARCH TABLE ordercallback AS cb USING INDEX ordercallback_index5 
(sessionuuid=? AND endstate=? AND working=?)
0|0|0|EXECUTE SCALAR SUBQUERY 1
1|0|0|SEARCH TABLE session USING INDEX sqlite_autoindex_session_2 (date=?)
1|0|0|EXECUTE SCALAR SUBQUERY 2
2|0|0|SEARCH TABLE session USING COVERING INDEX sqlite_autoindex_session_2
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 3
3|0|0|SEARCH TABLE ordercallback USING INDEX ordercallback_index3 
(server_order_id=? AND sessionuuid=? AND working=?)

The cpu's are not very busy:

top - 14:52:57 up 481 days, 18:07,  5 users,  load average: 0.32, 0.36, 0.32
Tasks: 769 total,   1 running, 761 sleeping,   0 stopped,   7 zombie
Cpu0  : 18.8%us,  7.0%sy,  0.0%ni, 64.1%id,  0.0%wa,  0.0%hi, 10.1%si,  
0.0%st
Cpu1  :  8.8%us,  3.4%sy,  0.0%ni, 87.5%id,  0.0%wa,  0.0%hi, 0.3%si,  
0.0%st
Cpu2  :  9.5%us,  4.1%sy,  0.0%ni, 86.1%id,  0.0%wa,  0.0%hi, 0.3%si,  
0.0%st
Cpu3  :  7.4%us,  3.0%sy,  0.0%ni, 89.6%id,  0.0%wa,  0.0%hi, 0.0%si,  
0.0%st
Cpu4  :  5.0%us,  3.0%sy,  0.0%ni, 91.6%id,  0.0%wa,  0.0%hi, 0.3%si,  
0.0%st
Cpu5  :  6.7%us,  2.0%sy,  0.0%ni, 91.2%id,  0.0%wa,  0.0%hi, 0.0%si,  
0.0%st
Cpu6  :  3.4%us,  1.0%sy,  0.0%ni, 95.6%id,  0.0%wa,  0.0%hi, 0.0%si,  
0.0%st
Cpu7  :  8.3%us,  1.3%sy,  0.0%ni, 90.3%id,  0.0%wa,  0.0%hi, 0.0%si,  
0.0%st
Cpu8  :  7.9%us,  2.7%sy,  0.0%ni, 89.4%id,  0.0%wa,  0.0%hi, 0.0%si,  
0.0%st
Cpu9  :  8.4%us,  2.7%sy,  0.0%ni, 88.6%id,  0.0%wa,  0.0%hi, 0.3%si,  
0.0%st
Cpu10 : 10.1%us,  2.4%sy,  0.0%ni, 87.2%id,  0.0%wa,  0.0%hi, 0.3%si,  
0.0%st
Cpu11 :  4.0%us,  2.0%sy,  0.0%ni, 94.0%id,  0.0%wa,  0.0%hi, 0.0%si,  
0.0%st
Cpu12 :  6.4%us,  2.0%sy,  0.0%ni, 91.6%id,  0.0%wa,  0.0%hi, 0.0%si,  
0.0%st
Cpu13 :  2.3%us,  1.3%sy,  0.0%ni, 96.0%id,  0.0%wa,  0.0%hi, 0.3%si,  
0.0%st
Cpu14 :  2.0%us,  1.0%sy,  0.0%ni, 97.0%id,  0.0%wa,  0.0%hi, 0.0%si,  
0.0%st
Cpu15 :  1.7%us,  0.7%sy,  0.0%ni, 97.7%id,  0.0%wa,  0.0%hi, 0.0%si,  
0.0%st
Cpu16 : 10.9%us,  6.0%sy,  0.0%ni, 83.1%id,  0.0%wa,  0.0%hi, 0.0%si,  
0.0%st
Cpu17 :  8.6%us,  1.7%sy,  0.0%ni, 89.4%id,  0.0%wa,  0.0%hi, 0.3%si,  
0.0%st
Cpu18 :  3.3%us,  1.7%sy,  0.0%ni, 95.0%id,  0.0%wa,  0.0%hi, 0.0%si,  
0.0%st
Cpu19 :  1.0%us,  1.0%sy,  0.0%ni, 98.0%id,  0.0%wa,  0.0%hi, 0.0%si,  
0.0%st
Cpu20 :  0.7%us,  0.7%sy,  0.0%ni, 98.7%id,  0.0%wa,  0.0%hi, 0.0%si,  
0.0%st
Cpu21 :  1.7%us,  0.3%sy,  0.0%ni, 98.0%id,  0.0%wa,  0.0%hi, 0.0%si,  
0.0%st
Cpu22 :  0.7%us,  0.3%sy,  0.0%ni, 99.0%id,  0.0%wa,  0.0%hi, 0.0%si,  
0.0%st
Cpu23 :  0.3%us,  0.3%sy,  0.0%ni, 99.3%id,  0.0%wa,  0.0%hi, 0.0%si,  
0.0%st
Cpu24 :  3.7%us,  1.3%sy,  0.0%ni, 95.0%id,  0.0%wa,  0.0%hi, 0.0%si,  
0.0%st
Cpu25 :  4.6%us,  1.3%sy,  0.0%ni, 94.0%id,  0.0%wa,  0.0%hi, 0.0%si,  
0.0%st
Cpu26 :  0.7%us,  0.7%sy,  0.0%ni, 98.7%id,  0.0%wa,  0.0%hi, 0.0%si,  
0.0%st
Cpu27 :  1.0%us,  0.7%sy,  0.0%ni, 98.3%id,  0.0%wa,  0.0%hi, 0.0%si,  
0.0%st
Cpu28 :  1.3%us,  0.3%sy,  0.0%ni, 98.3%id,  0.0%wa,  0.0%hi, 0.0%si,  
0.0%st
Cpu29 :  2.6%us,  0.7%sy,  0.0%ni, 96.7%id,  0.0%wa,  0.0%hi, 0.0%si,  
0.0%st
Cpu30 :  1.7%us,  0.0%sy,  0.0%ni, 98.3%id,  0.0%wa,  0.0%hi, 0.0%si,  
0.0%st
Cpu31 :  0.7%us,  0.7%sy,  0.0%ni, 98.7%id,  0.0%wa,  0.0%hi, 0.0%si,  
0.0%st
Mem:  32787924k total, 22357144k used, 10430780k free,52244k buffers
Swap: 16465912k total,38740k used, 16427172k free,  5721572k cached



And below is on the machine where the number of inserts is much higher 
but where the query _does_ always return (and also always quite fast). 
As you see the cpu's are more busy and the query plan is the same.

sqlite> EXPLAIN QUERY PLAN SELECT count(*) FROM ordercallback cb WHERE 
sessionuuid=(select uuid from session where date = (select max(date) 
from session)) AND endstate=0 AND working=1 AND cb_seq_num = (SELECT 
max(cb_seq_num) FROM ordercallback WHERE 
server_order_id=cb.server_order_id AND  sessionuuid=cb.sessionuuid AND 
working=1);
0|0|0|SEARCH TABLE ordercallback AS cb USING INDEX ordercallback_index5 
(sessionuuid=? AND endstate=? AND working=?)
0|0|0|EXECUTE SCALAR SUBQUERY 1
1|0|0|SEARCH TABLE session USING INDEX sqlite_autoindex_session_2 (date=?)
1|0|0|EXECUTE SCALAR SUBQUERY 2
2|0|0|SEARCH TABLE session USING COVERING INDEX sqlite_autoindex_session_2
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 3
3|0|0|SEARCH TABLE ordercallback USING INDEX ordercallback_index3 
(server_order_id=? AND sessionuuid=? AND working=?)


top - 14:59:00 up 186 days, 22:28,  7 users,  load average: 2.55, 2.24, 2.18
Tasks: 433 total,   1 running, 420 sleeping,   0 stopped,  12 zombie
Cpu0  : 16.0%us,  6.8%sy,  0.0%ni, 73.7%id,  0.0%wa,  0.0%hi, 3.4%si,  
0.0%st
Cpu1  : 

[sqlite] Creating a stable database

[Richard Hipp]

On 9/25/15, Aaron Digulla  wrote:
> Hello,
>
> I was wondering if it was possible to create the same database (= no binary
> difference) twice with sqlite.
>
> I tried by creating all tables in the same order and then inserting all rows
> in order of the primary key. But if I create two database files with the
> same code, the files are different (I checked with cmp(1) on Linux).

I suppose that depends on what commands you are doing to create your
database.  It works fine to create identical database files when I try
it:

   drh at bella:~/sqlite/bld$ ./sqlite3 db1 
> Is there a way to create database files which only change when the data
> inside changes?
>
> I'm using sqlite 3.8.10.1
>
> --
> Aaron "Optimizer" Digulla a.k.a. Philmann Dark
> "It's not the universe that's limited, it's our imagination.
> Follow me and I'll show you something beyond the limits."
> http://blog.pdark.de/
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Detect if db is already opened by another process?

[Michael Schlenker]

Am 23.09.2015 um 18:09 schrieb Richard Hipp:
> On 9/23/15, Michael Schlenker  wrote:
>> Hi,
>>
>> i just wondered if there is an API to detect if a sqlite database file
>> is already opened by another process.
> 
> Maybe try to change in or out of WAL mode?  That only works if there
> is a single connection to the database file.
> 
Good idea. Yes, seems to work nicely for my usecase.

Thank you,
   Michael

-- 
Michael Schlenker
Senior Software Engineer

CONTACT Software GmbH   Tel.:   +49 (421) 20153-80
Wiener Stra?e 1-3   Fax:+49 (421) 20153-41
28359 Bremen
E-Mail: michael.schlenker at contact-software.com
http://www.contact-software.com/

Registered office: Bremen, Germany
Managing directors: Karl Heinz Zachries, Ralf Holtgrefe
Court of register: Amtsgericht Bremen HRB 1321

[sqlite] Any database unique ID across multiple connections ?

[Wade, William]

It depends a lot on what kind of duplications you are concerned with. For 
instance, a file-system copy would, presumably, have copied any "unique 
identifier" that may have been in the file, so it will no longer be unique. To 
detect that kind of duplication, I think you really need support at the VFS 
level.

It sounds like you believe that you have a file-system path to both databases. 
If that is the case, you can probably find a way to put a temporary 
"fingerprint" on one file that is unlikely to be "copied" to another file. For 
instance on windows (at least with a situation where LockFileEx() works as 
advertised) I believe you could:

1) Open the first file (read-only works on windows)
2) Select 129 byte addresses that are unlikely to be in use (somewhere near the 
peta-byte address. On windows the file doesn't have to be that big, and you 
wouldn't want to use this if it was.
3) Get an exclusive lock on one of those bytes to indicate that you are doing a 
fingerprint test.
4) Generate a GUID, and come up with a mapping from each bit of the guid to one 
of the other 128 bytes.
5) For each "set" bit of the GUID, gain an exclusive lock on the corresponding 
byte of the file.
6) For each "unset" bit of the GUID, gain a shared lock on the corresponding 
byte of the file.
7) Open the second file.
8) Attempt (non blocking) to gain exclusive and shared locks on the same 129 
byte addresses in the second file.
9) If any attempt to gain an exclusive lock (2nd file) succeeds, the second 
file is different from the first file.
10) If any attempt to gain a shared lock (2nd file) succeeds where the first 
file has an exclusive lock, the files are different.
11) If any attempt to gain a shared lock (2nd file) fails, where the first file 
has a shared lock, the files are different.
12) Otherwise, the files are probably the same.
13) Release all of your locks.

This could fail if somebody (possibly malicious) writes and uses a 
copy-lock-pattern tool. Something in the first six steps could fail if somebody 
else was using those bytes for some reason, but if the first six steps succeed, 
it seems unlikely that the remaining steps would "accidently" fail if the 
underlying file-system and GUID generation are robust.

You could use fewer locks in a larger region to represent a guid (for instance, 
instead of mapping 128 GUID bits to 128 file bytes, map 16 GUID bytes to 16x256 
file bytes).

Regards,
Bill

-Original Message-
From: ALBERT Aur?lien [mailto:aurelien.alb...@alyotech.fr]
Sent: Thursday, September 24, 2015 4:42 AM
To: 'sqlite-users at mailinglists.sqlite.org'
Subject: [sqlite] Any database unique ID across multiple connections ?

Hi,

I'm using SQLite C API and my application use multiple databases, each database 
having multiple connections.

I need to identify which connection use the same database as another connection.

For the moment, I use the absolute database filename as an "unique database 
identifier", and I store this information with my "sqlite3*" connection handle.

Is there any simpler/safer way to get a unique database identifier across 
multiple connections ? (for example, if one day I need the same about 
":memory:" databases, bad things are gonna to happen)

Maybe there is already a database identifier in the SQLite API and I missed it ?

Thanks.



**
This e-mail and any attachments thereto may contain confidential information 
and/or information protected by intellectual property rights for the exclusive 
attention of the intended addressees named above. If you have received this 
transmission in error, please immediately notify the sender by return e-mail 
and delete this message and its attachments. Unauthorized use, copying or 
further full or partial distribution of this e-mail or its contents is 
prohibited.
**

[sqlite] Query containing correlated subquery gets "stuck"

[Clemens Ladisch]

gunnar wrote:
> the sqlite client is stuck with the following stack traces

According to these stack traces, the client is not stuck but is
busy searching and reading data from the database.

What is the EXPLAIN QUERY PLAN output for this query?

> Disk is not busy

And the CPU?


Regards,
Clemens

[sqlite] The Simplest SQLite Common Table Expression Tutorial

[David Barrett]

Hey all, just wanted to share this in case anybody is also looking for a
very simple tutorial for CTE's in sqlite:

http://blog.expensify.com/2015/09/25/the-simplest-sqlite-common-table-expression-tutorial/

The Simplest SQLite Common Table Expression Tutorial

I?ve been trying to wrap my head aroundCommon Table Expressions
 for a while, and all the tutorials
I?ve read started out with ?simple? examples that were way too advanced for
me to follow. Here?s my attempt to write a tutorial that starts as simple
as possible.

First, let?s start with the simplest query:

sqlite> SELECT 1;
1
sqlite>

All this does is return a result set containing a row. Next, consider the
simplest subquery:

sqlite> SELECT * FROM ( SELECT 1 );
1
sqlite>

This just selects all the results from the subquery ? which in this case,
is just a single row. A ?Common Table Expression? is basically the same as
a subquery, except assigned a name and defined prior to the query in which
it?s referenced. Accordingly, the simplest CTE version of the above query
would be like:

sqlite> WITH one AS ( SELECT 1 )
SELECT * FROM one;
1
sqlite>

Breaking that down a bit further:

   - We?ve defined a common table expression named ?one?
   - We?ve ?filled? it with the output of SELECT 1, which is just 1 row
   - Then we selected everything from ?one?
   - Such that the final result is a single value: 1

But a CTE can have multiple columns, too, and those columns can be assigned
names:

sqlite> WITH twoCol( a, b ) AS ( SELECT 1, 2 )
SELECT a, b FROM twoCol;
1|2
sqlite>

Similarly, a CTE can query other tables:

sqlite> CREATE TABLE foo ( bar INTEGER );
sqlite> INSERT INTO foo VALUES(1);
sqlite> INSERT INTO foo VALUES(2);
sqlite> SELECT * FROM foo;
1
2
sqlite> WITH fooCTE AS (SELECT * FROM foo)
SELECT * FROM fooCTE;
1
2
sqlite>

Additionally, you can define as many CTEs as you want in a single query:

sqlite> WITH aCTE AS (SELECT 'a'),
 bCTE AS (SELECT 'b')
SELECT * FROM aCTE, bCTE;
a|b
sqlite>

So, common table expressions can be used to restructure a query to make it
more readable, by moving the subqueries out in front. But the real power of
common table expressions is when you define an expression that recursively
selects itself. They key to this is using a ?Compound Select Statements?,
such as the UNION ALL operator. This just combines two result sets into one
(so long as they have the same number of columns):

sqlite> SELECT 1, 2
UNION ALL
SELECT 3, 4;
1|2
3|4
sqlite>

Take this example:

sqlite> WITH RECURSIVE infinite AS (
SELECT 1
UNION ALL
SELECT * FROM infinite
)
SELECT * FROM infinite;
^CError: interrupted
sqlite>

Let?s break down why that query will never finish:

   - ?WITH RECURSIVE infinite? defines a common table expression named
   ?infinite?
   - ?SELECT 1? seeds that CTE?s output with a single row ? containing ?1?
   - Next the ?UNION ALL? says ?combine the output of what?s on the left,
   with the output of what?s on the right
   - And on the right we do ?SELECT * FROM infinite? ? meaning, select
   everything currently in the table.
   - The result is we?re defining a common table expression named
   ?infinite? to be the union of ?a single row? and ?all other rows?.
   - Because no ?cap? has been placed on this (via a WHERE or LIMIT), this
   means we?ve defined an infinitely recurring CTE. Fun!

So we can ?cap? that CTE by writing a query like:

sqlite> WITH RECURSIVE finite AS (
SELECT 1
UNION ALL
SELECT * FROM finite LIMIT 2
)
SELECT * FROM finite;
1
1
sqlite>

This does the same basic thing, but we?ve limited the number of possible
results to only be 2. Ok, so that?s all well and good, but what is this
good for? It turns out, a lot. Say you wanted to generate a table on the
fly containing the numbers one through ten:

sqlite> WITH RECURSIVE ten(x) AS (
SELECT 1
UNION ALL
SELECT x+1 FROM ten WHERE x<10
)
SELECT * FROM ten;
1
2
3
4
5
6
7
8
9
10
sqlite>

To do this, we?ve defined a CTE named ?ten?, with a single column named ?x?
(the column name is optional, but in this case we need it to refer to
later). Then in the recursive UNION ALL, we keep adding one more row to the
result set ? each one larger than the row before ? until we reach a limit
of 10.

So CTEs can be used to generate a wide array of different types of data ?
such as date ranges, perhaps to join against when doing a historical
analysis against a sparse dataset (where some months have no data, so a
simple group-by won?t suffice):

sqlite> WITH RECURSIVE dates(x) AS (
SELECT '2015-01-01'
UNION ALL
SELECT DATE(x, '+1 MONTHS') FROM dates WHERE x<'2016-01-01'
)
SELECT * FROM dates;
2015-01-01
2015-02-01
2015-03-01
2015-04-01
2015-05-01
2015-06-01

[sqlite] Creating a stable database

[Warren Young]

On Sep 25, 2015, at 11:59 AM, Richard Hipp  wrote:
> 
> On 9/25/15, Aaron Digulla  wrote:
>> 
>> I was wondering if it was possible to create the same database (= no binary
>> difference) twice with sqlite.
> 
> It works fine to create identical database files when I try it:
> 
>   drh at bella:~/sqlite/bld$ ./sqlite3 db1drh at bella:~/sqlite/bld$ ./sqlite3 db2  dump.sql
sqlite3 a.db < dump.sql
sqlite3 b.db < dump.sql

If a.db differs from b.db in that condition, then narrowing the test case down 
by hand-editing dump.sql would be the way to find out why the difference is 
occurring.

If no difference occurs in this case, then it sounds like your current method 
to create the DBs isn?t identical, Aaron.

Another way to attack it would be:

sqlite3 my-a.db .dump > a-dump.sql
sqlite3 my-b.db .dump > b-dump.sql
diff -u ?-dump.sql

If that results in differences, it will probably clue you into why the 
difference is occurring.

If there is no difference in the dump outputs, then there is a structural-only 
change to the sqlite DB files, which suggests that the difference might be 
purely structural.  Perhaps a VACUUM would fix it.  Or, just dump and re-load, 
as above.

[sqlite] Query containing correlated subquery gets "stuck"

[gunnar]

Hi!

So, we're still havng troubles with one of our sqlite databases, or more 
likely one of our disks, but our system administrators cannot find 
anything wrong with it.

Perhaps someone reading this list has a clue what might be the case with 
this disk, or something else, when the sqlite client is stuck with the 
following stack traces (I made a couple of them after the query is 
already running for many minutes).

Disk is not busy but there is one process that is inserting records into 
the table that is at the same time queried by the sqlite client, a 
couple hundred thousand records per day.
(On other machines that insert even more records we don't see this 
happening and with the sqlite client there I can query whatever i want 
and query returns in a second and many times much faster. On all the 
machines the access pattern and configuration of the sqlite database is 
exactly the same)



The table definition is:
sqlite> .schema ordercallback
CREATE TABLE ordercallback (sessionuuid TEXT NOT NULL,cb_seq_num INTEGER 
NOT NULL,cb_uuid TEXT NOT NULL,server_order_id TEXT NOT NULL,product_id 
INTEGER NOT NULL,int_account INTEGER NOT NULL,ext_account INTEGER NOT 
NULL,client_order_id INTEGER NOT NULL,user_reference TEXT NOT 
NULL,user_exch_ref TEXT NOT NULL,buy_sell INTEGER NOT NULL,price INTEGER 
NOT NULL,stop_price INTEGER NOT NULL,size INTEGER NOT 
NULL,disclosed_size INTEGER NOT NULL,residual_size INTEGER NOT 
NULL,current_traded_size INTEGER NOT NULL,last_trade_price INTEGER NOT 
NULL,ext_trade_id TEXT NOT NULL,tot_traded_size INTEGER NOT 
NULL,imatch_traded_size INTEGER NOT NULL,avg_traded_price INTEGER NOT 
NULL,user_id INTEGER NOT NULL,trader_id INTEGER NOT NULL,status_time 
INTEGER NOT NULL,orig_time INTEGER NOT NULL,endstate INTEGER NOT 
NULL,order_type INTEGER NOT NULL,order_time_type INTEGER NOT 
NULL,order_price_type INTEGER NOT NULL,user_order_type INTEGER NOT 
NULL,expiry_date INTEGER NOT NULL,price_signs INTEGER NOT 
NULL,fill_flags INTEGER NOT NULL,specific_order_route TEXT NOT 
NULL,future_fields TEXT NOT NULL,reason TEXT NOT NULL,working INTEGER 
NOT NULL,FOREIGN KEY(sessionuuid) REFERENCES session(uuid) ON DELETE 
CASCADE);
CREATE INDEX ordercallback_index1 ON ordercallback (sessionuuid, 
int_account, cb_seq_num);
CREATE INDEX ordercallback_index2 ON ordercallback (sessionuuid, 
ext_account, cb_seq_num);
CREATE INDEX ordercallback_index3 ON ordercallback (server_order_id, 
sessionuuid, working);
CREATE INDEX ordercallback_index4 ON ordercallback (sessionuuid, 
endstate, working, int_account);
CREATE INDEX ordercallback_index5 ON ordercallback (sessionuuid, 
endstate, working, ext_account);
CREATE INDEX ordercallback_index6 ON ordercallback (cb_uuid);

On the machine where the query gets stuck there is more variation in 
values for the 'ext_account' column. Could that have to do with it?






Regards,
Gunnar


  [hiq at hiqserver2 ~]$ pstack 96660
#0  0x0034f020e530 in __read_nocancel () from /lib64/libpthread.so.0
#1  0x0041a485 in seekAndRead ()
#2  0x0041a5f9 in unixRead ()
#3  0x00410a86 in sqlite3OsRead ()
#4  0x00422433 in readDbPage ()
#5  0x00424e88 in sqlite3PagerAcquire ()
#6  0x0042c514 in btreeGetPage ()
#7  0x0042c60a in getAndInitPage ()
#8  0x0042fc25 in moveToChild ()
#9  0x00430871 in sqlite3BtreeMovetoUnpacked ()
#10 0x0043d65f in sqlite3VdbeCursorMoveto ()
#11 0x00444e7d in sqlite3VdbeExec ()
#12 0x00440163 in sqlite3Step ()
#13 0x0044034d in sqlite3_step ()
#14 0x00405240 in shell_exec ()
#15 0x0040c4aa in process_input ()
#16 0x0040d573 in main ()
  [hiq at hiqserver2 ~]$
  [hiq at hiqserver2 ~]$ pstack 96660
#0  0x0043ec95 in sqlite3VdbeRecordCompare ()
#1  0x0043f51f in sqlite3VdbeIdxKeyCompare ()
#2  0x00448db1 in sqlite3VdbeExec ()
#3  0x00440163 in sqlite3Step ()
#4  0x0044034d in sqlite3_step ()
#5  0x00405240 in shell_exec ()
#6  0x0040c4aa in process_input ()
#7  0x0040d573 in main ()
  [hiq at hiqserver2 ~]$
  [hiq at hiqserver2 ~]$ pstack 96660
#0  0x0034f020e530 in __read_nocancel () from /lib64/libpthread.so.0
#1  0x0041a485 in seekAndRead ()
#2  0x0041a5f9 in unixRead ()
#3  0x00410a86 in sqlite3OsRead ()
#4  0x00422433 in readDbPage ()
#5  0x00424e88 in sqlite3PagerAcquire ()
#6  0x0042c514 in btreeGetPage ()
#7  0x0042c60a in getAndInitPage ()
#8  0x0042fc25 in moveToChild ()
#9  0x0042fff2 in moveToLeftmost ()
#10 0x00430b6d in sqlite3BtreeNext ()
#11 0x004488d1 in sqlite3VdbeExec ()
#12 0x00440163 in sqlite3Step ()
#13 0x0044034d in sqlite3_step ()
#14 0x00405240 in shell_exec ()
#15 0x0040c4aa in process_input ()
#16 0x0040d573 in main ()
  [hiq at hiqserver2 ~]$
  [hiq at hiqserver2 ~]$ pstack 96660
#0  0x0034f020ec50 in 

[sqlite] Feature Suggestions

[Simon Slavin]

On 25 Sep 2015, at 2:39am, Allen  wrote:

> I wanted
> to confirm the readers would work correctly while another connection
> held an EXCLUSIVE lock, because if they didn't, and the lock
> eventually went EXCLUSIVE automatically, that might be a problem,

Don't forget to have each connection set a timeout using one of




This makes SQLite automatically retry (using monotonic backoff) if the database 
is locked.

Simon.

[sqlite] Feature Suggestions

[Allen]

> Don't forget to have each connection set a timeout This makes SQLite 
> automatically retry (using monotonic backoff) if the database is locked.

I had that set to 10 seconds using sqlite3_busy_timeout(db, 1).
That doesn't work in shared cache mode--in that mode, you immediately
get an SQLITE_LOCKED error regardless of the timeout setting.

[sqlite] Feature Suggestions

[Simon Slavin]

On 25 Sep 2015, at 1:27am, Allen  wrote:

>> Why on earth are you using BEGIN EXCLUSIVE?
> 
> If WAL works "as advertised", I might as well go straight to EXCLUSIVE so I
> don't have to later escalate the lock from RESERVED to EXCLUSIVE.  And it
> does work just fine, once I turned off the shared cache mode.

You don't have to escalate at all.  That happens automatically.  The problem 
with going to EXCLUSIVE manually is that you lock out other connections for 
longer than necessary.

Simon.