Apache Ignite "Update of composite key column is not supported"

Question

I have a cache with an AffinityKey as Key and want to insert an entry. If i try to insert the entry using a SQL-Insert statement i get an exception stating "Update of composite key column is not supported" which i guess is because the AffinityKey is a composite key. On https://ignite.apache.org/docs/latest/SQL/indexes#indexing-nested-objects i have read that if you are using indexed nested objects you will no longer be able to use insert or update statements but it is not mentioned on https://ignite.apache.org/docs/latest/data-modeling/affinity-collocation for composite keys.

Does using composite keys also cause insert and update statements to no longer be usable or am i missing something?

Example:

// creating cache using QueryEntities
cache = ignite.CreateCache<AffinityKey, MyClass>(new CacheClientConfiguration(cacheConfiguration)
  {
    Name = "MyCache",
    QueryEntities = new[]
    {
      new QueryEntity(typeof(AffinityKey), typeof(MyClass)){ TableName = "MyTable" }
    },
  });

// insert statement
cache.Query(
  new SqlFieldsQuery(
    "INSERT INTO " +
      "MyTable ( " +
        "_key, " +
        $"{nameof(MyClass.Text)} ) " +
      "VALUES ( " +
        "?, " +
        "? )"
  )
  {
    Arguments = new object[]
    {
      new AffinityKey("Key1", "AffinityKey1"),
      "TextValue"
    },
  });

Solution: As described below by @Alexandr Shapkin, setting "IGNITE_SQL_ALLOW_KEY_VAL_UPDATES" to true makes it possible to update entries via SQL that have been added using a composite key.

For anyone interested https://ignite.apache.org/docs/latest/setup#setting-ignite-system-properties shows how to list available ignite properties/environment variables with a brief description.

Answer 1

You are mixing the concepts.

In Apache Ignite you can access or insert your data multiple ways. One would be to use Cache API, like cache#get, cache#put, another option would be - using SQL, like INSERT, SELECT, etc.

Since eventually everything is just a key-value pair undeneath, Ignite provide a special SQL _key and _val properties to access the corresponded values.

The thing is - if you don't need to use Cache API and SQL interchangeably and are OK using only, say, SQL, you don't set _key at all.

On the other hand, if you need to use both APIs, say, defined with QueryEntity just like in your example, you need to specify _key field properly.

Consider this example:

IgniteCache<AffinityKey<Long>, Person> colPersonCache = Ignition.ignite().cache(PERSON_CACHE);

Person p1 = new Person(org1, "John", "Doe", 2000, "John Doe has Master Degree.");

colPersonCache.put(p1.key(), p1);

...

it's fine to do this:

    colPersonCache.query(new SqlFieldsQuery(
        "INSERT INTO Person (id, orgId, firstName, lastName, salary, resume) " +
            "VALUES ( ?, ?, ?, ?, ?, ?)")
        .setArgs(6, 2, "first", "last", 1, "Master Degree")).getAll();

But in that case, you won't be able to locate the record using cache API:

// the following line returns null, but you can use SQL to locate the record
colPersonCache.get(new AffinityKey<Long>(6L,2L)); 

But if you use _key:

colPersonCache.query(new SqlFieldsQuery(
            "INSERT INTO Person (_key, id, orgId, firstName, lastName, salary, resume) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?)")
            .setArgs(new AffinityKey<Long>(6L,2L), 6, 2, "first", "last", 1, "Master Degree")).getAll();

Cache API now does return the value:

colPersonCache.get(new AffinityKey<Long>(6L,2L)); 

//Person [id=6, orgId=2, lastName=last, firstName=first, salary=1.0, resume=Master Degree]

Note.

There is a special restriction to avoid possible key desync, and in order to make the latest query (and the original in the question) work, you have to set the following enviroment property:

System.setProperty(IgniteSystemProperties.IGNITE_SQL_ALLOW_KEY_VAL_UPDATES, "true");