MySQL Bugs: #28567: can not use chinese in store procedure

Bug #28567	can not use chinese in store procedure
Submitted:	21 May 2007 16:39	Modified:	10 Jul 2007 12:06
Reporter:	Cao GP	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Charsets	Severity:	S3 (Non-critical)
Version:	5.0.4 & 5.0.6	OS:	Linux (ubuntu 7.04)
Assigned to:		CPU Architecture:	Any

Description:
My environments:
    -- os:ubuntu 7.04
    -- JDK 1.6.0 Update 1
    -- MySQL Connector/J 5.0.4 & 5.0.6
    -- MySQL Server 5.0.38-Ubuntu_0ubuntu1-log

How to repeat:
I create a table and a store procedure:

CREATE TABLE  `types` (
  `ID` int(11) NOT NULL,
  `NAME` varchar(80) NOT NULL,
  PRIMARY KEY  (`ID`),
  UNIQUE KEY `UQ_TYPES_NAME` (`NAME`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312

DELIMITER $$

DROP PROCEDURE IF EXISTS `SP_TYPES_INSERT$$
CREATE PROCEDURE `SP_TYPES_INSERT` (
P_ID INT,
P_NAME VARCHAR(80)
)
BEGIN
INSERT INTO types (`ID`, NAME) VALUES (P_ID, P_NAME);
END$$

DELIMITER ;

when I run  the program below:
            Class.forName("com.mysql.jdbc.Driver");
            Connection cnn=DriverManager.getConnection ("jdbc:mysql://localhost/ph?useUnicode=true&characterEncoding=gb2312", "root", "");

            CallableStatement cmd=cnn.prepareCall("CALL SP_TYPES_INSERT(?, ?)");
            cmd.setInt(1, 1);
            cmd.setString(2, "中文");
            cmd.execute();

            cmd.close();
            cnn.close();

an exception was been thrown:
java.sql.SQLException: Incorrect string value: '\xD6\xD0\xCE\xC4' for column 'P_NAME' at row 1
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:946)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2870)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1573)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665)
        at com.mysql.jdbc.Connection.execSQL(Connection.java:3176)
        at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1153)
        at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:794)
        at com.mysql.jdbc.CallableStatement.execute(CallableStatement.java:752)
        at application1.Main.main(Main.java:35)

Hi and thanks for your report.

Verified as described on:
 - MySQL server 5.0.38BK on WinXP Pro Sp2 localhost
 - latest c/J 5.0 sources
 - JDK 1.5.0_11b
There was 1 error:
1) testBug28567(testsuite.simple.TestBug28567)java.sql.SQLException: Unsupported character encoding 'GB2312'.

On my system, asObject is always CHARSET_CONVERTER_NOT_AVAILABLE_MARKER:
Connection.java 3458
	if (this.usePlatformCharsetConverters) {
		return null; // we'll use Java's built-in routines for this
		             // they're finally fast enough
	}
	SingleByteCharsetConverter converter = null;
	synchronized (this.charsetConverterMap) {
		Object asObject = this.charsetConverterMap.get(javaEncodingName);

		if (asObject == CHARSET_CONVERTER_NOT_AVAILABLE_MARKER) {
			return null;
		}

Maybe related to Bug#27867.

Test case

Attachment: TestBug28567.java (text/x-java), 2.46 KiB.

Tonci,

Getting CHARSET_CONVERTER_NOT_AVAILABLE_MARKER == null is not a bug. It means that the driver has determined that we can't support the character encoding with our own more performant routines (which only work for single-byte character encodings), and instead the driver will use the multi-byte encoders built into the JDK.

Here's what the driver's sending to the server, which should work:

SHOW VARIABLES;
SHOW COLLATION;
SET NAMES gb2312;
SET character_set_results = NULL;

...

SHOW CREATE PROCEDURE "test"."SP_BUG28567";
CALL SP_BUG28567(1,'中文')

(here's the CALL dumped as a wire-level packet):

1b 00 00 00 03 43 41 4c     . . . . . C A L 
4c 20 53 50 5f 42 55 47     L . S P _ B U G 
32 38 35 36 37 28 31 2c     2 8 5 6 7 ( 1 , 
27 d6 d0 ce c4 27 29        ' . . . . ' ) 

As far as I know, the byte sequence 0xd6 0xd0 0xce 0xc4 is a sequence of two valid GB2312 characters.

I'll have to have the character set experts take a look at this one.

It appears this works fine if you fully specify the character encoding for the parameter in question, e.g:

(IN P_ID INT, IN P_NAME VARCHAR(80) character set gb2312)

We're wondering if your default character set isn't gb2312, and thus the parameter is picking up the character encoding from the database or server.

Could you tell us what "SHOW CREATE PROCEDURE" for the stored procedure in question returns?

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".

According to the manual (http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html):

"For character data types, if there is a CHARACTER SET clause in the declaration, the specified character set and its default collation are used. If there is no such clause, the database character set and collation that are in effect at the time the routine is created are used. (These are given by the values of the character_set_database and collation_database system variables.) The COLLATE attribute is not supported. (This includes use of BINARY, because in this context BINARY specifies the binary collation of the character set.)"

If there is a test case that proves something different than above, send it.