Bug #23304 Inconsistency between getSchemas and INFORMATION_SCHEMA
Submitted: 15 Oct 2006 9:37 Modified: 22 Feb 2007 13:58
Reporter: Alexander Hristov (Candidate Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S2 (Serious)
Version:5.0.3 OS:Any (Any)
Assigned to: Mark Matthews CPU Architecture:Any
Tags: empty, getSchemas, information_schema

[15 Oct 2006 9:37] Alexander Hristov
Description:
getSchemas() always returns an empty resultset, which is incorrect for a 5.x server as it should correspond to the result of

select CATALOG_NAME,SCHEMA_NAME from INFORMATION_SCHEMA.SCHEMATA

How to repeat:
public class Test {
  public static void main(String[] args) throws Exception {
    Class.forName("com.mysql.jdbc.Driver");
    Properties p = new Properties();
    p.setProperty("user","...");
    p.setProperty("password","...");
    p.setProperty("useInformationSchema","true");
    Connection con = (Connection)DriverManager.getConnection("jdbc:mysql://localhost:3306/test?autoReconnect=true&characterEncoding=UTF-8",p);
    DatabaseMetaData dbmt = con.getMetaData();
    ResultSet rs = dbmt.getSchemas();
    
    // The next loop prints nothing, and it should
    while (rs.next()) {
      System.out.println(rs.getString(1));
    }
  }
} 

Suggested fix:
In com.mysql.jdbc.DatabaseMetadataUsingInfoSchema.java, add the following method (overriden from DatabaseMetadata)

  @Override
  public ResultSet getSchemas() throws SQLException {
    PreparedStatement pStmt = null;
    try {
      pStmt = prepareMetaDataSafeStatement("SELECT " +
          "SCHEMA_NAME AS TABLE_SCHEM," +
          "CATALOG_NAME AS TABLE_CATALOG " +
          "FROM INFORMATION_SCHEMA.SCHEMATA");
      ResultSet rs = executeMetadataQuery(pStmt);
      ((com.mysql.jdbc.ResultSet) rs).redefineFieldsForDBMD(new Field[] {
          new Field("", "TABLE_SCHEM", java.sql.Types.VARCHAR, 255), 
          new Field("", "TABLE_CATALOG", java.sql.Types.VARCHAR, 255)
      });
      return rs;
    } finally {
      if (pStmt != null)
        pStmt.close();
    }
  }
[16 Oct 2006 6:53] Tonci Grgin
Hi Alexander and thanks for your problem report.
Varified as described by reporter, DatabaseMetaDataUsingInfoSchema.java does not implement getSchemas().
[16 Oct 2006 14:18] Mark Matthews
Because of legacy issues (going all the way back to the ODBC driver, and ease of porting from other ODBC databases), our drivers do not support the concept of schemas. Databases are mapped to catalogs in both our JDBC and ODBC drivers.

We are considering supporting schemas in a future release of the drivers, most likely when schemas in MySQL actually act like what most people expect from a schema (i.e. owners, etc). JDBC itself is inconsistent in that it gives schema metadata, but does not allow you to change the current schema on a connection, and many users of MySQL require the ability to change the current "database" on a given connection.
[17 Oct 2006 9:09] Alexander Hristov
Mark, while the first argument is reasonable ("we choose not to support schemas"), I don't agree with your labelling of this as "Not a bug", and neither with the rest of the arguments. 

The fact is that now there is an inconsistency between what is reported by the server and the information reported by the driver. MySQL Server chooses to report its internal structure as a global "null" catalog with schemas (even if they don't behave *completely* as such), and your driver chooses to report the structure as a schema-less set of catalogs. But this can - and does - generate problems. A stored procedure could choose to retrieve information from the INFORMATION_SCHEMA, return the results via JDBC, and the application would fail to use that information properly because of this inconsistency. 

This situation *is* a bug. Who's responsible? I don't know. Maybe your driver is right and MySQL Server should fix the INFORMATION_SCHEMA by reporting databases as what they currently are - as schemaless catalogues. But then again, maybe the driver is wrong in the sense that from an architectural viewpoint it's not up to a driver to invade the responsabilities of a server and fix an "incorrect" behaviour of that server.  Even more - the driver has a property that says "Use Information Schema", but then it doesn't! (as it is performing arbitrary translations). Now you can't invoke legacy arguments here : this is a new option and it defaults to false. Legacy appliacations are using DatabaseMetaData.java, not DatabaseMetaDataUsingInfoSchema.java

Regarding JDBC "inconsistency", you say that it does not provide a a means of changing the "current schema". It does: The SQL standard gives you the SET CATALOG (ISO/IEC 9075-2:2003 18.5) and SET SCHEMA (ISO/IEC 9075-2:2003 18.6) statements to change the values of the default catalog and schema associated to a SQL session, so changing the schema and catalog is performed by executing these statements.

Yes, the JDBC does not have a *specific api method* for that sql statement, but it doesn't have a CreateTable() method, or an AlterView() method either and I don't see how that makes it inconsistent.  It's up to *the server* to implement that SQL statements (or the driver to translate the statements into the implementation-dependant actions with similar effect). JDBC has nothing to do here.
[17 Oct 2006 14:34] Mark Matthews
Alexander,

JDBC in the past as a defacto standard hasn't allowed one to use _any_ "SET ..." statements that alter session state. This is actually being explicitly specified in the JDBC-4.0 specification, because otherwise drivers have to scan every query and look for queries that might change session state, as this state is often required to be materialized client-side in order to support the API itself.

I'll agree that there is a bug in that when using information_schema with the JDBC driver that results aren't consistent when not using the information_schema, but I don't agree that just using information_schema means that the driver has to support schemas. MySQL doesn't have schemas. It has something it _names_ schemas, but they're not, it's just an alias for databases, which behave more like catalogs.
[17 Oct 2006 17:31] Alexander Hristov
Regarding the first part, I don't know. Right now I cannot think of any part of the JDBC API that forces a SET-changeable session state to be maintained client-side and I don't see what difference can exist between executing a SET statement through a driver AND executing a stored procedure through a driver which executes the SET statement, effectively achieving the same result without the driver being aware.

"Which is the default schema" doesn't seem something a driver should care about, much less attempt to keep track of. Anyway, I haven't written any JDBC drivers so I assume you know better. 

I disagree about 'not being able to execute SET statements to be a de-facto standard'. It's perfectly valid in Oracle for example to do the following (usr1 and usr2 are two schemas with full privileges)

    // Create table1 in schema "usr1", and insert "foo" as a value
    con = DriverManager.getConnection("jdbc:oracle:thin:@host:1521:test","usr1","usr1");
    Statement sql = con.createStatement();
    sql.executeUpdate("CREATE TABLE table1 ( whatever VARCHAR(10))");
    sql.executeUpdate("INSERT INTO table1 values('foo')");
    
    // Create table1 in schema "usr2", and insert 3.14 as a value
    con = DriverManager.getConnection("jdbc:oracle:thin:@host1521:test","usr2","usr2");
    sql = con.createStatement();
    sql.executeUpdate("CREATE TABLE table1 ( something NUMERIC(5,2) )");
    sql.executeUpdate("INSERT INTO table1 values(3.14)");

    ResultSet rs = sql.executeQuery("SELECT * FROM table1 ");
    while (rs.next()) {
      System.out.println(rs.getString(1));   // Prints 3.14
    }
    
    // Set default schema to usr1
    sql.executeUpdate("ALTER SESSION SET CURRENT_SCHEMA = usr1");
    
    rs = sql.executeQuery("SELECT * FROM table1 ");
    while (rs.next()) {
      System.out.println(rs.getString(1));   // Prints "foo"
    }

Is the Oracle driver scanning the statements to catch an ALTER SESSION? I doubt it. Probably it couldn't care more about which is the current schema.

Anyway, the bug is not about this, since what you choose to support is what you choose to support and I cannot file a bug for "not supporting SET xxx " statement.

What I'm saying (and what the bug is about) is basically the same as what you are saying in the second paragraph: A principled position that it's not the driver's responsability to alter the (buggy) behaviour of a server or to provide missing functionality. If the server says "this is a schema", I believe the driver (barring some *specific* spec requirement) should rely that information as provided by the server, even though if it is incorrect. 

Of course, this would be very hard if there was a huge amount of legacy applications written otherwise, but imho this is not the case:

You currently have a new useInformationSchema switch that defaults to false. It's a mostly legacy-free switch because it was introduced recently. So it seems reasonable to make it report data as reported by the server, even though it is not compatible with the results obtained by useInformationSchema=false. Otherwise it may happen that, in the future, when/if MySQL adds schema support, yet another switch like "schemasAreReallySchemas" will have to be added in order to make the driver report correctly and without translations the data from INFORMATION_SCHEMA.
[7 Dec 2006 22:47] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/16623
[22 Feb 2007 13:58] MC Brown
A note has been added to the 5.0.5 changelog.