Hi
Is using v 7.27-dev
When Cancel/delet an account and
mark> Delete the account and make its content belong to the Anonym user.
Messages are:
The update has been performed. - (GREEN)
PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '17-0-111.111.111.111' for key 'PRIMARY': UPDATE {poll_vote} SET uid=:db_update_placeholder_0 WHERE (uid = :db_condition_placeholder_0) ; Array ( [:db_update_placeholder_0] => 0 [:db_condition_placeholder_0] => 211 ) in poll_user_cancel() (line 992 of /home/www/xx.xxxxx.xx/modules/poll/poll.module). - (RED)
But the account is not deleted
What to do?
Pafla
Comment | File | Size | Author |
---|---|---|---|
#53 | Deleting_users_can_result_in_a_duplicate_primary_key_in_poll_vote-2268673-53.patch | 602 bytes | danyg |
#50 | pollusercancel-2268673-50.patch | 623 bytes | andriyun |
#43 | pollusercancel-2268673-43.patch | 623 bytes | secretsayan |
#22 | pollmoduleprimarykeychange-2268673-22.patch | 544 bytes | secretsayan |
#21 | pollmoduleprimarykeychange-2268673-19.patch | 548 bytes | secretsayan |
Comments
Comment #1
secretsayan CreditAttribution: secretsayan commentedHello,
This is happening when we are deleting a user
1) who has voted for a poll and
2) Another user who had also voted in the same poll has been deleted and in both cases "Delete the account and make its content belong to the Anonymous user" option has been used.
3) Both the above mentioned users have similar host names
I am fixing it right now
Comment #2
Pafla CreditAttribution: Pafla commentedHi
When it is fixet, do i have to create a user for this and if what shall i call it?
Or is it build in Drupal
Do you write here when fixet?
Pafla
Comment #3
secretsayan CreditAttribution: secretsayan commentedHey I got it fixed. Hope it helps.
Check it out...
Comment #4
Pafla CreditAttribution: Pafla commentedTHX
Is it included in next drupal 7 ?
Comment #5
secretsayan CreditAttribution: secretsayan commentedYes , if its reviewed and tested by the community.
Comment #6
mgiffordSeems like a direct enough patch.
What's the best way to replicate this problem though?
Comment #7
secretsayan CreditAttribution: secretsayan commented@mgifford please check comment #1....
Comment #8
mgiffordI couldn't replicate it, sorry.
Comment #9
secretsayan CreditAttribution: secretsayan commented@mgifford replicate using the following steps-
0) Assume users John and Lucy has same host-names
1) User John has voted for a poll created by admin.
2) The admin now deletes John and uses "Delete the account and make its content belong to the Anonymous user" while deleting.
3) Now user Lucy also comes in and votes for the same poll.
4) The admin now deletes Lucy using the same "Delete the account and make its content belong to the Anonymous user" options.
And we get the following error-
PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '17-0-111.111.111.111' for key 'PRIMARY': UPDATE {poll_vote} SET uid=:db_update_placeholder_0 WHERE (uid = :db_condition_placeholder_0) ; Array ( [:db_update_placeholder_0] => 0 [:db_condition_placeholder_0] => 211 ) in poll_user_cancel() (line 992 of /home/www/xx.xxxxx.xx/modules/poll/poll.module). - (RED)
Comment #10
dcam CreditAttribution: dcam commentedThere are a few code-style problems with #3:
The docblock needs to be on one line. Something like "Add the timestamp field to the primary key." is enough information.
These comments need to have a period at the end.
After that, I think this is RTBC. It may need to have a test added, but I'll let the committer make that call.
Comment #11
dcam CreditAttribution: dcam commentedComment #12
secretsayan CreditAttribution: secretsayan commentedHere's the updated patch with changes according to #11 by dcam...
Comment #13
secretsayan CreditAttribution: secretsayan commentedComment #15
secretsayan CreditAttribution: secretsayan commentedComment #17
secretsayan CreditAttribution: secretsayan commentedComment #18
secretsayan CreditAttribution: secretsayan commentedComment #19
dcam CreditAttribution: dcam commentedThanks for continuing to work on this, @secretsayan!
I hate to nitpick, but that second, blank line in the docblock needs to be removed.
Comment #20
dcam CreditAttribution: dcam commentedComment #21
secretsayan CreditAttribution: secretsayan commentedComment #22
secretsayan CreditAttribution: secretsayan commentedThanks dcam! Any feedback will be appreciated..... Please go ahead if you have any. That's not at all a problem. Plz check this one out
Comment #23
secretsayan CreditAttribution: secretsayan commentedComment #27
secretsayan CreditAttribution: secretsayan commentedComment #28
secretsayan CreditAttribution: secretsayan commentedComment #30
dcam CreditAttribution: dcam commentedI'm setting this back to Needs Review for now. I know I said that the patch would probably be RTBC back in #10, but no one has done a final review of it yet after your most recent changes nor have I actually tested to make sure it fixes the problem. Then as the reviewer I should be the one to set the status (or anyone else doing the review).
I'll try to get back to this tomorrow, but I can't guarantee it. I'm very busy at work right now.
Comment #31
rcls CreditAttribution: rcls commentedI'm having the same problem.
I ran the patch #22, disabled \ enabled Poll, cleared cache, ran cron, but no effect. Running Drupal 7.31.
This error occurs when I attemp to clear out users who haven't logged in over 3 years and they seem to have some past content with Polls. I will attempt to go past this by disabling Poll.
*Successfully executed user deletion after disabling Polls.
Comment #32
secretsayan CreditAttribution: secretsayan commented@rcls you need to uninstall and install the poll module.
Comment #33
dcam CreditAttribution: dcam commented@rcls Or you could try to apply #22, run update.php, and report back as to whether it solves your problem. That would help us get this issue solved for everyone.
Comment #34
code-brighton CreditAttribution: code-brighton commentedHi
I'm also experiencing this issue. Here is the error from the logs when trying to delete a user that does have a similar username (e.g the oser I'm trying to delete is fred.smith_1 and there is another user called fred.smith one user is 3 years 1 month old, the other user is 2 years 9 months old):
PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '456024-0-' for key 'PRIMARY': UPDATE {poll_vote} SET uid=:db_update_placeholder_0 WHERE (uid = :db_condition_placeholder_0) ; Array ( [:db_update_placeholder_0] => 0 [:db_condition_placeholder_0] => 68653 ) in poll_user_cancel() (line 992 of C:\xampp\htdocs\projects\csl_portal_drupal_7\modules\poll\poll.module).
Has any progress been made on this issue? Is the patch scheduled to be committed to core? And if so when?
Comment #35
secretsayan CreditAttribution: secretsayan commented@code-brighton the patch can only be committed to the core if someone would review it and make it RTBC
Comment #36
quotesBro CreditAttribution: quotesBro commented1) I successfully applied patch #22 but I got 1 warning:
2) #22 didn't solve the issue for me (I applied #22 and ran update.php). I still get errors like
PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '187860-0--0' for key 'PRIMARY': UPDATE {poll_vote} SET uid=:db_update_placeholder_0 WHERE (uid = :db_condition_placeholder_0) ; Array ( [:db_update_placeholder_0] => 0 [:db_condition_placeholder_0] => 38025
(before applying the patch duplicate entries were like '176593-0-' - with only 1 zero)
I suppose it's because my site was upgraded from Drupal 6. In D6' poll module there was no 'timestamp' field in {poll_votes} table, so after upgrade to D7 timestamp fields of old poll votes got value '0'.
My solution of this issue: I changed PRIMARY to INDEX in {poll_vote}.
Comment #37
dcam CreditAttribution: dcam commentedThanks for your hard work, @secretsayan! Unfortunately, there are at least four problems with #22.
1. The first, minor issue is that there is a space in the "blank" line 217. That's what is creating the whitespace issue when applying the patch.
2. The function needs to be placed before the
since it is one of the 7.x "extra" updates.
3. The timestamp field also needs to be added to the indexes array of $schema['poll_vote'] in poll_schema(). Without it, the timestamp field won't be added to the primary index on install.
4. The most serious problem is that this patch won't solve the issue entirely. The problem will still exist if two deleted users happen to have voted in a poll at the same second. While this is kind of unlikely, #36 reports this exact problem with a site that has been upgraded from 6.x that has lots of timestamps = 0, which is where we're more likely to see this issue to continue popping up.
So, we need another solution.
Comment #38
dcam CreditAttribution: dcam commentedClosed #1490856: Cancel account impossible when user has voted for a poll with the same ip as a duplicate.
Comment #39
dcam CreditAttribution: dcam commentedThis could use some input from @David_Rothstein. It seems like we might have to add a unique ID column to fix this issue. As mentioned in #37, even extending the Primary index to all four of the table's columns won't solve the issue.
Comment #40
David_Rothstein CreditAttribution: David_Rothstein as a volunteer commentedHm, we could remove that primary key entirely (and replace it with an index on the same columns) but then we're violating the assumption that the module makes elsewhere that the hostname can be used to define an anonymous user's vote. For example, poll_cancel_form() (with submit handler poll_cancel()) is apparently available to anonymous users and when submitted will delete all anonymous votes from the hostname; it is essentially assuming there only can be one. If there ever is more than one, this would delete votes it shouldn't.
It's a tough call; maybe to fix this we should just remove the code in poll_user_cancel() that tries to anonymize the votes in the first place? It's not an ideal solution but it's not like the user IDs mean much anyway once the account has been deleted, so perhaps they can just stay in the database table (although there could be some privacy concerns with that?). I am also not sure what else might break if we did that.
Tests sound like a good idea here too.
Comment #41
dcam CreditAttribution: dcam commentedThis sounds like a good place to start, if someone wants to roll a new patch. My guess is that some test will blow up if the anonymizing is removed, most likely something to do with node/%nid/votes. Maybe not though.
Comment #42
secretsayan CreditAttribution: secretsayan commentedComment #43
secretsayan CreditAttribution: secretsayan at Wipro Technologies commentedOk then....I have created a patch...I have removed the poll_user_cancel function. This works fine.
Another option can be to update the hostname field after appending with the uid before setting it to '0'. But then any reverse proxy lookup would fail.
Please check this out and suggest.
Comment #44
GlenRanson CreditAttribution: GlenRanson commentedIs this patch going to be applied anytime soon?
It works for me
Comment #45
secretsayan CreditAttribution: secretsayan commentedComment #46
vpanicke CreditAttribution: vpanicke as a volunteer and at Wipro Technologies commentedComment #47
dcam CreditAttribution: dcam commentedWhy was this marked as being fixed? No patch has been applied. If you're going to change the status like that, at least explain why.
Comment #48
dcam CreditAttribution: dcam commentedRe-added the Needs Tests tag because none have been written yet.
Comment #49
secretsayan CreditAttribution: secretsayan commentedComment #50
andriyun CreditAttribution: andriyun as a volunteer and at Bellcom, Drupal Ukraine Community for Drupal Ukraine Community commented#43 patch reroll
Comment #52
scott_euser CreditAttribution: scott_euser as a volunteer and at Fat Beehive commented@andriyun I don't think this is the solution. There may be code referencing the user ID so leaving it as is with the user ID referencing a user that no longer exists could have knock-on effects.
I think the better solution is enforcing unique poll submissions with poll form validation but not requiring unique at the database table level (ie, altering the table structure).
Comment #53
danyg CreditAttribution: danyg at Cheppers commentedI've fixed the solution suggested in patch #22 what changes the database's primary key to use all 4 fields to determine the unique vote at all.
Update: If you are still facing errors on the mass update as I have done then you probably migrated your site from D6 to D7 after the poll module had introduced the timestamp column in poll_vote table. In this case, the timestamp field has been filled with the creation date of the poll node and equals in all related records. So, if you are in this case, this snippet will help. It adds 100 seconds to all equal timestamps and helps move on the user cancel issue.
Comment #54
izmeez CreditAttribution: izmeez commentedThere seem to be two different patches in this queue. One is reflected in comment #50 where hook_user_cancel() is removed and the second is in comment #53 that follows up from the patch in #22 to Add the timestamp field to the primary key.
Do both patches need to be combined into one patch? How do each or both of them relate to the issue summary and the steps to reproduce described in comment #9 ?
Comment #55
danyg CreditAttribution: danyg at Cheppers commented@izmeez: In my opinion and according to @scott_euser disabling the implementation of hook_user_cancel is not the right way. It simply turns the method off and ignores to work with the records which should be changed. The patch in comment #50 causes orphan records among the records of the poll_vote table.
So, I suggest to use the patch in comment #53.