Thread: [SQLObject] transaction support with mysql
SQLObject is a Python ORM.
Brought to you by:
ianbicking,
phd
From: gian p. c. <gp....@su...> - 2005-02-11 14:30:40
|
Hi all, since mysql with innodb supports transaction is there something wrong try to enable supportTransactions in mysql/mysqlconnection.py and implement _setAutoCommit ? If you use MyIsam backend the commit/rollback simply does nothing (I can see some potential misleading behaviour if someone has tables with mixed physical storage, however) Cheers /gp -- Gian Paolo Ciceri Via G.Marconi 5 - 22036 ERBA CO ITALY mobile : ++39 340 7218101 eMail : gp....@ac..., : gp....@su... webSite: http://www.suddenthinks.com ICQ # : 94620118 jabber : gp....@ja... |
From: Oleg B. <ph...@ma...> - 2005-02-11 14:47:26
|
On Fri, Feb 11, 2005 at 03:29:41PM +0100, gian paolo ciceri wrote: > since mysql with innodb supports transaction is there > something wrong try to enable supportTransactions > in mysql/mysqlconnection.py and implement _setAutoCommit ? Would you like to implement, test and publish your patches? Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: gian p. c. <gp....@su...> - 2005-02-12 17:49:27
Attachments:
mysqlconnection.py.trx.patch
|
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Oleg Broytmann wrote: | On Fri, Feb 11, 2005 at 03:29:41PM +0100, gian paolo ciceri wrote: | |>since mysql with innodb supports transaction is there |>something wrong try to enable supportTransactions |>in mysql/mysqlconnection.py and implement _setAutoCommit ? | | | Would you like to implement, test and publish your patches? | | Oleg. Here in attach there's a patch for sqlobject/mysql/mysqlconnection. I've briefly tested it and it seems to work. HTH /gp - -- Gian Paolo Ciceri Via G.Marconi 5 - 22036 ERBA CO ITALY ~ mobile : ++39 340 7218101 ~ eMail : gp....@ac..., ~ : gp....@su... ~ webSite: http://www.suddenthinks.com ~ ICQ # : 94620118 ~ jabber : gp....@ja... -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.0 (Darwin) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCDkGf5CXmEF0gaHgRArVzAKCkqQDfTtUalxAAZ2GbXl7EfMOL/gCgr1V/ s3AXyDbhjA+keQjmN5D/KT4= =oqwn -----END PGP SIGNATURE----- |
From: deelan <de...@in...> - 2005-02-12 18:27:49
|
gian paolo ciceri wrote: (...) > > Here in attach there's a patch for sqlobject/mysql/mysqlconnection. > I've briefly tested it and it seems to work. may i ask you to test it also with mysqldb 1.1.6+ ? for details see my previous post on the subject: <http://sourceforge.net/mailarchive/forum.php?thread_id=6352936&forum_id=30269> thank you. -- deelan |
From: Oleg B. <ph...@ma...> - 2005-02-12 18:59:02
|
On Sat, Feb 12, 2005 at 06:49:19PM +0100, gian paolo ciceri wrote: > class MySQLConnection(DBAPI): > > - supportTransactions = False > + supportTransactions = True The problem is that you allow transaction without testing if the database and tables really support transactions. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: gian p. c. <gp....@su...> - 2005-02-12 22:17:02
|
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Oleg Broytmann wrote: | On Sat, Feb 12, 2005 at 06:49:19PM +0100, gian paolo ciceri wrote: | |> class MySQLConnection(DBAPI): |> |>- supportTransactions = False |>+ supportTransactions = True | | | The problem is that you allow transaction without testing if the | database and tables really support transactions. | | Oleg. Correct. I can check if the database does support transactions with mysql variables like 'have_innodb' or 'have_bdb'. I can check if a table does support transaction with SHOW TABLE STATUS. Any idea where to put this check ? I need it in MyClass.get(objectId,trx), but I don't want to subclass SQLObject. Again, any idea how to check if (on the same connection) I've both transactional and not transactional tables involved in a transaction ? TIA /gp - -- Gian Paolo Ciceri Via G.Marconi 5 - 22036 ERBA CO ITALY ~ mobile : ++39 340 7218101 ~ eMail : gp....@ac..., ~ : gp....@su... ~ webSite: http://www.suddenthinks.com ~ ICQ # : 94620118 ~ jabber : gp....@ja... -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.0 (Darwin) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCDoBS5CXmEF0gaHgRAiT+AJ9h68OTfr6u8afmJJW22zPqVg2V+ACfZ/Dg 0QMuxUnConqeY1+yFqSas/8= =rrs/ -----END PGP SIGNATURE----- |
From: Oleg B. <ph...@ph...> - 2005-02-12 22:42:53
|
On Sat, Feb 12, 2005 at 11:16:51PM +0100, gian paolo ciceri wrote: > I can check if a table does support transaction > with SHOW TABLE STATUS. Any idea where to put this check ? I don' see any good place. It seems supportTransaction should not be a connection class' attribute, but a connection instance's instead. Make it a property. For an example look at how I've added server_version property in my patches related to Postgres 7.2: http://phd.pp.ru/Software/Python/misc/SQLObject/ and look at pgconnection.py. > Again, any idea how to check if (on the same connection) I've both > transactional and not transactional tables involved in a transaction ? That's the worst problem with transactions in MySQL. They are not ACID transactions! :( Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: gian p. c. <gp....@su...> - 2005-02-13 11:33:46
Attachments:
mysqlconnection.py.patch
|
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Oleg Broytmann wrote: | On Sat, Feb 12, 2005 at 11:16:51PM +0100, gian paolo ciceri wrote: | |>I can check if a table does support transaction |>with SHOW TABLE STATUS. Any idea where to put this check ? | | | I don' see any good place. It seems supportTransaction should not be | a connection class' attribute, but a connection instance's instead. Make | it a property. For an example look at how I've added server_version | property in my patches related to Postgres 7.2: | http://phd.pp.ru/Software/Python/misc/SQLObject/ and look at | pgconnection.py. | Sounds good. I've upgraded mysqldb to 1.2.0 and implemented supportTransaction as a property. A new patch is attached (this one does not work with mysqldb 0.9.3 anymore, however) To see if a *specific* table does support transactions, I've added a checker, tableHasTransactions, in the mysqlconnect.py and used to add a classmethod to an user class, like this (conn points to a mysql dbms, of course). class MyPerson(SQLObject): _connection = conn ... def hasTransaction(self): return self._connection.tableHasTransactions(self._table) hasTransaction = classmethod(hasTransaction) With this you can test if a table is not-transactional even in a trx.-enabled db and take actions in the user code calling MyPerson.hasTransaction() Since mysql is, AFAIK, the only database where you can have this mixed situations I'm not sure how (and if) to integrate this control in the library. Any idea ? | |>Again, any idea how to check if (on the same connection) I've both |>transactional and not transactional tables involved in a transaction ? | | | That's the worst problem with transactions in MySQL. They are not | ACID transactions! :( Well, the only thing I've found to be aware of this situation is a mysqld warning: "Warning: Some non-transactional changed tables couldn't be rolled back" But this is an ex-post message. You should asssert that any table (I'm thinking to intermediate tables, too) has the transaction support. /gp - -- Gian Paolo Ciceri Via G.Marconi 5 - 22036 ERBA CO ITALY ~ mobile : ++39 340 7218101 ~ eMail : gp....@ac..., ~ : gp....@su... ~ webSite: http://www.suddenthinks.com ~ ICQ # : 94620118 ~ jabber : gp....@ja... -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.0 (Darwin) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCDzsO5CXmEF0gaHgRAp9HAKDXobkqrsGGredcViJDfM+K3Wj3AQCfWd4j JsEFbx1Bzs/ucyLSq6LAnJ8= =z7RW -----END PGP SIGNATURE----- |
From: Ian B. <ia...@co...> - 2005-02-16 00:19:40
|
gian paolo ciceri wrote: > |>Again, any idea how to check if (on the same connection) I've both > |>transactional and not transactional tables involved in a transaction ? > | > | > | That's the worst problem with transactions in MySQL. They are not > | ACID transactions! :( > > Well, the only thing I've found to be aware of this situation is a > mysqld warning: > > "Warning: Some non-transactional changed tables couldn't be rolled back" > > But this is an ex-post message. You should asssert that any table > (I'm thinking to intermediate tables, too) has the transaction support. I guess you could do this when the class was created or assigned a connection. That wouldn't involve too much overhead. Generally I think it needs to be a connection-level configuration parameter -- configuration in that you can't *make* MySQL have transactions, but you can indicate if you intend for it to have transactions, and you really ought to have a consistent set of tables (either all supporting transactions, or don't use transactions with any of them). It's also too bad a failed rollback is a warning, not an exception. -- Ian Bicking / ia...@co... / http://blog.ianbicking.org |