Merging error (from https://moodle.org/plugins/tool_mergeusers)

[jpahullo]

Adrian Sutantiodc, 3 feb 2016, 06:30

Hi there,

Thank you for the amazing plugin. We have used this several times when we were on Moodle 2.7. We have just upgraded our Moodle to 2.9 and I got this error when tried to use the plugin

Exception thrown when merging: 'Error reading from database". Unknown column 'id' in 'field list' Trace:

0 /home/ddd/public_html/lib/dml/mysqli_native_moodle_database.php(1080): moodle_database->query_end(false)

1 /home/ddd/public_html/admin/tool/mergeusers/lib/table/generictablemerger.php(65): mysqli_native_moodle_database->get_records_sql('SELECT id FROM ...')

2 /home/ddd/public_html/admin/tool/mergeusers/lib/mergeusertool.php(281): GenericTableMerger->merge(Array, Array, Array)

3 /home/ddd/public_html/admin/tool/mergeusers/lib/mergeusertool.php(216): MergeUserTool->_merge('1709', '14610')

4 /home/ddd/public_html/admin/tool/mergeusers/index.php(135): MergeUserTool->merge('1709', '14610')

5 {main}

Can you help please?

[jpahullo]

Hi again Adrian,

Taking a closer look at your message, I need more details on what is the problem about, mainly the database table where the "id" column does not exist. Have you any custom plugin/development/?? which is using a table without an "id" column?

It is very strange, since Moodle defines (via XMLDB editor) always an "id" column. Can you search for tables without "id" column?

You can use this select to get the list of them:

select distinct c.table_name from Information_schema.columns c left join (select table_name from information_schema.columns where column_name like 'id') t ON t.table_name = c.table_name WHERE t.table_name IS Null and c.tablename like 'mdl%';

replace "mdl_" for your own table prefix.

Thanks in advance!

[ndunand]

@jpahullo any news about this? I'm trying to clean up as many issued as possible to get a clearer view about what has to actually be fixed.

[jpahullo]

Hi @ndunand! Unfortunatelly, there is no news about this. I added a comment on the moodle.org plugin page to ask Adrian to say something on this. Wait some days to let him say something to this respect.

[fcbsd]

Hi @ndunand, I had exactly the same issue - and it was due to table we use to autoenrol students from our Management Infromation System (MIS) having no id. The issue was trivally solved by adding the table name into getTablesToSkip function in table/generictablemerger.php like so:

public function getTablesToSkip() { return array('ext_enrolments'); // empty array when doing nothing. }

Where mdl_ext_enrolments is a table we use to do student autoenrolment.

Following this change merging users worked as expected.

We are using Moodle 3.1.1

[ndunand]

Hi @fcbsd , thanks for taking the time to document this, it is good for us to know, and will be helpful to others.

[jpahullo]

If that is the case, you'd better can include in your config.local.php file these lines:

[...]
    'exceptions' => array(
        'user_preferences',
        'user_private_key',
        'user_info_data',
        'my_pages',
        'ext_enrolments',
    ),
[...]

so that you do not modify source code and behavior remains the same.

In conclusion, you need to exclude some tables to be processed by our plugin. Note that you need to include all table names that already exist on config.php, in order to complement the configuration settings and not to replace that exceptions setting with just a single table.

Hoping that helps.

Jordi

[peterkhl]

Hello. Thank you for your plugin, it is really great but something went wrong in our case. We are testing it right now before applying to a real system and there is one problem we faced with. It is about forum grades. After merging two user accounts it has happend that the user does not have grades for any forum. When I started digging into the problem, it turned out that the grades have left in ratings table but not in grade_grades table? Can you please help me with this problem? May be I am doing something wrong? Forgot to mention that we are using moodle 3.1 and have just moved from 1.9 Serenely, Petr.

[jpahullo]

Hi!

As I told in https://moodle.org/plugins/tool_mergeusers :

It may be possible, but this is the first time it was known for us. When you told us about the tables (grades and grade_grades or something like that), would you please explain a bit more about that? Which table was updated and which table was not?

We dealt about grades on quiz attempts to be updated correspondingly, thanks to a contribution. Maybe this is a particular case to be updated too.

Jordi

[peterkhl]

Thank you for your quick response, and I am sorry for being late with my answer. About the problem. As I said, I’ve tried to find out the reason and found out that the values from table mdl_grade_items simply disappeared.

Here is the result of the query:

SELECT mdl_grade_items.id AS giid, mdl_grade_grades.id AS ggid, mdl_forum.course, mdl_forum.id, mdl_grade_items.itemname, mdl_grade_grades.finalgrade, mdl_grade_grades.userid, user1.username AS studentname, mdl_grade_grades.usermodified, user2.username AS teachername, mdl_forum.name, mdl_grade_grades.rawgrade FROM mdl_grade_items INNER JOIN mdl_grade_grades ON mdl_grade_grades.itemid = mdl_grade_items.id INNER JOIN mdl_forum ON mdl_grade_items.iteminstance = mdl_forum.id INNER JOIN mdl_user AS user1 ON user1.id = mdl_grade_grades.userid INNER JOIN mdl_user AS user2 ON user2.id = mdl_grade_grades.usermodified WHERE mdl_grade_items.itemtype LIKE 'mod' AND mdl_grade_items.itemmodule LIKE 'forum' AND mdl_grade_items.courseid = 188 AND mdl_grade_grades.finalgrade IS NOT null and (user1.username like 'student1%' or user1.username like 'student2%') ORDER BY studentname

before

1763 38885 188 1081 forum №8 11 571 student2 2 admin1 forum №8 11 3084 52930 188 1512 forum №24 14 571 student2 61 teacher2 forum №24 14 2938 48372 188 1475 forum № 19 14 571 student2 61 teacher2 forum № 19 14 1766 41124 188 1082 forum №14 14 571 student2 2 admin1 forum №14 14 1770 33396 188 1083 forum №1 11 571 student2 61 teacher2 forum №1 11 1773 36770 188 1084 forum №6 11 571 student2 61 teacher2 forum №6 11 2939 50071 188 1476 forum № 21 14 571 student2 61 teacher2 forum № 21 14 1773 35813 188 1084 forum №6 14 344 student1 61 teacher2 forum №6 14 2939 50067 188 1476 forum № 21 14 344 student1 61 teacher2 forum № 21 14 1763 38881 188 1081 forum №8 14 344 student1 2 admin1 forum №8 14 3084 52928 188 1512 forum №24 14 344 student1 61 teacher2 forum №24 14 1766 41120 188 1082 forum №14 14 344 student1 2 admin1 forum №14 14 2938 48374 188 1475 forum № 19 8 344 student1 61 teacher2 forum № 19 8 1770 33389 188 1083 forum №1 13 344 student1 61 teacher2 forum №1 13

and after

1763 38881 188 1081 forum №8 14 344 student1 2 admin1 forum №8 14 1766 41120 188 1082 forum №14 14 344 student1 2 admin1 forum №14 14 1770 33389 188 1083 forum №1 13 344 student1 61 teacher1 forum №1 13 1773 35813 188 1084 forum №6 14 344 student1 61 teacher1 forum №6 14 2938 48374 188 1475 forum № 19 8 344 student1 61 teacher1 forum № 19 8 2939 50067 188 1476 forum № 21 14 344 student1 61 teacher1 forum № 21 14 3084 52928 188 1512 forum №24 14 344 student1 61 teacher1 forum №24 14

meanwhile, the following queries

SELECT mdl_forum.course, mdl_forum_discussions.forum, mdl_forum_posts.discussion, mdl_rating.itemid AS postid, mdl_forum_posts.userid AS studentid, student.username AS studentname, mdl_rating.contextid, mdl_context.instanceid AS course_module_id, mdl_rating.scaleid, mdl_rating.rating, mdl_rating.userid AS teacherid, teacher.username AS teachername, mdl_rating.timecreated, mdl_rating.timemodified FROM mdl_forum_discussions INNER JOIN mdl_forum_posts ON mdl_forum_posts.discussion = mdl_forum_discussions.id INNER JOIN mdl_course_modules ON mdl_forum_discussions.forum = mdl_course_modules.instance INNER JOIN mdl_context ON mdl_context.instanceid = mdl_course_modules.id INNER JOIN mdl_rating ON mdl_rating.contextid = mdl_context.id AND mdl_rating.itemid = mdl_forum_posts.id INNER JOIN mdl_forum ON mdl_forum_discussions.forum = mdl_forum.id INNER JOIN mdl_user AS student ON mdl_forum_posts.userid = student.id INNER JOIN mdl_user AS teacher ON mdl_rating.userid = teacher.id WHERE (student.username like 'student1%' or student.username like 'student2%') and mdl_context.contextlevel = 70 AND mdl_forum.course = 188 GROUP BY mdl_forum_discussions.id, mdl_forum_posts.userid order by studentname

get the same result before

188 1512 5045 42473 571 student2 17533 18741 -54 14 61 teacher2 1493538156 1493538156 188 1082 4164 36323 571 student2 9833 11925 -54 14 61 teacher2 1467537235 1467537235 188 1084 4161 33894 571 student2 9850 11942 -54 11 61 teacher2 1454232132 1454232132 188 1476 4788 41188 571 student2 16905 18392 -54 14 61 teacher2 1485571115 1485571115 188 1081 4163 34282 571 student2 9816 11908 -54 11 61 teacher2 1456643183 1456643183 188 1083 4159 30767 571 student2 9837 11929 -54 11 61 teacher2 1443335098 1443335098 188 1475 4786 40447 571 student2 16904 18391 -54 14 61 teacher2 1483340966 1483340966 188 1475 4786 40099 344 student1 16904 18391 -54 8 61 teacher2 1483340969 1483340969 188 1512 5045 42275 344 student1 17533 18741 -54 14 61 teacher2 1493538154 1493538154 188 1082 4164 36287 344 student1 9833 11925 -54 14 61 teacher2 1467537235 1467537235 188 1084 4161 33185 344 student1 9850 11942 -54 14 61 teacher2 1451814869 1451814869 188 1476 4788 40939 344 student1 16905 18392 -54 14 61 teacher2 1485571209 1485571209 188 1081 4163 34249 344 student1 9816 11908 -54 14 61 teacher2 1456643182 1456643182 188 1083 4159 30732 344 student1 9837 11929 -54 13 61 teacher2 1443333588 1443333588

and after

188 1476 4788 41188 571 student2 16905 18392 -54 14 61 teacher2 1485571115 1485571115 188 1081 4163 34282 571 student2 9816 11908 -54 11 61 teacher2 1456643183 1456643183 188 1083 4159 30767 571 student2 9837 11929 -54 11 61 teacher2 1443335098 1443335098 188 1475 4786 40447 571 student2 16904 18391 -54 14 61 teacher2 1483340966 1483340966 188 1512 5045 42473 571 student2 17533 18741 -54 14 61 teacher2 1493538156 1493538156 188 1082 4164 36323 571 student2 9833 11925 -54 14 61 teacher2 1467537235 1467537235 188 1084 4161 33894 571 student2 9850 11942 -54 11 61 teacher2 1454232132 1454232132 188 1082 4164 36287 344 student1 9833 11925 -54 14 61 teacher2 1467537235 1467537235 188 1084 4161 33185 344 student1 9850 11942 -54 14 61 teacher2 1451814869 1451814869 188 1476 4788 40939 344 student1 16905 18392 -54 14 61 teacher2 1485571209 1485571209 188 1081 4163 34249 344 student1 9816 11908 -54 14 61 teacher2 1456643182 1456643182 188 1083 4159 30732 344 student1 9837 11929 -54 13 61 teacher2 1443333588 1443333588 188 1475 4786 40099 344 student1 16904 18391 -54 8 61 teacher2 1483340969 1483340969 188 1512 5045 42275 344 student1 17533 18741 -54 14 61 teacher2 1493538154 1493538154

are the same?

In all of these queries the student1 is the one who was not merged, and student2 is the one who was merged.

The question is, why the grades disappeared in mdl_grade_items table? And it mean's that there are no grades in grades table.

If there is something else you need, I can try to get it? Serenely, Petr.

[jpahullo]

There is subject addressed in this table grade_grades: a compound index:

https://github.com/ndunand/moodle-tool_mergeusers/blob/master/config/config.php#L57

What this line makes is that for the same itemid there will be only a record for both users. This is an invariant the plugin must keep for consistency. If you realize, there are 7 records for both student1 and student2 before the merge, and after that, remains only that from student1 as expected.

Meanwhile, the result from the second query has no sense at all. Are you sure your merging operation went ok? I checked the tables involved in the second query against the config.php file for some other special case, but no match found.

Lastly, I've seen that there is a fieldname in grade_grades that refer to a user.id (usermodified).

Please, try to merge both users again having these settings in a config/config.local.php file:

return array(
    'userfieldnames' => array(
        'grade_grades' => array('userid', 'usermodified'),
    ),
);

if you have this file created, just merge the settings with your current content.

Just let us know.

Jordi

[peterkhl]

Thank you Mr.Jordi. Sorry once again for my late response. I was trying to apply your advice, but no result. Everything left the same. After that I tried to debug the mergeusers pluging and found out, that there is place where grade_update_mod_grades($activity, $toid); function is called for all types of activities. I’ve disabled it for forums and it worked. I don’t know what it does, but grades are now displayed correctly. And I found the way to fix already merged account by this script

update mdl_grade_grades, mdl_grade_grades_2017_08_18, mdl_grade_items set mdl_grade_grades.finalgrade = mdl_grade_grades_2017_08_18.finalgrade where (mdl_grade_grades_2017_08_18.userid = 247 or mdl_grade_grades_2017_08_18.userid = 571) and mdl_grade_items.itemmodule LIKE 'forum' and mdl_grade_grades_2017_08_18.id = mdl_grade_grades.id and mdl_grade_items.id = mdl_grade_grades.itemid

where mdl_grade_grades – is my current grade_grades table mdl_grade_grades_2017_08_18 – is grade_grades copy made before users merge 247 – old user id and 571 – new user id.

What is you opinion about it? Are there any consequences of forum grades update disabling? Serenely, Petr.

[jpahullo]

Thanks Petr for your feedback.

Your SQL script is a good way for those making trials and having a backup on the same database of some tables. This is a good job.

We'll keep an eye on that function, and consider if something may be missing or wrong.

We'll get back to you when we have something new.

Jordi

[peterkhl]

Good Afternoon Mr.Jordi.

At last I found the reason of such strange behavior of our site and mergeusers plugin. In my first posts I did not mention about our problem with our forums after upgrading from moodle 1.9 to moodle 3.1 (we have made 3 transition from 1.9 to 2.2, from 2.2 to 2.8, and from 2.8 to 3.1). The problem was that all the marks in the student and teacher grade tables were displayed correctly, but in the forums itself there were no grades. As it turned out, the fields mdl_rating.component and mdl_rating.ratingarea were empty while there should be values 'mod_forum' and 'post'. After applying this script

update mdl_forum_discussions ,mdl_forum_posts ,mdl_course_modules ,mdl_context ,mdl_rating set mdl_rating.component = 'mod_forum', mdl_rating.ratingarea = 'post' WHERE mdl_context.contextlevel = 70 and mdl_forum_posts.discussion = mdl_forum_discussions.id and mdl_forum_discussions.forum = mdl_course_modules.instance and mdl_context.instanceid = mdl_course_modules.id and mdl_rating.contextid = mdl_context.id AND mdl_rating.itemid = mdl_forum_posts.id and mdl_rating.component like '' and mdl_rating.ratingarea like ''

the grades in forum have appeared.

And I’ve tried to try to merge users after this operation and everything passed well. Sorry for disturbing. Once again thank you for you plugin. Sincerely, Petr.

[jpahullo]

Uoh!!!

These are good news Petr! Congrats for finding the key problem. We also migrated some years ago from 1.9 to 2.4 and it was somehow difficult. Good job.

For this plugin, it seems that the plugin's behaviour is correct.

So, I proposo to close this issue.

Thanks for sharing.

Jordi

[ndunand]

Hi all,

Thanks @jpahullo again for taking the time to look into all this. I agree to close this issue for now.