Hello, I'm currently writing a python program that scans some web directories and then
stores some information in a local database. I'm playing with using InnoDB for this
application as a test bed for using InnoDB in further applications, but I'm running into
a couple issues.
When I try to write my code and send it to the server, it appears as if the commands don't
actually get processed. But if I print out the MySQL queries and copy and paste them into
the SQL input via phpmyadmin, the commands work as expected. However, I can wrap my
statements in START TRANSACTION and COMMIT statements which then actually processes
everything. I'm getting some 2014 "Commands out of sync" errors when I use transactions
and try to perform a second or third set of queries.
Is it required to use transaction statements when using InnoDB, and if not, are there any
ideas on why my installation doesn't seem to autocommit the queries without it? If I do
use transaction statements/InnoDB, is there some common mistake that I'm using or do I
need to use a separate connection to the MySQL database for every query I send?
I can try to send my code if it is helpful, but I hope that my questions are general
enough to not need it.
The basic structure of my program is to do the following.
Scan a webpage and parse it for information to send to the table named folders.
Then scan the folders table for every record that has 0 in the updated field.
Then for every record that the last query matched, scan another page relevant to that
record and update a second table named observations with the information parsed from that
page. This could be thousands of records needing inserting.
Then update the folder table to have the updated field equal to 1 for the records I just
I do have a foreign key set in the observations table so that if I delete a record in the
folders table, then everything in the observations table that contains that key will be
deleted as well. This hopefully will make it so that I don't have to queue up deletion
requests for all the observation request records.
Thanks in advance!