Upgrading 1.9.16 to 2.2.x --- Error reading from Database

Upgrading 1.9.16 to 2.2.x --- Error reading from Database

by Aaron Hockett -
Number of replies: 13

Hello all,

Well in testing of getting our college up to the latest and greatest version of Moodle, I'm in the final phases of testing and getting us up-to-date on Moodle 2.x.  Right now I'm experiencing an issue where I get the following error:

Debug info: Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='

SELECT DISTINCT c.id AS courseid, ra.enrol, c.timecreated, c.timemodified
FROM mdl_course c
JOIN mdl_context ctx ON (ctx.instanceid = c.id AND ctx.contextlevel = 50)
JOIN mdl_role_assignments ra ON (ra.contextid = ctx.id AND ra.enrol <> '')
LEFT JOIN mdl_enrol e ON (e.courseid = c.id AND e.enrol = ra.enrol)
WHERE c.id <> ? AND e.id IS NULL
[array (
0 => '1',
)]
Stack trace:
  • line 394 of /lib/dml/moodle_database.php: dml_read_exception thrown
  • line 768 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()
  • line 4051 of /lib/db/upgrade.php: call to mysqli_native_moodle_database->get_recordset_sql()
  • line 1406 of /lib/upgradelib.php: call to xmldb_main_upgrade()
  • line 236 of /admin/index.php: call to upgrade_core()[

 

So I'm trying to determine if this is a permissions in the call to the MySQL database or if it simply cannot find what it's looking for.

The picture below offers a better view.  Thanks for the help!

 

-Aaron

Attachment moodle2.xupgrade.PNG
Average of ratings: -
In reply to Aaron Hockett

Re: Upgrading 1.9.16 to 2.2.x --- Error reading from Database

by Alex Saavedra -

Hello Aaron,

Just make sure that collation of the following columns match:

  • mdl_role_assignments.enrol
  • mdl_enrol.enrol

Right now it seems one of them is utf8_unicode_ci and the other one is utf8_general_ci. Just make them match.

A. Saavedra

Average of ratings: -
In reply to Alex Saavedra

Re: Upgrading 1.9.16 to 2.2.x --- Error reading from Database

by Aaron Hockett -

I'm going through the dump file now and seeing if I can just replace it using the UTF8 guide posted below.  Once that's done, I'm sure I can re-import the DB and re-run the update.  I'll let you know.

Average of ratings: -
In reply to Aaron Hockett

Re: Upgrading 1.9.16 to 2.2.x --- Error reading from Database

by Aaron Hockett -

So when I look for utf8_general_ci in the DB, the pattern is not found.  Now I'm somewhat stumped.

Average of ratings: -
In reply to Aaron Hockett

Re: Upgrading 1.9.16 to 2.2.x --- Error reading from Database

by Alex Saavedra -

Chances are it is inheriting this property from 1.9.16 database current structure. I suggest changing collation in affected columns before the upgrade.

A. Saavedra

Average of ratings: -
In reply to Aaron Hockett

Re: Upgrading 1.9.16 to 2.2.x --- Error reading from Database

by Guillermo Madero -

To do the UTF8 conversion:

http://docs.moodle.org/22/en/Converting_your_MySQL_database_to_UTF8

Average of ratings: -
In reply to Guillermo Madero

Re: Upgrading 1.9.16 to 2.2.x --- Error reading from Database

by Aaron Hockett -

Bumping this as I followed this guide and I'm still getting this error:

 

Debug info: Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='
SELECT DISTINCT c.id AS courseid, ra.enrol, c.timecreated, c.timemodified
FROM mdl_course c
JOIN mdl_context ctx ON (ctx.instanceid = c.id AND ctx.contextlevel = 50)
JOIN mdl_role_assignments ra ON (ra.contextid = ctx.id AND ra.enrol <> '')
LEFT JOIN mdl_enrol e ON (e.courseid = c.id AND e.enrol = ra.enrol)
WHERE c.id <> ? AND e.id IS NULL
[array (
0 => '1',
)]
Stack trace:
  • line 394 of /lib/dml/moodle_database.php: dml_read_exception thrown
  • line 768 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()
  • line 4051 of /lib/db/upgrade.php: call to mysqli_native_moodle_database->get_recordset_sql()
  • line 1394 of /lib/upgradelib.php: call to xmldb_main_upgrade()
  • line 236 of /admin/index.php: call to upgrade_core()

This is getting absurd.

Average of ratings: -
In reply to Aaron Hockett

Re: Upgrading 1.9.16 to 2.2.x --- Error reading from Database

by steve miley -

Aaron - a temporary workaround might be for you to issue some alter commands on the database,  just change your table names before you do it (from my paste below)

 

ALTER TABLE `mdl_19_course` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

 
ALTER TABLE `mdl_19_role_context` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
 
ALTER TABLE `mdl_19_role_assignments` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
 
ALTER TABLE `mdl_19_course_modules` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
Average of ratings: -
In reply to steve miley

Re: Upgrading 1.9.16 to 2.2.x --- Error reading from Database

by Aaron Hockett -

Steve,

I'm in MySQL and have issued these commands on my Moodle DB:

ALTER TABLE `mdl_course` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

ALTER TABLE `mdl_wiki` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

ALTER TABLE `mdl_context` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

ALTER TABLE `mdl_enrol` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

Error I'm getting now: (post those commands in the upgrade process)

[code]

Debug info: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='
SELECT po.id AS oldpage_id, po.pagename AS oldpage_pagename, po.version, po.flags,
po.content, po.author, po.userid AS oldpage_userid, po.created, po.lastmodified, po.refs, po.meta, po.hits, po.wiki,
p.id AS newpage_id, p.subwikiid, p.title, p.cachedcontent, p.timecreated, p.timemodified AS newpage_timemodified,
p.timerendered, p.userid AS newpage_userid, p.pageviews, p.readonly, e.id AS entry_id, e.wikiid, e.course AS entrycourse,
e.groupid, e.userid AS entry_userid, e.pagename AS entry_pagename, e.timemodified AS entry_timemodified,
w.id AS wiki_id, w.course AS wiki_course, w.name, w.summary AS summary, w.pagename AS wiki_pagename, w.wtype,
w.ewikiprinttitle, w.htmlmode, w.ewikiacceptbinary, w.disablecamelcase, w.setpageflags, w.strippages, w.removepages,
w.revertchanges, w.initialcontent, w.timemodified AS wiki_timemodified,
cm.id AS cmid
FROM mdl_wiki_pages_old po
LEFT OUTER JOIN mdl_wiki_entries_old e ON e.id = po.wiki
LEFT OUTER JOIN mdl_wiki w ON w.id = e.wikiid
LEFT OUTER JOIN mdl_wiki_subwikis s ON e.groupid = s.groupid AND e.wikiid = s.wikiid AND e.userid = s.userid
LEFT OUTER JOIN mdl_wiki_pages p ON po.pagename = p.title AND p.subwikiid = s.id
JOIN mdl_modules m ON m.name = 'wiki'
JOIN mdl_course_modules cm ON (cm.module = m.id AND cm.instance = w.id)
[array (
)]
Stack trace:
  • line 394 of /lib/dml/moodle_database.php: dml_read_exception thrown
  • line 768 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()
  • line 226 of /mod/wiki/db/upgradelib.php: call to mysqli_native_moodle_database->get_recordset_sql()
  • line 175 of /mod/wiki/db/upgrade.php: call to wiki_upgrade_migrate_versions()
  • line 540 of /lib/upgradelib.php: call to xmldb_wiki_upgrade()
  • line 271 of /lib/upgradelib.php: call to upgrade_plugins_modules()
  • line 1437 of /lib/upgradelib.php: call to upgrade_plugins()
  • line 269 of /admin/index.php: call to upgrade_noncore()[/code]

Looks like mdl_wiki_pages is not having an issue. :/

 

 

Average of ratings: -
In reply to Aaron Hockett

Re: Upgrading 1.9.16 to 2.2.x --- Error reading from Database

by Aaron Hockett -

Holy crap.

So I did alter on the wiki pages, hit continue, upgrade to another point, got a "You do not have permission to update the calendar".  Permission issue, figured... Hmm, why not try and login locally as the admin and try re-running the script. 

 

BAM! 

Re-ran the script with correct permissions and my site is up.  I think this has been THE most painful upgrade process I have ever encountered.

Average of ratings: -
In reply to Aaron Hockett

Re: Upgrading 1.9.16 to 2.2.x --- Error reading from Database

by steve miley -

remember, this is just a workaround , not the real solution -  try these

 

ALTER TABLE `mdl_19_modules` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE `mdl_19_wiki_pages` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE `mdl_19_wiki_subwikis` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE `mdl_19_wiki` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE `mdl_19_wiki_entries_old` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE `mdl_19_wiki_pages_old` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
Average of ratings: -
In reply to steve miley

Re: Upgrading 1.9.16 to 2.2.x --- Error reading from Database

by Aaron Hockett -

Hey Steve,

Silver bullet to my problems:

mysql -u root -p

On Moodle database:

ALTER SCHEMA DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

That will convert the old DB to the new character set and allow an earlier version of 1.9.x to be upgraded to 1.9.18+  Turns out Moodle dev's are FORCING the DB conversion before upgrading to 1.9.18+ now so running this is vital for the upgrade to happen.  I'm happy once I get our DB up to 2.x then I won't ever have to deal with this again.

Average of ratings: -
In reply to steve miley

Re: Upgrading 1.9.16 to 2.2.x --- Error reading from Database

by Paul Nijbakker -

Hi,

We run into the same error when trying to upgrade. However, we have run a script to turn the entire database to utf8_general_ci. I cannot track down any other collation in the database (but then again, I cannot see any tables called mdl_wiki_entries_old or mdl_wiki_pages_old either.

Rgrds,
Paul.

Average of ratings: -