Empty result set when it shouldn't be

[redwall_hp]

I'm having a bit of an odd problem with MySQL. I'm trying to run a query that's fairly simple (and I've been dealing with SQL for a while now, so I'm not a total noob ). I run the query, and it returns an empty result set, when I know full well that it *should* be returning a single row. I'm running it directly through the SQL tab in phpMyAdmin, since the query wasn't working via my PHP script and I wanted to make sure that the problem was with the query and not my script.The query is:

SELECT * FROM fc_users WHERE activation_key = 'df0dd07382a72a396ae18bea43af7aa'

The row that should be returned has a value of 'df0dd07382a72a396ae18bea43af7aa' (yes, I'm sure) in the activation_key column. Why am I getting an empty result set? What am I overlooking?This is the sort of bug I hate the most, the type that looks perfectly fine and doesn't return any sort of error.

[Synook]

Umm... any errors?Are you _sure_ it is the same?Are you looking at the right table???

[redwall_hp]

Umm... any errors?Are you _sure_ it is the same?Are you looking at the right table???

No errors. Just an empty result set.I'm sure it's the same.Yep, definitely the right table.Here's a slightly condensed SQL dump, if it helps:

CREATE TABLE `fc_users` (  `uid` int(8) NOT NULL auto_increment,  `username` varchar(30) NOT NULL default '',  `password` text NOT NULL,  `email` varchar(255) NOT NULL default '',  `highscore` int(20) NOT NULL default '0',  `active` int(1) NOT NULL default '0',  `activation_key` varchar(255) NOT NULL default '',  PRIMARY KEY  (`uid`)) TYPE=MyISAM AUTO_INCREMENT=4 ;*snip*INSERT INTO `fc_users` VALUES (3, 'kingbob', '*snip*', 'kingbob713@mailinator.com', 0, 0, 'df0dd07382a72a396ae018bea43af7aa');

(And yes, the passwords are MD5 encrypted. No plaintext here. )

[justsomeguy]

Try changing the password field to varchar. If it's MD5 then the length will be 32.

[redwall_hp]

I changed the `password` field to a 32-char VARCHAR field, and now my query's working? What the heck does the password field have to do with the activation_key??EDIT: Well, it worked once. Now it's not. Very odd...

[justsomeguy]

Oops, I thought the password field was what you were selecting by.If this is the query you're using:SELECT * FROM fc_users WHERE activation_key = 'df0dd07382a72a396ae18bea43af7aa'Then if that is not returning any rows then the only possible reason for that is because the activation_key value is not matching anything. Try selecting all of the records and printing out their activation keys, make sure that there aren't any spaces or extra characters around it. But if it matched exactly what was in the query it would return the record. If it's not returning, then it must not match.

[redwall_hp]

Oops, I thought the password field was what you were selecting by.If this is the query you're using:SELECT * FROM fc_users WHERE activation_key = 'df0dd07382a72a396ae18bea43af7aa'Then if that is not returning any rows then the only possible reason for that is because the activation_key value is not matching anything. Try selecting all of the records and printing out their activation keys, make sure that there aren't any spaces or extra characters around it. But if it matched exactly what was in the query it would return the record. If it's not returning, then it must not match.

As I'm currently developing this, I'm working with only 3 records in the database for now, to keep things simple. To ensure that it works, only the one record has a value stored in the activation_key field. I've checked, and the activation_key appears to be exactly the same (no spaces or anything). Maybe I missed something, but it looks identical. I exported the table and pasted the SQL results in a post above, if you want to check yourself. Here's the relevant portion:

INSERT INTO `fc_users` VALUES (3, 'kingbob', '*snip*', 'kingbob713@mailinator.com', 0, 0, 'df0dd07382a72a396ae018bea43af7aa');

I find it interesting that the query worked a single time after editing the table structure and then quit. Simply changing a column from TEXT to VARCHAR(32) fixed the problem for one query (the exact same query I listed in my original post). But then it didn't work when I tried it again.

[justsomeguy]

If you open up phpMyAdmin and paste these two queries in will it return the row?INSERT INTO `fc_users` VALUES (3, 'kingbob', '*snip*', 'kingbob713@mailinator.com', 0, 0, 'df0dd07382a72a396ae018bea43af7aa');SELECT * FROM fc_users WHERE activation_key = 'df0dd07382a72a396ae018bea43af7aa';

[redwall_hp]

I tried, expecting it to not work (since it hadn't so many times before), but it seems to be working now. I've performed the SELECT query three times now, and it's returned it every time. What are you doing differently?

[justsomeguy]

I'm not doing anything differently, I'm not sure why it didn't work the first time though.

[redwall_hp]

I think I figured it out, *slaps head*. I had stored the key in a text document, an example output of the script that generates the activation key and emails it. The key was wrapped onto a new line, which could conceivably cause this issue. I guess I'll have to revise the script then and have it email an HTML-type email instead, to prevent things like that.