I've installed version 4.6 many times with Fantistico and never had a problem...

I tried to 4.7 up with Fantastico for a friend today and got numerous errors...

Here are some of the errors:

Warning: Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation 'like' query: SELECT * FROM access WHERE status = 1 AND type = 'host' AND LOWER('IP ADDRESS) LIKE LOWER(mask) in /home/my server/public_html/xxxxxx/includes/database.mysql.inc on line 120

Warning: Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation 'like' query: SELECT * FROM access WHERE status = 0 AND type = 'host' AND LOWER('IP ADDRESS') LIKE LOWER(mask) in /home/my server/ public_html/xxxx/includes/database.mysql.inc on line 120

warning: Cannot modify header information - headers already sent by (output started at /home/xxxxxxx/public_html/xxxxxx/includes/database.mysql.inc:120) in /home/xxxxxxx/public_html/xxxxxx/includes/common.inc on line 139.

Does anyone know what I need to do to fix this...?

Thanks in advance... :)

Comments

Rosamunda’s picture

I had the same problem.
This is what I´ve done and everything went just fine (until now :)

The problem came because I´ve installed Drupal 4.7 via Fantastico on MySQL 4.1 instead of 4.0. I don´t know if the issue cames because of Drupal or fantástico, because I´ve tried the same without Fantastico, and have the same problem. Apparently is a "collate" problem, the database doesn´t get that you want all the tables on utf8_general.

So: You have to enter phpMyAdmin and enter the table where you have installed Drupal.

1. Enter to the structure of "access" and "user" tables and change the collation to from latin_swedish to utf8_general.
This way you avoid that errors and can enter the site without problems.

2. To change collations of all tables click on "SQL" to write this commands:

ALTER TABLE `access` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `accesslog` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `aggregator_category` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `aggregator_category_feed` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `aggregator_category_item` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `aggregator_feed` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `aggregator_item` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `authmap` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `blocks` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `book` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `boxes` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `cache` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `client` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `client_system` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `comments` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `contact` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `file_revisions` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `files` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `filter_formats` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `filters` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `flood` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `forum` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `history` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `locales_meta` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `locales_source` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `locales_target` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `menu` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `node` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `node_access` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `node_comment_statistics` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `node_counter` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `node_revisions` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `permission` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `poll` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `poll_choices` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `poll_votes` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `profile_fields` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `profile_values` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `role` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `search_dataset` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `search_index` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `search_total` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `sequences` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `sessions` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `system` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `term_data` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `term_hierarchy` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `term_node` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `term_relation` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `term_synonym` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `url_alias` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `users` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `users_roles` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `variable` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `vocabulary` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `vocabulary_node_types` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `watchdog` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

Enter again to your site again... and voilá! Everything is working!

Hope that helps, I went crazy with that problem!

Rosamunda
Buenos Aires | Argentina
www.ligadelconsorcista.org

pj@plainjaingraphics.com’s picture

I did what you suggested in Step 1:

1. Enter to the structure of "access" and "user" tables and change the collation to from latin_swedish to utf8_general.

This seems to have fixed it so far...

What about Step 2? Is it necessary to do that also or will I be okay just doing Step 1? :)

Rosamunda’s picture

After step 1º, all seemed fixed.
I´ve done it "just in case".
;-)

Rosamunda
Buenos Aires | Argentina
www.ligadelconsorcista.org

pj@plainjaingraphics.com’s picture

and I'm still getting errors in the log file, like:

hp 05/18/2006 - 11:17pm Illegal mix of collations (latin1_swedish_ci,IMPLICIT) ...

*sigh* I just uninstalled Drupal from my site... I don't understand why I'm getting these errors or really how to fix them...

Rosamunda’s picture

Let´s see if I can explain this better (english isn´t my mother´s tongue :)

1. Enter PHPMyAdmin (you can do that fron cPanel)
2. Search for your Drupal Database and select it.
3. Once selected it, you´ll see at the top right area of the screen a row of links called: Structure - SQL - Search - Query - Export - Operations. Besides that row there´s a list of all the drupal tables of your database.
4. Click on the "SQL" link.
5. You´ll see a texarea to write on it, that says: "Run SQL query/queries on database your-database-name:"
6. Copy and pase all the code that I´ve posted on my first post.
7. Click the "go" button
That´s it.

The code that I´ve posted serves to change the collation of all your Drupal database tables. Because it seems that it is there the problem.

You must change each table from latin_swedish to utf8. I even enter each table and checked it, one by one to be sure. And there are +50 tables. So, to made the job easier you can copy and paste the code.

I´m not a programmer, but that´s what I´ve done to make it work properly. And everything seems fine now due to "intensive" tests that I´ve made to check if Drupal was finally working.

Hope this helps... :)

Rosamunda

Rosamunda
Buenos Aires | Argentina
www.ligadelconsorcista.org

wildsofwales’s picture

Thanks for the script Rosamunda

MySQL version 4.1.14-standard-log

I did a new 4.7 install via fantastico - same errors as OP.
Did stage 1 for the tables & Run the sql script you gave ... Tables updated to collation utf8_general_ci

No difference - same errors.

Then manually updated the fields within the access and user tables. The script does all the tables but the fields within these 2 tables I had to do manually after running the script. Not sure why, but ok now

Many Thanks for your help here...

TomArah’s picture

Thanks for your help Rosamunda. To begin with I thought that your Step 2 incorporated and extended Step 1, but it doesn't stop the errors. The crucial step seems to be using PHPAdmin Structure to manually change the Access table's Mask and and the Users table's Name field's collation type from latin1_swedish_ci to utf8_general_ci . What I'm not sure of if is if it's also a good idea to change the other Access and Users fields' collation settings, or to run step 2 come to that (while this changes each table's overall setting all the individual fields seem to keep their own latin1_swedish_ci collation). Maybe it's best to keep changes to a minimum to avoid future potential problems?

All pretty horrible and just what Fantastico installation is meant to prevent. I might just leave it for a week or so to see if Fantastico or my ISP can fix it.

pj@plainjaingraphics.com’s picture

I'm with you - I'll just wait to see if it can get fixed before I install it... I did steps 1 and 2 and still got a page full of errors in the Logs... I don't want to start off with a bad install... dangit, I was looking forward to setting up this new site... *sigh* oh well...

Rosamunda’s picture

I´ve started a topic at the Fantastico Forum, but they seem to think that it´s Drupal or Cpanel´s fault.
We´ll have to wait and see what happens.
Anyway, I didn´t get another error... yet :)

Did anyone of you try and install manually Drupal 4.7? Because I did and get the same errors...

Rosamunda
Buenos Aires | Argentina
www.ligadelconsorcista.org

pj@plainjaingraphics.com’s picture

I guess I could take the time to figure it out... I might do that sometime in the next few days...

Are you saying that when you look at Logs in the admin section you didn't see any errors at all...?

Rosamunda’s picture

Yep, I can enter normally.
But I´ve entered again at phpmyadmin trying to remember what else I could have done...
And I´ve made a 3º step:
I´ve entered each single table and changed all fields´s collations. Yeah, It was a hard work, but I have no error messages since then (not a single one), and I´ve completed the task in 45 min only.

BTW, installing Drupal is easy, you upload all files, create a folder named "files" and CHMOD 777 (inside the drupal root installation), and configure the data in sites/default/settings.php according to the database were you´ve installed Drupal.
The database file to upload to your drupal database (via phpmyadmin) is inside "database" folder.
And there you have :)

Rosamunda

gjaxson01’s picture

Okay, I am still having some trouble. I followed the instructions and all my fields changed. But I am still getting the same errors.

I am setting this up for a friend. So it is not mission critical for me, but my friend thinks everything is a crisis.
Thanks,
Garrett

Site: http://www.warriorcaste.com
Errors


Warning: Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation 'like' query: SELECT * FROM access WHERE status = 1 AND type = 'host' AND LOWER('70.240.49.102') LIKE LOWER(mask) in /home/threesix/public_html/warriorcaste/includes/database.mysql.inc on line 120

Warning: Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation 'like' query: SELECT * FROM access WHERE status = 0 AND type = 'host' AND LOWER('70.240.49.102') LIKE LOWER(mask) in /home/threesix/public_html/warriorcaste/includes/database.mysql.inc on line 120

Second set of errors.


# warning: Cannot modify header information - headers already sent by (output started at /home/threesix/public_html/warriorcaste/includes/database.mysql.inc:120) in /home/threesix/public_html/warriorcaste/includes/common.inc on line 139.
# user warning: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '=' query: SELECT * FROM users WHERE status = 1 AND name = LOWER('admin') in /home/threesix/public_html/warriorcaste/includes/database.mysql.inc on line 120.
# user warning: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '=' query: SELECT * FROM users WHERE status = 0 AND name = LOWER('admin') in /home/threesix/public_html/warriorcaste/includes/database.mysql.inc on line 120.
# user warning: Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation 'like' query: SELECT * FROM access WHERE status = 1 AND type = 'user' AND LOWER('admin') LIKE LOWER(mask) in /home/threesix/public_html/warriorcaste/includes/database.mysql.inc on line 120.
# user warning: Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation 'like' query: SELECT * FROM access WHERE status = 0 AND type = 'user' AND LOWER('admin') LIKE LOWER(mask) in /home/threesix/public_html/warriorcaste/includes/database.mysql.inc on line 120.
# user warning: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '=' query: SELECT * FROM users u WHERE LOWER(name) = LOWER('admin') AND pass = 'fff5d4e611bba146b2063d0e9e66ad7b' AND status = 1 in /home/threesix/public_html/warriorcaste/includes/database.mysql.inc on line 120.
gjaxson01’s picture

My web server has not yet updated all of the software required by drupal, ie. PHP and MySQL

THanks.
Garrett

Rosamunda’s picture

Hi again.
Happy that the problem didn´t was in Drupal at all.
What was your server´s lack of software? what didn´t they installed?

Hope they fix that soon!!

Rosamunda
Buenos Aires | Argentina
www.ligadelconsorcista.org

M.J. Taylor’s picture

The above doesn't always fix the collation problem with all hosting firms. In some cases certain of the field structures are modified with a "character set latin1" addition by running the above patch. Example:

===before===

CREATE TABLE `access` (
  `aid` tinyint(10) NOT NULL auto_increment,
  `mask` varchar(255) NOT NULL default '',
  `type` varchar(255) NOT NULL default '',
  `status` tinyint(2) NOT NULL default '0',
  PRIMARY KEY  (`aid`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;

===after===

CREATE TABLE `access` (
  `aid` tinyint(10) NOT NULL auto_increment,
  `mask` varchar(255) character set latin1 NOT NULL default '',
  `type` varchar(255) character set latin1 NOT NULL default '',
  `status` tinyint(2) NOT NULL default '0',
  PRIMARY KEY  (`aid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

The fix we used, (possible not correct, so someone with a much greater level of MySQL knowledge reviewing this would be appreciated) is below.

Important! This is only for a brand new, Fantastico (Auto-Install). And assumes you can navigate your way around Fantastico and phpMyAdmin.

- Install Drupal through Fantastico
- Go into phpMyAdmin and apply the above 57 "ALTER TABLE" (cut and paste works great)
- "Export" the entire Database with "Add DROP TABLE" and "Complete inserts" and "Save as file" to your local system
- Open the exported file and replace "character set latin1 " with nothing
- [Optional: Delete the "INSERT INTO `cache`" lines. We had to as they were causing MySQL to throw errors over line length. Losing cache data at this point won't hurt anything.]
- Under the phpMyAdmin "SQL" tab click "Chose (Location of the text file:)" find the file you altered and click (the lower) "Go"

And, if you have no other oddities with your hosting firm, your site should now re-load cleanly.

Best Regards,

M.J. Taylor
Publisher
from Reason to Freedom
Weekly libertarian magazine: Reason is a natural life-giving activity of the mind. We regard independent thinking as the most important virtue, as well

mfitch’s picture

Thanks for posting the export / edit / import sql - that worked for me!

alexis’s picture

Hello, as an alternative I did this:

1. Used mysqldump to create a SQL to recreate the database:

mysqldump -u user -p mydatabase > db.sql

2. Edited db.sql and deleted all the instances of:

DEFAULT CHARSET=latin1

Any editor will do, I used vi with something simple like:

%s/DEFAULT CHARSET=latin1//g

3. Drop the database (make sure your backup is ok and you have at least two copies, just in case):

mysqladmin -u user -p drop mydatabase

4. Recreate database using your modified db.sql:

mysql -u user -p mydatabase < db.sql

5. That's all! It worked for me. I repeat: make a good backup before following these steps.

Regards!

Alexis Bellido - Ventanazul web solutions

RogerB’s picture

I did more or less what Alexis describes above.

Be sure that your new database container has a default collating sequence of utf8_general_ci before you load you tables into it..

Kayla’s picture

I have resolved collation issues using MAMP (preferences set to PHP5 w/Zend Optimizer) and Drupal 4.7.2* after doing Rosamunda's steps and then M.J. Taylor's.

Thank you both so much for posting these solutions. I would have never originated this solution on my own; I had already exhuasted my limited logic and scant familiarity with both PHP and SQL. I am very grateful to those who take the time to share their knowledge here on drupal.org. :)

*This was not a fresh install of Drupal, but taking an existing website offline. Everything on the live website works just fine functionally, but taking an exported copy of that DB into the MAMP phpMyAdmin had caused a illegal mix of collations and I could not log into the localhost site. Since I am only working on theme/css files locally, I will not be taking the modified, local DB file back to the live website; I do not know if this would cause "reverse" collation issues.