11 Replies Latest reply on Apr 3, 2012 3:14 PM by luistavera

    cannot modify database or layout

    luistavera

      Hi all,

       

      I recently started experiencing some strange behavior when working with a database hosted in a FMS advanced 11 running on windows server 2008. Last week, changes that I made to a layout were not preserved when saving, and today, when trying to modify a relationship I get the error "user X is modifying the database", but user X does not have privileges to do any changes to the database, and I cannot do any changes. Same for other users with privileges to do db modifications. When I had the issue with the layout I restarted the database and things went back to normal, but I'd like to avoid doing this because it affects a good number of users.

       

      I know this is vague, but if anybody can point me in a direction that can help troubleshoot the problem I'd greatly appreciate it.

       

      thanks,

       

      Luis T.

        • 1. Re: cannot modify database or layout

          Hi Luis,

           

          "changes that I made to a layout were not preserved when saving, and today, when trying to modify a relationship"

           

          If you modify a layout with a trigger, for example, it can affect Users on that layout causing unknown consequences if scripts are firing.  And changing relationships is very dangerous (table of dependencies).  One reason the separation model is so appealing is its ability to allow UI modification on separate file with simple switch during off hours.

           

          And the effects you see (and hear from Users) are only the ones you find out about ... which is usually less than half of the problems caused.  Behind the scenes, you may not discover a problem for months at which time you will be clueless as to why the data gritched the way it did in that spot.  Working in Manage Database is dangerous if Users are in the system. 

           

          Kind regards,

          LaRetta

          • 2. Re: cannot modify database or layout
            Vaughan

            Despite the fact you report the the file is hosted by FMS, the problem sounds like something that may be caused by storing a file on a network share. I'd check that all users are opening the file through FMP open remote command and not by double-clicking on the file icon.

             

            At this stage I'd also assume there is some file corruption and start by getting the backups together. Save a compressed copy of the file and see if that's better. Otherwise do a recover and work out what the problem is.

            • 3. Re: cannot modify database or layout
              Stephen Huston

              Is the FM Server machine a dedicated FM Server?

              • No network shared access to users except via Hosted Open Remote by FileMaker only, no other access ro FM files.
              • Not running other applications or services.

              FM Servers should be fully dedicated to FM Server only and must not allow file sharing reach to the FM files or problems with improper opening of files can really mess your system, leading to lost edits at various levels of both the data and schema.

              • 4. Re: cannot modify database or layout
                luistavera

                Thanks all for your input. It turned out to be a corrupt database. I created a compacted copy of the file and ran recover on it, and this generated a couple of errors, fortunately non critical. That is the DB we are using now, because the backups we had, all produced the same errors after we ran recover on them (I thought "verify backup integrity" did more than it does). We will now turn to the practice of running a recover on our daily backup to check that all is good, because it seems we had been working on the corrupt file for some time without noticing.

                 

                I gather from one of the comments that modifying an active database is not safe. If this is the case, do people do development on a copy and then transfer all the data from the production system to the new version? How do they deal with resetting ID counters? Or do developers modify the database only during off hours?

                 

                Thanks again,

                 

                Luis

                 

                PS: The FM server is a dedicated FM Server with no network shared access to users. All users open the DB via "Open Remote".

                • 5. Re: cannot modify database or layout
                  Vaughan

                  luistavera wrote:

                   

                  We will now turn to the practice of running a recover on our daily backup to check that all is good, because it seems we had been working on the corrupt file for some time without noticing.

                   

                   

                  No, no no. The Recover command recovers data from a file. It is the last-ditch option that gets the data out at the expense of structure. That is, recover will delete bits of scripts, layouts or whatever it needs to save the data. It's the emergency rescue crew cutting off the roof of your car because you locked the keys inside. You didn't need rescue, you needed a locksmith.

                   

                  Get the FMS set up correctly and all will be well.

                   

                  Did you ever check FMS to see whether the verifications were ok?

                  • 6. Re: cannot modify database or layout
                    Stephen Huston

                    I've got to second Vaughan's response to you using the recovered file for ongoing work.

                     

                    However, it may be difficult to tell how far back in your backups (you keep them right?) you  have to go to find a non-corrupt copy. The only way to find out is to run Recover on COPIES of backups and check the results until you get to a copy that was clean, then use the Unrecovered original from which you made that clean copy.

                     

                    As for modifying the database while it's hosted: that's actually very safe with one or two exceptions or GOTCHAs you have to watchout for:

                    1. Don't edit database Schema (file structure: table/field definitions) or Security/Accounts during an backup.
                    2. Don't edit more than a small number of account/security items in a single session.

                    Any of those things have been know to cause problems which appear later, after you have trusted the file for a while.

                    • 7. Re: cannot modify database or layout
                      Mike_Mitchell

                      Luis -

                       

                      To answer your other questions, serial ID values are usually reset via script. The Set Next Serial Value script step will handle this nicely.

                       

                      And yes, some people (myself included) will typically do development on a separate copy, then take an outage and transfer the data. This is also scripted.

                       

                      HTH

                       

                      Mike

                      • 8. Re: cannot modify database or layout

                        Stephen Huston wrote:

                         

                        As for modifying the database while it's hosted: that's actually very safe with one or two exceptions or GOTCHAs you have to watchout for:

                        1. Don't edit database Schema (file structure: table/field definitions) or Security/Accounts during an backup.
                        2. Don't edit more than a small number of account/security items in a single session.

                        Any of those things have been know to cause problems which appear later, after you have trusted the file for a while.

                         

                        I must strongly disagree.  If you simply add a tab to a tab panel in an active layout, you can change which portal a script lands on in that moment.  That will break the script.  If you change stacking order or tab order of fields, you can affect script trigger which is firing on that layout in the moment.  And if you are in field definitions in Options, it will stop users from being able to even add new records.  As for modifying Schema or active relationships?  Really?  FM does a pretty good job of trying to protect so Developer can work in schema while active Users are in the file but it simply can't protect from everything.

                         

                        What are the odds that you might hit one of these issues in the exact nanosecond?   The odds aren't high but if the data is important, it is not worth the risk.   At least think very carefully before modifying a calculation or relationship and how it might ripple through a live system, affecting Users in the middle of scripts or in the middle of acting on calculation results.

                         

                        And consider the effect on the Users.  The first networked system I designed, I sat in a large room with 5 Users and I worked in the system live.  I asked them to speak up whenever something 'strange' happened.  It is amazing how they were affected by every little thing I did.  And it SHAKES THEIR FAITH in the program when it wonks out, even if it resolves itself properly.  I will do anything to reduce or eliminate User mistrust for the solution.  Work at night if you have to or use separation model.

                        • 9. Re: cannot modify database or layout

                          I just came across this link which is one of the best videos I have ever seen!!  I thought others might be interested as well if you haven't seen it.  Even if you have, it is worth watching ten times ... every six months.

                           

                          http://vimeo.com/38809893

                           

                          Thank you so much, Chad at Skeleton Key  http://vimeo.com/skeletonkeystl

                          • 10. Re: cannot modify database or layout
                            Stephen Huston

                            Those of us who are In-House developers for systems which run 24/7/366 really have no choice but to work on live systems.

                             

                            My point about safety is that with the cautions I mentioned, we have had NO issues in live development via served files. Yes, sometimes users ask why something changed.

                             

                            When modifying layouts in any obvious way, I try to warn them what's coming.

                             

                            And yes, it's conceivable that a running script could land in the wrong place if one is not careful and is also making major changes.

                             

                            So, sure, editing off-line is safer in some ways, but not due the the server environment. In fact, client machine freezes or FM crashes run less risk to the files if they are served at the time rather than open in single-user mode.

                            • 11. Re: cannot modify database or layout
                              luistavera

                              Thanks all again for your responses.

                               

                              We have decided not to take any risks and limit changes to well defined maintenance windows where no users are allowed in the application, at least while we migrate to a separation model (although the separation model won't allow us to modify tables either) and we prepare data moving scripts (A good side effect of this is that it forces developers to systematically document their changes).

                               

                              What still baffles me a bit is the fact that the recovery process doesn't seem to be trustworthy. If it only reports, for example, that script X is damaged, but it turns out that you don't care about script X, why wouldn't you delete the script and use the recovered file?

                               

                              cheers,

                               

                              Luis