mysql can't rollback all in one transaction

[Former user]

I used one session to insert two records( with same primary key) as follow:

    session1 = Session()
    user1 = User(1, 'user1')
    user2 = User(1, 'user2')
    try:
        with session1.begin(subtransactions=True):
            session1.add(user1)
            session1.add(user2)
    except IntegrityError:
         print('get IntegrityError1')

1) I expect no user can be inserted due to IntegrityError, but I got the usr1 was inserted . It seemed that session didn't rollback all the operations. 2) This only occured with mysql. I can't insert user at all with sqlite with same code.

3) The sqlalchemy version is 0.7.9. Do I miss something or missuse session

[Former user]

sample code with session

[Former user]

1)This is output with mysql

-bash-4.1# ./test1.py
-----------sqlalchemy version-----------
0.7.9
2013-09-11 05:31:23,118 INFO sqlalchemy.engine.base.Engine SELECT DATABASE()
2013-09-11 05:31:23,118 INFO sqlalchemy.engine.base.Engine ()
2013-09-11 05:31:23,124 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'character_set%%'
2013-09-11 05:31:23,125 INFO sqlalchemy.engine.base.Engine ()
2013-09-11 05:31:23,126 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'lower_case_table_names'
2013-09-11 05:31:23,126 INFO sqlalchemy.engine.base.Engine ()
2013-09-11 05:31:23,128 INFO sqlalchemy.engine.base.Engine SHOW COLLATION
2013-09-11 05:31:23,128 INFO sqlalchemy.engine.base.Engine ()
2013-09-11 05:31:23,132 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'sql_mode'
2013-09-11 05:31:23,132 INFO sqlalchemy.engine.base.Engine ()
2013-09-11 05:31:23,133 INFO sqlalchemy.engine.base.Engine DESCRIBE `users`
2013-09-11 05:31:23,133 INFO sqlalchemy.engine.base.Engine ()
2013-09-11 05:31:23,135 INFO sqlalchemy.engine.base.Engine delete from users
2013-09-11 05:31:23,135 INFO sqlalchemy.engine.base.Engine ()
2013-09-11 05:31:23,136 INFO sqlalchemy.engine.base.Engine COMMIT
1
------------test1 ----------------
2013-09-11 05:31:23,138 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2013-09-11 05:31:23,139 INFO sqlalchemy.engine.base.Engine INSERT INTO users (id, name) VALUES (%s, %s)
2013-09-11 05:31:23,139 INFO sqlalchemy.engine.base.Engine ((1, 'user1'), (1, 'user2'))
2013-09-11 05:31:23,140 INFO sqlalchemy.engine.base.Engine ROLLBACK
get IntegrityError1
-----------------------get all users-------------------
2013-09-11 05:31:23,143 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2013-09-11 05:31:23,144 INFO sqlalchemy.engine.base.Engine SELECT users.id AS users_id, users.name AS users_name
FROM users
2013-09-11 05:31:23,144 INFO sqlalchemy.engine.base.Engine ()
<User('1','user1')>

2)This is output with sqlite

-bash-4.1# ./test1.py
-----------sqlalchemy version-----------
0.7.9
2013-09-11 05:31:46,127 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("users")
2013-09-11 05:31:46,127 INFO sqlalchemy.engine.base.Engine ()
2013-09-11 05:31:46,128 INFO sqlalchemy.engine.base.Engine delete from users
2013-09-11 05:31:46,128 INFO sqlalchemy.engine.base.Engine ()
2013-09-11 05:31:46,128 INFO sqlalchemy.engine.base.Engine COMMIT
0
------------test1 ----------------
2013-09-11 05:31:46,139 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2013-09-11 05:31:46,140 INFO sqlalchemy.engine.base.Engine INSERT INTO users (id, name) VALUES (?, ?)
2013-09-11 05:31:46,140 INFO sqlalchemy.engine.base.Engine ((1, 'user1'), (1, 'user2'))
2013-09-11 05:31:46,141 INFO sqlalchemy.engine.base.Engine ROLLBACK
get IntegrityError1
-----------------------get all users-------------------
2013-09-11 05:31:46,143 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2013-09-11 05:31:46,143 INFO sqlalchemy.engine.base.Engine SELECT users.id AS users_id, users.name AS users_name
FROM users
2013-09-11 05:31:46,144 INFO sqlalchemy.engine.base.Engine ()

[Mike Bayer]

Reopen this if I'm incorrect, but I'm 99.9% sure you don't have your tables set as InnoDB (and therefore commit/rollback have no effect):

http://dev.mysql.com/doc/refman/5.0/en/ansi-diff-transactions.html

[Former user]

Yes , it works for me after setting storage engine with InnoDB. Thanks.

[Mike Bayer]

Removing milestone: 0.7.xx (automated comment)