MySQL Bugs: #24840: characterEncoding=US-ASCII no longer works

Bug #24840	characterEncoding=US-ASCII no longer works
Submitted:	5 Dec 2006 22:45	Modified:	22 Feb 2007 14:29
Reporter:	Razvan Surdulescu	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	3.1.14, 5.0.4	OS:	Linux (Linux RedHat)
Assigned to:		CPU Architecture:	Any

Description:
Until recently, I've been using MySQL Connector/J version 3.1.6 to connect to MySQL 5.0.18. The JDBC URL I've been using has a "&characterEncoding=US-ASCII" hint in it. This has been working fine.

Since I upgraded to 3.1.14, the application breaks with the following exception:

Exception in thread "main" java.sql.SQLException: Unknown character set: 'usa7'

If I remove the "&characterEncoding=US-ASCII" from the JDBC url, the code runs, but the strings it returns are mangled. It seems something changed in the JDBC driver with respect to character encoding in Java.

How to repeat:
import java.sql.*;

public class Test {
  public static void main(String[] args) throws Exception {
    Class.forName("org.gjt.mm.mysql.Driver");
    Connection conn = DriverManager.getConnection(
      "jdbc:mysql://[server]/[db]?user=[user]&password=[password]&characterEncoding=US-ASCII");
    Statement stmt = conn.createStatement();

    ResultSet res = stmt.executeQuery("SELET ...");
    while(res.next()) {
        // do stuff
    }
  }
}

With Connector/J 3.1.6, the code works fine. With Connector/J 3.1.14, I get the following exception:

Exception in thread "main" java.sql.SQLException: Unknown character set: 'usa7'
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2975)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1600)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1695)
        at com.mysql.jdbc.Connection.execSQL(Connection.java:3020)
        at com.mysql.jdbc.Connection.configureClientCharacterSet(Connection.java:2322)
        at com.mysql.jdbc.Connection.initializePropsFromServer(Connection.java:3748)
        at com.mysql.jdbc.Connection.createNewIO(Connection.java:2585)
        at com.mysql.jdbc.Connection.<init>(Connection.java:1485)
        at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:266)
        at java.sql.DriverManager.getConnection(DriverManager.java:525)
        at java.sql.DriverManager.getConnection(DriverManager.java:193)
        at Test.main(Test.java:8)

Suggested fix:
I looked through the Connector/J source, and it seems that the Java character mapping "US-ASCII" used to be mapped to "binary" back in 3.1.6; since then, it's been mapped to "usa7", which I guess the server doesn't understand?

usa7 was deprecated back in 2003, in favor of 'ascii'. 

The current mapping puts usa7 in front, probably it needs version hints for 'ascii':

                        "US-ASCII =                     usa7,"
                        + "US-ASCII =                   ascii,"

Backtrace of my characterEncoding=US-ASCII connection:

Error message: Unknown character set: 'usa7'
com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Unknown character set: 'usa7'
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
        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:3170)
        at com.mysql.jdbc.Connection.configureClientCharacterSet(Connection.java:2416)
        at com.mysql.jdbc.Connection.initializePropsFromServer(Connection.java:3913)
        at com.mysql.jdbc.Connection.createNewIO(Connection.java:2683)
        at com.mysql.jdbc.Connection.<init>(Connection.java:1531)
        at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:266)
        at java.sql.DriverManager.getConnection(DriverManager.java:525)
        at java.sql.DriverManager.getConnection(DriverManager.java:171)
        at mytest.main(mytest.java:26)

Thanks for the fast follow-up! What do you mean by "version hints
for 'ascii'"?

Razvan, just a tip in documentation, nothing more...

I understand, thanks. As far as the bug report is concerned, it sounds like this is a real bug? I find it strange that giving the US-ASCII hint on the JDBC URL would throw an exception from inside Connector/J ... regardless of whether the documentation is correct or not.

Razvan, it's a bug in connector, as Domas verified. But this also needs a note in docs. So we have two problems here.

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/16622

A note has been made in the 3.1.15 and 5.0.5 changelogs.