update_record not working but not failing.

[peter]

I am using sqlite 3.

I have a ticket booking system. Transaction.status is set to 'pending'  in the database, when the transaction is initiated. 

When the paypal payment completes:

    transaction.status='booked'

    transaction.update_record()

emails are then sent out.

I have a strange example where the 'update_record' seems to have not worked, and yet execution of the routine was not interrupted, so the emails were still sent out, but status remains at pending.

Is it possible for 'update_record' to not work, and yet not fail and generate a ticket?

If so, is there any way to know this is happening to 'handle' it?

Thanks for any help on this.

Peter  

[Niphlod]

update_record is just an handy shortcut, so it shouldn't (under the hood it just calls update() on the right table with the right values), and I never heard of.

[Anthony]

We probably need more details. Is the code executed in a regular HTTP request or an external script? How/when are checking whether the update was successful?

[peter]

It is in a regular HTTP request. I actually somewhat later when someone turned up to a gig believing they had paid as they had a confirmation email, but the database still said pending.

There is no way the database record could get reset to 'pending'.

 Execution must have passed through the 

   transaction.status='booked'

    transaction.update_record

lines in order for the email to have been sent out. So the only explanation seems to be that the update_record failed to carry out its task. Clearly this is disconcerting. 

The ticketing system has been running for a couple of years now and I have not been aware of this happening before.

Peter

[Derek]

Well, I suppose you could start by running a profiler on the database to record activity, or you can log all sql statements and see what's going on there. 

[Anthony]

Can you show the full controller code?

[peter]

Here is the routine.

The emails both before and after get sent fine.. 

The software sends an email to spinjazz...@gmail.com so we have a record of bookings in case  of a database meltdown.

def paypal_complete_a():

    import datetime

    from paypal import PayPal

    paypal = PayPal()

    resp=paypal.GetExpressCheckoutDetails(request.vars.token)

    mail.send(to=['me_for_d...@gmail.com'],

        subject="resp",

        message='A' if not request else 'a '+str(resp)

        )

    transaction=db(db.transactions.token==request.vars.token).select().first()

    resp=paypal.DoExpressCheckoutPayment(request.vars.token, request.vars.PayerID, transaction.amount)

    if not resp['ACK']=='Success':

        redirect(URL('paypal_failure'))

    transaction.status='booked'

    transaction.update_record()

     event=db.events[transaction.event_id]

     get_tickets(event,transaction.quantity)

     scheduler.queue_task('save_sqlite')

     if transaction.quantity == '1':

            tickets=' ticket booked for '

     else:

            tickets=' tickets booked for '

     mail.send(to=['spinjazz...@gmail.com'],

                  subject=event.band,

                  message='{0}{1}{2} on {3} bought by {4} {5}'.format(transaction.quantity,tickets,event.band,show_date(event.date), auth.user.first_name,auth.user.last_name)

                  )

     mail.send(to=[auth.user.email],

                  subject=event.band,

                  message='{0} {1} you have {2}{3}{4} on {5}. Order number is {6}'.format(auth.user.first_name, auth.user.last_name, transaction.quantity,tickets,event.band,show_date(event.date),transaction.id)

                  ) 

     return

Thanks

Peter

[Ian Ryder]

I can't give specifics as I had to workaround it but I was having a similar issue last week with update_record. I'm using it everywhere without issue but one particular method wouldn't update and didn't show any error...if I see it again I'll post up and not work around it (I was having trouble posting to the board at the time and needed to move on with the code). 

I was 100% sure my code was fine and it was a solid fact the database (MySQL in my case) wasn't getting what I was trying to write. _lastsql didn't seem to have evidence of the update instruction either. Will see if I can recreate now and post up the code..

[peter]

Yes, I remember there was a thread about output_record always not working. Here it works most of the time, but very occasionally fails.

Peter

[Anthony]

And the problem only happens intermittently? If so, is it frequent enough that you could do some logging to figure out what is happening in the cases of failure? If you can do some logging, maybe check things like db._lastsql, transaction.update_record.colset, and transaction.update_record.id right before/after the update.

If you want to check for success, I suppose you could select the record again, though that will result in an extra database hit.

Anthony

On Monday, June 1, 2015 at 10:43:30 AM UTC-4, peter wrote:

Here is the routine.

The emails both before and after get sent fine.. 

The software sends an email to spinjazz...@gmail.com so we have a record of bookings in case  of a database meltdown.

def paypal_complete_a():

    import datetime

    from paypal import PayPal

    paypal = PayPal()

    resp=paypal.GetExpressCheckoutDetails(request.vars.token)

    mail.send(to=['me_for_debu...@gmail.com'],

        subject="resp",

        message='A' if not request else 'a '+str(resp)

        )

    transaction=db(db.transactions.token==request.vars.token).select().first()

    resp=paypal.DoExpressCheckoutPayment(request.vars.token, request.vars.PayerID, transaction.amount)

    if not resp['ACK']=='Success':

        redirect(URL('paypal_failure'))

    transaction.status='booked'

    transaction.update_record()

     event=db.events[transaction.event_id]

     get_tickets(event,transaction.quantity)

     scheduler.queue_task('save_sqlite')

     if transaction.quantity == '1':

            tickets=' ticket booked for '

     else:

            tickets=' tickets booked for '

     mail.send(to=['spinjazztickets...@gmail.com'],

[peter hutchinson]

I use the ipn_handling from Paypal to check the database. As I do not know why the update_record fails, I am not certain that several fails could not happen in a row. It only happens rarely so I cannot log it. If things go wrong punters turn up to the jazz club having paid and not having a seat, which is clearly unsatisfactory. 

What I have decided to do is to get the system to send me an email when the ipn_handling first detects a failure. In this way I can manually check that at least the problem gets dealt with properly by the update_record in the ipn_handler. 

I am still bemused that update_record can not know that it has failed and return a result reflecting this. This is standard practice in computing. Maybe this is just a weakness of sqlite I do not know. 

Peter

On Mon, Jun 1, 2015 at 7:06 PM, Anthony <abas...@gmail.com> wrote:

And the problem only happens intermittently? If so, is it frequent enough that you could do some logging to figure out what is happening in the cases of failure? If you can do some logging, maybe check things like db._lastsql, transaction.update_record.colset, and transaction.update_record.id right before/after the update.

If you want to check for success, I suppose you could select the record again, though that will result in an extra database hit.

Anthony

On Monday, June 1, 2015 at 10:43:30 AM UTC-4, peter wrote:

Here is the routine.

The emails both before and after get sent fine.. 

The software sends an email to spinjazz...@gmail.com so we have a record of bookings in case  of a database meltdown.

def paypal_complete_a():

    import datetime

    from paypal import PayPal

    paypal = PayPal()

    resp=paypal.GetExpressCheckoutDetails(request.vars.token)

    mail.send(to=['me_for_d...@gmail.com'],

        subject="resp",

        message='A' if not request else 'a '+str(resp)

        )

    transaction=db(db.transactions.token==request.vars.token).select().first()

    resp=paypal.DoExpressCheckoutPayment(request.vars.token, request.vars.PayerID, transaction.amount)

    if not resp['ACK']=='Success':

        redirect(URL('paypal_failure'))

    transaction.status='booked'

    transaction.update_record()

     event=db.events[transaction.event_id]

     get_tickets(event,transaction.quantity)

     scheduler.queue_task('save_sqlite')

     if transaction.quantity == '1':

            tickets=' ticket booked for '

     else:

            tickets=' tickets booked for '

     mail.send(to=['spinjazz...@gmail.com'],

                  subject=event.band,

                  message='{0}{1}{2} on {3} bought by {4} {5}'.format(transaction.quantity,tickets,event.band,show_date(event.date), auth.user.first_name,auth.user.last_name)

                  )

     mail.send(to=[auth.user.email],

                  subject=event.band,

                  message='{0} {1} you have {2}{3}{4} on {5}. Order number is {6}'.format(auth.user.first_name, auth.user.last_name, transaction.quantity,tickets,event.band,show_date(event.date),transaction.id)

                  ) 

     return

Thanks

Peter

--

Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to a topic in the Google Groups "web2py-users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/web2py/hOHmp5B190Q/unsubscribe.
To unsubscribe from this group and all its topics, send an email to web2py+un...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Anthony]

I am still bemused that update_record can not know that it has failed and return a result reflecting this. This is standard practice in computing. Maybe this is just a weakness of sqlite I do not know.

Well, we don't know where the failure is yet, so hard to say it is the fault of update_record. However, I agree that it might be useful if update_record provided some indication if no record gets updated (no reason to believe that would help in this particular case, though).

Anyway, maybe replace your code with:

db(db.transactions.token == request.vars.token).update(status='booked')

If the problem persists, then at least we know the problem is not specifically with update_record.

Anthony

[Dmitry Ermolaev]

db.commit()

пятница, 22 мая 2015 г., 12:51:00 UTC+3 пользователь peter написал:

[peter]

Yes Thanks Dmitry

 

Maybe that is the problem. I always forget about commit().

I will add this in. 

It will take me a while to know if it is the solution as the problem only occurs every few months. 

If I commit() does not sort it I will report back to the group eventually!

Peter

[Anthony]

If this is in the context of an HTTP request, then web2py automatically handles the commit, so calling db.commit() is unnecessary. If the transaction is failing to commit, that means that your code is throwing an exception sometime after the confirmation email gets sent to the user (which seems unlikely given that that is the last line of the controller -- though the problem could be in the view). Were there any error tickets associated with the cases where the database update failed?

Anyway, be careful calling db.commit(). When an exception occurs, any database operations are automatically rolled back, which is usually what you want. If you call db.commit() in the middle of a request, any database operations up to that point will be committed, regardless of any subsequent exceptions encountered in the code.

One option to consider is to use the scheduler to schedule the email to be sent to the user after the request has been processed. The email task could first confirm the database update and then send the email only in case the confirmation was successful.

Anthony

[peter]

Okay, useful to know it is not the absence of a commit. Each time the problem has occurred there has not been a ticket. 

The ipn messages from paypal are rather like a scheduled task. So I use them.They seem to be reliable. But although they passed through the 'update_record', they did crash later, so they effectively did not commit. So that explains to me why my second line of defence failed. I am still bemused why the controller presented above failed to carry out the update_record however.

Now that I can be more confident that my second line of defence  (the ipn_handler) will sort out any failed update_records I feel more secure the system will be reliable.

Thanks for your input everyone.

Those errors that do not get us to loose all credibility with our clients do help us to learn, with the aid of this forum.

Peter