Bug #39184 Procedure call returns wrong error message for connection failure
Submitted: 2 Sep 2008 12:03 Modified: 31 Dec 2015 0:45
Reporter: Zhihong Zhang Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S3 (Non-critical)
Version:mysql-connector-java-5.1.6 OS:Any
Assigned to: Alexander Soklakov CPU Architecture:Any
Tags: procedure call, stale connection

[2 Sep 2008 12:03] Zhihong Zhang
Description:
When there is connection problem, the JDBC prepareCall() throws SQLException with message "FUNCTION xxx does not exist". This is very confusing and it took me a whole day to find the root cause.

It's caused by this logic in DatabaseMetaData.java,

			try {
				paramRetrievalRs = paramRetrievalStmt
						.executeQuery("SHOW CREATE PROCEDURE "
								+ procNameBuf.toString());
				parsingFunction = false;
			} catch (SQLException sqlEx) {
				paramRetrievalRs = paramRetrievalStmt
						.executeQuery("SHOW CREATE FUNCTION "
								+ procNameBuf.toString());
				parsingFunction = true;
			}

The I/O error is swallowed by the catch and it tries the "SHOW CREATE FUNCTION". Because called routine is a procedure, the server returns the message, which is in the final exception the caller is getting.

How to repeat:
1. Make sure JDBC connection URL has "autoReconnect=true".
2. Send any request.
3. Wait for the connection to close by idle timer on server. The connection must be idle longer than wait_timeout.
4. Try to call any PROCEDURE  X(not function) with prepareCall(). You should get SQLException with the misleading error "FUNCTION X does not exist".

Suggested fix:
Check the server response and only catch the specific errors. Bubble up the IO exceptions to caller.

This logic really panelizes FUNCTION calls (it has to go to server twice for prepareCall). It would be great for performance if this logic is removed entirely.
[2 Sep 2008 12:21] Mark Matthews
As for removing the logic, there isn't a single place one can retrieve the metadata for stored functions and procedures, and some poorly-behaved applications treat them interchangably, so we have to support that as well, unfortunately, with no way to distinguish client-side what the end-user is trying to call.
[2 Sep 2008 15:16] Zhihong Zhang
I know you can't remove the logic without server changes. Since the "SHOW CREATE" are SQL extensions. Can we extend it further by adding something like "SHOW CREATE ROUTINE" to retrieve meta data for both procedures and functions?
[2 Sep 2008 19:17] Tonci Grgin
Hi Zhihong and thanks for reasonable feature request.

We'll be monitoring server changes and add this functionality to c/J in due time.
[2 Sep 2008 19:21] Mark Matthews
The required information schema view ("parameters"), is available in mysql-6.0.0 (alpha), and code to support their availability is sitting in the trunk of c/j.

For "brave souls", you can get a nightly snapshot build of c/j trunk at http://downloads.mysql.com/snapshots.php
[30 Sep 2008 18:43] Konstantin Osipov
"parameters" view is available now, no reason to keep this bug in TBFL status.
[29 Jun 2012 14:10] Alexander Soklakov
Hi Zhihong,

I was not able to repeat the problem with latest c/J sources. Please, report if it's still actual for you.

I_S "parameters" view is used too if connect with useInformationSchema=false.
[23 Sep 2012 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[23 Sep 2012 11:37] Zhihong Zhang
You can close this bug now. We don't use MySQL any more so there is no way for me to confirm the fix. Thanks!
[31 Dec 2015 0:45] Filipe Silva
Posted by developer:
 
Fixed in Connector/J 5.1.13. 
I'm closing this bug report.