0

I'm trying to create a loadData entry in my liquibase changeset that can be used in both a MySQL database and a H2 database (for testing and development). I used the liquibase-maven-plugin and successfully generated CSV files that contain entries for my database. All of the BINARY(16) UUID for my database appear the following way in my CSV file: "[B@2ba4b32b" -- which I'm assuming is a toString() call on the byte[]. This works fine with Liquibase inserting the data back into MySQL. However, when I try to run the same on H2 I get the following error:

INSERT INTO PUBLIC.account (systemUuid, ...) VALUES ('[B@2ba4b32b', ...) -- ('[B@2ba4b32b', ...) [90003-187]
! org.h2.jdbc.JdbcSQLException: Hexadecimal string with odd number of characters: "[B@2ba4b32b"; SQL statement:
! INSERT INTO PUBLIC.account (systemUuid, ...) VALUES ('[B@2ba4b32b', ...) -- ('[B@2ba4b32b', ...) [90003-187]
! at org.h2.message.DbException.getJdbcSQLException(DbException.java:345) ~[h2-1.4.187.jar:1.4.187]
! at org.h2.message.DbException.get(DbException.java:179) ~[h2-1.4.187.jar:1.4.187]
! at org.h2.message.DbException.get(DbException.java:155) ~[h2-1.4.187.jar:1.4.187]
! at org.h2.util.StringUtils.convertHexToBytes(StringUtils.java:970) ~[h2-1.4.187.jar:1.4.187]
! at org.h2.value.Value.convertTo(Value.java:864) ~[h2-1.4.187.jar:1.4.187]
! at org.h2.table.Column.convert(Column.java:148) ~[h2-1.4.187.jar:1.4.187]
! at org.h2.command.dml.Insert.insertRows(Insert.java:143) ~[h2-1.4.187.jar:1.4.187]
! at org.h2.command.dml.Insert.update(Insert.java:114) ~[h2-1.4.187.jar:1.4.187]
! at org.h2.command.CommandContainer.update(CommandContainer.java:78) ~[h2-1.4.187.jar:1.4.187]
! at org.h2.command.Command.executeUpdate(Command.java:254) ~[h2-1.4.187.jar:1.4.187]
! at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:184) ~[h2-1.4.187.jar:1.4.187]
! at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:158) ~[h2-1.4.187.jar:1.4.187]
! at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:294) ~[liquibase-core-3.1.1.jar:na]
! at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:54) ~[liquibase-core-3.1.1.jar:na]
! ... 16 common frames omitted

However, if I convert the binary array into a hexadecimal string, like "9a122c7c991a41c7bfcbf02586f97293" then I get the following error when I try and import the data into MySQL:

! com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column 'systemUuid' at row 1
! at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4185) ~[mysql-connector-java-5.1.25.jar:na]
! at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4119) ~[mysql-connector-java-5.1.25.jar:na]
! at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2570) ~[mysql-connector-java-5.1.25.jar:na]
! at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2731) ~[mysql-connector-java-5.1.25.jar:na]
! at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2809) ~[mysql-connector-java-5.1.25.jar:na]
! at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2758) ~[mysql-connector-java-5.1.25.jar:na]
! at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:894) ~[mysql-connector-java-5.1.25.jar:na]
! at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:732) ~[mysql-connector-java-5.1.25.jar:na]
! at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:294) ~[liquibase-core-3.1.1.jar:na]
! at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:54) ~[liquibase-core-3.1.1.jar:na]

How am I supposed to store binary arrays in a Liquibase changeset so that both MySQL and H2 can read them?

voor
  • 101
  • 1
  • 5
  • Your problem here is that you column in MySQL is too short. It looks like you are not going in H2, but MySQL when running your test. – pmartin8 Sep 22 '15 at 16:32
  • So maybe I'm not generating the UUID in hex correctly? I'm trying to use th e solution from here: [GUID to ByteArray](https://stackoverflow.com/questions/2983065/guid-to-bytearray) in conjunction with `Hex.encodeHexString()` in `org.apache.commons.codec.binary.Hex` – voor Sep 22 '15 at 18:10
  • No matter how you generate your HEX, your not targetting the right DB, so I would look this problem up before anything else... – pmartin8 Sep 22 '15 at 18:14
  • I'm targeting two databases. The `"[B@..."` format works fine in MySQL, but not in H2. The HEX format works fine in H2, but not in MySQL. – voor Sep 22 '15 at 19:51
  • What's the problem with "[B@..." in H2? – pmartin8 Sep 22 '15 at 19:52
  • See error message above, it tries to interpret it as a Hexadecimal string instead of a binary array. – voor Sep 22 '15 at 23:52

1 Answers1

0

You can set different values depending on the database in your liquibase script. 

<property name="myValue" value="[B@2ba4b32b" dbms="MySQL" />
<property name="myValue" value="9a122c7c991a41c7bfcbf02586f97293" dbms="H2" />

And then, you can use the value using ${myvalue} anywhere in the script.

pmartin8
  • 1,545
  • 1
  • 20
  • 36
  • These values appear in CSV files, and there's hundreds of them, while this might work for 1 or 2 instances, I'm really looking for a more automated solution. – voor Sep 23 '15 at 14:15
  • I know it's a workaround, but how about you have 2 CSV files, and you load the right one according to the DBMS ? – pmartin8 Sep 23 '15 at 14:24