1

I have a maxscale mariadb cluster with one master and two slaves. I am using flask-sqlachemy ORM for querying and writing.

I have written read queries in style

db.session(User).join()....

Now all my read queries are going to max scale master node

Below are maxcalse logs

2021-09-14 17:38:26   info   : (1239) (Read-Write-Service) > Autocommit: [disabled], trx is [open], cmd: (0x03) COM_QUERY, plen: 287, type: QUERY_TYPE_READ, stmt: SELECT some_col FROM user 
2021-09-14 17:38:26   info   : (1239) [readwritesplit] (Read-Write-Service) Route query to master: Primary <

I have tried other ways too

conn = mysql_connector.connect(...)
conn.autocommit(True)
cursor = conn.cursor()
cursor.execute(query)

This works fine and routes query to one of slave.

But my most of code is written in ORM style. Is there any way to achieve this while using flask-sqlalchemy

Sarvesh Kumar
  • 131
  • 1
  • 1
  • 4

1 Answers1

0

If autocommit is disabled, you always have an open transaction: use START TRANSACTION READ ONLY to start an explicit read-only transaction. This allows MaxScale to route the transaction to a slave.

markusjm
  • 2,358
  • 1
  • 11
  • 23
  • How can I use that in flask-sqlalchemy? – Sarvesh Kumar Sep 15 '21 at 06:59
  • There's an open issue for sqlalchemy to support this: https://github.com/sqlalchemy/sqlalchemy/issues/5815 Currently this doesn't seem to be possibly unless you execute an explicit SQL query whenever you want to start a transaction. – markusjm Sep 16 '21 at 07:09