MySQL - Cant see all the data in the columns

This is follow on from a couple of other tips that I have put up recently

  1. Export to csv from SQLite
  2. Error 13 (HY000): Can't get stat of


This is another follow on, I suppose you could call this a mini series :-).

After importing the database I could not see all the data in all the columns.  When I done select * from , it looked garbled and would not show all the information.

The row count was correct, so I then ran the command again but this time instead of ending the statement with ; (semi colon),  I used \G .  When you use \G instead of ; it shows the data in Raw Form, not in table view.  From here is looked ok, but I still wanted to see why it was showing messed up.

Next was to change the way I view the data, so I used pager, this allows you to use OS commands and formatting like less and more in Linux.  The statement I chose was pager less -n -i S.

The options mean

-i ignore case
-n suppress line numbers
-S Chop long lines

From here when the table was displayed it showed the last column had the data followed by ^M.  Because of this control character that is what was messing the display up.

A quick update statement to set the columns to what they were supposed to be solved the problem. In this case the column had Regions in it, so a few simple updates update main_db set region = ‘EMEA’ where region like EME%’, repeated with NA and APAC, this sorted the issue. Once updated I could easily select * from and see all the data.

I am always interested in feedback so please feel free to add any comments, or you can mail me  here.  If you would like to submit a quick tip  with full credit and links back to your site then also feel free to contact me.

Related Posts Plugin for WordPress, Blogger...

Two times on iPad screen

ERROR 13 (HY000): Can't get stat of