sqlrelay-discussion

[Sqlrelay-discussion] Testing sqlrelay's drop-in replacement libraries on Debian Wheezy

[Petros M. <ern...@ya...>]

I' ve tested sqlrelay's drop-in replacement libraries for MySQL and
PostgreSQL on Debian Wheezy (x86_64).

The drop-in for MySQL does not seem to work. It just hangs when trying
to connect with the mysql client program. It asks for password and after
entering it, it freezes without ever giving back a mysql prompt. Maybe
this is related to the fact that Debian Wheezy comes with mysql 5.5, but
sqlrelay provides libraries up to 5.1? If this is not relevant, I would
be glad to do any tests needed to narrow down the possible causes of the
problem.

The drop-in replacement library for PostgreSQL works when using the psql
client program.
However, it is not usable if trying to connect with psycopg2, the most
popular library to interface with postgres from python. In particular,
psycopg2 checks for the protocol version of the connection and if it is
not 3, it exits. Maybe sqlrelay's implementation of postgres api does
not support protocol version requests. Dave, if that is the case, would
it be easy to include support for this type of requests in a following
release of sqlrelay?

Re: [Sqlrelay-discussion] Testing sqlrelay's drop-in replacement libraries on Debian Wheezy

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

Hmmm.  The mysql 5.1 library should work with 5.5.  I have a client that 
I think it using it with 5.5, but I could be wrong about that.  It's 
possible that one of the MySQL internal structures changed between 5.1 
and 5.5 and the code needs to be updated to handle that.  Take a look at 
the file src/api/mysql/mysql.cpp.  Near the top there are several 
ifdef's for different versions of MySQL.  Compare the structures defined 
for 5.1 with the ones defined in the mysql headers for 5.5.  If the 
sizes look different, then that could be the problem.  Otherwise, you'd 
have to enable debug and trace through it.

I just looked at the code for the postgres drop-in library and I don't 
see a function that returns the protocol version, so that could be 
tricky to solve.  It might be returned in a struct or there could be 
some set of commands that get run or something.  I'll look into it.  It 
should be possible to get it working in an upcoming release.

Dave

On 01/18/2013 12:23 PM, Petros Moisiadis wrote:
> I' ve tested sqlrelay's drop-in replacement libraries for MySQL and
> PostgreSQL on Debian Wheezy (x86_64).
>
> The drop-in for MySQL does not seem to work. It just hangs when trying
> to connect with the mysql client program. It asks for password and after
> entering it, it freezes without ever giving back a mysql prompt. Maybe
> this is related to the fact that Debian Wheezy comes with mysql 5.5, but
> sqlrelay provides libraries up to 5.1? If this is not relevant, I would
> be glad to do any tests needed to narrow down the possible causes of the
> problem.
>
> The drop-in replacement library for PostgreSQL works when using the psql
> client program.
> However, it is not usable if trying to connect with psycopg2, the most
> popular library to interface with postgres from python. In particular,
> psycopg2 checks for the protocol version of the connection and if it is
> not 3, it exits. Maybe sqlrelay's implementation of postgres api does
> not support protocol version requests. Dave, if that is the case, would
> it be easy to include support for this type of requests in a following
> release of sqlrelay?
>
> ------------------------------------------------------------------------------
> Master HTML5, CSS3, ASP.NET, MVC, AJAX, Knockout.js, Web API and
> much more. Get web development skills now with LearnDevNow -
> 350+ hours of step-by-step video tutorials by Microsoft MVPs and experts.
> SALE $99.99 this month only -- learn more at:
> http://p.sf.net/sfu/learnmore_122812
> _______________________________________________
> 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
>

Re: [Sqlrelay-discussion] Testing sqlrelay's drop-in replacement libraries on Debian Wheezy

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

Interesting.  The mysql client for 5.5 doesn't appear to be against the 
libmysqlclient library at all:

[dmuse@localhost mysql]$ mysql --version
mysql  Ver 14.14 Distrib 5.5.28, for Linux (x86_64) using readline 5.1
[dmuse@localhost mysql]$ ldd /usr/bin/mysql
	linux-vdso.so.1 =>  (0x00007fff22d33000)
	libpthread.so.0 => /lib64/libpthread.so.0 (0x0000003775200000)
	libz.so.1 => /lib64/libz.so.1 (0x0000003775e00000)
	librt.so.1 => /lib64/librt.so.1 (0x0000003775a00000)
	libssl.so.10 => /lib64/libssl.so.10 (0x0000003785e00000)
	libcrypto.so.10 => /lib64/libcrypto.so.10 (0x0000003783a00000)
	libdl.so.2 => /lib64/libdl.so.2 (0x0000003775600000)
	libncurses.so.5 => /lib64/libncurses.so.5 (0x0000003788600000)
	libtinfo.so.5 => /lib64/libtinfo.so.5 (0x0000003785a00000)
	libstdc++.so.6 => /lib64/libstdc++.so.6 (0x000000377e600000)
	libm.so.6 => /lib64/libm.so.6 (0x0000003776200000)
	libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x0000003778600000)
	libc.so.6 => /lib64/libc.so.6 (0x0000003774e00000)
	/lib64/ld-linux-x86-64.so.2 (0x0000003774a00000)
	libgssapi_krb5.so.2 => /lib64/libgssapi_krb5.so.2 (0x0000003782e00000)
	libkrb5.so.3 => /lib64/libkrb5.so.3 (0x0000003783200000)
	libcom_err.so.2 => /lib64/libcom_err.so.2 (0x000000377fe00000)
	libk5crypto.so.3 => /lib64/libk5crypto.so.3 (0x0000003783e00000)
	libkrb5support.so.0 => /lib64/libkrb5support.so.0 (0x0000003783600000)
	libkeyutils.so.1 => /lib64/libkeyutils.so.1 (0x0000003784600000)
	libresolv.so.2 => /lib64/libresolv.so.2 (0x0000003776a00000)
	libselinux.so.1 => /lib64/libselinux.so.1 (0x0000003776600000)

or use any of the mysql client api functions:

[dmuse@localhost mysql]$ ltrace -e "mysql_real_connect" mysql --host=db 
--user=test --password=test
ERROR 1045 (28000): Access denied for user 'test'@'192.168.1.73' (using 
password: YES)

5.1 uses libmysqlclient though:

[dmuse@fedora ~]$ mysql --version
mysql  Ver 14.14 Distrib 5.1.60, for redhat-linux-gnu (i386) using 
readline 5.1
[dmuse@fedora ~]$ ldd /usr/bin/mysql
	linux-gate.so.1 =>  (0x0043e000)
	libncursesw.so.5 => /lib/libncursesw.so.5 (0x00475000)
	libtinfo.so.5 => /lib/libtinfo.so.5 (0x0033f000)
	libpthread.so.0 => /lib/libpthread.so.0 (0x0079f000)
	libmysqlclient.so.16 => /usr/lib/mysql/libmysqlclient.so.16 (0x00110000)
	libcrypt.so.1 => /lib/libcrypt.so.1 (0x00511000)
	libnsl.so.1 => /lib/libnsl.so.1 (0x006ba000)
	libssl.so.10 => /usr/lib/libssl.so.10 (0x00284000)
	libcrypto.so.10 => /lib/libcrypto.so.10 (0x007ba000)
	libz.so.1 => /lib/libz.so.1 (0x00e5c000)
	libstdc++.so.6 => /usr/lib/libstdc++.so.6 (0x00d0e000)
	libm.so.6 => /lib/libm.so.6 (0x00c26000)
	libgcc_s.so.1 => /lib/libgcc_s.so.1 (0x00b1a000)
	libc.so.6 => /lib/libc.so.6 (0x00946000)
	libdl.so.2 => /lib/libdl.so.2 (0x002dc000)
	/lib/ld-linux.so.2 (0x00c60000)
	libfreebl3.so => /lib/libfreebl3.so (0x002e1000)
	libgssapi_krb5.so.2 => /lib/libgssapi_krb5.so.2 (0x0035f000)
	libkrb5.so.3 => /lib/libkrb5.so.3 (0x00541000)
	libcom_err.so.2 => /lib/libcom_err.so.2 (0x0032e000)
	libk5crypto.so.3 => /lib/libk5crypto.so.3 (0x00397000)
	libresolv.so.2 => /lib/libresolv.so.2 (0x00ebc000)
	libkrb5support.so.0 => /lib/libkrb5support.so.0 (0x0072e000)
	libkeyutils.so.1 => /lib/libkeyutils.so.1 (0x0041e000)
	libselinux.so.1 => /lib/libselinux.so.1 (0x00e97000)

I'm not sure what they did in 5.5.  Maybe they static-linked and inlined 
the functions or something.  Strange.  That explains why it's not 
working though.  I would expect it to work with any program that uses 
libmysqlclient.  Try it with a third-party mysql app and see what happens.

Dave


On 01/18/2013 12:46 PM, David Muse wrote:
> Hmmm.  The mysql 5.1 library should work with 5.5.  I have a client that
> I think it using it with 5.5, but I could be wrong about that.  It's
> possible that one of the MySQL internal structures changed between 5.1
> and 5.5 and the code needs to be updated to handle that.  Take a look at
> the file src/api/mysql/mysql.cpp.  Near the top there are several
> ifdef's for different versions of MySQL.  Compare the structures defined
> for 5.1 with the ones defined in the mysql headers for 5.5.  If the
> sizes look different, then that could be the problem.  Otherwise, you'd
> have to enable debug and trace through it.
>
> I just looked at the code for the postgres drop-in library and I don't
> see a function that returns the protocol version, so that could be
> tricky to solve.  It might be returned in a struct or there could be
> some set of commands that get run or something.  I'll look into it.  It
> should be possible to get it working in an upcoming release.
>
> Dave
>
> On 01/18/2013 12:23 PM, Petros Moisiadis wrote:
>> I' ve tested sqlrelay's drop-in replacement libraries for MySQL and
>> PostgreSQL on Debian Wheezy (x86_64).
>>
>> The drop-in for MySQL does not seem to work. It just hangs when trying
>> to connect with the mysql client program. It asks for password and after
>> entering it, it freezes without ever giving back a mysql prompt. Maybe
>> this is related to the fact that Debian Wheezy comes with mysql 5.5, but
>> sqlrelay provides libraries up to 5.1? If this is not relevant, I would
>> be glad to do any tests needed to narrow down the possible causes of the
>> problem.
>>
>> The drop-in replacement library for PostgreSQL works when using the psql
>> client program.
>> However, it is not usable if trying to connect with psycopg2, the most
>> popular library to interface with postgres from python. In particular,
>> psycopg2 checks for the protocol version of the connection and if it is
>> not 3, it exits. Maybe sqlrelay's implementation of postgres api does
>> not support protocol version requests. Dave, if that is the case, would
>> it be easy to include support for this type of requests in a following
>> release of sqlrelay?
>>
>> ------------------------------------------------------------------------------
>>
>> Master HTML5, CSS3, ASP.NET, MVC, AJAX, Knockout.js, Web API and
>> much more. Get web development skills now with LearnDevNow -
>> 350+ hours of step-by-step video tutorials by Microsoft MVPs and experts.
>> SALE $99.99 this month only -- learn more at:
>> http://p.sf.net/sfu/learnmore_122812
>> _______________________________________________
>> 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
>>

Re: [Sqlrelay-discussion] Testing sqlrelay's drop-in replacement libraries on Debian Wheezy

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

What version of psycopg2 is on Wheezy?

The attached code (also in CVS) should work with psycopg2-2.4.6 (and 
maybe other 2.4's)

However, there are some caveats...

Psycopg runs a begin query to start a transaction, so if the db you're 
using doesn't support begin queries (Oracle doesn't, for example) then 
you need to enable fake transaction blocks by adding 
faketransactionblocks=yes to the db connect string.

See: http://sqlrelay.sourceforge.net/sqlrelay/admin/configuring.html for 
more info on that.

Psycopg also wants the date to come back from the DB in YYYY-MM-DD 
format.  It checks the date format and if it's not "ISO" then it tries 
to set it to ISO using a "SET datetype TO 'ISO'" query, which works on 
postgresql but probably not on most databases.  The drop-in lib has been 
tweaked to return ISO no matter what, but if the DB isn't set to return 
dates in that format, then psycopg can't parse the date.  To work around 
this, you can change your database's default date format, or if you 
can't do that, you can use SQL Relay's "session query" feature to set 
the date format for the duration of the session.  For example, with Oracle:

<instance ...>
     ...
     </users>
         <session>
             <start>
                 <runquery>alter session set 
nls_date_format='YYYY-MM-DD'</runquery>
             </start>
             <end>
                 <runquery>alter session set 
nls_date_format='DD-MON-YYYY'</runquery>
             </end>
         </session>
     <connections>
     ...
</instance>


Another caveat is the client_encoding.  For now, the drop-in lib returns 
UTF8 when asked for the client_encoding, which may or may not be 
correct, depending on your set up.


The attached file won't work with Psycopg 2.2 if SQL Relay is aimed at a 
non-postgresql DB because 2.2 runs the "show client_encoding" query to 
get the client encoding, rather than calling PQparameterStatus, which 
non-postgredsql db's don't like.  I can't seem to get it working with a 
postgresql db either though, so something else must be wrong too.  Maybe 
I'll figure it out soon though.


Anyway, I only tried some simple tests but they worked.  Give it a try 
if you're using psycopg 2.4 and let me know if you run into any issues.

Dave
dav...@fi...


On 01/18/2013 03:12 PM, David Muse wrote:
> Interesting.  The mysql client for 5.5 doesn't appear to be against the
> libmysqlclient library at all:
>
> [dmuse@localhost mysql]$ mysql --version
> mysql  Ver 14.14 Distrib 5.5.28, for Linux (x86_64) using readline 5.1
> [dmuse@localhost mysql]$ ldd /usr/bin/mysql
>      linux-vdso.so.1 =>  (0x00007fff22d33000)
>      libpthread.so.0 => /lib64/libpthread.so.0 (0x0000003775200000)
>      libz.so.1 => /lib64/libz.so.1 (0x0000003775e00000)
>      librt.so.1 => /lib64/librt.so.1 (0x0000003775a00000)
>      libssl.so.10 => /lib64/libssl.so.10 (0x0000003785e00000)
>      libcrypto.so.10 => /lib64/libcrypto.so.10 (0x0000003783a00000)
>      libdl.so.2 => /lib64/libdl.so.2 (0x0000003775600000)
>      libncurses.so.5 => /lib64/libncurses.so.5 (0x0000003788600000)
>      libtinfo.so.5 => /lib64/libtinfo.so.5 (0x0000003785a00000)
>      libstdc++.so.6 => /lib64/libstdc++.so.6 (0x000000377e600000)
>      libm.so.6 => /lib64/libm.so.6 (0x0000003776200000)
>      libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x0000003778600000)
>      libc.so.6 => /lib64/libc.so.6 (0x0000003774e00000)
>      /lib64/ld-linux-x86-64.so.2 (0x0000003774a00000)
>      libgssapi_krb5.so.2 => /lib64/libgssapi_krb5.so.2 (0x0000003782e00000)
>      libkrb5.so.3 => /lib64/libkrb5.so.3 (0x0000003783200000)
>      libcom_err.so.2 => /lib64/libcom_err.so.2 (0x000000377fe00000)
>      libk5crypto.so.3 => /lib64/libk5crypto.so.3 (0x0000003783e00000)
>      libkrb5support.so.0 => /lib64/libkrb5support.so.0 (0x0000003783600000)
>      libkeyutils.so.1 => /lib64/libkeyutils.so.1 (0x0000003784600000)
>      libresolv.so.2 => /lib64/libresolv.so.2 (0x0000003776a00000)
>      libselinux.so.1 => /lib64/libselinux.so.1 (0x0000003776600000)
>
> or use any of the mysql client api functions:
>
> [dmuse@localhost mysql]$ ltrace -e "mysql_real_connect" mysql --host=db
> --user=test --password=test
> ERROR 1045 (28000): Access denied for user 'test'@'192.168.1.73' (using
> password: YES)
>
> 5.1 uses libmysqlclient though:
>
> [dmuse@fedora ~]$ mysql --version
> mysql  Ver 14.14 Distrib 5.1.60, for redhat-linux-gnu (i386) using
> readline 5.1
> [dmuse@fedora ~]$ ldd /usr/bin/mysql
>      linux-gate.so.1 =>  (0x0043e000)
>      libncursesw.so.5 => /lib/libncursesw.so.5 (0x00475000)
>      libtinfo.so.5 => /lib/libtinfo.so.5 (0x0033f000)
>      libpthread.so.0 => /lib/libpthread.so.0 (0x0079f000)
>      libmysqlclient.so.16 => /usr/lib/mysql/libmysqlclient.so.16
> (0x00110000)
>      libcrypt.so.1 => /lib/libcrypt.so.1 (0x00511000)
>      libnsl.so.1 => /lib/libnsl.so.1 (0x006ba000)
>      libssl.so.10 => /usr/lib/libssl.so.10 (0x00284000)
>      libcrypto.so.10 => /lib/libcrypto.so.10 (0x007ba000)
>      libz.so.1 => /lib/libz.so.1 (0x00e5c000)
>      libstdc++.so.6 => /usr/lib/libstdc++.so.6 (0x00d0e000)
>      libm.so.6 => /lib/libm.so.6 (0x00c26000)
>      libgcc_s.so.1 => /lib/libgcc_s.so.1 (0x00b1a000)
>      libc.so.6 => /lib/libc.so.6 (0x00946000)
>      libdl.so.2 => /lib/libdl.so.2 (0x002dc000)
>      /lib/ld-linux.so.2 (0x00c60000)
>      libfreebl3.so => /lib/libfreebl3.so (0x002e1000)
>      libgssapi_krb5.so.2 => /lib/libgssapi_krb5.so.2 (0x0035f000)
>      libkrb5.so.3 => /lib/libkrb5.so.3 (0x00541000)
>      libcom_err.so.2 => /lib/libcom_err.so.2 (0x0032e000)
>      libk5crypto.so.3 => /lib/libk5crypto.so.3 (0x00397000)
>      libresolv.so.2 => /lib/libresolv.so.2 (0x00ebc000)
>      libkrb5support.so.0 => /lib/libkrb5support.so.0 (0x0072e000)
>      libkeyutils.so.1 => /lib/libkeyutils.so.1 (0x0041e000)
>      libselinux.so.1 => /lib/libselinux.so.1 (0x00e97000)
>
> I'm not sure what they did in 5.5.  Maybe they static-linked and inlined
> the functions or something.  Strange.  That explains why it's not
> working though.  I would expect it to work with any program that uses
> libmysqlclient.  Try it with a third-party mysql app and see what happens.
>
> Dave
>
>
> On 01/18/2013 12:46 PM, David Muse wrote:
>> Hmmm.  The mysql 5.1 library should work with 5.5.  I have a client that
>> I think it using it with 5.5, but I could be wrong about that.  It's
>> possible that one of the MySQL internal structures changed between 5.1
>> and 5.5 and the code needs to be updated to handle that.  Take a look at
>> the file src/api/mysql/mysql.cpp.  Near the top there are several
>> ifdef's for different versions of MySQL.  Compare the structures defined
>> for 5.1 with the ones defined in the mysql headers for 5.5.  If the
>> sizes look different, then that could be the problem.  Otherwise, you'd
>> have to enable debug and trace through it.
>>
>> I just looked at the code for the postgres drop-in library and I don't
>> see a function that returns the protocol version, so that could be
>> tricky to solve.  It might be returned in a struct or there could be
>> some set of commands that get run or something.  I'll look into it.  It
>> should be possible to get it working in an upcoming release.
>>
>> Dave
>>
>> On 01/18/2013 12:23 PM, Petros Moisiadis wrote:
>>> I' ve tested sqlrelay's drop-in replacement libraries for MySQL and
>>> PostgreSQL on Debian Wheezy (x86_64).
>>>
>>> The drop-in for MySQL does not seem to work. It just hangs when trying
>>> to connect with the mysql client program. It asks for password and after
>>> entering it, it freezes without ever giving back a mysql prompt. Maybe
>>> this is related to the fact that Debian Wheezy comes with mysql 5.5, but
>>> sqlrelay provides libraries up to 5.1? If this is not relevant, I would
>>> be glad to do any tests needed to narrow down the possible causes of the
>>> problem.
>>>
>>> The drop-in replacement library for PostgreSQL works when using the psql
>>> client program.
>>> However, it is not usable if trying to connect with psycopg2, the most
>>> popular library to interface with postgres from python. In particular,
>>> psycopg2 checks for the protocol version of the connection and if it is
>>> not 3, it exits. Maybe sqlrelay's implementation of postgres api does
>>> not support protocol version requests. Dave, if that is the case, would
>>> it be easy to include support for this type of requests in a following
>>> release of sqlrelay?
>>>
>>> ------------------------------------------------------------------------------
>>>
>>>
>>> Master HTML5, CSS3, ASP.NET, MVC, AJAX, Knockout.js, Web API and
>>> much more. Get web development skills now with LearnDevNow -
>>> 350+ hours of step-by-step video tutorials by Microsoft MVPs and
>>> experts.
>>> SALE $99.99 this month only -- learn more at:
>>> http://p.sf.net/sfu/learnmore_122812
>>> _______________________________________________
>>> 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
>>>