MySQL. Get records affected by UPDATE

[Axcontrols2]

Ha! When you read that title you came here to tell me to use the return value of ExecuteNonQuery. Well, that's what I have been using for years. But, it seems, we can't trust it anymore.

For years I've been using UPDATE and the return value of .ExecuteNonQuery to know if a record already exists and, if not, I can immediately do an INSERT.

But on a new project with MariaDB 10 I just noticed new records were being INSERTed when they didn't need to be (They already existed)

It seems that MySQL has a setup parameter introduced in 5.2.6

UseAffectedRows
Default: false

When true, the connection reports changed rows instead of found rows. This option was introduced with the 5.2.6 connector.

So, ExecuteNonQuery returns number of Changed Rows. So, if the row already exists but the UPDATE doesn't actually change anything in that row then ExecuteNonQuery returns zero.

Who on earth thought of that!

It seems the default is OFF so you'd think it will behave the same as it's always done. But the fact that the parameter exists and can be changed means I can't make assumptions in my code. I can no long use the return value of ExecuteNonQuery to know if a record already existed.

Testing with a fresh instance of MariaDB 10 that parameter appears to be TRUE by default (So completely different to how it's always been)

Is there another way to know if a record already exists. Sure I could do a query first to find out if the record exists and then do an UPDATE or an INSERT. But in most cases the record will exist and I don't want to do unnecessary round trips to the server.

[Zvoni]

Why not write a SP, say TRY_UPDATE with an IN-Param (Primary-Key) and an OUT-Param (SUCCESS Boolean)?

EDIT: The scenario "How to check if a record already exists" implies looking up data in your database.
I don't think it makes much difference, if you fire an UPDATE against the db (and then checking records affected or whatever), or a simple SELECT ID FROM MyTable, and then check returned rows.

EDIT2: If your UPDATE/INSERT is always the same column-wise, i'd move your
"If Not UPDATE Then INSERT" to a stored procedure and have everything done server-side
Aircode of SP
SELECT ID FROM MyTable
If RecordCount=1 Then UPDATE ELSE INSERT

[FunkyDexter]

MySQL supports an Upsert which would be a much more robust and more performant way of handling this.

I took a quick look to see if MariaDB supports it and couldn't find a definite answer but if MySQL supports it I struggle to believe that MariaDB doesn't.

Edit> MariaDB supports it too




At the risk of breaking into a rant, update->check->insert, insert->error->update, checkExists->updateOrInsert are all terrible approaches to this problem where an Upsert is supported. They all involve multiple round trips so are non-performant and all need to be wrapped in a transaction so risk blocking issues. No matter what platform you're using, check to see if an Upsert is supported and switch to using that. Oracle, My SQL and SQL Server all have some form of Upsert. In fact Access is about the only DB I can think of that doesn't.

[Zvoni]

Eh? Didn't know that MySQL has a built-in error-trap if a PK already exists.
OK, i might have only some 6 months experience with MySQL.
Thx, FD. Learned something new.

[Axcontrols2]

Thanks guys.

Sorry. I neglected to say that the application can connect to JET, Microsoft SQL Server or MySQL. I try to write db agnostic code. Obviously different connectors but as far as possible the code is the same. I have fixups in the code for handling things like "TOP n" vs "LIMIT n" etc but, in the main, the code is simple without any vendor specific syntax.

So in an ideal world I'd have some way to attempt an UPDATE and, if record doesn't exist, I'd fallback to an INSERT. But therein lies the problem. MySQL can return zero from an UPDATE if the record exists but nothing was actually changed.

So I'm looking for suggestions.

I could first do a SELECT query and then decide whether to INSERT or UPDATE but that's two trips to the server that I'd like to avoid.

Currently I'm looking at the idea of adding a 'LastUpdate' column to each table and every time I UPDATE a table I put a timestamp into that column. That would mean that the UPDATE will always update something and MySQL will therefore return a sensible value for records affected.

Bit of an overkill adding a column just to get round the problem but I'm sure in future that column could come in handy for debugging etc.

[FunkyDexter]

Currently I'm looking at the idea of adding a 'LastUpdate' column to each table and every time I UPDATE a table I put a timestamp into that column. That would mean that the UPDATE will always update something and MySQL will therefore return a sensible value for records affected.

Personally I don't like that suggestion for a number of reasons.
1. It means you will be doing pointless updates which will have a performance implications. Hard to say without testing (and highly dependent on you hardware configuration) but I suspect this would be greater than the double round trip (disk hit is typically the biggest bottleneck - bigger than network)
2. The presence of that field implies you're doing optimistic concurrency. You know you're not but the next developer might waste a lot of time wondering about that.

Personally, if you really want to stay platform independent, I'd go with Insert->Error->Update. I.e. try the Insert first, if it errors with a Primary Key constraint then you know the record already exists so Update. This gives you an average of 1.5 round trips (same as you're original approach although this varies with whether you typically end up inserting or updating more often) and is compliant with the original ISO standard so should be syntactically the same across platforms.

The only reservation I have is that errors typically bubble up the call stack through a different path to return values and it might be hungrier. I think that will be outweighed by the .5 of round trip you'd be saving over a Select->Insert or Update approach.

[Axcontrols2]

@FunkyDexter

I'm interested in what you said about disk hit being a bottle neck. This application was originally written (by me) back in the days of ISDN before broadband. So trips to the server was something I avoided as much as possible. I've rewritten the application a few years ago in c# but the logic is basically the same. But still, in my (old) mind, network traffic is my concern. You're saying that nowadays it's disk hits I should be concentrating on?

If I did something like "UPDATE sometable SET Name= 'Bob', LastUpdate=MyTimeStamp WHERE id=1234"

Are you saying that writing those two values into the database would take longer than calling SELECT and then doing an UPDATE where the update doesn't need to write the TimeStamp value.

I suppose I could test that.

[FunkyDexter]

You're saying that nowadays it's disk hits I should be concentrating on?

That varies highly with your hardware. For example, solid state drives are now common which vastly reduces the disk bottleneck. A slow network connection would tip the balance back toward that being the primary concern. There are so many variables that it's really impossible to give a single, correct answer.

Typically, I tend to say the disk is likely to be the most bound because most people aren't splashing out for solid states that are big enough to cope with a production DB while they are more happy to splash out on chips, memory and network. Note, that everything in that is qualified with a "depends" though.

edit>Corrected a spelling but I HAD to requote it here in the interests of posterity: "because moist people aren't splashing out"

[Axcontrols2]

"because moist people aren't splashing out"

Ha! Made my day!

[Zvoni]

Personally, if you really want to stay platform independent, I'd go with Insert->Error->Update. I.e. try the Insert first, if it errors with a Primary Key constraint then you know the record already exists so Update. This gives you an average of 1.5 round trips (same as you're original approach although this varies with whether you typically end up inserting or updating more often) and is compliant with the original ISO standard so should be syntactically the same across platforms.

I was thinking about that approach too, but what are you going to do, if his PK is an auto-increment?
Does an INSERT even work if you pass a static value for a PK, if the PK in the DB is auto-increment?

I'd rather stay with my simple SELECT PrimaryKey FROM MyTable WHERE PrimaryKey=MyParamID and then check if Recordcount is either 0 or 1.
Even for some follow-up developer it's clean and clear, what's going on.
What's the cost of this SELECT in terms of traffic? Sending 4 Bytes for the PrimaryKey plus some overhead for the SQL-string, receiving at least 4 bytes for the recordcount plus some overhead (plus 4 bytes for the PK if it exists).
I'm assuming of course, his PK is an Integer in that case. We all know that a PK can be something else.
Or did i get that wrong?

[FunkyDexter]

I was thinking about that approach too, but what are you going to do, if his PK is an auto-increment?
Does an INSERT even work if you pass a static value for a PK, if the PK in the DB is auto-increment?

Good point. I don't think it would be an issue though. If it's autoincrement then you're either updating against a known primary key or inserting against an unknown one. So you could decide whether to insert or update based on whether you had a known value for the pk or not. But without knowing more about the system it would be hard to say for sure.

Either way, I doubt it will make a huge amount of difference whether he uses Insert->Error->Update or CheckExists->Insert/Update. Either's a valid approach.

[Zvoni]

Hmmm....
i just did a test (admittedly against an SQLite-DB):
INSERT with an unknown/unused PK passed as a value works, INSERT with a known PK throws an error (i did the SQL's in SQLiteStudio).
Firing INSERTS without passing a PK afterwards starts the auto-increment from the highest value of the PK.

EDIT: Thinking about it:
Using the Approach
If INSERTwithPKpassed = Error Then UPDATE....
from where would he get the PK in the first place?
He would have to have it retrieved from somewhere which implies a lookup (SELECT?)

[FunkyDexter]

from where would he get the PK in the first place?

That's what I meant. If he's using auto ids then there's a material difference in the client between a new record and an existing one, namely that the primary key would be known or unknown. That would make the issue moot as he could simply check the record in the client before hitting the DB at all.

[Zvoni]

After thinking about it....
i wouldn't be surprised, if he's actually upserting a Detail-table, where his PK is a FK in that table

That's the only scenario i can think of, where his issue makes sense, since it's possible to have a "master"-record with/without "detail"-records

[Axcontrols2]

Thanks for the posts guys. I've been away so didn't respond earlier. Sorry. But I really do appreciate your comments/suggestions.

If I have read a unique Key then of course I know whether to insert or update. If I have the key then I UPDATE. If I have no key then I assume a new record is being created so I INSERT.

The table I'm working on though is a detail table that is mostly accessed as the 'many' side of a one-to-many join. It doesn't have any Unique Key.

I do know that the record exist because I just read it. So I should be able to edit the record and UPDATE.

Using Microsoft Databases I just do an UPDATE because that is most likely to succeed in a single round trip to the server and ExecuteNonQuery returns an integer telling me that one record was updated.

But this is a multi-user system and somebody else may have just UPDATED the exact same record with the exact same data.

With an MS database that wouldn't matter. I'd still get a result=1 because the record does exist.

But in MySQL I'd get result=0. Although no error occurred the record was not actually changed because somebody else beat me to it. I'd get result=0, assume that the record does not exist so I INSERT a new one.

.. and that's the problem. I should not INSERT a new one because one already exists.

I suppose if UPDATE results in 0 then I could do an SELECT to check before doing an INSERT. But now I've just done 3 round trips.

Having said that, 99.999% of the time the UPDATE will succeed. The INSERT in my current code is just a 'belt and braces' approach to the way I code.

It will be perfectly acceptable to do the UPDATE and then, in the 0.001% of cases where result=0 I will do a SELECT before INSERT.

[FunkyDexter]

Sound to me like you've understood the problem well enough and the various options available to you. None of them are perfect but any will do. If it's an update 99% of the time then I think I might consider the Update->Select->Insert. It's 2.5 round trips but it caters for the most common scenario most of the time so probably works out much lower... less than 2. Or Select ->Insert/Update is a guaranteed 2.

Coming completely out of left field (and this suggestion's likely a non-starter because you'd probably have wanted to design it in from the start but I'm just putting it out there) have you considered re-architecting the system to use a repository pattern and dependency injection? Basically you write all your DB access code into it's own layer which exposes an object orientated interface to it's consumers. The advantage of this approach is that it would allow you to inject different repository depending on which database you're talking to so you can leverage all the groovy syntax of each database (e.g. Upserts). I've written systems that had to work against different SQL DBs, NoSQL DBs and even web services and this pattern is gold dust for making it all flexible. It probably is too late to work this in now but it's definitely worth boning up on for future projects.