sqlrelay-discussion — Discussion of topics related to SQL Relay

Re: [Sqlrelay-discussion] (no subject)

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

Roger wrote:
> On Tue, 28 Jul 2009, David Muse wrote:
>
>   
>> See responses below.
>>
>> David Muse
>>     
>
> 	Thanks, they are what I expected. And your your question for me:
>
>   
>>> 1) Does the native API (Perl API) only allow a single connection to
>>> SQLRelay at a time?
>>>
>>>       
>> No, it allows multiple connections to SQL Relay.
>>
>> There was originally a limit, but it was fixed a long time ago.  What
>> doc says that only 1 connection will go at a time?  Let me know and I'll
>> fix it.
>>     
>
> http://sqlrelay.sourceforge.net/sqlrelay/programming/perl.html
>
> Under "Establishing a Session", 2nd last paragraph:
>
> For the duration of the session, the client stays connected to a database
> connection daemon. While one client is connected, no other client can
> connect. Care should be taken to minimize the length of a session.
>
> 	That seems to explicitly state that only one client can connect at
> a time.
>   

Wow, that is worded badly :)

What it means is that only one client can connect to each connection
daemon at a time.  But, you can configure any number of connection
daemons to run.  The connections="X" parameter in the sqlrelay.conf file
indicates how many connection daemons are started, and by extension, how
many clients can connect simultaneously.  If connections="1" is set in
the config file, then only 1 client could connect at a time.

I'll reword that part of the docs.

Dave
dav...@fi...

Re: [Sqlrelay-discussion] Cursor Error

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

I recently put a fix in CVS that should fix this problem with MySQL.

A patch would be to modify src/connections/mysql/mysqlconnection.C and
change the openCursor method as follows:

bool mysqlcursor::openCursor(uint16_t id) {
    stmt=mysql_stmt_init(&mysqlconn->mysql);
    return sqlrcursor_svr::openCursor(id);
}

Give that a try, I think it will fix the problem.

David Muse
dav...@fi...

b-r...@ro... wrote:
> 	(This is a repost, since the original garnered no responses.)
>
> 	I've been working with the most recent version for a couple weeks,
> and for the most part, it functions quite well. However, periodically I
> get the following error:
>
> Failed to get a cursor id. A network error may have ocurred.
>
> 	Since I'm not going across the network, I know that is not the
> problem. However, there doesn't seem to be any other information out there
> on this error.
>
> 	The query (SELECT) itself is perfectly valid, and will execute if
> I simply rerun it. However, when it happens in a web script, an end user
> gets a script failure message on their browser, which isn't very elegant.
>
> 	My questions:
>
> 1) What is happening?
>
> 2) Is there a solution to the problem?
>
> 3) Is there a preferred kludge (such as checking for this specific error
> text and rerunning the query if found) if there is no solution? (I'm
> currently looping - if the error occurs, then I redo the query scratch.)
>
> 	Thanks.
>
> R
>
> ------------------------------------------------------------------------------
> Enter the BlackBerry Developer Challenge  
> This is your chance to win up to $100,000 in prizes! For a limited time, 
> vendors submitting new applications to BlackBerry App World(TM) will have
> the opportunity to enter the BlackBerry Developer Challenge. See full prize  
> details at: http://p.sf.net/sfu/Challenge
> _______________________________________________
> Sqlrelay-discussion mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
>
> __________________________________________________
> D O T E A S Y - "Join the web hosting revolution!"
>              http://www.doteasy.com
>   

Re: [Sqlrelay-discussion] (no subject)

[Roger <b-r...@ro...>]

On Tue, 28 Jul 2009, David Muse wrote:

> See responses below.
>
> David Muse

	Thanks, they are what I expected. And your your question for me:

> > 1) Does the native API (Perl API) only allow a single connection to
> > SQLRelay at a time?
> >
> No, it allows multiple connections to SQL Relay.
>
> There was originally a limit, but it was fixed a long time ago.  What
> doc says that only 1 connection will go at a time?  Let me know and I'll
> fix it.

http://sqlrelay.sourceforge.net/sqlrelay/programming/perl.html

Under "Establishing a Session", 2nd last paragraph:

For the duration of the session, the client stays connected to a database
connection daemon. While one client is connected, no other client can
connect. Care should be taken to minimize the length of a session.

	That seems to explicitly state that only one client can connect at
a time.

-- 
Roger

Re: [Sqlrelay-discussion] [sqlrelay] new instance won't start :-(

[Roger <b-r...@ro...>]

On Tue, 28 Jul 2009, David Muse wrote:

> This kind of problem is usually caused when the xml file has a problem,
> like a missing double-quote or missing end-tag or something similar.
>
> Try running xmllint on the sqlrelay.conf file and see if it turns up any
> errors.
>
> Dave

	Thanks for the tip. However...

	With --valid (or even with no options), the only thing I see is
the output is reformatted a bit, but there are no errors reported. As I
indicated, I copied one instance and simply changed the id, database,
port, and the maxconnections, as appropriate. When I restart, the dummy
(of course) does nothing, the other two instances restart, but nothing
happens for the new instance.

-- 
Roger

Re: [Sqlrelay-discussion] (no subject)

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

Roger,

See responses below.

David Muse
dav...@fi...

b-r...@ro... wrote:
> 	(This is a resend, as the original garnered no responses.)
>
> 	Some initial tests with the Perl API indicated that we got a
> performance hit of 50% or more by using the DBI directly. I'm not
> particularly worried by this, as the connection pooling is a good
> trade-off, and we will realize a considerable performance gain by
> converting our existing applications to use MySQL rather than CSV.
>
> 	I really like the Perl DBI API as, other than the initial
> dbi->connect(), everything else just works. At the moment, that's what I'm
> using. Here is what I have done:
>
> 	I set up a 'low level' library with basic routines to access the
> database tables - delete, insert, select, update. Each routine does it's
> own dbi->connect(). There are no worries about looping through millions of
> inserts to load a table - the rows get inserted on an as-needed basis, not
> in bulk. The application scripts never call these routines directly.
>
> 	Next, I set up intermediary libraries that set up the various SQL
> statements used, so they get formatted the way the applications require.
> The application scripts call these libraries.
>
> 	One of the reasons for using SQLRelay is to avoid setting up and
> breaking down the database connections, but since I'm using the Perl DBI
> API, I don't think that is happening, because I still have to do a
> dbi->connect(), even to get to SQLRelay. When I looked at the
> documentation for the Perl API (native), it indicated that only session
> can run at a time.
>
> 	My questions:
>
> 1) Does the native API (Perl API) only allow a single connection to
> SQLRelay at a time?
>   
No, it allows multiple connections to SQL Relay.

There was originally a limit, but it was fixed a long time ago.  What
doc says that only 1 connection will go at a time?  Let me know and I'll
fix it.
> 2) If so, doesn't that negate the connection pooling benefits of SQLRelay?
>
> 3) If using the Perl DBI API (DBD::SQLRelay), how does the connection
> performance to SQLRelay compare to connecting directly to the database?
>   
It will be slower because of the extra layer.  I don't have stats on it,
but I'd imagine that it would be small, but measurable.
> 4) Is there a significant performance difference between using the native
> API versus the Perl DBI API? What would that be?
>   
The difference between DBI and native perl should be small, but measurable.
> 5) I only want to send basic SQL statements to be executed, with only
> MySQL built-in functions (basic delete, insert, select, and update). Which
> API is recommended - native or Perl DBI API?
>   
For the best performance, use the native API.  DBI is easier to use
though.  The performance difference should be small.
> 	Thanks,
>
> R
>
> ------------------------------------------------------------------------------
> Enter the BlackBerry Developer Challenge  
> This is your chance to win up to $100,000 in prizes! For a limited time, 
> vendors submitting new applications to BlackBerry App World(TM) will have
> the opportunity to enter the BlackBerry Developer Challenge. See full prize  
> details at: http://p.sf.net/sfu/Challenge
> _______________________________________________
> Sqlrelay-discussion mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
>
> __________________________________________________
> D O T E A S Y - "Join the web hosting revolution!"
>              http://www.doteasy.com
>   

Re: [Sqlrelay-discussion] [sqlrelay] new instance won't start :-(

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

This kind of problem is usually caused when the xml file has a problem,
like a missing double-quote or missing end-tag or something similar.

Try running xmllint on the sqlrelay.conf file and see if it turns up any
errors.

Dave
dav...@fi...

Roger wrote:
> 	My 'sqlrelay.conf' file had 3 instances defined. The first one is
> dummy on port 9000. Next two are separate MySQL databases that are
> functioning, on ports 9001 and 9002.
>
> 	I have (tried to) set up a development database for one of the two
> that are now running, but duplicating the production indstance and
> changing the port to 2003, the instance id to something unique, and the
> database name to the development database. (I also reduced the number of
> connections and cursors.)
>
> 	However, when I restart sqlrelay - I still only get the other two,
> and not the new one defined. I have tried moving the relative positions of
> the instance definitions, to no avail.
>
> 	Note: Excluding the dummy instance, there are 100 maxconnections
> for the other two instances, and 10 maxconnections for the newly defined
> (but not working) instance. The my.cnf file has 200 connections to MySQL,
> maximum.
>
> 	Anyone have ideas for tracking down and correcting the problem?
> Would this issue be getting logged somewhere that I'm not aware of?
>
> 	Thanks.
>
>   

[Sqlrelay-discussion] [sqlrelay] new instance won't start :-(

[Roger <b-r...@ro...>]

	My 'sqlrelay.conf' file had 3 instances defined. The first one is
dummy on port 9000. Next two are separate MySQL databases that are
functioning, on ports 9001 and 9002.

	I have (tried to) set up a development database for one of the two
that are now running, but duplicating the production indstance and
changing the port to 2003, the instance id to something unique, and the
database name to the development database. (I also reduced the number of
connections and cursors.)

	However, when I restart sqlrelay - I still only get the other two,
and not the new one defined. I have tried moving the relative positions of
the instance definitions, to no avail.

	Note: Excluding the dummy instance, there are 100 maxconnections
for the other two instances, and 10 maxconnections for the newly defined
(but not working) instance. The my.cnf file has 200 connections to MySQL,
maximum.

	Anyone have ideas for tracking down and correcting the problem?
Would this issue be getting logged somewhere that I'm not aware of?

	Thanks.

-- 
Roger

Re: [Sqlrelay-discussion] [sqlrelay] Cursor Error

[Roger <b-r...@ro...>]

	Thanks for the response...

On Mon, 27 Jul 2009, Benoit Cattié wrote:

> i got this error when using idleclienttimeout parameters and idle time
> is reached. Maybe this error could also happen with "sessiontimeout"
> parameter. Try to set an higher value. (regarding to your script
> execution time)

	I'll try 50, instead of 10, and monitor the results

	Also thinking that, if it doesn't work, I'll increase cursors to
100 to match the maxconnections.

> I see maxqueuelength="0" in your config.
> All 100 connexions are spawned at start ?

	No. I expect that to be covered by 'growby', and that
'maxqueuelength' just means there would be no waiting before something is
spawned.

-- 
Roger

Re: [Sqlrelay-discussion] Auto reconnect not working with freetds and sybase, am I missing something?

[Alexandre F. <lex...@gm...>]

The bug is still there with sqlrelay-0.41 and fresh from cvs


On Wed, Apr 29, 2009 at 11:47 AM, David Muse <dav...@fi...> wrote:
> I have a vague memory of fixing a bug like that with sybase a few
> versions ago.  I can't find it in the changelog though.  You might want
> to try a newer version of SQL Relay, it may just be fixed.
>
> If the problem still exists in the current version, let me know and I'll
> fix it.
>
> Dave
> dav...@fi...
>
> On Fri, 2009-04-03 at 11:19 -0400, Alexandre Forget wrote:
>> Hi,
>>
>> My problem is with the connection between sqlrelay and sybase, it is
>> not automatically reconnected.
>> To test this case I kill the connection in sybase with a command in sqlrsh:
>>
>> > select * from master..sysprocesses;
>> > kill spid
>>
>> After this command I can no longer ping the database
>> > ping;
>>  database is down
>>
>> This is the debug info I get from the sqlr-connection
>> =====================
>> done getting command
>> ping
>> unlinking /var/cache/sqlrelay/tmp/ipc/example-sybase1
>> relogging in...
>> closing cursors...
>> 0
>> 1
>> 2
>> 3
>> 4
>> done closing cursors...
>> trying...
>> initializing cursors...
>> 0
>> 1
>> 2
>> 3
>> 4
>> done initializing cursors
>> done relogging in
>> creating /var/cache/sqlrelay/tmp/ipc/example-sybase1
>> getting command...
>> =======================
>>
>> If I restart sqlrsh the database stay down if I am connected to the
>> same sqlr-connection process. To me, it seem sqlrelay try to reconnect
>> to the db but fail to get a working connexion
>>
>>
>> If I restart sqlrelay width "sqlr-stop ;sqlr-start -id example" then
>> all the connections are working.
>>
>> versions:
>>
>> sqlrelay: 0.39
>> freetds: 0.82
>> sybase: 11.0.3
>>
>> thanks
>>
>
>
> __________________________________________________
> D O T E A S Y - "Join the web hosting revolution!"
>             http://www.doteasy.com
>
> ------------------------------------------------------------------------------
> Register Now & Save for Velocity, the Web Performance & Operations
> Conference from O'Reilly Media. Velocity features a full day of
> expert-led, hands-on workshops and two days of sessions from industry
> leaders in dedicated Performance & Operations tracks. Use code vel09scf
> and Save an extra 15% before 5/3. http://p.sf.net/sfu/velocityconf
> _______________________________________________
> Sqlrelay-discussion mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
>



-- 
Alexandre Forget

Re: [Sqlrelay-discussion] [sqlrelay] Cursor Error

[Benoit C. <ben...@fr...>]

Hi,

i got this error when using idleclienttimeout parameters and idle time
is reached.
Maybe this error could also happen with "sessiontimeout" parameter.

Try to set an higher value. (regarding to your script execution time)

I see maxqueuelength="0" in your config. 
All 100 connexions are spawned at start ?


Best regards

--
Benoit Cattié



Roger a écrit :
> Failed to get a cursor id. A network error may have ocurred. Success
>
> This error is occuring on an increasing frequency with the use of
> DBD::SQLRelay, as the two databases I'm using SQLRelay to access are
> getting busier. I have a kludge in place that will disconnect from
> SQLRelay, reconnect, and retry the query (in a loop). It works and has
> never had to retry more than once - so far. I am not using network at all
> (local database, using sockets). I am concerned that I can find no
> information about this error and what can be done to fix it.
>
> Any help, fixes, pointers, would be appreciated. Thanks.
>
> I am using sqlrelay-0.41 on a CentOS 5.x system with a MySQL 5.0 RDBMS.
> The following is my my.cnf file, and one of the instance definitions from
> the sqlrelay.conf file (two instances are identical except for port and
> database they are accessing - errors appear for both):
>
> === my.cnf ===
> [mysqld]
>
> datadir=/var/lib/mysql
> socket=/var/lib/mysql/mysql.sock
> user=mysql
>
> # Default to using old password format for compatibility with mysql 3.x
> # clients (those using the mysqlclient10 compatibility package).
> #old_passwords   = 1
> old_passwords   = 0
>
> skip-networking
> log_slow_queries
> log_long_format
> skip-bdb
> query_cache_type    = 1
> thread_cache_size   = 40
> wait_timeout        = 15
> max_connections     = 200
> max_connect_errors  = 10
> table_cache         = 1024
> max_allowed_packet  = 2M
> binlog_cache_size   = 1M
> max_heap_table_size = 16M
> key_buffer_size     = 16M
> read_buffer_size    = 2M
> sort_buffer_size    = 8M
> join_buffer_size    = 1M
> query_cache_size    = 8M
> query_cache_limit   = 1M
> tmp_table_size      = 64M
> long_query_time     = 2
> thread_concurrency  = 8
> read_rnd_buffer_size    = 2M
> innodb_buffer_pool_size = 2G
> innodb_lock_wait_timeout    = 10
> innodb_max_dirty_pages_pct  = 90
> innodb_additional_mem_pool_size = 16M
> innodb_flush_log_at_trx_commit  = 1
>
> [mysqld_safe]
>
> log-error           = /var/log/mysqld.log
> pid-file            = /var/run/mysqld/mysqld.pid
> open-files-limit    = 4096
> === ===
>
> === sqlrelay.conf ===
>     <instance
>         id="testid"
>         port="9002"
>         dbase="mysql"
>         handoff="pass"
>         connections="1"
>         maxconnections="100"
>         maxqueuelength="0"
>         cursors="50"
>         growby="2"
>         ttl="300"
>         maxsessioncount="0"
>         sessiontimeout="10"
>     >
>     <users>
>         <user
>             user="testuser"
>             password="testpassword"
>         />
>     </users>
>     <connections>
>         <connection
>             connectionid="con1"
>             string="user=testuser;password=testpassword;db=testdatabase;"
>         />
>     </connections>
>     </instance>
> === ===
>
>   

[Sqlrelay-discussion] [sqlrelay] Cursor Error

[Roger <b-r...@ro...>]

Failed to get a cursor id. A network error may have ocurred. Success

This error is occuring on an increasing frequency with the use of
DBD::SQLRelay, as the two databases I'm using SQLRelay to access are
getting busier. I have a kludge in place that will disconnect from
SQLRelay, reconnect, and retry the query (in a loop). It works and has
never had to retry more than once - so far. I am not using network at all
(local database, using sockets). I am concerned that I can find no
information about this error and what can be done to fix it.

Any help, fixes, pointers, would be appreciated. Thanks.

I am using sqlrelay-0.41 on a CentOS 5.x system with a MySQL 5.0 RDBMS.
The following is my my.cnf file, and one of the instance definitions from
the sqlrelay.conf file (two instances are identical except for port and
database they are accessing - errors appear for both):

=== my.cnf ===
[mysqld]

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql

# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
#old_passwords   = 1
old_passwords   = 0

skip-networking
log_slow_queries
log_long_format
skip-bdb
query_cache_type    = 1
thread_cache_size   = 40
wait_timeout        = 15
max_connections     = 200
max_connect_errors  = 10
table_cache         = 1024
max_allowed_packet  = 2M
binlog_cache_size   = 1M
max_heap_table_size = 16M
key_buffer_size     = 16M
read_buffer_size    = 2M
sort_buffer_size    = 8M
join_buffer_size    = 1M
query_cache_size    = 8M
query_cache_limit   = 1M
tmp_table_size      = 64M
long_query_time     = 2
thread_concurrency  = 8
read_rnd_buffer_size    = 2M
innodb_buffer_pool_size = 2G
innodb_lock_wait_timeout    = 10
innodb_max_dirty_pages_pct  = 90
innodb_additional_mem_pool_size = 16M
innodb_flush_log_at_trx_commit  = 1

[mysqld_safe]

log-error           = /var/log/mysqld.log
pid-file            = /var/run/mysqld/mysqld.pid
open-files-limit    = 4096
=== ===

=== sqlrelay.conf ===
    <instance
        id="testid"
        port="9002"
        dbase="mysql"
        handoff="pass"
        connections="1"
        maxconnections="100"
        maxqueuelength="0"
        cursors="50"
        growby="2"
        ttl="300"
        maxsessioncount="0"
        sessiontimeout="10"
    >
    <users>
        <user
            user="testuser"
            password="testpassword"
        />
    </users>
    <connections>
        <connection
            connectionid="con1"
            string="user=testuser;password=testpassword;db=testdatabase;"
        />
    </connections>
    </instance>
=== ===

-- 
Roger

[Sqlrelay-discussion] Why no 'errno' with DBD::SQLRelay?

[<b-r...@ro...>]

	(This is a repost, since the original garnered no responses.)

	I've got a Perl package that checks for the errno when there is a
MySQL error. However, I found that SQLRelay was not passing them through -
just the error text. As a result, I was getting failures that were not
being caught.

	Why does SQLRelay not pass through the errno?

$errno = $dbh->{ 'mysql_errno' } ;

	Thanks,

R

[Sqlrelay-discussion] Cursor Error

[<b-r...@ro...>]

	(This is a repost, since the original garnered no responses.)

	I've been working with the most recent version for a couple weeks,
and for the most part, it functions quite well. However, periodically I
get the following error:

Failed to get a cursor id. A network error may have ocurred.

	Since I'm not going across the network, I know that is not the
problem. However, there doesn't seem to be any other information out there
on this error.

	The query (SELECT) itself is perfectly valid, and will execute if
I simply rerun it. However, when it happens in a web script, an end user
gets a script failure message on their browser, which isn't very elegant.

	My questions:

1) What is happening?

2) Is there a solution to the problem?

3) Is there a preferred kludge (such as checking for this specific error
text and rerunning the query if found) if there is no solution? (I'm
currently looping - if the error occurs, then I redo the query scratch.)

	Thanks.

R

[Sqlrelay-discussion] (no subject)

[<b-r...@ro...>]

	(This is a resend, as the original garnered no responses.)

	Some initial tests with the Perl API indicated that we got a
performance hit of 50% or more by using the DBI directly. I'm not
particularly worried by this, as the connection pooling is a good
trade-off, and we will realize a considerable performance gain by
converting our existing applications to use MySQL rather than CSV.

	I really like the Perl DBI API as, other than the initial
dbi->connect(), everything else just works. At the moment, that's what I'm
using. Here is what I have done:

	I set up a 'low level' library with basic routines to access the
database tables - delete, insert, select, update. Each routine does it's
own dbi->connect(). There are no worries about looping through millions of
inserts to load a table - the rows get inserted on an as-needed basis, not
in bulk. The application scripts never call these routines directly.

	Next, I set up intermediary libraries that set up the various SQL
statements used, so they get formatted the way the applications require.
The application scripts call these libraries.

	One of the reasons for using SQLRelay is to avoid setting up and
breaking down the database connections, but since I'm using the Perl DBI
API, I don't think that is happening, because I still have to do a
dbi->connect(), even to get to SQLRelay. When I looked at the
documentation for the Perl API (native), it indicated that only session
can run at a time.

	My questions:

1) Does the native API (Perl API) only allow a single connection to
SQLRelay at a time?

2) If so, doesn't that negate the connection pooling benefits of SQLRelay?

3) If using the Perl DBI API (DBD::SQLRelay), how does the connection
performance to SQLRelay compare to connecting directly to the database?

4) Is there a significant performance difference between using the native
API versus the Perl DBI API? What would that be?

5) I only want to send basic SQL statements to be executed, with only
MySQL built-in functions (basic delete, insert, select, and update). Which
API is recommended - native or Perl DBI API?

	Thanks,

R

Re: [Sqlrelay-discussion] Zope adapter limit of 1001 records?

[Cameron L. <cla...@gm...>]

Many thanks, again, for your help with this.  You can imagine what a help it
is to have correct results, rather than ones truncated after 1001 records.

Can you effectively use my help updating the ZSQLRelayDA adapter?  I
certainly owe you.

We have identified a few errors that we can work around for now.  One,
however, is both puzzling and apparently crucial to work I'm preparing.  Let
me know if you'd like me to report this in the mailing list or elsewhere:

Briefly,
    sudo -u test-user sqlrsh $HOST 9005 "" $ACCOUNT $PASSWORD $SCRIPT
segfaults.  "test-user" is an unremarkable account, with pedestrian
privileges.  I can
    su test-user
    sqlrsh $HOST 9005 "" $ACCOUNT $PASSWORD $SCRIPT
and everything goes fine.

Have you come across this before?  What do you recommend?  Shall I generate
sqlrsh with debugging symbols, so I can get a readable backtrace?


On Tue, Jul 7, 2009 at 10:57 PM, David Muse <dav...@fi...>wrote:

> Hey Cameron,
>
> I figured out the 1001 rows issue.  By default, a ZSQLMethod has a limit
> of 1000 rows and passes this limit into the ZSQLRelayDA adapter when it
> runs the query.  To change this, click the Advanced tab of the
> ZSQLMethod and set the "Maximum rows to retrieve" setting to a larger
> number.
>
> If you have a gazillion ZSQLMethods, then you can probably modify
> lib/python/Shared/DC/ZRDB/DA.py and change the line:
>
> max_rows_=1000
>
> to something else.
>
> Arguably the ZSQLRelayDA adapter does have a bug that's causing it to
> retrieve 1001 rather than 1000 rows.
>
>
> Aside from that though, the adapter doesn't work with Zope 2.11 or
> higher because it uses several deprecated objects and attributes.  So,
> that does need to be fixed, ideally in a back-compatible way.
>    ...

Re: [Sqlrelay-discussion] [sqlrelay] propagating placeholders (question marks) with DBD::SQLRelay

[<b-r...@ro...>]

	Thanks (and also to "Lawrence, Gabriel" <gla...@uc...>) for
the responses. See inline...

On Thu, 9 Jul 2009, David Muse wrote:

> [SNIP EXPLANATION]
> In the short term, the only easy solution is to add fakebinds=yes to the
> db connect string.  For example:
>
>     <connection connectionid="mysqltest"
> string="user=blah;password=blah;db=blah;fakebinds=yes" metric="1"/>
>
> In this case, the ?'s will be converted to colon-delimited variables but
> SQL Relay will rewrite the query rather than pass the binds directly to
> the db.  It's less efficient, but may not be too bad.

	That did not work. Yes, I did restart SQLRelay. There was no
change to the results at all. I reverted that change.

> Alternatively, you might be able to comment out the line:
>
>     $statement =~ s/\?/":" . ++$count/eg;
>
> in the installed SQLRelay.pm file, which should be line around 135.
> This will disable conversion of ?'s to colon-delimited variables.  The
> query should be passed directly into the DB as-is with ?'s.
>
> Give these a shot, let me know if they work for you or not.

	Yes, the latter did work. I have two identical copies of the file:

/usr/lib/perl5/site_perl/5.8.8/DBD/SQLRelay.pm
/usr/src/sqlrelay-0.41/src/api/perl/DBD/SQLRelay.pm

	So, I made the change to both.

	My next question, then: If I choose to use MySQL's bind variables
in the future, how will that be affected? I currently have a kludge in my
own code that fairly handily takes care of the '?' problem, though I'd
like to remove it in favor of this change just made, but I'd hate to find
out that it's either "bind variables and no question mark" or "no bind
variables and question mark". I'd like to know that I can have both.

R.

p.s. And now that I see the list seems to be active, I'll probably repost
my earlier queries

Re: [Sqlrelay-discussion] [sqlrelay] propagating placeholders (question marks) with DBD::SQLRelay

[Lawrence, G. <gla...@uc...>]

I had the same problem in the past and commenting out this worked for me:

 $statement =~ s/\?/":" . ++$count/eg;

in the installed SQLRelay.pm file, which should be line around 135.
This will disable conversion of ?'s to colon-delimited variables.  The
query should be passed directly into the DB as-is with ?'s.

Re: [Sqlrelay-discussion] [sqlrelay] propagating placeholders (question marks) with DBD::SQLRelay

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

A long time ago most of the DB's that SQL Relay supported either used
colon-delimited bind variables (oracle) or didn't support bind variables
at all (mysql, postgresql, msql, sqlite, and others).  For DB's that
didn't natively support binds, SQL Relay would fake binds by rewriting
the query, but required colon-delimited bind variables to do so.  So, at
that time, colon-delimited bind values were required for all cases.

As such, the DBI driver would convert ?'s to colon-delimited bind
variables and pass the rewritten query to SQL Relay.

Since then mysql and postgresql have started supporting bind variables
natively and support for DB's that don't use colons natively was added. 
For those DB's, SQL Relay passes the query directly to the DB.  But the
DBI driver was never updated, it rewrites the query, then SQL Relay
passes a bad query to the DB.  Ideally what it should do is detect the
backend db type and convert the ?'s (or leave them alone) as
appropriate.  The PHP Pear DB driver does this.

In the short term, the only easy solution is to add fakebinds=yes to the
db connect string.  For example:

    <connection connectionid="mysqltest"
string="user=blah;password=blah;db=blah;fakebinds=yes" metric="1"/>

In this case, the ?'s will be converted to colon-delimited variables but
SQL Relay will rewrite the query rather than pass the binds directly to
the db.  It's less efficient, but may not be too bad.

Alternatively, you might be able to comment out the line:

    $statement =~ s/\?/":" . ++$count/eg;

in the installed SQLRelay.pm file, which should be line around 135. 
This will disable conversion of ?'s to colon-delimited variables.  The
query should be passed directly into the DB as-is with ?'s.

Give these a shot, let me know if they work for you or not.

David Muse
dav...@fi...

b-r...@ro... wrote:
> (Hmm. It's been three weeks since my last posts and there have been
> absolutely no responses. I'll try one more time with something new...)
>
> I'm using DBD::SQLRelay as it purports to be directly compatible with Perl
> DBI. This means doing my database accesses the same way as always,
> supposedly.
>
> I attempted to enter (insert or update) column values that include
> question marks ('?'). This fails, with each question mark being translated
> to :1 :2 :3 (etc). I tried every method of escaping and quoting I could
> think of, to no avail. I was using $dbh->do() directly, with and without
> placeholders.
>
> I next went back to basics: Create a simple table and a short script to
> use placeholders and insert a column that contained a '?'.
>
> If I used 'DBI' for a direct connection to the MySQL database, it worked.
>
> If I used DBD::SQLRelay (to take advantage of connection pooling), MySQL
> rejected the SQL statement due to the '?' being translated to ':1' - bad
> syntax.
>
> What gives? How can I properly use placeholders with the DBD::SQLRelay?
>
> Thanks.
>
>   

Re: [Sqlrelay-discussion] Zope adapter limit of 1001 records?

[Cameron L. <cla...@gm...>]

Marvelous!  Many thanks.  I'll spend a couple of days shaking down changes
in our code, and then get back to you so I can begin to repay you by helping
modernize ZSQLRelayDA.
Do you work with Zope?  You seem to have quite a span, being comfortable
wrangling the adapters for several different languages and/or frameworks.

On Tue, Jul 7, 2009 at 10:57 PM, David Muse <dav...@fi...>wrote:

> Hey Cameron,
>
> I figured out the 1001 rows issue.  By default, a ZSQLMethod has a limit
> of 1000 rows and passes this limit into the ZSQLRelayDA adapter when it
> runs the query.  To change this, click the Advanced tab of the
> ZSQLMethod and set the "Maximum rows to retrieve" setting to a larger
> number.
>
> If you have a gazillion ZSQLMethods, then you can probably modify
> lib/python/Shared/DC/ZRDB/DA.py and change the line:
>
> max_rows_=1000
>
> to something else.
>
> Arguably the ZSQLRelayDA adapter does have a bug that's causing it to
> retrieve 1001 rather than 1000 rows.
>
>
> Aside from that though, the adapter doesn't work with Zope 2.11 or
> higher because it uses several deprecated objects and attributes.  So,
> that does need to be fixed, ideally in a back-compatible way.
>
> Dave
> dav...@fi...
>
> Cameron Laird wrote:
> > Super!
> >
> > Yes, I'm ready to commit to a few hours a week on the Zope (and
> > Python?) adapter for SQL Relay.  We can follow-up in this same thread
> > once we have a technical solution.  In the meantime, I'll reply
> > privately so we can set up a development environment and maintenance
> > plan for me.
> >
> > On Tue, Jun 30, 2009 at 10:01 PM, David Muse
> > <dav...@fi... <mailto:dav...@fi...>> wrote:
> >
> >     Someone else reported this issue a while back.  I was able to
> >     reproduce
> >     it, but I didn't spend enough time to track down the exact source.
> >
> >     I plan on putting in some time later this week working on this and
> >     other
> >     issues and getting a release out as soon as possible.
> >
> >     If you guys are willing to put in some work updating and
> >     maintaining the
> >     adapter, then I'd be happy to include the updates in the official
> >     distro.  I'd be grateful if you'd maintain it going forward as well.
> >
> >     Most of the language bindings and adapters were written,
> >     contributed and
> >     maintained by others, and then eventually they quit maintaining
> >     them and
> >     I took over.  Some of them I haven't done too much maintenance on :)
> >     Most are up-to-date because they mimic the c/c++ api, but the ones
> >     that
> >     don't (zope, python db, php pear db, perl dbi, etc.) I usually
> >     just run
> >     some quick tests on to make sure they haven't gotten really broken.
> >     Some may have had long standing bugs that were never addressed.
> >
> >     David Muse
> >     dav...@fi... <mailto:dav...@fi...>
> >
> >     Cameron Laird wrote:
> >     > We have well-reproducible cases that suggest a Z SQLRelay Database
> >     > Connection truncates its results at 1001 records.  I welcome any
> >     > information on my alternatives.  I've searched without success for
> >     > similar results.
> >     >
> >     > Happiest, of course, would be to find that there's some way to
> >     > configure the Database Connection to accept larger results.
> >     >
> >     > More broadly, are there any plans to maintain this adapter?  It
> >     > appears to be at least six years old.  We've collected a few
> >     definite
> >     > errors or at least version skews (improper handling of MONEY
> >     datatype,
> >     > ...).  We'd be open to shouldering the responsibility of
> maintenance
> >     > for the adapter, and/or paying others (firstworks?) to do so.
> >     >
> >     > --
> >     >
> >     > Cameron Laird
> >     > +1 817 280 1145  Building 27, Q2/#35
> >     > +1 281 648 9889
> >     >
> >
> ------------------------------------------------------------------------
> >     >
> >     >
> >
> ------------------------------------------------------------------------------
> >     >
> >     > __________________________________________________
> >     > D O T E A S Y - "Join the web hosting revolution!"
> >     >              http://www.doteasy.com
> >     >
> >
> ------------------------------------------------------------------------
> >     >
> >     > _______________________________________________
> >     > Sqlrelay-discussion mailing list
> >     > Sql...@li...
> >     <mailto:Sql...@li...>
> >     > https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
> >     >
> >     > __________________________________________________
> >     > D O T E A S Y - "Join the web hosting revolution!"
> >     >              http://www.doteasy.com
> >
> >
> >
> ------------------------------------------------------------------------------
> >     _______________________________________________
> >     Sqlrelay-discussion mailing list
> >     Sql...@li...
> >     <mailto:Sql...@li...>
> >     https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
> >
> >
> >
> >
> > --
> >
> > Cameron Laird
> > +1 817 280 1145  Building 27, Q2/#35
> > +1 281 648 9889
> > ------------------------------------------------------------------------
> >
> >
> ------------------------------------------------------------------------------
> >
> > __________________________________________________
> > D O T E A S Y - "Join the web hosting revolution!"
> >              http://www.doteasy.com
> > ------------------------------------------------------------------------
> >
> > _______________________________________________
> > Sqlrelay-discussion mailing list
> > Sql...@li...
> > https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
> >
> > __________________________________________________
> > D O T E A S Y - "Join the web hosting revolution!"
> >              http://www.doteasy.com
>
>
>
> ------------------------------------------------------------------------------
> Enter the BlackBerry Developer Challenge
> This is your chance to win up to $100,000 in prizes! For a limited time,
> vendors submitting new applications to BlackBerry App World(TM) will have
> the opportunity to enter the BlackBerry Developer Challenge. See full prize
> details at: http://p.sf.net/sfu/Challenge
> _______________________________________________
> Sqlrelay-discussion mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
>



-- 

Cameron Laird
+1 817 280 1145  Building 27, Q2/#35
+1 281 648 9889

Re: [Sqlrelay-discussion] Zope adapter limit of 1001 records?

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

Hey Cameron,

I figured out the 1001 rows issue.  By default, a ZSQLMethod has a limit
of 1000 rows and passes this limit into the ZSQLRelayDA adapter when it
runs the query.  To change this, click the Advanced tab of the
ZSQLMethod and set the "Maximum rows to retrieve" setting to a larger
number.

If you have a gazillion ZSQLMethods, then you can probably modify
lib/python/Shared/DC/ZRDB/DA.py and change the line:

max_rows_=1000

to something else.

Arguably the ZSQLRelayDA adapter does have a bug that's causing it to
retrieve 1001 rather than 1000 rows.


Aside from that though, the adapter doesn't work with Zope 2.11 or
higher because it uses several deprecated objects and attributes.  So,
that does need to be fixed, ideally in a back-compatible way.

Dave
dav...@fi...

Cameron Laird wrote:
> Super!
>
> Yes, I'm ready to commit to a few hours a week on the Zope (and
> Python?) adapter for SQL Relay.  We can follow-up in this same thread
> once we have a technical solution.  In the meantime, I'll reply
> privately so we can set up a development environment and maintenance
> plan for me. 
>
> On Tue, Jun 30, 2009 at 10:01 PM, David Muse
> <dav...@fi... <mailto:dav...@fi...>> wrote:
>
>     Someone else reported this issue a while back.  I was able to
>     reproduce
>     it, but I didn't spend enough time to track down the exact source.
>
>     I plan on putting in some time later this week working on this and
>     other
>     issues and getting a release out as soon as possible.
>
>     If you guys are willing to put in some work updating and
>     maintaining the
>     adapter, then I'd be happy to include the updates in the official
>     distro.  I'd be grateful if you'd maintain it going forward as well.
>
>     Most of the language bindings and adapters were written,
>     contributed and
>     maintained by others, and then eventually they quit maintaining
>     them and
>     I took over.  Some of them I haven't done too much maintenance on :)
>     Most are up-to-date because they mimic the c/c++ api, but the ones
>     that
>     don't (zope, python db, php pear db, perl dbi, etc.) I usually
>     just run
>     some quick tests on to make sure they haven't gotten really broken.
>     Some may have had long standing bugs that were never addressed.
>
>     David Muse
>     dav...@fi... <mailto:dav...@fi...>
>
>     Cameron Laird wrote:
>     > We have well-reproducible cases that suggest a Z SQLRelay Database
>     > Connection truncates its results at 1001 records.  I welcome any
>     > information on my alternatives.  I've searched without success for
>     > similar results.
>     >
>     > Happiest, of course, would be to find that there's some way to
>     > configure the Database Connection to accept larger results.
>     >
>     > More broadly, are there any plans to maintain this adapter?  It
>     > appears to be at least six years old.  We've collected a few
>     definite
>     > errors or at least version skews (improper handling of MONEY
>     datatype,
>     > ...).  We'd be open to shouldering the responsibility of maintenance
>     > for the adapter, and/or paying others (firstworks?) to do so.
>     >
>     > --
>     >
>     > Cameron Laird
>     > +1 817 280 1145  Building 27, Q2/#35
>     > +1 281 648 9889
>     >
>     ------------------------------------------------------------------------
>     >
>     >
>     ------------------------------------------------------------------------------
>     >
>     > __________________________________________________
>     > D O T E A S Y - "Join the web hosting revolution!"
>     >              http://www.doteasy.com
>     >
>     ------------------------------------------------------------------------
>     >
>     > _______________________________________________
>     > Sqlrelay-discussion mailing list
>     > Sql...@li...
>     <mailto:Sql...@li...>
>     > https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
>     >
>     > __________________________________________________
>     > D O T E A S Y - "Join the web hosting revolution!"
>     >              http://www.doteasy.com
>
>
>     ------------------------------------------------------------------------------
>     _______________________________________________
>     Sqlrelay-discussion mailing list
>     Sql...@li...
>     <mailto:Sql...@li...>
>     https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
>
>
>
>
> -- 
>
> Cameron Laird
> +1 817 280 1145  Building 27, Q2/#35
> +1 281 648 9889
> ------------------------------------------------------------------------
>
> ------------------------------------------------------------------------------
>
> __________________________________________________
> D O T E A S Y - "Join the web hosting revolution!"
>              http://www.doteasy.com
> ------------------------------------------------------------------------
>
> _______________________________________________
> Sqlrelay-discussion mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
>
> __________________________________________________
> D O T E A S Y - "Join the web hosting revolution!"
>              http://www.doteasy.com

[Sqlrelay-discussion] [sqlrelay] propagating placeholders (question marks) with DBD::SQLRelay

[<b-r...@ro...>]

(Hmm. It's been three weeks since my last posts and there have been
absolutely no responses. I'll try one more time with something new...)

I'm using DBD::SQLRelay as it purports to be directly compatible with Perl
DBI. This means doing my database accesses the same way as always,
supposedly.

I attempted to enter (insert or update) column values that include
question marks ('?'). This fails, with each question mark being translated
to :1 :2 :3 (etc). I tried every method of escaping and quoting I could
think of, to no avail. I was using $dbh->do() directly, with and without
placeholders.

I next went back to basics: Create a simple table and a short script to
use placeholders and insert a column that contained a '?'.

If I used 'DBI' for a direct connection to the MySQL database, it worked.

If I used DBD::SQLRelay (to take advantage of connection pooling), MySQL
rejected the SQL statement due to the '?' being translated to ':1' - bad
syntax.

What gives? How can I properly use placeholders with the DBD::SQLRelay?

Thanks.

-- 
Roger

Re: [Sqlrelay-discussion] Zope adapter limit of 1001 records?

[Cameron L. <cla...@gm...>]

Super!
Yes, I'm ready to commit to a few hours a week on the Zope (and Python?)
adapter for SQL Relay.  We can follow-up in this same thread once we have a
technical solution.  In the meantime, I'll reply privately so we can set up
a development environment and maintenance plan for me.

On Tue, Jun 30, 2009 at 10:01 PM, David Muse <dav...@fi...>wrote:

> Someone else reported this issue a while back.  I was able to reproduce
> it, but I didn't spend enough time to track down the exact source.
>
> I plan on putting in some time later this week working on this and other
> issues and getting a release out as soon as possible.
>
> If you guys are willing to put in some work updating and maintaining the
> adapter, then I'd be happy to include the updates in the official
> distro.  I'd be grateful if you'd maintain it going forward as well.
>
> Most of the language bindings and adapters were written, contributed and
> maintained by others, and then eventually they quit maintaining them and
> I took over.  Some of them I haven't done too much maintenance on :)
> Most are up-to-date because they mimic the c/c++ api, but the ones that
> don't (zope, python db, php pear db, perl dbi, etc.) I usually just run
> some quick tests on to make sure they haven't gotten really broken.
> Some may have had long standing bugs that were never addressed.
>
> David Muse
> dav...@fi...
>
> Cameron Laird wrote:
> > We have well-reproducible cases that suggest a Z SQLRelay Database
> > Connection truncates its results at 1001 records.  I welcome any
> > information on my alternatives.  I've searched without success for
> > similar results.
> >
> > Happiest, of course, would be to find that there's some way to
> > configure the Database Connection to accept larger results.
> >
> > More broadly, are there any plans to maintain this adapter?  It
> > appears to be at least six years old.  We've collected a few definite
> > errors or at least version skews (improper handling of MONEY datatype,
> > ...).  We'd be open to shouldering the responsibility of maintenance
> > for the adapter, and/or paying others (firstworks?) to do so.
> >
> > --
> >
> > Cameron Laird
> > +1 817 280 1145  Building 27, Q2/#35
> > +1 281 648 9889
> > ------------------------------------------------------------------------
> >
> >
> ------------------------------------------------------------------------------
> >
> > __________________________________________________
> > D O T E A S Y - "Join the web hosting revolution!"
> >              http://www.doteasy.com
> > ------------------------------------------------------------------------
> >
> > _______________________________________________
> > Sqlrelay-discussion mailing list
> > Sql...@li...
> > https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
> >
> > __________________________________________________
> > D O T E A S Y - "Join the web hosting revolution!"
> >              http://www.doteasy.com
>
>
>
> ------------------------------------------------------------------------------
> _______________________________________________
> Sqlrelay-discussion mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
>



-- 

Cameron Laird
+1 817 280 1145  Building 27, Q2/#35
+1 281 648 9889

Re: [Sqlrelay-discussion] Zope adapter limit of 1001 records?

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

Someone else reported this issue a while back.  I was able to reproduce
it, but I didn't spend enough time to track down the exact source.

I plan on putting in some time later this week working on this and other
issues and getting a release out as soon as possible.

If you guys are willing to put in some work updating and maintaining the
adapter, then I'd be happy to include the updates in the official
distro.  I'd be grateful if you'd maintain it going forward as well.

Most of the language bindings and adapters were written, contributed and
maintained by others, and then eventually they quit maintaining them and
I took over.  Some of them I haven't done too much maintenance on :) 
Most are up-to-date because they mimic the c/c++ api, but the ones that
don't (zope, python db, php pear db, perl dbi, etc.) I usually just run
some quick tests on to make sure they haven't gotten really broken. 
Some may have had long standing bugs that were never addressed.

David Muse
dav...@fi...

Cameron Laird wrote:
> We have well-reproducible cases that suggest a Z SQLRelay Database
> Connection truncates its results at 1001 records.  I welcome any
> information on my alternatives.  I've searched without success for
> similar results.
>
> Happiest, of course, would be to find that there's some way to
> configure the Database Connection to accept larger results.
>
> More broadly, are there any plans to maintain this adapter?  It
> appears to be at least six years old.  We've collected a few definite
> errors or at least version skews (improper handling of MONEY datatype,
> ...).  We'd be open to shouldering the responsibility of maintenance
> for the adapter, and/or paying others (firstworks?) to do so.
>
> -- 
>
> Cameron Laird
> +1 817 280 1145  Building 27, Q2/#35
> +1 281 648 9889
> ------------------------------------------------------------------------
>
> ------------------------------------------------------------------------------
>
> __________________________________________________
> D O T E A S Y - "Join the web hosting revolution!"
>              http://www.doteasy.com
> ------------------------------------------------------------------------
>
> _______________________________________________
> Sqlrelay-discussion mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
>
> __________________________________________________
> D O T E A S Y - "Join the web hosting revolution!"
>              http://www.doteasy.com

[Sqlrelay-discussion] Zope adapter limit of 1001 records?

[Cameron L. <cla...@gm...>]

We have well-reproducible cases that suggest a Z SQLRelay Database
Connection truncates its results at 1001 records.  I welcome any information
on my alternatives.  I've searched without success for similar results.
Happiest, of course, would be to find that there's some way to configure the
Database Connection to accept larger results.

More broadly, are there any plans to maintain this adapter?  It appears to
be at least six years old.  We've collected a few definite errors or at
least version skews (improper handling of MONEY datatype, ...).  We'd be
open to shouldering the responsibility of maintenance for the adapter,
and/or paying others (firstworks?) to do so.

-- 

Cameron Laird
+1 817 280 1145  Building 27, Q2/#35
+1 281 648 9889

[Sqlrelay-discussion] General debugging advice: "Error, decimal.InvalidOperation"

[Cameron L. <cla...@gm...>]

I'm running the Zope SQL Relay product.  A particular query works fine from
the sqlrsh command line, but, within Zope, tosses "Error,
decimal.InvalidOperation".
I'd welcome a few words of general advice:  if someone happens to know of
recent fixes in this area, perhaps I should update my Zope product.
 Otherwise, I assume my only practical recourse is to figure out the
product's logging and dive into the implementing source code.  While I'm
plenty comfortable working through Python and C source, I need to make good
use of my time, and welcome any advice on shortcuts I should know before I
start.

Searches for "decimal.InvalidOperation" reports associated with the Zope
product have turned up nothing.

My Zope is a standard 2.10 installed under Ubuntu as the plone-site package.
 Python is 2.5.  The product __init__.py embeds "... 1.1.1.1 2002/11/16
06:49:52 ..."  SQL Relay seems to date from 2000.

Is there anything I should do apart from debugging the product?

-- 

Cameron Laird
+1 817 280 1145  Building 27, Q2/#35
+1 281 648 9889