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?
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?
UNIQUE isn't just for primary keys (though primary keys should be unique)
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.
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
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)
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?
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.
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?
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
This topic is now closed. New replies are no longer allowed.