syntax error while executing 2 queries separeted by semicolon

Question

After upgraded MySQL to MariaDB 10.6.11 on Ubuntu 22, I get syntax error when I run the same query I was using in a PHP project. After some tests, it looks like multiple queries separated by semicolons cannot be executed anymore. I've tried even a simpler query like this

$sql = "SET @var1 = 1; SET @var2 = 2;"

and I still get

PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SET @var2 = 2' at line 1

The weird thing is that only via PHP I get this error; if I try this in PhpMyAdmin SQL tab, it works. The DELIMITER is the semicolon ";". Any idea?

Answer 1

Most client interfaces for MySQL and MariaDB don't support multi-query by default. You can only run one SQL statement per call to query().

There's never a need to enable multi-query. There's no advantage to doing so that justifies the complexity.

In the case of the SET statement to assign variables, you can assign multiple variables in a single SQL statement like this:

$sql = "SET @var1 = 1, @var2 = 2"

Even in other cases where you think you need to run multiple SQL statements in a single call to query(), you almost certainly don't need to. Just run them one statement at a time.

Also, the DELIMITER command is not a statement you can execute through client interfaces like PDO at all. This is a mysql command-line builtin command. These builtin commands are only recognized by the mysql client, not by the MySQL Server SQL parser.

You need to set the DELIMITER only if you are running an SQL script through the mysql client or a GUI client that supports SQL script input. Since the programming interfaces only support one SQL statement per call to query(), there's no need to change the delimiter, because it's unambiguous that semicolons inside the body of a routine are not to terminate the statement.