System.Data.SQLite

This issue is related to the closed ticket [343d392b51] In fact the problem still remains. Please read the old ticket first.

As a workaround I now do not use adapter.Update() any longer (as it raises the exception). Instead of

String table = “table”; try { sql = "select * from table where ..."; da = new SQLiteDataAdapter(sql, db_con); da.Fill(ds, table); dt = ds.Tables[table]; … manipulate_data(); int i = da.Update(dt); // here the DBConcurrencyException raises !!!

I now use a stupid sql.command in a loop. … String sql = String.Format("update table set … “

SQLiteCommand cmd = new SQLiteCommand(sql, dbconnection); return cmd.ExecuteNonQuery(); …

This works, but it does not perform well and it is very ugly code. ________________________________

The DBConcurrencyException raises not on every adapter.Update() statement. Sometimes it works, sometimes not. Sometimes a few records are updated, sometimes just only one, sometimes none. Sometimes it raises on this table, sometimes on the other table. All my tables have as a first column a DateTime column (“CHANGE_DATE”). It does not matter whether I do row[“CHANGE_DATE”] = DateTime.Now; Or row["CHANGE_DATE"] = DateTime.Now.Year + "-" + DateTime.Now.Month + "-" + DateTime.Now.Day + " " + DateTime.Now.TimeOfDay;

regards, Simon

mistachkin added on 2011-12-25 23:31:09 UTC:
Are you using DateTimeFormat=JulianDay or UnixEpoch in the connection string? This is necessary to take advantage of the fix for the previous ticket you mentioned.

anonymous added on 2011-12-26 23:23:14 UTC:
If I use > DateTimeFormat=JulianDay < in the connectionstring the statement

xydatetime = reader.GetDateTime(0);

raises an exception:

TypeInitialisationException: wrong format _______

String sql = "select CHANGE_DATE, ... from TABLE .. ";

SQLiteCommand cmd = new SQLiteCommand(sql, dbconnection);

SQLiteDataReader reader = cmd.ExecuteReader();

if (reader.HasRows) while (reader.Read()) { ... xydatetime = reader.GetDateTime(0); ...

}

reader.Close();

mistachkin added on 2011-12-27 03:24:15 UTC:
When you use the JulianDay format, the type conversion code assumes that database fields with the type DATETIME (or DATE, etc) will actually be a floating point number. Likewise, when you use the UnixEpoch format, it assumes that database fields with the type DATETIME will actually be an integer number of seconds since the Unix epoch.

anonymous added on 2011-12-27 17:54:42 UTC:
connection string contains > DateTimeFormat=JulianDay <

the following code raises System.FormatException:

String Sql = "Select CHANGE_DATE, … from table ..”;

da = new SQLiteDataAdapter(sql, dbconnection);

da.Fill(dset, table);

The column CHANGE_DATE is of type DATETIME.

anonymous added on 2011-12-28 00:06:25 UTC:
Why did you close this ticket ?

mistachkin added on 2011-12-28 05:07:57 UTC:
Because the behavior you appear to describe is not a bug. When using the UnixEpoch or JulianDay formats in the connection string, the underlying data in the database itself must be of the type expected by the type conversion code in System.Data.SQLite. For UnixEpoch, it must be an integer. For JulianDay, it must be a double-precision floating-point number.

anonymous added on 2011-12-28 09:43:43 UTC:
Does that mean that I can't use columns of type DatTime ? Just to use the DateTimeFormat=JulianDay does not convert the underlying data in the database. Right ? Do I have to convert the data in the database first before working on it ?

mistachkin added on 2011-12-29 01:43:17 UTC:
Effectively, yes. The DateTimeFormat property of the connection string controls how System.Data.SQLite attempts to *interpret* the actual data. If you want to use JulianDay or UnixEpoch (or Ticks), the actual data in the database must be changed first. Alternatively, you can use ISO8601 if your DateTime strings match one of the supported format strings.

anonymous added on 2012-01-14 00:01:19 UTC:
this issue is drivin me nuts

I tried:

first version

connectionstring: DateTimeFormat= JulianDay

table product:

change_date datetime, price number, name varchar2, id integer

firstly, I did:

update product set change_date = 2455928.0 //which is 2012-01-01 12:00:00

then:

sql = “select change_date, id, name, price from product”;

adapter = new SQLiteDataAdapter(sql, db_con);

adapter.Fill(ds, datatab);

….

row[“price”] += 1;

row[“change_date”] = DateTime.Now;

….

adapter.update(datatab); <- here the DBConcurrencyException raises

_______________________________________________

second version:

same table;

connectionString: DateTimeFormat= JulianDay

update product set change_date = 2455928.0 //which is 2012-01-01 12:00:00

then

!! difference !!

sql = “select julianday(change_date) as change_date, id, name, price from product”;

adapter = new SQLiteDataAdapter(sql, db_con);

adapter.Fill(ds, datatab);

….

row[“price”] += 1.0;

// in fact now the column is of type System.Double

row[“change_date”] = (double)row[“change_date”] + 1.0;

….

adapter.update(datatab);

no exception raises, but what happens is:

the price column is updated, the DateTime-Column is not !

What's wrong ?

mistachkin added on 2012-01-14 03:00:45 UTC:

Instead of:

row["change_date"] = DateTime.Now;

Try:

row["change_date"] = SQLiteConvert.ToJulianDay(DateTime.Now);

anonymous added on 2012-01-14 14:27:33 UTC:
row["change_date"] = SQLiteConvert.ToJulianDay(DateTime.Now);

=> compiler says:

An object reference is required for the non-static field, method, or property 'System.Data.SQLite.SQLiteConvert.ToJulianDay(System.DateTime)'

mistachkin added on 2012-01-15 01:01:22 UTC:
Ok, that is an issue because those type conversion methods should be static as they do not need any instance data.

mistachkin added on 2012-01-15 01:28:17 UTC:
Changed to static by check-in [114024ffe7], with tests.

anonymous added on 2012-01-15 19:04:07 UTC:
connectionstring: DateTimeFormat= JulianDay

update product set change_date = 2455928.0 // which is 2012-01-01 12:00:00

change_date is of type DateTime !

----------------

sql = “select change_date, price, ....”;

adapter = new SQLiteDataAdapter(sql, db_con);

adapter.Fill(ds, datatab);

….

row[[price]] += 1;

row[[change_date]] = SQLiteConvert.ToJulianDay(DateTime.Now); <-

TypeConverionException raises,

which is clear, because:

the original SQLite table-column is of type DateTime, then the ADO-table is also of type System.DateTime, to which we want to assign a double.

System.ArgumentException: invalid type conversion "Double" to "DateTime".<2455941,93204139> cannot be saved to CHANGE_DATE column. Expected type: DateTime. ---> System.InvalidCastException: invalid conversion "Double" to "DateTime".

mistachkin added on 2012-01-16 01:00:09 UTC:
I forgot that the necessary conversions happen internally. Here is an example. Basically, this means the statement should actually be something like:

row["change_date"] = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.FFFFFFFK");

anonymous added on 2012-01-18 21:07:58 UTC:
I still have the DBConcurrencyException issue.

I now use DateTimeFormat=JulianDay as you recommended. I converted the data to floats as described: update product set change_date = 2455928.0 // which is 2012-01-01 12:00:00

I now use: row["change_date"] = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.FFFFFFFK");

but the DBConcurrencyException still raises (not everytime, but most often)

anonymous added on 2012-02-03 06:56:17 UTC:
Did you see my last comment ? The issue still exists. I tested with 1.0.79.0 - no changes. I think it's a deeper problem, probably linked to [d76b409d07] What can I do ?

mistachkin added on 2012-02-03 13:46:16 UTC:
I'm still unable to reproduce the problem at all here. One thing to try (using the latest checked-in code, not 1.0.79.0) is to set the Flags property of the SQLiteConnection object to LogPrepare and then watch the trace output to see what SQL statement is returning zero records. Then, add that SQL statement to this ticket so I can figure out where things are going wrong.

anonymous added on 2012-02-05 23:02:43 UTC:
ok, will do. for the first the exception in detail:

e.Source: System.Data

e.Data: System.Collections.ListDictionaryInternal

e.StackTrace: at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount) at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount) at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping) at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping) at System.Data.Common.DbDataAdapter.Update(DataTable dataTable) at Test.mytest.mytest.UpdateTest(DataTable dt) in C:\Users\admin\Documents\Visual Studio 2008\Projects\Test\Test\mytest\mytest.cs:Line 236.

e.TargetSite: Int32 UpdatedRowStatusErrors(System.Data.Common.RowUpdatedEventArgs, BatchCommandInfo[], Int32)

e.Message: Concurrency violation: the UpdateCommand affected 0 of the expected 1 records.

mistachkin added on 2012-02-06 22:01:43 UTC:
The exception information itself is not particularly illuminating. The SQL statement causing the issue will be far more informative.

anonymous added on 2012-02-08 13:42:08 UTC:
5 rows where updated (of ~200) then the exception ocurred.

trace output:

...

Prepare: 123241624

SQLite message (0): Preparing {UPDATE [main].[PRODUCT] SET [CHANGE_DATE] = @param1, [ID] = @param2, [PRICE] = @param3 WHERE (([CHANGE_DATE] = @param4) AND ([ID] = @param5) AND ((@param6 = 1 AND [PRICE] IS NULL) OR ([PRICE] = @param7)))}...

Prepare: 123239424

'Test.vshost.exe' (Managed): Loaded 'C:\Windows\assembly\GAC_MSIL\System.Data.resources\2.0.0.0_de_b77a5c561934e089\System.Data.resources.dll'

A first chance exception of type 'System.Data.DBConcurrencyException' occurred in System.Data.dll

...

mistachkin added on 2012-02-08 21:13:05 UTC:
Judging from the query you posted, it looks like this could again be a DateTime format issue since there is a DateTime column in the WHERE clause. Would it be possible to figure out what parameter values are being used at that point?

anonymous added on 2012-02-09 23:19:57 UTC:
again 5 rows where updated, then the exception ocurred

code:

cstr = new SQLiteConnectionStringBuilder();

cstr.DataSource = myDB;

cstr.Version = 3;

cstr.DateTimeKind = DateTimeKind.Local;

cstr.DateTimeFormat = SQLiteDateFormats.JulianDay;

cstr.Flags = SQLiteConnectionFlags.LogPrepare;

...

for (int i = 0; i < rcount; i++)

{

System.Threading.Thread.Sleep(5000);

dt.Rows[i]["PRICE"] = (double)dt.Rows[i]["PRICE"] + 5.123;

string s = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.FFFFFFFK");

dt.Rows[i]["CHANGE_DATE"] = s;

}

---

adapter.Update();

string s outputs:

2012-02-09 23:51:24.746+01:00

2012-02-09 23:51:29.756+01:00

2012-02-09 23:51:34.762+01:00

2012-02-09 23:51:39.772+01:00

2012-02-09 23:51:44.777+01:00

2012-02-09 23:51:49.787+01:00

2012-02-09 23:51:54.797+01:00

2012-02-09 23:51:59.807+01:00

2012-02-09 23:52:04.817+01:00

2012-02-09 23:52:09.823+01:00

2012-02-09 23:52:14.829+01:00

2012-02-09 23:52:19.835+01:00

and so on

mistachkin added on 2012-02-10 06:55:38 UTC:
I've been trying to reproduce this issue here based on the modified example you provided; however, no matter what variation I try, I cannot get the concurrency exception. Here is my current test case (the complete C# code is embedded within the test script itself):

runTest {test tkt-4bbf851fa5-1.1 {SQLiteDataAdapter update fail} -setup {
  setupDb [set fileName tkt-4bbf851fa5-1.1.db]
} -body {
  set id [object invoke Interpreter.GetActive NextId]
  set dataSource [file join [getDatabaseDirectory] $fileName]

  set sql(inserts) ""
  set sql(1) [subst { \
    CREATE TABLE t1(y DATETIME PRIMARY KEY, z FLOAT); \
    [for {set i 1} {$i < 10} {incr i} {
      append sql(inserts) [appendArgs \
          "INSERT INTO t1 (y, z) VALUES(JULIANDAY('" \
          [clock format $i -format [getDateTimeFormat]] "'), " $i "); "]
    }; return [expr {[info exists sql(inserts)] ? $sql(inserts) : ""}]] \
  }]

  set sql(2) [subst { \
    SELECT y, z FROM t1 ORDER BY y; \
  }]

  unset -nocomplain results errors

  set code [compileCSharpWith [subst {
    using System;
    using System.Data;
    using System.Data.SQLite;

    namespace _Dynamic${id}
    {
      public class Test${id}
      {
        public static void Main()
        {
          SQLiteConnectionStringBuilder connStr =
              new SQLiteConnectionStringBuilder();

          connStr.DataSource = "${dataSource}";
          connStr.Version = 3;
          connStr.DateTimeKind = DateTimeKind.Local;
          connStr.DateTimeFormat = SQLiteDateFormats.JulianDay;
          connStr.Flags = SQLiteConnectionFlags.LogPrepare;

          using (SQLiteConnection connection = new SQLiteConnection(
              connStr.ToString()))
          {
            connection.Open();

            using (SQLiteCommand command = connection.CreateCommand())
            {
              command.CommandText = "${sql(1)}";
              command.ExecuteNonQuery();
            }

            using (SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter(
                "${sql(2)}", connection))
            {
              using (DataSet dataSet = new DataSet())
              {
                dataAdapter.Fill(dataSet, "t1");

                DataTable dataTable = dataSet.Tables\["t1"\];

                dataTable.Columns\["y"\].Unique = true;
                dataTable.PrimaryKey = new DataColumn\[\] {
                  dataTable.Columns\["y"\]
                };

                [expr {[isMono] ? "#pragma warning disable 219" : ""}]
                SQLiteCommandBuilder commandBuilder =
                    new SQLiteCommandBuilder(dataAdapter);
                [expr {[isMono] ? "#pragma warning restore 219" : ""}]

                for (int index = 0; index < dataTable.Rows.Count - 1; index++)
                {
                  System.Threading.Thread.Sleep(1000);

                  dataTable.Rows\[index\]\["y"\] =
                      DateTime.Now.ToString("[getDateTimeFormat]");

                  dataTable.Rows\[index\]\["z"\] =
                      (double)dataTable.Rows\[index\]\["z"\] + 1.123;
                }

                dataAdapter.Update(dataTable); // DBConcurrencyException (?)
              }
            }
          }
        }
      }
    }
  }] true true true results errors System.Data.SQLite.dll]

  list $code $results \
      [expr {[info exists errors] ? $errors : ""}] \
      [expr {$code eq "Ok" ? [catch {
        object invoke _Dynamic${id}.Test${id} Main
      } result] : [set result ""]}] $result
} -cleanup {
  cleanupDb $fileName

  unset -nocomplain result code results errors sql dataSource id db fileName
} -constraints \
{eagle monoBug28 command.sql compile.DATA SQLite System.Data.SQLite} -match \
regexp -result {^Ok System#CodeDom#Compiler#CompilerResults#\d+ \{\} 0 \{\}$}}

mistachkin added on 2012-02-10 19:13:56 UTC:
By any chance, is there another thread or process modifying the database at the same time as the data adapter is trying to?

anonymous added on 2012-02-14 07:33:46 UTC:
I've sent an email to sqlite-dev with example code an a db-file which generates the exception. Did you see it ?

mistachkin added on 2012-02-14 09:59:01 UTC:
I haven't seen it yet. Also, since the mailing list strips attachments, it might be best to upload it somewhere public (e.g. Dropbox) and then provide a link to it in the email.

anonymous added on 2012-02-14 11:47:02 UTC:
here is the link:

db-file: http://dl.dropbox.com/u/62206335/xy.db3

code: http://dl.dropbox.com/u/62206335/code.txt

I found another strange behaviour: the test code likes to override the price with the value 5.123. the first ten rows are updated but with the value 5, not 5.123. Then the exception occurs. (it's the same code i've sent by email)

mistachkin added on 2012-02-14 21:47:05 UTC:
Thanks. I think your example will be helpful in tracking this issue down. As for the PRICE issue, the "NUMBER" column type is not recognized by SQLite nor System.Data.SQLite. I would use DOUBLE or REAL instead.

mistachkin added on 2012-02-14 22:14:23 UTC:

It appears the mixture of data in the database combined with the NUMBER column type is the root cause of the issue. For example, try this query with your database:

    SELECT typeof(PRICE) FROM product;

Notice that some of the results say "integer" and others "real". For SQLite, this is not a big deal, since it will simply default the column affinity to TEXT; however, System.Data.SQLite uses the column type information to provide a mapping to the actual .NET type and database column type. In the case of "NUMBER", it returns the default, DbType.Object.

At that point, I think .NET attempts to figure out the type of the column based on the data currently in it for the row (this is just an educated guess) and then attempts to convert the new value to that type. If the new value ends up being truncated from REAL to INTEGER, for example, it could result in 5.123 becoming 5, as you stated. This may cause the exception, for example, if the value was actually 5 to start with.

mistachkin added on 2012-02-14 22:21:31 UTC:
For the future, I've added a Trace warning (in the Debug build only) to the type mapping code, see check-in [10fae679b2].

anonymous added on 2012-02-15 07:01:55 UTC:
thanks.

and just for your information:

you also closed ticket [d76b409d07] which was not my ticket. (and this guy uses only varchars and one integer prim key, seems that my problem is not his problem)

Simon

mistachkin added on 2012-02-15 07:56:04 UTC:
Given the rather extensive amount of analysis and testing I have conducted on these concurrency exception issues and the fact that this ticket and another ticket like it were both caused by data type conversion issues outside of the direct control of the library, I am fairly confident that the final such ticket, [d76b409d07], does not actually represent a real issue with SQLite or System.Data.SQLite. That being said, if the original reporter discovers new information or evidence to point to an actual issue, he can always re-open the ticket.

anonymous added on 2012-02-18 11:13:37 UTC:
if tested - no changes.

I started with a complete new db-file, created a table product with one Datetime column, one integer prim key column and then only text and real columns. Again I inserted my 180 products.

after round about ten runs of my program with each updates round about 30 rows correctly with no problems the exception ocurrs again, having updated only 15 rows.

running the given code with the given dbfile will update again only 15 rows before the exception occurs.

http://dl.dropbox.com/u/62206335/code2.txt

http://dl.dropbox.com/u/62206335/code2.txt

anonymous added on 2012-02-18 16:31:33 UTC:
correct links:

http://dl.dropbox.com/u/62206335/code2.txt

http://dl.dropbox.com/u/62206335/abc.db3

mistachkin added on 2012-02-19 12:10:11 UTC:
After researching this issue, it appears to be caused by rounding errors in the double precision values being used for the Julian Day numbers. For example, the UPDATE query generated by the .NET Framework data adapter subsystem for the first row in the table looks something like this:

  UPDATE [main].[t1] SET [y] = @param1, [z] = @param2
  WHERE (([x] = @param3) AND ([y] = @param4) AND ([z] = @param5))

And the values for the parameters are as follows:

  @param1 = 2455976.6870139237 (double)
  @param2 = 9.876 (double)
  @param3 = 1 (long)
  @param4 = 2455975.9869903936 (double)
  @param5 = 5.123 (double)

The problem is that the actual first row in the table contains this data:

  INSERT INTO t1 (x, y, z) VALUES(1, 2455975.98699039, 5.123);

This causes the WHERE clause in the above UPDATE query to match zero rows because 2455975.98699039 (in database) is not equal to 2455975.9869903936 (query parameter).

anonymous added on 2012-02-19 22:42:13 UTC:
so the question is: where do the additional 2 digits come from ? and it is not an immediate explanation for the exception.

mistachkin added on 2012-02-20 00:13:42 UTC:
First, the WHERE clause in the UPDATE query returning zero rows is precisely what causes the exception.

The "extra" digits are presumably caused by double precision rounding errors, see:

http://docs.oracle.com/cd/E19957-01/806-3568/ncg_goldberg.html

anonymous added on 2012-02-20 00:28:39 UTC:
wow, for that article I need days ...

but what I don't understand is the cause for the exception. If the where clause does not match, ok, so what, the row will not be updated. But why the exception ?

mistachkin added on 2012-02-20 04:10:09 UTC:
The exception occurs because the .NET Framework tries to UPDATE one row at a time and if zero rows are matched, it throws the exception automatically.