SQLAlchemy 0.5.5 MySQL Issue

[gizli]

HI all,

I am having trouble with the autoflush functionality. From the SA
documentation: "Database operations will be issued in the current
transactional context and do not affect the state of the transaction.
You may flush() as often as you like within a transaction to move
changes from Python to the database’s transaction buffer."

This basically means that the transaction is still ongoing and the
changes are buffered at the db side rather than in the SA session
state, right? So I should still be able to commit/rollback changes..

What I see with mysql is closer to autocommit behavior. A simple
example:

session.add(Obj(5))
print session.query(Obj).all()
session.rollback()

What I observe with sqlite is that the print statement prints the
added Obj, however after the program is done, there is no trace of Obj
(5) in the sqlite database. This is expected.

With mysql however, the insert done by the query() actually does a
commit.. i turned on echo, and i dont see an explicit commit but this
is what seems to happen.. After the program is done, I can see Obj(5)
in the mysql database.

Is this a known problem? Or am I doing something wrong here?

Thanks!

[Alexandre Conrad]

2009/11/27 gizli <meh...@gmail.com>:

> With mysql however, the insert done by the query() actually does a
> commit.. i turned on echo, and i dont see an explicit commit but this
> is what seems to happen.. After the program is done, I can see Obj(5)
> in the mysql database.
>
> Is this a known problem? Or am I doing something wrong here?

Which MySQL storage engine are you using?

Alex

[gizli]

I am not sure about the storage engine but here goes:

mysql> status
--------------
mysql Ver 14.12 Distrib 5.0.75, for debian-linux-gnu (i486) using
readline 5.2

the mysql-python version is MySQL_python-1.2.3c1

On Nov 26, 11:47 pm, Alexandre Conrad <alexandre.con...@gmail.com>
wrote:
> 2009/11/27 gizli <mehm...@gmail.com>:

[Alexandre Conrad]

You may be using MyISAM storage engine which doesn't support transactions and may make all flushes persistent. The other storage engine widely used is InnoDB which does support transactions. Find out in the MySQL docs how to figure out which storage engine you're using.

Sent from my fantastic HTC Hero

On Nov 28, 2009 7:27 AM, "gizli" <meh...@gmail.com> wrote:

I am not sure about the storage engine but here goes:

mysql> status
--------------
mysql  Ver 14.12 Distrib 5.0.75, for debian-linux-gnu (i486) using
readline 5.2

the mysql-python version is MySQL_python-1.2.3c1

On Nov 26, 11:47 pm, Alexandre Conrad <alexandre.con...@gmail.com>
wrote:
> 2009/11/27 gizli <mehm...@gmail.com>:

> > > With mysql however, the insert done by the query() actually does a > > commit.. i turned on ec...

-- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To...

[gizli]

Thank you. That was exactly the problem. MyISAM engine was selected as
default, changed to InnoDB and now the autoflush behavior is as
expected.

On Nov 28, 4:36 am, Alexandre Conrad <alexandre.con...@gmail.com>
wrote:

> You may be using MyISAM storage engine which doesn't support transactions
> and may make all flushes persistent. The other storage engine widely used is
> InnoDB which does support transactions. Find out in the MySQL docs how to
> figure out which storage engine you're using.
>
> Sent from my fantastic HTC Hero
>