admax: admaxMCPCHistory table runs out of IDs

the id column and primary key is not necessary because there are no foreign keys in other tables that reference it.

I propose we drop the id colum and primary key on all admax*History tables that do not have foreign key references and update the code not to write to that column.

== the fix ==

CREATE TABLE `admaxMCPCHistory_20110526` ( `id` int(11) NOT NULL AUTO_INCREMENT, `accountID` smallint(5) unsigned NOT NULL DEFAULT '0', `level` enum('account','bidTarget','distribution','searchEngineAccount','searchEngineGroup') NOT NULL DEFAULT 'bidTarget', `bidTargetID` int(11) NOT NULL DEFAULT '0', `time` date NOT NULL DEFAULT '0000-00-00', `type` enum('unknown','cr','rpc','ctr','cpi') CHARACTER SET latin1 NOT NULL DEFAULT 'cr', `R` double NOT NULL DEFAULT '0', `h` double NOT NULL DEFAULT '0', `revenue` double NOT NULL DEFAULT '0', `cogs` double NOT NULL DEFAULT '0', `cost` double NOT NULL DEFAULT '0', `wDelta2` double NOT NULL DEFAULT '0', `dayOfWeek` enum('monday','tuesday','wednesday','thursday','friday','saturday','sunday','all') CHARACTER SET latin1 NOT NULL DEFAULT 'all', PRIMARY KEY (`id`), UNIQUE KEY `MCPCElement` (`accountID`,`type`,`dayOfWeek`,`level`,`bidTargetID`,`time`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

nohup mysql -u spike -ptar63t -h acctdb-05-write.dc1bos.thesearchagency.com st-tracker -e "insert into admaxMCPCHistory_20110526 (accountID, level, bidTargetID, time, type, R, h, revenue, cogs, cost, wDelta2, dayOfWeek) select accountID, level, bidTargetID, time, type, R, h, revenue, cogs, cost, wDelta2, dayOfWeek from admaxMCPCHistory where time > '2011-01-26'" > /tmp/mcpcfix.out &

rename table admaxMCPCHistory to admaxMCPCHistory_backup_20110526

rename table admaxMCPCHistory_20110526 to admaxMCPCHistory

== The rerun ==

Now admax must be rerun for the accounts that failed: (Below, repalce "I" with Caitlyn Duggan - who was kind enough to provide these instructions)

I had to rerun admax a month ago. To figure out the accounts and dates to run for this is what I did (the log that had exceptions was in /tmp/2011-08-30 so the 'yesterday' was 2011-08-29):

Reran AdMaxSummarizer using:

nohup $DIR/admax.sh -d3 -T 2011-08-29 -a 477,480,485,523,527,530,536,546,548,564,580,582,587,597,620,621,641,643,645,649,650,652,653,672,673,674,675,676,677,679,682,688,689,694,695,697,706,713,714,716,719,723,732,733,734,743,744,745,747,752,756,759,760,761,763,764,765,766,767,768,771,772,775,776,777,778,779,780,781,783,784,787,788,793,794,795,796,797,798,802,803,805,806,809,810,811,812,813,814,815,816,818,820,822,823,824,826,827,828,829,830,831,832,833,834,835,836,837,838,840,841,843,845 2> /tmp/2011-08-30/admax.sh_rerun_2011-08-29.log &

I got this list of accounts by retrieving all the accounts that completed and then searching tsacommon.accounts for the remaining active accounts.

Some accounts have special date rules which occur when the date param (-T) is NOT passed in (usually this means default to yesterday). I checked the log from 2011-08-29 and got all the accounts that have special date rules and compared against the list of remaining active accounts and none of them matched. Therefore I passed in -T 2011-08-29 for all the remaining accounts.

The comment in the log file to see the special date rules is, for example: "Adjusting date by 2 day(s) for account [804]. Data date is now Fri Aug 26 00:00:00 PDT 2011"

Note that if I had found some accounts with special date rules, I would have had to run those accounts in a different instance of admax.sh with the appropriate date.

== the exception ==

2011-05-26 00:05:38.399 (1) [P2T3]: Exception [Error saving record]:com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException: Duplicate entry '2147483645' 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)

at com.mysql.jdbc.Connection.execSQL(Connection.java:3170)

at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1316)

at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1235)

at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1381)

at com.carldunham.jst.db.Database.executeUpdate(Database.java:401)

at com.carldunham.jst.db.BaseDatabaseTable.insert(BaseDatabaseTable.java:345)

at com.carldunham.jst.db.BaseDatabaseTableRow.save(BaseDatabaseTableRow.java:490)

at com.carldunham.jst.db.BaseDatabaseTableRow.save(BaseDatabaseTableRow.java:424)

at com.thesearchagency.admax.algorithms.AdMaxMCPCAlgorithm.applyMCPC(AdMaxMCPCAlgorithm.java:850)

at com.thesearchagency.admax.algorithms.AdMaxMCPCAlgorithm.applyMCPC(AdMaxMCPCAlgorithm.java:739)

at com.thesearchagency.admax.algorithms.AdMaxMCPCAlgorithm.applyAlgorithm(AdMaxMCPCAlgorithm.java:475)

at com.thesearchagency.admax.AdMaxSummarizer$AccountWorker.run(AdMaxSummarizer.java:420)

at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)

at java.util.concurrent.FutureTask$Sync.innerRun(Unknown Source)

at java.util.concurrent.FutureTask.run(Unknown Source)

at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source)

at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)

at java.lang.Thread.run(Unknown Source)