I have been runnning a Drupal 4.7.4 test site on a Red Hat server with Apache 1.3, MySQL 3.23, and PHP 4.3.
In order to upgrade several other sites to the latest PostNuke software, I needed to upgrade MySQL. Now I have Apache 2.0, MySQL 4.1, and PHP 5.1 on the new server config.
All of the PostNuke sites survived the upgrade well -- the newest versions with no issues at all -- but the MySQL upgrade seems to have disabled my Drupal test site.
When I tried to access the Drupal site after the upgrade, I received 2 different series of error messages:
1) Warning: Illegal mix of collations (utf8_general_ci, COERCIBLE) and (latin1_swedish_ci, IMPLICIT) for operation like . . .
2) Warning: Cannot modify header information - headers already sent . . .
I then took a copy of the database and ran upgrade.php from the 4.7.4 code. This got rid of the error messages, but I still can not get to anything other than the home page. Trying to go aywhere else just gives me the error message: "The requested URL /drupal/node/3 was not found on this server."
I next did a clean install of the 4.7.4 code, which runs fine on the new server config, but which will not access the old database, either.
My question is this: What must I do, in order to get Drupal to recognize my MySQL 3.23 data in the new 4.1 database?
Are there settings changes needed? New parameter to adjust somewhere? MySQL instructions to implement conversion?
Comments
My first thought was that the
My first thought was that the Apache configuration and not MySQL is responsible for your current problem. Is "The requested URL /drupal/node/3 was not found on this server" a Drupal message? What I suspect is that Drupal never saw your request.
Also, what is the error message when trying to access the database with your new working clean install? Is it the same? If so, and if the error message was from Drupal, then Drupal did see the request and ignored it.
Try to access your site without using clean URLs (?q=node/3 instead node/3) to see what happens, or disable clean URLs (http://drupal.org/node/5590)
I have heard a recommendation to disable clean URLs before moving a site to a different server, and then enable them again. Maybe it applies here.
Aha!
Aha! You're a wizard! Using ?q=node/3 instead node/3 worked well -- as did ?q=admin/settings after that. It looks like this is a server config issue, in re: clean URLs, instead of either a MySQL or Drupal problem. I guess I was looking for bugs in all the wrong places!
Thanks for your help.
You will find a few good
You will find a few good pointers here.
http://drupal.org/node/15365
Argh, next issue
Yes, I was just reading the clean URLs page, thanks. Another issue has popped up first, though (or perhaps two):
?q=admin/settings worked fine before, since I had a persistent login from earlier. After logging out, however, I am unable to log back in. All I get now is "Sorry. Unrecognized username or password."
In addition, it looks like some MySQL issue is still there, too, since ?q=user also gives me 3 versions of the following error message:
"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('username') in /usr/local/apache2/htdocs/drupal/includes/database.mysql.inc on line 121"
You've been a great help so far. Any quick fix for this one?
I am no expert, but here is
I am no expert, but here is what I have found. (Actually this is part of what I do whenever I move my Greek database to MySQL 4.1 or higher with their full Unicode support. There is more, but probably not relevant.)
My database dump does not seem to contain any collation information. So, using phpMyAdmin, I set up everything first in the empty new database: utf8 encoding, utf8_general_ci collation, no "legacy" or "compatibility" shit. When I load the database everything seems to fall into place with the right collation.
You can probably fix your tables even now. First, you get all the tables names into a text file with a:
SHOW TABLES
and then you edit the text file to apply to all an:
ALTER TABLE tablename DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci
---------
Edited to add:
If you have non English characters there may be one more point to watch, but I left it out for now.
Edit2: Also keep this query handy just in case (although it would cure only the symptom)
UPDATE users SET name='yourusername', pass=md5('yourpassword') WHERE uid=1
OK, thanks.
OK, thanks. I'll give it a try!
Success!
Thanks again, because your series of posts pointed me in the right direction. Just in case anybody else happens to stumble into the same problem, here are the steps which worked for me:
1. Use phpMyAdmin to create a new database 'newbase'
2. Run an SQL query to setup the proper character set and collation values, such as:
ALTER DATABASE newbase DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
3. For each table in the MySQL 3.23 backup database dump, find and replace 'latin1' with 'utf8'
4. Import the old data (excluding that of the CACHE table, to avoid any posssible troublesome entries) into the new database
5. Change the end of the database URL format in settings.php to 'newbase' and FTP that updated file to the server
6. Cross your fingers and reload the home page in your browser
** At this point, the website ran perfectly again, except that I could still not log in **
7. Run an SQL query to establish the primary user again, such as:
UPDATE users SET name='yourusername', pass=md5('yourpassword') WHERE uid=1;
8. Test for ability to update, logout, login, etc. -- and then go have a beer!
Thanks again for your help.
x2
Hi,
I also had success following your summary tcochran.
Thanks to everyone who helped solve this issue. WHEW!!
Kent