duplicate key entry in content editor on admax service redeploy

excerpt from the log file

2011-11-23 09:03:23.643 (4) [P8T1]: User optusclientaccount+56599@gmail.com in TSA MCC, connecting using master username/password and client email:optusclientaccount+56599@gmail.com

2011-11-23 09:03:23.643 (4) [P8T1]: client customer id = 71135

2011-11-23 09:03:23.643 (4) [P8T1]: seuserid[56599] username[null] password[null] clientemail[optusclientaccount+56599@gmail.com] customerID[71135]

2011-11-23 09:13:09.185 (2) [P8T1]: Data truncated for column 'maxCPC' at row 2 (1265)

status = "delete" should be "deleted"

subselects... join on adgroup tbl instead

select where status=delete and headline_struct is null? is that a left join?

2011-11-23 09:13:09.590 (4) [P8T1]: spike: SQL->[create temporary table `content_editor_google`.`tmp39` engine=innodb character set latin1 collate latin1_swedish_ci (select `content_editor_google`.`gce_textA

d`.`id` AS `id_gce`,`content_editor_google`.`tmp37`.`id` AS `id_struct`,`content_editor_google`.`gce_textAd`.`adGroupID` AS `adGroupID_gce`,`content_editor_google`.`tmp37`.`adGroupID` AS `adGroupID_struct`,`

content_editor_google`.`gce_textAd`.`searchEngineIdentifier` AS `searchEngineIdentifier_gce`,`content_editor_google`.`tmp37`.`searchEngineIdentifier` AS `searchEngineIdentifier_struct`,`content_editor_google

`.`gce_textAd`.`headline` AS `headline_gce`,`content_editor_google`.`tmp37`.`headline` AS `headline_struct`,`content_editor_google`.`gce_textAd`.`description1` AS `description1_gce`,`content_editor_google`.`

tmp37`.`description1` AS `description1_struct`,`content_editor_google`.`gce_textAd`.`description2` AS `description2_gce`,`content_editor_google`.`tmp37`.`description2` AS `description2_struct`,`content_edito

r_google`.`gce_textAd`.`displayURL` AS `displayURL_gce`,`content_editor_google`.`tmp37`.`displayURL` AS `displayURL_struct`,`content_editor_google`.`gce_textAd`.`destinationURL` AS `destinationURL_gce`,`cont

ent_editor_google`.`tmp37`.`destinationURL` AS `destinationURL_struct`,`content_editor_google`.`gce_textAd`.`exemptionRequest` AS `exemptionRequest_gce`,`content_editor_google`.`tmp37`.`exemptionRequest` AS

`exemptionRequest_struct`,`content_editor_google`.`gce_textAd`.`isDisapproved` AS `isDisapproved_gce`,`content_editor_google`.`tmp37`.`isDisapproved` AS `isDisapproved_struct`,`content_editor_google`.`gce_te

xtAd`.`status` AS `status_gce`,`content_editor_google`.`tmp37`.`status` AS `status_struct` from `content_editor_google`.`gce_textAd` left join `content_editor_google`.`tmp37` on (((`content_editor_google`.`g

ce_textAd`.`adGroupID`=`content_editor_google`.`tmp37`.`adGroupID`) and (`content_editor_google`.`gce_textAd`.`headline`=`content_editor_google`.`tmp37`.`headline`) and (`content_editor_google`.`gce_textAd`.

`description1`=`content_editor_google`.`tmp37`.`description1`) and (`content_editor_google`.`gce_textAd`.`description2`=`content_editor_google`.`tmp37`.`description2`) and (`content_editor_google`.`gce_textA

d`.`displayURL`=SUBSTRING_INDEX(SUBSTRING_INDEX(`content_editor_google`.`tmp37`.`displayURL`, 'http', -1),'//',-1)))) left join `content_editor_google`.`gce_adGroup` on ((`content_editor_google`.`gce_adGroup

`.`id`=`content_editor_google`.`gce_textAd`.`adGroupID`)) left join `content_editor_google`.`gce_campaign` on ((`content_editor_google`.`gce_campaign`.`id`=`content_editor_google`.`gce_adGroup`.`campaignID`)

) where ((`content_editor_google`.`gce_textAd`.`status`Unable to render embedded object: File (="Disabled") and (`content_editor_google`.`gce_adGroup`.`status`) not found.="Deleted") and (`content_editor_google`.`gce_campaign`.`searchEngineUserID`=56600) an

d (`content_editor_google`.`gce_campaign`.`id` in (210461,210460,210463,210462,210489,210488,210487,210486,210485,210484,210481,210480,210478,210479,210476,210477,210472,210473,210470,210471,210468,210469,21

0464,210465)))) union (select `content_editor_google`.`gce_textAd`.`id` AS `id_gce`,`content_editor_google`.`tmp38`.`id` AS `id_struct`,`content_editor_google`.`gce_textAd`.`adGroupID` AS `adGroupID_gce`,`co

ntent_editor_google`.`tmp38`.`adGroupID` AS `adGroupID_struct`,`content_editor_google`.`gce_textAd`.`searchEngineIdentifier` AS `searchEngineIdentifier_gce`,`content_editor_google`.`tmp38`.`searchEngineIdent

ifier` AS `searchEngineIdentifier_struct`,`content_editor_google`.`gce_textAd`.`headline` AS `headline_gce`,`content_editor_google`.`tmp38`.`headline` AS `headline_struct`,`content_editor_google`.`gce_textAd

`.`description1` AS `description1_gce`,`content_editor_google`.`tmp38`.`description1` AS `description1_struct`,`content_editor_google`.`gce_textAd`.`description2` AS `description2_gce`,`content_editor_google

`.`tmp38`.`description2` AS `description2_struct`,`content_editor_google`.`gce_textAd`.`displayURL` AS `displayURL_gce`,`content_editor_google`.`tmp38`.`displayURL` AS `displayURL_struct`,`content_editor_goo

gle`.`gce_textAd`.`destinationURL` AS `destinationURL_gce`,`content_editor_google`.`tmp38`.`destinationURL` AS `destinationURL_struct`,`content_editor_google`.`gce_textAd`.`exemptionRequest` AS `exemptionReq

uest_gce`,`content_editor_google`.`tmp38`.`exemptionRequest` AS `exemptionRequest_struct`,`content_editor_google`.`gce_textAd`.`isDisapproved` AS `isDisapproved_gce`,`content_editor_google`.`tmp38`.`isDisapp

roved` AS `isDisapproved_struct`,`content_editor_google`.`gce_textAd`.`status` AS `status_gce`,`content_editor_google`.`tmp38`.`status` AS `status_struct` from `content_editor_google`.`tmp38` left join `cont

ent_editor_google`.`gce_textAd` on (((`content_editor_google`.`gce_textAd`.`adGroupID`=`content_editor_google`.`tmp38`.`adGroupID`) and (`content_editor_google`.`gce_textAd`.`headline`=`content_editor_google

`.`tmp38`.`headline`) and (`content_editor_google`.`gce_textAd`.`description1`=`content_editor_google`.`tmp38`.`description1`) and (`content_editor_google`.`gce_textAd`.`description2`=`content_editor_google`

.`tmp38`.`description2`) and (`content_editor_google`.`gce_textAd`.`displayURL`=SUBSTRING_INDEX(SUBSTRING_INDEX(`content_editor_google`.`tmp38`.`displayURL`, 'http', -1),'//',-1)) and (`content_editor_google

`.`gce_textAd`.`status`=`content_editor_google`.`tmp38`.`status`))) where ((`content_editor_google`.`gce_textAd`.`headline` is null) and (`content_editor_google`.`tmp38`.`status`!="Disabled")))]

2011-11-23 09:13:09.602 (4) [P8T1]: spike: SQL->[start transaction]

2011-11-23 09:13:09.602 (4) [P8T1]: time=0.0

2011-11-23 09:13:09.602 (4) [P8T1]: Query executed in 0.000s

2011-11-23 09:13:09.602 (4) [P8T1]: spike: SQL->[create temporary table `content_editor_google`.`tmp40` engine=myisam character set latin1 collate latin1_swedish_ci select `content_editor_google`.`tmp39`.`id_gce` from `content_editor_google`.`tmp39` where ((adGroupID_gce not in (select adGroupID from `content_editor_google`.`gce_adGroupPendingUpdate` where (`status`="delete"))) and (`content_editor_google`.`tmp39`.`headline_struct` is null))]

2011-11-23 09:13:09.604 (4) [P8T1]: time=0.0020

2011-11-23 09:13:09.604 (4) [P8T1]: Query executed in 0.002s

2011-11-23 09:13:09.604 (4) [P8T1]: spike: SQL->[insert into `content_editor_google`.`gce_textAdPendingChange` (`content_editor_google`.`gce_textAdPendingChange`.`textAdID`,`content_editor_google`.`gce_textAdPendingChange`.`type`,`content_editor_google`.`gce_textAdPendingChange`.`tryCount`,`content_editor_google`.`gce_textAdPendingChange`.`isFailed`) select `tmp40`.`id_gce`,"delete" AS `type`,0 AS `tryCount`,"false" AS `isFailed` from `content_editor_google`.`tmp40` on duplicate key update `type`="delete",`tryCount`=0,`isFailed`="false"], genflags=no

2011-11-23 09:13:09.605 (4) [P8T1]: time=0.0010

2011-11-23 09:13:09.605 (4) [P8T1]: Query executed in 0.001s, 54 row(s) affected

2011-11-23 09:13:09.605 (4) [P8T1]: spike: SQL->[drop table `content_editor_google`.`tmp40`]

2011-11-23 09:13:09.606 (4) [P8T1]: time=0.0010

2011-11-23 09:13:09.606 (4) [P8T1]: Query executed in 0.001s

2011-11-23 09:13:09.606 (4) [P8T1]: spike: SQL->[insert into `gce_textAd` (`content_editor_google`.`gce_textAd`.`adGroupID`,`content_editor_google`.`gce_textAd`.`headline`,`content_editor_google`.`gce_textAd`.`description1`,`content_editor_google`.`gce_textAd`.`description2`,`content_editor_google`.`gce_textAd`.`displayURL`,`content_editor_google`.`gce_textAd`.`isDisapproved`,`content_editor_google`.`gce_textAd`.`destinationURL`,`content_editor_google`.`gce_textAd`.`status`,`content_editor_google`.`gce_textAd`.`exemptionRequest`) select `content_editor_google`.`tmp39`.`adGroupID_struct`,`content_editor_google`.`tmp39`.`headline_struct`,`content_editor_google`.`tmp39`.`description1_struct`,`content_editor_google`.`tmp39`.`description2_struct`,`content_editor_google`.`tmp39`.`displayURL_struct`,`content_editor_google`.`tmp39`.`isDisapproved_struct`,`content_editor_google`.`tmp39`.`destinationURL_struct`,`content_editor_google`.`tmp39`.`status_struct`,`content_editor_google`.`tmp39`.`exemptionRequest_struct` from `content_editor_google`.`tmp39` where ((`content_editor_google`.`tmp39`.`headline_gce` is null))], genflags=no

2011-11-23 09:13:09.607 (4) [P8T1]: time=0.0010

2011-11-23 09:13:09.607 (4) [P8T1]: Query executed in 0.001s, 18 row(s) affected

2011-11-23 09:13:09.607 (4) [P8T1]: spike: SQL->[insert into `gce_textAdPendingChange` (`content_editor_google`.`gce_textAdPendingChange`.`textAdID`,`content_editor_google`.`gce_textAdPendingChange`.`type`,`content_editor_google`.`gce_textAdPendingChange`.`tryCount`,`content_editor_google`.`gce_textAdPendingChange`.`isFailed`) select `content_editor_google`.`gce_textAd`.`id`,"add" AS `type`,0 AS `tryCount`,"false" AS `isFailed` from `content_editor_google`.`tmp39` join `gce_textAd` on (((`content_editor_google`.`tmp39`.`adGroupID_struct`=`content_editor_google`.`gce_textAd`.`adGroupID`) and (`content_editor_google`.`tmp39`.`headline_struct`=`content_editor_google`.`gce_textAd`.`headline`) and (`content_editor_google`.`tmp39`.`description1_struct`=`content_editor_google`.`gce_textAd`.`description1`) and (`content_editor_google`.`tmp39`.`description2_struct`=`content_editor_google`.`gce_textAd`.`description2`) and (`content_editor_google`.`tmp39`.`status_struct`=`content_editor_google`.`gce_textAd`.`status`) and (`content_editor_google`.`tmp39`.`destinationURL_struct`=`content_editor_google`.`gce_textAd`.`destinationURL`) and (`content_editor_google`.`tmp39`.`displayURL_struct`=`content_editor_google`.`gce_textAd`.`displayURL`) and (`content_editor_google`.`tmp39`.`isDisapproved_struct`=`content_editor_google`.`gce_textAd`.`isDisapproved`) and (`content_editor_google`.`tmp39`.`searchEngineIdentifier_gce` is null))) left join `gce_textAdPendingChange` on ((`content_editor_google`.`gce_textAdPendingChange`.`textAdID`=`content_editor_google`.`tmp39`.`id_struct`)) where ((`content_editor_google`.`tmp39`.`headline_gce` is null) and (`content_editor_google`.`gce_textAdPendingChange`.`textAdID` is null))], genflags=no

2011-11-23 09:13:09.610 (2) [P8T1]: SQLTransaction try # 1: non-deadlock exception, not retrying

2011-11-23 09:13:09.610 (4) [P8T1]: spike: SQL->[rollback]

2011-11-23 09:13:09.612 (4) [P8T1]: spike: SQL->[drop table `content_editor_google`.`tmp37`]

2011-11-23 09:13:09.612 (4) [P8T1]: spike: SQL->[drop table `content_editor_google`.`tmp38`]

2011-11-23 09:13:09.616 (4) [P8T1]: spike: SQL->[drop table `content_editor_google`.`tmp39`]

2011-11-23 09:13:09.621 (2) [P8T1]: Exception [SQL Exception running content editor, quitting]:com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException: Duplicate entry '8061567' 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:1388)

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

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

at com.thesearchagency.searchengines.google.contenteditor.GoogleContentEditorHelper.insertTextAdRequests(GoogleContentEditorHelper.java:2217)

at com.thesearchagency.searchengines.google.contenteditor.GoogleContentEditorHelper.createReplaceTextAdRequest(GoogleContentEditorHelper.java:2040)

at com.thesearchagency.searchengines.google.contenteditor.GoogleContentEditorHelper.access$600(GoogleContentEditorHelper.java:95)

at com.thesearchagency.searchengines.google.contenteditor.GoogleContentEditorHelper$ReplaceTextAdTransaction.databaseCalls(GoogleContentEditorHelper.java:2802)

at com.carldunham.jst.db.SQLTransaction.execute(SQLTransaction.java:68)

at com.thesearchagency.searchengines.google.contenteditor.GoogleContentEditorHelper.replaceTextAds(GoogleContentEditorHelper.java:1023)

at com.thesearchagency.searchengines.google.contenteditor.GoogleContentEditorHelper.replace(GoogleContentEditorHelper.java:257)

at com.thesearchagency.searchengines.google.contenteditor.GoogleContentEditor$SearchEngineUserWorker.handlePreRunActions(GoogleContentEditor.java:909)

at com.thesearchagency.searchengines.google.contenteditor.GoogleContentEditor$SearchEngineUserWorker.run(GoogleContentEditor.java:834)

at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:441)

at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)

at java.util.concurrent.FutureTask.run(FutureTask.java:138)

at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)

at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)

at java.lang.Thread.run(Thread.java:662)

2011-11-23 09:13:09.621 (3) [P8T1]: ... SEUser 56600 finished