MySQL Bugs: #69415: incr on a locked record returns the wrong value.

Bug #69415	incr on a locked record returns the wrong value.
Submitted:	6 Jun 2013 12:21	Modified:	16 Dec 2014 19:45
Reporter:	Sadao Hiratsuka	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Memcached	Severity:	S2 (Serious)
Version:	5.6.12, 5.6.21	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	innodb, memcached

Description:
incr on a locked record returns the wrong value.

How to repeat:
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM demo_test WHERE c1 = 'a' FOR UPDATE;
+----+------+------+------+------+
| c1 | c2   | c3   | c4   | c5   |
+----+------+------+------+------+
| a  | 1    |    0 |    2 |    0 |
+----+------+------+------+------+
1 row in set (0.00 sec)

$ telnet localhost 11211
Trying 127.0.0.1...
Connected to localhost.
Escape character is '^]'.
get a
VALUE a 0 1
1
END
incr a 1
4294967296

Suggested fix:
When the record is locked, innodb_memcached should return an error.

Hello Hiratsuka-San,

Thank you for the bug report.

Thanks,
Umesh

// CLI session

mysql> show global variables like '%version%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 5.6.21                       |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| version                 | 5.6.21-log                   |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Linux                        |
+-------------------------+------------------------------+
7 rows in set (0.00 sec)

mysql> select * from demo_test;
+------+--------------+------+------+------------+
| c1   | c2           | c3   | c4   | c5         |
+------+--------------+------+------+------------+
| AA   | HELLO, HELLO |    8 |    0 |          0 |
| test | 1234         |    0 |    1 | 1416600551 |
+------+--------------+------+------+------------+
2 rows in set (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from demo_test FOR UPDATE;
+------+--------------+------+------+------------+
| c1   | c2           | c3   | c4   | c5         |
+------+--------------+------+------+------------+
| AA   | HELLO, HELLO |    8 |    0 |          0 |
| test | 1234         |    0 |    1 | 1416600551 |
+------+--------------+------+------+------------+
2 rows in set (0.00 sec)

// Telnet session

[ushastry@ushastry ~]$ telnet 127.0.0.1 11211
Trying 127.0.0.1...
Connected to 127.0.0.1.
Escape character is '^]'.
stats
STAT pid 4691
STAT uptime 204690
STAT time 1416801724
STAT version 5.6.21
STAT libevent 5.6.21
STAT pointer_size 64
STAT rusage_user 17.496340
STAT rusage_system 207.709423
STAT daemon_connections 2
STAT curr_connections 3
STAT total_connections 3
STAT connection_structures 3
..
.
.
STAT engine_maxbytes 134217728
END
get AA
VALUE AA 8 12
HELLO, HELLO
END
incr AA 1
51539607552

// CLI session

mysql> select * from demo_test;
+------+--------------+------+------+------------+
| c1   | c2           | c3   | c4   | c5         |
+------+--------------+------+------+------------+
| AA   | HELLO, HELLO |    8 |    0 |          0 |
| test | 1234         |    0 |    1 | 1416600551 |
+------+--------------+------+------+------------+
2 rows in set (0.00 sec)

// Tried decrement - affected

decr AA 1
51539607552

When rows are locked and incr tried on a string value - it shows wrong value , and with int value it shows correctly incremented value in memcached(telnet session) where as CLI confirms value are not incremented/decremented ( warning should have been better for locked rows). This is expected but this can cause confusion because memcahed session(telnet) shows value incremented but in reality it doesn't increment or decrements any values.
So, when rows are locked - values are not incremented/decremented for the given key but memcached session shows otherwise.

#####
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from demo_test FRO UPDATE;
+------+------+------+------+------------+
| c1   | c2   | c3   | c4   | c5         |
+------+------+------+------+------------+
| AA   | 2    |    8 |    2 |          0 |
| test | 1234 |    0 |    1 | 1416600551 |
+------+------+------+------+------------+
2 rows in set (0.00 sec)

// Telnet session - now increment works with int even when rows are locked

incr AA 1
3

// CLI session - nothing incremented when locked

mysql> select * from demo_test;
+------+------+------+------+------------+
| c1   | c2   | c3   | c4   | c5         |
+------+------+------+------+------------+
| AA   | 2    |    8 |    2 |          0 |
| test | 1234 |    0 |    1 | 1416600551 |
+------+------+------+------+------------+
2 rows in set (0.00 sec)

Posted by developer:
 
In innodb_api_arithmetic, we need to return the right return value of this function.

Posted by developer:
 
Fixed as of the upcoming 5.6.23, 5.7.6 releases, and here's the changelog entry:

The integer column value was handled incorrectly for the "memcached"
"incr" and "decr" commands.

$ git show -s 5b1102c
commit 5b1102cf95a6b5f6a3b072055b903fe19b94493b
Author: Allen.Lai <zheng.lai@oracle.com>
Date:   Thu Dec 11 19:44:21 2014 +0800

    Bug#20044123        INCR/DECR COMMANDS ON INTEGER VALUE WORK incorrect
    Bug#20083106        INCR ON A LOCKED RECORD RETURNS THE WRONG VALUE.
    
    We didn't handle the integer column value properately in memcached
    incr/decr command.  We need to check if the value is integer or string
    in innodb_api_arithmetic, and we also need to return the right return
    value of this function.
    
    Reviewed-by: Jimmy Yang<jimmy.yang@oracle.com>
    RB:7490