MySQL Bugs: #35044: Optimize Column Width

Bug #35044	Optimize Column Width
Submitted:	4 Mar 2008 17:33	Modified:	5 Mar 2008 17:14
Reporter:	Jerome Asselin	Email Updates:
Status:	Verified	Impact on me:	None
Category:	Connector / ODBC	Severity:	S4 (Feature request)
Version:	3.51.23	OS:	Windows (XP)
Assigned to:		CPU Architecture:	Any
Tags:	FLAG_FIELD_LENGTH

Description:
I request an option to "Optimize Column Width" in MyODBC. This used to be the default behavior in 3.51.12 when FLAG_FIELD_LENGTH (Don't Optimize Column Width) was turned off.

Now the default behavior do not optimize column width.

Since 3.51.18, FLAG_FIELD_LENGTH has been removed. Now I see no option to optimize column width. I have severe performance issues in 3.51.23. Getting data from MySQL TEXT fields into SPSS via 3.51.23 is drastically slower than with 3.51.12 and the generated SPSS data file occupies much more space. In my real-life data (not shown here), the new SPSS data files occupy approximately 100 times more space than before!

In future versions of MyODBC 3.51, I'm asking to please, PLEASE!, add an option to turn on column width optimization like in in 3.51.12.

WORKAROUND:
Downgrade to 3.51.12.

How to repeat:
# IN MySQL
CREATE TABLE `test` (
`a` text NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `test` set a = 'Hola';

# IN SPSS
GET DATA
/TYPE = ODBC
/CONNECT='dsn=MyDSN' /UNENCRYPTED
/SQL = 'select * from test'.

# RESULTS:
# With MyODBC 3.5.23, column "a" is a string with width 255.
# With MyODBC 3.5.12, column "a" is a string with width 4 (which corresponds
# to the length of the data string "Hola"). This is the desired behavior.

Suggested fix:
Add an option in MyODBC to allow the user to "Optimize Column Width".

Hi Jerome and thanks for your feature request.
3.51.18 (6-Aug-2007)
  * Lengths returned by SQLColumns(), SQLDescribeCol(), and SQLColAttribute()
    were often incorrect. These lengths should now conform to the ODBC
    specification. FLAG_FIELD_LENGTH no longer has any effect. The default
    behavior was incorrect. (Bug #27862)
3.51.20 (7-Sep-2007)
  * Removed checkbox in setup dialog for FLAG_FIELD_LENGTH ("Don't
    Optimize Column Width"), which was removed from the driver in 3.51.18.

I don't believe that this will be reintroduced but I'll set report to "Verified" on accounts that "Optimize Column Width" checkbox is truly removed.

Thank you for your reply.

I've been seeking another workaround in light of the fact that the old behavior will not reintroduced (not even as an option). I can use CAST or CONVERT in the SQL query to optimize column width on the fly.

WORKAROUND #2
# OLD QUERY:
  select * from test;
# NEW QUERY:
  select convert(a,char(4)) as a from test;

This leads to the desired result at the expense of having to list the column names and pre-calculate the optimal column width for TEXT columns.

If someone finds a better workaround, please post.