Getting Failed to insert node

[atifoxon]

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

[manik]

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?

[atifoxon]

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

[manik]

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?

[atifoxon]

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

[manik]

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.

[atifoxon]

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)

[atifoxon]

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)

[mircea.markus]

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.

[manik]

"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.

[atifoxon]

"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)