Odd behaviour with autoincrement keys

[seebrown99]

I'll be the first to admit I might be doing something wrong here, but...

I am using non-null, autoincrementing unsigned integers as my primary key in MySQL 4.11 (NT), the column name is 'id'. I am creating strongly-typed datasets by dragging and configuring a connection, commands, and dataadapters (including building the insert, update and delete commands with the Command generator tab of the dataadapter configuration) onto my form, then selecting "Generate dataset" with multiple dataadapters selected. All of the commands have a where clause "where .deleted = 0" so that I can soft-delete rows in code and reinstate them later if needed. This bit seems to work OK.

When I add rows to the table, I am creating new rows in code with something like:

Code: Select all

Dim drNew as TypedDataSet.tablenameRow
Dim drNewArray(0) as TypedDataSet.tablenameRow
drNew = ds.NewtablenameRow
with drNew
    .column1=value1
    .column2=value2
    ...
    .columnX=valueX
End With

ds.tablename.AddtablenameRow(drNew)
drNewArray(0)=drNew
daTablename.Update(drNewArray)

The reason I do this is so that a user can add one or more new rows and get unique IDs for each (they will be used to add related data in the same operation), but because the deleted column is 1, they are not visible to other users. So, a user might add several rows to this table, then add related data to other tables. If the user cancels while editing this row, it can be tracked and deleted, if they cancel after adding related data, the row is left orphaned in the table with deleted=1 so it's invisble and can be culled by a server-side process later using timestamps to track deleted rows that have been orphaned for a certain period.

When I do this with MySQLDirect.NET, I'm getting *strange* key column values. For example, the last several times I've been consistently getting 36, 37, 38 etc as the key values in one table and 53, 54, 55 in another, even though these already exist in the target table. Sometimes the first generated ID is missing in the sequence (hard-deleted row), but subsequent ones are, and if I manually add a row and fix the ID to the one first auto-generated, it still comes back with the same ID on the next run.

When I was doing this with MySQL Connector/.NET, I was following the same procedure except for explicitly setting id=0 along with the data values in the column, and Connector/.NET was writing the correct id value when it did the update. If I try the same thing here, the row in the dataset has an ID of zero even though the row in MySQL has a server-assigned ID.

Am I doing something crazy here, or is there a problem with generating unique IDs when adding rows?

I can't use negative autoincrementing IDs in the dataset, as I need the IDs to be valid in the table for the related rows in other tables that will all be commited or cancelled in one operation. I can't define all the relationships in the typed dataset, as some of the tables are lookups that use used from multiple columns in the parent table.

Any suggestions?

[seebrown99]

I tried to correct the ID manually using command.InsertID as follows;

Code: Select all

ds.tablename.AddtablenameRow(drNew) 
drNewArray(0)=drNew 
daTablename.Update(drNewArray) 
drNew.id = cmdTablename.InsertID
daTablename.Update(drNewArray) 

But then the second .Update(drNewArray) fails with the error:

System.Data.DBConcurrencyException: Concurrency violation: the UpdateCommand affected 0 of the expected 1 records.
at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows)

which I suppose isn't surprising, as the primary key has changed.

So, I'm back to needing to work out why I'm getting such strange primary key values in the first place.

[Serious]

Modify your insert command text basing on my example (In this example id is autoincrement column)

Code: Select all

insert into table1 (col1,col2) values (:col1, :col2);
select id from table1;

Set MySqlDataAdapter.InsertCommand.UpdatedRowSource = Both. This will return autoincrement field value to the DataSet after insert.

[seebrown99]

OK, I did just that. I configured the DataAdapter, went to the Update commands tab and in the Insert command, I added:

Code: Select all

select `table`.`id` from `database`.`table`;

and then in the DataAdapters properties, I expanded the Insert command and set the UpdatedRowSource value to Both.

I ran my application and set a break just before the line

Code: Select all

drNew=ds.table.NewtableRow

and then single stepped. The newly-created row had an ID that was already in the database, not the first or last, but right in the middle. I then stepped through adding the row to the array and calling the daTable.Update(array) command, and then when I inspected the drNew.id property, it was set to the first ID in the table, so I'm either overwriting (if the user saves) or deleting (if the user cancels) existing data.

I'm going to try and write a simple test with a few tables to see if I can replicate the problem (although this does do exactly the same thing in three different DataAdapters using different tables from the same strongly-typed DataSet) in a simpler environment.

Where do the IDs on the new rows come from in the first place? Why are they seemingly consistent, even after rebooting the PC and adding data manually that ensures the IDs are already in the table?

Am I right in thinking that daTable.Update *should* correct the values in the DataSet's DataTable from the underlying MySQL data, and this is a bug somewhere?

[seebrown99]

I created a test application using a new database to make sure that the problem wasn't the database, table names etc.

I created a simple database called `test` to represent a music collection, with tables called artist, album and single. Each table has a primary key called `id` which is MySQL integer type, non-null and auto-incrementing. The tables have either name (artist) or title (album and single) which are varchar(45), comments (text type) and album and single have a released date column. All tables have a column called `deleted`, which is MySQL bit (tinyint) type.

I created a new form, dragged a Connection, three Commands and three DataAdapters onto it and configured them with select statements with where deleted=0 clauses. I built Insert, Update and Delete commands from the DataAdapters, then generated a DataSet from all three DataAdapters.

I then wrote some code to put the three tables into a treeview (storing the row's id value in each node's tag) with a three-level [b]For Each [i][/i] in [i][/i][/b] construct. I added a context menu that allows me to add rows to Artist and Album, by creating a new row in the relevant table, setting default values, then passing the row to a new form's DataRow property, this dialog form presenting a UI and allowing the user to edit the data values. The dialog form returns DialogResult.OK or DialogResult.Cancel based on the user's choices, and if OK it calls da[i][/i].Update(rowarray) again to save the data to the database, if Cancel, it deletes the row and calls da[i][/i].Update(rowarray) to remove it from MySQL.

A code fragment is shown below:
[code] Private Sub AddAlbum(ByVal Node As TreeNode)
Dim drAlbum As DataSetMusic.albumRow
Dim aAlbum(0) As DataSetMusic.albumRow
Dim wAlbum As frmAlbum
Dim oNode As TreeNode
drAlbum = dsMusic.album.NewalbumRow
With drAlbum
.artistid = Node.Tag
.comments = ""
.deleted = 1
.released = Now
.title = ""
End With
dsMusic.album.AddalbumRow(drAlbum)
aAlbum(0) = drAlbum
daAlbum.Update(aAlbum)
wAlbum = New frmAlbum
wAlbum.AlbumRow = drAlbum
wAlbum.ShowDialog()
If wAlbum.DialogResult = Windows.Forms.DialogResult.OK Then
drAlbum.deleted = 0
daAlbum.Update(aAlbum)
oNode = New TreeNode
oNode.Name = "album.id:" + drAlbum.id.ToString
oNode.Text = drAlbum.title
oNode.Tag = drAlbum.id
Node.Nodes.Add(oNode)
Else
drAlbum.Delete()
daAlbum.Update(aAlbum)
End If
wAlbum.Dispose()
drAlbum = Nothing
End Sub[/code]

This did work OK until I had either manually removed rows from the table in MQB, or had selected Cancel in the dialog form, causing the newly-added row to be deleted. When this happened, future runs of the application would cause IDs to be generated for new rows that were either duplicates in the table, or were not highest id value +1.

I think I figured out why. My select statement had that [b]where deleted=0[/b] clause in it, and the invalid IDs were the [i]highest id value [b]returned by the select query[/b][/i], +1.

I removed the where clause, and re-ran. This time, the id was generated correctly: [i]highest id [b]in the table[/b][/i] +1. However, now the [b]daAlbum.Update(aAlbum)[/b] call is throwing an exception as follows:
[quote]System.Data.ConstraintException was unhandled
Message="Column 'id' is constrained to be unique. Value '1' is already present."
Source="System.Data"
StackTrace:
at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows)
at MySQLTest.frmMain.AddAlbum(TreeNode Node) in D:\development\Visual Studio Projects\MySQLDirectTest\MySQLDirectTest\frmMain.vb:line 153
at MySQLTest.frmMain.ctxAdd_Click(Object sender, EventArgs e) in D:\development\Visual Studio Projects\MySQLDirectTest\MySQLDirectTest\frmMain.vb:line 71
...
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()[/quote]

Now, where it's getting "[b]Value '1' is already present[/b]" from I don't know. Of course, that value is present in the table, but if I hover over drAlbum.id in the IDE when the exception occurs, it's showing the right id value.

If I press F10 and contine stepping, it carries on, and I can run the [b]daAlbum.Update(aAlbum)[/b] line again, and it actually does add the row, with the right ID. If I then use the dialog form to add some detail and click OK, the resulting code runs and updates the MySQL table correctly.

Obviously, I can't live with the first "[b]Value '1' is already present[/b]" exception, I'm sure my users would complain about an application with an exception message that they have to click Continue on... :)

I also can't live without my where clauses in select statements; some of my proper application's tables will have potentially tens of thousands of rows, which I don't want to have to load into memory only to skip most of them when processing data into the UI.

I have the entire test solution including a MySQL structure and data dump, both with and without the where clause present in the select statements, each is a 174Kb ZIP, if these are any use.

[seebrown99]

I just tried taking the "select id from [i][/i]" out of the DataAdapter's Update Commands tab, and now the id values are still generated correctly, and the "Value 1" exceptions are gone.

However, if another row is added by another process with the recently generated ID, the second .Update will update the wrong row; a classic concurrency issue.

So, it would appear that having a where clause in the Select statement is breaking the generated Insert statement's functionality, by causing it to generate auto-increments based on a limited visibility of existing rows.

Coming back to that concurrency issue, if the DataAdapter was able to update the value of auto-incrementing columns from the actual data MySQL puts there, this would solve both problems as the generated ID would only be possibly invalid until a DataAdapter .Update had been called, then it would be fixed. If I am then a stupid programmer and add a row to a table, and use it's auto-increment values as primary keys to add related data without first having called the DataAdapter's .Update to fix my auto-increments, that's my own fault!

[Serious]

the second .Update will update the wrong row

To avoid this problem use transactions or use update commands like this:

Code: Select all

UPDATE test.dept SET DEPTNO = :DEPTNO, DNAME = :DNAME, LOC = :LOC WHERE ((DEPTNO = :Original_DEPTNO) AND ((:IsNull_DNAME = 1 AND DNAME IS NULL) OR (DNAME = :Original_DNAME)) AND ((:IsNull_LOC = 1 AND LOC IS NULL) OR (LOC = :Original_LOC)))

[seebrown99]

To avoid this problem use transactions or use update commands like this:

Code: Select all

UPDATE test.dept SET DEPTNO = :DEPTNO, DNAME = :DNAME, LOC = :LOC WHERE ((DEPTNO = :Original_DEPTNO) AND ((:IsNull_DNAME = 1 AND DNAME IS NULL) OR (DNAME = :Original_DNAME)) AND ((:IsNull_LOC = 1 AND LOC IS NULL) OR (LOC = :Original_LOC)))

Transactions sound like a winner; some of my tables have lots of columns and I'm not a fan of assuming that every other data column's value will be unique across rows.

So, any suggestions on the auto-increment generation? As it's replicable across projects, it looks to me like an "unexpected feature". It's a bit of a show-stopper for me, not being able to add rows to a DataSet built with where clauses is a heavyweight limitation for how my application works, and unfortunately I'm on tight timescales and need this working yesterday (OK, so what programmer isn't always behind schedule?).

I dont want to have to think about going back to "that other" data provider...

[Serious]

To solve the problem with "Value '1' is already present" error use following INSERT queries:

Code: Select all

insert into autoinc (f_char, f_varchar) values (:f_char, :f_varchar);
select last_insert_id() as id;

[seebrown99]

Ah-ha! Now we're getting somewhere!

I put select last_insert_id() as id; onto the end of all my Insert statements in the DataAdapters Update Commands tab, and it works. Even if the generated id is wrong when adding a new row to the dataset, calling .Update(array) fixes the value and updates the id column with the correct value from the table. It also ensures that the concurrency issue noted before is sidestepped, so I am a happy coder indeed!

Do you think there's a chance you will release a version of MySQLDirect that will automatically 'fix up' auto-increment values after an insert in future? The reason I ask is that, although this works, it's a bit of a bind having to edit the Insert command manually each time I change something and regenerate it, and if I had an auto-incrementing column that was not the primary key, I couldn't use last_insert_id() to fetch that value, although I suppose in that case I could do select from t able where id=last_insert_id(), but that seems a little "work around"-ish, and it would be nice if the provider did all that stuff for me (I'm a lazy programmer, I know).

Thanks for all your help, you guys at Core Lab are awesome. I'm starting to think buying MySQLDirect.NET was the smartest software purchase I've made in quite a while.

Regards,

Chris.

[Serious]

If we'll implement behavior you offer this will contradict MySQL Server concepts. Autoincrement fields concept in MySQL differs from Oracle's and MS SQL's ones, and we may not change it by adding SELECT statement in INSERT queries. This must be user's deliberate action.

[[email protected]]

I've discovered this same behavior (I call it a bug) with autoincrement keys with simple MySQLDataTables. Here's my scenario:

I have a table with 10 records, sequentially numbered with an autoincrement key called 'id'. I perform the following code in VB:

MyDataTable.Active = False
dim cmd as new mysqlcommand("select * from mydbtable where id = 4",mysqlconn)
MyDataTable.SelectCommand = cmd
MyDataTable.Active = True

Now if I attempt to add a new row to MyDataTable and issue an update command, it tries to create a record with id = 5, the next highest autoincrement value from the resulting dataset from my where clause. This is nonsense. The next record should have id=11 according to any database methodology I've ever encountered.

Here's my (lazy programmer) workaround which so far has never failed me:

Create a second mysqldatatable to "ghost" your first, but don't include a where clause. So in this case my selectcommand would look like ""select * from mydbtable". When you want to add a record to the database, add it to the second mysqldatatable, and the autoincrement will correctly force id=11.

Totally stupid workaround, but it does what it is supposed to do.

I'm open to comments if anybody knows of a better solution.

Thanks,

John

[Alexey]

We cannot reproduce this behaviour. Do you use the latest version?

[[email protected]]

I am using version 3.50.10.0.

To recreate, do the following:

1) Create a new form and drop a MySQLConnection and a MySQLDataTable control on the form. Set the appropriate properties.
2) Create a data table in MySQL and add a handful of rows (lets say for the sake of argument 10 rows). Make sure you have a Primary Key of 'id' which is an auto-increment integer.
3) Add a button to your form which has the following code:

MyTable.Active = False
Dim cmd As New MySqlCommand("select * from MyTable where id = 5", MySQLConnection)
MyTable.SelectCommand = cmd
MyTable.Active = True

4) Add another button to your form and put the following code into it:

Dim dr as DataRow = MyTable.NewRow
MyTable.rows.add(dr)
MyTable.update

5) Add a dbMonitor control and activate it. Turn on the dbMonitor tool.
6) Run your program. Click the first button to limit MyTable to the single row (id=5).
7) Click the second button to add a new row to the table. In the dbMonitor you will see that your control is trying to create a row with id=6, not id=11 as it should.
8) Your application will throw a concurrency violation error since id=6 already exists in the table.

Please advise as to whether you can recreate this problem. It has been ongoing for months now.

Thank you,

John

[Alexey]

I did almost tha same yesterday and everything worked fine. Now i double-checked this. Same results: id=11 is added to a table (not id=6).