[Zope] mysql auto_increment adding numbers on its own

[Martijn Pieters]

On Tue, Oct 02, 2001 at 08:34:14PM -0400, Ron Bickers wrote:
> I have a ZSQL Method that uses MySQL's auto_increment and LAST_INSERT_ID()
> features to return the next order number.  After my Python Script runs the
> zsql method to obtain the number, it proceeds to encrypt the order and
> updates the newly added MySQL record to include the encrypted text.
> 
> This normally works okay, but somewhat regularly, and for no apparent
> reason, I end up with anywhere from 1 to 3 records in a row with
> auto_incremented numbers, but are null where there is normally the encrypted
> order.  The orders are also e-mailed, and there has never been a case where
> an order was e-mailed and not inserted into the database, so it seems that
> *something* is causing MySQL to insert records on its own even when no order
> is being placed.
> 
> Any ideas on why and/or how to prevent this?

Sounds like you are bitten by the fact that MySQL is by default not
transactional; my guess is that the request is conflicting somewhere, or
maybe you mail server is raising an error, and the transaction is aborted or
retried (the latter in case of a conflict). MySQL will not roll back the
transaction at that moment, while the email hasn't been sent yet.

Note that sending emails right now isn't transactional either; I believe
some people are working on this by postponing sending the actual email until
the transaction is ready to commit. However, if the email normally is sent
out at the end, and a conflict is raised before that point, no email will be
sent at all.

-- 
Martijn Pieters
| Software Engineer  mailto:mj@zope.com
| Zope Corporation   http://www.zope.com/
| Creators of Zope   http://www.zope.org/
---------------------------------------------