DbUnit: NoSuchColumnException and case sensitivity

Question

Before posting this I googled a bit, I looked for in dbunit-user archives and a bit also in DbUnit bug list, but I'm not found what looking for. Unfortunately, answers here did not help me either.

I'm using DbUnit 2.4.8 with MySQL 5.1.x to populate in setUp some JForum tables. The issue is first appearing on jforum_users table created by this 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 '',
       `user_password` VARCHAR(32) NOT NULL DEFAULT '',
       [...]
       PRIMARY KEY (`user_id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
ROW_FORMAT=DEFAULT
AUTO_INCREMENT=14

Executing REFRESH as database setup operation the following exception is raised.

org.dbunit.dataset.NoSuchColumnException: jforum_users.USER_ID -
(Non-uppercase input column: USER_ID) in ColumnNameToIndexes cache
map. Note that the map's column names are NOT case sensitive.
       at org.dbunit.dataset.AbstractTableMetaData.getColumnIndex(AbstractTableMetaData.java:117)
       at org.dbunit.operation.AbstractOperation.getOperationMetaData(AbstractOperation.java:89)
       at org.dbunit.operation.RefreshOperation.execute(RefreshOperation.java:98)
       at org.dbunit.AbstractDatabaseTester.executeOperation(AbstractDatabaseTester.java:190)
       at org.dbunit.AbstractDatabaseTester.onSetup(AbstractDatabaseTester.java:103)
       at net.jforum.dao.generic.AbstractDaoTest.setUpDatabase(AbstractDaoTest.java:43)

I looked in AbstractTableMetaData.java sources and nothing seems -statically- wrong. The method

private Map createColumnIndexesMap(Column[] columns)

uses

columns[i].getColumnName().toUpperCase()

in writing map keys. And then the method

public int getColumnIndex(String columnName)

uses

String columnNameUpperCase = columnName.toUpperCase();
Integer colIndex = (Integer) this._columnsToIndexes.get(columnNameUpperCase);

to read object from the map.

I really can't undestand what's going on... Anybody can help me please?

Edit after last @limc answer

I'm using a PropertiesBasedJdbcDatabaseTester to configure my DbUnit env, as follow:

Properties dbProperties = new Properties();
dbProperties.load(new FileInputStream(testConfDir+"/db.properties"));
System.setProperty(PropertiesBasedJdbcDatabaseTester.DBUNIT_DRIVER_CLASS, 
    dbProperties.getProperty(PropertiesBasedJdbcDatabaseTester.DBUNIT_DRIVER_CLASS));
System.setProperty(PropertiesBasedJdbcDatabaseTester.DBUNIT_CONNECTION_URL, 
    dbProperties.getProperty(PropertiesBasedJdbcDatabaseTester.DBUNIT_CONNECTION_URL));
System.setProperty(PropertiesBasedJdbcDatabaseTester.DBUNIT_USERNAME, 
    dbProperties.getProperty(PropertiesBasedJdbcDatabaseTester.DBUNIT_USERNAME));
System.setProperty(PropertiesBasedJdbcDatabaseTester.DBUNIT_PASSWORD, 
    dbProperties.getProperty(PropertiesBasedJdbcDatabaseTester.DBUNIT_PASSWORD));
System.setProperty(PropertiesBasedJdbcDatabaseTester.DBUNIT_SCHEMA, 
    dbProperties.getProperty(PropertiesBasedJdbcDatabaseTester.DBUNIT_SCHEMA));

databaseTester = new PropertiesBasedJdbcDatabaseTester();
databaseTester.setSetUpOperation(getSetUpOperation());
databaseTester.setTearDownOperation(getTearDownOperation());

IDataSet dataSet = getDataSet();
databaseTester.setDataSet(dataSet);

databaseTester.onSetup();

Saccharometer

one for Stack Overflow methinks – Sami

Answer 1

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.

Tobietobin

@Tobietobin The flat xml dataset contains <JFORUM_USERS user_id="100" username="First User" user_password="nopass" /> and user_id is the primary key. In that post the poster had maybe forgotten the PK column... mumble mumble... – Saccharometer

@Tobietobin Using you Java code everything is OK. Using mine the exception in raised. Maybe I miss something in DatabaseTester configuration? – Saccharometer

The dataset is built so: IDataSet dataSet = flatXmlDataSetBuilder.build(new File(testConfDir+"/user/users-and-forums.xml")); – Saccharometer

What exception did you get? Make sure the file path is correct... do a file.exists() on your File statement to ensure it is correct. – Tobietobin

The exception is the org.dbunit.dataset.NoSuchColumnException: jforum_users.USER_ID reported above. I think this means the file is found – Saccharometer

FWIW I had the same issue which caused by improper name in the dataset xml file. I named a property "employee" instead of "employee_id". This is what happens when you have to switch your brain back and forth from OOP to RDBMS – Exoergic

Answer 2

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.

Lashondalashonde

For PostgreSQL one will have to add: dbConfig.setProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY, new PostgresqlDataTypeFactory()); This will stop the error about columns that cannot be found. – Ritualist

Answer 3

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

Barytone

Answer 4

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.

Bradski

Answer 5

Well in my case it was a csv file encoded in UTF-8 with BOM char in the beginning. I was using notepad to create csv files. Use notepade++ to avoid saving BOM char.

Everybody

Answer 6

I was faced with this problem and the reason was that the dtd of my dataset file had a description different of the table where i wanted to insert data.

So check that your table where you want to insert data has the same columns that your dtd file.

when I delete in the dtd file the column that was not in the table where i inserted the data the problem disappeared.

Octonary

Answer 7

I had same problem , then figured I have used different column name in my DB than what I have inside my XML file.

I'm sure you got problem in user_id vs USER_ID.

Tremann

Answer 8

I've just stumbled my self over this error message.

I had to extend an old piece of code - I needed to add a new column to several tables. In one of my entities I've forgotten to create a setter for this column. So you might check your entities if they are "complete".

Sometimes it might be as simple as that.

Asterism

Answer 9

Ok I faced the same trouble and I found the solution, The way we are creating the test data is wrong,for what kind of data set we were using, We were using the xml data set for which following format is correct is you are using the FlatXmlDataSet then there is a different format, for more explanation read in the link provided below. the xml should be in following format.

<?xml version="1.0" encoding="UTF-8"?>
<dataset>
    <table>
        <column>id</column>
        <column>name</column>
        <column>department</column>
        <column>startDate</column>
        <column>endDate</column>
        <row>
            <value>999</value>
            <value>TEMP</value>
            <value>TEMP DEPT</value>
            <value>2113-10-13</value>
            <value>2123-10-13</value>
        </row>
    </table>
</dataset>

If you wish to know more go to this link : http://dbunit.sourceforge.net/components.html

Kamilahkamillah

Another format is correct because it's a FlatXML, your format would be a XML format. Both are correct depending how you builder the dataset. – Tricuspid

Updated in answer :) :) – Kamilahkamillah

Update link in the answer thanks for notifying(who ever down voted ;) ) – Kamilahkamillah