By using AWS re:Post, you agree to the AWS re:Post Terms of Use
AWS re:Postre:Post

Mysql Aurora 8.0 and PyMySQL issue with autocommit

0

Hello,

I will try to explain as briefly as possible. We have a MySQL Aurora 8.0 server(version 3.05.1 if you want to reproduce it), where the server default variable value for autocommit is set to "0", meaning it's OFF. I connect with Python's PyMySQL module(v1.1.0, which is the latest one) and I set autocommit=True like so:

pymysql.connect(host='some-host.vpc.abc.com', user='abc', password="cba", autocommit=True)

and when I try to insert/update/delete something it actually doesn't apply the changes, despite autocommit being set to True. This is very strange behavior. Having that in mind I started digging in the module itself and turns out that it's reading some binary is returned from the Mysql server when connection which contains something they refer as to "server_status" . It should return 0 when autocommit is OFF and 2 when it's ON by default, but in case of Aurora it's returning always 2 even if it's actually OFF and the breaks the logic of the module that sets autocommit which results of really confusing consequences.

Can you please check and investigate that issue, because that is really concerning having in mind that this module is used by thousands of processes using RDS.

Topics
Database
Tags
DatabaseAurora MySQL
Language
English
Mario Kirov
asked 4 months ago263 views
1 Answer
0

Hi, I hope my answer would help you. Confirm the autocommit status directly in the Aurora database by checking the global variable value. This will validate if the server setting is actually being overridden. (SHOW VARIABLES LIKE 'autocommit';) By default, autocommit is enabled in Aurora MySQL, so the server_status value returned would be 2. This indicates that all SQL statements are committed implicitly, without the need for an explicit COMMIT statement.

Clients like the PyMySQL Python module rely on the server_status value to determine whether to issue COMMIT statements or not. But in Aurora MySQL, the value may be 2 even if autocommit is actually disabled at the server level.

As a workaround, you could explicitly start transactions and commit/rollback instead of relying on autocommit detection.

I'd summary like below: Aurora autocommit enabled (default): server_status value is 2 Aurora autocommit disabled: server_status value should be 0 but Aurora may still return 2 This mismatch can cause issues for clients expecting the standard MySQL behavior. Explicit transactions are recommended as a workaround when autocommit detection fails.

AWS
Regina
answered 3 months ago
profile picture
EXPERT
Giovanni Lauria
reviewed 24 days ago
  • Mario Kirov
    3 months ago

    Hi Regina, thanks for the answer. I know how to make it work explicitly, but problem is that this is existing bug with Aurora, because PyMySQL is even on official AWS RDS tutorial pages in python examples and people using this module will expect it to work properly with AWS databases and it's not currently. Never in any AWS page it's being noted that you need to explicitly commit every transaction, because autocommit option is not working for this module using Aurora. Autocommit is very important, as I don't imagine people commit after each select whenever you are doing readonly application., it just doesn't make sense. Either PyMySQL should be notified and they need to update the module to stop relying on this or Aurora team need to make sure proper server_status is being returned. Just to make clear that "server_status" is a variable in PyMySQL, they call it that way.

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions

Relevant content