Solved

MYSQL single row issue

Posted on 2013-11-14
6
352 Views
Last Modified: 2013-11-14
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
0
Comment
Question by:loki0609
  • 2
  • 2
  • 2
6 Comments
 
LVL 82

Expert Comment

by:Dave Baldwin
Comment Utility
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
 
LVL 35

Expert Comment

by:Terry Woods
Comment Utility
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
 
LVL 2

Author Comment

by:loki0609
Comment Utility
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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 35

Accepted Solution

by:
Terry Woods earned 250 total points
Comment Utility
Interesting problem! You could perhaps try exporting the query result (using "like '%test%'") as an SQL and see what it gives as output.
0
 
LVL 82

Assisted Solution

by:Dave Baldwin
Dave Baldwin earned 250 total points
Comment Utility
I'd go with Terry's suggestion.  Or export as CSV.
0
 
LVL 2

Author Comment

by:loki0609
Comment Utility
Great suggestion guys. I exported it and guess what was lurking at the end.

\r\n
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now