Subject Re: [firebird-support] Re: Database corruption if server is not properly shutdown
Author Federico Tello Gentile
Milan Babuskov wrote:
> Federico Tello Gentile wrote:
>
> FlameRobin does not create DDL scripts (yet), so perhaps you can show
> them to us?

the script I ran was like this:
drop table sometable;
... 7 in total
drop domain something;
... 8 in total
drop generator id_generator;
create domain something
... 8 in total
create table someting
... 7 in total
create generator id_generator;
set term ^ ;
create trigger...
begin
...
end ^
create trigger...
begin
...
end ^
... several triggers more, all ended with ^
set term ; ^

That was the script. It had no errors, and I ran it pressing F9 and if
no error message appeared, I pressed F5 (commit). sometimes when i had a
typo or a missing comma, I rolled back after pressing F5.

What I don't remember is if all the times I did this I had shut down the
Tomcat server (that was holding opened connections using the JDBC driver).
Having discarded the unclean shutdown, because the last time it hapened
I am 100% sure I always saw the server shutdown in the linux shutdown
log and I never resetted the machine or had a power failure, I think FB
is breaking because I ran that script 2 or 3 times in the database lifetime.
After that 3rd corruption, I used isql to drop and recreate single
objects, instead of dropping everything and starting over. So far I see
the DB is working fine. I have the corrupt DB file if anybody wants to
play CSI with it.

>
> Please note that FlameRobin simply parses the input script and sends it
> to Firebird, it does not alter anything, so if the script makes hell in
> FR, to will probably do it in isql too.
>

Probably, but I'm not going to test it... If anybody wants try I can
send you the full script.

>
> Also, note that automatic commiting of DDL is by default *on* in isql,
> and by default *off* in FlameRobin. This could make a very big
> difference. FlameRobin 0.4.0 (soon to be released) will support isql's
> SET AUTODDL command. Until then I suggest you don't use FR to run
> scripts generated by isql (for example with isql -x) as there is no way
> to turn autocommit on.
>

I don't know what is the big differnce since I always pressed commit or
rollback after every single execution.

Since the error always gets discovered when I try to insert a record in
a specific place I guess FB is losing that specific foreign key index.
It is a Person to Member relationship. One person can be many members
(can have many memberships), when I create the member I also create the
person (the first time) and there the foreign key check did its job
(forcing the person to exist when I insert the membership), until it
threw that internal consistency check error.

After that error, I could still connect, sometimes not at the first try,
could query, but not update or insert to that table.
It alowed violating the foreign key, rerunning the full script dropping
and recreating (all objects) did not solve the problem.

The DB was created with a user, not sysdba. The connections are always
using the user account. Never sysdba.