Tuesday, July 8, 2008

MySQL command line pager & mysmartpager

Few days back, Baron re-introduced MySQL's command line pager command and described some cool tricks with maatkit's mk-visual-explain (one of my favorite tools). Soon after reading it, I wished if it was possible to describe regex based (on query) paging. I have written a small hack, christened mysmartpager, that can actually do regex based paging for you. The idea is simple, write a relay that will redirect the output based on to the desired pager. The problem was complex, there was no direct way of getting to know the original query. There are a couple of indirect ways of doing so, but of course with hurdles:

  • Run mysql client with --xml option: This will print the output of each command in xml and the command itself is included in the xml. The downside was not many pagers (including mk-visual-explain) understand the xml format and parsing the xml to create a output like string was going to be costly. This will also include writing a lot of code that I don't do for a hack. :)
  • Use the tee command: MySQL allows you to tee output to a file that contains everything from standard output including mysql> command... We could have used and easily take the last command using a simple grep. The problem is output is tee'd after pager exits.
Well, that was easy. Now since we know that MySQL will tee after the pager exits we will make the pager exit while still working. And introduce exit if fork; in our code. :) It's working fine for me with small display problems like the time spent is now displayed above the query result. That's fine for me. Files: I don't have an exact way of uploading files into blogger so got it on scribd. Install:
  • Copy mysmartpager file to your /path/to/bin directory
  • Copy .mysmartpager file to your $HOME directory
  • Copy mysmartpager.cnf file to your MySQL's config directory and make sure it is included.
  • Make appropriate changes in mysmartpager.cnf file
  • More regexs (perl regex's can be written in .mysmartpager)
I have listed three regex's in .mysmartpager file. Would like to hear more ideas flowing.

3 comments:

nsdigitalworld said...

I always feel relief to see these types of useful posts. By this post, we can be able to get all solutions and decisions about the MySQL command line pager and my smart pager. It commands and described some cool tricks. It is very useful in the increasing globalization because each country's development and economy totally depends on businesses. Most of the users are confused about it but they can clarify their doubt by visiting on websites. Experts always share their thoughts and point of view on the internet. But you can't forget that development depends on various types of sources. Because developers are very useful and helpful in most of the big firms. If anyone says that the success of industries depends on these experts it is 100% true. In this article, we are talking about blockchain developers in Ukraine. We can know about all details of mehr uber blockchain. It was initially only used by cryptocurrencies.

nsdigitalworld said...

The conversion scale of AUD/JPY shows a more intricate picture. Its present moment upturn is supposed to experience a mishap as it is as of now moving toward the html-law.com will be restricted considering different difficulties in front of Japan like contracting labor forces and the endless deferment of Olympic Games.

fintech app development said...

thanks a lot for information