How to use MySQL 8.2 read/write splitting with Connector/Python

As you know, one of the most eagerly waited features was released with MySQL 8.2: the transparent read/write splitting.

In this post, we’ll look at how to use it with MySQL-Connector/Python.

Architecture

To play with our Python program, we will use an InnoDB Cluster.

This is an overview of the cluster in MySQL Shell:

JS > cluster.status()
{
    "clusterName": "fred", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "127.0.0.1:3310", 
        "ssl": "REQUIRED", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "127.0.0.1:3310": {
                "address": "127.0.0.1:3310", 
                "memberRole": "PRIMARY", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "replicationLag": "applier_queue_applied", 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.2.0"
            }, 
            "127.0.0.1:3320": {
                "address": "127.0.0.1:3320", 
                "memberRole": "SECONDARY", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "replicationLag": "applier_queue_applied", 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.2.0"
            }, 
            "127.0.0.1:3330": {
                "address": "127.0.0.1:3330", 
                "memberRole": "SECONDARY", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "replicationLag": "applier_queue_applied", 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.2.0"
            }
        }, 
        "topologyMode": "Single-Primary"
    }, 
    "groupInformationSourceMember": "127.0.0.1:3310"
}

JS > cluster.listRouters()
{
    "clusterName": "fred", 
    "routers": {
        "dynabook::system": {
            "hostname": "dynabook", 
            "lastCheckIn": "2023-11-09 17:57:59", 
            "roPort": "6447", 
            "roXPort": "6449", 
            "rwPort": "6446", 
            "rwSplitPort": "6450", 
            "rwXPort": "6448", 
            "version": "8.2.0"
        }
    }
}

MySQL Connector/Python

The Python program uses MySQL-Connector/Python 8.2.0.

This is the initial code:

import mysql.connector

cnx = mysql.connector.connect(user='python',
                              passowrd='Passw0rd!Python',
                              host='127.0.0.1',
                              port='6450')

cursor = cnx.cursor()

query = ("""select member_role, @@port port 
            from performance_schema.replication_group_members 
            where member_id=@@server_uuid""")

for (role, port) in cursor:
    print("{} - {}".format(role, port))

cursor.close()
cnx.close()

We can already test it:

$ python test_router.py 
PRIMARY - 3310

Good, we can connect to the cluster using the read/write splitting port (6540) and execute the query…. oh ?! But why are we reaching the Primary instance ?

Shouldn’t we access a Read/Only instance (one of the Secondaries) ?

autocommit

Connector/Python disables autocommit by default (see MySQLConnection.autocommit Property). And the Read/Write Splitting functionality must have autocommit enabled to work properly.

Add the following code above line 8:

cnx.autocommit = True

Then we can run the program again:

$ python test_router.py 
SECONDARY - 3320
$ python test_router.py 
SECONDARY - 3330

Great, it works as expected !

query attributes

Now let’s see how to force execution of the query on the Primary node.

The MySQL Router offers the possibility of using a query attribute to force the Read/Write Split decision: router.access_mode.

Add the following line just before executing the query (cursor.execute(query)):

 cursor.add_attribute("router.access_mode", "read_write")

Let’s execute it one more time:

$ python test_router.py 
PRIMARY - 3310

The accepted values for the router.access_mode are:

• auto
• read_only
• read_write

Test with DML

Let’s try something different, we’re going to insert rows into a table.

We’ll use the following table:

CREATE TABLE `t1` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `port` int DEFAULT NULL,
  `role` varchar(15) DEFAULT NULL,
  `timestamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB 

We’ll use the following python script:

import mysql.connector

cnx = mysql.connector.connect(user='python', 
                              password='Passw0rd!Python',
                              host='127.0.0.1',
                              port='6450',
                              database='test')
cnx.autocommit = True
cursor = cnx.cursor()

for i in range(3):
    query = ("""insert into t1 values(0, @@port, (
          select member_role
            from performance_schema.replication_group_members 
            where member_id=@@server_uuid), now())""")
    cursor.execute(query)

cursor.close()
cnx.close()

for i in range(3):
    cnx = mysql.connector.connect(user='python', 
                              password='Passw0rd!Python',
                              host='127.0.0.1',
                              port='6450',
                              database='test')
    cnx.autocommit = True
    cursor = cnx.cursor()
    query = ("""select *, @@port port_read from t1""") 
    cursor.execute(query)
    for (id, port, role, timestamp, port_read) in cursor:
             print("{} : {}, {}, {} : read from {}".format(id, 
                                             port, 
                                             role, 
                                             timestamp, 
                                             port_read))

    cursor.close()
    cnx.close()

Let’s execute it :

$ python test_router2.py 
1 : 3310, PRIMARY, 2023-11-09 17:44:00 : read from 3330
2 : 3310, PRIMARY, 2023-11-09 17:44:00 : read from 3330
3 : 3310, PRIMARY, 2023-11-09 17:44:00 : read from 3330
1 : 3310, PRIMARY, 2023-11-09 18:44:00 : read from 3320
2 : 3310, PRIMARY, 2023-11-09 18:44:00 : read from 3320
3 : 3310, PRIMARY, 2023-11-09 18:44:00 : read from 3320
1 : 3310, PRIMARY, 2023-11-09 17:44:00 : read from 3330
2 : 3310, PRIMARY, 2023-11-09 17:44:00 : read from 3330
3 : 3310, PRIMARY, 2023-11-09 17:44:00 : read from 3330

We can see that there were no errors and that we wrote to the Primary node and read from all Secondaries.

Be careful if you set the query attribute for router.access_mode to read_only just before writing (line 16), you’ll get an error as writes are not allowed on a secondary:

_mysql_connector.MySQLInterfaceError: The MySQL server is running with the --super-read-only option so it cannot execute this statement

Transactions

Now we’re going to play with transactions. We create a new script that will perform several transactions:

1. a read operation in autocommit
2. a read operation in a transaction (by default, this is a read/write transaction)
3. a read operation in a read only transaction
4. a transaction with several inserts and rollback

This is the source of the program:

import mysql.connector

cnx = mysql.connector.connect(user='python', 
                              password='Passw0rd!Python',
                              host='127.0.0.1',
                              port='6450',
                              database='test')
cnx.autocommit = True
cursor = cnx.cursor()
query = ("""select member_role, @@port port 
            from performance_schema.replication_group_members 
            where member_id=@@server_uuid""")
cursor.execute(query)

for (role, port) in cursor:
    print("{} - {}".format(role, port))


cnx.start_transaction()
query = ("""select member_role, @@port port 
            from performance_schema.replication_group_members 
            where member_id=@@server_uuid""")
cursor.execute(query)

for (role, port) in cursor:
    print("{} - {}".format(role, port))

cnx.commit()

cnx.start_transaction(readonly=True)
query = ("""select member_role, @@port port 
            from performance_schema.replication_group_members 
            where member_id=@@server_uuid""")
cursor.execute(query)

for (role, port) in cursor:
    print("{} - {}".format(role, port))
    

cnx.commit()

cnx.start_transaction()

for i in range(3):
    query = ("""insert into t1 values(0, @@port, (
          select member_role
            from performance_schema.replication_group_members
            where member_id=@@server_uuid), now())""")
    cursor.execute(query)

cnx.rollback()
cursor.close()

cnx.close()

Let’s execute the script:

$ python test_router3.py 
SECONDARY - 3320
PRIMARY - 3310
SECONDARY - 3320

We can see that the first operation (1) reached a secondary instance, the second operation (2), which was a transaction, reached the primary node.

The read-only transaction (3) reached a secondary node.

We didn’t get any errors for the multiple writes that were part of the transaction we rolled back.

Conclusion

We’ve seen how easy it is to use MySQL Connector/Python with MySQL 8.2 Read/Write Splitting for an InnoDB Cluster.

Enjoy using MySQL Read / Write Splitting with MySQL Connector Python !