Data truncation: Data too long for column 'art'

[BlvckBytes]

Greetings!

From time to time, the following database-error shows up in our console, indicating that an SQL-command could not be executed successfully.

[15:57:23 WARN]: java.sql.BatchUpdateException: Data truncation: Data too long for column 'art' at row 1
[15:57:23 WARN]:        at com.mysql.cj.jdbc.exceptions.SQLError.createBatchUpdateException(SQLError.java:214)
[15:57:23 WARN]:        at com.mysql.cj.jdbc.ClientPreparedStatement.executeBatchWithMultiValuesClause(ClientPreparedStatement.java:784)
[15:57:23 WARN]:        at com.mysql.cj.jdbc.ClientPreparedStatement.executeBatchInternal(ClientPreparedStatement.java:457)
[15:57:23 WARN]:        at com.mysql.cj.jdbc.StatementImpl.executeBatch(StatementImpl.java:858)
[15:57:23 WARN]:        at CoreProtect-23.3.jar//net.coreprotect.hikari.pool.ProxyStatement.executeBatch(ProxyStatement.java:127)
[15:57:23 WARN]:        at CoreProtect-23.3.jar//net.coreprotect.hikari.pool.HikariProxyPreparedStatement.executeBatch(HikariProxyPreparedStatement.java)
[15:57:23 WARN]:        at CoreProtect-23.3.jar//net.coreprotect.consumer.process.Process.commit(Process.java:303)
[15:57:23 WARN]:        at CoreProtect-23.3.jar//net.coreprotect.consumer.process.Process.processConsumer(Process.java:258)
[15:57:23 WARN]:        at CoreProtect-23.3.jar//net.coreprotect.consumer.Consumer.run(Consumer.java:133)
[15:57:23 WARN]:        at java.base/java.lang.Thread.run(Thread.java:1583)
[15:57:23 WARN]: Caused by: com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Data too long for column 'art' at row 1
[15:57:23 WARN]:        at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:96)
[15:57:23 WARN]:        at com.mysql.cj.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:612)
[15:57:23 WARN]:        at com.mysql.cj.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:320)
[15:57:23 WARN]:        at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1168)
[15:57:23 WARN]:        at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1103)
[15:57:23 WARN]:        at com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1450)
[15:57:23 WARN]:        at com.mysql.cj.jdbc.ClientPreparedStatement.executeBatchWithMultiValuesClause(ClientPreparedStatement.java:745)
[15:57:23 WARN]:        ... 8 more

Seeing how co_art_map is the only table containing an art-column, it looks like the plugin is trying to insert a string that exceeds the limit of 255 characters; depending on the complexity of the input, maybe TEXT would be a more appropriate column-type here?

==== co_art_map ====
Field	Type	Null	Key	Default	Extra
rowid	int(11)	NO	PRI	NULL	auto_increment
id	int(11)	YES	MUL	NULL	
art	varchar(255)	YES		NULL	

[Intelli]

What is the truncated data?

[BlvckBytes]

If only I knew - that is all the plugin logged.

[BlvckBytes]

Here you go - I've added a custom log to get a hold of the truncated data; also, it only happens when using MariaDB - SQLite does not seem to care about column-length.

diff --git a/src/main/java/net/coreprotect/consumer/process/ArtInsertProcess.java b/src/main/java/net/coreprotect/consumer/process/ArtInsertProcess.java
index 1479f7f..21c493a 100644
--- a/src/main/java/net/coreprotect/consumer/process/ArtInsertProcess.java
+++ b/src/main/java/net/coreprotect/consumer/process/ArtInsertProcess.java
@@ -13,6 +13,7 @@ class ArtInsertProcess {
 
     static void process(PreparedStatement preparedStmt, Statement statement, int batchCount, Object name, int materialId) {
         if (name instanceof String) {
+            Chat.console("Logging art name=\"" + name + "\"");
             String query = "SELECT id FROM " + ConfigHandler.prefix + "art_map WHERE id = '" + materialId + "' LIMIT 0, 1";
             boolean hasMaterial = MaterialStatement.hasMaterial(statement, query);
             if (!hasMaterial) {

[18:48:33 INFO]: [CoreProtect] Logging art name="craftart{holder=reference{resourcekey[minecraft:painting_variant / minecraft:burning_skull]=paintingvariant[width=4, height=4, assetid=minecraft:burning_skull, title=optional[translation{key='painting.minecraft.burning_skull.title', args=[]}[style={color=yellow}]], author=optional[translation{key='painting.minecraft.burning_skull.author', args=[]}[style={color=gray}]]]}}"
[18:48:34 WARN]: java.sql.BatchUpdateException: Data truncation: Data too long for column 'art' at row 1
[18:48:34 WARN]: 	at com.mysql.cj.jdbc.exceptions.SQLError.createBatchUpdateException(SQLError.java:214)
[18:48:34 WARN]: 	at com.mysql.cj.jdbc.ClientPreparedStatement.executeBatchWithMultiValuesClause(ClientPreparedStatement.java:784)
[18:48:34 WARN]: 	at com.mysql.cj.jdbc.ClientPreparedStatement.executeBatchInternal(ClientPreparedStatement.java:457)
[18:48:34 WARN]: 	at com.mysql.cj.jdbc.StatementImpl.executeBatch(StatementImpl.java:858)
[18:48:34 WARN]: 	at CoreProtect-23.0.jar//net.coreprotect.hikari.pool.ProxyStatement.executeBatch(ProxyStatement.java:127)
[18:48:34 WARN]: 	at CoreProtect-23.0.jar//net.coreprotect.hikari.pool.HikariProxyPreparedStatement.executeBatch(HikariProxyPreparedStatement.java)
[18:48:34 WARN]: 	at CoreProtect-23.0.jar//net.coreprotect.consumer.process.Process.commit(Process.java:303)
[18:48:34 WARN]: 	at CoreProtect-23.0.jar//net.coreprotect.consumer.process.Process.processConsumer(Process.java:258)
[18:48:34 WARN]: 	at CoreProtect-23.0.jar//net.coreprotect.consumer.Consumer.run(Consumer.java:133)
[18:48:34 WARN]: 	at java.base/java.lang.Thread.run(Thread.java:1575)
[18:48:34 WARN]: Caused by: com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Data too long for column 'art' at row 1
[18:48:34 WARN]: 	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:96)
[18:48:34 WARN]: 	at com.mysql.cj.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:612)
[18:48:34 WARN]: 	at com.mysql.cj.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:320)
[18:48:34 WARN]: 	at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1168)
[18:48:34 WARN]: 	at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1103)
[18:48:34 WARN]: 	at com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1450)
[18:48:34 WARN]: 	at com.mysql.cj.jdbc.ClientPreparedStatement.executeBatchWithMultiValuesClause(ClientPreparedStatement.java:745)
[18:48:34 WARN]: 	... 8 more

This value has 374 characters and thus obviously exceeds the constraint of 255. In order to reproduce the issue, simply configure an empty SQL database and place a vanilla artwork (used burning skull here).

[BlvckBytes]

Would you be okay with this column becoming of type TEXT? I could create a pull-request that automatically migrates this table after schema-creation, which is probably the most straight forward way of getting rid of these issues. My console keeps on getting spammed, and I cannot possibly be the only one encountering this behaviour.

[Intelli]

Haven’t had a chance to check yet, but I believe it’s supposed to be just logging a short string like “burning_skull”, rather than the full craftart string.

But I’ll have to check what actually works for rollbacks.

[BlvckBytes]

I've logged the exact value your plugin is trying to write to the database, so I do not see how there's much of a debate regarding its length. This is to be expected, since it's the .toString() of a bukkit Art-instance. If you want it to be more concise, access the namespaced key.

I also mentioned how to easily reproduce the exception. For now, I manually changed the column-type on the database instance, seeing how it doesn't look like issues like these are given any priority whatsoever, despite the fact that the user of this plugin who I am helping out with this predicament has donated on Patreon. Well, great stuff ¯\_(ツ)_/¯.

[Diamondback88]

Can confirm this has happened to me as well

[BlvckBytes]

Can confirm this has happened to me as well

The quickest way to circumvent this obvious bug is to alter the table manually yourself, using the sql-shell:

ALTER TABLE co_art_map ALTER COLUMN art TEXT;

The above assumes that your table-prefix is co_, which is the default; adjust as needed.

[Diamondback88]

Can confirm this has happened to me as well

The quickest way to circumvent this obvious bug is to alter the table manually yourself, using the sql-shell:

ALTER TABLE co_art_map ALTER COLUMN art TEXT;

The above assumes that your table-prefix is co_, which is the default; adjust as needed.

What does this do, exactly? Any side effects for logging?

[BlvckBytes]

What does this do, exactly? Any side effects for logging?

No, none at all! We've been using the plugin perfectly fine with this modification applied. All it does is to extend the column art of the table co_art_map from VARCHAR(255) to TEXT, which can now hold up to 65535 characters, instead of the mere 255, which is overflown by the output of modern Bukkit artworks.