Handlersocket off by one error in IN() requests

[Erik Jacobson]

Using Percona server 5.5.13-rel20.4-138.lenny with the included Handlersocket plugin and the following config:

handlersocket_verbose = 10
handlersocket_address = '192.168.0.1'
handlersocket_port = 2678
handlersocket_threads = 16 # 2xcpu... 3 x spins wildly out of control
handlersocket_threads_wr = 1
handlersocket_timeout = 30
handlersocket_backlog = 32768
# defaults, 262144 is 256k
handlersocket_sndbuf = 0
handlersocket_rcvbuf = 0
# Set to non 0 if connection pooling/persistent connections are used
handlersocket_accept_balance = 0
handlersocket_wrlock_timeout = 6

There seems to be an odd off by one error when using the IN() syntax (which otherwise seems to work):
"2 > 1 0 5 0 @ 0 4 10 20 30 40"

[Index Id 2 / PRIMARY(id) > 0 / limit 0,5 / id IN(10,20,30,40)]

As an example, the result set here would be user id and user name:

11 / usera, 21 / userb, 31 / userc, 41 / userd

So, it's actually returning the next ID for each in the set (the usernames are correct for the returned 11/21/31/41 ids) rather then the requested IDs. I've been manually fixing this by iterating over my request and decrementing each ID in the IN() section by one.

The second part of this is when using a FILTER argument. The off by one error also seems to show up in the other direction (so I iterate over and increment instead of decrement each element)

If you need any further info or debugging, please let me know. Thanks!

[Erik Jacobson (kross)]

I echoed this bug on upstream here: https://github.com/DeNADev/HandlerSocket-Plugin-for-MySQL/issues/70

There was a reply regarding a pull request, which makes me believe I just may have gotten IN() support removed? Which would be unfortunate, as I do rely on it (after applying the workaround)

[Raghavendra D Prabhu (raghavendra-prabhu)]

@Erik

1. The commit revert doesn't seem to remove the IN support but its ability to match multiple rows for single IN value (atleast what I could get from https://github.com/DeNADev/HandlerSocket-Plugin-for-MySQL/issues/56 )

2. Seems #56 has been reverted in Jan 23, 2012 in https://github.com/DeNADev/HandlerSocket-Plugin-for-MySQL/commit/33fda0d039b03f9a036a1fae36a3f10f0e7de56e

3. However, the HandlerSocket plugin bundled with Percona Server seems to last updated upto commit -- https://github.com/DeNADev/HandlerSocket-Plugin-for-MySQL/commit/635b93097cf8c08594b8248d5da13237778e98b0 which is from April 11, 2011. So, it is not possible that it has the revert commit of 33fda0d.

So, have you tested with latest HandlerSocket build (built from the git here https://github.com/DeNADev/HandlerSocket-Plugin-for-MySQL) ?

We will need to file bug for #3 separately or use same bug for that.

[Erik Jacobson (kross)]

Oh hey, thanks for the update! I was beginning to suspect handlersocket was on its way to becoming a dead project in favor of other key/value methods. :)

I have not yet evaluated the new plugin, but will try as soon as I'm able, though that may not happen for a while due to other tasks.

[Stewart Smith (stewart)]

We'll happily keep Percona Server up to date with HandlerSocket releases, so it's up to the HandlerSocket guys to resolve this :)

[Laurynas Biveinis (laurynas-biveinis)]

Current upstream bug link https://github.com/DeNA/HandlerSocket-Plugin-for-MySQL/issues/70

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-2715