I have created some PHP code for my website to call a stored procedure in my database. This will get text from a table so that I can dynamically update the text on the web page without modifying it in code.
This part works very well so far. However, When I open the MySQL error log I see the following message printed:
Aborted connection 161 to db: 'dbname' user: 'username' host: 'localhost' (Got an error reading communication packets)
I have checked the firewall, which is inactive. I have attempted using 127.0.0.1 instead of localhost in the PHP code. I have cleared the results using "mysqli_free_result($result);", this did seem to remove one of the two errors I got per query. I have checked the max allowed packet size, which is 16M. I have un-commented the extension mysqli in the php.ini file.
PHP:
<?php
$servername = "localhost";
$username = "username";
$password = "password ";
$dbname = "dbname";
// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
$sql = "Call StoredProcedure('primarykey',@OutValue);";
$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0) {
// output data of each row
while($row = mysqli_fetch_assoc($result)) {
echo $row["ColumnHeader"];
}
} else {
echo "0 results";
}
mysqli_free_result($result);
$t_id = mysqli_thread_id($conn);
mysqli_kill($conn,$t_id);
mysqli_close($conn);
?>
SQL:
CREATE DEFINER=`username`@`hostname` PROCEDURE `StoredProcedure`(IN PrimaryKey
VARCHAR(50), OUT Result VARCHAR(50))
BEGIN
start transaction;
select ColumnName
from dbname.tablename
where PrimaryKeyColumn = PrimaryKey;
commit;
END
As I mentioned, I am getting the expected result from my query and it is perfectly functional, so I am not sure what can be the cause. The server is running MySQL version 5.7.27-0ubuntu0.18.04.1((ubuntu)). Any help with this would be greatly appreciated!
Update - 11/Sep/2019:
I have attempted to check how long the execution of the my PHP script takes. To do this I added the following code borrowed from this thread: Tracking the script execution time in PHP.
// Script start
$rustart = getrusage();
// Code ...
// Script end
function rutime($ru, $rus, $index) {
return ($ru["ru_$index.tv_sec"]*1000 + intval($ru["ru_$index.tv_usec"]/1000))
- ($rus["ru_$index.tv_sec"]*1000 + intval($rus["ru_$index.tv_usec"]/1000));
}
$ru = getrusage();
echo "This process used " . rutime($ru, $rustart, "utime") .
" ms for its computations\n";
echo "It spent " . rutime($ru, $rustart, "stime") .
" ms in system calls\n";
However, the result was the following:
This process used 0 ms for its computations It spent 1 ms in system calls
Which should not cause any timeouts.
I also activated the general log file on the server and i did see that the it would track the query as follows:
Command Type, Detail
Connect, username@hostname on dbname using Socket
Query, Call StoredProcedure('PrimaryKey', @result)
I am curious about there being no log saying disconnect though I do not know if this is default behaviour from MySQL.
Another curious thing I discovered was that MySQL Workbench states my query time for the stored procedure is on average 7ms but all resources I could find states that PHP waits for the query to finish before continuing.
Query, Total Time, Max Time, Avg Time
CALL StoredProcedure ( username @ hostname ) , 98.94, 66.26, 7.07
In short, I still have not found any solutions to the issue, but potentially some leads that could eventually lead to a resolution.
Update - 18/Sep/2019:
After more digging on the issue I've come across the following thread: https://dba.stackexchange.com/questions/144773/mysql-aborted-connection-got-an-error-reading-communication-packets
It is suggesting that due to MySQL and PHP being installed on the same server, they are competing for RAM. Considering my server is running on a Raspberry Pi 3 model B, this seems like a plausible explanation for the issue I am facing.
