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
Yeah... it sucks... but there´s a solution!
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:
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
do I need to complete both steps 1 and 2
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? :)
After step 1º, all seemed
After step 1º, all seemed fixed.
I´ve done it "just in case".
;-)
Rosamunda
Buenos Aires | Argentina
www.ligadelconsorcista.org
I don't really understand what to do in step 2...
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...
hmmm
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
new 4.7 via fantastico
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...
It's Step 1 that fixes it
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.
I see lots of people are having this problem...
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...
I´ve started a topic at the
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
I don't know how to manually install it, hehe...
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...?
yep
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
Okay
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
Second set of errors.
found the problem
My web server has not yet updated all of the software required by drupal, ie. PHP and MySQL
THanks.
Garrett
hmm....
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
Fix requires extra steps for some Hosting firms
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===
===after===
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
Success!
Thanks for posting the export / edit / import sql - that worked for me!
An Alternative: Recreate The Database
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
Create DB with utf8_general_ci
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..
Thank You
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.