MySQL Bugs: #23304: Inconsistency between getSchemas and INFORMATION

Bug #23304	Inconsistency between getSchemas and INFORMATION_SCHEMA
Submitted:	15 Oct 2006 11:37	Modified:	22 Feb 2007 14:58
Reporter:	Alexander Hristov (Candidate Quality Contributor)
Status:	Closed
Category:	Connector/J	Severity:	S2 (Serious)
Version:	5.0.3	OS:	Any (Any)
Assigned to:	Mark Matthews	Target Version:
Tags:	information_schema, getSchemas, empty

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=tr
ue&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();
    }
  }

Hi Alexander and thanks for your problem report.
Varified as described by reporter, DatabaseMetaDataUsingInfoSchema.java does not implement
getSchemas().

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.

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.

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.

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.

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

A note has been added to the 5.0.5 changelog.