List:General Discussion« Previous MessageNext Message »
From:Michael Dykman Date:May 17 2010 6:28pm
Subject:Re: InnoDB / Transactions question
View as plain text  
MyISAM does not support transactions so it is inherently in
'autocommit mode' all the time.  You will run into this with any
transactional database, be it InnoDB, Falcon, or Oracle and DB2
installations for that matter.

For many classes of application, avoiding autocommit and explicitly
creating and commiting transactions is the only way to keep the data
coherent.  For lightweight purposes,  this can be overkill

On Mon, May 17, 2010 at 2:21 PM, Michael Stroh <stroh@stripped> wrote:
> Thanks Michael, it seems that using that works. I have other python scripts that
> write to other tables on the same installation. The only difference that I can think of is
> that they are MyISAM type whereas these two are InnoDB. Does the different type require
> this flag to be set? Or is there be something else going on here?
> Michael
> On May 17, 2010, at 2:12 PM, Michael Dykman wrote:
>> The autocommit option can be set globally in your config (there
>> probably is an example in the my.conf file that came with your
>> distro).  Alternatively, you may explicitly turn on auto commit on
>> your connection by issuing this command first:
>> set autocommit = 1;
>> As this is a session variable, this only affect the current connection.
>> - michael
>> On Mon, May 17, 2010 at 12:34 PM, Michael Stroh <stroh@stripped> wrote:
>>> 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 scanned.
>>> 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!
>>> Michael
>>> --
>>> MySQL General Mailing List
>>> For list archives:
>>> To unsubscribe:
>> --
>> - michael dykman
>> - mdykman@stripped
>> May the Source be with you.

 - michael dykman
 - mdykman@stripped

 May the Source be with you.
InnoDB / Transactions questionMichael Stroh17 May
Re: InnoDB / Transactions questionMichael Dykman17 May
  • Re: InnoDB / Transactions questionMichael Stroh17 May