0 Replies Latest reply on Feb 5, 2009 4:17 AM by sridhar reddy

    JDBC Cache loader error

    sridhar reddy Newbie

      Hi All,

      I am very new to Jboss cache and this is my first example tring with jboss cache.

      I would like to load the data from mysql database to jboss cache.

      my code

      public class Test {
       public static void main(String[] args) {
      
       CacheFactory factory = new DefaultCacheFactory();
       Cache cache = factory.createCache("cacheloader.xml");
       cache.create();
       cache.start();
      
       Node rootNode = cache.getRoot();
       System.out.println(rootNode);
       System.out.println(rootNode.getFqn());
      
       }
      
      }


      And my Config file is

      <?xml version="1.0" encoding="UTF-8"?>
      <jbosscache xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn:jboss:jbosscache-core:config:3.0">
      
       <!-- Configure the TransactionManager -->
       <transaction transactionManagerLookupClass="org.jboss.cache.transaction.GenericTransactionManagerLookup"/>
      
       <!-- Specific eviction policy configurations -->
       <eviction wakeUpInterval="5000">
       <!-- Cache wide default -->
       <default algorithmClass="org.jboss.cache.eviction.LRUAlgorithm" eventQueueSize="200000">
       <property name="maxNodes" value="5000"/>
       <property name="timeToLive" value="3000"/>
       </default>
       <region name="/org/jboss/test/data">
       <property name="maxNodes" value="100"/>
       <property name="timeToLive" value="3000"/>
       </region>
       </eviction>
      
       <loaders passivation="false" shared="false">
       <preload>
       <node fqn="/"/>
       </preload>
       <!-- if passivation is true, only the first cache loader is used; the rest are ignored -->
       <loader
       class="org.jboss.cache.loader.JDBCCacheLoader"
       async="false"
       fetchPersistentState="true"
       ignoreModifications="false"
       purgeOnStartup="false">
       <properties>
       cache.jdbc.table.name=jbosscache
       cache.jdbc.table.create=true
       cache.jdbc.table.drop=true
       cache.jdbc.table.primarykey=jbosscache_pk
       cache.jdbc.fqn.column=fqn
       cache.jdbc.fqn.type=VARCHAR(255)
       cache.jdbc.node.column=value
       cache.jdbc.node.type=LONGBLOB
       cache.jdbc.parent.column=null
       cache.jdbc.driver=com.mysql.jdbc.Driver
       cache.jdbc.url=jdbc:mysql://172.16.16.42:3306/jbosscache
       cache.jdbc.user=root
       cache.jdbc.password=root
       </properties>
       </loader>
       </loaders>
      </jbosscache>
      
      


      When i run the code its giving the error
      log4j:WARN No appenders could be found for logger (org.jboss.cache.factories.ComponentRegistry).
      log4j:WARN Please initialize the log4j system properly.
      Exception in thread "main" org.jboss.cache.CacheException: java.lang.reflect.InvocationTargetException
       at org.jboss.cache.util.reflect.ReflectionUtil.invokeAccessibly(ReflectionUtil.java:148)
       at org.jboss.cache.factories.ComponentRegistry$PrioritizedMethod.invoke(ComponentRegistry.java:1005)
       at org.jboss.cache.factories.ComponentRegistry.internalStart(ComponentRegistry.java:775)
       at org.jboss.cache.factories.ComponentRegistry.start(ComponentRegistry.java:629)
       at org.jboss.cache.invocation.CacheInvocationDelegate.start(CacheInvocationDelegate.java:344)
       at org.jboss.cache.DefaultCacheFactory.createCache(DefaultCacheFactory.java:121)
       at org.jboss.cache.DefaultCacheFactory.createCache(DefaultCacheFactory.java:93)
       at org.jboss.cache.DefaultCacheFactory.createCache(DefaultCacheFactory.java:77)
       at Test.main(Test.java:18)
      Caused by: java.lang.reflect.InvocationTargetException
       at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
       at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
       at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
       at java.lang.reflect.Method.invoke(Method.java:597)
       at org.jboss.cache.util.reflect.ReflectionUtil.invokeAccessibly(ReflectionUtil.java:144)
       ... 8 more
      Caused by: org.jboss.cache.CacheException: Unable to start cache loaders
       at org.jboss.cache.loader.CacheLoaderManager.startCacheLoader(CacheLoaderManager.java:467)
       ... 13 more
      Caused by: 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 org.jboss.cache.loader.CacheLoaderManager.startCacheLoader(CacheLoaderManager.java:461)
       ... 13 more
      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)
       ... 17 more
      
      


      What is the problem?
      I tried this in another way too
      I replaced the main method code with following code and still getting the same error.

      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");
      
       IndividualCacheLoaderConfig config = new IndividualCacheLoaderConfig();
       config.setProperties(props);
       JDBCCacheLoader jdbcCacheLoader = null;
      
       jdbcCacheLoader = new JDBCCacheLoader();
       jdbcCacheLoader.setConfig(config);
       try {
       jdbcCacheLoader.start();
       } catch (Exception e) {
       // TODO Auto-generated catch block
       e.printStackTrace();
       }
      


      My table is like
      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;
      


      Please help me.