#333 NoSuchTableException - Multiple Schema

[Andrew Newsome]

Apologies if this is a duplicate - I have read through the older bugs and the issue is still affecting me, so I thought I'd raise a new bug.

Environment
-----------------
DBUnit 2.4.9, Mysql 5.5, MySQL connector 5.1.16.

Issue
-------

The issue is that on a DELETE_ALL execution for a dataset which has fully qualified table names, I get a NoSuchTableException for 'SchemaName.TableName', when I have explicitly set the fully qualified name feature to true:

dbUnitConnection = new DatabaseConnection(connection);
dbUnitConnection.getConfig().setProperty(DatabaseConfig.FEATURE_QUALIFIED_TABLE_NAMES, true);

I am connecting to MySQL with root privileges and the connection URL is 'jdbc:mysql://localhost:3306/'. I have also tried 'jdbc:mysql://localhost:3306/information_schema', and 'jdbc:mysql://localhost:3306/<SchemaName>' , and none of them appear to work.

I am connecting to the root of Mysql, rather than specifying a schema because I am deleting/inserting data into multiple schemas.
As you can see in the code snippet above, I am not explicitly setting a schema name within the DatabaseConnection constructor, again, because there are multiple schemas.

I have debugged through the DBUnit code, and can see that in the org.dbunit.database.DatabaseDataSet class (line 210) no table meta data rows are returned from the Mysql driver.

I understand that there may be a problem with the way in which I am using DBUnit, in the way in which I am not specifying a schema anywhere other than in the Dataset XML files, however I haven't come across any documentation which directs me in to using a different approach for the setup I am wishing to perform. If a schema name is required within the DatabaseConnection constructor, then can it be any of them? I would have thought that the meta data returned would only be for one of the multiple schemas that I am using then, in that case.

[Pedro Delacruz]

I had the same problem. After severe frustration I used the source code to debug the issue. The root cause is that the MySqlMetadataHandler.getTables() method doesn't return a list of all tables. It only returns a list of tables of a single schema even if the "qualified table names" feature is enabled.

To work around this issue I created my own class that extends MySQLMetadataHandler and set it in the connection config:

DatabaseConfig config = databaseConnection.getConfig();//DBUnit's DatabaseConnection
config.setProperty(DatabaseConfig.FEATURE_QUALIFIED_TABLE_NAMES, true);
config.setProperty(DatabaseConfig.PROPERTY_METADATA_HANDLER, new FixedMySqlMetadataHandler());

This class iterates over all the schemas in order to get a list of all tables. The result sets from each of the schemas are combined into a single logical result set.

I've attached my FixedMySqlMetadataHandler. I didn't do much error handling in it because if an SQLException is thrown then my tests will fail anyway, so I didn't bother with any try-catches. I'm sure any changes to the DBUnit source code will use better code than mine.

[Jeff Jensen]

Pedro, Thanks for tracking it down. Are you able to make a patch to MySqlMetadataHandler with your changes that make it work? Then I can easily commit your fix.

Also providing some unit tests would help future maintenance.

[Kevin]

Pedro, thanks so much for FixedMySqlMetadataHandler.java this issue was driving me insane! :)

[Jeff Jensen]

Kevin, Pedro, any suggested changes (a patch with tests) to dbUnit? I see your file but not sure if it's a good core change or just something users must do depending on the situation.

[Test]

Hi, I'm starting using DBUnit 2.5.1 with db2, but I think I still have this issue.
I've got a schema with a lot of aliases reffered in related schemas.
When I try a export with QueryDataSet and FlatXmlDataSet.write, I can access on every tables, but when I try to insert my xml into my tables with DatabaseOperation.INSERT and my FlatXmlDataSet, I've got a NoSuTableException.
I've already tried a lot of things but the only one that work is to manualy set schema for every table, which mean one xml file per table.
Have you got a solution ?

[Test]

I've finally found a way.
I've set :

conn=getConnection();
conn.getConfig().setProperty(DatabaseConfig.FEATURE_QUALIFIED_TABLE_NAMES, true);

After that, I just precise original schema in xml before every table (schema.table )

[Kevin]

Jeff -- I used Pedro's FixedMySqlMetadataHandler and the above property change (FEATURE_QUALIFIED_TABLE_NAMES). Not sure if both are required, but I know this works:

        config.setProperty(DatabaseConfig.FEATURE_QUALIFIED_TABLE_NAMES, true);
        //config.setProperty(DatabaseConfig.PROPERTY_METADATA_HANDLER, new MySqlMetadataHandler());
        config.setProperty(DatabaseConfig.PROPERTY_METADATA_HANDLER, new FixedMySqlMetadataHandler());

We have actually migrated to using http://www.unitils.org/tutorial-database.html, it wraps DbUnit really nicely, built-in compatiblity with JUnit4, gives you straightforward property file configuration, and lets you use annotations to invoke any form of transactional behavior one might desire. In particular, we prefer to have each database-backed test run in a transaction that gets rolled back afterward, so that the database is returned to it's pristine state. Unitils also provides annotations for loading fixture files at the test class or method level.