By drhilarius on

Hi all - I am in the process of migrating to a new server (to go from shared hosting to a VPS). I was at 4.7.2, and tried to install 4.7.3 on the new server. However, I think the new server machine is the issue, not the drupal version. (BTW that means this post is miscategorized as an upgrade problem, but that seems like the closest thing).

Everything went pretty well with the move, except I am unable to log in. When I try to log in with the admin account or any other account, I get "Sorry. Unrecognized username or password. Have you forgotten your password?"

When I click on the "have you forgotten your password" link for kicks and enter my username (eg drhilarius)/email, I get "sorry. the username drhilarius is not recognized."

But the thing is, I go to the users table in the database, and all the users are in there!

So here's what I know:

- Drupal is successfully accessing the database (all the nodes are showing up fine)
- Drupal is successfully getting a hold of the form data (I know this because it said "the username drhilarius is not recognized")
- I'm pretty sure issue is with the new server, not the upgrade, because I tried installing a 4.7.2 instance on the new server and the same thing happened. (They were both pointing at the same db fwiw).
- I don't think it's a problem with Md5ing the passwords or anything, because the above error complains that even the username is not known.
- I did not update the database from 4.7.2 to 4.7.3 because update.php said one was not needed.
- The new server uses mysql v4.x, whereas the old one had mysql v3.x. Maybe that's causing the problem, but if so I don't know what to do about it.

At this point I've been experimenting and searching the forums for many hours, and I am still stumped. So I thought I would see if any of the drupal superstars on this board have any thoughts.

Thanks in advance for any ideas you have...

rich

⋅ Categories: Drupal 4.7.x

Comments

pwolanin’s picture

pwolanin commented

When moving servers did you move between different versions of MySQL? If so, maybe there is a different character encoding that's throwing you off.

---
Work: BioRAFT

drhilarius’s picture

drhilarius commented

Hi pwolanin - Thanks for the response - I think you are right. I see that the new version of mysql is a different encoding, and furthermore that drupal uses different mysql schemas for pre-4.1 mysql vs. post-4.1. I had just dumped the old database, so I have the wrong schema as well!

I'm not sure how best to approach the problem of migrating the drupal data from mysql 3.23 to mysql 4.1. Looks like it's time for more digging around in the forums...

Thanks again,
Rich

pwolanin’s picture

pwolanin commented

I think the only difference in the schema is the ability to enforce UTF-8 encoding in MySQL 4.1+.

for some possible help see: http://drupal.org/node/76486

---
Work: BioRAFT

drhilarius’s picture

drhilarius commented

I appreciate the pointers - you have saved me a lot of time tonight!

Thanks,
Rich

drhilarius’s picture

drhilarius commented

Hmmm, well I'm still having trouble so I thought I would tell you what I'm doing -- I'm not too experienced with mysql so I might be making a newbie mistake here.

In dumping the data from my 3.23 mysql server, I added the --default-character-set flag per that post you referenced:

mysqldump -u rich -p --default-character-set=latin1 dbname > drupal41.sql

And then over on the box with mysql 4.1, I imported it thusly:

mysql -u rich -p drupaltest < drupal41.sql

My assumption from reading your other post was that the extra flag on the dump would somehow indicate the proper encoding, so that the second mysql import command would be able to "translate" correctly. I am either misinterpreting, or I did something wrong.

Any thoughts? I really appreciate the help...

thanks!
Rich

PS - There are no "weird" characters in the usernames I am trying... perhaps the encoding issue isn't the problem after all... I don't know.

pwolanin’s picture

pwolanin commented

Hmmm, if there are no odd characters in the usernames than maybe this is barking up the wrong tree. However, I think you'll still need to have done this encoding change correctly or you'll see mis-encoded chars in your posts.

I'm quite perplexed since you claim the error is that the username is "not found" at all. It still sounds like there is an extra or missing non-printing character somehow.

On you new host can you edit one of the usernames in phpmyadmin, replace is with a different name, and try logging in as that user?

---
Work: BioRAFT

drhilarius’s picture

drhilarius commented

Very good idea... should have thought of it. Unfortunatley it didn't work... there weren't any weird characters in phpmysql, and when I erased the whole name string and replaced it, the problem still occurred (I tried replacing both with an identical username and a different one). I also tried creating a new user in the new db, and it didn't recognize that one either.

On another issue (or maybe the same issue?) you are correct that the re-encoding needed to happen. But it didn't happen... despite my attempts as outline above, I AM getting funny characters in the aggregator and elsewhere.

So I guess first I need to figure out what I did wrong with the encoding. Maybe that will somehow miraculously fix this login thing. If you have any ideas on what is wrong with the mysql dump/import above, I am all ears. I found it suspicious that in the dump file there was no instance of the string "latin1", so I think I did something wrong with the initial dump command.

Thanks so much for your continued help!!!

Rich

drhilarius’s picture

drhilarius commented

OK, I think it has to do with "collations"... in the new db, all table columns appear to have the collation "latin1_swedish_ci."

And I found this in the watchdog... that ' around the username certainly looks suspicious.

Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='
query: SELECT * FROM users u WHERE LOWER(name) = LOWER('drhilarius') AND status = 1 in /var/www/vhosts/doctorhilarius.com/httpdocs/includes/database.mysql.inc on line 120.

So I have to figure out how to import the new db such that the collation is whatever it should be...

rich

UPDATE - the error snipped above isn't displaying it, but there are &#039's around the username instead of single quotes... that has got to be the problem

brianV’s picture

brianV commented

Can you not change the collations in use by the mysql engine through PHPmyadmin/

I would try creating a new blank DB. I think PHPmyadmin defaults to using the Swedish collation. Switch that to the latin1 collation, and then import all your data. Then, AFAIK, the collation issue should be solved!

Good Luck...

--
Drupal Pages I've Designed:
http://www.free-journal-articles.com
http://www.outfrontps.com
http://www.infohatter.com

My Blog: http://www.infohatter.com/blog

drhilarius’s picture

drhilarius commented

Thanks Brian - You were definitely onto something there.

What I tried was to convert the users table to be UTF8 encoding/collation like so:

ALTER TABLE users CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

And I was magically able to log in! Hooray!

So what I think was going on was that drupal (somehow "knowing" that i had a 4.1 mysql server) was speaking UTF8 to the mysql server, and the server (expecting to hear latin1) was misunderstanding. Once I changed the mysql server to speak UTF8 that fixed the login problem.

(You are absolutely right that the correct approach is to create a new db with utf8 defaults and to reload everything... since it takes hours to load all my data, I wanted to try the above as an experiment first).

Now, I am still left with the problem (much less serious than the login one) that the new db is displaying weird characters where there used to be quotes. I think the problem is that the old stuff is encoded in latin1, and the new server reads it in as something else (utf8 I guess) and misinterprets it when the data is being loaded.

The best way to do this would be to do a mysql dump on the old server and specify utf8 as the character encoding. But that version of mysql does not support utf8. So the next best thing, or so I thought, was to export and import with the same character encoding specified (I was trying this with my aggregator_item table, because lots of weird characters were showing up there):

mysqldump -u rich -p --default-character-set=utf-8 olddb aggregator_item > aggregator_item.sql on the old machine

mysqlimport --local --default-character-set=latin1 -v -u rich -p drupaltest aggregator_item.sql on the new one

However, the above mysqlimport does not properly import the file - it throws off lots of warnings and the resulting rows are mostly empty. The same input sql file, on the other hand, can be used with a mysql command:

mysql -u rich -p drupaltest < aggregator_item.sql

And that creates the table just fine... EXCEPT that quotes and other special characters show up all funny.

So in short:

- username recognition problem is solved by converting tables to UTF8.
- some odd characters are still getting lost in the translation, but it's not causing any functional problems

I think I may have to just live with the second issue... it's not a big deal and i am out of things to try. The important thing is that the first issue with the user authentication was solved!

Guys, thanks very much for your assistance... it was invaluable.

Rich

pwolanin’s picture

pwolanin commented

The post I linked to above has a series of comands to run: mysqldump with latin1, and then import into mysql specifying UTF8. This fixed the quotes to weird characters issue for me.

---
Work: BioRAFT

drhilarius’s picture

drhilarius commented

Hmm, that is weird because mysql on my server (according to the man page) doesn't have a --default-character-set flag.

I think I tried this, but honestly I have tried so many things I am not sure. I will give it another whack.

Thanks again!

rich

update: it's not in the man page but the mysql site sez it is valid... i will give it a try.

drhilarius’s picture

drhilarius commented

OK, you were right, (no big surprise!). Maybe it didn't work the first time because I hadn't set up the db columns with the right encoding scheme at that time. Anyhoo, this worked like a charm and all my issues are solved... thanks a million!!!

rich

JasonSafro’s picture

JasonSafro commented

I had the same trouble. I moved servers so I copied my files and exported my database. I was unable to log in but Usernames were present and auto-completing. Turns out the problem was that I had not updated the site base url in the settings file. Fixed that and I was golden.