sqlrelay-discussion — Discussion of topics related to SQL Relay

Re: [Sqlrelay-discussion] Query filter per user

[Matias R. <cy...@sa...>]

Hola!

I created the following sqlrelay.conf and so far is working as expected. Only filtering select where client_id=2 for the sqlrelay "clientid2" user.

<?xml version="1.0"?>
<!DOCTYPE instances SYSTEM "sqlrelay.dtd">

<instances>

        <!-- This instance maintains connections to a MySQL database -->
        <instance id="database" port="" socket="/tmp/database.socket" dbase="mysql" connections="3" maxconnections="15" maxqueuelength="5" growby="1" ttl="60" endofsession="commit" sessiontimeout="600" runasuser="sqlrelay" runasgroup="sqlrelay" cursors="5" authtier="listener" handoff="pass" deniedips="" allowedips="" debug="none" maxquerysize="65536" maxstringbindvaluelength="4000" maxlobbindvaluelength="71680" idleclienttimeout="-1">
                <users>
                        <user user="mysqluser" password="mysqlpassword"/>
                </users>
                <connections>
                        <connection connectionid="db" string="user=mysqluser;password=mysqlpassword;host=localhost;db=database;" metric="1" behindloadbalancer="no"/>
                </connections>
        </instance>


        <!-- This instance sends queries to the
                mysqldb instance after filtering them -->
        <instance id="router" port="9001" socket="/tmp/router.socket" dbase="router" connections="3" maxconnections="15" maxqueuelength="5" growby="1" ttl="60" endofsession="commit" sessiontimeout="600" runasuser="sqlrelay" runasgroup="sqlrelay" cursors="5" authtier="listener" handoff="pass" deniedips="" allowedips="" debug="none" maxquerysize="65536" maxstringbindvaluelength="4000" maxlobbindvaluelength="71680" idleclienttimeout="-1">
                <users>
                        <user user="clientid2" password="clientid2"/>
                </users>
                <connections>
                </connections>
                <router>
                        <!-- send queries to "mysqldb" which match the
                                specified patterns -->
                        <route host="" port="" socket="/tmp/database.socket" user="mysqluser" password="mysqlpassword">
                                <!-- allow selects with a where clause -->
                                <query pattern="^\s*select\s+.*\s+from\s+.*\s+where\s+client_id=2"/>
                        </route>
                        <!-- all other queries will be filtered out -->
                        <filter>
                                <query pattern=".*"/>
                        </filter>
                </router>
        </instance>


</instances>

I only found that that the dbase="router" wasn't working because the "router" value wasn't listed in sqlrelay.dtd . I added it to sqlrelay.dtd file and now is working fine.

The sqlrelay version that I am currently using is:
ii  sqlrelay                          1:0.39.4-6ubuntu1               Database connection pooling, proxying and lo


Now I guess only need to create one entry per "client" to solve the problem I explained in the previous email but I am wondering if there's a better way to achieve this than creating one instance per user and query pattern to solve my problem of filtering each SQLrelay user to a certain query form ?

Thanks in advance,

--
Matias Rollan
<cy...@sa...>

On Tue, Apr 13, 2010 at 04:49:59PM -0300, Matias Rollan wrote:
> Hola!
> 
> I am new to SQLrelay and I just installed it and connected it to MySQL for testing purposes as I think it may solve my problem.
> 
> 
> I need to find a way to filter queries that are currently being executed from differents mysql clients so each client can only modify the rows that belong to them and not the others. Right now every client is connecting to a MySQL server with the same user/passwd so I have no control on how to limit each client to certains rows from a table.
> 
> An example to try to clarify this:
> 
> table:   M
> fields:  a,b,c,d,client
> 
> I need that client 1 can only "UPDATE" the rows from table M WHERE client=1 and so on.
> 
> Since MySQL has no permissions per row I think that I can use SQLrelay in the middle, connecting each "client" to SQLrelay to handle the SQL query permissions at the SQLrelay level.
> 
> The clients queries are written the following way:
> 
> "UPDATE M set field=value WHERE client=$client"
> 
> I was thinking that I could create one SQLrelay user per client and only allow the queries with the form written above. Is this possible? Do I have to add one user per client with the query that is allowed for that user?
> 
> Any recommendation would be highly appreciated.
> 
> Thanks in advance,  
> 
> -- 
> Matias Rollan
> <cy...@sa...>
> 
> 
> 
> ------------------------------------------------------------------------------
> Download Intel&#174; Parallel Studio Eval
> Try the new software tools for yourself. Speed compiling, find bugs
> proactively, and fine-tune applications for parallel performance.
> See why Intel Parallel Studio got high marks during beta.
> http://p.sf.net/sfu/intel-sw-dev
> _______________________________________________
> Sqlrelay-discussion mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion

Re: [Sqlrelay-discussion] listener not listening on port

[Andre H. <and...@gm...>]

Hello,
> My conf is still simple. i created a listener and a connection to mysql on
> other host. the connection to mysql is created and seems to work but there
> is no listener listening on host where sqlrelay is running. also no socket
> is created.

think found the fault. there is an error in original dtd  of my 
distribution.
connections and routers were no optional but required elements. so i had to 
insert this empty <routers> tag and this triggered the error.
now it is running but there are further problems.

i do a connect from other host to port 9000 where sqlrelay is running. a 
connection is established but nothing more is happening.
i expected to get a answer of the mysqlhost which is connected by sqlrelay 
but there seems to be no other traffic.

What happens here?
Thanks,
Andre

[Sqlrelay-discussion] (no subject)

[SmallFries <sma...@gm...>]

http://EvangelinaFugatt1391.co.cc

[Sqlrelay-discussion] listener not listening on port

[Andre H. <and...@gm...>]

Hello List,

do my first steps with sqlrelay.

I installed original packages which were delivered by Ubuntu 8.04.4 LTS, 
Version should be 0.38

My conf is still simple. i created a listener and a connection to mysql on 
other host. the connection to mysql is created and seems to work but there 
is no listener listening on host where sqlrelay is running. also no socket 
is created.

i cant find a explanation (no other process on port 9000)
This is the startup output:
###
Starting SQL Relay ...
Starting listener:
  sqlr-listener-debug -id my_server_id -config /etc/sqlrelay/sqlrelay.conf &
Debugging to: /var/cache/sqlrelay/debug/sqlr-listener.7298

Starting 1 connections to db1 :
  sqlr-connection-mysql -id my_server_id -connectionid db1 -config 
/etc/sqlrelay/sqlrelay.conf

Starting 1 connections to defaultid :
  sqlr-connection-mysql -id my_server_id -connectionid defaultid -config 
/etc/sqlrelay/sqlrelay.conf
mysql_real_connect failed: Access denied for user 'sqlrelay'@'localhost' 
(using password: NO)
Couldn't log into database.

sqlr-connection-mysql failed to start.

Starting scaler:
  sqlr-scaler  -id my_server_id -config /etc/sqlrelay/sqlrelay.conf

Starting cache manager:
  sqlr-cachemanager
Warning: using default id.


Thanks to MP3.com for sponsoring:
        Clustered/Replicated database support.
        Perl API.
Thanks to FeedLounge for sponsoring:
        Query routing and filtering.
.
###
i wonder why defaultid  also is used. i dont use it in my conf and dont find 
any part where its used. is this efffecting the listener?

listener debug log only says:
###
creating shared memory and semaphores

id filename:

/var/cache/sqlrelay/tmp/ipc/dd25012

creating shared memory...

creating semaphores...
###


this is my current conf:
###
<?xml version="1.0"?>
<!DOCTYPE instances SYSTEM "sqlrelay.dtd">

<instances>

        <!-- Regular SQL Relay Instance -->
        <instance id="my_server_id" addresses="0.0.0.0" port="9000" 
socket="/tmp/sqlrelay.socket" dbase="mysql" connections="3" 
maxconnections="15" maxqueuelength="5" growby="1" ttl="60" 
endofsession="commit" sessiontimeout="600" runasuser="sqlrelay" 
runasgroup="sqlrelay" cursors="5" authtier="listener" handoff="pass" 
deniedips="" allowedips="" debug="listener" maxquerysize="65536" 
maxstringbindvaluelength="4000" maxlobbindvaluelength="71680" 
idleclienttimeout="-1" maxlisteners="25" listenertimeout="0">
                <users>
                        <user user="user1" password="password1"/>
                </users>
                <connections>
                        <connection connectionid="db1" 
string="user=mysqluser;password=mysqlpass;db=mysqldb;host=mysqlhost;port=3306;fakebinds=no" 
metric="1" behindloadbalancer="no"/>
                </connections>
                <router>
                </router>
        </instance>
</instances>
###


What could be the issue? I dont find any fault.

Thanks,
Andre 

[Sqlrelay-discussion] The JAVA client is crashed with SIGSEGV in 64bit LINUX

[seong h. p. <seo...@gm...>]

HI~

In x86_64  linux  ,  java client is crashed with SIGSEGV

-  sqlrelay version : 0.41 , rudiments version : 0.32  ( but same in
older version )
-  jdk :   both  openjdk 1.6 and SUN JDK 1.6
-  OS :  RHEL  , CENTOS
-  In 32bit machine , there is no problem.


REPRODUCING:

$ java MyClass.java

/* after  some output */

#
# An unexpected error has been detected by Java Runtime Environment:
#
#  SIGSEGV (0xb) at pc=0x00002aaaf42d08f1, pid=18442, tid=1087682880
#
# Java VM: OpenJDK 64-Bit Server VM (1.6.0-b09 mixed mode linux-amd64)
# Problematic frame:
# C  [libsqlrclient-0.41.so.1+0x128f1]  _ZN10sqlrcursor12prepareQueryEPKcj+0x17
#
# An error report file with more information is saved as:
# /neiz/cpool4/java/hs_err_pid18442.log
#
# If you would like to submit a bug report, please visit:
#   http://icedtea.classpath.org/bugzilla
# The crash happened outside the Java Virtual Machine in native code.
# See problematic frame for where to report the bug.
#
Aborted




- MyClass.java is a simple program which just fetches and prints  .
- The error is occured in more high frequency when executing the
program repeatedly than just executing once .

/* MyClass.java */

import com.firstworks.sqlrelay.*;
import java.io.*;


public class MyClass {
    public static void main(String[] arg) {
            int i;


            for(i=0;i<10000;i++)
            {

            System.out.printf("%d\n",i);
            SQLRConnection      con=new
SQLRConnection("sqlr.ssc.",(short)2001,"","user","pass",0,1);
            con.delete();

            SQLRCursor          cur=new SQLRCursor(con);

            if(!cur.sendQuery("select * from tab"))
            {
                System.out.println(cur.errorMessage());
            }
            con.endSession();

            for (int row=0; row<cur.rowCount(); row++) {
                    String[]    rowarray=cur.getRow(row);
                    for (int col=0; col<cur.colCount(); col++) {
                            System.out.println(rowarray[col] + ",");
                    }
                    System.out.println();
            }

            cur.delete();

            }

    }
}

[Sqlrelay-discussion] Query filter per user

[Matias R. <cy...@sa...>]

Hola!

I am new to SQLrelay and I just installed it and connected it to MySQL for testing purposes as I think it may solve my problem.


I need to find a way to filter queries that are currently being executed from differents mysql clients so each client can only modify the rows that belong to them and not the others. Right now every client is connecting to a MySQL server with the same user/passwd so I have no control on how to limit each client to certains rows from a table.

An example to try to clarify this:

table:   M
fields:  a,b,c,d,client

I need that client 1 can only "UPDATE" the rows from table M WHERE client=1 and so on.

Since MySQL has no permissions per row I think that I can use SQLrelay in the middle, connecting each "client" to SQLrelay to handle the SQL query permissions at the SQLrelay level.

The clients queries are written the following way:

"UPDATE M set field=value WHERE client=$client"

I was thinking that I could create one SQLrelay user per client and only allow the queries with the form written above. Is this possible? Do I have to add one user per client with the query that is allowed for that user?

Any recommendation would be highly appreciated.

Thanks in advance,  

-- 
Matias Rollan
<cy...@sa...>

[Sqlrelay-discussion] Strange behaviour of sqlr-connection-mysql

[Tom H. <tom...@sa...>]

Sorry for the repost, forgot to attacht the configuration file.

Hi All,

I am observing some strange behaviour of sqlr-connection-mysql.

I am running a webservice against SQLRelay version 0.41 on MySQL version
5.0.45 using drop-in library libmysql50sqlrelay.so on a CentOS 5.2 x64
server.

When the webservice is running under relatively high load during office
hours everything seems to be working fine, performance is as expected
and no errors.
However, when the load is low (during the night for instance) I see that
one or more of my sqlr-connection processes die. I haven't been able to
find out whether
it simply exits or crashes, I have not been able to get them to core
dump. After that I see that new sqlr-connection processes are started
(with options '-silent' and '-ttl 60').

I then observe that queries sometimes take very long to complete,
sometimes they even take more than an hour! The data returned is correct
though.
Anyone who have seen this behaviour as well?

I have attached my SQLRelay configuration file for reference.

Thanks for your time.

Kind regards,
Tom Hesp
tom...@sa...

[Sqlrelay-discussion] Strange behaviour of sqlr-connection-mysql

[Tom H. <tom...@sa...>]

Hi All,

I am observing some strange behaviour of sqlr-connection-mysql.

I am running a webservice against SQLRelay version 0.41 on MySQL version 
5.0.45 using drop-in library libmysql50sqlrelay.so on a CentOS 5.2 x64 
server.

When the webservice is running under relatively high load during office 
hours everything seems to be working fine, performance is as expected 
and no errors.
However, when the load is low (during the night for instance) I see that 
one or more of my sqlr-connection processes die. I haven't been able to 
find out whether
it simply exits or crashes, I have not been able to get them to core 
dump. After that I see that new sqlr-connection processes are started 
(with options '-silent' and '-ttl 60').

I then observe that queries sometimes take very long to complete, 
sometimes they even take more than an hour! The data returned is correct 
though.
Anyone who have seen this behaviour as well?

I have attached my SQLRelay configuration file for reference.

Thanks for your time.

Kind regards,
Tom Hesp
tom...@sa...

Re: [Sqlrelay-discussion] [SPAM] - sqlr-scaler error - Email found in subject

[Carlos V. <cve...@em...>]

Search forums for setting up dummy instance in config file before your real instance.  There is a bug with the XML parser.  So you must create a dummy instance in the config.

Sent from CJ's iPhone

On Apr 5, 2010, at 9:50 PM, "Jason Lee" <dz...@ho...<mailto:dz...@ho...>> wrote:

Hello!
    I have Rudiments 0.32, Sqlrelay 0.41  installed on a CentOS 5.4 32bit system,
but i just can not start sqlrelay, the error message is:
[root@mail bin]# ./sqlr-start -id postgresqltest

Starting listener:
  sqlr-listener -id postgresqltest -config /usr/local/firstworks/etc/sqlrelay.conf

Starting 3 connections to postgresqltest :
  sqlr-connection-postgresql -id postgresqltest -connectionid postgresqltest -config /usr/local/firstworks/etc/sqlrelay.conf
  sqlr-connection-postgresql -id postgresqltest -connectionid postgresqltest -config /usr/local/firstworks/etc/sqlrelay.conf
  sqlr-connection-postgresql -id postgresqltest -connectionid postgresqltest -config /usr/local/firstworks/etc/sqlrelay.conf

Starting scaler:
  sqlr-scaler -id postgresqltest -config /usr/local/firstworks/etc/sqlrelay.conf

sqlr-scaler error:
        The file /usr/local/firstworks/var/sqlrelay/tmp/pids/sqlr-listener-postgresqltest was not found.
        This usually means that the sqlr-listener is not running.
        The sqlr-listener must be running for the sqlr-scaler to start.


sqlr-scaler failed to start.


Thanks to MP3.com<http://MP3.com> for sponsoring:
        Clustered/Replicated database support.
        Perl API.
Thanks to FeedLounge for sponsoring:
        Query routing and filtering.

However, There are 3 files in the pids dir:
[root@mail bin]# cd /usr/local/firstworks/var/sqlrelay/tmp/pids/
[root@mail pids]# ls
sqlr-connection-postgresqltest.3136  sqlr-connection-postgresqltest.3139  sqlr-connection-postgresqltest.3142
[root@mail pids]#

The sqlrelay.conf is:
<?xml version="1.0"?>
<!DOCTYPE instances SYSTEM "sqlrelay.dtd">

<instances>

        <!-- Regular SQL Relay Instance -->
        <instance id="postgresqltest" port="9001" socket="/tmp/postgresqltest.socket" dbase="postgresql" connections="3" maxconnections="15" maxqueuelength="0" growby="1" ttl="60" endofsession="commit" sessiontimeout="600" runasuser="nobody" runasgroup="nobody" cursors="5" authtier="listener" handoff="pass">
                <users>
                        <user user="user1" password="password1"/>
                        <user user="user2" password="password2"/>
                        <user user="user3" password="password3"/>
                </users>
                <connections>
                        <connection connectionid="postgresqltest" string="host=localhost;db=xxxxxx;user=xxxxxx;password=xxxxxx;" metric="1"/>
                </connections>
        </instance>

</instances>

What's wrong with it?

Regards.



------------------------------------------------------------------------------
Download Intel&#174; Parallel Studio Eval
Try the new software tools for yourself. Speed compiling, find bugs
proactively, and fine-tune applications for parallel performance.
See why Intel Parallel Studio got high marks during beta.
http://p.sf.net/sfu/intel-sw-dev
_______________________________________________
Sqlrelay-discussion mailing list
Sql...@li...<mailto:Sql...@li...>
https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion

[Sqlrelay-discussion] sqlr-scaler error

[Jason L. <dz...@ho...>]

Hello!
    I have Rudiments 0.32, Sqlrelay 0.41  installed on a CentOS 5.4 32bit system,
but i just can not start sqlrelay, the error message is: 

[root@mail bin]# ./sqlr-start -id postgresqltest

Starting listener:
  sqlr-listener -id postgresqltest -config /usr/local/firstworks/etc/sqlrelay.conf

Starting 3 connections to postgresqltest :
  sqlr-connection-postgresql -id postgresqltest -connectionid postgresqltest -config /usr/local/firstworks/etc/sqlrelay.conf
  sqlr-connection-postgresql -id postgresqltest -connectionid postgresqltest -config /usr/local/firstworks/etc/sqlrelay.conf
  sqlr-connection-postgresql -id postgresqltest -connectionid postgresqltest -config /usr/local/firstworks/etc/sqlrelay.conf

Starting scaler:
  sqlr-scaler -id postgresqltest -config /usr/local/firstworks/etc/sqlrelay.conf

sqlr-scaler error: 
        The file /usr/local/firstworks/var/sqlrelay/tmp/pids/sqlr-listener-postgresqltest was not found.
        This usually means that the sqlr-listener is not running.
        The sqlr-listener must be running for the sqlr-scaler to start.


sqlr-scaler failed to start.


Thanks to MP3.com for sponsoring: 
        Clustered/Replicated database support.
        Perl API.
Thanks to FeedLounge for sponsoring: 
        Query routing and filtering. 

However, There are 3 files in the pids dir:
[root@mail bin]# cd /usr/local/firstworks/var/sqlrelay/tmp/pids/
[root@mail pids]# ls
sqlr-connection-postgresqltest.3136  sqlr-connection-postgresqltest.3139  sqlr-connection-postgresqltest.3142
[root@mail pids]# 

The sqlrelay.conf is:
<?xml version="1.0"?>
<!DOCTYPE instances SYSTEM "sqlrelay.dtd">

<instances>

        <!-- Regular SQL Relay Instance -->
        <instance id="postgresqltest" port="9001" socket="/tmp/postgresqltest.socket" dbase="postgresql" connections="3" maxconnections="15" maxqueuelength="0" growby="1" ttl="60" endofsession="commit" sessiontimeout="600" runasuser="nobody" runasgroup="nobody" cursors="5" authtier="listener" handoff="pass">
                <users>
                        <user user="user1" password="password1"/>
                        <user user="user2" password="password2"/>
                        <user user="user3" password="password3"/>
                </users>
                <connections>
                        <connection connectionid="postgresqltest" string="host=localhost;db=xxxxxx;user=xxxxxx;password=xxxxxx;" metric="1"/>
                </connections>
        </instance>

</instances>

What's wrong with it?

Regards.

[Sqlrelay-discussion] Problems with text fields containig the ? character

[Albert V. <av...@im...>]

Hi all

I reported this problem in 2006. You can check the email in the
following url http://sourceforge.net/mailarchive/message.php?msg...@im...

I've just installed the sqlrelay 0.39.4 version and it seems the
problem still occur.

Dave, you said you will fix the problem when the ? character was
inside quotes. Any help with this will be appreciate.

Best Regards

Albert

[Sqlrelay-discussion] svr_connection counter patch

[Renat S. <sr...@st...>]

In some conditions "Open   Server Connections" counter shows number of 
connections less than we actually have.

Look at src/connections/oracle8/main.C:

If initConnection() returns false, we call

cleanUp() .. closeConnection() .. logOutUpdateStats().

But initConnection() could return false without calling

attemptLogIn() .. logInUpdateStats()

and in this case open_svr_connections will be decremented without being 
incremented. The small patch that I attached solves this problem.

-- 
Renat Sabitov   e-mail: sr...@st...
Stack Soft         jid: sr...@ja...

Re: [Sqlrelay-discussion] Using Java API on Win32

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

Unfortunately I've had the same experience and have never been able to  
get it working.  It may be some kind of cygwin issue.  I've been  
working on a native win32 port. It has a better chance of working, but  
I don't have it working yet either.

David Muse
dav...@fi...



On Mar 26, 2010, at 1:09 PM, Jan Peter Stotz <Jan...@si... 
 > wrote:

> Hi,
>
> currently I am trying to get the Java-API of SqlRelay working on  
> Win32.
> I have compiled Rudiments and SqlRelay in my Cygwin environment.
>
> The created DLLs are looking fine. I copied all necessary DLLs into my
> Java project (checked with Dependency Walker) and changed the file  
> name
> of "cygSQLRConnection-0.dll" to "SQLRConnection.dll" as it is  
> referenced
> in SQLRConnection.java.
>
> The problem is that when Java executes the command
> System.loadLibrary("SQLRConnection") it never returns. This blocks the
> classloader loading com.firstworks.sqlrelay.SQLRConnection which is
> really bad...
>
> What is going wrong and what do I have to change to make it work?
> I have to access a SqlRelay encapsulated SQLite database on a remote
> Linux system...
>
> Jan
>
> --- 
> --- 
> --- 
> ---------------------------------------------------------------------
> Download Intel&#174; Parallel Studio Eval
> Try the new software tools for yourself. Speed compiling, find bugs
> proactively, and fine-tune applications for parallel performance.
> See why Intel Parallel Studio got high marks during beta.
> http://p.sf.net/sfu/intel-sw-dev
>
> _______________________________________________________
> Unlimited Disk, Data Transfer, PHP/MySQL Domain Hosting
>              http://www.doteasy.com
> _______________________________________________
> Sqlrelay-discussion mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
>
>
> _______________________________________________________
> Unlimited Disk, Data Transfer, PHP/MySQL Domain Hosting
>              http://www.doteasy.com

[Sqlrelay-discussion] unsubscribe

[Nick P. <ni...@sk...>]

unsubscribe

On Mar 26, 2010, at 1:09 PM, Jan Peter Stotz wrote:

> Hi,
>
> currently I am trying to get the Java-API of SqlRelay working on  
> Win32.
> I have compiled Rudiments and SqlRelay in my Cygwin environment.
>
> The created DLLs are looking fine. I copied all necessary DLLs into my
> Java project (checked with Dependency Walker) and changed the file  
> name
> of "cygSQLRConnection-0.dll" to "SQLRConnection.dll" as it is  
> referenced
> in SQLRConnection.java.
>
> The problem is that when Java executes the command
> System.loadLibrary("SQLRConnection") it never returns. This blocks the
> classloader loading com.firstworks.sqlrelay.SQLRConnection which is
> really bad...
>
> What is going wrong and what do I have to change to make it work?
> I have to access a SqlRelay encapsulated SQLite database on a remote
> Linux system...
>
> Jan
>
> ---------------------------------------------------------------------- 
> --------
> Download Intel® Parallel Studio Eval
> Try the new software tools for yourself. Speed compiling, find bugs
> proactively, and fine-tune applications for parallel performance.
> See why Intel Parallel Studio got high marks during beta.
> http://p.sf.net/sfu/intel-sw- 
> dev_______________________________________________
> Sqlrelay-discussion mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion



--

Nick Pavey
ni...@sk...

[Sqlrelay-discussion] Using Java API on Win32

[Jan P. S. <Jan...@si...>]

Hi,

currently I am trying to get the Java-API of SqlRelay working on Win32.
I have compiled Rudiments and SqlRelay in my Cygwin environment.

The created DLLs are looking fine. I copied all necessary DLLs into my
Java project (checked with Dependency Walker) and changed the file name
of "cygSQLRConnection-0.dll" to "SQLRConnection.dll" as it is referenced
in SQLRConnection.java.

The problem is that when Java executes the command
System.loadLibrary("SQLRConnection") it never returns. This blocks the
classloader loading com.firstworks.sqlrelay.SQLRConnection which is
really bad...

What is going wrong and what do I have to change to make it work?
I have to access a SqlRelay encapsulated SQLite database on a remote
Linux system...

Jan

[Sqlrelay-discussion] sqlr-connection waits for data in handoff socket forever

[Renat S. <sr...@st...>]

Hello!

Sometimes on hard load, for instance 100 requests per second for one
connection, sqlr-connection process sleeps in infinite wait for data in
handoff socket with this stack:

----------
recvmsg
sqlrconnection_svr::receiveFileDescriptor
filedescriptor::receiveFileDescriptor
waitForClient
...
----------

I have made the patch for rudiments, wich uses poll() to set timeout
(2min) for waiting data from socket. It is named
rudiments_0.32_poll.patch without automake, autoconf and autoheader and
http://www.srr.pp.ru/www/sqlr/rudiments_0.32_poll.FULL.patch.gz with them.

Unfortunately, this is not enough to solve the problem, because it seems
that it is impossible to change handoff socket for sqlr-connection on
hard load. For this reason I made a patch for sqlrelay-0.41 wich make
sqlr-connection exit when error occured while reading from handoff socket.

-- 
Renat Sabitov   e-mail: sr...@st...
Stack Soft         jid: sr...@ja...

[Sqlrelay-discussion] Client session counter patch

[Renat S. <sr...@st...>]

Hello David!

Client session counter becomes invalid when connection is killed in 
during client session.

Here is small patch that makes connection to decrement client session 
counter on exit.


-- 
Renat Sabitov   e-mail: sr...@st...
Stack Soft         jid: sr...@ja...

Re: [Sqlrelay-discussion] statistics and shm access without semaphores

[Renat S. <sr...@st...>]

Renat Sabitov пишет:

> Solution ids obvious: semaphores should be used to access shared memory.

I have made a patch for sqlrelay-0.41 which use mutex #9 to access 
statistics shared memory.

-- 
Renat Sabitov   e-mail: sr...@st...
Stack Soft         jid: sr...@ja...

Re: [Sqlrelay-discussion] Problem with startup on RHEL ES5 64 bit

[Alberto C. <aca...@pu...>]

2010/3/3 Rob Tanner <rt...@li...>:
> I’m replying to my own message.  Did dome googling and apparently this
> startup issue with the listener has been discussed before and the work
> around is to create a dummy instance in the sqlrelay.conf file prior to the
> actual instance.  So I simply made a copy of the real instance and named it
> “dummy” (okay, I’m just not that creative today).   Now, when I start
> sqlrelay, all the daemons start but I get this back:
>
> Starting SQL Relay:
> Launching instance with id 'webapps':                      [  OK  ]
> 19440 is not sqlr! removing pidfile ...
>
> The pid that it claims isn’t sqlr! is actually the sqlr-cachemanager and
> since it removes the pid file, when I stop sqlrelay, sqlr-cachemanger isn’t
> stopped and I have to kill it manually.  Is this the compromise that at
> least brings up all the components and so I’m just stuck with it as a
> downside?  I can certainly live with that if I have to, but is there a
> work-around for this as well?

I don't have that problem, but then my start/stop routine is quite
different. I think cleanup() is not working properly, will check my
scripts.

[Sqlrelay-discussion] question about sqlrelay version history

[김피터 <kp...@na...>]

Hi friends 
 
I am using a sqlrelay of which version was made in 2006. 
 
Can I get a documentation of a version of sqlrelay in 2006 or update history of sqlrelay?
 
Because I'd like to know if a sqlrelay in 2006 supports mysql native binds
 
Thanks in advance =)
 
 

Re: [Sqlrelay-discussion] Problem with startup on RHEL ES5 64 bit

[Rob T. <rt...@li...>]

I¹m replying to my own message.  Did dome googling and apparently this
startup issue with the listener has been discussed before and the work
around is to create a dummy instance in the sqlrelay.conf file prior to the
actual instance.  So I simply made a copy of the real instance and named it
³dummy² (okay, I¹m just not that creative today).   Now, when I start
sqlrelay, all the daemons start but I get this back:

Starting SQL Relay:
Launching instance with id 'webapps':                      [  OK  ]
19440 is not sqlr! removing pidfile ...

The pid that it claims isn¹t sqlr! is actually the sqlr-cachemanager and
since it removes the pid file, when I stop sqlrelay, sqlr-cachemanger isn¹t
stopped and I have to kill it manually.  Is this the compromise that at
least brings up all the components and so I¹m just stuck with it as a
downside?  I can certainly live with that if I have to, but is there a
work-around for this as well?

Thanks,
Rob


On 3/2/10 5:04 PM, "Rob Tanner" <rt...@li...> wrote:

> Hi,
> 
> The regular startup script in the distribution
> (sqlrelay-0.41/init/redhat/init.d/sqlrelay) fails to start the listener and
> the scaler, the latter I presume because sqlr-listener is not running.
> However, I can manually start both of them, listener first and then the
> scaler.  Is there a way to get the listener to provide some debug info when it
> doesn¹t start up?
> 
> Here the startup script as found in the sqlrelay distribution after compile.
> Can anyone see any problems in it?
> 
> Thanks,
> Rob
> 
> #! /bin/sh
> #
> # sqlrelay   This starts and stops SQL relay.
> #
> # chkconfig: 345 85 15
> # description: Persistent database connection system.
> 
> # Source function library.
> success() {
>         echo success
> }
> failure() {
>         echo failure
> }
> passed() {
>         echo passed
> }
> if [ -r "/etc/init.d/functions" ]; then
>         . /etc/init.d/functions
> else
>         if [ -r "/etc/rc.d/init.d/functions" ]; then
>                 . /etc/rc.d/init.d/functions
>         fi
> fi
> 
> # Get config.
> test -f /etc/sysconfig/network && . /etc/sysconfig/network
> 
> # Check that networking is up.
> [ ${NETWORKING} = "yes" ] || exit 0
> 
> prefix=${DESTDIR}/usr/local/firstworks
> sysconfdir=${prefix}/etc
> localstatedir=${prefix}/var
> tmpdir=${localstatedir}/sqlrelay/tmp
> cachedir=${localstatedir}/sqlrelay/cache
> debugdir=${localstatedir}/sqlrelay/debug
> 
> [ -f ${sysconfdir}/sqlrelay.conf ] || exit 1
> 
> RETVAL=0
> 
> # Add appropriate bin/lib paths
> if [ ${prefix} != "/usr" ]; then
>         export PATH=$PATH:${prefix}/bin
>         export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:${prefix}/lib
> fi
> 
> cleanup(){
>     for i in `ls ${tmpdir}/pids/* 2>/dev/null`
>     do
>         if ( test -r "$i" )
>         then
>             PID=`cat $i`
>             if ( test -z "`ps -p $PID | egrep -E
> 'sqlr-cachemana|sqlr-connectio|sqlr-listener'`" )
>             then
>                 echo "$PID is not sqlr! removing pidfile ..."
>                 rm $i
>             fi
>         fi
>     done
> }
> 
> start(){
>     echo -n $"Starting SQL Relay: "
>     if [ -r /etc/sysconfig/sqlrelay ]; then
>         launched=0
>         for connid in `grep -v ^# /etc/sysconfig/sqlrelay`; do
>             echo
>             echo -n $"Launching instance with id '${connid}':"
>             sqlr-start -id ${connid} 0<&- 1>&- 2>&-
>             RETVAL=$?
>             [ $RETVAL -eq 0 ] && success || failure
>             launched=1
>         done
>         [ "$launched" -eq 1 ] || passed
>         echo
>     else
>         failure
>     fi
>     [ $RETVAL -eq 0 ] && touch /var/lock/subsys/sqlrelay
>     cleanup
>     return $RETVAL
> }
> 
> stop(){
>     echo -n $"Stopping SQL Relay: "
>     if [ -r /etc/sysconfig/sqlrelay ]; then
>         launched=0
>         for connid in `grep -v ^# /etc/sysconfig/sqlrelay`; do
>             echo
>             echo -n $"Stopping instance with id '${connid}':"
>             sqlr-stop ${connid} 0<&- 1>&- 2>&-
>             RETVAL=$?
>             [ $RETVAL -eq 0 ] && success || failure
>             launched=1
>         done
>         [ "$launched" -eq 1 ] || passed
>         echo
>     else
>         failure
>     fi
>     [ $RETVAL -eq 0 ] && rm -f /var/lock/subsys/sqlrelay
>     cleanup
>     return $RETVAL
> }
> 
> restart(){
>     stop
>     start
> }
> 
> condrestart(){
>     [ -e /var/lock/subsys/sqlrelay ] && restart
>     return 0
> }
> 
> 
> # See how we were called.
> case "$1" in
>     start)
>         start
>         ;;
>     stop)
>         stop
>         ;;
>     status)
>         status sqlr-listener
>         ;;
>     restart)
>         restart
>         ;;
>     reload)
>         restart
>         ;;
>     condrestart)
>         condrestart
>         ;;
>     *)
>         echo "Usage: sqlrelay {start|stop|status|restart|condrestart}"
>         RETVAL=1
> esac
> 
> exit $RETVAL
> 
> 
> ------------------------------------------------------------------------------
> Download Intel&#174; Parallel Studio Eval
> Try the new software tools for yourself. Speed compiling, find bugs
> proactively, and fine-tune applications for parallel performance.
> See why Intel Parallel Studio got high marks during beta.
> http://p.sf.net/sfu/intel-sw-dev
> 
> _______________________________________________
> Sqlrelay-discussion mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion

[Sqlrelay-discussion] Problem with startup on RHEL ES5 64 bit

[Rob T. <rt...@li...>]

Hi,

The regular startup script in the distribution
(sqlrelay-0.41/init/redhat/init.d/sqlrelay) fails to start the listener and
the scaler, the latter I presume because sqlr-listener is not running.
However, I can manually start both of them, listener first and then the
scaler.  Is there a way to get the listener to provide some debug info when
it doesn¹t start up?

Here the startup script as found in the sqlrelay distribution after compile.
Can anyone see any problems in it?

Thanks,
Rob

#! /bin/sh
#
# sqlrelay   This starts and stops SQL relay.
#
# chkconfig: 345 85 15
# description: Persistent database connection system.

# Source function library.
success() {
        echo success
}
failure() {
        echo failure
}
passed() {
        echo passed
}
if [ -r "/etc/init.d/functions" ]; then
        . /etc/init.d/functions
else
        if [ -r "/etc/rc.d/init.d/functions" ]; then
                . /etc/rc.d/init.d/functions
        fi
fi

# Get config.
test -f /etc/sysconfig/network && . /etc/sysconfig/network

# Check that networking is up.
[ ${NETWORKING} = "yes" ] || exit 0

prefix=${DESTDIR}/usr/local/firstworks
sysconfdir=${prefix}/etc
localstatedir=${prefix}/var
tmpdir=${localstatedir}/sqlrelay/tmp
cachedir=${localstatedir}/sqlrelay/cache
debugdir=${localstatedir}/sqlrelay/debug

[ -f ${sysconfdir}/sqlrelay.conf ] || exit 1

RETVAL=0

# Add appropriate bin/lib paths
if [ ${prefix} != "/usr" ]; then
        export PATH=$PATH:${prefix}/bin
        export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:${prefix}/lib
fi

cleanup(){
    for i in `ls ${tmpdir}/pids/* 2>/dev/null`
    do
        if ( test -r "$i" )
        then
            PID=`cat $i`
            if ( test -z "`ps -p $PID | egrep -E
'sqlr-cachemana|sqlr-connectio|sqlr-listener'`" )
            then
                echo "$PID is not sqlr! removing pidfile ..."
                rm $i
            fi
        fi
    done
}

start(){
    echo -n $"Starting SQL Relay: "
    if [ -r /etc/sysconfig/sqlrelay ]; then
        launched=0
        for connid in `grep -v ^# /etc/sysconfig/sqlrelay`; do
            echo
            echo -n $"Launching instance with id '${connid}':"
            sqlr-start -id ${connid} 0<&- 1>&- 2>&-
            RETVAL=$?
            [ $RETVAL -eq 0 ] && success || failure
            launched=1
        done
        [ "$launched" -eq 1 ] || passed
        echo
    else
        failure
    fi
    [ $RETVAL -eq 0 ] && touch /var/lock/subsys/sqlrelay
    cleanup
    return $RETVAL
}

stop(){
    echo -n $"Stopping SQL Relay: "
    if [ -r /etc/sysconfig/sqlrelay ]; then
        launched=0
        for connid in `grep -v ^# /etc/sysconfig/sqlrelay`; do
            echo
            echo -n $"Stopping instance with id '${connid}':"
            sqlr-stop ${connid} 0<&- 1>&- 2>&-
            RETVAL=$?
            [ $RETVAL -eq 0 ] && success || failure
            launched=1
        done
        [ "$launched" -eq 1 ] || passed
        echo
    else
        failure
    fi
    [ $RETVAL -eq 0 ] && rm -f /var/lock/subsys/sqlrelay
    cleanup
    return $RETVAL
}

restart(){
    stop
    start
}

condrestart(){
    [ -e /var/lock/subsys/sqlrelay ] && restart
    return 0
}


# See how we were called.
case "$1" in
    start)
        start
        ;;
    stop)
        stop
        ;;
    status)
        status sqlr-listener
        ;;
    restart)
        restart
        ;;
    reload)
        restart
        ;;
    condrestart)
        condrestart
        ;;
    *)
        echo "Usage: sqlrelay {start|stop|status|restart|condrestart}"
        RETVAL=1
esac

exit $RETVAL

[Sqlrelay-discussion] DB Routing based on user

[Carlos V. <cve...@em...>]

Is it possible to route queries based on the username used to connect to the Router Instance?

For example, say I setup 2 users in the router instance.  Is there a way to setup the route query pattern to check the user.

So all queries from routerUser1 go to db1 and all queries from routerUser2 go to db2, instead matching against the query themselves.

I ask because unlike the Documented example of db1 is mysql and db2 is postgre.  I actually have 2 Sybase databases.  I need another way to separate these queries other than regexing the query itself.

<instance id="router" port="9000" socket="/tmp/router.socket" dbase="router" connections="3" maxconnections="15">
 <users>
   <user user="routerUser1" password="routerpassword"/>
   <user user="routerUser2" password="routerpassword"/>
 </users>
 <router>
 <!-SOME HOW SEND ALL USER1 REQUESTS TO THIS DATABASE -->
   <route host="" port="" socket="/tmp/Sybase1.socket" user=Sybase1dbuser" password="dbpassword">
     <query pattern="+\smysqldb\."/>
<!-MAYBE SOMETHING LIKE THIS? -->
      <query user="routerUser1">
   </route>
 <!-SOMEHOW SEND ALL USER2 REQUESTS TO THIS DATABASE -->
   <route host="" port="" socket="/tmp/Sybase2.socket" user="Sybase2dbuser" password="dbpassword">
     <query pattern="+\spostgresqldb\."/>
<!-MAYBE SOMETHING LIKE THIS? -->
      <query user="routerUser2">
   </route>
 </router>
</instance>

Re: [Sqlrelay-discussion] trouble compiling

[Claudio F. <kla...@gm...>]

On Fri, Feb 12, 2010 at 6:24 PM, tyju tiui <jck...@ya...> wrote:

> Any other ideas?
>

Nope... there must be something broken somewhere but I can't think of
anything.

Re: [Sqlrelay-discussion] trouble compiling

[tyju t. <jck...@ya...>]

Any other ideas?



________________________________
From: tyju tiui <jck...@ya...>
To: Discussion of topics related to SQL Relay <sql...@li...>
Sent: Fri, February 12, 2010 9:51:37 AM
Subject: Re: [Sqlrelay-discussion] trouble compiling


It is defined in /usr/include/mysql/mysql.h
MYSQL *         STDCALL mysql_connect(MYSQL *mysql, const char *host,
                                      const char *user, const char *passwd);



________________________________
From: Claudio Freire <kla...@gm...>
To: Discussion of topics related to SQL Relay <sql...@li...>
Sent: Fri, February 12, 2010 8:51:12 AM
Subject: Re: [Sqlrelay-discussion] trouble compiling



On Thu, Feb 11, 2010 at 9:20 PM, tyju tiui <jck...@ya...> wrote:

>mysqlconnection.C:106: error: 'mysql_connect' was not declared in this scope
>

Read the includes in /usr/include/mysql and make sure they define mysql_connect.
If not, you may have to patch sqlrelay. But I suspect the includes are missing.