| Bug #38171 | tinyint(N) is always reported as COLUMN_SIZE 3 | ||
|---|---|---|---|
| Submitted: | 16 Jul 2008 13:45 | Modified: | 23 Apr 2013 17:51 |
| Reporter: | Tobias Dittrich | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | Connector / J | Severity: | S3 (Non-critical) |
| Version: | 5.1.7, 5.0.9 | OS: | Any |
| Assigned to: | Filipe Silva | CPU Architecture: | Any |
[17 Jul 2008 4:42]
Valeriy Kravchuk
Recent 5.0.x versions of server returns length correctly: C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3308 -T test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.0.62-enterprise-gpl-nt MySQL Enterprise Server (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> create table ti (t1 tinyint(1), t2 tinyint(2), t3 tinyint(3), t4 tinyint( 4)); Query OK, 0 rows affected (0.58 sec) mysql> select * from ti; Field 1: `t1` Catalog: `def` Database: `test` Table: `ti` Org_table: `ti` Type: TINY Collation: binary (63) Length: 1 Max_length: 0 Decimals: 0 Flags: NUM Field 2: `t2` Catalog: `def` Database: `test` Table: `ti` Org_table: `ti` Type: TINY Collation: binary (63) Length: 2 Max_length: 0 Decimals: 0 Flags: NUM Field 3: `t3` Catalog: `def` Database: `test` Table: `ti` Org_table: `ti` Type: TINY Collation: binary (63) Length: 3 Max_length: 0 Decimals: 0 Flags: NUM Field 4: `t4` Catalog: `def` Database: `test` Table: `ti` Org_table: `ti` Type: TINY Collation: binary (63) Length: 4 Max_length: 0 Decimals: 0 Flags: NUM 0 rows in set (0.03 sec)
[17 Jul 2008 5:42]
Tonci Grgin
Tobias, we need to rule out error in MySQL server metadata first. Please do as Valery did and make cl client test. Paste the output here. To get metadata info, you need to start mysql cl client with "-T" option: mysql -uroot -proot -P3308 -T test
[17 Jul 2008 6:44]
Tobias Dittrich
Hi, I'm sorry I did not do that - I thought because in the very same setup with driver version 5.0 it works that means the server can not be the problem. However here's the output :) [test]> create table ti (t1 tinyint(1), t2 tinyint(2), t3 tinyint(3), t4 tinyint(4)); Query OK, 0 rows affected (0.07 sec) [test]> select * from ti; Field 1: `t1` Catalog: `def` Database: `test` Table: `ti` Org_table: `ti` Type: TINY Collation: binary (63) Length: 1 Max_length: 0 Decimals: 0 Flags: NUM Field 2: `t2` Catalog: `def` Database: `test` Table: `ti` Org_table: `ti` Type: TINY Collation: binary (63) Length: 2 Max_length: 0 Decimals: 0 Flags: NUM Field 3: `t3` Catalog: `def` Database: `test` Table: `ti` Org_table: `ti` Type: TINY Collation: binary (63) Length: 3 Max_length: 0 Decimals: 0 Flags: NUM Field 4: `t4` Catalog: `def` Database: `test` Table: `ti` Org_table: `ti` Type: TINY Collation: binary (63) Length: 4 Max_length: 0 Decimals: 0 Flags: NUM 0 rows in set (0.00 sec)
[17 Jul 2008 9:51]
Tonci Grgin
Test case
Attachment: TestBug38171.java (text/x-java), 4.15 KiB.
[17 Jul 2008 9:54]
Tonci Grgin
Tobias, truly the bug is reproducible on latest c/J 5.1.7 sources. Here's the result from attached test case: Connected to 5.0.68-pb10-log java.vm.version : 1.5.0_12-b04 java.vm.vendor : Sun Microsystems Inc. java.runtime.version : 1.5.0_12-b04 os.name : Windows XP os.version : null sun.management.compiler : HotSpot Client Compiler Test 1.1: tinyInt1isBit=false, useInformationSchema=true column name: t1 column type: <TINYINT> column size: <3> column name: t2 column type: <TINYINT> column size: <3> column name: t3 column type: <TINYINT> column size: <3> column name: t4 column type: <TINYINT> column size: <3> Test 1.2: tinyInt1isBit=true, useInformationSchema=true column name: t1 column type: <TINYINT> column size: <3> column name: t2 column type: <TINYINT> column size: <3> column name: t3 column type: <TINYINT> column size: <3> column name: t4 column type: <TINYINT> column size: <3> Test 2.1: tinyInt1isBit=false, useInformationSchema=false column name: t1 column type: <TINYINT> column size: <3> column name: t2 column type: <TINYINT> column size: <3> column name: t3 column type: <TINYINT> column size: <3> column name: t4 column type: <TINYINT> column size: <3> Test 2.2: tinyInt1isBit=true, useInformationSchema=false column name: t1 column type: <BIT> column size: <null> column name: t2 column type: <TINYINT> column size: <3> column name: t3 column type: <TINYINT> column size: <3> column name: t4 column type: <TINYINT> column size: <3>
[17 Jul 2008 9:58]
Tonci Grgin
Same result observed using c/J 5.0 latest sources.
[23 Apr 2013 17:51]
Filipe Silva
This bug is a duplicate of BUG#64069. -- The behavior reported is compliant with the JDBC API documentation, specifically with java.sql.DatabaseMetaData.getColumns(...) description. MySQL utilizes one Byte to store TINYINT data, hence these fields may hold values between -128 and 127, in case of SIGNED values, and between 0 and 255 in case of UNSIGNED values. In both cases the value maximum precision is 3, which is the returned value from DatabaseMetaData.getColumns(...).getInt("COLUMN_SIZE"). When creating a table with TINYINT fields, or whatever int data types, one can choose to set a "length" option to it, as in: CREATE TABLE ti ( t1 TINYINT(1), t2 TINYINT(2), t3 TINYINT(3), t4 TINYINT(4)); These *length* options consists in the best effort to retrieve values from each field with the given length and never to limit, in any way, their maximum precision. However, once this fields are integers, the fact is that it will have no effect at all because the only way to guarantee a fixed length of "at least *length* digits", is to apply zero left padding, which will be discarded unless ZEROFILL option is also provided. So, in order to make use of the *length* option, this should have been done instead: CREATE TABLE ti ( t1 TINYINT(1), -- no mater what, t1 will always have length at least equals 1, no ZEROFILL is not needed. t2 TINYINT(2) ZEROFILL, t3 TINYINT(3) ZEROFILL, t4 TINYINT(4) ZEROFILL); Please refer to documentation to further information: * Java 1.5 DatabaseMetaData.getColumns(...): [http://docs.oracle.com/javase/1.5.0/docs/api/java/sql/DatabaseMetaData.html#getColumns(jav...)] * Java 7 DatabaseMetaData.getColumns(...) [http://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html#getColumns(java.la...)] * MySQL Numeric Type Overview: [http://dev.mysql.com/doc/refman/5.1/en/numeric-type-overview.html] * MySQL Integer Types: [http://dev.mysql.com/doc/refman/5.1/en/integer-types.html]
Description: When using tinyint(N) the database meta data always report COLUMN_SIZE 3 regardless of what N is. The problem is not there in the 5.0 driver (I did not check older 5.1 versions, though). I tested on Open SuSe 10 (client and server) as well as Ubuntu 6.06 (client). I tested server versions 5.0.26 and 5.0.27-max. How to repeat: Create a table in database test with some tinyint columns: create table ti (t1 tinyint(1), t2 tinyint(2), t3 tinyint(3), t4 tinyint(4)); Then you can use the following little class to see the (wrong) results: import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.DriverManager; import java.sql.ResultSet; public class TinyIntTest { public static void main(String[] args) throws Exception { Class.forName("com.mysql.jdbc.Driver"); Connection con = DriverManager .getConnection("jdbc:mysql://yourserver/test?tinyInt1isBit=false", "user", "pwd"); DatabaseMetaData dbm = con.getMetaData(); ResultSet res = dbm.getColumns("test", null, "ti", null); while (res.next()) { System.out.println("Column: " + res.getString("COLUMN_NAME") + " Type " + res.getString("TYPE_NAME") + " Size: " + res.getInt("COLUMN_SIZE")); } con.close(); } } The result will be: Column: t1 Type TINYINT Size: 3 Column: t2 Type TINYINT Size: 3 Column: t3 Type TINYINT Size: 3 Column: t4 Type TINYINT Size: 3 NOTE: the "tinyInt1isBit=false" is only to show that tinyint(1) is also reported wrong. If set to true however it's correctly reported as BIT(0).