1

I create a table with counter column in using com.datastax.driver.core packages, and a function in class:

public void doStartingJob(){
   session.execute("CREATE KEYSPACE myks WITH replication "
            + "= {'class':'SimpleStrategy', 'replication_factor':1};");
   session.execute("CREATE TABLE myks.clients_count(ip_address text PRIMARY KEY,"
                                        + "request_count counter);");
}

After this I deleted table entry from CQLSH like:

jay@jayesh-380:~$ cqlsh
Connected to Test Cluster at 127.0.0.1:9042.
[cqlsh 5.0.1 | Cassandra 3.9 | CQL spec 3.4.2 | Native protocol v4]
Use HELP for help.

cqlsh:myks> DELETE FROM clients_count WHERE ip_address='127.0.0.1';

Then to insert row with same primary-Key I used following statement(via cqlsh):

UPDATE myks.clients_count SET request_count = 1 WHERE ip_address ='127.0.0.1';

And it is not allowed as:

InvalidRequest: Error from server: code=2200 [Invalid query] message="Cannot set the value of counter column request_count (counters can only be incremented/decremented, not set)"

But, I want the value of record's counter column should be set to 1, and with same primary-Key. (Functional Requirement)

How to do the same ??

S Jayesh
  • 191
  • 1
  • 4
  • 19

1 Answers1

3

The usage of counters is a bit strange, but you'll get used to. The main thing however, is that counters cannot be reused. Once you delete a counter value for a particular primary key, this counter is lost forever. This is by design and I think is not going to change.

Back to your question, the first of your problems is the initial DELETE. Don't.

Second, if the counter value for a primary key doesn't exists, C* will treat it is zero by default. Following the documentation, to load data in the counter for the first time you have to issue:

 UPDATE mysecurity.clients_count SET request_count = request_count + 1 WHERE ip_address ='127.0.0.1';

And a SELECT will return the correct answer: 1

Again, beware of deletes! Don't! If you do, any subsequent query:

 UPDATE mysecurity.clients_count SET request_count = request_count + 1 WHERE ip_address ='127.0.0.1';

will NOT fail, but the counter will NOT be updated.

Another thing to note is that C* don't support atomic read and update (or update and read on counter columns. That is you cannot issue an update and get within the same query the new (or the old) value of the counter. You'll need to perform two distinct queries, one with SELECT and one with UPDATE, but in a multi-client environment the SELECT value you get could not reflect the counter value during the UPDATE.

Your app will definitely fail if you do underestimate this.

xmas79
  • 5,060
  • 2
  • 14
  • 35
  • after DELETE following the UPDATE myks.clients_count SET request_count = 1 WHERE ip_address ='127.0.0.1'; when i re-update via UPDATE myks.clients_count SET request_count = request_count +1 WHERE ip_address = '127.0.0.1'; it is working and updating the counter with the value that i have DELETED. – S Jayesh Nov 10 '16 at 10:32
  • **Counter record is never deleted** See [THIS.](http://stackoverflow.com/a/13694777/7081346) – S Jayesh Nov 10 '16 at 11:23
  • 2
    You **can** delete a counter. You **cannot** use it afterwards. Instead of deleting a counter you can **try** to bring it back to zero if it makes you feel good. However, it is not **atomic**, and if you read a value **V** from a counter you can add **-V**, but it is not guaranteed that your counter will be zero in a multi client environment. – xmas79 Nov 10 '16 at 11:50
  • Yes _You can delete a counter. You cannot use it afterwards_. **Only two ways to get a _Guaranteed Solution_**. Either **use single node** in application or **donot use cassandra** database in this application. – S Jayesh Nov 10 '16 at 12:30
  • There are other solutions. For relatively small deployments and/or low throughputs you can *complement* Cassandra with a *queue system* that will handle this pretty well. – xmas79 Nov 11 '16 at 09:01