I have an app that relies on MySQL to fetch a user's data. The way it works is like this:
- React JS fetches get-account.php
- get-account.php retrieves information based on the session token (which is a randomly generated string). In the process, it does 9-12 database operations.
- React JS gets back the info from get-account.php
As users use the site, every page requires a request to get-account.php (in order to update the data for the user). However, I've noticed that after exactly 30 requests, it slows down CONSIDERABLY, sometimes taking 60-90 seconds to retrieve the user data back. After the 60-90 seconds, it gives me the error:
Fatal error: Uncaught Exception: Prepare failed: (0) MySQL server has gone away in /home/u359419547/domains/quizzynow.com/public_html/php/classes/user.php:2758 Stack trace: #0 /home/u359419547/domains/quizzynow.com/public_html/php/classes/user.php(2460): User->send_mass_db() #1 /home/u359419547/domains/quizzynow.com/public_html/php/classes/session_token.php(129): User->on_login() #2 /home/u359419547/domains/quizzynow.com/public_html/php/imports.php(518): SessionToken->get_data_for_client() #3 /home/u359419547/domains/quizzynow.com/public_html/php/testing2.php(26): get_user_info() #4 {main} thrown in /home/u359419547/domains/quizzynow.com/public_html/php/classes/user.php on line 2758
When I comment out the database operation on line 2758, it still results in the same error.
I have tried using the MySQL command SHOW PROCESSLIST, which shows 3 processes as the delay is happening. After a bit, it goes back to 2, but the delay continues to happen for several seconds, before giving me the MySQL has gone away error.
I was told that I need to manually close the MySQL connection, by a Hostinger support member. I thought that MySQL connections were automatically closed.
For reference. I am using a singleton database structure, where I am using ->getInstance()
Here's my Database class:
<?php
define('DB_SERVER', '...');
define('DB_USERNAME', '...');
define('DB_PASSWORD', '...');
define('DB_NAME', '...');
define('MYSQL_GONE_AWAY_ERROR_CODE', 2006);
define('MYSQL_DUPLICATE_ENTRY_ERROR_CODE', 1062);
define('MYSQL_QUERY_INTERRUPTED_ERROR_CODE', 1317);
class Database {
/**
* @var array local cache for prepared statements
*/
protected static $prepared_statement_cache = [];
private static $instance = NULL;
private $link;
private $inTransaction;
public $error;
/**
* Constructs a new Database instance and establishes a connection.
*/
public function __construct() {
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$this->connect();
}
/**
* Establishes a connection to the database.
*/
private function connect() {
try {
$this->link = new mysqli(DB_SERVER, DB_USERNAME, DB_PASSWORD, DB_NAME);
} catch (mysqli_sql_exception $e) {
# log the error
\error_log( 'Database::connect exception' );
\error_log( $e->getMessage() );
\error_log( $e->getTraceAsString() );
# recover
$this->handleException($e);
}
}
/**
* Retrieves the instance of the Database class.
*
* @return Database The Database instance.
*/
public static function getInstance() {
if (self::$instance == null) {
self::$instance = new Database();
}
return self::$instance;
}
/**
* Escapes special characters in a string for use in an SQL statement.
*
* @param string $value The string to be escaped.
* @return string The escaped string.
*/
public function escapeString($value) {
return $this->link->real_escape_string($value);
}
/**
* get the error
*/
public function get_error_no () : string {
$result = '';
if (isset($this->link)) {
$result = $this->link->errno;
}
return $result;
}
/**
* Prepares an SQL statement for execution.
*
* @param string $query The SQL query.
* @return mysqli_stmt|false The prepared statement or false on failure.
*/
public function prepare($query) {
// echo("prepare: ");
// debug_print_backtrace();
// echo("\n\n");
try {
if (!$this->link || $this->link->errno == MYSQL_GONE_AWAY_ERROR_CODE) {
//echo("SOMETHING HAPPENED");
//echo("ERRNO: " . $this->link->errno);
$this->reconnect();
}
$this->error = null; // Clear the error before executing a new query
$stmt = $this->link->prepare($query);
# done
return $stmt;
} catch (mysqli_sql_exception $e) {
# log the error
\error_log( 'Database::prepare SQL: ' . print_r($query, true) );
\error_log( $e->getMessage() );
\error_log( $e->getTraceAsString() );
# recover ?
$this->handleException($e);
}
return false;
}
/**
* Prepares an SQL statement for execution. Cached version
*
* @param string $query The SQL query.
* @return mysqli_stmt|false The prepared statement or false on failure.
*/
public function prepare_cached (string $query) {
// echo("prepare: ");
// debug_print_backtrace();
// echo("\n\n");
try {
if (!$this->link || $this->link->errno == MYSQL_GONE_AWAY_ERROR_CODE) {
//echo("SOMETHING HAPPENED");
//echo("ERRNO: " . $this->link->errno);
$this->reconnect();
}
$this->error = null; // Clear the error before executing a new query
# use the cached version
if (isset(self::$prepared_statement_cache[ $query ]) and (self::$prepared_statement_cache[ $query ] instanceof \mysqli_stmt)) {
$stmt = self::$prepared_statement_cache[ $query ];
#echo "I", $query, \PHP_EOL;
#echo json_encode($stmt->sqlstate); #########
#echo \PHP_EOL;
} else {
$stmt = $this->link->prepare($query);
self::$prepared_statement_cache[ $query ] = $stmt;
# echo "NEW ", $query, \PHP_EOL, \PHP_EOL;
}
# done
return $stmt;
} catch (mysqli_sql_exception $e) {
# log the error
\error_log( 'Database::prepare_cached SQL: ' . print_r($query, true) );
\error_log( $e->getMessage() );
\error_log( $e->getTraceAsString() );
# recover ?
$this->handleException($e);
}
return false;
}
public function get_insert_id() {
return mysqli_insert_id($this->link);
}
public function query($query, $params = [])
{
// echo("query\n\n");
// debug_print_backtrace();
try {
if (!$this->link || $this->link->errno == MYSQL_GONE_AWAY_ERROR_CODE) {
$this->reconnect();
// echo("SOMETHING HAPPENED");
//echo("ERRNO: " . $this->link->errno);
}
$this->error = null; // Clear the error before executing a new query
$stmt = $this->link->prepare($query);
if ($stmt === false) {
throw new Exception("Prepare statement failed: (" . $this->link->errno . ") " . $this->link->error);
}
if (!empty($params)) {
$types = "";
$paramValues = [];
foreach ($params as $param) {
if (is_int($param)) {
$types .= "i";
} else if (is_float($param)) {
$types .= "d";
} else {
$types .= "s";
}
$paramValues[] = $param;
}
array_unshift($paramValues, $types);
$bindParams = [];
foreach ($paramValues as $key => $value) {
$bindParams[$key] = &$paramValues[$key];
}
call_user_func_array([$stmt, 'bind_param'], $bindParams);
}
if ($stmt->execute() === false) {
throw new Exception("Execute failed: (" . $stmt->errno . ") " . $stmt->error);
}
$result = $stmt->get_result();
$stmt->close();
return $result;
} catch (Exception $e) {
# log the error
\error_log( 'Database::query SQL: ' . print_r($query, true) );
\error_log( 'Database::query params: ' . print_r($params, true) );
\error_log( $e->getMessage() );
\error_log( $e->getTraceAsString() );
# recover
$this->handleException($e);
}
}
/**
* Re-establishes the database connection.
*/
private function reconnect() {
try {
$this->link = new mysqli(DB_SERVER, DB_USERNAME, DB_PASSWORD, DB_NAME);
} catch (mysqli_sql_exception $e) {
# log the error
\error_log( 'Database::reconnect exception');
\error_log( $e->getMessage() );
\error_log( $e->getTraceAsString() );
# recover ?
$this->handleException($e);
}
}
/**
* Starts a new database transaction.
*/
public function begin_transaction() {
try {
if (!$this->inTransaction) {
$this->link->begin_transaction();
$this->inTransaction = true;
}
} catch (mysqli_sql_exception $e) {
# log the error
\error_log( $e->getMessage() );
\error_log( $e->getTraceAsString() );
# recover ?
$this->handleException($e);
}
}
/**
* Rolls back the current transaction.
*/
public function rollback() {
try {
if ($this->inTransaction) {
$this->link->rollback();
$this->inTransaction = false;
}
} catch (mysqli_sql_exception $e) {
# log the error
\error_log( $e->getMessage() );
\error_log( $e->getTraceAsString() );
# recover ?
$this->handleException($e);
}
}
/**
* Commits the current transaction.
*/
public function commit() {
try {
if ($this->inTransaction) {
$this->link->commit();
$this->inTransaction = false;
}
} catch (mysqli_sql_exception $e) {
# log the error
\error_log( $e->getMessage() );
\error_log( $e->getTraceAsString() );
# recover ?
$this->handleException($e);
}
}
/**
* Handles exceptions thrown during database operations.
*
* @param mysqli_sql_exception $e The exception object.
*/
private function handleException(mysqli_sql_exception $e) {
$this->error = $e->getMessage();
switch ($e->getCode()) {
case MYSQL_GONE_AWAY_ERROR_CODE:
$this->reconnect();
break;
case MYSQL_DUPLICATE_ENTRY_ERROR_CODE:
// Handle duplicate entry error
break;
case MYSQL_QUERY_INTERRUPTED_ERROR_CODE:
// Handle query interrupted error
break;
default:
throw $e;
}
}
}
?>
And here's how it is being used:
public function send_mass_db($properties) {
$setParts = [];
$types = '';
$values = [];
$db = Database::getInstance();
foreach ($properties as $prop => $val) {
$setParts[] = "`{$prop}` = ?";
$types .= 's'; // This assumes string values; adjust as necessary
$values[] = $val;
}
$sql = "UPDATE `users` SET " . implode(', ', $setParts) . " WHERE `id` = ?";
$types .= 'i'; // add type for $userid
$values[] = $this->id; // add $userid to the values
$stmt = $db->prepare($sql);
if ($stmt === false) {
throw new Exception("Prepare failed: (" . $db->get_error_no() . ") " . $db->error);
}
if (!$stmt instanceof mysqli_stmt) {
throw new Exception("Statement is not a valid instance of mysqli_stmt.");
}
$params = array_merge([$types], $values);
$tmp = array();
foreach($params as $key => $value) $tmp[$key] = &$params[$key];
call_user_func_array([$stmt, 'bind_param'], $tmp);
if (!$stmt->execute()) {
throw new Exception("Execute failed: (" . $stmt->errno . ") " . $stmt->error);
}
}
If someone can tell me how I can fix this, it would be VERY appreciated, because this is an issue I've had for 8 months and it has significantly impacted the release date.
Thank you!
