search

studentquiz / moodle-mod_studentquiz

Moodle-Plugin
GNU General Public License v3.0
38 stars 38 forks source link

Installing V5.0.0 on Moodle 4.0 caused error #411

Closed jtuttas closed 1 year ago

jtuttas commented 2 years ago

Hello, just try to install the Version V5.0.0 on a Moodle 4.0 system. Doing this I got en error dmlwriteexception the complete error message looks like this

Debug-Info:  You can't specify target table 'mdl_studentquiz_question' for update in FROM clause
UPDATE mdl_studentquiz_question
SET studentquizid =
(SELECT sq.id
FROM mdl_question q
JOIN mdl_question_versions qv ON q.id = qv.questionid
JOIN mdl_question_bank_entries qbe ON qv.questionbankentryid = qbe.id
JOIN mdl_question_categories qc ON qc.id = qbe.questioncategoryid
JOIN mdl_context ctx ON ctx.id = qc.contextid
JOIN mdl_studentquiz sq ON sq.coursemodule = ctx.instanceid
JOIN mdl_studentquiz_question sqq ON sqq.questionid = q.id
WHERE mdl_studentquiz_question.questionid = q.id
)

[array (
)]
Error code: dmlwriteexception

And the strack trace looks like this....

line 489 of /lib/dml/moodle_database.php: dml_write_exception thrown
line 1167 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()
line 1137 of /mod/studentquiz/db/upgrade.php: call to mysqli_native_moodle_database->execute()
line 883 of /lib/upgradelib.php: call to xmldb_studentquiz_upgrade()
line 582 of /lib/upgradelib.php: call to upgrade_plugins_modules()
line 1935 of /lib/upgradelib.php: call to upgrade_plugins()
line 719 of /admin/index.php: call to upgrade_noncore()

Any idea what is going wrong? This plugin is the one until we decided to switch to Moodle 4.0 on our productive system. So it would be nice to have it working under moodle 4.0 !

best regards

jtuttas

timhunt commented 2 years ago

So, that is usign MySQL, I assume. OK. we will look into that.

jtuttas commented 2 years ago

yes, ! I think it is a problem with the migration, wenn i removed student quiz (delete it from the DB) and reinstall V 5.0.0 it works !

vuvanhieu143 commented 2 years ago

Hi @jtuttas , Could you please give me your mysql version ?

jtuttas commented 2 years ago

I've tested it on two system....

One Version Ubuntu 20.04 LTS: MYSQL Ver 14.14 Distrib 5.7.36, for Linux (x86_64) One on Windows Subsystem with Version: Ver 8.0.28-0ubuntu0.20.04.3 for Linux on x86_64 ((Ubuntu))

mcassisa commented 2 years ago

I got the same issue here installing studentquiz as an upgrade.

You can't specify target table 'mdl_studentquiz_question' for update in FROM clause UPDATE mdl_studentquiz_question SET studentquizid = (SELECT sq.id FROM mdl_question q JOIN mdl_question_versions qv ON q.id = qv.questionid JOIN mdl_question_bank_entries qbe ON qv.questionbankentryid = qbe.id JOIN mdl_question_categories qc ON qc.id = qbe.questioncategoryid JOIN mdl_context ctx ON ctx.id = qc.contextid JOIN mdl_studentquiz sq ON sq.coursemodule = ctx.instanceid JOIN mdl_studentquiz_question sqq ON sqq.questionid = q.id WHERE mdl_studentquiz_question.questionid = q.id ) [array ( )] Error code: dmlwriteexception

Ubuntu 16.04.7 LTS mysql Ver 14.14 Distrib 5.7.33, for Linux (x86_64) using EditLine wrapper

Can it be because of "In MySQL, you can't modify the same table which you use in the SELECT part. This behaviour is documented at: http://dev.mysql.com/doc/refman/5.6/en/update.html"? In that case the query could be nested in a second level (to avoid the optimizer running) as suggested here https://stackoverflow.com/a/9843719/421063

mcassisa commented 2 years ago

I got the same issue here installing studentquiz as an upgrade.

I suggest to change the SQL statement. A change could be:

    $DB->execute("UPDATE {studentquiz_question} sqq JOIN
                         (SELECT sq.id 
                            FROM {question} q
                            JOIN {question_versions} qv ON q.id = qv.questionid
                            JOIN {question_bank_entries} qbe ON qv.questionbankentryid = qbe.id
                            JOIN {question_categories} qc ON qc.id = qbe.questioncategoryid
                            JOIN {context} ctx ON ctx.id = qc.contextid
                            JOIN {studentquiz} sq ON sq.coursemodule = ctx.instanceid
                            JOIN {studentquiz_question} sqq ON sqq.questionid = q.id
                            WHERE q.id IS NOT NULL
                         ) aa ON sqq.questionid = aa.id
                     SET sqq.studentquizid = aa.id

                   ");

and also the next SQL statements have to be checked because the UPDATE statement complains that no NULL value can be set (it results from a subquery)

vuvanhieu143 commented 2 years ago

Thank @mcassisa for your help. "In MySQL, you can't modify the same table which you use in the SELECT part." due to locking issue, the original code should work fine for MariaDB and PostgreSQL I have create a PR to fix the issue for NULL value when we have bad data and fix issue for mysql. The code is peer-review yet.

412

timhunt commented 2 years ago

The fixed upgrade code is now in the 'main' branch, so if you are able to test this again, that would be very helpful. Thanks.

mcassisa commented 2 years ago

Cloned from main

The procedure raises an error

Unknown column 'mdl_studentquiz_comment.questionid' in 'where clause' UPDATE mdl_studentquiz_comment SET studentquizquestionid = ( SELECT id FROM mdl_studentquiz_question WHERE questionid = mdl_studentquiz_comment.questionid )

[array ( )] Error code: dmlwriteexception×Ignora notifica Stack trace:

Il giorno mer 7 set 2022 alle ore 14:51 Tim Hunt @.***> ha scritto:

The fixed upgrade code is now in the 'main' branch, so if you are able to test this again, that would be very helpful. Thanks.

— Reply to this email directly, view it on GitHub https://github.com/studentquiz/moodle-mod_studentquiz/issues/411#issuecomment-1239349332, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAWYPSRDTSQVRZ6QWFJ72WDV5CFU5ANCNFSM57R5ZH4Q . You are receiving this because you were mentioned.Message ID: @.***>

timhunt commented 2 years ago

@mcassisa, I think that error happens because you already used our old StudentQuiz code, and are now trying the new upgrade code on the same Moodle install. Is it feasible for you to go back to a backup, and try the entire upgrade again?

nwp90 commented 2 years ago

Looks like line 892 in upgrade.php is using incorrect column name - should be studentquizquestionid rather than questionid. Our upgrade gave errors due to trying to write null in there as a result:

Database transaction aborted automatically in /d1/srv/moodle/otagounifom/moodle/admin/cli/upgrade.php Default exception handler: Error writing to database Debug: ERROR: null value in column "questionid" violates not-null constraint DETAIL: Failing row contains (1, null, 3197, 2, 1492255872). INSERT INTO mdl_studentquiz_state_history (userid,state,timecreated) VALUES($1,$2,$3) RETURNING id [array ( 'userid' => 3197, 'state' => 2, 'timecreated' => 1492255872, )] Error code: dmlwriteexception

  • line 489 of /lib/dml/moodle_database.php: dml_write_exception thrown
  • line 291 of /lib/dml/moodle_read_slave_trait.php: call to moodle_database->query_end()
  • line 338 of /lib/dml/pgsql_native_moodle_database.php: call to pgsql_native_moodle_database->read_slave_query_end()
  • line 1122 of /lib/dml/pgsql_native_moodle_database.php: call to pgsql_native_moodle_database->query_end()
  • line 1170 of /lib/dml/pgsql_native_moodle_database.php: call to pgsql_native_moodle_database->insert_record_raw()
  • line 595 of /mod/studentquiz/classes/utils.php: call to pgsql_native_moodle_database->insert_record()
  • line 923 of /mod/studentquiz/db/upgrade.php: call to mod_studentquiz\utils::question_save_action()
  • line 883 of /lib/upgradelib.php: call to xmldb_studentquiz_upgrade()
  • line 582 of /lib/upgradelib.php: call to upgrade_plugins_modules()
  • line 1935 of /lib/upgradelib.php: call to upgrade_plugins()
  • line 202 of /admin/cli/upgrade.php: call to upgrade_noncore()

!!! Error writing to database !!! !! ERROR: null value in column "questionid" violates not-null constraint DETAIL: Failing row contains (1, null, 3197, 2, 1492255872). INSERT INTO mdl_studentquiz_state_history (userid,state,timecreated) VALUES($1,$2,$3) RETURNING id [array ( 'userid' => 3197, 'state' => 2, 'timecreated' => 1492255872, )] Error code: dmlwriteexception !! !! Stack trace: * line 489 of /lib/dml/moodle_database.php: dml_write_exception thrown

  • line 291 of /lib/dml/moodle_read_slave_trait.php: call to moodle_database->query_end()
  • line 338 of /lib/dml/pgsql_native_moodle_database.php: call to pgsql_native_moodle_database->read_slave_query_end()
  • line 1122 of /lib/dml/pgsql_native_moodle_database.php: call to pgsql_native_moodle_database->query_end()
  • line 1170 of /lib/dml/pgsql_native_moodle_database.php: call to pgsql_native_moodle_database->insert_record_raw()
  • line 595 of /mod/studentquiz/classes/utils.php: call to pgsql_native_moodle_database->insert_record()
  • line 923 of /mod/studentquiz/db/upgrade.php: call to mod_studentquiz\utils::question_save_action()
  • line 883 of /lib/upgradelib.php: call to xmldb_studentquiz_upgrade()
  • line 582 of /lib/upgradelib.php: call to upgrade_plugins_modules()
  • line 1935 of /lib/upgradelib.php: call to upgrade_plugins()
  • line 202 of /admin/cli/upgrade.php: call to upgrade_noncore() !!
nwp90 commented 2 years ago

Line 899 will need updating too.

mcassisa commented 2 years ago

Dear all Unfortunately I can't switch back to a previous dataset Thank you for your work M

Il ven 9 set 2022, 12:26 Tim Hunt @.***> ha scritto:

@mcassisa https://github.com/mcassisa, I think that error happens because you already used our old StudentQuiz code, and are now trying the new upgrade code on the same Moodle install. Is it feasible for you to go back to a backup, and try the entire upgrade again?

— Reply to this email directly, view it on GitHub https://github.com/studentquiz/moodle-mod_studentquiz/issues/411#issuecomment-1241794214, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAWYPSQRANY55MDDUVUNCO3V5MGHBANCNFSM57R5ZH4Q . You are receiving this because you were mentioned.Message ID: @.***>

timhunt commented 1 year ago

The code that fixes this is released, so closing this now.