Problem in JDBC cache loader

[sridhar_ratna]

I have written a small program to load the database contents into jboss cache.

public class Test {

 public static void main(String[] args) {

 //JDBCCacheLoader init
 Properties props = new Properties();
 props.put("cache.jdbc.table.name","jbosscache");
 props.put("cache.jdbc.table.create","false");
 props.put("cache.jdbc.fqn.column","fqn");
 props.put("cache.jdbc.node.column","value");
 props.put("cache.jdbc.driver","com.mysql.jdbc.Driver");
 props.put("cache.jdbc.url","jdbc:mysql://172.16.16.42:3306/jbosscache");
 props.put("cache.jdbc.user","root");
 props.put("cache.jdbc.password","root");
 props.put("cache.jdbc.parent.column","null");

 CacheLoaderConfig.IndividualCacheLoaderConfig config = new CacheLoaderConfig.IndividualCacheLoaderConfig();
 config.setProperties(props);
 JDBCCacheLoader jdbcCacheLoader = null;

 jdbcCacheLoader = new JDBCCacheLoader();
 jdbcCacheLoader.setConfig(config);
 try {
 jdbcCacheLoader.start();
 } catch (Exception e) {
 e.printStackTrace();
 }

 }

I am using the MySQL database and the table structure is

CREATE TABLE `jbosscache`.`jbosscache` (
 `jbosscache_pk` int(10) unsigned NOT NULL auto_increment,
 `fqn` varchar(255) NOT NULL default '',
 `value` longblob NOT NULL,
 PRIMARY KEY (`jbosscache_pk`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

When i run the program it is giving the following error.

log4j:WARN No appenders could be found for logger (org.jboss.cache.loader.JDBCCacheLoaderConfig).
log4j:WARN Please initialize the log4j system properly.
java.lang.IllegalStateException: Failed to insert node: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'null) SELECT '/', null, null FROM jbosscache_D WHERE NOT EXISTS (SELECT fqn FROM' at line 1
 at org.jboss.cache.loader.AdjListJDBCCacheLoader.insertNode(AdjListJDBCCacheLoader.java:540)
 at org.jboss.cache.loader.JDBCCacheLoader.addNewSubtree(JDBCCacheLoader.java:348)
 at org.jboss.cache.loader.JDBCCacheLoader.put(JDBCCacheLoader.java:145)
 at org.jboss.cache.loader.JDBCCacheLoader.start(JDBCCacheLoader.java:380)
 at Test.main(Test.java:63)
Caused by: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'null) SELECT '/', null, null FROM jbosscache_D WHERE NOT EXISTS (SELECT fqn FROM' at line 1
 at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
 at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2985)
 at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1631)
 at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1723)
 at com.mysql.jdbc.Connection.execSQL(Connection.java:3283)
 at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1332)
 at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1604)
 at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1519)
 at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1504)
 at org.jboss.cache.loader.AdjListJDBCCacheLoader.insertNode(AdjListJDBCCacheLoader.java:527)
 ... 4 more

What might be the problem. Can anybody help me to solve the problem.

Thanks in advance

[manik]

set cache.jdbc.table.create to true, and make sure you remove the table in your database. Your table structure is probably wrong.

After JBC generates the tables, you can then consider dropping and recreating the tables with additional settings like engine type.

[sridhar_ratna]

Dear Manik,

I have done the modifications as u suggested and ran the program. It created the table as

DROP TABLE IF EXISTS `jbosscache`.`jbosscache`;
CREATE TABLE `jbosscache`.`jbosscache` (
 `fqn` varchar(255) NOT NULL,
 `value` blob,
 `parent` varchar(255) default NULL,
 PRIMARY KEY (`fqn`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

There is no problem in porting the data from existing table (only 2 columns) into this table. but parent key will be null in my case.

In to the table created by jboss cache i inserted two rows like

insert into jbosscache(fqn,value) values ('1','1');
insert into jbosscache(fqn,value) values ('2','2');

when I ran the same program by adding the following lines of code

System.out.println(jdbcCacheLoader.exists(Fqn.fromString("/")));
System.out.println(jdbcCacheLoader.getChildrenNames(Fqn.fromString("/")));
System.out.println(jdbcCacheLoader.getNodeCount());

its printing on console

true
null
3

Can you please help me in how to traverse thru the elements in the cache.
Please help me

[manik]

The JDBC cache loader was not designed for direct manipulation of the tables using inserts, since it encodes data as byte streams (complete with versioning bits, etc).

If you want to be able to manipulate the tables by hand - or some other process - I would recommend you write your own cache loader implementation, perhaps by extending the JDBCCacheLoader or AbstractCacheLoader.

[sridhar_ratna]

Dear Manik,

Thanks for your suggestion.

Do this original JDBCCacheLoader implementation have any memory based cache or just for database cache only.

When i given a look into the JDBCCacheLoader.java which extends the AdjListJDBCCacheLoader.jav class.

whenever we call get(Fqn name), its inturn calling protected Map loadNode(Fqn name) method.

In this method, it just querying the database and returning the values but its not checking if any key with the given is present in memory.

Please clarify me on this.

Thanks in advance.