Hello, I am trying to add a record to a MySQL database using a Z SQL method. The Z SQL method works fine and I have tested it by itself. Also, when called from a DTML method the Z SQL method adds the record to the database but Zope returns the following error: Site Error An error was encountered while publishing this resource. _mysql_exceptions.NotSupportedError Sorry, a site error occurred. Traceback (innermost last): * Module ZPublisher.Publish, line 163, in publish_module_standard * Module Products.PlacelessTranslationService.PatchStringIO, line 45, in new_publish * Module ZPublisher.Publish, line 108, in publish * Module Zope.App.startup, line 226, in abort * Module ZODB.Transaction, line 134, in abort * Module Shared.DC.ZRDB.TM, line 63, in abort * Module Products.ZMySQLDA.db, line 328, in _abort NotSupportedError: (1196, "Warning: Some non-transactional changed tables couldn't be rolled back") I have checked the Zope error log but this error is not logged. Any help would be greatly appreciated. Thanks. - Asad
Asad Habib wrote at 2004-5-12 10:32 -0400:
Hello, I am trying to add a record to a MySQL database using a Z SQL method. The Z SQL method works fine and I have tested it by itself. Also, when called from a DTML method the Z SQL method adds the record to the database but Zope returns the following error: ... * Module Shared.DC.ZRDB.TM, line 63, in abort * Module Products.ZMySQLDA.db, line 328, in _abort
NotSupportedError: (1196, "Warning: Some non-transactional changed tables couldn't be rolled back")
This is not mysterious at all: The standard MySQL tables do not support transactions (there are "inno"db tables that do). Your request was aborted and Zope wanted to tell MySQL to rollback the transaction (and undo the changes done in this request). This is not possible (with standard tables). You get informed about this failure. There is a way to suppress this error report. I think (though I am not sure) that prefixing the connect string with "-" does this. Of course, MySQL does not learn aborts by this. It is just that the problem report is suppressed. The cleaner way is to use transactional tables or a different database system.... -- Dieter
Well, I noticed what was the culprit. It turned out that portal_catalog turned into a 'tumor'. When I export the catalog it results in a 195Mb file while a few days earlier it was only a few Mbs. For some reason it suddenly grew many times larger than all my content together while hardly any new content was added. Reindexing took ages. About 1000+x longer than normal (= 70seconds). Now it took almost an hour to reindex my content. So then I decided to tried I cleared all indexes. I then tried to let it recreate the indexes (Find tab) but that took ages too. In fact it never finished (I stopped it eventually). So that didn't work. Then I got a backup from a few days old. I exported all my content from the tumorous database and imported it into the backup version (after first removing the existing content). Then I let it reindex the portal_catalog in the backup version and then things were back to normal again. It took about 70seconds to reindex and the database was about 45Mb. I have no idea why the catalog suddenly got into this state and why it took about 2 minutes to save plone content (reindexing the object) and why it took one hour for a full reindex. Right now my database is ok again but this really freaks me out!!! At that point I realized again how much of a black box the zope database actually is and I really keep my fingers crossed that nothing more serious will happen. Danny "Dieter Maurer" <dieter@handshake.de> wrote in message news:16546.36012.687963.233027@gargle.gargle.HOWL...
Asad Habib wrote at 2004-5-12 10:32 -0400:
Hello, I am trying to add a record to a MySQL database using a Z SQL method. The Z SQL method works fine and I have tested it by itself. Also, when called from a DTML method the Z SQL method adds the record to the database but Zope returns the following error: ... * Module Shared.DC.ZRDB.TM, line 63, in abort * Module Products.ZMySQLDA.db, line 328, in _abort
NotSupportedError: (1196, "Warning: Some non-transactional changed tables couldn't be rolled back")
This is not mysterious at all:
The standard MySQL tables do not support transactions (there are "inno"db tables that do).
Your request was aborted and Zope wanted to tell MySQL to rollback the transaction (and undo the changes done in this request). This is not possible (with standard tables). You get informed about this failure.
There is a way to suppress this error report. I think (though I am not sure) that prefixing the connect string with "-" does this. Of course, MySQL does not learn aborts by this. It is just that the problem report is suppressed.
The cleaner way is to use transactional tables or a different database system....
-- Dieter
_______________________________________________ Zope maillist - Zope@zope.org http://mail.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://mail.zope.org/mailman/listinfo/zope-announce http://mail.zope.org/mailman/listinfo/zope-dev )
Oops sorry, wrong thread! :-( It's getting late. "Danny Bloemendaal" <Danny.Bloemendaal@companion.nl> wrote in message news:c7u5db$hav$1@sea.gmane.org...
Well, I noticed what was the culprit. It turned out that portal_catalog turned into a 'tumor'. When I export the catalog it results in a 195Mb file while a few days earlier it was only a few Mbs. For some reason it suddenly grew many times larger than all my content together while hardly any new content was added. Reindexing took ages. About 1000+x longer than normal (= 70seconds). Now it took almost an hour to reindex my content. So then I decided to tried I cleared all indexes. I then tried to let it recreate the indexes (Find tab) but that took ages too. In fact it never finished (I stopped it eventually). So that didn't work.
Then I got a backup from a few days old. I exported all my content from the tumorous database and imported it into the backup version (after first removing the existing content). Then I let it reindex the portal_catalog in the backup version and then things were back to normal again. It took about 70seconds to reindex and the database was about 45Mb.
I have no idea why the catalog suddenly got into this state and why it took about 2 minutes to save plone content (reindexing the object) and why it took one hour for a full reindex. Right now my database is ok again but this really freaks me out!!! At that point I realized again how much of a black box the zope database actually is and I really keep my fingers crossed that nothing more serious will happen.
Danny
"Dieter Maurer" <dieter@handshake.de> wrote in message news:16546.36012.687963.233027@gargle.gargle.HOWL...
Asad Habib wrote at 2004-5-12 10:32 -0400:
Hello, I am trying to add a record to a MySQL database using a Z SQL method. The Z SQL method works fine and I have tested it by itself. Also, when called from a DTML method the Z SQL method adds the record to the database but Zope returns the following error: ... * Module Shared.DC.ZRDB.TM, line 63, in abort * Module Products.ZMySQLDA.db, line 328, in _abort
NotSupportedError: (1196, "Warning: Some non-transactional changed tables couldn't be rolled back")
This is not mysterious at all:
The standard MySQL tables do not support transactions (there are "inno"db tables that do).
Your request was aborted and Zope wanted to tell MySQL to rollback the transaction (and undo the changes done in this request). This is not possible (with standard tables). You get informed about this failure.
There is a way to suppress this error report. I think (though I am not sure) that prefixing the connect string with "-" does this. Of course, MySQL does not learn aborts by this. It is just that the problem report is suppressed.
The cleaner way is to use transactional tables or a different database system....
-- Dieter
_______________________________________________ Zope maillist - Zope@zope.org http://mail.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://mail.zope.org/mailman/listinfo/zope-announce http://mail.zope.org/mailman/listinfo/zope-dev )
_______________________________________________ Zope maillist - Zope@zope.org http://mail.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://mail.zope.org/mailman/listinfo/zope-announce http://mail.zope.org/mailman/listinfo/zope-dev )
Dieter Maurer wrote:
The standard MySQL tables do not support transactions (there are "inno"db tables that do).
As far as I know the current MySQLDA implementation doesn't support transactions at all ... even not on innodb tables. Andreas Heckel http://easyleading.org/
Hello. Thanks for your input. All I am doing is inserting a record into a table and as far as I know, this is not considered to be a transaction. A transaction would consist of multiple SQL statements whose execution would either be successsfull or unsuccessfull(hence the need to either commit or rollback). The funny thing is that I can insert a record using this same ZSQL method while attempting to test it via the ZMI. However, when called from a DTML method, the NotSupportedError still occurs. Suggestions anyone? - Asad On Wed, 12 May 2004, Dieter Maurer wrote:
Asad Habib wrote at 2004-5-12 10:32 -0400:
Hello, I am trying to add a record to a MySQL database using a Z SQL method. The Z SQL method works fine and I have tested it by itself. Also, when called from a DTML method the Z SQL method adds the record to the database but Zope returns the following error: ... * Module Shared.DC.ZRDB.TM, line 63, in abort * Module Products.ZMySQLDA.db, line 328, in _abort
NotSupportedError: (1196, "Warning: Some non-transactional changed tables couldn't be rolled back")
This is not mysterious at all:
The standard MySQL tables do not support transactions (there are "inno"db tables that do).
Your request was aborted and Zope wanted to tell MySQL to rollback the transaction (and undo the changes done in this request). This is not possible (with standard tables). You get informed about this failure.
There is a way to suppress this error report. I think (though I am not sure) that prefixing the connect string with "-" does this. Of course, MySQL does not learn aborts by this. It is just that the problem report is suppressed.
The cleaner way is to use transactional tables or a different database system....
-- Dieter
Asad Habib wrote:
Hello. Thanks for your input. All I am doing is inserting a record into a table and as far as I know, this is not considered to be a transaction. That's not right ...
A transaction would consist of multiple SQL statements whose execution would either be successsfull or unsuccessfull(hence the need to either commit or rollback). In short ... also a single SQL statement can end up in multiple database operatoions so it makes sense to initiate a transaction for it.
Keep in mind: Zope has it's own transaction handling. It initializes an transaction on rendering your DTML Method or ZPT. If an error occures, Zope will try to rollback all subtransactions called. So if you call an ZSQL Method within your DTML Method and you get an error on your DTML Method Zope will try to rollback you insert statement and call the standard_error_messager template. Because MySQL can't handle this rollback you have to delete the inserted data if your DTML fails ... which can also fail and you are left with an inconsistent MySQL database ;-) <flame> Now fellow MySQL users ... what are transactions good for ;-) </flame>
The funny thing is that I can insert a record using this same ZSQL method while attempting to test it via the ZMI. However, when called from a DTML method, the NotSupportedError still occurs. Suggestions anyone?
As far as I know you had to patch the MySQLDA to get it working because it can't determine if transactions are supported. How did you do that? How about using PostgreSQL ? Greetings Andreas Heckel http://easyleading.org/
- Asad
On Wed, 12 May 2004, Dieter Maurer wrote:
Asad Habib wrote at 2004-5-12 10:32 -0400:
Hello, I am trying to add a record to a MySQL database using a Z SQL method. The Z SQL method works fine and I have tested it by itself. Also, when called from a DTML method the Z SQL method adds the record to the database but Zope returns the following error: ... * Module Shared.DC.ZRDB.TM, line 63, in abort * Module Products.ZMySQLDA.db, line 328, in _abort
NotSupportedError: (1196, "Warning: Some non-transactional changed tables couldn't be rolled back")
This is not mysterious at all:
The standard MySQL tables do not support transactions (there are "inno"db tables that do).
Your request was aborted and Zope wanted to tell MySQL to rollback the transaction (and undo the changes done in this request). This is not possible (with standard tables). You get informed about this failure.
There is a way to suppress this error report. I think (though I am not sure) that prefixing the connect string with "-" does this. Of course, MySQL does not learn aborts by this. It is just that the problem report is suppressed.
The cleaner way is to use transactional tables or a different database system....
-- Dieter
_______________________________________________ Zope maillist - Zope@zope.org http://mail.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://mail.zope.org/mailman/listinfo/zope-announce http://mail.zope.org/mailman/listinfo/zope-dev )
Asad Habib wrote at 2004-5-13 13:34 -0400:
Hello. Thanks for your input. All I am doing is inserting a record into a table and as far as I know, this is not considered to be a transaction. A transaction would consist of multiple SQL statements whose execution would either be successsfull or unsuccessfull(hence the need to either commit or rollback). The funny thing is that I can insert a record using this same ZSQL method while attempting to test it via the ZMI. However, when called from a DTML method, the NotSupportedError still occurs. Suggestions anyone?
This indicates some error during the DTML processing. This errors raises an exception. This triggers that Zope want to abort its transaction which aborts the transactions in all connections. And one of these connections, the MySQL connection, raises a secondary error informing about the impossible to abort the transaction. What you see here is an impedance mismatch between Zope and MySQL: Zope has a transaction concept and it wants to perform all actions during a request in a single transaction: ZODB modifications, relational database modifications. However, MySQL cannot fulfill its responsibilities in the Zope framework. As I already suggested: * either use "innodb" tables (which should have transactional behaviour (according to documentation)) * or disable the error report (telling the connection, it should behave non-transactional without warning). -- Dieter
participants (4)
-
Andreas Heckel -
Asad Habib -
Danny Bloemendaal -
Dieter Maurer