C# Dapper - What is wrong with this MySql?

c# dapper mysql

Question

I created a database table using this line:

CREATE TABLE mytable(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
uniquename VARCHAR(256) NOT NULL UNIQUE KEY, creationtime TIMESTAMP, 
updatedtime TIMESTAMP);

Here is my dapper-dot-net INSERT OR UPDATE command:

const string uniquename = "25975B8F882E7B1DD99116B71C5A8D04";

// Has format "yyyy-MM-dd HH:mm:ss"
string mysqlTimeStampString = DateTime.UtcNow.ToMysqlTimeStampString();

dbConn.Execute(@"INSERT INTO mytable (uniquename, creationtime, 
updatedtime) VALUES (@uniquename, @creationtime, @updatedtime) ON DUPLICATE 
KEY UPDATE updatedtime=@updatedtime;", new { uniquename=uniquename, 
creationtime = mysqlTimeStampString, updatedtime = mysqlTimeStampString });

After the first time I ran it, I select * from mytable \G and I got this result:

id: 1
uniquename: 25975B8F882E7B1DD99116B71C5A8D04
creationtime: 2016-01-25 00:06:55
updatedtime: 2016-01-25 00:06:55

So far everything looks good, but when I run the same INSERT OR UPDATE a few minutes later, I get this result:

id: 1
uniquename: 25975B8F882E7B1DD99116B71C5A8D04
creationtime: 2016-01-24 19:10:00
updatedtime: 2016-01-25 00:10:00

This is puzzling for the following reasons:

  1. On duplicate, only the updatedtime field is supposed to be updated, but both the creationtime and duplicatetime fields are being updated. Why are both fields being updated?
  2. The mysqlTimeStampString string is passed into the INSERT OR UPDATE command twice. Literally one string passed in twice, so there is absolutely no possibility that creationtime is local time while updatedtime is UTC. How on earth is it possible, during the duplicate update, that the creationtime is being converted to local time (UTC -5:00) while the updatedtime is set to UTC?

The best I can figure, this must be a dapper bug, mysql bug, or both, or my syntax is wrong.

Accepted Answer

  1. In the create table statement creationtime timestamp firld is defined first. According to mysql's documentation on auto initialising timestamp fields:

By default, the first TIMESTAMP column has both DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP if neither is specified explicitly.

  1. Since it is mysql and not the c# code that updates creationtime field, its value is set according to mysql server's clock and timezone setting.

The documentation I linked in the 1st point also describes how to override these settings so that creationtime field does not get updated by mysql. The solutions are described right below the quoted paragraph.



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why