11 Replies Latest reply on Jul 13, 2012 3:30 PM by Stephen Huston

    Database crashes when adding/deleting fields in general

    ADNPlus

      Situation: FileMaker 11 server with about 45 users on a regular basis running on dedicated Windows Server 2008. Fast-Ethernet network.

       

      Problem: Can't modify a field, add or delete, without killing everyone's connection. Coffee cup appears on their PCs. Calculation changes are basically impossible during working hours. I have to make my updates on a database copy and then make the changes at the end of the day with no one connected. Otherwise, the file will be forced shut by FileMaker server and a recovery of the file will be needed.

       

      Is this the way it has to be? It's über frustrating.

        • 1. Re: Database crashes when adding/deleting fields in general
          Vaughan

          While field changes are being made the table is locked for all users: no user edits, new records or deletions. If the table has a lot of records and lots of indexes the wait for the lock to be removed can be lengthy, like several minutes or more.

           

          If you have 45 concurrent users you might have to schedule down-time windows so you can make these changes.

          • 2. Re: Database crashes when adding/deleting fields in general
            ADNPlus

            Thanks! I have been doing that all along. It's very disruptive to the creative process, but I guess that just the way it is.

            • 3. Re: Database crashes when adding/deleting fields in general
              Mike_Mitchell

              Something to consider: Have another copy of the database to do development / tinkering on. When it's working the way you want, take an outage and migrate your changes. This allows you to do all your testing, tweaking, etc., without disrupting productivity.

               

              It also minimizes the risk to your database structure. And prevents angering your user base.   

               

              Mike

              • 4. Re: Database crashes when adding/deleting fields in general
                ADNPlus

                Thanks Mike, that's what I said in my original post. That helps somewhat, plus it can get a little messy if there are updates on a script needed in the production copy.

                • 5. Re: Database crashes when adding/deleting fields in general
                  psijmons

                  Depending on the size and complexity, and expected future development, you may consider to rebuild your solution using data separation. That way you can work on de UI files in a developer copy and just swap these after office hours in a 2 min process.
                  It may take a while to get used to it, but once you got the hang of it it works well. Matt Patrowsky did a great series on data separation you may want to look at.

                  • 6. Re: Database crashes when adding/deleting fields in general
                    Stephen Huston

                    I was recently supporting a file system with about that many clients, and did field-def editing live all the time. The difference for me was probably that we had a 68-file system, most were old single-table files converted from pre-7. Each file had its own limits on user editing during my work, so I bumped heads with client edits fairly infrequently. When I did, I would simply get them to commit the record so the Save could progress on the file structure.

                     

                    As you move more toward a single- or fewer-file system, the conflicts within a single file will tend to increase. The separation model helps a great deal, but the real issue remains the need to change the schema (tables/fields). You can build in some flexibility with the separation model by adding some extra fields of each data type so they will pre-exist when you need them in the interface file.

                     

                    However, you are still going to need down time eventually when you can update schema, if only to add more extra fields for later as you use the previous ones up.

                     

                    Funny how in some ways the old 1 table per file minimized this particular problem for live development. (Though I still prefer a small number of files using data separation for new projects.)

                    • 7. Re: Database crashes when adding/deleting fields in general
                      ADNPlus

                      Thanks but that's another way to skin the cat and I am trying to avoid skinning it in the first place. This index lock/rebuild business is problematic at best. I don't know if it's because an underlying performance problem with the design of FileMaker, or because of inefficient index implementation. I'll keep rolling with the punches either way.

                      • 8. Re: Database crashes when adding/deleting fields in general
                        ADNPlus

                        Indeed. In my case, the problem manifests on the table used the most by users. I have tried splitting it into baby tables but strangely enough it's not really practical from an end user standpoint. So am kinda stuck with the way it is. Thanks.

                        • 9. Re: Database crashes when adding/deleting fields in general
                          Stephen Huston

                          And I suspect that your biggest delay comes when revising calculation or index settings, as FM needs to actually reprocess every single record in the table as it finishes saving the schema changes. That was where things got really slow, especially on one of our servers in another city accessed via a T4 line instead of local Ethernet. An index or calc change could take over a half hour on some large-record-count tables via fairly high-speed WAN.

                          • 10. Re: Database crashes when adding/deleting fields in general
                            jormond

                            Without trying to sound crass...

                            what systems allow you to make structure and script changes without negatively affecting the end user?

                            What should FileMaker do with those users that mid-script when you make the structure change?

                            There are major problems when you start making live changes to a solution. Not that you can never do it. But you need to fully understand what is happening when you do.

                             

                            I think Skeleton Key had a nice video that showed some of the dangers of working on a live, production system.

                            • 11. Re: Database crashes when adding/deleting fields in general
                              Stephen Huston

                              Joshua Ormond wrote:

                               

                              There are major problems when you start making live changes to a solution. Not that you can never do it. But you need to fully understand what is happening when you do.

                               

                              I agree that the problem for many who need to work on live systems is in understanding all those pesky implications, which  vary depending on the file structures and what kind of live changes are attempted.

                               

                              It sounds like cmorse has  least one table which is pretty wide, and the bigger a table gets, both in field count and record count, the more schema changes will interfere with  clients.

                               

                              In my case, most of the clients were in the same office areas with me, with about a third  in another city, while a few might be on the road. Warning them all when something was in the works was important but never foolproof. Dealing with locked records with a remote client was a real chore, and never made either of us happy with the process. My vote is for:

                              • Separation Model with dummy fields for ad hoc field additions. [YMMV]