| Bug #48947 | mysql J connector 5.1.10 returns wrong metadata for view | ||
|---|---|---|---|
| Submitted: | 20 Nov 2009 16:58 | Modified: | 3 Dec 2009 13:35 |
| Reporter: | Alan LaPenn | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | Connector / J | Severity: | S2 (Serious) |
| Version: | 5.1.10 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | 5.1.10, Connector/J, meteadata, VIEW | ||
[20 Nov 2009 17:03]
Mark Matthews
What's returned if you use RSMD.getColumnLabel() instead of .getColumnName()? Later versions of Connector/J 5.1 changed the behavior of .getColumnName() to match a clarification in the JDBC-4.0 specification surrounding the behavior of this method. There's a workaround if getColumnLabel() indeed returns what you expect, although really I say that's a bug in the server's returned metadata, because the client should expect to see a VIEW as a TABLE and know nothing about the underlying TABLEs in said VIEW.
[20 Nov 2009 18:11]
Alan LaPenn
getColumnLabel() does return the correct column title and it is a workaround but as Mark Matthews said getColumnName really should be returning the same value regardless if it is a table or a view.
[23 Nov 2009 7:17]
Tonci Grgin
Hi Alan. I would like you to attach small but complete test case demonstrating unwanted behavior. You can find numerous examples in BugsDB. I guess an output of same query in mysql command line client (use -T for MySQL servers 5.0 or --column-type-info for 5.1 and up) should be interesting too.
[23 Nov 2009 13:45]
Alan LaPenn
I am a bit busy this week, have to pay the bills by working for a living:-) ao I won't get to the test case until next week. However I did run the select statement against the view and the results are below. Everything looks ok when I query from the mysql command line client on the local host: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1743 Server version: 5.0.45 Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> select * from fp_pim_contact; Field 1: `userid` Catalog: `def` Database: `funambol` Table: `fp_pim_contact` Org_table: `fnbl_pim_contact` Type: VAR_STRING Collation: utf8_general_ci (33) Length: 765 Max_length: 18 Decimals: 0 Flags: MULTIPLE_KEY BINARY PART_KEY Field 2: `id` Catalog: `def` Database: `funambol` Table: `fp_pim_contact` Org_table: `fnbl_pim_contact` Type: LONGLONG Collation: binary (63) Length: 20 Max_length: 4 Decimals: 0 Flags: NOT_NULL PRI_KEY NUM PART_KEY Field 3: `first_name` Catalog: `def` Database: `funambol` Table: `fp_pim_contact` Org_table: `fnbl_pim_contact` Type: VAR_STRING Collation: utf8_general_ci (33) Length: 192 Max_length: 8 Decimals: 0 Flags: BINARY Field 4: `middle_name` Catalog: `def` Database: `funambol` Table: `fp_pim_contact` Org_table: `fnbl_pim_contact` Type: VAR_STRING Collation: utf8_general_ci (33) Length: 192 Max_length: 5 Decimals: 0 Flags: BINARY Field 5: `last_name` Catalog: `def` Database: `funambol` Table: `fp_pim_contact` Org_table: `fnbl_pim_contact` Type: VAR_STRING Collation: utf8_general_ci (33) Length: 192 Max_length: 10 Decimals: 0 Flags: BINARY Field 6: `title` Catalog: `def` Database: `funambol` Table: `fp_pim_contact` Org_table: `fnbl_pim_contact` Type: VAR_STRING Collation: utf8_general_ci (33) Length: 96 Max_length: 0 Decimals: 0 Flags: BINARY Field 7: `company` Catalog: `def` Database: `funambol` Table: `fp_pim_contact` Org_table: `fnbl_pim_contact` Type: VAR_STRING Collation: utf8_general_ci (33) Length: 765 Max_length: 14 Decimals: 0 Flags: BINARY Field 8: `photo_type` Catalog: `def` Database: `funambol` Table: `fp_pim_contact` Org_table: `fnbl_pim_contact` Type: SHORT Collation: binary (63) Length: 6 Max_length: 1 Decimals: 0 Flags: NUM Field 9: `job_title` Catalog: `def` Database: `funambol` Table: `fp_pim_contact` Org_table: `fnbl_pim_contact` Type: VAR_STRING Collation: utf8_general_ci (33) Length: 384 Max_length: 0 Decimals: 0 Flags: BINARY Field 10: `department` Catalog: `def` Database: `funambol` Table: `fp_pim_contact` Org_table: `fnbl_pim_contact` Type: VAR_STRING Collation: utf8_general_ci (33) Length: 765 Max_length: 0 Decimals: 0 Flags: BINARY Field 11: `body` Catalog: `def` Database: `funambol` Table: `fp_pim_contact` Org_table: `fnbl_pim_contact` Type: BLOB Collation: utf8_general_ci (33) Length: 196605 Max_length: 2 Decimals: 0 Flags: BLOB Field 12: `categories` Catalog: `def` Database: `funambol` Table: `fp_pim_contact` Org_table: `fnbl_pim_contact` Type: VAR_STRING Collation: utf8_general_ci (33) Length: 765 Max_length: 8 Decimals: 0 Flags: BINARY Field 13: `display_name` Catalog: `def` Database: `funambol` Table: `fp_pim_contact` Org_table: `fnbl_pim_contact` Type: VAR_STRING Collation: utf8_general_ci (33) Length: 384 Max_length: 18 Decimals: 0 Flags: BINARY Field 14: `item_type` Catalog: `def` Database: `funambol` Table: `fp_pim_contact` Org_table: `fnbl_pim_contact_item` Type: SHORT Collation: binary (63) Length: 6 Max_length: 2 Decimals: 0 Flags: PRI_KEY NUM PART_KEY Field 15: `value` Catalog: `def` Database: `funambol` Table: `fp_pim_contact` Org_table: `fnbl_pim_contact_item` Type: VAR_STRING Collation: utf8_general_ci (33) Length: 765 Max_length: 21 Decimals: 0 Flags: BINARY Field 16: `address_type` Catalog: `def` Database: `funambol` Table: `fp_pim_contact` Org_table: `fnbl_pim_address` Type: SHORT Collation: binary (63) Length: 6 Max_length: 1 Decimals: 0 Flags: PRI_KEY NUM PART_KEY Field 17: `street` Catalog: `def` Database: `funambol` Table: `fp_pim_contact` Org_table: `fnbl_pim_address` Type: VAR_STRING Collation: utf8_general_ci (33) Length: 384 Max_length: 19 Decimals: 0 Flags: BINARY Field 18: `city` Catalog: `def` Database: `funambol` Table: `fp_pim_contact` Org_table: `fnbl_pim_address` Type: VAR_STRING Collation: utf8_general_ci (33) Length: 192 Max_length: 6 Decimals: 0 Flags: BINARY Field 19: `state` Catalog: `def` Database: `funambol` Table: `fp_pim_contact` Org_table: `fnbl_pim_address` Type: VAR_STRING Collation: utf8_general_ci (33) Length: 192 Max_length: 0 Decimals: 0 Flags: BINARY Field 20: `postal_code` Catalog: `def` Database: `funambol` Table: `fp_pim_contact` Org_table: `fnbl_pim_address` Type: VAR_STRING Collation: utf8_general_ci (33) Length: 48 Max_length: 0 Decimals: 0 Flags: BINARY Field 21: `country` Catalog: `def` Database: `funambol` Table: `fp_pim_contact` Org_table: `fnbl_pim_address` Type: VAR_STRING Collation: utf8_general_ci (33) Length: 96 Max_length: 2 Decimals: 0 Flags: BINARY mysql> exit Bye User time 0.00, System time 0.01 Maximum resident set size 0, Integral resident set size 0 Non-physical pagefaults 749, Physical pagefaults 0, Swaps 0 Blocks in 0 out 0, Messages in 0 out 0, Signals 0 Voluntary context switches 54, Involuntary context switches
[24 Nov 2009 12:32]
Tonci Grgin
Ok Alan. Waiting on small but complete test case.
[30 Nov 2009 15:20]
Alan LaPenn
//Create the database and tables and a user
CREATE DATABASE IF NOT EXIST conjprob DEFAULT CHARSET=utf8;
CREATE TABLE `testTbl` (
`fieldName` char(10) DEFAULT NULL,
`type` smallint(6) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE VIEW `testView` AS SELECT `testTbl`.`fieldName` AS `fieldName`,`testTbl`.`type` AS `field_type` FROM `testTbl`;
// Code to reproduce problem with connector
import java.sql.*;
public class j {
public static void main(String args[]) {
Connection con = null;
ResultSet rs = null;
ResultSetMetaData rsmd = null;
Statement s = null;
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/conjprob",
"conj", "test");
if(!con.isClosed())
{
s = con.createStatement ();
s.executeQuery ("SELECT * FROM testView");
rs = s.getResultSet ();
rsmd = rs.getMetaData();
System.out.println();
System.out.println("View Name: testView");
System.out.println("-------------------");
System.out.println("Column Label" + "\t" + "Column Name");
System.out.println("------------" + "\t" + "-----------");
int columnCount = rsmd.getColumnCount();
for (int i = 1; i <= columnCount; ++i)
{
System.out.println(rsmd.getColumnLabel(i) + "\t" + rsmd.getColumnName(i));
}
}
} catch(Exception e) {
System.err.println("Exception: " + e.getMessage());
} finally {
try {
if(con != null)
s.close();
rs.close();
con.close();
} catch(SQLException e) {}
}
}
}
// Result of running above code against the created database
// Using the Connector/J version 5.1.10
View Name: testView
-------------------
Column Label Column Name
------------ -----------
fieldName fieldName
field_type type
[3 Dec 2009 11:34]
Tonci Grgin
Alan, I can not repeat this behavior using latest c/J sources... Connected to 5.1.31-log java.vm.version : 1.5.0_17-b04 java.vm.vendor : Sun Microsystems Inc. java.runtime.version : 1.5.0_17-b04 os : Windows Server 2008, 6.0, x86 sun.management.compiler : HotSpot Client Compiler ------------------------------------------------- View Name: vbug48947 ------------------- Column Label Column Name ------------ ----------- fieldName fieldName field_type field_type Time: 0,387 OK (1 test) Test case attached. JVM arguments and connection string: -Xmx512M -XX:+UseParallelGC -Dcom.mysql.jdbc.java6.javac=C:\jvms\jdk1.6.0\bin\javac.exe -Dcom.mysql.jdbc.java6.rtjar=C:\jvms\jdk1.6.0\jre\lib\rt.jar -Dcom.mysql.jdbc.testsuite.url.default=jdbc:mysql://xx:xx/test?user=xx&password=xx&autoReconnect=false&connectTimeout=5000&socketTimeout=30000&useUnicode=true&characterSetResults=utf8&useInformationSchema=true&useServerPrepStmts=true&cacheResultSetMetadata=true&useAffectedRows=false&useColumnNamesInFindColumn=true
[3 Dec 2009 11:36]
Tonci Grgin
Test case for our test framework, working in JDK 1.5.
Attachment: TestBug48947.java (text/x-java), 2.37 KiB.
[3 Dec 2009 13:34]
Alan LaPenn
I am using: java version "1.6.0_12" Java(TM) SE Runtime Environment (build 1.6.0_12-b04)
[3 Dec 2009 13:35]
Alan LaPenn
Java HotSpot(TM) Client VM (build 11.2-b01, mixed mode, sharing) centOS 5.0
[4 Dec 2009 16:53]
Tonci Grgin
Alan, no, still can't repeat with JDK 1.6: .Loading JDBC driver 'com.mysql.jdbc.Driver' Done. Done. java.vm.version : 11.0-b16 java.vm.vendor : Sun Microsystems Inc. java.runtime.version : 1.6.0_11-b03 os : Windows Server 2008, 6.0, x86 sun.management.compiler : HotSpot Client Compiler ------------------------------------------------- Connected to 5.1.31-log View Name: vbug48947 ------------------- Column Label Column Name ------------ ----------- fieldName fieldName field_type field_type Time: 0,741 OK (1 test) Guys, please use my test case and latest snapshot of cJ while trying to repeat the problem.
Description: When trying the retrieve metadata from a view the wrong column name is returned for the view columns. For example if I define the following view: CREATE ALGORITHM=UNDEFINED DEFINER=`someuser`@`localhost` SQL SECURITY DEFINER VIEW `f_pim_contact` AS SELECT `c`.`userid` AS `userid`,`c`.`id` AS `id`,`c`.`first_name` AS `first_name`,`c`.`middle_name` AS `middle_name`,`c`.`last_name` AS `last_name`,`c`.`title` AS `title`,`c`.`company` AS `company`,`c`.`photo_type` AS `photo_type`,`c`.`job_title` AS `job_title`,`c`.`department` AS `department`,`c`.`body` AS `body`,`c`.`categories` AS `categories`,`c`.`display_name` AS `display_name`,`i`.`type` AS `item_type`,`i`.`value` AS `value`,`a`.`type` AS `address_type`,`a`.`street` AS `street`,`a`.`city` AS `city`,`a`.`state` AS `state`,`a`.`postal_code` AS `postal_code`,`a`.`country` AS `country` FROM ((`fnbl_pim_contact` `c` LEFT JOIN `fnbl_pim_address` `a` ON((`a`.`contact` = `c`.`id`))) LEFT JOIN `fnbl_pim_contact_item` `i` ON((`i`.`contact` = `c`.`id`))) WHERE (`c`.`status` <> _UTF8 'D'); When I try to get the column named `item_type` it returns the name of the column from the table it was linked to in the view `type`. `i`.`type` AS `item_type` Switching to an earlier version of connector/J 5.0.8 does not exhibit this behavior. How to repeat: Create a view making sure there is a column in the view defined with a label other than the name of the column the data was retrieved from, like `type` AS `item_type` Execute a select statement on the view and retrieve the column names with the following snippet: ResultSetMetaData rsmd; for (int i = 1; i <= columnCount; ++i) { column = rsmd.getColumnName(i); log.trace("Column Name = " + column); } Suggested fix: Don't know anything about the connector/J code so I can't venture a guess at the fix. (I know there are a connector/J experts out there that can solve this problem in a much shorter time than I can).