SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Duplicated inserts for some reason

    I have some code on my website that sends out an email to a registered user when they have a newly generated lead. It has been working great for years. Recently I looked in the database and discovered that some of the emails are being duplicated several times. Sometimes 6-8 rows are being generated when there should only be one. This means that when my code processes the rows, and sends out the emails, that my registered users are getting spammed by me. What makes this hard to pinpoint is the fact that not every message gets duplicated.

    Could this have anything to do with the fact that my ISP, where the website and database is hosted, has had confirmed problems with other mySQL users that share my same CPU?

    Thanks!
    Convert your dollars into silver coins. www.convert2silver.com

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by busboy View Post
    Could this have anything to do with ...
    yup, it could

    did you know that with a UNIQUE index on the appropriate columns, you can prevent these duplicate rows from ever being inserted?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    I solve practical problems. bronze trophy
    Michael Morris's Avatar
    Join Date
    Jan 2008
    Location
    Knoxville TN
    Posts
    2,033
    Mentioned
    65 Post(s)
    Tagged
    0 Thread(s)
    UNIQUE isn't just for primary keys (though primary keys should be unique)

  4. #4
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,182
    Mentioned
    66 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Michael Morris View Post
    UNIQUE isn't just for primary keys (though primary keys should be unique)
    PKs are always unique

  5. #5
    I solve practical problems. bronze trophy
    Michael Morris's Avatar
    Join Date
    Jan 2008
    Location
    Knoxville TN
    Posts
    2,033
    Mentioned
    65 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by K. Wolfe View Post
    PKs are always unique
    In a valid database, yes. I said should be because I've ran across my fair share of databases put together by tyros where the primary keys of a table weren't unique - usually because they weren't properly declared as primary keys to begin with.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Michael Morris View Post
    ... usually because they weren't properly declared as primary keys to begin with.
    and that would be the only way that could happen

    by the way, saying "UNIQUE isn't just for primary keys" is a bit misleading -- you would ~not~ want someone adding UNIQUE to a primary key, would you
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Can someone explain exactly what is happening since I don't have a UNIQUE index on the appropriate columns? How are these duplicates added only sometimes?
    Convert your dollars into silver coins. www.convert2silver.com

  8. #8
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,182
    Mentioned
    66 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by busboy View Post
    Can someone explain exactly what is happening since I don't have a UNIQUE index on the appropriate columns? How are these duplicates added only sometimes?
    We'd have to see how you are inserting the data. he problem originally lies there. What we are saying though is that it could / can be avoided provided a proper UNIQUE index or primary key (preferably primary key)

  9. #9
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Here is the query that is inserting the data:

    INSERT INTO notifications (dateAdded, priority, type, sender, recipient, subject, uID, message, dateScheduled)

    describe notifications produces:

    nID - int
    uID - int
    dateAdded - datetime
    type - varchar
    priority - varchar
    sender - varchar
    recipient - varchar
    subject - varchar
    message - text
    dateScheduled - datetime
    attempts - tinyint
    error - varchar
    dateSent - dateTime

    Does this help?

    Thanks!
    Convert your dollars into silver coins. www.convert2silver.com

  10. #10
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    So I'm guessing that adding a unique index on the uID and dateAdded columns that this should prevent duplicate entries? No one has answered yet as to what actually causes duplicates to happen in the first place.

    Thank you!
    Convert your dollars into silver coins. www.convert2silver.com

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by busboy View Post
    So I'm guessing that adding a unique index on the uID and dateAdded columns that this should prevent duplicate entries?
    depends on whether or not the php routine "accidentally" does another insert using a later datetime value, in which case the unique index wouldn't stop a duplicate row -- well, not exactly a duplicate, since the datetime value would be different, but you know what i mean, right?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,182
    Mentioned
    66 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by busboy View Post
    Here is the query that is inserting the data:

    INSERT INTO notifications (dateAdded, priority, type, sender, recipient, subject, uID, message, dateScheduled)

    describe notifications produces:

    nID - int
    uID - int
    dateAdded - datetime
    type - varchar
    priority - varchar
    sender - varchar
    recipient - varchar
    subject - varchar
    message - text
    dateScheduled - datetime
    attempts - tinyint
    error - varchar
    dateSent - dateTime

    Does this help?

    Thanks!
    We need one step further back, where and how are you getting the data? Your getting dupes because of the step right before this (dupes in your original data). Also, give us an explanation of the non obvious columns so that we can possibly help you build a PK


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •