PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'rules_event_whitelist' for key 'PRIMARY

Please try whether clearing your caches and/or running update.php helps.

We have been seeing this issue and have been working on finding the solution for the last month, flushing caches works but the issue comes back for us at a seemingly random point in time.

Our research has yielded that clearing the cache and cache_bootstrap tables works. We flushed each cache table individually, nothing fixed, but both together and everything works. Dont think this is actually a Rules issue per se, but the issue happens when trying to send an email after adding a new comment.

Might be related to #1903498: PDOException: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock;

Just experienced this issue too. The weird thing is that variable_set() uses db_merge() which checks if the variable already exists in the table and dynamically choose insert / update.
However, the check is done in php code and not via the native INSERT ... ON DUPLICATE KEY UPDATE as provided by mysql (if you're using MySQL...).
Which means there could be a timing issue if a lot of stuff is going on simultaneously.
Locking could be an issue of course, I'm not totally sure but I think innodb locking just affects the write statement in db_merge() so the select to see if the variable exists will work right away, while the write may has to wait for another write transaction to be done. This means if the other write transaction was setting the same variable we're screwed because the return of the select statement isn't valid anymore after the lock is released.

I've switched to READ COMMITTED and things are behaving a lot better.
#1650930: Use READ COMMITTED by default for MySQL transactions

Oh, I probably should mention that I use redis as cache backend. This issue could be an argument to use cache_get() / cache_set() instead variable_get() / variable_set(). The whole code was introduced by #1555634: Improve performance of event cache by moving to a whitelist - the question whether to use the caches or variables to store this was raised but not really answered.
However, this issue here seems to be, in my case, just a symptom of #2189645: Avoid full cache clear whenever a rules component or reaction rule is edited (Patches available).

Confirming this issue. We've just had the same... mysterious!
"PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'rules_event_whitelist' for key 'PRIMARY': ..."

I get the same error in Rules 7.x-2.6. It seems to happen when orders are placed in Commerce while cron is running.

how did you solve the problem?

I concur with Paul B. I am using Rules 7.x-2.7 and get this error only when it's during a cron run.

Also getting a couple of these on cron run.

PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry rules_event_whitelist for key PRIMARY: INSERT INTO {variable} (name, value) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1);
 Array
(
    [:db_insert_placeholder_0] =>
 rules_event_whitelist
    [:db_insert_placeholder_1] =>
 a:13:{s:25:"commerce_cart_product_add";
i:0;
s:28:"commerce_cart_product_remove";
i:1;
s:37:"commerce_product_calculate_sell_price";
i:2;
s:26:"commerce_checkout_complete";
i:3;
s:21:"commerce_order_update";
i:4;
s:31:"commerce_shipping_collect_rates";
i:5;
s:40:"commerce_multicurrency_set_display_price";
i:6;
s:32:"commerce_shipping_calculate_rate";
i:7;
s:21:"commerce_order_insert";
i:8;
s:35:"commerce_payment_transaction_insert";
i:9;
s:35:"commerce_payment_order_paid_in_full";
i:10;
s:31:"commerce_coupon_applied_to_cart";
i:11;
s:22:"commerce_order_presave";
i:12;
}
    )
 in variable_set() (line 983 of /includes/bootstrap.inc).

'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'rules_event_whitelist' for key 'PRIMARY'' in includes/database/database.inc:2171
Stack trace:
#0 includes/database/database.inc(2171): PDOStatement->execute(Array)
#1 includes/database/database.inc(683): DatabaseStatementBase->execute(Array, Array)
#2 includes/database/mysql/query.inc(36): DatabaseConnection->query('INSERT INTO {va...', Array, Array)
#3 includes/database/query.inc(1621): InsertQuery_mysql->execute()
#4 includes/bootstrap.inc(983): MergeQuery->execute()
#5 sites/all/modules/contrib/rules/includes/rules.plugins.inc(812): variable_set('rules_event_whi...', Array)
#6 sites/all/modules/contrib/rules/rules.module(359): RulesEventSet::rebuildEventCache()
#7 sites/all/modules/contrib/rules/rules.module(999): rules_get_cache('event_twitter_a...')
#8 sites/all/modules/contrib/entity/includes/entity.controller.inc(359): rules_invoke_event('twitter_account...', Object(stdClass))
#9 sites/all/modules/contrib/entity/includes/entity.controller.inc(443): EntityAPIController->invoke('presave', Object(stdClass))
#10 sites/all/modules/contrib/entity/entity.module(289): EntityAPIController->save(Object(stdClass))
#11 sites/all/modules/contrib/twitter/twitter.inc(214): entity_save('twitter_account', Object(stdClass))
#12 sites/all/modules/contrib/twitter/twitter.module(223): twitter_account_save(Object(TwitterAccount))
#13 [internal function]: twitter_cron()
#14 sites/all/modules/contrib/elysia_cron/elysia_cron.module(1264): call_user_func_array('twitter_cron', Array)
#15 sites/all/modules/contrib/elysia_cron/elysia_cron.module(1229): elysia_cron_internal_execute_job('twitter_cron')
#16 sites/all/modules/contrib/elysia_cron/elysia_cron.module(1095): elysia_cron_internal_execute_channel('default', Array, false)
#17 sites/all/modules/contrib/elysia_cron/elysia_cron.module(153): elysia_cron_run(false)
#18 [internal function]: elysia_cron_cron()
#19 includes/module.inc(935): call_user_func_array('elysia_cron_cro...', Array)
#20 includes/common.inc(5265): module_invoke('elysia_cron', 'cron')
#21 /cron.php(25): drupal_cron_run()
#22 {main}

I just want to add my error notice. Maybe it helps to find the problem...

PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'rules_event_whitelist' for key 'PRIMARY': INSERT INTO {variable} (name, value) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1); Array ( [:db_insert_placeholder_0] => rules_event_whitelist [:db_insert_placeholder_1] => a:19:{s:31:"commerce_shipping_collect_rates";i:0;s:25:"commerce_cart_product_add";i:1;s:28:"commerce_cart_product_remove";i:2;s:37:"commerce_product_calculate_sell_price";i:3;s:26:"commerce_checkout_complete";i:4;s:43:"commerce_stock_check_add_to_cart_form_state";i:5;s:40:"commerce_stock_add_to_cart_check_product";i:6;s:37:"commerce_stock_check_product_checkout";i:7;s:21:"commerce_order_update";i:8;s:32:"commerce_shipping_calculate_rate";i:9;s:24:"commerce_payment_methods";i:10;s:23:"commerce_product_update";i:11;s:32:"commerce_ss_event_stock_adjusted";i:12;s:24:"commerce_coupon_validate";i:13;s:19:"commerce_fees_order";i:14;s:22:"commerce_coupon_redeem";i:15;s:11:"node_update";i:16;s:11:"node_insert";i:17;s:22:"commerce_order_presave";i:18;} ) in variable_set() (row 983 of /includes/bootstrap.inc).

PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'private://retour.fr__0.pdf' for key 'uri': INSERT INTO {file_managed} (uid, filename, uri, filemime, filesize, status, timestamp, origname, type) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3, :db_insert_placeholder_4, :db_insert_placeholder_5, :db_insert_placeholder_6, :db_insert_placeholder_7, :db_insert_placeholder_8); Array ( [:db_insert_placeholder_0] => 1 [:db_insert_placeholder_1] => retour.fr_.pdf [:db_insert_placeholder_2] => private://retour.fr__0.pdf [:db_insert_placeholder_3] => application/pdf [:db_insert_placeholder_4] => 77123 [:db_insert_placeholder_5] => 0 [:db_insert_placeholder_6] => 1416385718 [:db_insert_placeholder_7] => retour.fr_.pdf [:db_insert_placeholder_8] => document ) in drupal_write_record() (line 7231 of /var/www/html/arendus/includes/common.inc).

Cleaned cache and this gone

After some investigation I find out this error happens more often than I thought. In my case every day and always in combination with drupal commerce.

For example at:
domain.com/checkout/xxx/shipping
domain.com/admin/commerce/orders/xxx/
domain.com/checkout/xxx/payment/return/xxxx

Error message is always the same (see above).

@nikita.izotov your error has nothing to do with this issue.

This was already fixed by #2190775: Misuse of variables table with rules_event_whitelist

I got the same issue when I push my database to live server. This issue occur because some inline item entries are not remove from other tables so i write sql statement to remove the unwanted entries. It work for me.

SQL Statement:

SELECT @maxcount := MAX(line_item_id) FROM commerce_line_item;
DELETE FROM `field_data_commerce_unit_price` WHERE entity_id > @maxcount;
DELETE FROM `field_revision_commerce_unit_price` WHERE entity_id > @maxcount;
DELETE FROM `field_data_commerce_total` WHERE entity_id > @maxcount;
DELETE FROM `field_revision_commerce_total` WHERE entity_id > @maxcount;
DELETE FROM `field_data_commerce_product` WHERE entity_id > @maxcount
DELETE FROM `field_revision_commerce_product` WHERE entity_id > @maxcount;
DELETE FROM `field_data_commerce_display_path` WHERE entity_id > @maxcount;
DELETE FROM `field_revision_commerce_display_path` WHERE entity_id > @maxcount;
DELETE FROM `field_data_field_line_item_audience` WHERE entity_id > @maxcount;
DELETE FROM `field_revision_field_line_item_audience` WHERE entity_id > @maxcount;
DELETE FROM `field_data_field_line_item_item_type` WHERE entity_id > @maxcount;
DELETE FROM `field_revision_field_line_item_item_type` WHERE entity_id > @maxcount;

aloknarwaria , you've saved my day! Your post is extremely useful for me! Thanks a lot!