[CONJ-870] Incorrect Stored Procedure Parameters Returned Created: 2021-04-01  Updated: 2021-05-02  Resolved: 2021-04-02

Status: Closed
Project: MariaDB Connector/J
Component/s: Other
Affects Version/s: 2.0.1, 2.3.0, 1.8.0, 2.4.4, 2.7.0, 2.7.2
Fix Version/s: N/A

Type: Bug Priority: Critical
Reporter: Juan Assignee: Diego Dupin
Resolution: Not a Bug Votes: 0
Labels: None

Attachments: Java Source File SPRunner.java     File cs0274360-example-procedure.sql    

 Description   

When the MariaDB connector is used to call a stored procedure with defined output variables, but only a subset of these variables is registered for output, the wrong values are returned to the calling object. For example, if the stored procedure has 2 output parameters P1 and P2, and the calling object registers and gets the value for only parameter P2, the value returned will be the value of P1. In other words, the MariaDB driver is unaware that parameter P1 was skipped in the call. By contrast, MySQL 8.0.11 connector correctly returns the value of P2 in this case.

In the provided example a 6 parameter stored procedure is called and values 2, 3, 4, and 6 are called and returned when using the MySQL 8.0.11 driver, while the MariaDB drivers returns values 1, 2, 3, and 4.

Same results using MariaDB 10.5.8 or 10.4.17, and MariaDB Connector/J drivers going as far back as 1.8.0

 Comments   
Comment by Diego Dupin [ 2021-04-02 ]

As JDBC state : All OUT parameters must be registered before a stored procedure is executed.

Not registering an output parameter as the example state is not an option.

Implementation of MySQL Driver and MariaDB differ completely for stored procedure.

to simplify MariaDB use

  • PREPARE "call EXAMPLE_OUTPUT_SP(?, ? , ?, ?, ?, ?)"
  • EXECUTE
  • CLOSE
    3 exchanges with server for first execution, 1 exchange (EXECUTE) afterwhile.

MySQL use multiple commands 4 exchanges for first use, 3 for second execution:

  • Identify store procedure : COMMAND
    "SELECT SPECIFIC_SCHEMA AS PROCEDURE_CAT, NULL AS `PROCEDURE_SCHEM`, SPECIFIC_NAME AS `PROCEDURE_NAME`, IFNULL(PARAMETER_NAME, '') AS `COLUMN_NAME`, CASE WHEN PARAMETER_MODE = 'IN' THEN 1 WHEN PARAMETER_MODE = 'OUT' THEN 4 WHEN PARAMETER_MODE = 'INOUT' THEN 2 WHEN ORDINAL_POSITION = 0 THEN 5 ELSE 0 END AS `COLUMN_TYPE`, CASE WHEN UPPER(DATA_TYPE)='DECIMAL' THEN 3 WHEN UPPER(DATA_TYPE)='DECIMAL UNSIGNED' THEN 3 WHEN UPPER(DATA_TYPE)='TINYINT' THEN CASE WHEN LOCATE('ZEROFILL', UPPER(DTD_IDENTIFIER)) = 0 AND LOCATE('UNSIGNED', UPPER(DTD_IDENTIFIER)) = 0 AND LOCATE('(1)', DTD_IDENTIFIER) != 0 THEN -7 ELSE -6 END WHEN UPPER(DATA_TYPE)='TINYINT UNSIGNED' THEN CASE WHEN LOCATE('ZEROFILL', UPPER(DTD_IDENTIFIER)) = 0 AND LOCATE('UNSIGNED', UPPER(DTD_IDENTIFIER)) = 0 AND LOCATE('(1)', DTD_IDENTIFIER) != 0 THEN -7 ELSE -6 END WHEN UPPER(DATA_TYPE)='BOOLEAN' THEN 16 WHEN UPPER(DATA_TYPE)='SMALLINT' THEN 5 WHEN UPPER(DATA_TYPE)='SMALLINT UNSIGNED' THEN 5 WHEN UPPER(DATA_TYPE)='INT' THEN 4 WHEN UPPER(DATA_TYPE)='INT UNSIGNED' THEN 4 WHEN UPPER(DATA_TYPE)='FLOAT' THEN 7 WHEN UPPER(DATA_TYPE)='FLOAT UNSIGNED' THEN 7 WHEN UPPER(DATA_TYPE)='DOUBLE' THEN 8 WHEN UPPER(DATA_TYPE)='DOUBLE UNSIGNED' THEN 8 WHEN UPPER(DATA_TYPE)='NULL' THEN 0 WHEN UPPER(DATA_TYPE)='TIMESTAMP' THEN 93 WHEN UPPER(DATA_TYPE)='BIGINT' THEN -5 WHEN UPPER(DATA_TYPE)='BIGINT UNSIGNED' THEN -5 WHEN UPPER(DATA_TYPE)='MEDIUMINT' THEN 4 WHEN UPPER(DATA_TYPE)='MEDIUMINT UNSIGNED' THEN 4 WHEN UPPER(DATA_TYPE)='DATE' THEN 91 WHEN UPPER(DATA_TYPE)='TIME' THEN 92 WHEN UPPER(DATA_TYPE)='DATETIME' THEN 93 WHEN UPPER(DATA_TYPE)='YEAR' THEN 91 WHEN UPPER(DATA_TYPE)='VARCHAR' THEN 12 WHEN UPPER(DATA_TYPE)='VARBINARY' THEN -3 WHEN UPPER(DATA_TYPE)='BIT' THEN -7 WHEN UPPER(DATA_TYPE)='JSON' THEN -1 WHEN UPPER(DATA_TYPE)='ENUM' THEN 1 WHEN UPPER(DATA_TYPE)='SET' THEN 1 WHEN UPPER(DATA_TYPE)='TINYBLOB' THEN -3 WHEN UPPER(DATA_TYPE)='TINYTEXT' THEN 12 WHEN UPPER(DATA_TYPE)='MEDIUMBLOB' THEN -4 WHEN UPPER(DATA_TYPE)='MEDIUMTEXT' THEN -1 WHEN UPPER(DATA_TYPE)='LONGBLOB' THEN -4 WHEN UPPER(DATA_TYPE)='LONGTEXT' THEN -1 WHEN UPPER(DATA_TYPE)='BLOB' THEN -4 WHEN UPPER(DATA_TYPE)='TEXT' THEN -1 WHEN UPPER(DATA_TYPE)='CHAR' THEN 1 WHEN UPPER(DATA_TYPE)='BINARY' THEN -2 WHEN UPPER(DATA_TYPE)='GEOMETRY' THEN -2 WHEN UPPER(DATA_TYPE)='UNKNOWN' THEN 1111 WHEN UPPER(DATA_TYPE)='POINT' THEN -2 WHEN UPPER(DATA_TYPE)='LINESTRING' THEN -2 WHEN UPPER(DATA_TYPE)='POLYGON' THEN -2 WHEN UPPER(DATA_TYPE)='MULTIPOINT' THEN -2 WHEN UPPER(DATA_TYPE)='MULTILINESTRING' THEN -2 WHEN UPPER(DATA_TYPE)='MULTIPOLYGON' THEN -2 WHEN UPPER(DATA_TYPE)='GEOMETRYCOLLECTION' THEN -2 WHEN UPPER(DATA_TYPE)='GEOMCOLLECTION' THEN -2 ELSE 1111 END AS `DATA_TYPE`, UPPER(CASE WHEN UPPER(DATA_TYPE)='TINYINT' THEN CASE WHEN LOCATE('ZEROFILL', UPPER(DTD_IDENTIFIER)) = 0 AND LOCATE('UNSIGNED', UPPER(DTD_IDENTIFIER)) = 0 AND LOCATE('(1)', DTD_IDENTIFIER) != 0 THEN 'BIT' WHEN LOCATE('UNSIGNED', UPPER(DTD_IDENTIFIER)) != 0 AND LOCATE('UNSIGNED', UPPER(DATA_TYPE)) = 0 THEN 'TINYINT UNSIGNED' ELSE DATA_TYPE END WHEN LOCATE('UNSIGNED', UPPER(DTD_IDENTIFIER)) != 0 AND LOCATE('UNSIGNED', UPPER(DATA_TYPE)) = 0 AND LOCATE('SET', UPPER(DATA_TYPE)) <> 1 AND LOCATE('ENUM', UPPER(DATA_TYPE)) <> 1 THEN CONCAT(DATA_TYPE, ' UNSIGNED') WHEN UPPER(DATA_TYPE)='POINT' THEN 'GEOMETRY' WHEN UPPER(DATA_TYPE)='LINESTRING' THEN 'GEOMETRY' WHEN UPPER(DATA_TYPE)='POLYGON' THEN 'GEOMETRY' WHEN UPPER(DATA_TYPE)='MULTIPOINT' THEN 'GEOMETRY' WHEN UPPER(DATA_TYPE)='MULTILINESTRING' THEN 'GEOMETRY' WHEN UPPER(DATA_TYPE)='MULTIPOLYGON' THEN 'GEOMETRY' WHEN UPPER(DATA_TYPE)='GEOMETRYCOLLECTION' THEN 'GEOMETRY' WHEN UPPER(DATA_TYPE)='GEOMCOLLECTION' THEN 'GEOMETRY' ELSE UPPER(DATA_TYPE) END) AS TYPE_NAME, CASE WHEN LCASE(DATA_TYPE)='date' THEN 0 WHEN LCASE(DATA_TYPE)='time' OR LCASE(DATA_TYPE)='datetime' OR LCASE(DATA_TYPE)='timestamp' THEN DATETIME_PRECISION WHEN (UPPER(DATA_TYPE)='TINYINT' AND LOCATE('ZEROFILL', UPPER(DTD_IDENTIFIER)) = 0) AND LOCATE('UNSIGNED', UPPER(DTD_IDENTIFIER)) = 0 AND LOCATE('(1)', DTD_IDENTIFIER) != 0 THEN 1 WHEN UPPER(DATA_TYPE)='MEDIUMINT' AND LOCATE('UNSIGNED', UPPER(DTD_IDENTIFIER)) != 0 THEN 8 WHEN UPPER(DATA_TYPE)='JSON' THEN 1073741824 ELSE NUMERIC_PRECISION END AS `PRECISION`, CASE WHEN LCASE(DATA_TYPE)='date' THEN 10 WHEN LCASE(DATA_TYPE)='time' THEN 8+(CASE WHEN DATETIME_PRECISION>0 THEN DATETIME_PRECISION+1 ELSE DATETIME_PRECISION END) WHEN LCASE(DATA_TYPE)='datetime' OR LCASE(DATA_TYPE)='timestamp' THEN 19+(CASE WHEN DATETIME_PRECISION>0 THEN DATETIME_PRECISION+1 ELSE DATETIME_PRECISION END) WHEN (UPPER(DATA_TYPE)='TINYINT' OR UPPER(DATA_TYPE)='TINYINT UNSIGNED') AND LOCATE('ZEROFILL', UPPER(DTD_IDENTIFIER)) = 0 AND LOCATE('UNSIGNED', UPPER(DTD_IDENTIFIER)) = 0 AND LOCATE('(1)', DTD_IDENTIFIER) != 0 THEN 1 WHEN UPPER(DATA_TYPE)='MEDIUMINT' AND LOCATE('UNSIGNED', UPPER(DTD_IDENTIFIER)) != 0 THEN 8 WHEN UPPER(DATA_TYPE)='JSON' THEN 1073741824 WHEN CHARACTER_MAXIMUM_LENGTH IS NULL THEN NUMERIC_PRECISION WHEN CHARACTER_MAXIMUM_LENGTH > 2147483647 THEN 2147483647 ELSE CHARACTER_MAXIMUM_LENGTH END AS LENGTH,NUMERIC_SCALE AS `SCALE`, 10 AS RADIX,1 AS `NULLABLE`, NULL AS `REMARKS`, NULL AS `COLUMN_DEF`, NULL AS `SQL_DATA_TYPE`, NULL AS `SQL_DATETIME_SUB`, CHARACTER_OCTET_LENGTH AS `CHAR_OCTET_LENGTH`, ORDINAL_POSITION, 'YES' AS `IS_NULLABLE`, SPECIFIC_NAME FROM INFORMATION_SCHEMA.PARAMETERS WHERE SPECIFIC_SCHEMA = 'testj' AND SPECIFIC_NAME LIKE 'EXAMPLE_OUTPUT_SP' AND (PARAMETER_NAME LIKE '%' OR PARAMETER_NAME IS NULL) ORDER BY SPECIFIC_SCHEMA, SPECIFIC_NAME, ROUTINE_TYPE, ORDINAL_POSITION"
  • COMMAND "SELECT @@session.transaction_read_only"
  • COMMAND "CALL EXAMPLE_OUTPUT_SP(@com_mysql_jdbc_outparam_p_output1, @com_mysql_jdbc_outparam_p_output2, @com_mysql_jdbc_outparam_p_output3, @com_mysql_jdbc_outparam_p_output4, @com_mysql_jdbc_outparam_p_output5, @com_mysql_jdbc_outparam_p_output6)"
  • COMMAND "SELECT @com_mysql_jdbc_outparam_p_output1,@com_mysql_jdbc_outparam_p_output2,@com_mysql_jdbc_outparam_p_output3,@com_mysql_jdbc_outparam_p_output4,@com_mysql_jdbc_outparam_p_output5,@com_mysql_jdbc_outparam_p_output6"

So implementation completely differ.
MySQL driver depending on stored procedure is generally 3 times slower, but MariaDB required output parameter to be set as JDBC required.

Generated at Thu Feb 08 03:18:55 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.