C# Dapper - What is wrong with this 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:

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?
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.

Slightly off topic, but I don't understand how the command can work at all. You're trying to match on duplicate IDs, but you're not passing in an id. The fact that you're getting a match indicates to me that something else is updating the data. As far as I can see, that code will always insert. — Rob
– Rob ♦, Commented Jan 25, 2016 at 0:41
Also, you should be able to simply pass a DateTime into dapper, without passing a string. You might also try ON DUPLICATE KEY UPDATE updatedtime = VALUES(updatedtime) — Rob
– Rob ♦, Commented Jan 25, 2016 at 0:44
if the links to the .cs files listed on the tag wiki page weren't broken, I'd download and breakpoint it for you. — Drew
– Drew, Commented Jan 25, 2016 at 1:03
But I could see this totally happening. It's an orm. It's an IODKU. All day long data developers chase around these anomalies. Geez, why did this timestamp come back wrong? Oh, from_unixtime with TZ diff. — Drew
– Drew, Commented Jan 25, 2016 at 1:10
@Rob The mysql insert on duplicate page says "If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, MySQL performs an UPDATE of the old row." — Edward Ned Harvey
– Edward Ned Harvey, Commented Jan 25, 2016 at 1:18

Shadow · Accepted Answer · 2016-01-25 01:49:34Z

2

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.

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.

answered Jan 25, 2016 at 1:49

Shadow

34.5k10 gold badges67 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Edward Ned Harvey Over a year ago

Yes, that solved it, thank you! To prevent the TIMESTAMP fields from auto-updating, I need to create the table as follows:

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

Collectives™ on Stack Overflow

C# Dapper - What is wrong with this MySql?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related