fix: get child ids for folder in a separate query during movescckit-backports

Signed-off-by: Robin Appelman <robin@icewind.nl>

feat: add option to disable scanner transactions

Signed-off-by: Robin Appelman <robin@icewind.nl>

feat: add request id as comment to all queries

Signed-off-by: Robin Appelman <robin@icewind.nl>

fix(session): Do not update authtoken last_check for passwordless

Signed-off-by: Christoph Wurst <christoph@winzerhof-wurst.at>

feat: add additional logging for database errors

Signed-off-by: Robin Appelman <robin@icewind.nl>

7 files changed, 127 insertions, 21 deletions

diff --git a/config/config.sample.php b/config/config.sample.php
index 48189e067c1..b0cebc0e8b8 100644
--- a/config/config.sample.php
+++ b/config/config.sample.php
@@ -152,6 +152,13 @@ $CONFIG = [
 'dbpersistent' => '',
 
 /**
+ * Add request id to the database query in a comment.
+ *
+ * This can be enabled to assist in mapping database logs to Nextcloud logs.
+ */
+'db.log_request_id' => false,
+
+/**
  * Indicates whether the Nextcloud instance was installed successfully; ``true``
  * indicates a successful installation, and ``false`` indicates an unsuccessful
  * installation.
diff --git a/lib/private/DB/Connection.php b/lib/private/DB/Connection.php
index 85c6a72dfdb..316822eab17 100644
--- a/lib/private/DB/Connection.php
+++ b/lib/private/DB/Connection.php
@@ -54,6 +54,7 @@ use OCP\PreConditionNotMetException;
 use OCP\Profiler\IProfiler;
 use OC\DB\QueryBuilder\QueryBuilder;
 use OC\SystemConfig;
+use OCP\Server;
 use Psr\Log\LoggerInterface;
 
 class Connection extends \Doctrine\DBAL\Connection {
@@ -79,6 +80,11 @@ class Connection extends \Doctrine\DBAL\Connection {
 	/** @var DbDataCollector|null */
 	protected $dbDataCollector = null;
 
+	protected bool $logRequestId;
+	protected string $requestId;
+
+	private ?array $transactionBacktrace = null;
+
 	/**
 	 * Initializes a new instance of the Connection class.
 	 *
@@ -106,6 +112,9 @@ class Connection extends \Doctrine\DBAL\Connection {
 		$this->systemConfig = \OC::$server->getSystemConfig();
 		$this->logger = \OC::$server->get(LoggerInterface::class);
 
+		$this->logRequestId = $this->systemConfig->getValue('db.log_request_id', false);
+		$this->requestId = Server::get(IRequestId::class)->getId();
+
 		/** @var \OCP\Profiler\IProfiler */
 		$profiler = \OC::$server->get(IProfiler::class);
 		if ($profiler->isEnabled()) {
@@ -233,8 +242,7 @@ class Connection extends \Doctrine\DBAL\Connection {
 			$platform = $this->getDatabasePlatform();
 			$statement = $platform->modifyLimitQuery($statement, $limit, $offset);
 		}
-		$statement = $this->replaceTablePrefix($statement);
-		$statement = $this->adapter->fixupStatement($statement);
+		$statement = $this->finishQuery($statement);
 
 		return parent::prepare($statement);
 	}
@@ -255,22 +263,30 @@ class Connection extends \Doctrine\DBAL\Connection {
 	 * @throws \Doctrine\DBAL\Exception
 	 */
 	public function executeQuery(string $sql, array $params = [], $types = [], QueryCacheProfile $qcp = null): Result {
-		$sql = $this->replaceTablePrefix($sql);
-		$sql = $this->adapter->fixupStatement($sql);
+		$sql = $this->finishQuery($sql);
 		$this->queriesExecuted++;
 		$this->logQueryToFile($sql);
-		return parent::executeQuery($sql, $params, $types, $qcp);
+		try {
+			return parent::executeQuery($sql, $params, $types, $qcp);
+		} catch (\Exception $e) {
+			$this->logDatabaseException($e);
+			throw $e;
+		}
 	}
 
 	/**
 	 * @throws Exception
 	 */
 	public function executeUpdate(string $sql, array $params = [], array $types = []): int {
-		$sql = $this->replaceTablePrefix($sql);
-		$sql = $this->adapter->fixupStatement($sql);
+		$sql = $this->finishQuery($sql);
 		$this->queriesExecuted++;
 		$this->logQueryToFile($sql);
-		return parent::executeUpdate($sql, $params, $types);
+		try {
+			return parent::executeUpdate($sql, $params, $types);
+		} catch (\Exception $e) {
+			$this->logDatabaseException($e);
+			throw $e;
+		}
 	}
 
 	/**
@@ -288,11 +304,15 @@ class Connection extends \Doctrine\DBAL\Connection {
 	 * @throws \Doctrine\DBAL\Exception
 	 */
 	public function executeStatement($sql, array $params = [], array $types = []): int {
-		$sql = $this->replaceTablePrefix($sql);
-		$sql = $this->adapter->fixupStatement($sql);
+		$sql = $this->finishQuery($sql);
 		$this->queriesExecuted++;
 		$this->logQueryToFile($sql);
-		return (int)parent::executeStatement($sql, $params, $types);
+		try {
+			return (int)parent::executeStatement($sql, $params, $types);
+		} catch (\Exception $e) {
+			$this->logDatabaseException($e);
+			throw $e;
+		}
 	}
 
 	protected function logQueryToFile(string $sql): void {
@@ -354,11 +374,21 @@ class Connection extends \Doctrine\DBAL\Connection {
 	 * @deprecated 15.0.0 - use unique index and "try { $db->insert() } catch (UniqueConstraintViolationException $e) {}" instead, because it is more reliable and does not have the risk for deadlocks - see https://github.com/nextcloud/server/pull/12371
 	 */
 	public function insertIfNotExist($table, $input, array $compare = null) {
-		return $this->adapter->insertIfNotExist($table, $input, $compare);
+		try {
+			return $this->adapter->insertIfNotExist($table, $input, $compare);
+		} catch (\Exception $e) {
+			$this->logDatabaseException($e);
+			throw $e;
+		}
 	}
 
 	public function insertIgnoreConflict(string $table, array $values) : int {
-		return $this->adapter->insertIgnoreConflict($table, $values);
+		try {
+			return $this->adapter->insertIgnoreConflict($table, $values);
+		} catch (\Exception $e) {
+			$this->logDatabaseException($e);
+			throw $e;
+		}
 	}
 
 	private function getType($value) {
@@ -516,6 +546,16 @@ class Connection extends \Doctrine\DBAL\Connection {
 		return $schema->tablesExist([$table]);
 	}
 
+	protected function finishQuery(string $statement): string {
+		$statement = $this->replaceTablePrefix($statement);
+		$statement = $this->adapter->fixupStatement($statement);
+		if ($this->logRequestId) {
+			return $statement . " /* reqid: " . $this->requestId . " */";
+		} else {
+			return $statement;
+		}
+	}
+
 	// internal use
 	/**
 	 * @param string $statement
@@ -608,4 +648,35 @@ class Connection extends \Doctrine\DBAL\Connection {
 			return new Migrator($this, $config, $dispatcher);
 		}
 	}
+
+	public function beginTransaction() {
+		if (!$this->inTransaction()) {
+			$this->transactionBacktrace = debug_backtrace(DEBUG_BACKTRACE_IGNORE_ARGS);
+		}
+		return parent::beginTransaction();
+	}
+
+	public function commit() {
+		$result = parent::commit();
+		if ($this->getTransactionNestingLevel() === 0) {
+			$this->transactionBacktrace = null;
+		}
+		return $result;
+	}
+
+	public function rollBack() {
+		$result = parent::rollBack();
+		if ($this->getTransactionNestingLevel() === 0) {
+			$this->transactionBacktrace = null;
+		}
+		return $result;
+	}
+
+	public function logDatabaseException(\Exception $exception): void {
+		if ($exception instanceof Exception\UniqueConstraintViolationException) {
+			$this->logger->info($exception->getMessage(), ['exception' => $exception, 'transaction' => $this->transactionBacktrace]);
+		} else {
+			$this->logger->error($exception->getMessage(), ['exception' => $exception, 'transaction' => $this->transactionBacktrace]);
+		}
+	}
 }
diff --git a/lib/private/DB/ConnectionAdapter.php b/lib/private/DB/ConnectionAdapter.php
index a53c7ecd994..2de7c5a565b 100644
--- a/lib/private/DB/ConnectionAdapter.php
+++ b/lib/private/DB/ConnectionAdapter.php
@@ -242,4 +242,8 @@ class ConnectionAdapter implements IDBConnection {
 	public function getInner(): Connection {
 		return $this->inner;
 	}
+
+	public function logDatabaseException(\Exception $exception) {
+		$this->inner->logDatabaseException($exception);
+	}
 }
diff --git a/lib/private/DB/QueryBuilder/QueryBuilder.php b/lib/private/DB/QueryBuilder/QueryBuilder.php
index 2c84951e711..2511527f40c 100644
--- a/lib/private/DB/QueryBuilder/QueryBuilder.php
+++ b/lib/private/DB/QueryBuilder/QueryBuilder.php
@@ -277,7 +277,12 @@ class QueryBuilder implements IQueryBuilder {
 			]);
 		}
 
-		$result = $this->queryBuilder->execute();
+		try {
+			$result = $this->queryBuilder->execute();
+		} catch (\Exception $e) {
+			$this->connection->logDatabaseException($e);
+			throw $e;
+		}
 		if (is_int($result)) {
 			return $result;
 		}
diff --git a/lib/private/Files/Cache/Cache.php b/lib/private/Files/Cache/Cache.php
index 5a3e38dd569..02a1c1a25a3 100644
--- a/lib/private/Files/Cache/Cache.php
+++ b/lib/private/Files/Cache/Cache.php
@@ -705,6 +705,11 @@ class Cache implements ICache {
 			if ($sourceData['mimetype'] === 'httpd/unix-directory') {
 				//update all child entries
 				$sourceLength = mb_strlen($sourcePath);
+
+				$childIds = $this->getChildIds($sourceStorageId, $sourcePath);
+
+				$childChunks = array_chunk($childIds, 1000);
+
 				$query = $this->connection->getQueryBuilder();
 
 				$fun = $query->func();
@@ -717,7 +722,7 @@ class Cache implements ICache {
 					->set('path_hash', $fun->md5($newPathFunction))
 					->set('path', $newPathFunction)
 					->where($query->expr()->eq('storage', $query->createNamedParameter($sourceStorageId, IQueryBuilder::PARAM_INT)))
-					->andWhere($query->expr()->like('path', $query->createNamedParameter($this->connection->escapeLikeParameter($sourcePath) . '/%')));
+					->andWhere($query->expr()->in('fileid', $query->createParameter('files')));
 
 				// when moving from an encrypted storage to a non-encrypted storage remove the `encrypted` mark
 				if ($sourceCache->hasEncryptionWrapper() && !$this->hasEncryptionWrapper()) {
@@ -730,19 +735,22 @@ class Cache implements ICache {
 				for ($i = 1; $i <= $retryLimit; $i++) {
 					try {
 						$this->connection->beginTransaction();
-						$query->executeStatement();
+						foreach ($childChunks as $chunk) {
+							$query->setParameter('files', $chunk, IQueryBuilder::PARAM_INT_ARRAY);
+							$query->executeStatement();
+						}
 						break;
 					} catch (\OC\DatabaseException $e) {
 						$this->connection->rollBack();
 						throw $e;
 					} catch (RetryableException $e) {
+						$this->connection->rollBack();
+
 						// Simply throw if we already retried 4 times.
 						if ($i === $retryLimit) {
 							throw $e;
 						}
 
-						$this->connection->rollBack();
-
 						// Sleep a bit to give some time to the other transaction to finish.
 						usleep(100 * 1000 * $i);
 					}
@@ -784,6 +792,15 @@ class Cache implements ICache {
 		}
 	}
 
+	private function getChildIds(int $storageId, string $path): array {
+		$query = $this->connection->getQueryBuilder();
+		$query->select('fileid')
+			->from('filecache')
+			->where($query->expr()->eq('storage', $query->createNamedParameter($storageId, IQueryBuilder::PARAM_INT)))
+			->andWhere($query->expr()->like('path', $query->createNamedParameter($this->connection->escapeLikeParameter($path) . '/%')));
+		return $query->executeQuery()->fetchAll(\PDO::FETCH_COLUMN);
+	}
+
 	/**
 	 * remove all entries for files that are stored on the storage from the cache
 	 */
diff --git a/lib/private/Files/Cache/Scanner.php b/lib/private/Files/Cache/Scanner.php
index fd1aba9f99b..b820fda1dde 100644
--- a/lib/private/Files/Cache/Scanner.php
+++ b/lib/private/Files/Cache/Scanner.php
@@ -37,6 +37,7 @@ namespace OC\Files\Cache;
 
 use Doctrine\DBAL\Exception;
 use OC\Files\Storage\Wrapper\Encryption;
+use OC\SystemConfig;
 use OCP\Files\Cache\IScanner;
 use OCP\Files\ForbiddenException;
 use OCP\Files\NotFoundException;
@@ -95,7 +96,10 @@ class Scanner extends BasicEmitter implements IScanner {
 		$this->storage = $storage;
 		$this->storageId = $this->storage->getId();
 		$this->cache = $storage->getCache();
-		$this->cacheActive = !\OC::$server->getConfig()->getSystemValueBool('filesystem_cache_readonly', false);
+		/** @var SystemConfig $config */
+		$config = \OC::$server->get(SystemConfig::class);
+		$this->cacheActive = !$config->getValue('filesystem_cache_readonly', false);
+		$this->useTransactions = !$config->getValue('filescanner_no_transactions', false);
 		$this->lockingProvider = \OC::$server->getLockingProvider();
 		$this->connection = \OC::$server->get(IDBConnection::class);
 	}
diff --git a/lib/private/User/Session.php b/lib/private/User/Session.php
index b6d6adf6523..45898231503 100644
--- a/lib/private/User/Session.php
+++ b/lib/private/User/Session.php
@@ -755,8 +755,6 @@ class Session implements IUserSession, Emitter {
 				return false;
 			}
 
-			$dbToken->setLastCheck($now);
-			$this->tokenProvider->updateToken($dbToken);
 			return true;
 		}