Hi,

I am trying to create a page in Views for an OpenLayermap I have created. When trying to save the views page I get the following error message. I have already fully tested the Openlayers map in preview and it works perfect. I can though not determine if it is a problem in views as the message below refer more to issues with Ctools

Error message

PDOException: SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '=': SELECT s.uid, c.updated FROM {ctools_object_cache} c INNER JOIN {sessions} s ON c.sid = s.sid WHERE s.sid <> :session_id AND c.obj = :obj AND c.name = :name ORDER BY c.updated ASC; Array ( [:session_id] => V3jZ-jr-ImopL2vSPO1MZrLi46cYV8ReDQN6Mwne2zY [:obj] => view [:name] => might_this_work ) in ctools_object_cache_test() (line 145 of /home/sm7pkk/public_html/drupal/sites/all/modules/ctools/includes/object-cache.inc).
The website encountered an unexpected error. Please try again later.

Comments

merlinofchaos’s picture

merlinofchaos CreditAttribution: merlinofchaos commented
Status: Active » Closed (won't fix)

The problem is in your installation.

What this error message means is that you have managed to install your Drupal using different collations on tables. One or some of your tables are utf8_general_ci while one or some of your tables are utf8_unicode_ci -- and once you've done this, those tables cannot be joined together.

By default, modules install their tables with whatever the mysql server default says to; we do not specify the collation. So this can happen if the collation changes or if someone has gone in and changed the collation on tables without changing in the default.

This is something you will have to fix locally. Do a google search on collations and the two collations above, and I believe you will be able to find proper advice on doing so.

matsglobetrotter’s picture

matsglobetrotter CreditAttribution: matsglobetrotter commented

Thanks for the information most useful :-)

It is a bit strange that such would happen as I due to the error completely re-did the full installation from scratch. I also ensured the setup created the entries towards the SQL database. Thus no such collation was never touched.

I have now installed everything again and can get very nice open layer maps working with the modules, Ctools, Open Layers, Views. The error seems thus to come if I install geofield, geophp modules which I used to mark some positions. Seems I can do a better job with the internal kml module in openlayers for that though.

gagallo7’s picture

gagallo7 CreditAttribution: gagallo7 commented

I solved this error by correcting the MySQL query created in the object-cache.inc (located /sites/all/modules/ctools/includes/object-cache.inc).
That query mixes different collations, which is not supported by MySQL.
Add the BINARY operator before table comparisons to correct this.

Thus replace the line 145 from object-cache.inc with:

return db_query('SELECT s.uid, c.updated FROM {ctools_object_cache} c INNER JOIN {sessions} s ON BINARY c.sid = BINARY s.sid WHERE s.sid <> :session_id AND BINARY c.obj = BINARY :obj AND BINARY c.name = BINARY :name ORDER BY c.updated ASC', array(':session_id' => $sid, ':obj' => $obj, ':name' => $name))
jessZ’s picture

jessZ CreditAttribution: jessZ commented
Issue summary: View changes

I have this same error and I am unable to create any views (or use the sample views). Are you confirming that there is a bug in ctools or is this a unique fix for your installation only. The broader solution is te re-do the collations in the dbase but I am fairly sure all my tables are utf8_unicode_ci so I don't really want to run the script succested in
https://www.drupal.org/node/251938 is this the same issue? I followed the suggested change in #3 above and it worked for me. I don't know squat about php or mysql so i'm doing it on faith.

PDOException: SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '=': SELECT s.uid, c.updated FROM {ctools_object_cache} c INNER JOIN {sessions} s ON c.sid = s.sid WHERE s.sid <> :session_id AND c.obj = :obj AND c.name = :name ORDER BY c.updated ASC; Array ( [:session_id] => cyGgQj_7dXvBWdlMWx3QdICZDS2mHC0o8FupGegqQjw [:obj] => view [:name] => test ) in ctools_object_cache_test() (line 145 of /home/reimagi8/public_html/new/sites/all/modules/ctools/includes/object-cache.inc).
The website encountered an unexpected error. Please try again later.