X-Git-Url: https://git.mxchange.org/?a=blobdiff_plain;f=include%2Fdba.php;h=f6120f673fb63b1e0bd69e6f1953ac0393b4fa1c;hb=820afa2284eb603c286921a302d5b827a1b47b9e;hp=33709e863f1349d2a049daad51ecf2f28b1dd879;hpb=0a026515bd5c774e311106e84e707c2f7dfe28e9;p=friendica.git diff --git a/include/dba.php b/include/dba.php index 33709e863f..f6120f673f 100644 --- a/include/dba.php +++ b/include/dba.php @@ -1,6 +1,9 @@ getConfigVariable() instead. +//use Friendica\Core\Config; + use Friendica\Core\System; use Friendica\Database\DBM; use Friendica\Database\DBStructure; @@ -22,16 +25,26 @@ class dba { private static $errorno = 0; private static $affected_rows = 0; private static $in_transaction = false; + private static $in_retrial = false; private static $relation = []; + private static $db_serveraddr = ''; + private static $db_user = ''; + private static $db_pass = ''; + private static $db_name = ''; + private static $db_charset = ''; - public static function connect($serveraddr, $user, $pass, $db, $install = false) { - if (!is_null(self::$db)) { + public static function connect($serveraddr, $user, $pass, $db, $charset = null) + { + if (!is_null(self::$db) && self::connected()) { return true; } - $a = get_app(); - - $stamp1 = microtime(true); + // We are storing these values for being able to perform a reconnect + self::$db_serveraddr = $serveraddr; + self::$db_user = $user; + self::$db_pass = $pass; + self::$db_name = $db; + self::$db_charset = $charset; $serveraddr = trim($serveraddr); @@ -46,21 +59,12 @@ class dba { $user = trim($user); $pass = trim($pass); $db = trim($db); + $charset = trim($charset); if (!(strlen($server) && strlen($user))) { return false; } - if ($install) { - // server has to be a non-empty string that is not 'localhost' and not an IP - if (strlen($server) && ($server !== 'localhost') && filter_var($server, FILTER_VALIDATE_IP) === false) { - if (! dns_get_record($server, DNS_A + DNS_CNAME)) { - self::$error = L10n::t('Cannot locate DNS info for database server \'%s\'', $server); - return false; - } - } - } - if (class_exists('\PDO') && in_array('mysql', PDO::getAvailableDrivers())) { self::$driver = 'pdo'; $connect = "mysql:host=".$server.";dbname=".$db; @@ -69,11 +73,13 @@ class dba { $connect .= ";port=".$port; } - if (isset($a->config["system"]["db_charset"])) { - $connect .= ";charset=".$a->config["system"]["db_charset"]; + if ($charset) { + $connect .= ";charset=".$charset; } + try { self::$db = @new PDO($connect, $user, $pass); + self::$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); self::$connected = true; } catch (PDOException $e) { } @@ -85,21 +91,60 @@ class dba { if (!mysqli_connect_errno()) { self::$connected = true; - if (isset($a->config["system"]["db_charset"])) { - self::$db->set_charset($a->config["system"]["db_charset"]); + if ($charset) { + self::$db->set_charset($charset); } } } // No suitable SQL driver was found. if (!self::$connected) { + self::$driver = null; self::$db = null; } - $a->save_timestamp($stamp1, "network"); return self::$connected; } + /** + * Disconnects the current database connection + */ + public static function disconnect() + { + if (is_null(self::$db)) { + return; + } + + switch (self::$driver) { + case 'pdo': + self::$db = null; + break; + case 'mysqli': + self::$db->close(); + self::$db = null; + break; + } + } + + /** + * Perform a reconnect of an existing database connection + */ + public static function reconnect() { + self::disconnect(); + + $ret = self::connect(self::$db_serveraddr, self::$db_user, self::$db_pass, self::$db_name, self::$db_charset); + return $ret; + } + + /** + * Return the database object. + * @return PDO|mysqli + */ + public static function get_db() + { + return self::$db; + } + /** * @brief Returns the MySQL server version string * @@ -129,7 +174,7 @@ class dba { */ public static function database_name() { $ret = self::p("SELECT DATABASE() AS `db`"); - $data = self::inArray($ret); + $data = self::inArray($ret); return $data[0]['db']; } @@ -141,7 +186,7 @@ class dba { private static function logIndex($query) { $a = get_app(); - if (empty($a->config["system"]["db_log_index"])) { + if (empty(Config::get('system', 'db_log_index'))) { return; } @@ -160,18 +205,18 @@ class dba { return; } - $watchlist = explode(',', $a->config["system"]["db_log_index_watch"]); - $blacklist = explode(',', $a->config["system"]["db_log_index_blacklist"]); + $watchlist = explode(',', Config::get('system', 'db_log_index_watch')); + $blacklist = explode(',', Config::get('system', 'db_log_index_blacklist')); while ($row = dba::fetch($r)) { - if ((intval($a->config["system"]["db_loglimit_index"]) > 0)) { + if ((intval(Config::get('system', 'db_loglimit_index')) > 0)) { $log = (in_array($row['key'], $watchlist) && - ($row['rows'] >= intval($a->config["system"]["db_loglimit_index"]))); + ($row['rows'] >= intval(Config::get('system', 'db_loglimit_index')))); } else { $log = false; } - if ((intval($a->config["system"]["db_loglimit_index_high"]) > 0) && ($row['rows'] >= intval($a->config["system"]["db_loglimit_index_high"]))) { + if ((intval(Config::get('system', 'db_loglimit_index_high')) > 0) && ($row['rows'] >= intval(Config::get('system', 'db_loglimit_index_high')))) { $log = true; } @@ -181,7 +226,7 @@ class dba { if ($log) { $backtrace = debug_backtrace(DEBUG_BACKTRACE_IGNORE_ARGS); - @file_put_contents($a->config["system"]["db_log_index"], DateTimeFormat::utcNow()."\t". + @file_put_contents(Config::get('system', 'db_log_index'), DateTimeFormat::utcNow()."\t". $row['key']."\t".$row['rows']."\t".$row['Extra']."\t". basename($backtrace[1]["file"])."\t". $backtrace[1]["line"]."\t".$backtrace[2]["function"]."\t". @@ -307,7 +352,7 @@ class dba { * For all regular queries please use dba::select or dba::exists * * @param string $sql SQL statement - * @return bool|object statement object + * @return bool|object statement object or result object */ public static function p($sql) { $a = get_app(); @@ -341,7 +386,7 @@ class dba { $orig_sql = $sql; - if (x($a->config,'system') && x($a->config['system'], 'db_callstack')) { + if ($a->getConfigValue('system', 'db_callstack')) { $sql = "/*".System::callstack()." */ ".$sql; } @@ -385,7 +430,12 @@ class dba { } foreach ($args AS $param => $value) { - $stmt->bindParam($param, $args[$param]); + if (is_int($args[$param])) { + $data_type = PDO::PARAM_INT; + } else { + $data_type = PDO::PARAM_STR; + } + $stmt->bindParam($param, $args[$param], $data_type); } if (!$stmt->execute()) { @@ -430,23 +480,23 @@ class dba { break; } - $params = ''; + $param_types = ''; $values = []; foreach ($args AS $param => $value) { if (is_int($args[$param])) { - $params .= 'i'; + $param_types .= 'i'; } elseif (is_float($args[$param])) { - $params .= 'd'; + $param_types .= 'd'; } elseif (is_string($args[$param])) { - $params .= 's'; + $param_types .= 's'; } else { - $params .= 'b'; + $param_types .= 'b'; } $values[] = &$args[$param]; } if (count($values) > 0) { - array_unshift($values, $params); + array_unshift($values, $param_types); call_user_func_array([$stmt, 'bind_param'], $values); } @@ -469,7 +519,27 @@ class dba { $errorno = self::$errorno; logger('DB Error '.self::$errorno.': '.self::$error."\n". - System::callstack(8)."\n".self::replaceParameters($sql, $params)); + System::callstack(8)."\n".self::replaceParameters($sql, $args)); + + // On a lost connection we try to reconnect - but only once. + if ($errorno == 2006) { + if (self::$in_retrial || !self::reconnect()) { + // It doesn't make sense to continue when the database connection was lost + if (self::$in_retrial) { + logger('Giving up retrial because of database error '.$errorno.': '.$error); + } else { + logger("Couldn't reconnect after database error ".$errorno.': '.$error); + } + exit(1); + } else { + // We try it again + logger('Reconnected after database error '.$errorno.': '.$error); + self::$in_retrial = true; + $ret = self::p($sql, $args); + self::$in_retrial = false; + return $ret; + } + } self::$error = $error; self::$errorno = $errorno; @@ -477,16 +547,15 @@ class dba { $a->save_timestamp($stamp1, 'database'); - if (x($a->config,'system') && x($a->config['system'], 'db_log')) { - + if ($a->getConfigValue('system', 'db_log')) { $stamp2 = microtime(true); $duration = (float)($stamp2 - $stamp1); - if (($duration > $a->config["system"]["db_loglimit"])) { + if (($duration > $a->getConfigValue('system', 'db_loglimit'))) { $duration = round($duration, 3); $backtrace = debug_backtrace(DEBUG_BACKTRACE_IGNORE_ARGS); - @file_put_contents($a->config["system"]["db_log"], DateTimeFormat::utcNow()."\t".$duration."\t". + @file_put_contents($a->getConfigValue('system', 'db_log'), DateTimeFormat::utcNow()."\t".$duration."\t". basename($backtrace[1]["file"])."\t". $backtrace[1]["line"]."\t".$backtrace[2]["function"]."\t". substr(self::replaceParameters($sql, $args), 0, 2000)."\n", FILE_APPEND); @@ -536,6 +605,13 @@ class dba { logger('DB Error '.self::$errorno.': '.self::$error."\n". System::callstack(8)."\n".self::replaceParameters($sql, $params)); + // On a lost connection we simply quit. + // A reconnect like in self::p could be dangerous with modifications + if ($errorno == 2006) { + logger('Giving up because of database error '.$errorno.': '.$error); + exit(1); + } + self::$error = $error; self::$errorno = $errorno; } @@ -766,10 +842,25 @@ class dba { */ public static function lock($table) { // See here: https://dev.mysql.com/doc/refman/5.7/en/lock-tables-and-transactions.html - self::e("SET autocommit=0"); + if (self::$driver == 'pdo') { + self::e("SET autocommit=0"); + self::$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, true); + } else { + self::$db->autocommit(false); + } + $success = self::e("LOCK TABLES `".self::escape($table)."` WRITE"); + + if (self::$driver == 'pdo') { + self::$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); + } + if (!$success) { - self::e("SET autocommit=1"); + if (self::$driver == 'pdo') { + self::e("SET autocommit=1"); + } else { + self::$db->autocommit(true); + } } else { self::$in_transaction = true; } @@ -783,9 +874,21 @@ class dba { */ public static function unlock() { // See here: https://dev.mysql.com/doc/refman/5.7/en/lock-tables-and-transactions.html - self::e("COMMIT"); + self::performCommit(); + + if (self::$driver == 'pdo') { + self::$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, true); + } + $success = self::e("UNLOCK TABLES"); - self::e("SET autocommit=1"); + + if (self::$driver == 'pdo') { + self::$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); + self::e("SET autocommit=1"); + } else { + self::$db->autocommit(true); + } + self::$in_transaction = false; return $success; } @@ -796,23 +899,51 @@ class dba { * @return boolean Was the command executed successfully? */ public static function transaction() { - if (!self::e('COMMIT')) { + if (!self::performCommit()) { return false; } - if (!self::e('START TRANSACTION')) { - return false; + + switch (self::$driver) { + case 'pdo': + if (self::$db->inTransaction()) { + break; + } + if (!self::$db->beginTransaction()) { + return false; + } + break; + case 'mysqli': + if (!self::$db->begin_transaction()) { + return false; + } + break; } + self::$in_transaction = true; return true; } + private static function performCommit() + { + switch (self::$driver) { + case 'pdo': + if (!self::$db->inTransaction()) { + return true; + } + return self::$db->commit(); + case 'mysqli': + return self::$db->commit(); + } + return true; + } + /** * @brief Does a commit * * @return boolean Was the command executed successfully? */ public static function commit() { - if (!self::e('COMMIT')) { + if (!self::performCommit()) { return false; } self::$in_transaction = false; @@ -825,11 +956,22 @@ class dba { * @return boolean Was the command executed successfully? */ public static function rollback() { - if (!self::e('ROLLBACK')) { - return false; + $ret = false; + + switch (self::$driver) { + case 'pdo': + if (!self::$db->inTransaction()) { + $ret = true; + break; + } + $ret = self::$db->rollBack(); + break; + case 'mysqli': + $ret = self::$db->rollback(); + break; } self::$in_transaction = false; - return true; + return $ret; } /** @@ -860,7 +1002,7 @@ class dba { * @param array $conditions Field condition(s) * @param array $options * - cascade: If true we delete records in other tables that depend on the one we're deleting through - * relations + * relations (default: true) * @param boolean $in_process Internal use: Only do a commit after the last delete * @param array $callstack Internal use: prevent endless loops * @@ -876,7 +1018,7 @@ class dba { $commands = []; // Create a key for the loop prevention - $key = $table . ':' . implode(':', array_keys($conditions)) . ':' . implode(':', $conditions); + $key = $table . ':' . json_encode($conditions); // We quit when this key already exists in the callstack. if (isset($callstack[$key])) { @@ -903,7 +1045,7 @@ class dba { $rel_def = array_values(self::$relation[$table])[0]; // Create a key for preventing double queries - $qkey = $field . '-' . $table . ':' . implode(':', array_keys($conditions)) . ':' . implode(':', $conditions); + $qkey = $field . '-' . $table . ':' . json_encode($conditions); // When the search field is the relation field, we don't need to fetch the rows // This is useful when the leading record is already deleted in the frontend but the rest is done in the backend @@ -970,8 +1112,8 @@ class dba { // Split the SQL queries in chunks of 100 values // We do the $i stuff here to make the code better readable - $i = $counter[$key_table][$key_condition]; - if (count($compacted[$key_table][$key_condition][$i]) > 100) { + $i = isset($counter[$key_table][$key_condition]) ? $counter[$key_table][$key_condition] : 0; + if (isset($compacted[$key_table][$key_condition][$i]) && count($compacted[$key_table][$key_condition][$i]) > 100) { ++$i; } @@ -1147,29 +1289,9 @@ class dba { $condition_string = self::buildCondition($condition); - $order_string = ''; - if (isset($params['order'])) { - $order_string = " ORDER BY "; - foreach ($params['order'] AS $fields => $order) { - if (!is_int($fields)) { - $order_string .= "`" . $fields . "` " . ($order ? "DESC" : "ASC") . ", "; - } else { - $order_string .= "`" . $order . "`, "; - } - } - $order_string = substr($order_string, 0, -2); - } - - $limit_string = ''; - if (isset($params['limit']) && is_int($params['limit'])) { - $limit_string = " LIMIT " . $params['limit']; - } - - if (isset($params['limit']) && is_array($params['limit'])) { - $limit_string = " LIMIT " . intval($params['limit'][0]) . ", " . intval($params['limit'][1]); - } + $param_string = self::buildParameter($params); - $sql = "SELECT " . $select_fields . " FROM `" . $table . "`" . $condition_string . $order_string . $limit_string; + $sql = "SELECT " . $select_fields . " FROM `" . $table . "`" . $condition_string . $param_string; $result = self::p($sql, $condition); @@ -1226,14 +1348,14 @@ class dba { * @param array $condition * @return string */ - private static function buildCondition(array &$condition = []) + public static function buildCondition(array &$condition = []) { $condition_string = ''; if (count($condition) > 0) { reset($condition); $first_key = key($condition); if (is_int($first_key)) { - $condition_string = " WHERE ".array_shift($condition); + $condition_string = " WHERE (" . array_shift($condition) . ")"; } else { $new_values = []; $condition_string = ""; @@ -1242,6 +1364,30 @@ class dba { $condition_string .= " AND "; } if (is_array($value)) { + /* Workaround for MySQL Bug #64791. + * Never mix data types inside any IN() condition. + * In case of mixed types, cast all as string. + * Logic needs to be consistent with dba::p() data types. + */ + $is_int = false; + $is_alpha = false; + foreach ($value as $single_value) { + if (is_int($single_value)) { + $is_int = true; + } else { + $is_alpha = true; + } + } + + if ($is_int && $is_alpha) { + foreach ($value as &$ref) { + if (is_int($ref)) { + $ref = (string)$ref; + } + } + unset($ref); //Prevent accidental re-use. + } + $new_values = array_merge($new_values, array_values($value)); $placeholders = substr(str_repeat("?, ", count($value)), 0, -2); $condition_string .= "`" . $field . "` IN (" . $placeholders . ")"; @@ -1250,7 +1396,7 @@ class dba { $condition_string .= "`" . $field . "` = ?"; } } - $condition_string = " WHERE " . $condition_string; + $condition_string = " WHERE (" . $condition_string . ")"; $condition = $new_values; } } @@ -1258,6 +1404,39 @@ class dba { return $condition_string; } + /** + * @brief Returns the SQL parameter string built from the provided parameter array + * + * @param array $params + * @return string + */ + public static function buildParameter(array $params = []) + { + $order_string = ''; + if (isset($params['order'])) { + $order_string = " ORDER BY "; + foreach ($params['order'] AS $fields => $order) { + if (!is_int($fields)) { + $order_string .= "`" . $fields . "` " . ($order ? "DESC" : "ASC") . ", "; + } else { + $order_string .= "`" . $order . "`, "; + } + } + $order_string = substr($order_string, 0, -2); + } + + $limit_string = ''; + if (isset($params['limit']) && is_int($params['limit'])) { + $limit_string = " LIMIT " . $params['limit']; + } + + if (isset($params['limit']) && is_array($params['limit'])) { + $limit_string = " LIMIT " . intval($params['limit'][0]) . ", " . intval($params['limit'][1]); + } + + return $order_string.$limit_string; + } + /** * @brief Fills an array with data from a query * @@ -1317,8 +1496,18 @@ class dba { $ret = $stmt->closeCursor(); break; case 'mysqli': - $stmt->free_result(); - $ret = $stmt->close(); + // MySQLi offers both a mysqli_stmt and a mysqli_result class. + // We should be careful not to assume the object type of $stmt + // because dba::p() has been able to return both types. + if ($stmt instanceof mysqli_stmt) { + $stmt->free_result(); + $ret = $stmt->close(); + } elseif ($stmt instanceof mysqli_result) { + $stmt->free(); + $ret = true; + } else { + $ret = false; + } break; }