fix(db): Store last insert id before reconnect

During a reconnect we are losing the connection and when the
realLastInsertId call is the one triggering the reconnect, it
does not return the ID. But inside the reconnect, we were able
to save the last insert id, so calling it a second time is going
to be successful.
We can not return the result on the initial call, as we are already
way deeper in the stack performing the actual database query on
the doctrine driver.

Signed-off-by: Joas Schilling <coding@schilljs.com>

4 files changed, 43 insertions, 10 deletions

diff --git a/lib/private/DB/Adapter.php b/lib/private/DB/Adapter.php
index 8f1b8e6d75f..edd8c1bf023 100644
--- a/lib/private/DB/Adapter.php
+++ b/lib/private/DB/Adapter.php
@@ -28,11 +28,25 @@ class Adapter {
 	/**
 	 * @param string $table name
 	 *
-	 * @return int id of last insert statement
+	 * @return int id of last insert statement, 0 in case there was no INSERT before or it failed to get the ID
 	 * @throws Exception
 	 */
-	public function lastInsertId($table) {
-		return (int)$this->conn->realLastInsertId($table);
+	public function lastInsertId($table, bool $allowRetry = true): int {
+		$return = $this->conn->realLastInsertId($table);
+		if ($return === 0 && $allowRetry) {
+			/**
+			 * During a reconnect we are losing the connection and when the
+			 * realLastInsertId call is the one triggering the reconnect, it
+			 * does not return the ID. But inside the reconnect, we were able
+			 * to save the last insert id, so calling it a second time is going
+			 * to be successful.
+			 * We can not return the result on the initial call, as we are already
+			 * way deeper in the stack performing the actual database query on
+			 * the doctrine driver.
+			 */
+			return $this->lastInsertId($table, false);
+		}
+		return $return;
 	}
 
 	/**
diff --git a/lib/private/DB/AdapterOCI8.php b/lib/private/DB/AdapterOCI8.php
index 0a509090bca..f5ad9f7c934 100644
--- a/lib/private/DB/AdapterOCI8.php
+++ b/lib/private/DB/AdapterOCI8.php
@@ -8,7 +8,7 @@
 namespace OC\DB;
 
 class AdapterOCI8 extends Adapter {
-	public function lastInsertId($table) {
+	public function lastInsertId($table, bool $allowRetry = true): int {
 		if (is_null($table)) {
 			throw new \InvalidArgumentException('Oracle requires a table name to be passed into lastInsertId()');
 		}
diff --git a/lib/private/DB/AdapterPgSql.php b/lib/private/DB/AdapterPgSql.php
index db48c81c2c5..b321fcf4715 100644
--- a/lib/private/DB/AdapterPgSql.php
+++ b/lib/private/DB/AdapterPgSql.php
@@ -9,7 +9,7 @@ namespace OC\DB;
 
 class AdapterPgSql extends Adapter {
 
-	public function lastInsertId($table) {
+	public function lastInsertId($table, bool $allowRetry = true): int {
 		$result = $this->conn->executeQuery('SELECT lastval()');
 		$val = $result->fetchOne();
 		$result->free();
diff --git a/lib/private/DB/Connection.php b/lib/private/DB/Connection.php
index 96dd578b2ef..4ba2d2a341d 100644
--- a/lib/private/DB/Connection.php
+++ b/lib/private/DB/Connection.php
@@ -92,6 +92,8 @@ class Connection extends PrimaryReadReplicaConnection {
 	protected ShardConnectionManager $shardConnectionManager;
 	protected AutoIncrementHandler $autoIncrementHandler;
 	protected bool $isShardingEnabled;
+	protected bool $disableReconnect = false;
+	protected int $lastInsertId = 0;
 
 	public const SHARD_PRESETS = [
 		'filecache' => [
@@ -510,9 +512,9 @@ class Connection extends PrimaryReadReplicaConnection {
 	 * because the underlying database may not even support the notion of AUTO_INCREMENT/IDENTITY
 	 * columns or sequences.
 	 *
-	 * @param string $seqName Name of the sequence object from which the ID should be returned.
+	 * @param ?string $name Name of the sequence object from which the ID should be returned.
 	 *
-	 * @return int the last inserted ID.
+	 * @return int the last inserted ID, 0 in case there was no INSERT before or it failed to get the ID
 	 * @throws Exception
 	 */
 	public function lastInsertId($name = null): int {
@@ -526,8 +528,13 @@ class Connection extends PrimaryReadReplicaConnection {
 	 * @internal
 	 * @throws Exception
 	 */
-	public function realLastInsertId($seqName = null) {
-		return parent::lastInsertId($seqName);
+	public function realLastInsertId($seqName = null): int {
+		if ($this->lastInsertId !== 0) {
+			$lastInsertId = $this->lastInsertId;
+			$this->lastInsertId = 0;
+			return $lastInsertId;
+		}
+		return (int)parent::lastInsertId($seqName);
 	}
 
 	/**
@@ -896,11 +903,23 @@ class Connection extends PrimaryReadReplicaConnection {
 		if (
 			!isset($this->lastConnectionCheck[$this->getConnectionName()]) ||
 			time() <= $this->lastConnectionCheck[$this->getConnectionName()] + 30 ||
-			$this->isTransactionActive()
+			$this->isTransactionActive() ||
+			$this->disableReconnect
 		) {
 			return;
 		}
 
+		if ($this->getDatabaseProvider() === IDBConnection::PLATFORM_MYSQL) {
+			/**
+			 * Before reconnecting we save the lastInsertId, so that if the reconnect
+			 * happens between the INSERT executeStatement() and the getLastInsertId call
+			 * we are able to return the correct result after all.
+			 */
+			$this->disableReconnect = true;
+			$this->lastInsertId = (int)parent::lastInsertId();
+			$this->disableReconnect = false;
+		}
+
 		try {
 			$this->_conn->query($this->getDriver()->getDatabasePlatform()->getDummySelectSQL());
 			$this->lastConnectionCheck[$this->getConnectionName()] = time();