General help

 
 
Picture of Jerry Lau
During upgrade: DDL sql execution error Debug: Duplicate entry '679992' for key 'mdl_qtypmatcopti_que_uix'
 

Hello,

During the upgrade from 2.2.4+ to 2.5.1+, we encountered this error.

Default exception handler: DDL sql execution error Debug: Duplicate entry '679992' for key 'mdl_qtypmatcopti_que_uix'
CREATE UNIQUE INDEX mdl_qtypmatcopti_que_uix ON mdl_qtype_match_options (questionid)
Error code: ddlexecuteerror

I can find the dupe but wanted to know what this new table, "mdl_qtype_match_options" in 2.2.1+ does and which table or tables does it work with so that at least I can remove the cause of these dupes.

MariaDB [ourmoodledatabase]> select * from mdl_qtype_match_options where questionid = '679992';
+------+------------+-------------------+----------------+-----------------+-----------------------+--------------------------+--------------------------------+-------------------+-------------------------+----------------+
| id | questionid | subquestions | shuffleanswers | correctfeedback | correctfeedbackformat | partiallycorrectfeedback | partiallycorrectfeedbackformat | incorrectfeedback | incorrectfeedbackformat | shownumcorrect |
+------+------------+-------------------+----------------+-----------------+-----------------------+--------------------------+--------------------------------+-------------------+-------------------------+----------------+
| 1425 | 679992 | 14186,14187,14188 | 1 | | 0 | | 0 | | 0 | 0 |
| 1429 | 679992 | 14211,14212,14213 | 1 | | 0 | | 0 | | 0 | 0 |
+------+------------+-------------------+----------------+-----------------+-----------------------+--------------------------+--------------------------------+-------------------+-------------------------+----------------+
2 rows in set (0.01 sec)

Thoughts?

 

 
Average of ratings: -
Tim at Lone Pine Koala Sanctuary
Re: During upgrade: DDL sql execution error Debug: Duplicate entry '679992' for key 'mdl_qtypmatcopti_que_uix'
Group DevelopersGroup Documentation writersGroup Particularly helpful Moodlers

qtype_match_options is just a rename of the old question_match table. Hopefully it is obvious that this relates to the match question type, so you can see what the upgrade is trying to do by looking at https://github.com/moodle/moodle/blob/master/question/type/match/db/upgrade.php

 
Average of ratings: -
Picture of Jerry Lau
Re: During upgrade: DDL sql execution error Debug: Duplicate entry '679992' for key 'mdl_qtypmatcopti_que_uix'
 

Thanks Tim.

I'm not technical but from what I gather, I have to delete any records that have duplicate values in the "question" field in the mdl_question_match table first then do the upgrade correct?

If this is the case, could you clarify the formatting or what the comma delimited values in the "subquestions" field in mdl_question_match are derived or calculated?

And how could you find out the course id number of the course that contains these questions?

What are the tables involved or sql statement to pull them in together?

Thanks

 

 

 
Average of ratings: -
Tim at Lone Pine Koala Sanctuary
Re: During upgrade: DDL sql execution error Debug: Duplicate entry '679992' for key 'mdl_qtypmatcopti_que_uix'
Group DevelopersGroup Documentation writersGroup Particularly helpful Moodlers

"I have to delete any records that have duplicate values in the 'question' field in the mdl_question_match table first then do the upgrade correct?"

Correct.

The comma delimited values in the subquestions field are nto used for anything any more, and are thrown away during the upgrade. I think you can ignore them.

To link a question to a course is a bit tricky. You need to follow the links mdl_question.category -> mdl_question_categories.id; mdl_question_categories.contextid -> mdl_contetext.id.

Then if mdl_context.contextlevel = 50, mdl_context.instanceid -> mdl_course.id

Otherwise if mdl_context.contextlevel = 70, mdl_context.instanceid -> mdl_course_modules.id; mdl_course_modules.cousre -> course.id.

(And mdl_course_modules.instance probably -> mdl_quiz.id.)

Putting it all together leads to a rather scary DB query, but one I have written before, so here is the kind of think you need to do:

(Note that, if you are using MySQL rather than Postgres, you need to change the x || y || z string concatenation into CONCAT(x, y, z) instead.)

SELECT 
COALESCE(c.shortname, cmc.shortname) AS courseshortname,
quiz.name AS quiz_name,
qc.name AS category_name,
q.name AS question_name,
COALESCE(
'http://example.com/moodle/question/edit.php' || chr(63) || 'courseid=' || c.id || '&category=' || qc.id || '%2C' || ctx.id || '&lastchanged=' || q.id,
'http://example.com/moodle/question/edit.php' || chr(63) || 'cmid=' || cm.id || '&category=' || qc.id || '%2C' || ctx.id || '&lastchanged=' || q.id
) AS question_bank_url

FROM mdl_question q
JOIN mdl_question_categories qc ON qc.id = q.category
JOIN mdl_context ctx ON ctx.id = qc.contextid
LEFT JOIN mdl_course c ON ctx.contextlevel = 50 AND c.id = ctx.instanceid
LEFT JOIN mdl_course_modules cm ON ctx.contextlevel = 70 AND cm.id = ctx.instanceid
LEFT JOIN mdl_quiz quiz ON quiz.id = cm.instance
LEFT JOIN mdl_course cmc ON cmc.id = cm.course
LEFT JOIN mdl_qtype_opaque_options qo ON qo.questionid = q.id
LEFT JOIN mdl_qtype_opaque_engines qe ON qe.id = qo.engineid

WHERE q.qtype = 'match'

ORDER BY courseshortname, quiz.name, qc.name, qe.name, q.name

LIMIT 10
 
Average of ratings: -
Picture of Jerry Lau
Re: During upgrade: DDL sql execution error Debug: Duplicate entry '679992' for key 'mdl_qtypmatcopti_que_uix'
 

Fantastic! 

Am I correct that this query is for 2.5.x as the table, "mdl_qtype_opaque_options " is not in version 2.2.x.

Thanks Tim ... a lot

 
Average of ratings: -
Tim at Lone Pine Koala Sanctuary
Re: During upgrade: DDL sql execution error Debug: Duplicate entry '679992' for key 'mdl_qtypmatcopti_que_uix'
Group DevelopersGroup Documentation writersGroup Particularly helpful Moodlers

Sorry, no. That was an OU-specific query, and I just failed to remove all the irrelevant bits. Just delete the two lines that mention opaque.

 
Average of ratings: -
Picture of Jerry Lau
Re: During upgrade: DDL sql execution error Debug: Duplicate entry '679992' for key 'mdl_qtypmatcopti_que_uix'
 

Yes... I already have and the query went through although the url did not show anything (just null)...

 
Average of ratings: -
Picture of Jerry Lau
Re: During upgrade: DDL sql execution error Debug: Duplicate entry '679992' for key 'mdl_qtypmatcopti_que_uix'
 

Hmmmm... still only get the quiz name... no id or course name is shown ... sad in any of the "course" or "category" columns I queried for...

This is for 2.2.4+ to prepare a clean upgrade to 2.5.1.x+

Thanks

 
Average of ratings: -
Picture of Jerry Lau
Re: During upgrade: DDL sql execution error Debug: Duplicate entry '679992' for key 'mdl_qtypmatcopti_que_uix'
 

anybody? sad

query for 2.2.4+ to check for the associated course which a quiz belongs to.

Thanks

 
Average of ratings: -
Sketch...
Re: During upgrade: DDL sql execution error Debug: Duplicate entry '679992' for key 'mdl_qtypmatcopti_que_uix'
Group Particularly helpful Moodlers

How about:

SELECT
    mdl_quiz.id AS quiz_id,
    mdl_quiz.name AS quiz_name,
    mdl_course.id AS course_id,
    mdl_course.fullname AS course_fullname,
    mdl_course.shortname AS course_shortname
FROM
    mdl_quiz
INNER JOIN mdl_course ON mdl_quiz.course = mdl_course.id
WHERE
    mdl_quiz.id = 1425;

Replace "1425" with the id of your quiz.

 
Average of ratings: -
Picture of Jerry Lau
Re: During upgrade: DDL sql execution error Debug: Duplicate entry '679992' for key 'mdl_qtypmatcopti_que_uix'
 

Thanks Luis.

But this query supposes that you know the id of the quiz. I don't know the id of the quiz.

When I do this query:

mysql> select id, question, subquestions from  mdl_question_match where question in (select question from mdl_question_match group by question having count(question) > 1) order by question;

 

Note that the "question" column field have duplicate values.

I want to find out which courses these records returned by my query are attached to:

+------+----------+-------------------------------------------------------------+
| id   | question | subquestions                                                |
+------+----------+-------------------------------------------------------------+
| 1422 |   679986 | 14164,14165,14166,14167,14168,14169,14170,14171,14172,14173 |
| 1426 |   679986 | 14189,14190,14191,14192,14193,14194,14195,14196,14197,14198 |
| 1423 |   679987 | 14174,14175,14176,14177,14178                               |
| 1427 |   679987 | 14199,14200,14201,14202,14203                               |
| 1424 |   679988 | 14179,14180,14181,14182,14183,14184,14185                   |
| 1428 |   679988 | 14204,14205,14206,14207,14208,14209,14210                   |
| 1425 |   679992 | 14186,14187,14188                                           |
| 1429 |   679992 | 14211,14212,14213                                           |
+------+----------+-------------------------------------------------------------+
8 rows in set (0.31 sec)

 

It is important to fix or remove these duplicates in the question column before the upgrade because if I don't will get the "DDL sql execution error Debug: Duplicate entry 'XXXXXX' for key 'mdl_qtypmatcopti_que_uix'" where XXXXXX represents the question column field value error (ie. DDL sql execution error Debug: Duplicate entry '679992' for key 'mdl_qtypmatcopti_que_uix)

Thanks

 

 

 

 
Average of ratings: -
Sketch...
Re: During upgrade: DDL sql execution error Debug: Duplicate entry '679992' for key 'mdl_qtypmatcopti_que_uix'
Group Particularly helpful Moodlers

The Moodle upgrade code should be able to handle those duplicate records without you having to delete anything from the database. Deleting records from the database can be dangerous...

So, you should report that DDL Execution Error in the Moodle Tracker so that the Moodle developers are aware of the problem and they can fix it.

 
Average of ratings: -
Picture of Jerry Lau
Re: During upgrade: DDL sql execution error Debug: Duplicate entry '679992' for key 'mdl_qtypmatcopti_que_uix'
 
Average of ratings: -
Sketch...
Re: During upgrade: DDL sql execution error Debug: Duplicate entry '679992' for key 'mdl_qtypmatcopti_que_uix'
Group Particularly helpful Moodlers

To get the COURSE name and id try a query like:

SELECT
  mdl_question_match.id,
  mdl_question_match.question,
  mdl_question_match.subquestions,
  mdl_question.id As question_id,
  mdl_quiz.course,
  mdl_course.id As course_id,
  mdl_course.fullname,
  mdl_course.shortname
FROM
  mdl_quiz
  INNER JOIN mdl_quiz_attempts ON mdl_quiz.id = mdl_quiz_attempts.quiz
  INNER JOIN (mdl_question_match
    INNER JOIN mdl_question ON mdl_question_match.question = mdl_question.id
    INNER JOIN mdl_question_attempts On mdl_question.id = mdl_question_attempts.questionid) ON mdl_quiz_attempts.uniqueid = mdl_question_attempts.id
  INNER JOIN mdl_course ON mdl_quiz.course = mdl_course.id
WHERE
  mdl_question_match.question IN (
    SELECT mdl_question_match_subq.question
    FROM mdl_question_match As mdl_question_match_subq
    GROUP BY mdl_question_match_subq.question
    HAVING COUNT(mdl_question_match_subq.question) > 1)
ORDER BY
  mdl_question_match.question

 
Average of ratings: -
Picture of Jerry Lau
Re: During upgrade: DDL sql execution error Debug: Duplicate entry '679992' for key 'mdl_qtypmatcopti_que_uix'
 

Thanks Luis. Will report it on the tracker.

When I ran your tracker against the database where the dupes are supposedly are, I get an empty return set... meaning.. they did not find any mixed

 

 

 
Average of ratings: -
Picture of Jerry Lau
Re: During upgrade: DDL sql execution error Debug: Duplicate entry '679992' for key 'mdl_qtypmatcopti_que_uix'
 

Upgrade worked after implementing Tim's fix before official integration!

https://github.com/timhunt/moodle/compare/MOODLE_25_STABLE...MDL-41093_25

https://tracker.moodle.org/browse/MDL-41093

 

 

 
Average of ratings: -