JDBC Migrator or: How I Learned to Stop Worrying About Buckets and Utilise the JdbcStringBasedStore!

Infinispan 9 has introduced many improvements to its marshalling codebase in order to improve performance and allow for greater flexibility. Consequently, data marshalled and persisted by Infinispan 8.x is no longer compatible with Infinispan 9.x. Furthermore, as part of our ongoing efforts to improve the cache stores provided by Infinispan, we have removed both the JdbcBinaryStore and JdbcMixedStore in Infinispan 9.0.

To assist users migrating from Infinispan 8.x, we have created the JDBC Migrator that enables existing JDBC stores to be migrated to Infinispan 9's JdbcStringBasedStore.

No More Binary Keyed Stores!

The original intention of the JdbcBinaryStore was to provide greater flexibility over the JdbcStringBasedStore as it did not require a Key2StringMapper implementation.  This was achieved by utilising the hashcode of an entries key for a table's ID column entry.  However, due to the possibility of hash collisions all entries had to be placed inside a Bucket object which was then serialised and inserted into the underlying table. Utilising buckets in this manner was far from optimal as each read/write to the underlying table required an existing bucket for a given hash to be retrieved, deserialised, updated, serialised and then re-inserted back into the db.

Introducing JDBC Migrator

The JDBCMigrator is a standalone application that takes a single argument, the path to a .properties file which must contain the configuration properties for both the source and target stores.  To use the migrator you need the infinispan-tools-9.x.jar, as well as the jdbc drivers required by your source and target databases, on your classpath.

An example maven pom that launches the migrator via mvn exec:java is presented below:

	<?xml version="1.0" encoding="UTF-8"?>
	<project xmlns="http://maven.apache.org/POM/4.0.0"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<groupId>org.infinispan.example</groupId>
	<artifactId>jdbc-migrator-example</artifactId>
	<version>1.0-SNAPSHOT</version>
	<dependencies>
	<dependency>
	<groupId>org.infinispan</groupId>
	<artifactId>infinispan-tools</artifactId>
	<version>9.0.0.CR3</version>
	</dependency>
	<!-- ADD YOUR REQUIRED JDBC DEPENDENCIES HERE -->
	</dependencies>
	<build>
	<plugins>
	<plugin>
	<groupId>org.codehaus.mojo</groupId>
	<artifactId>exec-maven-plugin</artifactId>
	<version>1.2.1</version>
	<executions>
	<execution>
	<goals>
	<goal>java</goal>
	</goals>
	</execution>
	</executions>
	<configuration>
	<mainClass>org.infinispan.tools.jdbc.migrator.JDBCMigrator</mainClass>
	<arguments>
	<argument><!-- PATH TO YOUR MIGRATOR.PROPERTIES FILE --></argument>
	</arguments>
	</configuration>
	</plugin>
	</plugins>
	</build>
	</project>

view raw JDBCMigrator.xml hosted with ❤ by GitHub

Migration Examples

Below are several example .properties files used for migrating various stores, however an exhaustive list of all available properties can be found in the Infinispan user guide.  

Before attempting to migrate your existing stores please ensure you have backed up your database!

8.x JdbcBinaryStore -> 9.x JdbcStringBasedStore

The most important property to set in this example is "source.marshaller.type=LEGACY" as this instructs the migrator to utilise the Infinispan 8.x marshaller to unmarshall data stored in your existing DB tables. 

If you specified custom AdvancedExternalizer implementations in your Infinispan 8.x configuration, then it is necessary for you to specify these in the migrator configuration and ensure that they are available on the migrators classpath.  To Specify the AdvancedExternalizers to load, it is necessary to define the "source.marshaller.externalizers" property with a comma-separated list of class names. If an ID was explicitly set for your externalizer, then it is possible to prepend the externalizers class name with "<id>:" to ensure the IDs is respected by the marshaller. 

	# Configuration of the source store that we are migrating from
	source.type=BINARY # Type of source store enum. [MIXED, BINARY, STORE]
	source.marshaller.type=LEGACY # Marshaller type enum. [CURRENT, LEGACY, CUSTOM]
	source.externalizers=25:Externalizer1,org.example.Externalizer2 # List of externalizer implementations
	source.cache_name=source_cache # Cache name of source store
	source.dialect=POSTGRES # DB dialect of source store
	# Database connection settings
	source.connection_pool.connection_url=jdbc:postgresql:postgres
	source.connection_pool.driver_class=org.postgresql.Driver
	source.connection_pool.username=postgres
	source.connection_pool.password=redhat
	# Database Binary Table settings
	source.table.binary.table_name_prefix=binary_table
	source.table.binary.binary.id.name=id_column
	source.table.binary.id.type=VARCHAR
	source.table.binary.data.name=datum_column
	source.table.binary.data.type=bytea
	source.table.binary.timestamp.name=timestamp_column
	source.table.binary.timestamp.type=BIGINT
	# Configuration of the target store that we are migrating to
	target.type=STRING # For target stores STRING is the only option
	target.cache_name=target_cache
	target.dialect=POSTGRES
	target.connection_pool.connection_url=jdbc:postgresql:postgres
	target.connection_pool.driver_class=org.postgresql.Driver
	target.connection_pool.username=postgres
	target.connection_pool.password=redhat
	target.table.string.table_name_prefix=string_table
	target.table.string.id.name=id_column
	target.table.string.id.type=VARCHAR
	target.table.string.data.name=datum_column
	target.table.string.data.type=bytea
	target.table.string.timestamp.name=timestamp_column
	target.table.string.timestamp.type=BIGINT
	target.key_to_string_mapper=org.infinispan.persistence.keymappers.DefaultTwoWayKey2StringMapper

view raw example-jdbcmigrator.properties hosted with ❤ by GitHub

TwoWayKey2StringMapper Migration

As well as using the JDBC Migrator to migrate from Infinispan 8.x, it is also possible to utilise it to migrate from one DB dialect to another or to migrate from one TwoWayKey2StringMapper implementation to another. 

	# Configuration of the source store that we are migrating from
	source.type=STRING
	source.marshaller.type=CURRENT
	source.cache_name=source_cache
	source.dialect=POSTGRES
	source.connection_pool.connection_url=jdbc:postgresql:postgres
	source.connection_pool.driver_class=org.postgresql.Driver
	source.connection_pool.username=postgres
	source.connection_pool.password=redhat
	source.table.string.table_name_prefix=string_table
	source.table.string.id.name=id_column
	source.table.string.id.type=VARCHAR
	source.table.string.data.name=datum_column
	source.table.string.data.type=bytea
	source.table.string.timestamp.name=timestamp_column
	source.table.string.timestamp.type=BIGINT
	source.key_to_string_mapper=org.infinispan.persistence.keymappers.DefaultTwoWayKey2StringMapper
	# Configuration of the target store that we are migrating to
	target.type=STRING
	target.cache_name=target_cache
	target.dialect=POSTGRES
	target.connection_pool.connection_url=jdbc:postgresql:postgres
	target.connection_pool.driver_class=org.postgresql.Driver
	target.connection_pool.username=postgres
	target.connection_pool.password=redhat
	target.table.string.table_name_prefix=another_string_table
	target.table.string.id.name=id_column
	target.table.string.id.type=VARCHAR
	target.table.string.data.name=datum_column
	target.table.string.data.type=bytea
	target.table.string.timestamp.name=timestamp_column
	target.table.string.timestamp.type=BIGINT
	target.key_to_string_mapper=org.example.keymapper.CustomerKeyMapper # Different mapper class

view raw string-store-migrator.properties hosted with ❤ by GitHub

Summary

Infinispan 9 stores are no longer compatible with Infinispan 8.x stores due to internal marshalling changes. Furthermore, the JdbcBinary and JdbcMixed stores have been removed due to their poor performance characteristics.  To aid users in their transition from Infinispan 8.x we have created the JDBC Migrator to enable users to migrate their existing JDBC stores.

If you're a user of the JDBC stores and have any feedback on the latest changes, let us know via the forum, issue tracker or the #infinispan channel on Freenode.