INFORMATION_SCHEMA columns mixup in memory db

[Mohamed Mansour]

Hello,

I was trying to figure out this very odd problem I am having. One of my coworkers changed the table name from User to Users without creating unit tests, then after a month when I created the unit tests, it started failing (we are using dbunit for testing).

org.dbunit.dataset.DataSetException: org.h2.jdbc.JdbcSQLException: Column "NAME" not found; SQL statement:
select NAME, ADMIN, REMARKS, ID, ID, VERSION, FIRST_NAME, IS_ACTIVE, LAST_NAME, LOGIN, PERMISSION_ID from USERS order by ID [42122-147]

As you seen above, it is prepending NAME, ADMIN, REMARKS, ID to my real table (all columns after that). I realized that this is from INFORMATION_SCHEMA. Are we not allowed creating tables that are named ROLES, USERS, or anything inside INFORMATION_SCHEMA.* ?

My datasource is set to this:

> <datasources>

>    <local-tx-datasource>

>       <jndi-name>appTestDatasource</jndi-name>

>       <connection-url>jdbc:h2:mem:mint;DB_CLOSE_DELAY=-1</connection-url>

>       <driver-class>org.h2.Driver</driver-class>

>       <user-name>sa</user-name>

>       <password></password>

>    </local-tx-datasource>

> </datasources>

I am using dbunit/H2 to help me test my Oracle database. Any help is appreciated! There was a thread in russian that had the same problem:

http://www.sql.ru/forum/actualthread.aspx?bid=38&tid=599722&hl=

I ran H2 database from the client, and it is correctly giving me the metadata. I initially thought it was DBunit who is doing this, but from the source of DBUnit, it is just doing this:

String schemaName = _connection.getSchema();
Connection jdbcConnection = _connection.getConnection();
DatabaseMetaData databaseMetaData = jdbcConnection.getMetaData();
ResultSet resultSet = databaseMetaData.getColumns(null, schemaName, tableName, "%");

Where schemaName is returning null.  But the databaseMetadata gives all the columns merged for INFORMATION_SCHEMA.USERS and USERS.

Any idea what to do?

Kind regards,

Mohamed

[Thomas Mueller]

Hi,

I don't know DbUnit, maybe you should ask there. Basically,
_connection.getSchema() needs to return "PUBLIC" and not null. Maybe
setting System.setProperty(PropertiesBasedJdbcDatabaseTester.DBUNIT_SCHEMA,
"PUBLIC") will help - I found that here:
http://www.dbunit.org/howto.html

> String schemaName = _connection.getSchema();
> Connection jdbcConnection = _connection.getConnection();
> DatabaseMetaData databaseMetaData = jdbcConnection.getMetaData();
> ResultSet resultSet = databaseMetaData.getColumns(null, schemaName,
> tableName, "%");
> Where schemaName is returning null.

That's not good, because when using null, you will get all columns of
all tables (in all schemas) with this table name.

> But the databaseMetadata gives all the columns merged for INFORMATION_SCHEMA.USERS and USERS.

That's what DatabaseMetaData.getColumns is supposed to do (according
to the JDBC API spec) if schemaName is null. It's not a bug (in H2).

> Any idea what to do?

I don't know how to make _connection.getSchema() return the correct
schema name, I guess you need to find out why in DbUnit (I don't know
DbUnit). Basically, schemaName should be "PUBLIC" and not null.

> Are we not allowed creating tables that are named ROLES,
> USERS, or anything inside INFORMATION_SCHEMA.* ?

No. You can't create such a table: "create table
information_schema.users(id int)" fails.

Regards,
Thomas

[Mohamed Mansour]

Thanks for replying, comments inline!

On Friday, February 11, 2011 1:56:38 PM UTC-5, Thomas Mueller wrote:

Hi,

I don't know DbUnit, maybe you should ask there. Basically,
_connection.getSchema() needs to return "PUBLIC" and not null. Maybe
setting System.setProperty(PropertiesBasedJdbcDatabaseTester.DBUNIT_SCHEMA,
"PUBLIC") will help - I found that here:
http://www.dbunit.org/howto.html

I will try that, and I will examine the dbunit source to figure out why its null (I thought I had last time, but I must have missed something, I apologize).

 

> String schemaName = _connection.getSchema();
> Connection jdbcConnection = _connection.getConnection();
> DatabaseMetaData databaseMetaData = jdbcConnection.getMetaData();
> ResultSet resultSet = databaseMetaData.getColumns(null, schemaName,
> tableName, "%");
> Where schemaName is returning null.

That's not good, because when using null, you will get all columns of
all tables (in all schemas) with this table name.

Thats good to know :) Can we include something like that in the documentation, would be helpful for other implementations :)

 

> But the databaseMetadata gives all the columns merged for INFORMATION_SCHEMA.USERS and USERS.

That's what DatabaseMetaData.getColumns is supposed to do (according
to the JDBC API spec) if schemaName is null. It's not a bug (in H2).

Thanks for this info, I didn't know about the default behavior. If schemaName is null, it is not specified, in the past, not specifying the schema goes to the default database, maybe it would be nice if a default database could be set in H2 (I will check further to see how that is done).

 

> Any idea what to do?

I don't know how to make _connection.getSchema() return the correct
schema name, I guess you need to find out why in DbUnit (I don't know
DbUnit). Basically, schemaName should be "PUBLIC" and not null.

> Are we not allowed creating tables that are named ROLES,
> USERS, or anything inside INFORMATION_SCHEMA.* ?

No. You can't create such a table: "create table
information_schema.users(id int)" fails.

Sorry, I meant the other way around, since there is no default schema in H2, we have to place "PUBLIC" infront of tables that have the exact same name as the ones in INFORMATION_SCHEMA. So in this case, any tables inside INFORMATION_SCHEMA that we define in the public namespace should be prefixed with PUBLIC.* If not, then H2 will merge the columns together.

 

Would be nice if the default namespace in H2 is set to PUBLIC instead of setting it ourselves everytime. Thanks for the valuable information, I really appreciate it :)

I love this project, you have done a superb job!

Regards,
Thomas

[Thomas Mueller]

Hi,

>> That's not good, because when using null, you will get all columns of
>> all tables (in all schemas) with this table name.
>
> Thats good to know :) Can we include something like that in the
> documentation, would be helpful for other implementations :)

Well, it's already documented...

> Thanks for this info, I didn't know about the default behavior. If
> schemaName is null, it is not specified, in the past, not specifying the
> schema goes to the default database, maybe it would be nice if a default
> database could be set in H2 (I will check further to see how that is done).

As far as I know, the JDBC specification required that using null
means all schemas. This is how other databases also behave.

> Sorry, I meant the other way around, since there is no default schema in H2,
> we have to place "PUBLIC" infront of tables that have the exact same name as
> the ones in INFORMATION_SCHEMA.

That's not correct. There is a default schema when running SQL
statements, however using null in the DatabaseMetaData call doesn't
use the default schema according to the JDBC specification. The
default schema only applies to running SQL statements. See also:

http://h2database.com/html/grammar.html#set_schema

> Would be nice if the default namespace in H2 is set to PUBLIC instead of
> setting it ourselves everytime.

The default schema doesn't apply to DatabaseMetaData calls per JDBC
specification.

Regards,
Thomas