file size limit?

[Sean Hogan]

Hello all,

I'm using SQLCipher 2.0.8 for Android (precompiled binaries). I've
found that opening a 2.1GB unencrypted database file works fine with the
native SQLite API, but fails using the SQLCipher wrapper
(net.sqlcipher.database.SQLiteDatabase.openOrCreateDatabase). It craps
out with a complaint about setLocale. (Sorry I don't have the exact
message, I'm working from memory.)

Is there a known limitation of SQLCipher in this area?

Thanks,
Sean

[Stephen Lombardo]

Hi Sean,

I've used SQLCipher to create and manipulate databases larger that 2GB without issues before. Does the same code work opening a smaller database file, or an an encrypted database?

Cheers,
Stephen

[Sean Hogan]

Hello Stephen,

The same code works with other database files up to hundreds of megs,
and works with encrypted databases. Here's the relevant portion of the
stack trace:

11-14 11:08:29.144: E/Database(8911): CREATE TABLE android_metadata failed
11-14 11:08:29.204: E/Database(8911): Failed to setLocale() when
constructing, closing the database
11-14 11:08:29.204: E/Database(8911):
net.sqlcipher.database.SQLiteDiskIOException: disk I/O error
11-14 11:08:29.204: E/Database(8911): at
net.sqlcipher.database.SQLiteDatabase.native_setLocale(Native Method)
11-14 11:08:29.204: E/Database(8911): at
net.sqlcipher.database.SQLiteDatabase.setLocale(SQLiteDatabase.java:2103)
11-14 11:08:29.204: E/Database(8911): at
net.sqlcipher.database.SQLiteDatabase.<init>(SQLiteDatabase.java:1969)
11-14 11:08:29.204: E/Database(8911): at
net.sqlcipher.database.SQLiteDatabase.openDatabase(SQLiteDatabase.java:902)
11-14 11:08:29.204: E/Database(8911): at
net.sqlcipher.database.SQLiteDatabase.openDatabase(SQLiteDatabase.java:949)

I'm also making the file available if you want to try it:
ftp://ows9:cslt...@ftp.compusult.net/modis.geopackage. The file is
2223258624 bytes long, which is about 72MB over 2GB.

Thanks again,
Sean

[Sean Hogan]

Further information... I removed enough data from the
'modis_terra_correctedreflectance_truecolor_tiles' table to bring the
file size under 2GB (by about 80MB, as it happens), and the problem
disappeared.

Sean

[Nick Parker]

Hi Sean,

Thanks for the sample file, we will look into it and see if we can
replicate the issue locally.

Nick Parker

--
Nick Parker

[Sean Hogan]

Hi guys,

Any results from the sample file I sent? Were you at least able to
reproduce the issue?

Sean

On 12-11-13 06:55 PM, Stephen Lombardo wrote:

[Nick Parker]

Hi Sean,

Yes, I have been able to reproduce the issue you are seeing.  We are investigating as to whether or not there is a fix we can apply for this.  Thanks!

Nick Parker

[Stephen Lombardo]

Hi Sean,

I'm just replying to your earlier question from the SQLCipher 2.1.0 post here to keep discussion on this thread. Unfortunately the 2.1.0 beta release doesn't resolve the issue you were seeing.

We've done some investigation into this problem already. We were able to reproduce the problem on an Android system, but opening and modifying your large database file on other platforms works just fine. Thus, we don't believe it is a core SQLCipher limitation, but rather a platform and/or compiler issue with the Android port.

Two follow-up questions:

1. When opening the database, is it located on an SD card?
2. What versions of Android have you tested this with?

Cheers,
Stephen

[Sean Hogan]

Okay thanks. We tested this on a Nexus 7 with no SD card, internal
memory only. It originally had Android 4.1.2 (or 4.1.1, can't quite
recall) but also has been tested on 4.2.

Sean

[Sean Hogan]

Hello Stephen,

Someone at a partner company of ours did some excellent research on this problem.  Here are the results:

This has to do with the sqlite compile flags that are specified in the sqlcipher Android.mk file.

To get large file support you need to have USE_PREAD64 defined. The wrapper function for pread64 and pwrite64 are not provided in the NDK though so you need to generate these and include them in your library. You can generate the wrapper functions by checking out bionic from the AOSP project and running the gensyscalls.py script.

I think there's another similar issue in that sqlite uses ftruncate instead of ftruncate64. If I'm interpreting things correctly this could mean that a > 2Gb file could get truncated to 2Gb accidentally.

Hope this helps.

Cheers,
Sean

On 12-11-27 11:21 AM, Stephen Lombardo wrote:

Hi Sean,

I'm just replying to your earlier question from the SQLCipher 2.1.0 post here to keep discussion on this thread. Unfortunately the 2.1.0 beta release doesn't resolve the issue you were seeing. 

We've done some investigation into this problem already. We were able to reproduce the problem on an Android system, but opening and modifying your large database file on other platforms works just fine. Thus, we don't believe it is a core SQLCipher limitation, but rather a platform and/or compiler issue with the Android port. 

Two follow-up questions: 

1. When opening the database, is it located on an SD card?
2. What versions of Android have you tested this with?

Cheers,
Stephen

[Nick Parker]

Hi Sean,

Thanks for the additional information!  I have rebuilt SQLCipher for Android with -DUSE_PREAD64 and included some of the various assembly fragments from platform_bionic however the new build fails all of the tests within our suite.  I am looking into this further to understand the issue.  Thanks!

Nick Parker

[Nick Parker]

Hi Sean,

I just wanted to get back to you with this, after spending a bit more time looking into it I was able to make some adjustments to get pread64/pwrite64 to work with SQLCipher for Android running Android 4.1.

You were correct with regard to including the -DUSE_PREAD64 for the LOCAL_CFLAGS, I also added -D_XOPEN_SOURCE=500 -D_FILE_OFFSET_BITS=64.  Inclusion of the bionic pread64.S/pwrite64.S assembly does not appear necessary.

While the above passed compilation, I/O disk error's were abundant.  As it turns out, some additional adjustments were required within the core of the SQLCipher os_unix.c file where the system call interfaces are defined.  Bionic defines pread64/pwrite64 with off64_t instead of off_t, so adjusting those mappings allowed things to work.  I've attached a patch file for the os_unix.c changes.  While this works on Android 4.1, running the same on 2.1 cause a failure.  Hopefully this information should get you going if you were to pursue this.

Nick Parker

On Thu, Dec 13, 2012 at 8:41 AM, Sean Hogan <hogan...@gmail.com> wrote:

[Sean Hogan]

Thanks Nick. We were doing a proof of concept which is now complete, so
our immediate needs have been met. I'm sure we'll be in touch in the
future.

Regards,
Sean

[Tony]

Sorry to bring this old thread back to life but if I open a new one I'll just have to point it here.

What is the likelihood that SQLCipher for Android will as standard support larger file sizes? I have a requirement to use the same information from the desktop on a mobile device, unfortunately the SQLCipher database size can be up to 10GB. 

The devices we brought support exFAT formatted SD cards so theres no 4GB file limit, but as I Sean found, opening the database SQLCipher "craps out with a complaint about setLocale" and disk I/O error.

I seem to have a number of options, I could recompile SQLCipher myself with these fixes listed here, although I failed miserably earlier trying this on Windows. The alternative is I split the data over a number of attached databases, rewriting some of the desktop code at the same time. 

Another question I guess is either of these options significantly better? Is one huge database just going to use all the phones memory anyway.

Tony

[Nick Parker]

Hi Tony,

I've replied inline below to your comments:

Nick Parker

On Sun, Apr 28, 2013 at 2:13 PM, Tony <em...@chellew.co.uk> wrote:

Sorry to bring this old thread back to life but if I open a new one I'll just have to point it here.

What is the likelihood that SQLCipher for Android will as standard support larger file sizes? I have a requirement to use the same information from the desktop on a mobile device, unfortunately the SQLCipher database size can be up to 10GB. 

This may be a feature we look at for a future release of SQLCipher for Android, but there hasn't been a large demand for this change as of yet.  While I was able to get a build working previously, it was not stable on all platform versions we are currently supporting.  We do not wish to introduce a regression, so this would need to be addressed.

 

The devices we brought support exFAT formatted SD cards so theres no 4GB file limit, but as I Sean found, opening the database SQLCipher "craps out with a complaint about setLocale" and disk I/O error.

When SQLCipher attempts to perform a IO read/write operation with the setLocale call, pread64/pwrite64 calls need to be used in order to access database files larger than 2GB.  This usage comes into play with regard to how SQLite reads the file.

 

I seem to have a number of options, I could recompile SQLCipher myself with these fixes listed here, although I failed miserably earlier trying this on Windows. The alternative is I split the data over a number of attached databases, rewriting some of the desktop code at the same time. 

The toolchain is easier to acquire and configure on either Linux or OS X, creating a Linux VM for this may be a good option.

 

Another question I guess is either of these options significantly better? Is one huge database just going to use all the phones memory anyway.

That may be difficult to say, as it may depend of the type of query performed and the resulting dataset returned from the query.  Profiling your usage maybe helpful in understanding the behavior.

 

Tony

--
 
---
You received this message because you are subscribed to the Google Groups "SQLCipher Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlcipher+...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Tony]

Thanks for the response Nick, 

I think for now I'll look at attached databases as a way around the file size problem, its too much by luck that the current devices are 4.1 and support exFAT. An added bonus that deleting data should just mean deleting an entire file. 

I'll save trying to compiling from code for another day, although now I know not to use Windows!

Tony

[Tony]

Guys,

I've got SQLCipher compiling under MacOSX, added the cflags to Android.mk but have no idea what to do with the bionic code beyond running gensyscalls.py, what do I need to do to get the SQLCipher make to find the pread64 and pwrite64 functions?

I feel stupid even having to ask but I'm not a C/C++ person I'm far too reliant 4GLs like java.

Tony

[Nick Parker]

Hi Tony,

Sorry for the delay, I wanted to loop back on your question above.  You will need to apply the patch to SQLCipher in external/sqlcipher that I provided in this[1] thread.  Explicit inclusion of the assembly code was not needed.  Please note that once this has been applied, it does not work on all mainline supported versions of SQLCipher for Android.  You can verify this with your build by running the test suite available here [2].

1.  https://groups.google.com/forum/#!msg/sqlcipher/DoJzRVBUisQ/jdHDt42o2YAJ

2.  https://github.com/sqlcipher/sqlcipher-android-tests

Nick Parker

Nick Parker

Tony

[Tony]

Nick,

I have applied the patch, but adding -DUSE_PREAD64 flag to the SQLCipher .mk file causes problems with it not being able to find pread64 and pwrite64 functions in compiling, so it seems something from bionic is required to be included at least for the compiler to build against?

Regards,

Tony  

[Nick Parker]

Hi Tony,

I remember trying various iterations on this, but it does appear bionic maybe needed, thought this should already exist in libc.  You can add the git submodule for it from here [1].  Also, you would need to adjust the external/Android.mk to resemble something like:

sqlcipher_files := \

sqlcipher/sqlite3.c \

platform_bionic/libc/arch-${TARGET_ARCH}/syscalls/pread64.S \

  platform_bionic/libc/arch-${TARGET_ARCH}/syscalls/pwrite64.S \

platform_bionic/libc/bionic/__set_errno.cpp

sqlcipher_cflags := -DSQLITE_HAS_CODEC -DHAVE_FDATASYNC=0 -Dfdatasync=fsync

include $(CLEAR_VARS)

LOCAL_CFLAGS += $(android_sqlite_cflags) $(sqlcipher_cflags)

LOCAL_C_INCLUDES := includes openssl/include sqlcipher \

platform_bionic/libc/arch-${TARGET_ARCH}/include \

platform_bionic/libc/include

LOCAL_LDFLAGS += $(project_ldflags)

1.  git://github.com/android/platform_bionic.git

Nick Parker