10 Replies Latest reply on Feb 13, 2009 7:46 AM by M. Atif Riaz

    Getting Failed to insert node

    M. Atif Riaz Newbie

      I am using cache loader but getting following in log
      two syntax errors
      1. while executing creating table
      2. whil executing insert node sql

      insert node sql log
      org.jboss.cache.loader.AdjListJDBCCacheLoader insertNode
      SEVERE: Failed to insert node :A syntax error has occurred.

      query i am getting is

      INSERT INTO jbosscache (fqn, node , parent) SELECT ?, ?, ? FROM jbosscache_D WHERE NOT EXISTS (SELECT fqn FROM jbosscache WHERE fqn = ?)


      help me in resolving the problem

      2. further i think the sql generated by api is not correct (or expected)
      e.g for table creation when using jdbc loader i get following query

      CREATE TABLE jbosscache
      (
      fqn VARCHAR(255) NOT NULL,
      node blob,
      parent VARCHAR(255),
      CONSTRAINT jbosscache_pk PRIMARY KEY (fqn)
      )


      which generates a syntax error

      complete 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"/>
      
      
       <!-- wakeUpInterval: time interval (millis) when the eviction thread kicks in. -->
       <eviction wakeUpInterval="5000">
       <!--
       Cache wide defaults
       default algorithmClass: if an algorithm class is not specified for a region, this one is used by default.
       default eventQueueSize if an event queue size is not specified for a region, this one is used by default.
       -->
       <default algorithmClass="org.jboss.cache.eviction.LRUAlgorithm" eventQueueSize="200000">
       <property name="maxNodes" value="5000" />
       <property name="timeToLive" value="100" />
       </default>
       <region name="/MCP/cards/">
       <property name="maxNodes" value="3000" />
       <property name="minTimeToLive" value="400" />
       </region>
      
       </eviction>
      
      <!-- Cache Passivation for Tree Cache
      On passivation, The objects are written to the backend store on eviction if passivation
      is true, otherwise the objects are persisted. On activation, the objects are restored in
      the memory cache and removed from the cache loader if 'passivation' attribute is true,
      otherwise the objects are only loaded from the cache loader -->
       <loaders passivation="true" shared="true">
       <!-- if passivation is true, only the first cache loader is used; the rest are ignored -->
       <loader
       class="org.jboss.cache.loader.JDBCCacheLoader"
       async="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=node
       cache.jdbc.node.type=blob
       cache.jdbc.parent.column=parent
       cache.jdbc.driver=com.informix.jdbc.IfxDriver
       cache.jdbc.url=jdbc:informix-sqli://<host>:<port1533>/<database>:informixserver=<dbservername>
       cache.jdbc.user=userid
       cache.jdbc.password=password
       </properties>
       </loader>
       </loaders>
      
      
      </jbosscache>
      




      Thanks
      Atif

        • 1. Re: Getting Failed to insert node
          Manik Surtani Master

          we haven't tested this with informix.

          Does the insert statement fail when you attempt to type in directly into an informix console? What do the existing tables look like?

          • 2. Re: Getting Failed to insert node
            M. Atif Riaz Newbie

            yes both statements (create table and insert) generates a syntax error when tried through a console

            tables are as

            store table

            CREATE
             TABLE jbosscache
             (
             fqn VARCHAR(255) NOT NULL,
             node BLOB,
             parent VARCHAR(255),
             PRIMARY KEY (fqn)
             )
            


            dummy table

            CREATE
             TABLE jbosscache_d
             (
             i CHAR(1)
             )



            Thanks
            Atif

            • 3. Re: Getting Failed to insert node
              Manik Surtani Master

              I've raised a bug. https://jira.jboss.org/jira/browse/JBCACHE-1477

              1) Could you pls paste the error message you get from the DB?
              2) Also, is it only the insert SQL that fails?
              3) Why do you say the create table DDL is incorrect? Does this fail when run on the DB directly?

              • 4. Re: Getting Failed to insert node
                M. Atif Riaz Newbie

                than you for your follow up

                1) I am getting following error repeatedly whenever the evicted node is about to be persisted to store (DB)

                Feb 12, 2009 10:47:49 AM org.jboss.cache.loader.AdjListJDBCCacheLoader insertNode
                SEVERE: Failed to insert node :A syntax error has occurred.
                


                2) I have checked and following SQL fails (syntax errors) marked in red

                deleteNodeSql = DELETE FROM jbosscache WHERE fqn = ?
                selectChildNamesSql = SELECT fqn FROM jbosscache WHERE parent = ?
                deleteAllSql = DELETE FROM jbosscache
                
                selectChildFqnsSql = SELECT fqn FROM jbosscache WHERE parent = ?
                
                insertNodeSql = INSERT INTO jbosscache (fqn, node , parent) SELECT ?, ?, ? FROM jbosscache_D WHERE NOT EXISTS (SELECT fqn FROM jbosscache WHERE fqn = ?)
                
                
                updateNodeSql = UPDATE jbosscache SET node = ? WHERE fqn = ?
                selectNodeSql = SELECT node FROM jbosscache WHERE fqn = ?
                
                createTableDDL = CREATE TABLE jbosscache(fqn VARCHAR(255) NOT NULL, node blob, parent VARCHAR(255), CONSTRAINT jbosscache_pk PRIMARY KEY (fqn))
                
                dropTableDDL = DROP TABLE jbosscache
                


                3) Actually i have quoted the correct DDL, create table generating syntax error is
                createTableDDL = CREATE TABLE jbosscache(fqn VARCHAR(255) NOT NULL, node blob, parent VARCHAR(255), CONSTRAINT jbosscache_pk PRIMARY KEY (fqn))
                


                It is primary key constraint syntax which is generating error

                Regards
                Atif

                • 5. Re: Getting Failed to insert node
                  Manik Surtani Master

                  After installing Informix (whew!!) and giving it a go, I see your problem with the DDL. I have fixed this in trunk and have included info on a workaround on the JIRA. Please let me know if you see any other issues.

                  • 6. Re: Getting Failed to insert node
                    M. Atif Riaz Newbie

                    Have you checked the insert SQL also?

                    Conceptual Understanding
                    Cache Loader maintains evicted data from memory in a persistent store (DB, file system).
                    Does caching solutions offer synchronization between actual source of data (mostly DB) and in memory data (cache)? E.g. I am caching data from a table card_accounts then does caching API offers synchronization between this table and cache (if data changes in table then it automatically changes in memory and vice versa)

                    • 7. Re: Getting Failed to insert node
                      M. Atif Riaz Newbie

                      Have you checked the insert SQL also?

                      Conceptual Understanding

                      Cache Loader maintains evicted data from memory in a persistent store (DB, file system).
                      Does caching solutions offer synchronization between actual source of data (mostly DB) and in memory data (cache)? E.g. I am caching data from a table card_accounts then does caching API offers synchronization between this table and cache (if data changes in table then it automatically changes in memory and vice versa)




                      • 8. Re: Getting Failed to insert node
                        Mircea Markus Master

                        The cache loader will modify the data in the store (database in your scenario) when in memory data gets changed. What it won't do is update in-memry data whenever data gets modified in the database. The way the data from DB is updated when an in memory change is made is influenced by the "passivation" attribute of the "loaders" config element.

                        • 9. Re: Getting Failed to insert node
                          Manik Surtani Master

                           

                          "atifoxon" wrote:
                          Have you checked the insert SQL also?


                          I thought you said it was just the create table DDL that was broken. Have you tried it with my workaround? Could just be that your table structure was broken and hence the other SQL not working.

                          • 10. Re: Getting Failed to insert node
                            M. Atif Riaz Newbie

                             

                            "manik.surtani@jboss.com" wrote:
                            "atifoxon" wrote:
                            Have you checked the insert SQL also?


                            I thought you said it was just the create table DDL that was broken. Have you tried it with my workaround? Could just be that your table structure was broken and hence the other SQL not working.


                            Yes i have checked it after creating table manually but insert SQL is still generating syntax error

                            "mircea.markus" wrote:

                            The cache loader will modify the data in the store (database in your scenario) when in memory data gets changed. What it won't do is update in-memry data whenever data gets modified in the database. The way the data from DB is updated when an in memory change is made is influenced by the "passivation" attribute of the "loaders" config element.


                            I am talking about the real database and not db acting as cache loader store. The scenario I am referring to is how dirty data marker concepts operates, whenever data get modified it is reflected in dirty data marker in db, so when requesting application see this dirty data marker set, it refresh its cached data.

                            Does API provides such sort of synchronization: like if cache data is populated from let say joining two different tables (card_accounts, card_profile)