Click here to Skip to main content
14,641,343 members
Rate this:
Please Sign up or sign in to vote.
See more:
I wrote the following to test/workout a problem I've been having dealing with a MySQL database.

I can update a single record without a problem but when I try to update multiple records I get a Concurrency violation

The only field I'm trying to update is a date field which was left null, I created the same 'setup' with the test database table (null dates) and it ran just fine.

The original table is more complex than the test database (multiple indexes and more fields) but I set the permissions exactly the same, using the same user account/password...

Here's the 'test' code - it was written on a windows form but I stripped all the b.s. out of it... UpdateMySQL was called with either a 1 or 2, 1 worked, 2 failed.

Imports MySql.Data.MySqlClient
Public Class mysql_test
    Public Sub UpdateMySQL(TestPass)
        Dim myTableName As String
        If TestPass = 1 Then
            myTableName = "test_table"
        Else
            myTableName = "live_table"
        End If
        Dim SQL As String = "Select * from " & myTableName
        Dim Conn As MySqlConnection
        Dim myDataAdapter As New MySqlDataAdapter
        Dim MySQL_Command As New MySqlCommand
        Dim MyDataTable As New DataTable
        Conn = New MySqlConnection()
        If TestPass = 1 Then
            Conn.ConnectionString = "server=mysqlserver;user id=userid;password=useridpassword;database=testdb;"
        Else
            Conn.ConnectionString = "server=mysqlserver;user id=userid;password=useridpassword;database=livedb;"
        End If
        Try
            Conn.Open()
            MySQL_Command.Connection = Conn
            MySQL_Command.CommandText = SQL
            myDataAdapter.SelectCommand = MySQL_Command
            myDataAdapter.Fill(MyDataTable)
            With MyDataTable
                Dim r As Integer = .Rows.Count
                For x = 0 To .Rows.Count - 1
                    If TestPass = 1 Then
                        .Rows(x).Item("date2") = .Rows(x).Item("date1")
                        .Rows(x).Item("date1") = Now
                    Else
                        .Rows(x).Item("date_modified") = .Rows(x).Item("date_entered")
                    End If
                Next
            End With
            Dim cb As MySqlCommandBuilder = New MySqlCommandBuilder(myDataAdapter)
            myDataAdapter.Update(MyDataTable)   ' Concurrency violation: the UpdateCommand affected 0 of the expected 1 records.
            myDataAdapter.AcceptChangesDuringUpdate = True
        Catch ex As MySqlException
            MsgBox("MySQL Error: " & ex.Message)
        Finally
            Conn.Close()
            Conn.Dispose()
        End Try
    End Sub
End Class


What I have tried:

I can't see anything wrong with the code, maybe not the best way to code this but since it works on one database and not the other, it leads me to think there is something 'odd' with the database/MySQL setup itself?

Any pointers in the right direction, greatly appreciated, if I loose anymore hair I'll be Homer Simpsons twin! ;)
Posted
Updated 22-Apr-16 6:59am
Comments
CHill60 22-Apr-16 4:59am
   
It's a red herring that TestPass = 1 works whilst = 2 does not - you're updating different columns on different tables.
You could however look at the schema for livedb.live_table.date_modified and compare that to the date1/date2 fields. Also are the regional settings on both databases the same?
This appears to be a known problem with CommandBuilder and DataAdaptors
Use GetUpdateCommand on the command builder(s) to really have a look at the sql that is being built to see if you spot any differences there.
You could try cb.ConflictOption = ConflictOption.OverwriteChanges which seems to be a popular work around.
Or, you could try doing it manually instead of using the command builder
bayotle 22-Apr-16 12:53pm
   
Thanks, that pushed me in a different direction that may or may not be a valid solution but for now the initial problem is resolved.

I added the cb.ConflictOption and took a quick 'peek' and then got the idea to replace * with the fields I was actually updating and it worked..

a day wasted on trying to correct an issue in the db just so I could resume recoding other issues!

Thanks!
CHill60 22-Apr-16 12:56pm
   
Glad you're over that hump! And it is always a good idea to list the columns instead of using *.
If you don't mind I'll transfer the comment to a solution in case anyone else has a similar issue (some folks don't bother to read the comments!)
bayotle 22-Apr-16 19:36pm
   
I did already! ;)
Thanks for the kick!
Rate this:
Please Sign up or sign in to vote.

Solution 1

I am not sure if that could be the problem:
.Rows(x).Item("date2") = .Rows(x).Item("date1")
.Rows(x).Item("date1") = Now

You try to update date2 with the value of date1 and at the same time to set date1 to Now. The query generator could run into troubles.
I'd add a temporary variable, e.g.
Dim tmp as DateTime =  .Rows(x).Item("date1")
.Rows(x).Item("date2") = tmp
.Rows(x).Item("date1") = Now
   
Comments
bayotle 22-Apr-16 4:47am
   
Thanks for the quick reply!
Actually those lines do work, it's the date_modified / date_entered line that's failing...
The other lines were test code to see wth was going on! ;)
Bernhard Hiller 22-Apr-16 5:37am
   
Did you try the trick with the temporary variable here?
bayotle 22-Apr-16 10:43am
   
Actually yes, and the date1/date2 work fine for 100's of records, the date_modified and date_entered work for one record but if I try to update multiples, it fails on update (where marked)
Rate this:
Please Sign up or sign in to vote.

Solution 2

As per our conversation in the comments section:

It's a red herring that TestPass = 1 works whilst = 2 does not - you're updating different columns on different tables.

You could however look at the schema for livedb.live_table.date_modified and compare that to the date1/date2 fields. Also are the regional settings on both databases the same?

This appears to be a known problem with CommandBuilder and DataAdaptors

Use GetUpdateCommand on the command builder(s) to really have a look at the sql that is being built to see if you spot any differences there.

You could try cb.ConflictOption = ConflictOption.OverwriteChanges which seems to be a popular work around.

Or, you could try doing it manually instead of using the command builder.

OP also commented that they replaced * with the specific list of columns they intended to update - it's always a good idea to list the columns explicitly in queries.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100