| Bug #20913 | DatabaseMetaData.getTables fails to list all tables in all catalogs | ||
|---|---|---|---|
| Submitted: | 8 Jul 2006 3:49 | Modified: | 16 May 2007 8:04 |
| Reporter: | George Van Treeck | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | Connector / J | Severity: | S2 (Serious) |
| Version: | 3.1.13 | OS: | Windows (Windows XP, SP2) |
| Assigned to: | Mark Matthews | CPU Architecture: | Any |
| Tags: | DatabaseMetaData, getTables | ||
[12 Jul 2006 9:56]
Tonci Grgin
Hi George and thanks for your problem report. Test case is attached to Bug #20912. Sorry, I don't see this as a bug according to documentation: http://java.sun.com/j2se/1.3/docs/api/java/sql/DatabaseMetaData.html#getTables(java.lang.S...]) Parameters: catalog - a catalog name; "" retrieves those without a catalog; null means drop catalog name from the selection criteria schemaPattern - a schema name pattern; "" retrieves those *without a schema* etc... Your tables do not match criteria *without a schema*
[14 Jul 2006 6:48]
George Van Treeck
First, you referenced J2EE 1.3 documentation which describes a different behavior than Java 1.4 and 1.5. Your own test case conforms to the Java 1.3 behavior but not the later versions of Java. 1.5 documentation states: Parameters: catalog - a catalog name; must match the catalog name as it is stored in the database; "" retrieves those without a catalog; null means that the catalog name should not be used to narrow the search schemaPattern - a schema name pattern; must match the schema name as it is stored in the database; "" retrieves those without a schema; null means that the schema name should not be used to narrow the search According to the Java 1.4 and 1.5 documentation, passing null in both the catalog and schema parameters should list all tables in all catalogs and schemas. You own test case did not do this! How does one list all tables in all databases (catalogs)? Second, the account I used for the JDBC connection has all priviliges on the entire database. I program to the same database everyday with PHP and no problems. And using MySQL Adminstrator with same account lists all tables in the catalog. Thus, I'm am certain it is not a privileges issue. It is a JDBC driver issue. Third: I think you are using different terminology and thus perhaps misunderstanding the reported problem. In the test cases you provided, what you call a schema name, you pass to the catalog parameter -- not in the schema parameter. Further, on my system, getSchemas() returns nothing at all! getCatalogs() returns a list of what you call a schema. Therefore, I'm not sure what you meant when you were talking about the schema parameter, because as far as I can tell the 3.1.13 JDBC driver does not make use of the "schema" parameter at all.
[14 Jul 2006 8:33]
Tonci Grgin
Hi George. > How does one list all tables in all databases (catalogs)? Use my function from test case: testResult(dbmd.getTables(null, null, "%", tableTypes)); but set nullCatalogMeansCurrent option to false in your connection string. See manual at http://dev.mysql.com/doc/refman/5.1/en/cj-configuration-properties.html >Second, the account I used for the JDBC connection has all priviliges on the entire database. I program to the same database everyday with PHP and no problems. And using MySQL Adminstrator with same account lists all tables in the catalog. Thus, I'm am certain it is not a privileges issue. It is a JDBC driver issue. See above. >Third: Further, on my system, getSchemas() returns nothing at all! getCatalogs() returns a list of what you call a schema. Agree, getSchema() returns empty set. >because as far as I can tell the 3.1.13 JDBC driver does not make use of the "schema" parameter at all. From connector/J docs: - All DBMD result set columns describing schemas now return NULL to be more compliant with the behavior of other JDBC drivers for other databases (MySQL does not support schemas). We are aware of the fact that MySQL server itself sometimes does not return enough metadata to connector. Request has been made to change this behavior.
[18 Jul 2006 0:29]
Mark Matthews
Have you tried "nullCatalogMeansCurrent=false" as a configuration property in your URL? This is a switch to change the legacy behavior from prior JDKs where NULL didn't mean "drop catalog criteria", and thus the default is "true" for the old behavior.
[18 Aug 2006 23:00]
Bugs System
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".
[16 May 2007 8:04]
Tonci Grgin
Thank you for your bug report. This issue has already been fixed in the latest released version of that product, which you can download at http://www.mysql.com/downloads/ Explanation: This is indeed working as Mark suggested in 3.1 and 5.0 SVN. Test case is attached. .Loading JDBC driver 'com.mysql.jdbc.Driver' Done. Done. Connected to 5.0.38-log java.vm.version : 1.5.0_10-b03 java.vm.vendor : Sun Microsystems Inc. java.runtime.version : 1.5.0_10-b03 os.name : Windows XP os.version : null sun.management.compiler : HotSpot Client Compiler Tables in mysql DB: 19 Tables total on MySQL server: 1021 Tables total on mysql database: 19 Time: 6,547 OK (1 test) 070516 9:33:33 10 Connect root@localhost on mysql 10 Query SET NAMES utf8 10 Query SET character_set_results = NULL 10 Query SHOW VARIABLES 10 Query SHOW COLLATION 10 Query SET autocommit=1 10 Query SET sql_mode='STRICT_TRANS_TABLES' 10 Query SELECT VERSION() 10 Query DROP DATABASE IF EXISTS bug20913 10 Query CREATE DATABASE bug20913 10 Query CREATE TABLE bug20913.bug20913tst(Id INT UNSIGNED) 11 Connect root@localhost on mysql 11 Query SET NAMES utf8 11 Query SET character_set_results = NULL 11 Query SHOW VARIABLES 11 Query SHOW COLLATION 11 Query SET autocommit=1 11 Query SET sql_mode='STRICT_TRANS_TABLES' 11 Query USE mysql 11 Query SHOW TABLES 11 Query SHOW DATABASES 11 Query SHOW CHARACTER SET 11 Query SHOW FULL TABLES FROM `information_schema` LIKE '%' 11 Query SHOW FULL TABLES FROM `bug20084` LIKE '%' 070516 9:33:35 11 Query SHOW FULL TABLES FROM `encodingtest` LIKE '%' 11 Query SHOW FULL TABLES FROM `utf8test` LIKE '%' 070516 9:33:39 11 Query SHOW FULL TABLES FROM `world` LIKE '%' 11 Quit 12 Connect root@localhost on mysql 12 Query SET NAMES utf8 12 Query SET character_set_results = NULL 12 Query SHOW VARIABLES 12 Query SHOW COLLATION 12 Query SET autocommit=1 12 Query SET sql_mode='STRICT_TRANS_TABLES' 12 Query SHOW FULL TABLES FROM `mysql` LIKE '%' 10 Query DROP DATABASE IF EXISTS bug20913 12 Quit 10 Quit
[16 May 2007 8:05]
Tonci Grgin
Test case
Attachment: TestBug20913.java (text/x-java), 4.08 KiB.
Description: i create a connection with the default catalog being "inventory". DatabaseMetaData dbmd = connection.getMetaData(); String[] tableTypes = { "TABLE" }; dbmd.getTables("inventory", null, "%", tableTypes); Which returns all 36 tables in the default catalog. But, dbmd.getTables(null, null, "%", tableTypes); also returns only those tables in the default catalog, which is a bug. It should return all tables for all catalogs, when the catalog argument is set to null. If the catalog argument is set to an empty string, then it should only return the tables without a catalog. The following is the quote from the DatabaseMetaData API parameters doc: "catalog - a catalog name; must match the catalog name as it is stored in the database; "" retrieves those without a catalog; null means that the catalog name should not be used to narrow the search" How to repeat: Take some existing program and insert a call to getTables, passing null as the value for the catalog parameter and print out the list of reported tables.