clean up duplicates from multichoice answers in 4.x

Hi,

This is very strange. Let me investigate.

I can't reproduce this updating from a number of sites.

Were there any errors before/after that might have caused the update to interrupt?

I have the same error:

The following updates are pending:

long_answer module :
  7500 -   Normalize data storage.

multichoice module :
  7502 -   Normalize data storage.

Do you wish to run all pending updates? (y/n):y
Cannot add field quiz_multichoice_user_answers.result_answer_id: field already exists.                    [error]
Performed update: multichoice_update_7502                                                                 [ok]
'all' cache was cleared in /var/aegir/platforms/drupal-7.34#online-campus.dlc-ecsu.org                    [success]
Finished performing updates.                                                                              [ok]

It looks like the update failed earlier, and you are running it again.

I'd roll back the DB to be safe.

What's happening here is during the normalization updates, there are duplicates in the database, somehow.

The update runs, replace question_nid, question_vid, and result_id, with result_answer_id, then sets result_answer_id to a matching record. Sometimes though, it may find more than 1 which may have resulted from a user doubleclicking on a submit button.

To find these duplicates you can run this query (replace drupal_ with your prefix). You can do this on your existing site pre-upgrade, or the production site running alpha 8/9.

select * from drupal_quiz_multichoice_user_answers ua1 inner join drupal_quiz_multichoice_user_answers ua2 on(ua1.result_answer_id = ua2.result_answer_id) where ua1.id != ua2.id;

Once you find the duplicate ids you can delete them with this query. In our experience it is extremely rare, in 45,000 records there were 2 duplicates.

delete from drupal_quiz_multichoice_user_answers where id=12345;

Run the first query again, and verify it returns nothing.

NOW: If you are currently running in production, you need to do the following steps to fix the site and prevent that update from being run again. If you are upgrading to alpha8 or alpha9, and you performed the above steps on a pre-upgrade site, just run the upgrade again.

The last step of this is creating the unique index that failed and caused the update to also fail.

In devel/php, or drush php-eval (this will fail if you have not removed the duplicates):

db_add_unique_key('quiz_multichoice_user_answers', 'result_answer_id', array('result_answer_id'));

Then you must manually update multichoice's schema to the failed update, so that it does not run again.

update drupal_system set schema_version = 7502 where name='multichoice'

Thank you very much, that worked for me - and also the update to 10 was a smooth ride.

Thanks a lot for this great support. According to me, this is fixed.

Good to hear - thanks for the followup.

oops sorry I think it should be in the new comment section below

I am having exactly this problem with the duplicate entries.

The problem I have is that there are a lot of duplicates. Something like 900 of them. I can't delete those manually as you suggest. The query to find them all took 15 mins to complete.

I should add this is for a production site with over 170,000 entries in that table

Any ideas on how to address this?
Thanks

Hi,

Did you run the updates on production or are you just testing updates against production?

It should be fairly straightforward to fix the duplicates from 4.x so I think we should put some effort into doing it automatically as part of the update.

Hi

I ran the updates on a copy of production so I'm safe but I am committed to updating to Quiz 5 (the code is so much nicer).

Yes it makes sense to fix this in the update, I'm not sure if I'll be able to help but I'll take a look at the update code and try and understand it at least.

Many thanks

Hi

I had a look at the update code and it makes sense but I'm not sure how to go about fixing the issue. Could you let me know if you have plans to look at this anytime soon or are you looking for someone else to work on it?

If you don't have time perhaps you could give me a steer as to how to go about fixing this?

Thanks

Just another thought

Presumably If I can't perform the upgrade and I was willing to loose all the users quiz data, I could truncate the quiz_multichoice_user_answers table.

If I do that I guess I'll need to truncate these tables too

quiz_multichoice_user_answers
quiz_node_results_answers
quiz_node_results

Can you confirm this?
Thanks

Truncating quiz_multichoice_user_answers will fix the update, but you'll lose selected multichoice answers.

I believe there is a way to find the duplicates and delete one of them programmatically but let me see what we did in the past as the issue did come up.

Let me know if this works

Assuming this is MySQL, on the source site, before updating (also make a backup):

delete from quiz_multichoice_user_answers where id in (select id from (select id from quiz_multichoice_user_answers ua group by result_id, question_nid, question_vid having count(*) > 1) as q);

This will delete a random duplicate. In my investigation the duplicates are always the same answer, just recorded twice. You may have to run it twice if there are triples. It should return 0 rows affected if there are no more.

Then try the 7.x-5.x update.

Hi djdevin

Thanks so much for that query. I was diverted onto something else and only just got around to running it. I can confirm that it works and I can now perform the update. Going to try it on production next week. I had to run it 15 times to remove all the duplicates!

Again many thanks for you help on this and other issues you have responded to.
Tim

Fixed!