dcsimg
Results 1 to 16 of 16

Thread: MySQL. Get records affected by UPDATE

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Jun 2018
    Posts
    22

    MySQL. Get records affected by UPDATE

    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

    Code:
    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.
    Last edited by Axcontrols2; Sep 2nd, 2018 at 01:01 AM. Reason: Spelling

  2. #2
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    1,431

    Re: MySQL. Get records affected by UPDATE

    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
    Last edited by Zvoni; Sep 3rd, 2018 at 01:05 AM.
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    I say you're crazy not to!
    --------------------------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  3. #3
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    6,925

    Re: MySQL. Get records affected by UPDATE

    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.
    Last edited by FunkyDexter; Sep 3rd, 2018 at 03:26 AM.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  4. #4
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    1,431

    Re: MySQL. Get records affected by UPDATE

    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.
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    I say you're crazy not to!
    --------------------------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  5. #5

    Thread Starter
    Junior Member
    Join Date
    Jun 2018
    Posts
    22

    Re: MySQL. Get records affected by UPDATE

    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.
    Last edited by Axcontrols2; Sep 3rd, 2018 at 08:10 AM. Reason: Spelling

  6. #6
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    6,925

    Re: MySQL. Get records affected by UPDATE

    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.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  7. #7

    Thread Starter
    Junior Member
    Join Date
    Jun 2018
    Posts
    22

    Re: MySQL. Get records affected by UPDATE

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

  8. #8
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    6,925

    Re: MySQL. Get records affected by UPDATE

    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"
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  9. #9

    Thread Starter
    Junior Member
    Join Date
    Jun 2018
    Posts
    22

    Re: MySQL. Get records affected by UPDATE

    Quote Originally Posted by FunkyDexter View Post
    "because moist people aren't splashing out"
    Ha! Made my day!

  10. #10
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    1,431

    Re: MySQL. Get records affected by UPDATE

    Quote Originally Posted by FunkyDexter View Post
    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?
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    I say you're crazy not to!
    --------------------------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  11. #11
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    6,925

    Re: MySQL. Get records affected by UPDATE

    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.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  12. #12
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    1,431

    Re: MySQL. Get records affected by UPDATE

    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?)
    Last edited by Zvoni; Sep 4th, 2018 at 01:59 AM.
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    I say you're crazy not to!
    --------------------------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  13. #13
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    6,925

    Re: MySQL. Get records affected by UPDATE

    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.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  14. #14
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    1,431

    Re: MySQL. Get records affected by UPDATE

    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
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    I say you're crazy not to!
    --------------------------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  15. #15

    Thread Starter
    Junior Member
    Join Date
    Jun 2018
    Posts
    22

    Re: MySQL. Get records affected by UPDATE

    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.

  16. #16
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    6,925

    Re: MySQL. Get records affected by UPDATE

    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.
    Last edited by FunkyDexter; Sep 12th, 2018 at 02:02 AM. Reason: Found a link to a VB repository pattern
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width