MySQL Bugs: #4115: unset TIMESTAMP fields disallow modification in linked ODBC tables

Bug #4115	unset TIMESTAMP fields disallow modification in linked ODBC tables
Submitted:	12 Jun 2004 2:28	Modified:	25 Oct 2005 14:57
Reporter:	Ronald Jeremy	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S1 (Critical)
Version:	3.51	OS:	Windows (Windows XP)
Assigned to:	MySQL Verification Team	CPU Architecture:	Any

Description:
The MySQL ODBC driver seems to confuse MS Access if a MySQL table has a TIMESTAMP column that is not yet given a time.  This prevents you from being able to use access to change data in the table.  Attempts to change any field in a row will cause access to say "Write Conflict".  Access is probably the most common database front-end in the world, this particular error has been plaguing me for years and im sure thousands of other users too.

How to repeat:
CREATE TABLE t1 (id int(1) PRIMARY KEY,val varchar(10),time1 TIMESTAMP,time2 TIMESTAMP)type=MyISAM;
INSERT INTO t1 (id,val) SELECT 1,'a';

Now in MS Access, create a linked table to table t1.  Open the table, try to change 'a' to 'b', and since time2 is unset, the "Write Conflict" error occurs. Now:
UPDATE t1 SET time2 = null;

This will set time2 to the current time.  Open the linked table in MS Access once again, and you can now change 'a' to 'b' without error.

Suggested fix:
Possibly some way/option to "trick" access into accepting unset TIMESTAMP.  If you have and quick-and-dirty source code hacks I could try, it would be sooo helpful to not have to manually type UPDATE queries into the access query builder just to modify a single row.

Verified with Access 2003.

Please attach your testcase.

CREATE TABLE t1 (id int(1) PRIMARY KEY,val varchar(10),time1
TIMESTAMP,time2 TIMESTAMP)type=MyISAM;
INSERT INTO t1 (id,val) SELECT 1,'a';

Now in MS Access, create a linked table to table t1.  Open the table, try to change 'a' to 'b', and since time2 is unset, the "Write Conflict" error occurs. Now:
UPDATE t1 SET time2 = null;

This will set time2 to the current time.  Open the linked table in MS Access once again, and you can now change 'a' to 'b' without error.

Tested with c/odbc v3.51.12.

I doubt there is a bug here - or at least the problem can be avoided if the following is considered;

- there is no need for more than one timestamp column in a table
- avoid using timestamp as a pk (not done in this case but good to keep in mind)