Installing and upgrading help

Ok,
I'm not an expert with databases but I've asked my colleague to answer your question.

Well, there is not a direct way to say 'next value of the sequence will be 35'.
So, the best solution we could find was to increase the sequence until the value you need.
The good new is that you can increase the sequence 20 by 20, for example, in order to 1 by 1.

So, these are the steps you can follow for increasing the sequence:

1. Find the last ID value in the 'mdl_question_attempt_steps' table.
      SELECT MAX(ID) FROM mdl_question_attempt_steps;
   Imagine that it is 3520.

2. Find the last value in the sequence 'mdl_quesattestep_id_seq'. The instruction you can use is:
      SELECT LAST_NUMBER FROM USER_SEQUENCES WHERE SEQUENCE_NAME = mdl_quesattestep_id_seq';
   Imagine that it is 3515.

3. In this case you would need to increment the sequence in 5. To be really sure, we are going to increment the sequence in 6.
   So, now, modify the increment of the sequence in 6 with the intruction:
      alter sequence mdl_quesattestep_id_seq increment by 6;

4. Increment the sequence with the instruction:
      select mdl_quesattestep_id_seq.nextval from dual;
   Now, the sequence next value is 6 times bigger.

5. Restore the increment of the sequence to 1:
      alter sequence mdl_quesattestep_id_seq increment by 1;


And, that's all.
Remember you must do something similar with the 'mdl_question_attempt_steps_data' table and the 'mdl_quesattestepdata_id_seq' sequence, too.

I've tried upgrading from 2.0.4+ to 2.1.10 and then to 2.2.10 and also directly from 2.0.4+ to 2.2.10 (on MSSQL 2008 R2). Both attempts produce the same results - the upgrade crashes with the "Cannot insert duplicate key row in object 'dbo.mdl_question_attempt_steps' with unique index 'mdl_quesattestep_queseq_uix'" error. And this is AFTER I have already DELETED all the duplicate records returned by the original query that Tim suggested earlier in this thread (SELECT question,  attempt,  seq_number, COUNT (seq_number)AS NumOccurrences FROM mdl_question_states GROUP BY  question,  attempt, seq_number HAVING (COUNT(seq_number) > 1) ) .

If I now run that SELECT query I get zero results implying, I presume, that there should be no more "duplicates" in mdl_question_states. But if there are supposedly no more duplicates in that table why does the above duplicate row error still occur?

The ONLY way I've been able to upgrade from version 2.0.4+ to version 2.2.10 without getting this error is to DISABLE the mdl_quesattestep_queseq_uix index on the new mdl_question_attempt_steps table that is created in 2.1 or 2.2.

But then when I try to rebuild that mdl_quesattestep_queseq_uix index after the 2.2.10 (or 2.3 or 2.5) upgrade is complete I get a "Rebuild failed for Index mdl_quesattestep_queseq_uix" error:

"The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.mdl_question_attempt_steps' and the index name 'mdl_quesattestep_queseq_uix'. The duplicate key value is (0, 0)."

Thus disabling that index, just to get past the upgrade, is not an option.

So what are my other options for cleaning out the supposed duplicates? How do I determine what these remaining duplicates are?

I flicked the coin. It disappeared into the drain! I'm now broke!

Seriously, the query:

SELECT question, attempt, seq_number, COUNT (seq_number) AS NumOccurrences FROM mdl_question_states GROUP BY question, attempt, seq_number HAVING (COUNT(seq_number) > 1 )

returns about 6300 rows on my Moodle database, so going through each one and verifying it against .../mod/quiz/review.php?attempt={quiz_attempts.id} as you suggested earlier in this thread isn't really an option. It will take forever. The ones that I have checked are all valid attempts that I want to keep.

So I need more help sorting this mess out! As I've described elsewhere in this thread, I've tried deleting all those 6300+ rows that the above query returns but I still crash with the "Cannot insert duplicate key row in object 'dbo.mdl_question_attempt_steps' with unique index 'mdl_quesattestep_queseq_uix'" error when I try to finish the upgrade to 2.1 or 2.2, so something else is still wrong.

I did try disabling the mdl_quesattestep_queseq_uix index and the upgrade from 2.0.4 to 2.1 or 2.2 completed "successfully" - although I haven't tested the results thoroughly yet. But I really wouldn't like to do that on the production database. Especially since I can't rebuild the index after the upgrade because that also fails with duplicate messages (see 213578#p1002572). And disabling that index is probably a dumb thing to do anyway...

So, what else do I need to do to clean up that mdl_question_states table? I really don't know what is still wrong with it and what the remaining problem data is.

Should I create a Tracker issue for this?