Mysql Aurora 8.0 and PyMySQL issue with autocommit

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


               
                
                 Database
                
               
               
                
                 Aurora MySQL


               Language


                English


                 
                  Mario Kirov


              1 Answer

Newest
Most votes
Most comments


                  
                   
                    
                   
                   Are these answers helpful? Upvote the correct answer to help the community benefit from your knowledge.

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.