Tuesday, 26 February 2013

MySQL Pager Options

I've had a long hiatus from Windows Server work... I wasn't working much whilst moving away from the Peak District to York... and now I am diving back in with a digital asset management software, ResourceSpace running on a RackSpace Cloud Server (and on a local BitNami ResourceSpace VM, PHP, MySQL - not my usual bunch, but they are proving not too hard to dig deep and debug).

The MySQL console app is a very powerful .. and probably one should only go into a small function in detail, rather than attempt to explain everything that can be done with it... I've been using it in several ways over the last few weeks.. most of this will be familiar to seasoned Unix users...

This one comes in handy when I want to output a whole load of detail into a tab-delimited file, which I can then grab via ftp and stick into Excel (unfortunately not directly - I needed to open the text file in Notepad++, select all, copy, then paste into Excel - that little trick converts the Unix tabs into Excel cells correctly) - from the command prompt (not in MySQL):
echo "select field1, field2, field3 from table1 where somecriteria = true" | mysql resourcespace -uroot -p > results.txt
If I just want to log what I am seeing to inspect it later, or just in case I want to keep a track of what I have done in that session .. then on the MySQL command line \T /home/user/os.txt will log output to the file, ox.txt.  All output.  It won't stop logging until you enter \t by itself.

Anyway, today I have taken a break from using SSH to access the Cloud Server, where I can scroll back through the handy buffer.. and am using the terminal session on my local VM via VirtualBox... and there's no buffer... So here's my discovery for today (which I'll probably use later in SSH too)... (in mysql)

  \P cat > results.txt

Pager...  I just wanted to stop the results scrolling off screen, to page through them one page at a time so I can scan the records...  but I've discovered something that is altogether very powerful...  Pager - this relies on popen() - which creates a pipe, forks the results and invokes the shell to process the command - it is very useful for passing results into sh scripts which is where a lot of power can be found...  The above pager outputs only the results to a file - which is preferred over tee.  

  \P more

Pager = More: Now I can press space to jump a page, CTRL-Z if I want to jump back to the Unix prompt (fg to return to the mysql session - this works whether you use more or not), CTRL-C quits the SELECT command but not the mysql session... Another pager:

  \P less

Pager = Less: Now you can use d = page down, u = page up, r = skip, q = quit, 100g = jump to line 100, g = jump to first line, G = jump to end line, '=' = tells you how big the results are and where you are in that i.e 85%, /pattern = search forward for regex pattern, ?pattern = search back for pattern and so on.

  \P less -I -p "headline"

Pager = Less (-p highlight text) Now that's pretty cool... on all mysql output, the word 'headline' is highlighted (-I = case insensitive).. useful if I am trying to look out for fields that contain a particular word or phrase... 

  \P less -S

Pager = Less (-S = no wrap) stops wrapping of lines in the results, but if fields contain carriage returns then they do drop to the next line - you can use the left and right arrows to view columns that are off-screen... (or you can use \G instead of \g to run your SELECT statement ( SELECT * FROM table \G ) to view rows as a single column.

A more complex version (from the MySql pages) of cat -> tee -> less can log to two different files at the same time and output to less:

  \P cat | tee /tmp/file1 | tee /home/user1/file2 | less -niSFX

  \P /tmp/grep_cmd

Pager = grepping (found on the SQL Performance Blog) this requires creating a file in /tmp which I am calling grep_cmd with the following text -:
grep -A 1 -B 1 -i --color 'headline'
You could add a pipe into less (" | less ") after 'headline' or add the pipe to the \P command above to page the output... Then make this an executable using chmod a+x /tmp/grep_cmd - Now once the pager is set, mysql will only print out the record with a match, and a single record above and below it.  Matches will be highlighted or coloured.  You could just put the grep command in the pager.. but the script could be built on.

  \P cat > /dev/null

Pager = (hide output) This hides the record output from view.  You can see the number of rows returned and number of seconds to run the query.   Useful for comparing sets of queries, where you aren't interested in the output.

  \P vim -

Pager = vim  see Daily Vim - I don't use vim - but this might be of use to those who do.  I imagine it's useful to throw the results directly into a text editor...

  \P tr -d '`'

Pager = trim backquotes (one backquote surrounded by single quotes)- This was posted by Giuseppe Maxima here.  This can be used to strip out characters from your result set.  His example (above) is to strip the backquotes from a ( SHOW CREATE TABLE table1 \G ) statement.  I found that you can add different characters between the single quotes and it'll strip them away too.. even whole words like 'NULL'.  It can also strip out the vertical lines from a table result set ('|')...

Finally... for anyone who gets this far... log into mysql using the first line... and then call the second.  (I saw this tip on Parvesh Garg's blog Optim MySQL )

  mysql databasename --xml -uroot -p 
  \P cat | tee /home/user1/output.xml | less -niSFX

This outputs the results to an XML file and to the screen.  The XML file output is well-formed too.


  1. Pixaria Gallery has been designed from the groud up to meet the needs of professional photographers and digital image librarians wanting to publish and sell images on the web.
    Best digital asset management software

  2. Unfortunately a condition in their decision for choosing a DAM application was that it is open source and capable of handling multiple document types (Images/Documents/Video/Presentations/etc. Pixaria Gallery is open source, but it is not as full-featured as Pixaria AssetDeck (which is not open source).

    For anyone looking however, Pixaria Gallery might be suitable for their needs...