Thread: BUG #5994: Can't excute DBI->connect to oracle by client site
The following bug has been logged online:
Bug reference: 5994
Logged by: Li Hongbing
Email address: lihongbing@intasect.com.cn
PostgreSQL version: PostgreSQL 8.4.
Operating system: x86_64_unknonwn_linux_gnu
Description: Can't excute DBI->connect to oracle by client site
Details:
I am trying to create a 64-bit postgresql 8.4 database server which can
retrieve data from Oracle(10gR2) at an other server by DBI-LINK.
DBI-LINK works good on postgresql server site when I connect to linux by
telnet.When I connect to postgresql server by postgresql client just like
pgAdmin or EMS SQL Manager,I got a error like this:
error from Perl function "set_up_connection": DBI
connect('host=192.168.138.173;sid=smmacs;port=1521','rmtrsys',...) failed:
ORA-24327: need explicit attach before authenticating a user (DBD ERROR:
OCISessionBegin) at line 43 at line 35.
so I write test code to confirm DBI connect function
CREATE OR REPLACE FUNCTION dbi_link.test2()
RETURNS char AS
$BODY$
use DBI;
use DBD::Oracle;
my $dbname = 'xxx';
my $user = 'rmtrsys';
my $passwd = 'rmtrsys';
my $dbh =
DBI->connect("dbi:Oracle:host=192.168.138.173;sid=smmacs;port=1521", $user,
$passwd,{ora_charset => 'UTF8'}) or
die "Database connection not made: $DBI::errstr";
$dbh -> disconnect;
return "OK";
$BODY$
LANGUAGE 'plperlu' VOLATILE
COST 100;
When I connect to linux by telnet and excute psql -d dbi_link_test to run,it
works good
dbi_link_test=# select dbi_link.test2();
test2
-------
OK
(1 row)
But when I connect by postgresql client (pgAdmin or EMS SQL Manager) to run
same sql, it returned an error:
NOTICE: DBI
connect('host=192.168.138.173;sid=smmacs;port=1521','rmtrsys',...) failed:
ORA-24327: need explicit attach before authenticating a user (DBD ERROR:
OCISessionBegin) at line 10
ERROR: error from Perl function "test2": Database connection not made:
ORA-24327: need explicit attach before authenticating a user (DBD ERROR:
OCISessionBegin) at line 10.
********** ã¨ã©ã¼ **********
ERROR: error from Perl function "test2": Database connection not made:
ORA-24327: need explicit attach before authenticating a user (DBD ERROR:
OCISessionBegin) at line 10.
SQLã¹ãã¼ã:XX000
when got this error,I can't shutdown the server normally,ths shutdown
command will not terminate unless I cancel it.
[postgres@localhost ~]$ pg_ctl stop -D /usr/local/pgsql/data
waiting for server to shut down...LOG: received smart shutdown request
.LOG: autovacuum launcher shutting down
..........................
Above test let me know the DBI-LINK crushed by DBI->connect, I think the
really reason is one of these:
1.postgresql create backend process has a bug,most likely with bad process
environment var
2.Orace OCI functions has a bug
3.DBD:Oracle has a bug
However,I should report this as a postgresql bug ,becourse of run same sql
must get same result anywhere(server or client).
Versions:
Oracle-10.2.0.1.0
DBI-1.616
DBD-Oracle-1.28
YAML-0.72
perl, v5.8.8 built for x86_64-linux-thread-multi
Regards,
Li Hongbing,China
On Tue, Apr 26, 2011 at 5:36 AM, Li Hongbing <lihongbing@intasect.com.cn> w=
rote:
> I am trying to create a 64-bit postgresql 8.4 database server which can
> retrieve data from Oracle(10gR2) at an other server by DBI-LINK.
>
> DBI-LINK works good on postgresql server site when I connect to linux by
> telnet.When I connect to postgresql server by postgresql client just like
> pgAdmin or EMS SQL Manager,I got a error like this:
> error from Perl function "set_up_connection": DBI
> connect('host=3D192.168.138.173;sid=3Dsmmacs;port=3D1521','rmtrsys',...) =
failed:
> ORA-24327: need explicit attach before authenticating a user (DBD ERROR:
> OCISessionBegin) at line 43 at line 35.
>
> so I write test code to confirm DBI connect function
>
> CREATE OR REPLACE FUNCTION dbi_link.test2()
> RETURNS char AS
> $BODY$
> use DBI;
> use DBD::Oracle;
>
>
> my $dbname =3D 'xxx';
> my $user =3D 'rmtrsys';
> my $passwd =3D 'rmtrsys';
>
> my $dbh =3D
> DBI->connect("dbi:Oracle:host=3D192.168.138.173;sid=3Dsmmacs;port=3D1521"=
, $user,
> $passwd,{ora_charset =3D> 'UTF8'}) or
> die "Database connection not made: $DBI::errstr";
>
> $dbh -> disconnect;
>
> return "OK";
>
> $BODY$
> LANGUAGE 'plperlu' VOLATILE
> COST 100;
>
>
> When I connect to linux by telnet and excute psql -d dbi_link_test to run=
,it
> works good
>
> dbi_link_test=3D# select dbi_link.test2();
> test2
> -------
> OK
> (1 row)
>
> But when I connect by postgresql client (pgAdmin or EMS SQL Manager) to =
run
> same sql, it returned an error:
> NOTICE: DBI
> connect('host=3D192.168.138.173;sid=3Dsmmacs;port=3D1521','rmtrsys',...) =
failed:
> ORA-24327: need explicit attach before authenticating a user (DBD ERROR:
> OCISessionBegin) at line 10
>
>
> ERROR: error from Perl function "test2": Database connection not made:
> ORA-24327: need explicit attach before authenticating a user (DBD ERROR:
> OCISessionBegin) at line 10.
>
> ********** =A5=A8=A5=E9=A9` **********
>
> ERROR: error from Perl function "test2": Database connection not made:
> ORA-24327: need explicit attach before authenticating a user (DBD ERROR:
> OCISessionBegin) at line 10.
> SQL=A5=B9=A5=C6=A9`=A5=C8:XX000
>
>
> when got this error,I can't shutdown the server normally,ths shutdown
> command will not terminate unless I cancel it.
> [postgres@localhost ~]$ pg_ctl stop -D /usr/local/pgsql/data
> waiting for server to shut down...LOG: received smart shutdown request
> .LOG: autovacuum launcher shutting down
> ..........................
>
> Above test let me know the DBI-LINK crushed by DBI->connect, I think the
> really reason is one of these:
> 1.postgresql create backend process has a bug,most likely with bad process
> environment var
> 2.Orace OCI functions has a bug
> 3.DBD:Oracle has a bug
>
> However,I should report this as a postgresql bug ,becourse of run same sql
> must get same result anywhere(server or client).
My guess is that, depending on how you connect, something in your
environment is different, and that is what is making the Oracle
connection succeed or fail.
--=20
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
I run my test program with 2 ways in the same login environment.One succeed and one fail.
1) psql -d dbi_link_test
-->connect succeed
2) psql -h HOSTNAME -d dbi_link_test
-->connect fail
The different is connection options(Unix Domain Sockets/TCP Sockets).
----- Original Message -----
From: "Robert Haas" <robertmhaas@gmail.com>
To: "Li Hongbing" <lihongbing@intasect.com.cn>
Cc: <pgsql-bugs@postgresql.org>
Sent: Tuesday, May 10, 2011 10:40 AM
Subject: Re: [BUGS] BUG #5994: Can't excute DBI->connect to oracle by client site
On Tue, Apr 26, 2011 at 5:36 AM, Li Hongbing <lihongbing@intasect.com.cn> wrote:
> I am trying to create a 64-bit postgresql 8.4 database server which can
> retrieve data from Oracle(10gR2) at an other server by DBI-LINK.
>
> DBI-LINK works good on postgresql server site when I connect to linux by
> telnet.When I connect to postgresql server by postgresql client just like
> pgAdmin or EMS SQL Manager,I got a error like this:
> error from Perl function "set_up_connection": DBI
> connect('host=192.168.138.173;sid=smmacs;port=1521','rmtrsys',...) failed:
> ORA-24327: need explicit attach before authenticating a user (DBD ERROR:
> OCISessionBegin) at line 43 at line 35.
>
> so I write test code to confirm DBI connect function
>
> CREATE OR REPLACE FUNCTION dbi_link.test2()
> RETURNS char AS
> $BODY$
> use DBI;
> use DBD::Oracle;
>
>
> my $dbname = 'xxx';
> my $user = 'rmtrsys';
> my $passwd = 'rmtrsys';
>
> my $dbh =
> DBI->connect("dbi:Oracle:host=192.168.138.173;sid=smmacs;port=1521", $user,
> $passwd,{ora_charset => 'UTF8'}) or
> die "Database connection not made: $DBI::errstr";
>
> $dbh -> disconnect;
>
> return "OK";
>
> $BODY$
> LANGUAGE 'plperlu' VOLATILE
> COST 100;
>
>
> When I connect to linux by telnet and excute psql -d dbi_link_test to run,it
> works good
>
> dbi_link_test=# select dbi_link.test2();
> test2
> -------
> OK
> (1 row)
>
> But when I connect by postgresql client (pgAdmin or EMS SQL Manager) to run
> same sql, it returned an error:
> NOTICE: DBI
> connect('host=192.168.138.173;sid=smmacs;port=1521','rmtrsys',...) failed:
> ORA-24327: need explicit attach before authenticating a user (DBD ERROR:
> OCISessionBegin) at line 10
>
>
> ERROR: error from Perl function "test2": Database connection not made:
> ORA-24327: need explicit attach before authenticating a user (DBD ERROR:
> OCISessionBegin) at line 10.
>
> ********** エラ�` **********
>
> ERROR: error from Perl function "test2": Database connection not made:
> ORA-24327: need explicit attach before authenticating a user (DBD ERROR:
> OCISessionBegin) at line 10.
> SQLステ�`ト:XX000
>
>
> when got this error,I can't shutdown the server normally,ths shutdown
> command will not terminate unless I cancel it.
> [postgres@localhost ~]$ pg_ctl stop -D /usr/local/pgsql/data
> waiting for server to shut down...LOG: received smart shutdown request
> .LOG: autovacuum launcher shutting down
> ..........................
>
> Above test let me know the DBI-LINK crushed by DBI->connect, I think the
> really reason is one of these:
> 1.postgresql create backend process has a bug,most likely with bad process
> environment var
> 2.Orace OCI functions has a bug
> 3.DBD:Oracle has a bug
>
> However,I should report this as a postgresql bug ,becourse of run same sql
> must get same result anywhere(server or client).
My guess is that, depending on how you connect, something in your
environment is different, and that is what is making the Oracle
connection succeed or fail.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
On 05/10/11 8:09 PM, =E6=9D=8E=E7=B4=85=E5=85=B5 wrote: > I run my test program with 2 ways in the same login environment.One=20 > succeed and one fail. > > 1) psql -d dbi_link_test > -->connect succeed > 2) psql -h HOSTNAME -d dbi_link_test > -->connect fail > > The different is connection options(Unix Domain Sockets/TCP Sockets).=20 is your server listen_addresses =3D '*' ? if not, its not listening on the= =20 interface that will respond to connections to 'HOSTNAME' and, is pg_hba.conf configured for an appropriate authentication method=20 for `host` connections ?
Perhaps you misunderstanding what I mean. I can connect to postgreSQL server correctly, but When I connect postgreSQL with TCP Sockets, it return error to connect to oracle server by DBI->connect from perlu script. When I connect postgreSQL with Unix Domain Sockets,it works well. My conf file: listen_addresses = '*' # what IP address(es) to listen on; host all all 0.0.0.0/0 password ----- Original Message ----- From: "John R Pierce" <pierce@hogranch.com> To: <pgsql-bugs@postgresql.org> Sent: Wednesday, May 11, 2011 11:40 AM Subject: Re: [BUGS] BUG #5994: Can't excute DBI->connect to oracle by client site On 05/10/11 8:09 PM, 李紅兵 wrote: > I run my test program with 2 ways in the same login environment.One succeed and one fail. > > 1) psql -d dbi_link_test > -->connect succeed > 2) psql -h HOSTNAME -d dbi_link_test > -->connect fail > > The different is connection options(Unix Domain Sockets/TCP Sockets). is your server listen_addresses = '*' ? if not, its not listening on the interface that will respond to connections to 'HOSTNAME' and, is pg_hba.conf configured for an appropriate authentication method for `host` connections ? -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
On 11/05/11 13:53, æç´ å µ wrote: > Perhaps you misunderstanding what I mean. > I can connect to postgreSQL server correctly, > but When I connect postgreSQL with TCP Sockets, it return error to > connect to oracle server by DBI->connect from perlu script. > > When I connect postgreSQL with Unix Domain Sockets,it works well. Are you _certain_ that you're connecting to the same database on the same machine when you use TCP vs unix sockets? The simplest explanation for this problem would be that you're running different test perlu code or running it on different machines/DBs when connecting via TCP than when connecting via unix socket. If that's not the case, about the only thing I can imagine is some weird kernel security module getting in the way. Maybe something that tries to isolate processes started as a result of tcp/ip connections so they can't make new connections themselves, or something crazy like that. You wouldn't happen to be on a box with SELinux/Apparmor/TOMOYO/SMACK/etc, would you? -- Craig Ringer
yes,I am sure that I connect to the same database and same machine. because I run my test code at the linux server with telnet. As you said,it likes some security modules inhibit to make a new connection. My server is Redhat Linux Enterprice version with typical installation.and I don't know about SELinux/Apparmor/TOMOYO/SMACK ----- Original Message ----- From: "Craig Ringer" <craig@postnewspapers.com.au> To: "李紅兵" <lihongbing@intasect.com.cn> Cc: "John R Pierce" <pierce@hogranch.com>; <pgsql-bugs@postgresql.org> Sent: Wednesday, May 11, 2011 2:17 PM Subject: Re: [BUGS] BUG #5994: Can't excute DBI->connect to oracle by client site > On 11/05/11 13:53, 李紅兵 wrote: >> Perhaps you misunderstanding what I mean. >> I can connect to postgreSQL server correctly, >> but When I connect postgreSQL with TCP Sockets, it return error to >> connect to oracle server by DBI->connect from perlu script. >> >> When I connect postgreSQL with Unix Domain Sockets,it works well. > > Are you _certain_ that you're connecting to the same database on the > same machine when you use TCP vs unix sockets? The simplest explanation > for this problem would be that you're running different test perlu code > or running it on different machines/DBs when connecting via TCP than > when connecting via unix socket. > > If that's not the case, about the only thing I can imagine is some weird > kernel security module getting in the way. Maybe something that tries to > isolate processes started as a result of tcp/ip connections so they > can't make new connections themselves, or something crazy like that. You > wouldn't happen to be on a box with SELinux/Apparmor/TOMOYO/SMACK/etc, > would you? > > -- > Craig Ringer > > -- > Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-bugs > >
addendum ,I can run my test code on a windows postgreSQL(8.4) with tcp/ip connection. ----- Original Message ----- From: "Craig Ringer" <craig@postnewspapers.com.au> To: "李紅兵" <lihongbing@intasect.com.cn> Cc: "John R Pierce" <pierce@hogranch.com>; <pgsql-bugs@postgresql.org> Sent: Wednesday, May 11, 2011 2:17 PM Subject: Re: [BUGS] BUG #5994: Can't excute DBI->connect to oracle by client site > On 11/05/11 13:53, 李紅兵 wrote: >> Perhaps you misunderstanding what I mean. >> I can connect to postgreSQL server correctly, >> but When I connect postgreSQL with TCP Sockets, it return error to >> connect to oracle server by DBI->connect from perlu script. >> >> When I connect postgreSQL with Unix Domain Sockets,it works well. > > Are you _certain_ that you're connecting to the same database on the > same machine when you use TCP vs unix sockets? The simplest explanation > for this problem would be that you're running different test perlu code > or running it on different machines/DBs when connecting via TCP than > when connecting via unix socket. > > If that's not the case, about the only thing I can imagine is some weird > kernel security module getting in the way. Maybe something that tries to > isolate processes started as a result of tcp/ip connections so they > can't make new connections themselves, or something crazy like that. You > wouldn't happen to be on a box with SELinux/Apparmor/TOMOYO/SMACK/etc, > would you? > > -- > Craig Ringer > > -- > Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-bugs > >
On 05/11/2011 03:13 PM, æç´ å µ wrote: > yes,I am sure that I connect to the same database and same machine. > because I run my test code at the linux server with telnet. > > As you said,it likes some security modules inhibit to make a new > connection. > My server is Redhat Linux Enterprice version with typical > installation.and I don't know about > SELinux/Apparmor/TOMOYO/SMACK If you're running on RHEL, you're using SELinux. For a test, you could set SELinux policy to audit mode (ie: not enforcing) and see if you still have the problem. If the problem goes away that way, you know you need to tweak your SELinux policies or set an SELinux boolean. See the SELinux documentation to learn more. -- Craig Ringer
I found this problem two weeks ago in a project,and then go to other team.Environment and machine has been changed after. so I cannot test now.Colleague told me this problem has been resolved by other pepole. I read the config of selinux.It changed as you said. SELINUX=enforcing changed to SELINUX=disabled Have no test,but I think this is the reason why I cannot execute DBI-connect in my program. Thank you very mach. regards, Li Hongbing ----- Original Message ----- From: "Craig Ringer" <craig@postnewspapers.com.au> To: "李紅兵" <lihongbing@intasect.com.cn> Cc: "John R Pierce" <pierce@hogranch.com>; <pgsql-bugs@postgresql.org> Sent: Wednesday, May 11, 2011 5:18 PM Subject: Re: [BUGS] BUG #5994: Can't excute DBI->connect to oracle by client site > On 05/11/2011 03:13 PM, 李紅兵 wrote: >> yes,I am sure that I connect to the same database and same machine. >> because I run my test code at the linux server with telnet. >> >> As you said,it likes some security modules inhibit to make a new >> connection. >> My server is Redhat Linux Enterprice version with typical >> installation.and I don't know about >> SELinux/Apparmor/TOMOYO/SMACK > > If you're running on RHEL, you're using SELinux. > > For a test, you could set SELinux policy to audit mode (ie: not enforcing) and see if you still have the problem. If theproblem > goes away that way, you know you need to tweak your SELinux policies or set an SELinux boolean. > > See the SELinux documentation to learn more. > > -- > Craig Ringer > > -- > Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-bugs > >