DbUnit: NoSuchColumnException and case sensitivity

I have reason to believe the problem stemmed from user_id column as the record ID. I have similar problem in the past where the row ID is generated natively by SQL Server. I'm not at my work desk now, but try this solution to see if it helps: http://old.nabble.com/case-sensitivity-on-tearDown--td22964025.html

UPDATE - 02-03-11

I have a working solution here. Here's my test code:-

MySQL Script

CREATE TABLE `jforum_users` (
       `user_id` INT(11) NOT NULL AUTO_INCREMENT,
       `user_active` TINYINT(1) NULL DEFAULT NULL,
       `username` VARCHAR(50) NOT NULL DEFAULT '',
       PRIMARY KEY (`user_id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
ROW_FORMAT=DEFAULT
AUTO_INCREMENT=14

dbunit-test.xml Test File

<?xml version='1.0' encoding='UTF-8'?>

<dataset>
    <jforum_users user_id="100" username="First User" />
</dataset>

Java Code

Class.forName("com.mysql.jdbc.Driver");
Connection jdbcConnection = DriverManager.getConnection("jdbc:mysql://localhost:8889/test", "", "");
IDatabaseConnection con = new DatabaseConnection(jdbcConnection);

InputStream is = getClass().getClassLoader().getResourceAsStream("dbunit-test.xml");
IDataSet dataSet = new FlatXmlDataSetBuilder().build(is);
DatabaseOperation.CLEAN_INSERT.execute(con, dataSet);

con.close();

I didn't get any errors, and the row was added into the database.

Just FYI, I did try a REFRESH and that works fine without errors too:-

DatabaseOperation.REFRESH.execute(con, dataSet);

I'm using DBUnit 2.4.8 and MySQL 5.1.44.

Hope this helps.

When I got this error, it was because my schema had a not null constraint on a column, but this column was missing from my datafile.

For example, my table had

<table name="mytable">
    <column>id</column>
    <column>entity_type</column>
    <column>deleted</column>
</table>

<dataset>
    <mytable id="100" entity_type"2"/>
</dataset>

I have a not null constraint on the deleted column and when I run the test, I get the NoSuchColumnException.

When I change the dataset to

<mytable id="100" entity_type"2" deleted="0"/>

I get past the Exception.

I had a similar problem to this today (using the IDatabaseTester interface added in v2.2 against MySQL) and spent several hours tearing my hair out over it. The OP is using a PropertiesBasedJdbcDatabaseTester, whilst I was using its 'parent' JdbcDatabaseTester.

DBUnit has a FAQ answer related to this NoSuchColumnException (specific to MySQL) but it looks like an oversight to me that it neglects to mention that each connection drawn from the interface's getConnection() method will have separate config. In fact I'd go so far as to call it bug given the wording of the various bits of doco I looked at today and the names of the classes involved (eg. DatabaseConfig, yet per Connection?).

Anyway, in sections of code like setup/teardown (example below) you don't even provide the Connection object so there's no way I could see to set the config in there.

dbTester.setDataSet(beforeData);
dbTester.onSetup();

In the end I just extended JdbcDatabaseTester to @Override the getConnection() method and inject the configuration specific to MySQL each time:

class MySQLJdbcDatabaseTester extends org.dbunit.JdbcDatabaseTester {
  public MySQLJdbcDatabaseTester(String driverClass, String connectionUrl, String username, String password,
                                 String schema) throws ClassNotFoundException {
    super(driverClass, connectionUrl, username, password, schema);
  }

  @Override
  public IDatabaseConnection getConnection() throws Exception {
    IDatabaseConnection connection = super.getConnection();
    DatabaseConfig dbConfig = connection.getConfig();
    dbConfig.setProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY, new MySqlDataTypeFactory());
    dbConfig.setProperty(DatabaseConfig.PROPERTY_METADATA_HANDLER, new MySqlMetadataHandler());
    return connection;
  }
}

And finally all the errors went away.

I came here looking for an answer to this problem. For me the problem was the Hibernate Naming Strategy. I realised this is the problem as show_sql was true in the Spring's application.properties:

spring.jpa.show-sql=true

I could see the generated table SQL and the field name was 'FACT_NUMBER' instead of 'factNumber' I had in my dbunit's xml.

This was solved by forcing the default naming strategy (ironically the default seems to be org.hibernate.cfg.ImprovedNamingStrategy, which puts in the '_'):

spring.jpa.hibernate.naming-strategy=org.hibernate.cfg.DefaultNamingStrategy