DBUnit: NoSuchColumnException Non-uppercase input column in ColumnNameToIndexes cache map. map's column names are NOT case sensitive

Question

My java application stores your information in MySql database version 8. The user information and password are stored in this database. I am implementing an integration test to test the method that validates the user in the database using dbunit. After the test method runs, the error below occurs.

Caused by: org.dbunit.dataset.NoSuchColumnException: USER.USER_ID -  (Non-uppercase input column: USER_ID) in ColumnNameToIndexes cache map. Note that the map's column names are NOT case sensitive.

I checked through MySql Workbench that both the table and the columns were created in uppercase, so the error message displayed does not make sense. Does anyone know what can be causing this exception?

testContext.xml

unitils.properties

unitils.module.hibernate.enabled=true
unitils.module.jpa.enabled=false
unitils.module.easymock.enabled=false

database.url=jdbc:mysql://192.168.0.12:3306/db_ca?useUnicode=true&characterEncoding=UTF-8&serverTimezone=America/Sao_Paulo
database.driverClassName=com.mysql.jdbc.Driver
database.userName=root
database.password=9999
database.schemaNames=db_main, db_ca
database.dialect=mysql

unitils.module.database.className=com.myapplication.test.unitils.SingleConnectionDatabaseModule
DatabaseModule.Transactional.value.default=disabled

DbUnitModule.DataSet.loadStrategy.default=org.unitils.dbunit.datasetloadstrategy.impl.CleanInsertLoadStrategy

updateDataBaseSchema.enabled=true
dbMaintainer.script.locations=src/test/dbscripts
dbMaintainer.autoCreateExecutedScriptsTable=true
dbMaintainer.generateDataSetStructure.enabled=false
dbMaintainer.preserve.schemas=

In src/test/dbscripts exists the file 001_SCRIPT_01.00.00

SET character_set_client = utf8mb4 ;
CREATE TABLE db_ca.ROLE (
  ROLE_ID INT(11) NOT NULL AUTO_INCREMENT,
  NAME VARCHAR(200) NOT NULL,
  PRIMARY KEY (ROLE_ID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

SET character_set_client = utf8mb4 ;
CREATE TABLE db_ca.USER (
  USER_ID INT(11) NOT NULL AUTO_INCREMENT,
  EMAIL VARCHAR(100) NULL DEFAULT NULL,
  NAME VARCHAR(150) NOT NULL,
  PASSWORD VARCHAR(200) NOT NULL,
  PRIMARY KEY (USER_ID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

SET character_set_client = utf8mb4 ;
CREATE TABLE db_ca.USER_ROLE (
  ROLE_ID INT(11) NOT NULL,
  USER_ID INT(11) NOT NULL,
  PRIMARY KEY (ROLE_ID, USER_ID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

/datasets/UserServiceTest.xml

<?xml version="1.0" encoding="UTF-8"?>
<dataset xmlns="db_ca">
    <USER
        USER_ID = "1"
        EMAIL = "test@test.com"
        NAME = "TEST"
        PASSWORD = "e8d95a51f3af4a3b134bf6bb680a213a"
   />    
   <ROLE
        ROLE_ID = "1"
        NAME = "ADMIN"
   />   
   <USER_ROLE
        USER_ID = "1"
        ROLE_ID = "1"
   />   
</dataset>

Entities

@Entity
@Table(name = "USER", schema="db_ca")
public class User {

    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name="USER_ID", nullable=false)
    private Long id;

    ...
}

@Entity
@Table(name = "ROLE", schema="db_ca" )
public class Role {

    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name="ROLE_ID", nullable=false)
    private Long id;    

    ...
}   

Class Test AccessServiceIT.java

@DataSet("/datasets/UsuarioServiceTest.xml")
@RunWith(UnitilsJUnit4TestClassRunner.class)
@SpringApplicationContext("testContext.xml")
public class AccessServiceIT {

    @SpringBean("AccessServiceImpl")
    private AccessService accessService;

    @Test
    public void accessTest() {
        CredentialsBean credentialsBean = accessService.confirmLogon("test@test.com", "e8d95a51f3af4a3b134bf6bb680a213a");
        assertEquals("test@test.com", credentialsBean.getLogon());
        assertEquals(true, credentialsBean.isAuthenticated());
    }
}

Answer 1

Multiple datasets

Old question, but encountered the problem today. Issue was due to multiple datasets containing inserts on the same table, with some having fields provided while others did not. I had to merge every data contained in those various file in a single giga file.

Concrete case

I had three datasets imported in my @DatabaseSetup:

• customer_underaged.xml
• customer_company.xml
• customer_no_birthdate.xml

In the customer_no_birthdate.xml, column birthdate was purposefully not provided. When running tests, I encountered the infamous:

Caused by: org.dbunit.dataset.NoSuchColumnException: USER.BIRTHDATE -  (Non-uppercase input column: BIRTHDATE) in ColumnNameToIndexes cache map. Note that the map's column names are NOT case sensitive.

Merging all those three dataset into a single customer_mixed.xml, the issue disappeared.

Idea

I found out that for some reason only the last dataset containing definition of a table is taken into account to compute the definition of that table for every other datasets. I think there is some kind of issue in computation of table metadata which are cached (see org.dbunit.dataset.xml.FlatXmlProducer#createTableMetaData), where every files are checked, and for each table defined, only the latest definition is kept for later inserts.

Answer 2

In my case, this error has been occurring in tests with the XML files imitating the DB using the @DatabaseSetup.

I had to add a new column to the companyOffice entities, but there were company entities along with those, and all mixed up in the XML file.. Thus, I added the column to the company entity, and it ended up trying to find this field in the wrong place.

I'm not very attentive, but the error message is not clear at all.

Answer 3

For me I get this error, because the column was not declared in the hibernate entity, but the column exists in the table (oracle database). That's why Dbunit dataset tought that the column may be is declared in lower or upper case, but it was not declared at all. The exception message is not accurate.

Answer 4

I wasn't using Unitils, I was invoking dbunit directly. I solved this by setting the property of the connection:

final IDatabaseConnection connection = new DatabaseConnection(dataSource.getConnection());
connection.getConfig()
          .setProperty(DatabaseConfig.PROPERTY_METADATA_HANDLER, new MySqlMetadataHandler());

Answer 5

I found the solution to the problem. I just put the following property in unitils.properties and everything is working perfectly fine now.

org.dbunit.database.IMetadataHandler.implClassName=org.dbunit.ext.mysql.MySqlMetadataHandler

I found the solution here https://stackoverflow.com/a/39549867/3554659

I hope it helps who is going through the same mistake.