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:Microsoft Windows (Windows XP, Linux)
Assigned to: Mark Matthews CPU Architecture:Any
Tags: bfsm_2007_03_01, bfsm_2007_10_18

[15 Jun 2006 21:53] Thomas Breitkreuz
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));
      }
      
   }
   
}
[15 Jun 2006 22:02] Thomas Breitkreuz
I have to correct my previous submission. We have also the effect with an Ubuntu Linux 6.06.
[15 Jun 2006 22:27] Mark Matthews
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>
[20 Jun 2006 10:00] Valeriy Kravchuk
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.
[12 Dec 2006 14:01] Thomas Breitkreuz
Any new infos on this bug? Is it possible to fix it temporarily in the JDBC driver?

Bye Thomas
[23 Feb 2007 17:42] Thomas Breitkreuz
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.
[23 Feb 2007 18:20] Valeriy Kravchuk
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.
[31 Jul 2007 20:19] Konstantin Osipov
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.
[31 Jul 2007 20:21] Konstantin Osipov
mysql> select version();
+---------------------------+
| version()                 |
+---------------------------+
| 5.0.48-valgrind-max-debug | 
+---------------------------+
1 row in set (0.00 sec)
[1 Aug 2007 7:48] Thomas Breitkreuz
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
[1 Aug 2007 10:18] Tonci Grgin
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.
[1 Aug 2007 11:17] Thomas Breitkreuz
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
[8 Aug 2007 11:31] Tonci Grgin
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.
[13 Aug 2007 8:37] Tonci Grgin
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
[23 Oct 2007 17:47] Bugs System
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
[23 Oct 2007 18:01] Bugs System
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
[30 Oct 2007 13:06] Thomas Breitkreuz
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
[30 Oct 2007 13:32] Mark Matthews
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".
[19 Nov 2007 0:57] Bugs System
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
[19 Nov 2007 2:52] Bugs System
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
[3 Dec 2007 7:20] MC Brown
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.