Glad it worked. So, uh, this is my first foray into database device
driver land, and in terms of the information model for its
configuration, what I see is:
/ns/db/driver/${drivername} (in your case, that's "mysqldriver")
openly configurable; gets a configuration path at startup. Easy to put
this configuration there, but it will automatically apply to all pools
if we put it there.
/ns/db/pools/${poolname} (in your case, that's "mysqlpool")
Not openly configurable. Parameters in this section are
effectively owned by the pool management code; the configuration path is
not passed into the driver. The driver can discover it by replicating
logic from dbinit, but that's not a documented path.
So, a question for you folks with more experience (and other use cases
for these drivers):
* Does AOLServer have a convention for configuring *pool-specific*
parameters for database drivers? If so, what is it?
* Isn't it important for these to be pool-specific, for example in
the case where one pool is running MySQL 3 or 4, and the other is
running MySQL 5? How's that been handled for the other drivers?
On a completely different tack, I'm not as convinced as you are that
making these configurable is a good idea. Here's the complete list of
CLIENT_ vars made available by mysql5 client libraries (in mysql_com.h):
#define CLIENT_LONG_PASSWORD 1 /* new more secure passwords */
#define CLIENT_FOUND_ROWS 2 /* Found instead of affected
rows */
#define CLIENT_LONG_FLAG 4 /* Get all column flags */
#define CLIENT_CONNECT_WITH_DB 8 /* One can specify db on connect
*/
#define CLIENT_NO_SCHEMA 16 /* Don't allow
database.table.column */
#define CLIENT_COMPRESS 32 /* Can use compression protocol
*/
#define CLIENT_ODBC 64 /* Odbc client */
#define CLIENT_LOCAL_FILES 128 /* Can use LOAD DATA LOCAL */
#define CLIENT_IGNORE_SPACE 256 /* Ignore spaces before '(' */
#define CLIENT_PROTOCOL_41 512 /* New 4.1 protocol */
#define CLIENT_INTERACTIVE 1024 /* This is an interactive client
*/
#define CLIENT_SSL 2048 /* Switch to SSL after handshake
*/
#define CLIENT_IGNORE_SIGPIPE 4096 /* IGNORE sigpipes */
#define CLIENT_TRANSACTIONS 8192 /* Client knows about
transactions */
#define CLIENT_RESERVED 16384 /* Old flag for 4.1 protocol */
#define CLIENT_SECURE_CONNECTION 32768 /* New 4.1 authentication */
#define CLIENT_MULTI_STATEMENTS (1UL << 16) /* Enable/disable multi-stmt
support */
#define CLIENT_MULTI_RESULTS (1UL << 17) /* Enable/disable
multi-results */
#define CLIENT_SSL_VERIFY_SERVER_CERT (1UL << 30)
#define CLIENT_REMEMBER_OPTIONS (1UL << 31)
As far as I can see (not far), most of these require modification of the
driver beyond just specifying the flag. E.g., to use the
SSL_VERIFY_SERVER_CERT, the configuration section probably needs
components for loading the trusted-CA list. To use the compress option,
the driver might need to be linked with the gzip module. To use
CONNECT_WITH_DB, the driver would have to take database names as an
argument where it doesn't now. And so on. It's possible to just make
them all configurable and experiment, but I wouldn't be at all confident
of the reliability of the system without understanding MySQL way better
than I do. Of them, I'd say the secure_connection / SSL stuff looks the
most attractive to me.
Anyway, I'd like to get Dossy's opinion on next directions before moving
forward here.
Glad to have helped -
-- ReC
________________________________
From: AOLserver Discussion [mailto:[EMAIL PROTECTED] On Behalf
Of Rajesh nair
Sent: Friday, December 28, 2007 12:15 AM
To: AOLSERVER@LISTSERV.AOL.COM
Subject: Re: [AOLSERVER] Calling mysql procedure using nsmysql
Thanks Rick!
Confirming hardcoding the CLIENT_MULTI_RESULT in the mysql__real_connect
call in nsmysql resolved this problem.
I really believe we should have a way to configure this (and other
connection initialization parameters ) as a ns_param to driver
declaration ns_section.
-- Rajesh Nair
Rick Cobb wrote:
Hmm. A quick look at the source says none of the client flags are
settable via the ns_section/ns_param stuff. And while the MySQL page
says the bug is fixed in 5.00.10, that's apparently only in their odbc
driver, not in the direct API. Is there any reason not to just try the
driver with CLIENT_MULTI_RESULT always set?
-- ReC
________________________________
From: AOLserver Discussion [mailto:[EMAIL PROTECTED] On Behalf
Of Rajesh nair
Sent: Wednesday, December 26, 2007 9:22 AM
To: AOLSERVER@LISTSERV.AOL.COM
Subject: Re: [AOLSERVER] Calling mysql procedure using nsmysql
All,
We are facing the same problem when trying to execute a mysql 5.1 stored
procedure from nsmysql.
I have learned the original error
Database operation "select" failed (exception Error, "PROCEDURE
MYDB.MYPROC can't return a result set in the given context")
is due to mysql not able to decide whether to return multiple or single
result set in the stored procedure.
The solution given by mysql is to set the CLIENT_MULTI_RESULT
<http://bugs.mysql.com/bug.php?id=24485> flag while connecting to mysql
database.
My question is , where can I pass the connection flags in nsmysql?
I tried passing this as ns_param as given below:-
# Database interface -- nsdb
ns_section "ns/db/drivers"
ns_param mysqldriver nsmysql.so
ns_section "ns/db/pools"
ns_param mysqlpool "MySQLPool"
ns_section "ns/db/pool/mysqlpool"
ns_param driver mysqldriver
ns_param connections 2
ns_param user $Reporting_DB_UserName
ns_param password <pwd>
ns_param datasource <usr>
ns_param verbose on
ns_param CLIENT_MULTI_RESULT 1
# Database pools accessible by server
ns_section "ns/server/${servername}/db"
ns_param defaultpool "mysqlpool"
ns_param pools *
But this did not work!
Any pointers would be greatly appreciated!
Rajesh Nair
Hello Dossy,
An i using a too new libmysql? Does nsmysql needs updating to support
this?
kind regards,
On Oct 25, 2007 3:41 PM, shedis <[EMAIL PROTECTED]> wrote:
> Without the call i get a different error message like:
>
> Database operation "select" failed (exception Error, "You have an
> error in your SQL syntax; check the manual that corresponds to your
> MySQL server version for the right syntax to use near
> 'MYPROC('SCANRES,'2007-10-2' at line 1")
>
> My ns_mysql version is: Ns_MySQL_DriverInit(nsmysql): Loaded
> Panoptic MySQL Driver v0.6, built on Jul 19 2007 at 19:30:40.
>
> kind regards,
>
> On Oct 25, 3:00 pm, Dossy Shiobara <[EMAIL PROTECTED]> wrote:
> > On 2007.10.24, shedis <[EMAIL PROTECTED]> wrote:
> >
> > > set sqlstmt "call MYPROC('SCANRES','2007-10-24
00:00:01','2007-10-24
> > > 23:59:59','%','%','%','%','%','%','%')";
> > ...
> > > Output:
> > > Database operation "select" failed (exception Error, "PROCEDURE
> > > MYDB.MYPROC can't return a result set in the given context")
> >
> > Remove the "call" from the SQL statement. Do you still get the
error?
> >
> > -- Dossy
> >
> > --
> > Dossy Shiobara | [EMAIL PROTECTED] |http://dossy.org/
> > Panoptic Computer Network |http://panoptic.com/
> > "He realized the fastest way to change is to laugh at your own
> > folly -- then you can let go and quickly move on." (p. 70)
> >
> > --
> > AOLserver -http://www.aolserver.com/
> >
> > To Remove yourself from this list, simply send an email to <[EMAIL
> > PROTECTED]> with the
>
> > body of "SIGNOFF AOLSERVER" in the email message. You can leave the
> > Subject: field of your email blank.
>
>
> --
> AOLserver - http://www.aolserver.com/
>
> To Remove yourself from this list, simply send an email to <[EMAIL
> PROTECTED]> with the
> body of "SIGNOFF AOLSERVER" in the email message. You can leave the
Subject:
> field of your email blank.
>
--
AOLserver - http://www.aolserver.com/
To Remove yourself from this list, simply send an email to <[EMAIL
PROTECTED]>
with the
body of "SIGNOFF AOLSERVER" in the email message. You can leave the
Subject:
field of your email blank.
--
AOLserver - http://www.aolserver.com/
To Remove yourself from this list, simply send an email to
<[EMAIL PROTECTED]> <mailto:[EMAIL PROTECTED]> with the
body of "SIGNOFF AOLSERVER" in the email message. You can leave the
Subject: field of your email blank.
--
AOLserver - http://www.aolserver.com/
To Remove yourself from this list, simply send an email to
<[EMAIL PROTECTED]> <mailto:[EMAIL PROTECTED]> with the
body of "SIGNOFF AOLSERVER" in the email message. You can leave the
Subject: field of your email blank.
--
AOLserver - http://www.aolserver.com/
To Remove yourself from this list, simply send an email to
<[EMAIL PROTECTED]> with the
body of "SIGNOFF AOLSERVER" in the email message. You can leave the
Subject: field of your email blank.
--
AOLserver - http://www.aolserver.com/
To Remove yourself from this list, simply send an email to <[EMAIL PROTECTED]>
with the
body of "SIGNOFF AOLSERVER" in the email message. You can leave the Subject:
field of your email blank.
