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? _______________________ Ron Bickers Logic Etc, Inc.
Guess: Do you see a ConflictError in the STUPID_LOG_FILE at around the same time that the bogus orders are added? 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?
_______________________
Ron Bickers Logic Etc, Inc.
_______________________________________________ Zope maillist - Zope@zope.org http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
-- Chris McDonough Zope Corporation http://www.zope.org http://www.zope.com "Killing hundreds of birds with thousands of stones"
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/ ---------------------------------------------
-----Original Message----- From: Martijn Pieters [mailto:mj@zope.com]
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.
I figured maybe something like that. Looking at the timestamp of the last two empty MySQL records, they correspond conspicuously with the session logging information I just implemented to debug my disappearing carts: 2001-10-02T21:01:19 INFO(0) Session START new token pyid 147139568 sid 19270093Az-6bVJDKrM ip 65.100.181.192 2001-10-02T21:01:19 INFO(0) Session CALL (add [get]) new token pyid 142775672 sid 19270093Az-6bVJDKrM len 0 cart None 2001-10-02T21:01:19 INFO(0) Session CALL (add [set]) new token pyid 147428688 sid 19270093Az-6bVJDKrM len 0 cart None 2001-10-02T21:01:20 INFO(0) Session CALL (Items [get]) pyid 150552544 sid 19270093Az-6bVJDKrM len 1 cart 1 2001-10-02T21:02:17 INFO(0) Session CALL (checkout [get]) pyid 147444240 sid 19270093Az-6bVJDKrM len 1 cart 1 2001-10-02T21:04:20 INFO(0) Session CALL (gatherCheckout [set]) pyid 147436552 sid 19270093Az-6bVJDKrM len 1 cart 1 2001-10-02T21:04:21 INFO(0) Session CALL (verify [get]) pyid 145239704 sid 19270093Az-6bVJDKrM len 2 cart 1 2001-10-02T21:04:21 INFO(0) Session CALL (Items [get]) pyid 145921552 sid 19270093Az-6bVJDKrM len 2 cart 1 2001-10-02T21:04:49 INFO(0) Session CALL (Items [get]) pyid 145921552 sid 19270093Az-6bVJDKrM len 2 cart 1 2001-10-02T21:04:49 INFO(0) Session CALL (Items [get]) pyid 146880720 sid 19270093Az-6bVJDKrM len 2 cart 1 2001-10-02T21:04:50 INFO(0) Session CALL (sendOrder [invalidate]) pyid 145792704 sid 19270093Az-6bVJDKrM len 2 cart 1 2001-10-02T21:04:50 INFO(0) Session END sid 19270093Az-6bVJDKrM 2001-10-02T21:05:09 INFO(0) Session START pyid 152529584 sid 19270093Az-6bVJDKrM ip 65.100.181.192 2001-10-02T21:05:13 INFO(0) Session START pyid 148488176 sid 19270093Az-6bVJDKrM ip 65.100.181.192 It's the last two entries that have the same timestamp as two empty records in the MySQL database. There are no conflict log entires in the stupid log. The 'Session END' entry above has the same timestamp as the successfully stored and e-mailed order. The only thing the 'sendOrder' python script does after invalidate() is RESPONSE.redirect to another page. It seems that if it made it that far, a simple RESPONSE.redirect wouldn't cause any conflicts. Does this make sense? Is there a more reliable way to produce a sequence of numbers with less (zero?) risk of skipping or duplicate numbers? _______________________ Ron Bickers Logic Etc, Inc.
On Tue, Oct 02, 2001 at 10:25:50PM -0400, Ron Bickers wrote:
It's the last two entries that have the same timestamp as two empty records in the MySQL database. There are no conflict log entires in the stupid log. The 'Session END' entry above has the same timestamp as the successfully stored and e-mailed order. The only thing the 'sendOrder' python script does after invalidate() is RESPONSE.redirect to another page. It seems that if it made it that far, a simple RESPONSE.redirect wouldn't cause any conflicts. Does this make sense?
Is there a more reliable way to produce a sequence of numbers with less (zero?) risk of skipping or duplicate numbers?
I am not sure what your session log actually tells me; are you saying you are creating the MySQL autoinc number at the beginning of a series of requests and responses with a client? Or only when the order is finalized? As for unique, sequential numbering; try a transactional database. Current versions of MySQL can do transactions as well, but for reliability I'd personally opt for PostgreSQL; orders should be stored as securely as possible, automically, etc. And PostgreSQL may even be faster than MySQL for you in your application; MySQL no longer can claim being the fastest. -- Martijn Pieters | Software Engineer mailto:mj@zope.com | Zope Corporation http://www.zope.com/ | Creators of Zope http://www.zope.org/ ---------------------------------------------
-----Original Message----- From: Martijn Pieters [mailto:mj@zope.com]
I am not sure what your session log actually tells me; are you saying you are creating the MySQL autoinc number at the beginning of a series of requests and responses with a client? Or only when the order is finalized?
I included the entire session from start to finish, but probably the only important part is the last 4 entries. There is a single Python Script 'sendOrder' that does the following, in this order, when a user selects the final "send order" link. 1) get the next order number from MySQL. 2) encrypt the order based on session (cart) information. 3) e-mail the encrypted order. 4) store the encrypted order in the record created by #1 above. 5) "invalidate" (end) the session. 6) redirect to a "thank you" page. The log shows that at least through #5 happened, apparently without any problems. The last two log entries (and their corresponding empty records in the MySQL database) seem to indicate that 'sendOrder' ran again, which initiated a new session since the previous had been invalidated. I'm not sure what all this means, but it looks bizarre.
As for unique, sequential numbering; try a transactional database. Current versions of MySQL can do transactions as well, but for reliability I'd personally opt for PostgreSQL; orders should be stored as securely as possible, automically, etc. And PostgreSQL may even be faster than MySQL for you in your application; MySQL no longer can claim being the fastest.
Interesting you should say that. Has PostgreSQL done something in the last 6 months or so to increase its speed? Every time I give it a try, my simple queries used for browsing and searching products prove many times slower than MySQL. It might mean 4 seconds instead of 1, but 4 seconds in Web time is a lot. 100% of my queries (except this order storage) are read only. Maybe I'll try setting up a transaction capable table just for this portion. However, I can't see why the transaction would have been rolled back since it appears to have made it to the end on the first try. _______________________ Ron Bickers Logic Etc, Inc.
On Tue, Oct 02, 2001 at 11:45:51PM -0400, Ron Bickers wrote:
The log shows that at least through #5 happened, apparently without any problems. The last two log entries (and their corresponding empty records in the MySQL database) seem to indicate that 'sendOrder' ran again, which initiated a new session since the previous had been invalidated. I'm not sure what all this means, but it looks bizarre.
I see. You may want to analyze your HTTP logs as well, see if there were multiple requests, etc.
As for unique, sequential numbering; try a transactional database. Current versions of MySQL can do transactions as well, but for reliability I'd personally opt for PostgreSQL; orders should be stored as securely as possible, automically, etc. And PostgreSQL may even be faster than MySQL for you in your application; MySQL no longer can claim being the fastest.
Interesting you should say that. Has PostgreSQL done something in the last 6 months or so to increase its speed? Every time I give it a try, my simple queries used for browsing and searching products prove many times slower than MySQL. It might mean 4 seconds instead of 1, but 4 seconds in Web time is a lot. 100% of my queries (except this order storage) are read only.
Maybe an index here or there would help? I do know that version 7.1 has introduced many speedups and that SourceForge has switched from MySQL to PostgreSQL because the latter was faster for their usage.
Maybe I'll try setting up a transaction capable table just for this portion. However, I can't see why the transaction would have been rolled back since it appears to have made it to the end on the first try.
So it would seem. Strange! -- Martijn Pieters | Software Engineer mailto:mj@zope.com | Zope Corporation http://www.zope.com/ | Creators of Zope http://www.zope.org/ ---------------------------------------------
participants (5)
-
Chris McDonough -
Martijn Pieters -
Ron Bickers -
Ron Bickers -
Ron Bickers