Search code examples
javainsertkeyignite

Exception while inserting a record in to Apache Ignite Cluster using JDBC connection

I've been struggling to run an insert query on Apache Ignite Cache using JDBC connection. I've created my Cache using the following configuration:

CacheConfiguration<Long, MyApplication> cacheCfg = new CacheConfiguration<>("SQL_PUBLIC_MYAPPLICATION");
cacheCfg.setCacheMode(CacheMode.REPLICATED);
cacheCfg.setIndexedTypes(Long.class, MyApplication.class);
cacheCfg.setSqlSchema("PUBLIC");
cacheCfg.setCacheStoreFactory(FactoryBuilder.factoryOf(CacheMyApplicationStore.class));
cacheCfg.setReadThrough(true);
cacheCfg.setWriteThrough(true);
FifoEvictionPolicy<Long, MyApplication> eviction = new FifoEvictionPolicy<Long, MyApplication>();
eviction.setMaxSize(100_000);
IgniteCache<Long, MyApplication> cache = getIgnite().getOrCreateCache(cacheCfg);
cache.loadCache(null, 100_000);

MyApplication.java

public class MyApplication {

    @QuerySqlField(index = true,orderedGroups = {@QuerySqlField.Group(name = "idx3", order = 0)})
    private Long ID;

    @QuerySqlField(index = true,orderedGroups = {@QuerySqlField.Group(name = "idx3", order = 1)})
    private String NAME;

    @QuerySqlField
    private String DESCRIPTION;
}

I was able to update or delete a record in the Cache using JDBC connection but the insert is not working. I'm getting an exception when I try to insert using the following code:

Connection conn = DriverManager.getConnection("jdbc:ignite:thin://url:4000");

try (PreparedStatement stmt = conn.prepareStatement("insert into MyApplication (ID, NAME, DESCRIPTION) values (1, 'TestApp', 'To be deleted')")) {
    stmt.setLong(1, 1L);
    stmt.setString(2, "TestApp");
    stmt.setString(3, "To be deleted");
    stmt.executeUpdate();
}

Exception:

[17:15:14,894][ERROR][sql-connector-#58%agrid%][JdbcRequestHandler] Failed to execute SQL query [reqId=1, req=JdbcQueryExecuteRequest [schemaName=null, pageS ize=1024, maxRows=0, sqlQry=insert into MyApplication (ID, NAME, DESCRIPTION) values (?,?,?), args=[1, TestApp, To be deleted]]] class org.apache.ignite.internal.processors.query.IgniteSQLException: Failed to execute DML statement [stmt=insert into MyApplication (ID, NAME, DESCRIPTION) values (?,?,?), params=[1, TestApp, To be deleted]]]
        at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.queryDistributedSqlFields(IgniteH2Indexing.java:1324)
        at org.apache.ignite.internal.processors.query.GridQueryProcessor$6.applyx(GridQueryProcessor.java:1856)
        at org.apache.ignite.internal.processors.query.GridQueryProcessor$6.applyx(GridQueryProcessor.java:1852)
        at org.apache.ignite.internal.util.lang.IgniteOutClosureX.apply(IgniteOutClosureX.java:36)
        at org.apache.ignite.internal.processors.query.GridQueryProcessor.executeQuery(GridQueryProcessor.java:2293)
        at org.apache.ignite.internal.processors.query.GridQueryProcessor.querySqlFieldsNoCache(GridQueryProcessor.java:1860)
        at org.apache.ignite.internal.processors.odbc.jdbc.JdbcRequestHandler.executeQuery(JdbcRequestHandler.java:188)
        at org.apache.ignite.internal.processors.odbc.jdbc.JdbcRequestHandler.handle(JdbcRequestHandler.java:122)
        at org.apache.ignite.internal.processors.odbc.SqlListenerNioListener.onMessage(SqlListenerNioListener.java:152)
        at org.apache.ignite.internal.processors.odbc.SqlListenerNioListener.onMessage(SqlListenerNioListener.java:44)
        at org.apache.ignite.internal.util.nio.GridNioFilterChain$TailFilter.onMessageReceived(GridNioFilterChain.java:279)
        at org.apache.ignite.internal.util.nio.GridNioFilterAdapter.proceedMessageReceived(GridNioFilterAdapter.java:109)
        at org.apache.ignite.internal.util.nio.GridNioAsyncNotifyFilter$3.body(GridNioAsyncNotifyFilter.java:97)
        at org.apache.ignite.internal.util.worker.GridWorker.run(GridWorker.java:110)
        at org.apache.ignite.internal.util.worker.GridWorkerPool$1.run(GridWorkerPool.java:70)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
        at java.lang.Thread.run(Unknown Source)
Caused by: class org.apache.ignite.IgniteCheckedException: Key is missing from query
        at org.apache.ignite.internal.processors.query.h2.dml.UpdatePlanBuilder.createSupplier(UpdatePlanBuilder.java:331)
        at org.apache.ignite.internal.processors.query.h2.dml.UpdatePlanBuilder.planForInsert(UpdatePlanBuilder.java:196)
        at org.apache.ignite.internal.processors.query.h2.dml.UpdatePlanBuilder.planForStatement(UpdatePlanBuilder.java:82)
        at org.apache.ignite.internal.processors.query.h2.DmlStatementsProcessor.getPlanForStatement(DmlStatementsProcessor.java:438)
        at org.apache.ignite.internal.processors.query.h2.DmlStatementsProcessor.updateSqlFields(DmlStatementsProcessor.java:164)
        at org.apache.ignite.internal.processors.query.h2.DmlStatementsProcessor.updateSqlFieldsDistributed(DmlStatementsProcessor.java:222)
        at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.queryDistributedSqlFields(IgniteH2Indexing.java:1321)
        ... 17 more

Any help would be greatly appreciated.

Solution

  • Just configure the cache and SQL indexes using CREATE TABLE and CREATE INDEX commands, especially, if you plan access Ignite with SQL operations mostly.

    If key-value, computational or machine learning APIs are still required then check up this project that showcases how to do that when the caches are defined using SQL DDL commands.