sqlobject-discuss

[SQLObject] MySQL and transactions

[John A. B. <ja...@os...>]

Hi,

I noticed something strange when looking at the MySQL query log: after
every query by SQLObject, a COMMIT was being issued.  I tracked it down
to the _runWithConnection method in the DBAPI class, which calls
conn.commit() in line 3.  I'm baffled as to why this is necessary.  I'm
not sure about Postgres, but in MySQL a COMMIT is ignored unless you're
using InnoDB and AUTOCOMMIT=3D0.  Any clues?

Speaking of InnoDB, it's becoming more and more popular, so shouldn't
MySQL transactions be supported in SQLObject?  It won't break things
with non-InnoDB tables, it'll just be silently ignored.  Would a patch
for this be accepted?

-jab

--=20
John A. Barbuto	                                ja...@os...
Senior System Administrator,
Open Source Development Network                 http://www.osdn.com/

[SQLObject] MySQL and transactions

[Jaime W. <pro...@gm...>]

Hi all.  Can anyone tell me why the code below commits rows to the table?
I've read the documentation and I'm not quite sure why it is happening.
# BEGIN CODE SNIPPET
from sqlobject import *

conn = connectionForURI('mysql://user:pass@localhost/test?debug=1')

class TestTable(SQLObject):
    _connection = conn
    col1 = StringCol()
    col2 = StringCol()

TestTable.createTable(ifNotExists = True)

trans = conn.transaction()
t = TestTable(col1 = 'col1', col2 = 'col2')
trans.rollback()

# END CODE SNIPPET
The console output is below.  I expected to see a `BEGIN` or `START
TRANSACTION` somewhere at the beginning of the output, but it is not there.
Is this a bug, or user error :) ?

 1/Query   :  DESCRIBE test_table
 2/QueryIns:  INSERT INTO test_table (col2, col1) VALUES ('col2', 'col1')
 2/QueryOne:  SELECT col1, col2 FROM test_table WHERE ((test_table.id) =
(3))
 1/ROLLBACK:

Thanks!
jw

-- 
"Government does not solve problems; it subsidizes them."
Ronald Reagan

Re: [SQLObject] MySQL and transactions

[Oleg B. <ph...@ph...>]

On Mon, Oct 22, 2007 at 07:59:34AM -0500, Jaime Wyant wrote:
> trans = conn.transaction()
[skip]
> The console output is below.  I expected to see a `BEGIN` or `START
> TRANSACTION` somewhere at the beginning of the output, but it is not there.
> Is this a bug, or user error :) ?

   Neither. Opening a transaction is implemented via DB API driver
- usually using its .autocommit() method, and what way the method is
implemented  is up to the driver; SQLObject doesn't know what the driver
does.

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] MySQL and transactions

[Jaime W. <pro...@gm...>]

It turned out to be user error... My tables weren't INNODB, which I ASSumed
would automagically happen after specifying the INNODB paramaters in the
MYSQL ini file.  Anyway, the answer to my problems were found here ->

http://www.sqlobject.org/FAQ.html#how-can-i-specify-the-mysql-engine-to-use-or-tweak-other-sql-engine-specific-features

Thanks!
jw


On 10/22/07, Oleg Broytmann <ph...@ph...> wrote:
>
> On Mon, Oct 22, 2007 at 07:59:34AM -0500, Jaime Wyant wrote:
> > trans = conn.transaction()
> [skip]
> > The console output is below.  I expected to see a `BEGIN` or `START
> > TRANSACTION` somewhere at the beginning of the output, but it is not
> there.
> > Is this a bug, or user error :) ?
>
>    Neither. Opening a transaction is implemented via DB API driver
> - usually using its .autocommit() method, and what way the method is
> implemented  is up to the driver; SQLObject doesn't know what the driver
> does.
>
> Oleg.
> --
>      Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
>            Programmers don't die, they just GOSUB without RETURN.
>
> -------------------------------------------------------------------------
> This SF.net email is sponsored by: Splunk Inc.
> Still grepping through log files to find problems?  Stop.
> Now Search log events and configuration files using AJAX and a browser.
> Download your FREE copy of Splunk now >> http://get.splunk.com/
> _______________________________________________
> sqlobject-discuss mailing list
> sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
>



-- 
"Government does not solve problems; it subsidizes them."
Ronald Reagan

Re: [SQLObject] MySQL and transactions

[Dan P. <da...@ag...>]

On Monday 22 October 2007, Jaime Wyant wrote:
> Hi all.  Can anyone tell me why the code below commits rows to the
> table? I've read the documentation and I'm not quite sure why it is
> happening. # BEGIN CODE SNIPPET
> from sqlobject import *
>
> conn = connectionForURI('mysql://user:pass@localhost/test?debug=1')
>
> class TestTable(SQLObject):
>     _connection = conn
>     col1 = StringCol()
>     col2 = StringCol()
>
> TestTable.createTable(ifNotExists = True)
>
> trans = conn.transaction()
> t = TestTable(col1 = 'col1', col2 = 'col2')
> trans.rollback()
>
> # END CODE SNIPPET
> The console output is below.  I expected to see a `BEGIN` or `START
> TRANSACTION` somewhere at the beginning of the output, but it is not
> there. Is this a bug, or user error :) ?

I'd say it's an user error. You assigned a non-transactional connection to 
the TestTable class (with _connection = conn). The transaction you 
created later will not be used with the statements unless you explicitly 
say so with:

t = TestTable(col1 = 'col1', col2 = 'col2', connection=trans)

otherwise TestTable._connection will be used, which is non-transactional

>
>  1/Query   :  DESCRIBE test_table
>  2/QueryIns:  INSERT INTO test_table (col2, col1) VALUES ('col2',
> 'col1') 2/QueryOne:  SELECT col1, col2 FROM test_table WHERE
> ((test_table.id) = (3))
>  1/ROLLBACK:
>
> Thanks!
> jw



-- 
Dan

Re: [SQLObject] MySQL and transactions

[Ian B. <ia...@co...>]

On Thu, 2003-07-17 at 21:29, John A. Barbuto wrote:
> Hi,
> 
> I noticed something strange when looking at the MySQL query log: after
> every query by SQLObject, a COMMIT was being issued.  I tracked it down
> to the _runWithConnection method in the DBAPI class, which calls
> conn.commit() in line 3.  I'm baffled as to why this is necessary.  I'm
> not sure about Postgres, but in MySQL a COMMIT is ignored unless you're
> using InnoDB and AUTOCOMMIT=0.  Any clues?

Huh... I feel like I must have had some reason for that, but I don't
know what that might be.

> Speaking of InnoDB, it's becoming more and more popular, so shouldn't
> MySQL transactions be supported in SQLObject?  It won't break things
> with non-InnoDB tables, it'll just be silently ignored.  Would a patch
> for this be accepted?

Is there anything currently keeping you from using transactions in this
situation?  It's not tested, but so long as MySQLdb supports it, I don't
see any reason SQLObject shouldn't already.

  Ian

Re: [SQLObject] MySQL and transactions

[Sidnei da S. <si...@re...>]

On Thu, Jul 17, 2003 at 09:47:29PM -0500, Ian Bicking wrote:
| On Thu, 2003-07-17 at 21:29, John A. Barbuto wrote:
| > Hi,
| > 
| > I noticed something strange when looking at the MySQL query log: after
| > every query by SQLObject, a COMMIT was being issued.  I tracked it down
| > to the _runWithConnection method in the DBAPI class, which calls
| > conn.commit() in line 3.  I'm baffled as to why this is necessary.  I'm
| > not sure about Postgres, but in MySQL a COMMIT is ignored unless you're
| > using InnoDB and AUTOCOMMIT=0.  Any clues?
| 
| Huh... I feel like I must have had some reason for that, but I don't
| know what that might be.

Speaking of the said line, I just got hit by it when trying to
integrate SQLObject and Zope3. The problem was on a unittest, so not
that big, but I would like to see an option to make this commit
optional. Im not good at naming, but I would suggest something like
`commit_before_execute` or something like that. It would be passed on
the connection initialization and checked on the _runWithConnection method.

Thoughts?

[]'s
-- 
Sidnei da Silva (dreamcatcher) <si...@re...>
Debian GNU/Linux  2.4.20-powerpc ppc

"The Computer made me do it."