Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a system that sends emails for me and my clients every day in the morning. This process is triggered automatically through access to my website, via an iframe.

Often, I receive the same email twice.

The simple process I created on a MySQL database to control this is as follows:

  • run this query:

    SELECT nome, email
    FROM clientes
    WHERE DATE_FORMAT(lastsend, '%d/%m/%y') <> DATE_FORMAT(now(), '%d/%m/%y')  
    
  • send emails

  • UPDATE clientes SET lastsend = Now();

I need a way to lock this down to prevent the same emails from being sent multiple times on the same day.

share|improve this question
add comment

1 Answer

This should be your main two queries

STEP01 : Run this query

SELECT nome,email FROM clientes
WHERE lastsend < (DATE(NOW()) + INTERVAL 0 SECOND);

STEP02 : Run your process to send out emails

STEP03 : Run this query

UPDATE clientes SET lastsend = NOW()
WHERE lastsend < (DATE(NOW()) + INTERVAL 0 SECOND);

The expression (DATE(NOW()) + INTERVAL 0 SECOND); is always midnight today.

Look it over

  • STEP01 will check for lastsend to make sure it is before midnight today
  • STEP03 changes the lastsend to the current date and time for the emails that have not been sent out today
  • You can run STEP01 - STEP03 multiple times in the same and only new emails will go out

You should also make sure that lastsend is indexed. If it is not, then run this

ALTER TABLE clientes ADD INDEX (lastsend);

This will help speed up the query in STEP01

As for locking the table during this process, I would need to see the table structure. I have an idea concerning using the DB Connection ID. It goes something like this:

Add a column called sender to clientes table and index it

ALTER TABLE clientes ADD COLUMN sender TINYINT DEFAULT 0;

Once you create that column and index it as I specified, you can perform the following:

STEP01 : Mark everything before midnight in your DB Connection as midnight

UPDATE clientes SET
    sender   = CONNECTION_ID(),
    lastsend = (DATE(NOW()) + INTERVAL 0 SECOND)
WHERE
    lastsend < (DATE(NOW()) + INTERVAL 0 SECOND)
;

STEP02 : Run your process to send out emails where sender=CONNECTION_ID() and lastsend = (DATE(NOW()) + INTERVAL 0 SECOND)

STEP03 : Mark your stuff as sent

UPDATE clientes SET
    lastsend = NOW()
WHERE
    sender   = CONNECTION_ID() AND
    lastsend = (DATE(NOW()) + INTERVAL 0 SECOND)
;

This will restrict which DB Connection send which batch of email

Give it a Try !!!

share|improve this answer
    
thanks a lot! i will try and post the results here but my problem is too many access for seconds for my "send page" and so the email is sended too many times until update my "clientes" table. Thanks! –  Adriano Oliveira Sep 7 '12 at 21:31
    
Excuse me. I had one typo. I just updated my STEP02 from lastsend < (DATE(NOW()) + INTERVAL 0 SECOND) to lastsend = (DATE(NOW()) + INTERVAL 0 SECOND). Please use this. –  RolandoMySQLDBA Sep 7 '12 at 21:33
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.