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?

🔥 Save unlimited web pages along with a full PDF snapshot of each page.
Unlock Premium →

Asked

Viewed 25k times

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.

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.

1

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:

2

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]

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

Source: https://dba.stackexchange.com/questions/51472/how-to-auto-refresh-re-run-a-query-every-few-seconds
This web page was saved on Monday, Feb 08 2021.

Upgrade to Premium Plan

✔ Save unlimited bookmarks.

✔ Get a complete PDF copy of each web page

✔ Save PDFs, DOCX files, images and Excel sheets as email attachments.

✔ Get priority support and access to latest features.

Upgrade now →

No comments: