| Bug #64069 | Wrong column size returned by mysql 5 connector | ||
|---|---|---|---|
| Submitted: | 19 Jan 2012 9:48 | Modified: | 24 Apr 2013 8:31 |
| Reporter: | Ankur Mittal | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | Connector / J | Severity: | S2 (Serious) |
| Version: | 5.1.18 | OS: | Any |
| Assigned to: | Alexander Soklakov | CPU Architecture: | Any |
| Tags: | column info, Column Size | ||
[19 Jan 2012 10:54]
Valeriy Kravchuk
What exact server version, 5.x.y, are you working with now?
[19 Jan 2012 15:59]
Ankur Mittal
We are currently using 5.0.51a. we have also tried connector 5.0.8 but with same results. Both the connectors have hard coded values for column size.
[20 Jan 2012 0:06]
Mark Matthews
The older versions of the connector had the bug. The behavior in currently shipping versions is correct. The JDBC specification states that COLUMN_SIZE is maximum precision, not character length. Size "specifiers" in the DDL for numeric columns in MySQL relate to presentation only (they do not alter the maximum precision that a column can store). From the JDBC APIDOCS for DBMD.getColumns(): "The COLUMN_SIZE column the specified column size for the given column. For numeric data, this is the maximum precision" From the MySQL documentation at http://dev.mysql.com/doc/refman/5.1/en/numeric-type-attributes.html "MySQL supports an extension for optionally specifying the display width of integer data types in parentheses following the base keyword for the type. For example, INT(4) specifies an INT with a display width of four digits. This optional display width may be used by applications to display integer values having a width less than the width specified for the column by left-padding them with spaces. (That is, this width is present in the metadata returned with result sets. Whether it is used or not is up to the application.) The display width does not constrain the range of values that can be stored in the column. Nor does it prevent values wider than the column display width from being displayed correctly. For example, a column specified as SMALLINT(3) has the usual SMALLINT range of -32768 to 32767, and values outside the range permitted by three digits are displayed in full using more than three digits." You may be able to get what you need from ResultSetMetaData.getColumnDisplaySize()?
[21 Jan 2012 6:02]
Ankur Mittal
Ok, thanks for the information.
[24 Jan 2012 8:49]
Ankur Mittal
Hi, Is this information mentioned in release note of any connector version? Thanks.
Description: Recently we updated our mysql connector from 3.0.10 to 5.1.18 as we have upgraded our mysql server to 5 In our code we are trying to get the column information of a particular table. The table contains fields with type tinyint(4),int(11),bigint(12) We used the following code to get the column size rec = meta.getColumns(null, null, table.tableName, "%");/DatabaseMetaData meta while (rec.next()) field.size = rec.getInt(7); ideally it should return 4,11,12 respectively but for tinyint it always returns 3, for int it always returns 10 and for bigint it always returns 19. the code used to work fine with old connector, but the new connector has broken our code. How to repeat: Create a table with fields tinyint,int,bigint try to get their sizes. Suggested fix: In class type descriptor the values for these datatypes are hardcoded. In previous version the connctor used to calculate the size from the type by getting the index of "(" and then parsing the integer value.