Using `pdo_mysql` driver with `MySQL` and `MariaDB` databases
requires that buffered result sets are fully read or explicitly
free'd. That means, that queries returning a result set needs
to consume the full result set by reading all rows and not only
one record and leaving the rest in the buffer.
TYPO3 mitigates this in several places for select queries by
explicitly enforcing `setMaxResults(1)` when reading only one
record and it could be a duplicates, which is not an ideal or
reading the full result set.
The DDL `OPTIMIZE TABLE` for `MariaDB` and `MySQL` databases
returns a result stating the outcome of the operation and is
a buffered query, which requires to use `executeQuery()` and
not `executeStatement()` on the connection and fully consume
the result when using `pdo_mysql`. However, the `mysqli`
driver is more forgiving here.
This change now uses `executeQuery()` in the scheduler task
`OptimizeDatabaseTableTask` to retrieve the result without
using it to ensure buffer is consumed and works with both
possible drivers for the aforementioned database vendors.
As a first guard, one functional test execution is changed
to use `pdo_mysql` and is scheduled to rework the matrix
in a dedicated change to cover differences with both drivers
for `MySQL` and `MariaDB`.
Can be verified executing the single test case, working with
`mysqli` and failing with `pdo_mysql` (without this change):
> Build/Scripts/runTests.sh -s functional -d mariadb -a mysqli -- \
--filter 'OptimizeDatabaseTableTaskTest'
> Build/Scripts/runTests.sh -s functional -d mariadb -a pdo_mysql -- \
--filter 'OptimizeDatabaseTableTaskTest'
> Build/Scripts/runTests.sh -s functional -d mysql -a mysqli -- \
--filter 'OptimizeDatabaseTableTaskTest'
> Build/Scripts/runTests.sh -s functional -d mysql -a pdo_mysql -- \
--filter 'OptimizeDatabaseTableTaskTest'
Added tests are executed against all databases albeit the task
silently does nothing for other database systems than `MariaDB`
or `MySQL` to ensure that the platform detection stays in place.
TYPO3 v12 used the `Connection::query()` method using
`executeQuery()` internally, which means that it has
been replaced with the wrong method while upgrading to
Doctrine DBAL 4 with #102875 in TYPO3 v13.