This happened to me once before, but I don't remember what I did to fix
it--although I thought it was strange then as well.  But now, of
course, I'm in a panic because it happened on the customer's server
database.

I was manually running the script for a nightly database backup job
because there was an error somewhere and I was trying to figure out
where.  I had run it a couple of times within the hour, but this time,
it was taking a long time and I thought it was locked or in an endless
loop (I think I actually got a timeout notice).

So I killed EDB Manager.  I looked at the server, and the service was
still going, but EDB Manager couldn't connect to it.  So I restarted
the database service and then EDB Manager could connect.

However, now all the tables that are published give me the following
error:

ElevateDB Error #100 There is an error in the metadata for the table
OrderAckDetails (Signature, password, character set (ANSI/Unicode), or
version number mismatch)


There are three tables that are not published--each of those are fine,
meaning I can still open them.  I cannot unpublish or open any of the
other tables.

The database script I ran, but broke out of is below.  Any help
(quickly?) would be greatly appreciated.


JOB "NightlyBackup"
BEGIN
 DECLARE BackupsCursor CURSOR FOR BackupsStatement;
 DECLARE ABackup VARCHAR DEFAULT '';
 DECLARE UpdatesCursor CURSOR FOR UpdatesStatement;
 DECLARE AnUpdate VARCHAR DEFAULT '';

 DECLARE curr_dt TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
 DECLARE curr_dt_str VARCHAR DEFAULT
   LEFT(REPLACE(' ', '_', REPLACE(':', '', CAST(curr_dt AS VARCHAR))),
17);

 -- clear out temp tables before we get started
 USE DCI_Master;
 EXECUTE IMMEDIATE 'DELETE FROM TempDetails';

 -- delete prior backup
 USE Configuration;
 EXECUTE IMMEDIATE 'SET FILES STORE TO "MasterUpdates"';
 PREPARE BackupsStatement FROM 'SELECT Name FROM Files WHERE Name =
''DCI_Master.EDBBkp''';
 OPEN BackupsCursor;
 FETCH FIRST FROM BackupsCursor (Name) INTO ABackup;
 IF ROWCOUNT(BackupsCursor) = 1 THEN
   EXECUTE IMMEDIATE 'DELETE FILE "' + ABackup + '" FROM STORE
"MasterUpdates"';
   SET LOG MESSAGE TO 'Removed old backup ' + ABackup;
 END IF;
 CLOSE BackupsCursor;
 UNPREPARE BackupsStatement;

 -- make new backup in archives and copy to current master
 BEGIN                 
   EXECUTE IMMEDIATE 'UNPUBLISH DATABASE "DCI_Master"';
   EXECUTE IMMEDIATE 'BACKUP DATABASE "DCI_Master" ' +
                     'AS "DCI_Master_' + curr_dt_str + '" ' +
                     'TO STORE "DCI_Archive" ' +
                     'TABLES <table list removed> ' +
                     'INCLUDE CATALOG';                   
   EXECUTE IMMEDIATE 'PUBLISH DATABASE "DCI_Master" TABLES ' +
                     '<table list removed>';
   SET LOG MESSAGE TO 'Backed up DCI_Master to
DCI_Archive."DCI_Master_' + curr_dt_str + '"';
   EXECUTE IMMEDIATE 'COPY FILE "DCI_Master_' + curr_dt_str +
'.EDBBkp" ' +
                     'IN STORE "DCI_Archive" ' +
                     'TO "DCI_Master.EDBBkp" ' +
                     'IN STORE "MasterUpdates"';
   SET LOG MESSAGE TO 'Copied backup to MasterUpdates';
 EXCEPTION
   SET LOG MESSAGE TO 'Error backing up DCI_Master to DCI_Archive: ' +
ERRORMSG();
 END;

 -- now that we have a new backup, remove any "master" updates prior
to this
 -- (remember, "master" updates are copied to new staff members)
 EXECUTE IMMEDIATE 'SET UPDATES STORE TO "MasterUpdates"';
 PREPARE UpdatesStatement FROM 'SELECT Name FROM Updates';
 OPEN UpdatesCursor;
 FETCH FIRST FROM UpdatesCursor (Name) INTO AnUpdate;
 WHILE NOT EOF(UpdatesCursor) DO
   EXECUTE IMMEDIATE 'DELETE FILE "' + AnUpdate + '.EDBUpd" FROM STORE
"MasterUpdates"';
   SET LOG MESSAGE TO 'Deleted "' + AnUpdate + '"';
   FETCH NEXT FROM UpdatesCursor (Name) INTO AnUpdate;
 END WHILE;
END


--
David Cornelius
CorneliusConcepts.com

Repair all tables ?

Heiko Knuettel wrote:

> Repair all tables ?

Can't repair.  Can't open.  Can't unpublish.

--
David Cornelius
CorneliusConcepts.com

Have you tried to replace your catalog and tables with the *.old files ? (If not, do a
backup of the whole db folder in explorer first before you try anything)

Heiko

> Have you tried to replace your catalog and tables with the *.old
> files ? (If not, do a backup of the whole db folder in explorer first
> before you try anything)

I restored from a backup.  Don't know what happened.

--
David Cornelius
CorneliusConcepts.com

David,

<< This happened to me once before, but I don't remember what I did to fix
it--although I thought it was strange then as well.  But now, of course, I'm
in a panic because it happened on the customer's server database. >>

You need to stop panicing like this.  Most of these things have simple
explanations, but you're making each situation worse and compounding the
issue with more problems by doing things like killing the server.

<< I was manually running the script for a nightly database backup job
because there was an error somewhere and I was trying to figure out where.
I had run it a couple of times within the hour, but this time, it was taking
a long time and I thought it was locked or in an endless loop (I think I
actually got a timeout notice).

So I killed EDB Manager.  I looked at the server, and the service was still
going, but EDB Manager couldn't connect to it.  So I restarted the database
service and then EDB Manager could connect.

However, now all the tables that are published give me the following error:
>>

You probably killed the service while it was in the middle of publishing the
tables and modifying the catalog.  Now you've most likely got a mismatch
between the tables and the catalog.  You *cannot* just kill the database
service while it is altering a database and expect everything to be okay.

<< The database script I ran, but broke out of is below.  Any help
(quickly?) would be greatly appreciated. >>

Is anyone else using the ElevateDB Server when you're executing this script
?  If so, then it's quite possible that a backup would wait until it could
get a lock on the database.

--
Tim Young
Elevate Software
www.elevatesoft.com

> You need to stop panicing like this.  Most of these things have
> simple explanations, but you're making each situation worse and
> compounding the issue with more problems by doing things like killing
> the server.

How can I stop panicing when I'm months behind deploying this
application to the customer because of database replication delays and
errors?  And when in my testing, everything works, so I commit to the
customer they can go live and convert their data and they start using
it, then a small error occurs and someone has out-dated data, but the
replication won't synchronize it properly because of multiple and
various errors.


> So I killed EDB Manager.  I looked at the server, and the service was
> still going, but EDB Manager couldn't connect to it.  So I restarted
> the database service and then EDB Manager could connect.
>
> However, now all the tables that are published give me the following
> error:  >>
>
> You probably killed the service while it was in the middle of
> publishing the tables and modifying the catalog.  Now you've most
> likely got a mismatch between the tables and the catalog.  You cannot
> just kill the database service while it is altering a database and
> expect everything to be okay.

Usually when I kill the server, there has been nothing happening for a
long time--like 10 or 20 minutes.  Often, I even leave the room so I
won't be tempted to break a currently running process.  Once I waited
for at least 30 minutes.  But I don't have time to keep waiting for a
process to finish that should've been done in seconds.  And most of
what I'm doing in the last day or so is very short and small updates.


> Is anyone else using the ElevateDB Server when you're executing this
> script ?  If so, then it's quite possible that a backup would wait
> until it could get a lock on the database.

No.  This server is only used by me (at night) and my customer
(occassionally during the day).  There is very little going on with the
server.  This one customer that is using it only has 4 users and they
each have their own local database and only a few times a day
synchronize.  In the evenings, there's no one using it except me.

There is an hourly job that runs through a replication process, but
that turns off at 8:30 PM, about the time I start hitting it hard.
sometimes, I turn off the job if I'm going to be doing things before
that and don't want it to be updating while I'm changing things.

The backups typically take about 20-30 seconds when things are running
smoothly.

--
David Cornelius
CorneliusConcepts.com

David,

<< How can I stop panicing when I'm months behind deploying this application
to the customer because of database replication delays and errors?  And when
in my testing, everything works, so I commit to the customer they can go
give and convert their data and they start using it, then a small error
occurs and someone has out-dated data, but the
replication won't synchronize it properly because of multiple and various
errors. >>

The panic is not helpful to your situation at all.  I don't know how else to
say it.  And a lot of these errors have not been due to EDB, but to your
lack of understanding regarding the product.  So, it would be beneficial to
you if you took a step back and tried to logically figure out where you have
issues in your application, and what those issues are.

<< Usually when I kill the server, there has been nothing happening for a
long time--like 10 or 20 minutes.  Often, I even leave the room so I won't
be tempted to break a currently running process.  Once I waited for at least
30 minutes.  But I don't have time to keep waiting for a process to finish
that should've been done in seconds.  And most of what I'm doing in the last
day or so is very short and small updates. >>

Was this perhaps during the loading process where you had the un-optimized
queries being run in the triggers ?  In such a case, it could take a very
long time for the loading to complete.  Look, I can't possibly tell you what
the server is doing during these times when it is "busy" because I simply
don't know without seeing your system, but it is doing something, and if it
is taking way too long, then you need to analyse your application and figure
out where the slowdown is or whether you have a locking conflict that is
causing the delays.

You can query the ServerSessionLocks table to see what locks are being held
by what sessions.  You may want to check it out when you have one of these
slowdowns to see if there's a restrictive lock being held that you're
unaware of.

--
Tim Young
Elevate Software
www.elevatesoft.com

> The panic is not helpful to your situation at all.  I don't know how
> else to say it.  And a lot of these errors have not been due to EDB,
> but to your lack of understanding regarding the product.  So, it
> would be beneficial to you if you took a step back and tried to
> logically figure out where you have issues in your application, and
> what those issues are.

I know the panic is not helpful--it's just a reaction to the stress I'm
under when I keep encountering errors and everything I try seems to
fail and more deadlines slip.

> Was this perhaps during the loading process where you had the
> un-optimized queries being run in the triggers ?  

No, that was resolved long ago.  Most of the time this happens, there
has been nothing happening on the server and I'm just running short
queries resulting in small table changes or small result sets.  One
statement returns quickly, then another one takes forever.  After
restarting either EDB Manager or the server, I retry the exact same
statement and it is done in seconds as expected.

> You can query the ServerSessionLocks table to see what locks are
> being held by what sessions.  You may want to check it out when you
> have one of these slowdowns to see if there's a restrictive lock
> being held that you're unaware of.

That's a very good idea--one I had forgotten about.  I'll definitely do
that.

--
David Cornelius
CorneliusConcepts.com

David,

<< No, that was resolved long ago.  Most of the time this happens, there has
been nothing happening on the server and I'm just running short queries
resulting in small table changes or small result sets.  One statement
returns quickly, then another one takes forever.  After restarting either
EDB Manager or the server, I retry the exact same statement and it is done
in seconds as expected. >>

If you're running all of these in the EDB Manager, then you could be hitting
this issue:

http://www.elevatesoft.com/incident?action=viewaddr&category=edb&release=2.02&type=f&incident=2797

--
Tim Young
Elevate Software
www.elevatesoft.com