[Zope] Mysterious Database Error

[Andreas Heckel]

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 at 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 )
> 
>