Bug #60816 | Cannot pass NULL to an INOUT procedure parameter | ||
---|---|---|---|
Submitted: | 9 Apr 2011 11:13 | Modified: | 3 Apr 2013 1:58 |
Reporter: | Lukas Eder | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | Connector / J | Severity: | S3 (Non-critical) |
Version: | 5.1.15 | OS: | Any |
Assigned to: | Alexander Soklakov | CPU Architecture: | Any |
Tags: | INOUT parameter, null, stored procedure |
[9 Apr 2011 11:13]
Lukas Eder
[11 Apr 2011 14:17]
Tonci Grgin
Hi Lukas and thanks for your report. Alas, it is not as complete as you think. Please provide full test case including the connection string and do note MySQL server version you run against.
[11 Apr 2011 14:37]
Lukas Eder
The server versions I discovered this with, are in the bug's header (5.1.14 and 5.5.8). This is how I connect to the database: Class.forName(driver); Connection connection = DriverManager.getConnection( "jdbc:mysql://localhost/test", "root", "");
[11 Apr 2011 14:47]
Tonci Grgin
Thank you Lukas. What happens if you put useInformationSchema=true in your connection string?
[11 Apr 2011 14:47]
Tonci Grgin
And, btw, "Version" field is for *c/J* version, not server version so please note it somewhere.
[11 Apr 2011 16:11]
Lukas Eder
This has been observed with Connector/J versions 5.1.10 and 5.1.15 And with the MySQL database version 5.1.14 and 5.5.8
[11 Apr 2011 16:16]
Lukas Eder
This JDBC URL produces the same issue: jdbc:mysql://localhost/test?useInformationSchema=true Or when I create the connection like this: Properties properties = new Properties(); properties.setProperty("user","root"); properties.setProperty("password",""); properties.setProperty("useInformationSchema","true"); connection = DriverManager.getConnection("jdbc:mysql://localhost/test", properties);
[13 Apr 2011 8:08]
Tonci Grgin
Lukas, we have several points here: o Since NULL is not "" or 0, your actual return value is NULL (not 1 as you might expected): mysql> DELIMITER $$ mysql> CREATE PROCEDURE bug60816(INOUT x INTEGER) BEGIN SET x = x + 1; END$$ Query OK, 0 rows affected (0.14 sec) mysql> DELIMITER ; mysql> SET @x = NULL; Query OK, 0 rows affected (0.00 sec) mysql> CALL bug60816(@x); Query OK, 0 rows affected (0.00 sec) mysql> SELECT @x; +------+ | @x | +------+ | NULL | +------+ 1 row in set (0.00 sec) o Next, we have JDBC specs not completely clear on INOUT parameter being NULL in http://docsrv.sco.com/JDK_guide/jdbc/getstart/callablestatement.doc.html. As can be seen, only 7.1.5 Retrieving NULL Values as OUT Parameters is covered which makes sense in your test with 2 parameters. There is also a http://forums.mysql.com/read.php?39,412249,412249 so I will have to consult on this more.
[13 Apr 2011 8:16]
Lukas Eder
Tonci, thanks for analysing this. NULL is what I would expect to be returned, not 1. Maybe there was a misunderstanding somewhere... This is the expected behaviour on the failing execution after a fix: assertEqual(0, call.getInt(1)); assertTrue(call.wasNull());
[26 Jul 2011 17:06]
Sveta Smirnova
Set to "Verified" as case was confirmed. Test case will be attached.
[26 Jul 2011 17:10]
Sveta Smirnova
test case for the testsuite
Attachment: bug60816.java (text/plain), 1.52 KiB.
[27 Mar 2013 10:33]
Andrey Denisov
When this error is corrected? Library version 5.1.24 and still not fixed
[27 Mar 2013 13:01]
Alexander Soklakov
Hi, The reason of this exception is that driver sets variable to 'null' literal instead of real NULL value. So this isn't the matter of JDBC spec of something, just the bug in driver impl. Patch is quite simple, I guess the fix will be included to next release.
[28 Mar 2013 8:51]
Andrey Denisov
Hi Thank you, we will wait for the next release
[3 Apr 2013 1:58]
Paul DuBois
Noted in 5.1.25 changelog. An exception occurred if NULL was passed to a stored procedure INOUT parameter.