Bug #81114 Inserts via Memcached Plugin API do not invalidate the query cache for the table
Submitted: 17 Apr 2016 23:13 Modified: 19 Apr 2016 9:22
Reporter: Troy NOT_FOUND Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: Memcached Severity:S2 (Serious)
Version:5.7, 5.6.30, 5.7.12 OS:Any
Assigned to: CPU Architecture:Any
Tags: innodb, memcached, memcached plugin, query cache

[17 Apr 2016 23:13] Troy NOT_FOUND
IF the query cache is enabled, and a query has been made to a table mapped as a memcached container, subsequent repeated queries do not reflect the updated underlying table.

How to repeat:
Have query cache enabled.

SQL Query: "select * from test.demo_test;" and note number of rows returned. and note number of rows returned.

Use Memcached API and set a new key/value pair:

set this 0 0 4

Return to SQL prompt and re-execute query "SELECT * FROM test.demo_test;" and note number of rows returned. The Query Result returns the same number of rows as before and does NOT show the new key/value pair (this/that).

Execute a slightly different query and you will see the results:  "SELECT *, NOW() FROM test.demo_test;" and the new values will show.

Repeat the original query "SELECT * FROM test.demo_test;" and note that the new key/value (this/that)is not returned.

Insert a new key/value pair via SQL:

"INSERT INTO test.demo_test SET c1='here', c2='now';"

Repeat original query:

"SELECT * FROM test.demo_test;"

All new rows are reflected in query (this/that and here/now)

Suggested fix:
Memcached commits should invalidate the query cache for the particular table in question. Either after the SET command or a periodic timeout (if slight inconsistencies are acceptable)

Interim work around:

"select *, now() from test.demo_text;"


"select SQL_NO_CACHE * from test.demo_test;"


disable the query cache completely.
[18 Apr 2016 15:06] Troy NOT_FOUND
Or one could just update the documentation with a warning about the query cache issues when using memcached InnoDB plugin.
[19 Apr 2016 9:22] MySQL Verification Team

Thank you for the report.
Observed this with 5.6.30/5.7.12 builds.