Thanks all,

I will start with changing the protocol to TCP.
The path to the DB is surely correct and no more than one connection
uses it. I see this by dumping the ADO connection string to the log,
from where I took the example.
Selects are not so huge, as records are processed by first letter, so
thay can be as large as 200K rows. Then the app identifies
problematic records (wrong formatted or duplicated data, for
instance) and presents these records for editing by human. Yes, they
sometimes need to manually edit hundreds of records, but this is the
legacy from their old application.
Once the "letter" processing is over, the application re-selects the
records from the temp data (usually less than the original number),
generates in memory, for each record some additional data and inserts
into the 4 tables I mentioned. There is one big SELECT, then
itteration thru the records and inserts into the new tables.

I've been running tests several times on a new, test DB since the
crash occurs and cannot reproduce the situation. I also see that the
temp files are being deleted properly.

Now, to a possible reason for the crash...
We've found today that near the time of the crash, the anti-virus
program, AVG, performed automatic update, downloaded files to the
same Windows TEMP directory, installed them and restarted itself.
I've already contacted them for details about this operation. We will
also try to simulate it, to see if this can be the source of the
problem.

Now, another question, related to the big selects, discussed here.
Supposed I know that the select may return high number of records
(thousands and more). How can I limit the number of records returned?
Get them by predfined blocks? Is there something similar to the LIMIT
clause (as in MySQL?). I've been looking in the Interbase
documentation, and seems the only answer is to use CURSOR. Or am I
wrong? Sorry, but as I said, I'm newbie to FB/IB.

--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@t...>
wrote:

> At 11:43 PM 13/03/2005 +0000, you wrote:
>
>
> >I hope this provides the missing details:
> >
> >- FB is v1.5.1. Superserver
> >- ODBC is v1.2.0.68
> >- The PC has 1Gb RAM, 40 GB Disk space, of which 28 free
> >
> >Here is the connection string:
> >
> >DRIVER=Firebird/InterBase(r) driver;
> >DBNAME=D:\DataCenter\DB\Data\DCDB.FDB; UID=GWC; PWD=******; ROLE=SU
> >
> >For isql:
> >
> >connect "D:\DataCenter\DB\Data\DCDB.FDB" user 'gwc'

password '******';

> >
> >The path is stored in registry in the following
> >format "..\..\DCDB.FDB", if on the same drive. If on another the

full

> >path is stored. The application transforms always to full path,

incl.

> >drive, as you can see above.
>
> 1) When you say "the application transforms...", how does it do

this? i.e.

> does it do it by calls to Windows API? During debug, are you

*certain*

> that the path that is presented to the Firebird API is exactly the
> absolute, hard path?
>
> 2) Presumably your application installer writes this Registry key.

Why is

> it that you can't write the hard path into this key, IN EVERY CASE?
>
> 3) How do you check EXACTLY what the connection path is?
>
>
> >All I have in firebird.log is:
> >
> >JOYBRINGER (Client) Thu Mar 10 22:31:53 2005
> > Guardian starting: D:\Program
> >Files\DataCenter\DB\bin\fbserver.exe
>
> Where (exactly) are isql and your custom data viewer located?
>
> Is your application starting its own instances of Guardian and

fbserver, as

> applications, while a Firebird server is running somewhere else on

the

> system as a service ?
>
> >
> >I guess that the sort files are from the selects from the "temp"
> >table. It is not a real temp table, but a table where the raw data
> >from the DB2 is initally inserted. Then a human checks and modifies
> >some fields, which are again saved in this "temp" table.
> >Next, the application generates some additional data (like hash
> >values, automatic classification, etc.) - sorry, I cannot reveal

all

> >details here :(.
> >This is done in memory. The modified data is inserted - single

record

> >each time, updating all four tables (4 or more INSERT statements)

and

> >commiting the transaction. Yes, commit after each row in the main
> >table. Two other tables may have more than one INSERT. ID's as PK's
> >are generated BEFORE INSERT from two generators for the main and
> >hashes tables.
>
> Relating this back to earlier information...you are iteratively
> 1) starting a transaction
> 2) selecting gigabytes of source records from the temp table
> 3) reading one source record
> 4) letting the user do some stuff with that source record and

writing it

> back to the temp table (as yet uncommitted)
> 5) meanwhile,
> a) in memory, massaging some data from the current source record
> b) from the application, constructing a series of INSERT

statements

> populated by data from these memory structures
> 6) logging something to a log table?
> 7) finally, committing the whole transaction
>
> Correct ?
>
> If this is not the sequence of events, please try to modify this to
> describe what the application is really doing from start to finish

of a

> transaction.
>
> Also, comment on what is the purpose of Step 4, above?
>
> >Indices: 1 on varchar(20) and 1 on varchar(250) + 3 on int. Charset
> >is NONE.
>
> Are these indices in the source table or the target tables?
>
>
> >I am afraid that I cannot paste here the exact INSERT statements,

but

> >they are really simple. The data is passed verbatim, not from

another

> >select. Example:
> > INSERT INTO TABLE1 VALUES ('','blah','blah','5');
> >Only VARCHAR, CHAR and INT fields are used.
> >
> >The logs I mention are mine, but they will contain any ODBC error,

as

> >returned by the driver. As I wrote, there was no such error.
>
> The server returns exceptions to the driver via the API. Data

integrity

> exceptions (violation of constraints, type mismatches,
> overflows, conflicts, etc.) would come back on posting the new

record,

> i.e. when the request causes a new record to be written to disk.

This

> record is known only to the transaction that writes it.

Communication

> exceptions would come back on any request, as long as the client
> application remains able to post requests and get something back.

If the

> application should crash, or the client connection should crash,

your ODBC

> log will have only the last known communication. The Firebird log,

OTOH,

> may show evidence of a connection that was lost, provided the

server itself

> wasn't in a state where it was unable to listen for messages from

the

> network, i.e. itself crashed.
>
> So let's assume that, from the POV of your internal logging of a

live

> connection, a single instance of your application appears to

complete the

> whole of its task without any data integrity errors or lost
> connections. Yet something (?) seems to be telling you that the

task

> *wasn't* completed. The server was interrupted during the last

commit, by

> something external and destructive. Given that this is

Superserver, the

> least likely cause will be compromised path syntax. Given that

this is

> IPServer, the most likely cause is corrupt IPC memory.
>
> Your internal logs might be enhanced greatly for your

troubleshooting if

> they recorded context information for each log entry:

CURRENT_TRANSACTION

> and CURRENT_CONNECTION (both BigInts).
>
>
> >The temp (sort) files are from the same day, but I cannot relate

them

> >to a given operation, as someone was working with the app all the
> >day. I guess they are result from SELECT from the "temp" table. It
> >has 2.3 million records and the SELECT has ORDER BY clause.
>
> Then these will be ****enormous***** temp files bridging both

memory and

> disk. If your program flow is as it appears from your description,

you

> will be having a furious overlap of operations, with huge queues of

demand

> on machine resources. Amongst all this, you are using the IPServer
> protocol, which is notoriously fragile. You haven't mentioned

whether you

> have multi-threading in this equation....Risks abound here.
>
> Experiment with development copies of the database, until you're

very clear

> that you've got it right. With your current architecture, you

would be

> very unwise to keeping playing Russian roulette with any databases

that you

> care about. A good place to start will be to abandon the IPServer

protocol

> and use TCP/IP local loopback (the localhost server) for every

connection

> to the database, as you indicated elsewhere you would do.
>
> Because you have this source table open to a ravenously hungry UI,

you need

> to find a selection strategy to minimise the number of source

records your

> client connection requests at each turn of your conversion

procedure. A

> human can't read 2.3 million records in a lifetime. A machine is

going to

> struggle, gasp and finally choke if you tell it to read, sort and

fetch 2.3

> million records each time the human wants to read one. Presumably,

the

> human knows what s/he is looking for, or you wouldn't be asking her

to look

> at all. Provide selectors to populate a WHERE clause. Provide

flags to

> tag the source records that are no longer of interest...and so on.
>
> ./hb