MonoTouch SQLite Insert Error - Unable to open the database file

Question

I'm using MonoTouch and have run into some issues with SQLite:

After a few hundred, sometimes thousands, of inserts into a Sqlite Database, using the iPad simulator on a Mac, the following error occurs:

<<

    Mono.Data.Sqlite.SqliteException: Unable to open the database file
      at Mono.Data.Sqlite.SQLite3.Reset (Mono.Data.Sqlite.SqliteStatement stmt)
    [0x0008a] in /Developer/MonoTouch/Source/mono/mcs/class/Mono.Data.Sqlite/Mono.Data.Sqlite_2.0/SQLite3.cs:226 
      at Mono.Data.Sqlite.SQLite3.Step (Mono.Data.Sqlite.SqliteStatement stmt)
    [0x00046] in /Developer/MonoTouch/Source/mono/mcs/class/Mono.Data.Sqlite/Mono.Data.Sqlite_2.0/SQLite3.cs:168 
      at Mono.Data.Sqlite.SqliteDataReader.NextResult () [0x00129] in
    /Developer/MonoTouch/Source/mono/mcs/class/Mono.Data.Sqlite/Mono.Data.Sqlite_2.0/SQLiteDataReader.cs:908 
      at Mono.Data.Sqlite.SqliteDataReader..ctor (Mono.Data.Sqlite.SqliteCommand
    cmd, CommandBehavior behave) [0x00051] in /Developer/MonoTouch/Source/mono/mcs/class/Mono.Data.Sqlite/Mono.Data.Sqlite_2.0/SQLiteDataReader.cs:89 
      at (wrapper remoting-invoke-with-check) Mono.Data.Sqlite.SqliteDataReader:.ctor (Mono.Data.Sqlite.SqliteCommand,System.Data.CommandBehavior)
      at Mono.Data.Sqlite.SqliteCommand.ExecuteReader (CommandBehavior behavior) [0x00006] in
    /Developer/MonoTouch/Source/mono/mcs/class/Mono.Data.Sqlite/Mono.Data.Sqlite_2.0/SQLiteCommand.cs:539 
      at Mono.Data.Sqlite.SqliteCommand.ExecuteNonQuery () [0x00000] in
    /Developer/MonoTouch/Source/mono/mcs/class/Mono.Data.Sqlite/Mono.Data.Sqlite_2.0/SQLiteCommand.cs:568 
      at iDispatch.DevVehicle.Insert () [0x0003f] in
    /Users/Projects/iDispatch/iDispatch/DevVehicleDAL.cs:240 

>>

This is typically followed by an internal stack trace:

<<

    Stacktrace:

      at (wrapper managed-to-native) Mono.Data.Sqlite.UnsafeNativeMethods.sqlite3_prepare (intptr,intptr,int,intptr&,intptr&) <IL 0x0002a, 0xffffffff>
      at Mono.Data.Sqlite.SQLite3.Prepare (Mono.Data.Sqlite.SqliteConnection,string,Mono.Data.Sqlite.SqliteStatement,uint,string&) [0x00044] in
    /Developer/MonoTouch/Source/mono/mcs/class/Mono.Data.Sqlite/Mono.Data.Sqlite_2.0/SQLite3.cs:258
      at Mono.Data.Sqlite.SqliteCommand.BuildNextCommand () [0x00019] in /Developer/MonoTouch/Source/mono/mcs/class/Mono.Data.Sqlite/Mono.Data.Sqlite_2.0/SQLiteCommand.cs:230
      at Mono.Data.Sqlite.SqliteCommand.GetStatement (int) [0x0000b] in /Developer/MonoTouch/Source/mono/mcs/class/Mono.Data.Sqlite/Mono.Data.Sqlite_2.0/SQLiteCommand.cs:264
      at (wrapper remoting-invoke-with-check) Mono.Data.Sqlite.SqliteCommand.GetStatement (int) <IL 0x00039, 0xffffffff>
      at Mono.Data.Sqlite.SqliteDataReader.NextResult () [0x000cc] in
    /Developer/MonoTouch/Source/mono/mcs/class/Mono.Data.Sqlite/Mono.Data.Sqlite_2.0/SQLiteDataReader.cs:891
      at Mono.Data.Sqlite.SqliteDataReader..ctor
    (Mono.Data.Sqlite.SqliteCommand,System.Data.CommandBehavior) [0x00051] in /Developer/MonoTouch/Source/mono/mcs/class/Mono.Data.Sqlite/Mono.Data.Sqlite_2.0/SQLiteDataReader.cs:89
      at (wrapper remoting-invoke-with-check) Mono.Data.Sqlite.SqliteDataReader..ctor (Mono.Data.Sqlite.SqliteCommand,System.Data.CommandBehavior) <IL 0x00021, 0xffffffff>
      at Mono.Data.Sqlite.SqliteCommand.ExecuteReader (System.Data.CommandBehavior)
    [0x00006] in /Developer/MonoTouch/Source/mono/mcs/class/Mono.Data.Sqlite/Mono.Data.Sqlite_2.0/SQLiteCommand.cs:539
      at Mono.Data.Sqlite.SqliteCommand.ExecuteNonQuery () [0x00000] in /Developer/MonoTouch/Source/mono/mcs/class/Mono.Data.Sqlite/Mono.Data.Sqlite_2.0/SQLiteCommand.cs:568
      at iDispatch.DevVehicle.Insert () [0x0003f] in /Users/Projects/iDispatch/iDispatch/DevVehicleDAL.cs:240
      at iDispatch.ThreadSQL.DoWorkSQL () [0x0038a] in /Users/Projects/iDispatch/iDispatch/ThreadSQL.cs:140
      at System.Threading.Thread.StartUnsafe () [0x00016] in /Developer/MonoTouch/Source/mono/mcs/class/corlib/System.Threading/Thread.cs:684
      at (wrapper runtime-invoke) object.runtime_invoke_void__this__ (object,intptr,intptr,intptr) <IL 0x0004e, 0xffffffff>

Native stacktrace:

    0   iDispatch                           0x000d0db5 mono_handle_native_sigsegv + 343
    1   iDispatch                           0x0000f80c mono_sigsegv_signal_handler + 322
    2   libSystem.B.dylib                   0x9833705b _sigtramp + 43
    3   ???                                 0xffffffff 0x0 + 4294967295
    4   libsqlite3.dylib                    0x03645dc1 sqlite3DbStrNDup + 33
    5   libsqlite3.dylib                    0x03645e07 sqlite3NameFromToken + 23
    6   libsqlite3.dylib                    0x036486dc sqlite3IdListAppend + 92
    7   libsqlite3.dylib                    0x03682720 sqlite3Parser + 11904
    8   libsqlite3.dylib                    0x03684536 sqlite3RunParser + 310
    9   libsqlite3.dylib                    0x03684b34 sqlite3Prepare + 276
    10  libsqlite3.dylib                    0x03684fa7 sqlite3LockAndPrepare + 151
    11  ???                                 0x10c309ff 0x0 + 281217535
    12  ???                                 0x10c2fd1c 0x0 + 281214236
    13  ???                                 0x10c2f857 0x0 + 281213015
    14  ???                                 0x10c2f58a 0x0 + 281212298
    15  ???                                 0x10c2f50a 0x0 + 281212170
    16  ???                                 0x10c2eeea 0x0 + 281210602
    17  ???                                 0x10c2ea3c 0x0 + 281209404
    18  ???                                 0x10c2e8d6 0x0 + 281209046
    19  ???                                 0x10c2cc9e 0x0 + 281201822
    20  ???                                 0x10c2cb30 0x0 + 281201456
    21  ???                                 0x10fde444 0x0 + 285074500
    22  ???                                 0x10bb6786 0x0 + 280717190
    23  ???                                 0x106f2cba 0x0 + 275721402
    24  ???                                 0x077cfbf0 0x0 + 125631472
    25  iDispatch                           0x0000f5c7 mono_jit_runtime_invoke + 1332
    26  iDispatch                           0x001ed281 mono_runtime_invoke + 137
    27  iDispatch                           0x001ee8d1 mono_runtime_delegate_invoke + 111
    28  iDispatch                           0x00229c74 start_wrapper_internal + 692
    29  iDispatch                           0x00229cc2 start_wrapper + 17
    30  iDispatch                           0x00266b99 thread_start_routine + 191
    31  iDispatch                           0x002974f1 GC_start_routine + 107
    32  libSystem.B.dylib                   0x982fe259 _pthread_start + 345
    33  libSystem.B.dylib                   0x982fe0de thread_start + 34
* Assertion at ../../../../mono/mini/mini-exceptions.c:2188, condition `res != -1' not met
>>

FYI: iDispatch is the name of my program.

Now, although I'm inserting into multiple tables using multiple methods, the problem randomly arises in any and all of the methods. Typically the insert method looks something like this:

<<

    public void BulkInsert(StandardEquipmentCollection sec)
    {
        try
        {
            Connection.Open();

            SQLiteCommand cmd = Connection.CreateCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText =
                "INSERT INTO StandardEquipment (" + "Category, Description, VehicleID" +
                ") VALUES (" + "@Category, @Description, @VehicleID)";
            foreach (StandardEquipment se in sec)
            {
                cmd.Parameters.AddWithValue("@Category", se.Category);
                cmd.Parameters.AddWithValue("@Description", se.Description);
                cmd.Parameters.AddWithValue("@VehicleID", se.VehicleID);
                cmd.ExecuteNonQuery();
            }

            Connection.Close();
        }
        catch (Exception ex)
        {
            threadSQL.AddOutQ("Error: StandardEquipment BulkInsert - " + ex);
        }
    }

>>

The problem arises after 100's, sometimes 1000's, of records have been inserted.

Those that make the insert into the table look fine (I use Navicat Lite), and the ones it errors on look good as well (i.e. from the debugger I can see that all the data is correct, as is the format).

I have the exact same code running on Windows (ADO.NET 2.0 Data Provider for SQLite), Mono for Windows, and Mono running on the MacBook Pro...only on MonoTouch (version 2.4.2; Release ID: 20402005; Git revision: b7eeb779e1134c204f22d9fd5b6ebec4d758f60d; Build date: 2011-04-22 22:48:43+0000) do I see this error.

Any help would be greatly appreciated!

BTW: After searching online for a solution, I added the following 'Persist Journal' command, but it had no effect:

<<
SqliteConnectionStringBuilder connectionStringBuilder = new SqliteConnectionStringBuilder ();
connectionStringBuilder.JournalMode = SQLiteJournalModeEnum.Persist;
connectionStringBuilder.DataSource = dbPath;
SqliteConnection Connection = new SqliteConnection(connectionStringBuilder.ConnectionString);
>>

Thanks!

You might try adding the parameters to the command object's Parameters collection before you reach your foreach loop, and then assign the value of the parameters inside the loop. E.g. `command.Parameters.Add("@ID", SqlDbType.Int);` outside the loop; and then inside the loop: `command.Parameters["@ID"].Value = customerID;` http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparametercollection.addwithvalue.aspx — Tim, Aug 24 '11 at 20:24
In the foreach clause you should clear parameters before adding new. I think you hit the limit of number of parameters — Petar Petrov, Apr 05 '12 at 10:19

score 2 · Accepted Answer · edited May 23 '17 at 11:50

2

From your description this looks like a out-of-memory condition. Remember that iOS devices do not have much memory compared to desktop computer. You may be exhausting memory too fast and crash inside unmanaged code. e.g. The following line will be hit only for a SEGSIGV (from native code) when no debugger is attached:

* Assertion at ../../../../mono/mini/mini-exceptions.c:2188, condition `res != -1' not met

To fix this try disposing your objects (all the ones that implement IDisposable), either with using or calling Dispose and/or recycle them when possible.

Have a look at How can I recycle my SqliteCommand to speed up this Sqlite bulk insert (iOS)? for the later.

edited May 23 '17 at 11:50

Community

1
1

answered Aug 24 '11 at 17:28

poupou

43,413
6
77
174

Thanks you! I looked and used the ideas in the code you linked to (recycle SqliteCommand) and solved the issue: – Bob Aug 29 '11 at 16:58
I'm glad it did work for you :-). You can accept the answer so it gets marked as such (for anyone who search for a similar issue). Also are you the same 'Bob' that filled http://bugzilla.xamarin.com/show_bug.cgi?id=339 ? If so I'll mark it as fixed (and add a link to this questions). – poupou Aug 29 '11 at 17:42

MonoTouch SQLite Insert Error - Unable to open the database file

1 Answers1