Hi,

I think that the two implementations can coexist and my suggestion in MDEV-15935 about making the error packet version a "forceful" redirection would allow that. I believe that a reasonable way of doing a very graceful redirection would be as follows:

• Turn on the redirection to another server (SET GLOBAL server_redirect_mode=ON). This then causes an advisory redirection to be added to all OK packets as well as EOF packets if the deprecate EOF protocol is in use. Newer client libraries would use this to migrate over to another server once their transactions are over and older clients that either don't implement the deprecate EOF protocol or don't know about redirection would remain oblivious to this.
  
• After some grace period, the forceful redirection would be turned on (SET GLOBAL server_redirect_mode=FORCE). This would turn the advisory redirection into the currently proposed forceful redirection that sends an error packet. This amounts to the same as lowering max_connections to prevent new connections from taking place except that you get a different error message.
  
• Once forceful redirection is in effect, you would wait for open transactions to complete, possibly after turn on read_only, and then shut down the server.

As for the redirection information, a URL would be a more flexible way of connecting and would allow things like UNIX domain sockets and named pipes to be used as redirection targets. I definitely agree that a simple host and port approach (correctly formatted to support IPv6 of course) must be accepted as a redirection by all client libraries but some form of standardization for the redirection format string is needed or at least how client libraries should interpret unknown values.

The forceful redirection has one minor problem that the advisory redirection does not have: only one redirection target can be given in the current form. The reason for multi-host redirections would be to prevent sudden spikes of new connections from happening on the server where the redirection is being done. The advisory redirection, as mentioned in MDEV-15935, could use SQL to set a session-level value which in turn opens up limitless possibilities of using it for load balanced redirection. This is probably a very minor in the grand scheme of things and having one host to redirect to is probably OK in most cases if the advisory redirection is implemented on top of the forceful one: the former would shift most of the connections away from the server and the latter would only be the final step that prevents new ones from being created.

For things like JDBC connection pools that support multiple hosts, redirections are technically already implemented: I believe they just pick another server when they get a Too many connections error. The benefits of a forceful redirection are thus minimal for these use-cases and the advisory one would be much more effective in avoiding new work from being placed on servers that are about to shut down. However even in this case the redirection error could be interpreted as an instruction to avoid using the given server for some period of time.

Markus

Hi Sergei,

On Thu, 2023-06-29 at 15:58 +0200, Sergei Golubchik wrote:

On Jun 28, Lenski, Daniel via developers wrote:

I would like to propose a new feature in the MariaDB client-server
protocol<https://mariadb.com/kb/en/clientserver-protocol>:
application-layer redirection of client connections.

We want the MariaDB server to be able to tell clients connecting to
it, “Sorry, this server is unavailable. Connect to an alternate server
instead.” This mechanism is inspired by HTTP 302 (“temporary
redirect”) mechanism familiar to all developers of web applications,
and is intended to have similar semantics and security properties,
since these have now been widely deployed and tested for decades.

I have submitted a minimal but viable implementation of this at:

  *   https://github.com/MariaDB/server/pull/2681, server-side implementation
  *   https://github.com/mariadb-corporation/mariadb-connector-c/pull/226, MariaDB Connector/C implementation

I'm afraid MDEV-15935 is a bit confusing. There are lots of discussions
and ideas in the comments, like, using a specially modified OK packet,
using the error message, etc.

Right! I got the sense from the Jira discussion (https://jira.mariadb.org/browse/MDEV-15935) that there's a large and varied "wish lish" of different capabilities for this feature — not all of which
are practical or compatible.

It has also been open for over 5 years without an accepted implementation.

This is why I thought it would make a lot of sense to implement redirection in a very MINIMAL and SIMPLE form — but one that would already be useful in its initial form, and with a design that's
amentable to future extensibility. (Many thanks to Otto for encouraging and guiding this implementation!)

But the final consensus was to use session
tracking feature, it allows for most flexibility and user control over
how redirect is happening. To quote here:

  We'll add a new global/session variable, say, redirect_url. The value
  should be an empty string or a connection string in the conventional
  format (in the style of a connection url of C/J or C/NodeJS or C/C or
  FederatedX).

  This variable is appended to the default value of
  session_track_system_variables variable.

  When this variable is changed, the existing
  SESSION_TRACK_SYSTEM_VARIABLES implementation will send its new value
  to the client as a part of the OK packet. It's up to the client or to
  the connector to use this value or to ignore it.

I read through this proposal as well, but I didn't realize that it had been in any way chosen as the desired implementation. I am surprised, because there seems to be 2 key problems with it…

(1) Redirection targets in "URL" format will be extremely difficult to support in consistent and secure ways across client implementations.

The Connector/C library currently has no ability to parse connection "URLs" in either the Connector/J (https://mariadb.com/kb/en/about-mariadb-connector-j) or the MySQL formats (
https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-jdbc-url-format.html), which are partially but fully compatible with each other, and to my knowledge there is no formal definition of
either. As far as I know, Connector/C is the basis for most higher-level languages’ connector libraries (e.g. Connector/Python).

If the redirection target can contain all the different kinds of connection information that MariaDB Connector/J URLs support (very long list of optional parameters! 
https://mariadb.com/kb/en/about-mariadb-connector-j/#optional-url-parameters), it will be very difficult to ensure that all languages’ Connector/X libraries handle all of these parameters in a
consistent and secure way when redirecting.

For example, if a *server* sends a redirect URL containing the optional `keyStorePassword` field, should a *client* library trust or attempt to use this value? I think not. Or what if Connector/J
observes the `timezone` parameter when redirecting to a new server, but Connector/C ignores it? This would be a source of inconsistent behavior and could frustrate users of the redirection feature.

Precisely in order to avoid this level of complexity, my proposal includes a *much simpler* format for the redirection target: `host[:port]`. This should be simple enough for any and all Connector/X
client libraries to implement in a uniform fashion. At the same time, it is designed to be extensible; if there are clear use cases for it, it could be extended in the future to include more complex
formats for the redirection target, such as Connector/J-style and/or MySQL-style connection URLs.

(2) Using OK packets and session-tracking, the server would have no way to enforce redirection.

As you wrote, "It's up to the client or to the connector to use this value [of redirect_url] or to ignore it."

In this form, redirection instructions sent by the server would be merely "advisory", and the server would have no way to ensure that clients stop connecting to it.

- Clients using OLD Connector libraries will continue to be oblivious to this value, and to ignore it. As all of us have experienced, old/forgotten/abandoned/unpatched client software is often used
for DECADES beyond its originally-intended lifespan.
- Clients using NEW Connector libraries can be configured to ignore the redirection value. Quite likely many of them WILL do this, since connecting to a new server is more difficult and more error-
prone than just continuing to send queries to the already-connected server.

This "advisory" redirection would seem to be greatly limited in the kind of use cases it supports. If servers sending redirection information can't RELY ON clients disconnecting from them, then they
can't use redirection as a prelude to shutting down the server.

This is why my proposal instead includes the redirection information in an ERROR packet: supporting clients will be able to follow the redirection (or simply disconnect if they don't want to follow
redirects, e.g. `mariadb --no-follow-redirects`), while older clients will be disconnected with a comprehensible error message. In this way, a MariaDB server will be able to RELY ON clients
disconnecting from it, which will simplify and clarify the usage of redirection for server shutdown. Note that my initial proposal only involves sending ER_SERVER_REDIRECT error packets to *newly-
connecting* clients; in the future, it could also be extended to send similar packets to *already-existing* clients, for example for load-balancing purposes.



Thanks,

Daniel Lenski
Amazon RDS MySQL/MariaDB engine team
_______________________________________________
developers mailing list -- developers@lists.mariadb.org
To unsubscribe send an email to developers-leave@lists.mariadb.org

-- 
Markus Mäkelä, Senior Software Engineer
MariaDB Corporation