MySQL 'ON DUPLICATE KEY' Alternative

How Developers Are Using Aerospike
1

Greetings,

Hope someone can help me, trying to find a way to do MySQL’s INSERT – ON DUPLICATE KEY UPDATE in Aerospike but not sure if there’s a native way to do that or if it must be done with user defined functions or checking it in the app logic.

What I’m tryin to do is insert a counter that increments on a certain action for a user_id on a certain category_id, the table on mysql is as follows:

category_id | user_id | counter

And my SQL for the operation is like this:

“INSERT INTO counter_table(category_id, user_id, counter) VALUES (‘xx’,‘yy’,1) ON DUPLICATE KEY UPDATE counter = counter + 1;”

Thanks in advance

2

Most key/value databases support this natively. What you’re looking for is called an Increment. With Aerospike this is also called the Add operation, which you can read more about here:

http://www.aerospike.com/docs/guide/single.html

Just send an Add operation with the key being Category ID + User ID and a bin holding the count. If the key doesn’t exist, Aerospike will create it on the first increment. Afterwards, every new increment for that key will just update that bin value by 1 (or whatever integer value you pass).

2 Likes
3

mani is right.

Following is the snippet of code you can test using java client:

        WritePolicy policy = new WritePolicy();
       
        
        // Write a single value.
        Key key = new Key("test", "", "xyz");
        
        //Write a record
        Bin bin = new Bin("default", 1);
         
        Record record = client.operate(policy, key, Operation.add(bin), Operation.get());
2 Likes
4

Thanks, that’s exactly what I need. And from what I understand if I want to be able to retrieve all the counts per user regarding a single category id I just need to create another bin in the same record with the category id an create a secondary index on that bin right?

5

Yes.

However I’d recommend doing the bin and index on a user ID because that’s what you would query by. Otherwise you would get back all users that have a category ID which could be a lot of data if you have millions of users (if that’s how your app works).

6

Thanks, I will consider that but now I get how it works.

I’m trying using increment with the PHP client library, and have noted that if the key doesn’t exist it just throws an error:

[2] AEROSPIKE_ERR_RECORD_NOT_FOUND

It is only creating the bin if it deosn’t exist, but have to create the key prior to using the increment on a non-existent bin.

Not sure if there’s something wrong with the PHP library, if I’m doing something wrong or if that is the way it is meant to be.

The only options available for the increment call on the PHP library are there:

Aerospike::OPT_POLICY_KEY
Aerospike::OPT_WRITE_TIMEOUT
Aerospike::OPT_POLICY_RETRY
Aerospike::OPT_POLICY_GEN
Aerospike::OPT_POLICY_COMMIT_LEVEL

And I think they are not related to the “issue”.

7

Post the code you’re using and look through options here: https://github.com/aerospike/aerospike-client-php/blob/c534ad48a150d4d23d172232a1fe4c257f5f6502/doc/aerospike.md

8

Increment works on a bin in an existing record. If you didn’t first put() the record in its place, you’d get an error status.

1 Like
9

Post the code you’re using and look through options here: https://github.com/aerospike/aerospike-client-php/blob/c534ad48a150d4d23d172232a1fe4c257f5f6502/doc/aerospike.md

by manigandham

I checked that, but the increment method only seems to accept the options listed in my previous post, so there wasn’t much to do by using one of the options. But after what rbotzer posted, it is all clear now, the record must exist, the bin can be created with the increment action if it doesn’t exist but it will not create non-existent records.

Increment works on a bin in an existing record. If you didn’t first put() the record in its place, you’d get an error status.

by rbotzer

Thanks, it is all clear now.

10

Glad that clarified things. Raul, let me know if you run into any other problems. The documentation for the PHP client is at:

aerospike/aerospike-client-php

and

http://www.aerospike.com/docs/client/php/

I’ll try and fix sections that are unclear.

11

Maybe just adding that the record or key must exist in the documentation of the increment method would help.

Just to make sure this is the best way to create the key on the fly at the moment that I want to use increment, right now I’m doing this:

$key = $db->initKey("foo", "bar", $category_id.'_'.$user_id);
$put = $db->put($key, array("category_id" => $category_id, "user_id" => $user_id));
$inc = $db->increment($key, "count", 1, 1);

I was trying to do this on a single call with operate using write and then increment, but the write operation on operate also fails if the key doesn’t exist, so I guess what I’m showing above is the only way to do it, please correct me if I’m wrong or if there’s a better way.

12

Right, operate() provides multiple bin-operations on a record, but unfortunately the record must exist already.

Thanks for the tip. I’ll work that into the documentation.

13

I think this is just the case with the PHP driver, the .Net client and others can create records on an add operation as well.

@rbotzer any reason why the PHP driver doesn’t support this natively?

14

The PHP client is consistent with the other clients that are wrapped around the C client, such as Python and Node.js. The .Net client is ‘native’ (i.e. it implements all levels from connection pooling, cluster tending, wire protocol, etc). It has a different interface.

Definitely something we’ll consider in future iterations. For now I’m making the documentation for the bin operations more clear.