Bug #38014 | Concurrency violation in Update “TIME DATA TYPE”. | ||
---|---|---|---|
Submitted: | 10 Jul 2008 12:23 | Modified: | 21 Jul 2008 9:42 |
Reporter: | Fausto Ciccocioppo | Email Updates: | |
Status: | Can't repeat | Impact on me: | |
Category: | Connector / ODBC | Severity: | S2 (Serious) |
Version: | 5.1.4, 5.1.5 | OS: | Windows |
Assigned to: | CPU Architecture: | Any |
[10 Jul 2008 12:23]
Fausto Ciccocioppo
[17 Jul 2008 12:18]
Tonci Grgin
Hi Fausto and thanks for your report. Can you please attach: - Query log from MyODBC driver - Relevant part of general query log from server - ODBC trace so I can check this.
[21 Jul 2008 6:55]
Fausto Ciccocioppo
myodbc Query Log on Server Version 5.0.18
Attachment: myodbc_QueryLogServerVersion5.0.18.sql (text/plain), 596 bytes.
[21 Jul 2008 6:56]
Fausto Ciccocioppo
ODBC Trace on Server Version 5.0.18
Attachment: SQL_ODBCTraceServerVersion5.0.18.LOG (application/octet-stream, text), 94.08 KiB.
[21 Jul 2008 6:56]
Fausto Ciccocioppo
ODBC Trace on Server Version 5.1.25
Attachment: SQL_ODBCTraceServerVersion5.1.25.LOG (application/octet-stream, text), 46.92 KiB.
[21 Jul 2008 6:57]
Fausto Ciccocioppo
Odbc Exception on Server Version 5.1.25
Attachment: Error_OdbcExceptionServerVersion5.1.25.txt (text/plain), 1.63 KiB.
[21 Jul 2008 7:02]
Fausto Ciccocioppo
Hi Tonci, thanks for your attention. I sent you these files valid for two server version (5.0.18 - 5.1.25-rc-community): - query log - odbc trace Best regards Fausto Ciccocioppo
[21 Jul 2008 8:56]
Tonci Grgin
Fausto, thanks for info provided. This is just to keep you informed on progress while I'm looking into this problem. Your test case is just too long for me to analyze so I cut it down and managed to get exceptions but in *both* 5.0.68 and 5.1.28 MySQL servers. Since I have created and populated test table like this: mysql [root@localhost-5.0.68-pb10-log:5068] (test): create table bug38014( -> Id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, -> TimeFld TIME DEFAULT NULL); Query OK, 0 rows affected (0.14 sec) mysql [root@localhost-5.0.68-pb10-log:5068] (test): insert into bug38014 values -> (NULL,"16:54:01"), (NULL,"13:12:11"); Query OK, 2 rows affected (0.05 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql [root@localhost-5.0.68-pb10-log:5068] (test): select * from bug38014; +----+----------+ | Id | TimeFld | +----+----------+ | 1 | 16:54:01 | | 2 | 13:12:11 | +----+----------+ 2 rows in set (0.02 sec) I only needed to add this part of your code to get exception: dtsDataSet.Tables(0).Rows(0).Item(1) = System.DBNull.Value dtaDataAdapter.Update(dtsDataSet) dtsDataSet.AcceptChanges() What seems to be the problem is the inability of framework to handle PK column that is "nullable" (as MSSQL server does not know of this feature). Check this code: Dim schemaTable As DataTable Dim adapter As System.Data.Odbc.OdbcDataAdapter --<cut>-- cmdCommand = New System.Data.Odbc.OdbcCommand cmdCommand.Connection = dbConnection cmdCommand.CommandText = "SELECT * FROM bug38014" cmdCommand.CommandType = CommandType.Text schemaTable = New DataTable("bug38014") adapter = New OdbcDataAdapter(cmdCommand) adapter.FillSchema(schemaTable, SchemaType.Source) Console.WriteLine() Console.WriteLine("Col.name: " & schemaTable.Columns(0).ColumnName) Console.WriteLine("AllowDBNull: " & schemaTable.Columns(0).AllowDBNull) Console.WriteLine("Unique: " & schemaTable.Columns(0).Unique) If (schemaTable.PrimaryKey.Length = 0) Then Console.WriteLine("No primary key is defined for table!") End If schemaTable.Dispose() adapter.Dispose() --<cut>-- and you'll see the output like this: Col.name: Id AllowDBNull: True Unique: True No primary key is defined for table! which in turn results in "impossible" update statement formed and exception as update should have had modified at least 1 row: UPDATE bug38014 SET TimeFld = NULL WHERE (((0 = 1 AND Id IS NULL) OR (Id = 1)) AND ((0 = 1 AND TimeFld IS NULL) OR (TimeFld = '16:54:01''\'16:54:0'))) Getting exception trying to set value for field that is defined as AI is no surprise to me. This should be quite easy to work around by manually defining UPDATE SQL...
[21 Jul 2008 9:23]
Tonci Grgin
VB test case
Attachment: Bug38014.txt (text/plain), 3.13 KiB.
[21 Jul 2008 9:28]
Tonci Grgin
I would describe the root of the problem as "MyODBC 5.1.5 is unable to discover AI PK under .NET framework" although it might not be so and related only to TIME field type (as DATE type works, for example, and using SQLPrimaryKey() on same table gives correct result). Will see what others has to say. Fausto, please code UPDATE statement manually as a workaround until this is solved.
[21 Jul 2008 9:42]
Tonci Grgin
I stand corrected, using latest builds by Kent I am unable to repeat the problem with MyODBC 5.1. I guess this was fixed somewhere along the way. The only remaining thing is that 5.1 is unable to recognize AI PK, will inform others of this. Fausto, 5.1.5 will be released shortly.