FM unstable and corrupted

[DonH]

I've walked into a situation where the client is HUGE. Using FM 3.0 for the last 7 years with a very nice interface they built way back then.

Now it is so big that they need to move on to Oracle. BUT before they can move on they need FM stabilized. Over the last few months they have lost thousands of records due to FM crashing. Plus data is being corrupted.

Upon initial inspection, I think the problem is that the db is just too large for FM to handle and for the hardware they have.

I haven't used FM since the early days and am trying to come back up to speed on it. ugh.

Anyway, any ideas on what would be causing files to cross contaminate other files and corrupt the whole file? Or just any suggestions on how to "stabilize" FM.

Oh, another thing. The previous person found out the hard way that if he had a file open on his workstation like myfile.fm_ and people were using the FM app, FM would actually connect to the file HE had open instead of the original on the server. Any suggestions on how to prevent FM from looking at the copy that is open on a workstation?

For general info: server is Win2K with FM Server 3.0

[Kurt Knippel]

First what is "HUGE"? How many DBs, how many records in each and how many users? LAN, WAN, Web?

It honestly sounds to me like they do not need to move to Oracle, but need some reconfiguration and/or updates to thier databases.

Filemaker is one of the most reliable and stable RDBMS that you can get.

One thing that immediately springs to mind is running FMS 3.0 on Win2k. Filemaker 3 is like 1996 vintage and I highly doubt that it is designed with Win2K in mind, running on NT 4.x might be good, but for Win2k you really need FMS 5.5 or 6.x

How are the users opening the DBs? Do they use an opener file and/or File->Open->Hosts menu, or do they just navigate to a shared volume and open the DBs directly?

Is file sharing enabled on the server and is this server acting as any other kind of server (file, web, DNS, domain controller, etc)?

Regarding the opening of the file on the user's machine: basically Filemaker opens file by IP address, so generally a file will not be found unless that machine was originally hosting the file or someone switched the reference to point to that file. If the file is not found at that IP, the Filemaker will search the local subnet to find the file and it is possible that the file had been take down at some point while users were still using it and opened on that system.

Crashing and loss of records points to a problem with the implementation of the system not with Filemaker itself.

I'd seriously suggest looking at the existing system, before just going with an Oracle implmentation, it may not be necessary at all and will be far more expensive that updating the existing system, especially if it had a nice interface that the company and users are used to.

[Anatoli]

I guess they messed up the FileMaker. Next step will be expensive Oracle and they will mess expensive Oracle.

Properly maintained FM server will never mess up something. During 5 years I have single small problem, but we didn't research what was it. We just restored everything from night backup. And FM is running 5 years with downtime approximately 15 minutes.

We run even server 5 on NT and one 5.5 on Windows 2000 workstation.

[DonH]

Huge is closing rapidly on a million records with 50 databases holding it all. There are around 50 people using the system at any given time.

As a guidepoint, this is for a large hospital and they have gone paperless. All labs, meds, tests, notes etc are in this system. They do 40 doctors seeing 4 patients an hour in just the one clinic I am working in. Plus the nurses accessing records. Plus the pharmacy, techs etc

All users have a shortcut on their desktop that goes to to the host login.

Some problems I did find already deal with the lack of space on the harddrive. Truncating the database to 5 years worth of data seems to have helped somewhat.

Still waiting to see if the data cross contamination continues. But little things like certain date fields are now showing 1954 for today's date. Not all, so it doesn't seem to be a system clock error.

Oracle is a given. That is already coming down the pike. The next version won't get messed up because they have hired ME!

What I am wondering is this whole system started on a Mac server. About 6 months ago they migrated to the windows machine. Nobody knows how the person at the time did it.

Any possibility that some compatibility issues arose during the move?

[Kurt Knippel]

One million records across 50 DBs is NOTHING. One million records in each of 50 DBs might be something.

I currently administer a company with 5 Filemaker Servers, each of which hosts anywhere between 50 and 125 DBs, although we only have about 25 users. I worked for another company which had 150 or so DBs across 4 Filemaker Server, serving 250+ users across an entire state. None of this taxed Filemaker or caused any kinds of problems. Others on the FMForums will have similiar situations.

Moving from Mac to Windows will not cause problems, but running FMS 3 on Win2k might be a problem. FMS 3 is not made for Win2k, it was made for WinNT 4.x

So there is a potentially problematic combination of FMS and OS versions; running out of space on the DB server harddrive; possibly a system date conflict; a probable unnecessary database server upgrade done by someone who is no longer with the company and may have done things incorrectly. None of this is really a Filemaker issue, nor is Filemaker really causing any of this.

Correctly the above problems should eliminate the issues that you are experiencing.

Of course it is also possible that when the system was not developed correctly or may not have been designed with the current needs in mind. So there is a definate possiblity that something within the design of the system itself is causing the problems.

[Ted S]

Hi Don,

We're running the same setup that you have; FMP 3.0 on W2K boxes. It's been running fine for us for many years, and we too are moving to Oracle but we did have some problems with the server crashing a month or two back. It turns out that the problem was a bad client, not the server. We never really figured out exactly what the problem was on the client but there was one guy who could bring the whole thing down by simply clicking on the hosts button. The problem fixed itself with just a simple reboot of the client.

-Ted

[DonH]

50 databases that each have a part of the whole picture of all the patients. So each one may have more or less records than the main db. Like the main db holds the patient info. Then for instance their chart has comments. Each persons' chart may have 1 or 1000 entries. Pretty wild.

But, I had problems with FM itself way back when I first started out so know that it isn't, or wasn't, the greatest thing on earth.

Cross contamination of data is not an OS problem but a FM problem. Why FM is doing it is another matter. Could be incompatibility with the OS or could be a design flaw or it could be the FM server is overloaded.

The other consideration I have to take into account is that it was working okay until recently and then slowly started degrading in performance as the size ballooned according to the users. Truncating the db to just 5 years worth of data seems to have stabilized the system.

Now I have to remember how to export the data and view db layouts. They have NO documentation here anymore. sigh.

[Anatoli]

That everything is piece of cake. FM will run that in 3% of processor time.

But it can be messed up starting with design and implementation. Oracle done in the same fashion will blow hole in ceiling...

[DonH]

True. Any system that is abused enough will self-destruct. What worries me is the cross contamination of files. Things like Mr. Jones delivering a baby. hmmmmm. I could understand a LOSS of files and info as harddrive space ran out, but not this problem.

But then, here are some of the other gems that were dropped on me today. The original person to set all this up left right after migrating to Windows2k machine. Nobody else was assigned to watch over the system until the first crash about a month later due to a lack of harddrive space. The solution was to clean off the harddrive of files no longer needed. Then a new person in admitting decided to be "helpful". When a person would check in but have nothing done he would recycle their chart number. The chart number is set up as an index. And on and on.

Okay, now I am trying to open up these files to view the setup etc and am getting "ACCESS DENIED". I'm trying to remember how the security is set up on these files because I know as far as Windows is concerned I have full access to the files. Any help in this area?

[Kurt Knippel]

More and more your problems point to the schmuck who set this system up in the first place. Filemaker has a built-in security system to which you will need the master password that was setup by the aforementioned "schmuck".

[DonH]

The security portion, yup. And I actually got in touch with him.

Looking more and more like most of the problems cropped up after he left due to neglect and abuse by the "users".

[DonH]

Okay. I finally got in touch with the guy who built the system. We went thru all the problems and I really made him mad when I announced that his design, tho it held up for 7 years, was the root of all the problems.

Take heed all database designer wannabes and even some who think they are old hands at this.

One of the fundamentals of db design is to NOT duplicate fields. If you get a person's name in one database, do not ask for it again in another db. Always pull the data from one table (db). Not from more than 1.

Here is what happened. There are 2 files say Index1.fm and Index2.fm BOTH of them contain the full personal info on the patient. When a doctor first enters a name to pull up the records they get a list of people with that name. Then they can select one. This data comes from Index1.fm When the Dr then selects one to pull up their record, you guessed it, the data comes from Index2.fm and is keyed off a patientid.

Now, this led to disaster because a file clerk was "helping out" by checking for records that had no data. In other words they created the chart but the person never came in or whatever so it was never used. He would go back and put a new name on the old chart along with all the info. No problem because the app is set up to update both Index1 and index2 from this.

Disaster struck when the system crashed due to lack of disk space. The admin at the time, knowing nothing about FM, just grabbed the newest backup he could find. It was almost a year old. Yup. No backups were being done.

He restored the Index1.fm because it was missing totally from the backup. Hope everyone sees what just happened. If not, then --- the reused charts in Index1 now had the Original persons' info while index2 had the new person's info.

This was discovered when one of the patients with a reused file came in and the receptionist pulled up their file and then found it was the wrong name.

LUCKILY there are only about 100 of these. whew. BUT hopefully newbies can learn from this that having the same data in 2 places and pulling it from 2 places is a no-no.

[Anatoli]

RE: that having the same data in 2 places and pulling it from 2 places is a no-no.

Absolutely!

[DonH]

Okay, here is what is going on. FM3 running on W2K server. For the past 3 months I have had FMServer do an "unexpected shutdown". I have full logging turned on to see if I can catch a correlation between something else. On this server is also Cold Fusion Server 5.0. It has done this 10 times in 3 months.

Yesterday it crashed again and we tried to restart it like always. No go. 10% of the files were corrupted and had to be recovered. However THEN we rec'd "OLE ERROR" when trying to start the application. Finally we used the previous day's backup of the files and they worked fine.

Any idea why FMServer would just shut down all by itself? How about an OLE error? Any clues for the clueless here? Just trying to keep it stable until the new solution is in place.

[Kurt Knippel]

Well, this is not the reason for the crash, but I would think that having both FMS and Cold Fusion might be contributing to the problems. Easiest solution would be a fresh installation of Filemaker Server.

[DonH]

That was my first thought. Now if we could ONLY find the disks. sigh. I wonder if Claris would give us new disks if we can prove we have a license.

[LiveOak]

They probably won't, not on principle, but because it is an unsupported back version. FM Server 3 may have come on a floppy. I would ask FMI anyway. You might also take a look on ebay. I don't think Server 3 required an activation code.

-bd

[DonH]

FM said "NO!" Not because they didn't have them but because it is running on an unsupported platform. sigh.

[LiveOak]

There is currently a copy of FM Server Mac & PC on ebay for $109.99 "buy it now". You could purchase it, make a copy, and resell the license.

-bd

[Will]

I'm not sure that FM3 is certified to run on Windows 2000 - you might explore that to see if it could be the cause of your problems.

Will