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 13:54	Modified:	10 Apr 13:17
Reporter:	Rawden Hoff
Status:	Verified
Category:	Connector/Net	Severity:	S4 (Feature request)
Version:	5.2.5	OS:	Microsoft Windows (XP)
Assigned to:	Reggie Burnett	Target Version:
Tags:	autoincrement, dataset, UPDATE
Triage:	D4 (Minor)

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