MYSQL single row issue

I had a weird thing happen today that I've never came across. I have a table full of data. I could not pull  certain row and only that row

example:

SELECT * FROM my_table where user = 'test'

I couldn't pull that row trying ANY of the the fields IE: address = 'whataever' city = 'mycity' etc.

Once i manually updated the row and re-enterd the data i could pull it.

repair table didn't help. I'm not sure what would cause this and would like to know
LVL 2
loki0609Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Terry WoodsConnect With a Mentor IT GuruCommented:
Interesting problem! You could perhaps try exporting the query result (using "like '%test%'") as an SQL and see what it gives as output.
0
 
Dave BaldwinFixer of ProblemsCommented:
Since you have already fixed it and haven't posted any error messages, it's going to be hard to figure it out.  Nothing left to work with.
0
 
Terry WoodsIT GuruCommented:
Sounds like some kind of special character may have been included in the value(s). Using "user like '%test%' " may have found it?

I've wasted substantial time before trying to find why something wasn't retrieving only to find the id was appended with a space character. Since then, it's something I've learnt to look for without wasting substantial time first...
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
loki0609Author Commented:
Thanks for the replies.

The table i fixed was a restore from another server. So i went to the old server and sure enough that same row is fubar'd.

I was able to retrieve it with like '%test%'

I tried right(columnname, 1) and the 1 and 2 position return nothing.

If you go into a program like sqlyog or phpmyadmin and go to the field to manually edit it you can normally see spaces when you enter the field. This one you enter the field to edit and hit delete after the last character and it still doesn't fix it. You have to write over the whole field

trim(field) = 'test' does not retrieve the field.

I'm not going to spend much more time on it but it would be nice to know why or find a way to show ALL characters even if they are special or something.

Thanks for the help all
0
 
Dave BaldwinConnect With a Mentor Fixer of ProblemsCommented:
I'd go with Terry's suggestion.  Or export as CSV.
0
 
loki0609Author Commented:
Great suggestion guys. I exported it and guess what was lurking at the end.

\r\n
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.