Posted by Gordon Burditt on 03/02/07 23:42

>1 - Gather all the information from the user other than the actual charging
>information.
>2 - Present the user with a summary of what he is about to purchase
>3 - Give him the choice of card (via PayPal) or by check.
>4 - If by check, go to a screen that he has to click the "Pay" button.
>5 - After the button is clicked, the data are entered into the database and
>emails are sent to the customer and the seller.
>6 - If by Paypal, he is taken to the PayPal screen where charge information
>is collected and processed. A successful return comes back to a thank you
>screen on my server where the data are entered into the database. PayPal
>handles sending the emails to the customer and the seller.
>
>My problem is this: What happens if there is a failure in connecting to the
>database after paying via PayPal? I use the same code (an included file)
>for the connection and the same code ( a different included file) for the
>insertion.

You shouldn't get a failure in the database. If it happens often,
get a new host, or upgrade your hardware. But just in case the
database server UPS has 2 minutes less life than the web server
UPS, log the transaction ANY and EVERY way you possibly can, for
later manual recovery. Log it to a file. Send yourself email.
Send it to your pager. Leave yourself voice mail. If possible,
use smoke signals and/or mental telepathy.

>Last night the seller tested a transaction and got:
>
>*Warning*: mysql_pconnect(): Can't connect to MySQL server on
>'dbet.valueweb.net' (115) in
>*/nfs/cust/0/43/11/711340/web/dbConnect.php* on line *6*
>Can't connect to MySQL server on 'dbet.valueweb.net' (115)

A seller shouldn't be getting warning messages from PHP (these can
be a security issue, plus it's unfriendly. Would you consider it
friendly for a human cashier, when I gave her a Susan B. Anthony
dollar, then told her it wasn't a quarter, to say "Huh?", look at
the "quarter", repeat "what the hell" a few times, mumbled something
about counterfeit money, and go catatonic for 5 minutes until I got
another three cashiers to carry her off to someplace she could lie
down? Yes, that really happened. Not only to me, either. At
Wal-mart you can get the police called on you for trying to spend
a $2 bill. Anyway, your web site is being equally unfriendly. ).

Also, use of pconnect() is an invitation to run the server out of
allowable connections while leaving a huge number of perfectly good
ones idle.

>Line 6 in dbConnect is:
>
>$dbLogin = mysql_pconnect($hostname, $username_db, $password_db) or
>die(mysql_error());

You should give an intelligible error to the CUSTOMER - like telling
them there was a problem and asking them to call customer service
and maybe warning them not to retry the order because of possible
duplicate billing. If you must spew technogeek babble to the
browser, tell the customer to print the page, have it available
when they call customer service, and ask the customer to spell it
to you when they call.

>He sent me an email and then when I tried I succeeded. Not connecting when
>paying by check is bad enough, tbut what about AFTER the customer has
>already paid? That would be terrible.

>So, my questions are:
>
>1 - Other than an intermittentcy on the server, are there any ideas why this
>would happen?

Fat-fingered admin leaned on the reset button or tripped over the power cord.
You have a problem with the number of allowable database connections (don't
use mysql_pconnect()).

>and much, much, more importantly:

>2 - What would you reccommend to bulletproof this so that the customer is
>protected and the transaction gets processed. One idea I have is rather
>than "die", that I send an email to the seller with the insertion query so
>that it can be manually inserted later.

Also log it in a file in case the web server doesn't stay up long enough
for that email to get off.

Sometimes it is worthwhile to wait a few seconds and retry the connection
ONCE or TWICE. Do not retry indefinitely. The user's browser will time
out waiting, and you'll just ensure that if the database gets overloaded,
the web server does also. If you put in retry logic, log even SUCCESSFUL
retries. If you're constantly having to retry, something is wrong, look
at it and fix it before the problem gets intolerable for the customers.

[Back to original message]