Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

up vote 1 down vote accepted

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();

Give it a Try !!!

Caveat

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

share|improve this answer
    
I ended up re-writing the PHP script that fills the database in C# with a WPF GUI, but since this is the only Windows answer, I've accepted it! Thanks for your effort. –  Danny Beckett Oct 17 '13 at 12:13

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 --database=dbname --user=username --password=$(cat /path/to/file/with/mysql/password) --execute="select *, (select count(*) from domains) as count, (select 456976-count(*) from domains) as remaining from domains order by domain desc limit 0, 1;"'

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:

share|improve this answer
    
Good answer David, thanks! Only problem is, I'm running Windows. I've given you an upvote anyway though! –  Danny Beckett Oct 14 '13 at 12:47
    
It would be worth mentioning in your question that you are using Windows, in case others don't spot you comment here and provide more non-windows solutions. The command line tools for mysql are there in Windows, so you might just need to find a watch equivalent. Alternately, you could write a simple batch file or power shell script that infinitely loops (until ctrl+c is pressed), sleeping for a bit then clearing the screen then running your query using the mysql command line, and run script - not as neat as watch but it should achieve the goal in this case. –  David Spillett Oct 14 '13 at 13:57

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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