MySQL Bugs: #6399: getColumnDisplaySize wrong

Bug #6399	getColumnDisplaySize wrong
Submitted:	3 Nov 2004 12:46	Modified:	25 Apr 2005 1:14
Reporter:	[ name withheld ]
Status:	No Feedback
Category:	Server	Severity:	S1 (Critical)
Version:	4.1.x	OS:	Any (All)
Assigned to:	Alexander Barkov	Target Version:

Description:
I use mysql 4.1.7 on redhat 7.3
I converted my existing database to utf8 with

ALTER DATABASE Database CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE Table CONVERT TO CHARACTER SET utf8;

I changed the JDBC Connection String to

jdbc:mysql://192.168.7.11/Database?autoReconnect=true&characterEncoding=utf8&useUnicode=true

Now the getColumnDisplaySize from ResultSetMetaData ist wrong.
For some columns I get the right size, for some I get the size multiplied by 3.
If I enter a german umlaut in a column with right size, it returns the wrong size the
next time.

I don't know if this is a JDBC Connector or a server problem.

How to repeat:
as described.

Currently, the server returns the 'byte[]' length of columns. The protocol needs to be
extended so that both values (byte[] and char[]) are returned.

This is fixed in 3.0.17/3.1.2, however it really is a server-shortcoming and should be
addressed there, as it is returning incorrect metadata.

Mark,
there are three length attributes of a character string:
- number of bytes (SQL function LENGTH())
- number of characters (SQL function CHAR_LENGTH())
- number of display spaces (curently unexistent DISPLAY_LENGTH() and DISPLAY_WIDTH())
function.

Currently result set metadata contains only the maximum number of bytes
a column can occupy. You also can evaluate number of bytes in the column
when reading the row. Apart from that, the metadata doesn't contain any of the 
three mentioned above characteristics of result set rows, as these lengths
depend on actual content of the result set columns.
So, generally speaking, LENGTH(), CHAR_LENGTH() and DISPLAY_LENGTH()
are not result set metadata, but rather describe contents of every column/field
in the result set.
It's possible to extend the client-server protocol so that upon client's request it
sends such rows descriptions in addition to the actual contents of the rows. 
We may consider adding that. The question is, however, what's the purpose
of having it in the protocol when the client can evaluate it using SQL 
when it's needed?

Konstantin,

I figured out a workaround, but the reason it's needed are twofold:

1). Because many specifications require it (JDBC/ODBC, probably the SQL CLI)
2.) Because MySQL often rewrites column types and sizes when performing queries (by
creating 'approximate' temporary tables), you can't always get the _correct_ value after
issuing the query.

Perhaps a new function in libmysqlclient can solve this,
something like mysql_fetch_display_lengths().
What do you thinks?

Perhaps a new function in libmysqlclient can solve this,
something like mysql_fetch_display_lengths().
What do you think?

Sorry for putting my foot in it, but I personally can't think up how a function in
libmysql can help a JDBC driver problem. This is why I am on the position of not having
character-set related functionality in the client. Alexander knows that already (a point
of our disagreement ;)

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".