Upgrade issue - autoincrement id field

Upgrade issue - autoincrement id field

Postby john@haldi.net » Mon 13 Aug 2007 13:39

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
john@haldi.net
 
Posts: 38
Joined: Tue 07 Mar 2006 17:13

Postby john@haldi.net » Mon 13 Aug 2007 16:16

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
john@haldi.net
 
Posts: 38
Joined: Tue 07 Mar 2006 17:13

Postby Alexey » Wed 15 Aug 2007 07:24

We are investigating this problem carefully.
I'll let you know our progress.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby Alexey » Thu 06 Sep 2007 09:49

The problem with autoincrement fields is fixed in the release version of MyDirect .NET 4.20.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43


Return to dotConnect for MySQL