Bug #55351 ADO Entity Framework does not handle StoreGeneratedPattern for TimeStamp
Submitted: 18 Jul 2010 19:58 Modified: 23 Jun 2011 14:23
Reporter: Daniel Ulfe Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / NET Severity:S2 (Serious)
Version:6.3.6 OS:Windows (W7 x64 - VS 2010 Pro)
Assigned to: Julio Casal CPU Architecture:Any

[18 Jul 2010 19:58] Daniel Ulfe
Description:
The ADO Entity Framework generates the wrong SQL command when using "StoreGeneratedPattern" as "Computed" in a TimeStamp field.

It tries to update/insert the value of a computed field even though it was defined as "computed"

Workaround:

DO NOT USE computed values... added them directly from code.

How to repeat:
- Create a web site project
- Create a ADO Entity Model and generate it from Database
- Add a table with a default value (like timestamp)
- Assign the property "StoreGeneratedPattern" to "Computed" to the appropriate field.
- Save and close the model.
- Try to insert a record to that table.

Example tables:

CREATE TABLE `tablewithtimestamp` (
  `id` int(11) NOT NULL auto_increment,
  `value` varchar(45) NOT NULL,
  `a_timestamp_field` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8$$

delimiter $$

CREATE TABLE `tablewithtimestamp_null` (
  `id` int(11) NOT NULL auto_increment,
  `value` varchar(45) NOT NULL,
  `a_timestamp_field` timestamp NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8$$

After added 1 record to each table... the generated SQL (from the ADO Entity Framework) is:

INSERT INTO `tablewithtimestamp`(
`a_timestamp_field`, 
`value`) VALUES (
'1970-01-01 00:00:01', 
'test');
SELECT
`id`
FROM `tablewithtimestamp`
 WHERE  row_count() > 0 AND `id`=last_insert_id()

INSERT INTO `tablewithtimestamp_null`(
`a_timestamp_field`, 
`value`) VALUES (
NULL, 
'test 2');
SELECT
`id`
FROM `tablewithtimestamp_null`
 WHERE  row_count() > 0 AND `id`=last_insert_id()
[20 Jul 2010 22:16] Kai Wolf
the problem seems even deeper. When trying to update with the the entity framework it generates a completely wrong SQL statement.

In my case (simplified - CreateDate and CreateUserID are set by a trigger on the server):

UPDATE `Order` SET `SomeField`='abc' WHERE `OrderID` = 2;
SELECT
`CreateDate`, 
`CreateUserID`
FROM `Order`
 WHERE  row_count() > 0 AND `OrderID`=last_insert_id()

obviously in an update statement the correct select statement should have ended in WHERE  row_count() > 0 AND `OrderID`= 2

the last_insert_id() only makes sense after an insert statement.
[1 Jun 2011 15:38] Julio Casal
I can't reproduce this issue. Please use the latest Connector/Net version, where I have verified that Computed columns work as expected.
[13 Jun 2011 1:35] Daniel Ulfe
Test case showing the error.

Attachment: TestMySQL01.zip (application/zip, text), 148.91 KiB.

[13 Jun 2011 1:36] Daniel Ulfe
I checked the new build and the error persist.

It works fine with "default values" and "triggers"... but if you add a TIMESTAMP value... it fails.

Error:

A store-generated value of type 'System.DateTime' could not be converted to a value of type 'System.DateTimeOffset' required for member 'a_timestamp_field' of type 'Example.TestData.tablewithtimestamp'
[23 Jun 2011 14:23] Julio Casal
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

Fixed in 6.1.6, 6.2.5, 6.3.8 and 6.4.1+.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html