MaxScale v2.1.2 Read Cache is not discarded after update/insert
We used the new cache filter of MaxScale with v2.1.2 in combination with thew readwritesplit and 3 servcers (but we also tested it with readconnroute and only 1 server with the same result).
We got an old script which updates a value in a mySQL table and afterwards it selects a sum where this value is used in a join. The result is that the sum still delivers the old value from the MaxScale cache. Afterwards, even when reloading the whole webpage with a fresh select, the statement is still outdated until the cache TTL is passed or until we restart MaxScale.
Shouldn't the cache data for this stable/query be discarded by MaxScale automatically after an update/insert?
Our cache config is:
[Cache] type=filter module=cache hard_ttl=300 soft_ttl=290 max_resultset_size=512Ki max_size=100Mi
the script causing this problem is a bit durty but easy to read:
...
$conn = mysqli_connect($hostname, $username, $password, $database); $json = array('status' => 'false'); $sql = "Select id from task_data where task_id =".$_REQUEST["task_id"]." and month =".$_REQUEST["month"]." and year = ".$_REQUEST["year"]; $result = $conn->query($sql); if($result->num_rows > 0){ $data_id = $result->fetch_assoc()["id"]; $sql = "Update task_data set hours = ".$_REQUEST["hours"]." where id = ".$data_id; $result = $conn->query($sql); if($result){ $json['status'] = 'true'; } } else{ $sql = "Insert into task_data (task_id, month, year, hours) Values (".$_REQUEST["task_id"].", ".$_REQUEST["month"].", ".$_REQUEST["year"].", ".$_REQUEST["hours"].");"; $result = $conn->query($sql); if($result){ $json['status'] = 'true'; } } if($json["status"] == "true"){ $task_hours = $conn->query("select ifnull(round(sum(hours),2), 0) as hours from project join task on (project.id = task.project_id) join task_data on (task_data.task_id = task.id) where month = ".$_REQUEST["month"]." and year = ".$_REQUEST['year']." and project.id = ".$_REQUEST["project_id"].";")->fetch_assoc()["hours"]; $project_hours = $work_hours = $conn->query("select ifnull(round(sum(worked_hours),2), 0) as hours from project join day on (day.project_id = project.id) join data on(data.id = day.data_id) where month = ".$_REQUEST["month"]." and year = ".$_REQUEST['year']." and project.id = ".$_REQUEST["project_id"].";")->fetch_assoc()["hours"]; $json["hours"] = $task_hours; $json["project_hours"] = $project_hours; }
...
Answer Answered by Markus Mäkelä in this comment.
This behavior is expected and is explained in the cache filter documentation. The cache invalidation feature can be found in the MXS-1134 Jira item.