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
• Status: Verified
• Category: MySQL Server: Memcached
• Severity: S2 (Serious)
• Version: 5.7, 5.6.30, 5.7.12
• OS: Any
• CPU Architecture: Any
• Tags: innodb, memcached, memcached plugin, query cache

[17 Apr 2016 23:13] Troy NOT_FOUND

Description:
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
that
STORED

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;"

or 

"select SQL_NO_CACHE * from test.demo_test;"

or

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

Hello!

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

Thanks,
Umesh