MySQL Bugs: #40529: Autoincrement values are passed to DS.Update method causing duplicate errors.

Bug #40529	Autoincrement values are passed to DS.Update method causing duplicate errors.
Submitted:	5 Nov 2008 12:54	Modified:	10 Apr 2009 11:17
Reporter:	Rawden Hoff	Email Updates:
Status:	Verified	Impact on me:	None
Category:	Connector / NET	Severity:	S4 (Feature request)
Version:	5.2.5	OS:	Windows (XP)
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	autoincrement, dataset, UPDATE

Description:
I have seen another post similar to this one, but I don't seem to see a solution posted although it has been closed. 

http://bugs.mysql.com/bug.php?id=27642&thanks=3&notify=67
http://www.experts-exchange.com/Database/MySQL/Q_21550897.html?sfQueryTermInfo=1+%22connec...

Could someone please clarify the work-around or fix.

Rather than try to (badly) explain what the problem is, I have attached sample code to replicate the problem. Basically I'm getting the following error when running the code:

Column 'ID' is constrained to be unique.  Value '1' is already present.

You'll need to truncate the table each time, in order to get the error.

How to repeat:
VB Code:

Imports MySql.Data.MySqlClient

Class form1

    Private Sub btnTest_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnTest.Click
        Test()
    End Sub
End Class

Module mySQLBugTest
    Const strUser As String = "myUser"
    Const strPassword As String = "myPassword"
    Const strServer As String = "myServer"
    Const strDatabase As String = "myDatabase"

    Sub Test()
        Dim strConn As String = "Server=" & strServer & ";" & "Database=" & strDatabase & ";UId=" & strUser & ";Pwd=" & strPassword & ";Allow Zero Datetime=True;"
        Dim tbl As DataTable
        Dim DA As MySqlDataAdapter
        Dim Row As DataRow
        Dim CB As MySqlCommandBuilder

        Try
            Using Conn As New MySqlConnection(strConn)
                Conn.Open()
                tbl = New DataTable
                DA = New MySqlDataAdapter
                DA.SelectCommand = New MySqlCommand("SELECT * FROM tmp", Conn)
                DA.FillSchema(tbl, SchemaType.Source)
                DA.Fill(tbl)

                For t As Int16 = 1 To 2
                    Row = tbl.NewRow
                    Row("MyTextValue") = "test" & t
                    tbl.Rows.Add(Row)
                Next

                CB = New MySqlCommandBuilder(DA)
                DA.Update(tbl)

            End Using

            MessageBox.Show("Done")
        Catch ex As Exception
            MessageBox.Show(ex.ToString)
        End Try

    End Sub
End Module

mySQL Table Creation Code:

CREATE TABLE `tmp` (
  `ID` INTEGER(11) NOT NULL AUTO_INCREMENT,
  `MyTextValue` VARCHAR(6) COLLATE utf8_general_ci DEFAULT NULL,
  PRIMARY KEY (`ID`)
)ENGINE=MyISAM
AUTO_INCREMENT=1 ROW_FORMAT=DYNAMIC 
CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'
COMMENT='';

Alternative C# Code (if preferred):

     using MySql.Data.MySqlClient;
    
     static class mySQLBugTest
     {
         const string strUser = "myUser";
         const string strPassword = "myPassword";
         const string strServer = "myServer";
         const string strDatabase = "myDatabase";
        
         public static void Test()
         {
             string strConn = "Server=" + strServer + ";" + "Database=" + strDatabase + ";UId=" + strUser + ";Pwd=" + strPassword + ";Allow Zero Datetime=True;";
             DataTable tbl = default(DataTable);
             MySqlDataAdapter DA = default(MySqlDataAdapter);
             DataRow Row = default(DataRow);
             MySqlCommandBuilder CB = default(MySqlCommandBuilder);
            
             try {
                 using (MySqlConnection Conn = new MySqlConnection(strConn)) {
                     Conn.Open();
                     tbl = new DataTable();
                     DA = new MySqlDataAdapter();
                     DA.SelectCommand = new MySqlCommand("SELECT  FROM tmp", Conn);
                     DA.FillSchema(tbl, SchemaType.Source);
                     DA.Fill(tbl);
                    
                     for (Int16 t = 1; t <= 2; t++) {
                         Row = tbl.NewRow;
                         Row("MyTextValue") = "test" + t;
                         tbl.Rows.Add(Row);
                     }
                    
                     CB = new MySqlCommandBuilder(DA);
                     DA.Update(tbl);
                 }
                
                
                 MessageBox.Show("Done");
             }
             catch (Exception ex) {
                 MessageBox.Show(ex.ToString);
             }
            
         }
     }

Suggested fix:
All I know is that if you comment out the line DA.FillSchema, it then works.

Hello Rawden and thanks for your report.

You only posted your comment to original bug report yesterday! Please go there and find attached pdf. If after examining it you still find this is a separate bug, reopen this report. I'll be monitoring.

We do not look keen on having same problem reported multiple times in BugsDB as it makes our life very difficult and lowers the usability of system.

Closing (for now) as duplicate of Bug#27642

Hi Tonci,

Sorry for the delay. I've been working on another project, but I'm back on this again now.

I have had a look deeper into the bug you posted and I'm pretty sure this is a separate issue.

The ReturnGeneratedIdentifiers property defaults to True anyway, so in theory my example given, should just work, but it never does the first time after a table truncation.

Thanks in advance for any help you can provide.

Regards,

Rawden.

Has anyone had a chance to look at this at all?

Rawden, I assigned to this report but I can't manage all at once.

Ok Thanks Tonci,

Sorry - I didn't mean to chase, only it's been nearly month and I wasn't sure if I'd been forgotten. 

Say no more - I'll leave you alone. 

Thanks again...

Rawden, see my test case and make comments if any (DisplayData is simple proc, not relevant for test case):

  o http://dev.mysql.com/doc/refman/5.1/en/connector-net-examples-mysqldataadapter.html#connec...

            MySqlConnection con = new MySqlConnection();
            con.ConnectionString = "DataSource=**;Database=test;UserID=**;Password=**;PORT=3306;logging=True";
            con.Open();
            MySqlCommand cmdCreateTable = new MySqlCommand("DROP TABLE IF EXISTS bug40529", con);
            cmdCreateTable.ExecuteNonQuery();
            cmdCreateTable.CommandText = "CREATE TABLE `bug40529`(`Col1` int(10) NOT NULL auto_increment, `Col2` int(10) NOT NULL, `Col3` VARCHAR(50), PRIMARY KEY (`Col1`)) ENGINE=MyISAM DEFAULT CHARSET=latin1;";
            cmdCreateTable.ExecuteNonQuery();
             MySqlCommand cmdSel = new MySqlCommand("SELECT * FROM bug40529", con);
            DataTable dt = new DataTable();
            MySqlDataAdapter da = new MySqlDataAdapter(cmdSel);
            da.Fill(dt);
            dt.Columns["Col1"].AutoIncrement = true;
            dt.Columns["Col1"].AutoIncrementSeed = -1;
            dt.Columns["Col1"].AutoIncrementStep = -1;
            DataRow dr = dt.NewRow();
            dr["Col2"] = 3;
            dr["Col3"] = "Some text for Col2=3";
            dt.Rows.Add(dr);
            dr = dt.NewRow();
            dr["Col2"] = 5;
            dr["Col3"] = "Some text for Col2=5";
            dt.Rows.Add(dr);
            MySqlCommandBuilder cmdBuilder = new MySqlCommandBuilder(da);
            da.Update(dt);
            DisplayData(dt);
            con.Close();

Produces correct result:
Col1 = 1
Col2 = 3
Col3 = Some text for Col2=3
============================
Col1 = 2
Col2 = 5
Col3 = Some text for Col2=5
============================

with following log:
[8.4.2009 18:19:46] - Executing command QUERY with text ='SHOW COLLATION'
[8.4.2009 18:19:46] - Executing command QUERY with text ='SET NAMES utf8;SET character_set_results=NULL'
[8.4.2009 18:19:46] - Executing command QUERY with text ='DROP TABLE IF EXISTS bug40529'
[8.4.2009 18:19:46] - Executing command QUERY with text ='CREATE TABLE `bug40529`(`Col1` int(10) NOT NULL auto_increment, `Col2` int(10) NOT NULL, `Col3` VARCHAR(50), PRIMARY KEY (`Col1`)) ENGINE=MyISAM DEFAULT CHARSET=latin1'
[8.4.2009 18:19:46] - Executing command QUERY with text ='SELECT * FROM bug40529'
[8.4.2009 18:19:46] - Executing command QUERY with text ='SET SQL_SELECT_LIMIT=0'
[8.4.2009 18:19:46] - Executing command QUERY with text ='SELECT * FROM bug40529'
[8.4.2009 18:19:46] - Executing command QUERY with text ='SET SQL_SELECT_LIMIT=-1'
[8.4.2009 18:19:46] - Executing command QUERY with text ='SET SQL_SELECT_LIMIT=0'
[8.4.2009 18:19:46] - Executing command QUERY with text ='SELECT * FROM bug40529'
[8.4.2009 18:19:46] - Executing command QUERY with text ='SET SQL_SELECT_LIMIT=-1'
[8.4.2009 18:19:46] - Executing command QUERY with text ='INSERT INTO `test`.`bug40529` (`Col2`, `Col3`) VALUES (3, 'Some text for Col2=3'); SELECT last_insert_id() AS `Col1`'
[8.4.2009 18:19:46] - Executing command QUERY with text ='INSERT INTO `test`.`bug40529` (`Col2`, `Col3`) VALUES (5, 'Some text for Col2=5'); SELECT last_insert_id() AS `Col1`'

So, if I'm not missing something, this is "Can't repeat".

Hi Tonci,

Thank you again for looking at this.

I tried your example.

If you swap the following three lines:

dt.Columns["Col1"].AutoIncrement = true;
dt.Columns["Col1"].AutoIncrementSeed = -1;
dt.Columns["Col1"].AutoIncrementStep = -1;

for:

da.FillSchema(dt, SchemaType.Source);

you should get the error. I'm under the impression that the FillSchema should set the schema of the dataTable for you, so you don't have to manually enter the lines like you have. Or is this not an option?

Rowden, according to specs, what System.Data.SchemaType.Source does is:
o Ignore any table mappings on the DataAdapter. Configure Dataset using the incoming schema *without* applying any transformations.

It is my opinion this can not work as you expect it to.

Finally, .NET has problems regarding what MS call "Identity" fields and setting *seed* might be necessary to allow for different auto-increment implementations (what if some DBMS has AI implemented in CHAR field for example) or when rule for fetching seed might not be obvious from DB schema (ie. you might want to start inserts with AI field value of 100 as values from 0-99 are reserved for another department etc.). What happens in your sample is following:
o FillSchema *does* it's job exactly according to specs. No additional processing done on c/NET side. This means we can drop dt.Columns["Col1"].AutoIncrement = true; as it's already there. We can also drop dt.Columns["Col1"].AutoIncrementSseed = -1; line.
o Default AISeed is 0, default AIStep is 1 according to specs.
o First datarow sets it's AI column to 0 (correct). Upon insertion, however, value of Col1 becomes *1* (correct and expected).
o Next datarow, using default step, sets Col1 to 1 and your problem starts.
I have found no other workaround but to define Seed and/or Step as -1 which will fix .NET FW default behavior to fit MySQL behavior.

Here's what I've found on MSDN related to this problem:
ADO.NET has its own mechanism to handle client-side Identity values, because ADO.NET works with "disconnected" data and does not expect to have live access to the "real" server-side data table. This means that as you add rows to a DataTable object on your client, the Identity value generated locally by ADO.NET won't have any bearing on the Identity values of existing rows in the database or on the rows in the local DataTable. Huh? How can that work? Well, ADO.NET does not make any effort to test the Identity values it generates against any existing Identity values in the client-side disconnected table. It sets the new Identity values based on the AutoIncrementSeed and AutoIncrementStep. This means that if your existing DataTable has identity values ranging from 1 to 10 and you set the AutoIncrementSeed value to 10, ADO.NET does what it's told—it starts at 10 and you'll end up with two rows with the same Identity value (not good). These "autoincrement" properties can be set before you add any rows to the DataTable—afterwards, it does not seem to matter. The demonstration application included with this article illustrates this behavior.

So I can only verify this as a feature request for us to override default behavior of FillSchema regarding Seed and Step to fit MySQL server. Do you agree?

Workaround is obvious and acceptable; use dt.Columns["Col1"].AutoIncrementStep = -1; as the new values will count down into negative preventing duplicate key errors.

Yes I agree. 

I was just doing some testing on your example and I couldn't understand why if I queried the col1 value of the first DataRow, I got the value of -1, whereas on my app, it was returning an AI value of two less than it should be. For example, if I had data in the table already (ID values 1 through 30), the DataRow AI should have had a value of 31, but it didn’t, it was 29! I couldn’t understand why the value was not -1 like in your example, but in the end I tracked it down to the da.Fill(dt); having to come before the da.FillSchema line – then it worked. 

How feasible is the feature request implementation?

Rawden, I think you do *not* need both FillSchema and Fill. Please try this modified code:
            da.FillSchema(dt, SchemaType.Source);

            //da.Fill(dt);
            ////The FillSchema creates the Schema of a DataSet / DataTable (Columns etc) based on the information given by the SQL-SELECT-Statement and adds information about the primary key (The Fill-Method does not add the information about the primary key).
            //dt.Columns["Col1"].AutoIncrement = true;
            //dt.Columns["Col1"].AutoIncrementSeed = -1;
            dt.Columns["Col1"].AutoIncrementStep = -1;

            MySqlCommandBuilder cmdBuilder = new MySqlCommandBuilder(da);

and so on...

As for feasibility of feature request, I have not yet gathered all opinions. As far as I'm concerned I think it's valid request but I might not see all the ends.

Changing to feature request.

Hi Tonci,

In my particular situation, I need to modify existing data as well as add 'new' rows so I do need the da.Fill(dt) in order to get the data.

I think I'm ok now that I know it has to come before the FillSchema line and I have to set the seed and step to -1. Thank you.

I had a work-around before, but it was by no means the best.

Thanks for your help.

Rawden.

As I had no response from Reggie regarding this so I'm verifying on the basis of my analysis posted on [9 Apr 10:07].

This problem still exists with Connector .NET 6.9.8
I don't see how the workaround of setting the DataTable column to autoincrement with a seed and step of can be correct, because if after insertion you subsequently modify the row in the client DataTable and call MySqlDataApater.Update() it uses the value in the DataTable in the WHERE clause, like "... WHERE Col1=-2 ...", and a row with this key does not exist in the database so you get the dreaded "Concurrency violation" error.
If I am right I think this should be a bug, not a feature request. If I am wrong please tell me what I am doing wrong :-)