Apache Ignite - select query returns 0 records but data exists in cache

Question

We are using Apache Ignite 2.9.0. Its a 5 node cluster with Zookeeper discovery.

We create table in Ignite by executing DDL statements from Intellij. Then we are able to see all data by running select queries from Intellij itself.

Today after wiping Ignite data and recreating tables, some of the tables return 0 records when select queries are run on them. But other applications which use cache API are able to fetch data without any issue.

I checked using Ignite Visor and all tables are there, including ones on which SQL queries fail. When I use sqlline tool also I see those tables. But there also SQL queries return 0 data.

Below is the table which is visible but does not return any data via SQL API.

create TABLE IF NOT EXISTS PRODSCHEMA.TRADESTATS 
(
    TRADERECORDCREATIONDATE varchar(100),
    PRODUCTTYPE varchar(100),
    PRODUCTSUBTYPE varchar(100),
    TRADETYPE varchar(100),
    TOTALIMPACTEDTRADES BIGINT,
    TOTALTRADEOPENEXCEPTION BIGINT,
    TOTALTRADECOUNT BIGINT,
    UPDATEDAT TIMESTAMP,
    PRIMARY KEY (TRADERECORDCREATIONDATE,PRODUCTTYPE,PRODUCTSUBTYPE,TRADETYPE)
) with "template=outputDataCacheTemplate,
    CACHE_NAME=TradeStatsCache,
    AFFINITY_KEY=TRADERECORDCREATIONDATE,
    CACHE_GROUP=TRADE_METADATA_AGGREGATES,
    KEY_TYPE=com.abc.entities.trade.TradeStatsEntityKey,
    VALUE_TYPE=com.abc.entities.trade.TradeStatsEntity";

Template outputDataCacheTemplate exists and other tables also are using that template.

Please help.

Answer 1

The situation

• You know for a fact that your data is there
• You are able to query the data via key-value API
• You can see the SQL tables and they are fine
• You can't see any data in the SQL tables

Possible cause

The most common cause for this in my experience is a mismatch between the actual value data type and the data type SQL expects. This can happen if you load the data using non-SQL ways (key-value, data streamer, CacheStore, etc).

When you put an entry into a cache, Ignite will first add it to the cache itself (available via key-value API). Then it'll try to associate the value with an SQL table. Each cache may have multiple SQL tables associated with it*, and for each table, Ignite compares the entry's value type with the table's value type. If the types match, entry is put into the SQL table.

So, if an entry's type doesn't match any table's type, it won't be a part of the table.

How to diagnose

The good thing is that it's in the logs! Look for the message

Key-value pair is not inserted into any SQL table

How to fix

Well, you'll need to re-upload your data. Find why are your value types aren't matching. Maybe there is a typo in the VALUE_TYPE in the table definition, or maybe you use BinaryObject and provide an incorrect type name there.

One way of making sure that your data is visible via SQL is to use SQL INSERT. That way you know for sure that the data ends up in the table. But please don't read it as a recommendation to always use SQL to write data - other options are equally fine, just need a little bit of care.

---

* Sidenote: I wouldn't recommend mixing multiple tables in a single cache, as well as mixing multiple caches in a single cache group, if you can help it. Systems with 1 table - 1 cache - 1 cache group are much easier to maintain. It is often useful to even have ... - 1 data region to keep things nicely separated.