Read SQL table via cache api

[yongjec <yo...@gmail.com>]

I ran below SQL from Ignite Sqlline which creates a table and inserts one
entry. I am trying to query that entry using cache api. However, the value
always returns null.

############## SQL ################
CREATE TABLE SIMPLE_TABLE (
    ACCOUNT_ID INT,
    SECURITY_ALIAS INT,
    POSITION_TYPE VARCHAR(50),
    EFFECTIVE_DATE TIMESTAMP,
    GROSS_RETURN DOUBLE,
    PRIMARY KEY (ACCOUNT_ID, SECURITY_ALIAS, POSITION_TYPE, EFFECTIVE_DATE)
)
WITH "template=partitioned, backups=1, affinityKey=ACCOUNT_ID,
KEY_TYPE=com.xyz.ie.SimpleKey, VALUE_TYPE=com.xyz.ie.SimpleVal";

INSERT INTO SIMPLE_TABLE VALUES (100, 200, 'PT0', '2018-11-01', 1000.0);
##################################


< SimpleKey.java >
package com.xyz.ie;

import org.apache.ignite.cache.affinity.AffinityKeyMapped;
import java.sql.Timestamp;

public class SimpleKey {

    @AffinityKeyMapped
    private int ACCOUNT_ID;

    private int SECURITY_ALIAS;

    private String POSITION_TYPE;

    private Timestamp EFFECTIVE_DATE;

    public SimpleKey(int accountId, int securityAlias, String positionType,
Timestamp effectiveDate) {
        this.ACCOUNT_ID = accountId;
        this.SECURITY_ALIAS = securityAlias;
        this.POSITION_TYPE = positionType;
        this.EFFECTIVE_DATE = effectiveDate;
    }
}

<SimpleVal.java>
package com.xyz.ie;

public class SimpleVal {
    private double GROSS_RETURN;

    public SimpleVal(double grossReturn) {
        this.GROSS_RETURN = grossReturn;
    }

    public double getGrossReturn() {
        return GROSS_RETURN;
    }

    public void setGrossReturn(double grossReturn) {
        this.GROSS_RETURN = grossReturn;
    }
}


Here is my code that tries to read the entry.

IgniteCache<SimpleKey, SimpleVal> simpleTableCache =
ignite.cache("SQL_PUBLIC_SIMPLE_TABLE");

// cacheSize evaluates to 1 as expected.
int cacheSize = simpleTableCache.size(CachePeekMode.PRIMARY);

SimpleKey key = new SimpleKey(100, 200, "PT0", Timestamp.valueOf("2018-11-01
00:00:00"));

*// I expect val below to contain the one entry in the cache. However, it is
null.*
SimpleVal val = simpleTableCache.get(key);
if (val == null) {
    System.out.println("Can't find the key.");
}


Could someone point out what I am doing wrong here and why it is not finding
the key in the cache?



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/

Re: Read SQL table via cache api

[yongjec <yo...@gmail.com>]

Hi Andrei,

I got this to work by setting the server and client time zones to be the
same.

So what happened was that I had created the table and inserted the record
using Sqlline.sh on the server host. That must have stored the
effective_date field in the key based on the server's time zone (UTC).

Then when I queried the key from the client code, which was running on a
host in a different time zone (UTC-5:00), it probably generated the same
field from the local time zone.

I should have known that Timestamp type is zone specific.

Thank you,
Yong



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/

Re: Read SQL table via cache api

[yongjec <yo...@gmail.com>]

Hi Andrei,

I captured below logs with the -DIGNITE_QUIET=false flag. There is a 5-hour
difference between the two time zones.


Server Log 
ignite-ef771a6b.log
<http://apache-ignite-users.70518.x6.nabble.com/file/t2147/ignite-ef771a6b.log>  


Client Log
ignite-86fd5276.log
<http://apache-ignite-users.70518.x6.nabble.com/file/t2147/ignite-86fd5276.log>  

Thank you,
Yong



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/

Re: Read SQL table via cache api

[aealexsandrov <ae...@gmail.com>]

Hi,

Could you please set -DIGNITE_QUIET=false to turn off the quiet mode and
re-attach the logs?

BR,
Andrei



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/

Re: Read SQL table via cache api

[yongjec <yo...@gmail.com>]

Another observation is that if I take out the effective_date field
(Timestamp) from the key, and use only the Int and Double columns as the
primary keys, the server finds it without any problem.



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/

Re: Read SQL table via cache api

[yongjec <yo...@gmail.com>]

Hi Andrei,

Thank you for your reply. 

Here is the server log. However, I do not see any relevant information in
it. The cache get request was made from the client between line 117 (client
joins the topology) and 127 (client leaves the topology).

ignite-db357bff.log
<http://apache-ignite-users.70518.x6.nabble.com/file/t2147/ignite-db357bff.log>  

Regards,
Yong



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/

Re: Read SQL table via cache api

[aealexsandrov <ae...@gmail.com>]

Hi,

I don't think that it's possible to understand a reason without logs. Please
attach them here or possible if you can reproduce it then you may file a
Jira issue and attach logs there with a full description of the problem.

BR,
Andrei



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/

Re: Read SQL table via cache api

[yongjec <yo...@gmail.com>]

Hi Andrei,

I have figured this out after several hours of troubleshooting today.

I had the Ignite server process, which hosted the table/cache, running on a
Linux host (CentOS7). On the other hand, I was running the client code on a
Macbook. It seems that the two platform was deserializing the key POJO into
different binary format, so when the client asks for a key, the server
doesn't see the match.

When I run both the server and the client on the Macbook, it finds the key
as expected.

Thank you,
Yong



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/

Re: Read SQL table via cache api

[aealexsandrov <ae...@gmail.com>]

Hi,

I think that information about it could be located in node log. Could you
please provide the log of the node where you tries to read the data?

BR,
Andrei



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/