What, exactly, does allowMultiQueries do?

Question

Adding allowMultiQueries=true to the JDBC string makes MySQL accept Statements with multiple queries.

But what exactly does this do? Is there any benefit to this?

Perhaps it reduces the delay due to round trips? Something like

LOCK
UPDATE ...
UNLOCK

which if done in one statements holds the lock for less time.

When, if ever, would I want to combine queries in a single Statement, rather than in separate ones?

Answer 1

For running safe scripts of your own creation that otherwise would need to be run line by line. For instance, a script from mysqldump, or one that you would have run anyway, safely and trusted. This was pointed out to me once by someone when I asked "why would you want to do that?" He responded, his stockpile of scripts, of his own, each of which has no user input for tomfoolery and the potential of sql injection. The size of these routines is limited by max_allowed_packet and the strategy would be, of course, reading the file into your buffer, and using that for the query in a Multi.

For running a few statements in concert where one relies on the other in the transient nature of a call. Transient meaning that had you issued a subsequent call not via a Multi, that the necessary information is no longer available for a piece of it. A common example often given, wise or not, is the duo of SQL_CALC_FOUND_ROWS and FOUND_ROWS() which popularly was debunked in the Percona article To SQL_CALC_FOUND_ROWS or not to SQL_CALC_FOUND_ROWS?. There is an argument to be made in that situation that a single call that not only returns the resultset but has available the count to be grabbed shortly thereafter is a wiser route for more accurate pagination routines. This assumes that a separate call for count(*) and another for the data could generate a discrepancy in multi-user concurrent systems like all of ours most likely. So, the just mentioned verbiage addresses accuracy, not performance which is what the Percona article is about. Another use-case is priming and using User-Defined Variables into queries. Many of these can be folded into the query and initialized with a cross join, however.

Answer 2

When, if ever, would I want to combine queries in a single Statement, rather than in separate ones?

There are two great use cases for this feature:

• If you are lazy and like to blindly run queries without checking for success or row counts or auto_increment value assignment, or

• If you like the idea of increasing the odds of SQL injection vulnerabilities username ='' AND 0 = 1; ← right here. With this mode inactive, anything after the injected semicolon is an error, as it should be. With this mode active, a whole world of "oops" can open right up.

What I am saying is... You're right. Don't use it.

Yes, it reduces the impact of round-trip time to the database, pipelining queries... which can be significant with a distant database... but at the cost of increased risk that isn't worth it.