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:
updatedtime
field is supposed to be updated, but both the creationtime
and duplicatetime
fields are being updated. Why are both fields being updated?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.
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.
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.