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

removed connection.commit() from code to test if it can insert to cloudSQL without commit()
ran the python file few times, data showed up in local terminal but not on cloud database
put connection.commit() back into the code and ran
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)

RahulAN · Accepted Answer · 2020-04-21 19:14:27Z

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

answered Apr 21, 2020 at 19:14

RahulAN

1171 gold badge1 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Yitasha Over a year ago

Looks similar, but I am running it locally with python file, the database is hosted on Google Cloud, is there a way of preventing this kind of things happen with failed inserts?

RahulAN Over a year ago

I think you can use AUTO_INCREMENT=2 while creating table Like below -

CREATE TABLE foo ( id int(11) NOT NULL AUTO_INCREMENT, name int(11) DEFAULT NULL, PRIMARY KEY (id), UNIQUE KEY uniqname (name) ) ENGINE=InnoDB AUTO_INCREMENT=2

Collectives™ on Stack Overflow

Python MySQL is cached at Python side

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related