sqlrelay-discussion — Discussion of topics related to SQL Relay

[Sqlrelay-discussion] Bind (bytea) with Postgresql

[Marcel C. P. <mpa...@mt...>]

Hi,

I use sqlrelay 36.4 with the programming language C.

with the following code:

/* C-CODE */
FILE *file;
char *fileName="input.dat";
long id = 1;
unsigned int len = 14112;

file = fopen(fileName, "r");
char data[len+1];
if( fread(data, 1, len, file) != len )
     printf ("NOK\n");
data[len]=NULL;
fclose(file);

sqlrcur_prepareQuery(cur, "UPDATE data SET info = ? WHERE id = ?");
sqlrcur_inputBindBlob(cur, "1", data, len);
sqlrcur_inputBindLong(cur, "2", id);
if (!sqlrcur_executeQuery(cur))
       printf (" %s\n", sqlrcur_errorMessage(cur));
/* C-CODE END */

This returns an error (cur.getErrorMessage):

ERROR:  syntax error at or near "WHERE" at character 42

Attempted Query:
UPDATE queue_job_info SET job_request = ? WHERE job_id = ?

For what do I make wrong around blob to read/write?

Thanks and Regards,
Marcel C. Pantigny
-- 
______________________________

media transfer AG
Dipl.-Ing. Marcel C. Pantigny

Dolivostrasse 11
D-64293 Darmstadt
Germany

Tel   : +49 61 51 81 93 36
Fax   : +49 61 51 81 93 41
E-Mail: MPa...@mt...
Web   : http://www.mtg.de
______________________________

[Sqlrelay-discussion] Sybase ASA 8.0

[Davide C. <dav...@re...>]

Hi *,

I've installed SQLRelay 0.37 on a Debian Sid server, and with freetds I
can succesfully query the Sybase server, but I can only access the
default database of the ASA instance.

How can I specify the name of the desired db?

it seems that sqlrelay works correctly only with ASE and not with ASA

-- 
Davide Corio                                   dav...@re...
Redomino S.r.l.            C.so Monte Grappa 90/b - 10145 Torino - Italy
Tel: +39 011 19502871 - Fax: +39 011 19791122 - http://www.redomino.com/

Re: [Sqlrelay-discussion] mysql_insertid

[Gaucho <ga...@gm...>]

Thanks for the tip but would this be the last_insert_id for the database
handler I'm connected to (which is what I'd want) or would this be the
last_insert_id of the entire database (which would cause problems if other
inserts were going on at the same time)?

thanks very much,

//Gaucho


On 3/23/06, Ady Wicaksono <ady...@re...> wrote:
>
> Hi Gaucho
>
> Maybe you should run this query : "select last_insert_id()"
>
> Good luck
>
> Gaucho wrote:
>
> > Hi there,
> >
> > I've searched all around and found hints from different posts but
> > still can't find the answer to this problem....
> >
> > I just implemented sqlrelay with my mysql server (4.0.22) and I had
> > some code that used to work that got the insertid from an
> > autoincrement field when I inserted it as such:
> >
> > --------------------------------------
> >     $sql_query =3D "  INSERT INTO mytable  (autoinc_field,y,z) VALUES
> > ('','foo','bar')";
> >     $db_ptr =3D &run_sqlquery($sql_query);
> >     my $insert_id =3D $db->{'mysql_insertid'};
> > print STDERR "I GOT THIS: $insert_id\n";
> >
> > --------------------------------------
> >
> > When I run that code directly to the database, it prints out the value
> > assigned to the autoincrement field in that table when I inserted...
> > when I run it through sqlrelay it returns blank.
> >
> > Does this functionality not work in sqlrelay?  Is there a workaround?
> >
> > Some supplimental info:
> >
> > I'm using the DBI::SQLRelay dropin functionality to replace the
> > current way I'm doing it with DBI / DBD::mysql.
> >
> > The relevant part of the runsqlquery function is as follows:
> >
> > --------------------
> > sub run_sqlquery{
> > my ($sql_query) =3D @_;
> >
> > my $prepared =3D $db->prepare($sql_query);
> > my $dbquery =3D $prepared->execute();
> > return $prepared;
> > }
> >
> > ---------------------------
> >
> >
> >
> > Any help would be greatly appreciated.
> >
> >
> >
> > //Gaucho
> >
>
>
>
>
> -------------------------------------------------------
> This SF.Net email is sponsored by xPML, a groundbreaking scripting
> language
> that extends applications into web and mobile media. Attend the live
> webcast
> and join the prime developer group breaking into this new coding
> territory!
> http://sel.as-us.falkag.net/sel?cmd=3Dlnk&kid=3D110944&bid=3D241720&dat=
=3D121642
> _______________________________________________
> Sqlrelay-discussion mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
>

Re: [Sqlrelay-discussion] Problem with compilation of SQLRelay on RedHatEnterprise

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

_Unwind_Resume should be defined in either libstdc++ or libgcc_s.  So,
it's possible that one of them isn't getting pulled in, which would
imply an error in the gcc link spec.  That's kind of unlikely though.

Another possibility is that the .o file was generated using a different
version of gcc, or copied over from another machine.  Is it possible
that someone built the code on one machine, tarred it up, copied it to
another machine, untarred it, ran configure and make, but forgot to do a
make distclean before making the tarball?

Dave
dav...@fi...

On Wed, 2006-03-22 at 15:30 +0100, Maciej Wisniowski wrote:
> Hi!
> 
> Rudiments are compiled correctly, but when trying to 'make' SQLRelay 
> 0.37 we
> have error like below (our system is Red Hat Enterprise Linux ES release 
> 4 (Nahant)).
> Any hints?
> 
> g++ -o .libs/sqlr-listener .libs/sqlrlistener.o .libs/main.o  
> -L/root/install/relay/sqlrelay-0.37/src/util 
> /root/install/relay/sqlrelay-0.37/src/util/.libs/libsqlrutil.so 
> -L/usr/local/firstworks/lib /usr/local/firstworks/lib/librudiments.so 
> -lpthread -lrt -lcrypt -ldl  -Wl,--rpath -Wl,/usr/local/firstworks/lib
> .libs/sqlrlistener.o(.text+0x164): In function 
> `sqlrlistener::sqlrlistener()':
> : undefined reference to `_Unwind_Resume'
> .libs/sqlrlistener.o(.text+0x2d4): In function 
> `sqlrlistener::sqlrlistener()':
> : undefined reference to `_Unwind_Resume'
> .libs/sqlrlistener.o(.text+0xa3a): In function 
> `sqlrlistener::initListener(int, char const**)':
> : undefined reference to `_Unwind_Resume'
> .libs/sqlrlistener.o(.text+0xf50): In function 
> `sqlrlistener::initListener(int, char const**)':
> : undefined reference to `_Unwind_Resume'
> .libs/sqlrlistener.o(.text+0x13c5): In function 
> `sqlrlistener::verifyAccessToConfigFile(char const*, sqlrconfigfile*)':
> : undefined reference to `_Unwind_Resume'
> .libs/sqlrlistener.o(.text+0x1715): more undefined references to 
> `_Unwind_Resume' follow
> collect2: ld returned 1 exit status
> make[2]: *** [sqlr-listener] Error 1
> make[2]: Leaving directory `/root/install/relay/sqlrelay-0.37/src/listener'
> make[1]: *** [all] Error 2
> make[1]: Leaving directory `/root/install/relay/sqlrelay-0.37/src'
> make: *** [all] Error 2
> 
> 

Re: [Sqlrelay-discussion] mysql_insertid

[Ady W. <ady...@re...>]

Hi Gaucho

Maybe you should run this query : "select last_insert_id()"

Good luck

Gaucho wrote:

> Hi there, 
>  
> I've searched all around and found hints from different posts but 
> still can't find the answer to this problem....
>  
> I just implemented sqlrelay with my mysql server (4.0.22) and I had 
> some code that used to work that got the insertid from an 
> autoincrement field when I inserted it as such:
>  
> --------------------------------------
>     $sql_query = "  INSERT INTO mytable  (autoinc_field,y,z) VALUES  
> ('','foo','bar')";
>     $db_ptr = &run_sqlquery($sql_query);
>     my $insert_id = $db->{'mysql_insertid'};
> print STDERR "I GOT THIS: $insert_id\n";
>  
> --------------------------------------
>  
> When I run that code directly to the database, it prints out the value 
> assigned to the autoincrement field in that table when I inserted... 
> when I run it through sqlrelay it returns blank.
>  
> Does this functionality not work in sqlrelay?  Is there a workaround?
>  
> Some supplimental info:
>  
> I'm using the DBI::SQLRelay dropin functionality to replace the 
> current way I'm doing it with DBI / DBD::mysql.
>  
> The relevant part of the runsqlquery function is as follows:
>  
> --------------------
> sub run_sqlquery{
> my ($sql_query) = @_;
>
> my $prepared = $db->prepare($sql_query);
> my $dbquery = $prepared->execute();
> return $prepared;
> }
>
> ---------------------------
>
>  
>
> Any help would be greatly appreciated.
>
>  
>
> //Gaucho
>

[Sqlrelay-discussion] mysql_insertid

[Gaucho <ga...@gm...>]

Hi there,

I've searched all around and found hints from different posts but still
can't find the answer to this problem....

I just implemented sqlrelay with my mysql server (4.0.22) and I had some
code that used to work that got the insertid from an autoincrement field
when I inserted it as such:

--------------------------------------
    $sql_query =3D "  INSERT INTO mytable  (autoinc_field,y,z) VALUES
('','foo','bar')";
    $db_ptr =3D &run_sqlquery($sql_query);
    my $insert_id =3D $db->{'mysql_insertid'};
print STDERR "I GOT THIS: $insert_id\n";

--------------------------------------

When I run that code directly to the database, it prints out the value
assigned to the autoincrement field in that table when I inserted... when I
run it through sqlrelay it returns blank.

Does this functionality not work in sqlrelay?  Is there a workaround?

Some supplimental info:

I'm using the DBI::SQLRelay dropin functionality to replace the current way
I'm doing it with DBI / DBD::mysql.

The relevant part of the runsqlquery function is as follows:

--------------------
sub run_sqlquery{
my ($sql_query) =3D @_;

my $prepared =3D $db->prepare($sql_query);
my $dbquery =3D $prepared->execute();
return $prepared;
}

---------------------------



Any help would be greatly appreciated.



//Gaucho

Re: [Sqlrelay-discussion] side effect in TCL API inputBinds

[Rodney H. <rh...@et...>]

another note on this.  The value '0123456000009' is being treated as an octal 
and is converted from octal to decimal by the Tcl_GetLongFromObj before being 
sent to the database.

rodney


Rodney Holm wrote:
> Im using the inputBinds method to pass parameters to an oracle stored 
> procedure.
> 
> sample table definition
> =====
> num_field number(10)
> char_field varchar2(24)
> 
> 
> 
> Passing this data to inputBinds
> ===============================
> 123456
> 0123456000009
> 
> 
> It appears that the inputBind and inputBinds sections in 
> api/tcl/sqlrelayCmd.C use a sequence of steps in an attempt to 
> automatically determine the data type of the parameter.
> 
> <<< BEGIN snippet >>>
> if ( Tcl_GetLongFromObj(interp, valueObj, &value) == TCL_OK ||
>      Tcl_GetIntFromObj(interp, valueObj, (int *)&value) == TCL_OK ) {
>    cur->inputBind(Tcl_GetString(variableObj), value);
> } else {
>    cur->inputBind(Tcl_GetString(variableObj),
>        Tcl_GetString(valueObj));
> }
> <<< END snippet >>>
> 
> Seems to me that either Tcl_GetLongFromObj, or Tcl_GetIntFromObj are 
> seeing '0123456000009' and treating it as a number rather than a string, 
> this causes the data to be changed when it is put in the &value.
> 
> Is there a way to force the data to be interpreted as a string rather 
> than a number in this case?
> 
> 
> rodney
> 
> 
> 
> 
> 
> 
> -------------------------------------------------------
> This SF.Net email is sponsored by xPML, a groundbreaking scripting language
> that extends applications into web and mobile media. Attend the live 
> webcast
> and join the prime developer group breaking into this new coding territory!
> http://sel.as-us.falkag.net/sel?cmd=lnk&kid=110944&bid=241720&dat=121642
> _______________________________________________
> Sqlrelay-discussion mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion

[Sqlrelay-discussion] side effect in TCL API inputBinds

[Rodney H. <rh...@et...>]

Im using the inputBinds method to pass parameters to an oracle stored procedure.

sample table definition
=====
num_field number(10)
char_field varchar2(24)



Passing this data to inputBinds
===============================
123456
0123456000009


It appears that the inputBind and inputBinds sections in api/tcl/sqlrelayCmd.C 
use a sequence of steps in an attempt to automatically determine the data type 
of the parameter.

<<< BEGIN snippet >>>
if ( Tcl_GetLongFromObj(interp, valueObj, &value) == TCL_OK ||
      Tcl_GetIntFromObj(interp, valueObj, (int *)&value) == TCL_OK ) {
    cur->inputBind(Tcl_GetString(variableObj), value);
} else {
    cur->inputBind(Tcl_GetString(variableObj),
        Tcl_GetString(valueObj));
}
<<< END snippet >>>

Seems to me that either Tcl_GetLongFromObj, or Tcl_GetIntFromObj are seeing 
'0123456000009' and treating it as a number rather than a string, this causes 
the data to be changed when it is put in the &value.

Is there a way to force the data to be interpreted as a string rather than a 
number in this case?


rodney

[Sqlrelay-discussion] Problem with compilation of SQLRelay on RedHatEnterprise

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

Hi!

Rudiments are compiled correctly, but when trying to 'make' SQLRelay 
0.37 we
have error like below (our system is Red Hat Enterprise Linux ES release 
4 (Nahant)).
Any hints?

g++ -o .libs/sqlr-listener .libs/sqlrlistener.o .libs/main.o  
-L/root/install/relay/sqlrelay-0.37/src/util 
/root/install/relay/sqlrelay-0.37/src/util/.libs/libsqlrutil.so 
-L/usr/local/firstworks/lib /usr/local/firstworks/lib/librudiments.so 
-lpthread -lrt -lcrypt -ldl  -Wl,--rpath -Wl,/usr/local/firstworks/lib
.libs/sqlrlistener.o(.text+0x164): In function 
`sqlrlistener::sqlrlistener()':
: undefined reference to `_Unwind_Resume'
.libs/sqlrlistener.o(.text+0x2d4): In function 
`sqlrlistener::sqlrlistener()':
: undefined reference to `_Unwind_Resume'
.libs/sqlrlistener.o(.text+0xa3a): In function 
`sqlrlistener::initListener(int, char const**)':
: undefined reference to `_Unwind_Resume'
.libs/sqlrlistener.o(.text+0xf50): In function 
`sqlrlistener::initListener(int, char const**)':
: undefined reference to `_Unwind_Resume'
.libs/sqlrlistener.o(.text+0x13c5): In function 
`sqlrlistener::verifyAccessToConfigFile(char const*, sqlrconfigfile*)':
: undefined reference to `_Unwind_Resume'
.libs/sqlrlistener.o(.text+0x1715): more undefined references to 
`_Unwind_Resume' follow
collect2: ld returned 1 exit status
make[2]: *** [sqlr-listener] Error 1
make[2]: Leaving directory `/root/install/relay/sqlrelay-0.37/src/listener'
make[1]: *** [all] Error 2
make[1]: Leaving directory `/root/install/relay/sqlrelay-0.37/src'
make: *** [all] Error 2


-- 
Maciej Wisniowski

[Sqlrelay-discussion] changing oracle stored procedure without restarting sqlrelay

[Rodney H. <rh...@et...>]

sqlrelay - 0.37
oracle client - 9.2.0

If I compile a change to a stored procedure, which is called through sqlrelay, 
subsequent calls to the stored procedure through sqlrelay seem to hang 
indefinately.  I am not restarting sqlrelay after compiling the change to the 
stored procedure.

Wondering if anyone else has noticed this?

thanks
rodney

[Sqlrelay-discussion] FreeBSD

[Arturo L. <kin...@gm...>]

Hello,

- I'm trying to connect from a FreeBSD box to an oracle server running on
RH.

- SQLRelay is up and running fine on RH box.  Wrote test Perl Script and am
able to insert/select from a table on RH box.

- On Freebsd box , I installed from ports p5-sqlrelay.  When I run the same
script that i used on the RH server, it hangs on Fetch .
The insert occurs , but the script never returns....


- anything obvious I"m mising here ?


oracle@oractest2[tests]=3D>./testsqlr.pl -S sqlr_sequioa
testing connection thru sqlrelay
Connecting to listener...
Inet socket: dbdev1:1531
Authenticating with listener : dbuser:xxxx
No authentication error.
Must Not Reconnect.
Authenticating with connection : dbuser:xxxx
No authentication error.
Setting AutoCommiton...
Sending Query:
insert into arthur values(3,'doors')
Length: 36
Requesting a new cursor.
Sending 0 Input Bind Variables:
Sending Output Bind Variables:
Send Column Info: yes
Skipping and Fetching
Skipping 0 rows
Fetching 0 rows
Checking For An Error... none.
Getting Cursor ID...
Cursor ID: 0
Previous result set was not suspended.
Parsing Column Info
Actual row count: unknown
Affected row count: 0
Column count: 65537
Receiving Output Bind Values:
(null)=3D
(null)=3D
(null)=3D
^C

-- perl snippet

# use SQLRelay API
  # --SQLRelay::Connection->new(server, port, socket, user, password,
retrytime, tries);

$dbh=3DSQLRelay::Connection->new("$host",$port,"","$dbuser","$dbpassword",0=
,1);
  $sql=3DSQLRelay::Cursor->new($dbh);
  $dbh->debugOn();
  $dbh->autoCommitOn();
  $sql->prepareQuery("insert into arthur values(3,'doors')");
  $sql->executeQuery();
  $dbh->endSession();


-- connection log output

done waiting for listener
releasing announce mutex
done releasing announce mutex
done announcing availability...
waiting for client...
pass succeeded
done waiting for client
client session...
getting command...
done getting command
authenticate
authenticate...
authentication was done on listener
getting command...
done getting command
autocommit...
autocommit on
getting command...
done getting command
getting a cursor...
found a free cursor:
0
done getting a cursor
new query
handling query...
getting query...
querylength:
36
query:
insert into arthur values(3,'doors')
getting query succeeded
getting input binds...
done getting input binds
getting output binds...
done getting output binds
getting send column info...
send column info
done getting send column info...
processing query...
preparing/executing...
commit or rollback check...
commit or rollback needed
done with commit or rollback check
processing query succeeded
done processing query
returning result set header...
returning row counts...
sending row counts...
actual rows unknown
affected rows: 1
done sending row counts
done returning row counts
column info will be sent
returning column counts...
done returning column counts
sending column type format...
id's
done sending column type format
returning column info...
done returning column info
returning output bind values
0
done returning output bind values
done returning result set header
handle query succeeded
returning result set data...

< manual Cntrl-C of perl script>


returning result set data failed
ending session...
aborting all busy cursors...
0
done aborting all busy cursors
committing...
done committing...
resetting autocommit behavior...
setting autocommit off...
done setting autocommit off...
done resetting autocommit behavior...
done ending session
waiting for client to close the connection...
done waiting for client to close the connection...
closing sockets from a previously suspended session...
done closing sockets from a previously suspended session...
done with client session
decrementing session count...
acquiring session count mutex
done acquiring session count mutex
0
releasing session count mutex
done releasing session count mutex
done decrementing session count
waiting for available database...
database is available
database is available
initializing session...
done initializing session...
announcing availability...
acquiring announce mutex
done acquiring announce mutex
handoff=3Dpass
signalling listener to read
done signalling listener to read
waiting for listener


thanks

Art

[Sqlrelay-discussion] sqlrelay-0.38-pre1

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

Hello group,

SQL Relay 0.38 prerelease 1 is out at:

http://www.firstworks.com/sqlrelay-0.38-pre1.tar.gz


"Can I send insert/update/delete's to a master database and distribute
select's over a group of slaves?"

Yes you can!

Aim your web browser at the sqlrelay-0.38/doc/router.html file in the
distribution and see how.

This release also supports native bind variables in MySQL 4.1 and
contains a couple of PHP Pear DB driver updates.

David Muse
dav...@fi...

Re: [Sqlrelay-discussion] RE: NULL values from queries

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

I just applied this patch.  It's in CVS now.

Dave

On Tue, 2006-03-14 at 06:47 -0500, Dave Sugar wrote:
> Ok, so I sent the message and then I went to look though the interface
> again to see if there was a good solution.  Well, it turns out that
> there is a portability option in the PEAR DB stuff to switch between
> getting NULLs as empty strings or real NULLs and this wasn't being
> checked by the SQLRelay PEAR code.
> 
> 
> So, I have modified this and attached a patch.
> 
> 
> David - please note this is a patch against your CVS tree.  It
> includes the patch that I previously submitted to you with the lower
> case of column names.
> 
> 
> Dave Sugar
> 
> 
> Begin forwarded message:
> 
> > From: Dave Sugar <dsu...@co...>
> > Date: 14 March, 2006 5:59:53 EST
> > To: sql...@li...
> > Subject: NULL values from queries
> > 
> > 
> > I'm using the PEAR PHP interface for SQLRelay while modifying an
> > application to support SQLRelay.  I'm converting from the PEAR oci8
> > (Oracle) interface.  I'm having a problem because of NULL values.  I
> > see the default for SQLRelay is to return empty strings instead of
> > NULLs (sqlrcur_getNullsAsEmptyStrings) and this is problematic for
> > the code (it is expecting NULLs).  I see the function to change the
> > setting to that NULLs are returned (sqlrcur_getNullsAsNulls).
> > 
> > 
> > But, as I'm using the PEAR interface I don't see a way of making
> > this function call when the cursor is created (except to modify the
> > source DB/sqlrelay.php).  
> > 
> > 
> > Is there a way to modify this default value globally?
> > 
> > 
> > I don't know that there is a reasonable way to modify the PEAR
> > interface to make this configurable.  
> > 
> > 
> > Anyone have any suggestions?
> > 
> > 
> > Thanks
> > Dave
> 
> 
> 
> 

[Sqlrelay-discussion] RE: NULL values from queries

[Dave S. <dsu...@co...>]

Ok, so I sent the message and then I went to look though the =20
interface again to see if there was a good solution.  Well, it turns =20
out that there is a portability option in the PEAR DB stuff to switch =20=

between getting NULLs as empty strings or real NULLs and this wasn't =20
being checked by the SQLRelay PEAR code.

So, I have modified this and attached a patch.

David - please note this is a patch against your CVS tree.  It =20
includes the patch that I previously submitted to you with the lower =20
case of column names.

Dave Sugar


Begin forwarded message:

> From: Dave Sugar <dsu...@co...>
> Date: 14 March, 2006 5:59:53 EST
> To: sql...@li...
> Subject: NULL values from queries
>
> I'm using the PEAR PHP interface for SQLRelay while modifying an =20
> application to support SQLRelay.  I'm converting from the PEAR oci8 =20=

> (Oracle) interface.  I'm having a problem because of NULL values.  =20
> I see the default for SQLRelay is to return empty strings instead =20
> of NULLs (sqlrcur_getNullsAsEmptyStrings) and this is problematic =20
> for the code (it is expecting NULLs).  I see the function to change =20=

> the setting to that NULLs are returned (sqlrcur_getNullsAsNulls).
>
> But, as I'm using the PEAR interface I don't see a way of making =20
> this function call when the cursor is created (except to modify the =20=

> source DB/sqlrelay.php).
>
> Is there a way to modify this default value globally?
>
> I don't know that there is a reasonable way to modify the PEAR =20
> interface to make this configurable.
>
> Anyone have any suggestions?
>
> Thanks
> Dave

=EF=BF=BC=

[Sqlrelay-discussion] NULL values from queries

[Dave S. <dsu...@co...>]

I'm using the PEAR PHP interface for SQLRelay while modifying an  
application to support SQLRelay.  I'm converting from the PEAR oci8  
(Oracle) interface.  I'm having a problem because of NULL values.  I  
see the default for SQLRelay is to return empty strings instead of  
NULLs (sqlrcur_getNullsAsEmptyStrings) and this is problematic for  
the code (it is expecting NULLs).  I see the function to change the  
setting to that NULLs are returned (sqlrcur_getNullsAsNulls).

But, as I'm using the PEAR interface I don't see a way of making this  
function call when the cursor is created (except to modify the source  
DB/sqlrelay.php).

Is there a way to modify this default value globally?

I don't know that there is a reasonable way to modify the PEAR  
interface to make this configurable.

Anyone have any suggestions?

Thanks
Dave

Re: [Sqlrelay-discussion] [Fwd: Hei ... performance of SQLRelay]

[Ady W. <ady...@re...>]

Hei,

I try to move SQLRelay to the MySQL Machine and connection between 
SQLRelay to MySQL become unix socket
and found that the performance is boost :D
Look at the result for the same testing, around 1000sms/seconds could be 
inserted :D

Thx

+----------+---------------------+
| count(*) | j                   |
+----------+---------------------+
|      482 | 2006-03-14 11:06:43 |
|     1056 | 2006-03-14 11:06:44 |
|     1104 | 2006-03-14 11:06:45 |
|     1048 | 2006-03-14 11:06:46 |
|     1112 | 2006-03-14 11:06:47 |
|     1013 | 2006-03-14 11:06:48 |
|     1071 | 2006-03-14 11:06:49 |
|     1129 | 2006-03-14 11:06:50 |
|     1083 | 2006-03-14 11:06:51 |
|     1035 | 2006-03-14 11:06:52 |
|     1056 | 2006-03-14 11:06:53 |
|     1075 | 2006-03-14 11:06:54 |
|     1146 | 2006-03-14 11:06:55 |
|     1134 | 2006-03-14 11:06:56 |
|     1032 | 2006-03-14 11:06:57 |
|     1056 | 2006-03-14 11:06:58 |
|     1046 | 2006-03-14 11:06:59 |
|     1099 | 2006-03-14 11:07:00 |
|     1027 | 2006-03-14 11:07:01 |
|     1076 | 2006-03-14 11:07:02 |
|     1116 | 2006-03-14 11:07:03 |
|     1080 | 2006-03-14 11:07:04 |
|     1114 | 2006-03-14 11:07:05 |
|     1128 | 2006-03-14 11:07:06 |
|     1060 | 2006-03-14 11:07:07 |
|     1092 | 2006-03-14 11:07:08 |


Ady Wicaksono wrote:

> The MySQL and SQLRelay stay on different host.
>
> SQLRelay itself is running on the same machine with webserver + PHP
>
> I don't think the memory is the problem, since we gave 2 Gbytes Memory 
> (big enough right).
>
> I try to move SQLRelay to the MySQL machine, and retest again
>
> Thx
>
> David Muse wrote:
>
>> Is the instance of mysql running on the same machine or on another
>> machine?  If it's running on the same machine, then try configuring SQL
>> Relay to use a unix socket to connect to mysql rather than host/port.
>>
>> For example, to connect to mysql running on the local machine, you could
>> use a connection string like
>> "socket=/var/lib/mysql/mysql.sock;user=xxxxx;password=xxxxx;db=xxxxx"
>> rather than
>> "host=xxxx;user=xxxx;password=xxxx;db=xxxxx"
>>
>> If the mysql database isn't running on the local host and connecting
>> directly to is is that much faster, then you may have uncovered a bug.
>> I'd expect SQL Relay to be a little slower (like maybe 10%) due to
>> having 1 more layer in the middle, but that much slower.  Most of my
>> tests have shown it to be almost as fast as going directly against
>> MySQL.
>>
>> One thing that I noticed, you have 350 database connections running,
>> which is a lot, especially if you're running SQL Relay on the same
>> machine as the web server.  Is it possible that the machine you're
>> running SQL Relay on is out of (or nearly out of) memory?
>>
>> Dave
>> dav...@fi...
>>
>> On Mon, 2006-03-13 at 18:13 +0700, Ady Wicaksono wrote:
>>  
>>
>>> -------- Original Message --------
>>> Subject:     Hei ... performance of SQLRelay
>>> Date:     Mon, 13 Mar 2006 18:01:08 +0700
>>> From:     Ady Wicaksono <ady...@in...>
>>> To:     sql...@li...
>>>
>>>
>>>
>>> Today i try to benchmark SQLRelay performance and found that this 
>>> tools is not good in performance.
>>>
>>> I try with PHP SQLRelay using this script
>>> <?
>>>    $id = substr(substr(uniqid(mktime(),4),16),0,8).chr(rand(65,90));
>>>   
>>> $con=sqlrcon_alloc("127.0.0.1",9000,"/tmp/sqlrelay.socket","username","password",0,1); 
>>>
>>>    $cur=sqlrcur_alloc($con);
>>>
>>>   sqlrcur_sendQuery($cur,"INSERT INTO testaja.images (i)  VALUES 
>>> ('$pin')");
>>>   sqlrcon_endSession($con);
>>>   sqlrcur_free($cur);
>>>   sqlrcon_free($con);
>>> ?>
>>>
>>> Hit with apache bencmark
>>> ab -n 30000 -c 100 http://localhost/smshdlr/local6288/sqlrelay.php
>>>
>>> and found that around 200 - 300rows/seconds could be inserted.
>>>
>>> +----------+---------------------+
>>> | count(*) | j                   |
>>> +----------+---------------------+
>>> |      299 | 2006-03-13 17:37:16 |
>>> |      297 | 2006-03-13 17:37:17 |
>>> |      302 | 2006-03-13 17:37:18 |
>>> |      267 | 2006-03-13 17:37:19 |
>>> |      287 | 2006-03-13 17:37:20 |
>>> |      288 | 2006-03-13 17:37:21 |
>>> |      236 | 2006-03-13 17:37:22 |
>>> |      260 | 2006-03-13 17:37:23 |
>>> |      259 | 2006-03-13 17:37:24 |
>>> |      251 | 2006-03-13 17:37:25 |
>>> |      246 | 2006-03-13 17:37:26 |
>>> |      259 | 2006-03-13 17:37:27 |
>>> |      276 | 2006-03-13 17:37:28 |
>>> |      229 | 2006-03-13 17:37:29 |
>>> |      256 | 2006-03-13 17:37:30 |
>>> |      259 | 2006-03-13 17:37:31 |
>>> |      239 | 2006-03-13 17:37:32 |
>>> |      239 | 2006-03-13 17:37:33 |
>>> |      268 | 2006-03-13 17:37:34 |
>>> |      258 | 2006-03-13 17:37:35 |
>>> |      226 | 2006-03-13 17:37:36 |
>>> |      259 | 2006-03-13 17:37:37 |
>>> |      297 | 2006-03-13 17:37:38 |
>>> |      284 | 2006-03-13 17:37:39 |
>>> |      232 | 2006-03-13 17:37:40 |
>>> |      306 | 2006-03-13 17:37:41 |
>>> |      241 | 2006-03-13 17:37:42 |
>>> |      268 | 2006-03-13 17:37:43 |
>>> |      285 | 2006-03-13 17:37:44 |
>>> |      248 | 2006-03-13 17:37:45 |
>>> |      215 | 2006-03-13 17:37:46 |
>>> |      227 | 2006-03-13 17:37:47 |
>>> |      204 | 2006-03-13 17:37:48 |
>>> |      248 | 2006-03-13 17:37:49 |
>>> |      269 | 2006-03-13 17:37:50 |
>>> |      235 | 2006-03-13 17:37:51 |
>>> |      242 | 2006-03-13 17:37:52 |
>>> |      229 | 2006-03-13 17:37:53 |
>>> |      236 | 2006-03-13 17:37:54 |
>>> |      205 | 2006-03-13 17:37:55 |
>>> |      274 | 2006-03-13 17:37:56 |
>>> |      235 | 2006-03-13 17:37:57 |
>>> |      293 | 2006-03-13 17:37:58 |
>>>
>>>
>>> I use MySQL -  PHP API and found that it's faster
>>>
>>> <?
>>>       $c = mysql_pconnect("dbhost","username","userpassword");
>>>       $r = mysql_query ("INSERT INTO testaja.images (i)  VALUES 
>>> ('$pin')",$c);
>>>       mysql_close($c);
>>> ?>
>>>
>>> And found that more than 1000 rows/seconds could be inserted :)
>>> +----------+---------------------+
>>> | count(*) | j                   |
>>> +----------+---------------------+
>>> |     1195 | 2006-03-13 17:51:27 |
>>> |     1317 | 2006-03-13 17:51:28 |
>>> |     1341 | 2006-03-13 17:51:29 |
>>> |     1289 | 2006-03-13 17:51:30 |
>>> |     1243 | 2006-03-13 17:51:31 |
>>> |     1239 | 2006-03-13 17:51:32 |
>>> |     1323 | 2006-03-13 17:51:33 |
>>> |     1283 | 2006-03-13 17:51:34 |
>>> +----------+---------------------+
>>>
>>> Any explanation about this issue?
>>>
>>> SQLRelay i use is:
>>> 1. connection is using unix sockect
>>> 2. Configuration is like this
>>>
>>> <?xml version="1.0"?>
>>> <!DOCTYPE instances SYSTEM "sqlrelay.dtd">
>>> <instances>
>>>       <instance id="dbisat" port="9000" 
>>> socket="/tmp/sqlrelay.socket" dbase="mysql" connections="350" 
>>> maxconnections="450" maxqueuelength="12000" growby="2" ttl="60"  
>>> runasuser="sqlrelay" runasgroup="sqlrelay" authier="listener" 
>>> handoff="pass" idleclienttimeout="-1">
>>>               <users>
>>>                       <user user="username" password="userpassword"/>
>>>               </users>
>>>               <connections>
>>>                       <connection connectionid="db1" 
>>> string="user=xxxx;password=xxxxx;db=xxx;host=xxxx;" metric="1" 
>>> behindloadbalancer="no"/>
>>>               </connections>
>>>       </instance>
>>> </instances>
>>>
>>>
>>>
>>>
>>>
>>> -------------------------------------------------------
>>> This SF.Net email is sponsored by xPML, a groundbreaking scripting 
>>> language
>>> that extends applications into web and mobile media. Attend the live 
>>> webcast
>>> and join the prime developer group breaking into this new coding 
>>> territory!
>>> http://sel.as-us.falkag.net/sel?cmd=lnk&kid=110944&bid=241720&dat=121642 
>>>
>>> _______________________________________________
>>> Sqlrelay-discussion mailing list
>>> Sql...@li...
>>> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
>>>
>>>   
>>
>>
>>
>>
>> -------------------------------------------------------
>> This SF.Net email is sponsored by xPML, a groundbreaking scripting 
>> language
>> that extends applications into web and mobile media. Attend the live 
>> webcast
>> and join the prime developer group breaking into this new coding 
>> territory!
>> http://sel.as-us.falkag.net/sel?cmd=lnk&kid=110944&bid=241720&dat=121642
>> _______________________________________________
>> Sqlrelay-discussion mailing list
>> Sql...@li...
>> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
>>
>>
>>  
>>
>
>
>
>
> -------------------------------------------------------
> This SF.Net email is sponsored by xPML, a groundbreaking scripting 
> language
> that extends applications into web and mobile media. Attend the live 
> webcast
> and join the prime developer group breaking into this new coding 
> territory!
> http://sel.as-us.falkag.net/sel?cmd=lnk&kid=110944&bid=241720&dat=121642
> _______________________________________________
> Sqlrelay-discussion mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
>
>

Re: [Sqlrelay-discussion] [Fwd: Hei ... performance of SQLRelay]

[Ady W. <ady...@re...>]

The MySQL and SQLRelay stay on different host.

SQLRelay itself is running on the same machine with webserver + PHP

I don't think the memory is the problem, since we gave 2 Gbytes Memory 
(big enough right).

I try to move SQLRelay to the MySQL machine, and retest again

Thx

David Muse wrote:

>Is the instance of mysql running on the same machine or on another
>machine?  If it's running on the same machine, then try configuring SQL
>Relay to use a unix socket to connect to mysql rather than host/port.
>
>For example, to connect to mysql running on the local machine, you could
>use a connection string like
>"socket=/var/lib/mysql/mysql.sock;user=xxxxx;password=xxxxx;db=xxxxx"
>rather than
>"host=xxxx;user=xxxx;password=xxxx;db=xxxxx"
>
>If the mysql database isn't running on the local host and connecting
>directly to is is that much faster, then you may have uncovered a bug.
>I'd expect SQL Relay to be a little slower (like maybe 10%) due to
>having 1 more layer in the middle, but that much slower.  Most of my
>tests have shown it to be almost as fast as going directly against
>MySQL.
>
>One thing that I noticed, you have 350 database connections running,
>which is a lot, especially if you're running SQL Relay on the same
>machine as the web server.  Is it possible that the machine you're
>running SQL Relay on is out of (or nearly out of) memory?
>
>Dave
>dav...@fi...
>
>On Mon, 2006-03-13 at 18:13 +0700, Ady Wicaksono wrote:
>  
>
>>-------- Original Message --------
>>Subject: 	Hei ... performance of SQLRelay
>>Date: 	Mon, 13 Mar 2006 18:01:08 +0700
>>From: 	Ady Wicaksono <ady...@in...>
>>To: 	sql...@li...
>>
>>
>>
>>Today i try to benchmark SQLRelay performance and found that this tools 
>>is not good in performance.
>>
>>I try with PHP SQLRelay using this script
>><?
>>    $id = substr(substr(uniqid(mktime(),4),16),0,8).chr(rand(65,90));
>>   
>>$con=sqlrcon_alloc("127.0.0.1",9000,"/tmp/sqlrelay.socket","username","password",0,1);
>>    $cur=sqlrcur_alloc($con);
>>
>>   sqlrcur_sendQuery($cur,"INSERT INTO testaja.images (i)  VALUES 
>>('$pin')");
>>   sqlrcon_endSession($con);
>>   sqlrcur_free($cur);
>>   sqlrcon_free($con);
>>?>
>>
>>Hit with apache bencmark
>>ab -n 30000 -c 100 http://localhost/smshdlr/local6288/sqlrelay.php
>>
>>and found that around 200 - 300rows/seconds could be inserted.
>>
>>+----------+---------------------+
>>| count(*) | j                   |
>>+----------+---------------------+
>>|      299 | 2006-03-13 17:37:16 |
>>|      297 | 2006-03-13 17:37:17 |
>>|      302 | 2006-03-13 17:37:18 |
>>|      267 | 2006-03-13 17:37:19 |
>>|      287 | 2006-03-13 17:37:20 |
>>|      288 | 2006-03-13 17:37:21 |
>>|      236 | 2006-03-13 17:37:22 |
>>|      260 | 2006-03-13 17:37:23 |
>>|      259 | 2006-03-13 17:37:24 |
>>|      251 | 2006-03-13 17:37:25 |
>>|      246 | 2006-03-13 17:37:26 |
>>|      259 | 2006-03-13 17:37:27 |
>>|      276 | 2006-03-13 17:37:28 |
>>|      229 | 2006-03-13 17:37:29 |
>>|      256 | 2006-03-13 17:37:30 |
>>|      259 | 2006-03-13 17:37:31 |
>>|      239 | 2006-03-13 17:37:32 |
>>|      239 | 2006-03-13 17:37:33 |
>>|      268 | 2006-03-13 17:37:34 |
>>|      258 | 2006-03-13 17:37:35 |
>>|      226 | 2006-03-13 17:37:36 |
>>|      259 | 2006-03-13 17:37:37 |
>>|      297 | 2006-03-13 17:37:38 |
>>|      284 | 2006-03-13 17:37:39 |
>>|      232 | 2006-03-13 17:37:40 |
>>|      306 | 2006-03-13 17:37:41 |
>>|      241 | 2006-03-13 17:37:42 |
>>|      268 | 2006-03-13 17:37:43 |
>>|      285 | 2006-03-13 17:37:44 |
>>|      248 | 2006-03-13 17:37:45 |
>>|      215 | 2006-03-13 17:37:46 |
>>|      227 | 2006-03-13 17:37:47 |
>>|      204 | 2006-03-13 17:37:48 |
>>|      248 | 2006-03-13 17:37:49 |
>>|      269 | 2006-03-13 17:37:50 |
>>|      235 | 2006-03-13 17:37:51 |
>>|      242 | 2006-03-13 17:37:52 |
>>|      229 | 2006-03-13 17:37:53 |
>>|      236 | 2006-03-13 17:37:54 |
>>|      205 | 2006-03-13 17:37:55 |
>>|      274 | 2006-03-13 17:37:56 |
>>|      235 | 2006-03-13 17:37:57 |
>>|      293 | 2006-03-13 17:37:58 |
>>
>>
>>I use MySQL -  PHP API and found that it's faster
>>
>><?
>>       $c = mysql_pconnect("dbhost","username","userpassword");
>>       $r = mysql_query ("INSERT INTO testaja.images (i)  VALUES 
>>('$pin')",$c);
>>       mysql_close($c);
>>?>
>>
>>And found that more than 1000 rows/seconds could be inserted :)
>>+----------+---------------------+
>>| count(*) | j                   |
>>+----------+---------------------+
>>|     1195 | 2006-03-13 17:51:27 |
>>|     1317 | 2006-03-13 17:51:28 |
>>|     1341 | 2006-03-13 17:51:29 |
>>|     1289 | 2006-03-13 17:51:30 |
>>|     1243 | 2006-03-13 17:51:31 |
>>|     1239 | 2006-03-13 17:51:32 |
>>|     1323 | 2006-03-13 17:51:33 |
>>|     1283 | 2006-03-13 17:51:34 |
>>+----------+---------------------+
>>
>>Any explanation about this issue?
>>
>>SQLRelay i use is:
>>1. connection is using unix sockect
>>2. Configuration is like this
>>
>><?xml version="1.0"?>
>><!DOCTYPE instances SYSTEM "sqlrelay.dtd">
>><instances>
>>       <instance id="dbisat" port="9000" socket="/tmp/sqlrelay.socket" 
>>dbase="mysql" connections="350" maxconnections="450" 
>>maxqueuelength="12000" growby="2" ttl="60"  runasuser="sqlrelay" 
>>runasgroup="sqlrelay" authier="listener" handoff="pass" 
>>idleclienttimeout="-1">
>>               <users>
>>                       <user user="username" password="userpassword"/>
>>               </users>
>>               <connections>
>>                       <connection connectionid="db1" 
>>string="user=xxxx;password=xxxxx;db=xxx;host=xxxx;" metric="1" 
>>behindloadbalancer="no"/>
>>               </connections>
>>       </instance>
>></instances>
>>
>>
>>
>>
>>
>>-------------------------------------------------------
>>This SF.Net email is sponsored by xPML, a groundbreaking scripting language
>>that extends applications into web and mobile media. Attend the live webcast
>>and join the prime developer group breaking into this new coding territory!
>>http://sel.as-us.falkag.net/sel?cmd=lnk&kid=110944&bid=241720&dat=121642
>>_______________________________________________
>>Sqlrelay-discussion mailing list
>>Sql...@li...
>>https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
>>
>>    
>>
>
>
>
>-------------------------------------------------------
>This SF.Net email is sponsored by xPML, a groundbreaking scripting language
>that extends applications into web and mobile media. Attend the live webcast
>and join the prime developer group breaking into this new coding territory!
>http://sel.as-us.falkag.net/sel?cmd=lnk&kid=110944&bid=241720&dat=121642
>_______________________________________________
>Sqlrelay-discussion mailing list
>Sql...@li...
>https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
>
>
>  
>

Re: [Sqlrelay-discussion] [Fwd: Hei ... performance of SQLRelay]

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

Is the instance of mysql running on the same machine or on another
machine?  If it's running on the same machine, then try configuring SQL
Relay to use a unix socket to connect to mysql rather than host/port.

For example, to connect to mysql running on the local machine, you could
use a connection string like
"socket=/var/lib/mysql/mysql.sock;user=xxxxx;password=xxxxx;db=xxxxx"
rather than
"host=xxxx;user=xxxx;password=xxxx;db=xxxxx"

If the mysql database isn't running on the local host and connecting
directly to is is that much faster, then you may have uncovered a bug.
I'd expect SQL Relay to be a little slower (like maybe 10%) due to
having 1 more layer in the middle, but that much slower.  Most of my
tests have shown it to be almost as fast as going directly against
MySQL.

One thing that I noticed, you have 350 database connections running,
which is a lot, especially if you're running SQL Relay on the same
machine as the web server.  Is it possible that the machine you're
running SQL Relay on is out of (or nearly out of) memory?

Dave
dav...@fi...

On Mon, 2006-03-13 at 18:13 +0700, Ady Wicaksono wrote:
> 
> -------- Original Message --------
> Subject: 	Hei ... performance of SQLRelay
> Date: 	Mon, 13 Mar 2006 18:01:08 +0700
> From: 	Ady Wicaksono <ady...@in...>
> To: 	sql...@li...
> 
> 
> 
> Today i try to benchmark SQLRelay performance and found that this tools 
> is not good in performance.
> 
> I try with PHP SQLRelay using this script
> <?
>     $id = substr(substr(uniqid(mktime(),4),16),0,8).chr(rand(65,90));
>    
> $con=sqlrcon_alloc("127.0.0.1",9000,"/tmp/sqlrelay.socket","username","password",0,1);
>     $cur=sqlrcur_alloc($con);
> 
>    sqlrcur_sendQuery($cur,"INSERT INTO testaja.images (i)  VALUES 
> ('$pin')");
>    sqlrcon_endSession($con);
>    sqlrcur_free($cur);
>    sqlrcon_free($con);
> ?>
> 
> Hit with apache bencmark
> ab -n 30000 -c 100 http://localhost/smshdlr/local6288/sqlrelay.php
> 
> and found that around 200 - 300rows/seconds could be inserted.
> 
> +----------+---------------------+
> | count(*) | j                   |
> +----------+---------------------+
> |      299 | 2006-03-13 17:37:16 |
> |      297 | 2006-03-13 17:37:17 |
> |      302 | 2006-03-13 17:37:18 |
> |      267 | 2006-03-13 17:37:19 |
> |      287 | 2006-03-13 17:37:20 |
> |      288 | 2006-03-13 17:37:21 |
> |      236 | 2006-03-13 17:37:22 |
> |      260 | 2006-03-13 17:37:23 |
> |      259 | 2006-03-13 17:37:24 |
> |      251 | 2006-03-13 17:37:25 |
> |      246 | 2006-03-13 17:37:26 |
> |      259 | 2006-03-13 17:37:27 |
> |      276 | 2006-03-13 17:37:28 |
> |      229 | 2006-03-13 17:37:29 |
> |      256 | 2006-03-13 17:37:30 |
> |      259 | 2006-03-13 17:37:31 |
> |      239 | 2006-03-13 17:37:32 |
> |      239 | 2006-03-13 17:37:33 |
> |      268 | 2006-03-13 17:37:34 |
> |      258 | 2006-03-13 17:37:35 |
> |      226 | 2006-03-13 17:37:36 |
> |      259 | 2006-03-13 17:37:37 |
> |      297 | 2006-03-13 17:37:38 |
> |      284 | 2006-03-13 17:37:39 |
> |      232 | 2006-03-13 17:37:40 |
> |      306 | 2006-03-13 17:37:41 |
> |      241 | 2006-03-13 17:37:42 |
> |      268 | 2006-03-13 17:37:43 |
> |      285 | 2006-03-13 17:37:44 |
> |      248 | 2006-03-13 17:37:45 |
> |      215 | 2006-03-13 17:37:46 |
> |      227 | 2006-03-13 17:37:47 |
> |      204 | 2006-03-13 17:37:48 |
> |      248 | 2006-03-13 17:37:49 |
> |      269 | 2006-03-13 17:37:50 |
> |      235 | 2006-03-13 17:37:51 |
> |      242 | 2006-03-13 17:37:52 |
> |      229 | 2006-03-13 17:37:53 |
> |      236 | 2006-03-13 17:37:54 |
> |      205 | 2006-03-13 17:37:55 |
> |      274 | 2006-03-13 17:37:56 |
> |      235 | 2006-03-13 17:37:57 |
> |      293 | 2006-03-13 17:37:58 |
> 
> 
> I use MySQL -  PHP API and found that it's faster
> 
> <?
>        $c = mysql_pconnect("dbhost","username","userpassword");
>        $r = mysql_query ("INSERT INTO testaja.images (i)  VALUES 
> ('$pin')",$c);
>        mysql_close($c);
> ?>
> 
> And found that more than 1000 rows/seconds could be inserted :)
> +----------+---------------------+
> | count(*) | j                   |
> +----------+---------------------+
> |     1195 | 2006-03-13 17:51:27 |
> |     1317 | 2006-03-13 17:51:28 |
> |     1341 | 2006-03-13 17:51:29 |
> |     1289 | 2006-03-13 17:51:30 |
> |     1243 | 2006-03-13 17:51:31 |
> |     1239 | 2006-03-13 17:51:32 |
> |     1323 | 2006-03-13 17:51:33 |
> |     1283 | 2006-03-13 17:51:34 |
> +----------+---------------------+
> 
> Any explanation about this issue?
> 
> SQLRelay i use is:
> 1. connection is using unix sockect
> 2. Configuration is like this
> 
> <?xml version="1.0"?>
> <!DOCTYPE instances SYSTEM "sqlrelay.dtd">
> <instances>
>        <instance id="dbisat" port="9000" socket="/tmp/sqlrelay.socket" 
> dbase="mysql" connections="350" maxconnections="450" 
> maxqueuelength="12000" growby="2" ttl="60"  runasuser="sqlrelay" 
> runasgroup="sqlrelay" authier="listener" handoff="pass" 
> idleclienttimeout="-1">
>                <users>
>                        <user user="username" password="userpassword"/>
>                </users>
>                <connections>
>                        <connection connectionid="db1" 
> string="user=xxxx;password=xxxxx;db=xxx;host=xxxx;" metric="1" 
> behindloadbalancer="no"/>
>                </connections>
>        </instance>
> </instances>
> 
> 
> 
> 
> 
> -------------------------------------------------------
> This SF.Net email is sponsored by xPML, a groundbreaking scripting language
> that extends applications into web and mobile media. Attend the live webcast
> and join the prime developer group breaking into this new coding territory!
> http://sel.as-us.falkag.net/sel?cmd=lnk&kid=110944&bid=241720&dat=121642
> _______________________________________________
> Sqlrelay-discussion mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
> 

[Sqlrelay-discussion] [Fwd: Hei ... performance of SQLRelay]

[Ady W. <ady...@re...>]

-------- Original Message --------
Subject: 	Hei ... performance of SQLRelay
Date: 	Mon, 13 Mar 2006 18:01:08 +0700
From: 	Ady Wicaksono <ady...@in...>
To: 	sql...@li...



Today i try to benchmark SQLRelay performance and found that this tools 
is not good in performance.

I try with PHP SQLRelay using this script
<?
    $id = substr(substr(uniqid(mktime(),4),16),0,8).chr(rand(65,90));
   
$con=sqlrcon_alloc("127.0.0.1",9000,"/tmp/sqlrelay.socket","username","password",0,1);
    $cur=sqlrcur_alloc($con);

   sqlrcur_sendQuery($cur,"INSERT INTO testaja.images (i)  VALUES 
('$pin')");
   sqlrcon_endSession($con);
   sqlrcur_free($cur);
   sqlrcon_free($con);
?>

Hit with apache bencmark
ab -n 30000 -c 100 http://localhost/smshdlr/local6288/sqlrelay.php

and found that around 200 - 300rows/seconds could be inserted.

+----------+---------------------+
| count(*) | j                   |
+----------+---------------------+
|      299 | 2006-03-13 17:37:16 |
|      297 | 2006-03-13 17:37:17 |
|      302 | 2006-03-13 17:37:18 |
|      267 | 2006-03-13 17:37:19 |
|      287 | 2006-03-13 17:37:20 |
|      288 | 2006-03-13 17:37:21 |
|      236 | 2006-03-13 17:37:22 |
|      260 | 2006-03-13 17:37:23 |
|      259 | 2006-03-13 17:37:24 |
|      251 | 2006-03-13 17:37:25 |
|      246 | 2006-03-13 17:37:26 |
|      259 | 2006-03-13 17:37:27 |
|      276 | 2006-03-13 17:37:28 |
|      229 | 2006-03-13 17:37:29 |
|      256 | 2006-03-13 17:37:30 |
|      259 | 2006-03-13 17:37:31 |
|      239 | 2006-03-13 17:37:32 |
|      239 | 2006-03-13 17:37:33 |
|      268 | 2006-03-13 17:37:34 |
|      258 | 2006-03-13 17:37:35 |
|      226 | 2006-03-13 17:37:36 |
|      259 | 2006-03-13 17:37:37 |
|      297 | 2006-03-13 17:37:38 |
|      284 | 2006-03-13 17:37:39 |
|      232 | 2006-03-13 17:37:40 |
|      306 | 2006-03-13 17:37:41 |
|      241 | 2006-03-13 17:37:42 |
|      268 | 2006-03-13 17:37:43 |
|      285 | 2006-03-13 17:37:44 |
|      248 | 2006-03-13 17:37:45 |
|      215 | 2006-03-13 17:37:46 |
|      227 | 2006-03-13 17:37:47 |
|      204 | 2006-03-13 17:37:48 |
|      248 | 2006-03-13 17:37:49 |
|      269 | 2006-03-13 17:37:50 |
|      235 | 2006-03-13 17:37:51 |
|      242 | 2006-03-13 17:37:52 |
|      229 | 2006-03-13 17:37:53 |
|      236 | 2006-03-13 17:37:54 |
|      205 | 2006-03-13 17:37:55 |
|      274 | 2006-03-13 17:37:56 |
|      235 | 2006-03-13 17:37:57 |
|      293 | 2006-03-13 17:37:58 |


I use MySQL -  PHP API and found that it's faster

<?
       $c = mysql_pconnect("dbhost","username","userpassword");
       $r = mysql_query ("INSERT INTO testaja.images (i)  VALUES 
('$pin')",$c);
       mysql_close($c);
?>

And found that more than 1000 rows/seconds could be inserted :)
+----------+---------------------+
| count(*) | j                   |
+----------+---------------------+
|     1195 | 2006-03-13 17:51:27 |
|     1317 | 2006-03-13 17:51:28 |
|     1341 | 2006-03-13 17:51:29 |
|     1289 | 2006-03-13 17:51:30 |
|     1243 | 2006-03-13 17:51:31 |
|     1239 | 2006-03-13 17:51:32 |
|     1323 | 2006-03-13 17:51:33 |
|     1283 | 2006-03-13 17:51:34 |
+----------+---------------------+

Any explanation about this issue?

SQLRelay i use is:
1. connection is using unix sockect
2. Configuration is like this

<?xml version="1.0"?>
<!DOCTYPE instances SYSTEM "sqlrelay.dtd">
<instances>
       <instance id="dbisat" port="9000" socket="/tmp/sqlrelay.socket" 
dbase="mysql" connections="350" maxconnections="450" 
maxqueuelength="12000" growby="2" ttl="60"  runasuser="sqlrelay" 
runasgroup="sqlrelay" authier="listener" handoff="pass" 
idleclienttimeout="-1">
               <users>
                       <user user="username" password="userpassword"/>
               </users>
               <connections>
                       <connection connectionid="db1" 
string="user=xxxx;password=xxxxx;db=xxx;host=xxxx;" metric="1" 
behindloadbalancer="no"/>
               </connections>
       </instance>
</instances>

[Sqlrelay-discussion] Re: Does PEAR fetchInto work?

[Dave S. <dsu...@co...>]

Well, it turns out that it was a problem in the code I was  
integrating SQLRelay into and not a problem with SQLRelay at all.  On  
to figure out the other problems now.

Dave

On 10 Mar, 2006, at 23:14, sqlrelay-discussion- 
re...@li... wrote:

> To: sql...@li...
> From: Dave Sugar <dsu...@co...>
> Date: Thu, 9 Mar 2006 23:19:00 -0500
> Subject: [Sqlrelay-discussion] Does PEAR fetchInto work?
> Reply-To: sql...@li...
>
>
> I'm just getting started with SQlRelay at this point and I'm working
> on retrofitting a website that I have done a lot of work on in the
> past.  It already uses the PHP PEAR DB interfaces for Oracle
> (primarily) but also for MtSQL, MSSQL and some for postgreSQL.
> Anyway there are a lot of calls to things that call the prepare
> function to setup a cursor,  Then call fetchInto to loop through all
> the matched rows.
>
> But what I'm seeing is it is never advancing the cursor
> (DB_sqlrelay_cursor) past the first row (the call is incrementing the
> instance of the cursor), but on the next call to fetchInto it is
> again 0.
>
> I have not done extensive testing with  the problem to be 100% sure
> it isn't my code, but just looking to verify it isn't a known issue.
>
> I'm working with tar download installed from source version 0.37 (not
> CVS version).  PHP 4.3.11
>
> Simple queries (not using perpare) seem to be functioning ok, so
> something is working correctly.
>
> I have a gut feeling there is something wrong in the sqlrelay.php
> file - function 'simpleQuery' where it is returning the new
> DB_sqlrelay_cursor - because it isn't returning a reference - but I
> have no basis for that feeling yet.
>
> Any help greatly appreciated.
> Dave
>

[Sqlrelay-discussion] Does PEAR fetchInto work?

[Dave S. <dsu...@co...>]

I'm just getting started with SQlRelay at this point and I'm working  
on retrofitting a website that I have done a lot of work on in the  
past.  It already uses the PHP PEAR DB interfaces for Oracle  
(primarily) but also for MtSQL, MSSQL and some for postgreSQL.   
Anyway there are a lot of calls to things that call the prepare  
function to setup a cursor,  Then call fetchInto to loop through all  
the matched rows.

But what I'm seeing is it is never advancing the cursor  
(DB_sqlrelay_cursor) past the first row (the call is incrementing the  
instance of the cursor), but on the next call to fetchInto it is  
again 0.

I have not done extensive testing with  the problem to be 100% sure  
it isn't my code, but just looking to verify it isn't a known issue.

I'm working with tar download installed from source version 0.37 (not  
CVS version).  PHP 4.3.11

Simple queries (not using perpare) seem to be functioning ok, so  
something is working correctly.

I have a gut feeling there is something wrong in the sqlrelay.php  
file - function 'simpleQuery' where it is returning the new  
DB_sqlrelay_cursor - because it isn't returning a reference - but I  
have no basis for that feeling yet.

Any help greatly appreciated.
Dave

Re: [Sqlrelay-discussion] 0.37 problems

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

Hi
I think when you're installing new version it is good to remove old 
version.
It solves most problems and David Muse pointed this few times in his replies
to postings like this (problems with compilation).

> All in all, not big issues but enough to make me curse early in the
> morning because i didn't expected such problems.
Some (all?) APIs (like Java, Python) were changed too, so be 
careful, and read CHANGES file and previous postings 
about 0.37.

-- 
Maciej Wisniowski

Re: [Sqlrelay-discussion] 0.37 problems

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

Hi,
one more thing :)

I had server on one machine and client on another. After compiling
server and restarting it client couldn't connect until i upgraded it
also. Looks like protocol has changed ... Or i'm just imagining things?

Regards,
Dario

On Pet, 2006-03-03 at 09:55 +0100, Dario Lah wrote:
> Hi,
> i had installed 0.36 and today i started upgrade to 0.37 and found out
> few issues.
> 
> 1. compiling fails because libsqlrclient.so (0.36) is linked against old
> libssl and when compiling utilities (ex. query) they are linked to
> installed libraries instead of to just compiled libraries which are not
> yet installed (v 0.37).
> 
> Solution for me is: cd src/api/c++ && make install
> After that again: make
> 
> It looks like problem with configure/automake. OS is Debian.
> 
> 2. After installing sql-relay file /etc/sqlrelay is overwritten. If it
> exists it shouldn't be touched at all!
> 
> All in all, not big issues but enough to make me curse early in the
> morning because i didn't expected such problems.
> 
> So ... watch out :)
> 
> Regards,
> Dario
> --
> 67. Think of all the psychic energy expended in seeking a fundamenta
> distinction between "algorithm" and "program"
> 
> 
> 
> 
> -------------------------------------------------------
> This SF.Net email is sponsored by xPML, a groundbreaking scripting language
> that extends applications into web and mobile media. Attend the live webcast
> and join the prime developer group breaking into this new coding territory!
> http://sel.as-us.falkag.net/sel?cmd=lnk&kid=110944&bid=241720&dat=121642
> _______________________________________________
> Sqlrelay-discussion mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
--
11. If you have a procedure with 10 parameters, you probably missed some

[Sqlrelay-discussion] 0.37 problems

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

Hi,
i had installed 0.36 and today i started upgrade to 0.37 and found out
few issues.

1. compiling fails because libsqlrclient.so (0.36) is linked against old
libssl and when compiling utilities (ex. query) they are linked to
installed libraries instead of to just compiled libraries which are not
yet installed (v 0.37).

Solution for me is: cd src/api/c++ && make install
After that again: make

It looks like problem with configure/automake. OS is Debian.

2. After installing sql-relay file /etc/sqlrelay is overwritten. If it
exists it shouldn't be touched at all!

All in all, not big issues but enough to make me curse early in the
morning because i didn't expected such problems.

So ... watch out :)

Regards,
Dario
--
67. Think of all the psychic energy expended in seeking a fundamenta
distinction between "algorithm" and "program"

Re: [Sqlrelay-discussion] Fix Firewall problem

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

Hey Dave,

Is it a Cisco PIX firewall?

Many firewalls don't actually disconnect idle connections, rather they
just start dropping packets from both sides.  To actually disconnect an
idle session, the firewall would need to send a "close" packet to both
sides of the connection.  But since firewalls don't generally do that,
SQL Relay thinks it's still connected to the database and the database
thinks that it's still connected to SQL Relay, but when SQL Relay sends
a query to the database, the query just vanishes.

Some database API's detect a vanishing query via a timeout and report an
obscure error.  Others don't detect it and just sit there forever
waiting for a response from the server.

I believe that Oracle does detect and report the error, so it should be
very easy to modify SQL Relay to catch that error and re-log in, just
like it currently does for a other db-is-down errors.  We'd need to run
some tests though to figure out what the error is.

SQL Relay catches a couple of db-is-down errors already.  There's an
outside chance that Oracle just returns one of those errors when it
detects this condition and that as a result SQL Relay already handles
this situation with Oracle.

Give it a try and let me know if it works or not.  If it doesn't already
work, let me know what error the SQL Relay client returns and I'll
modify it for you so that it does work.

David Muse
dav...@fi...

On Wed, 2006-03-01 at 23:19 -0500, Dave Sugar wrote:
> I have an interesting situation that I'm looking for a solution.  I'm  
> wondering if SQLRealy will be able to solve my problem.  Please let  
> me know what you think.
> 
> I have a client with the following setup.
> 1) Back end database running Oracle 9i - running on Solaris
> 2) 4 front end web servers running Linux RedHat Enterprise, Apache,  
> PHP 4, etc...
> 3) between the database server and the web servers is a firewall
> 
> Now - the problem
> The firewall is setup in such a way that it will disconnect idle  
> connections between the web server and oracle server after 30  
> minutes.  For efficiency reasons the connection created from PHP to  
> Oracle is a persistent connection (oci_pconnect).
> 
> The obvious solution is to get rid of the firewall or at least  
> reconfigure it to not drop these connections.  I have been asking for  
> this for about 2 years now and my client is not willing to make this  
> change for whatever reason.  But, now they are asking me to look into  
> other solutions.
> 
> SQLRelay was suggested to me as a possible solution and while it  
> looks like it might work.  It looks like it might silently reconnect  
> the connection to Oracle if the firewall has decided to drop the  
> connection.  I'm just trying to see if anyone has any sort of  
> experience of this or a similar situation and can let me know if they  
> have had success in solving such a situation.
> 
> The real test is of course to give it a try and see.  But, before I  
> go through the setup and modifying code to use SQLRelay, I was hoping  
> for some more info from other users.
> 
> Thanks,
> 
> Dave Sugar
> dsu...@co...
> 
> 
> -------------------------------------------------------
> This SF.Net email is sponsored by xPML, a groundbreaking scripting language
> that extends applications into web and mobile media. Attend the live webcast
> and join the prime developer group breaking into this new coding territory!
> http://sel.as-us.falkag.net/sel?cmd=lnk&kid=110944&bid=241720&dat=121642
> _______________________________________________
> Sqlrelay-discussion mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
>