Hi, we have used this query in ignite 2.3 for a while now. But we had some data streamer exceptions that seemed to have been resolved on 2.4, so we decided to upgrade. However, in 2.6 and also downgrading to 2.4 we have been seeing this issue, that a SQL query does not return the data that is in cache. when we go back to 2.3, it works as expected.
Here is the cache config for that cache.
<bean id="tradeOrderCache"
class="org.apache.ignite.configuration.CacheConfiguration">
<property name="name" value="TradeOrder" />
<property name="readThrough" value="True" />
<property name="cacheMode" value="PARTITIONED" />
<property name="atomicityMode" value="TRANSACTIONAL" />
<property name="backups" value="1" />
<property name="queryDetailMetricsSize" value="512" />
<property name="cacheStoreFactory">
<bean
class="javax.cache.configuration.FactoryBuilder$SingletonFactory">
<constructor-arg>
<ref bean="tradeOrderCacheStore" />
</constructor-arg>
</bean>
</property>
<property name="writeSynchronizationMode" value="FULL_SYNC" />
<property name="statisticsEnabled" value="true" />
<property name="queryEntities">
<list>
<bean id="tradeOrderQueryEntity"
class="org.apache.ignite.cache.QueryEntity">
<property name="keyType"
value="com.tudor.datagridI.client.data.trading.OrderKey" />
<property name="valueType"
value="com.tudor.datagridI.client.data.trading.TradeOrder" />
<property name="fields">
<map>
<entry key="traderId"
value="java.lang.Integer" />
<entry key="orderId"
value="java.lang.Integer" />
<entry key="insIid"
value="java.lang.Integer" />
<entry key="settlement"
value="java.util.Date" />
<entry key="clearAgent"
value="java.lang.String" />
<entry key="strategy"
value="java.lang.String" />
<entry key="pvId"
value="java.lang.Integer" />
<entry key="pvDate"
value="java.util.Date" />
<entry key="linkId"
value="java.lang.Integer" />
<entry key="parentId"
value="java.lang.Integer" />
</map>
</property>
<property name="indexes">
<list>
<bean
class="org.apache.ignite.cache.QueryIndex">
<constructor-arg>
<list>
<value>traderId</value>
<value>orderId</value>
</list>
</constructor-arg>
<constructor-arg>
<value>SORTED</value>
</constructor-arg>
<property
name="name" value="tradeOrder_key_index" />
</bean>
<bean
class="org.apache.ignite.cache.QueryIndex">
<constructor-arg>
<list>
<value>traderId</value>
<value>insIid</value>
<value>clearAgent</value>
<value>strategy</value>
</list>
</constructor-arg>
<constructor-arg>
<value>SORTED</value>
</constructor-arg>
<property
name="name" value="loadTradeOrders_index" />
</bean>
<bean
class="org.apache.ignite.cache.QueryIndex">
<constructor-arg>
<list>
<value>parentId</value>
</list>
</constructor-arg>
<constructor-arg>
<value>SORTED</value>
</constructor-arg>
<property
name="name" value="parentId_index" />
</bean>
</list>
</property>
</bean>
</list>
</property>
</bean>
and here is the query
public List<TradeOrder> getTradeOrdersForPSGroup(Integer traderId, Short
psRuleId, Integer tid, String clearAgent, String strategy, Integer pvId,
Date settlementDate, Date psTime) {
logger.info(String.format("Getting TradeOrders from the cache
for
traderId: %s, tid: %s, clearAgent: %s, strategy: %s, pvId: %s, settlement:
%s, psTime: %s", traderId, tid, clearAgent, strategy, pvId, settlementDate,
psTime));
List<TradeOrder> tradeOrders = new ArrayList<TradeOrder>();
String sqlQuery = "select * from \"TradeOrder\".TradeOrder
where traderId
= ? and insIid = ? and clearAgent = ? and strategy = ? and isnull(pvId,0) =
?";
SqlQuery<OrderKey, TradeOrder> sql = new SqlQuery<OrderKey,
TradeOrder>(TradeOrder.class, sqlQuery).setArgs(traderId, tid, clearAgent,
strategy, pvId);
if(settlementDate != null) {
sqlQuery = sqlQuery + " and isnull(pvDate, settlement)
= ?";
sql = new SqlQuery<OrderKey,
TradeOrder>(TradeOrder.class,
sqlQuery).setArgs(traderId, tid, clearAgent, strategy, pvId,
settlementDate);
}
sql.setTimeout(10, TimeUnit.SECONDS);
SortedSet<OrderKey> keys = new TreeSet<OrderKey>();
try (QueryCursor<Entry<OrderKey, TradeOrder>> cursor =
tradeOrderCache.query(sql)) {
for (Entry<OrderKey, TradeOrder> e : cursor) {
boolean addThisTo = false;
TradeOrder to = e.getValue();
logger.info("Query read this tradeOrder: " +
to.getOrderKey());
for(Trade t: to.getTrades()) {
if(t.getPsTime().compareTo(psTime) >=
0) {
addThisTo = true;
break;
}
}
if (addThisTo) {
//exclude open orders
if
("open".equals(to.getStatus().trim()))
addThisTo = false;
String repoFlag = to.getRepo();
if (repoFlag == null)
repoFlag = "N";
repoFlag = repoFlag.trim();
//open and term repos should also be
excluded
if (repoFlag.equals("T") ||
repoFlag.equals("O"))
addThisTo = false;
if (to.getPsRuleId() != psRuleId)
addThisTo = false;
}
if(addThisTo) {
logger.info("Adding tradeOrder to
psGroup returnList:" +
to.getOrderKey());
keys.add(e.getKey());
tradeOrders.add(to);
}
}
}
/*
* getting all keys to acquire locks on them in a transaction,
* if we do not do this, then another transaction can update
these objects
leading cache in an inconsistent state
*/
tradeOrderCache.getAll(keys);
return tradeOrders;
}
we do a getAll(keys) because we are running this in a transaction and want
to acquire locks. however, I have tried this outside of a transaction as
well. The data is never returned by the SQL.
Do you have any ideas what could be causing this?
Let us know if you need anything else. The code not throw exceptions.
Thanks,
Binti
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
