MaxScale v2.1.2 Read Cache is not discarded after update/insert - Source

Revision: 54794
User: Dr. Sven Abels
Date: 2017-05-21 10:33

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:
<<code>>
[Cache]
type=filter
module=cache
hard_ttl=300
soft_ttl=290
max_resultset_size=512Ki
max_size=100Mi
<</code>>

the script causing this problem is a bit durty but easy to read:

...
<<code>>
    $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;
	}
<</code>>

...

Answer

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.

MaxScale v2.1.2 Read Cache is not discarded after update/insert - Source

Answer

Products

Services

Pricing

Resources

About Us

Download MariaDB