[RndTbl] shell scripting mysql commands

Adam Thompson athompso at athompso.net
Sun Aug 1 22:24:15 CDT 2010


Further to the previous question about shell scripting and MySQL…

 

The script looks generally like this:

 

( commands; commands; commands; 

cat <<__EOF__

sql statements;

sql statements;

__EOF__

 

find $basedir –args | while read VARIABLE; do

sed –i –e ‘s/xxx/yyy/gi’ –e ‘s/aaa/bbb/gi’ $VARIABLE

cat << __EOF__

LOAD DATA INFILE ‘$VARIABLE’…;

UPDATE stuff…;

__EOF__

) | mysql --user=… --password=… --host=… --database=… --batch

 

 

I didn’t really think about order-of-operations until it came back to bite me in the ass.  The script now has these comments in it.  Note carefully the last point in the comments, if you’re ever doing anything like this.

 

# The subshell allows us to start processing the mysql commands as soon

# as they are echo(1)'d, improving parallelism dramatically.

# Note that this does not cause magic to happen - commands will still

# finish executing in order, so it's valid to, say, run sed(1) to pre-

# process a file before echo(1)ing the LOAD DATA INFILE command to

# MySQL.

# In fact, due to stdio buffering issues, the MySQL commands probably

# won't start executing until sometime shortly after they are sent;

# execution is definitely asynchronous, so keep in mind there are two

# separate process "pipelines" happening.  So:

# 1. each shell command may depend on previous shell commands having

#    executed;

# 2. each MySQL statement may depend on previous MySQL statements

#    having executed;

# 3. each MySQL statement may depend on previous *shell* commands

#    having executed;

# 4. but each shell command ***MAY NOT*** depend on previous MySQL

#    statements having executed!

 

-Adam

 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://www.muug.mb.ca/pipermail/roundtable/attachments/20100801/71edd8bb/attachment.html 


More information about the Roundtable mailing list