1.9->2 SQL error: Illegal mix of collations utf8_general_ci utf8_unicode_ci

1.9->2 SQL error: Illegal mix of collations utf8_general_ci utf8_unicode_ci

by kab4 u -
Number of replies: 21

Upgrading to new version

You are not logged in.

(Login)

System Error reading from database

More information about this error

Debug info: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_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 391 of /lib/dml/moodle_database.php: dml_read_exception thrown •line 728 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end() •line 4082 of /lib/db/upgrade.php: call to mysqli_native_moodle_database->get_recordset_sql() •line 1309 of /lib/upgradelib.php: call to xmldb_main_upgrade() •line 252 of /admin/index.php: call to upgrade_core()

In data base I have some tables with utf8_general_ci other are using utf8_unicode_ci

all tables appear in this SELECT are utf8_unicode_ci (for one table I changes char set from utf8_general_ci to utf8_unicode_ci using PhpMyAdmin)

I updated moodle files today - same problem

Average of ratings: Useful (1)
In reply to kab4 u

Re: 1.9->2 SQL error: Illegal mix of collations utf8_general_ci utf8_unicode_ci

by Colin Fraser -
Picture of Documentation writers Picture of Testers

I am not sure what is causing the mix of charsets, it should only be one or the other. To fix this is related to another problem we have been seeing and uses the same fix.

In phpMyAdmin, open the Moodle database and you can enter the SQL statement:

ALTER DATABASE `moodle` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

which changes the entire database.It has been reported though that sometimes the issue persists with individual tables so try

ALTER TABLE `mdl_tablename` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

Between them, both these SQL commands should do what you want them to do.

It should look something like the image below:

 

If you do not have phpMyAdmin then you can go to Modules and Plugins,  download the Moodle 1.9.x version and unzip it to the moodle/admin folder. You will then see Database if you go to Administration > Server so you can directly access your database there. Alternatively, you can just download phpMyAdmin from Sourceforge I think it is, unzip it into your document root folder, the same folder as the Moodle, run it and it automatically detects the databases you are using. Obviously you need to access them, but it is a simple process.

Attachment altertablesqlm19.png
Average of ratings: -
In reply to Colin Fraser

Re: 1.9->2 SQL error: Illegal mix of collations utf8_general_ci utf8_unicode_ci

by kab4 u -

1) Both ALTER commands didn't work so I had to enter table and change each column manually in phpMyAdmin

many tables are in different encoding is there another ALTER I can test?

 

2) now new problem:

Plugin "mod_hotpot" is defective or outdated, can not continue, sorry.

More information about this error

Debug info: Plugin is not compatible with Moodle 2.x or later.
Stack trace:
  • line 425 of /lib/upgradelib.php: plugin_defective_exception thrown
  • line 265 of /lib/upgradelib.php: call to upgrade_plugins_modules()
  • line 1352 of /lib/upgradelib.php: call to upgrade_plugins()
  • line 290 of /admin/index.php: call to upgrade_noncore()
Average of ratings: -
In reply to kab4 u

Re: 1.9->2 SQL error: Illegal mix of collations utf8_general_ci utf8_unicode_ci

by Colin Fraser -
Picture of Documentation writers Picture of Testers

For starters, why are you updating a v1.9.x with a 2.0? There are far too many incompatibilities in the database alone to make it all a nightmare. Best bet at this point is to just create a new install and manually port your courses.

The alter statements should work, they have been used successfully elsewhere so why are they different here? Syntax error? Check the alter database syntax in the documentation guides - it might be a little different than I remember it.

AFAIK, the Hot-pot plugin is not yet ready for Moodle 2.0. This was always going to be an issue for the programming team, do you concentrate on core modules first and then worry about the plugins or do you try a similtaneous/parallel development? One gives you your core program but it means delays on peoples favourite tools, the other means long delays for everything, we would be looking at June next year before releasing Moodle 2.0. Rock and a hard place stuff. In the end, the decision was for the core coding first, get it out there for people to start learning and for some to start building new Moodles and acknowledge that people will not like whatever is presented to them. Anything different is not as good as what we had, which is why, if Henry Ford had his way, we would all be driving Model As and Ts and we would be using Morse code to run the Internet..smile  Give it time and things will happen, but for such a huge project, there is always going to be delays in some areas. Apparently Gordon has been polishing it, that is, getting the last little problems sorted and it is expected within a short space fo time. (I am hanging out for it too..)

EDIT: Just checked the MySQL syntax and it is correct... this falls into the realms of "no idea" for me, perhaps someone else may be able to explain it, sorry...sad Did you give it the right database name? Mine name is Moodle, what's yours? Does the utf8_general_ci charset exist on your server? Check the phpMyAdmin collation charsets list, see if it is there. I can't think of anything else...

Average of ratings: -
In reply to kab4 u

Re: 1.9->2 SQL error: Illegal mix of collations utf8_general_ci utf8_unicode_ci

by Brent Lee -

Good Morning,

I had the same issue with database collations with several databases that I was contracted to upgrade. Here is the solution that works every time:

Dump Database to SQL File:

Open Database in VI and issue the following commands:

Change Charset with VIU

:%s/CHARSET=utf8/CHARSET=utf8 COLLATE utf8_unicode_ci

Change Database Type: ENGINE=MyISAM

:%s/MyISAM/InnoDB

These two VI commands replace one piece of data with another.

Cheers,

Brent.

Average of ratings: -
In reply to Brent Lee

Re: 1.9->2 SQL error: Illegal mix of collations utf8_general_ci utf8_unicode_ci

by Jason Githeko -

Is it possible to explain this in general terms without using VI commands? What string needs to be replaced with what?

I am trying to solve the same upgrade problem. 

Thanks for your time.

Average of ratings: -
In reply to Colin Fraser

Re: 1.9->2 SQL error: Illegal mix of collations utf8_general_ci utf8_unicode_ci

by Todd Carney -

Colin--I'm having many of the same problems as others are in upgrading 1.9 > 2.0.  I'm upgrading because when I tried to copy an existing course to a new one, 1.9 came back with an unzip error when I tried to "restore" the backup copy.  On this forum I found that 2.0 uses a better UNZIP program that won't choke. Unfortunately, I did not know about Moodle's internal upgrade functions and I used Fantastico.  See my earlier posting for the details.

I've found all 198 tables in my Moodle database are set to the latin_swedish_ci collation.  I tried your suggestion to use phpMyAdmin to (shown below with my actual database name):

ALTER DATABASE 'tcarney_mdle1' DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

In response, I got no errors and phpMyAdmin reported okay. But when I look at the database, every table is still showing latin_swedish_ci.  I pasted in the line from your example, so I don't syntax is an issue.

You also gave syntax to change an individual table within the database, but there are 198 tables! Using phpMyAdmin, is there a way I can select all the tables and enter an sql command to change them as a batch?

Many thanks!

Average of ratings: -
In reply to Todd Carney

Re: 1.9->2 SQL error: Illegal mix of collations utf8_general_ci utf8_unicode_ci

by Colin Fraser -
Picture of Documentation writers Picture of Testers

Going back over the SQL statements and finding them right, got me thinking a bit harder.. a real challenge while in holiday mode I can tell you tongueout.

Instead of using the SQL as given, try a slightly different track... MySQL allows for a different naming process for tables and databases... while I have never tried this, I understand that MySQL have been using it for a while so they may be giving preference for the generic name "schema" instead.Try:

ALTER SCHEMA 'tcarney_mdle1' DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

I suspect that if you are in the database then you may not even need the database name, just

ALTER SCHEMA DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

This is bothering me, I know it has worked as originally given, and now it seems it is not, je ne comprend pas!!!!!

I am out of ideas after this, guys, sorry, perhaps Mauno or Howard my be able to sort it... feeling a bit like I have laid an egg !!!!

Average of ratings: -
In reply to Colin Fraser

Re: 1.9->2 SQL error: Illegal mix of collations utf8_general_ci utf8_unicode_ci

by Colin Fraser -
Picture of Documentation writers Picture of Testers

Yet another thought:

ALTER SCHEMA 'yourdbname' DEFAULT CHARACTER SET utf-8 COLLATE utf8_general_ci;

The dash has never been an issue in the past, so there is no reason to think it may be now... however, reading other posts of error messages relating to this problem the dash has been included.

and Todd.. mmm I know it is all supposed to be dynamic, and I have seen this before but...

EDIT: I have been testing the SQL and the table syntax worked like this:

ALTER TABLE `mdl_backup_files` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

Notice that the quote marks are NOT single quotes, but they are the reverse quote under the tilde key... might make a difference...

 

Average of ratings: -
In reply to Colin Fraser

Re: 1.9->2 SQL error: Illegal mix of collations utf8_general_ci utf8_unicode_ci

by Colin Fraser -
Picture of Documentation writers Picture of Testers

Been testing the DATABASE syntax and while it does not display an error, it does not make the change either. This is in phpmyadmin.. mmm might just try command line, haven't been there for absolute ages.. so might make a mess of my database mmm would not be the first time.smile

Average of ratings: -
In reply to Colin Fraser

Re: 1.9->2 SQL error: Illegal mix of collations utf8_general_ci utf8_unicode_ci

by Colin Fraser -
Picture of Documentation writers Picture of Testers

Well, almost everything survived that..smile The dash does not work, just tosses an error. Seems there is an issue with the GRANT when using phpMyAdmin. There is probably somewhere in the security routines a bit of code that prevents altering the database, which means there needs to be either a review or  perhaps a hack to allow the ROOTUSER to alter the entire database. In the command line the Alter table worked, but the Alter database just tossed error after error - usually telling me the syntax was not matching the version - but as it came out of their manual, I am not sure what is going on. Sorry guys, really need someone a lot more current than me I am afraid... And while writing this, I suspect that this syntax will just breeze through a Moodle 1.9.x... mmmm testing testing... but maybe not...

Average of ratings: -
In reply to Colin Fraser

Re: 1.9->2 SQL error: Illegal mix of collations utf8_general_ci utf8_unicode_ci

by kab4 u -

I changed all my db manually to utf8_unicode_ci and bot utf8_general_ci

it will behave diferently at comparing strings - is this a problem?

[This is because most of my old DB was utf8_unicode_ci and I changed it manually]

Average of ratings: -
In reply to kab4 u

Re: 1.9->2 SQL error: Illegal mix of collations utf8_general_ci utf8_unicode_ci

by Colin Fraser -
Picture of Documentation writers Picture of Testers

I initially understood it was could be either, but I noted that all the Moodle 2.x I have seen are all utf8_general_ci ... I do not understand the difference apart from a number of characters so I doubt it will make too much of an issue intially, but as the database becomes larger, then it may very well be. Things are likely to expand exponentially as all courses are now stored in the db rather than moodledata folder.

Average of ratings: -
In reply to kab4 u

Re: 1.9->2 SQL error: Illegal mix of collations utf8_general_ci utf8_unicode_ci

by Evan ODonnell -

I'm having the same problem I think.  I've tried Colin's mysql commands to alter the database and tables, and get the same 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 391 of /lib/dml/moodle_database.php: dml_read_exception thrown
  • line 728 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()
  • line 4082 of /lib/db/upgrade.php: call to mysqli_native_moodle_database->get_recordset_sql()
  • line 1309 of /lib/upgradelib.php: call to xmldb_main_upgrade()
  • line 252 of /admin/index.php: call to upgrade_core()
Average of ratings: -
In reply to Evan ODonnell

Re: 1.9->2 SQL error: Illegal mix of collations utf8_general_ci utf8_unicode_ci

by Mike Grew -

I'm getting the same error. I've tried changing those tables listed in the error individually, and phpmyadmin shows that they're utf8_general now, but the error persists. Changing the whole database seems to be quite difficult. Might end up copying and pasting every table into a massive query that changes each one individually.

UPDATE: Changed every table individually. Problem persists. The whole database is definitely general now, but I still get the utf8_unicde issue above.

Average of ratings: -
In reply to Mike Grew

Re: 1.9->2 SQL error: Illegal mix of collations utf8_general_ci utf8_unicode_ci

by kab4 u -

change only the table needed

you chnagend only the table default

you need to chnage each field of table!!

Average of ratings: -
In reply to kab4 u

Re: 1.9->2 SQL error: Illegal mix of collations utf8_general_ci utf8_unicode_ci

by Matteo Scaramuccia -
Picture of Core developers Picture of Peer reviewers Picture of Plugin developers

Hi All,

you could take a look at Phoca Changing Collation Tool, already described in some previous posts about solving the Moodle-on-MySQL collation issue and at MDL-24845, maybe starting from here to see a proposal to add something within 1.9 to address the collation issue which is something affecting many "standard" (i.e. using the suggested collation settings) MySQL based Moodle installations.

Beware: do NOT try it on your production system, test it on a cloned system - i.e. cloning wwwroot, dataroot, db, CHANGING wwwroot/config.php to point to the just cloned test resources! - derived from your production.

HTH,
Matteo

Average of ratings: -
In reply to Matteo Scaramuccia

Re: 1.9->2 SQL error: Illegal mix of collations utf8_general_ci utf8_unicode_ci

by Evan ODonnell -

Matteo - thank you!  This did the trick for me.  Supper easy to use as well:

http://www.phoca.cz/download/category/17-phoca-changing-collation-tool

Average of ratings: -
In reply to Evan ODonnell

Re: 1.9->2 SQL error: Illegal mix of collations utf8_general_ci utf8_unicode_ci

by Jay Pfaffman -

That worked for me too. Thanks.

I don't usually post a "me too," but this one saved me. Thanks.

Average of ratings: -
In reply to Evan ODonnell

Re: 1.9->2 SQL error: Illegal mix of collations utf8_general_ci utf8_unicode_ci

by Jay Pfaffman -

That worked for me too. Thanks.

I don't usually post a "me too," but this one saved me. Thanks.

Average of ratings: -
In reply to kab4 u

Re: 1.9->2 SQL error: Illegal mix of collations utf8_general_ci utf8_unicode_ci

by Chad Outten -

beyond the sql queries suggested by colin, as kab4u suggests, you also need to convert the collation of table fields. as per the debug info provided by colin, try run the following queries...

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

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

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

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

---

HTH

Average of ratings: -
In reply to Mike Grew

Re: 1.9->2 SQL error: Illegal mix of collations utf8_general_ci utf8_unicode_ci

by Keith Bloom -

I very much wish now I had documented my fix for this some months ago, however kab4 blelow is correct. Changing the default is not enough, you must update the existing records to complete the fix.

It took a while to work through the full retinue of tables affected, but it does turn out successful in the end.

I believe there is also a note in the PHP/MySQL blogosphere about how MySQLdump can instigate this problem or further it during a server migration. Since I was using MySQL dump regularly in development to reload production data to my development server, I think I ended up updating the default collations on my 1.9.9 production install as well.

Keith

Average of ratings: -