MariaDB - Inserting Values doesn't affect any rows-CodePudding

I want to insert given values from my docker app-service to the MariaDB-service.

The connection has been established because I can execute SELECT * FROM via the MariaDB.connection.cursor.

First of all I create the connection:

def get_conn() -> mariadb.connection:
    try:
        conn = mariadb.connect(
            user="XXX",
            database="XXX",
            password="XXX",
            host="db",
            port=33030,
        )
    except mariadb.Error as e:
        print(f'Error connecting to MariaDB Platform: {e}')
        sys.exit(1)

    return conn

Then I create a mariadb.connection.cursor-Object:

def get_cur() -> mariadb.connection.cursor:
    conn = get_conn()
    cur = conn.cursor()
    return cur

Finally I want to insert new values in the table testing:

def write_data():
    cursor = get_cur()
    conn = get_conn()

    cursor.execute('INSERT INTO testing (title) VALUE ("2nd automatic entry");')
    print("Executed Query")
    conn.commit()
    cursor.close()
    conn.close()
    print("Closed Connection")

    return True

To test, if the entries are inserted, I started with 1 manual entry, then executed the write_data()-function and to finish of I inserted a 2nd manual entry via the console.

After the procedure the table looks like:

Note that the ìd is on AUTO_INCREMENT. So the function write_data() was not skipped entirely, because the 2nd manual entry got the id 3 and not 2.

CodePudding user response：

You're committing a transaction in a different connection than the one your cursor belongs to.

get_conn() creates a new database connection and returns it.

get_cur() calls get_conn, that gets it a new connection, retrieves a cursor object that belongs to it, and returns it.

In your main code, you call get_conn - that gives you connection A. Then you obtain a cursor by calling get_cur - that creates a connection B and returns a cursor belonging to it.

You run execute on the cursor object (Connection B) but commit the connection you got in the first call (Connection A).

PS: This was a really fun problem to debug, thanks :)

CodePudding user response：

It's really easy, in a new table with new code, to unintentionally do an INSERT without a COMMIT. That is especially true using the Python connector, which doesn't use autocommit. A dropped connection with an open transaction rolls back the transaction. And, a rolled-back INSERT does not release the autoincremented ID value for reuse.

This kind of thing happens, and it's no cause for alarm.

A wise database programmer won't rely on a set of autoincrementing IDs with no gaps in it.