Link to home
Start Free TrialLog in
Avatar of street9009
street9009Flag for United States of America

asked on

MySQL Upgrade - Queries Not Working?

Okay. I just finished migrating a MySQL server from one server to another (old was 5.0.45 and new is 5.5.28). PHPMyAdmin is showing all of the users, privileges, databases, etc. Working like a champ.

However, some of my very basic queries are not working and I can't figure out why. For example-

customerID      int(11)      No              
cust_password      varchar(255)      No              
Email      varchar(64)      Yes       NULL

If I run a query like so, it returns an empty set:

SELECT * FROM table WHERE LOWER(email)='test@test.com';

Now, I can go into PHPMyAdmin and actually see that table row. It's definitely there. And I can query on customerID=1 and it returns every time.

What is happening? The DB engine and character sets are all the same (so far as I can tell). I'm open to suggestions.
Avatar of arnold
arnold
Flag of United States of America image

Try
Select * from table where lower(email)='test\@test.com'
Avatar of street9009

ASKER

Still get an empty set.
The way the data is stored might be the issue that prevents the matching.
Try
Select * from table where lower(email) like 'test%'
Then add the @ before the % and see if the data returns or you end up with an empty set.
Found one problem. The dots in the email address. Some of the tests I've tried have a dot before the @ symbol and of course all have it after the symbol. It works if I do the LIKE and up to the dot. Including the dot makes it fail. The @ symbol seems to be fine (LIKE 'test@%' returns results).
Are you saying the email is in the form 'user@.domain.com'?
After the upgrade of mysql, did you run the upgrade database tool?
Email is in the form of 'user@domain.com' but some of the 'user' strings have periods in them as well "first.last@domain.com".

I followed the instructions found here to do the upgrade: http://bitwise.blogspot.com/2010/12/upgrading-mysql-50-to-55-with-mysqldump.html

All the data is in the MySQL 5.5 instance, but some of the queries are failing. I have both instances installed (so I can swap back and forth by disabling one service and starting the other).
Not sure why your lookup fails.if you have one record with a single '.', can you run a select and get that record?
I'm thinking the issue is that the character set inserting the data on one end does not match the one on the other in the select.
The test@test.com example doesn't return once you include the period.

SELECT *
FROM  table
WHERE LOWER( email ) LIKE  'test@test%'  <---- This works

SELECT *
FROM  table
WHERE LOWER( email ) LIKE  'test@test.%' <---- This does not.
One thing to check is whether the character set is the same for the client and the server.
Try this, insert into table (cust_passwd, email) values ('password','.')
You may have to set/include Id.
Then see if a select on a '.' Is matched.
SELECT * FROM table WHERE email='.'

That returns an empty set as well.
You insert and select using the same interface and get an empty set?
select * from table where length(email)=1
That's correct. I inserted through PHPMyAdmin and also selected from it (which worked on the old version as well). Empty set.

The query you pasted does return the row I just inserted.
Can you try the query using the command line mysql tool?
Trying to see whether phpmyadmin may encode the query.
With updating of the database, did you update the phpmyadmin as well?
I deleted the row and then did the INSERT and the SELECT from the command line. Still getting an empty set.

I did download the latest PHPMyAdmin when I installed the latest MySQL.
ASKER CERTIFIED SOLUTION
Avatar of street9009
street9009
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Found the solution on my own.