MySQL Bugs: #25055: MyODBC unable to update a timestamp field from Access

Bug #25055	MyODBC unable to update a timestamp field from Access
Submitted:	13 Dec 2006 23:22	Modified:	31 Jan 2008 18:29
Reporter:	Erica Moss	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	3.51	OS:	Windows (win XP/Access 2003)
Assigned to:		CPU Architecture:	Any

Description:
When executing a query in Access that inserts rows into a linked MySQL Table, the query fails if there is a TIMESTAMP field.  The error states that this "field is not updatable".  The same thing will happen if you have two similar linked MySQL tables and you try to execute a query that copies records from one to the other.

However if a similar query is executed using the mysql client it works fine so it's not a case of the field being truly non-updatable.  Given that almost all tables used with Access need to have a timestamp field for interop, this could be a nuisance, and would require that table copies specify every field rather than doing a select *  It would also mean that the destination would need to have a suitable default value, which obviously not be the same value that was stored in the source table.  This could have consequences for the user if the timestamp was needed for the business logic.

How to repeat:
create table timetable (c1 int auto_increment primary key,c2 timestamp);
insert into timetable values ();
create table timetable2 LIKE timetable;

Create a new linked table in Access to 'timetable'
Create another linked table in Access to 'timetable2'

Create this query in Access:

INSERT INTO [timetable2]
SELECT [timetable].*
FROM [timetable];

Run the query and it fails

However if you run this from the mysql client it works:
mysql> CREATE TABLE test (C1 INT AUTO_INCREMENT PRIMARY KEY,C2 TIMESTAMP);
mysql> CREATE TABLE test2 LIKE test;
mysql> INSERT INTO test VALUES ();
mysql> INSERT INTO test2 SELECT * FROM test;
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM test2;
+----+---------------------+
| C1 | C2                  |
+----+---------------------+
|  1 | 2006-12-13 17:12:21 |
+----+---------------------+
1 row in set (0.00 sec)

It is possible that this is intentional -- a TIMESTAMP field like that auto-updates, so Microsoft Access treats it as a read-only field. With the fix for Bug #9927, you can declare a TIMESTAMP field that does not auto-set or auto-update, and it can be updated as usual.

Bug #30933 is marked as duplicate of this bug.

Eric, please reconsider Jim's last post. Access itself is declaring field to be RO...