File: /home/infowars/askalexjones.com/wp-content/plugins/backup/src/JetBackup/Data/Mysqldump.php
<?php
namespace JetBackup\Data;
use Exception;
use JetBackup\Factory;
use JetBackup\Filesystem\AtomicWrite;
use JetBackup\Log\LogController;
use Mysqldump\Mysqldump as MysqldumpAlias;
use PDO;
use PDOException;
if (!defined( '__JETBACKUP__')) die('Direct access is not allowed');
class Mysqldump extends MysqldumpAlias {
private ArrayData $_data;
private LogController $_logController;
const QUERY_MAX_RETRIES = 10;
const SQLSTATE_ERRORS = ['08S01', '08001', '40001', 'HY000'];
/***
* These SQLSTATE errors indicate connection failures and trigger a retry.
* - '08S01' → Communication link failure (e.g., network disconnect, timeout, or dropped connection)
* - '08001' → Client unable to establish a connection (e.g., incorrect credentials, DNS issues)
* - '40001' → Transaction deadlock detected (e.g., deadlock errors requiring retry)
* - 'HY000' → General MySQL error (covers various issues, including MySQL server disconnects)
*/
/**
* @throws Exception
*/
public function __construct($db_name, $db_user, $db_password, $db_host) {
$this->_data = new ArrayData();
$this->_setDBName($db_name);
$this->_setDBPassword($db_password);
$this->_setDBUser($db_user);
$this->_setDBHost($db_host);
parent::__construct(
'mysql:host='.$this->getDBHost().';port='.$this->getDBPort().';dbname='.$this->getDBName(),
$this->getDBUser(),
$this->getDBPassword(),
[
'compress' => MysqldumpAlias::NONE,
'add-drop-table' => true,
'if-not-exists' => true,
'reset-auto-increment' => true,
'complete-insert' => true,
'default-character-set' => MysqldumpAlias::UTF8MB4,
'extended-insert' => false,
'insert-ignore' => true,
'lock-tables' => false,
'init_commands' => [
// Snapshot current mode safely (NULL-safe)
"SET @jb_sql_mode := IFNULL(@@SESSION.sql_mode, '');",
// Remove modes that commonly break dumps
"SET SESSION sql_mode = TRIM(BOTH ',' FROM
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
CONCAT(',', @jb_sql_mode, ','),
',ONLY_FULL_GROUP_BY,', ','
),
',STRICT_TRANS_TABLES,', ','
),
',STRICT_ALL_TABLES,', ','
),
',NO_ZERO_DATE,', ','
),
',TRADITIONAL,', ','
)
);",
],
]
);
}
public function setDumpSetting($key, $value): void { $this->dumpSettings[$key] = $value; }
public function getDumpSetting($key, $default = null) { return $this->dumpSettings[$key] ?? $default; }
public function set($key, $value) { $this->_data->set($key, $value); }
public function get($key, $default = '') {return $this->_data->get($key, $default);}
public function setLogController(LogController $log) {$this->_logController = $log;}
private function getLogController(): LogController {
if (!isset($this->_logController)) $this->_logController = new LogController();
return $this->_logController;
}
/**
* Check if the given object is a VIEW in the current database.
*
* @param string $name Table or view name
* @return bool true if it's a VIEW, false otherwise
* @throws Exception on query failure
*/
public function _isView(string $name): bool {
if (!$this->dbHandler) {
$this->connect();
}
$sql = "SELECT TABLE_TYPE
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = :db
AND TABLE_NAME = :name
LIMIT 1";
$stmt = $this->dbHandler->prepare($sql);
$stmt->execute([
':db' => $this->getDBName(),
':name' => $name
]);
$type = $stmt->fetchColumn();
return ($type === 'VIEW');
}
private function _setDBHost($db_host) {
if (strpos($db_host, ':') !== false) {
$parts = explode(':', $db_host, 2);
$db_host = $parts[0] ?? 'localhost';
$this->_setDBPort((int) ($parts[1] ?? 3306));
}
$this->set('db_host', $db_host);
}
public function getDBHost() { return $this->get('db_host'); }
private function _setDBPort($db_port) { $this->set('db_port', $db_port); }
public function getDBPort() { return $this->get('db_port', Factory::getSettingsGeneral()->getMySQLDefaultPort()); }
private function _setDBName($db_name) { $this->set('db_name', $db_name); }
public function getDBName() { return $this->get('db_name'); }
private function _setDBUser($db_user) { $this->set('db_user', $db_user); }
public function getDBUser() { return $this->get('db_user'); }
private function _setDBPassword($db_password) { $this->set('db_password', $db_password); }
public function getDBPassword() { return $this->get('db_password'); }
/**
* @throws Exception
*/
public function setInclude(array $include) {
$name = $include[0] ?? null;
// reset first
$this->setDumpSetting('include-tables', []);
$this->setDumpSetting('include-views', []);
$this->setDumpSetting('exclude-tables', []);
if (!$name) return;
$isView = $this->_isView($name);
if ($isView) {
$this->setDumpSetting('include-views', [$name]);
$pattern = '/^(?!' . preg_quote($name, '/') . '$).*/';
$this->setDumpSetting('exclude-tables', [$pattern]);
$this->setDumpSetting('skip-triggers', true);
$this->setDumpSetting('add-drop-table', false);
$this->setDumpSetting('no-create-info', false);
} else {
$this->setDumpSetting('include-tables', [$name]);
$this->setDumpSetting('include-views', [$name]);
$this->setDumpSetting('exclude-tables', []);
}
}
public function getInclude() { return $this->getDumpSetting('include-tables', []); }
public function setExclude($exclude) { $this->setDumpSetting('no-data', $exclude); }
public function getExclude() { return $this->getDumpSetting('no-data', []); }
/**
* @param $buffer
* @return mixed|null
* Detect problematic SET commands that might involve '@OLD_' or '@saved_' variables
*/
private function _checkResume($buffer) {
$problematicSetPattern = '/SET\s+\w+\s*=\s*@[\w_]+/i';
if (preg_match($problematicSetPattern, $buffer)) {
$this->getLogController()->logMessage("Skipping problematic statement: {$buffer}");
return null; // Skip this query
}
return $buffer;
}
/**
* Override connect method with proper retry mechanism.
* @throws Exception
*/
protected function connect() {
$attempts = 0;
$waitTime = 500000; // Start with 500ms
while ($attempts < self::QUERY_MAX_RETRIES) {
try {
$this->getLogController()->logDebug("Attempting to reconnect to MySQL (Attempt #$attempts)");
parent::connect();
if ($this->dbHandler) {
$this->getLogController()->logDebug("Successfully reconnected to MySQL.");
return;
}
} catch (Exception $e) {
$this->getLogController()->logMessage("Reconnect attempt #$attempts failed (SQLSTATE: {$e->getCode()}): " . $e->getMessage());
if (in_array($e->getCode(), self::SQLSTATE_ERRORS)) {
$this->getLogController()->logMessage("Connection error detected (SQLSTATE: {$e->getCode()}), destroying dbHandler...");
$this->dbHandler = null;
}
if ($attempts >= self::QUERY_MAX_RETRIES - 1) {
throw new Exception("MySQL reconnect failed after $attempts attempts: " . $e->getMessage());
}
usleep($waitTime);
$waitTime = min($waitTime * 2, 60000000); // max 60s
}
$attempts++;
}
}
/**
* Import SQL file with resumable progress tracking.
*
* @throws Exception
*/
public function import($path) {
try {
if (!$path || !is_file($path)) throw new Exception("[import] File {$path} does not exist.");
$_table = basename($path);
$_progress_file = $path . ".progress";
$_progress_position = file_exists($_progress_file) ? (int) file_get_contents($_progress_file) : 0;
$handle = fopen($path, 'rb');
if (!$handle) throw new Exception("Failed reading file {$path}. Check access permissions.");
if (!$this->dbHandler) $this->connect();
// BEFORE ANY CHANGES:
$this->query_exec("SET @jb_prev_sql_mode := @@SESSION.sql_mode;");
$this->query_exec("SET @jb_prev_fk := @@SESSION.FOREIGN_KEY_CHECKS;");
$this->query_exec("SET SESSION FOREIGN_KEY_CHECKS=0;");
// Add NO_ENGINE_SUBSTITUTION without clobbering others
$this->query_exec("
SET SESSION sql_mode = TRIM(BOTH ',' FROM
CONCAT_WS(',',
REPLACE(REPLACE(@@SESSION.sql_mode, ',NO_ENGINE_SUBSTITUTION', ''), 'NO_ENGINE_SUBSTITUTION', ''),
'NO_ENGINE_SUBSTITUTION'
)
);
");
// Relax strict/only_full_group_by
$this->query_exec("
SET SESSION sql_mode = TRIM(BOTH ',' FROM
REPLACE(REPLACE(REPLACE(
CONCAT(',', IFNULL(@@SESSION.sql_mode,''), ','),
',ONLY_FULL_GROUP_BY,', ','
), ',STRICT_TRANS_TABLES,', ','
), ',STRICT_ALL_TABLES,', ','
)
);
");
if ($_progress_position > 0) {
fseek($handle, $_progress_position);
$this->getLogController()->logMessage("Resuming import for {$_table}, Position: {$_progress_position}");
} else {
$this->getLogController()->logMessage("Starting new import for: {$_table}");
}
$buffer = '';
while (!feof($handle)) {
$lineRaw = fgets($handle);
if ($lineRaw === false) break;
$lineTrim = ltrim($lineRaw);
if (substr($lineTrim, 0, 2) === '--' || trim($lineRaw) === '') continue;
$buffer .= $lineRaw;
if (preg_match('/;\s*$/', rtrim($lineRaw))) {
try {
$stmt = $this->_checkResume($buffer);
if ($stmt !== null) {
if (stripos($stmt, 'CREATE') !== false && stripos($stmt, 'VIEW') !== false) {
$stmt = self::normalizeCreateView($stmt);
}
$this->query_exec($stmt);
}
try {
AtomicWrite::write($_progress_file, (string) ftell($handle), $this->getLogController());
} catch (Exception $e) {
$this->getLogController()->logError("Failed to update progress file: " . $e->getMessage());
// Continue execution despite progress file write failure
}
$buffer = '';
} catch (PDOException $e) {
$this->getLogController()->logMessage("Failed to execute query: {$buffer}");
$this->getLogController()->logMessage("Error: " . $e->getMessage());
$this->getLogController()->logMessage("SQLSTATE: " . $e->getCode());
throw new Exception("Failed to execute query: {$buffer}");
}
}
}
fclose($handle);
$this->getLogController()->logMessage("Finished importing {$_table}");
if (is_file($_progress_file)) {
@unlink($_progress_file);
$this->getLogController()->logMessage("Progress file for table removed");
}
} catch (Exception $e) {
$this->getLogController()->logMessage("Error: " . $e->getMessage());
throw new Exception($e->getMessage());
} finally {
// Best-effort restore of previous mode
try { $this->query_exec("SET SESSION sql_mode = @jb_prev_sql_mode;"); } catch (\Exception $e) {}
try { $this->query_exec("SET SESSION FOREIGN_KEY_CHECKS=@jb_prev_fk;"); } catch (\Exception $e) {}
try { $this->query_exec("SET SESSION sql_mode = @jb_prev_sql_mode;"); } catch (\Exception $e) {}
}
}
private static function normalizeCreateView(string $sql): string {
if (!preg_match('/\bCREATE\b.*\bVIEW\b/is', $sql)) {
return $sql;
}
$parts = preg_split('/\bAS\b/i', $sql, 2);
$header = $parts[0] ?? $sql;
$body = $parts[1] ?? '';
$header = preg_replace(
'/\/\*!\d+\s+DEFINER\s*=\s*[^*]+SQL\s+SECURITY\s+(?:DEFINER|INVOKER)\s*\*\//i',
' ',
$header
);
$header = preg_replace(
'/\bDEFINER\s*=\s*(?:`[^`]+`@`[^`]+`|\'[^\']+\'@\'[^\']+\'|[^ \t\n\r\f\)]+)\s*/i',
' ',
$header
);
$header = preg_replace('/\bALGORITHM\s*=\s*\w+\s*/i', ' ', $header);
$header = preg_replace('/\bCREATE\s+(?!OR\s+REPLACE\b)/i', 'CREATE OR REPLACE ', $header, 1);
if (preg_match('/\bSQL\s+SECURITY\s+(?:DEFINER|INVOKER)\b/i', $header)) {
$header = preg_replace('/\bSQL\s+SECURITY\s+(?:DEFINER|INVOKER)\b/i', 'SQL SECURITY INVOKER', $header, 1);
} else {
$header = preg_replace(
'/\b(CREATE\s+(?:OR\s+REPLACE\s+)?)(VIEW\b)/i',
'$1SQL SECURITY INVOKER $2',
$header,
1
);
}
$header = preg_replace('/[ \t]+/', ' ', $header);
$header = trim($header);
if ($body === '') {
return $header;
}
return $header . ' AS' . (preg_match('/^\s/', $body) ? '' : ' ') . $body;
}
/**
* Check if a table exists in the current database.
*
* @param string $name Table name (unquoted)
* @param bool $includeViews If true, treat views as existing "tables" as well
* @return bool
* @throws Exception
*/
public function tableExists(string $name, bool $includeViews = true): bool {
if ($name === '') return false;
$attempt = 0;
$waitTime = 500000; // 0.5s
$sql = "SELECT 1
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = :db
AND TABLE_NAME = :name" . ($includeViews ? "" : " AND TABLE_TYPE = 'BASE TABLE'") . "
LIMIT 1";
while ($attempt < self::QUERY_MAX_RETRIES) {
try {
if (!$this->dbHandler) $this->connect();
$stmt = $this->dbHandler->prepare($sql);
$stmt->execute([
':db' => $this->getDBName(),
':name' => $name,
]);
return (bool) $stmt->fetchColumn();
} catch (\PDOException $e) {
$sqlState = (string) $e->getCode();
if (in_array($sqlState, self::SQLSTATE_ERRORS, true)) {
$this->getLogController()->logMessage("tableExists(): SQLSTATE {$sqlState}, retrying (attempt #{$attempt})...");
$this->connect();
usleep($waitTime);
$waitTime = min($waitTime * 2, 60000000);
$attempt++;
continue;
}
throw new Exception("Failed checking existence for table '{$name}' [SQLSTATE {$sqlState}]: " . $e->getMessage(), 0, $e);
}
}
return false;
}
/**
* Execute a query that returns a result set (e.g., SELECT, SHOW TABLES).
*
* @param string $query The SQL query to execute.
* @param array $params Bound parameters
*
* @return array|null Returns an array of results or null on failure.
* @throws Exception
*/
public function query_exec(string $query, array $params = []): ?array {
$waitTime = 500000; // 0.5s
$attempt = 0;
$is_dml = (bool) preg_match('/^\s*(INSERT|UPDATE|DELETE|REPLACE)\b/i', $query);
$is_ddl_or_set = (bool) preg_match('/^\s*(CREATE|ALTER|DROP|RENAME|TRUNCATE|GRANT|REVOKE|ANALYZE|OPTIMIZE|REPAIR|SET)\b/i', $query);
$begin_tx = $is_dml && !$is_ddl_or_set;
$retryable_sqlstates = ['08S01'];
$retryable_drivercodes = [2006, 2013, 1205, 1213];
while ($attempt < self::QUERY_MAX_RETRIES) {
try {
if (!$this->dbHandler) {
$this->getLogController()->logMessage("No database handler, connecting...");
$this->connect();
}
if ($begin_tx) {
if ($this->dbHandler->inTransaction()) {
$this->getLogController()->logMessage("Warning: already in txn, committing previous one.");
try { $this->dbHandler->commit(); } catch (\Throwable $ignore) {}
}
$this->getLogController()->logMessage(
"Starting transaction for: " . (strlen($query) > 90 ? substr($query,0,87) . "..." : $query)
);
$this->dbHandler->beginTransaction();
}
$stmt = $this->dbHandler->prepare($query);
$ok = $stmt->execute($params);
if ($ok) {
if ($begin_tx && $this->dbHandler->inTransaction()) {
$this->getLogController()->logMessage(
"Committing transaction for: " . (strlen($query) > 90 ? substr($query,0,87) . "..." : $query)
);
$this->dbHandler->commit();
}
return $stmt->fetchAll(PDO::FETCH_OBJ);
}
$this->getLogController()->logMessage("Query execution returned false.");
return null;
} catch (PDOException $e) {
if ($begin_tx && $this->dbHandler && $this->dbHandler->inTransaction()) {
$this->getLogController()->logMessage("Rolling back transaction due to error.");
try { $this->dbHandler->rollBack(); } catch (\Throwable $ignore) {}
}
$sqlState = (string) $e->getCode();
$errInfo = property_exists($e, 'errorInfo') ? (array) $e->errorInfo : [];
$driverCode = $errInfo[1] ?? null;
$driverMsg = $errInfo[2] ?? '';
$snippet = (strlen($query) > 300) ? substr($query,0,297) . '...' : $query;
$this->getLogController()->logError(
"[SQL ERROR] SQLSTATE={$sqlState} driverCode=" . var_export($driverCode,true) .
" msg=" . $e->getMessage() . " driverMsg=" . $driverMsg .
" | Query: {$snippet} | Params: " . json_encode($params)
);
$retryable = in_array($sqlState, $retryable_sqlstates, true)
|| (is_int($driverCode) && in_array($driverCode, $retryable_drivercodes, true));
if ($retryable) {
$this->getLogController()->logMessage("Transient DB error; reconnecting and retrying (attempt {$attempt}).");
try { $this->connect(); } catch (\Throwable $ignore) {}
usleep($waitTime);
$waitTime = min($waitTime * 2, 60000000);
$attempt++;
continue;
}
throw new Exception(
"Query error [SQLSTATE {$sqlState}" . ($driverCode !== null ? "/{$driverCode}" : "") . "]: " . $e->getMessage(),
0,
$e
);
}
}
$this->getLogController()->logError("Max retries reached for query.");
return null;
}
}