This is a read-only copy of the MariaDB Knowledgebase generated on 2024-11-15. For the latest, interactive version please visit https://mariadb.com/kb/.

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.

Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.