Tail -f table with myterm

Posted January 20th by Björn Melinder
Some new features in myterm, extensible mysql command line client:
Tail

Ever found yourself running the same query over and over again to see if a table has got new content, just waiting for that magic row to appear? The tail command will watch for new lines in a table, just like you might tail a log file in Linux. Example:

  1. myterm> tail -f bid
  2. +--------+---------+-----------+--------+---------------------+
  3. | bid_id | item_id | bidder_id | price | created |
  4. +--------+---------+-----------+--------+---------------------+
  5. | 67925 | 107751 | 81594 | 92500 | 2010-01-20 19:12:58 |
  6. | 67926 | 34248 | 32530 | 10000 | 2010-01-20 19:12:59 |
  7. | 67927 | 111211 | 108032 | 22900 | 2010-01-20 19:12:59 |
  8. | 67928 | 116016 | 46720 | 90400 | 2010-01-20 19:13:00 |
  9. | 67929 | 129101 | 98949 | 61300 | 2010-01-20 19:13:03 |
  10. | 67930 | 44235 | 91806 | 46400 | 2010-01-20 19:35:03 |
  11. | 67931 | 59403 | 114210 | 30700 | 2010-01-20 19:35:06 |
  12. | 67932 | 110582 | 101572 | 41800 | 2010-01-20 19:35:07 |
  13. | 67933 | 46759 | 73844 | 32600 | 2010-01-20 19:35:07 |
  14. | 67934 | 122248 | 18709 | 105600 | 2010-01-20 19:35:09 |
  15. | 67935 | 87468 | 70444 | 28500 | 2010-01-20 19:35:17 |
  16. ... (more rows appear over time)


The command will ask the server for the primary or unique key(s) of the table. It will then ask for new rows every second, remembering the maximum primary key value returned last time. This works if you insert data by ascending primary key (e.g. auto_increment). The query for the above would be:

  1. SELECT * FROM bid WHERE bid_id > ? ORDER BY bid_id


You can also specify the column to use as key using the -k option, e.g.

  1. tail -f -kcreated bid


which will run:

  1. SELECT * FROM bid WHERE created > ? ORDER BY created

Repeat

Similar but different, repeat allows you to run a query multiple times with some delay.

  1. repeat SELECT COUNT(*) FROM some_table;


By default, it will repeat 10 times and sleep 1 second in-between, but you can adjust this. Go forever and sleep 100 ms in-between:

  1. repeat -n0 -i0.1 SELECT COUNT(*) FROM some_table;


Say you want to watch some global status variable, option -t adds a timestamp as first column so you can see over time:

  1. myterm> repeat -t SELECT * FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE 'Questions';
  2. +---------------------+---------------+----------------+
  3. | Time | VARIABLE_NAME | VARIABLE_VALUE |
  4. +---------------------+---------------+----------------+
  5. | 2010-01-20 19:11:25 | QUESTIONS | 8394418 |
  6. | 2010-01-20 19:11:26 | QUESTIONS | 8394420 |
  7. | 2010-01-20 19:11:27 | QUESTIONS | 8394422 |
  8. | 2010-01-20 19:11:28 | QUESTIONS | 8394424 |
  9. | 2010-01-20 19:11:29 | QUESTIONS | 8394426 |
  10. | 2010-01-20 19:11:30 | QUESTIONS | 8394428 |
  11. | 2010-01-20 19:11:31 | QUESTIONS | 8394430 |
  12. | 2010-01-20 19:11:32 | QUESTIONS | 8394432 |
  13. | 2010-01-20 19:11:33 | QUESTIONS | 8394434 |
  14. | 2010-01-20 19:11:34 | QUESTIONS | 8394436 |
  15. +---------------------+---------------+----------------+
  16. 10 rows in set (0.013 sec)


More or less tailor-made for this purpose, -d allows you to calculate a delta value for some column:

  1. myterm> repeat -t -d3 SELECT * FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE 'Questions';
  2. +---------------------+---------------+----------------+----------------------+
  3. | Time | VARIABLE_NAME | VARIABLE_VALUE | Delta_VARIABLE_VALUE |
  4. +---------------------+---------------+----------------+----------------------+
  5. | 2010-01-20 19:12:52 | QUESTIONS | 8395641 | 0 |
  6. | 2010-01-20 19:12:53 | QUESTIONS | 8396281 | 640 |
  7. | 2010-01-20 19:12:54 | QUESTIONS | 8396729 | 448 |
  8. | 2010-01-20 19:12:55 | QUESTIONS | 8397162 | 433 |
  9. | 2010-01-20 19:12:56 | QUESTIONS | 8397634 | 472 |
  10. | 2010-01-20 19:12:57 | QUESTIONS | 8398254 | 620 |
  11. | 2010-01-20 19:12:58 | QUESTIONS | 8398919 | 665 |
  12. | 2010-01-20 19:12:59 | QUESTIONS | 8399470 | 551 |
  13. | 2010-01-20 19:13:00 | QUESTIONS | 8399994 | 524 |
  14. | 2010-01-20 19:13:01 | QUESTIONS | 8400509 | 515 |
  15. +---------------------+---------------+----------------+----------------------+
  16. 10 rows in set (0.004 sec)


Exercise for the reader: see if you can modify the command above to show the result as a line chart.

The repeat command can also do updates or deletes. You can tell it to repeat until no more rows are affected using the -a option. Useful if you want to delete some data chunk by chunk:

  1. repeat -a DELETE FROM message WHERE created < DATE_SUB(NOW(), INTERVAL 30 DAY) LIMIT 1000;

Named connections

If you often connect to the same db machines, you can name them in your configuration file. You can then specify the name when starting myterm, e.g.

  1. ./myterm @workhorse1


Your conf file .myterm contains the login credentials:

  1. con_workhorse1_user = "admin_john_doe"
  2. con_workhorse1_host = "192.168.23.25"
  3. ..

Abbreviations

Abbreviations enables you do edit the word list for auto completion. Some abbreviations are added by default. For instance, if you type:

  1. myterm> sel (TAB)


the abbreviation sel will auto expand into:

  1. myterm> select * from


You can see the list of existing abbreviations by typing abbrev list. You can also add your own abbreviations by typing abbrev add.

Download

Myterm is still in alpha, get it here: https://launchpad.net/myterm

See also this post.

6 comments

Add your own

Shlomi Noach | January 21st at 08:11
Love it!
Bjorn | January 21st at 08:21
Thanks, Shlomi! Glad you liked it.

I noticed your oak-chunk-update which is much more versatile than repeat above. And some other commands like oak-online-alter-table which seem really useful.

An idea I had was to write an adapter plugin which would allow the use of any openark-kit command from within myterm. The benefit would be that the login credentials would be automatically shared, so less arguments would have to be passed. What do you think?
Shlomi Noach | January 21st at 08:27
Hi,

You have my blessing!
I'm still experiencing bugs and issues with both above mentioned tools (oak-chunk-update is more robust now).
Unfortunately I'm getting little to no feedback from users; almost no bug reports. So this is evolving on a very slow pace. Just so you know.
Feel free to integrate whatever you like. Let me know if I can help.
Bjorn | January 21st at 08:32
Thanks! No worries, myterm is not exactly GA either :)
brandon | July 7th at 12:16
awesome tool. thanks for writing it. was looking for exactly the tail -f functionality and here it is. a sweet find.
Bjorn | July 9th at 09:42
Thanks Brandon!


Post a comment


(required)

(will not be published) (required)





Notify me of followup comments via e-mail



Subscribe without commenting