Upgrade issue - autoincrement id field

[[email protected]]

Alexey,

I've upgraded from 3.50 to 4.00.13 this morning. My application had the following section of code which worked properly with 3.50:

NewHeartbeat = New MySqlDataTable("SELECT id, timestamp, host, pid, program, type, name, parm, text FROM gmsg where type = 'heartbeat' and name = '" & Name & "' order by timestamp desc;", "User Id=haldi;Password=XXX;Host=10.99.255.211;Database=mm_options;")
NewHeartbeat.Active = True

If NewHeartbeat.Rows.Count = 0 Then

Dim dr As DataRow = NewHeartbeat.NewRow
dr("host") = Mid(Environment.MachineName, 1, 16)
dr("pid") = PID
dr("timestamp") = Now
dr("program") = Program
dr("type") = "heartbeat"
dr("name") = Name
dr("parm") = 5
dr("text") = Text
NewHeartbeat.Rows.Add(dr)

Else

Dim dr As DataRow = NewHeartbeat.Rows(0)
dr("host") = Mid(Environment.MachineName, 1, 16)
dr("timestamp") = Now
dr("pid") = 1
dr("program") = Program
dr("type") = "heartbeat"
dr("name") = Name
dr("parm") = 5
dr("text") = Text

End If

NewHeartbeat.Update()

NewHeartbeat.Active = False

After the upgrade, it now throw an exeption at the .Update command with the following error:

Column 'id' does not allow nulls.

Did something change from 3.50 to 4.00 whereby I now have to explicitly set the id field? In previous versions, it would know how to set that autoincrement field for me automatically. Here is the table definition for gmsg:

CREATE TABLE `gmsg` (
`id` int(11) NOT NULL auto_increment,
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`host` varchar(16) NOT NULL default '',
`pid` int(11) NOT NULL default '0',
`program` varchar(16) NOT NULL default '',
`type` enum('msg','heartbeat') NOT NULL default 'msg',
`name` varchar(16) default NULL,
`parm` int(11) default NULL,
`text` varchar(255) default NULL,
UNIQUE KEY `id` (`id`),
KEY `timestamp` (`timestamp`),
KEY `program` (`program`),
KEY `type` (`type`),
KEY `Index_5` (`type`,`name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Any help would be greatly appreciated. Many thanks,

John

[[email protected]]

Here's a follow-up sample to recreate the problem:

Create a table as follows:

CREATE TABLE `update_test` (
`id` int(10) unsigned NOT NULL auto_increment,
`varcharfield1` varchar(45) default NULL,
`varcharfield2` varchar(45) default NULL,
`decimalfield1` decimal(10,2) default NULL,
`booleanfield1` tinyint(1) default NULL,
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

At design time add a MySqlConnection and MySqlDataTable object to a form. Configure them to point to the new test table with a select command as follows:

"select * from update_test"

Add a button to the form with the following code:

MySqlDataTable1.Active = True
Dim newrow As DataRow = MySqlDataTable1.NewRow
MySqlDataTable1.Rows.Add(newrow)
MySqlDataTable1.Update()

The test app throw an exception on the .rows.add(newrow) command saying id does not allow nulls.

Any idea what's wrong? In the constraints collection for the table it indicates that id is an autoincrement field. But it isn't populating the field when I add the row...

John

[Alexey]

We are investigating this problem carefully.
I'll let you know our progress.

[Alexey]

The problem with autoincrement fields is fixed in the release version of MyDirect .NET 4.20.