1TB limit on encrypted database

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

1TB limit on encrypted database

Andrew Stewart
Hi,
                I appear to have reached an issue with a 1TB limit on an encrypted database.  The page_size is 1024.  The max_page_count is 1073741823.  I don't appear to be able to change either of these numbers.  Any assistance would be appreciated.

Andrew Stewart
Argus Control Systems Ltd.

Notice: This electronic transmission contains confidential information, intended only for the person(s) named above. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or any other use of this email is strictly prohibited. If you have received this transmission by error, please notify us immediately by return email and destroy the original transmission immediately and all copies thereof.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 1TB limit on encrypted database

Richard Hipp-3
On 11/14/17, Andrew Stewart <[hidden email]> wrote:
> Hi,
>                 I appear to have reached an issue with a 1TB limit on an
> encrypted database.  The page_size is 1024.  The max_page_count is
> 1073741823.  I don't appear to be able to change either of these numbers.
> Any assistance would be appreciated.

You can try:

   PRAGMA max_page_count=2000000000;

Probably you should also look into increasing the page size:

   PRAGMA page_size=8192;
   VACUUM;

For that last step, make sure you have upgraded to a recent version of
the encryption extension as some of the older versions had issues.
Also, note that the VACUUM will take some time and will require a
couple TB of temporary disk space while it is running.

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 1TB limit on encrypted database

Andrew Stewart
Richard,
        If I am trying to change the max_page_count use the SEE.EXE program, how do I get the value to be updated in the database.  Every time I try changing this and then exit see.exe, the value has not changed.  Do I need the updated encryption extension first?

Andrew Stewart
Argus Control Systems Ltd.

-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Richard Hipp
Sent: Tuesday, November 14, 2017 11:33 AM
To: SQLite mailing list <[hidden email]>
Subject: Re: [sqlite] 1TB limit on encrypted database

On 11/14/17, Andrew Stewart <[hidden email]> wrote:
> Hi,
>                 I appear to have reached an issue with a 1TB limit on
> an encrypted database.  The page_size is 1024.  The max_page_count is
> 1073741823.  I don't appear to be able to change either of these numbers.
> Any assistance would be appreciated.

You can try:

   PRAGMA max_page_count=2000000000;

Probably you should also look into increasing the page size:

   PRAGMA page_size=8192;
   VACUUM;

For that last step, make sure you have upgraded to a recent version of the encryption extension as some of the older versions had issues.
Also, note that the VACUUM will take some time and will require a couple TB of temporary disk space while it is running.

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Notice: This electronic transmission contains confidential information, intended only for the person(s) named above. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or any other use of this email is strictly prohibited. If you have received this transmission by error, please notify us immediately by return email and destroy the original transmission immediately and all copies thereof.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 1TB limit on encrypted database

Richard Hipp-3
On 11/14/17, Andrew Stewart <[hidden email]> wrote:
> Richard,
>         If I am trying to change the max_page_count use the SEE.EXE program,
> how do I get the value to be updated in the database.  Every time I try
> changing this and then exit see.exe, the value has not changed.  Do I need
> the updated encryption extension first?

The PRAGMA only changes the one database connection in which it runs.

To make the setting persistent, recompile with
-DSQLITE_MAX_PAGE_COUNT=2000000000

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 1TB limit on encrypted database

Andrew Stewart
Thanks.  This has worked.

Andrew Stewart
Argus Control Systems Ltd.

-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Richard Hipp
Sent: Tuesday, November 14, 2017 12:34 PM
To: SQLite mailing list <[hidden email]>
Subject: Re: [sqlite] 1TB limit on encrypted database

On 11/14/17, Andrew Stewart <[hidden email]> wrote:
> Richard,
>         If I am trying to change the max_page_count use the SEE.EXE
> program, how do I get the value to be updated in the database.  Every
> time I try changing this and then exit see.exe, the value has not
> changed.  Do I need the updated encryption extension first?

The PRAGMA only changes the one database connection in which it runs.

To make the setting persistent, recompile with
-DSQLITE_MAX_PAGE_COUNT=2000000000

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Notice: This electronic transmission contains confidential information, intended only for the person(s) named above. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or any other use of this email is strictly prohibited. If you have received this transmission by error, please notify us immediately by return email and destroy the original transmission immediately and all copies thereof.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 1TB limit on encrypted database

Richard Hipp-3
On 11/15/17, Andrew Stewart <[hidden email]> wrote:
> Thanks.  This has worked.

Temporarily.  You cannot increase the max_page_count above 2147483646,
so if your database continues to grow, you are going to need to look
into increasing the page size from 1024 to something larger like 8192.

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 1TB limit on encrypted database

Andrew Stewart
My code actually sets this to 2048, but it doesn't appear to stick (or get used).  I am just setting using the PRAGMA command (same as I am now doing the max_page_count).  Could this be an issue with an older copy of the Encryption package?  I have not updated yet - am in the middle of other work.

Andrew Stewart
Software Designer

Argus Control Systems Ltd.
Tel: (604) 536-9100 ext. 108

[hidden email]

#101 - 18445 53rd Avenue | Surrey BC | CANADA | V3S 7A4
(604) 538-3531 | (800) 667-2090 | Fax (604) 538-4728
www.arguscontrols.com

-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Richard Hipp
Sent: Wednesday, November 15, 2017 7:18 AM
To: SQLite mailing list <[hidden email]>
Subject: Re: [sqlite] 1TB limit on encrypted database

On 11/15/17, Andrew Stewart <[hidden email]> wrote:
> Thanks.  This has worked.

Temporarily.  You cannot increase the max_page_count above 2147483646, so if your database continues to grow, you are going to need to look into increasing the page size from 1024 to something larger like 8192.

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Notice: This electronic transmission contains confidential information, intended only for the person(s) named above. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or any other use of this email is strictly prohibited. If you have received this transmission by error, please notify us immediately by return email and destroy the original transmission immediately and all copies thereof.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 1TB limit on encrypted database

David Raymond
To adjust the page size with the pragma command it has to be done either right at the start of a new database, before SQLite has created the file yet, or right before a vacuum command, and the vacuum command will then include the page size change as part of its process. Running the page_size pragma outside of those two times won't have any effect.


-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Andrew Stewart
Sent: Friday, November 17, 2017 1:47 PM
To: SQLite mailing list
Subject: Re: [sqlite] 1TB limit on encrypted database

My code actually sets this to 2048, but it doesn't appear to stick (or get used).  I am just setting using the PRAGMA command (same as I am now doing the max_page_count).  Could this be an issue with an older copy of the Encryption package?  I have not updated yet - am in the middle of other work.

Andrew Stewart
Software Designer

Argus Control Systems Ltd.
Tel: (604) 536-9100 ext. 108

[hidden email]

#101 - 18445 53rd Avenue | Surrey BC | CANADA | V3S 7A4
(604) 538-3531 | (800) 667-2090 | Fax (604) 538-4728
www.arguscontrols.com

-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Richard Hipp
Sent: Wednesday, November 15, 2017 7:18 AM
To: SQLite mailing list <[hidden email]>
Subject: Re: [sqlite] 1TB limit on encrypted database

On 11/15/17, Andrew Stewart <[hidden email]> wrote:
> Thanks.  This has worked.

Temporarily.  You cannot increase the max_page_count above 2147483646, so if your database continues to grow, you are going to need to look into increasing the page size from 1024 to something larger like 8192.

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Notice: This electronic transmission contains confidential information, intended only for the person(s) named above. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or any other use of this email is strictly prohibited. If you have received this transmission by error, please notify us immediately by return email and destroy the original transmission immediately and all copies thereof.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users