[Firebird-test] Firebird does not create unique constraint.

[Mike Starov ( aka xsaero00 )]

I am using FB RC3 without NPTL support on SLES 9.
I get the error message "ERROR: attempt to store duplicate value (visible
to active transactions) in unique index " when I try to create unique
index on VarChar column in table with no duplicates. I even emptied out
the table and then tried to create the index but got the same message:
Code:
ALTER TABLE REFERRAL ADD CONSTRAINT REFERRALBYREFERRALCODE UNIQUE
(REFERRALCODE);
Result:
ERROR: attempt to store duplicate value (visible
to active transactions) in unique index
"REFERRALBYREFERRALCODE ".
I spent about an hour checking system tables and trying to pinpoint the
issue. Then I restarted the firebird server and tried again and all went
fine. Seems like there was an active transaction that did not let me do
it. I did not check outstanding transactions before restart so I dont
know for sure.

--
Mike Starov
mikestarov@...

CME Resource, Inc.
Roseville, CA 95661
www.netce.com
[phone]




-------------------------------------------------------------------------
Take Surveys. Earn Cash. Influence the Future of IT
Join SourceForge.net's Techsay panel and you'll get the chance to share your
opinions on IT & business topics through brief surveys -- and earn cash
http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
_______________________________________________
Firebird-test mailing list
Firebird-test@...
https://lists.sourceforge.net/lists/listinfo/firebird-test

[Leyne, Sean]

Mike

> I am using FB RC3 without NPTL support on SLES 9.
> I get the error message "ERROR: attempt to store duplicate value
(visible
> to active transactions) in unique index " when I try to create unique
> index on VarChar column in table with no duplicates. I even emptied
out
> the table and then tried to create the index but got the same message:
> Code:
> ALTER TABLE REFERRAL ADD CONSTRAINT REFERRALBYREFERRALCODE UNIQUE
> (REFERRALCODE);
> Result:
> ERROR: attempt to store duplicate value (visible
> to active transactions) in unique index
> "REFERRALBYREFERRALCODE ".
> I spent about an hour checking system tables and trying to pinpoint
the
> issue. Then I restarted the firebird server and tried again and all
went
> fine. Seems like there was an active transaction that did not let me
do
> it. I did not check outstanding transactions before restart so I dont
> know for sure.

A Firebird index includes index data for all active transactions, this
is by design.

Accordingly, what you are seeing is the correct behaviour.


Sean


-------------------------------------------------------------------------
Take Surveys. Earn Cash. Influence the Future of IT
Join SourceForge.net's Techsay panel and you'll get the chance to share your
opinions on IT & business topics through brief surveys -- and earn cash
http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
_______________________________________________
Firebird-test mailing list
Firebird-test@...
https://lists.sourceforge.net/lists/listinfo/firebird-test

[Mike Starov ( aka xsaero00 )]

Leyne, Sean wrote:
> A Firebird index includes index data for all active transactions, this
> is by design.
>
> Accordingly, what you are seeing is the correct behaviour.
>
>
> Sean
>  
I know that. The question is why there were active transactions
affecting the new index. I don't think this is a new issue. See CORE-400
<http://tracker.firebirdsql.org/browse/CORE-400> in bug database. I have
a copy of the database running on FB 1.5 and the same operation went
through fine there.

I have only been using the FireBird DB, did not get into development of
debugging, so I don't know all the secrets. More of the end user point
of view.

--
Mike Starov
mikestarov@...

CME Resource, Inc.
Roseville, CA 95661
www.netce.com
[phone]





-------------------------------------------------------------------------
Take Surveys. Earn Cash. Influence the Future of IT
Join SourceForge.net's Techsay panel and you'll get the chance to share your
opinions on IT & business topics through brief surveys -- and earn cash
http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
_______________________________________________
Firebird-test mailing list
Firebird-test@...
https://lists.sourceforge.net/lists/listinfo/firebird-test

[Leyne, Sean]

Mike,

> Leyne, Sean wrote:
> > A Firebird index includes index data for all active transactions,
this
> > is by design.
> >
> > Accordingly, what you are seeing is the correct behaviour.
> >
> >
> > Sean
> >
> I know that. The question is why there were active transactions
> affecting the new index.

Because a unique index requires that the engine consider both committed
transactions and also active transactions, in order to ensure
uniqueness.

You will note that the uniqueness error is noted when the insert/update
takes place, not when the transaction is committed.  Accordingly, the
index/engine is affected by active transactions.

>I don't think this is a new issue. See CORE-400
> <http://tracker.firebirdsql.org/browse/CORE-400> in bug database.

This case is not applicable -- it involves restoring a database which
now has a unique constraint but which has data which isn't unique.  This
is a restore problem.


> I have a copy of the database running on FB 1.5 and the same operation
> went through fine there.

Unless you had active transactions, I would have expected the same but
you didn't so the comparison is not equivalent.

Regards,

Sean

-------------------------------------------------------------------------
Take Surveys. Earn Cash. Influence the Future of IT
Join SourceForge.net's Techsay panel and you'll get the chance to share your
opinions on IT & business topics through brief surveys -- and earn cash
http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
_______________________________________________
Firebird-test mailing list
Firebird-test@...
https://lists.sourceforge.net/lists/listinfo/firebird-test

[Mike Starov ( aka xsaero00 )]

I am running FB RC3 on Suse Linux and for reason unknown firebird server
just keep on restarting. I used to run RC3 with NPTL but because of the
problem I switched to one without NPTL, but it did not help.
I don't know how to go about debugging it to pinpoint the problem so
here I am asking for pointers. It usually happens when I run python unit
tests. I use kinterbasdb for connecting to the database. The restart
usually happens when the statement is executed; the connection part
seems to go fine. In the firebird log I simply get messages about server
being abnormally shut down and then restarted by the fbguard.
Like so:
devserver (Client)    Tue Aug  1 15:58:01 2006
    /opt/firebird/bin/fbguard: bin/fbserver terminated abnormally (-1)

devserver (Client)    Tue Aug  1 15:58:01 2006
    /opt/firebird/bin/fbguard: guardian starting bin/fbserver

Where can I find out more about what caused the restart? What procedure
to follow? Any suggestion is anticipated.

--
Mike Starov
mikestarov@...

CME Resource, Inc.
Roseville, CA 95661
www.netce.com
[phone]





-------------------------------------------------------------------------
Take Surveys. Earn Cash. Influence the Future of IT
Join SourceForge.net's Techsay panel and you'll get the chance to share your
opinions on IT & business topics through brief surveys -- and earn cash
http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
_______________________________________________
Firebird-test mailing list
Firebird-test@...
https://lists.sourceforge.net/lists/listinfo/firebird-test

[Cédric Simon-2]

Hi Mike,

I got this problem earlier (RC1 & 2). The server restarted without apparent
reason.

Finally I found out (after moving to MySQL v5) that the problem was 'too
many connection' open (or connection pool exhausted). The problem was that
in one of my codings (of course, the most used...) I was opening a
connection and not closing it properly!

MySQL has error messages much more explicit than Firebird, and as MySQL has
now views, procedures, triggers... I will stay with MySQL. I loved very much
Firebird, I enjoyed it a lot, but I need a more reliable database for my
business.

Now I created a debugging option logging any open or closing of connection,
so I can detect easily what (new) coding is not closing connection properly.

I hope it will help you.

Regards,

Cedric.

-----Mensaje original-----
De: firebird-test-bounces@...
[mailto:firebird-test-bounces@...]En nombre de Mike
Starov ( aka xsaero00 )
Enviado el: Jueves, 03 de Agosto de 2006 10:18 a.m.
Para: firebird-test@...
Asunto: [Firebird-test] Firebird RC3 keeps restarting


I am running FB RC3 on Suse Linux and for reason unknown firebird server
just keep on restarting. I used to run RC3 with NPTL but because of the
problem I switched to one without NPTL, but it did not help.
I don't know how to go about debugging it to pinpoint the problem so
here I am asking for pointers. It usually happens when I run python unit
tests. I use kinterbasdb for connecting to the database. The restart
usually happens when the statement is executed; the connection part
seems to go fine. In the firebird log I simply get messages about server
being abnormally shut down and then restarted by the fbguard.
Like so:
devserver (Client)    Tue Aug  1 15:58:01 2006
    /opt/firebird/bin/fbguard: bin/fbserver terminated abnormally (-1)

devserver (Client)    Tue Aug  1 15:58:01 2006
    /opt/firebird/bin/fbguard: guardian starting bin/fbserver

Where can I find out more about what caused the restart? What procedure
to follow? Any suggestion is anticipated.

--
Mike Starov
mikestarov@...

CME Resource, Inc.
Roseville, CA 95661
www.netce.com
[phone]





-------------------------------------------------------------------------
Take Surveys. Earn Cash. Influence the Future of IT
Join SourceForge.net's Techsay panel and you'll get the chance to share your
opinions on IT & business topics through brief surveys -- and earn cash
http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
_______________________________________________
Firebird-test mailing list
Firebird-test@...
https://lists.sourceforge.net/lists/listinfo/firebird-test


-------------------------------------------------------------------------
Take Surveys. Earn Cash. Influence the Future of IT
Join SourceForge.net's Techsay panel and you'll get the chance to share your
opinions on IT & business topics through brief surveys -- and earn cash
http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
_______________________________________________
Firebird-test mailing list
Firebird-test@...
https://lists.sourceforge.net/lists/listinfo/firebird-test

[Miguel-33]

Hey Cedric,

        Could you please tell me, how many connections on Firebird you found
out?

Miguel

----- Original Message -----
From: "Cedric Simon" <cedric@...>
To: <xsaero00@...>; <firebird-test@...>
Sent: Saturday, August 05, 2006 10:32 AM
Subject: Re: [Firebird-test] Firebird RC3 keeps restarting

> Hi Mike,
>
> I got this problem earlier (RC1 & 2). The server restarted without
> apparent
> reason.
>
> Finally I found out (after moving to MySQL v5) that the problem was 'too
> many connection' open (or connection pool exhausted). The problem was that
> in one of my codings (of course, the most used...) I was opening a
> connection and not closing it properly!
>
> MySQL has error messages much more explicit than Firebird, and as MySQL
> has
> now views, procedures, triggers... I will stay with MySQL. I loved very
> much
> Firebird, I enjoyed it a lot, but I need a more reliable database for my
> business.
>
> Now I created a debugging option logging any open or closing of
> connection,
> so I can detect easily what (new) coding is not closing connection
> properly.
>
> I hope it will help you.
>
> Regards,
>
> Cedric.
>
> -----Mensaje original-----
> De: firebird-test-bounces@...
> [mailto:firebird-test-bounces@...]En nombre de Mike
> Starov ( aka xsaero00 )
> Enviado el: Jueves, 03 de Agosto de 2006 10:18 a.m.
> Para: firebird-test@...
> Asunto: [Firebird-test] Firebird RC3 keeps restarting
>
>
> I am running FB RC3 on Suse Linux and for reason unknown firebird server
> just keep on restarting. I used to run RC3 with NPTL but because of the
> problem I switched to one without NPTL, but it did not help.
> I don't know how to go about debugging it to pinpoint the problem so
> here I am asking for pointers. It usually happens when I run python unit
> tests. I use kinterbasdb for connecting to the database. The restart
> usually happens when the statement is executed; the connection part
> seems to go fine. In the firebird log I simply get messages about server
> being abnormally shut down and then restarted by the fbguard.
> Like so:
> devserver (Client)    Tue Aug  1 15:58:01 2006
>    /opt/firebird/bin/fbguard: bin/fbserver terminated abnormally (-1)
>
> devserver (Client)    Tue Aug  1 15:58:01 2006
>    /opt/firebird/bin/fbguard: guardian starting bin/fbserver
>
> Where can I find out more about what caused the restart? What procedure
> to follow? Any suggestion is anticipated.
>
> --
> Mike Starov
> mikestarov@...
>
> CME Resource, Inc.
> Roseville, CA 95661
> www.netce.com
> [phone]
>
>
>
>
>
> -------------------------------------------------------------------------
> Take Surveys. Earn Cash. Influence the Future of IT
> Join SourceForge.net's Techsay panel and you'll get the chance to share
> your
> opinions on IT & business topics through brief surveys -- and earn cash
> http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
> _______________________________________________
> Firebird-test mailing list
> Firebird-test@...
> https://lists.sourceforge.net/lists/listinfo/firebird-test
>
>
> -------------------------------------------------------------------------
> Take Surveys. Earn Cash. Influence the Future of IT
> Join SourceForge.net's Techsay panel and you'll get the chance to share
> your
> opinions on IT & business topics through brief surveys -- and earn cash
> http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
> _______________________________________________
> Firebird-test mailing list
> Firebird-test@...
> https://lists.sourceforge.net/lists/listinfo/firebird-test
>
>
> --
> No virus found in this incoming message.
> Checked by AVG Free Edition.
> Version: 7.1.394 / Virus Database: 268.10.7/410 - Release Date: 5/8/2006
>
>

-------------------------------------------------------------------------
Take Surveys. Earn Cash. Influence the Future of IT
Join SourceForge.net's Techsay panel and you'll get the chance to share your
opinions on IT & business topics through brief surveys -- and earn cash
http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
_______________________________________________
Firebird-test mailing list
Firebird-test@...
https://lists.sourceforge.net/lists/listinfo/firebird-test

[Cedric Simon-3]

Hi Miguel,

Under windows I could investigate running FB as application: it was 63
connection.
Under Linux I didn't find out, but I suspect it was less than 100 as I had a
connection pool of 100. Now I am under MySQL I didn't investigate FB
backwards with my connection debugging code. You can do it easily logging
any open and close of connection in your codes (time, open or close, code
used).

Good luck...

Cedric.

-----Mensaje original-----
De: firebird-test-bounces@...
[mailto:firebird-test-bounces@...]En nombre de Miguel
Enviado el: Lunes, 07 de Agosto de 2006 07:16 a.m.
Para: firebird-test@...
Asunto: Re: [Firebird-test] Firebird RC3 keeps restarting


Hey Cedric,

        Could you please tell me, how many connections on Firebird you found
out?

Miguel

----- Original Message -----
From: "Cedric Simon" <cedric@...>
To: <xsaero00@...>; <firebird-test@...>
Sent: Saturday, August 05, 2006 10:32 AM
Subject: Re: [Firebird-test] Firebird RC3 keeps restarting

> Hi Mike,
>
> I got this problem earlier (RC1 & 2). The server restarted without
> apparent
> reason.
>
> Finally I found out (after moving to MySQL v5) that the problem was 'too
> many connection' open (or connection pool exhausted). The problem was that
> in one of my codings (of course, the most used...) I was opening a
> connection and not closing it properly!
>
> MySQL has error messages much more explicit than Firebird, and as MySQL
> has
> now views, procedures, triggers... I will stay with MySQL. I loved very
> much
> Firebird, I enjoyed it a lot, but I need a more reliable database for my
> business.
>
> Now I created a debugging option logging any open or closing of
> connection,
> so I can detect easily what (new) coding is not closing connection
> properly.
>
> I hope it will help you.
>
> Regards,
>
> Cedric.
>
> -----Mensaje original-----
> De: firebird-test-bounces@...
> [mailto:firebird-test-bounces@...]En nombre de Mike
> Starov ( aka xsaero00 )
> Enviado el: Jueves, 03 de Agosto de 2006 10:18 a.m.
> Para: firebird-test@...
> Asunto: [Firebird-test] Firebird RC3 keeps restarting
>
>
> I am running FB RC3 on Suse Linux and for reason unknown firebird server
> just keep on restarting. I used to run RC3 with NPTL but because of the
> problem I switched to one without NPTL, but it did not help.
> I don't know how to go about debugging it to pinpoint the problem so
> here I am asking for pointers. It usually happens when I run python unit
> tests. I use kinterbasdb for connecting to the database. The restart
> usually happens when the statement is executed; the connection part
> seems to go fine. In the firebird log I simply get messages about server
> being abnormally shut down and then restarted by the fbguard.
> Like so:
> devserver (Client)    Tue Aug  1 15:58:01 2006
>    /opt/firebird/bin/fbguard: bin/fbserver terminated abnormally (-1)
>
> devserver (Client)    Tue Aug  1 15:58:01 2006
>    /opt/firebird/bin/fbguard: guardian starting bin/fbserver
>
> Where can I find out more about what caused the restart? What procedure
> to follow? Any suggestion is anticipated.
>
> --
> Mike Starov
> mikestarov@...
>
> CME Resource, Inc.
> Roseville, CA 95661
> www.netce.com
> [phone]
>
>
>
>
>
> -------------------------------------------------------------------------
> Take Surveys. Earn Cash. Influence the Future of IT
> Join SourceForge.net's Techsay panel and you'll get the chance to share
> your
> opinions on IT & business topics through brief surveys -- and earn cash
> http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
> _______________________________________________
> Firebird-test mailing list
> Firebird-test@...
> https://lists.sourceforge.net/lists/listinfo/firebird-test
>
>
> -------------------------------------------------------------------------
> Take Surveys. Earn Cash. Influence the Future of IT
> Join SourceForge.net's Techsay panel and you'll get the chance to share
> your
> opinions on IT & business topics through brief surveys -- and earn cash
> http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
> _______________________________________________
> Firebird-test mailing list
> Firebird-test@...
> https://lists.sourceforge.net/lists/listinfo/firebird-test
>
>
> --
> No virus found in this incoming message.
> Checked by AVG Free Edition.
> Version: 7.1.394 / Virus Database: 268.10.7/410 - Release Date: 5/8/2006
>
>

-------------------------------------------------------------------------
Take Surveys. Earn Cash. Influence the Future of IT
Join SourceForge.net's Techsay panel and you'll get the chance to share your
opinions on IT & business topics through brief surveys -- and earn cash
http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
_______________________________________________
Firebird-test mailing list
Firebird-test@...
https://lists.sourceforge.net/lists/listinfo/firebird-test


-------------------------------------------------------------------------
Using Tomcat but need to do more? Need to support web services, security?
Get stuff done quickly with pre-integrated technology to make your job easier
Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642
_______________________________________________
Firebird-test mailing list
Firebird-test@...
https://lists.sourceforge.net/lists/listinfo/firebird-test