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 NULLDebug 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 378 of /lib/dml/moodle_database.php: dml_read_exception thrown
• line 591 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()
• line 4057 of /lib/db/upgrade.php: call to mysqli_native_moodle_database->get_recordset_sql()
• line 1247 of /lib/upgradelib.php: call to xmldb_main_upgrade()
• line 248 of /admin/index.php: call to upgrade_core()
[array (
0 => '1',
)]
Stack trace:
• line 378 of /lib/dml/moodle_database.php: dml_read_exception thrown
• line 591 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()
• line 4057 of /lib/db/upgrade.php: call to mysqli_native_moodle_database->get_recordset_sql()
• line 1247 of /lib/upgradelib.php: call to xmldb_main_upgrade()
• line 248 of /admin/index.php: call to upgrade_core()
I've read up on the issue and it seems like there is a problem with my old imported 1.9.8 database. I tried to change the collation of the offending tables listed in the debug statement using the alter table syntax:
ALTER TABLE xxx DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
This didn't fix my problem so I tried changing the collation on all of the tables in my moodle database. I used the following perl script to do so. (I know it's ugly, no comments)
#!/usr/bin/perl
# Script to change the collation of all the moodle tables at once.
#Read in the Database password
print "Enter DB Password";
system("stty -echo");
$DBPASS = <STDIN>;
system("stty echo");
chomp($DBPASS);
#Find all fragmented tables
$TABLES=`mysql -u root -p'$DBPASS' -N -e 'use information_schema; select TABLE_NAME from TABLES where TABLE_SCHEMA="moodle";' | grep -v "^+"`;
@TABLES=split(/\n/, $TABLES);
# Prompt the User to continue
print "\n\nThe following tables will be changed to a character set of utf8 and a collation of utf8_unicode_ci: \n";
print $TABLES;
print "Do you want to continue (y/n)? ";
$CONTINUE = <STDIN>;
chomp($CONTINUE);
#print "\nInput Value: " . $CONTINUE;
while ($CONTINUE ne "y" && $CONTINUE ne "n") {
print "Please enter \"y\" or \"n\"";
chomp($CONTINUE = <STDIN>);
}
$COMMAND = "mysql -u root -p'$DBPASS' -N -e 'use moodle; ";
if ($CONTINUE eq "y") {
foreach $TABLENAME (@TABLES) {
$SQL = $SQL . "ALTER TABLE $TABLENAME DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci; ";
}
$COMMAND = $COMMAND . $SQL . "'";
#print $COMMAND;
$RESULT = `$COMMAND`;
}
else {
print "Quitting...\n";
exit;
}
exit;
This didn't fix my problem either. Has anyone else experienced this problem? Also, is my method of changing the table collation correct, or is there another way I should go about it. Thanks.
-Will
