7 Replies Latest reply on Mar 8, 2011 6:07 PM by MikeHackett

    Creating a NEW data base / Transactions Table per YEAR , Crashes

    MikeHackett

      Summary

      Creating a NEW data base / Transactions Table per YEAR , Crashes

      Product

      FileMaker Pro

      Version

      11 Trial

      Operating system version

      Windows 7

      Description of the issue

      I have Transaction data that is organized by quarter. This is in a Text.csv file. I have a table set-up and choose import file to try to combine a full years worth of transactions.

      I have tried multiple times with the same error. When it gets to about 6 to 7 million records, it will crash and restart I get the ERROR: "Filename" is damaged and cannot be opened, use the recover command to recover this file.

      Recover processes and fails.

      Steps to reproduce the problem

      Blank database. Import Header, then import files to make up for years worth of transactions.

      2003     3     AL      1,556,645
      2003     3     MZ      2,400,674
      2003     6     AL      1,823,486
      2003     6     MZ      2,448,940
      2003     9     AL      1,873,406
      2003     9     MZ     
      2003     12     AL     
      2003     12     MZ

      Expected result

      I can never get just these 5 files complete.
      I have tried individually, and different in different order but seems to always crash between 6 to 7 million records. The actual FP7 size varies from 2.2gb to 4.4gb

      Exact text of any error message(s) that appear

      2011-03-04 23:21:40.230 -0700     PRoject1.fp7     0     *** Started consistency check of improperly closed file, total of 1048385 block(s) to check
      2011-03-04 23:21:40.387 -0700     PRoject1.fp7     8430     ERROR: Last page 1049345 doesn't match end of file page 1048385
      2011-03-04 23:35:44.678 -0700     PRoject1.fp7     8437     ERROR: Page 1045768 invalid previous page link 1045515
      2011-03-04 23:35:44.706 -0700     PRoject1.fp7     805     FAILED consistency check, error 805
      2011-03-04 23:36:10.086 -0700     PRoject1.fp7     0     *** Started consistency check of improperly closed file, total of 1048385 block(s) to check
      2011-03-04 23:36:14.363 -0700     PRoject1.fp7     0     Skipped rest of consistency check, only 22154 block(s) were checked
      2011-03-04 23:46:24.065 -0700     PRoject1.fp7     0     *** Started consistency check of improperly closed file, total of 1048385 block(s) to check
      2011-03-04 23:57:06.542 -0700     PRoject1.fp7     8437     ERROR: Page 1045768 invalid previous page link 1045515
      2011-03-04 23:57:06.545 -0700     PRoject1.fp7     805     FAILED consistency check, error 805
      2011-03-05 00:10:04.471 -0700     PRoject1.fp7     0     *** Started consistency check of improperly closed file, total of 1048385 block(s) to check
      2011-03-05 00:22:15.137 -0700     PRoject1.fp7     8437     ERROR: Page 1045768 invalid previous page link 1045515
      2011-03-05 00:22:15.184 -0700     PRoject1.fp7     805     FAILED consistency check, error 805
      2011-03-05 09:32:11.004 -0700     PRoject1.fp7     0     *** Started consistency check of improperly closed file, total of 1048385 block(s) to check
      2011-03-05 09:44:32.727 -0700     PRoject1.fp7     8437     ERROR: Page 1045768 invalid previous page link 1045515
      2011-03-05 09:44:32.794 -0700     PRoject1.fp7     805     FAILED consistency check, error 805
      2011-03-05 16:29:58.492 -0700     PRoject1.fp7     0     *** Started consistency check of improperly closed file, total of 1048385 block(s) to check
      2011-03-05 16:40:35.671 -0700     PRoject1.fp7     8437     ERROR: Page 1045768 invalid previous page link 1045515
      2011-03-05 16:40:35.685 -0700     PRoject1.fp7     805     FAILED consistency check, error 805
      2011-03-05 20:11:50.688 -0700     PRoject1.fp7     0     *** Started consistency check of improperly closed file, total of 1048385 block(s) to check
      2011-03-05 20:24:11.154 -0700     PRoject1.fp7     8437     ERROR: Page 1045768 invalid previous page link 1045515
      2011-03-05 20:24:11.161 -0700     PRoject1.fp7     805     FAILED consistency check, error 805
      2011-03-05 20:29:39.858 -0700     PRoject1.fp7     0     *** Started consistency check of improperly closed file, total of 1048385 block(s) to check
      2011-03-05 20:40:10.585 -0700     PRoject1.fp7     8437     ERROR: Page 1045768 invalid previous page link 1045515
      2011-03-05 20:40:10.589 -0700     PRoject1.fp7     805     FAILED consistency check, error 805

      Workaround

      Make all smaller tables / half years.. but doing this for 2002-2010 :-(

      ErrorPopups.png

        • 1. Re: Creating a NEW data base / Transactions Table per YEAR , Crashes
          philmodjunk

          Are you always importing into the same file in all your tests?

          Have you tried importing into a new file created just to test this issue?

          That would rule out the possibility of a problem in your target file resulting in the crash.

          • 2. Re: Creating a NEW data base / Transactions Table per YEAR , Crashes
            MikeHackett

            I create a New Database each time i try. 

            I even tried without headers, and same results.

            I have tried different sets of data thinking maybe its one of the imports.

            I have 8 Files per year of transactions. I am trying to import the seperated 8 files into 1 table for the entire years transactions.

            I have tried making the database local, instead of on the External DB where the data resides.

            I will be importing using SQL this week.

            Thanks for your post. I was hoping this wasnt an issue because i am using a trial version ? Wouldnt want to buy software if it was going to crash the same way, but the person requesting this conversion is a MAC only user and would love it in MAC / via FileMaker..

            Any help is appreciated..

            • 3. Re: Creating a NEW data base / Transactions Table per YEAR , Crashes
              philmodjunk

              Hmmm, You start with New Database from the file menu, not save a copy as clone. That's how I read your last post. Save a copy as could replicate issues from the original file into the copy so we want to rule that out.

              How do you define the table in the new file that will receive the imported data? Are you using the New Table option in the import dialog or are you defining the table before you initiate the import? I don't see why that would make a difference, but it might be useful info for the FileMaker techs when they finally get around to taking a closer look at this.

              I'm not sure what this list from your original post represents:
              2003 3 AL 1,556,645
              2003 3 MZ 2,400,674
              2003 6 AL 1,823,486
              2003 6 MZ 2,448,940
              2003 9 AL 1,873,406
              2003 9 MZ
              2003 12 AL
              2003 12 MZ

              Is this a sample of the data found in the import files? (I don't see any commas typical to a csv file.)

              I haven't seen any similar reports here in the forum so we need to make sure we can rule out all possible "non bug" issues if possible.

              • 4. Re: Creating a NEW data base / Transactions Table per YEAR , Crashes
                MikeHackett

                I create a NEW database each time.

                When i Import the File, i choose import into a NEW table. Should i create indexes before importing datasets, is one of my ideas.. but havent tried that yet.

                The 2003 3 AL 1,556,645 < Represens a description of the file.  "year" "Month" "AL(Customers Names from A-L)" and the actual record Size.

                The actual table has 23 columns,

                I listed the File information to show the actual records sizes.. 

                Here is 2 sample records:

                733,2002,12,2,"RAT2                    ",2,10/1/2002 0:00:00,12/31/2002 0:00:00,"PS","NWPP                                              ","Lancaster - 3193                                  ","N/A ","LT  ","H   ","FP  ","ENERGY                                            ","402422",1.56,"MWH       ","0","631400","",12/30/1899 0:00:0096,2002,12,1,"1                       ",1,10/1/2002 0:00:00,10/31/2002 0:00:00,"CS","Entergy                                           ","N/A                                               ","N/A ","LT  ","H   ","FP  ","CAPACITY                                          ","48682",0.00,"$/MWH     ","0","4342187.7","",12/30/1899 0:00:00

                • 5. Re: Creating a NEW data base / Transactions Table per YEAR , Crashes
                  MikeHackett

                  733,2002,12,2,"RAT2                    ",2,10/1/2002 0:00:00,12/31/2002 0:00:00,"PS","NWPP                                              ","Lancaster - 3193                                  ","N/A ","LT  ","H   ","FP  ","ENERGY                                            ","402422",1.56,"MWH       ","0","631400","",12/30/1899 0:00:00

                  96,2002,12,1,"1                       ",1,10/1/2002 0:00:00,10/31/2002 0:00:00,"CS","Entergy                                           ","N/A                                               ","N/A ","LT  ","H   ","FP  ","CAPACITY                                          ","48682",0.00,"$/MWH     ","0","4342187.7","",12/30/1899 0:00:00

                  • 6. Re: Creating a NEW data base / Transactions Table per YEAR , Crashes
                    philmodjunk

                    One of the reasons that I asked about this is that just after posting this list, you say: "I can never get just these 5 files complete"

                    Yet your list is more than 5 items long. Thus it appears to list more than 5 files. So I wasn't sure what that list represented.

                    Hmmm, had one more crazy thought, but you have to ask dumb questions some times. You indicated that the crash always seems to happen around 6-7 million records. Do you have sufficient unused space on your hard drive that you aren't running out of space as the file grows during the import? (Like I said a dumb question given the huge size of hard drives these days, but had to ask just to be sure.)

                    Can you run checkDisk or other hard drive scan to confirm that your drive is healthy? (Maybe the file grows 'til it hits a bad sector and then crashes...)

                    • 7. Re: Creating a NEW data base / Transactions Table per YEAR , Crashes
                      MikeHackett
                      File Definition
                      Year Month Group Records 
                      2003 3 AL          1,556,645     
                      2003 3 MZ          2,400,674     
                      2003 6 AL          1,823,486     
                      2003 6 MZ          2,448,940     
                      2003 9 AL          1,873,406     
                      2003 9 MZ          2,749,730     
                      2003 12 AL          2,198,799     
                      2003 12 MZ          2,123,079   

                      Hard Drive is a New 500gb -  300gb Free / Checkdisk no errors, Also tried to load locally on my Laptop, HD 250GB - 110GB Free Checkdisk also no errors.