Transaction rolled back stacktrace during cron job

[ThaChillera]

Hi,

I'm sometimes getting a transaction rolled back exception when running the cron job, very similar to #89 . This doesn't happen every 5 minutes (every run), but it does happen semi regularly. three times last night, one time the day before, etc.

Environment:
Nextcloud: 29.0.3
Postgres: 13

stacktrace:

#4 {main}
#3 /var/www/nextcloud/cron.php(211): OC\BackgroundJob\JobList->setLastJob(Object(OCA\SuspiciousLogin\BackgroundJob\ETLJob))
#2 /var/www/nextcloud/lib/private/BackgroundJob/JobList.php(356): OC\BackgroundJob\JobList->unlockJob(Object(OCA\SuspiciousLogin\BackgroundJob\ETLJob))
#1 /var/www/nextcloud/lib/private/BackgroundJob/JobList.php(368): OC\DB\QueryBuilder\QueryBuilder->executeStatement()
#0 /var/www/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php(328): OC\DB\Exceptions\DbalException::wrap(Object(Doctrine\DBAL\Exception\DriverException))
Stack trace:
Next OC\DB\Exceptions\DbalException: An exception occurred while executing a query: SQLSTATE[25P02]: In failed sql transaction: 7 ERROR:  current transaction is aborted, commands ignored until end of transaction block in /var/www/nextcloud/lib/private/DB/Exceptions/DbalException.php:71
#11 {main}
#10 /var/www/nextcloud/cron.php(211): OC\BackgroundJob\JobList->setLastJob(Object(OCA\SuspiciousLogin\BackgroundJob\ETLJob))
#9 /var/www/nextcloud/lib/private/BackgroundJob/JobList.php(356): OC\BackgroundJob\JobList->unlockJob(Object(OCA\SuspiciousLogin\BackgroundJob\ETLJob))
#8 /var/www/nextcloud/lib/private/BackgroundJob/JobList.php(368): OC\DB\QueryBuilder\QueryBuilder->executeStatement()
#7 /var/www/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php(326): OC\DB\QueryBuilder\QueryBuilder->execute()
#6 /var/www/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php(280): Doctrine\DBAL\Query\QueryBuilder->execute()
#5 /var/www/nextcloud/3rdparty/doctrine/dbal/src/Query/QueryBuilder.php(393): OC\DB\Connection->executeStatement('UPDATE "jobs" S...', Array, Array)
#4 /var/www/nextcloud/lib/private/DB/Connection.php(373): Doctrine\DBAL\Connections\PrimaryReadReplicaConnection->executeStatement('UPDATE "jobs" S...', Array, Array)
#3 /var/www/nextcloud/3rdparty/doctrine/dbal/src/Connections/PrimaryReadReplicaConnection.php(292): Doctrine\DBAL\Connection->executeStatement('UPDATE "jobs" S...', Array, Array)
#2 /var/www/nextcloud/3rdparty/doctrine/dbal/src/Connection.php(1213): Doctrine\DBAL\Connection->convertExceptionDuringQuery(Object(Doctrine\DBAL\Driver\PDO\Exception), 'UPDATE "jobs" S...', Array, Array)
#1 /var/www/nextcloud/3rdparty/doctrine/dbal/src/Connection.php(1885): Doctrine\DBAL\Connection->handleDriverException(Object(Doctrine\DBAL\Driver\PDO\Exception), Object(Doctrine\DBAL\Query))
#0 /var/www/nextcloud/3rdparty/doctrine/dbal/src/Connection.php(1943): Doctrine\DBAL\Driver\API\PostgreSQL\ExceptionConverter->convert(Object(Doctrine\DBAL\Driver\PDO\Exception), Object(Doctrine\DBAL\Query))
Stack trace:
Next Doctrine\DBAL\Exception\DriverException: An exception occurred while executing a query: SQLSTATE[25P02]: In failed sql transaction: 7 ERROR:  current transaction is aborted, commands ignored until end of transaction block in /var/www/nextcloud/3rdparty/doctrine/dbal/src/Driver/API/PostgreSQL/ExceptionConverter.php:87
#10 {main}
#9 /var/www/nextcloud/cron.php(211): OC\BackgroundJob\JobList->setLastJob(Object(OCA\SuspiciousLogin\BackgroundJob\ETLJob))
#8 /var/www/nextcloud/lib/private/BackgroundJob/JobList.php(356): OC\BackgroundJob\JobList->unlockJob(Object(OCA\SuspiciousLogin\BackgroundJob\ETLJob))
#7 /var/www/nextcloud/lib/private/BackgroundJob/JobList.php(368): OC\DB\QueryBuilder\QueryBuilder->executeStatement()
#6 /var/www/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php(326): OC\DB\QueryBuilder\QueryBuilder->execute()
#5 /var/www/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php(280): Doctrine\DBAL\Query\QueryBuilder->execute()
#4 /var/www/nextcloud/3rdparty/doctrine/dbal/src/Query/QueryBuilder.php(393): OC\DB\Connection->executeStatement('UPDATE "jobs" S...', Array, Array)
#3 /var/www/nextcloud/lib/private/DB/Connection.php(373): Doctrine\DBAL\Connections\PrimaryReadReplicaConnection->executeStatement('UPDATE "jobs" S...', Array, Array)
#2 /var/www/nextcloud/3rdparty/doctrine/dbal/src/Connections/PrimaryReadReplicaConnection.php(292): Doctrine\DBAL\Connection->executeStatement('UPDATE "jobs" S...', Array, Array)
#1 /var/www/nextcloud/3rdparty/doctrine/dbal/src/Connection.php(1207): Doctrine\DBAL\Driver\PDO\Statement->execute()
#0 /var/www/nextcloud/3rdparty/doctrine/dbal/src/Driver/PDO/Statement.php(132): Doctrine\DBAL\Driver\PDO\Exception::new(Object(PDOException))
Stack trace:
Next Doctrine\DBAL\Driver\PDO\Exception: SQLSTATE[25P02]: In failed sql transaction: 7 ERROR:  current transaction is aborted, commands ignored until end of transaction block in /var/www/nextcloud/3rdparty/doctrine/dbal/src/Driver/PDO/Exception.php:28
#10 {main}
#9 /var/www/nextcloud/cron.php(211): OC\BackgroundJob\JobList->setLastJob(Object(OCA\SuspiciousLogin\BackgroundJob\ETLJob))
#8 /var/www/nextcloud/lib/private/BackgroundJob/JobList.php(356): OC\BackgroundJob\JobList->unlockJob(Object(OCA\SuspiciousLogin\BackgroundJob\ETLJob))
#7 /var/www/nextcloud/lib/private/BackgroundJob/JobList.php(368): OC\DB\QueryBuilder\QueryBuilder->executeStatement()
#6 /var/www/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php(326): OC\DB\QueryBuilder\QueryBuilder->execute()
#5 /var/www/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php(280): Doctrine\DBAL\Query\QueryBuilder->execute()
#4 /var/www/nextcloud/3rdparty/doctrine/dbal/src/Query/QueryBuilder.php(393): OC\DB\Connection->executeStatement('UPDATE "jobs" S...', Array, Array)
#3 /var/www/nextcloud/lib/private/DB/Connection.php(373): Doctrine\DBAL\Connections\PrimaryReadReplicaConnection->executeStatement('UPDATE "jobs" S...', Array, Array)
#2 /var/www/nextcloud/3rdparty/doctrine/dbal/src/Connections/PrimaryReadReplicaConnection.php(292): Doctrine\DBAL\Connection->executeStatement('UPDATE "jobs" S...', Array, Array)
#1 /var/www/nextcloud/3rdparty/doctrine/dbal/src/Connection.php(1207): Doctrine\DBAL\Driver\PDO\Statement->execute()
#0 /var/www/nextcloud/3rdparty/doctrine/dbal/src/Driver/PDO/Statement.php(130): PDOStatement->execute(NULL)
Stack trace:
PDOException: SQLSTATE[25P02]: In failed sql transaction: 7 ERROR:  current transaction is aborted, commands ignored until end of transaction block in /var/www/nextcloud/3rdparty/doctrine/dbal/src/Driver/PDO/Statement.php:130

[ThaChillera]

I noticed errors in my database regarding duplicate key violations, when checking the data in table login_ips_aggregated somehow some entries were duplicated (username/ip combo).

I removed the duplicate entries, reindexed the whole database, so whatever went wrong hopefully won't happen again. If I don't report back in a week, consider this issue resolved :)