Solved

MYSQL single row issue

Posted on 2013-11-14
6
355 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 83

Expert Comment

by:Dave Baldwin
ID: 39649841
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
ID: 39649901
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
ID: 39650201
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 35

Accepted Solution

by:
Terry Woods earned 250 total points
ID: 39650214
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 83

Assisted Solution

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

Author Comment

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

\r\n
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Mysqli Query 5 50
MySQL Error 3 42
Clean text to insert in database 9 60
Upgrade MySQL 5.5 to MySQL 5.6 on Windows 13 24
I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.

786 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