Upgrade error during insert nto mdl_question_attempt_steps

Upgrade error during insert nto mdl_question_attempt_steps

by Luis de Vasconcelos -
Number of replies: 4

I'm attempting to upgrade a test instance of Moodle 2.0.4+ (Build: 20110907) to version 2.2.1+ (Build: 20120112) and I get the following error:

Debug info: SQLState: 23000<br>
Error Code: 2601<br>
Message: [Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot insert duplicate key row in object 'dbo.mdl_question_attempt_steps' with unique index 'mdl_quesattestep_queseq_uix'.<br>
SQLState: 01000<br>
Error Code: 3621<br>
Message: [Microsoft][SQL Server Native Client 10.0][SQL Server]The statement has been terminated.<br>
INSERT INTO mdl_question_attempt_steps (fraction,timecreated,userid,state,sequencenumber,questionattemptid) VALUES(NULL,'1268039088','7472',N'todo','0','1000')
[array (
0 => NULL,
1 => '1268039088',
2 => '7472',
3 => 'todo',
4 => 0,
5 => 1000,
)]
Stack trace:
?? line 397 of \lib\dml\moodle_database.php: dml_write_exception thrown
?? line 252 of \lib\dml\sqlsrv_native_moodle_database.php: call to moodle_database->query_end()
?? line 368 of \lib\dml\sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->query_end()
?? line 881 of \lib\dml\sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->do_query()
?? line 959 of \lib\dml\sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->insert_record_raw()
?? line 299 of \question\engine\upgrade\upgradelib.php: call to sqlsrv_native_moodle_database->insert_record()
?? line 258 of \question\engine\upgrade\upgradelib.php: call to question_engine_attempt_upgrader->insert_record()
?? line 221 of \question\engine\upgrade\upgradelib.php: call to question_engine_attempt_upgrader->save_usage()
?? line 176 of \question\engine\upgrade\upgradelib.php: call to question_engine_attempt_upgrader->convert_quiz_attempt()
?? line 131 of \question\engine\upgrade\upgradelib.php: call to question_engine_attempt_upgrader->update_all_attempts_at_quiz()
?? line 1112 of \mod\quiz\db\upgrade.php: call to question_engine_attempt_upgrader->convert_all_quiz_attempts()
?? line 538 of \lib\upgradelib.php: call to xmldb_quiz_upgrade()
?? line 271 of \lib\upgradelib.php: call to upgrade_plugins_modules()
?? line 1449 of \lib\upgradelib.php: call to upgrade_plugins()
?? line 269 of \admin\index.php: call to upgrade_noncore()

At the beginning of the upgrade all the server checks are ok - I get the "Your server environment meets all minimum requirements" message.

The mdl_quesattestep_queseq_uix index is a unique nonclustered index on the questionattemptid and sequencenumber columns on the mdl_question_attempt_steps table.

The following SELECT:

select * from mdl_question_attempt_steps
where sequencenumber = 0
and questionattemptid = 1000

returns one record:

id questionattemptid sequencenumber state fraction timecreated userid
1973 1000 0 todo NULL 1239178400 6320

This SELECT:

select * from mdl_question_attempt_steps
where questionattemptid = 1000 

returns two records:

 id questionattemptid sequencenumber state fraction timecreated userid
1973 1000 0 todo NULL 1239178400 6320
1974 1000 1 gradedpartial 0.4000000 1239181470 6320

Any suggestions on how I can fix this error?

My server setup:

  • DB Driver: Microsoft SQL Server Driver for PHP 2
  • DB Engine: Microsoft SQL Server 2005 database (9.00.4035.00)
  • OS: Windows 2003 Server
  • Web Server: IIS 6

Thanks.

Average of ratings: -
In reply to Luis de Vasconcelos

Re: Upgrade error during insert nto mdl_question_attempt_steps

by Tim Hunt -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

The problem is not with the question_attempt_steps table. The upgrade script is copying data form somewhere else, and is crashing when it tries to insert a duplicate record, so of you don't get the second record added to that table.

The data is being copied FROM the mdl_question_states table. Have a look for duplciate combinations of attempt, question and seq_number there. (However, I thought the upgrade was robust enough to resolve duplicate seq_numbers there.)

Average of ratings: Useful (1)
In reply to Tim Hunt

Re: Upgrade error during insert nto mdl_question_attempt_steps

by Luis de Vasconcelos -

Thanks Tim.

What do I do if there is a duplicate row in mdl_question_states? Is it safe to delete one of them?

I see that I reported this a few months ago in MDL-27900.

Average of ratings: -
In reply to Luis de Vasconcelos

Re: Upgrade error during insert nto mdl_question_attempt_steps

by Tim Hunt -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

 MDL-27900 Looks like a completely different bug.

If you have duplicate mdl_question_states, first find the quiz attempt id (question_states->attempt = quiz_attempts->uniqueid, look at the id in that quiz_attempts row).

The go to mod/quiz/review.php?attempt={id} or whatever the right URL is, to see if that attempt is worth saving, or if it is just garbage.

That will let you know if it is safe to delete.

Average of ratings: -