Bug #72102 bad COUNT(*) result after loading data with memcached plugin
Submitted: 22 Mar 2014 15:54 Modified: 16 Nov 2014 19:44
Reporter: Nicolas De rico Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: Memcached Severity:S2 (Serious)
Version:5.6.16 OS:Mac OS X (10.9)
Assigned to: CPU Architecture:Any
Tags: memcached, MySQL, server

[22 Mar 2014 15:54] Nicolas De rico
Description:
I ran memslap and then selected results from the SQL prompt.  Data came back fine but for some reason COUNT(*) returns a bad count.  Other variations of COUNT return proper count.  It seems that SELECT COUNT(*) FROM.. has an optimization that is broken with memcached.

The settings of my CNF file that seem possibly relevant are:

[mysqld]
plugin-load = daemon_memcached=libmemcached.so
transaction-isolation = READ-COMMITTED

How to repeat:
Not too sure the exact steps.

1- mysql client already connected

2- run memslap many times to insert data
memslap --servers=localhost:11211 --test set
memslap --servers=localhost:11211 --test set
memslap --servers=localhost:11211 --test set
memslap --servers=localhost:11211 --test set
memslap --servers=localhost:11211 --test set
memslap --servers=localhost:11211 --test set
memslap --servers=localhost:11211 --test set
memslap --servers=localhost:11211 --test set
memslap --servers=localhost:11211 --test set

This inserts 10K rows every time.  There were two at the start for a total of 90002.

3- Access data from SQL.

When "I ran SELECT * from demo_test" all rows were retrieved.

but when running "SELECT COUNT(*) FROM demo_test", I got:

+----------+
| count(*) |
+----------+
|    50002 |
+----------+
1 row in set (0.00 sec)

After that, I ran all kinds of variations.  Example:

mysql> select 1,count(*) from demo_test;
+---+----------+
| 1 | count(*) |
+---+----------+
| 1 |    90002 |
+---+----------+
1 row in set (0.26 sec)

mysql> select count(c2) from demo_test;
+-----------+
| count(c2) |
+-----------+
|     90002 |
+-----------+

Then one more time:
mysql> select count(*) from demo_test;
+----------+
| count(*) |
+----------+
|    50002 |
+----------+
1 row in set (0.00 sec)

Suspecting some caching or transaction isolation issue, I decided play with transactions to stir the pot:

mysql> begin;
Query OK, 0 rows affected (0.01 sec)

mysql> select count(*) from demo_test;
+----------+
| count(*) |
+----------+
|    90002 |
+----------+
1 row in set (0.26 sec)

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

mysql> select count(*) from demo_test;
+----------+
| count(*) |
+----------+
|    50002 |
+----------+
1 row in set (0.00 sec)

I don't know why  COUNT(*) got stuck on 50002 rows while every other COUNT returned 90002 rows.

NOTE: Perhaps I connected or reconnected after running memslap 5 times and it got stuck on that number.

Suggested fix:
Don't know.
[16 Oct 2014 19:44] Sveta Smirnova
Thank you for the report.

> NOTE: Perhaps I connected or reconnected after running memslap 5 times and it got stuck on that number.

Did you reconnect after run SELECT COUNT(*) first time? Do you have query cache enabled? What is output of SELECT @@autocommit?
[17 Nov 2014 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".