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.

• mysmartpager
• mysmartpager.cnf
• .mysmartpager

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.

GET_LOCK & FAMILY

Again I have a well neglected and documented feature of MySQL. As we all often need and use locks in MySQL, we tend to forget (or not bother about) MySQL internals and how they cause trouble. For example, try something like LOCK TABLE ... WRITE on an InnoDB table in a transaction and see the same transaction getting timed out while waiting for a lock on one of the rows, Ref: [Bug 5998]. All these problems occur when we have difference in semantics of statement at MySQL and Engine levels. But recently, we figured out this good technique of keeping the logic with ourselves and not relying on MySQL too much. Though even this technique is not fool proof in all the cases.

The secret is: use GET_LOCK function of MySQL. GET_LOCK(str, timeout) function tries to get an exclusive lock with a name (str) using timeout seconds. The return values are tri-state, 1 if the lock was obtained successfully, 0 if there was a timeout and NULL in case of an error. For applications just bothered about getting the lock, following can be used.

   1: SELECT COALESCE(GET_LOCK('lock_string_name', 2), 0)

This is guaranteed to return either 1 or 0 meaning lock taken or otherwise respectively. Locks taken using GET_LOCK function are released in following conditions

• Lock is released explicitly using RELEASE LOCK function.
• Issue a new GET_LOCK request.
• Connection terminates.

Other functions of this family are IS_FREE_LOCK and IS_USED_LOCK which are used to check the lock string status. IS_FREE_LOCK checks if the string is free i.e., returns 1 if the string is free, 0 if it is in use and NULL in case of an error. IS_USED_LOCK checks if the lock is used and returns the connection identifier of the clients that holds the lock otherwise 0. Again NULL is used for error cases.

USEFULLNESS:

• This is one way of maintaining the mutual exclusivity of a process in a distributed environment while ensuring BCP also. Just have a named lock which will can be obtained only by one process at a time and rest of the processes just timing out and retrying for the lock after a sound sleep.
• The lock is very light weight and doesn't eat up too many resources at the MySQL server end.
• Lock is at MySQL level and engines are not involved at all, thus saving you to read bundles of documentation before using something.

CONS:

• It requires due diligence in the application to ensure that all the conflicting transactions are taken care of. Even one single left out transaction may spoil the whole game. So, I would suggest such techniques are not good for heavily loaded (read lines of code) applications or otherwise you have a good QA team.
• It actually cannot equate other locks as you can have obtain only one lock for a connection.

MISC:

• These locks are at connection level and do not respect transactions. Meaning, committing transactions do not have any effect on these locks. I think the fairness of this feature depends on the use case, hence this was put under MISC category :)

Hope this blog was helpful to you. Keep posting your comments.

Customized Order By Sequence - A Small Hack

What if you have to customize the sequence of order by clause on a column. Say, you have a integer column 'weight' that can have values ranging from 1 to 5 and you want to get the rows in the sequence 4,2,5,1,3. So what do you do? Here is a simple trick using the field function.

   1: mysql> SELECT * FROM table ORDER BY FIELD(weight, 4, 2, 5, 1, 3);

Now what does it do. Field function returns the index of the first argument in the rest of the list. So, effectively the order by logic will be the same, just that the values that are being ordered are generated on the fly.

Other approach can be to use CASE.. WHEN .. END statement as mentioned in the MySQL Documentation. The above code can be rewritten as:

   1: mysql> SELECT *, CASE 

   2:     -> WHEN weight = 1 THEN 4

   3:     -> WHEN weight = 2 THEN 2

   4:     -> WHEN weight = 3 THEN 5

   5:     -> WHEN weight = 4 THEN 1

   6:     -> WHEN weight = 5 THEN 3

   7:     -> END AS weight_custom

   8:     -> FROM table

   9:     -> ORDER BY weight_custom;

But I'm a little biased towards the first approach due to its simplicity. Though it's a matter of personal choice.

Such requirements are rare, just a food for thought.

Hope this blog was helpful to you. Keep posting your comments.