Travis Smith Travis Smith - 11 days ago 4
MySQL Question

Bash while loop wait for mysql import

I'm attempting to create a bash script to initialise a database. Right now everything is going well -- I've been able to create the database, create the user, and grant the user permission on the database.

Now it comes to running the import script. I can do it simply by running the mysql command, but then the user sits there for potentially a minute or two with no feedback.

So I decided to use a spinner while the command is running.

i=0
spin='-\|/'
echo "Beginning Data Import. This may take a while..."
while ((mysql -u$newUser -p$newPassword -D$dbName < import.sql) &>/dev/null &); do
i=$(( (i+1) %4 ))
printf "\rImporting milestone SQL (All data)... ${spin:$i:1}"
sleep .3
done


I've used other variations of this command, but they all seem to result in either

a) the spinner just keeps going forever. It never stops, or

b) it runs the mysql import command multiple times

I've tried Googling but can't seem to find anything that answers my question. Most seem to indicate I can do something like

while ((mysql -u$newUser -p$newPassword -D$dbName < l1jdb_m9.sql) &>/dev/null &) != 1


but that doesn't seem to work either, resulting in a syntax error, or usually an infinite loop.

Answer

When you do this:

while ((mysql -u$newUser -p$newPassword -D$dbName < l1jdb_m9.sql) &>/dev/null &)

...the shell is launching mysql in the background.

The result of (mysql -u$newUser -p$newPassword -D$dbName < l1jdb_m9.sql) &>/dev/null & is almost always going to be a true value: that is, starting the command will succeed. while receives this true result and begins iterating. At the end of the loop, it runs the expression again, to see if it should continue iterating. Since the expression runs again, mysql is executed again, in the background, and it returns success again.

The loop will do this forever, launching a new mysql command in the background on every iteration. Since you're launching in the background, nothing is waiting for the result; it's fire-and-forget, and it always returns true.

If you want to wait for the command to complete, you could do something like this:

mysql -u$newUser -p$newPassword -D$dbName < import.sql &>/dev/null &
mysql_pid=$!
while (ps -p $mysql_pid >&-);
do
  i=$(( (i+1) %4 ))
  printf "\rImporting milestone SQL (All data)... ${spin:$i:1}"
  sleep .3
done

That captures the PID of the sub-process ($!), then checks on each loop iteration to see if the process is still running, with ps -p. The >&- discards the output, and ps returns true if the process is running, false otherwise. This will be re-evaluated each iteration, so the loop will end when the process completes.