MySQL Bugs: #63456: MetaData precision is different when using UTF8 or Latin1 tables

Bug #63456	MetaData precision is different when using UTF8 or Latin1 tables
Submitted:	28 Nov 2011 15:36	Modified:	22 Mar 2012 19:46
Reporter:	Adrien Bonnin	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	5.5.18	OS:	Windows (Win64)
Assigned to:	John Russell	CPU Architecture:	Any

Description:
When you use an UTF 8 table, the precision of the ResultSetMetaData isn't the same  precision that has been used in the CREATE TABLE.

How to repeat:
MySQL version : 5.5.18
Connector version : 5.1.18

SQL script : 
------------ 

create table table_latin ( 
TEST VARCHAR(10) 
) DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci; 

create table table_utf8 ( 
TEST VARCHAR(10) 
) DEFAULT CHARACTER SET utf8 COLLATE utf8_bin; 

MySQLTest.java : 
---------------- 

import java.sql.ResultSet; 
import java.sql.ResultSetMetaData; 
import java.sql.SQLException; 
import java.sql.Statement; 
import java.util.HashMap; 
import java.util.Map; 

import com.mysql.jdbc.Connection; 
import com.mysql.jdbc.jdbc2.optional.MysqlDataSource; 

public class MySQLTest { 

	/** 
	 * @param args 
	 */ 
	public static void main(String[] args) { 

		MysqlDataSource dataSource = new MysqlDataSource(); 
		dataSource.setUser("username"); 
		dataSource.setPassword("password"); 
		dataSource.setURL("jdbc:mysql://localhost:3306/test"); 

		Connection connection = null; 
		Statement statement = null; 

		ResultSet rsLatin = null; 
		ResultSet rsUtf8 = null; 

		try { 
			connection = (Connection)dataSource.getConnection(); 
			statement = connection.createStatement(); 

			rsLatin = statement.executeQuery("select * from table_utf8"); 
			printMetaData(rsLatin.getMetaData()); 

			rsUtf8 = statement.executeQuery("select * from table_latin"); 
			printMetaData(rsUtf8.getMetaData()); 
		} 

		catch (SQLException e) { 
			e.printStackTrace(); 
		} 

		finally { 

			if (rsUtf8 != null) { 
				try { rsUtf8.close(); } catch (SQLException e) {} 
			} 

			if (rsLatin != null) { 
				try { rsLatin.close(); } catch (SQLException e) {} 
			} 

			if (statement != null) { 
				try { statement.close(); } catch (SQLException e) {} 
			} 

			if (connection != null) { 
				try { connection.close(); } catch (SQLException e) {} 
			} 
		} 
	} 

	public static void printMetaData(ResultSetMetaData metaData) { 

		Map<String, Object>	metas = new HashMap<String, Object>(); 

		try { 
			metas.put("CatalogName", metaData.getCatalogName(1)); 
			metas.put("ColumnClassName", metaData.getColumnClassName(1)); 
			metas.put("ColumnDisplaySize", metaData.getColumnDisplaySize(1)); 
			metas.put("ColumnLabel", metaData.getColumnLabel(1)); 
			metas.put("ColumnName", metaData.getColumnName(1)); 
			metas.put("ColumnType", metaData.getColumnType(1)); 
			metas.put("ColumnTypeName", metaData.getColumnTypeName(1)); 
			metas.put("Precision", metaData.getPrecision(1)); 
			metas.put("Scale", metaData.getScale(1)); 
			metas.put("SchemaName", metaData.getSchemaName(1)); 
			metas.put("SchemaName", metaData.getSchemaName(1)); 
		} 

		catch (SQLException e) { 
			e.printStackTrace(); 
		} 

		StringBuffer buf = new StringBuffer(); 
		for (String meta : metas.keySet()) { 
			buf.append(meta).append(" : ").append(metas.get(meta)).append("\r\n"); 
		} 

		System.out.println(buf.toString()); 
	} 
}

I have no clue about Connector/J but the underlying reason could be the same as here:
http://bugs.mysql.com/bug.php?id=57709

Peter
(not a MySQL person)

Please, paste the results you've got with latin1 and with utf8.

UTF-8
-----

ColumnLabel : TEST 
ColumnDisplaySize : 7 
CatalogName : test 
Precision : 7 
ColumnType : 12 
ColumnClassName : java.lang.String 
Scale : 0 
ColumnName : TEST 
ColumnTypeName : VARCHAR 
SchemaName : 

Latin 1
-------

ColumnLabel : TEST 
ColumnDisplaySize : 10 
CatalogName : test 
Precision : 10 
ColumnType : 12 
ColumnClassName : java.lang.String 
Scale : 0 
ColumnName : TEST 
ColumnTypeName : VARCHAR 
SchemaName :

This is what mysql client shows as metadata for utf8 table (in 5.5.17):

mysql> select * from table_utf8;
Field   1:  `TEST`
Catalog:    `def`
Database:   `test`
Table:      `table_utf8`
Org_table:  `table_utf8`
Type:       VAR_STRING
Collation:  utf8_general_ci (33)
Length:     30
Max_length: 0
Decimals:   0
Flags:      BINARY
...

For us to have all the relevant details, please, send the output of:

show variables like 'char%';

from the same database that you created these tables in.

Thank you having a look at this error :)

character_set_client      latin1
character_set_connection  latin1
character_set_database	  latin1
character_set_filesystem  binary
character_set_results	
character_set_server      latin1
character_set_system      utf8
character_sets_dir        C:\Program Files\MySQL\MySQL Server 5.5\share\charsets\

Added to changelog for 5.1.19: 

For a UTF-8 table using a collation other than the default
(utf8_general_ci), the precision of the ResultSetMetaData could be
different from the precision specified in the CREATE TABLE statement.
The fix corrects the return value from getMaxBytesPerChar().

This fix changes the behavior of some connection string parameters.
useDynamicCharsetInfo no longer has any effect. With the setting
cacheServerConfiguration=true, the cached settings also include the
results of the SHOW CHARACTER SET statement.