InnoDB / Transactions question

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=3Dgcdmg-mysql-2@m.gmane.o rg

Re: InnoDB / Transactions question

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 wrote:
> Thanks Michael, it seems that using that works. I have other python scrip=
ts 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 Inn=
oDB. Does the different type require this flag to be set? Or is there be so=
mething 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). =A0Alternatively, you may explicitly turn on auto commit on
>> your connection by issuing this command first:
>>
>> set autocommit =3D 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 wrote=
:
>>> Hello, I'm currently writing a python program that scans some web direc=
tories and then stores some information in a local database. I'm playing wi=
th using InnoDB for this application as a test bed for using InnoDB in furt=
her 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 qu=
eries and copy and paste them into the SQL input via phpmyadmin, the comman=
ds work as expected. However, I can wrap my statements in START TRANSACTION=
and COMMIT statements which then actually processes everything. I'm gettin=
g some 2014 "Commands out of sync" errors when I use transactions and try t=
o 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 connecti=
on 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 question=
s 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 re=
levant to that record and update a second table named observations with the=
information parsed from that page. This could be thousands of records need=
ing inserting.
>>> Then update the folder table to have the updated field equal to 1 for t=
he records I just scanned.
>>>
>>> I do have a foreign key set in the observations table so that if I dele=
te 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 i=
t so that I don't have to queue up deletion requests for all the observatio=
n request records.
>>>
>>> Thanks in advance!
>>>
>>> Michael
>>>
>>>
>>> --
>>> MySQL General Mailing List
>>> For list archives: http://lists.mysql.com/mysql
>>> To unsubscribe: =A0 =A0http://lists.mysql.com/mysql?unsub=3Dmdykman@gma=
il.com
>>>
>>>
>>
>>
>>
>> --
>> - michael dykman
>> - mdykman@gmail.com
>>
>> May the Source be with you.
>
>



--=20
- michael dykman
- mdykman@gmail.com

May the Source be with you.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: InnoDB / Transactions question

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.
>=20
> 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
>=20
>=20
> On Mon, May 17, 2010 at 2:21 PM, Michael Stroh =
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?
>>=20
>> Michael
>>=20
>>=20
>>=20
>> On May 17, 2010, at 2:12 PM, Michael Dykman wrote:
>>=20
>>> 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:
>>>=20
>>> set autocommit =3D 1;
>>>=20
>>> As this is a session variable, this only affect the current =
connection.
>>>=20
>>>=20
>>> - michael
>>>=20
>>> On Mon, May 17, 2010 at 12:34 PM, Michael Stroh =
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.
>>>>=20
>>>> 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.
>>>>=20
>>>> 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?
>>>>=20
>>>> I can try to send my code if it is helpful, but I hope that my =
questions are general enough to not need it.
>>>>=20
>>>> The basic structure of my program is to do the following.
>>>>=20
>>>> 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.
>>>>=20
>>>> 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.
>>>>=20
>>>> Thanks in advance!
>>>>=20
>>>> Michael
>>>>=20
>>>>=20
>>>> --
>>>> MySQL General Mailing List
>>>> For list archives: http://lists.mysql.com/mysql
>>>> To unsubscribe: =
http://lists.mysql.com/mysql?unsub=3Dmdykman@gmail.com
>>>>=20
>>>>=20
>>>=20
>>>=20
>>>=20
>>> --
>>> - michael dykman
>>> - mdykman@gmail.com
>>>=20
>>> May the Source be with you.
>>=20
>>=20
>=20
>=20
>=20
> --=20
> - michael dykman
> - mdykman@gmail.com
>=20
> May the Source be with you.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg