MySQL Bugs: #20491: Illegal codepage usage while DatabaseMetaData.getColumns()

Bug #20491	Illegal codepage usage while DatabaseMetaData.getColumns()
Submitted:	15 Jun 2006 21:53	Modified:	3 Dec 2007 7:20
Reporter:	Thomas Breitkreuz	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	5.0.22	OS:	Windows (Windows XP, Linux)
Assigned to:	Mark Matthews	CPU Architecture:	Any
Tags:	bfsm_2007_03_01, bfsm_2007_10_18

Description:
If a table column name contains German umlauts, the method DatabaseMetaData.getColumns() returns a ResultSet that contains unusable column names.

We could reproduce this only in Windows systems. Linux, Solaris and AIX works well. Older drivers than 3.1 works also in Windows well. If the meta data of a ResultSet is used, the column names are correct with all driver versions.

The attached test program shows only the output to the console, but the incorrect column names are also unusable in Java itself.

How to repeat:
Plattform: Windows XP
Database: MySQL 4.1 (ANSI mode)
Database and test program at the same machine

-------------------------------------------------------
Create Test Table
-------------------------------------------------------
CREATE TABLE  "tst" (
  "field1_ae_ä" int(10) unsigned NOT NULL default '0',
  "field2_ue_ü" varchar(45) default '',
  "field3_oe_ö" varchar(45) default '',
  "field4_sz_ß" varchar(45) default '',
  PRIMARY KEY  ("field1_ae_ä")
);

-------------------------------------------------------
Test Output
-------------------------------------------------------
Output of the test program with JDBC-Driver (3.0.11-stable):
INFO: driver version 3.0
reading meta data for table 'tst'
field name:field1_ae_ä
field name:field2_ue_ü
field name:field3_oe_ö
field name:field4_sz_ß
reading column names for table 'tst'
field name:field1_ae_ä
field name:field2_ue_ü
field name:field3_oe_ö
field name:field4_sz_ß

Output of the test program with JDBC-Driver  (3.1.12):
INFO: driver version 3.1
reading meta data for table 'tst'
field name:field1_ae_Ã€
field name:field2_ue_ÃŒ
field name:field3_oe_Ã¶
field name:field4_sz_Ã?
reading column names for table 'tst'
field name:field1_ae_ä
field name:field2_ue_ü
field name:field3_oe_ö
field name:field4_sz_ß

--------------------------------------------------------------------
Test Program
--------------------------------------------------------------------
public class MySQLMetaDataTest {

static final String CONNECTION_DRIVER = "com.mysql.jdbc.Driver";
static final String CONNECTION_URL = "jdbc:mysql://localhost/validate";
static final String CONNECTION_USR = "root";
static final String CONNECTION_PWD = "";

static final String TABLE_NAME = "tst";

   static void msg(String msg) {
      System.out.println(msg);
   }
   static void msg(String msg, Exception e) {
      System.out.println(msg);
      System.out.println("Exception: " + e);
   }
   
   public static void main(String argv[]) {
      java.sql.Connection c = null;
      
      if (getDriver() == null) {
         msg("Driver not found - correct classpath");
         System.exit(-1);
      }
      
      c = getConnection();
      
      if (c == null) {
         msg("can't get connection");
         System.exit(-2);
      }
      msg("reading meta data for table 'tst'");
      try {
         printMetaData(c);
      } catch (java.sql.SQLException e) {
         msg("ERROR: ???.", e);
         System.exit(-5);
      }
   
      msg("reading column names for table 'tst'");
      try {
         printResultSetMetaData(c);
      } catch (java.sql.SQLException e) {
         msg("ERROR: ???.", e);
         System.exit(-6);
      }
      System.exit(0);
   }
   
   
   static java.sql.Driver getDriver() {
   
      if (CONNECTION_DRIVER != null) {
         try {
            Class.forName(CONNECTION_DRIVER);
         } catch (java.lang.ClassNotFoundException e) {
            msg("ERROR: can't load class for driver: " + CONNECTION_DRIVER, e);
         }
      }
   
      java.sql.Driver result = null;
   
      if (CONNECTION_URL != null) {
         try {
            result = java.sql.DriverManager.getDriver(CONNECTION_URL);
            msg("INFO: driver version " + result.getMajorVersion() + "." + result.getMinorVersion());
         } catch (java.sql.SQLException e) {
            msg("ERROR: Unable to find driver among the registered drivers.", e);
         }
      } else {
         msg("WARN: database url is not set!");
      }
   
      return result;
   }
   
   static java.sql.Connection getConnection() {
      java.sql.Connection c = null;
   
      try {
         if (CONNECTION_USR == null) {
         c = java.sql.DriverManager.getConnection(CONNECTION_URL);
      } else {
         c = java.sql.DriverManager.getConnection(CONNECTION_URL, CONNECTION_USR, CONNECTION_PWD);
      }
      
      
      } catch (java.sql.SQLException e) {
         msg("ERROR: Unable to establish a connection through the driver manager.", e);
         return null;
      }
      
      return c;
   }
   
   
   
   
   static void printMetaData(java.sql.Connection c) throws java.sql.SQLException {
      java.sql.DatabaseMetaData md = c.getMetaData();
      
      if (md == null) {
         msg("ERROR: can't get database meta data!");
         System.exit(-3);
      }
      
      java.sql.ResultSet rs = md.getColumns(null, "%", TABLE_NAME, "%");
      
      if (rs == null) {
         msg("ERROR: meta data result set is 'null'!");
         System.exit(-4);
      }
      
      if (rs.next()) {
         do {
            msg("field name:" + rs.getString(4));
         } while (rs.next());
      } else {
         msg("ERROR: meta data result set is EMPTY!");
      }
      
   }

   static void printResultSetMetaData(java.sql.Connection c) throws java.sql.SQLException {
      java.sql.Statement s = c.createStatement();
      
      if (s == null) {
         msg("ERROR: can't statement!");
         System.exit(-3);
      }
      
      java.sql.ResultSet rs = s.executeQuery("select * from " + TABLE_NAME);
   
      if (rs == null) {
         msg("ERROR: result set is 'null'!");
         System.exit(-4);
      }
      
      java.sql.ResultSetMetaData rsmd = rs.getMetaData();
      
      if (rsmd == null) {
         msg("ERROR: result set meta data is 'null'!");
         System.exit(-6);
      }

      for (int i=1; i <= rsmd.getColumnCount(); i++) {
            msg("field name:" + rsmd.getColumnName(i));
      }
      
   }
   
}

I have to correct my previous submission. We have also the effect with an Ubuntu Linux 6.06.

Seems to be an issue with the server. If one does a 

"SET CHARACTER_SET_RESULTS=null" as the JDBC driver does, one gets the garbled data in the MySQL command-line client as well:

mysql> set CHARACTER_SET_RESULTS=null;
Query OK, 0 rows affected (0.00 sec)

mysql> show full columns from testBug20491\G
*************************** 1. row ***************************
     Field: field1_ae_Ã¤
      Type: int(10) unsigned
 Collation: NULL
      Null: NO
       Key: PRI
   Default: 0
     Extra:
Privileges: select,insert,update,references
   Comment:
*************************** 2. row ***************************
     Field: field2_ue_Ã¼
      Type: varchar(45)
 Collation: latin1_swedish_ci
      Null: YES
       Key:
   Default:
     Extra:
Privileges: select,insert,update,references
   Comment:
*************************** 3. row ***************************
     Field: field3_oe_Ã¶
      Type: varchar(45)
 Collation: latin1_swedish_ci
      Null: YES
       Key:
   Default:
     Extra:
Privileges: select,insert,update,references
   Comment:
*************************** 4. row ***************************
     Field: field4_sz_Ã
      Type: varchar(45)
 Collation: latin1_swedish_ci
      Null: YES
       Key:
   Default:
     Extra:
Privileges: select,insert,update,references
   Comment:
4 rows in set (0.01 sec)

mysql>

I've got the same result as presented in comment from  Mark Matthews on [16 Jun 0:27]. See private discussion with Mark on why this is, likely, a server bug. 

If it is intended behaviour (as I still think), it has to be explicitely described with detailed examples.

Any new infos on this bug? Is it possible to fix it temporarily in the JDBC driver?

Bye Thomas

It seems to be no progress on this issue.

Is there any usable workaroud available? If not we are locked to MySQL 4.X and the 3.0.11 JDBC driver (the newest combination known as good)

We use MySQL as the default database for our product and yes we pay license fees to MySQL Germany.

Thomas,

We had already escalated this bug report, but if you are a customer, please, open support issue about it to get workarounds if possible in the meantime.

I can't repeat it with the latest 5.0.
What am I doing wrong?

mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE  "tst" (
    ->   "field1_ae_ä" int(10) unsigned NOT NULL default '0',
    ->   "field2_ue_ü" varchar(45) default '',
    ->   "field3_oe_ö" varchar(45) default '',
    ->   "field4_sz_ß" varchar(45) default '',
    ->   PRIMARY KEY  ("field1_ae_ä")
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> set character_set_results=null;
Query OK, 0 rows affected (0.00 sec)

mysql> show full columns from tst\G
*************************** 1. row ***************************
     Field: field1_ae_ä
      Type: int(10) unsigned
 Collation: NULL
      Null: NO
       Key: PRI
   Default: 0
     Extra: 
Privileges: select,insert,update,references
   Comment: 
*************************** 2. row ***************************
     Field: field2_ue_ü
      Type: varchar(45)
 Collation: latin1_swedish_ci
      Null: YES
       Key: 
   Default: 
     Extra: 
Privileges: select,insert,update,references
   Comment: 
*************************** 3. row ***************************
     Field: field3_oe_ö
      Type: varchar(45)
 Collation: latin1_swedish_ci
      Null: YES
       Key: 
   Default: 
     Extra: 
Privileges: select,insert,update,references
   Comment: 
*************************** 4. row ***************************
     Field: field4_sz_ß
      Type: varchar(45)
 Collation: latin1_swedish_ci
      Null: YES
       Key: 
   Default: 
     Extra: 
Privileges: select,insert,update,references
   Comment: 
4 rows in set (0.33 sec)

Perhaps this bug was fixed along with the fix for Bug#10491.
I'd be glad to accept a C test case for this problem into the test suite.

mysql> select version();
+---------------------------+
| version()                 |
+---------------------------+
| 5.0.48-valgrind-max-debug | 
+---------------------------+
1 row in set (0.00 sec)

Hi,

First
Thanks that someone works on this issue

Second
The original report is for the JDBC driver (not the C API). We have retestet it with: 
  MySQL: 5.0.45-community-nt
  JDBC: mysql-connector-java-5.0.7
With this combination it does not work.

Third
> mysql> select version();
> +---------------------------+
> | version()                 |
> +---------------------------+
> | 5.0.48-valgrind-max-debug | 
> +---------------------------+
This version is not accessable for us. If this version is released, we will retest this issue. If you tell us where we can find a downloadable (Windows) version of MySQL, that is fixed, we will retest this version. But we can deploy only GA versions to our customers.

Bye Thomas

Thomas, as explained here and in Bug#10491 (of which I believe this is a duplicate) this problem is *not* on connectors side. Please review posts in both reports carefully.

Tonci,

it is not in my competence to detect where the error is. What I can do is testing whether the bug is gone or not - and this only with the JDBC connector.

If you release the 5.0.48 I will retest this issue.

Bye Thomas

Thomas, we are actively working on clarifying this problem... The report is the longest one in bugs db I believe as there are a lot of private comments from MySQL staff. When we reach the conclusion, you'll be informed.

Thanks for your interest in MySQL.

At this time I have no alternative but set this to Verified in c/J (with test case attached) and let Mark rule.

Env:
 - Server version: 5.0.48-pb1030-log MySQL Pushbuild Edition, build 1030 on WinXP Pro SP2 localhost
 - JDK 1.5.0_11

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/36176

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/36184

Hi,

I test the patched 5.1.6 JConnector driver against the GA comunity server 5.0.45.

Database: 5.0.45-community-nt
JConnector: mysql-connector-java-5.1.6 ( Revision: 6651 )

The patch itself works. The german umlauts are available as aspected.

BUT

Now the method ResultSetMetaData.getColumnDisplaySize(int column) does not work anymore in streaming mode. The exception:

---------------- Snip -----------------

INFO: java.sql.SQLException: Streaming result set
com.mysql.jdbc.RowDataDynamic@1bbd23f is still active. No statements may
be issued when any streaming result sets are open and in use on a given
connection. Ensure that you have called .close() on any active streaming
result sets before attempting more queries.

INFO: at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:931)

INFO: at
com.mysql.jdbc.MysqlIO.checkForOutstandingStreamingData(MysqlIO.java:2530)

INFO: at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1760)

INFO: at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1973)

INFO: at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2538)

INFO: at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2467)

INFO: at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1384)

INFO: at
com.mysql.jdbc.ConnectionImpl.getMaxBytesPerChar(ConnectionImpl.java:2940)

INFO: at com.mysql.jdbc.Field.getMaxBytesPerCharacter(Field.java:582)

INFO: at com.mysql.jdbc.ResultSetMetaData.getColumnDisplaySize(ResultSetMetaData.java:211)

---------------- Snap -----------------

is thrown. It seems, that the patched version executes an addidtional query to retrieve the requested metadata.

Because of the large number of rows we have to process, we need the streaming mode. It is possible, that also other meta data methods does not work.

Bye Thomas

Thomas,

That's an unrelated issue, caused by changes in the version you upgraded to.

The workaround is to add "useDynamicCharsetInfo=false" to your configuration properties, which will use hard-coded values for this information, rather than querying the database. This will only be an issue if you've installed custom character sets, which would then require useDynamicCharsetInfo to be set to "true".

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/38019

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/38024

A note has been added to the 5.1.6 changelog: 

DatabaseMetadata.getColumns() doesn't return the correct column names if the connection character isn't UTF-8. A bug in MySQL server compounded the issue, but was fixed within the MySQL 5.0 release cycle. The fix includes changes to all the sections of the code that access the server metadata.