Sporadic test failure on PDO_OCI

[morozov]

There was 1 error:

1) Doctrine\DBAL\Tests\Functional\Schema\OracleSchemaManagerTest::testDropsDatabaseWithActiveConnections
Exception: [Doctrine\DBAL\Exception\DriverException] An exception occurred while executing 'DROP USER test_drop_database CASCADE':

SQLSTATE[HY000]: General error: 1940 OCIStmtExecute: ORA-01940: cannot drop a user that is currently connected
 (/opt/oracle/php-src-php-7.4.9/ext/pdo_oci/oci_driver.c:348)

According to the test name, it is meant to drop a database with active connections but under certain circumstances, it fails to do so.

At the same time, it's not clear why this functionality is needed in the first place.

[greg0ire]

That functionality was introduced in #849
@deeky666 , maybe you remember why this was needed?

See also #2636 , in which it was fixed for Oracle

I read this doc and think I found a reason why this doesn't work:

This does not affect the work performed by the command, but it returns control back to the current session immediately, rather than waiting for confirmation of the kill.

[morozov]

This PR closes all active connections before dropping the database if dropping the database failed before.

IMO this is a fundamentally wrong approach, even from the DBAL's own perspective. If DBAL loses its connection, it will attempt to recreate it again before the next query. If a service that uses the database being dropped gets its connection closed, it will attempt to restore it and proceed thereby interfering with the process of dropping the database.

A proper approach is to either have a maintenance window during the database is inaccessible to its clients or perform a rolling migration.

[greg0ire]

Should we drop the flaky test? It's annoying to have to restart the builds every time.

[morozov]

I can mark it as incomplete for Oracle in #4230 based on your earlier findings.

Then it would still fail on 2.10.x. I filed #4231 separately.

[github-actions]

This thread has been automatically locked since there has not been any recent activity after it was closed. Please open a new issue for related bugs.