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
Upgrade issue - autoincrement id field
-
- Posts: 38
- Joined: Tue 07 Mar 2006 17:13
-
- Posts: 38
- Joined: Tue 07 Mar 2006 17:13
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
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