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.