In 2010 we released a new version of one of our products which was designed to support 2 different SQL engines, Microsoft SQL Server 2005/2008 for our larger clients and an embedded SQL engine for our smaller clients. For the later we chose ElevateDB.

While in development things went smoothly, since release however we've encountered serious issues that have only afflicted those using the embedded database option, i.e. ElevateDB. In certain cases it looks like tables are getting wiped of all data, possibly the table files themselves are getting deleted as the files show up as 1k in size in Windows Explorer. Quite often the .old files are larger and when possible reverting to those files has restored the data. Typically only a single table is affected, however the other day a client had numerous files affected.

We have also experienced a number of instances where tables are suddenly become corrupt, typically the repair function doesn't handle it and just reports the same error we get when attempting to open the table. In the past this corruption has become apparent after the database structure has been updated (using the applications automated update code) however it can apparently occur anytime, e.g. things had been working fine the day before, but as soon as they log into the system the next day something has gone wrong. Again reverting to the .old version has fixed this.

At this point it has become a major concern as clients have lost data, though at this point we've been able to limit the damage. Given that only those using ElevateDB have been affected by this problem and the code accessing either database engine is mostly the dame (the differences are largely limited to SQL differences, e.g.date formatting, and the low level access) we can only conclude it has something to do with the ElevateDB rather than issues with our code.

In trying to resolve this we have noted other ElevateDB users mention that at least some anti-virus software can cause interference with ElevateDB's normal operation. We are wondering whether anyone can shed any further light on this problem?

Note development is with Delphi7 using the Delphi ElevateDB components. We started with version 2.02 b6 and are currently using 2.03 build 21.


Aaron Pearse

Hi

i'm using EDB 2 from years , 3500 installations, never had any particular
problems.


I was start with Delphi 2007 , EDB 2.00

1 year of ..EDB 2.03x Delphi 2010...

now... Delphi XE with EDB 2.05B2


never a problem of file corrupt. EDB work fine in this area.

if there are any problems... you should search in Antivirus.. Hw failure ,
other...
KasperSky, Avira... are terrible. NOD 32 is very good , AVG is Good.
( i have call center with 6 men for 3500 clients )

before of EDB 2 i was using DBISAM 4 , with good result for Database
integrity problems ,
but EDB is better.

<< In trying to resolve this we have noted other ElevateDB users mention that at least some anti-virus software can cause interference with ElevateDB's normal operation. We are wondering whether anyone can shed any further light on this problem? >>

One organisation I do work for has a large Microsoft SQL Server installation and the same thing has happened there.  Unbeknownst to us, Infrastructure staff set up virus scanning on the database servers and it brought all of our database systems to their knees.  Timeouts were occurring throughout the organisation and errors were popping up in all applications.  Once the AV software was removed everything returned to normal.

Regards,

SteveG

Jason


First and foremost if you have support contract email Tim on this directly.

Secondly you mention .old files.

From the manual

ElevateDB simply appends the ".Old" to the existing file when creating backup copies during the optimization, alteration, or repair of tables.

So have these been created as part of a table restructure or optimisation carried out under program control? If not who's been messing around? If so something has prevented the action taking place.

The fact that there are empty files there can be a bit of a red herring. ElevateDB when it attempts to open a table will, if they don't already exist, create the necessary disk files. Have a look in the session log (you'll need EDBManager) for events like

Warning - Open Table - The table file E:\HH Dev\TfR\UAC.EDBTbl was not present on disk and was automatically created

If you find them I strongly suspect antivirus to be the culprit.

Roy Lambert [Team Elevate]

Roy Lambert wrote:

Jason


First and foremost if you have support contract email Tim on this directly.

Secondly you mention .old files.

From the manual

ElevateDB simply appends the ".Old" to the existing file when creating backup copies during the optimization, alteration, or repair of tables.

So have these been created as part of a table restructure or optimisation carried out under program control? If not who's been messing around? If so something has prevented the action taking place.

The fact that there are empty files there can be a bit of a red herring. ElevateDB when it attempts to open a table will, if they don't already exist, create the necessary disk files. Have a look in the session log (you'll need EDBManager) for events like

Warning - Open Table - The table file E:\HH Dev\TfR\UAC.EDBTbl was not present on disk and was automatically created

If you find them I strongly suspect antivirus to be the culprit.

Roy Lambert [Team Elevate]


We did as you suggested and looked at the event log of a clients database and it did indeed have a warning entry indicating the table that we later found empty had not been found on disk and was recreated. There was also some other error entries from days earlier indicating file access was denied on a different table.

If this is indicative of anti-virus software deleting or otherwise obstructing access to database files, what options do we have in remedying this issue?

Obviously we could inform our clients to configure their anti-virus software so that it doesn't scan the database, however my manager considers that as a last resort because many of our clients will either dislike having to do so and/or don't have the IT know how to do so. Or even be able to do so as I think I saw one client using a free anti-virus software with little in configuration options. As such he would prefer an option that we could implement and therefore avoid any customer backlash.


Aaron

Jason

>We did as you suggested and looked at the event log of a clients database and it did indeed have a warning entry indicating the table that we later found empty had not been found on disk and was recreated. There was also some other error entries from days earlier indicating file access was denied on a different table.
>
>If this is indicative of anti-virus software deleting or otherwise obstructing access to database files, what options do we have in remedying this issue?
>
>Obviously we could inform our clients to configure their anti-virus software so that it doesn't scan the database, however my manager considers that as a last resort because many of our clients will either dislike having to do so and/or don't have the IT know how to do so. Or even be able to do so as I think I saw one client using a free anti-virus software with little in configuration options. As such he would prefer an option that we could implement and therefore avoid any customer backlash.

Tell your manager not to be an idiot (politely of course). Even then big boys (eg even MS) can suffer from it; if it was realistically possible for Tim to prevent this he would have done by now. Some of the AV software seems to cause this problem more than others (eg Norton) and its only some versions / updates that do it on some PC's / configurations.

Does your software carry out alteration, repair or optimisation of tables? If not I have no idea where the .old files are coming from. If so then you might be able to do something by storing file size / row count information prior to the operation, checking afterwards and at least flagging a warning if there's a mismatch. You could also require a backup prior to the operation and if the problem does occur restore from it.

Getting the root cause sorted (probably the av software) is preferable to any number of sticking plasters.

Roy Lambert [Team Elevate]

Aaron,

<< While in development things went smoothly, since release however we've
encountered serious issues that have only afflicted those using the embedded
database option, i.e. ElevateDB. In certain cases it looks like tables are
getting wiped of all data, possibly the table files themselves are getting
deleted as the files show up as 1k in size in Windows Explorer. Quite often
the .old files are larger and when possible reverting to those files has
restored the data. Typically only a single table is affected, however the
other day a client had numerous files affected. >>

That sounds like an improper shutdown is corrupting the Windows file system
blocks, including the ElevateDB table files.   If you send me a copy of one
of these databases, I can confirm whether this is the case.  Basically the
file will have "junk" in it like HTML/DLL/EXE files, and will be completely
trashed.  This is especially common if the database is stored on a local
hard drive and the file system is using FAT instead of NTFS.  The solution
to this is to make sure to use transactions or to use the
TEDBTable/Query/Script/StoredProc.FlushBuffers method occasionally to force
Windows to flush modified data from the file system to disk.

<< We have also experienced a number of instances where tables are suddenly
become corrupt, typically the repair function doesn't handle it and just
reports the same error we get when attempting to open the table. In the past
this corruption has become apparent after the database structure has been
updated (using the applications automated update code) however it can
apparently occur anytime, e.g. things had been working fine the day before,
but as soon as they log into the system the next day something has gone
wrong. Again reverting to the .old version has fixed this. >>

There were some ALTER TABLE issues in 2.03 that may cause some problems, but
the latest 2.03 B23 should take care of them.  Of course, I'm just guessing
here, because you aren't indicating what error codes/messages you're seeing.
If you look at the logged events for EDB using the session node of the EDB
Manager (View Logged Events task link), you should see the errors and the
corresponding error messages.

<< At this point it has become a major concern as clients have lost data,
though at this point we've been able to limit the damage.>>

Any time that you have an issue like this, you should make sure to contact
us directly via email at support@elevatesoft.com, or via phone.  You'll have
to have an active support plan, but I'll be able to help you a lot faster.
The newsgroups are primarily peer-supported, but I'll pop in here every few
days.

--
Tim Young
Elevate Software
www.elevatesoft.com

Aaron,

<< We did as you suggested and looked at the event log of a clients database
and it did indeed have a warning entry indicating the table that we later
found empty had not been found on disk and was recreated. There was also
some other error entries from days earlier indicating file access was denied
on a different table. >>

An "access denied" error message could be normal, but a "file not found,
recreated" error message is a good indication that AV software is mucking
about with the table files, especially if the table affected is a normal
table that should be present and populated with data.

<< If this is indicative of anti-virus software deleting or otherwise
obstructing access to database files, what options do we have in remedying
this issue?  >>

If the AV software is the culprit, then the only way to deal with this is to
stop the AV software from interfering with the operation of EDB.

--
Tim Young
Elevate Software
www.elevatesoft.com