CODEWITHSUNDEEP
pythonmysqlgoogle-cloud-sql
Praxiteles
Praxiteles

Reputation: 6010

Unable to insert data on Google Cloud SQL but UID field is still AUTO incrementing

I am running into MySQL behavior on Google Cloud SQL I have never seen before.

Every MySQL command we try is working from a Python script except INSERT. We can create the table and show tables, but when we insert data - nothing appears in the table. Yet, if we copy that exact same insert statement to the MySQL command line and run it, the insert works fine.

BUT here is the unusual part. Even though the Python script fails to insert data, the UID AUTO INCREMENT field has incremented for every empty and failed insert. For example, if the Python script fails to insert a row, the next time we run an insert from the mySQL command line, we see that the UID field has incremented by one.

It is as if MySQL started to insert the data, auto-incremented the UID field, but then the data never arrived.

We are using MySQL on Google Cloud SQL. The insert is a simple test:

insert into queue (filename, text) VALUES ('test', 'test')

Any ideas what this is or how to debug it?

Upvotes: 1

Views: 401

Answers (1)

Praxiteles
Praxiteles

Reputation: 6010

It turns out AUTOCOMMIT is set to OFF on Google Cloud SQL.

All SQL inserts must be followed by a commit statement.

For example: 

import MySQLdb
db = mdb.connect(ip, login, pword)
query = "insert into tbname (entity, attribute) VALUES('foo', 'bar')" 
cursor = db.cursor()
cursor.execute(query)
db.commit()

Upvotes: 1

Related Questions