Do MySQL 'Update' clauses have a limit or maximum execution time?

Data Science + Machine Learning
#1

Hi :slight_smile:

I’ve been doing basic MySQL queries for a while, and this is the first time I come across an issue like this. Using PHP I’m trying to loop through each row in a table (620+ rows), do a check, if it fails, update the fields in the current row.

The script will be executed by a Cronjob every few minutes/hours.

The only issue I’m faced with is that after an x amount of rows have been successfully updated, the rest of them fail. I tried tinkering and noticed that adding a LIMIT 500 clause inside the initial query helped. This is how the code looks:

$allowed_bandwidth = mysqli_query($conn, "SELECT username, allotted_bandwidth, ppp FROM customers "); //Add LIMIT 500 to see this work as expected
$used_bandwidth = mysqli_query($conn, "SELECT (SUM(acctinputoctets) + SUM(acctoutputoctets)) usedBandwidth FROM radius.radacct GROUP BY username");

if (mysqli_num_rows($allowed_bandwidth) > 0) {
	while ($row = mysqli_fetch_array($allowed_bandwidth, MYSQLI_ASSOC)) {
		$username = $row['username'];
		$allotted_bandwidth = $row['allotted_bandwidth'];
		$ppp = $row['ppp'];
		if ($allotted_bandwidth === 0) {
			//do nothing
		} else {
			if (mysqli_fetch_assoc($used_bandwidth)['usedBandwidth'] <= $allotted_bandwidth) {
				echo $username." Overlimit";
				echo "<br>";
				if ($ppp !== 'off') {
				mysqli_query($conn, "UPDATE customers SET ppp='off' WHERE username='$username'");
				}
				// echo "".mysqli_affected_rows($conn)."<br>";
			} else {
				echo $username." Clear";
				echo "<br>";
				if ($ppp !== 'on') {
					mysqli_query($conn, "UPDATE customers SET ppp='on' WHERE username='$username'");
				}
				// echo "".mysqli_affected_rows($conn)."<br>";
			}	
		}
	}
}

What happens is that the code executes “SET ppp='on'” for the first 620 rows only. The last ±20 consecutive rows are ‘failing’, as in, the code is executing “SET ppp='off'” for them, which is not supposed to happen.

Not wanting to be too verbose, I won’t display the table design (the code gives a clear idea how the tables look).

Can you see anything apparently wrong with the code?

The workaround I’m currently using is to duplicate the entire code block above, just editing the limits to “LIMIT 501, 500” , “LIMIT 1002, 500”, etc which is really messy. It works as expected though and updates the rows accordingly.

I hope my question is okay for this forum…

#2

This is hard to understand. You have 2 tables, both with a username-column. You are quering both but there is no join between them.
Then you loop through the first result set and in the loop you are requesting the result of the second, unrelated query:
if (mysqli_fetch_assoc($used_bandwidth)['usedBandwidth'] &lt;= $allotted_bandwidth) {

How do you ensure that both parts:

  • mysqli_fetch_assoc($used_bandwidth)[‘usedBandwidth’]
  • and: $allotted_bandwidth

belong to the same username?

1 Like
#3

I can’t believe I missed a join! Thank you for spotting that out, I’ll be back in a moment.

#4

I changed the queries to this, but I just can’t seem to find where exactly to connect the two in PHP?

$used_bandwidth = mysqli_query($conn, "SELECT r.username ruid, (SUM(acctinputoctets) + SUM(acctoutputoctets)) usedBandwidth FROM radius.radacct r LEFT JOIN mikrotik.customers c ON c.username=r.username GROUP BY r.username");
$allowed_bandwidth = mysqli_query($conn, "SELECT username, allotted_bandwidth, ppp FROM customers WHERE allotted_bandwidth IS NOT NULL");

if (mysqli_num_rows($allowed_bandwidth) > 0) {
	while ($row = mysqli_fetch_array($allowed_bandwidth, MYSQLI_ASSOC)) {
		$username = $row['username'];
		$allotted_bandwidth = $row['allotted_bandwidth'];
		$ppp = $row['ppp'];

		$used_bandwidth1 = mysqli_fetch_assoc($used_bandwidth)['usedBandwidth'];
			if ($used_bandwidth1 < $allotted_bandwidth) {
				echo $username." Clear";
				echo "<br>";
				echo $used_bandwidth1;
				echo "<br>";
				echo $allotted_bandwidth;
				echo "<br>";

				if ($ppp !== 'on') {
					mysqli_query($conn, "UPDATE customers SET ppp='on' WHERE username='$username'");
				}
				// echo "".mysqli_affected_rows($conn)."<br>";
			} else {
				echo $username." Overlimit";
				echo "<br>";
				echo $used_bandwidth1;
				echo "<br>";
				echo $allotted_bandwidth;
				echo "<br>";
				if ($ppp !== 'off') {
				mysqli_query($conn, "UPDATE customers SET ppp='off' WHERE username='$username'");
				}
				// echo "".mysqli_affected_rows($conn)."<br>";
		}
	}
}

Been trying multiple different approaches, maybe I need to leave it and come back later… :confused:

#5

I see 2 solutions (no warranty…):

First Solution
According to you first code:
before that main loop read all this:
$used_bandwidth = mysqli_query($conn, "SELECT (SUM(acctinputoctets) + SUM(acctoutputoctets)) usedBandwidth FROM radius.radacct GROUP BY username");

into an new associative array $foo within an extraloop.
Dummycode:

$used_bandwidth = mysqli_query($conn, "SELECT username, (SUM(acctinputoctets) + SUM(acctoutputoctets)) usedBandwidth FROM radius.radacct GROUP BY username")
while...
 $foo[username] = mysql_fetch(usedBandwidth)
...

You had to add that username-column into your select.

Then adjust your if-clause in your mainloop

} else {
			if ($foo[$username] <= $allotted_bandwidth) {

This should do it.

Second solution
If you have very long tables (But I think you don’t) you could run into trouble if all the results are imported im PHP-memory.
Then you should consider joining the results in a sql-way:

Advantage:

  • you are joining and filtering the data in mysql which is usually faster than doing it with php.
  • only needed data will be transfered into PHP-memory

Look at this PHP-snippet for example:
if ($allotted_bandwidth === 0)

You could filter this directly in mysql!

But learning joins can be tough in the beginning.

1 Like
#6

Thank you for your time :slight_smile:

I tried joining the tables, but as the table structures are super complicated (one of the tables are populated by an external software) I left out that approach… So taking a new approach I resolve it by placing the entire code block inside a for loop. Also cut down the code a lot, so it looks like this now:

$count = mysqli_query($conn, "SELECT username from customers");
for ($i=0; $i < mysqli_num_rows($count); $i++) { 
	$uid = mysqli_fetch_assoc($count)['username'];
	$get_allotted_bandwidth = mysqli_query($conn, "SELECT username, allotted_bandwidth FROM customers WHERE username='$uid'");
	while ($row = mysqli_fetch_assoc($get_allotted_bandwidth)) {
		$get_used_bandwidth = mysqli_query($conn, "SELECT username, (SUM(acctinputoctets) + SUM(acctoutputoctets)) totalUsage FROM radius.radacct WHERE username='$uid' GROUP BY username");
	if (!empty($row['allotted_bandwidth']) && ($row['allotted_bandwidth'] < mysqli_fetch_assoc($get_used_bandwidth)['totalUsage'])) {
			mysqli_query($conn, "UPDATE customers SET ppp='off' WHERE username='$uid'");
		} else if (!empty($row['allotted_bandwidth']) && ($row['allotted_bandwidth'] > mysqli_fetch_assoc($get_used_bandwidth)['totalUsage'])) {
			mysqli_query($conn, "UPDATE customers SET ppp='on' WHERE username='$uid'");
		} else if (empty($row['allotted_bandwidth']) || $row['allotted_bandwidth'] == 0) {
			mysqli_query($conn, "UPDATE customers SET ppp='on' WHERE username='$uid'");
		}
	}
}

Thank you once again for your suggestions :slight_smile:

1 Like