Bug #56766 Fetching metadata from I__S broken for Shift_JIS

• Submitted: 14 Sep 2010 9:18
• Modified: 13 Oct 2010 12:55
• Reporter: Tonci Grgin
• Status: Not a Bug
• Category: Connector / J
• Severity: S2 (Serious)
• Version: trunk
• OS: Any
• CPU Architecture: Any

[14 Sep 2010 9:18] Tonci Grgin

Description:
Fetching metadata from I__S seems broken for SJIS.

How to repeat:
    String url = "jdbc:mysql://localhost/test";
    Properties props = new Properties();
    props.setProperty("user", "**");
    props.setProperty("password", "**");
    props.setProperty("useInformationSchema", "true");
    props.setProperty("characterEncoding", "Shift_JIS");

    Connection conn;
    ResultSet rs;
    Statement st;
    com.mysql.jdbc.ResultSetMetaData md;

    try {
      Class.forName("com.mysql.jdbc.Driver");
      conn = DriverManager.getConnection(url, props);

      st = conn.createStatement();
      st.execute("drop table if exists tbl1");
      st.execute("create table tbl1 (`カラム` int, `表列` int)");

      rs = st.executeQuery("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='tbl1'");
      while(rs.next())
      {
        System.out.println("column1: " + rs.getString(1));
      }

      rs = st.executeQuery("SHOW VARIABLES LIKE 'char%'");
      while(rs.next())
      {
        System.out.println(rs.getString("Variable_name") + "\t: " + rs.getString("Value"));
      }

      DatabaseMetaData dbmd = conn.getMetaData();
      int ordinalPosOfCol2Full = 0;
      rs = dbmd.getColumns(conn.getCatalog(), null, "tbl1", null);
      while (rs.next()) {
        String s = new String(rs.getString(4).getBytes("Shift_JIS"), "UTF-8");
        System.out.println(s + "\t: " + rs.getInt(17));
      }

      rs = st.executeQuery("SELECT 'testing' AS t");
      md = (com.mysql.jdbc.ResultSetMetaData) rs.getMetaData();
      int bytesPerChar = ((com.mysql.jdbc.MySQLConnection)conn).getMaxBytesPerChar(md.getColumnCharacterSet(1));
      System.out.println("Characters: " + md.getColumnDisplaySize(1));
      System.out.println("Bytes per character: " + bytesPerChar);
      System.out.println("Character set: " + md.getColumnCharacterSet(1));
      System.out.println("Bytes:  " + md.getColumnDisplaySize(1) * bytesPerChar);
    } catch (Exception e) {
      e.printStackTrace();
    }

outputs:
Result for the attachment #4. Looks the result of dbmd.getColumns() is broken.

column1: カラム
column1: 表列
character_set_client : sjis
character_set_connection : sjis
character_set_database : utf8
character_set_filesystem : binary
character_set_results :
character_set_server : latin1
character_set_system : utf8
character_sets_dir : /usr/share/mysql/charsets/
カラ�? : 1
表�? : 2
Characters: 7
Bytes per character: 2
Character set: Shift_JIS
Bytes: 14

Suggested fix:
-

[14 Sep 2010 10:05] Tonci Grgin

Being that simple SELECT FROM I__S table produces good result on same environment, I'm marking this as Verified.

Workaround:
      st.execute("drop table if exists tbl1");
      st.execute("create table tbl1 (`カラム` int, `表列` int)");
      rs = st.executeQuery("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='tbl1'");
      while(rs.next())
...

[13 Oct 2010 12:55] Tonci Grgin

This is not a bug as when character_set_results=NULL then server returns all metadata in utf8, as documented.