Thanks for the detailed feature specification. Do you have any patches / prototype code or something related to this?

A simple approach to this would be to have a dedicated proxy return an error packet telling the user that they should reconnect to another server. To clients that do not support it, it would look like an error. For unauthorized users, the normal authentication error message would be received. This would not require any protocol changes nor any changes on the server side.

For example, the error that tells where to reconnect could be something like this:

ERROR 1045 (28000): Wrong server, reconnect to '192.168.0.201:3306'

A specific error number would need to be allocated for this purpose.

This approach does have a vulnerability to man-in-the-middle attacks so usage of TLS is required with server certificate verification.

A less invasive version of this would be to return the redirection target in a custom value with session_track_system_variables. This would allow older clients to connect via the proxy but newer clients could close the connection and create a direct connection to the redirection target. Using the OK packet to relay the information should also be easier for the connectors to process as no string parsing is required.

The capability flag should be defined in extended capabilities, 1UL << 31 is already used by CLIENT_REMEMBER_OPTIONS

#define MARIADB_CLIENT_SERVER_REDIRECTION (1ULL << 35)

I would suggest the following:

• Server always sends an error (with a new error number), e.g. ER_CONNECTION_REDIRECT
• If client has set MARIADB_CLIENT_SERVER_REDIRECTION flag, server sends an additional redirection package (it's easier to read and to extend than parsing an error message)

otto, markus makela, georg, Hi, thanks for your feedback. Please see the pcap attached. We prototyped the client code and server code based on mysql. The first logon (1,2) was non-ssl, which was supposed to uncover the unencrypted flow. The logon failed because it disobeyed the ssl enforcement (packet#2118) (markus makela, you are totally correct, we enforce TLS here). The second logon (3,4) was a successful logon.

Please open the pcap for detailed fields. We used 1UL << 28 now to avoid conflict. Use "tcp.port==16003||tcp.port==3306" to filter the mysql traffic we want (16003 was the redirected tcp port).

Thanks,
xiangyhu

xiangyhu

• is 1<<28 unused in MariaDB? what happens if MySQL adds a 1<<28 flag in the capabilities for anything? Did you talk to them, too?
• why would not you use redirect in server welcome packet already, at the end of packet? I'd even propose some generic "key=value,key2=value" string, so it can be used for more than just redirection.

wlad Hi, regarding your questions,

• 1 << 28 is currently not used by MariaDB. We currently use this bit slot to prototype.
  But you've raised a general problem that MySQL and MariaDB would diverge on the capabilities and how they should resolve conflicts, or how we add more flags if we consume all current 32-bit capability flags (extended flags included). As far as I can think of, for example, we can have capabilities flags variable by introducing a bit called "more capabilities". If it's set there would be another 32 bits flags coming after, composing a total of 64-bit capabilities. And one chains to anther, we can also have 128-bit capabilities. This is another topic that needs full discussion. Please let know if you have any suggestions.
• Redirection would need client login information to tell the location of the directed server. Think about load-balanced partitioned cluster where there is a proxy handles the incoming connections for a set of non-replicated servers. Web applications may provide the server name they want and the proxy would have the knowledge to redirect the connection to the specific server.

Thanks,
xiangyhu

I see.

• As for capabilities flag, I'd try to pitch this idea to MySQL development as well, if I were you, prior to proceeding with a protocol extension. I assume you're also interested in MySQL supporting the capability. MariaDB does already have an extension mechanism, but I'd prefer not to use it, if it is avoidable.

• If you need a client name to proceed, I'm with markus makela. No need to invent own packet type to pass some keys and values. OK packet has key-value encoding already, and is extensible. Even error packet is good enough, if one agrees on error code, and message format, which can be as simple as "Redirect:<hostname>:<port>"

xiangyhu:

As I mentioned before, please use extended capabilities (see include/mariadb_com.h of MariaDB Connector/C). Since 10.2 we don't use the 32-bit flags anymore and also obsoleted the CLIENT_PROGRESS flag and moved it to extended capabilities.

vlad:
I don't think it's a good idea to send redirection already in server hello package:

• client sends capabilities in response to server hello
• redirection should be allowed only for secure (TLS) connections

georg "redirection should be allowed only for secure (TLS) connections" , is not that an arbitrary restriction? Is this also like this in HTTP?
I think xiangyhu already answered the question , he needs a client info. This comes at a cost for the server and client,being authentication, and in his case also SSL handshake. If server just sent "go to another box" message in welcome packet, this all could be saved.

wlad

Thank you for your suggestions.

• We actually brought such ideas to MySQL previously but received little response. We consider it might be better to discuss and integrate this to the more open MariaDB community first given the offline discussions we have had. We can then have various client connector flavors to support MariaDB redirection. Leveraging the compatibility of mariadb client drivers, customers will be able to use redirection on the MySQL service we are building. Of course, we will continue efforts trying to communicate with MySQL/Oracle on this feature as well.
• We thought about using OK packet or Error packet to transmit the redirection information. Several considerations:
  1. OK packet requires CLIENT_SESSION_TRACK to be on to enable passing modified session variables. We thought it sort of hacky to reuse this flag to contain non-relevant redirection information.
  2. We currently disable CLIENT_SESSION_TRACK in our service. We do not want to introduce extra networking overheads for now.
  3. Error packet is better, as long as we have a dedicated error code for redirection notification. markus makela

Please let me know your thoughts. Thanks!

+ cvicentiu for awareness.

Thanks,
xiangyhu

georg Are you talking about this one?

if (server_capabilities & CLIENT_MYSQL)
string<4> filler
else
int<4> server capabilities 3rd part . MariaDB specific flags /* MariaDB 10.2 or later */

Which bit do you recommend we use?

Thanks you!
xiangyhu

The next free bit, which should be (1ULL << 35)

Maybe someone has a better idea on that, but I believe you can grab some error code you like from the higher range , and hardcode it ( progress reporting hijacked 0xFFFF already)

I.e you can define ERR_SERVER_REDIRECT as 0xF301 (to commemorate HTTP code 301) and send (intentionally) non-localizable error message as "Location: <host>:<port>" .
you might add SQLState HY000, but this does not really matter

Thank you georg. Sorry I missed your previous comment:

• In many existing load-balanced clusters, front-end proxies (maxscale, proxysql) proxy all data back and forth between client and backend servers, which by the way is why redirection is very much needed to reduce the pressure on the proxies. I would not suggest server always send ER_CONNECTION_REDIRECT even when client do not have it implemented. The better approach is fall back to proxied connections transiently. Thoughts?
• I partially agree with wlad's idea on reusing ERR packet, except that we still have the problem hijacking a self-defined error code that might potentially be conflicted with MySQL. So I would vote for another packet type for redirection information.

Thanks,
Xiangyu

there are 64K error codes, and there is already an area reserved for MariaDB. There won't be errors allocated in the highest range. It is very unexpected.
Again, at this point you already know that the client is MariaDB, because it sent this capability flag on connection , so you should not care about MySQL error messages at all. Besides, what do you do if MySQL adds a new packet that has 0xFC

Network analyzers are aware of error packets, 3rd party drivers are, documentation is there, so extending protocol to new type of packet, better not. Less is better here. Parsing overhead is minimal, as compared to all the exchanges already done.
I'm staying with my and markus makela recommendation for Error or OK packet, not adding something one can go without.

The one extra benefit that the OK packet has is the ability to fall back to normal proxy behavior if the client does not support it (meaning that the client would have to be smart enough to pick up the extra variables). This would still allow abstraction of the cluster that the proxy is in front of without any reduction to supported connectors.

And yes, you should not send error if client is not aware of redirection, I think, and proxy transparently.
I thought it could also be useful for hypothetical "sharding-aware" mysqld, where a normal server would send it because it is overloaded

Another use for the error would be to redirect new connections to a fallback server if a server is going down for maintenance.

Yes, markus makela, I like OK better, too. One could assume that MARIADB_CLIENT_SUPPORT_REDIRECT can parse the extra stuff in OK packet from authentication even if client does not specify the capability when connecting.

So, to summarize what I think of it , is that

• we should avoid adding "innovations" into protocol, it already hurt in the past, think DEPRECATE_EOF stuff and another way to encode key=value in OK packet.
• ideally we should avoid new bits in capabilities

So, using session_track_system_variables is actually ideal, with special variable, and added stuff in OK packet. This does not extend the protocol, allows aware clients to disconnect, unaware clients to transparent proxy.markus makela, nice ideas, from start on.

And xiangyhu, if you do not want actual backend server to support session tracking , backend does not have to send CLIENT_SESSION_TRACK in server welcome packet, but proxy should

Why not use a CR error code (CR_CONNECTION_REDIRECT) ? The server never will return this error, it is only used by client and proxy.

If the redirection information is part of the error message, the proxy should always send this error:

• an additional capability flag is not required anymore
• clients (e.g. a GUI interface), can display this information and the user can change the host/port manually
• If the client understands CR_CONNECTION_REDIRECT and the option MARIADB_OPT_REDIRECT was set via mysql_optionsv() before, a new (redirected) connection will be established.

I agree with markus makela: this CLIENT_SESSION_TRACK has been created because of all bits of OK_Packet status flag where nearly set. Using CLIENT_SESSION_TRACK permit to indicate extra information in a dedicated format when needed.
That corresponds exactly to this use case. Proxy returns an OK_Packet with redirect information. If connector support redirection, it will close the current socket and create a new one according to redirection information.
If the client doesn't support redirection, proxy will continue using traditional proxying.
No need for a client capability.

And this format is extensible then : like redirect to x.x.x.x for master, y.y.y.y for reads.

>We currently disable CLIENT_SESSION_TRACK in our service. We do not want to introduce extra networking overheads for now.
That can be disabled by setting session_track_state_change, session_track_schema, session_track_system_variables. No session change information will then be send to client.

Ok, I think here is probably the best thing I can think of, that does not involve session tracking.
Do everything as before, but put "Location: <host>:port" into

string<lenenc> info human readable status information
or
string<EOF> info human readable status information

in the post-authentication OK packet

That looks like a hack, and does mean that aware clients would actually need to read at least 1 byte of this "human readable information" and compare this to 'L', but this seems like a small price for compatibility it offers.

wlad diego dupin markus makela, what would happen if client is expecting authentication switch packet from the real server after proxy sends the login packet to the backend but it gets ok packet?

authentication always finish by an OK or Error packet, even when using authentication switch packet.

wlad why need a hack. driver does alredy support session tracking. Better to have a proper format.
see https://mariadb.com/kb/en/library/ok_packet/#server-status-flag :
each type can define his own format :
set a new Session change type like 31 : REDIRECTION :

REDIRECTION
string<lenenc> Redirected MASTER host is a valid IPv4 address, an A name pointing to this address, or a CNAME record
string<lenenc> Redirected SLAVE host is a valid IPv4 address, an A name pointing to this address, or a CNAME record

xiangyhu - nothing bad I guess. Client never really expects authentication switch in MariaDB. If server sends it, client handles it . If server sends OK, authentication is considered successful.

diego - I refered to other part of OK, and quoted the MySQL docs, not to tracking , but to "human readable" in
https://dev.mysql.com/doc/internals/en/packet-OK_Packet.html

this string is always sent disregarding session tracking, and after update contains something like "n rows affected".

Also , there is no the driver . there is are multiple drivers, and some support session tracking, and some do not. some would sent client flag, and some would not. I would not need it by default

diego dupin, wlad I mean capability flag is needed. Think about a client who does not support redirection gets an okay packet with redirection info, it will treat it as login success. But actually, there are other packet types could be sent at this stage that does not mean a successful login, authentication switch packet is one of them. So server must be able to distinguish the client capability to redirect and shutdown connection or proceed the normal login.

wlad oh, that would probably be the least invasive way to implement this (I completely forgot that OK packets can have arbitrary strings). This would give the best of both worlds in regards to the human-readability of an ERR packet with the transparency of the OK packet.

xiangyhu, then send an error, instead of OK, if your proxy does not perform authentication . There is no need in capability flag still

Authentication ends up with an OK packet, this is how it is defined. Or with error packet.
this OK packet needs redirection info, not the authentication switch

wlad, if there is no capability flag, proxy will shutdown all connections with redirection info wrapped in error packet, not falling back to proxied connections.

if you can fall back to transparent proxy, then you can do authentication, including pluggable one.
if you can do authentication, then you always send OK packet at the end of successful authentication.
if you send OK packet at the end of successful authentication, you can include redirect information.
so it works, without capability flag

wlad I understand your point now. So you are having client doing two full authentication, either of which ends with an ok/err message, while within the first one there could be a redirection info which is up to the client to choose to perform another direct connection.
Then there is indeed no need for capability flags.

wlad markus makela could you kindly let know what's our next step to push redirection feature to the next step? Shall we come to a documented proposal or publish a pull request directly? Thank you!

xiangyhu

wlad markus makela I am preparing the pull request for this. I need your input on the format of redirection information returned as part of the human readable message within the OK packet. I am thinking of a comma delimited string separating host, port, user, etc.. Do you have any suggestions?

Why not "Location: mysql://<host>:<port>/key=value&key2=value2"
basically the standard MySQL/MariaDB JDBC URL format, just without jdbc: prefix

we're already using HTTP-like redirection, thus URL syntax seems logical to me

IPv6 addresses are given in square brackets using this notation

mysql://[2001:db8:85a3:8d3:1319:8a2e:370:7348]:3307/

An URL does seem like a nice way to pass the information in one "payload". I'd recommend going this route if the redirection is to be given in the OK packet info message.

Instead of using a URL string, I would prefer to use the session state tracking information as described in the OK Packet documentation. Having the information readily split into a list of variables would make it significantly easier to convert to native types. For example, reserving the _redirect_host and _redirect_port system variables would make it very easy to use this information in environments that do not directly consume JDBC URL formats. This should not be a problem for connectors that would understand redirection as it is quite likely that they already support session state tracking.

The 2 solutions are ok, using human readable or session state tracking, but i tend to agree with markus makela : It's better to have structured format using ok_packet than hacking the "human readable" string value.

@markus makela and @diego dupin do you means you prefer to use the info property as shown below?

if yes, I suspect the string<lenenc> info format would be like:
5E(length of the following string)_redirect_host=host1:_redirect_port=port1:_redirect_user:user1:_ttl=10:self_defined_flag:flag1

I'm referring to the list of values that follow that info value. It is a list of items that support key-values.

do you means the format as: the total length encoded bytes, followed by list of the key-values?
such as:

total size of key-values
loop

as you mentioned, "I'm referring to the list of values that follow that info value. It is a list of items that support key-values."

I am not sure whether this format would confused the current driver, since this format is intended to pass the "SERVER_SESSION_STATE_CHANGE" info, NOT the redirection info

One can argue that redirection is state change. Basically state change is an opaque thing, not further specified. It is a way for the server to send keys and values.

@Vladislav Vaintroub from the spec of https://mariadb.com/kb/en/library/ok_packet/#server-status-flag

I am not sure whether it support list of key-values

In login packet, it spec to allow loop for key-values, but in ok packet, I don't see the loop

Hi, sorry for my misunderstanding for the OK packet format, I check the source code, and I think it can append list of changed system variables via the session_tracker

So I think the key-values list is available for the redirection scenario

But one thing is, only the MySQL/maria db 5.7 or above version support the CLIENT_SESSION_TRACK feature, and I think there are still many customers are using MySQL/Maria db 5.6 version

so I think a readable connection string in the OK packet may be suitable

Since those versions don't support redirection it doesn't matter. In those cases the extra information will not be sent and the client will connect normally with both old client and old server.

@markus makela

I think redirection does not depends on CLIENT_SESSION_TRACK feature, if ok packet can return redirection info to client driver, and client driver can recognize this info, the feature can work

we do want to support border range of version both for MariaDB and MySQL, I am thinking that, if session_tracking_supported IS NOT enable, we just return a readable redirection connection string in the OK packet, if session_tracking_supported is enable, we add the redirection info into the list of key-value of session state info

Yes, that is a valid option but implementing it means doubling the amount of functionality which inevitably leads to double the work.

I'd suggest compiling an initial list of connectors that would be willing to implement the reconnection functionality and then see which of those support session tracking. Given that all MariaDB connectors support session tracking, I don't think it would be a very big restriction to require the connector to implement session tracking in order for it to support connection redirection.

I suggest to do either URL in a readable format (no length encoding here, please). Or list in OK packet, with CLIENT_SESSION_TRACK.
But not both.

markus makela, parsing the URL is not a big deal, really. Not much more complicated than handle the length encoded list.

wlad That's true, I think using it would be OK.

@Vladislav Vaintroub and @markus makela

would you please help to have a check for the code review: https://github.com/MariaDB/server/compare/10.3...libliang17:10.3

many thanks

markus makela, wlad Thanks for your feedbacks. The above pull request implements the JDBC formatted string returned within the OK packet in string format. Could you kindly help to review?

thanks,
xiangyhu

Can we ensure that the redirection data put into the "human readable status information" field has a terminator, e.g., \n?

If we use just "Location: mysql://%s:%s/user=%s" (e.g., from https://github.com/MariaDB/server/compare/10.3...libliang17:10.3#diff-dca2f11b2511ceff9960dc3bcd972d04R13526) then we have no good way to extend this field for future needs. Clients will assume the user ID extends to the end of the packet, which will not allow other information to be added to the OK packet in the future.

Instead, we should say that the redirection header must be terminated by LF (or CR LF if you want to be just like HTTP) and not by the end of the packet. Servers have to emit the terminator and clients have to stop processing the Location "header" when the terminator is found.

What about a somewhat different approach: use not the info field of the OK packet, but the session state info of the OK packet, in particular the with the type being SESSION_TRACK_SYSTEM_VARIABLES, variable name being "redirect_url" (or any other better string) and the value being the url in the mysql://username@host:port/database?options syntax, as in FederatedX.

Benefits:

• still in the OK packet (same as with info)
• does not need to modify the protocol (same as with info)
• builds on top of existing features nothing new protocol-wise at all
• url syntax is something that already exists in the server, nothing new here either
• the implementation in the server is trivial — introduce a new session variable @@redirect_url. Redirection is done by changing the value of the variable
• redirection can be configured with a command line option, or with a SET, also dynamically from init-connect code using arbitrary complex logic in pure SQL (e.g. to one of the replication slaves in a round-robin fashion), can be done in the middle of the already established session (e.g. before a scheduled shutdown)

of course, it doesn't mean the client has to follow redirects in the middle of the session or at all, it only means that the server has a possibility of asking for it

We'll need at least the design part of this to be resolved in order for MXS-4153 to be doable.

From the MaxScale perspective, parsing the session state information is very simple to implement. This should allow MaxScale to gracefully move the traffic away from the server without having to do it in multiple places. The same design should be relatively simple to implement for anything that MaxScale wants to generate internally and send to the clients. Since the APIs exist for the connector-c, testing this is also very straightforward.

All in all, I think the use of session state change notifications would be a more flexible and well-defined approach compared to injecting text messages into the OK packet.

Few questions about the intended result(s) of redirection…

1. Can redirection occur only upon initial connection establishment? Or can the server decide to redirect the client at any point?
   The latter would be much more HTTP-like, and would presumably facilitate load-balancing scenarios, or a server gracefully redirecting its clients before being taken offline.
2. If redirects can occur at points other than initial connection, then what are the transactional semantics of redirection?
   I think that if a redirect can occur in the middle of a transaction, the server should send it as a subcategory of a transaction rolled back error (ping markus makela who suggested the redirect be embedded in an error response)) so that older clients will interpret it as a rollback, even if they don't understand the redirect.
3. Description states:

   There is no recursion, meaning that connector that have been redirect will not take another redirection in account.

   Does this mean that a client connection must ignore any redirection other than the first one it receives? That seems like an unnecessary and limiting complication; HTTP clients (for example) will allow multiple redirects, and only give up if they get stuck in a loop, or otherwise run out of

If the approach taken is to embed the redirection request into the variable length data (i.e. SESSION_TRACK_SYSTEM_VARIABLES) of an OK packet, the feature would be an opt-in one and as such it would not have any effect on clients that do not understand it. This also means that any response, be it a resultset or an OK response, could be used to start a redirection. This includes even the very first OK packet that's sent as the final step of the authentication process.

I would imagine that if the driver is aware of the transaction state, it could safely do the redirection at a point right after a transaction has committed. If the driver does not support the redirection, it would behave as if the feature never existed and would happily continue using it even if a server requested to redirect on every request.

I don't see a need to limit redirection in the server. It seems like something that could be controlled at the driver level with a maximum redirection count to prevent infinite loops.

markus makela wrote:

If the approach taken is to embed the redirection request into the variable length data (i.e. SESSION_TRACK_SYSTEM_VARIABLES) of an OK packet, the feature would be an opt-in one and as such it would not have any effect on clients that do not understand it. This also means that any response, be it a resultset or an OK response, could be used to start a redirection. This includes even the very first OK packet that's sent as the final step of the authentication process.

Ack, makes sense. That seems like a good approach given the greater flexibility it affords, in terms of the timing of the redirect.

I would imagine that if the driver is aware of the transaction state, it could safely do the redirection at a point right after a transaction has committed.

Agreed. I was thinking more about the case where the redirect occurs at another point; not after a commit (or rollback), but in the middle of another one.

I don't see a need to limit redirection in the server. It seems like something that could be controlled at the driver level with a maximum redirection count to prevent infinite loops.

Agreed. It would be better to have this managed in the driver/client layer. Should that line be removed from the description then? ("There is no recursion, meaning that connector that have been redirect will not take another redirection in account.").

If the driver does not support the redirection, it would behave as if the feature never existed and would happily continue using it even if a server requested to redirect on every request.

I am thinking more about how much state the server would have to manage here, particularly for supporting old/misconfigured clients.

Should the server have to care that it has attempted to redirect a client, and the client has ignored that redirect (perhaps because it's an old client that doesn't know about the redirect)?

If the server could embed a mid-transaction redirect in an ERR_packet indicating a rollback, this would induce old clients not to continue as if nothing had happened.

Basically, consider this^ a sub-case of a more general problem: "how much effort should the server have to put into maintaining an ongoing connection for a client which has received a redirect, but continued to ignore it?"

And my argument here is that this should be made as simple as possible for the server:

1. If the client understands the redirect, it follows it and the server no longer has to worry about the state of that client←→server connection.
2. If the client doesn't fully understand the redirect, ensure that it at least interprets it as a fatal error equivalent to a disconnect with a rolled-back transaction… so that the server no longer has to worry about the state of that client←→server connection.

Agreed. It would be better to have this managed in the driver/client layer. Should that line be removed from the description then? ("There is no recursion, meaning that connector that have been redirect will not take another redirection in account.").

serg could you update the description to match the current design? Not having to go through the comment history hopefully makes this easier for driver implementors to read.

I am thinking more about how much state the server would have to manage here, particularly for supporting old/misconfigured clients.

Should the server have to care that it has attempted to redirect a client, and the client has ignored that redirect (perhaps because it's an old client that doesn't know about the redirect)?

If the server could embed a mid-transaction redirect in an ERR_packet indicating a rollback, this would induce old clients not to continue as if nothing had happened.

The minimal amount of effort for the server is to only send the notification once when the global variable is changed by an administrator. From what I understand of serg's comments, this is very straightforward and a new global variable takes care of the server-side implementation. However I think that if the server is capable of retransmitting the notification for every request, the client side implementation gets easier as it can be written in a stateless manner, in other words, redirections in the middle of transactions are just ignored and the redirection in the COMMIT response is what trigger the redirection.

For the client-side implementation, most of the drivers comprehend the extra data and some (e.g. the C connector) already expose the contents at the API level. This means that once the feature is added, it's possible to use even very old versions of the drivers to emulate support for redirection. From a practical point of view, I think this'll have to be something for the drivers to implement before it gets used.

My experience with custom error messages is that if the driver or the application doesn't expect it, in the best case, it gracefully retries the transaction. More often than not it seems like drivers just pass it onto the application and the application itself just aborts everything and closes the connection. This would make the redirection faster but it wouldn't make it very graceful. A transaction rollback error would probably be something that applications can at least comprehend, for example MaxScale supports retrying of transactions that run into a deadlock error. However, there are cases where transactions cannot be replayed and as such the approach of returning errors where non-error responses would normally be generated is inherently disruptive.

In my mind the process of gracefully taking down a server with this feature would be:

1. Turn on the redirection and wait for existing connections to finish doing what they are doing. After some time, the server will have connections that understand redirection but are still being authenticated and connections that don't understand it at all. From this point on, it's pretty much the same thing you'd do right now to take down a server and the only connections that you'd have to worry about are the ones that don't understand redirection.
2. Lower max_connections so that new connections are rejected.This would get rid of new clients, both ones that understand redirection and ones that don't.
3. Turn on read_only to prevent writes. This waits for open transactions to commit and prevents new writes from happeing.
4. Shut the server down.

The minimal amount of effort for the server is to only send the notification once when the global variable is changed by an administrator. From what I understand of serg's comments, this is very straightforward and a new global variable takes care of the server-side implementation. However I think that if the server is capable of retransmitting the notification for every request, the client side implementation gets easier as it can be written in a stateless manner, …

The server-side implementation (no state) also gets easier if the server retransmits it in this way.

… in other words, redirections in the middle of transactions are just ignored and the redirection in the COMMIT response is what trigger the redirection.

Okay.

In my mind the process of gracefully taking down a server with this feature would be:

It still seems to me that this process would work better for old clients if the redirect gets embedded in…

• An OK response packet, if it's in response to a client committing or rolling back a transaction
• An Err response packet, if it's in response to a client that has done a read-only query, or is in the middle of a transaction
  • This will have the effect of getting old clients to disconnect faster, before the server has to resort to switching to read_only (which even then won't force off clients that are just doing read-only queries)

Am I understanding that your objection to this, markus makela, is that some clients will just ignore-and-retry after any error? That doesn't seem like too much of a downside; at least some clients will recognize the error and realize they need to immediately stop connecting to this server.

Way back, georg also proposed implementing the redirect with a custom error.

I think that if the response type (Err instead of OK) changes, this is not a graceful redirection and it turns into a forceful redirection. This is probably something that is not always wanted and if the idea is to make it opt-in, modifying the behavior breaks that goal. In addition, since old clients won't have any idea what to do with the error, they'll just report it back to the application.

This sort of stop connecting to this server kind of behavior can be implemented on top of the redirection but I don't think it's a core part of it. For a prompt shutdown, this would be a good feature similar to read_only) but a more forceful one that prevents all actions from being done. In the list of steps I listed previously, it would be after the first graceful redirection step and after max_connections is lowered to prevent new connections.

I think that if the response type (Err instead of OK) changes, this is not a graceful redirection and it turns into a forceful redirection.

Agreed. I wasn't totally clear on whether there were less-than-forceful redirection scenarios envisioned here.

This sort of stop connecting to this server kind of behavior can be implemented on top of the redirection but I don't think it's a core part of it.

Agree.

Is the name of the variable going to be redirect_url? Getting that locked in early would allow MaxScale to use the same name for it.

markus makela I will know after I start working on it next development cycle. Maybe serg can comment on this?

I ended up making the initial version of this in MaxScale very generic in the form of a customizable metadata string of the form connection_metadata=key=value,key2=value2 that's added to the OK packet that's sent once authentication is complete. The values are encoded as system variable changes as was suggested by serg.

This should make it possible to use it to implement basic connection redirection that's intended for graceful shutdowns.

I've recently submitted a working implementation:

• Discussion on the developers mailing list: https://lists.mariadb.org/hyperkitty/list/developers%40lists.mariadb.org/thread/7UGBTX2B2KPH7UAXCCWFMUNIRQEA3WLS/
• Connector/C PR: https://github.com/MariaDB/server/pull/2681
• Server PR: https://github.com/mariadb-corporation/mariadb-connector-c/pull/226

The very short summary of how it works is:

• On the server, an admin will set the following variables to begin redirecting new connections:

  SET GLOBAL server_redirect_target='other-mariadb-server.company.com:3306';

  SET GLOBAL server_redirect_mode=ON;

• When a supporting client connects, it will receive a pre-authentication error packet instructing it to redirect to the appropriate host/IP and port
  • Older clients won't be able to automatically redirect, but the error message is still human-readable and clearly explains what's going on
• Connections to a local socket (Unix socket or Windows named pipe), and to the EXTRA_PORT are excluded from redirection (but this can be overridden with server_redirect_mode=ALL.

I took a look at the client-side pull request (the links in your comment got mixed up) and I have some comments:

• The use of the redirection mechanism needs to be configurable as there are cases where redirections should never happen (e.g. when used by monitoring applications). As such, it seems like making it an opt-in feature would be better, at least for the relatively simple C connector. The JDBC and Node.js connectors that have connection pools seem like better candidates for the "smartness" of automatic reconnection.
• Self-redirects aren't detected which will lead to an infinite reconnect loop. Not really something you'd expect to do on purpose but running SQL commands that you intended for one server on another one isn't exactly unheard of.
• Some form of max_redirects would need to also be in place to prevent the case where the redirects end up creating a loop.
• It's sort of expected behavior but right now the client completely trusts the server to perform the redirection and has no agency in choosing whether a redirection should be done. Some accepted list of URLs might have to be added for redirection to make sure a client isn't redirected somewhere where it doesn't want to be redirected. An example of this I can think of if an application would be redirected across the globe, it might be preferable to fail instead of accepting the redirect.
• I'm not 100% sure about this but the connection timeouts might get multiplied by the number of redirects that take place. If my speculation is correct, it would mean that with a timeout of 10 seconds and with 5 redirections, the worst-case connection timeout might end up being close to 50 seconds. Personally I wouldn't consider this a very important thing to fix but there are cases where you want things to resolve within the configured timeouts, for example to fail over to another service or return an error to the client.

For the server-side pull request, I don't have much comments apart from the implementation approach. In its current incarnation it's a forceful redirection and doesn't allow any of the other use-cases described in this issue (e.g. zero-overhead proxying) from being implemented on top of it. However, I also think that the feature which uses the system variable tracker can be built on top of the suggested error packet implementation. It would cover use-cases other than forceful redirection and also make it possible to have a longer redirection period. As such, I'd name the value for server_redirect_mode as FORCE rather than ON: this way the door is left open for a less intrusive method of redirection which would also solve the problem you've described in CONC-648.

More thoughts about the session_track_system_variables approach:

• as written above, it might be desirable to keep sending the redirect info with every server reply, not just once. it would be unnatural for a system variable tracker to do that (but, of course, possible).
• tracker info isn't added to the first OK packet (solved in MDEV-31609)
• cannot do a forceful redirection "redirect + disconnect" actually, the server can disconnect after sending redirection info, whether it's an error or an ok packet
• the original user, who requested the feature wrote above that they disable CLIENT_SESSION_TRACK to avoid "extra networking overheads"

Let's get compare with the "error message" approach:

• can be forceful, but also advisory, if needed, if sent as a Note or Warning.
• notes can be sent for every packet, repeatedly, can be sent for the first OK, can be sent even for ERROR packets

But (drawbacks):

• A client will need to parse the error message (at least it does in the PR#2681)
• CONC-648 is no longer a nuisance, but a serious problem
• a user can still session-track redirect_url, duplicating the error/note functionality
• with advisory redirect (warning/note) a connector has to do SHOW WARNINGS automatically for every statement with non-zero warning count — it cannot rely on application doing that. Warnings aren't bundled with the OK packet.

It seems to me that session tracking is still preferable, despite its deficiencies.

markus makela wrote:

The use of the redirection mechanism needs to be configurable as there are cases where redirections should never happen (e.g. when used by monitoring applications). As such, it seems like making it an opt-in feature would be better, at least for the relatively simple C connector.

Absolutely. My very minimal client-side implementation at mariadb-connector-c #226 could/should be extended to offer --no-follow-redirects as an option.

• Self-redirects aren't detected which will lead to an infinite reconnect loop.
• Some form of max_redirects would need to also be in place to prevent the case where the redirects end up creating a loop.

Indeed.

Since I'm trying to follow the battle-tested semantics of HTTP(S) clients here, I think it would make sense to implement this in terms of a client-side "max redirection" limit, like most web browsers have. And cURL has:

       --max-redirs <num>

              (HTTP) Set maximum  number  of  redirection-followings  allowed.

              When  -L,  --location is used, is used to prevent curl from fol‐

              lowing redirections "in absurdum". By default, the limit is  set

              to 50 redirections. Set this option to -1 to make it unlimited.

As such, I'd name the value for server_redirect_mode as FORCE rather than ON: this way the door is left open for a less intrusive method of redirection

Good idea!

… which would also solve the problem you've described in CONC-648.

CONC-648 is a vulnerability caused by a TLS-using client to improperly trust information that is sent in plaintext prior to the TLS handshake. In CONC-654/MDEV-31585, it's even worse: the server actually requires certain client configuration information to be sent in plaintext, and ignores it when it's subsequently sent over the TLS channel.

CONC-648 makes an Error-packet-based redirection mechanism extremely exploitable by a MITM attacker; but not having such a mechanism doesn't solve CONC-648 in any way.

Oh yes, I didn't express myself correctly: if the implementation of redirection is done post-authentication, a problem similar to CONC-648 would be avoided. It doesn't solve CONC-648 by itself.

serg wrote:

More thoughts about the session_track_system_variables approach:

• …
• cannot do a forceful redirection "redirect + disconnect"
• …

Let's get compare with the "error message" approach:

…

It seems to me that session tracking is still preferable, despite its deficiencies.

This is a great summary of the technical pros and cons but…

It seems like "no forceful redirect" is a major deficiency of session tracking. (And for old clients or clients with session tracking disabled, there will be no way at all to force them to notice the redirection, unless with the ERR-packet approach.)

As you noted yourself on the mailing list, you don't know of any actual use cases for advisory-only redirection. By contrast, we are aware of one major use case for "forceful" redirection, graceful server shutdown, as discussed here and on the mailing list. And I don't see any comments here, in the last 5 years, from someone who isn't a MariaDB core developer.

So I'm quite confused about the idea that the "preferable" approach here is to implement a version of this feature which has no known actual use cases… particularly given that I have already implemented a version that does have practical use cases.

Hi!

The proposal in https://lists.mariadb.org/hyperkitty/list/developers@lists.mariadb.org/thread/7UGBTX2B2KPH7UAXCCWFMUNIRQEA3WLS/ to have a new error code ERR_REDIRECT and a IP/hostname payload so clients can connect to a new server perfectly suits the use case of one server being shut down and new connections being asked to go to a new server. This is a simple and solid approach, modeled after the time--tested HTTP 301 redirect, with TLS considerations and all.

Could we please get green light from Georg and Sergei for https://github.com/mariadb-corporation/mariadb-connector-c/pull/226 and https://github.com/MariaDB/server/pull/2681?

If you later want to have a more complex version that fulfills other use cases, you can have that too. This implementation does not block voluntary suggest redirect to logged in users approach via session_track_system_variables as suggested in this Jira. You can have both if you want.

dlenski, the original use case was load balancing. And that didn't need any code in the server it was supposed to be done in the proxy. Anyway, the user who wanted it stopped answering years ago.

And again, as far as graceful shutdown is concerned, any approach works, because any redirect will be forceful when the server shuts down. So this is not an argument. All the drawbacks from the above still apply, though.

otto, no, it would be strange to have two completely different approaches to the same functionality, if we'll do error message approach we'll likely stick with it.

Since the redirect affects not only C/C, but all MariaDB connectors, Serg and I are not the only decision makers, but other connector maintainers as well.

Speaking of Connector/C and other Connectors based on C/C the session tracking approach would be easy to implement, even if an older Connector/C (without redirection support) would be used.

Example in MariaDB Connector/Python:

import mariadb

def status_tracker(connection, status):

   if 'redirect' in status:

       # check if connector/c supports redirection

       if parse_version(mariadb.mariadbapi_version) < parse_version('3.3.7'):

           if connection.tls_cipher

               connection.redirect(status['redirect'])

           else

               raise Exception("No TLS connection - reconnect failed")

       else:

           # server automatically reconnected

           ....

connection= mariadb.connect(user="foo", status_callback=status_tracker, host="my.test.biz")

From my perspective using error packet has several disadvantages:

• client needs to indicate capability to support redirecting (instead of registering a session track variable).
• client doesn't get notification about server redirection
• applications which want to support redirect can't use older versions of C/C.
• workaround for supporting older C/C versions is to rewrite/hack applications error handling code

From the MaxScale point of view, adding support for server redirection via session state tracking should be trivial now that MXS-4635 is implemented for 23.08. All one would need to do make it propagate from MariaDB to all MaxScale instances is to add it via connection_metadata=server_redirect_target=auto. This'll fetch the value from MariaDB and if it's not empty, it'll get forwarded to the client when it initially connects. For any post-connection redirections, the normal MariaDB mechanism already takes care of it.

I've more or less caught up with the comments and mailing list
discussions and will start implementing the current proposal in the
description of this ticket.

Some relevant info about the implementation I have gathered from the
mailing list thread:

1. The baseline scope of this ticket is to add the session variable
redirect_url that could be an arbitrary string. But optionally,
the initial format of the variable to be validated should be
simple, like host:[port], maybe prepended with an mysql://.
A standard specification will be needed but is not in scope of this
ticket.
2. After the redirect_url changes to non-empty, i.e. the server
would like clients to disconnect and reconnect to redirect_url,
the server will kill all connections / shut down after a timeout.
However this is not in scope of the present ticket.

Hi serg, ptal thanks

https://github.com/MariaDB/server/commit/7be7664cb16

The commit has been updated (see the comment below).

One thing that would be nice is to accept both mysql:// and mariadb://. After all, it's a feature that's currently only in MariaDB so not accepting it seems odd.

> One thing that would be nice is to accept both mysql:// and
> mariadb://. After all, it's a feature that's currently only in
> MariaDB so not accepting it seems odd.

Done[1].

serg, sorry ptal this one instead:

[1] https://github.com/MariaDB/server/commit/04b99a30a3f

Hi serg, thanks for the comments, ptal at the updated commits, thank you

• https://github.com/MariaDB/server/commit/365faf7229f
• https://github.com/MariaDB/server/commit/8ac17273b14

The review has been taking place on the mailing list https://lists.mariadb.org/hyperkitty/list/developers@lists.mariadb.org/thread/2D5LPOFKWXZ35GPIZRAHIJJO3HSPUQCN/

Thanks for the comments serg, ptal at the updated commits

6128bc9b599 MDEV-15935 Adding global/session system var redirect_url
369aab59cf3 MDEV-15935 Clean up string sys_var types.

BTW I only included commit hash this time without the github link, but let me know if I should include the github commit urls instead.

Thanks for the third round review on the clean-up commit serg, ptal at the updated commits

9db04b3dc73 upstream/bb-11.3-mdev-15935 MDEV-15935 Adding global/session system var redirect_url

14a12f98eb8 MDEV-15935 Clean up string sys_var types.

9db04b3dc73 and 14a12f98eb8 are ok to push into preview-11.3-preview branch

with one change:

-  size_t max_length= 2000;

+  const size_t max_length= 2000;

Thanks for the review serg. Done and pushed the following to
preview-11.3-preview:

c2cc895ba61 upstream/preview-11.3-preview MDEV-15935 Adding global/session system var redirect_url

3dd85066499 MDEV-15935 Clean up string sys_var types.

ok to push

Thanks for the testing ramesh, can you clarify which commits are ok
to push, is it the following three?

76e20f00772 * upstream/bb-11.3-mdev-32254 upstream/preview-11.3-preview MDEV-32254 Make NULL an illegal value for redirect_url

#  [... 7 commits omitted]

dd2608fa92d * MDEV-15935 Adding global/session system var redirect_url

143d4360946 * cleanup: string sys_var types

ycp Test performed on latest preview-11.3-preview commit

commit 76e20f00772148fa928c6c6e42401f38ca89abf0 (grafted, HEAD -> preview-11.3-preview, origin/preview-11.3-preview)

Author: Yuchen Pei <ycp@mariadb.com>

Date:   Wed Sep 27 13:08:34 2023 +1000

    MDEV-32254 Make NULL an illegal value for redirect_url

OK, I squashed 76e20f00772 into dd2608fa92d, and cherry picked the
resulting commit as well as 143d4360946 on top of the current 11.3,
and here's what I get:

84365623309 upstream/bb-11.3-mdev-15935 MDEV-15935 Adding global/session system var redirect_url

afcfe0a9fcd cleanup: string sys_var types

sergramesh let me know if this needs testing by ramesh too
before pushing, otherwise I'll push these commits to 11.3 on Monday
2023-10-23.

ycp I will initiate new pquery test run on bb-11.3-mdev-15935 and let you know if there is any issue asap

ycp pquery test run looks good.

Thanks ramesh. Now that MDEV-32525 is fixed with the commit 3a3bb0771a3abc0ad88fee09ab7eec69935dfb79 in bb-11.3-mdev-15935, could you re-test the branch?

ycp bug fix branch looks good.

Thanks ramesh, pushing now...

pushed the following to 11.3

c4a506f0bf3 upstream/bb-11.3-mdev-15935 MDEV-32525 Validate --redirect_url supplied as server flag
5af70decca3 MDEV-15935 Adding global/session system var redirect_url
6151bde48c8 cleanup: string sys_var types