Alpha Video Training
Page 1 of 2 12 LastLast
Results 1 to 30 of 39

Thread: Confued by Database Compact error message ...

  1. #1
    Volunteer Moderator drgarytraub's Avatar
    Real Name
    Dr. Gary Traub
    Join Date
    May 2000
    Location
    Boca Raton, FL
    Posts
    2,784

    Default Confued by Database Compact error message ...

    I run Database compact at least several times per day. Sometimes I get an error message of the sort:

    Could not update:
    Tablename
    Would you like to continue?

    I have looked at the data of this table and cannot find any obvious problem. I can append the data to a new table and the new table is fine (no problem when run Database compact). I can copy the data to a new table and the new table also has no problem. But, if I empty the errant table, it still gives the error message when Database Compact is run, leading me to think the problem may not be with the data, but with an index or dictionary. How can I determine the cause, and more importantly, how can I fix it?

    Thanks in advance ...

    Gary

  2. #2
    "Certified" Alphaholic
    Real Name
    Russ Boehle
    Join Date
    Mar 2000
    Location
    Connecticut
    Posts
    2,767

    Default RE: Confued by Database Compact error message ...

    The table may be open (as in, in memory) even thought you don't think it is. Try restarting A5 before doing the compact and see if you get the same message.

    Russ

  3. #3
    Volunteer Moderator drgarytraub's Avatar
    Real Name
    Dr. Gary Traub
    Join Date
    May 2000
    Location
    Boca Raton, FL
    Posts
    2,784

    Default RE: Confued by Database Compact error message ...

    Hi Russ,

    Thanks for the input - unfortunately, I already tried that - even reboooting - and does not help. Any other ideas?

    Gary

  4. #4
    "Certified" Alphaholic
    Real Name
    Russ Boehle
    Join Date
    Mar 2000
    Location
    Connecticut
    Posts
    2,767

    Default RE: Confued by Database Compact error message ...

    A while back I had problems compacting a database, tried everything. Jerry Brightbill suggested recreating the database. I copied over all the code added the tables and it solved the problems. It was actually easier than I had anticipated.

    Russ

  5. #5
    Former Alpha Employee JerryBrightbill's Avatar
    Real Name
    Jerry Brightbill
    Join Date
    Apr 2000
    Posts
    5,173

    Default RE: Confued by Database Compact error message ...

    Gary

    There are really 2 main reasons you can't compact a table. Either the table is open somewhere or the table data dictionaries are badly corrupted. Sometimes running the compact from the menu distorts that, since it also packs and reindexes, but if you compact with dictionary.update() that only compacts the data dictionaries. Typically, a failed compact has nothing to do with any data problems. Bad data usually will show up with index issues, fetch problems, or packing errors.

    Unfortunately, if the failed compact is from corruption, your options are limited. My first choice would be to duplicate the table with data, but no data dictionary info (forms, layouts, etc) Try compacting that. It should compact easily, since with an empty data dictionary, there is nothing to compact. Then one by one, copy forms and other layouts from the old table to the new one. Compact after each addition. When compact fails, you will know where the corruption exists. If you get everything copied and it still compacts, delete the original and duplicate the copy back to the original name will all elements.

    I have seen corruption so bad, the only solution was to start over and completely rebuild. Not much fun and something you only want to do once. Regular compacting an saving series of backups will become normal behavior.

    Jerry

  6. #6
    Volunteer Moderator drgarytraub's Avatar
    Real Name
    Dr. Gary Traub
    Join Date
    May 2000
    Location
    Boca Raton, FL
    Posts
    2,784

    Default RE: Confued by Database Compact error message ...

    Russ and Jerry,

    Problem solved, but here is how I did it:

    Starting with Jerry's suggestion to duplicate the table, but instead of just copying the data (without layouts and field rules), I copied everything. Then ran Database Compact - the original table still caused an error, but the new table did not. So, I then moved the errant table and its associated files to a safe area on my hard drive. Then renamed the new table to the name of the old table. Ran Database COmpact and ... works fine! Tested the application, and all is well. Thanks guys!!

    Gary

  7. #7
    Volunteer Moderator drgarytraub's Avatar
    Real Name
    Dr. Gary Traub
    Join Date
    May 2000
    Location
    Boca Raton, FL
    Posts
    2,784

    Default RE: Confued by Database Compact error message ...

    Actually, I still have the same problem.

    The problem table is called progressnotes. If I run Database Compact, I get the errors as I described above.

    I then used Duplicate to create an exact copy of progressnotes (copying all components: records, indexes, dictionary, field rules) and called it progressnotes_new. I then removed progressnotes from the database. Then ran database compact and there is no error message, i.e., progressnotes_new caused no problem. So now, thinking I was just one step away from a solution, I renamed progressnotes_new to progressnotes. Then ran Database Compact, and again I get the error message ????

    How can just the name of the table be causing this error. I am very confused. And I certainly would rather not rebuild this entire module because it has been developed over quite some time, and was working flawlessly. Any ideas?

    Gary

  8. #8
    Former Alpha Employee JerryBrightbill's Avatar
    Real Name
    Jerry Brightbill
    Join Date
    Apr 2000
    Posts
    5,173

    Default RE: Confued by Database Compact error message ...

    Gary

    I suspect there is something in the code that references the name with the problem. If the cause was some old corruption, it may be very difficult to find. The easy solution is the obvious, rename the table.

    Another possibility that should be tried is to drop the table from the database, close A5 and reopen, compact, close and reopen and try to create a new table with the same name and see what happens. You will have to move the old table to another directory temporarily to not overwrite it. If a new table of the same name fails, I would just go back to the solution of renaming.

    Jerry

  9. #9
    "Certified" Alphaholic
    Real Name
    JohnZaleski
    Join Date
    Oct 2000
    Posts
    1,736

    Default RE: Confued by Database Compact error message ...

    Or, copy the problem table and its dictionaries to new directory.Then create a new database in that directory and add that table.Then compact.If it compacts OK then problem may reside in database ( adb ) itself.
    Then you could rebuild a new database and add all sets and tables.

  10. #10
    Volunteer Moderator Peter.Greulich's Avatar
    Real Name
    Peter Greulich
    Join Date
    Apr 2000
    Location
    Boston, MA
    Posts
    11,654

    Default RE: Confued by Database Compact error message ...

    Gary,

    Try compacting the troblesome table only. If you need the script, I'll post it (again).

    Peter

  11. #11
    Member
    Real Name
    James Urbanski
    Join Date
    May 2000
    Location
    New Port Richey, FL
    Posts
    620

    Default RE: Confued by Database Compact error message ...

    Plase post it since I have the same problem
    Thanks
    Jim

  12. #12
    Volunteer Moderator Peter.Greulich's Avatar
    Real Name
    Peter Greulich
    Join Date
    Apr 2000
    Location
    Boston, MA
    Posts
    11,654

    Default RE: Confued by Database Compact error message ...

    Here it is:
    (BTW: I sometimes compact shadow tables, then copy them to the server. Don't try this in your own home unless accompanied by a genuine Alphaholic.)
    [b]
    'Date Created: 21-Oct-2003 03:06:33 PM
    'Last Updated: 21-Oct-2003 03:06:33 PM
    'Created By : PG
    'Updated By : PG
    'Perform maintenance (compact) on the selected dictionary.
    '-----------------------------------------------
    IF a5.Get_Master_Path() "" ''then the shadow database is running
    vChoice = ui_get_radio("Select Master or Shadow for Dictionary Update",1,"Master","Shadow")
    SELECT
    CASE vChoice = "Master"
    vPath = vDBF_Path

    CASE vChoice = "Shadow"
    vPath = a5.Get_Path()

    CASE vChoice = ""
    End
    END SELECT
    ELSE
    vPath = vDBF_Path
    END IF

    vFilename = ui_get_file("Dictionary to Update", "table(*.DDD)|set(*.SET)|application(*.ALB)",vPath+CHR(92),"X")

    IF vFilename = "" THEN
    END
    END IF

    ''NEED TO GET FILE SIZE OF DDD/DDM BOTH or SET/SET or ALB/ALM
    Select
    Case FILE.FILENAME_PARSE(vFilename,"e") = ".ddd"
    vFileOtherExtension = ".ddm"

    Case FILE.FILENAME_PARSE(vFilename,"e") = ".set"
    vFileOtherExtension = ".sem"

    Case FILE.FILENAME_PARSE(vFilename,"e") = ".alb"
    vFileOtherExtension = ".alm"
    End Select

    vPreFileSize1 = FILE.SIZE(vFilename)
    vPreFileSize2 = FILE.SIZE(FILE.FILENAME_PARSE(vFilename,"DPN") + vFileOtherExtension)
    dictionary.update(vFilename)
    vPostFileSize1 = FILE.SIZE(vFilename)
    vPostFileSize2 = FILE.SIZE(FILE.FILENAME_PARSE(vFilename,"DPN") + vFileOtherExtension)

    vPreFileSize = vPreFileSize1 + vPreFileSize2
    vPostFileSize = vPostFileSize1 + vPostFileSize2

    vDiff = (1 - (vPostFileSize/vPreFileSize))*100

    Msg1 = vFilename + chr(13) + "Table Compacted" + chr(13) + \
    "Original FileSize = " + Ltrim(str(vPreFileSize)) + chr(13) + \
    "Comapcted FileSize = " + Ltrim(str(vPostFileSize)) + chr(13) + \
    "Diff. = " + str(vDiff,4,1) + "%"

    ui_msg_box("Database Dictionary Update",Msg1,UI_INFORMATION_SYMBOL)

    END

  13. #13
    Member
    Real Name
    James Urbanski
    Join Date
    May 2000
    Location
    New Port Richey, FL
    Posts
    620

    Default RE: Confued by Database Compact error message ...

    Thanks. I'll try it tomorrow
    Regards
    Jim

  14. #14
    Volunteer Moderator drgarytraub's Avatar
    Real Name
    Dr. Gary Traub
    Join Date
    May 2000
    Location
    Boca Raton, FL
    Posts
    2,784

    Default RE: Confued by Database Compact error message ...

    Thanks Peter ...

    Gary

  15. #15
    Volunteer Moderator Peter.Greulich's Avatar
    Real Name
    Peter Greulich
    Join Date
    Apr 2000
    Location
    Boston, MA
    Posts
    11,654

    Default RE: Confued by Database Compact error message ...

    Oops!

    vDBF_Path is a global variable I use. The script won't work unless you put this as the first line:
    [b]
    vDBF_Path = iif(:A5.Get_Master_Path()=="",:A5.Get_Path(),:A5.Get_Master_Path())

  16. #16
    Volunteer Moderator drgarytraub's Avatar
    Real Name
    Dr. Gary Traub
    Join Date
    May 2000
    Location
    Boca Raton, FL
    Posts
    2,784

    Default RE: Confued by Database Compact error message ...

    This is getting ridiculous!

    Now I have 3 tables that won't compact: progressnotes, patients, appointments.

    I need to know:

    what the heck is causing this

    and how to fix this

    Please help ....................

    Gary

  17. #17
    Volunteer Moderator drgarytraub's Avatar
    Real Name
    Dr. Gary Traub
    Join Date
    May 2000
    Location
    Boca Raton, FL
    Posts
    2,784

    Default RE: Confued by Database Compact error message ...

    Here's an update:

    I created a new directory, copied all the files from the application directory to the new directory.

    Then, created New database with the same name as my app. Then added all tables and sets to the database. This copied added all tables, sets, layouts (forms, reports, etc) but no scripts in the code tab. I guess I'd have to copy ALL 250 of them one by one, but before I did that, I ran Database Compact, and the same errors occur.

    I guess this indicated that the adb was noty the culprit, but probably the individual data dictionaries for the errant tables. Am I correct?

    What would be the next step?

    Gary

  18. #18
    Volunteer Moderator drgarytraub's Avatar
    Real Name
    Dr. Gary Traub
    Join Date
    May 2000
    Location
    Boca Raton, FL
    Posts
    2,784

    Default RE: Confued by Database Compact error message ...

    Okay, I seem to have resolved the problem. Here is what I did:

    As a matter of course, I make backups of my entire application at the end of every day. So, I went as far back as I needed to, to find a version of the app that caused no Database Compact errors.

    Once that was found, I copied my current data to that app. Ran Database Compact without any errors. Great!

    Then, I copied this working app to a safe directory, And then, added table data dictinaries of one module after another, one at a time, testing the Database Compact, aftrer adding each one. In this manner, I was able to get the app pretty much back to where it was most recently, and now have no more Database Compact errors.

    I am hoping that this problem is now over. It was not fun, to say the least. If it happens again, it will shake my confidence even further. I am left wondering what the heck caused that in the first place? Will it happen again? What will cause it? What can prevent it?

    Hope this experience can help others, and perhaps more importantly, lead to some discussion as to how to avoid this in the first place.

    Gary

  19. #19
    Volunteer Moderator Peter.Greulich's Avatar
    Real Name
    Peter Greulich
    Join Date
    Apr 2000
    Location
    Boston, MA
    Posts
    11,654

    Default RE: Confued by Database Compact error message ...

    Gary,

    Did you try to comapct the troublesome tables one at a time only (not the entire adb)?

    Peter

  20. #20
    Volunteer Moderator drgarytraub's Avatar
    Real Name
    Dr. Gary Traub
    Join Date
    May 2000
    Location
    Boca Raton, FL
    Posts
    2,784

    Default RE: Confued by Database Compact error message ...

    Well ... it happened again ... and now to 2 tables: patients and progressnotes. I will attempt to fix it again, in the way I described above. But does anyone have any ideas as to why this is happening?

    Gary

  21. #21
    Volunteer Moderator drgarytraub's Avatar
    Real Name
    Dr. Gary Traub
    Join Date
    May 2000
    Location
    Boca Raton, FL
    Posts
    2,784

    Default RE: Confued by Database Compact error message ...

    Peter,

    I must be dense, but I have not been able to get that script to work.

    Gary

  22. #22
    Volunteer Moderator drgarytraub's Avatar
    Real Name
    Dr. Gary Traub
    Join Date
    May 2000
    Location
    Boca Raton, FL
    Posts
    2,784

    Default RE: Confued by Database Compact error message ...

    Can anyone tell me why this keeps happening?

  23. #23
    Former Alpha Employee JerryBrightbill's Avatar
    Real Name
    Jerry Brightbill
    Join Date
    Apr 2000
    Posts
    5,173

    Default RE: Confued by Database Compact error message ...

    No, other than something continues to corrupt your data dictionaries or some process is leaving a table open. Perhaps you could post the database with minimal data and have someone else give it a try. Have you tried a different computer?

    Jerry

  24. #24
    Volunteer Moderator drgarytraub's Avatar
    Real Name
    Dr. Gary Traub
    Join Date
    May 2000
    Location
    Boca Raton, FL
    Posts
    2,784

    Default RE: Confued by Database Compact error message ...

    Jerry,

    The application is huge.

    I do not think any tables are open, as the problem still occurs after rebooting.

    Also, the problem occurs on 3 different machines that I have tried.

    I do think you are right that "something continues to corrupt your data dictionaries."

    I do have a feeling it as something to do with running the application on a network, because I do not recall it ever happening when I run the application as standalone, or even during development. I run Database Compact often and do not get this problem until, I believe, the application is run on a network, and then at the end of the day, I copy the data files (dbf, cdx, fpt) from the server back to my laptop. Does that lead to any ideas? Perhaps it has something to do with locking issues?

    Thanks for any other help with this.

    Gary

  25. #25
    Former Alpha Employee JerryBrightbill's Avatar
    Real Name
    Jerry Brightbill
    Join Date
    Apr 2000
    Posts
    5,173

    Default RE: Confued by Database Compact error message ...

    Gary

    If no design work is being performed, the data dictionaries shouldn't change. If the compact fails because a table is being left open, this might be harder to track.

    There are many ways to try to isolate the problem. First, can you compact on the laptop, which I assume is off network? If / when it does, save all of the ddd, ddm, ddx, set, sem, sex, alm, alb, alx files to a backup. Save no data. Then when the compact fails after copying data, start restoring each file until the compact succeeds. That should isolate the problem file if the problem is in a data dictionary. Big IF.

    If this has no effect, then the problem is a table being left open. The error should identify the table. Search for EVERY reference to this table anywhere in code. Verify that all dialog forms open and close correctly (if a process fails on a dialog form, the form may not close). Check every incidense where the table is opened and closed in code and verify that there is no way the table could be left open (bypassing a script line, ending a scripot before closing, etc) This might be quite a project.

    Another possibility is locking issues from using referential integrity in a set. This will lock the child tables when the parent is placed in edit. While I don't think this could cause a problem, most developers suggest leaving integrity off.

    There is a possibility that running on a network might have file locking issues, but I am not a network expert.

    You say the database is huge. How huge is huge. You mighht be able to email it to someone else for testing if it is less than 5 meg, which is a typical limit for email. If it is larger, you can create multiple backup files to make each one smaller. This may be a viable alternative.

    Jerry

  26. #26
    "Certified" Alphaholic
    Real Name
    JohnZaleski
    Join Date
    Oct 2000
    Posts
    1,736

    Default RE: Confued by Database Compact error message ...

    If its a network locking issue, rebooting the server and the client machine should allow compacting. Is that the case??
    Jerry said:"If no design work is being performed, the data dictionaries shouldn't change."
    That is not exactly true. There is at least one end user function - word merge with templates - that write to the dictionaries. There may be others.
    There are a number of things you can do in xbasic that write to the app dictionaries - adding dropping tables - shadow creates. Adhoc browses write to table dictionaries as well as app dictionaries. In theory when optimized you are only writing to your shadowed tables but maybe.

  27. #27
    "Certified" Alphaholic
    Real Name
    JohnZaleski
    Join Date
    Oct 2000
    Posts
    1,736

    Default RE: Confued by Database Compact error message ...

    If its a network locking issue, rebooting the server and the client machine should allow compacting. Is that the case??
    Jerry said:"If no design work is being performed, the data dictionaries shouldn't change."
    That is not exactly true. There is at least one end user function - word merge with templates - that write to the dictionaries. There may be others.
    There are a number of things you can do in xbasic that write to the app dictionaries - adding dropping tables - shadow creates. Adhoc browses write to table dictionaries as well as app dictionaries. In theory when optimized you are only writing to your shadowed tables but maybe.

  28. #28
    "Certified" Alphaholic
    Real Name
    JohnZaleski
    Join Date
    Oct 2000
    Posts
    1,736

    Default RE: Confued by Database Compact error message ...

    If its a network locking issue, rebooting the server and the client machine should allow compacting. Is that the case??
    Jerry said:"If no design work is being performed, the data dictionaries shouldn't change."
    That is not exactly true. There is at least one end user function - word merge with templates - that write to the dictionaries. There may be others.
    There are a number of things you can do in xbasic that write to the app dictionaries - adding dropping tables - shadow creates. Adhoc browses write to table dictionaries as well as app dictionaries. In theory when optimized you are only writing to your shadowed tables but maybe.

  29. #29
    "Certified" Alphaholic
    Real Name
    JohnZaleski
    Join Date
    Oct 2000
    Posts
    1,736

    Default RE: Confued by Database Compact error message ...

    If its a network locking issue, rebooting the server and the client machine should allow compacting. Is that the case??
    Jerry said:"If no design work is being performed, the data dictionaries shouldn't change."
    That is not exactly true. There is at least one end user function - word merge with templates - that write to the dictionaries. There may be others.
    There are a number of things you can do in xbasic that write to the app dictionaries - adding dropping tables - shadow creates. Adhoc browses write to table dictionaries as well as app dictionaries. In theory when optimized you are only writing to your shadowed tables but maybe.

  30. #30
    "Certified" Alphaholic
    Real Name
    JohnZaleski
    Join Date
    Oct 2000
    Posts
    1,736

    Default RE: Confued by Database Compact error message ...

    If anyone resonds to this, you don't have to do it four times. Key was stuck - voila.

Similar Threads

  1. Compact Database Error
    By WesOlfert in forum Alpha Five Version 5
    Replies: 4
    Last Post: 01-01-2005, 07:50 PM
  2. Expected Value error message on opening database
    By Bill Rogers in forum Alpha Five Version 5
    Replies: 1
    Last Post: 03-07-2004, 10:44 PM
  3. Database Compact Error
    By Walter in forum Alpha Five Version 5
    Replies: 8
    Last Post: 11-13-2003, 04:27 AM
  4. database compact error
    By WFaber in forum Alpha Five Version 5
    Replies: 7
    Last Post: 01-07-2003, 09:11 AM
  5. pRINTER DATABASE ERROR MESSAGE
    By Rokrz in forum Alpha Four Version 6 and Prior
    Replies: 2
    Last Post: 06-28-2000, 11:00 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •