Subject Re: [ib-support] Disappointed in Interbase (long)
Author Claudio Valderrama C.
"Louis van Alphen" <lja@...> wrote in message
> Our current project is expanding the system to include ordering, sales,
> invoicing, etc. We decided to stick to IB5.6 for now as it is certified,
> and it will not require a migration to a new version of IB.

Let me be clear and this is my opinion: the whole v5 cycle is a royal
nightmare. Only IB5.6 is better, but not very good. The few people I know
that are happily running IB for years here use IB4.2.

> The new project involves a team of 10 developers each developing their
> of the system. Each developer is using IBExpert to do the necessary
> metadata changes. These changes include table creation, adding/dropping
> columns altering SPs and triggers, etc. In short, we are hammering the DB
> with metadata changes. Unfortunately, it seems that IB does not like
> this... The typical problems we have encountered include:

And those problems are known for years. Metadata changes robustness was one
of the goals of the NewCo that never happened.

> - Getting the 'Object in use' message. Then everybody has to log off and
> have to do a backup & restore cycle to be able to use the
> index/table/SP/whatever again

Why a backup/restore. If everybody sign offs, IB detaches the db, then one
person connects and does the change that previously was stopped due to
"Object in use". This is a portion of a message I posted to fb-devel. Some
details may be not totally accurate, but it's basically what I remember from
a post made by Charles Caro himself:
I don't like quick and nasty solutions unless there's no alternative. Just
an example: years ago, the IB team forced IB to create indexes for FKs.
Previously it didn't (that's an "improvement" that I regret often). Then
they came crying to Charlie because they needed to avoid index corruption
and at the same time, ensure the change was enforced immediately. Since they
were finishing beta testing, they couldn't afford a big architectural
change. Charlie's workaround was "lock access to the index root page by
demanding exclusive access to the db". Said and done. Until now (several
years have passed since IB4), you see the infamous TABLE IN USE message if
you try to create or drop a FK with more than one db attachment, even if all
attachments to the db are due solely to the own sysdba working on different
tool instances.

> - Triggers not firing. Goodness knows why not.

Maybe because not all instances (connections) realized that triggers were
created. Do they fire after all people reconnected?

> - SP not running correctly. We have had the following problem that really
> boggles the mind. We have also repeated this to make sure we are not
> stupid mistakes. We compile a SP sucessfully. If we run it, the old
> still runs. No matter how often we recompile the SP, it still runs a
> previous version. Then we all log off, do a Backup & Restore (B&R) and
> viola! the problem disappears and the correct version of the SP runs. We
> change the SP & compile and same problem happens. And this on the restored
> DB! We had to resort to B&R after each SP update & compile session. I can
> assure you that this is no fun..!

No need to backup/restore. This is BY DESIGN. Only one version of a
procedure can be loaded and run at once. The first time the proc is called,
it's loaded. It remains the same until all connections are finished and
people reconnect. Then the new BLR is loaded. Do you think that it would be
funny is there are several inconsistent version of the same code being
executed at the same time? Maybe it could be reloaded, but only if nobody is
executing it.

> - Triggers same as SPs.

Probably due to the same.

> - Abnormal server terminations

What a surprise. Are you using views or several indices? If this is the
case, then only Firebird will survive for more time running.

> We had to install our new system last week. We had 3 developers on site
> making last minute changes. The same problems happened, even with one
> person doing metadata changes and continually doing B&Rs. It seems as if
> the DB is pretty trashed.... This causes so many problems for us as it
> almost made debugging a futile exercise. You never knew what you where
> going to get! You can imagine how this can kill a project....

If the db is already trashed, your best bet is to recreate it. Backup and
restore DOESN'T resolve all problems. There're cases where only a fresh
start will save you.

> I have to admit that we initially made the connection string mistake, but
> we resolved that and did a B&R. We also did copy the DB all over the place
> on our network. The machines we are using are Win98SE, NT4 SP6 and Win2K
> Beta. If this is part of the problem, I will admit guilt. But why do the
> problems prevail even after a B&R and the DB staying on the server?

In some cases, yes. I would have to know the complexity of your schema to
give more opinions. For example, total number of tables, procs, triggers,
indexes, views, etc.

Claudio Valderrama C. - -
Independent developer
Owner of the Interbase® WebRing