MySQL Bugs: #38881: stored procedure with blob as INOUT type return wrong while charset not latin1

Bug #38881	stored procedure with blob as INOUT type return wrong while charset not latin1
Submitted:	19 Aug 2008 4:41	Modified:	24 Feb 2022 13:49
Reporter:	Yiping Wang	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	mysql-connector-java-5.1.6-bin.jar	OS:	Any
Assigned to:	Alexander Soklakov	CPU Architecture:	Any
Tags:	BLOB, charset, INOUT, stored procedure

Description:
MySQL use default character set "latin1", when we change it to "GBK" or "UTF-8". And we call a store process which constains a blob, and its type is "INOUT", when the store process is
finished, we check the result, the value in blob column is not right.

MySQL version is:  5.0.51b-community-nt
MySQL jdbc driver version is : mysql-connector-java-5.1.6-bin.jar

How to repeat:
1. create table
create table testblob (int_col integer, blob_col blob)
2. create stored procedure

create PROCEDURE spp (IN INT_P INTEGER, INOUT BLOB_P BLOB)

P1:  BEGIN INSERT INTO testblob(INT_COL, BLOB_COL) values (INT_P, BLOB_P);

SELECT BLOB_COL INTO BLOB_P FROM testblob WHERE INT_COL = INT_P;

END P1

run the follwoing java client.

import java.sql.CallableStatement;
import java.sql.Connection;
import com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource;

public class MySQLTest {
	public static void main(String[] args) throws Exception {
	    callProc();
	}	
	public static Connection getConnection() { 
    	MysqlConnectionPoolDataSource ds = new MysqlConnectionPoolDataSource();
        ds.setServerName("localhost");
        ds.setPortNumber(3306);
        ds.setDatabaseName("vtba");
        ds.setUser("root");
        ds.setPassword("12345678");
        System.out.println("URL:" + ds.getURL());
        try {
        	Connection conn = ds.getConnection();
        	System.out.println("Get connection: " + conn.toString());
        	return conn;
        	}catch (Exception e) {
        		e.printStackTrace();
        		return null;
        }
	}	
	public static void callProc() {
    	try{
    	    String sql = "{call spp(?,?)}";
            Connection conn = getConnection();
            CallableStatement ps = conn.prepareCall(sql);
    	    String txt = "ABC";
    	    byte[] b = txt.getBytes();
    	    System.out.print("The expected: ");
    	    printBytes(b);
    		ps.setInt(1, 1);
    		ps.setBytes(2, txt.getBytes());
    		ps.registerOutParameter(2, java.sql.Types.BLOB);    		
    		ps.execute();   		
    		System.out.print("The real: ");
    		byte[] o = ps.getBytes(2);
    		printBytes(o);
    	}catch(Exception e) {
    		e.printStackTrace();
    	}
    }	
	private static void printBytes(byte[] b) {
	    for (byte bb: b){
	        System.out.print(bb);
	        System.out.print(",");
	    }
	    System.out.println();
	}
}

the result is :

The expected: 65,66,67,
The real: 120,39,52,49,52,50,52,51,39,

Suggested fix:
from the byte value it looks like, while iserting a byte array, the intern implementation have to a tranformation to byte[] again, then make the fault.

such as:
I want to insert "ABC", then I transform it to [65, 66, 67], and call setBytes,
but looks the intern Impl receive the byte[] and do another transformation too.

some material to reprodure the bug

Attachment: material.zip (application/x-zip-compressed, text), 62.14 KiB.

add one comment, it also can be reproduced with VARBINARY or BINARY type.

Output parameters are problematic with MySQL, in that there isn't direct support for them. The JDBC driver needs to use user variables to hold their value, and these have casting and conversion artifacts.

If you need to return BLOBs from stored procedures, return them as result sets, i.e. just "SELECT BLOB_COL FROM testblob WHERE INT_COL = INT_P;" and access the result set as you would with any other statement.

Please try the suggestion done by Mark in his last comment. Thanks.

Hi All,
Thanks for your quick response. yes it can return valid value in resultset, but what I concern is the issue only occurs in "INOUT" type and it has not related to if or not the stored procedure has a select script.
please look the below stored procedure:

create PROCEDURE spp (IN INT_P INTEGER, inout BLOB_P varbinary(20))
P1:  BEGIN INSERT INTO testblob(INT_COL, BLOB_COL) values (INT_P, BLOB_P);
END P1

the blob_p is inout type, I just insert a row in the table. the issue still can be reproduced.

I have another try for blob, use the below sp.
create PROCEDURE spp (IN INT_P INTEGER, out BLOB_P varbinary(20))
P1:  BEGIN
--SELECT BLOB_COL into blob_p FROM testblob WHERE INT_COL = INT_P;
END P1

insert a row before call the sp, I can get the right value.

sorry, the second sp should be:
create PROCEDURE spp (IN INT_P INTEGER, out BLOB_P varbinary(20))
P1:  BEGIN
SELECT BLOB_COL into blob_p FROM testblob WHERE INT_COL = INT_P;
END P1

and the point is this issue doesn't exist if charset is latin1. but appears in GBK or UTF8

Yiping, please retest with special attention to other unicode support properties in connection string:

Property Name Definition Default Value Since Version

useUnicode Should the driver use Unicode character encodings when handling
strings? Should only be used when the driver can't determine
the character set mapping, or you are trying to 'force' the
driver to use a character set that MySQL either doesn't natively
support (such as UTF-8), true/false, defaults to 'true'
true 1.1g

characterEncoding If 'useUnicode' is set to true, what character encoding should the
driver use when dealing with strings? (defaults is to 'autodetect')
1.1g

characterSetResults Character set to tell the server to return results as. 3.0.13

connectionCollation If set, tells the server to use this collation via 'set collation_
connection'
3.0.13

useBlobToStoreUTF8OutsideBMP Tells the driver to treat [MEDIUM/LONG]BLOB columns as
[LONG]VARCHAR columns holding text encoded in UTF-8
that has characters outside the BMP (4-byte encodings), which
MySQL server can't handle natively.
false 5.1.3

utf8OutsideBmpExcludedColumnNamePattern
When "useBlobToStoreUTF8OutsideBMP" is set to "true",
column names matching the given regex will still be treated as
BLOBs unless they match the regex specified for
"utf8OutsideBmpIncludedColumnNamePattern". The regex
must follow the patterns used for the java.util.regex package.
5.1.3

utf8OutsideBmpIncludedColumnNamePattern
Used to specify exclusion rules to
"utf8OutsideBmpExcludedColumnNamePattern". The regex
must follow the patterns used for the java.util.regex package.
5.1.3

and so on. Inform me of result. By definition BLOB is "binary" which means "charsetless" so this problem really confuses me.
Please paste output from mysql command line client of: SHOW VARIABLES LIKE "%charset%";
Please paste output from mysql command line client of: SHOW CREATE TABLE testblob; (I want to see something like ") ENGINE=MyISAM DEFAULT CHARSET=utf8 |").

My best guess, for now, is that your table is actually latin1 and you get UTF bytes converted to latin1 producing double the output.

Hi Tonci,

I am sorry I am just back from vocation. below is the information of the table.

-------------------------------------------------
mysql> show variables like "%charset%";
Empty set (0.00 sec)

mysql> show create table testblob;
+----------+----------------+
| Table    | Create Table
                                                |
----------------------------+
| testblob | CREATE TABLE `testblob` (
  `int_col` int(11) default NULL,
  `blob_col` blob
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
----------------------------+

I am sorry, I don't fully understand which property you want me to try. I just try the UseUnicode, not matter true or false will introdece same wrong result. could you be more soecific? thank you very much.

I am confused that why the out type for blob can get right value in sp, but inout can't.

I am realy

Yiping sorry. Please go to database with this table (USE my_database) and then do:
SHOW VARIABLES LIKE "%char%";

Paste the output here.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".