Problems reading/writing database files over 2GB

[GoogleCodeExporter]

What steps will reproduce the problem?
1. Create table
2. Insert enough rows so that the database file size is over 2GB (2^31-1 bytes)
3. Or, have a database file that is over 2GB in file size
4. Read a row

What is the expected output? What do you see instead?
There should be no error.  For write, I see "SQL Logic error or missing 
database"; for read I see "database disk image is malformed"

What version of the product are you using? On what operating system?
3.6.23.  Win 7 64-bit Pro.

Please provide any additional information below.
Traced the error and found that it happens in decodeFlags() method in 
btree_c.cs when reading a database file over 2GB.

Original issue reported on code.google.com by fusoy...@gmail.com on 24 Jun 2010 at 10:50

[GoogleCodeExporter]

Do you have a script that will reproduce this problem, or did you start with a 
large database before?

If you take your 2GB database and use the SQLite command line tool to do your 
reads and write, what happens?

Precompiled Binaries For Windows are here http://sqlite.org/download.html

Please download sqlite-3_6_23_1.zip, and let me know your results

Thanks,

Noah

Original comment by noah.hart@gmail.com on 25 Jun 2010 at 3:24

• Changed state: Reviewed

[GoogleCodeExporter]

I do have a script that reproduces this problem.

The SQLite binaries work fine when reading/writing on 2GB+ files.

So here's what I did:

I created a database file over 2GB (2.4GB) using the latest System.Data.SQLite 
DLL and it works (reading/writing).

However, when I try doing the same with C#-Sqlite (latest), reading/writing 
doesn't work for anything over 2GB.

I've explored it, and it looks like the root of the problem is found in 
os_win_c.cs:

    static int sqlite3_fileSize(sqlite3_file id, ref int pSize)
    {
        ...
        pSize = id.fs.CanRead ? (int) id.fs.Length : 0;
        return SQLITE_OK;
    }

pSize should be a long.  I've changed this function to:

    static int sqlite3_fileSize(sqlite3_file id, ref sqlite3_int64 pSize)
    {   
        ... 
        pSize = id.fs.CanRead ? (sqlite3_int64) id.fs.Length : 0;   
        return SQLITE_OK;
    }

This affected many other classes (replacing int32 with long):
1. backup_c.cs
    -   static int backupTruncateFile(sqlite3_file pFile, sqlite3_int64 iSize)
        {
            sqlite3_int64 iCurrent = 0;
            ...
        }
2. Delegates.cs - public delegate int dxFileSize(sqlite3_file File_ID, ref 
sqlite3_int64 size);
3. memjournal_c.cs - static int memjrnlFileSize(sqlite3_file pJfd, ref 
sqlite3_int64 pSize)
4. os_c.cs - static int sqlite3OsFileSize( sqlite3_file id, ref sqlite3_int64 
pSize )
5. pager_c.cs
    -   static int read32bits(sqlite3_file fd, sqlite3_int64 offset, ref int pRes)
    -   static int readMasterJournal( sqlite3_file pJrnl, byte[] zMaster, u32 nMaster )
        { 
            ... 
            sqlite3_int64 szJ = 0;                  /* Total size in bytes of journal file pJrnl */
            ...
        }
    -   static int zeroJournalHdr( Pager pPager, int doTruncate )
        { 
            ... 
            sqlite3_int64 sz = 0;
            ...
        }
    -   static int writeMasterJournal( Pager pPager, string zMaster )
        {
            ...
            sqlite3_int64 jrnlSize = 0;                  /* Size of journal file on disk */
            ...
        }
    -   static int pager_truncate( Pager pPager, u32 nPage )
        {
            ...
            sqlite3_int64 currentSize = 0; sqlite3_int64 newSize;
            ...
        }
    -   static int pager_playback( Pager pPager, int isHot )
        {
            ...
            sqlite3_int64 szJ = 0;             /* Size of the journal file in bytes */
            ...
        }
    -   static int sqlite3PagerPagecount( Pager pPager, ref int pnPage )
        {
            ....
            sqlite3_int64 n = 0;              /* File size in bytes returned by OsFileSize() */
            ....
            nPage = (int) (n / pPager.pageSize);
            ....
        }

I think that's all of them.  If you change the above, it should compile and 
writing/reading 2GB+ files should work.  

I think there are places where some of the offsets are stored as int32.  I 
didn't catch them all but one place is the FilePoint class in memjournal_c.cs.  
I don't know about any others.

Hope this helps.

Original comment by fusoy...@gmail.com on 25 Jun 2010 at 11:12

[GoogleCodeExporter]

I'll make the changes now, can you send me the script so I can test them

Original comment by noah.hart@gmail.com on 25 Jun 2010 at 11:39

• Changed state: Accepted

[GoogleCodeExporter]

Fixed with changeset:   75:efd357f21d84

Please retest, since I've not have a chance to run the full test suite against 
the code

Original comment by noah.hart@gmail.com on 26 Jun 2010 at 12:20

• Changed state: Fixed

[GoogleCodeExporter]

static void Write4GBfile(IDbConnection conn)
{
    long targetFileSize = (long)Math.Pow(2, 32) - 1;
    int rowLength = 1024; // 2^10

    int loopCount = (int) (targetFileSize / rowLength);

    char[] chars = new char[rowLength];
    for (int i = 0; i < rowLength; i++)
    {
        chars[i] = 'A';
    }

    string row = new string(chars);

    conn.Open();
    IDbCommand cmd = conn.CreateCommand();

    try
    {
        cmd.CommandText = "PRAGMA cache_size = 16000; PRAGMA synchronous = OFF; PRAGMA journal_mode = MEMORY";
        cmd.ExecuteNonQuery();

        cmd.CommandText = "drop table if exists [MyTable]";
        cmd.ExecuteNonQuery();

        cmd.CommandText = "create table [MyTable] ([MyField] varchar(" + rowLength + ") null)";
        cmd.ExecuteNonQuery();

        cmd.CommandText = "insert into [MyTable] ([MyField]) VALUES ('" + row + "')";
        for (int i = 0; i < loopCount; i++)
        {
            cmd.ExecuteNonQuery();
        }
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.Message);
    }
    finally
    {
        cmd.Cancel();
        conn.Close();
        conn.Dispose();
    }
}

static void Main(string[] args)
{            
    string datasource = "file://c:/temp/myBigDb.s3db";

    using (IDbConnection conn = new SqliteConnection("uri=" + datasource))
    {
        Write4GBfile(systemDataSQLiteConnection);
    }
}

Original comment by fusoy...@gmail.com on 26 Jun 2010 at 12:33

[GoogleCodeExporter]

Issue 68 has been merged into this issue.

Original comment by noah.hart@gmail.com on 30 Jun 2010 at 3:55

[GoogleCodeExporter]

Additional fixes in changeset:   76:d6968f1d2545
date:        Tue Jul 06 08:49:24 2010 -0700

Original comment by noah.hart@gmail.com on 6 Jul 2010 at 3:50