Bug #30464 parameters passed to procedures in serial mode rather than all at once

• Submitted: 16 Aug 2007 18:22
• Modified: 28 Oct 2007 14:42
• Reporter: Don Cohen
• Status: Closed
• Category: Connector / J
• Severity: S5 (Performance)
• OS: Any
• CPU Architecture: Any

[16 Aug 2007 18:22] Don Cohen

Description:
I notice that in order to call a stored procedure the current java connector takes a network round trip for each parameter, and waits for the last one before sending the next one.  So if the network connection is across the country and takes .1 sec then just passing the parameters to a procedure with 10 parameters takes a second.  Is there any reason not to pass all at once?  That would reduce the time to call the procedure (in the normal case where it doesn't take any significant processing time) from 1 sec to .1, which seems like what one might expect/hope.

How to repeat:
call any stored procedure with multiple arguments, look at the tcpdump of the call and see that the first parameter is set and the reply received before the request to set the second is sent.

Suggested fix:
I've not looked at the code.
I imagine that it currently sends one parameter, waits for an indication of success, then sends the next.  What I'd expect and want is a protocol for sending all at once, or even for calling the procedure directly without setting temporary variables to hold the parameter values.

I currently have some procedures that take something like 10 parameters, and occasionally (for reasons I don't yet understand) the round trip times get to be an appreciable part of a second, which results in the action that does the call taking close to 10 seconds.

[17 Aug 2007 3:24] Mark Matthews

This only happens when you have OUT or INOUT parameters, since the only way the server can handle these currently is through the use of user variables. Does your stored procedure have any such parameters?

In the end, this is a shortcoming in the MySQL protocol, not the JDBC driver. Eventually, when the server supports binding OUT or INOUT parameters directly into a procedure, the performance issue goes away.

We could look at batching them, however it would require at least two extra round-trips per set, because the JDBC driver does not set the flags for compound statements (i.e. separated by ";") by default since it's a SQL injection hazard.

[17 Aug 2007 11:52] Don Cohen

> This only happens when you have OUT or INOUT parameters, since the only way the server can handle these currently is through the use of user variables. Does your stored procedure have any such parameters?

Yes, I use an out parameter to return a status value.
I use the same approach in all of my procedures (which is why I didn't realize that the problem only occurs in this case).
Is this documented anywhere?
I suppose I could instead return the value as the result of a query.

> Eventually, when the server supports binding OUT or INOUT parameters directly into a procedure, the performance issue goes away.

Doesn't sound imminent.  What's the projected time frame?

> We could look at batching them, however it would require at least two extra round-trips per set, because the JDBC driver does not set the flags for compound statements (i.e. separated by ";") by default since it's a SQL injection hazard.

Is this described in documentation somewhere?  I don't yet follow the problem.
Why should not all inputs be sent together and all outputs returned together in a single round trip?

[17 Aug 2007 13:10] Mark Matthews

>> This only happens when you have OUT or INOUT parameters, since the only way the server
can handle these currently is through the use of user variables. Does your stored
procedure have any such parameters?

>Yes, I use an out parameter to return a status value.
>I use the same approach in all of my procedures (which is why I didn't realize that the problem only occurs in this case).

>Is this documented anywhere?
>I suppose I could instead return the value as the result of a query.

It's documented only in the omission of an API call in libmysql to change the "directionality" of an OUT parameter when using prepared statements, and by the fact that all examples of OUT parameters utilize user variables.

> Eventually, when the server supports binding OUT or INOUT parameters directly into a procedure, the performance issue goes away.

> Doesn't sound imminent.  What's the projected time frame?

It's not planned for 5.1, or 6.0.

>> We could look at batching them, however it would require at least two extra round-trips per set, because the JDBC driver does not set the flags for compound statements (i.e.separated by ";") by default since it's a SQL injection hazard.

> Is this described in documentation somewhere?  I don't yet follow the problem.
Why should not all inputs be sent together and all outputs returned together in a single round trip?

You can only set one user variable at a time, i.e. one statement at a time. The only way to "batch" statements in MySQL in one round-trip is to use multi-statements. The JDBC driver does _not_ enable this functionality by default, because it's a huge SQL injection hazard for applications that don't consistently escape data provided by users. Therefore, to send the user variables in one batch, the driver would have to do three round-trips. One to turn multi-statements on just for this batch, the batch itself, and finally one to turn multi-statements off again.

The driver _does_ _return_ OUT/INOUT variables in one trip, they're all in a single result set.

[20 Aug 2007 5:33] Tonci Grgin

Hi Don and thanks for your report.

I believe Mark has answered all of your questions but if you need more details please see discussion in bug#17898.

[31 Aug 2007 7:19] Don Cohen

I have a version of the stored procedure with only IN parameters 
create procedure addRoleAssessment (in err varchar(250), in role integer,        
   in rating double, in stat integer,                                            
   in poscomment text, in negcomment text, in subjcomment text)                  

but I still see it taking a round trip for every parameter.
How does the driver know which parameters are out/inout (or whether there are any)?

[31 Aug 2007 18:43] Mark Matthews

It's asking the server (via "SHOW CREATE PROCEDURE").

[1 Sep 2007 0:24] Don Cohen

> It's asking the server (via "SHOW CREATE PROCEDURE").

Ok.  Show create procedure =>
 CREATE PROCEDURE `addRoleAssessment3`(in err varchar(250), in role integer,
   in rating double, in stat integer,
   in poscomment text, in negcomment text, in subjcomment text)
  select ""
Ping and tcpdump show the server's only ~20ms (round trip) away and that a round trip is used for each parameter, leading to
 prepare {call addRoleAssessment3(?,?,?,?,?,?,?)} took 217 ms

How can I get all the parameters sent in the same round trip?

[1 Sep 2007 1:27] Mark Matthews

If they're all "in", it _should_ be one round trip.

Since I don't have a standalone testcase, I came up with one, tell me where it differs from your code:

Looking at what's happening on-the-wire shows that there is only one round trip, the query to execute the procedure and the returned result:

public void testBug30464() throws Exception {
		createProcedure("addRoleAssessment3", "(in err varchar(250), in role integer,"
				   + "in rating double, in stat integer,"
				   + "in poscomment text, in negcomment text, in subjcomment text)"
				   + "\nBEGIN\nSELECT 1;\nEND");
		
		Connection profiledConn = getConnectionWithProps("profileSQL=true");
		CallableStatement cStmt = profiledConn.prepareCall("{call addRoleAssessment3(?,?,?,?,?,?,?)}");
		cStmt.setString(1, "abc");
		cStmt.setInt(2, 1);
		cStmt.setDouble(3, 2.0D);
		cStmt.setInt(4, 4);
		cStmt.setString(5, "Bad");
		cStmt.setString(6, "Good");
		cStmt.setString(7, "Ugly");
		cStmt.execute();
				

	}

Fri Aug 31 20:25:28 CDT 2007 INFO: Profiler Event: [QUERY] 	at testsuite.regression.CallableStatementRegressionTest.testBug30464(CallableStatementRegressionTest.java:1213) duration: 0 ms, connection-id: 8243, statement-id: 3, resultset-id: 5, message: CALL addRoleAssessment3('abc',1,2.0,4,'Bad','Good','Ugly')
Fri Aug 31 20:25:28 CDT 2007 INFO: Profiler Event: [FETCH] 	at testsuite.regression.CallableStatementRegressionTest.testBug30464(CallableStatementRegressionTest.java:1213) duration: 0 ms, connection-id: 8243, statement-id: 3, resultset-id: 5

[1 Sep 2007 1:29] Mark Matthews

(I ran that test with Connector/J 5.0.7, for the record).

[1 Sep 2007 4:19] Don Cohen

> If they're all "in", it _should_ be one round trip.
> Since I don't have a standalone testcase, I came up with one, tell me where it > differs from your code:
I've now started from your code, removed the create procedure (already defined as in previous message), used the connection I already had (not a profiling connection; also uses ssl - I hope that doesn't matter), so the code looks like this:
    public static void testBug30464() throws Exception { 
// 	createProcedure("addRoleAssessment3",
// 			"(in err varchar(250), in role integer," 
// 			+ "in rating double, in stat integer," 
// 			+ "in poscomment text, in negcomment text, in subjcomment text)" 
// 			+ "\nBEGIN\nSELECT 1;\nEND"); 
                 
// 	Connection profiledConn = getConnectionWithProps("profileSQL=tr\
// ue"); 
	CallableStatement cStmt = // profiledConn.prepareCall("{call
	    conn.prepareCall("{call addRoleAssessment3(?,?,?,?,?,?,?)}"); 
	cStmt.setString(1, "abc"); 
	cStmt.setInt(2, 1); 
	cStmt.setDouble(3, 2.0D); 
	cStmt.setInt(4, 4); 
	cStmt.setString(5, "Bad"); 
	cStmt.setString(6, "Good"); 
	cStmt.setString(7, "Ugly"); 
	cStmt.execute(); 
    } 
I then call it with a break point on the execute (in eclipse), then do the execute while running tcpdump.
I get 9 round trips (22 packets).  It's hard to see from the packets what they're doing since they're encrypted, but the log shows
070831 20:57:26     531 Query       SET @com_mysql_jdbc_outparam_0=_binary'abc'
                    531 Query       SET @com_mysql_jdbc_outparam_1=_binary'1'
                    531 Query       SET @com_mysql_jdbc_outparam_2=_binary'2.0'
                    531 Query       SET @com_mysql_jdbc_outparam_3=_binary'4'
                    531 Query       SET @com_mysql_jdbc_outparam_4=_binary'Bad'
                    531 Query       SET @com_mysql_jdbc_outparam_5=_binary'Good'
                    531 Query       SET @com_mysql_jdbc_outparam_6=_binary'Ugly'
                    531 Query       CALL addRoleAssessment3(@com_mysql_jdbc_outp
aram_0,@com_mysql_jdbc_outparam_1,@com_mysql_jdbc_outparam_2,@com_mysql_jdbc_out
param_3,@com_mysql_jdbc_outparam_4,@com_mysql_jdbc_outparam_5,@com_mysql_jdbc_ou
tparam_6)

> (I ran that test with Connector/J 5.0.7, for the record).
I'm still using my own modifications on top of 5.0.4.
I think I've sent them all to you.  Are they incorporated into 5.0.7 ?
I think I need them all.

[9 Sep 2007 19:55] Don Cohen

I'm just wondering, is this still on your radar?
Have you looked at my last entry?
Should I post a tcpdump showing the parameters being sent separately?
Alternatively, perhaps you could post a dump showing them sent together, along with output from the log showing what that looks like (or tell me that the log looks the same in either case).
Also you might check that this is not something that changed between 5.0.4 and 5.0.7.

[10 Sep 2007 13:47] Mark Matthews

"Inspection" shows that the code that handles this hasn't changed between 5.0.4 and 5.0.7. The snippet that I posted earlier is an exact log of what was sent to the server.

As far as your SSL-related patches, if you're referring to BUG#25545, those went into 5.0.6.

[10 Sep 2007 18:33] Don Cohen

> The snippet that I posted earlier is an exact log of what was sent to the server.

I'm not sure what snippet you refer to.  The best candidate I see appears to be output from the profiling connection.  I did ask you to post tcpdump output and log output.

Now that I have investigated further I see this would have helped.
When I create a connection more or less as you did I get totally different output from both of those.  The problem seems to be related to the following in my connection url:
  noAccessToProcedureBodies=true
I guess the point is that it needs this to determine that there are only in parameters.  Is there any way to make this all work without reading the procedure body?  I don't remember why I added that originally but I think there was a reason.  For a start it seems like a security risk to let anyone with permission to use the system read the code.

[10 Sep 2007 19:52] Mark Matthews

> I'm not sure what snippet you refer to.  The best candidate I 
> see appears to be output from the profiling connection.  I 
> did ask you to post tcpdump output and log output.

Which would've shown the same thing. What you probably can't know without digging pretty far into the guts of the driver is that what you see when "profileSQL=true" is what goes out on the wire, there's no code that changes it in between when it was logged and when it goes to the server. 

> Now that I have investigated further I see this would have helped.
> When I create a connection more or less as you did I get 
> totally different output from both of those.  The problem 
> seems to be related to the following in my connection url:
>   noAccessToProcedureBodies=true
> I guess the point is that it needs this to determine that 
> there are only in parameters.  Is there any way to make this 
> all work without reading the procedure body?  I don't 

5.0.8 now treats _all_ parameters as "IN" if you set this property, so with 5.0.8 you wouldn't see the behavior you're now seeing with 5.0.4. 5.0.8 hasn't been released yet, but you can get snapshots that do have this behavior change in them from http://downloads.mysql.com/snapshots.php#connector-j

> remember why I added that originally but I think there was a 
> reason.  For a start it seems like a security risk to let 
> anyone with permission to use the system read the code.

MySQL server doesn't expose any metadata about stored procedure parameters, and there isn't a plan to add this functionality until at least MySQL-6.0. However JDBC requires parameter metadata for CallableStatements, and many applications/frameworks require this metadata to be correct to operate at all, hence the JDBC driver _has_ to get it somehow, and the only place the data exists is in the procedure source code.

If you could try a snapshot of 5.0.8 and let us know if it indeed solves the issue for you, I'd appreciate it, I could then close this bug.

[10 Sep 2007 21:16] Don Cohen

Even knowing what bytes go out the wire is not enough - I wanted to know where the packet boundaries were and which ones had to wait for a reply.  The string
  call proc(1,2,3)
COULD have been sent in separate packets for each parameter, 
  call proc( | 1, | 2, | 3) |
with each packet waiting to be sent until the previous one was ack'd.
The difference in the log was dramatic - 
===
070910 10:43:09    2639 Query       CALL addRoleAssessment3('abc',1,2.0,4,'Bad',
'Good','Ugly')
===
vs
===
070910 11:19:57    2646 Query       SET @com_mysql_jdbc_outparam_0=_binary'abc'
                   2646 Query       SET @com_mysql_jdbc_outparam_1=_binary'1'
                   2646 Query       SET @com_mysql_jdbc_outparam_2=_binary'2.0'
                   2646 Query       SET @com_mysql_jdbc_outparam_3=_binary'4'
                   2646 Query       SET @com_mysql_jdbc_outparam_4=_binary'Bad'
                   2646 Query       SET @com_mysql_jdbc_outparam_5=_binary'Good'
                   2646 Query       SET @com_mysql_jdbc_outparam_6=_binary'Ugly'
                   2646 Query       CALL addRoleAssessment3(@com_mysql_jdbc_outp\
aram_0,@com_mysql_jdbc_outparam_1,@com_mysql_jdbc_outparam_2,@com_mysql_jdbc_out\
param_3,@com_mysql_jdbc_outparam_4,@com_mysql_jdbc_outparam_5,@com_mysql_jdbc_ou\
tparam_6)
===

> 5.0.8 now treats _all_ parameters as "IN" if you set this property
I assume you mean if I do  noAccessToProcedureBodies=true 
But then I better not use any OUT parameters, right?
So I have to change all my code before I try it.
Also before I try it I better check to see what other changes I've made that I rely on.  One that comes to mind is requireSSLcert=false.  Is that there?
I'll upload a diff between my 5.0.4 and the original.

[10 Sep 2007 21:24] Don Cohen

diff between original 5.0.4 and the one I currently use

Attachment: mysql-connector-diff3 (application/octet-stream, text), 9.27 KiB.

[10 Sep 2007 21:30] Don Cohen

Looking through the diff I see several changes just for debugging.
ConnectionProperties.java defines requireSSLcert, which I need
ExportControlled.java is part of that change
TrustingSSLSocketFactory.java is also part of that change
MysqlIO.java I think is the ssl fix, which you have

So at worst I would just have to reinstall requireSSLcert.

Changing all my code to not use out params will take me a while.

[10 Sep 2007 21:31] Mark Matthews

> Even knowing what bytes go out the wire is not enough - I 
> wanted to know where the packet boundaries were and which 
> ones had to wait for a reply.
>  The string
>   call proc(1,2,3)
> COULD have been sent in separate packets for each parameter,
>   call proc( | 1, | 2, | 3) |
> with each packet waiting to be sent until the previous one was ack'd.

With the MySQL protocol, no it wouldn't. Sorry if I didn't go as "outside of the code" as you wanted, but because I'm intimately familiar with this code, I knew what was logged went on the wire verbatim.

[10 Sep 2007 21:32] Mark Matthews

> > 5.0.8 now treats _all_ parameters as "IN" if you set this property
> I assume you mean if I do  noAccessToProcedureBodies=true But 
> then I better not use any OUT parameters, right?

No, here's the changelog entry:

"Fixed BUG#28689 - CallableStatement.executeBatch() doesn't work when 
      connection property "noAccessToProcedureBodies" has been set to "true".
     
      The fix involves changing the behavior of "noAccessToProcedureBodies",in 
      that the driver will now report all paramters as "IN" paramters
      but allow callers to call registerOutParameter() on them without throwing
      an exception."

[10 Sep 2007 21:38] Mark Matthews

We can probably get "requireSSLCert" into the 5.0.8 release cycle. I've just been busy getting other functionality in 5.1 out the door, so we haven't gotten back to earlier feature requests. My apologies for not getting to it sooner.

[10 Sep 2007 22:06] Don Cohen

My point about byte streams vs packets is that there is quite a bit of info in the packets that is missing from the byte streams, and at most what you posted could be interpreted as a byte stream.
I gather you are saying that I should be able to use current 5.0.8 without fixing all the out params, only having to add requireSSLCert, and that then my connections with noAccessToProcedureBodies should (1) work and (2?) even send all the inputs in a single round trip (or is that true only if no params are registered as out?).
If (2) is true then I don't have to change my protocol after all (I hope!!).

If you are planning to add requireSSLCert to 5.0.8 soon then I'll wait for it.
Alternatively, I could do it and send you the diffs - I imagine they'd be very similar to those you already have for 5.0.4.

Another small related question - is there a _priv that controls whether a user can do show create procedure ?  It's not much security to just distribute applets that don't do that when anyone can replace the applet.  Clearly the control belongs at the server.

You wrote
  MySQL server doesn't expose any metadata about stored procedure parameters
I don't understand that.  What is there to know other than what you can find out from show create procedure?

[12 Sep 2007 18:47] Don Cohen

It occurs to me that this might be related to my null problem.
When you pass null as a parameter what should appear in the log?
I'm guessing that when all params are sent together it will be something like
  CALL addRoleAssessment3('abc',1,2.0,4,'Bad',null,'Ugly')
which would distinguish between the string 'null' and the null value
whereas in my current logs I see
 SET @com_mysql_jdbc_outparam_6=_binary'null'
which looks the same as a string.
So my current theory is that this is a bug in the current version:
when the driver decides to send each parameter separately it sends null inputs as strings rather than as null values.
BTW my OUT parameter, which is never set but only registered as out, shows up in the log like so:
 SET @com_mysql_jdbc_outparam_0=null
which I imagine is how the other one OUGHT to appear.

[28 Sep 2007 20:18] Tonci Grgin

Hi Don. Can you please review my test case and add your comments as I'm unable to repeat the reported behavior (with noAccessToProcedureBodies = true/false):

Environment:
 - MySQL server 5.0.50-pb1046 on WinXP Pro SP2 localhost
 - c/J 5.0 branch, latest sources
 - java.vm.version: 1.5.0_12-b04

070928 22:05:47	      9 Connect     root@localhost on test
		      9 Query       SHOW SESSION VARIABLES
		      9 Query       SHOW COLLATION
		      9 Query       SET NAMES utf8
		      9 Query       SET character_set_results = NULL
		      9 Query       SET autocommit=1
		      9 Query       SET sql_mode='STRICT_TRANS_TABLES'
		      9 Query       SELECT VERSION()
		      9 Query       DROP TABLE IF EXISTS `bug30464`
		      9 Query       CREATE  TABLE `bug30464` (err VARCHAR(250), role INTEGER, rating DOUBLE, stat INTEGER, poscomment TEXT, negcomment TEXT, subjcomment TEXT)
		      9 Query       DROP PROCEDURE IF EXISTS addRoleAssessment3
		      9 Query       CREATE  PROCEDURE addRoleAssessment3 (in err varchar(250), in role integer,in rating double, in stat integer,in poscomment text, in negcomment text, in subjcomment text)
BEGIN
INSERT INTO bug30464 VALUES (err, role, rating, stat, poscomment, negcomment, subjcomment);
END
		     10 Connect     root@localhost on test
		     10 Query       SHOW SESSION VARIABLES
		     10 Query       SHOW COLLATION
		     10 Query       SET NAMES utf8
		     10 Query       SET character_set_results = NULL
		     10 Query       SET autocommit=1
		     10 Query       SET sql_mode='STRICT_TRANS_TABLES'
		     10 Query       CALL addRoleAssessment3(null,1,2.0,4,'Bad','Good','Ugly')
		      9 Query       DROP TABLE IF EXISTS `bug30464`
		      9 Query       DROP PROCEDURE IF EXISTS addRoleAssessment3
		      9 Quit

[28 Sep 2007 20:19] Tonci Grgin

Test case

Attachment: TestBug30464.java (text/java), 1.71 KiB.

[30 Sep 2007 4:57] Don Cohen

the previous file with comments added

Attachment: TestBug30464.annotations (application/octet-stream, text), 3.55 KiB.

[30 Sep 2007 5:02] Don Cohen

I've added some comments to the file you posted describing differences between your test and mine.  I've not tried to test any further to see how close I can get to your example.  I hope this is what you had in mind.  I have the capability to get somewhat closer to your test but probably not all the way.  I hope it's easier for you to move toward mine.

[1 Oct 2007 8:40] Tonci Grgin

Hi Don.

> I've added some comments to the file you posted describing differences between your test and mine.  I've not tried to test any further to see how close I can get to your example.

Comments later.
 
> I hope this is what you had in mind.  I have the capability to get somewhat closer to your test but probably not all the way.  I hope it's easier for you to move toward mine.

We have defined procedure and test framework so if I go towards your code that would actually be code-review (which I'm not allowed to do). So please make your test more similar to mine, if possible.

> I hope it doesn't make any difference, but I do not create tables and procedures as part of my test.  I create them in advance from another connection. I suppose it's possible that the client sending such definitions could save them and then later use them in place of the definition it wanted to get in order to decide which parameters were inputs. 

Again, this is done so that test case is easily imported into our test suite. In any case I don't think client saves anything.

> Here's how I create the connection:

Ok, I'll make necessary changes.

> I suppose the problem could be more than just noAccessToProcedureBodies.
I think I just tried removing that one line and seeing the behavior change.
So it might be related to some of the other properties, or even the different
way the connection is created or even the profileSQL that you've added

Agree, so let me explain about our test framework:
 - We run all tests from inside Eclipse against latest c/J sources found in SVN repositories
 - Test case is extension of BaseTestCase class found in c/J sources
 - We are obliged to run tests against latest sources available (both connector and server)
 - Sometimes, if it looks like the problem is OS dependent, we run tests on different OS's.
 - Test should be stand alone, exhibiting the problem every time it is run.

Waiting on your results.

[1 Oct 2007 17:53] Don Cohen

> We have defined procedure and test framework so if I go towards your code that would actually be code-review (which I'm not allowed to do). 
When I talk about moving in some direction I really mean search for the boundary between desirable and undesirable behavior.  I'd call that debugging.  It's certainly not code review.
> > Here's how I create the connection:
> Ok, I'll make necessary changes.
Then I guess I should hear back from you what the result was and perhaps a new source for the test case.

I'm willing to try to run your test case but it's not immediately obvious how to do so.  For instance, I don't see BaseTestCase in my 5.0.4 source.  Is it there?
I gather the test case starts out with some connection that it uses to create the table and procedure - where are the parameters of that connection supplied, such as what server, user, password?  I don't see that for the second connection either.

> - We run all tests from inside Eclipse against latest c/J sources found in SVN
repositories
As mentioned earlier I am using some extensions not in those latest sources.
I doubt that's related to the problem, but it's at least possible.
I'll need a url for those latest sources in order to use them.

> - Test case is extension of BaseTestCase class found in c/J sources
starting in which version?

> - We are obliged to run tests against latest sources available (both connector and server)
I understand that you're always interested in the latest version but it's a huge problem for people like me to keep switching versions.  I've already installed several versions of servers.  Of course it's also a pain to use new versions of c/J when I have my own modifications.

 - Test should be stand alone, exhibiting the problem every time it is run.
This sounds reasonable.  In order to help me do this, perhaps you could send me a sample shell script showing how to compile and run the example.

[1 Oct 2007 20:12] Don Cohen

I've made a little progress but, as you see, still could use some help:
===
# /tmp/jdk1.6.0/bin/javac -cp /tmp/mysql-connector-java-5.0.4+-bin.jar:/tmp/mysql-connector-java-5.0.4+/src:/tmp/mysql-connector-java-5.0.4+/src/lib/junit.jar ./TestBug30464.java
./TestBug30464.java:21: cannot find symbol
symbol  : method createProcedure(java.lang.String,java.lang.String)
location: class testsuite.simple.TestBug30464
	    		createProcedure("addRoleAssessment3", "(in err varchar(250), in role integer,"
	    		^
Note: /tmp/mysql-connector-java-5.0.4+/src/testsuite/BaseTestCase.java uses unchecked or unsafe operations.
Note: Recompile with -Xlint:unchecked for details.
1 error

[3 Oct 2007 6:51] Tonci Grgin

Don, here's a quick cook-book on how to write/test like we do:
 1) Install JDK and Eclipse
 2) Install SVN plug-in if necessary
    http://www.polarion.org/index.php?page=download&project=subversive
    To add a plugin, in Eclipse, go to "Help" -> "Software Updates" -> "Find and Install"
    Choose "Search for new features to install"
    Click "New remote site" and then put in the URL that the URL from above
 3)Go to "Window" -> "Open Perspective" and if "SVN Repository Exploring" is not in the list, then choose "Other", otherwise choose "SVN Repository Exploring". Now on the left, you should have a "SVN Repositories" tab, but it's empty. Right click in that area, and choose "New". In the "Root URL", put http://svn.mysql.com/svnpublic/connector-j. Click "Finish" on the bottom. Expand repository tree and then expand "BRANCHES". Then expand "branch_5_0 xxxxx" and right click on the "connector-j" that's _under_ the "branch_5_0 xxxxx". Click and choose "Check out". "Operation in progress" will take a little bit, depending on how fast your connection is.
 4) After check out is done, switch back to the "Java" perspective and open "connector-j" or "connector-j-5-0" project (which ever you see). There you'll see the packages, and one of them will be "testsuite" (under SRC). The regression tests are in "testsuite/regression", the unit tests are in "simple" etc. If you right-click on them, and choose "RunAs" you'll see "Junit test", that will run the _entire_ class as a Junit test. 
 5) To set up URL props, username etc, go to Window -> Preferences and open up "Java", then "Installed JREs" to edit your default JVM properties. Now in "default vm arguments", you'll can put something like - -Xmx256M -Dcom.mysql.jdbc.testsuite.url.default=jdbc:mysql://blah,blah,blah... The advantage of doing it this way is it will allow you to launch any test, but you could later on override the URL if need be for a specific test, without changing this setting (just like in my attached test case).

That's it. You're set up for testing "our" way and with latest sources from the branch of your choice.

[3 Oct 2007 18:31] Don Cohen

Progress so far:
http://www.polarion.org/index.php?page=download&project=subversive
says something about "callisto" and it was not clear to me how that related to my version of eclipse - 3.2.2.  I gather now that callisto is 3.2.
The url also has a link to notes for non-windows users.  I followed that to
http://www.polarion.org/index.php?page=installation&project=subversive#notes
And it turned out that this page (after I went to the top of it) was the one that actually told me what to do to install.  So I've now followed the directions there.  I suggest you use that url (without the #notes) as the starting point.

current problem:
I follow your instructions to check out connector/J but notice that it has a little red x on it.  Under problems - errors I see two things.  First is
 The project cannot be built until build path errors are resolved
So far I can't figure out what to do about this.
Since I've tried a bunch of things it would help if you not only tell me how to do whatever is needed but also how to UNDO whatever I've messed up.
The other error is
 Unbound classpath variable JSSE_JAR in project connector-j-5-0

[4 Oct 2007 8:04] Tonci Grgin

Don yes, seen that too many times :-( I'm using Eclipse SDK 3.3 and it has few changes there so I'll try to remember how to do it in  3.2.

> The project cannot be built until build path errors are resolved
 Window/Preferences/Java/Build Path/Classpath Variables:
  Point JRE_LIB variable to your rt.jar file
    If this fails, add new variable, JRE, and point it to rt.jar file
  Point your JSSE_JAR variable to jsse.jar file

This should do the trick.

[4 Oct 2007 8:05] Tonci Grgin

Eclipse SDK 3.3

Attachment: EclipseSDK3-3.JPG (image/jpeg, text), 85.62 KiB.

[4 Oct 2007 21:53] Don Cohen

It was not easy but I now seem to have these variables ok.
I originally had compiler compliance level 1.4 and installed jre 1.6 and that gave me many errors.  I moved both to 1.5.
Now I guess I'm supposed to import TestBug30464.java into testsuite.simple?
Then I try to run (debug) it.  I have
under test
 project: connector-j-5-0
 test class: testsuite.simple.TestBug30464
 test runner: JUnit 3
under arguments:
 - -Dcom.mysql.jdbc.testsuite.url.default=jdbc:mysql://localhost:3306/test?user=root&password=...
(also tried without the extra -)
But this must not be correct cause I get in the console
====
Loading JDBC driver 'com.mysql.jdbc.Driver'
Done.

java.sql.SQLException: Access denied for user ''@'localhost' (using password: NO)
====

[5 Oct 2007 8:24] Tonci Grgin

Don, yes, testsuite.simple is the place.

Now, go to Window/Preferences/Java/Installed JREs and double click the checked jre (for me it's jre1.5.0_12) and put something like
-Xmx512M -XX:+UseParallelGC -Dcom.mysql.jdbc.testsuite.url.default=jdbc:mysql://your_server:3306/your_db?user=your_user&password=your_pwd&noAccessToProcedureBodies=true
into Default VM Arguments box. That should allow you to run my test.

[6 Oct 2007 0:24] Don Cohen

Ok, I got that to work.  Now how do I make the connection use ssl?
When I try to add that to the url I get exceptions.

[11 Oct 2007 6:20] Tonci Grgin

Don, have you tried adding "&useSSL=true"? In any case you have all our test cases locally now so you can check. I don't think SSL is relevant here and you should proceed with testing.

[11 Oct 2007 18:16] Don Cohen

> have you tried adding "&useSSL=true"? 
yes
> In any case you have all our test cases locally now so you can check. 
I did try it and got exceptions.  I think this stuff is harder than you think.
That's why I'm asking you to try it.
> I don't think SSL is relevant here and you should proceed with testing.
I wouldn't have thought so either, but that has already turned out to be wrong in an earlier bug.

I've run the test on the server I was using before.  There are only two things remaining.  One is ssl.  The other is to start to change the method of making the connection to look more like my original code.  I guess that would include changes to the driver.  Anyhow, the second looks a lot more complicated so I thought the first should be tried first.  I'm afraid you're going to have to help me a lot more on the second.  The issue will be what path to take between your method of connecting and mine.

[14 Oct 2007 7:44] Don Cohen

Here's some progress.
I've now gone back to my original code and removed the ssl and a bunch 
of other stuff, so it's pretty plain.  I get the connection like this:

        String url = "jdbc:mysql://" + Sonario.hostname + ":3306/" + Sonario.dbname
            + "?user=" + LeoLIB.user + "&password=" + LeoLIB.pass
            //+ "&useSSL=true&requireSSL=true&requireSSLcert=false"
            //+ "&allowMultiQueries=true"
            //+ "&autoGenerateTestcaseScript=true"
            //+ "&cacheCallableStmts=true"
            //+ "&dontTrackOpenResources=true"
            //+ "&dumpQueriesOnException=true"
            + "&noAccessToProcedureBodies=true" // hurray!
            //+ "&traceProtocol=true"
            //+ "&useUsageAdvisor=true"
            ;
        conn = DriverManager.getConnection(url);

and then call the stored procedure:
	CallableStatement cStmt = // profiledConn.prepareCall("{call
	    conn.prepareCall("{call addRoleAssessment3(?,?,?,?,?,?,?)}"); 
	cStmt.setString(1, "abc"); 
	cStmt.setInt(2, 1); 
	cStmt.setDouble(3, 2.0D); 
	cStmt.setInt(4, 4); 
	cStmt.setString(5, "Bad"); 
	cStmt.setString(6, "Good"); 
	cStmt.setString(7, "Ugly"); 
	cStmt.execute(); 

The result is multiple round trips and the following in the log:
071014  0:20:17    2641 Query       SET @com_mysql_jdbc_outparam_0=_binary'abc'
                   2641 Query       SET @com_mysql_jdbc_outparam_1=_binary'1'
                   2641 Query       SET @com_mysql_jdbc_outparam_2=_binary'2.0'
                   2641 Query       SET @com_mysql_jdbc_outparam_3=_binary'4'
                   2641 Query       SET @com_mysql_jdbc_outparam_4=_binary'Bad'
                   2641 Query       SET @com_mysql_jdbc_outparam_5=_binary'Good\
'
                   2641 Query       SET @com_mysql_jdbc_outparam_6=_binary'Ugly\
'
                   2641 Query       CALL addRoleAssessment3(@com_mysql_jdbc_out\
param_0,@com_mysql_jdbc_outparam_1,@com_mysql_jdbc_outparam_2,@com_mysql_jdbc_o\
utparam_3,@com_mysql_jdbc_outparam_4,@com_mysql_jdbc_outparam_5,@com_mysql_jdbc\
_outparam_6)
                   2641 Query       SELECT @com_mysql_jdbc_outparam_0,@com_mysq\
l_jdbc_outparam_1,@com_mysql_jdbc_outparam_2,@com_mysql_jdbc_outparam_3,@com_my\
sql_jdbc_outparam_4,@com_mysql_jdbc_outparam_5,@com_mysql_jdbc_outparam_6

Whereas, when I run your test case on the same server (5.0.18) I get the desired result.  So the difference must be in either the way we connect or in the driver.  I've sent the diffs between 5.0.4 and the driver I use for the code above.  You should at least be able to try my code above (on a database in which the stored procedure has been defined in advance) and see what result you get.
Does that sound like a reasonable next step?

[14 Oct 2007 21:20] Mark Matthews

Don, 

Prior to Connector/J 5.1.3, setting "noAccessToProcedureBodies=true" causes the driver to treat every parameter as INOUT, thus it should always cause this behavior (i.e. this is expected behavior). In 5.1.3 and newer, if "noAccessToProcedureBodies" is "true", the driver will report every parameter as IN, but will allow applications to call .registerOutParameter() on them, and thus the non-optimal way of sending parameters is only encountered if the parameter is only registered as an OUT parameter by the application. However, applications that require the metadata about a procedure to "match" what they can actually do won't work with this change.

When a parameter is OUT or INOUT, the only way to pass it currently is via user variables (a shortcoming of the server, itself).

We couldn't change the behavior of "noAccessToProcedureBodies" in 5.0, as that branch had been stable for some time, and as such the change was too large to be safe to implement there, which is why the change is in 5.1.

[14 Oct 2007 22:18] Don Cohen

regarding the last comment:
I'm not sure whether that's supposed to be in reply to my previous post.
I thought that the recent part of the thread was about procedures that use only IN parameters.  In the example I posted, addRoleAssessment3 used only IN parameters.  The issue was whether noAccessToProcedureBodies=true caused it to use multiple round trips.  In my test it did, while the test from Tonci Grgin showed one round trip.  Note that I ran that test using connector 5.0 - I don't think I even have a 5.1.  Are you saying that this test should not have worked?
Or perhaps the driver I have labeled connector-j-5-0 6607 is really 5.1 ?

[15 Oct 2007 7:15] Tonci Grgin

Hi Don.

> Whereas, when I run your test case on the same server (5.0.18) I get the desired result.

Good.

> So the difference must be in either the way we connect or in the driver.

I have posted my full connect string so it shouldn't be that. Please try latest version of c/J 5.0 branch or, even better, 5.1 branch as I think, judging by results we have here, that your problems are already fixed there.

> I've sent the diffs between 5.0.4 and the driver I use for the code above.  You should at least be able to try my code above (on a database in which the stored procedure has been defined in advance) and see what result you get.
Does that sound like a reasonable next step?

Well, I will give it a try but next reasonable step should be for you to try MySQL c/J driver as suggested above.

[15 Oct 2007 7:22] Don Cohen

> try latest version of c/J 5.0 branch
Is that different from what I have ? connector-j-5-0 6607
By try that you mean get a .jar equivalent and use it in my other test?
How does one do that?
BTW I can't actually use it in my applet until I make the corresponding changes to those I've made in my 5.0.4.

[15 Oct 2007 7:42] Tonci Grgin

Don, you need to re-sync your source repository, there has been several revisions since yours (I'm using 6625 at the moment). Let's focus on one thing at a time. My test case still works (meaning with predefined table and procedure) so here are my settings again:
 VM settings: -Xmx512M -XX:+UseParallelGC -Dcom.mysql.jdbc.testsuite.url.default=jdbc:mysql://localhost:3306/test?user=root&password=tonchika&UseUnicode=true
and
  Properties props = new Properties();
  props.setProperty("noAccessToProcedureBodies", "true");
  props.setProperty("profileSQL", "true");

and the result:
071015  9:22:35	      2 Connect     root@localhost on test
		      2 Query       SHOW SESSION VARIABLES
		      2 Query       SHOW COLLATION
		      2 Query       SET NAMES utf8
		      2 Query       SET character_set_results = NULL
		      2 Query       SET autocommit=1
		      2 Query       SET sql_mode='STRICT_TRANS_TABLES'
		      2 Query       SELECT VERSION()
		      3 Connect     root@localhost on test
		      3 Query       SHOW SESSION VARIABLES
		      3 Query       SHOW COLLATION
		      3 Query       SET NAMES utf8
		      3 Query       SET character_set_results = NULL
		      3 Query       SET autocommit=1
		      3 Query       SET sql_mode='STRICT_TRANS_TABLES'
		      3 Query       CALL addRoleAssessment3(null,1,2.0,4,'Bad',null,'Ugly')
		      2 Quit       

Let's try with my connection settings and new driver and add one option at the time until you encounter the error again, ok?

[15 Oct 2007 18:31] Don Cohen

> re-sync your source repository
I tried right click on connector-j - team - sync with repository.
There were no complaints but I still see 6607.
When I try team - update I get operation failed - some resources were not updated.
So I guess you'll have to tell me how to resync.
After that I guess you want me to run your test again.  But since it already works
My vm settings differ from yours in terms of host,user,password, of course,
but also I have &noAccessToProcedureBodies=true and I don't have &UseUnicode=true
I hope that's no problem.
My properties in the code look the same as yours.
Your result differs from mine now in that you don't seem to include the definition of the procedure, but I see the same sort of call that you show.

BTW, I now think my previous report was incorrect - I just tried my old code with no ssl and no properties other than no access to procedure bodies and I got the multiple round trip behavior.
Fortunately I changed a parameter in the test so I can distinguish the two cases in the log.

Given that 6607 already works I'm not sure why it's important to get the last few changes, unless you think they're really relevant here.
Since my code actually uses
            + "&dontTrackOpenResources=true"
            + "&dumpQueriesOnException=true"
I tried adding those to the test case, and that still worked.
So the only remaining difference is ssl and the driver.
I see a couple approaches from here.

- you should tell me how to use ssl in your test case and I can try that
- you or I can try to add my changes to the current 5.0 connector and then
  I can make a new jar out of it and try that in my applet test
If that works then we know that the difference is somewhere between 5.0.4
and the current 5.0.
However, in terms of actually helping me solve the problem in my applet it seems more worth while to put my changes into 5.1.

Is there any expectation that the 5.1 driver will (soon) contain my addition of
requireSSLcert ?  If so I'd like to just try that version.  If not, then I guess I'll have to add the change in order to try it.
Which version of 5.1 do you think I should use and how do I get it?

[17 Oct 2007 6:16] Tonci Grgin

Hi Don. I'll try to get Mark take another look into this as he's the only one who can answer some of your questions.

> - you should tell me how to use ssl in your test case and I can try that

Please see testsuite.simple.SSLTest.java and our documentation for this.

> - you or I can try to add my changes to the current 5.0 connector and then
  I can make a new jar out of it and try that in my applet test
If that works then we know that the difference is somewhere between 5.0.4
and the current 5.0.

I can't, it's against SOP for me to do code review unless you have support contract.

>However, in terms of actually helping me solve the problem in my applet it seems more worth while to put my changes into 5.1.
> Is there any expectation that the 5.1 driver will (soon) contain my addition of requireSSLcert ?  If so I'd like to just try that version.  If not, then I guess I'll have to add the change in order to try it.

Only Mark can decide on this, not me.

> Which version of 5.1 do you think I should use and how do I get it?

As 5.1 proved to complicated for me to compile I would suggest you try latest 5.1 and/or 5.0 from http://downloads.mysql.com/snapshots.php. As for using sources, SVN repositories have been reorganized so might need to clone again.

[21 Oct 2007 20:14] Don Cohen

ok, I reply just to return the state to open

[22 Oct 2007 16:27] Don Cohen

> > - you should tell me how to use ssl in your test case and I can try that
> Please see testsuite.simple.SSLTest.java and our documentation for this.
I've now looked at it and I don't see how or even THAT it works.

public class SSLTest extends BaseTestCase {
	public SSLTest(String name) {
		super(name);
		System.setProperty("javax.net.debug", "all");
		StringBuffer sslUrl = new StringBuffer(dbUrl);
		if (dbUrl.indexOf("?") == -1) {
			sslUrl.append("?");
		} else {
			sslUrl.append("&");
		}
		sslUrl.append("useSSL=true");
	}
This all appears to add useSSL to a new StringBuffer but I don't see how that
value is ever used.
When I run the test I get output:
===
Loading JDBC driver 'com.mysql.jdbc.Driver'
Done.

Done.

<<<<<<<<<<< Look for SSL debug output >>>>>>>>>>>
Connected to 5.0.18-log
===
I guess that shows that it connected, which is all I wanted, but it does not
show that the connection used SSL, and in fact, when I record the packets it appears that the connection did NOT use SSL.

[25 Oct 2007 10:42] Tonci Grgin

Don, seems so, see Bug#19705.

[25 Oct 2007 15:09] Don Cohen

> Don, seems so, see Bug#19705.
I'm having a hard time making sense of this.
You mean the test doesn't work?  
And yet the bug above is supposed to be fixed?
If the test is supposed to work, tell me what to do to make it work.
If not, then (a) it's a bug to be fixed and (b) you have not yet told me how to run the other test using ssl.

#19705:
> [18 Aug 2006 14:01] Jon Stephens
> Thank you for your bug report. This issue has been committed to our source 
> repository of that product and will be incorporated into the next release.
Is this supposed to mean that this test works in some more recent version - of what?  I think all of the software I now use is more recent than Aug 2006.
I just tried connecting to a server using 5.0.45 - same result.
Also #19705 says at the top that this test works in linux but not windows.  And I'm using linux here - both client and server.
And what do you have to say about my complaint looking at the source that I don't see how the string buffer containing the ssl directive is ever used?

[25 Oct 2007 16:01] Mark Matthews

Don, 

I'm not sure what Tonci was getting at with Bug#19705.

It appears the testcase is broken as well.

The answer to *this* bug is that you need the fix to BUG#28689, which shipped in 5.0.8. 5.0.8 and 5.1.5 don't have your patches to support non-verification of server certificates, it looks like they'll end up in a future release of 5.1 or perhaps 6.0, due to other priorities.

If you happen to be able to re-generate a patch against either of these versions (5.1 would be great), I can look at applying it immediately, and getting it out in 5.1.6 before the end of the year.

[25 Oct 2007 16:47] Tonci Grgin

Thanks Mark.

[27 Oct 2007 23:08] Don Cohen

transcript showing diffs from 5.1.5

Attachment: diffs-5.1.5 (application/octet-stream, text), 4.24 KiB.

[27 Oct 2007 23:27] Don Cohen

I've just uploaded a shell transcript showing diffs from 5.1.5.
This does not include the file TrustingSSLSocketFactory.java
but that's unchanged from the previous diffs I sent.
I've now tried this with my application and it seems to work.
I can't quite tell what's happening at the packet level when I use ssl, but the number of packets seems about the same, so I'm not worried.
When I use an out parameter I see a pattern of four round trips, corresponding to what I see in the log:
       SET @com_mysql_jdbc_outparam_0=null
       CALL CVupdate2(@com_mysql_jdbc_outparam_0, 8, 'b', 'bccdd', 'bbb',  
            '2007/1/2', '1970-01-01', 'bbaaa',1)
       SELECT @com_mysql_jdbc_outparam_0
       SHOW WARNINGS
I think I'm explicitly asking for the warnings, so I'm essentially paying one extra round trip to set the output param and another to read it.  So there's room for improvement, but a lot better than a round trip for each input.

When I avoid output parameters I see
       CALL addStaffAssessment4(1,1,14,0.0,0.0,0,1,'','','','','','')   
       SHOW WARNINGS
Which will give me some incentive to move to the new protocol not using output
parameters.
So I view this problem as solved.
Thanks for your help.

[28 Oct 2007 14:42] Tonci Grgin

Don, I'm glad that the problem is solved.

[2 Nov 2007 16:52] Mark Matthews

Don,

Please see http://lists.mysql.com/commits/36585, which is in the nightly snapshots of 5.1, and will ship in 5.1.6. We ended up wanting to change the name of the configuration parameter a bit, and it sends you into the code path where if you _want_ to use a client-side certificate that you specify the keystore path, password and (optionally) type as URL configuration properties rather than system properties, but that's actually more desirable for most folks.

Thanks for your patience on getting this all integrated!

 --Mark