Duplicate column name 'alternatename'

Duplicate column name 'alternatename'

by Mahmoud Abdel Azeem -
Number of replies: 4

I upgraded old moodle to version 3.8 and it appear error wile I try to view quize report as follow 

Error reading from database


×Debug info: Duplicate column name 'alternatename'
SELECT COUNT(1) FROM (SELECT DISTINCT CONCAT(u.id, '#', COALESCE(quiza.attempt, 0)) AS uniqueid,
(CASE WHEN (quiza.state = 'finished' AND NOT EXISTS (
SELECT 1 FROM mdl_quiz_attempts qa2
WHERE qa2.quiz = quiza.quiz AND
qa2.userid = quiza.userid AND
qa2.state = 'finished' AND (
COALESCE(qa2.sumgrades, 0) > COALESCE(quiza.sumgrades, 0) OR
(COALESCE(qa2.sumgrades, 0) = COALESCE(quiza.sumgrades, 0) AND qa2.attempt < quiza.attempt)
))) THEN 1 ELSE 0 END) AS gradedattempt,
quiza.uniqueid AS usageid,
quiza.id AS attempt,
u.id AS userid,
u.idnumber, u.firstnamephonetic,u.lastnamephonetic,u.middlename,u.alternatename,u.firstname,u.lastname,
u.picture,
u.imagealt,
u.institution,
u.department,
u.email, u.phone1, u.phone2, u.alternatename,
quiza.state,
quiza.sumgrades,
quiza.timefinish,
quiza.timestart,
CASE WHEN quiza.timefinish = 0 THEN null
WHEN quiza.timefinish > quiza.timestart THEN quiza.timefinish - quiza.timestart
ELSE 0 END AS duration FROM mdl_user u
LEFT JOIN mdl_quiz_attempts quiza ON
quiza.userid = u.id AND quiza.quiz = ?
JOIN mdl_user_enrolments ej1_ue ON ej1_ue.userid = u.id
JOIN mdl_enrol ej1_e ON (ej1_e.id = ej1_ue.enrolid AND ej1_e.courseid = ?)
JOIN (SELECT DISTINCT userid
FROM mdl_role_assignments
WHERE contextid IN (1,38965,9025,16805,332859)
AND roleid IN (5)
) ra ON ra.userid = u.id WHERE quiza.preview = 0 AND quiza.id IS NOT NULL AND 1 = 1 AND u.deleted = 0 AND u.id <> ? AND u.deleted = 0) temp WHERE 1 = 1
[array (
0 => '489',
1 => '147',
2 => '1',
)]
Error code: dmlreadexception


×Stack trace:
  • line 486 of /lib/dml/moodle_database.php: dml_read_exception thrown
  • line 1247 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()
  • line 1587 of /lib/dml/moodle_database.php: call to mysqli_native_moodle_database->get_records_sql()
  • line 1660 of /lib/dml/moodle_database.php: call to moodle_database->get_record_sql()
  • line 1870 of /lib/dml/moodle_database.php: call to moodle_database->get_field_sql()
  • line 1620 of /lib/tablelib.php: call to moodle_database->count_records_sql()
  • line 592 of /mod/quiz/report/attemptsreport_table.php: call to table_sql->query_db()
  • line 363 of /mod/quiz/report/overview/overview_table.php: call to quiz_attempts_report_table->query_db()
  • line 1674 of /lib/tablelib.php: call to quiz_overview_table->query_db()
  • line 210 of /mod/quiz/report/overview/report.php: call to table_sql->out()
  • line 97 of /mod/quiz/report.php: call to quiz_overview_report->display()

Average of ratings: -
In reply to Mahmoud Abdel Azeem

Re: Duplicate column name 'alternatename'

by AL Rachels -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Plugin developers Picture of Testers
Hi Mahmoud,
Just as the error message says, you have u.alternatename in multiple places, first in line 14 and then again in line 19.
Average of ratings: -
In reply to AL Rachels

Re: Duplicate column name 'alternatename'

by Mahmoud Abdel Azeem -
Hi AL Rachels
Thanks for your response but I try to find which file contain this sql code to edit on it - moodle just appear to me the code instead of the file who contain this code
Average of ratings: -
In reply to Mahmoud Abdel Azeem

Re: Duplicate column name 'alternatename'

by Leon Stringer -
Picture of Core developers Picture of Particularly helpful Moodlers

What is $CFG->showuseridentity set to in the database? Try the query:

SELECT name, value FROM mdl_config WHERE name = 'showuseridentity';

I'm guessing this will be 'phone1,phone2,alternatename'. But Alternate Name isn't one of the valid fields for this setting.

Note: It's possible that this value is set in config.php instead of the database.

Assuming it's set in the database – and not config.php – then you could probably fix this with:

UPDATE mdl_config SET value = 'phone1,phone2' WHERE name = 'showuseridentity';

You'll probably need to purge caches afterwards to ensure this change is read from the database.

Average of ratings: Useful (3)
In reply to Leon Stringer

Re: Duplicate column name 'alternatename'

by Mahmoud Abdel Azeem -
Hi Leon Stringer,
Thanks for advice this code really magic it solve the problem many thanks for you.
Average of ratings: -