python mysql.connector not committing

Question

I have this issue both in the development and testing enviroments, the first one hosting a MariaDB database and the other one MySQL database:

I want to increment a field in my database (avoiding race conditions), so I retrieve it, increment and update inside a transaction with REPEATABLE READ isolation level. The problem is that the field doesn't get updated in the database. Meanwhile if I try to execute the SQL query directly using the mysql client, it works as intended.

IMPORTANT NOTE: I commit the connection with self.conn.commit() (as you can see in the code below)

Additional informations:

• I had to change some variable names uploading the code, so if you see some stupid typo it (probably) ins't the problem.
• I had to explicitly cast url to CHAR because it's supposed to be a string like "12345.jpg" and it gave problems in that prepared statement with 2 arguments
• After a lot of tries, the code may be kinda messy, some redundants operations will be removed after solving the issue

here are the relevant functions:

import mysql.connector


class DbInteractor:

    conn = None
    cur = None
    exception_message = 'Exception raised and handled by DbInteractor:\t'

    def connect(self):
        self.conn = mysql.connector.connect( ... )

    def handle_closure(self, ex='', error=False):
        if error:
            print(self.exception_message, ex)
        if self.cur:
            self.cur.close()
        cur = None
        if self.conn:
            self.conn.close()
        conn = None
        if error:
            return -1
        return 1

    def set_cursor(self, prepared=True):
        try:
            self.connect()
        except Exception as ex:
            return self.handle_closure(ex, error=True)
        try:
            self.cur = self.conn.cursor(prepared=prepared)
        except Exception as ex:
            return self.handle_closure(ex, error=True)
        return 1

    def retrieve_number(self, url):
        stmt = 'SELECT numbers FROM photos WHERE url = %s'

        if not self.set_cursor():
            if self.set_cursor() == -1:
                return -1
        try:
            self.cur.execute(stmt, (url,))
            number_list = self.cur.fetchall()
            if len(number_list) != 1:
                return self.handle_closure('failed numbers selection', error=True)
            self.handle_closure()
            result, = number_list[0]
            return result
        except Exception as ex:
            self.handle_closure(ex, error=True)
            return -1

    def increment_number(self, url):

        stmt = "UPDATE photos SET numbers = CAST(%s AS INT) WHERE url = CAST(%s AS CHAR);"
        if self.set_cursor() == -1:
            return -1
        try:
            self.conn.autocommit = False
            self.conn.start_transaction(isolation_level='REPEATABLE READ')
            numbers = self.retrieve_numbers(url)
            print('prima: ', numbers)
            if numbers == -1:
                self.handle_closure('error incrementing numbers', error=True)
                return -1
            numbers += 1
            tuple1 = (str(url), numbers)
            self.set_cursor()
            self.cur.execute(stmt, tuple1)
            self.conn.commit()
            print('dopo: ',numbers)
            new_numbers = self.retrieve_numbers(url)

            if new_numbers != numbers:
                self.handle_closure('race condition incrementing numbers detected, can\'t rollback', error=True)
                return -1

        except Exception as ex:
            self.handle_closure(ex, error=True)
            return -1
        self.handle_closure()
        return 1

Thank you

Answer 1

Reverse the tuple, because the first %s is for the int you catsed, and the string is for the laST 5S

tuple1 = ( numbers,str(url))