MariaDB - Inserting Values doesn't affect any rows

Question

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.

Were there any failed insertions or rollbacks, or cases where you could have had two transactions open at once? Usually once an id has been allocated the id provider will advance to the next value, even if the previous value ended up not being used (or hasn't been committed yet). — snakecharmerb, Dec 28 '21 at 10:43
Not really. I use FastAPI as my web-app. There I just call the ```write_data()```-function in one endpoint. — Lukas Scholz, Dec 28 '21 at 10:57

score 2 · Accepted Answer · answered Dec 28 '21 at 11:38

2

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 :)

answered Dec 28 '21 at 11:38

kalatabe

2,909
2
15
24

1

That did the job. Great! I now return the ```conn```and ```cur```-Objects from the ```get_conn()```-function – Lukas Scholz Dec 28 '21 at 11:51
As a next exercise, I'd recommend you look into "connection pooling", as opening a new connection for every request in a real application is quite wasteful. – kalatabe Dec 28 '21 at 11:52

score 1 · Answer 2 · answered Dec 28 '21 at 11:13

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.

That doesn't answer why the value isn't inserted. – Lukas Scholz Dec 28 '21 at 11:16 — Lukas Scholz, Dec 28 '21 at 11:16

MariaDB - Inserting Values doesn't affect any rows

2 Answers2