My Collections

Question

I have a script running against a database with no GUI. This simple query lets me see its progress:

select      *,      (select count(*) from domains) as count,      (select 456976-count(*) from domains) as remaining  from domains  order by domain desc  limit 0, 1;

Using MySQL Workbench, how can I automatically refresh/re-run this query every few seconds?

I tried Googling, but didn't come up with anything relevant.

^{Aside: the magic number 456,976 is 26^4; it has to do with the script.}

RolandoMySQLDBA · Accepted Answer · 2013-10-14 14:26:30Z

First, create a Stored Procedure that calls the query and sleeps every 10 seconds

use mydatabase  DELIMITER $$  DROP PROCEDURE IF EXISTS `MyDataStatus` $$  CREATE PROCEDURE `MyDataStatus` ()  BEGIN      SET @SecondsToPause = 10;      SELECT COUNT(*) INTO @CurrentCount FROM domains;      SET @Remaining = 456976 - @CurrentCount;      WHILE @Remaining > 0 DO          SELECT *,@CurrentCount as `count`,@CurrentCount as `remaining`          FROM domains ORDER BY domain DESC LIMIT 0, 1;          SELECT SLEEP(@SecondsToPause) INTO @SleepValue;          SELECT COUNT(*) INTO @CurrentCount FROM domains;          SET @Remaining = 456976 - @CurrentCount;      END $$  DELIMITER ;

Now, call the Stored Procedure from DOS Command Line

set MYSQL_USER=root  set MYSQL_PASS=password  set MYSQL_MYDB=mydatabase  set MYSQL_CONN=-u%MYSQL_USER% -p%MYSQL_PASS% -D%MYSQL_MYDB%  mysql %MYSQL_CONN% -ANe"CALL MyDataStatus()"

or you could login to mysql

mysql %MYSQL_CONN%

then call the query at the MySQL prompt

mysql> CALL MyDataStatus();

Please notice I call the COUNT once and subtract it from 456976 to speed up the count a little.

David Spillett · Answer 2 · 2013-10-14 12:35:38Z

You don't state what OS you are using, but under Linux/OSX and other Unix-a-likes you can use the mysql command line tools with the pretty standard watch utility to do this. Something like:

watch -n 10 'mysql

Breaking that down:

watch -n 10 '<stuff>' runs <stuff> every ten seconds and echos anything that goes to stdout to your console window, the single quotes wrapped around the command(s) to run tell your shell not to run any wildcard globbing or other substitutions before giving the command to watch (we want this to happen when watch runs the command)
mysql --database=dbname --user=username --password=password runs the command line tool against a given DB with the authentication details given
Using command substitution ($(cat /path/to/file/with/mysql/password)) to read the password instead of giving it directly on the command line stops your password being presented to anyone looking at the output of top or ps (this is why we use the single quotes: without them this substitution will be done before watch is in control so the real password will end up on the process list as watch runs the resulting command)
--execute=<stuff> tells the command line tool to run <stuff> then exit immediately (otherwise it logs in and waits for you to enter commands/queries/statements).
The rest is just your SELECT statement. Each time watch runs the mysql command it will output the results of this and watch will draw them on your screen.

More detail:

mysql command line syntax
watch command man page
watch command examples with animated example output
some more examples of simple mysql command-line use (the first of many in a simple search, so you will find a lot more examples if you need/want them)

Answer 3 · 2017-03-13 12:10:06Z

My requirements were the same but in Git-Bash. As Git-Bash does not have the watch command I was able to use the While loop as follows:

while true; do mysql -u root -p[password] -e "select * from table"; sleep 5; clear; done

P.s. There should be no space in -p and [password] i.e. -p[password]

My Collections

Tuesday, February 09, 2021

How to auto-refresh/re-run a query every few seconds?

How to auto-refresh/re-run a query every few seconds?

Not the answer you're looking for? Browse other questions tagged mysql mysql-workbench or ask your own question.

Upgrade to Premium Plan