4 Replies Latest reply on Feb 7, 2009 1:22 PM by sridhar reddy

    Problem in JDBC cache loader

    sridhar reddy Newbie

      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


        • 1. Re: Problem in JDBC cache loader
          Manik Surtani Master

          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.

          • 2. Re: Problem in JDBC cache loader
            sridhar reddy Newbie

            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

            • 3. Re: Problem in JDBC cache loader
              Manik Surtani Master

              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.


              • 4. Re: Problem in JDBC cache loader
                sridhar reddy Newbie

                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.