MySQL Bugs: #71749: MySQL Fed issue Unexpected error code S1C00 received from data source

Bug #71749	MySQL Fed issue Unexpected error code S1C00 received from data source - Paramete
Submitted:	17 Feb 2014 11:38	Modified:	27 Jan 2022 12:07
Reporter:	Antonello Supino	Email Updates:
Status:	Can't repeat	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

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.

Hi Antonello,

Thank you for this bug report.

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,

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.

Mark,
thanks for investigating.

Would you like me me to raise a new bug report for having this fixed on server side?

I leave this report as verified until we decide if it's possible to update server functionality.

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.

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

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.

Posted by developer:
 
The required server with was implemented in MySQL 8.0.22 under WL#9384.

There is no need to fix anything in Connector/J regarding this functionality. The following test demonstrates current behaviour against MySQL 8.0.28:

    @Test
    public void testBug71749() throws Exception {
        createTable("testBug71749", "(id char(10),time_end TIMESTAMP(6))");

        Properties props = new Properties();
        props.setProperty(PropertyKey.useSSL.getKeyName(), "false");
        props.setProperty(PropertyKey.allowPublicKeyRetrieval.getKeyName(), "true");

        boolean useSPS = false;
        boolean generateSimpleMetadata = false;
        do {
            props.setProperty(PropertyKey.useServerPrepStmts.getKeyName(), "" + useSPS);
            props.setProperty(PropertyKey.generateSimpleParameterMetadata.getKeyName(), "" + generateSimpleMetadata);
            System.out.println("\nuseServerPrepStmts=" + useSPS + ", generateSimpleParameterMetadata=" + generateSimpleMetadata);
            Connection con = getConnectionWithProps(props);

            PreparedStatement ps = con.prepareStatement("select id from testBug71749 where time_end < ?");
            ParameterMetaData md = ps.getParameterMetaData();
            try {
                System.out.println("parameter type = " + md.getParameterType(1));
            } catch (Exception e) {
                System.out.println(e.getClass() + ": " + e.getMessage());
            }

        } while ((generateSimpleMetadata = !generateSimpleMetadata) || (useSPS = !useSPS));
    }

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Connected to 8.0.28

useServerPrepStmts=false, generateSimpleParameterMetadata=false
class java.sql.SQLException: Parameter metadata not available for the given statement

useServerPrepStmts=false, generateSimpleParameterMetadata=true
parameter type = 12

useServerPrepStmts=true, generateSimpleParameterMetadata=false
parameter type = 93

useServerPrepStmts=true, generateSimpleParameterMetadata=true
parameter type = 93

Please note that only real server-side prepared statements (with useServerPrepStmts=true) give a proper results. With useServerPrepStmts=false prepared statements are emulated in Connector/J and sent as a plain query, so that there is no way to figure out real parameter types.

Posted by developer:
 
This bug is not reproducible with the latest Connector/J 8.0 and MySQL 8.0.22 and above.

Connector/J 5.1 series came to EOL on Feb 9th, 2021, see https://www.mysql.com/support/eol-notice.html, so this bug will not be fixed there.