Wednesday, April 13, 2011

Getting started with Cluster/J - inserts

Cluster/J is a the Java direct API to MySQL Cluster which means it bypasses the MySQL Server and executes requests directly on the data nodes of MySQL Cluster. This gives very good performance (typically >2x faster than SQL) and low latency.

In this blog I will present how to get started and how to do simple lookups.

The complete source code (actually a bit extended to show batching) for this example can be found at Severalnines (here). The code you will see below is just snippets.

Environment

To start with you need MySQL Cluster up and running.
For development on localhost then you can get a sandbox from Severalnines.
If you want to have a test/production environment you should you the Configurator for MySQL Cluster.

First of all you have to import the following Cluster/J jars into your project.
In Eclipse, on the project, do "Properties -> Java Build Path --> Add External JARs"
The JARs you need to import are typically located in /usr/local/mysql/share/java/ (if you have installed MySQL Cluster in this location - this is the default location used by Severalnines).

You also need to set the JVM configuration in order to point out the libndbclient library :
-Djava.library.path=/usr/local/mysql/lib/ -Xms128m -Xmx512m

Create the table


The first thing we should do is to create the table we need for this example:
CREATE TABLE `my_data` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`data` varbinary(255) DEFAULT NULL,
`last_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`) ) ENGINE=ndbcluster DEFAULT CHARSET=latin1

Mapping table to Java Interface
An interfae describing each table is needed. You annotate the code with @PrimaryKey and @Column do denote if the names of columns and the column names.
public interface MyData {     

@PrimaryKey
long getId();
void setId(long i);

@Column(name = "data")
byte[] getData();
void setData(byte[] b);

@Column(name = "last_updated")
long getLastUpdated();
void setLastUpdated(long ts);
}
Setting up the connection to MySQL Cluster

Properties p = new Properties();
p.setProperty("com.mysql.clusterj.connectstring", "localhost:1186");
p.setProperty("com.mysql.clusterj.database", "test");
SessionFactory sf=ClusterJHelper.getSessionFactory(p);
Creating a Session

The SessionFactory provides Sessions. On a session instance you can do a couple of things, e.g, makePersistent, updatePersistent, and find.
/**
* Sessions are not thread safe!
*/
Session s = sf.getSession();

Performing an insert

Populate the fields in the object, and make it persistent.
MyData my_data=s.newInstance(MyData.class);
/**
* Set the data on the object
*/

my_data.setId(i);
my_data.setData(data);
my_data.setLastUpdated(System.currentTimeMillis());
/**
* Persist the object */
s.makePersistent(my_data);
That is it, if you run the example code, you will see the output.
Done inserting 1000 records in 230 ms

5 comments:

Anonymous said...

Could you please explain why do you set identifier explicitly: {my_data.setId(i);} while AUTO_INCREMENT is set for the id column. Thanks.

Johan Andersson said...

Hi,

that is a good question. I have never tested without. In the C++ NDBAPI you have to explicitly set the autoincrements yourself. I think that is why i set it here..

It would be great if you can take out that line and see if it works. If it doesn't let me know here and I can see what options there are to deal with auto increments in clusterj.

Johan Andersson said...

Hi Anonymous,
UPDATE: I have looked at the clusterj code and I can't find it supports auto increment :(

-j

Anonymous said...

Hi, i have faced this message while trying it without setting an id:

Error in NdbJTie: returnCode -1, code 630, mysqlCode 121, status 2, classification 3, message Tuple already existed when attempting to insert .
com.mysql.clusterj.ClusterJDatastoreException: Error in NdbJTie: returnCode -1, code 630, mysqlCode 121, status 2, classification 3, message Tuple already existed when attempting to insert .
at com.mysql.clusterj.tie.Utility.throwError(Utility.java:752) ~[clusterj-7.1.15.jar:na]....

The system is trying to insert entity with id=0, and if row with the same value already exists, we can see the message above.

I think it might be some annotation added to notify ClusterJ that value is automatically generated, like @GeneratedValue in JPA.

Is there any way to be notified about the status of this issue? Will really appreciate this.

DeepakD said...

We are using MySQL cluster based solution and have implemented some DB Inserts using ClusterJ.

Versin Info: Server version: 5.1.56-ndb-7.1.19-cluster-gpl

Problem we are facing is that some inserts (done using Session.persist() API) do not actually get inserted and there is no exception about it. This is making it very difficult to implement any error handling.

Any suggestions would be highly welcomed.