Pages: 1

#1 2015-06-21 16:06:30

mike76
Member
Registered: 2015-06-16
Posts: 18

sqlite3_key() expects PRAGMA mmap_size=0 on an ecrypted SQLite3DB

Hi,

I have a sql3 database on which I made test of speed, adding batch 10.000 rows. It worked nice.
Now, I've tried to use the encrypted option and create a database with password.
When trying to add the 60.000+ rows I get this error message:
sqlite3_key() expects PRAGMA mmap_size=0

And when I try to open the database again, nothing happens.
Note, I'm using TSynDBDataSet to load the DB into a DBGrid and latest nightly.

Hope you can help me find the problem on this.
Thanks

Offline

#2 2015-06-21 16:53:14

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,207
Website

Re: sqlite3_key() expects PRAGMA mmap_size=0 on an ecrypted SQLite3DB

You should not use MMAP with encryption - both options are incompatible.
So do not use the TSQLDataBase.MemoryMappedMB property.

Also ensure that you use the latest .obj files - currently in revision 3.8.10.2.
See http://synopse.info/files/sqlite3obj.7z

Offline

#3 2015-06-21 18:44:50

mike76
Member
Registered: 2015-06-16
Posts: 18

Re: sqlite3_key() expects PRAGMA mmap_size=0 on an ecrypted SQLite3DB

I don't use TSQLDataBase.MemoryMappedMB.

I'm using your 17 sample with Dataset and modified it with encryption. A database with more than 65.000 records fails to load, actually dataset open just freezes.

var
   fProps: TSQLDBSQLite3ConnectionProperties;
begin
.......
    fProps := TSQLDBSQLite3ConnectionProperties.Create(StringToUTF8(fDBFileName),'','',StringToUTF8('mypassword'));
......

So what should I change to fix this ?

Offline

#4 2015-06-21 19:00:33

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,207
Website

Re: sqlite3_key() expects PRAGMA mmap_size=0 on an ecrypted SQLite3DB

What is the .obj revision you are using?

Offline

#5 2015-06-21 19:04:43

mike76
Member
Registered: 2015-06-16
Posts: 18

Re: sqlite3_key() expects PRAGMA mmap_size=0 on an ecrypted SQLite3DB

The ones from your link, dated 28.05.2015 19:55

Offline

#6 2015-06-21 20:14:02

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,207
Website

Re: sqlite3_key() expects PRAGMA mmap_size=0 on an ecrypted SQLite3DB

Which version of sqlite3 engine is reported?
Do the TestSQL3 regression tests pass?

I've enhanced read/write low-level functions logging for SQlite3 static.
Please try them and report back here.
See http://synopse.info/fossil/info/d78fd69647

Offline

#7 2015-06-21 20:44:58

mike76
Member
Registered: 2015-06-16
Posts: 18

Re: sqlite3_key() expects PRAGMA mmap_size=0 on an ecrypted SQLite3DB

sqlite3_key() expects PRAGMA mmap_size=0 write(off=0 mmapsize=9662464 bufflen 1024)

Here is the output from TestSQL3:

   Synopse mORMot Framework Automated tests
  ------------------------------------------


1. Synopse libraries

 1.1. Low level common:
  - System copy record: 22 assertions passed  181us
  - TRawUTF8List: 130,009 assertions passed  123.13ms
  - TDynArray: 1,079,722 assertions passed  157.43ms
  - TDynArrayHashed: 1,200,629 assertions passed  124.14ms
  - TObjectListHashed: 999,885 assertions passed  308.72ms
  - TObjectDynArrayWrapper: 167,501 assertions passed  35.21ms
  - TObjArray: 2,016 assertions passed  1.37ms
  - Fast string compare: 7 assertions passed  116us
  - IdemPropName: 138 assertions passed  166us
  - Url encoding: 150 assertions passed  799us
  - GUID: 9,005 assertions passed  1.39ms
  - IsMatch: 599 assertions passed  227us
  - Soundex: 35 assertions passed  114us
  - Numerical conversions: 1,117,321 assertions passed  429.02ms
  - crc32c: 20,020 assertions passed  55.96ms
      pas 19.54ms 305.3 MB/s fast 3.34ms 1.7 GB/s
  - Curr 64: 20,053 assertions passed  1.94ms
  - CamelCase: 11 assertions passed  97us
  - Bits: 4,774 assertions passed  117us
  - Ini files: 7,004 assertions passed  20.58ms
  - UTF8: 80,127 assertions passed  735.80ms
  - Iso 8601 date and time: 36,015 assertions passed  4.55ms
  - Time zones: 407 assertions passed  14.27ms
  - Url decoding: 1,100 assertions passed  278us
  - Mime types: 23 assertions passed  119us
!  - TSynTable: 93 / 875 FAILED  6.73ms
  - TSynCache: 404 assertions passed  176us
  - TSynFilter: 1,005 assertions passed  2.01ms
  - TSynValidate: 677 assertions passed  576us
  - TSynLogFile: 42 assertions passed  522us
  Total failed: 93 / 4,879,576  - Low level common FAILED  2.02s

 1.2. Low level types:
  - RTTI: 61 assertions passed  145us
  - Url encoding: 200 assertions passed  585us
  - Encode decode JSON: 283,483 assertions passed  141.13ms
  - Variants: 45 assertions passed  118us
  - Mustache renderer: 144 assertions passed  1.93ms
  - TDocVariant: 71,712 assertions passed  137.91ms
  - BSON: 245,048 assertions passed  3.32ms
  - TSynTableStatement: 205 assertions passed  172us
  Total failed: 0 / 600,898  - Low level types PASSED  285.91ms

 1.3. Big table:
  - TSynBigTable: 19,126 assertions passed  123.57ms
  - TSynBigTableString: 16,104 assertions passed  25.29ms
  - TSynBigTableMetaData: 475,563 assertions passed  1.17s
  - TSynBigTableRecord: 375,563 assertions passed  1.57s
  Total failed: 0 / 886,356  - Big table PASSED  2.90s

 1.4. Synopse PDF:
!  - TPdfDocument: 1 / 2 FAILED  5.29ms
  - TPdfDocumentGDI: 5 assertions passed  52.54ms
  Total failed: 1 / 7  - Synopse PDF FAILED  58.05ms

 1.5. Cryptographic routines:
  - Adler32: 1 assertion passed  541us
  - MD5: 1 assertion passed  110us
  - SHA1: 5 assertions passed  112us
  - SHA256: 5 assertions passed  114us
  - AES256: 16,816 assertions passed  715.07ms
  - RC4: 1 assertion passed  109us
  - Base64: 11,994 assertions passed  87.55ms
  - CompressShaAes: 1,683 assertions passed  3.87ms
  Total failed: 0 / 30,506  - Cryptographic routines PASSED  812.44ms

 1.6. Compression:
  - In memory compression: 12 assertions passed  237.39ms
  - GZIP format: 19 assertions passed  456.34ms
  - ZIP format: 54 assertions passed  1.38s
  - SynLZO: 3,006 assertions passed  85.02ms
  - SynLZ: 32,221 assertions passed  591.65ms
  Total failed: 0 / 35,312  - Compression PASSED  2.75s


2. mORMot

 2.1. File based:
  - Database direct access: 20,248 assertions passed  259.60ms
  - Virtual table direct access: 12 assertions passed  1.53ms
  - TSQLTableJSON: 144,070 assertions passed  122.67ms
  - TSQLRestClientDB: 608,193 assertions passed  4.18s
  - TRecordVersion: 20,060 assertions passed  185.35ms
  Total failed: 0 / 792,583  - File based PASSED  4.75s

 2.2. File based memory map:
  - Database direct access: 20,246 assertions passed  186.49ms
  - Virtual table direct access: 12 assertions passed  1.03ms
  - TSQLTableJSON: 144,070 assertions passed  111.66ms
  - TSQLRestClientDB: 608,192 assertions passed  4.10s
  - TRecordVersion: 20,060 assertions passed  188.31ms
  Total failed: 0 / 792,580  - File based memory map PASSED  4.59s

 2.3. File based WAL:
  - Database direct access: 20,248 assertions passed  216.55ms
  - Virtual table direct access: 12 assertions passed  948us
  - TSQLTableJSON: 144,070 assertions passed  120.42ms
  - TSQLRestClientDB: 608,193 assertions passed  4.30s
  - TRecordVersion: 20,060 assertions passed  165.41ms
  Total failed: 0 / 792,583  - File based WAL PASSED  4.80s

 2.4. Memory based:
  - Database direct access: 20,246 assertions passed  182.93ms
  - Virtual table direct access: 12 assertions passed  839us
  - TSQLTableJSON: 144,070 assertions passed  114.38ms
  - TSQLRestClientDB: 676,500 assertions passed  5.18s
  - TRecordVersion: 20,060 assertions passed  187.59ms
  - RTree: 140,000 assertions passed  1.27s
  Total failed: 0 / 1,000,888  - Memory based PASSED  6.95s

 2.5. Basic classes:
  - TSQLRecord: 80 assertions passed  582us
  - TSQLRecordSigned: 200 assertions passed  4.09ms
  - TSQLModel: 16 assertions passed  126us
  - TSQLRestServerFullMemory: 562,310 assertions passed  745.05ms
  Total failed: 0 / 562,606  - Basic classes PASSED  752.45ms

 2.6. Client server access:
  - TSQLHttpServer: 2 assertions passed  10.42ms
     using WinSock 2.0.514
  - TSQLHttpClient: 3 assertions passed  27.60ms
     using TSQLHttpClientWinHTTP
  - HTTP client keep alive: 3,087 assertions passed  230.43ms
     4818 B, first 4.46ms, done 214.04ms i.e. 4671/s, aver. 214us, 21.8 MB/s
  - HTTP client multi connect: 3,087 assertions passed  225.52ms
     4818 B, first 374us, done 213.69ms i.e. 4679/s, aver. 213us, 21.8 MB/s
  - HTTP client encrypted: 3,087 assertions passed  282.90ms
     4818 B, first 466us, done 270.18ms i.e. 3701/s, aver. 270us, 17.3 MB/s
  - Named pipe access: 3,089 assertions passed  624.58ms
     4818 B, first 271.33ms, done 127.69ms i.e. 7831/s, aver. 127us, 36.6 MB/s
  - Local window messages: 3,088 assertions passed  64.95ms
     4818 B, first 131us, done 54.20ms i.e. 18448/s, aver. 54us, 86.2 MB/s
  - Direct in process access: 3,056 assertions passed  42.04ms
     4818 B, first 33us, done 30.61ms i.e. 32664/s, aver. 30us, 152.7 MB/s
  - HTTP several DB servers: 9,604 assertions passed  997.49ms
     4818 B, first 483us, done 238.05ms i.e. 4200/s, aver. 238us, 19.6 MB/s
     4818 B, first 467us, done 238.12ms i.e. 4199/s, aver. 238us, 19.6 MB/s
     4818 B, first 459us, done 237.50ms i.e. 4210/s, aver. 237us, 19.6 MB/s
  Total failed: 0 / 28,103  - Client server access PASSED  2.51s

 2.7. Service oriented architecture:
  - Weak interfaces: 56 assertions passed  704us
  - Service initialization: 255 assertions passed  3.46ms
  - Direct call: 579,186 assertions passed  55.18ms
  - Server side: 579,206 assertions passed  39.67ms
  - Client side REST: 772,274 assertions passed  938.15ms
  - Client side REST result as object: 772,268 assertions passed  950.33ms
  - Client side REST sessions stats: 772,268 assertions passed  996.39ms
  - Client side REST locked: 772,272 assertions passed  933.21ms
  - Client side REST main thread: 772,272 assertions passed  1.90s
  - Client side REST background thread: 772,272 assertions passed  1.63s
  - Client side REST weak authentication: 772,268 assertions passed  908.90ms
  - Client side REST basic authentication: 772,268 assertions passed  1.60s
  - Client side REST custom record layout: 772,268 assertions passed  997.75ms
  - Client side JSONRPC: 772,268 assertions passed  1.03s
  - Test over HTTP: 9,661 assertions passed  583.58ms
  - Security: 135 assertions passed  2.65ms
  - Mocks and stubs: 30,031 assertions passed  66.23ms
  Total failed: 0 / 8,921,228  - Service oriented architecture PASSED  12.65s

 2.8. Bidirectional remote connection:
  - Websockets JSON protocol: 60 assertions passed  175us
  - Websockets binary protocol: 240 assertions passed  303us
  - Run http server: 2 assertions passed  1.40ms
  - SOA callback on server side: 4,452 assertions passed  2.44ms
  - SOA callback via JSON websockets: 4,455 assertions passed  197.88ms
  - SOA callback via binary websockets: 4,455 assertions passed  218.65ms
  - TRecordVersion: 20,068 assertions passed  234.33ms
  Total failed: 0 / 33,732  - Bidirectional remote connection PASSED  655.92ms

 2.9. External database:
  - TQuery: 2,003 assertions passed  12.44ms
! External database - SynDBRemote
! Exception ESQLDBRemote raised with messsage:
!  TSQLDBServerHttpApi.Create: administrator rights needed to register URI root
on port 888
  - DB properties persistence: 7 assertions passed  874us
  - External records: 2 assertions passed  347us
  - Auto adapt SQL: 708 assertions passed  73.84ms
  - Crypted database: 253,275 assertions passed  231.68ms
  - External via REST: 170,354 assertions passed  1.26s
  - External via virtual table: 170,354 assertions passed  2.28s
  - External via REST with change tracking: 180,454 assertions passed  3.37s
  - JET database: 7,007 assertions passed  874.47ms
  Total failed: 0 / 799,211  - External database PASSED  8.38s

 2.10. Multi thread process:
  - Create thread pool: 1 assertion passed  22.22ms
  - TSQLRestServerDB: 4,822 assertions passed  132.30ms
     1=17591/s  2=23098/s  5=22444/s  10=21160/s  30=24642/s  50=22295/s
  - TSQLRestClientDB: 4,822 assertions passed  1.46s
     1=18683/s  2=12276/s  5=7147/s  10=2889/s  30=886/s  50=533/s
  - TSQLRestClientURINamedPipe: 2,412 assertions passed  1.36s
     1=1213/s  2=1407/s  5=774/s
  - TSQLRestClientURIMessage: 3,222 assertions passed  185.92ms
     1=7682/s  2=12056/s  5=10171/s  10=7960/s
  - Windows API: 4,822 assertions passed  1.12s
     1=4474/s  2=4913/s  5=4224/s  10=2463/s  30=1369/s  50=1513/s
  - Socket API: 4,822 assertions passed  856.96ms
     1=7483/s  2=6952/s  5=5141/s  10=2948/s  30=1860/s  50=1514/s
  - Websockets: 4,822 assertions passed  1.15s
     1=7117/s  2=6414/s  5=3661/s  10=1970/s  30=1113/s  50=1158/s
  - Locked: 4,822 assertions passed  1.21s
     1=18640/s  2=12317/s  5=7136/s  10=2887/s  30=977/s  50=747/s
  - Unlocked: 4,822 assertions passed  123.86ms
     1=18639/s  2=22607/s  5=20222/s  10=21601/s  30=24115/s  50=21763/s
  - Main thread: 4,822 assertions passed  201.83ms
     1=14278/s  2=12859/s  5=12868/s  10=12865/s  30=12877/s  50=12979/s
  - Background thread: 4,822 assertions passed  139.50ms
     1=16884/s  2=20996/s  5=20512/s  10=20468/s  30=22246/s  50=19531/s
  Total failed: 0 / 49,033  - Multi thread process PASSED  8.00s

 2.11. DDD shared units:
  - User model: 3,276 assertions passed  734us
  - Authentication model: 20,006 assertions passed  132.93ms
  - Email validation process: 57 assertions passed  93.21ms
  - User CQRS repository: 57,106 assertions passed  562.15ms
  Total failed: 0 / 80,445  - DDD shared units PASSED  791.76ms


Using mORMot 1.18.1518 FTS3
Running on wEight_64 with code page 1250
TSQLite3LibraryStatic 3.8.10.2 with internal MM
Generated with: Delphi 7 compiler

Time elapsed for all tests: 63.86s
Tests performed at 21.06.2015 23:43:29

Total assertions failed for all test suits:  94 / 20,285,647
! Some tests FAILED: please correct the code.

Done - Press ENTER to Exit

Thanks.

Offline

#8 2015-06-21 21:46:35

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,207
Website

Re: sqlite3_key() expects PRAGMA mmap_size=0 on an ecrypted SQLite3DB

What does the mmap_size pragma return?

Offline

#9 2015-06-21 22:02:22

mike76
Member
Registered: 2015-06-16
Posts: 18

Re: sqlite3_key() expects PRAGMA mmap_size=0 on an ecrypted SQLite3DB

Do you refer to this ?

sqlite3_key() expects PRAGMA mmap_size=0 write(off=0 mmapsize=9662464 bufflen 1024)

Offline

#10 2015-06-21 22:26:02

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,207
Website

Re: sqlite3_key() expects PRAGMA mmap_size=0 on an ecrypted SQLite3DB

Try to run the pragma in the connection.

Offline

#11 2015-06-23 09:44:46

mike76
Member
Registered: 2015-06-16
Posts: 18

Re: sqlite3_key() expects PRAGMA mmap_size=0 on an ecrypted SQLite3DB

Seems I found how to replicate the issue:
It happens if the database is encrypted, has more than 60.000+ records (probably depends on table structure) and you add "Order By" to the SQL statement. No error happens if I don't use Order By.

TSynDBDataSet(ds1.DataSet).CommandText := 'select * from MyTable Order by MyColumn ASC';

Note that without encryption I can load the database with more than 500.000+ records using Order by.

Hope you can help me find the problem.

Offline

#12 2015-06-29 08:40:50

mike76
Member
Registered: 2015-06-16
Posts: 18

Re: sqlite3_key() expects PRAGMA mmap_size=0 on an ecrypted SQLite3DB

Hi ab,

I've sent you a few days ago by email a sample project which demonstrates the error. Have you received it?

Thanks

Offline

#13 2015-06-29 11:26:16

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,207
Website

Re: sqlite3_key() expects PRAGMA mmap_size=0 on an ecrypted SQLite3DB

Yes, but I did not have time investigating on it yet.

Offline

#14 2015-07-02 13:49:23

mike76
Member
Registered: 2015-06-16
Posts: 18

Re: sqlite3_key() expects PRAGMA mmap_size=0 on an ecrypted SQLite3DB

ab wrote:

Yes, but I did not have time investigating on it yet.

Ok. Hope you'll have time to check it soon. At the moment this is the only issue stopping us for using mORMot in our project.

Thanks

Offline

#15 2015-07-02 16:44:41

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,207
Website

Re: sqlite3_key() expects PRAGMA mmap_size=0 on an ecrypted SQLite3DB

Why do you need encryption at this level?

You may use an external sqlite3.dll which supports encryption, instead of the built-in "statically linked" version, in the meanwhile.

Offline

#16 2015-07-02 20:42:14

mike76
Member
Registered: 2015-06-16
Posts: 18

Re: sqlite3_key() expects PRAGMA mmap_size=0 on an ecrypted SQLite3DB

ab wrote:

Why do you need encryption at this level?

We store information that should not be available to view without our software.

ab wrote:

You may use an external sqlite3.dll which supports encryption, instead of the built-in "statically linked" version, in the meanwhile.

Yes, but the no dll feature of mORMot was one of the reasons we chose it.

Offline

#17 2015-07-09 21:30:20

mike76
Member
Registered: 2015-06-16
Posts: 18

Re: sqlite3_key() expects PRAGMA mmap_size=0 on an ecrypted SQLite3DB

Hi ab,

Did you had time to look into this? Do you think it's hard to fix ?

Thanks.

Offline

#18 2015-07-10 11:15:12

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,207
Website

Re: sqlite3_key() expects PRAGMA mmap_size=0 on an ecrypted SQLite3DB

I had no time to check it yet.

It may be hard to fix, since it may go within the Sqlite3.c code, outside the pascal/Delphi IDE range.

Offline

Pages: 1

Board footer

Powered by FluxBB