MySQL Bugs: #84011: TIMESTAMP(6) with DEFAULT and ON UPDATE constraints writes zeroes to the DB.

Bug #84011	TIMESTAMP(6) with DEFAULT and ON UPDATE constraints writes zeroes to the DB.
Submitted:	29 Nov 2016 21:43	Modified:	30 Dec 2016 13:18
Reporter:	Kevin Hollingshead	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:	6.9.9	OS:	Windows
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	database first, Entity Framework 6

Description:
Using EF6 Database First to generate an ADO.Net data model doesn't work as expected for TIMESTAMP columns with fractional seconds and constraints.

This works fine, both get assigned on insert and only update_ts gets updated on each update of that row (from code):
...
create_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
update_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT TIMESTAMP,
...

But adding microseconds doesn't work:
...
create_ts TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
update_ts TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT TIMESTAMP(6),
...

Creates and updates will write zeroes. ("0000-00-00 00:00:00.000000")

How to repeat:
Here's the Visual Studio project Mercurial archive:
https://bitbucket.org/kehollin/consoleapptesters

Steps (MySQL V5.6.30)
1. Run the CreateTestDb.sql and SetAccess scripts from the SqlScripts folder.
2. Verify that the script ran without errors.
3. Verify your MySQL has a DB named test_db and a table named test_table.

Steps (VS 2015 Enterprise + .Net 4.6.1 + Connector.Net 6.9.9):
1. Delete the Model1 folder in the Solution Explorer, and delete the connection string from app.config.
2. Right click the MySQLTester project and select Add, then New..., then ADO.Net Entity Data Model.
3. Select EF Designer from database and Next.
4. Create a new DB connection to your test DB and select it. Also click Yes, save sensitive data..., accept the default name (test_dbEntities) and click next.
5. Check the box for tables, leave the rest as defaults and click Finish.
6. Select OK, OK and Yes to all for the next 3 pop-ups.
7. Make sure test_table is empty and run the project, using Workbench to view the results as directed.

You should see both timestamps set to "0000-00-00 00:00:00.000000" on create, and a MySqlConversionException is thrown before the update since the zeroes cannot be converted to a valid C# DateTime object.

NOTE THAT INSERTING AND UPDATING VIA WORKBENCH WORK FINE, it just when accessing from the application thus the reason I tagged this as a Connector problem.

Suggested fix:
Pretty obvious, but should behave as the timestamps without fractional seconds.

(Note that if you look at the app.config file now you will see that the formatting is borked for the connectionStrings element, bonus points for fixing this too. The new connection string should be at the end of any list on its own line, indented 2 spaces (VS's default indent for XML).)

I've worked around it with DATETIME column types and triggers. In the table def:
...
create_ts datetime(6),
update_ts datetime(6),
...

And then triggers:
create trigger test_table_insert before insert on test_table
for each row set new.create_ts = now(6), new.update_ts = now(6);
create trigger test_table_update before update on test_table
for each row set new.update_ts = now(6);

Hello  Kevin,

Thank you for the bug report and test case.
I could not repeat the issue at our end using with Visual Studio 2015, Connector/NET 6.9.9 version.

Thanks,
Chiranjeevi.

mysql> select * from test_table;
Empty set (0.00 sec)

mysql> select * from test_table;
+-----+----------------------------+----------------------------+-------------------+
| _id | create_ts                  | update_ts                  | something_legible |
+-----+----------------------------+----------------------------+-------------------+
|   1 | 2016-11-30 18:16:15.229718 | 2016-11-30 18:16:15.229718 | thingy            |
+-----+----------------------------+----------------------------+-------------------+
1 row in set (0.00 sec)

mysql> select * from test_table;
+-----+----------------------------+----------------------------+-------------------+
| _id | create_ts                  | update_ts                  | something_legible |
+-----+----------------------------+----------------------------+-------------------+
|   1 | 2016-11-30 18:16:15.229718 | 2016-11-30 18:16:21.499727 | another thingy    |
+-----+----------------------------+----------------------------+-------------------+
1 row in set (0.01 sec)

mysql>

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".