List:General Discussion« Previous MessageNext Message »
From:Michael Stroh Date:May 17 2010 6:56pm
Subject:Re: InnoDB / Transactions question
View as plain text  
Thanks for the clarification.

Michael


On May 17, 2010, at 2:28 PM, Michael Dykman wrote:

> 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: http://lists.mysql.com/mysql
>>>> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>>>> 
>>>> 
>>> 
>>> 
>>> 
>>> --
>>> - michael dykman
>>> - mdykman@stripped
>>> 
>>> May the Source be with you.
>> 
>> 
> 
> 
> 
> -- 
> - michael dykman
> - mdykman@stripped
> 
> May the Source be with you.

Thread
InnoDB / Transactions questionMichael Stroh17 May
Re: InnoDB / Transactions questionMichael Dykman17 May
  • Re: InnoDB / Transactions questionMichael Stroh17 May