sqlrelay-discussion — Discussion of topics related to SQL Relay

[Sqlrelay-discussion] compiling SQLRelay with Oracle 10g thin client

[Seth G. <sgo...@op...>]

Hi,

I am trying to run ./configure and include Oracle 10g support.  I have 
installed the Oracle thin client (plus a bunch of additional libraries 
and headers that I needed to install compile DCOracle2 with) and It does 
not seem to work.  Could you tell me what libraries it is looking for?

Here is my ./configure command:

./configure --with-oracle-home=$ORACLE_HOME

where $ORACLE_HOME has the following:

classes12.jar      libnnz10.so      libsqlplusic.so  sdk
glogin.sql         libocci.so.10.1  libsqlplus.so    sqlplus
lib                libociei.so      ojdbc14.jar      tnsnames.ora
libclntsh.so.10.1  libocijdbc10.so  rdbms
libiodbc.so        libodbc.so       README_IC.htm

lib:
cdemo81.c      libclntsh.so.10.1  libocci.so.10.1  libsqlplus.so 
ojdbc14.jar
classes12.jar  libheteroxa10.so   libociei.so      occidemod.sql 
orai18n.jar
demo.mk        libiodbc.so        libocijdbc10.so  occidemo.sql   sysliblist
glogin.sql     libnnz10.so        libodbc.so       occidml.cpp
libclntsh.so   libocci.so         libsqlplusic.so  ocrs12.jar


(I recognize the redundancy)

Anything else I should add?

BTW, I didn't have a sysliblist in my lib directory so I copied it from 
my sdk directory.

--Seth

-- 
Seth Gottlieb
Optaros, Inc.
60 Canal Street
Boston, MA 02114
e: sgo...@op...
v: 617.227.1855 ext. 107
f: 617.227.1755
m: 617.852.2956

[Sqlrelay-discussion] SQLRelay install issue

[Vecchio, P. <PVe...@or...>]

So close! Can't quite figure out the last leg of this install.
=20
I have freetds-0.63 working - tested with the tsql command.
=20
unixODBC working -tested with the isql command.
=20
sqlr - working:
=20
some@here bin]# sqlr-start -id mssql
=20
Starting listener:
  sqlr-listener -id mssql -config =
/usr/local/firstworks/etc/sqlrelay.conf
=20
Starting 3 connections to db1 :
  sqlr-connection-freetds -id mssql -connectionid db1 -config =
/usr/local/firstworks/etc/sqlrelay.conf
  sqlr-connection-freetds -id mssql -connectionid db1 -config =
/usr/local/firstworks/etc/sqlrelay.conf
  sqlr-connection-freetds -id mssql -connectionid db1 -config =
/usr/local/firstworks/etc/sqlrelay.conf
=20
Starting scaler:
  sqlr-scaler  -id mssql -config /usr/local/firstworks/etc/sqlrelay.conf
=20
Starting cache manager:
  sqlr-cachemanager
=20

Thanks to MP3.com for sponsoring:=20
        Clustered/Replicated database support.
        Perl API.
=20
All running on RedHad Linux Enterprise.  Trying to finish connecting to =
MSSQL Server.
=20
When I installed SQLRelay, it put ZSQLRelayDA in /lib/python/Products.  =
I read somewhere that SQLRelay (in /lib/python/Products/ZSQLRelayDA/) =
needs to be in /lib/python.  So I tried it and have gotten this far.  =
Now when I restart zope with zopectl fg - this is the error I'm seeing:
=20
2005-10-20T13:32:29 ERROR(200) Zope Could not import =
Products.ZSQLRelayDA
Traceback (most recent call last):
  File "/usr/src/zope-2.7.7/lib/python/OFS/Application.py", line 673, in =
import_product
    product=3D__import__(pname, global_dict, global_dict, silly)
  File =
"/usr/src/zope-2.7.7/lib/python/Products/ZSQLRelayDA/__init__.py", line =
7, in ?
    import DA
  File "/usr/src/zope-2.7.7/lib/python/Products/ZSQLRelayDA/DA.py", line =
7, in ?
    from db import DB
  File "/usr/src/zope-2.7.7/lib/python/Products/ZSQLRelayDA/db.py", line =
1, in ?
    import SQLRelay, DateTime
  File "/usr/src/zope-2.7.7/lib/python/SQLRelay/__init__.py", line 4, in =
?
    import PySQLRDB
  File "/usr/src/zope-2.7.7/lib/python/SQLRelay/PySQLRDB.py", line 10, =
in ?
    from SQLRelay import CSQLRelay
  File "/usr/src/zope-2.7.7/lib/python/SQLRelay/SQLRelay/__init__.py", =
line 4, in ?
    import PySQLRDB
  File "/usr/src/zope-2.7.7/lib/python/SQLRelay/SQLRelay/PySQLRDB.py", =
line 10, in ?
    from SQLRelay import CSQLRelay
ImportError: libsqlrclient-0.36.4.so.1: cannot open shared object file: =
No such file or directory
Traceback (most recent call last):
  File "/usr/src/zope-2.7.7/lib/python/Zope/Startup/run.py", line 50, in =
?
    run()
  File "/usr/src/zope-2.7.7/lib/python/Zope/Startup/run.py", line 19, in =
run
    start_zope(opts.configroot)
  File "/usr/src/zope-2.7.7/lib/python/Zope/Startup/__init__.py", line =
52, in start_zope
    starter.startZope()
  File "/usr/src/zope-2.7.7/lib/python/Zope/Startup/__init__.py", line =
231, in startZope
    Zope.startup()
  File "/usr/src/zope-2.7.7/lib/python/Zope/__init__.py", line 47, in =
startup
    _startup()
  File "/usr/src/zope-2.7.7/lib/python/Zope/App/startup.py", line 45, in =
startup
    OFS.Application.import_products()
  File "/usr/src/zope-2.7.7/lib/python/OFS/Application.py", line 650, in =
import_products
    import_product(product_dir, product_name, raise_exc=3Ddebug_mode)
  File "/usr/src/zope-2.7.7/lib/python/OFS/Application.py", line 673, in =
import_product
    product=3D__import__(pname, global_dict, global_dict, silly)
  File =
"/usr/src/zope-2.7.7/lib/python/Products/ZSQLRelayDA/__init__.py", line =
7, in ?
    import DA
  File "/usr/src/zope-2.7.7/lib/python/Products/ZSQLRelayDA/DA.py", line =
7, in ?
    from db import DB
  File "/usr/src/zope-2.7.7/lib/python/Products/ZSQLRelayDA/db.py", line =
1, in ?
    import SQLRelay, DateTime
  File "/usr/src/zope-2.7.7/lib/python/SQLRelay/__init__.py", line 4, in =
?
    import PySQLRDB
  File "/usr/src/zope-2.7.7/lib/python/SQLRelay/PySQLRDB.py", line 10, =
in ?
    from SQLRelay import CSQLRelay
  File "/usr/src/zope-2.7.7/lib/python/SQLRelay/SQLRelay/__init__.py", =
line 4, in ?
    import PySQLRDB
  File "/usr/src/zope-2.7.7/lib/python/SQLRelay/SQLRelay/PySQLRDB.py", =
line 10, in ?
    from SQLRelay import CSQLRelay
ImportError: libsqlrclient-0.36.4.so.1: cannot open shared object file: =
No such file or directory

=20
libsqlrclient-0.36.r.so.1 is in /usr/local/firstworks/lib - this path is =
also in ld.so.conf  (of which I removed ld.so.cache and ran ldconfig)
=20
Yet I'm still getting this "cannot open shared object file" error.
=20
Can anyone help shed some light on this for me?  I would be eternally =
grateful.
=20
Thanks,


Peppi Vecchio | Web Developer=20
TECHNOLOGY SERVICES GROUP=20

=20


_________________________________________________________

The information contained in this message is privileged, confidential =
and=20
intended only for use of the individual or entity addressed above.  If =
you=20
have received this communication in error, please immediately notify us
by reply and delete the same.  Thank you.=20

Re: [Sqlrelay-discussion] UTF-8 with oracle and SQL Relay

[Maciej W. <mac...@co...>]

Hi

> I have some information stored in a data base oracle
> with charset utf-8, when I search these information,
> them to return with the characters "????",  for
> example

I'm using Oracle and SQLRelay with utf-8 and I have no such problems.
Do you have NLS_LANG system variable set correctly?

-- 
Maciej Wisniowski

[Sqlrelay-discussion] FYI: compile errror sqlrelay 0.37pre2

[Paul D. W. <pdw...@qu...>]

Hi All,

I just tried compiling 0.37pre2 on a machine that had no complaints with
0.36.4 and got the following error:

===
In file included from debugfile.C:4:
debugfile.h:22: type specifier omitted for parameter
debugfile.h:22: parse error before `)'
debugfile.h:25: type specifier omitted for parameter
debugfile.h:25: parse error before `)'
debugfile.h:26: type specifier omitted for parameter
debugfile.h:26: parse error before `)'
debugfile.C: In method `void debugfile::debugPrint (const char *, int,
int)':
debugfile.C:74: call of overloaded `write(char *&, int32_t &, int32_t
&)' is ambiguous
/usr/local/site/firstworks/include/rudiments/logger.h:116: candidates are:
void
rudiments::logger::write (const char *, int, char)
/usr/local/site/firstworks/include/rudiments/logger.h:119:
void
rudiments::logger::write (const char *, int, long int)
/usr/local/site/firstworks/include/rudiments/logger.h:122:
void
rudiments::logger::write (const char *, int, double)
debugfile.C: At top level:
debugfile.C:84: type specifier omitted for parameter
debugfile.C:84: parse error before `)'
debugfile.C: In method `void debugfile::debugPrintBlob (...)':
debugfile.C:91: `uint32_t' undeclared (first use this function)
debugfile.C:91: (Each undeclared identifier is reported only once for
each function it appears in.)
debugfile.C:91: parse error before `='
debugfile.C:91: `i' undeclared (first use this function)
debugfile.C:91: `length' undeclared (first use this function)
debugfile.C:92: `blob' undeclared (first use this function)
debugfile.C: At top level:
debugfile.C:110: type specifier omitted for parameter
debugfile.C:110: parse error before `)'
debugfile.C: In method `void debugfile::debugPrintClob (...)':
debugfile.C:116: parse error before `='
debugfile.C:117: `clob' undeclared (first use this function)
===

I checked the file is was complaining about (debugfile.h) and did a diff
against the previous version

===
diff debugfile.h ../../../sqlrelay-0.36.4/src/util/debugfile.h
17,26c17,22
<               void    debugPrint(const char *name, int32_t tabs,
<                                                       const char *string);
<               void    debugPrint(const char *name, int32_t tabs,
<                                                       int32_t number);
<               void    debugPrint(const char *name, int32_t tabs,
<                                                       uint32_t number);
<               void    debugPrint(const char *name, int32_t tabs,
<                                                       double number);
<               void    debugPrintBlob(const char *blob, uint32_t length);
<               void    debugPrintClob(const char *clob, uint32_t length);
---
>               void    debugPrint(const char *name, int tabs,
>                                               const char *string);
>               void    debugPrint(const char *name, int tabs, long number);
>               void    debugPrint(const char *name, int tabs, double
number);
>               void    debugPrintBlob(const char *blob, unsigned long
length);
>               void    debugPrintClob(const char *clob, unsigned long
length);
===

where int was refined as int32_t.  The compiler here doesnt like it.

Does anyone have any suggestions on how I can get around this problem?

The system is a Redhat 7.3 based system using gcc 2.96.

Any help would be appreciated.

- Paul

[Sqlrelay-discussion] FreeTDS "new operation while results pending" or something like that...

[David M. <dav...@fi...>]

Hello SQL Relay'ers,

For a while now, people have been running into a problem using FreeTDS
where they get an error like:

"Attempt to initiate a new SQL Server operation with results pending."

I finally discovered the cause of the problem last night.

Most databases support a connection/cursor paradigm where multiple
queries can be run and multiple result sets can be accessed
simultaneously through a single connection, using separate cursors for
each result set.

Sybase and MSSQLServer's native API's support this paradigm too, but
they also support running a query without a cursor.

FreeTDS currently only supports running a query without a cursor.  You
can apparently have multiple "statements" active at the same time, but
you cannot execute one statement while another statement's result set is
still open.  I had previously confused FreeTDS statements with cursors,
but now I understand.  And it has interesting consequences.

For instance, you really can't run nested queries using FreeTDS using a
single connection.  Ie, you can't "select * from mytable" and then step
through the result set, inserting rows into another table.  Or, it would
surprise me if you could.

At any rate, what's happening here is:

When it's time to run a query, the sqlr-connection-freetds deamon grabs
a cursor, closes the currently open result set of the previous query (if
there was one) for that cursor and runs the query.

The sqlr-connection-freetds deamon also keeps track of whether an
insert/update/delete was run and runs a commit when the client exits if
it was by allocating a new cursor, running the commit and closing the
cursor.

Whenever a new cursor is allocated, that cursor runs a "use" query to
select the proper database to use.

So, if an SQL Relay client runs an insert, then runs a select, then
closes the connection, the sqlr-connection-freetds daemon will attempt
to run a commit.  It will allocate a new cursor and try to run the "use"
query, but alas, the select's result set is still open, and since the
select isn't attached to a FreeTDS cursor, just to a FreeTDS statement,
it's really attached to a FreeTDS connection, and so is the "use" query,
thus the error:  we're attempting to initiate a new SQL Server operation
(the "use" query) with the results of the previous select still pending.



Understanding the problem doesn't exactly tell me how to solve it
though.  Fundamentally, FreeTDS needs to support cursors, or I need to
totally rewrite SQL Relay's freetds driver to do everything through the
connection and simulate multiple cursors by caching result sets or
something.  FreeTDS does have the ct_cursor function and all of the
associated macros, but they don't appear to work.  Or at least didn't
last time I tried, code that worked using Sybase's native ctlib doesn't
work against FreeTDS.  Programs like sqsh that can use FreeTDS only
allow you to run 1 query at a time. They don't even use cursors, so I
can't really look at any example code from there.

Anyway, I just wanted to keep folks informed.  I'm going to look into
the issue further.  Now that I know the problem, I'll find a solution.

In the mean time, there are some workarounds that might be helpful:

* If you do an insert/updtate/delete, doing a manual commit before the
client exits.

* Otherwise, do insert/update/delete's in separate transactions or
sessions from select's.

* If you have to do nested queries, such as doing a select, then
iterating over the result set and doing inserts, try writing a stored
procedure to do it.

Hope this helps, 

Dave
dav...@fi...

Re: [Sqlrelay-discussion] Oracle Ref Cursors created in Stored Procedures

[Matt F. <ma...@eq...>]

I am experiencing similar behavior.

I am also using sqlrcur_getOutputBindCursor but I have not yet 
determined if this is the (only) cause of open cursors on the oracle 
server.  Using PHP OCI I typically see around 8000 open cursors on one 
database for a particular db user.  Using sqlr I watched the open 
cursors climb to 20000+ before shutting down sqlrelay.  Oracle version 
is 9.2.0.5.

Here is my instance configuration in sqlrelay.conf:
<instance id="dbinstance" port="9000" socket="/tmp/dbinstance.socket" 
dbase="oracle8" connections="50" maxconnections="200" maxqueuelength="0" 
growby="5" ttl="60" endofsession="commit" sessiontimeout="600" 
runasuser="sqlrelay" runasgroup="sqlrelay" cursors="3" 
authtier="listener" handoff="pass" deniedips="" allowedips="" debug="none">

If cursors are left open on the Oracle server, does this mean a cursor 
is left hanging on the sqlrelay server?  Meaning that my php app can no 
longer use that sqlrelay cursor?  The behavior I am experiencing is that 
my sqlrelay connections are eventually all used up and my php app can no 
longer connect to the sqlrelay servers.  With my current configuration 
the increase of sqlr-connection processes running on the server is 
progressive even though the web server traffic is relatively stable.  My 
50 initial connections last maybe 15 minutes before they scale up and 
after about an hour to an hour and a half, all 200 sqlr-connection 
processes are running and the site can no longer connect to sqlr.

I have taken extreme care, per 
http://sqlrelay.sourceforge.net/sqlrelay/faq.html#php, to make sure ALL 
open sqlr connections and cursors are explicitly closed through calls to 
sqlrcur_free() and sqlrcon_free() before the php scripts end and I am 
positive this is not the issue.

Any suggestions or further troubleshooting steps are greatly appreciated.

Thanks,

Matt




Firstworks/4access wrote:

>Looks like the server-side isn't cleaning up those cursors properly.
>I'll take a look and see if I can figure out what's going on.
>
>Dave
>
>On Thu, 2005-09-22 at 11:01 -0700, Dennis Bolton wrote:
>  
>
>>Hi,
>>
>>When running the code below repeatedly I find that the
>>cursor created in 
>>
>> 	$returncur = sqlrcur_getOutputBindCursor($cur,'returncur');
>>
>>is never closed on the oracle server.  After enough iterations the
>>server will error with to many open cursors.
>>
>>I am running sqlrelay 3.6.4, but the same results occur on 3.6.2.
>>PHP version is 4.4 . Oracle is 9.2g
>>
>>Here is a couple of consecutive sample results from this code:
>>
>>1.
>>there are 711 open cursors
>>Array
>>(
>>    [StudentSortOrder] => 0
>>    [ClassID] => 818
>>    [Class] => Best Wisshes
>>    [ClassDesc] => 
>>    [GradeLevel] => 4
>>    [ActiveStudents] => 1
>>)
>>there are 712 open cursors
>>
>>2.
>>there are 712 open cursors
>>Array
>>(
>>    [StudentSortOrder] => 0
>>    [ClassID] => 818
>>    [Class] => Best Wisshes
>>    [ClassDesc] => 
>>    [GradeLevel] => 4
>>    [ActiveStudents] => 1
>>)
>>there are 713 open cursors
>>
>>
>>
>>What might I be doing wrong?
>>
>>
>>*************************************************************************************
>><pre>
>><?php
>>
>>$conn =
>>sqlrcon_alloc('localhost','','/tmp/emgames.socket','webuser','thisisit',0,1);
>>//print_r($conn);
>>
>>//print_r($relay);
>>
>>$countquery = "select count(*) from sys.gv_\$open_cursor";
>>
>>$qcur = sqlrcur_alloc($conn);
>>sqlrcur_sendquery($qcur,$countquery);
>>sqlrcon_endSession($conn);
>>
>>$rowarray=sqlrcur_getRow($qcur,0);
>>
>>echo "there are {$rowarray[0]} open cursors\n";
>>
>>$procedure = "BEGIN sa.s_classes_teacher(2559,:returncur); END;";
>>
>>if($cur = sqlrcur_alloc($conn)){
>>
>>	sqlrcur_prepareQuery($cur,$procedure);
>>
>>	sqlrcur_defineOutputBindCursor($cur,'returncur');
>>
>>	sqlrcur_executeQuery($cur);
>>  
>>	$returncur = sqlrcur_getOutputBindCursor($cur,'returncur');
>>  
>>	sqlrcur_fetchFromBindCursor($returncur);
>>
>>
>>	$numrows = sqlrcur_rowCount($returncur);
>>	$numcolumns = sqlrcur_colCount($returncur);
>>      
>>      
>>	// Get the column names and types
>>	for($i = 0; $i < $numcolumns; $i++){
>>		$columnnames[$i] = sqlrcur_getColumnName($returncur,$i);
>>		$columntypes[$i] = sqlrcur_getColumnType($returncur,$i);
>>	}
>>
>>	// Get each row column by column and store each of them in the Results
>>array
>>	for($i = 0 ; $i < $numrows  ; $i++) {
>>		for($j = 0 ; $j < $numcolumns; $j++){
>>			$results[$i][$columnnames[$j]] = sqlrcur_getField($returncur,$i,$j);
>>		}
>>	}
>>  print_r($results[0]);    
>>	sqlrcon_endsession($conn);
>>	sqlrcur_clearbinds($returncur);
>>	sqlrcur_free($returncur);
>>	sqlrcur_clearbinds($cur);
>>	sqlrcur_free($cur);
>>
>> }
>> else {
>>	 echo "couldn't open cursor";
>> }
>>
>>sqlrcur_sendquery($qcur,$countquery);
>>sqlrcon_endSession($conn);
>>
>>
>>$rowarray=sqlrcur_getRow($qcur,0);
>> 
>>echo "there are {$rowarray[0]} open cursors";
>>
>>
>>sqlrcur_free($qcur);
>>sqlrcon_free($conn);
>>?>
>></pre>
>>*********************************************************************
>>    
>>
>
>
>
>-------------------------------------------------------
>SF.Net email is sponsored by:
>Tame your development challenges with Apache's Geronimo App Server. Download
>it for free - -and be entered to win a 42" plasma tv or your very own
>Sony(tm)PSP.  Click here to play: http://sourceforge.net/geronimo.php
>_______________________________________________
>Sqlrelay-discussion mailing list
>Sql...@li...
>https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
>
>  
>

Re: [Sqlrelay-discussion] sqlrelay memory leak

[<dl...@ti...>]

sql-listener grows by 4 bytes, i use latest version. I'm not in office for
next week or so, so i cant do more testing at the moment.

Dario

> Dario,
>
> Does the sqlr-listener daemon grow by 4 bytes or the sqlr-connection
> daemon?
>
> There must be a memory leak somewhere.  What version are you using?
>
> Dave
> dav...@fi...
>
> On Fri, 2005-10-07 at 15:28 +0200, Dario Lah wrote:
>> Whenver I use endSession listener takes about 4 bytes of memory. This
>> happens in PHP and perl. If no one knows reason for this i can do some
>> testing later (in a week or so).
>>
>> Regards,
>> Dario
>> --
>> Now this foreknowledge cannot be elicited from spirits
>> it cannot be obtained inductively from experience
>> nor by any deductive calculation
>>          --The Art of War by Sun Tz
>>          Chapter XIII: The Use of Spie
>
>
>
> -------------------------------------------------------
> This SF.Net email is sponsored by:
> Power Architecture Resource Center: Free content, downloads, discussions,
> and more. http://solutions.newsforge.com/ibmarch.tmpl
> _______________________________________________
> Sqlrelay-discussion mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
>

Re: [Sqlrelay-discussion] about PEAR DB and PHP

[Firstworks/4access <dav...@fi...>]

If you're using PHP -> Pear DB -> MySQL now and want to convert it to
PHP -> Pear DB -> SQL Relay -> MySQL, then you shouldn't have to change
anything but the connection parameters.

However, if you're also switching out the back end database, then you'll
probably have to rewrite some of your queries as different databases
support slightly different SQL commands.

Some people have recently reported some PHP Pear DB bugs that have been
fixed since the 0.36.4 release and will be available in the upcoming
0.37 release.  Only a few people have run into them though, so it's not
all that likely that you will.  If you do find something that doesn't
work though, let me know and I'll get you a patch.

David Muse
dav...@fi...

On Tue, 2005-09-20 at 12:57 +0200, Arturo Gonzalez Ferrer wrote:
> Hello,
> 
> I'm involving in a project where I should migrate an application
> written in PHP with PEAR DB connecting to MySQL Server, to use
> SQLRelay. 
> 
> Could somebody tell me what issues would appear using the SQLRelay
> PEAR API? Are the functions names equal? Or a rewrite of all the
> queries is needed?
> 
> Cheers,
> 
> Arturo.

Re: [Sqlrelay-discussion] connection scaling, not dieing off after ttl elapsed

[Matt F. <ma...@eq...>]

sqlrelay-0.36.4

Firstworks/4access wrote:

>That sounds familiar, but I think I remember fixing that.  What version
>of SQL Relay are you using?
>
>  
>
>David Muse
>dav...@fi...
>
>On Tue, 2005-10-04 at 11:59 -0400, Matt Flynn wrote:
>  
>
>>None of my dynamic connections spawned with the '-ttl' argument are 
>>dieing off.  Even after several hours of sqlrelay receiving no traffic, 
>>I have 'sqlr-connection-oracle8 -ttl 120 ...' processes still running.
>>
>>Any suggestions?
>>
>>Thanks,
>>
>>Matt
>>
>>
>>-------------------------------------------------------
>>This SF.Net email is sponsored by:
>>Power Architecture Resource Center: Free content, downloads, discussions,
>>and more. http://solutions.newsforge.com/ibmarch.tmpl
>>_______________________________________________
>>Sqlrelay-discussion mailing list
>>Sql...@li...
>>https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
>>
>>    
>>
>
>
>
>-------------------------------------------------------
>This SF.Net email is sponsored by:
>Power Architecture Resource Center: Free content, downloads, discussions,
>and more. http://solutions.newsforge.com/ibmarch.tmpl
>_______________________________________________
>Sqlrelay-discussion mailing list
>Sql...@li...
>https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
>
>  
>

Re: [Sqlrelay-discussion] Sqlrelay with Oracle and large numbers of connections with Ruby-DBI

[Firstworks/4access <dav...@fi...>]

Your config file looks good.

Are a bunch of sqlr-connection-oracle8 processes getting created, or a
bunch of sqlr-listener processes?

One sqlr-listener process gets created for each client that connects.
That sqlr-listener process dies off when the client has been handed off
to an sqlr-connection-oracle8 daemon.  If lots of sqlr-listener
processes are getting created, then it could mean that lots of clients
are connecting for some reason, or that the sqlr-connection-oracle8
processes have gotten hung up.

If a bunch of sqlr-connection-oracle8 processes are getting created,
then there could be a bug in the sqlr-scaler process which spawns them.

Let me know whether sqlr-listener or sqlr-connection-oracle8 processes
are getting created and we can proceed from there.

Dave
dav...@fi...

On Mon, 2005-09-26 at 15:30 +0800, Rafael 'Dido' Sevilla wrote:
> We have a Ruby application running on Oracle 9i and are using Sqlrelay
> as a middleware layer to improve our application's efficiency.  Using it
> has given us a 20%-25% speed boost in our project's efficiency over
> using the OCI8 Ruby DBI module directly.  However, we have been
> experiencing some strange problems that manifest during times that there
> is heavy database load.  When very many parallel connections are made to
> the Sqlrelay daemon, eventually, the number of Sqlrelay processes shoots
> up, way, way beyond the maxconnections limit that we imposed (sometimes
> reaching 500 processes or even more; we have it configured at 30), and
> when we terminate the application (if we don't get to in time this
> inevitably leads to a system crash as virtual memory is exhausted),
> Sqlrelay is totally unusable for connecting to the database: sqlrsh
> simply hangs indefinitely.  The system basically runs small stub
> programs from time to time to perform small database updates, so when it
> connects to Sqlrelay, it freezes, and so both our stub program instances
> and Sqlrelay connection instances proliferate rapidly.  Through all
> this, it remains possible to connect to the database directly using
> something like sqlplus.
> 
> We are using sqlrelay-0.36.1, Oracle Instant Client 10.1.0.3, Ruby
> 1.8.2, Ruby-DBI 0.0.21 on Red Hat Enterprise 3.0, kernel
> 2.4.21-27.ELsmp.  The Oracle is on Solaris 9, version 9.2.0.6.0.  Our
> sqlrelay.conf looks like this (with hostnames and passwords rubbed out):
> 
> <?xml version="1.0"?>
> <!DOCTYPE instances SYSTEM "sqlrelay.dtd">
> <instances>
> 
>         <instance id="pacer-oracle" port="9000"
> socket="/tmp/oracletest.socket" dbase="oracle8" connections="3"
> maxconnections="30" maxqueuelength="0" growby="1" ttl="60"
> endofsession="commit" sessiontimeout="600" runasuser="nobody"
> runasgroup="nobody" cursors="5" authtier="listener" handoff="pass">
>                 <users>
>                         <user user="foo" password="bar"/>
>                 </users>
>                 <connections>
>                         <connection connectionid="oracle-db"
> string="user=XXXXX;password=XXXX;oracle_sid=XXXXX;oracle_home=/usr/lib/oracle/10.1.0.3/client"
> metric="1"/>
>                 </connections>
>         </instance>
> 
> </instances>
> 
> Is there something fishy with our config?
> 

Re: [Sqlrelay-discussion] Oracle bounces causing dead connections

[Todd F. <fr...@an...>]

On Tue, Oct 11, 2005 at 11:28:02AM -0400, Firstworks/4access wrote:
> What versions of Oracle SQL Relay are you running?
>=20

SQLRelay: sqlrelay-0.36.4.tar.gz

Oracle: Oracle9i Enterprise Edition Release 9.2.0.6.0

> I just tried to reproduce this problem using Oracle 10g and current SQL
> Relay, using a similar setup to yours (including authtier=3D"database").
>=20
> When I bounced my database, the SQL Relay connection daemons recovered
> gracefully and I was able to run queries against it again.

The odd part was that it appeared that only 1 connection had the problem...=
 I could run the script 3 at a time and randomly one would have the issue (=
because of the connection it was attached to) while the others ran just fin=
e.

>=20
> The connection daemons analyze each error that comes back from the
> database and recognize certain errors as meaning that the database was
> bounced.  It's possible that I missed one of those errors and that the
> connection daemon hasn't realized that the database is down and hasn't
> attempted to reconnect to it.

Only odd thing I can think of is that instead of just downing the database =
the DBA took the listener offline first and then did the shutdown. But that=
 shouldn't be that uncommon of a procedure.

>=20
> If I send you some code with debug in it, can you rebuild/reinstall SQL
> Relay and try to reproduce the problem again?

I can do that :}  I happen to have gotten real good at building it on evil =
solaris :}

>=20
> Dave
> dav...@fi...
>=20
> On Thu, 2005-10-06 at 16:26 -0400, Todd Freeman wrote:
> > Howdy all!
> >=20
> > Am having great fun with sqlrelay...  works great... except (you knew t=
hat was coming didn't you :} )  when the database bounces I am getting at l=
east 1 connection that 'hangs'.
> >=20
> > The symptoms are that some of my perl progs just run on fine...  but ot=
hers connecting to this connection get 'Broken Pipe'
> >=20
> > Below are the conf... the logs from the connection and the listener it =
says it is debugging to. The only odd thing I see is the 'invalid login/pas=
sword' message... (and yes... it is using the same script using the same us=
ername and password that the working ones are using)
> >=20
> > Any help would be appreciated! Ohh yeah... and ROCK ON!
> >=20
> > ##### Relevant conf #####
> >         <instance id=3D"oracleTEST" port=3D"9000" socket=3D"/tmp/oracle=
TEST.socket" dbase=3D"oracle8" connections=3D"2" maxconnections=3D"4" maxqu=
euelength=3D"0" growby=3D"2" ttl=3D"60" endofsession=3D"rollback" sessionti=
meout=3D"600" runasuser=3D"nobody" runasgroup=3D"nobody" cursors=3D"20" deb=
ug=3D"listener_and_connection" authtier=3D"database">
> >                 <users> </users>
> >                 <connections>
> >                         <connection connectionid=3D"its_proxy" string=
=3D"user=3Dits_proxy;password=3Dsecret;oracle_sid=3DTEST.world;autocommit=
=3Dno;" metric=3D"1"/>
> >                </connections>
> >         </instance>
> > #########################
> >=20
> > ##### /usr/local/firstworks/var/sqlrelay/debug/sqlr-connection.9268 ###=
##
> > Debugging to: /usr/local/firstworks/var/sqlrelay/debug/sqlr-listener.25=
739
> > 10/06/2005 16:11:50 EDT connection [9268] :     done waiting for listen=
er
> > 10/06/2005 16:11:50 EDT connection [9268] :     releasing announce mutex
> > 10/06/2005 16:11:50 EDT connection [9268] :     done releasing announce=
 mutex
> > 10/06/2005 16:11:50 EDT connection [9268] : done announcing availabilit=
y...
> > 10/06/2005 16:11:50 EDT connection [9268] : waiting for client...
> > 10/06/2005 16:11:50 EDT connection [9268] :     reconnect succeeded
> > 10/06/2005 16:11:50 EDT connection [9268] : done waiting for client
> > 10/06/2005 16:11:50 EDT connection [9268] : client session...
> > 10/06/2005 16:11:50 EDT connection [9268] :     getting command...
> > 10/06/2005 16:11:50 EDT connection [9268] :     done getting command
> > 10/06/2005 16:11:50 EDT connection [9268] :     authenticate
> > 10/06/2005 16:11:50 EDT connection [9268] :     authenticate...
> > 10/06/2005 16:11:50 EDT connection [9268] :     database-based authenti=
cation failed: invalid user/password
> > 10/06/2005 16:11:50 EDT connection [9268] :             ending session.=
=2E.
> > 10/06/2005 16:11:50 EDT connection [9268] :             aborting all bu=
sy cursors...
> > 10/06/2005 16:11:50 EDT connection [9268] :             done aborting a=
ll busy cursors
> > 10/06/2005 16:11:50 EDT connection [9268] :             resetting autoc=
ommit behavior...
> > 10/06/2005 16:11:50 EDT connection [9268] :                     setting=
 autocommit off...
> > 10/06/2005 16:11:50 EDT connection [9268] :                     done se=
tting autocommit off...
> > 10/06/2005 16:11:50 EDT connection [9268] :             done resetting =
autocommit behavior...
> > #########################
> >=20
> > ##### /usr/local/firstworks/var/sqlrelay/debug/sqlr-listener.25739 #####
> > 10/06/2005 16:11:50 EDT listener [25739] : getting authentication...
> > 10/06/2005 16:11:50 EDT listener [25739] : done getting authentication
> > 10/06/2005 16:11:50 EDT listener [25739] : incrementing session count...
> > 10/06/2005 16:11:50 EDT listener [25739] :      waiting for exclusive a=
ccess...
> > 10/06/2005 16:11:50 EDT listener [25739] :      done waiting for exclus=
ive access...
> > 10/06/2005 16:11:50 EDT listener [25739] :      3
> > 10/06/2005 16:11:50 EDT listener [25739] :      waiting for the scaler.=
=2E.
> > 10/06/2005 16:11:50 EDT listener [25739] :      done waiting for the sc=
aler...
> > 10/06/2005 16:11:50 EDT listener [25739] : done incrementing session co=
unt
> > 10/06/2005 16:11:50 EDT listener [25739] : getting a connection...
> > 10/06/2005 16:11:50 EDT listener [25739] : acquiring exclusive shm acce=
ss
> > 10/06/2005 16:11:50 EDT listener [25739] : done acquiring exclusive shm=
 access
> > 10/06/2005 16:11:50 EDT listener [25739] : waiting for an available con=
nection
> > 10/06/2005 16:11:50 EDT listener [25739] : done waiting for an availabl=
e connection
> > 10/06/2005 16:11:50 EDT listener [25739] :      handoff=3Dreconnect
> > 10/06/2005 16:11:50 EDT listener [25739] :      socket=3D/usr/local/fir=
stworks/var/sqlrelay/tmp/sockets/156  port=3D53973
> > 10/06/2005 16:11:50 EDT listener [25739] : signalling connection that w=
e've read
> > 10/06/2005 16:11:50 EDT listener [25739] : done signalling connection t=
hat we've read
> > 10/06/2005 16:11:50 EDT listener [25739] : releasing exclusive shm acce=
ss
> > 10/06/2005 16:11:50 EDT listener [25739] : done releasing exclusive shm=
 access
> > 10/06/2005 16:11:50 EDT listener [25739] :      done getting a connecti=
on
> > 10/06/2005 16:11:50 EDT listener [25739] : decrementing busy listeners
> > 10/06/2005 16:11:50 EDT listener [25739] : done decrementing busy liste=
ners
> > #########################
> >=20
> >=20
>=20
>=20
>=20
> -------------------------------------------------------
> This SF.Net email is sponsored by:
> Power Architecture Resource Center: Free content, downloads, discussions,
> and more. http://solutions.newsforge.com/ibmarch.tmpl
> _______________________________________________
> Sqlrelay-discussion mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion

--=20
Todd Freeman  Ext 6103                   .^.    Don't fear the penguins!
Programming Department                   /V\
Andrews University                      // \\    http://www.linux.org/
http://www.andrews.edu/~freeman/       /(   )\   http://www.debian.org/
                                        ^^ ^^

Re: [Sqlrelay-discussion] connection scaling, not dieing off after ttl elapsed

[Firstworks/4access <dav...@fi...>]

That sounds familiar, but I think I remember fixing that.  What version
of SQL Relay are you using?

David Muse
dav...@fi...

On Tue, 2005-10-04 at 11:59 -0400, Matt Flynn wrote:
> None of my dynamic connections spawned with the '-ttl' argument are 
> dieing off.  Even after several hours of sqlrelay receiving no traffic, 
> I have 'sqlr-connection-oracle8 -ttl 120 ...' processes still running.
> 
> Any suggestions?
> 
> Thanks,
> 
> Matt
> 
> 
> -------------------------------------------------------
> This SF.Net email is sponsored by:
> Power Architecture Resource Center: Free content, downloads, discussions,
> and more. http://solutions.newsforge.com/ibmarch.tmpl
> _______________________________________________
> Sqlrelay-discussion mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
> 

Re: [Sqlrelay-discussion] Oracle bounces causing dead connections

[Firstworks/4access <dav...@fi...>]

What versions of Oracle SQL Relay are you running?

I just tried to reproduce this problem using Oracle 10g and current SQL
Relay, using a similar setup to yours (including authtier="database").

When I bounced my database, the SQL Relay connection daemons recovered
gracefully and I was able to run queries against it again.

The connection daemons analyze each error that comes back from the
database and recognize certain errors as meaning that the database was
bounced.  It's possible that I missed one of those errors and that the
connection daemon hasn't realized that the database is down and hasn't
attempted to reconnect to it.

If I send you some code with debug in it, can you rebuild/reinstall SQL
Relay and try to reproduce the problem again?

Dave
dav...@fi...

On Thu, 2005-10-06 at 16:26 -0400, Todd Freeman wrote:
> Howdy all!
> 
> Am having great fun with sqlrelay...  works great... except (you knew that was coming didn't you :} )  when the database bounces I am getting at least 1 connection that 'hangs'.
> 
> The symptoms are that some of my perl progs just run on fine...  but others connecting to this connection get 'Broken Pipe'
> 
> Below are the conf... the logs from the connection and the listener it says it is debugging to. The only odd thing I see is the 'invalid login/password' message... (and yes... it is using the same script using the same username and password that the working ones are using)
> 
> Any help would be appreciated! Ohh yeah... and ROCK ON!
> 
> ##### Relevant conf #####
>         <instance id="oracleTEST" port="9000" socket="/tmp/oracleTEST.socket" dbase="oracle8" connections="2" maxconnections="4" maxqueuelength="0" growby="2" ttl="60" endofsession="rollback" sessiontimeout="600" runasuser="nobody" runasgroup="nobody" cursors="20" debug="listener_and_connection" authtier="database">
>                 <users> </users>
>                 <connections>
>                         <connection connectionid="its_proxy" string="user=its_proxy;password=secret;oracle_sid=TEST.world;autocommit=no;" metric="1"/>
>                </connections>
>         </instance>
> #########################
> 
> ##### /usr/local/firstworks/var/sqlrelay/debug/sqlr-connection.9268 #####
> Debugging to: /usr/local/firstworks/var/sqlrelay/debug/sqlr-listener.25739
> 10/06/2005 16:11:50 EDT connection [9268] :     done waiting for listener
> 10/06/2005 16:11:50 EDT connection [9268] :     releasing announce mutex
> 10/06/2005 16:11:50 EDT connection [9268] :     done releasing announce mutex
> 10/06/2005 16:11:50 EDT connection [9268] : done announcing availability...
> 10/06/2005 16:11:50 EDT connection [9268] : waiting for client...
> 10/06/2005 16:11:50 EDT connection [9268] :     reconnect succeeded
> 10/06/2005 16:11:50 EDT connection [9268] : done waiting for client
> 10/06/2005 16:11:50 EDT connection [9268] : client session...
> 10/06/2005 16:11:50 EDT connection [9268] :     getting command...
> 10/06/2005 16:11:50 EDT connection [9268] :     done getting command
> 10/06/2005 16:11:50 EDT connection [9268] :     authenticate
> 10/06/2005 16:11:50 EDT connection [9268] :     authenticate...
> 10/06/2005 16:11:50 EDT connection [9268] :     database-based authentication failed: invalid user/password
> 10/06/2005 16:11:50 EDT connection [9268] :             ending session...
> 10/06/2005 16:11:50 EDT connection [9268] :             aborting all busy cursors...
> 10/06/2005 16:11:50 EDT connection [9268] :             done aborting all busy cursors
> 10/06/2005 16:11:50 EDT connection [9268] :             resetting autocommit behavior...
> 10/06/2005 16:11:50 EDT connection [9268] :                     setting autocommit off...
> 10/06/2005 16:11:50 EDT connection [9268] :                     done setting autocommit off...
> 10/06/2005 16:11:50 EDT connection [9268] :             done resetting autocommit behavior...
> #########################
> 
> ##### /usr/local/firstworks/var/sqlrelay/debug/sqlr-listener.25739 #####
> 10/06/2005 16:11:50 EDT listener [25739] : getting authentication...
> 10/06/2005 16:11:50 EDT listener [25739] : done getting authentication
> 10/06/2005 16:11:50 EDT listener [25739] : incrementing session count...
> 10/06/2005 16:11:50 EDT listener [25739] :      waiting for exclusive access...
> 10/06/2005 16:11:50 EDT listener [25739] :      done waiting for exclusive access...
> 10/06/2005 16:11:50 EDT listener [25739] :      3
> 10/06/2005 16:11:50 EDT listener [25739] :      waiting for the scaler...
> 10/06/2005 16:11:50 EDT listener [25739] :      done waiting for the scaler...
> 10/06/2005 16:11:50 EDT listener [25739] : done incrementing session count
> 10/06/2005 16:11:50 EDT listener [25739] : getting a connection...
> 10/06/2005 16:11:50 EDT listener [25739] : acquiring exclusive shm access
> 10/06/2005 16:11:50 EDT listener [25739] : done acquiring exclusive shm access
> 10/06/2005 16:11:50 EDT listener [25739] : waiting for an available connection
> 10/06/2005 16:11:50 EDT listener [25739] : done waiting for an available connection
> 10/06/2005 16:11:50 EDT listener [25739] :      handoff=reconnect
> 10/06/2005 16:11:50 EDT listener [25739] :      socket=/usr/local/firstworks/var/sqlrelay/tmp/sockets/156  port=53973
> 10/06/2005 16:11:50 EDT listener [25739] : signalling connection that we've read
> 10/06/2005 16:11:50 EDT listener [25739] : done signalling connection that we've read
> 10/06/2005 16:11:50 EDT listener [25739] : releasing exclusive shm access
> 10/06/2005 16:11:50 EDT listener [25739] : done releasing exclusive shm access
> 10/06/2005 16:11:50 EDT listener [25739] :      done getting a connection
> 10/06/2005 16:11:50 EDT listener [25739] : decrementing busy listeners
> 10/06/2005 16:11:50 EDT listener [25739] : done decrementing busy listeners
> #########################
> 
> 

Re: [Sqlrelay-discussion] sqlrelay memory leak

[Firstworks/4access <dav...@fi...>]

Dario,

Does the sqlr-listener daemon grow by 4 bytes or the sqlr-connection
daemon?

There must be a memory leak somewhere.  What version are you using?

Dave
dav...@fi...

On Fri, 2005-10-07 at 15:28 +0200, Dario Lah wrote:
> Whenver I use endSession listener takes about 4 bytes of memory. This
> happens in PHP and perl. If no one knows reason for this i can do some
> testing later (in a week or so).
> 
> Regards,
> Dario
> --
> Now this foreknowledge cannot be elicited from spirits
> it cannot be obtained inductively from experience
> nor by any deductive calculation
>          --The Art of War by Sun Tz
>          Chapter XIII: The Use of Spie

Re: [Sqlrelay-discussion] Database Server Failover BUG

[Firstworks/4access <dav...@fi...>]

Thanks, I'll apply this fix and it'll be in the next release.

David Muse
dav...@fi...

On Fri, 2005-10-07 at 12:52 -0700, Cesar Peregrina wrote:
> I'm using sqlrelay to connect to MS-SQL thru freetds
> 
> When the DB Server goes down the I got this message:
> 
> ERROR sendQuery: Client Library error:
>         severity(78)
>         layer(0)
>         origin(0)
>         number(38)
> Error:  Write to SQL Server failed.
> 
> But the connection stay live and doesn't try to reconnect
> I look into the code and found that this error isn't considered
> A server fail for freetds only the read
> 
> src/connections/freetds/freetds.C: 
> 	CS_RETCODE freetdsconnection::csMessageCallback
> 	.
> 	.
> 	.
> 	// for a read from sql server failed message, set deadconnection
> to 1
>       } else if (CS_SEVERITY(msgp->msgnumber)==78 &&
>               CS_LAYER(msgp->msgnumber)==0 &&
>               CS_ORIGIN(msgp->msgnumber)==0 &&
>               CS_NUMBER(msgp->msgnumber)==36) {
>               deadconnection=true;
>       }
> 
> I think must be 
>       // for a read-from or write-to sql server failed message,
>       // set deadconnection to 1
>       } else if (CS_SEVERITY(msgp->msgnumber)==78 &&
>               CS_LAYER(msgp->msgnumber)==0 &&
>               CS_ORIGIN(msgp->msgnumber)==0 &&
>               (CS_NUMBER(msgp->msgnumber)==36 ||
>                CS_NUMBER(msgp->msgnumber)==38  )) {
>               deadconnection=true;
>       }
> 
> 
> And the same in 
> 
> 	CS_RETCODE freetdsconnection::clientMessageCallback
> 
> 
> Appreciate comments.
> 
> 	Thanx in advance
> 
> 
> -------------------------------------------------------
> This SF.Net email is sponsored by:
> Power Architecture Resource Center: Free content, downloads, discussions,
> and more. http://solutions.newsforge.com/ibmarch.tmpl
> _______________________________________________
> Sqlrelay-discussion mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
> 

Re: [Sqlrelay-discussion] UTF-8 with oracle and SQL Relay

[Celso J. R. R. <nun...@ya...>]

ok.......

I have some information stored in a data base oracle
with charset utf-8, when I search these information,
them to return with the characters "????",  for
example, the characters "...·¬ -|švª²¤m " 

Thanks!!!!

Celso Junior


--- Firstworks/4access <dav...@fi...>
escreveu:

> I think I'll need more information to be able to
> solve this problem.
> 
> Could you send an example query that fails and it's
> result set?
> 
> Thanks,
> 
> David Muse
> dav...@fi...
> 
> On Mon, 2005-10-10 at 16:32 -0300, Celso Junior
> Ramos Rodrigues wrote:
> > Hi!!!!
> > 
> > as I use ' sql relay ' and oracle with Utf-8 the
> > problem is that it is not returning the registers
> > correctly.
> > 
> > Thanks.........
> > 
> > Celso.
> 
> 
> 
>
-------------------------------------------------------
> This SF.Net email is sponsored by:
> Power Architecture Resource Center: Free content,
> downloads, discussions,
> and more.
> http://solutions.newsforge.com/ibmarch.tmpl
> _______________________________________________
> Sqlrelay-discussion mailing list
> Sql...@li...
>
https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
> 



	



	
		
_______________________________________________________ 
Promoção Yahoo! Acesso Grátis: a cada hora navegada você acumula cupons e concorre a mais de 500 prêmios! Participe! http://yahoo.fbiz.com.br/

Re: [Sqlrelay-discussion] SQL Server connections hanging

[Firstworks/4access <dav...@fi...>]

I have been looking into this lately, but I haven't figured out what's
going on yet.

I have a hunch that it has to do with the fact that Sybase and MS SQL
Server can return multiple result sets for a single query.  For example,
if you run a query which has output bind variables and a result set,
then the output bind variables are returned as one result set and the
rows and columns as another.

Maybe for queries where nocount isn't set on, some output bind variables
are getting implicitly generated and the main result set is getting
processed but the output bind variable result set isn't, and the attempt
to cancel it is failing too.

It seems like there was a time when this bug didn't occur.  Perhaps an
older version of FreeTDS didn't properly return both result sets and now
that it does, it exposes a bug in SQL Relay.

I'm still not sure, but fixing this is high on my list of priorities.

David Muse
dav...@fi...

On Mon, 2005-10-10 at 13:01 -0500, Dave Benjamin wrote:
> I've been trying to get Zope to talk to SQL Server using FreeTDS, SQL 
> Relay, and ZSQLRelayDA. I ran into a problem that apparently others have 
> experienced but nobody has been able to help with, so I'm posting this 
> message in hopes that it will help others understand the problem and 
> perhaps fix the bug.
> 
> I'm not sure if the bug is with FreeTDS or SQL Relay, so please let me 
> know if I should bring this up with the FreeTDS folks.
> 
> The basic problem is that any calls to "commit" or "rollback" after a 
> query operation result in an error message, "Attempt to initiate a new SQL 
> Server operation with results pending.", which is printed to the console. 
> Once this occurs, the connection is corrupted. This is particularly 
> unfortunate with Zope because the ZSQLRelayDA adapter calls "commit" or 
> "rollback" after every transaction, whether it was a query or otherwise. 
> The end result is that after three queries (or however many connections 
> SQL Relay is configured for), future queries hang and the whole site goes 
> down.
> 
> Apparently, as long as your last statement was not a query (ie. anything 
> other than SELECT), you can commit or roll back without crashing the 
> connection. My temporary fix was to alter the db.py file that comes with 
> ZSQLRelayDA to add some (hopefully harmless) dummy statements before the 
> commit or rollback:
> 
>      def _finish(self, *ignored):
>          self.cur.execute('SET NOCOUNT OFF') # hack
>          self.con.commit()
>          self.con.close()
> 
>      def _abort(self, *ignored):
>          self.cur.execute('SET NOCOUNT OFF') # hack
>          self.con.rollback()
>          self.con.close()
> 
> These two lines that end with "# hack" have solved the problem for me, at 
> least so far. However, now my database adapter is MSSQL-specific, since I 
> doubt this statement would work in any other database.
> 
> I don't know too much about how the underlying libraries work, but I 
> believe this problem has something to do with result sets not being freed 
> properly. I can get it to crash with something as simple as:
> 
> con = PySQLRDB.connect(...)
> cur = con.cursor()
> cur.execute('select * from users') # or some other table
> con.commit()
> 
> Thanks for your time,
> Dave
> 

Re: [Sqlrelay-discussion] UTF-8 with oracle and SQL Relay

[Firstworks/4access <dav...@fi...>]

I think I'll need more information to be able to solve this problem.

Could you send an example query that fails and it's result set?

Thanks,

David Muse
dav...@fi...

On Mon, 2005-10-10 at 16:32 -0300, Celso Junior Ramos Rodrigues wrote:
> Hi!!!!
> 
> as I use ' sql relay ' and oracle with Utf-8 the
> problem is that it is not returning the registers
> correctly.
> 
> Thanks.........
> 
> Celso.

[Sqlrelay-discussion] UTF-8 with oracle and SQL Relay

[Celso J. R. R. <nun...@ya...>]

Hi!!!!

as I use ' sql relay ' and oracle with Utf-8 the
problem is that it is not returning the registers
correctly.

Thanks.........

Celso.


	



	
		
_______________________________________________________ 
Promoção Yahoo! Acesso Grátis: a cada hora navegada você acumula cupons e concorre a mais de 500 prêmios! Participe! http://yahoo.fbiz.com.br/

[Sqlrelay-discussion] SQL Server connections hanging

[Dave B. <ra...@ra...>]

I've been trying to get Zope to talk to SQL Server using FreeTDS, SQL 
Relay, and ZSQLRelayDA. I ran into a problem that apparently others have 
experienced but nobody has been able to help with, so I'm posting this 
message in hopes that it will help others understand the problem and 
perhaps fix the bug.

I'm not sure if the bug is with FreeTDS or SQL Relay, so please let me 
know if I should bring this up with the FreeTDS folks.

The basic problem is that any calls to "commit" or "rollback" after a 
query operation result in an error message, "Attempt to initiate a new SQL 
Server operation with results pending.", which is printed to the console. 
Once this occurs, the connection is corrupted. This is particularly 
unfortunate with Zope because the ZSQLRelayDA adapter calls "commit" or 
"rollback" after every transaction, whether it was a query or otherwise. 
The end result is that after three queries (or however many connections 
SQL Relay is configured for), future queries hang and the whole site goes 
down.

Apparently, as long as your last statement was not a query (ie. anything 
other than SELECT), you can commit or roll back without crashing the 
connection. My temporary fix was to alter the db.py file that comes with 
ZSQLRelayDA to add some (hopefully harmless) dummy statements before the 
commit or rollback:

     def _finish(self, *ignored):
         self.cur.execute('SET NOCOUNT OFF') # hack
         self.con.commit()
         self.con.close()

     def _abort(self, *ignored):
         self.cur.execute('SET NOCOUNT OFF') # hack
         self.con.rollback()
         self.con.close()

These two lines that end with "# hack" have solved the problem for me, at 
least so far. However, now my database adapter is MSSQL-specific, since I 
doubt this statement would work in any other database.

I don't know too much about how the underlying libraries work, but I 
believe this problem has something to do with result sets not being freed 
properly. I can get it to crash with something as simple as:

con = PySQLRDB.connect(...)
cur = con.cursor()
cur.execute('select * from users') # or some other table
con.commit()

Thanks for your time,
Dave

-- 
  .:[ dave benjamin: ramen/[sp00] ]:.
        "Three pounds of VAX!"

[Sqlrelay-discussion] Database Server Failover BUG

[Cesar P. <cpe...@bt...>]

I'm using sqlrelay to connect to MS-SQL thru freetds

When the DB Server goes down the I got this message:

ERROR sendQuery: Client Library error:
        severity(78)
        layer(0)
        origin(0)
        number(38)
Error:  Write to SQL Server failed.

But the connection stay live and doesn't try to reconnect
I look into the code and found that this error isn't considered
A server fail for freetds only the read

src/connections/freetds/freetds.C:=20
	CS_RETCODE freetdsconnection::csMessageCallback
	.
	.
	.
	// for a read from sql server failed message, set deadconnection
to 1
      } else if (CS_SEVERITY(msgp->msgnumber)=3D=3D78 &&
              CS_LAYER(msgp->msgnumber)=3D=3D0 &&
              CS_ORIGIN(msgp->msgnumber)=3D=3D0 &&
              CS_NUMBER(msgp->msgnumber)=3D=3D36) {
              deadconnection=3Dtrue;
      }

I think must be=20
      // for a read-from or write-to sql server failed message,
      // set deadconnection to 1
      } else if (CS_SEVERITY(msgp->msgnumber)=3D=3D78 &&
              CS_LAYER(msgp->msgnumber)=3D=3D0 &&
              CS_ORIGIN(msgp->msgnumber)=3D=3D0 &&
              (CS_NUMBER(msgp->msgnumber)=3D=3D36 ||
               CS_NUMBER(msgp->msgnumber)=3D=3D38  )) {
              deadconnection=3Dtrue;
      }


And the same in=20

	CS_RETCODE freetdsconnection::clientMessageCallback


Appreciate comments.

	Thanx in advance

[Sqlrelay-discussion] Touching base

[Firstworks/4access <dav...@fi...>]

Hello all,

A lot of messages have piled up in the list that I haven't had a chance
to respond to.  I'll be out of town this weekend, but I'll try to catch
up on Monday.

Just don't want you guys to think I'm ignoring you :)

Take care,

Dave
dav...@fi...

[Sqlrelay-discussion] sqlrelay memory leak

[Dario L. <dl...@ti...>]

Whenver I use endSession listener takes about 4 bytes of memory. This
happens in PHP and perl. If no one knows reason for this i can do some
testing later (in a week or so).

Regards,
Dario
--
Now this foreknowledge cannot be elicited from spirits
it cannot be obtained inductively from experience
nor by any deductive calculation
         --The Art of War by Sun Tz
         Chapter XIII: The Use of Spie

[Sqlrelay-discussion] Oracle bounces causing dead connections

[Todd F. <fr...@an...>]

Howdy all!

Am having great fun with sqlrelay...  works great... except (you knew that =
was coming didn't you :} )  when the database bounces I am getting at least=
 1 connection that 'hangs'.

The symptoms are that some of my perl progs just run on fine...  but others=
 connecting to this connection get 'Broken Pipe'

Below are the conf... the logs from the connection and the listener it says=
 it is debugging to. The only odd thing I see is the 'invalid login/passwor=
d' message... (and yes... it is using the same script using the same userna=
me and password that the working ones are using)

Any help would be appreciated! Ohh yeah... and ROCK ON!

##### Relevant conf #####
        <instance id=3D"oracleTEST" port=3D"9000" socket=3D"/tmp/oracleTEST=
=2Esocket" dbase=3D"oracle8" connections=3D"2" maxconnections=3D"4" maxqueu=
elength=3D"0" growby=3D"2" ttl=3D"60" endofsession=3D"rollback" sessiontime=
out=3D"600" runasuser=3D"nobody" runasgroup=3D"nobody" cursors=3D"20" debug=
=3D"listener_and_connection" authtier=3D"database">
                <users> </users>
                <connections>
                        <connection connectionid=3D"its_proxy" string=3D"us=
er=3Dits_proxy;password=3Dsecret;oracle_sid=3DTEST.world;autocommit=3Dno;" =
metric=3D"1"/>
               </connections>
        </instance>
#########################

##### /usr/local/firstworks/var/sqlrelay/debug/sqlr-connection.9268 #####
Debugging to: /usr/local/firstworks/var/sqlrelay/debug/sqlr-listener.25739
10/06/2005 16:11:50 EDT connection [9268] :     done waiting for listener
10/06/2005 16:11:50 EDT connection [9268] :     releasing announce mutex
10/06/2005 16:11:50 EDT connection [9268] :     done releasing announce mut=
ex
10/06/2005 16:11:50 EDT connection [9268] : done announcing availability...
10/06/2005 16:11:50 EDT connection [9268] : waiting for client...
10/06/2005 16:11:50 EDT connection [9268] :     reconnect succeeded
10/06/2005 16:11:50 EDT connection [9268] : done waiting for client
10/06/2005 16:11:50 EDT connection [9268] : client session...
10/06/2005 16:11:50 EDT connection [9268] :     getting command...
10/06/2005 16:11:50 EDT connection [9268] :     done getting command
10/06/2005 16:11:50 EDT connection [9268] :     authenticate
10/06/2005 16:11:50 EDT connection [9268] :     authenticate...
10/06/2005 16:11:50 EDT connection [9268] :     database-based authenticati=
on failed: invalid user/password
10/06/2005 16:11:50 EDT connection [9268] :             ending session...
10/06/2005 16:11:50 EDT connection [9268] :             aborting all busy c=
ursors...
10/06/2005 16:11:50 EDT connection [9268] :             done aborting all b=
usy cursors
10/06/2005 16:11:50 EDT connection [9268] :             resetting autocommi=
t behavior...
10/06/2005 16:11:50 EDT connection [9268] :                     setting aut=
ocommit off...
10/06/2005 16:11:50 EDT connection [9268] :                     done settin=
g autocommit off...
10/06/2005 16:11:50 EDT connection [9268] :             done resetting auto=
commit behavior...
#########################

##### /usr/local/firstworks/var/sqlrelay/debug/sqlr-listener.25739 #####
10/06/2005 16:11:50 EDT listener [25739] : getting authentication...
10/06/2005 16:11:50 EDT listener [25739] : done getting authentication
10/06/2005 16:11:50 EDT listener [25739] : incrementing session count...
10/06/2005 16:11:50 EDT listener [25739] :      waiting for exclusive acces=
s...
10/06/2005 16:11:50 EDT listener [25739] :      done waiting for exclusive =
access...
10/06/2005 16:11:50 EDT listener [25739] :      3
10/06/2005 16:11:50 EDT listener [25739] :      waiting for the scaler...
10/06/2005 16:11:50 EDT listener [25739] :      done waiting for the scaler=
=2E..
10/06/2005 16:11:50 EDT listener [25739] : done incrementing session count
10/06/2005 16:11:50 EDT listener [25739] : getting a connection...
10/06/2005 16:11:50 EDT listener [25739] : acquiring exclusive shm access
10/06/2005 16:11:50 EDT listener [25739] : done acquiring exclusive shm acc=
ess
10/06/2005 16:11:50 EDT listener [25739] : waiting for an available connect=
ion
10/06/2005 16:11:50 EDT listener [25739] : done waiting for an available co=
nnection
10/06/2005 16:11:50 EDT listener [25739] :      handoff=3Dreconnect
10/06/2005 16:11:50 EDT listener [25739] :      socket=3D/usr/local/firstwo=
rks/var/sqlrelay/tmp/sockets/156  port=3D53973
10/06/2005 16:11:50 EDT listener [25739] : signalling connection that we've=
 read
10/06/2005 16:11:50 EDT listener [25739] : done signalling connection that =
we've read
10/06/2005 16:11:50 EDT listener [25739] : releasing exclusive shm access
10/06/2005 16:11:50 EDT listener [25739] : done releasing exclusive shm acc=
ess
10/06/2005 16:11:50 EDT listener [25739] :      done getting a connection
10/06/2005 16:11:50 EDT listener [25739] : decrementing busy listeners
10/06/2005 16:11:50 EDT listener [25739] : done decrementing busy listeners
#########################


--=20
Todd Freeman  Ext 6103                   .^.    Don't fear the penguins!
Programming Department                   /V\
Andrews University                      // \\    http://www.linux.org/
http://www.andrews.edu/~freeman/       /(   )\   http://www.debian.org/
                                        ^^ ^^

[Sqlrelay-discussion] connection scaling, not dieing off after ttl elapsed

[Matt F. <ma...@eq...>]

None of my dynamic connections spawned with the '-ttl' argument are 
dieing off.  Even after several hours of sqlrelay receiving no traffic, 
I have 'sqlr-connection-oracle8 -ttl 120 ...' processes still running.

Any suggestions?

Thanks,

Matt