Popen / Wait - Wait never finishes

user_16488793 0
Viewed 63
import sys
import os
from subprocess import Popen, PIPE, STDOUT

# Transfer Database
print ('Transferring from ' + mysql_source_database)
mysql = Popen(f"mysql -h {mysql_dest_host} -P 3306 -u {mysql_dest_username} -p{mysql_dest_pw} {mysql_dest_database}".split(), stdin=PIPE, stdout=PIPE)
dbnamerewrite = Popen(f"sed s/{mysql_source_database}/{mysql_dest_database}/g".split(), stdin=PIPE, stdout=mysql.stdin)
mysqldump = Popen(f"mysqldump --set-gtid-purged=OFF --column-statistics=0 -h {mysql_source_host} -P 3306 -u {mysql_source_username} -p{mysql_source_pw} {mysql_source_database}".split(), stdout=dbnamerewrite.stdin)
mysql_stdout = mysql.communicate()[0]
mysqldump.wait()

The above code does what I want it to but never stops waiting. Does anyone know how to fix the wait. If I ctrl-c it after the SQL work has finished this is the given error:

^CERROR 1064 (42000) at line 3829: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 3
Traceback (most recent call last):
  File "test.py", line 19, in <module>
    mysql_stdout = mysql.communicate()[0]
  File "/usr/lib/python3.8/subprocess.py", line 1028, in communicate
    stdout, stderr = self._communicate(input, endtime, timeout)
  File "/usr/lib/python3.8/subprocess.py", line 1868, in _communicate
    ready = selector.select(timeout)
  File "/usr/lib/python3.8/selectors.py", line 415, in select
    fd_event_list = self._selector.poll(timeout)
KeyboardInterrupt
subprocess python-3.x python
1 Answers

One thing is that you should drop the explicit call to mysqldump.wait(). According to the docs:

Note: This will deadlock when using stdout=PIPE or stderr=PIPE and the child process generates enough output to a pipe such that it blocks waiting for the OS pipe buffer to accept more data. Use Popen.communicate() when using pipes to avoid that.

mysql.communicate is sufficient in this case, because it will not receive an EOF until all the elements up the pipeline send one. So mysql.communicate() returning directly implies that the other two processes are done.

Another problem is that with the ordering of processes that you have, you will have to call communicate on all of them in reverse order to get data flowing through the pipeline. One solution is to do just that:

db_param = ['-h', mysql_dest_host, '-P', '3306', '-u', mysql_dest_username, f'p{mysql_dest_pw}', mysql_dest_database]

mysql = Popen(['mysql'] + db_param,
              stdin=PIPE, stdout=PIPE)
dbnamerewrite = Popen(['sed', f's/{mysql_source_database}/{mysql_dest_database}/g'],
                      stdin=PIPE, stdout=mysql.stdin)
mysqldump = Popen(['mysqldump', '--set-gtid-purged=OFF', '--column-statistics=0'] + db_param,
                  stdout=dbnamerewrite.stdin)

mysqldump.communicate()
dbnamerewrite.communicate()
mysql_stdout = mysql.communicate()[0]

The other alternative is to set up your pipe in the opposite order, in which case you only need to communicate with the last process:

db_param = ['-h', mysql_dest_host, '-P', '3306', '-u', mysql_dest_username, f'p{mysql_dest_pw}', mysql_dest_database]

mysqldump = Popen(['mysqldump', '--set-gtid-purged=OFF', '--column-statistics=0'] + db_param,
                  stdout=PIPE)
dbnamerewrite = Popen(['sed', f's/{mysql_source_database}/{mysql_dest_database}/g'],
                      stdin=mysqldump.stdout, stdout=PIPE)
mysql = Popen(['mysql'] + db_param, stdin=dbnamerewrite.stdout, stdout=PIPE)

mysql_stdout = mysql.communicate()[0]
user_29887300
Related