Paradox Database and Index Corruption using BDE
Spencer Morley
We are having severe database and index corruption problems. This is
occurring at a number of our customer's sites. We hope someone with
much more Paradox and BDE experience than we have will be able to
help us reduce the incident rate of data corruption. Also, I hope to
help others having similar problems.
SOME BACKGROUND:
Our main database is Paradox with a record size of 1-2K with a
primary unique index on an Auto Incrementing field. It also has 6
secondary indexes with at most 3 fields in each. We used Paradox
over DBase due to the faster indexes and Auto Inc. feature. We are
using only flat files. We also access 11 other Paradox files with
similar primary indexes. These each have about 2 secondary indexes
of similar structure.
The main database (request) keeps track of a request's base
information. We save various supporting data in other small
databases with names based on the Auto Inc number in numbered
subdirectories. Example: Request number 334 would have supporting
information in files: .\300\TR334.db, .\300\CL334.db,
.\300\EM334.db. Request 5123 would have similar files in:
.\5100\TR5123.db, etc. This was done this way due to directory
search times. The reason I wanted to explain this is that the system
opens on average 15 more databases when processing a request. That
means we may have 27+ databases open at a time by one module.
Our system consists of 8-10 modules running on a single NT 4.0
workstations. Two module has the 27+ databases open. The other
module only open the main database and 1-2 others. This brings the
system database load to 70 databases open. Most installations have
20-50 workstations that are opening the main database and 3-5 of the
others. When operating the main system modules are checking the
database for request to process approximately every 5 seconds.
All this equates to a lot of file and network access. All this works
well in our office. However, we have had way too many problems in the
field. We see the "Index Out Of Date" most often. Problems seem to
increase as the number of records increases past 20-30K.
Over the past few months, 70% or more of our total development time
has be toward solving this problem. First we built some simple
re-indexing utilities. Then we added TUTIL32.DLL to our arsenal (See
Borland's web pages). Then we added the TTUtillity component by "Out
& About Productions" http://www.o2a.com to ease the use of the
TUTIL32.DLL. It does a great job. Because of our use of Auto Inc.
fields, we could not rebuild the database. We implemented a plan to
save all the main database's fields (the one that dies the most) to
an INI file every time we post. We save this information in the
supporting directories. Then we built a utility that would read all
this information and reconstruct the database from scratch. This
data is the basis of our customers businesses, we had to make sure it
is safe! We have wrapped all this into one utility (sorry it is tied
to our data structure or we would publish it).
The "Index Out Of Date" was the hardest to figure out how to fix.
Most of this work was done with little or no documentation of the
Paradox structure and no all-encompassing tools. We did however find
that the "Sync Byte" that links the primary index and the .DB file
was to blame. We located that byte in our database and have been
able to correct this error so we can then use the dbiRegenIndexs
function. The problem is this, you must open the table to re-index,
however, if the index is out of date you can not open the table! How
do we fix the error? Here are the steps:
Backup the file and indexes
Delete all the indexes
Create a blank database and indexes in another directory
Could be deployed into a separate directory
Move in the blank .PX
Poke the database's "Sync Byte" into the .PX
Open the database
Re-Index using the dbiRegenIndexes
This is much faster than the TUTIL32.DLL's rebuild. For example, a
rebuild of 2 hours verses a re-index in 15 minutes. See code sample
below.
A "Key Violation" on the primary index was also a bummer. Since this
is an auto inc. field, we should not be able to get a duplicate in
the key. However, if the auto inc. seed was corrupted, this will
happen on the next post of a new record. We needed to change invalid
auto inc. seeds, something neither TUTIL.DLL nor the BDE provide.
This is similar to the code for fixing the "Sync Byte" below,
however, the offset in the database is 74th byte and is a long
integer. Note the number of records is at the 7th byte and is a long
integer. We prompt the user with both pieces of information and let
the then enter a number to be poked back into the database.
Now we can fix just about anything. KNOCK ON WOOD!
With our customer's data is "safe" and repairable, we are working on
plans to move away from Paradox files. However, we would like to
know we are doing all that we con do to keep the current user up and
running while we prepare a fully converted set of programs. Moving
to DBase or any other file format makes us update all the modules at
once. We would like to do incremental upgrades or no upgrades at
all. Our worry is that we will convert to a DBase file format and
see similar problems since we are still using the BDE in the same
environment.
Most of our customers have moved to NT 4.0 Server and NT 4.0
Workstations. However, some are using Novell NetWare version 3.x and
4.11. A few are using NT 4.0 Workstation as a "server" hosting the
data and running the modules. We have abandoned the use of Windows
95 as a "server" and quickly eliminating it as a workstation.
We have moved to have LOCALSHARE set to TRUE on all workstations. I
found some information on Corel's news groups:
1) Make sure that you have installed the latest patch for Paradox 7.
2) On the Win95 machines - disable the windows locking semantics.
(My Computer - Properties,File
System,Performance,Troubleshooting..)
3) Modify Registry on NT4 server to disable oplocks.
\\HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\
Services\LanManServer\Parameters\
Add to DWORD values:
EnableOplocks 0x0000000(0)
CachedOpenLimit 0x00000000(0) )
4) Another database software package suggested adding:
UseOpportunisticLocking 0x00000000(0)
UseLockReadUnlock 0x00000000(0)
#1 above would equate to getting the must recent BDE.
#2 who knows
#3 what is the impact to NT 4.0 Server. Also when added to an NT 4.0
workstation the Event Viewer says the parameter was ignored!
#4 again what is the impact on NT 4.0 Server. Is EnableOplocks the
same as UseOpportunisticLocking?
Will these settings help? Are there other settings for NetWare?
Would the same settings be necessary if using DBase or FoxPro format
and the BDE?
I am assuming the problems we are experiencing are environmental
since the BDE is widely used. We have looked at BDE replacements
such as Apollo. In fact this product was great for our 16 bit dialup
client product because it reduced the install to one diskette. The
main disadvantage is its lack of Paradox file support. Again forcing
use to do a complete overhaul and update. (DBase and FoxPro have 10
character name length limits and no auto inc. field type!)
CODE SAMPLE:
{ Most error checking, etc. has been removed to make more readable }
function FixDBIndexOutOfDate(pFileName: string): boolean;
const
{ Apparently, the .px index has a checksum (position 45) }
{ that needs to be in sync with the .db file (position 46) }
{ Setting this position will allow us to bypass the }
{ Index Out of Date error, so we can open the file and Reindex!! }
dbOffset = 45; {position 46}
pxOffset = 44; {position 45}
var
FileHandle: integer;
SyncByte: byte;
begin
Result := False;
FileHandle := FileOpen(pFileName,
fmOpenRead or fmShareDenyNone);
if (FileHandle > 0) then
begin
{Read the value from the .db file}
FileSeek(FileHandle, dbOffset, 0);
FileRead(FileHandle, SyncByte, 1);
FileClose(FileHandle);
FileHandle := FileOpen(ChangeFileExt(pFileName, '.px'),
fmOpenReadWrite or fmShareExclusive);
if (FileHandle > 0) then
begin
{Write the value to the .px file}
FileSeek(FileHandle, pxOffset, 0);
FileWrite(FileHandle, SyncByte, 1) ;
FileClose(FileHandle);
Result := True;
end;
end;
end;
--
(Remove nospam to email)
Sean McCall
Spencer Morley wrote:
>The problem is this, you must open the table to re-index,
> however, if the index is out of date you can not open the table! How
> do we fix the error?
Hi,
Just delete the indices before opening the table. The files used by a
paradox table are:
PTABLE.DB - Data
PTABLE.MB - Memos
PTABLE.VAL - Required Fields
PTABLE.PX - Primary Index (Delete This)
PTABLE.X?? - Secondary Index (Delete This)
PTABLE.Y?? - Secondary Index (Delete This)
Hope this helps,
Sean
P.S. If there is just one Windows 95 machine on the entire network with
a bad VREDIR.VXD, you will eventually get index corruption if that
machine is accessing the data.
Spencer Morley
Sean,
Thanks for the reply. These groups have been very helpful!
>Sean wrote:
> Just delete the indices before opening the table. The files used by
a
> paradox table are:
>
> PTABLE.DB - Data
> PTABLE.MB - Memos
> PTABLE.VAL - Required Fields
> PTABLE.PX - Primary Index (Delete This)
> PTABLE.X?? - Secondary Index (Delete This)
> PTABLE.Y?? - Secondary Index (Delete This)
We have tried this. The problem we have found is that you can not
create a primary key once the table is created. (At least one based
on an Auto Inc. field). We may have been going about the wrong,
however, the methods we tried worked for DBase files. I think we
used Table.AddIndex() and/or Table.IndexDefs.Add(). We may have used
SQL also since that is how we build the table in the first place.
dbiRegenIndexs must know the structure to rebuild the index. Paradox
index structure seems to be contained in the .PX or .X?? files so the
.DB alone does not "know" the structure. You must create the
indexes. But you can't. (You can create the .X??'s) So you move an
already created .PX into place and bang! "Index Out Of Date". Thus
the procedure we came up with.
If there is a way to create a .PX after delete all the indexs, we
would be greatful for a sample.
>Sean wrote:
> P.S. If there is just one Windows 95 machine on the entire network
with
> a bad VREDIR.VXD, you will eventually get index corruption if that
> machine is accessing the data.
We will be posting an alert too our customers pertaining to this
issue.
FYI: http://www.borland.com/devsupport/sqllinks/pub-90.html
Sean McCall
Spencer Morley wrote:
>
> We have tried this. The problem we have found is that you can not
> create a primary key once the table is created. (At least one based
> on an Auto Inc. field). We may have been going about the wrong,
> however, the methods we tried worked for DBase files. I think we
> used Table.AddIndex() and/or Table.IndexDefs.Add(). We may have used
> SQL also since that is how we build the table in the first place.
>
>
> If there is a way to create a .PX after delete all the indexs
>
I never use auto incrementing fields, bit I always use the
TTable.AddIndex method to add an index with the option [ixPrimary,
ixUnique]. If this doesn't work with an autoincrement field, you can try
using local SQL to add the index. Sorry, that's the best I can do.
Hope this helps,
Sean
Marius Popma
My experience with auto increment fields is bad.
Once i had a paradox table with an auto-inc. field with a primary index
on that field. After approx. 2000 records the field just stopped
to auto increment..... Causing a corrupt index of course.
Could not solve the problem in any way. Repaired the table
but the problem came back again and again.
So i always maintain my own increment fields in code.
Make the field non-auto increment, in code go to the last field,
remember the number (X), append a record,
Field number := Inc(X)
This way the problem should not occur.
HTH
Marius Popma
gsb
Spencer Morley schrieb:
Hi Spencer,
we have also the same problem with our Programm using on a WIN3.11(and
Novell Server) and a WIN95 Client (and a Novell Server).We have help us
delete all the Indexes of the corrupt table.If the Block size of the *.DB
file is corrupt we change the
block size in the DB-header and then append then Records with then
Database-Desktop
into new tables with all indices (with no data).Then we can use all data.
But several day's after we do that the same problem come.
If you have new information sent to us.
Günther Horn
email : g...@emsnet.de
Scott Frolich [Borland]
There are problems with some versions of Win95 and BDE. Look at:
http://www.borland.com/devsupport/sqllinks/pub-90.html
Scott
--
BDE Support:
http://www.borland.com/devsupport/bde
Delphi Support:
http://www.borland.com/devsupport/delphi
Common Delphi and BDE Questions and Answers:
http://www.borland.com/devsupport/delphi/qanda/delphi3qa.html
Spencer Morley
Scott Frolich [Borland] wrote:
>Hello,
> There are problems with some versions of Win95 and BDE. Look at:
>http://www.borland.com/devsupport/sqllinks/pub-90.html
>
> Scott
Yep, found that too.
I developed a Wise setup "Network Validation" to change various
registry settings (see original message) in NT Workstation, NT Server,
and 95 and install the VREDIR update for 95. Also, I have resorted to
renaming the IDAPI32.CFG and replacing it with a version with
LOCALSHARE=TURE. (This was necessary since the setup would not change
the parameter once the BDE was installed. I don't think a merge would
either.)
I have installed this on some of our largest clients. Note, they are
the ones that bought the big servers with NT Server, memory to the
max, some 100 Base T networks, etc. Best hardware and still having
problems. Even after the Network Validation setup was run on *all*
workstation and the server, they are having problems with "Index Out
Of Date" errors.
Most of their workstation are dedicated to our software and we can
dictate any settings.
Since we see the problems in the NT Server environment the most, we
designed testing applications to pound records into a database with
the same structure. Also, it will randomly edit records based on a
timer. I have run 80000+ records into the database and TUTIL32.DLL
will verify it as ok. (Add records only, LOCALSHARE=FALSE, Default NT
Server & Workstation settings for LanmanServer and LanmanWorkstation
registry keys) If we could break it in house, we have ample smarts to
fix it! We will continue to test nightly until we can break the
database. However, our customers need our help!
We will also drop the Auto Incrementing fields someone can say "Yep,
it is worth the time to do it." Even switching to a different data
structure (dBase, etc) would be a solution if we can justify it. At
this point, we are faced with too many unknowns.
Anything I can do to help them would be appreciated. If you have a
"Do these things to get the best data integrity possible in a NT
Server & Workstation environment" document or a "These thing *may*
help reduce your data corruption". At this point I am just looking
for the biggest shotgun!
We have lots of clients that are running fine. Some are using NT
Workstation to host the data, some are on Novell. If we had a severe
programming problem, we would see problems across the board.
If you do not have anything for me, do you have anything in the works?
Note, if this problem is beyond the newsgroups scope, we will gladly
pay for development level help.
Thanks in advance
Spencer
Peter
That's kind o' funny (or maybee it's not), but we have the same problem!
We discovered that memory-resident virusguards that check files over the
network
REALLY slow things down.
Also, running DOS-based programs on a win95 or NT machine will decrease
effiecency
a lot.
The famous "Index out of date" was brought to an end (or so we thought) by
keeping
mantained indexes , but we've still been seeing that darn index go out of
date.
Not when running a one-user-version locally, but when 20 people use the
same
table over the local network.
Spencer Morley
I hate to reply to my own message, however, we have found some
solutions and we would like to share them with the group and with the
Borland Team. Hopefully this will help both the group and Borland
diagnose what is going on.
**********
The conclusion is to *use only one protocol*, LOCALSHARE=TRUE, and
apply the VREDIR update to Windows 95.
**********
We had been testing continuously since the original post. Our tests
used:
* Data on an NT 4.0 Server with NO Delphi/BDE software running on it.
* Up to 4 NT 4.0 Workstations adding data into the main database.
* One Window 95 Workstation adding data into the main database.
* One NT 4.0 Workstation processing the data in the main database and
supporting databases. Normally with 12 separate Delphi/BDE programs
running.
* Each test was ran multiple times and time to crash varied from 5
minutes to 5 hours!
TEST 1:
We set out to break the database in our office so we could then set
out to correct the problem.
4 NT stations with LOCALSHARE=FALSE
RESULT:
Seemed like we could not break the data. However, we did get a
exception message:
Title: System process - Lost Destroyed-Write Data
Msg: The system was attempting to transfer file data from buffers to
\J:\nt_server\d\synsoft\data\2130\TR21318.db. The write operation
failed and only some of the data may have been written to file.
This would affect the name database but not the main database
(REQUEST.DB).
TEST 2:
We set out to break the database in our office so we could then set
out to correct the problem.
1 NT stations with LOCALSHARE=FALSE
1 Windows 95 stations with LOCALSHARE=FALSE
RESULT:
Windows 95 station would get an exception:
Title: Program Name
Msg: Lock Timeout
This would kill the main database. This error would come up very fast
compared to NT only environment.
Also, same as TEST 1.
** Try to alleviate the problems discovered in 1 & 2 **
TEST 3:
Same as TEST 1 with all stations having LOCALSHARE=TRUE
RESULT:
Same results as TEST 1!
TEST 4:
Same as TEST 2 with all stations having LOCALSHARE=TRUE and VREDIR.VXD
update.
RESULT:
Same results as TEST 2!
TEST 5:
Contacted 3 Microsoft Engineers, friends, old high school buddies,
etc. One MSE recommended removing all but one protocols. We were
using TCP/IP, NetBUEI, and IPX/SPX. We removed all but the IP from
the NT Server and all workstations in the test. Also, the
Client/Serve for NetWare was removed form 95 and NT. Since 95 would
cause the error the quickest we ran like test 2:
One 95 station with LOCALSHARE=FALSE and no VREDIR update
One NT station with LOCALSHARE=FALSE.
RESULT:
Same results as TEST 2!
TEST 6:
Same as TEST 5 with LOCALSHARE=TRUE and 95 VREDIR update applied.
RESULT:
~50,000 records (10+ Hours) added with no errors or corruption. This
was ~2GB of main and supporting databases. As far as our preliminary
results show, this is a stable environment. We could not get to these
numbers in the other tests.
TEST 7:
Only TCP/IP protocol.
Same as TEST 6 with:
2 NT stations adding
1 Windows 95 adding
RESULT:
As far as our preliminary results show, this is a stable environment,
like TEST 6.
---------
We are going to our customers with these results in hope to cure their
problems. We have not tested an IPX only or NetBUEI only environment.
Since, like us, our customers have both NT and NetWare, we will have
to address IPX protocol or using IP with NetWare.
Hope this helps,
Spencer Morley
Sean McCall
Spencer Morley wrote:
>
> I hate to reply to my own message, however, we have found some
> solutions and we would like to share them with the group and with the
> Borland Team.
>
Thank you for sharing. This will be very helpful for all our network
users.
Sean
Markku Nevalainen
Spencer Morley wrote:
>
> I hate to reply to my own message, however, we have found some
> solutions and we would like to share them with the group and with the
> diagnose what is going on.
>
I see you had to spend a lot of time for testings with BDE, thanks for the given
info. I would like to know, what was the idea of doing three of those seven
tests with setting LOCALSHARE=FALSE?
Is there somewhere, some kind of advice, that trying with that setting in multi user
environment you _possibly_ could get some better results. More stability or something?
I have always heard and thought that this setting *must* be TRUE in network
environment, and there are no exceptions, no matter if you have Win3.1/95/NT
workstations.
The results shortly, of your creditable test could be:
1. Always use LOCALSHARE=TRUE
2. With W95, try to get the right VREDIR version
3. Try taking the extra protocols away, run with only one protocol (TCP/IP)
There has been some discussions about setting OpportunisticLocking=OFF
on NT4 stations. I'm not sure what settings you did have during your tets?
Also, is there any possibility for human errors, or maybe some new settings
stayed only somewhere in W95 buffers. And maybe not being valid between your
various LocalShare settings, LOCALSHARE-->FALSE -->TRUE --> FALSE-->TRUE ??
Markku Nevalainen
Spencer Morley
Markku wrote:
>I would like to know, what was the idea of doing three of those seven
>tests with setting LOCALSHARE=FALSE?
The idea was to create a problem in our development lab. We had not
seen the problem at our sight.
Our goal was to duplicate our customers error then fix it!
>Is there somewhere, some kind of advice, that trying with that
setting in multi user
>environment you _possibly_ could get some better results. More
stability or something?
>I have always heard and thought that this setting *must* be TRUE in
network
>environment, and there are no exceptions, no matter if you have
Win3.1/95/NT
>workstations.
Yes you are correct. NT Server is treated as a Peer-to-Peer
connection so LOCALSHARE *must* be TRUE.
See my response to:
'LOCAL SHARE = TRUE' only on File Server?
and Scott's reponse from Borland.
>The results shortly, of your creditable test could be:
> 1. Always use LOCALSHARE=TRUE
> 2. With W95, try to get the right VREDIR version
> 3. Try taking the extra protocols away, run with only one protocol
(TCP/IP)
That is my suggestion in a nutshell.
>There has been some discussions about setting
OpportunisticLocking=OFF
>on NT4 stations. I'm not sure what settings you did have during your
tets?
I tested with LOCALSHARE=TRUE and:
Windows 95:
Set registry setting:
\\HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\FileSystem\SoftC
ompatMode=hex:00,00,00,00
This setting corresponds to "Control Panel | System | Performance |
File System | Troubleshooting | Disable New File Sharing And Locking
Semantics: Checked"
Windows NT Server and Workstation:
Set registry values in
\\HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\LanmanWorkstati
on\Parameters key:
UseLockReadUnlock=0
UseOpportunisticLocking=0
EnableOplocks=0
CachedOpenLimit=0
Set registry values in
\\HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\LanmanServer\Pa
rameters key:
UseLockReadUnlock=0
UseOpportunisticLocking=0
EnableOplocks=0
CachedOpenLimit=0
This should have disabled OpLocks. The problems persisted! These
settings were removed for other tests including the final stable
environment.
The idea was to find the minimal set of changes required to produce a
stable environment. Disabling OpLocks would be a major system
proformance hit so we were glad when it had no positive or negitive
impact.
>Also, is there any possibility for human errors, or maybe some new
settings
>stayed only somewhere in W95 buffers. And maybe not being valid
between your
>various LocalShare settings, LOCALSHARE-->FALSE -->TRUE -->
FALSE-->TRUE ??
Since the problems were very ellusive and sometime required many hours
to arise, we were very careful to initialize the testing environment
for each test. Also, we would verify the settings in the registry and
BDE configuration before each test. Although the test may not be
exhaustive, we are very confident we have found a serious glitch.
If anyone at Borland would like to discuss this further, please
contact me at:
Spencer Morley, Software Engineer
Synergistic Software Systems, Inc.
521 W. Maxwell
Spokane, WA 99201
509-324-1250
spen...@xact.com.nospam
---
(remove .nospam to email)