| Bug #69290 | JDBC Table type "SYSTEM TABLE" is used inconsitently | ||
|---|---|---|---|
| Submitted: | 20 May 2013 18:47 | Modified: | 23 Oct 2013 17:21 |
| Reporter: | Matthias Bläsing | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | Connector / J | Severity: | S3 (Non-critical) |
| Version: | 5.1.18 | OS: | Any |
| Assigned to: | Filipe Silva | CPU Architecture: | Any |
[29 May 2013 13:43]
Filipe Silva
Hi Matthias Bläsing, Thank you for this bug report. It was verified as reported.
[23 Oct 2013 17:21]
Daniel So
Added the following entry to the Connector/J 5.1.27 changelog: The SYSTEM TABLE type was handled inconsistently in different methods: DatabaseMetaData.getTableTypes() did not return system tables, and DatabaseMetaData.getTables() did not return all system tables. With this fix, the table types SYSTEM TABLE and SYSTEM VIEW are returned by getTableTypes(), and getTables() now returns tables from the internal schemas mysql, information_schema, and performance_schema as instances of SYSTEM TABLE.
[23 Oct 2013 21:59]
Daniel So
Revised the changelog entry for this bug: "The SYSTEM TABLE type was handled inconsistently in different methods: DatabaseMetaData.getTableTypes() did not return system tables, and DatabaseMetaData.getTables() did not return all system tables. With this fix, the table types SYSTEM TABLE and SYSTEM VIEW are returned by getTableTypes(), and getTables() now returns tables from the internal schemas mysql and performance_schema as instances of SYSTEM TABLE and tables from information_schema as instances of SYSTEM VIEW."
Description: The table type "SYSTEM TABLE" is not used consistently: 1. getTableTypes() in DatabaseMetaData has a hardcoded list of types, that does not include "SYSTEM TABLE", but getTables reports "SYSTEM TABLE" for system tables... 2. Tables in information_schema are reported as "SYSTEM TABLE", while neither mysql, nor performance_schema report system tables, while I would consider both to be system tables. For the code in "How to repeat" i got: -------------------------------------------------------- MySQL-AB JDBC Driver => mysql-connector-java-5.1.18 ( Revision: tonci.grgin@oracle.com-20110930151701-jfj14ddfq48ifkfq ) MySQL => 5.5.31-0ubuntu0.12.04.1 ========= TABLE TYPES ========== TABLE VIEW LOCAL TEMPORARY ====== System tables in 'information_schema' ========= CHARACTER_SETS SYSTEM TABLE COLLATIONS SYSTEM TABLE COLLATION_CHARACTER_SET_APPLICABILITY SYSTEM TABLE COLUMNS SYSTEM TABLE COLUMN_PRIVILEGES SYSTEM TABLE ENGINES SYSTEM TABLE EVENTS SYSTEM TABLE FILES SYSTEM TABLE GLOBAL_STATUS SYSTEM TABLE GLOBAL_VARIABLES SYSTEM TABLE INNODB_BUFFER_PAGE SYSTEM TABLE INNODB_BUFFER_PAGE_LRU SYSTEM TABLE INNODB_BUFFER_POOL_STATS SYSTEM TABLE INNODB_CMP SYSTEM TABLE INNODB_CMPMEM SYSTEM TABLE INNODB_CMPMEM_RESET SYSTEM TABLE INNODB_CMP_RESET SYSTEM TABLE INNODB_LOCKS SYSTEM TABLE INNODB_LOCK_WAITS SYSTEM TABLE INNODB_TRX SYSTEM TABLE KEY_COLUMN_USAGE SYSTEM TABLE PARAMETERS SYSTEM TABLE PARTITIONS SYSTEM TABLE PLUGINS SYSTEM TABLE PROCESSLIST SYSTEM TABLE PROFILING SYSTEM TABLE REFERENTIAL_CONSTRAINTS SYSTEM TABLE ROUTINES SYSTEM TABLE SCHEMATA SYSTEM TABLE SCHEMA_PRIVILEGES SYSTEM TABLE SESSION_STATUS SYSTEM TABLE SESSION_VARIABLES SYSTEM TABLE STATISTICS SYSTEM TABLE TABLES SYSTEM TABLE TABLESPACES SYSTEM TABLE TABLE_CONSTRAINTS SYSTEM TABLE TABLE_PRIVILEGES SYSTEM TABLE TRIGGERS SYSTEM TABLE USER_PRIVILEGES SYSTEM TABLE VIEWS SYSTEM TABLE ====== System tables in 'XXXXX' ========= ====== System tables in 'mysql' ========= ====== System tables in 'performance_schema' ========= ====== System tables in 'XXXXX' ========= ====== System tables in 'XXXXX' ========= ====== System tables in 'XXXXX' ========= -------------------------------------------------------- The problem of getTableTypes at least is also present in the newest version of the driver. How to repeat: Quick and dirty code (c is a mysql jdbc connection): DatabaseMetaData dmd = c.getMetaData(); System.out.println(dmd.getDriverName() + " => " + dmd.getDriverVersion()); System.out.println(dmd.getDatabaseProductName() + " => " + dmd.getDatabaseProductVersion()); System.out.println("========= TABLE TYPES =========="); ResultSet rs = dmd.getTableTypes(); while(rs.next()) { System.out.println(rs.getString(1)); } List<String> catalogs = new ArrayList<>(); rs = dmd.getCatalogs(); while(rs.next()) { catalogs.add(rs.getString(1)); } for (String catalog : catalogs) { System.out.println("====== System tables in '" + catalog + "' ========="); rs = dmd.getTables(catalog, null, "%", new String[]{"SYSTEM TABLE"}); while (rs.next()) { System.out.println(rs.getString("TABLE_NAME") + "\t" + rs.getString("TABLE_TYPE")); } }