#319 NoSuchTableException when working on multi-schema database

v2.4.*
closed-invalid
Bug (232)
5
2014-12-30
2011-07-08
enji
No

Hi,

Environment : Unitils 3.1, DbUnit 2.4.3, MySQL database
Test case : crash when unitils try to insert data using xml dataset before running a test
Stack trace :

Caused by: org.dbunit.dataset.NoSuchTableException: my_table_in_second_schema
at org.dbunit.database.DatabaseDataSet.getTableMetaData(DatabaseDataSet.java:278)
at org.dbunit.operation.DeleteAllOperation.execute(DeleteAllOperation.java:109)
at org.dbunit.operation.CompositeOperation.execute(CompositeOperation.java:79)
at org.unitils.dbunit.datasetloadstrategy.impl.CleanInsertLoadStrategy.doExecute(CleanInsertLoadStrategy.java:45)
at org.unitils.dbunit.datasetloadstrategy.impl.BaseDataSetLoadStrategy.execute(BaseDataSetLoadStrategy.java:44)
... 20 more

Explanation : this bug is due to the way mysql implement catalog, schema and database notions to some "illogical" compatibility reasons
see http://forums.mysql.com/read.php?39,137564,137564
in several dbunit methods, parameter catalog is passed as null when trying to retrieve tables, columns or primary keys from DatabaseMetaData object

Bug fix :

in org.dbunit.util.SQLHelper add method

public static boolean isMySQLDb(DatabaseMetaData metaData) throws SQLException
{
String dbProductName = metaData.getDatabaseProductName();
boolean isMySQL = (dbProductName != null && dbProductName.equals(DB_PRODUCT_MYSQL));
return isMySQL;
}

in org.dbunit.database.DatabaseDataSet modify method initialize()

Connection jdbcConnection = _connection.getConnection();
DatabaseMetaData databaseMetaData = jdbcConnection.getMetaData();

String schema = _connection.getSchema();

if(SQLHelper.isSybaseDb(jdbcConnection.getMetaData()) && !jdbcConnection.getMetaData().getUserName().equals(schema) ){
logger.warn("For sybase the schema name should be equal to the user name. " +
"Otherwise the DatabaseMetaData#getTables() method might not return any columns. " +
"See dbunit tracker #1628896 and http://issues.apache.org/jira/browse/TORQUE-40?page=all");
}

// Bug fix specific when using MySQL SGBD
// see http://forums.mysql.com/read.php?39,137564,137564#msg-137564
String catalog = null;
if (SQLHelper.isMySQLDb(databaseMetaData)) {
logger.warn("For MySQL the catalog name should not be null. It should be equal to the schema name. " +
"Otherwise the DatabaseMetaData#getTables() method might not return any columns when testing multiple schema database. " +
"See http://forums.mysql.com/read.php?39,137564,137564#msg-137564");
catalog = schema;
}

DatabaseConfig config = _connection.getConfig();
String[] tableType = (String[])config.getProperty(DatabaseConfig.PROPERTY_TABLE_TYPE);

ResultSet resultSet = databaseMetaData.getTables(
catalog, schema, "%", tableType);

    // end bug fix

in org.dbunit.database.DatabaseDataSet.DatabaseTableMetaData modify method getPrimaryKeyNames()

Connection connection = _connection.getConnection();
DatabaseMetaData databaseMetaData = connection.getMetaData();

// Bug fix specific when using MySQL SGBD
// see http://forums.mysql.com/read.php?39,137564,137564#msg-137564
String catalog = null;
if (SQLHelper.isMySQLDb(databaseMetaData)) {
logger.warn("For MySQL the catalog name should not be null. It should be equal to the schema name. " +
"Otherwise the DatabaseMetaData#getTables() method might not return any columns when testing multiple schema database. " +
"See http://forums.mysql.com/read.php?39,137564,137564#msg-137564");
catalog = schemaName;
}

ResultSet resultSet = databaseMetaData.getPrimaryKeys(
catalog, schemaName, tableName);
// end bug fix

in org.dbunit.database.DatabaseDataSet.DatabaseTableMetaData modify method tableExists(DatabaseMetaData metaData, String schema, String tableName)

// Bug fix specific when using MySQL SGBD
// see http://forums.mysql.com/read.php?39,137564,137564#msg-137564
String catalog = null;
if (SQLHelper.isMySQLDb(metaData)) {
logger.warn("For MySQL the catalog name should not be null. It should be equal to the schema name. " +
"Otherwise the DatabaseMetaData#getTables() method might not return any columns when testing multiple schema database. " +
"See http://forums.mysql.com/read.php?39,137564,137564#msg-137564");
catalog = schema;
}

ResultSet tableRs = metaData.getTables\(catalog, schema, tableName, null\);
// end bug fix

Until know I found only theses 3 pieces of code where catalog parameter was required to make it work with MySQL database, but I'm not sure they are the only ones.

Discussion

  • enji

    enji - 2011-07-08

    sorry, I copy/paste wrong things in my post
    the last bug fix is correct bug method tableExists() is in org.dbunit.util.SQLHelper
    and method getPrimaryKeyNames in org.dbunit.database.DatabaseTableMetaData

     
  • Jeff Jensen

    Jeff Jensen - 2011-07-08

    Thank you for sharing this. It will be much easier, clearer, and accurate to commit the changes if you would make an svn patch file and attach. Are you able to do that?

    Additionally, it's difficult to verify/know this is correct changes without tests. You have much higher chance of having the patch applied with tests included. Are you able to make tests to prove the behavior (particularly, fails without it)?

    Thanks again...

     
  • Jeff Jensen

    Jeff Jensen - 2011-07-08

    I just noticed the "DbUnit 2.4.3" - are you able to try with 2.4.8, or even better, 2.4.9-snapshot (build from source)? There have been some changes since 2.4.3 that may help, particularly a bug I fixed with multiple schemas (name was not always used).

     
  • enji

    enji - 2011-07-08

    I tried with 2.4.8, which fixred this NoSuchTableException, but I'm now stucked with another bug, a unitils one this time.
    I'll try to commit the changes as soon as possible.

     
  • Jeff Jensen

    Jeff Jensen - 2011-07-08
    • assigned_to: gommma --> jeffjensen
    • status: open --> closed-invalid
     
  • Jeff Jensen

    Jeff Jensen - 2011-07-08

    Great, glad 2.4.8 took care of that issue... thought it would.
    I'm closing this issue as the problem no longer exists. Please open another entry as needed for new ones.

     

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:





No, thanks