Bug #71749 MySQL Fed issue Unexpected error code S1C00 received from data source - Paramete
Submitted: 17 Feb 2014 11:38 Modified: 3 Aug 2015 13:13
Reporter: Antonello Supino Email Updates:
Status: Verified Impact on me:
None 
Category:Connector / J Severity:S2 (Serious)
Version:mysql-connector-java-5.1.26-bin.jar OS:Linux
Assigned to: Alexander Soklakov CPU Architecture:Any
Tags: parameter meta data, prepared statement

[17 Feb 2014 11:38] Antonello Supino
Description:
This issue is very similar to the one in http://bugs.mysql.com/bug.php?id=30858 and is hampering our ability to fetch data from MySQL data sources during ETL and produce business reports.

We are having issues when selecting data through IBM DB2 mysql federation investigated under IBM PMR 93917,019,866.

IBM suggested to raise this as MySQL bug in JDBC driver connector.

I can provide further details if needed.

How to repeat:

Hi Antonello,

As (IBM) our investigation, the error is expected for MySQL JDBC driver. We searched their document. You need to specify generateSimpleParameterMetadata=true to the URL to get rid of this problem.

e.g.
     ,PUSHDOWN  'Y'
     ,url 'jdbc:mysql://iiregr01.cn.ibm.com:3306/test?generateSimpleParameterMetadata=true'
     ,JDBC_LOG 'Y'
 
Unfortunately , we met another problem:
 
id
----
SQL1822N  Unexpected error code "HY000" received from data source
"JDBC_SERVER_MYSQL". Associated text and tokens are "Incorrect datetime value:
'   ) )S&s(   ".  SQLSTATE=560BD
 
This problem is also due to the MySQL JDBC driver, In our code when we call get_col_type to get the remote type. The value is Clob other than Timestamp.
 
59      entry DB2 UDB JDBC wrapper Jdbc_Utilities::getJavaTypeFromJDBCType fnc (1.3.180.153.0)
        pid 9484 tid 1083185472 cpid -1 node 0

        bytes 10

        Data1   (PD_TYPE_SINT,2) signed integer:
        12
 
#define SQLQG_JAVA_Timestamp    11    /**< \brief  java.sql.Timestamp */
#define SQLQG_JAVA_Clob       12
#define SQLQG_JAVA_Blob        13

We also wrote a sample Java application
 
=> java MySQLTest
remote type = 12
OK.
 
                        PreparedStatement pstmt = con.prepareStatement("select id from mysqlnick where time_end < ?");
                        ParameterMetaData md = pstmt.getParameterMetaData();
                        int type = md.getParameterType(1);
                        System.out.println("remote type = " + type);
 
I think it's a limitation if the vendor's driver return wrong type.

I have simplified and reproduced the issue you met in our lab environment, will keep on digging the root cause and update you when have new findings, thanks.

db2 => describe table mysqlnick

                                Data type                     Column
Column name                     schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
id                              SYSIBM    CHARACTER                    4     0 Yes
time_end                        SYSIBM    TIMESTAMP                   10     6 No

  2 record(s) selected.

db2 => select "id" from mysqlnick where "time_end" < current_timestamp

id
----
SQL1822N  Unexpected error code "S1C00" received from data source
"JDBC_MYSQL". Associated text and tokens are "Parameter metadata not available
for the given statem".  SQLSTATE=560BD
 
db2 => select "id" from mysqlnick where "time_end" < timestamp(date(current_timestamp),time(current_timestamp))

id
----
a002
a003

  2 record(s) selected.

Suggested fix:
Fix the JDBC driver and make it return the right type.
[19 Feb 2014 10:20] Filipe Silva
Hi Antonello,

Thank you for this bug report.
[21 Feb 2014 19:09] Filipe Silva
Hi Antonello,

I will need more detail on this. Ideally a piece of code with a sample case showing the results you are getting from the Connector/J (with and without generateSimpleParameterMetadata=true) that you expected differently. I possible, please tell us what exactly you were expecting.

Thanks,
[22 Feb 2014 17:41] Mark Matthews
MySQL Server doesn't return working parameter types for prepared statements, so I'm not sure how this could be fixed in the driver, at least not until it's fixed in the server.
[10 Mar 2014 14:32] Antonello Supino
Mark,
thanks for investigating.

Would you like me me to raise a new bug report for having this fixed on server side?
[10 Apr 2014 13:47] Alexander Soklakov
I leave this report as verified until we decide if it's possible to update server functionality.
[6 May 2014 8:34] Antonello Supino
Is there any update on this?
IBM is going to close the associated PMR 93917,019,866 as aged.

I appreciate this is a free of charge service and I am thankful that you are looking into this but I will take into consideration that resolution time may take months for future business decisions.
[30 Jun 2014 8:55] Antonello Supino
Is there any update ?

This issue has been opened for months and a solution is not available, IBM is going to close the associated PMR.

Regards
[3 Aug 2015 13:13] Antonello Supino
We keep hitting this issue and writing workarounds.

I am strongly dissatisfied of the level of support we receive from Oracle and MySQL, even if we pay for a maintenance license in case of issue like this we do not get any kind of support whatsoever.