Cannot add CREATE TRIGGER statements with the DBAL mysqli driver

[mpdude]

Bug Report

Q	A
BC Break	guess no
Version	3.5.5

More specifically,

• doctrine/migrations 3.5.5
• doctrine/dbal 3.6.0
• PHP 8.1.6
• ext-mysqli with client API library version "mysqlnd 8.1.16"

Summary

Passing a CREATE TRIGGER ... statement to $this->addSql() in a migration fails when the SQL is executed.

Current behavior

The migration fails with the error message:

An exception occurred while executing a query: This command is not supported in the prepared statement protocol yet

How to reproduce

Create a migration:

class Version... { ...
    public function up(Schema $schema): void
    {
        $this->addSql('CREATE TRIGGER some_trigger BEFORE UPDATE ON some_table FOR EACH ROW UPDATE other_table SET some_column = NEW.other_column WHERE fk = NEW.id');
    }
}

Then, execute the migration using the mysqli driver.

Expected behavior

Migration being executed with no error, trigger created.

Additional information

The DbalExecutor calls Connection::executeQuery():

migrations/lib/Doctrine/Migrations/Version/DbalExecutor.php

Lines 293 to 298 in dc441bb

	foreach ($this->sql as $key => $query) {
	$this->outputSqlQuery($query, $configuration);
	$stopwatchEvent = $this->stopwatch->start('query');
	// executeQuery() must be used here because $query might return a result set, for instance REPAIR does
	$this->connection->executeQuery($query->getStatement(), $query->getParameters(), $query->getTypes());

This, in turn, will call \Doctrine\DBAL\Driver\Connection::query() (on the DBAL Driver/Connection).

https://github.com/doctrine/dbal/blob/4173dfc23de02e2ac0c99cfb16fcd8af5fedc23b/src/Driver/Mysqli/Connection.php#L68-L71

In \Doctrine\DBAL\Driver\Mysqli\Connection::prepare(), we ultimately get to \mysqli::prepare(), which is where the initial exception is thrown.

Things work when I use Connection::executeStatement() on the DBAL connection, since that will use \Doctrine\DBAL\Driver\Connection::exec() on the DBAL Driver/Connection, at least when no parameters need to be bound.

executeStatement also seems more appropriate, at least for trigger creation. The code snippet quoted above includes a comment why executeQuery() is called, but in the Git history I can only trace that back to a reverted use of executeUpdate(); so basically it has been executeQuery() since a long time.

#1326 adds a new parameter to addSql() to indicate that the statement should be executed with executeStatement(), which would solve the issue.