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.
