MySQL InnoDB Cluster 8.0.12 – avoid old reads on partitioned members

We received feedback about how a member should act when leaving the group. And the majority of users wanted that when a node drops out of the group, it should kill all connections and shutdown. I totally agree with that behavior and it’s now the default in MySQL 8.0.12.

This new feature is explained in WL#11568.

Before this change, the server goes into super read only mode when dropping out of the group and allows users connected to this server or new connections (if you don’t use the router) to read old data.

Let’s check this out in the following video:

So now in MySQL 8.0.12, there is a mnew option called group_replication_exit_state_action and be default, when a node is evicted of the group it should abort mysqld (shutdown MySQL). This terminate all user connections and avoid stale reads… Let’s have a look at this in action:

Hey !? What happened here ? We were still able to read old data if we perform a new connection directly to the server ! Of course this is not recommended. All existing connection have been killed, those connected via the router or directly, but we are still able to connect again directly to the server and read data. This is of course a good reason to always use the router (or another routing/proxy solution that monitors the cluster).

But can we also avoid this ?

Yes of course ! In fact this is not a MySQL issue per se, but it is systemd that restarts the mysqld process. This is the default behavior.

Let’s see how to change this and how it acts:

As you could notice, once we have configured systemd to not restart mysqld on failure, it works as expected.

Enjoy MySQL InnoDB Cluster and don’t forget to register to Oracle Open World if you want to learn more about MySQL 8.0 and InnoDB Cluster !

Update:

If you set restart=no for systemd, you have a safer environment, however you won’t be able to use the RESTART command from MySQL 8.0. It will act as SHUTDOWN as systemd won’t allow a restart of the process.

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

10 Comments

  1. Hi,

    I find the comment “We received feedback about how a member should act when leaving the group. And the majority of users wanted that when a node drops out of the group, it should kill all connections and shutdown” quite interesting.

    In many cases I would say it depends. I would also think that this solution is not really that ideal. In theory a temporary network connectivity failure (of long enough to trigger this) will bring down the whole cluster in a way which it will not recover from without external help. When you lose a single node you still need help, so it is not ideal.

    Life was black before: “you read stale data”, but I am not sure it is white afterwards “In some cases you will not get any data” (that might be worse but I guess it depends on what you are doing).

    So what is the real problem? You do not want clients to read stale data? Why not fix only that?

    I would guess that you want:
    * clients can not query servers which are not part of the active cluster
    * ideally monitoring can query server which are part of the active cluster (but not “cluster data” only local data, e.g. mysql, information_schema or performance_schema tables ?)
    * you want recovery to rejoin or reform the cluster to be as fast as possible. Shutting down members of the cluster does not really help here. It will not be noticed while you have a quorum but once you go below that there is no return. Network glitches happen. If the cluster will come back and rebuild itself automatically that’s certainly a solution I would prefer than having to go and restart cluster members. Note: corruption and inconsistencies may be reasons to want to shut things down. That is slightly different and seems not to be your use case.
    * given there are often proxies sitting in front of the cluster [1] you need to notify them of such changes in state. If the server does not go down then you still want to disconnect active connections but also want to notify external systems of such changes. Is there not already a way to get streams of state changes? I thought there was now. (In MySQL X protocol?) If so use that to notify proxies.

    Either way having a single tcp listener does not help as all connections go through this end point. Monitoring and “system” processes might want to use something different to the end point used by “application users”? So making the single listen port configuration[2] more flexible might help here.

    I guess the point I am trying to make is that a dead server is dead. A disconnected server should be able to fix itself with help if the cause is resolved. I would certainly favour that as a solution. If that is where you want to go why not hint at that? This post seems to provide a solution, one that I am not sure I would like to use. (At least behaviour is configurable, so thanks for that.)

    Regards,

    Simon

    [1] Why not try supporting SRV records, that would make life easier, and skip dead boxes? Add that to the mysql client library and all processes that connect to MySQL can use that. See: https://bugs.mysql.com/bug.php?id=72808. Suggestion from 2014.
    [2] It is not really a single port as there’s a tcp socket and also a unix socket. It might be convenient to be able to configure ipv4 and ipv6 separately, and in any case for “tcp” having the option to configure more than one endpoint (and being able to reconfigure this dynamically) would be really nice.

    • Hi Simon, thank you for your feedback. Don’t forget that this is an option that can be changed and if you use some proxy to connect to your cluster (like ProxySQL) you are able to monitor all this via performance_schema or sys views. We are also working on making the Group more robust on network glitches. Of course your points are valid and I take note of them for future improvement driven by the Community. Having now the new shell and X Protocol, SRV records could be something interesting.

    • The read & write splitting is done via TCP ports. This means the application should be aware of what it wants to achieve. By default, if you send traffic to the port 6446 you will be able to send writes by reaching the Primary-Master, if you send traffic to port 6447 then a Secondary-Master (Read-Only) will be addressed.

Leave a Reply

Your email address will not be published. Required fields are marked *

As MySQL Community Manager, I am an employee of Oracle and the views expressed on this blog are my own and do not necessarily reflect the views of Oracle.

You can find articles I wrote on Oracle’s blog.

Continue