Open Forum

Expand all | Collapse all

Caching Issues in BC2020 - huge bug?

  • 1.  Caching Issues in BC2020 - huge bug?

    TOP CONTRIBUTOR
    Posted 21 days ago
    Hi Guys

    Having some strange issues with BC2020 v1

    We're prepping a new environment and having to mass update some things on the item cards. I'm using SQL to update posting groups rather than spending time making RapidStart packages

    I have my item list open and can see the Gen prod and inv post groups. I update them in SQL and them refresh the item list (try F5, Ctrl-F%, close list, reopen, logout, log back in)., They don't change. I open the item card, they still don't change.

    Here's what's really concerning. Logged back in, open list (old value still there), open item card (old value still there), edit description, it says saving..., saved. I looked in the DB: nothing has changed in description. Refresh the item and the description has reverted to old value even though it said saved.

    If I update the DB, open the item (still see stale data), make an edit and then exit edit mode I get a error, which is optimistic concurrency control kicking in

    I then tried without doing anything in SQL changes

    Open and item, change the description, item card says saved. refresh: old description is back. Nothing ever changed in the database even though the UI in top right of item card says saving...saved. Only when I exit edit mode (click pencil icon or go back to list) are the changes committed to the DB.

    This seems like a huge bug to me.

    1 aspect of it is a bonus for us (we now have an undo), if I edit something in a card and want to get back my old values I can just refresh or close the browser tab

    The 2nd aspect is more concerning and this is what I'm trying to figure out: when is client going to show actual DB content vs what it's cached and is there any control over this?

    Thanks

    Mark





    ------------------------------
    Mark Anderson
    Director of ERP Systems
    Clesen Wholesale
    Evanston, IL
    ------------------------------
    Academy - Online Interactive Learning from Experts


  • 2.  RE: Caching Issues in BC2020 - huge bug?

    Posted 21 days ago
    I believe the general consensus is to not update Nav data via SQL due to this exact reason.  The reason Nav caching works so well is that all data updates are done via the service tier where the cache exists.  Any updates to the data outside this path will certainly cause the cache to not match the database.  Likewise, any internal validation logic or OnModify logic will not be executed if you update externally, so you really have to watch out for that as well.  Due to these reasons, we have sworn off doing any sql updates to our native Nav database.  So it really is not a bug.

    But, if you do find it necessary to update via sql, the SELECTLATESTVERSION() command will clear the cache.  You could create a process only report that just runs this command and run it immediately after the sql update.

    Hope this helps.


    ------------------------------
    Mike Ashley
    Arbor Day Foundation
    Lincoln NE
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 3.  RE: Caching Issues in BC2020 - huge bug?

    TOP CONTRIBUTOR
    Posted 21 days ago
    Speaking from the Partner side, there is a reason we have the Rapid Start package.  I believe Mike's answer is the correct answer.  You should not use SQL to update the data in your tables.  You cannot guarantee you are validating the data if you are updating in SQL.

    ------------------------------
    Robb Delprado
    Senior Trainer and Consultant
    New View Strategies
    robb@newviewstrategies.com
    860.558.8032
    Houston, TX
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 4.  RE: Caching Issues in BC2020 - huge bug?

    TOP CONTRIBUTOR
    Posted 21 days ago
    Mark,
    Updating data using SQL is such a bad idea and can be the cause of so many issues that internally, we make a notation in our customer's information if we know they do it. It is specifically an area where we really cannot do much to help you if it is causing you issue except to tell you to not do it.

    Double ditto what Mike and Robb said!

    ------------------------------
    Crystal Tollison
    Customer Service
    Archerpoint
    Lawrenceville GA
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 5.  RE: Caching Issues in BC2020 - huge bug?

    TOP CONTRIBUTOR
    Posted 21 days ago
    Thanks All for the replies, I'm aware of the risks of using SQL and we are doing it in a test environment we're building. I use RapidStart a ton. but some things are WAY quicker in SQL. All that said, the issue exists regardless of whether SQL is used (I just discovered it by using SQL), as I explained here

    In the web client
    1. Open and item card
    2. Change the description
    3. Item card says saving..., saved.
    4. Refresh: or close browser tab
    5. Old description is back.

    Nothing ever changed in the database, even though the UI in top right of item card says saving...saved.

    *Only* when I exit edit mode (click pencil icon or go back to list) are the changes committed to the DB.


    This seems like a huge bug to me and is going to cause data loss: A user gets a message saying saved, and the data is not saved

    ------------------------------
    Mark Anderson
    Director of ERP Systems
    Clesen Wholesale
    Evanston, IL
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 6.  RE: Caching Issues in BC2020 - huge bug?

    SILVER CONTRIBUTOR
    Posted 21 days ago
    Hello!

    I agree with Crystal, Robb and Mike.  I've used 5 versions of NAV over 20 years and have never updated any data through SQL.  I understand that RapidStart can take forever when you have a large amount of data, so to get around that we have written XMLports in NAV.  Works like a charm and you can still take advantage of NAV validation and logic.

    Good luck!


    ------------------------------
    Andrea Wasley
    Project Manager
    NOCO Energy Corp.
    Tonawanda NY
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 7.  RE: Caching Issues in BC2020 - huge bug?

    Posted 20 days ago
    Hi Mark,

    I do something similar, update via SQL.  When I refresh my TEST database with PROD, I go into all my companies and change the System Indicators (because this can't be done through Rapidstart).  I always have to restart the NAV service in order for the UI to pickup the changes.  I reviewed this scenario with my partner and they don't quite understand it either (and they're quite knowledgeable).

    So my answer, you MUST restart the service/NST if you update via SQL.

    ------------------------------
    Jimmy Yu
    Enterprise Architect
    Step Energy Services Inc
    AB
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 8.  RE: Caching Issues in BC2020 - huge bug?

    TOP CONTRIBUTOR
    Posted 20 days ago

    Thanks Jimmy

     

     

     

    Mark Anderson | Director of ERP Systems

     

    Mobile 312.576.4332 | Direct 847.448.4202

     

     




    Academy - Online Interactive Learning from Experts


  • 9.  RE: Caching Issues in BC2020 - huge bug?

    SILVER CONTRIBUTOR
    Posted 17 days ago
    I agree with Jimmy. I have used SQL in the past only because I have examined the table and field validation triggers. Once you could subscribe to events, you had to debug the triggers to see who was subscribing to an event.​ So my rule of thumb is if I know the data (mostly just our custom tables), I can do a bulk load in SQL with no one on the system, then restart the services.

    Never, ever, ever update data with SQL while users are on the system and transacting data.

    ------------------------------
    Joseph Flynn
    Sr. Dynamics NAV Developer
    Gold Star Foods INC
    Ontario CA
    ------------------------------

    Academy - Online Interactive Learning from Experts


If you've found this thread useful, dive deeper into User Group community content by role