Is mySQL to blame for these duplicate posts?

[realmaverick]

One of our sites has an issue, where uploads, comments and posts will randomly duplicate during posting. As I say it's random, usually uploads, posts and comments will work fine.

We have a separate SQL and Web server running Litespeed. Both are managed servers.

The issue started very suddenly after some settings changed on both servers and both were rebooted. The next morning the duplicate issue was apparent. The host reverted all changes. However the issue didn't go away.

Our host focused on the web server and at first blamed Litespeed, however LS was reinstalled and LS engineers re-configured the installation and the problem persisted, PHP and Apache were also reinstalled. Settings have been tweaked over and over for the past 6 weeks but the issue remains.

Eventually the entire web server was swapped but didn't resolve the issue. SQL settings were also tweaked to death and finally tried a stock install but the issue remains.

When duplicates of the uploads occur, the browser says sending, waiting and then after a while it says sending again. When this happens, the upload posts several times rather than once. The upload usually consists of title, description, screenshots and another file of some kind. Though the issue can occur when simply uploading a small image. During successful uploads, the browser says sending, waiting and then I'm taken to my new upload.

Our comments system uses Ajax, on occasions I've posted a comment and the ajax loader pops up to tell me that my comment is being posted, but it just hangs. I've waited for up to 5 minutes. When I refresh the page, the comment HAD been posted and had a time stamp from 4 minutes ago. It appears the SQL server hadn't send the response back?

The host wants to blame our code, however no changes have been made to the comments or posting scripts. No changes were made to the upload script around the time the issue started to happen, however we reverted the code to an earlier version and eventually rewrote the code but it had no effect.

The only thing left that I can think of, is the hardware on the SQL server. HDD, ram or some kind of comms port?

If you have an information that maybe able to help us diagnose the issue, I'd be eternally greatful. It's been 6 exhausting weeks and it's also upsetting our members.

[rocknbil]

You're not alone. But I'd tend to agree, it's in the coding - but not in the way you'd expect. It may be timing out and restarting the process, or some other weirdness. I really double mySQL is to blame, I think it's happening en route.

Case 1: for quite some time right here on WebmasterWorld I had terrible trouble with double posts, and I am not the double-clicking type. A couple of things happened around the same time, some updates were done on this site, AND I move from my satellite connection to a DSL connection. Haven't seen it since.

My best guess: Satellite was a proxy server, and has tons of issues. It was probably counting the first request as a timeout, then resuming it somehow. Know that this is a guess and has zero basis in reality.

Case 2: One ecommerce site we have does this from time to time. The strange thing is there are several error traps in place to capture and prevent a double-click. On the merchant gateway side, there are also tools in place, we have seen the responses: "declined, duplicate transaction attempt." But when it happens, we get duplicate emails, duplicate auths in the merchant account, but the programming only puts a single entry into the database as an order (it's likely doing it twice, updating the second time.) Our logs show duplicate orders too, with the same order #.

Every time it happens, I spend hours, days trying to duplicate the problem, debug it, and it remains unsolved.

I have other cases of the ghost double-submit, so don't have a one-off answer. I would "guess" that it has something to do with a timeout, or something, but again, a guess.

If you don't have it already, organize a debugging scheme. Initialize the debug variable at the top of the script, at every step/function in the problem process, concatenate to it, then just before you print a response, write it to a log. This is where I'd start.

Another place to look: as you're duplicating the problem, have your error_log handy, as soon as it occurs, tail the error log. It may tell you something different than your script log will.

[jatar_k]

have you tried isolating portions of the process?

sounds like a bottleneck, they are brutally hard to find. Mostly I break the process down into the snallest parts possible, isolate various technologies, separating php from mysql and maybe ajax in this case, see how the communications are passing back and forth and see who or what is dropping the ball. Start with the smallest piece and rebuild it until the problem starts, hopefully removing some code and simplifying the problem.

Maybe, only maybe, if you can find the part that actually dies you could take a shot at the why of it.

I also doubt it's the sql, my first guess is the ajax loader is running into an issue which stops it from receiving the response but that is merely a guess.

If you can replicate it then you have a valid shot at finding it though, that's encouraging.

as rocknbil says, log everything and watch