MySQL Dataadapter - Problems since Mysql-Update

[obi]

Hi...

 

I'm running an onlineshop on an Ubuntu-Server and wrote a program to update the shop-database (MySQL) with data from internal SQL-Server DB.

 

Since I updated MySQL on the Ubuntu-Server from 5.1.41 to 5.1.61 (that update came automatically for security reasons) i have a weird problem.

 

First: every access to the MySQL-DB vie the shop(s) and from our CMS (Drupal) works perfect. No Problems at all.

 

For Speed-Reasons I use datatables/dataadapter to update the shop data in memory and write it back to MySQL after all neccessary updates/inserts/deletes are done. This worked perfect until the MySQL-Update.

 

Here is some sample code from my program that produces the following problem:

The first run works perfect, if the code is called a second time, the result from mysql for oxadapter.fill is empty (0 records) but there are DEFINITELY records in the DB. So on the second run all records coming from the internal sql-server are treated as new records and at the update i get a duplicate key. Why is the MySQL-Result empty? And why this happens just now since MySQL is updated?

 

...
...
   Private SteinConn As SqlConnection
   Private SteinSQL As SqlCommand
   Private SteinAdapter As SqlDataAdapter

   Private OxConn As MySqlConnection
   Private OxSQL As MySqlCommand
   Private OxAdapter As MySqlDataAdapter
   Private OxCB As MySqlCommandBuilder
...
...
...


Private Sub text()
       Dim dtStein As New DataTable
       Dim dtOxid As New DataTable


       ' connect databases
       SteinConn = New SqlConnection(strConnectSQL)        
       OxConn = New MySqlConnection(strConnectMYSQL)
       SteinConn.Open()                ' SQL-Server
       OxConn.Open()                   ' MySQL

       ' get data from SQL-Server
       SteinAdapter = New SqlDataAdapter("SELECT * FROM dbo.xxxxxx WHERE ShopNumber = 2", SteinConn)
       SteinAdapter.SelectCommand.CommandTimeout = 120
       SteinAdapter.Fill(dtStein)

       ' get data from mysql
       OxAdapter = New MySqlDataAdapter("SELECT aa,bb,cc,dd FROM aaa", OxConn)
       OxAdapter.SelectCommand.CommandTimeout = 120
       OxAdapter.Fill(dtOxid)


       ' here follows code to update the data in dtoxid
       ...
       ...
       ...


       ' update mysql
       OxCB = New MySqlCommandBuilder(OxAdapter)
       OxAdapter.InsertCommand = OxCB.GetInsertCommand
       OxAdapter.UpdateCommand = OxCB.GetUpdateCommand
       OxAdapter.DeleteCommand = OxCB.GetDeleteCommand
       OxAdapter.Update(dtOxid)

       OxConn.Close()
       SteinConn.Close()
Énd Sub

 

This code works perfect if it's called once... if I call it again then dtoxid has 0 rows after "OxAdapter.Fill(dtOxid)".

 

As stated before this works with the older MySQL-Version I'm using now (5.1.41) but not worked after a security-Patch to 5.1.61

 

Any Idea?

[obi]

The problem is still alive... no ideas anyone?

[allblacks]

Hi ,

 

I have the same probleme, i upgrade mysql 5.0 to 5.1 yesterday and when i use my dataadapter it return nothing. ( i upgrade to 5.5 and this is the same)

 

My problem :

First i select the data to show them (no problem)

Second i modify one value and i update (still no problem)

Third i select the data to show the modify ( Return nothing)

 

Have you a solution ?

 

(sorry for my english)