byteArray doesn't work right

[matthew-lucidchart]

Not sure the exact issue here, but it's 100% reproducible, and doesn't work as expected.

Using MySQL:

CREATE TABLE `broken` (
  `id` bigint(20) NOT NULL,
  `properties` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `broken` VALUES (1, "Implementação");

Using Relate:

val row = SQL("""SELECT * FROM `broken`""").asSingle(RowParser { row =>
    (row.long("id"), row.byteArray("properties")
})(connection)

SQL("""INSERT INTO `broken` VALUES ({id}, {properties})""").on { implicit stmt =>
    long("id", row._1 + 1)
    byteArray("properties", row._2)
}

Notice that the properties column was both retrieved and stored as a byteArray. This only seems to happen if the column in MySQL is a LONGTEXT. A simple fix is to do this:

val row = SQL("""SELECT * FROM `broken`""").asSingle(RowParser { row =>
    (row.long("id"), row.string("properties")
})(connection)

SQL("""INSERT INTO `broken` VALUES ({id}, {properties})""").on { implicit stmt =>
    long("id", row._1 + 1)
    byteArray("properties", row._2.getBytes())
}

Here is the stack trace when the first bit of code is run:

java.sql.SQLException: Incorrect string value: '\xE7\xE3o do...' for column 'properties' at row 1
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073) ~[mysql.mysql-connector-java-5.1.18.jar:na]
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3609) ~[mysql.mysql-connector-java-5.1.18.jar:na]
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3541) ~[mysql.mysql-connector-java-5.1.18.jar:na]
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2002) ~[mysql.mysql-connector-java-5.1.18.jar:na]
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2163) ~[mysql.mysql-connector-java-5.1.18.jar:na]
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2624) ~[mysql.mysql-connector-java-5.1.18.jar:na]
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2127) ~[mysql.mysql-connector-java-5.1.18.jar:na]
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2427) ~[mysql.mysql-connector-java-5.1.18.jar:na]
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2345) ~[mysql.mysql-connector-java-5.1.18.jar:na]
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2330) ~[mysql.mysql-connector-java-5.1.18.jar:na]
    at com.jolbox.bonecp.PreparedStatementHandle.executeUpdate(PreparedStatementHandle.java:203) ~[com.jolbox.bonecp-0.7.1.RELEASE.jar:0.7.1.RELEASE]
    at com.lucidchart.open.relate.StatementPreparer$class.executeUpdate(StatementPreparer.scala:55) ~[com.lucidchart.relate_2.10-1.5.jar:1.5]
    at com.lucidchart.open.relate.NormalStatementPreparer.executeUpdate(StatementPreparer.scala:76) ~[com.lucidchart.relate_2.10-1.5.jar:1.5]
    at com.lucidchart.open.relate.Sql$class.executeUpdate(SqlQuery.scala:276) ~[com.lucidchart.relate_2.10-1.5.jar:1.5]
    at com.lucidchart.open.relate.SqlQuery.executeUpdate(SqlQuery.scala:29) ~[com.lucidchart.relate_2.10-1.5.jar:1.5]

[pauldraper]

Perhaps it is due to this:

In MySQL Server 4.1 and higher, Connector/J supports a single character encoding between client and server, and any number of character encodings for data returned by the server to the client in ResultSets.

I believe that adding character_set_server=utf8mb4 to the connection string (or in the server config) will fix this.

[matthew-lucidchart]

well... that's stupid. but makes sense.