Finding a question in the database

Finding a question in the database

by curt bixel -
Number of replies: 16

Current working installation details:

Moodle 2.6,      VPS 2000 at Cloudways,     3 classes total being hosted.   Total of 150 students.


Attempting to upgrade to:

Moodle 2.9,      VPS 2000 at Cloudways,     3 classes total being hosted.   Total of 150 students.



I am currently troubleshooting a difficult restore on a new server.  I have a course that hangs during the restore process if I include quizzes as part of the restore.

I turned on full developer debugging, and I am getting a message that there are multiple instances of something in mdl_question that have the same "stamp" and "version."  Here is the message I get during the "process" part of the restore:


Did you remember to make the first column something unique in your call to get_records? Duplicate value 'uamoodle.uaschools.org+090619224405+RhXqik uamoodle.uaschools.org+090619224405+OKXtyP' found in column 'concat(stamp, ' ', version)'.

From the "uamoodle.uaschools" part of the "stamp" that these were created several years back on an old installation, and are still present.  This indicates that they have come through past backup and restores as I have moved to new servers. 

I am not sure why there are multiple items in my database with the same stamp and version.

Is there any way that I can associate the item in the database with the question in moodle?

I would prefer to delete these troublesome items from within the moodle application if that is possible.

I could, of course, delete the multiple instances of these items directly from the database, but don't have a deep enough understanding of how moodle works with the database to feel comfortable with that, as I would actually have to delete these items from the database in the server I am currently using in order to have them not be included in the backup.



Average of ratings: -
In reply to curt bixel

Re: Finding a question in the database

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

I am afraid it is terribly confusing, and you could not possibly guess wink

The questions are stored in the mdl_question table. Two of the columns in that table are stamp and version.

Average of ratings: -
In reply to Tim Hunt

Re: Finding a question in the database

by curt bixel -

Tim,

Thanks for the response.  I have found the list questions in the database that have identical stamp and version entries.  Somehow there has to be a way to connect them with actual questions.  Do you know of anyone in any of the forums who might be able to respond to this question?

Average of ratings: -
In reply to curt bixel

Re: Finding a question in the database

by Marcus Green -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Plugin developers Picture of Testers

Are you asking about joining the information in the mdl_question table with the answers to these questions? This diagram might help though I think the one Tim created for the docs is better.

http://www.examulator.com/er/components/question_bank.png


(just found this as well

https://moodle.org/mod/forum/discuss.php?d=115227)


Average of ratings: Useful (1)
In reply to Marcus Green

Re: Finding a question in the database

by curt bixel -

Marcus,


Thank you.  This helped me to associate entries in the database with the actual questions in moodle.  I have been able to solve my problems.  smile

Average of ratings: -
In reply to curt bixel

Re: Finding a question in the database

by Marcus Green -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Plugin developers Picture of Testers

Delighted to have helped, if you hadn't noticed there is a whole database diagram here

http://www.examulator.com/er/

And I am planning to do one for 2.9 with a few enhancements.

Average of ratings: Useful (1)
In reply to Marcus Green

Re: Finding a question in the database

by curt bixel -

Marcus,

You may know enough about the database and questions to help me with another issue I have.  For some reason, the images are not coming through in my questions for a specific class.  This class is also throwing an error message during restore.  Here is the error message:

Did you remember to make the first column something unique in your call to get_records? Duplicate value 'uamoodle.uaschools.org+090619224257+WytIDs uamoodle.uaschools.org+090619224257+msfSAQ' found in column 'concat(stamp, ' ', version)'.

•    line 1089 of /lib/dml/mysqli_native_moodle_database.php: call to debugging()

•    line 1402 of /lib/dml/moodle_database.php: call to mysqli_native_moodle_database->get_records_sql()

•    line 652 of /backup/util/dbops/restore_dbops.class.php: call to moodle_database->get_records_sql_menu()

•    line 518 of /backup/util/dbops/restore_dbops.class.php: call to restore_dbops::prechek_precheck_qbanks_by_level()

•    line 1605 of /backup/util/dbops/restore_dbops.class.php: call to restore_dbops::precheck_categories_and_questions()

•    line 1305 of /backup/moodle2/restore_stepslib.php: call to restore_dbops::process_categories_and_questions()

•    line 34 of /backup/util/plan/restore_execution_step.class.php: call to restore_process_categories_and_questions->define_execution()

•    line 181 of /backup/util/plan/base_task.class.php: call to restore_execution_step->execute()

•    line 177 of /backup/util/plan/base_plan.class.php: call to base_task->execute()

•    line 167 of /backup/util/plan/restore_plan.class.php: call to base_plan->execute()

•    line 333 of /backup/controller/restore_controller.class.php: call to restore_plan->execute()

•    line 224 of /backup/util/ui/restore_ui.class.php: call to restore_controller->execute_plan()

•    line 111 of /backup/restore.php: call to restore_ui->execute()


When I go into the database and search for the "stamp" shown in the error message, I get about 8 different entries...


Modifyidcategoryparentnamequestiontextquestiontextformatgeneralfeedbackgeneralfeedbackformatdefaultmarkpenaltyqtypelengthstampversionhiddentimecreatedtimemodifiedcreatedbymodifiedby
edit1978390019783Random (metric prefixes and sub-categories)11 11.00000000.5000000random1uamoodle.uaschools.org+090619224257+WytIDsuamoodle.uaschools.org+090619224257+msfSAQ01245451377124545137733
edit1982890019828Random (Default and sub-categories)11 11.00000000.5000000random1uamoodle.uaschools.org+090619224257+WytIDsuamoodle.uaschools.org+090619224257+msfSAQ01245451377124545137733
edit43191209643191Random (metric prefixes and sub-categories)11 11.00000000.5000000random1uamoodle.uaschools.org+090619224257+WytIDsuamoodle.uaschools.org+090619224257+msfSAQ01245451377124545137722
edit43236209643236Random (Default and sub-categories)11 11.00000000.5000000random1uamoodle.uaschools.org+090619224257+WytIDsuamoodle.uaschools.org+090619224257+msfSAQ01245451377124545137722
edit50017241950017Random (metric prefixes and sub-categories)11 11.00000000.5000000random1uamoodle.uaschools.org+090619224257+WytIDsuamoodle.uaschools.org+090619224257+msfSAQ01245451377124545137733
edit50062241950062Random (Default and sub-categories)

Any suggestions?  I have a suspicion that when I get these issues taken care of, my install will complete perfectly.  


What would happen if I simply deleted any of these problem questions directly from the database?


Average of ratings: -
In reply to curt bixel

Re: Finding a question in the database

by Marcus Green -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Plugin developers Picture of Testers

In my experience that error message is saying exactly what it looks like, the method call expects every value in the first column of the returned set to be unique. I was doing some development of a report recently and I kept getting that error message.

Average of ratings: -
In reply to Marcus Green

Re: Finding a question in the database

by curt bixel -

Any idea what to do?

Average of ratings: -
In reply to curt bixel

Re: Finding a question in the database

by Marcus Green -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Plugin developers Picture of Testers

I changed the query to ensure I was not getting duplicate values, but I suspect that is not an option for you. You might be able to find out what the exact query is, run that query and see why you are getting duplicate values. Then it might be possible to delete values that are duplicate.

Average of ratings: -
In reply to Marcus Green

Re: Finding a question in the database

by curt bixel -

I have been trying to delete the values that are duplicate by associating the duplicate values in the database with the actual questions within moodle.  I was able to get rid of several questions that seem to have been installed multiple times during various question imports.  

These questions, however, don't seem to be actual questions, I think.  They are in the catagory, "Random."  Any idea on how to find out why these are in the database and what they are associated with in moodle so I can delete that.

Another option would be to delete them directly within the database, but I am not quite sure what the possible pitfalls of that might be.

Average of ratings: -
In reply to curt bixel

Re: Finding a question in the database

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

Random questions are an implementation detail. I don't see how they could be causing your problem.

Average of ratings: -
In reply to Tim Hunt

Re: Finding a question in the database

by curt bixel -

Tim,

I am not sure either.  They do, however, show up in the database when I search for the items referred to in the error message as "duplicate" stamp entries.  Also, nothing else shows up in the database associated with the search for the offending duplicate files.  

Average of ratings: -
In reply to curt bixel

Re: Finding a question in the database

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

OK, well the safest thing is to just change the values in the stamp column, so they are all unique.

Average of ratings: -
In reply to Tim Hunt

Re: Finding a question in the database

by curt bixel -

This seems to be an easy fix.  Is there any risk of creating a problem?

Average of ratings: -
In reply to curt bixel

Re: Finding a question in the database

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

I can't think of a risk.

The cautious approach would be to try canging a few first. Or, Make sure you save the old values, so you can change it back later.

Average of ratings: -