Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP

Loading…

i can not update the column which updated by cql by entity.persistence()! #154

Closed
zhouhero opened this Issue · 30 comments

3 participants

@zhouhero

I can not update "status" and "game_sync_id " feilds when update the table.
but "lock_date" and "update_date " are OK. It is a bug of kundera2.2?
Can anyone help me?

[My DB&Kundera]
Cassandra 1.1.5
Kundera2.2 (using pelops driver)

[My table]
create table mystery_gift_serial (
mystery_gift_serial_id varchar,
mystery_gift_id uuid,
game_sync_id varchar,
pid varchar,
data_no varchar,
status int,
lock_date timestamp,
dl_date timestamp,
create_date timestamp,
update_date timestamp,
primary key(mystery_gift_serial_id)
);

[My JPQL]
"update MysteryGiftSerialEntity c set c.lockDate = :lockDate, c.updateDate = :updateDate, c.status = :status, c.gameSyncId = :gameSyncId where c.mysteryGiftSerialId = :mysteryGiftSerialId"

[My Entity]

import java.sql.Timestamp;
import java.util.UUID;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
import javax.xml.bind.annotation.XmlRootElement;

@Entity
@Table(name = "mystery_gift_serial", schema = "XXX@cassandra_pu")
@XmlRootElement(name = "MysteryGiftSerialEntity")
public class MysteryGiftSerialEntity {

// ------ メンバ変数  ここから ------

@Id
@Column(name = "mystery_gift_serial_id")
private String mysteryGiftSerialId;


@Column(name = "mystery_gift_id")
private UUID mysteryGiftId;

@Column(name = "game_sync_id")
private String gameSyncId;

@Column(name = "pid")
private String pid;


@Column(name = "data_no")
private String dataNo;

@Column(name = "status")
private int status;

@Column(name = "lock_date")
private Timestamp lockDate;


@Column(name = "dl_date")
private Timestamp dlDate;


@Column(name = "create_date")
private Timestamp createDate;

@Column(name = "update_date")
private Timestamp updateDate;

// ------ メンバ変数  ここまで ------


// ------ getter + setter  ここから ------

public String getMysteryGiftSerialId() {
    return mysteryGiftSerialId;
}

public void setMysteryGiftSerialId( String mysteryGiftSerialId ) {
    this.mysteryGiftSerialId = mysteryGiftSerialId;
}

public UUID getMysteryGiftId() {
    return mysteryGiftId;
}

public void setMysteryGiftId( UUID mysteryGiftId ) {
    this.mysteryGiftId = mysteryGiftId;
}

public String getGameSyncId() {
    return gameSyncId;
}

public void setGameSyncId( String gameSyncId ) {
    this.gameSyncId = gameSyncId;
}

public String getPid() {
    return pid;
}

public void setPid( String pid ) {
    this.pid = pid;
}

public String getDataNo() {
    return dataNo;
}

public void setDataNo( String dataNo ) {
    this.dataNo = dataNo;
}

public int getStatus() {
    return status;
}

public void setStatus( int status ) {
    this.status = status;
}

public Timestamp getLockDate() {
    return lockDate;
}

public void setLockDate( Timestamp lockDate ) {
    this.lockDate = lockDate;
}

public Timestamp getDlDate() {
    return dlDate;
}

public void setDlDate( Timestamp dlDate ) {
    this.dlDate = dlDate;
}

public Timestamp getCreateDate() {
    return createDate;
}

public void setCreateDate( Timestamp createDate ) {
    this.createDate = createDate;
}

public Timestamp getUpdateDate() {
    return updateDate;
}

public void setUpdateDate( Timestamp updateDate ) {
    this.updateDate = updateDate;
}

// ------ getter + setter  ここまで ------

}

@mevivs
Collaborator

Hi,
I guess problem with update on "lock_date" and "update_date is via query is,

update MysteryGiftSerialEntity c set c.lockDate = :lockDate, c.updateDate = :updateDate, c.status = :status, c.gameSyncId = :gameSyncId where c.mysteryGiftSerialId = :mysteryGiftSerialId"

because setParameter is an issue with update JPQL query? So as a workaround, if you fire a select query and then update them sequentially or go by CQL3 way, till that setParameter bug is fixed in current trunk?

-Vivek

@zhouhero

i also tried query and update, the result was same.

[MY CODE]
Timestamp sysdate = new Timestamp(System.currentTimeMillis());
MysteryGiftSerialEntity entity = getData(mysteryGiftSerialId);
entity.setGameSyncId("test1");
entity.setStatus(2);
entity.setLockDate(sysdate);
entity.setUpdateDate(sysdate);
update(entity);

by the way, i also find a strange problem is that,
when i do query again, like fellow

MysteryGiftSerialEntity entity = getData(mysteryGiftSerialId);

i can get entity right, the status and gamesyncid's value is OK.
but the cassandra db' data is older!
It seem that kunndera read data from buffer when sencondary query.

@mevivs
Collaborator

It Looks like, data is not yet flushed. try em.flush() or em.clear().

This can only happen, if working within transaction boundaries and commit is not issued or flush mode is set to COMMIT !

Here is a simple test case to demonstrate all ways of CRUD:

https://github.com/impetus-opensource/Kundera/blob/trunk/kundera-cassandra/src/test/java/com/impetus/client/crud/PersonCassandraTest.java

-Vivek

@zhouhero

I tried em.clear() after entityManager.merge(), i can read the data from db not buffer now.
But the update problem is till the same:
when doing entityManager.merge(entity), the "status" and "game_sync_id " feilds still can not be updated.
but "lock_date" and "update_date " are ok!

@mevivs
Collaborator

Can you please share your entity definition and CRUD code snippet to look into this? This is bit strange as 2 fields are getting and 2 of them are not!

-Vivek

@zhouhero

yes,
you can see the entity and table definition in the top comment.
here is my CRUD code:

EntityManagerFactory entityManagerFactory = Persistence.createEntityManagerFactory(PERSISTENCE_UNIT);
entityManager = entityManagerFactory.createEntityManager();
@SuppressWarnings("rawtypes") Map clients = (Map) entityManager.getDelegate();
PelopsClient client = (PelopsClient) clients.get(PERSISTENCE_UNIT);
client.setCqlVersion(CassandraConstants.CQL_VERSION_3_0);
client.setConsistencyLevel(ConsistencyLevel.QUORUM);

Timestamp sysdate = new Timestamp(System.currentTimeMillis());
MysteryGiftSerialEntity entity = entityManager.find(MysteryGiftSerialEntity , mysteryGiftSerialId);
entity.setGameSyncId("test1");
entity.setStatus(2);
entity.setLockDate(sysdate);
entity.setUpdateDate(sysdate);
entityManager.merge(entity);
entityManager.clear();

@kkmishra kkmishra was assigned
@mevivs
Collaborator

kk,
please look into this.

Asigning to kk.

-Vivek

@kkmishra

Hi zhouhero,
this is what I have tried and working fine with me,

Entity

@Entity
@Table(name = "mystery_gift_serial", schema = "UUIDCassandra@cass_pu")
@XmlRootElement(name = "MysteryGiftSerialEntity")
public class MysteryGiftSerialEntity
{
    @Id
    @Column(name = "mystery_gift_serial_id")
    private String mysteryGiftSerialId;

    @Column(name = "mystery_gift_id")
    private UUID mysteryGiftId;

    @Column(name = "game_sync_id")
    private String gameSyncId;

    @Column(name = "pid")
    private String pid;

    @Column(name = "data_no")
    private String dataNo;

    @Column(name = "status")
    private int status;

    @Column(name = "lock_date")
    private Timestamp lockDate;

    @Column(name = "dl_date")
    private Timestamp dlDate;

    @Column(name = "create_date")
    private Timestamp createDate;

    @Column(name = "update_date")
    private Timestamp updateDate;

CRUD

        EntityManagerFactory entityManagerFactory = Persistence.createEntityManagerFactory(PERSISTENCE_UNIT);
        EntityManager entityManager = entityManagerFactory.createEntityManager();

        MysteryGiftSerialEntity entity = new MysteryGiftSerialEntity();
        Timestamp createDate = new Timestamp(System.currentTimeMillis());
        Timestamp dlDate = createDate;
        Timestamp lockDate = createDate;
        UUID mysteryGiftId = UUID.randomUUID();
        Timestamp updateDate = createDate;

        entity.setCreateDate(createDate);
        entity.setDataNo("dataNo");

        entity.setDlDate(dlDate);
        entity.setGameSyncId("gameSyncId");

        entity.setLockDate(lockDate);

        entity.setMysteryGiftId(mysteryGiftId);
        entity.setMysteryGiftSerialId("mysteryGiftSerialId");
        entity.setStatus(0);
        entity.setPid("pid");

        entity.setUpdateDate(updateDate);

        entityManager.persist(entity);
        entityManager.clear();

        @SuppressWarnings("rawtypes")
        Map clients = (Map) entityManager.getDelegate();
        PelopsClient client = (PelopsClient) clients.get(PERSISTENCE_UNIT);
        client.setCqlVersion(CassandraConstants.CQL_VERSION_3_0);
        client.setConsistencyLevel(ConsistencyLevel.QUORUM);

        Timestamp sysdate = new Timestamp(System.currentTimeMillis());
        MysteryGiftSerialEntity foundEntity = entityManager.find(MysteryGiftSerialEntity.class, "mysteryGiftSerialId");
        foundEntity.setGameSyncId("test1");
        foundEntity.setStatus(2);
        foundEntity.setLockDate(sysdate);
        foundEntity.setUpdateDate(sysdate);
        entityManager.merge(foundEntity);
        entityManager.clear();

result set .....
Before Update

cqlsh:UUIDCassandra> select * from mystery_gift_serial;
 mystery_gift_serial_id | create_date              | data_no | dl_date                  | game_sync_id | lock_date                | mystery_gift_id                      | pid | status | update_date
------------------------+--------------------------+---------+--------------------------+--------------+--------------------------+--------------------------------------+-----+--------+--------------------------
    mysteryGiftSerialId | 2012-12-19 12:23:24+0000 |  dataNo | 2012-12-19 12:23:24+0000 |   gameSyncId | 2012-12-19 12:23:24+0000 | 5028652d-2bd2-4efb-987a-a1d81f4bcf49 | pid |      0 | 2012-12-19 12:23:24+0000

After Update

cqlsh:UUIDCassandra> select * from mystery_gift_serial;
 mystery_gift_serial_id | create_date              | data_no | dl_date                  | game_sync_id | lock_date                | mystery_gift_id                      | pid | status | update_date
------------------------+--------------------------+---------+--------------------------+--------------+--------------------------+--------------------------------------+-----+--------+--------------------------
    mysteryGiftSerialId | 2012-12-19 12:23:24+0000 |  dataNo | 2012-12-19 12:23:24+0000 |        test1 | 2012-12-19 12:23:33+0000 | 5028652d-2bd2-4efb-987a-a1d81f4bcf49 | pid |      2 | 2012-12-19 12:23:33+0000

persistence.xml

<persistence-unit name="cass_pu">
        <provider>com.impetus.kundera.KunderaPersistence</provider>
        <properties>
            <property name="kundera.nodes" value="localhost" />
            <property name="kundera.port" value="9160" />
            <property name="kundera.keyspace" value="UUIDCassandra" />
            <property name="kundera.dialect" value="cassandra" />
            <property name="kundera.client" value="pelops" />
            <property name="kundera.client.lookup.class"
                value="com.impetus.client.cassandra.pelops.PelopsClientFactory" />
            <property name="kundera.cache.provider.class"
                value="com.impetus.kundera.cache.ehcache.EhCacheProvider" />
            <property name="kundera.cache.config.resource" value="/ehcache-test.xml" />
        </properties>
    </persistence-unit>

and cassandra version 1.1.6

Thanks
-kk

@zhouhero

thanks for your test!
could you tell me your cassandra version?
i am using 1.1.5.
kundera2.2

@mevivs
Collaborator

This is done on 1.6 and kundera 2.2.

@mevivs
Collaborator

Let me know, if this not works for you.

On update query "setParameter" issues, we are working and will post an update as soon as we check-in that.

-Vivek

@zhouhero

please insert the record by CQL3 firstly like this:

update mystery_gift_serial set status=0 where mystery_gift_serial_id='1234567890123456'

then update the record by entity by CRUD.
i think the problem will happen.

@zhouhero

by the way, please insert the data by cqlsh!

@mevivs
Collaborator

Hi,
This is a known problem with CQL and thrift interoperability,.

As per my discussion in cassandra community, here is outcome:

This was discussed in one of the tickets. The problem is that CQL3's sparse
tables is it has different metadata that has NOT been added to thrift's
CFMetaData. Thus thrift is unaware of exactly how to verify the insert.

Originally it was made impossible for thrift to see a sparse table (but
that restriction has been lifted) it seems. It is probably a bad idea to
thrift insert into a sparse table until Cassandra does not have two
distinct sources of meta information.

So crux is do not mix thrift and CQL for data operation over cassandra!
As cassandra is moving towards CQL schema way and new java driver is out(thought not much mature) , Kundera team is also planning to build one more client over cassandra other than Pelops and thrift driver.

So if you are using Thrift/Pelops via kundera, please avoid dml operations via CQL !

-Vivek

@mevivs
Collaborator

As per my discussion with Jonathan(project lead for cassandra), here is what he has to say on this:

On Mon, Oct 22, 2012 at 6:34 PM, Vivek Mishra vivek.mishra@yahoo.com wrote:

Thanks. But it means i may have re-write all the stuff in CQL way.
Considering CQL as a future interface for cassandra interface,AFN i will
implement it without mixing them.

-Vivek

On Mon, Oct 22, 2012 at 6:32 PM, Jonathan Ellis jbellis@gmail.com wrote:

Mixing the two isn't really recommended because of just this kind of
difficulty, but if you must, I would develop against 1.2 since it will
actually validate that the CT encoding you've done manually is valid.
1.1 will just fail silently.

@zhouhero

thanks for you explanations!

"Do crux is do not mix thrift and CQL for data operation over cassandra!"

is it means that i can only use one of them( JPQL(thrift driver) or nativeQuery(CQL? pelops driver))
in kundera2.2 until cassandra cql3 fixed this bug?

by the way, when CQL is excuting in kundera which driver is using?is it pelops?
When i set using pelops driver and set the CQL's version to 3, JPQL was not excuted by CQL3?
(i know pelops can excute CQL3 now)

@mevivs
Collaborator

you can definitely execute CQL query to fetch your data but i suggest not to update/insert it.

If you select your client factory as "PelopsClientFactory" (in persistence.xml) then it is based on pelops. If client factory is selected as "ThriftClientFactory" then it is based on raw thrift client.

You can execute CQL query but if you mix it to update data, thrift might behave differently.

BTW Pelops is based on thrift API only.

-Vivek

@zhouhero

i see,
by the way,
when kundera 2.3, if i use cql jdbc dirver, can jpql(JPA action) be excuted by cql jdbc dirver?

@mevivs
Collaborator

yes, you will get all supported features of JPQL. Most of current cassandra clients are based on Thrift only. So last month release is a major shift by cassandra and we are also working to get this integrated in kundera.

CQL3: you can still execute using ThriftClientFactory or PelopsClientFactory within Kundera.

Just to describe problem: Cassandra complains about mixing Thrift and CQL togethere. So if you can avoid Update/Delete/Insert via CQL, you can still use CQL3 with Thrift.

Cheers,
-Vivek

@zhouhero

it means that ,
when kundera2.3 , if using cql jdbc driver,
can the data inserted by cql3 using cassandra csqlsh tool be updated correctly?
the fellowing action will work ok?


please insert the record by CQL3 firstly like this:

update mystery_gift_serial set status=0 where mystery_gift_serial_id='1234567890123456'

then update the record by entity by CRUD.

i think the problem will happen.

@zhouhero

sorry,
it's very important for me, because i am using cassandra's CQLSH tool to manage(insert,delete) the data now.

@mevivs
Collaborator

OK. wait for jdbc driver then.

-Vivek

@zhouhero

thank you very much!
looking forward to kundera 2.3...

@mevivs
Collaborator

KK,
Since we have upgraded to cassandra 1.2
Please verify if interoperability is no more an issue b/w thrift and CQL, as mentioned above.

@zhouhero

thank you for your reply.
i will test it.

@mevivs
Collaborator

Verified, releasing with 2.3

@zhouhero

thank you ,let close it.

@zhouhero zhouhero closed this
@zhouhero

"Do crux is do not mix thrift and CQL for data operation over cassandra!"

is this problem fixed in kundera 2.4 + cassandra1.2?

@zhouhero

i have tried it again by using kundera 2.4+ cassandra 1.2,
it seems that this problem has not been fixed now!
could you pls give me some adivices?

@mevivs
Collaborator

CQL + Thrift interoperability work is currently under development. will post an update soon

-Vivek

@kkmishra kkmishra was unassigned by zhouhero
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.