Re: [sqlalchemy] Re: partial rollback in transaction ?
On Jan 10, 2011, at 6:09 PM, Romy wrote:
Not sure what you mean, as I've seen hybrid setups before.
I'm pretty sure if you try to make a foreign key from an InnoDB table to a
MyISAM table, it will fail.
In any case, thanks for helping me narrow this down. I need to decide
whether I'll need real transactions here. Despite being an oversight
on my part, do you think perhaps the docs for rollback / commit should
mention DB support ? I did look at them before writing to the list,
and it still didn't dawn on me.
it might be worth it, though MySQL is the only database among all that we
support, to my knowledge, which has such a feature as transactions that do
nothing.
On Jan 10, 1:19 pm, Michael Bayer mike...@zzzcomputing.com wrote:
yah MySQL doesn't really operate with a mixture.
On Jan 10, 2011, at 4:13 PM, Romy wrote:
Face palm.. missed the forest for the trees.
Does this mean both tables would need to be InnoDB ?
On Jan 10, 7:10 am, Michael Bayer mike...@zzzcomputing.com wrote:
One important change here is to change the engine type to InnoDB,
otherwise transactions are entirely meaningless with MySQL.
If I use InnoDB, the end result of used is 0 in all cases. If I don't
and use MyISAM, the end result of used is 1 in all cases, regardless of
whether InviteCode is loaded before, or after, the begin().
It seems likely that the issue is simply that you forgot to use InnoDB.
On Jan 10, 2011, at 1:43 AM, Romy wrote:
Sorry Michael, 'self-contained' wasn't a proper term for that test
given that it required an initial DB state containing a single row.
I've modified your version to try and reproduce the bug.
Since yours didn't use elixir at all and I'm not familiar with
elixir's internals, I was able to reproduce only what I believe to be
the equivalent in sqlalchemy. Please note you'll need to create a
mysql database and fill in the connection string, as the test does not
fail with sqlite!
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
e = create_engine('mysql://user:p...@localhost/test', echo=True)
Base = declarative_base()
class InviteCode(Base):
__tablename__ = 'test_invite_codes'
id = Column(Integer, primary_key=True)
used = Column(Integer, default=0)
users = relationship(User, backref=invite_code)
class User(Base):
__tablename__ = 'test_users'
id = Column(Integer, primary_key=True)
invite_code_id = Column(Integer,
ForeignKey('test_invite_codes.id'))
email = Column(String(128), unique=True)
Base.metadata.create_all(e)
session = Session(e, autocommit=True)
session.query(User).delete()
session.query(InviteCode).delete()
invite_code = InviteCode()
session.add(invite_code)
session.flush()
assert invite_code.used == 0
session.close()
session.begin()
user_row = User(email=n...@unique.com, invite_code_id=None)
session.add(user_row)
session.commit()
invite_code = session.query(InviteCode).first()
assert invite_code.used == 0
session.begin()
invite_code.used = invite_code.used + 1
session.add(invite_code)
session.flush()
user_row_2 = User(email=n...@unique.com, invite_code_id=None)
session.add(user_row_2)
rolled_back = False
try:
session.commit()
except:
rolled_back = True
session.rollback()
assert rolled_back
assert invite_code.used == 0
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group
athttp://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group
athttp://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: partial rollback in transaction ?
One important change here is to change the engine type to InnoDB, otherwise
transactions are entirely meaningless with MySQL.
If I use InnoDB, the end result of used is 0 in all cases. If I don't and
use MyISAM, the end result of used is 1 in all cases, regardless of whether
InviteCode is loaded before, or after, the begin().
It seems likely that the issue is simply that you forgot to use InnoDB.
On Jan 10, 2011, at 1:43 AM, Romy wrote:
Sorry Michael, 'self-contained' wasn't a proper term for that test
given that it required an initial DB state containing a single row.
I've modified your version to try and reproduce the bug.
Since yours didn't use elixir at all and I'm not familiar with
elixir's internals, I was able to reproduce only what I believe to be
the equivalent in sqlalchemy. Please note you'll need to create a
mysql database and fill in the connection string, as the test does not
fail with sqlite!
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
e = create_engine('mysql://user:p...@localhost/test', echo=True)
Base = declarative_base()
class InviteCode(Base):
__tablename__ = 'test_invite_codes'
id = Column(Integer, primary_key=True)
used = Column(Integer, default=0)
users = relationship(User, backref=invite_code)
class User(Base):
__tablename__ = 'test_users'
id = Column(Integer, primary_key=True)
invite_code_id = Column(Integer,
ForeignKey('test_invite_codes.id'))
email = Column(String(128), unique=True)
Base.metadata.create_all(e)
session = Session(e, autocommit=True)
session.query(User).delete()
session.query(InviteCode).delete()
invite_code = InviteCode()
session.add(invite_code)
session.flush()
assert invite_code.used == 0
session.close()
session.begin()
user_row = User(email=n...@unique.com, invite_code_id=None)
session.add(user_row)
session.commit()
invite_code = session.query(InviteCode).first()
assert invite_code.used == 0
session.begin()
invite_code.used = invite_code.used + 1
session.add(invite_code)
session.flush()
user_row_2 = User(email=n...@unique.com, invite_code_id=None)
session.add(user_row_2)
rolled_back = False
try:
session.commit()
except:
rolled_back = True
session.rollback()
assert rolled_back
assert invite_code.used == 0
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: partial rollback in transaction ?
yah MySQL doesn't really operate with a mixture.
On Jan 10, 2011, at 4:13 PM, Romy wrote:
Face palm.. missed the forest for the trees.
Does this mean both tables would need to be InnoDB ?
On Jan 10, 7:10 am, Michael Bayer mike...@zzzcomputing.com wrote:
One important change here is to change the engine type to InnoDB, otherwise
transactions are entirely meaningless with MySQL.
If I use InnoDB, the end result of used is 0 in all cases. If I don't and
use MyISAM, the end result of used is 1 in all cases, regardless of
whether InviteCode is loaded before, or after, the begin().
It seems likely that the issue is simply that you forgot to use InnoDB.
On Jan 10, 2011, at 1:43 AM, Romy wrote:
Sorry Michael, 'self-contained' wasn't a proper term for that test
given that it required an initial DB state containing a single row.
I've modified your version to try and reproduce the bug.
Since yours didn't use elixir at all and I'm not familiar with
elixir's internals, I was able to reproduce only what I believe to be
the equivalent in sqlalchemy. Please note you'll need to create a
mysql database and fill in the connection string, as the test does not
fail with sqlite!
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
e = create_engine('mysql://user:p...@localhost/test', echo=True)
Base = declarative_base()
class InviteCode(Base):
__tablename__ = 'test_invite_codes'
id = Column(Integer, primary_key=True)
used = Column(Integer, default=0)
users = relationship(User, backref=invite_code)
class User(Base):
__tablename__ = 'test_users'
id = Column(Integer, primary_key=True)
invite_code_id = Column(Integer,
ForeignKey('test_invite_codes.id'))
email = Column(String(128), unique=True)
Base.metadata.create_all(e)
session = Session(e, autocommit=True)
session.query(User).delete()
session.query(InviteCode).delete()
invite_code = InviteCode()
session.add(invite_code)
session.flush()
assert invite_code.used == 0
session.close()
session.begin()
user_row = User(email=n...@unique.com, invite_code_id=None)
session.add(user_row)
session.commit()
invite_code = session.query(InviteCode).first()
assert invite_code.used == 0
session.begin()
invite_code.used = invite_code.used + 1
session.add(invite_code)
session.flush()
user_row_2 = User(email=n...@unique.com, invite_code_id=None)
session.add(user_row_2)
rolled_back = False
try:
session.commit()
except:
rolled_back = True
session.rollback()
assert rolled_back
assert invite_code.used == 0
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group
athttp://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: partial rollback in transaction ?
yeah there's not really enough detail here to produce an explanation, you'd need to narrow it down into a self-contained test case. On Jan 9, 2011, at 1:45 AM, Romy wrote: I believe it's both mapped and present in the session -- the log output seems to confirm it: [I 110108 22:40:30 base:1075] BEGIN [I 110108 22:40:30 base:1390] UPDATE invite_codes SET used=%s WHERE invite_codes.id = %s [I 110108 22:40:30 base:1391] (1L, 100L) [I 110108 22:40:30 base:1390] INSERT INTO users (username, email, password, verified, usertype_id, invite_code_id, created, invites_left) VALUES (%s, %s, %s, %s, %s, %s, %s, %s) [I 110108 22:40:30 base:1391] (u'blah', u'a...@b.com', 'snip', False, 1L, 100L, datetime.datetime(2011, 1, 9, 6, 40, 30, 808683), 5) [I 110108 22:40:30 base:1086] ROLLBACK The update's part of the transaction above, but it doesn't get rolled back.. scratching my head here. On Jan 8, 8:20 am, Michael Bayer mike...@zzzcomputing.com wrote: The rollback of attributes is actually an expiration of all mapped attributes.So used would need to be a mapped column for its value to be rolled back. Other than that, invite_code would need to be present in elixir.session for that to work, if something is causing that not to be the case, that would also exclude it from the expiry. On Jan 8, 2011, at 1:23 AM, Romy wrote: This may or may not be specific to SQLAlchemy (rather than elixir), but I figured I'd ask seeing as how this list is much more active. I'm experiencing only a partial rollback in the following code -- invite_code gets incremented even when the exception is caught and session rolled back. When I switch the two lines after begin(), this goes away. I'm using autocommit=True, sqlalchemy 0.6.4. --- invite_code = InviteCode.query.filter_by(code=code).first() elixir.session.begin() user = User(...) invite_code.used = invite_code.used + 1 try: elixir.session.commit() except IntegrityError: # Thrown on duplicate email address elixir.session.rollback() return --- Cheers! -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group athttp://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: partial rollback in transaction ?
On Jan 9, 2011, at 4:26 PM, Romy wrote: Okay. In assembling a test case I've noticed the following behavior. When the mapped object is created before the begin(), the bug is reproducible. When it's created after the begin(), the rollback happens correctly and the 'bug' disappears. Specifically: invite_code = InviteCode.query.first() elixir.session.begin() invite_code.used = invite_code.used + 1 breaks, but the following works: elixir.session.begin() invite_code = InviteCode.query.first() invite_code.used = invite_code.used + 1 Is that normal, or is it a bug ? If it's normal, how do I best deal with needing to use the object before the transaction -- would I need to reissue the query ? Still not seeing how that would make a difference if that's fully what's going on. What is the value of invite_code.used after the assignment ? Does loading invite_code.used change the result of the output ? Can you test assert invite_code in elixir.session ? Thanks. On Jan 9, 8:10 am, Michael Bayer mike...@zzzcomputing.com wrote: yeah there's not really enough detail here to produce an explanation, you'd need to narrow it down into a self-contained test case. On Jan 9, 2011, at 1:45 AM, Romy wrote: I believe it's both mapped and present in the session -- the log output seems to confirm it: [I 110108 22:40:30 base:1075] BEGIN [I 110108 22:40:30 base:1390] UPDATE invite_codes SET used=%s WHERE invite_codes.id = %s [I 110108 22:40:30 base:1391] (1L, 100L) [I 110108 22:40:30 base:1390] INSERT INTO users (username, email, password, verified, usertype_id, invite_code_id, created, invites_left) VALUES (%s, %s, %s, %s, %s, %s, %s, %s) [I 110108 22:40:30 base:1391] (u'blah', u...@b.com', 'snip', False, 1L, 100L, datetime.datetime(2011, 1, 9, 6, 40, 30, 808683), 5) [I 110108 22:40:30 base:1086] ROLLBACK The update's part of the transaction above, but it doesn't get rolled back.. scratching my head here. On Jan 8, 8:20 am, Michael Bayer mike...@zzzcomputing.com wrote: The rollback of attributes is actually an expiration of all mapped attributes.So used would need to be a mapped column for its value to be rolled back. Other than that, invite_code would need to be present in elixir.session for that to work, if something is causing that not to be the case, that would also exclude it from the expiry. On Jan 8, 2011, at 1:23 AM, Romy wrote: This may or may not be specific to SQLAlchemy (rather than elixir), but I figured I'd ask seeing as how this list is much more active. I'm experiencing only a partial rollback in the following code -- invite_code gets incremented even when the exception is caught and session rolled back. When I switch the two lines after begin(), this goes away. I'm using autocommit=True, sqlalchemy 0.6.4. --- invite_code = InviteCode.query.filter_by(code=code).first() elixir.session.begin() user = User(...) invite_code.used = invite_code.used + 1 try: elixir.session.commit() except IntegrityError: # Thrown on duplicate email address elixir.session.rollback() return --- Cheers! -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group athttp://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group athttp://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: partial rollback in transaction ?
I don't really understand that test since its calling first() on a nonexistent
InviteCode (the table is empty), and seems to be testing something about an
IntegrityError on a completely different table so I can't really see what it is
you're trying to achieve.
Below is a test case that includes only the key features of your test that seem
like they're relevant. Please modify this test case to show me how you get
the erroneous behavior.
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
e = create_engine('sqlite://', echo=True)
Base = declarative_base()
class InviteCode(Base):
__tablename__ = 'invite_codes'
id = Column(Integer, primary_key=True)
used = Column(Integer, default=0)
users = relationship(User, backref=invite_code)
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
invite_code_id = Column(Integer, ForeignKey('invite_codes.id'))
email = Column(String, unique=True)
Base.metadata.create_all(e)
session = Session(e, autocommit=True)
invite_code = InviteCode()
session.add(invite_code)
session.flush()
assert invite_code.used == 0
session.close()
invite_code = session.query(InviteCode).first()
invite_code.used = invite_code.used + 1
session.begin()
user_row = User(
email=sam...@email.com,
invite_code_id=None)
session.commit()
assert invite_code.used == 1
On Jan 9, 2011, at 8:58 PM, Romy wrote:
On Jan 9, 1:48 pm, Michael Bayer mike...@zzzcomputing.com wrote:
Still not seeing how that would make a difference if that's fully what's
going on. What is the value of invite_code.used after the assignment ?
Does loading invite_code.used change the result of the output ? Can you
test assert invite_code in elixir.session ?
If I insert print statements before and after the assignment, it shows
that it correctly increments invite_code.used by 1. So, if before is 1
after is 2, etc. Not sure what you meant by loading invite_code.used,
but I was printing it after the assignment (and it prints the
correctly updated value). assert invite_code in elixir.session is True
Here's a link [http://linux.ucla.edu/~bsack/dbtest.tgz] to a self-
contained test that requires elixir and a single row in the
invite_codes table, and produces the following output on my machine:
r...@dev-01:~$ ./dbtest.py
elixir: 0.7.1
sqlalchemy: 0.6.4
{'code': '1234567', 'email': 'a...@b.com', 'used': 8L, 'created_by_id':
30L, 'total': 1L, 'id': 1L}
before: 8
after: 9
Email already in use. Rolling back.
Afterwards, the database used value stays updated at 9, and will get
incremented every time I run.
Thanks.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
