Tested and recommended database settings for RHDG String-keyed-jdbc-store persistence

Environment

• Red Hat Data Grid (RHDG)
  • 8.x

Issue

• What column types are recommended for persistence with string-keyed-jdbc-store?
• The cache can not load entries from a string-keyed-jdbc-store using MYSQL with data type BINARY. (Note the ERROR can be slightly different based on the version or configuration)

ERROR [org.infinispan.interceptors.impl.InvocationContextInterceptor] ISPN000136: Error executing command GetKeyValueCommand on Cache 'db', writing keys [] org.infinispan.persistence.spi.PersistenceException: I/O error while unmarshalling from stream
    at org.infinispan.persistence.jdbc.stringbased.JdbcStringBasedStore.unmarshall(JdbcStringBasedStore.java:777)
    ...
Caused by: protostream.com.google.protobuf.InvalidProtocolBufferException: Protocol message contained an invalid tag (zero).
    at protostream.com.google.protobuf.InvalidProtocolBufferException.invalidTag(InvalidProtocolBufferException.java:105)
    ...

• The cache can not store entries to a string-keyed-jdbc-store. (Note the ERROR can be slightly different based on the database)

ERROR [org.infinispan.server.hotrod.BaseRequestProcessor] ISPN005003: Exception reported org.infinispan.persistence.spi.PersistenceException: Error while storing string key to database; key: 'WrappedByteArray[\J05\t\e\s\t\1 (7 bytes)]'
	at org.infinispan.persistence.jdbc.common.impl.BaseJdbcStore.lambda$write$4(BaseJdbcStore.java:149)
	...
Caused by: com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Data too long for column 'DATA_COLUMN' at row 1
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:104)
        ...

Resolution

To prevent this issue it is recommended to use the tested database data types shown below.
Also the lenght of a data type, i.e. VARBINARY(#) where # must be replaced with the length, is important and should match the requirements.

If a different database is used or a different column type is wanted it is highly recommended to do the following tests

• Configure the datasource and jdbc-store as wanted
• Add entries which include the desired objects and check there is no failure during write
• check the admin console whether it shows no issues
• Check with a new/restarted client whether the entry is readable
• Restart the server to ensure the database is used to restore the cache
• Start a new client and check whether the entries are read correctly

Tested column types

Root Cause

The string-keyed-jdbc-store needs to be configured with a proper data type for the different columns.
Otherwise it could lead to failures during cache.put(...) operations or, more serious, a put(...) will work but a get will lead to Exceptions during load from the persistence after passivation or server restart which cause data loss.

Also it is possible that there is no error message if the data is truncated because of a large enrties if the DB column does not configure a proper lenght to store the key or value. That depends on the database and its JDBC driver.

As example a MySQL or MariaDB data column type BINARY the content is padded with 0x00 to a specified lenght, in this case Infinispan can not read correctly.

This solution is part of Red Hat’s fast-track publication program, providing a huge library of solutions that Red Hat engineers have created while supporting our customers. To give you the knowledge you need the instant it becomes available, these articles may be presented in a raw and unedited form.