Duplicate Key exception because Yahoo is returning an out of range date

This is the original exception that occurred on 2/3:

Found this exception in sedatacheck-morning.sh_07.log

2012-02-03 07:33:46.063 (1) [P7T1]: Exception [ Exception parsing /var/local/tsa/sedata/yahoo/tsa-client-report-2012-01-03-2012-02-02-U773-tsaCampaignSummaryByDay.xml.zip]:com.mysql.jdbc.exceptions.MySQLInt

egrityConstraintViolationException: Duplicate entry '2012-02-03-238599042' for key 'PRIMARY'

at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:931)

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2870)

at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1573)

at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665)

Reran

nohup $DIR/sedatacheck-morning.sh -d3 -T 2012-02-02 -U 773 2> /tmp/2012-02-03/sedatacheck-morning.sh_U_773_rerun.log &

Still seeing the same exception

2012-02-03 10:49:58.088 (3) [P3T1]: Deleting existing records for 2012-02-02 through 2012-02-02

2012-02-03 10:50:02.771 (1) [P3T1]: Exception [ Exception parsing /var/local/tsa/sedata/yahoo/tsa-client-report-2012-02-02-2012-02-02-U773-tsaCampaignSummaryByDay.xml.zip]:com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException: Duplicate entry '2012-02-03-238599042' for key 'PRIMARY'

at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:931)

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2870)

at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1573)

This is Caitlyn's explanation:

If the date is 2012-02-02 - 2012-02-02 I don't think it should be getting data for 2012-02-03 (which is what the duplicate key exception is coming from). I looked at staging.googleDailyCheck and staging.msnDailyCheckV5 and there are no rows for 2012-02-03. There is one for Yahoo:

mysql> SELECT * FROM staging.yahooDailyCheck where date='2012-02-03'

mysql> limit 5;

------------------------------------------------------------------------------------------------------------------------+

------------------------------------------------------------------------------------------------------------------------+

------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.05 sec)

You should try rerunning with -d4. You can also check the file it creates in /var/local/tsa/sedata/yahoo. Maybe this has always happened for Yahoo? (the TSA time zone is Pacific still, but the Yahoo time zone is probably Australia so maybe Yahoo does some date translation?)

But the reason we are seeing a duplicate entry is because the code is deleting the correct rows based on the date used in the app (2012-02-02) but Yahoo is returning a date outside of that range, so that row is not deleted.. therefore when the row from the report is being inserted, it recognizes there is already a row. It makes sense to get this exception on a rerun, but not on the first run of the day (run by the cronjob). Prior to it running via cron this morning at 7am Pacific, when's the last time this was run without the date being passed in (or with the end date of 2012-02-02 passed in)?