MySQL Bugs: #38014: Concurrency violation in Update “TIME DATA TYPE”.

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:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	5.1.4, 5.1.5	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
DEFINING A TABLE WITH “TIME” DATA TYPE ..  THE ODBC ENGINE GENERATES A CONCURRENCY VIOLATION WHEN I TRY TO UPDATE A RECORDSET WHEN “TIME” FIELD IS NOT NULL.

How to repeat:
Module modTest
    ' CONSOLE APPLICATION
    ' VISUAL STUDIO 2005 - VISUAL BASIC 

    Private Const mnc_strMySqlServer As String = "www"
    Private Const mnc_strMySqlUid As String = "swcbeta"
    Private Const mnc_strMySqlPwd As String = "swcbeta"

    Private Const mnc_NumTest As Integer = 0

    Sub Main()

        Call TestMySql()

    End Sub

    Sub TestMySql()
        ' Connector/ODBC 5.1.4 
        ' 
        ' TEST
        '   WINDOWS VISTA SP1
        '   MySQL ServerVersion 5.0.18
        '   MySQL ServerVersion 5.1.25
        '
        '   WINDWOS 2000 SP4
        '   MySQL ServerVersion 5.0.18
        '
        '   WINDWOS XP SP3
        '   MySQL ServerVersion 5.0.18
        '
        Dim strDbName As String = "TestMysql"
        Dim strTableName As String = "Test_table"
        Dim dbConnection As System.Data.Common.DbConnection
        Dim cmdCommand As System.Data.Common.DbCommand

        dbConnection = New System.Data.Odbc.OdbcConnection
        dbConnection.ConnectionString = MyConnectionString("")
        Try
            dbConnection.Open()
        Catch ex As Exception
            Console.WriteLine(ex.ToString)
            Console.Read()
            End
        End Try
        cmdCommand = New System.Data.Odbc.OdbcCommand
        cmdCommand.Connection = dbConnection
        cmdCommand.CommandText = "DROP DATABASE IF EXISTS " & strDbName
        cmdCommand.ExecuteNonQuery()

        cmdCommand.CommandText = "CREATE DATABASE " & strDbName
        If cmdCommand.ExecuteNonQuery() = 1 Then
            dbConnection.Close()

            dbConnection = New System.Data.Odbc.OdbcConnection
            dbConnection.ConnectionString = MyConnectionString(strDbName)
            dbConnection.Open()

            cmdCommand.Connection = dbConnection
            cmdCommand.CommandText = "CREATE TABLE Test_Table ( ID_Test_Table INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY ( ID_Test_Table ) )"
            cmdCommand.ExecuteNonQuery()

            cmdCommand.CommandText = "ALTER TABLE Test_Table ADD COLUMN ( DbType_Time TIME NULL DEFAULT NULL )"
            cmdCommand.ExecuteNonQuery()

            Dim dtaDataAdapter As System.Data.Common.DbDataAdapter
            Dim cmdCommandBuilder As System.Data.Common.DbCommandBuilder

            dtaDataAdapter = New System.Data.Odbc.OdbcDataAdapter("SELECT * FROM Test_Table", CType(dbConnection, System.Data.Odbc.OdbcConnection))
            cmdCommandBuilder = New System.Data.Odbc.OdbcCommandBuilder(CType(dtaDataAdapter, Odbc.OdbcDataAdapter))

            Dim dtsDataSet As New System.Data.DataSet
            dtaDataAdapter.Fill(dtsDataSet)
            dtaDataAdapter.FillSchema(dtsDataSet, SchemaType.Source)

            Dim dtrNewRow As System.Data.DataRow
            dtrNewRow = dtsDataSet.Tables(0).NewRow
            dtrNewRow.Item(0) = 1
            ' ERROR concurrency violation if i set the item value in this way. 
            ' If i set the item value with null value the concurrency violation is not generated
            dtrNewRow.Item(1) = "16:56:25"
            dtsDataSet.Tables(0).Rows.Add(dtrNewRow)
            dtaDataAdapter.Update(dtsDataSet)
            dtsDataSet.AcceptChanges()

            dtrNewRow = dtsDataSet.Tables(0).NewRow
            dtrNewRow.Item(0) = 2
            ' ERROR concurrency violation if i set the item value in this way. 
            ' If i set the item value with null value the concurrency violation is not generated
            dtrNewRow.Item(1) = "16:56:25"
            dtsDataSet.Tables(0).Rows.Add(dtrNewRow)
            dtaDataAdapter.Update(dtsDataSet)
            dtsDataSet.AcceptChanges()

            If mnc_NumTest = 0 Then

                dtsDataSet.Tables(0).Dispose()
                dtsDataSet.Dispose()
                dtsDataSet = Nothing
                cmdCommandBuilder.Dispose()
                cmdCommandBuilder = Nothing
                dtaDataAdapter.Dispose()
                dtaDataAdapter = Nothing

                Dim dtaDataAdapterNew As System.Data.Common.DbDataAdapter
                Dim cmdCommandBuilderNew As System.Data.Common.DbCommandBuilder

                dtaDataAdapterNew = New System.Data.Odbc.OdbcDataAdapter("SELECT * FROM Test_Table", CType(dbConnection, System.Data.Odbc.OdbcConnection))
                cmdCommandBuilderNew = New System.Data.Odbc.OdbcCommandBuilder(CType(dtaDataAdapterNew, Odbc.OdbcDataAdapter))

                Dim dtsDataSetNew As New System.Data.DataSet
                dtaDataAdapterNew.Fill(dtsDataSetNew)
                dtaDataAdapterNew.FillSchema(dtsDataSetNew, SchemaType.Source)
                ' ERROR concurrency violation with any kind of value
                ' dtsDataSetNew.Tables(0).Rows(0).Item("DbType_Time") = "16:56:26"
                dtsDataSetNew.Tables(0).Rows(0).Item("DbType_Time") = System.DBNull.Value

                Try
                    ' ERROR concurrency violation
                    dtaDataAdapterNew.Update(dtsDataSetNew)
                    dtsDataSetNew.AcceptChanges()
                Catch ex As Exception
                    Console.Write(ex.ToString)
                End Try

            Else

                Call dtsDataSet.Tables(0).Rows(0).Delete()
                Try
                    ' ERROR concurrency violation
                    dtaDataAdapter.Update(dtsDataSet)
                    dtsDataSet.AcceptChanges()
                Catch ex As System.Data.DBConcurrencyException
                    Console.Write(ex.ToString)
                End Try

            End If

            cmdCommand = New System.Data.Odbc.OdbcCommand
            cmdCommand.Connection = dbConnection
            cmdCommand.CommandText = "DROP DATABASE IF EXISTS " & strDbName
            cmdCommand.ExecuteNonQuery()
            cmdCommand.Dispose()

            Console.Write(vbCrLf & "End test")
            Console.Read()
        End If

    End Sub

    Function MyConnectionString(ByVal strDbName As String) As String
        Dim strConnectionString As String

        strConnectionString = "DRIVER={MySQL ODBC 5.1 Driver};"
        strConnectionString &= "SERVER=" & mnc_strMySqlServer & ";"
        strConnectionString &= "UID=" & mnc_strMySqlUid & ";"
        strConnectionString &= "PWD=" & mnc_strMySqlPwd & ";"
        If strDbName <> "" Then
            strConnectionString &= "DATABASE=" & strDbName & ";"
        End If
        strConnectionString &= "OPTION=" & (2 Or 8 Or 16384) & ";"

        Return strConnectionString
    End Function

End Module

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.

myodbc Query Log on Server Version 5.0.18

Attachment: myodbc_QueryLogServerVersion5.0.18.sql (text/plain), 596 bytes.

ODBC Trace on Server Version 5.0.18

Attachment: SQL_ODBCTraceServerVersion5.0.18.LOG (application/octet-stream, text), 94.08 KiB.

ODBC Trace on Server Version 5.1.25

Attachment: SQL_ODBCTraceServerVersion5.1.25.LOG (application/octet-stream, text), 46.92 KiB.

Odbc Exception on Server Version 5.1.25

Attachment: Error_OdbcExceptionServerVersion5.1.25.txt (text/plain), 1.63 KiB.

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

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...

VB test case

Attachment: Bug38014.txt (text/plain), 3.13 KiB.

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.

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.