Odd duplicate entries in MYSQL issue...

[Maximilian]

So I have written an update to our ticketing system. Is is built with php and uses mysql as the database and apache as the webserver.

A tech can have a single ticket active at a time. When a tech clicks a "set active" button an ajax request is sent from the browser to php. This should (and mostly does) result in a single database entry with the ticketid, the techid and the startdate which is a unix timestamp. But sometimes there are duplicate entries i.e there will be 4 rows with the same techid, ticketid and startdate. As if the tech has started the ticket 2 times at the exact same millisecond. Sometimes there are 3 or more duplicates.

What could be going on here? I have been over the code extensively and im pretty sure there is nothing wrong with the logic here. I even pushed an update that disables the "set active" button after the tech pushes it and when the ajax request returns it is enabled again to prevent techs hammering the button if it lags etc. But there are still duplicates happening


tl;dr Given mysql, php5.4 and apache, how can 1 ajax request that 99% of the time results in 1 database row entry sometimes result in multiple duplicate rows. Primary key is auto incremented so it is the only thing not duplicated.

This is a browser based ticket system, 6 servers and a load balancer.

 

[DaveSimmons]

Narrow it down with the ever-reliable binary search.

Is it the (client / network) or the (PHP/MySQL) side?

I'd check the server raw access logs to see if a client is re-sending the request, perhaps because it didn't get an ack back from the server, perhaps because of a scripting bug in the client code.

If not, it must be in the PHP/MySQL

Add some local logging to text files by the PHP script, pull those logs

Once you know whether it's client/network or server, continue to narrow it down. It could be a network thing, client scripting, server scripting, server scripting SQL query bug, some sort of MySQL fault, ...?

 

[TempAcc99]

Log before and after Insert statement...with the inserted ticket id.

The only way to have multiple insertions with the disabled button is a loop or recursion. The loop could also be in the web page in JS sending the request multiple times.

And for the love of god add a unique constraint for columns that should be unique....That " catching the exception properly would solve the issue immediately albeit not knowing the cause.

And yeah, with MySQL you never know what it's up to...Especially since Oracle took over.

Last but not least make sure no one is trolling you and adding the entries on purpose.

 

[Cogman]

I want to echo what TempAcc99 is saying, add a Unique constraint/index to the table. It adds very little overhead and it will kick out whatever is doing the duplicate inserting. Then just watch your logs for a day and see what shakes out.

https://dev.mysql.com/doc/refman/5.7/en/create-index.html

 

[purbeast0]

I want to echo what TempAcc99 is saying, add a Unique constraint/index to the table. It adds very little overhead and it will kick out whatever is doing the duplicate inserting. Then just watch your logs for a day and see what shakes out.

https://dev.mysql.com/doc/refman/5.7/en/create-index.html

Yeah but that's not really fixing the issue, just covering it up.

 

[KIAman]

I'll bet your button is firing multiple click events. I would push an update that detaches the event to the button when pressed. You can get creative on how and when to attach the event again.

 

[Cogman]

Yeah but that's not really fixing the issue, just covering it up.

I disagree. The issue is getting duplicate values in the table. Adding a unique constraint will fix the problem while creating a new one "constraint violation" exceptions will be thrown wherever in the code is doing the duplicate inserting.

Yes, that will cause a different problem, but it certainly doesn't cover it up. If anything it does the opposite, it reveals exactly the source of the problem.

With the scant details that OP has given, there really isn't much more we can do here other than giving diagnostic tips on how to find the real problem.

 

[purbeast0]

I disagree. The issue is getting duplicate values in the table. Adding a unique constraint will fix the problem while creating a new one "constraint violation" exceptions will be thrown wherever in the code is doing the duplicate inserting.

Yes, that will cause a different problem, but it certainly doesn't cover it up. If anything it does the opposite, it reveals exactly the source of the problem.

With the scant details that OP has given, there really isn't much more we can do here other than giving diagnostic tips on how to find the real problem.

What I mean is, sure it won't be inserting the dupes into the DB, but it will still be making unnecessary calls to the backend (if that is the problem) or if there is a bug in the backend code trying to insert multiple times, that will still be happening.

Fixing that AND adding the constraint is what should be done.

Your solution doesn't reveal the source of the problem at all. We already know that dupes are being placed into the DB. All your solution will do is error out on the subsequent calls instead of put them into the DB. It won't tell him how or why they are getting there, which is what he's trying to figure out.

 

[Cogman]

Your solution doesn't reveal the source of the problem at all. We already know that dupes are being placed into the DB. All your solution will do is error out on the subsequent calls instead of put them into the DB. It won't tell him how or why they are getting there, which is what he's trying to figure out.

That is exactly what it will do.

Right now, the OP doesn't even know the PATH that is being taken for dups getting in the DB. Maybe it is all server side, maybe it is all client side, maybe some idiot is screwing with the DB and manually entering them. Who knows? But by having the constraint, so long as OP isn't throwing away stack traces, he will get an exact path that is being taken for a duplicate to be entered. From there, it is trivial to figure out "how is this being called" "Why is this being called" etc. Maybe it is the button being pushed twice, maybe it is some task cleanup process with a bug. Who knows? However, with a stack trace, you get exactly where to look.

Bonus points, if the OP has error logging everywhere, then when the server spits out a 500, he can get a stack trace on the client side as well that shows exactly where the problem is occurring.

I've had this problem before, and the first step was "add the constraint", the next was "What blew up" and from there it took very little effort to figure out exactly what was wrong.

 

[purbeast0]

That is exactly what it will do.

Right now, the OP doesn't even know the PATH that is being taken for dups getting in the DB. Maybe it is all server side, maybe it is all client side, maybe some idiot is screwing with the DB and manually entering them. Who knows? But by having the constraint, so long as OP isn't throwing away stack traces, he will get an exact path that is being taken for a duplicate to be entered. From there, it is trivial to figure out "how is this being called" "Why is this being called" etc. Maybe it is the button being pushed twice, maybe it is some task cleanup process with a bug. Who knows? However, with a stack trace, you get exactly where to look.

Bonus points, if the OP has error logging everywhere, then when the server spits out a 500, he can get a stack trace on the client side as well that shows exactly where the problem is occurring.

I've had this problem before, and the first step was "add the constraint", the next was "What blew up" and from there it took very little effort to figure out exactly what was wrong.

Okay I think you're just not getting it.

Either way, no matter what is causing it, he's getting duplicate entries into the database which means he's getting duplicate insert statements, which means no matter whether it's reason for happening is client or server side, it's telling the DB to insert the rows multiple times. It won't help him solve whether it is due to duplicate click events happening on the client or whether it's due to something triggering multiple insert statements on the server.

Your "solution" isn't going to let him know why it's happening regardless of where it's coming from. Either way, with your constraint or not, it's going to attempt to insert multiple times. With your "solution", it simply will error out when it tries the duplicate times instead of inserting it multiple times. It will give no reasons as to why it's happening.

As I said, sure your solution will make it so dupes aren't entered into the db, but the underlying issue still wont' be solved. It will still be trying to make multiple insert statements. I call that a bandaid solution.

I'm all ears if you can explain how it will let him know why it's happening. So far you haven't said why.

 

[Cogman]

Okay I think you're just not getting it.

I'm going to have to disagree with you here. It is you that is not "getting it". No worries, though, I'll walk you through it.

Either way, no matter what is causing it, he's getting duplicate entries into the database which means he's getting duplicate insert statements, which means no matter whether it's reason for happening is client or server side, it's telling the DB to insert the rows multiple times. It won't help him solve whether it is due to duplicate click events happening on the client or whether it's due to something triggering multiple insert statements on the server.

This is the problem. Given what the OP has given us, there is no way for anyone here to tell the OP exactly what is wrong. Further, the OP even says "Hey, I don't know what is going on here". So even he doesn't know why or how these duplicates are entering the database. You haven't provided a single bit of advice here, you've just criticized mine. And why haven't you given advice? Because you don't know what the problem is. Just like me, just like the OP.

Your "solution" isn't going to let him know why it's happening regardless of where it's coming from. Either way, with your constraint or not, it's going to attempt to insert multiple times. With your "solution", it simply will error out when it tries the duplicate times instead of inserting it multiple times. It will give no reasons as to why it's happening.

As I said, sure your solution will make it so dupes aren't entered into the db, but the underlying issue still wont' be solved. It will still be trying to make multiple insert statements. I call that a bandaid solution.

I'm all ears if you can explain how it will let him know why it's happening. So far you haven't said why.

Fine, here is the "Why". At this point. Nobody in this thread knows "Why" duplicates are getting into the database. The OP has spent time combing over the code trying to figure out exactly what is going wrong. But at this point, it is all guesses.

So what does an error buy? Depending on the language, a lot really. A well placed exception log will say

Hey, you idiot, you tried to insert a duplicate item at
TrackingSystemInsert:234
TrackingSystemService:24
TrackingSystemEndpoint:4

So how does this help? Because it tells you exactly the line number, where it came from, and how it got there. From here, I can see "Oh, hey, this came from 'TrackingSystemEndpoint:4, that means the user submitted it". Now, if your well behaving service returns a 500, as it should, and your client logs off those errors, as it should, then your logs will also have a message.

Hey, you idiot, the server barfed all over your request
TrackingSystemClient:54
TrackingSystemButton:48
UserClickEvent:896

And from there, it becomes clear that the issue originates from a user action linked to a button push (the line numbers will tell you EXACTLY which button was pushed that caused the issue.)

But even if the client logging isn't there (likely, it isn't) It is still somewhat of a simple matter to track down all the usages of the specific endpoint. The search for the problem is narrowed down significantly.

And that is how this leads to fixing the bug. Right now. The OP doesn't know what is going wrong. Adding an error (assuming good logging) will reveal exactly where the problem came from and how it got there. Just from the stack trace. Gravy is added if the OP tacks on some transients into the stack trace like "User x sent the request with Y data" into the stack trace, but for a problem like this, the stack trace will likely be enough.

Clear? Or are you still not getting it?

 

[Merad]

Yeah but that's not really fixing the issue, just covering it up.

There are two types of databases in the world: those with properly set up constraints, and those containing bad data.

 

[purbeast0]

Long quote

So basically you're agreeing that it doesn't solve the problem, which was my point. He still needs to figure out the underlying problem regardless of the constraint being added, which I agree should be on there.

 

[Maximilian]

You guys are awesome, thanks for the replies!

I highly suspected that the techs were pushing the button multiple times when it didn't react fast enough (fair enough I would do the same thing!). So yeah multiple button firing events. I pushed an update that disables the button after it is pushed and re enables when the ajax request returns. And I still got duplicates!! Long story short... I am a bonehead and it was because a lot of the techs still had the old javascript in their cache so could still mash the button. I pushed a second update that updated the javascript version my_filev1234.js etc. And that fixed it (probably!). After that there was only 1 duplicate on Friday. Previous days there were 15-20.

That 1 duplicate on Friday I cannot explain but ill check on Monday and see if there are any more. Fingers crossed that it is fixed.

Yeah adding a constraint to the database is something my boss mentioned as well. Ill be doing that if there has been any more after the weekend.

 

[Mr Evil]

...Yeah adding a constraint to the database is something my boss mentioned as well. Ill be doing that if there has been any more after the weekend.

You should do that anyway, or add some sort of server-side validation. In general, you should never trust the client.

 

[Cogman]

So basically you're agreeing that it doesn't solve the problem, which was my point. He still needs to figure out the underlying problem regardless of the constraint being added, which I agree should be on there.

And my point, which you disputed earlier, is that adding a constraint helps to identify exactly where the underlying problem is. Do you still dispute that or do you now agree with me that adding the constraint will help OP identify the problem?

 

[purbeast0]

And my point, which you disputed earlier, is that adding a constraint helps to identify exactly where the underlying problem is. Do you still dispute that or do you now agree with me that adding the constraint will help OP identify the problem?

It could help him sure, but it will do nothing more than putting a break point at the insertion point would do. You would get the same exact stack that the constraint error would spit out, so to me, that wouldn't be helpful really.

It would be more helpful to first see if the client was making multiple requests (which ended up being the case) vs. there being an error on the backend before even worrying about adding the constraint though.

Moral of the story - having client and server validation is worth while. And so are constraints.

 

[KIAman]

This solution looks very clean.

https://davidwalsh.name/javascript-debounce-function

This won't solve a tech continually hitting the button but it will ensure your SQL entries aren't exact duplicates.