Solved

Duplicate inserts on mysql

Posted on 2011-03-08
12
385 Views
Last Modified: 2012-06-27
Hi,

I am trying to solve an issue with my application but can't find the way:

I have the following mysql table:

entry_id       int(11)       Autoincrement                
camp_id       int(11)                       
date       datetime                
ip       varchar(15)

Now, many times records are saved at exactly the same time with the same details but the entry_id.

So the exact record could be stored several times, with the same camp_id, date and ip. However, I would like to avoid saving it more than once.

I tried INSERT IGNORE, but as the entry_id is different it does not work. Is there a way to use INSERT IGNORE but only checking the camp_id, date and ip?

Thanks!

0
Comment
Question by:ApOG
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
12 Comments
 
LVL 39

Expert Comment

by:Aaron Tomosky
ID: 35076988
You could do a If not exists (select * from table where whatever = whatever)
Begin
Insert into ...
End
0
 

Author Comment

by:ApOG
ID: 35077251
But does that kind of query executes entirely before another query is executed? or it is treated like a single query? Since otherwise another query may insert the data after the "internal" query and before the main one...
0
 
LVL 39

Expert Comment

by:Aaron Tomosky
ID: 35077397
Ok. So back to th original question. Insert ignore works off of unique indexes. So just define a unique index on the three columns you care about and insert ignore will handle the rest
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:ApOG
ID: 35077430
I don't think that will work since nor of the columns are unique (except the entry_id, which is an autoincrement), the three columns must match the new insert in order to be ignored...
0
 
LVL 39

Expert Comment

by:Aaron Tomosky
ID: 35078952
Well if you want those three other columns to be part of a unique index (across all 3, not individually unique) you can do that and insert ignore will work. It's the only way I can think of a solution in your situation. It's clean, simple, doesn't use alot of CPU, and will function in a high transaction environment.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35082927
>Now, many times records are saved at exactly the same time with the same details but the entry_id.

are you sure it's not about the web interface "submit/save" button clicked twice, and hence the underlying code being saved twice?

>I tried INSERT IGNORE, but as the entry_id is different it does not work
INSERT IGNORE will check the primary key, indeed.

so, 2 things:
* ensure that you have the submit button disabled on it's click event in the first place
* do an UPDATE instead of INSERT, and if the update returns 0 rows, do the INSERT (you might consider to put this logic into a stored procedure to avoid to clutter the front-end code)
0
 

Author Comment

by:ApOG
ID: 35083245
Thanks angellll!

The thing is this are visitors coming to a site and apparently sometimes by some reason the code that saves the pageview is loaded twice, even more sometimes. So there's no way to "disable" the double data insert. The only option is to do it via mysql.

The solution you suggest uses 2 queries, therefore, if the update is performed and return 0 rows and then the insert is made, another insert may occur after the update, so I'll have 2 inserts...

Is there some kind of UPDATE that if there's no record found to update, it inserts it? but with a single command..??
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 35084064
not if you use transaction control... however only with Innodb, not with MyISAM ..

the other option is to make the entry_id not a primary key nor a unique index, but just a plain index.
instead, create 1 single primary key/unique constraint/unique index  including all 3 columns
camp_id                        
date    
ip      

then, the insert ignore (or insert on duplicate key) shall work as you need.
you might then include a "count" column that would be 1 for the insert, and the ON DUPLICATE ... could update it to increment by 1 ... so you could account the "duplicate" views :)
0
 

Author Comment

by:ApOG
ID: 35084504
It sounds good!!

I have already setup 3 separate indexes on each column, plain indexes... Do I need to remove those indexes first?

(I use those indexes to get faster selects)
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35084561
> Do I need to remove those indexes first?
no, they can stay.
0
 

Author Closing Comment

by:ApOG
ID: 35085000
Thanks!
0
 
LVL 39

Expert Comment

by:Aaron Tomosky
ID: 35085039
Wow, I guess you just said it clearer or something because that is exactly the same solution I suggested.
0

Featured Post

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
updating the date data 12 40
How can i make performance tuning to my sql query? 6 64
category table 2 36
mysql query for sum() 3 47
Foreword This article was written many years ago, in the days when PHP supported the MySQL extension (http://php.net/manual/en/function.mysql-connect.php).  Today (http://php.net/manual/en/migration70.removed-exts-sapis.php) you would not use MySQL…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question