locked
Database values reverted to their defaults when updating the database RRS feed

  • Question

  • User-1158769213 posted

    Ecommerce web site, written using ASP.NET Core 3.1, Razor pages, EF 3.1.8 code first.

    I have a model called Global. When I first set this up, I included the default values for four properties (others not shown for clarity)...

    [Column(TypeName = "decimal(18,2)")]
    public decimal DeliveryCharge { get; set; } = 3.5M;
    
    [Column(TypeName = "decimal(18,2)")]
    public decimal FreeDeliveryOver { get; set; } = 70.0M;
    
    public string StripePublicKey { get; set; } = "pk_test_...";
    public string StripeSecretKey { get; set; } = "sk_test_...";
    

    When I deployed the site to the production server, I manually set the two Stripe keys to the live values, so the site could take payment.

    This all worked fine until one day, seemingly out of the blue, we got an exception from Stripe, as the site was using the test keys again. I checked the Globals table, and yes, the test keys were back in there.

    I thought it was because of the default values in the model. I removed these, updated the database and thought that was it. However, it wasn't. It's happened since, both on the production server and on my local machine.

    I'm not 100% sure, but I think the problem occurs after updating the database. UPDATE 3rd Dec: The issue happened again during last night, and no-one was touching the site, so it seems it's not updating the database that does it. Also, another property on that row (an integer) was zeroed. This one never had a default value set, so it doesn't seem to be anything to do with that.

    The odd thing is that on the first couple of times this happened, the two decimal properties in there did not have their values reset to the defaults. I know this, as the FreeDeliveryOver one was changed to 50.0M shortly after we launched, and that didn't get reverted when the issue was first noticed. However, the issue just happened again, and this time that property was set to zero (not the original default value), but DeliveryCharge was left at 3.50M (the default), so it seems to be inconsistent as to which values are reverted. The two Stripe keys always are, the FreeDeliveryOver wasn't the first couple of times, but was just now, and DeliveryCharge has never been reverted.

    What I don't understand is why Entity Framework is changing these values. I would have thought that they would only have been used when the table was first created, or when a property was added. In the latest change, the Globals table wasn't touched, so I can't see why EF changed the data in it.

    To clarify...

    • We don't have a constructor on the Global model
    • The only place the Stripe keys ever appeared in the code base was in the initialisers shown above, and in the migrations that EF created.
    • Whilst the initialisers were still there, this issue would cause the Stripe key properties to be set to the test values. Since removing the initialisers, the two keys are now set to null.
    • The Globals table only has one row (I know, we really should be using the app settings file for this!), and we never update the Stripe keys in the code, we only ever read them. The two decimals shown above can be updated from an admin page, but they haven't done this, certainly not just as I was updating the database, so that's not the cause.

    Anyone any idea what's going on? I'm really nervous about this, as I don't trust EF any more. How do I know its not reverting values in other tables?

    Thanks for any help you can give.

    Wednesday, December 2, 2020 11:39 PM

All replies

  • User1535942433 posted

    Hi Yossu,

    Accroding to your description,do you have  the  FreeDeliveryOver value  more than 18 digits? If you had 18,2 for example, you would have 18 digits, two of which would come after the decimal.

    Best regards,

    Yijing Sun

    Thursday, December 3, 2020 2:10 AM
  • User-1158769213 posted

    Hello, thanks for the reply.

    If you read my question, I said that the original value for FreeDeliveryOver was 70.0M, and we later changed it to 50.0M, neither of which are anywhere near the 18 digits.

    Also, the main issue is with the Stripe keys, which are strings.

    By the way, it doesn't seem to be connected with database updates, as the issue happened again during the night.

    Thanks, any other ideas?

    Thursday, December 3, 2020 11:24 AM
  • User475983607 posted

    Database records do not magically revert and Entity Framework does not execute code on its own.  There has to be logic somewhere that is updating the database.   Did you write logic that populates table(s) when the application starts?

    It should be very simple to find where the Global model is referenced in the code.  Anyplace where Global is is created has a potential to cause a problem. 

    Keep in mind, your design approach allows for configuration changes in the fly.   Configuration should be read only.   I recommend using standard configuration practices.  Move the configuration to appsetting.release.json or server variables rather than creating a custom solution.  Doing so should fix the problem.

    Thursday, December 3, 2020 1:42 PM
  • User753101303 posted

    Hi,

    As this is one row with default values for what appears to be settings I suspect that:
    - at some places in your code, this entity is used to read data back from the db and so settings are taken from current values stored in the database
    - at some other places an object instance is created and used immediately and so settings are taken from the default values defined in the class that may not match any more the db

    If values in the db really changed it could be also that you have an UI to edit those settings that ends up in updating a single property causing other values to be reverted back to the default value defined in the class.

    Or maybe your migration includes deleting and recreating this row reverting back to what you have in this class on each migration ????

    Thursday, December 3, 2020 3:29 PM
  • User-1158769213 posted

    @mgebhard and @PatriceSc,

    I wish it were as simple as that. As I said in my first post, the only place the Stripe keys ever appeared in the code base was in the initialisers shown above, and in the migrations that EF created. There isn't any way to change these via the UI, they were only ever changed manually.

    The FreeDeliveryOver column can be changed via the UI, but they haven't touched that since the one change shortly after we went live.

    As for the migrations, I haven't modified them at all. They are exactly what EF generated. As far as I understand, if I don't modify a model class, then there is no reason why the corresponding table should be modified.

    However, it turned out that the database update was a red herring, the issue happened again during the night, and as I'm the only one who can update the database, I can confidently say that this wasn't the cause.

    I know what you're both thinking, but I can assure you that as a software developer with over 30 years of experience, I am certain that my code is not changing these values. Again, there is nowhere in the UI that allows the Stripe keys to be changed, nor is there anywhere in the code base where we update it. That's why I wondered if somehow EF could be doing it, as I can't see any other explanation.

    As for using a table for configuration, you are completely correct, and this is a priority to be changed. However, that still doesn't escape the fact that database columns that we are not changing anywhere in the code are being modified. We need to know why and how that is heppening, as for all we know, it may be happening to other tables as well.

    Thank you both for your replies. Any further comments?

    Thursday, December 3, 2020 3:46 PM
  • User475983607 posted

    Yossu

    That's why I wondered if somehow EF could be doing it, as I can't see any other explanation.

    Something is updating record if the web application is not causing the behavior.  Try denying insert/update/delete on the table for whatever account or role you deem necessary.  Then, wait for something to blow up.  That should point to the code/system doing the insert/update/delete.

    Thursday, December 3, 2020 4:23 PM
  • User-1158769213 posted

    If the web application code is not updating the record then something else is.

    Agreed, I just can't work out what.

    You can always denying insert/update/delete on the table for whatever account or role you deem necessary.  Then, wait for something to blow up to figure out what is doing the insert/update/delete.

    As it happens, since my last post, I remembered that I have a script to generate audit tables from any tables. I ran this to generate an audit for the Globals table, so at least I will be able to see if it is changed, and what values are changed. Won't necessarily help me work out why though, but it's a start.

    I've also moved the Stripe keys into appSettings.json (where they should have been in the first place), so this is less of an issue. 

    Thanks again. I'll let you know if/when I find anything.

    Thursday, December 3, 2020 4:27 PM
  • User475983607 posted

    You could also add a trigger that logs the update event but stops the update.   You can log the user and all kinds of stuff.

    Thursday, December 3, 2020 4:33 PM
  • User-1158769213 posted

    You could also add a trigger that logs the update event but stops the update.   You can log the user and all kinds of stuff.

    Well, not I have removed the Stripe keys from that table, the only things left are settings the user can modify from the UI, so I don't want to stop the update. The script I mentioned added triggers to populate the audit table with old and new values.

    Also, this is coming in from a web site, for which there is only one admin user, so logging the user won't help. I'm not sure how I would distinguish between changes made by the user, by EF updating the database or anything else. Any thoughts?

    Thanks again.

    Thursday, December 3, 2020 4:36 PM
  • User753101303 posted

    So it seems that at one some point default values defined at the class level are overwriting current settings stored in the database? Could iit happen if the app is recycled (if it doesn't happen or configured to avoid) or maybe from a "backdoor" page that could end up in being triggered by a web crawler.

    Never tried to track that from a trigger but you also have for example SELECT host_name,program_name,login_name FROM sys.dm_exec_sessions where session_id=@@SPID that can help to understand from which computer it comes etc...

    Seems CONNECTIONPROPERTY could also hep.

    Thursday, December 3, 2020 5:37 PM
  • User-1158769213 posted

    So it seems that at one some point default values defined at the class level are overwriting current settings stored in the database? Could iit happen if the app is recycled (if it doesn't happen or configured to avoid) or maybe from a "backdoor" page that could end up in being triggered by a web crawler.

    As I said before, there is nowhere, but nowhere in the code base that sets either of the Stripe keys. They were only being read in three places (two for the public key and one for the secret key), so the backdoor is out (assuming I understand your use of the word).

    As for recycling the app, why would that make any difference? I can understand how updating the database might do something (although in my case it seems that isn't the cause), but I would have thought that recycling the app would just close any open connections to the database, requiring new ones to be opened. Can't see how opening a connection can reset any data.

    Never tried to track that from a trigger but you also have for example SELECT host_name,program_name,login_name FROM sys.dm_exec_sessions where session_id=@@SPID that can help to understand from which computer it comes etc...

    Now that looks useful, thanks. Maybe I'll add some columns to the audit trigger and log this stuff as well.

    Seems CONNECTIONPROPERTY could also hep.

    Lost me a it here. Please could you explain this one a bit more.

    Thanks again.

    Thursday, December 3, 2020 5:51 PM
  • User303363814 posted

    there is nowhere, but nowhere in the code base that sets either of the Stripe keys.

    Ummm ... statement of the bleedin' obvious - there is actually one place.  You showed it in your first post ...

    public string StripePublicKey { get; set; } = "pk_test_...";

    How is this Global used by other classes?  Do other classes hold a reference to it?  Omitting the correct .Include() clause on a different class which references Global can cause problems of the type you are seeing.

    Do you have a separate build for a TEST environment? (if not, why not?)  Create a Compilation symbol in each environment of the appropriate name and then make your Global class look something like this

    #if DEBUG
            public string StripePublicKey = "debug key ...";
    #endif
    #if TEST
            public string StripePublicKey = "test key ...";
    #endif
    #if PROD
            public string StripePublicKey = "prod key ...";
    #endif
    

    Now your production build only has production information and your test build only has test information ....

    There are lots of ways to do this sort of thing and I agree with the earlier comment that good configuration practices are important.  Web.config is easy to read from and easy to configure for different environments.  Where do you get your database connection strings from?  A database connection string is a 'key to an external service'.  StripePublicKey seems to be a 'key to an external service', configure/retrieve it the same way.

    Friday, December 4, 2020 12:51 AM
  • User753101303 posted

    My understanding is that live values stored in your database are overriden with the same values than the class default (at leat for the keys). So for now it seems likely that at some point you do have code that is causing this update apparently on some rare occurence and yes for now when it could happen is just a guess.

    Selecting values from dm_exec_sessions is maybe not allowed to the app account. https://docs.microsoft.com/en-us/sql/t-sql/functions/connectionproperty-transact-sql?view=sql-server-ver15 returns ome of the same information and should be allowed access to any account I guess. I would start even witgh just which machine is sending the SQL statement to confirm or not if it comes from the server hosting the web app or from some other place.

    Friday, December 4, 2020 7:41 AM
  • User-1158769213 posted

    Yossu

    there is nowhere, but nowhere in the code base that sets either of the Stripe keys.

    Ummm ... statement of the bleedin' obvious - there is actually one place.  You showed it in your first post ...

    public string StripePublicKey { get; set; } = "pk_test_...";

    Good catch! Let me rephrase my statement. Since I removed the default value from the two properties, there is nowhere, but nowhere in the code base that sets either of the Stripe keys.

    I removed those setters after the issue happened the first time, and it still happened again.

    How is this Global used by other classes?  Do other classes hold a reference to it?  Omitting the correct .Include() clause on a different class which references Global can cause problems of the type you are seeing.

    The Global is a standalone class, used (incorrectly, see below) to hold some settings. Nothing else has a reference to it, so there aren't any .Include()s that would be possibly or relevant.

    Do you have a separate build for a TEST environment? (if not, why not?)  Create a Compilation symbol in each environment of the appropriate name and then make your Global class look something like this

    #if DEBUG
            public string StripePublicKey = "debug key ...";
    #endif
    #if TEST
            public string StripePublicKey = "test key ...";
    #endif
    #if PROD
            public string StripePublicKey = "prod key ...";
    #endif

    Now your production build only has production information and your test build only has test information ....

    You're right, but the problem has only shown up on the live environment.

    There are lots of ways to do this sort of thing and I agree with the earlier comment that good configuration practices are important.  Web.config is easy to read from and easy to configure for different environments.  Where do you get your database connection strings from?  A database connection string is a 'key to an external service'.  StripePublicKey seems to be a 'key to an external service', configure/retrieve it the same way.

    Agreed 100%, and since the issue recurred, I have changed the code so that the Stripe keys are stored in appSettings.json. However, I'm still very concerned as to how this could have happened, as without understanding that, I may be open to other problems.

    Thanks for the reply. Don't know if that gives you anything more to comment on.

    Sunday, December 6, 2020 2:22 PM
  • User753101303 posted

    And what shows your audit trigger? My undertanding is that you have a table whose values are updated unexpectedly. Could you confirm that you do see that values on the single row in your Global table are changed or do you just see that your app starts to fail with wrong keys without knowing from where those wrong values are coming?

    Sunday, December 6, 2020 9:24 PM
  • User-1158769213 posted

    And what shows your audit trigger? My undertanding is that you have a table whose values are updated unexpectedly. 

    The problem hasn't happened since I added the audit table, so nothing there yet. It's an odd problem. Only happened three or four times, with no obvious pattern, so I have no idea of telling when/if it will happen again.

    Sunday, December 6, 2020 9:27 PM
  • User303363814 posted

    I removed those setters after the issue happened the first time, and it still happened again.
    So the code you showed is not the code we are trying to debug???  Makes things difficult 

    You're right, but the problem has only shown up on the live environment.
    Being picky, again, but for a purpose.  I think you can be more specific.  From what you have said the problem is in the production database, rather then the environment.  What accesses that database?  I can think of a few possibilitiies

    • Other environments with (sometimes) incorrect configuration information (all respondents to this thread have expressed concern about configuration management)
    • Humans.  Do people configure the test database by hand?  People doing manual actions == errors
    • The production code base with the wrong configuration (see first bullet point)

    If you are seeing a string in the production database and that string only exists in the TEST code base then why would you be looking in the production codebase/environment for the problem?  If the string only exists in the TEST code base then it would seem that there are some times when the test codebase interacts with the production database.  Or, someone who works with the test environment accesses the production database by error.  Or the production connection string makes its way into the test environment.  It all points to further configuration management problems.  If humans ever manually update the test or production databases then they will get it wrong at times (particularly in the middle of the night ...)

    Are configuration files generated automatically for each environment?  Is code built, distributed and installed automatically for each environment.  If there are manual steps then there will be mistakes.  If everything is under the control of scripts/build tools then those scripts and build tools need to be examined carefully and their change history examined.

    Sunday, December 6, 2020 10:52 PM
  • User-1158769213 posted

    Sorry for the confusion, I wrote and rewrote my question a few times, and maybe I wasn't clear.

    The code for the Global model was originally as shown, including the two default values, which are test keys. "Test" in this context means that we use them when testing our code against Stripe, it no (intrinsic) relation to which database or production environment was used.

    After I saw the issue the first time, I thought that it might have been because of those two default values, as they were the only place in the code base where they appeared. I removed them, and updated the database again, hoping that had fixed it. That's basically what I said in the first part of my first post.

    Note that we don't have a "test" code base, we only have the one which we deploy to a test environment for testing, then to the live environment when we are tested. In all cases (without exception), we do not deploy the appSettings.json files. These were written by hand and uploaded to the appropriate places from the start. We never copy these files when publishing, which is how we a) keep sensitive data out of source control and b) ensure that we don't upload the wrong settings to the live environment. As I am the only person who deploys, I am confident that we have never had the wrong connections strings in the two environments.

    As to the second part of your reply: The problem is with the production database, which is only accessed by the production web site. The test web site accesses its own database, where we haven't seen this problem. As explained above, I am certain that the test site never touched the live database.

    There are only two of us with direct access to the database, and neither of us have touched that table since I changed the test Stripe keys to be the lives ones right after we first deployed. 

    Don't know if that clarifies things. Thanks for the reply.

    Sunday, December 6, 2020 11:08 PM