Aurora inconsistent cache after failover

I had experience with an Aurora DB cluster with a inconsistent result of caches across the different cluster nodes. The initial configuration was an Aurora DB Cluster with instances , one writer and two readers.
The issue was spot just after a manual failover, the same query on the writer and on the readers gives a different results, it looked like the server cached the query and it gives back always the same result always even though the data of the table had changed.
We proved that it was a cache issue because changing the query to still return the same result , like adding an alias or an hint, we could retrieve the correct result on all servers.
The reader that had the problem was the former writer.

Accessing to the table with the same query give the wrong result

sebastianoc@scala-sb:~$ for ins in 1 2 3 ; do mysql     \
-h photo-service-database-$ins-read-replica-4-internal \
-e "select * from a order by id desc limit 1;" tmp1 ; done

Instance#1
+-------+---------------------+----------------+
| id    | d                   | col1           |
+-------+---------------------+----------------+
| 10227 | 2016-10-03 11:11:34 | ppvhn281l7ksg5 |
+-------+---------------------+----------------+
Instance#2
+------+---------------------+-----------------+
| id   | d                   | col1            |
+------+---------------------+-----------------+
| 1303 | 2016-10-03 10:34:53 | pp1jqz7gm55qj4v |
+------+---------------------+-----------------+
Instance#3
+-------+---------------------+----------------+
| id    | d                   | col1           |
+-------+---------------------+----------------+
| 10228 | 2016-10-03 11:11:34 | ppvhn281l7ksg5 |
+-------+---------------------+----------------+

While the issue was happening, I was doing inserts on the writer so the number of values is increasing at any time.

Running a query that gives the same result, like having an table alias , gives the right result on all instances.

select * from a a order by id desc limit 1;

Instance#1
+-------+---------------------+----------------+
| id    | d                   | col1           |
+-------+---------------------+----------------+
| 10328 | 2016-10-03 11:13:34 | ppvhn281l7ksg5 |
+-------+---------------------+----------------+
Instance#2
+-------+---------------------+-----------------+
| id    | d                   | col1            |
+-------+---------------------+-----------------+
| 10328 | 2016-10-03 11:13:34 | pp1jqz7gm55qj4v |
+-------+---------------------+-----------------+
Instance#3
+-------+---------------------+----------------+
| id    | d                   | col1           |
+-------+---------------------+----------------+
| 10328 | 2016-10-03 11:13:34 | ppvhn281l7ksg5 |
+-------+---------------------+----------------+

Confirm the issue is the cache

Sqlcachelog has old value, reading from the disk gives the right value

sebastianoc@scala-sb:~$for ins in 1 2 3 ; do mysql \
-h photo-service-database-$ins-read-replica-4-internal \
-e "select SQL_NO_CACHE * from a order by id desc limit 1;" tmp1 ; done

+-------+---------------------+----------------+
| id    | d                   | col1           |
+-------+---------------------+----------------+
| 13270 | 2016-10-03 11:23:45 | ppvhn281l7ksg5 |
+-------+---------------------+----------------+
+-------+---------------------+----------------+
| id    | d                   | col1           |
+-------+---------------------+----------------+
| 13271 | 2016-10-03 11:23:46 | ppvhn281l7ksg5 |
+-------+---------------------+----------------+
+-------+---------------------+----------------+
| id    | d                   | col1           |
+-------+---------------------+----------------+
| 13272 | 2016-10-03 11:23:46 | ppvhn281l7ksg5 |
+-------+---------------------+----------------+

Workaround

As temporary solution was to redirect the reads to the 'good' reader and writer and a permanent fix would be restart the server. After some times the cache was refreshed and the new value was stored in the cache.

Aws support

They recommended to disable and re-enable the cache, but that would require a two times restart of the instance. You can do this by changing the DB parameter query_cache_type value to 0, it is a static parameter so it require a reboot.