Enable multiple and single statement queries on the same connection

[boenrobot]

Right now, whether you can send multiple statement queries is determined at connection/pool creation time.

I understand this is done for security's sake, and yet it seems this is one of those cases where the alternative - creating a separate connection/pool for when you need to use multiple queries - is only creating more issues than it is solving... Case in point being the under or over utilization of the MySQL server's connection limit.

It would be much better if multiple queries can be opted into at the time of the method call. Opt in so that security is not compromised.

So, either a separate method (e.g. multiQuery("LOCK TABLES t; INSERT INTO t ?;UNLOCK TABLES", o, cb)) on connections and pools, or an option in the query object (e.g. query({multipleStatements: true, sql: "LOCK TABLES t; INSERT INTO t ?;UNLOCK TABLES"}, o, cb)).

[sidorares]

not possible, it's connection time flag
Basically the behaviour of client here reflects protocol

[boenrobot]

Really? Then how do PHP and Python hack around that?

[sidorares]

probably they just always allow multi statements at connection time

[boenrobot]

Well, here's a slightly different idea that if implemented, would still address my primary concern of connection limits... Keep the connection/pool option, BUT also add it to Pool.getConnection() (perhaps along with other connection time options), to get a connection with the specified options (merged with the default ones).

If the settings are different than the pool settings, make a new connection with those new settings, while keeping the total number of connections under the specified pool limit. Of course, tweaked connections should be reused where possible, just like normal connections. I realize allowing arbitrary options might make implementation difficult though, but if at least the multiple statements one gets in, it would still address 99% of real world cases IMHO.

It's possible other SQL clients do a similar thing under the hood, or at least PHP does, since it doesn't have the concept of pools, but just connections, and no concurrency of any type, so it can afford to just reconnect with the multiple statements option when multi_query() is called, and keep that connection until query() is called, where it reconnects without the multiple statements option, and keeps using it until multi_query() is called yet again.

[dougwilson]

I looked at thr Python and PHP implementations you pointed to and they simply always connect to thr databade with multi statements enabled. Those methods simply change the method in which you get the result set, since a normal query only provides one result set and multi provides multi sets. You can actually invoke the single methods in those libraries with multi queries and the actions will still happen.

They are only ever making one connection with the multi enabled. Why not just do the same here? Just connect with multiplueStatements: true? It's just disabled by default because most people don't need it and can catch dumb mistakes. If you use multi queries just enable it on your connections normally.

[boenrobot]

I just tried out to make sure, and PHP's mysqli::query() just fails if you pass multiple queries to it. I tried having two inserts, and neither of them made it to the DB.

Test code:

<?php

$mysqli = new mysqli('127.0.0.1', 'root', '1234', 'lsm');

var_dump(
    $mysqli->query(
        "INSERT INTO `sessions` SET `session_id` = 'a', `expires` = 1; INSERT INTO `sessions` SET `session_id` = 'b', `expires` = 1"
    )
);
var_dump($mysqli->errno, $mysqli->error);

(as you can guess, the test table just has a char and an int column, with the char one being an ID)

The output I get is

bool(false)
int(1064)
string(206) "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT INTO `sessions` SET `session_id` = 'b', `expires` = 1' at line 1"

and if I use multi_query(), I get both rows inserted, e.g.

<?php

$mysqli = new mysqli('127.0.0.1', 'root', '1234', 'lsm');

var_dump(
    $mysqli->multi_query(
        "INSERT INTO `sessions` SET `session_id` = 'a', `expires` = 1; INSERT INTO `sessions` SET `session_id` = 'b', `expires` = 1"
    )
);
var_dump($mysqli->errno, $mysqli->error);

output:

bool(true)
int(0)
string(0) ""

All as expected... So... there's some sort of magic going on, at least with PHP... And it's certainly not just a simple string delimiting, as having ";" as part of a string in a single query with no escaping also works as expected.

Just connect with multiplueStatements: true? It's just disabled by default because most people don't need it and can catch dumb mistakes.

That's the problem... I'm only human, and I also work with other humans... And humans make dumb mistakes from time to time. Security wise, it's better to not use mutipleStatements: true as much as possible, to minimize the possibility of such dumb mistakes, and in the few instances where multiple statements are required, REALLY make extra certain of all possible inputs, and check the logic in that first, should the DB get compromised.

If you use multi queries just enable it on your connections normally.

But I can't do that from a single connection pool... Not unless I enable multipleStatements for ALL connections in the pool. That's really the crux of the issue.

For example, if I have a connection limit of 150 connections at my MySQL server, I want my connection pool to allow for up to 150 connections, and scale up as requests to my app become too much for a handful of connections to bare, potentially letting me know through errors and/or slow downs if the app somehow becomes so overused that this is no longer enough.

If all of my connections use mutipleStatements: true or mutipleStatements: false, that's exactly what happens, but if I only want to use multiple statements in one or two particular spot(s) in my app, right now, I have two options:

• Have mutipleStatements: true for all connections and all query() calls. This is a problem, because it makes the app far more vulnerable, particularly to dumb developer mistakes, be they from me or others.
• Have a separate pool with mutipleStatements: true that is called only when needed. This is better for security, in that you have to opt into multiple statements, but the number of connections in total from the app will be the sum of the connection limit from both pools. This is now a problem regardless of what limit I set at the client.
  • If both pools have, let's say half the server's limit, I'm leaving the server under utilized, as the app will hold back from utilizing either pool up to the server's connection limit, while the other pool may as well be idle with only a few connections.
  • If both pools have the actual server's limit, the server will start refusing connections, and instead of just slowing down, the app will start to error more often as both pools cross the half threshold.
  • If I place an in between weight to either pool (say, I leave 10 connections for the multi statement pool, and 140 for the rest), that leaves the burden of which pool is more important to humans, and worse yet, the weights will be statically defined... I'm not a psychic... It's very much possible that the one place where multi queries are used will one day become the busiest piece of the app, and thus require more connections to it.

[dougwilson]

Ah, I see. I would suggest maybe using the PoolCluster implementation or perhaps put together a pull request that implements the feature you are looking for, and we can take a look!

[dougwilson]

Maybe like a pool solution could be some kind of multi config pool, where you can add multiple configs to the pool and ask for a connection for one of those configs. Then you can like set pool size = 150 and configA max = 100 and configB max = 100 which would allow you to check out up to 150 total connections, but make sure one of the configs cannot overwhelm 100% of the pool leaving either to starvation for one config or ending up with constant disconnecting / reconnecting to swap the configs under a high load. Just spit balling here. If you have an idea for how the implementation should be, please feel free to share here or via code in a pull request :) If you're not sure how to implement it, if you can describe the exact implementation, someone else can always write it up as well.

[boenrobot]

Ah. Multiple named configs in the pool creation, and then being able to get a connection by asking for a particular name - YES. That sounds great.

Implementation wise, I think all configs should share into the total limit, and just allow one config to potentially overwhelm the whole pool. Once the pool is full, if a connection with a different config is requested, an idle connection from the config with highest number of connections should be disconnected before one with the requested config is created. If that config has no idle connections, go down checking for idle connections based on the number of connections in each config.

An edge case here would be if one or more configs overwhelm the pool AND there's no idle connections at all out of them (thus not allowing the new config to have even one connection...). I think in that event, it's fair to just error.

[sidorares]

I personally don't like idea of keeping adding stuff into Pool / PoolCluster. If you need 2 separate configs, one can just use two separate pools:

// data_access.js:

let poolFoo, poolBaz;
module.exports.getPoolFoo = function () {
  if (!poolFoo) {
    poolFoo = mysql.createPool(fooConfig);
  }
  return poolFoo;
}
module.exports.getPoolBaz = function () {
  if (!poolBaz) {
    poolBaz = mysql.createPool(bazConfig);
  }
  return poolBaz;
}

// routes.js
app.get('/foo', (req, res) => {
  require('data_access').getPoolFoo().query(fooSql, (err, rows) => {
    res.json(rows);
  });
})

app.get('/baz', (req, res) => {
  require('data_access').getPoolBaz().query(bazSql, (err, rows) => {
    res.json(rows);
  });
})

[dougwilson]

I'm going to close this since no pull request ever materialized. You can already use the PoolCluster mechanism to achieve this, so it's not impossible to already do what you like. If you really want to see the multi config in pool, always feel free to open a PR with an implementation.