strangeness with MySQL, Python, and bolt

[dja]

If I run a python script from bolt, I can only do read operations, I can't do any write or delete operations. I'm using the *-all username with the same login that is granting me access into phpMyAdmin so I know it's a good login. If I have my script print the actual query to the page and input it as raw sql into phpMyAdmin, an alert dialog box will popup that asks me if I am sure that I want to complete this operation. I click "Ok" and then it's deleted. In my python script, I can print the query results of a SELECT statement, but it seems that's all I can do. I have not tried to create, but I know that I cannot delete or update. These are known good queries, with a known good login, seems strange that a delete or update operation cannot be performed. If it's my code, I apologize but I've tried several permutations of the same operation in order to try to diagnose what's going on and I'm coming up empty. I am using python2.6. Thanks for looking into this.

[williamt]

That doesn't make a lot of sense. If you can connect and issue a SELECT query an UPDATE or DELETE should work also unless your mysql user doesn't have permissions to run those.
Whats the name of your script?

[dja]

/home/d/dja/public_html/gpg_mailer/gpg_changepassphrase.py

i'm using the *-all username so it should have proper permissions. Thanks for looking into this.

[dja]

Logging into mysql shell client using the same login and running the same queries works (so it's not a firewall issue); however, running these queries from Python does not work. Are there any compatibility issues between python2.6's MySQLdb library and the new MySQL5.5?

So, before I was trying to delete and update, now I'm trying to create.
When I already have an identical entry in the database I get an error:

Traceback (most recent call last):
File "gpg_changepassphrase.py", line 24, in <module>
cursor.execute(str(query))
File "/usr/lib/python2.6/site-packages/MySQLdb/cursors.py", line 174, in execute
self.errorhandler(self, exc, value)
File "/usr/lib/python2.6/site-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
raise errorclass, errorvalue
_mysql_exceptions.IntegrityError: (1062, "Duplicate entry 'daily_passphrase' for key 'PRIMARY'")

So, It is logging in properly, but it's not doing anything with the database.
however, when I delete the record using phpMyAdmin and then run the script again, I do not get an exception and the database is not updated.

[dja]

Damn!!! I've never had to commit to a database before, but I knew something was wrong when my script wasn't doing anything and it also wasn't complaining.... Guess that's one of the differences in MySQL5.5... you have to commit on everything! boo!!! though, it probably makes it bit easier to secure against race conditions.... sorry guys, didn't know. my Php scripts don't have to commit so I figured Python wouldn't need to either, but as soon as I did a db commit in the python script it was all good!

[williamt]

Having to commit is a function of your client and tables you are using not the server version.

See: http://mysql-python.sourceforge.net/FAQ ... -t-go-away

[dja]

Thanks man, I guess before I was having my web stuff (PHP code) create and update the records, and my shell stuff (Python) was only reading the records.... I just never noticed. Thanks for the help on this, kinda feeling like a dunce right now..... lol