Python MySQL is cached at Python side

Question

everyone, I don't really know how to address my problem, I think it's about cache(memory) error. I am testing the connection of running python locally to Google ClouSQL - MYSQL using CloudProxy

I came across a problem where my table's auto-increment ID is glitched at python's side locally, I am very new to this.

What I did was

1. removed connection.commit() from code to test if it can insert to cloudSQL without commit()
2. ran the python file few times, data showed up in local terminal but not on cloud database
3. put connection.commit() back into the code and ran
4. somehow the auto-increment ID started at where python left, not continued from cloudSQL.

The userID was 11 at both sides, I removed connection.commit() and clicked run few times, it showed database output locally with insertion, then I checked that cloudSQL didn't receive any insertion, so I put connection.commit() back to the code. I was playing around with .commit()

When I click run again with connection.commit(), the output shows userID started at 22 at local and cloudSQL, but it should be 12.

I don't know how to fix this issue, I tried to restart cloud proxy and python program, didn't work. It's not a big problem if I leave commit() there and never remove it, but I just want to know what is going on.

I took screenshots of my terminal output and my CloudSQL database, here's my code.

import mysql.connector

connection = mysql.connector.connect(host='127.0.0.1',
                             user='root',
                             password='root',
                             db='account')
con = connection.cursor()

firstname = "Test123"
lastname = "Test123"
phone = "1234567890"
email = "[email protected]"
address1 = "RMIT Street"
address2 = "Melbourne"
postcode = "1234"
state = "VIC"
username = "test111"
password = "abc123"

#userid will auto increment
statement = "INSERT INTO user (firstname, lastname, phone, email, address1, address2, postcode, state, username, password) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
value = (firstname,lastname,phone,email,address1,address2,postcode,state,username,password)

con.execute(statement,value)
connection.commit()


con.execute("select * from user")
for a,b,c,d,e,f,g,h,i,j,k in con.fetchall():
    print (a,b,c,d,e,f,g,h,i,j,k)

Answer 1

It is not an issue of Python, it is expected. It seems you had some failed inserts and InnoDB actually is a Transactional engine.

Example - Session A inserts record 1 Session B inserts record 2 Session C inserts record 3 Session D failed and rolls back and now the next will be id on the insert will be 4, but 3 won't be there..

Refer -

https://stackoverflow.com/a/2788003/2008987