#313 MySqlDataTypeFactory saves TINYINT(1) as String

[Waclaw Borowiec]

When writing down DB content to an XML file TINYINT(1) columns are mapped to "true" and "false" strings. DBUnit isn't however able to restore them from the XML. It throws

org.dbunit.dataset.datatype.TypeCastException: Error casting value for table 't1' and column 'column1'
at org.dbunit.operation.AbstractBatchOperation.execute(AbstractBatchOperation.java:190)
at org.dbunit.operation.CompositeOperation.execute(CompositeOperation.java:79)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.junit.internal.runners.MethodRoadie.runBefores(MethodRoadie.java:122)
at org.junit.internal.runners.MethodRoadie.runBeforesThenTestThenAfters(MethodRoadie.java:86)
at org.junit.internal.runners.MethodRoadie.runWithTimeout(MethodRoadie.java:49)
at org.junit.internal.runners.MethodRoadie.run(MethodRoadie.java:40)
at org.junit.internal.runners.JUnit4ClassRunner.invokeTestMethod(JUnit4ClassRunner.java:88)
at org.junit.internal.runners.JUnit4ClassRunner.runMethods(JUnit4ClassRunner.java:51)
at org.junit.internal.runners.JUnit4ClassRunner$1.run(JUnit4ClassRunner.java:44)
at org.junit.internal.runners.ClassRoadie.runUnprotected(ClassRoadie.java:27)
at org.junit.internal.runners.ClassRoadie.runProtected(ClassRoadie.java:37)
at org.junit.internal.runners.JUnit4ClassRunner.run(JUnit4ClassRunner.java:42)
at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:46)
at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197)
Caused by: org.dbunit.dataset.datatype.TypeCastException: Unable to typecast value <false> of type <java.lang.String> to TINYINT
at org.dbunit.dataset.datatype.IntegerDataType.typeCast(IntegerDataType.java:81)
at org.dbunit.dataset.datatype.IntegerDataType.setSqlValue(IntegerDataType.java:106)
at org.dbunit.database.statement.SimplePreparedStatement.addValue(SimplePreparedStatement.java:73)
at org.dbunit.database.statement.AutomaticPreparedBatchStatement.addValue(AutomaticPreparedBatchStatement.java:63)
at org.dbunit.operation.AbstractBatchOperation.execute(AbstractBatchOperation.java:186)
... 23 more
Caused by: java.lang.NumberFormatException
at java.math.BigDecimal.<init>(BigDecimal.java:459)
at java.math.BigDecimal.<init>(BigDecimal.java:728)
at org.dbunit.dataset.datatype.IntegerDataType.typeCast(IntegerDataType.java:77)
... 27 more

To fix it I created and registered data type following factory:

public static class MyBetterDataTypeFactory extends MySqlDataTypeFactory {
@Override
public DataType createDataType(int sqlType, String sqlTypeName) throws DataTypeException
{
if (sqlType == Types.BIT && sqlTypeName.toLowerCase().contains("tinyint"))
{
return DataType.TINYINT;
}
DataType createDataType = super.createDataType(sqlType, sqlTypeName);
return createDataType;
}
}

and it works. It is MySQL 5.0. DBUnit 2.4.8.

[Luke R. Flesch]

This is a serious problem. It also seems to be true that if you try to set a MySQL TINYINT column to a number other than 0 or 1 in the XML, it always writes a 1.

[Luke R. Flesch]

Note the following:

"BOOL, BOOLEAN

These types are synonyms for TINYINT(1). A value of zero is considered false. Nonzero values are considered true:"

(http://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.html)

I think that this aspect of MySQL explains why it was coded wrong in the first place.

[Weifeng Li]

The reason is TINYINT was treated as BIT (aka boolean type).
To fix it, return TINYINT if sqlTypeName is BIT

dbunit_tinyint.patch is a fix

If you also used "true" and "false" as TINYINT value in dataset, apply dbunit_tinyint2.patch

[Jeff Jensen]

Thanks Weifeng Li, applied. In next release after 2.4.9.

[Mattias Jiderhamn]

Sad thing now is that this broke the use of BIT. Bit columns that had "true" or "false" in the XML can no longer be inserted.

Caused by: org.dbunit.dataset.datatype.TypeCastException: Unable to typecast value <true> of type <java.lang.string> to TINYINT
at org.dbunit.dataset.datatype.IntegerDataType.typeCast(IntegerDataType.java:81)
at org.dbunit.dataset.datatype.IntegerDataType.setSqlValue(IntegerDataType.java:106)
at org.dbunit.database.statement.SimplePreparedStatement.addValue(SimplePreparedStatement.java:73)</java.lang.string></true>

[Weifeng Li]

Which patch was applied, dbunit_tinyint.patch or dbunit_tinyint2.patch?
dbunit_tinyint2.patch can fix the issue, and also allows "true" and "false" as TINYINT value in dataset.

[Jeff Jensen]

Are more changes required to further fix this?

[Mattias Jiderhamn]

From my point of view this seems to be fine now

[Jeff Jensen]

Thanks Mattias for sharing.

[Jörg Eichhorn]

When trying to upgrade from DBUnit 2.4.9 to 2.5.1 i get this error.

[Jeff Jensen]

Are you able to attach a stack trace?
Are you able to track down the cause? Perhaps a suggested code change?

[Jörg Eichhorn]

Here is the stack trace:

Caused by: org.dbunit.dataset.datatype.TypeCastException: Unable to typecast value <false> of type <java.lang.String> to TINYINT
    at org.dbunit.dataset.datatype.IntegerDataType.typeCast(IntegerDataType.java:81)
    at org.dbunit.dataset.datatype.IntegerDataType.setSqlValue(IntegerDataType.java:106)
    at org.dbunit.database.statement.PreparedBatchStatement.addValue(PreparedBatchStatement.java:71)
    at org.dbunit.database.statement.AutomaticPreparedBatchStatement.addValue(AutomaticPreparedBatchStatement.java:63)
    at org.dbunit.operation.AbstractBatchOperation.execute(AbstractBatchOperation.java:200)
    ... 11 more
Caused by: java.lang.NumberFormatException
    at java.math.BigDecimal.<init>(BigDecimal.java:494)
    at java.math.BigDecimal.<init>(BigDecimal.java:383)
    at java.math.BigDecimal.<init>(BigDecimal.java:806)
    at org.dbunit.dataset.datatype.IntegerDataType.typeCast(IntegerDataType.java:77)
    ... 15 more

I wasn't able to identify the reason for this exception.

[Iwao AVE!]

Hi Jeff,

I have the same problem as Jörg.
And the second patch from Weifeng (dbunit_tinyint2.patch) contains the fix.
Could you please apply it?
Please let me know if I should open a new ticket.

Regards,
Iwao

[Jeff Jensen]

Thanks for the followups.
I applied the "Treat "false" as 0, "true" as 1" snippet from dbunit_tinyint2.patch as it seems this is the missing piece for expectations and Iwao mentioned that patch fixes it for him.

Please test the updated snapshot and reply with your results!

Can someone please make a test that proves this feature works correctly? I really appreciate help like that to improve dbUnit and ensure we're not breaking things.

[Iwao AVE!]

Thank you, Jeff!
I have verified that the latest snapshot fixed the issue and sent a merge request with the test case.

[Jeff Jensen]

Great, thanks Iwao! Merged.