Bug #84086 Invoking getString on blob data type doesn't return string value
Submitted: 7 Dec 2016 5:57 Modified: 7 Dec 2016 10:17
Reporter: Alexander Kunin Email Updates:
Status: Verified Impact on me:
None 
Category:Connector / J Severity:S3 (Non-critical)
Version:5.1.40 OS:Microsoft Windows
Assigned to:
Tags: BLOB, getstring, useServerPrepStmts=true

[7 Dec 2016 5:57] Alexander Kunin
Description:
Incorrect data returned on method call resultSet.getString("blob_value") when connection is set to useServerPrepStmts=true. 

When useServerPrepStmts=true

Calling resultSet.getString("blob_value") returns text of byte[].toString().
Incorrect result.

When useServerPrepStmts=false

Calling resultSet.getString("blob_value") returns actual text value of blob 
same as I can get with like new String(blob.getBytes(1l, (int) blob.length()));
Correct result.

How to repeat:
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * @author Alexander Kunin
 */
public class BlobAsStringTest {

    private static String USER = "root";
    private static String PASS = "root";
    private static String CATALOG = "use_server_prepared_statement_test";
    private static String URL = "jdbc:mysql://localhost:3306/"; //
    private static String USE_SERVER_PREP_STMTS = "?useServerPrepStmts=true"; //
    private static String VALUE_ONE = "bar";
    private static String VALUE_BLOB = "mdEVifSLatPfO1FLKlSzW0wojTJdCdnPjEn5fiCEg6Rals316NVyqT8Un7WBLqaDWYVaOUF/MaKGSVFCekLAqy33LCGcJoTL";

    private static String DRIVER = "com.mysql.jdbc.Driver";

    /**
     * Constructor
     */
    public BlobAsStringTest() {
    }

    public static void main(String[] args) {
        boolean dbExists = false;
        Connection conn1 = null;
        Connection conn2 = null;
        Statement st = null;
        PreparedStatement pstm1 = null, pstm2 = null;
        ResultSet rs = null, rs1 = null, rs2 = null;
        String sql = "select id, val_one, val_blob from foo where val_one = ?";
        try {
            Class.forName(DRIVER);
            System.out.println("Connecting to SQL server ...");
            conn1 = DriverManager.getConnection(URL, USER, PASS);
            st = conn1.createStatement();
            st.execute("DROP DATABASE IF EXISTS `use_server_prepared_statement_test`");
            rs = st.executeQuery("SHOW DATABASES");
            while (rs.next()) {
                String dbName = rs.getString(1);
                if (CATALOG.equals(dbName)) {
                    // we are good to go. Don't need to create DB.
                    System.out.println("Database " + CATALOG + " found");
                    dbExists = true;
                    break;
                }
            }
            if (!dbExists) {
                dbExists = BlobAsStringTest.createDb(conn1, CATALOG);
                st.execute("use " + CATALOG);
                // table with blob (mediumblob etc.) column type
                st.execute("CREATE TABLE `foo`( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `val_one` VARCHAR(10), `val_blob` blob, PRIMARY KEY (`id`) )");
                st.execute("INSERT INTO `foo`(`id`,`val_one`,`val_blob`) VALUES ( NULL,'" + VALUE_ONE + "','"
                        + VALUE_BLOB + "')");
            }
            System.out.println("---------------------------------------------");
            // test 1 - OK
            System.out.println("Test 1. URL: " + URL);
            conn1.setCatalog(CATALOG);
            pstm1 = conn1.prepareStatement(sql);
            pstm1.setString(1, VALUE_ONE);
            rs1 = pstm1.executeQuery();
            printDataTypes(rs1);
            if (rs1.next()) {
                printData(rs1);
                String blobString = rs1.getString("val_blob");
                compareValues(blobString);
            }
            System.out.println();
            System.out.println("---------------------------------------------");
            // - test 2 - Failed
            // let's test with setting useServerPrepStmts=true
            System.out.println("Test 1. URL: " + URL + USE_SERVER_PREP_STMTS);
            conn2 = DriverManager.getConnection(URL + USE_SERVER_PREP_STMTS, USER, PASS);
            conn2.setCatalog(CATALOG);
            pstm2 = conn2.prepareStatement(sql);
            pstm2.setString(1, VALUE_ONE);
            rs2 = pstm2.executeQuery();
            printDataTypes(rs2);
            if (rs2.next()) {
                printData(rs2);
                String blobString = rs2.getString("val_blob");
                compareValues(blobString);
                // looks like rs2.getBytes() returns byte[].toString() 
                byte[] bytes = rs2.getBytes("val_blob");
                System.out.println("getString(\"val_blob\") is returning byte[].toString(), for example, calling rs2.getBytes(\"val_blob\").toString() returns "
                        + bytes);
            }
            System.out.println("---------------------------------------------");
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            BlobAsStringTest.close(rs);
            BlobAsStringTest.close(st);
            BlobAsStringTest.close(rs1);
            BlobAsStringTest.close(rs2);
            BlobAsStringTest.close(pstm1);
            BlobAsStringTest.close(pstm2);
            BlobAsStringTest.close(conn1);
            BlobAsStringTest.close(conn2);
        }
    }

    private static void printData(ResultSet rs) throws SQLException {
        System.out.println("----Data----");
        System.out.println(rs.getString("id"));
        System.out.println(rs.getString("val_one"));
        java.sql.Blob blob = rs.getBlob("val_blob");
        String str = new String(blob.getBytes(1l, (int) blob.length()));
        System.out.println(str);
    }

    private static void printDataTypes(ResultSet rs) throws SQLException {
        System.out.println("----Data Type----");
        for (int i = 0; i < rs.getMetaData().getColumnCount(); i++) {
            System.out.print(rs.getMetaData().getColumnName(i + 1) + "(" + rs.getMetaData().getColumnTypeName(i + 1)
                    + ") | ");
        }
        System.out.println();
    }

    private static void compareValues(String blobString) {
        System.out.println("-----Compare values-----");
        System.out.println("getString(\"val_blob\") equals actual blob text value: "
                + (blobString.equals(VALUE_BLOB) ? "YES! :)" : "Oh no...  :(") + "\n    value returned: " + blobString);
        System.out.println();
    }

    public static void close(Statement st) {
        if (st != null) {
            try {
                st.close();
            } catch (SQLException e) {
            }
        }
    }

    public static void close(Connection conn) {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
            }
        }
    }

    public static void close(ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
            }
        }
    }

    /** create database and use it
     * @throws IOException */
    public static boolean createDb(Connection conn, String catalog) throws IOException {
        Statement st = null;
        try {
            st = conn.createStatement();
            st.execute("CREATE DATABASE `" + catalog + "`;");
            st.execute("USE `" + catalog + "`;");
            System.out.println("Database " + catalog + " created");
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } finally {
            close(st);
        }
    }
}
[7 Dec 2016 10:17] Chiranjeevi Battula
Hello Alexander,

Thank you for the bug report and test case.
Verified this behavior on MySQL Connector / J 5.1.40.

Thanks,
Chiranjeevi.
[7 Dec 2016 10:17] Chiranjeevi Battula
output:
mdEVifSLatPfO1FLKlSzW0wojTJdCdnPjEn5fiCEg6Rals316NVyqT8Un7WBLqaDWYVaOUF/MaKGSVFCekLAqy33LCGcJoTL
-----Compare values-----
getString("val_blob") equals actual blob text value: YES! :)
 value returned: mdEVifSLatPfO1FLKlSzW0wojTJdCdnPjEn5fiCEg6Rals316NVyqT8Un7WBLqaDWYVaOUF/MaKGSVFCekLAqy33LCGcJoTL

mdEVifSLatPfO1FLKlSzW0wojTJdCdnPjEn5fiCEg6Rals316NVyqT8Un7WBLqaDWYVaOUF/MaKGSVFCekLAqy33LCGcJoTL
-----Compare values-----
getString("val_blob") equals actual blob text value: Oh no...  :(
    value returned: [B@5cb0d902
[30 Jan 12:38] Martin Gubala
I have encountered the same problem on CentOS 6.3 with Java 1.6 and 1.8.

There's a workaround that seems to work in my case - the JDBC driver option blobsAreStrings set to true. Surprisingly, it handles even binary BLOBs correctly.