[kepler-users] problem using ExternalExecution actor to query MySQL from R

[ben leinfelder]

Hi David - 
Without diving deeper into your code I have two thoughts:
1. Are the single quotes around 'ebifarm.c4crop' the culprit? That value seems to come from the $pft variable.
2. Is the DBI package very sensitive to the R version? I see that it was built under 2.8.1 but what seems to be running is 2.7.0. But I imagine you're also using 2.7.0 when you call the bash script directly on the command line.

Perhaps you can add additional debug statements near the offending SQL referenced in the error log.
Thanks,
-ben

On Oct 8, 2010, at 8:21 AM, David LeBauer wrote:

> Hello,
> 
> I have a workflow that is contained in a series of bash scripts, and
> these scripts use R to query MySQL.
> 
> The first bash script works when called from the command line, but not
> through the External Execution actor, and the error is below.
> 
> what works:
> in a shell, I run:
> cd ~/pecantest
> ./bash/pecan.ma.sh $pft 15000 500
> 
> what doesnt:
> in the External Execution actor, I have set the command to
> ./bash/pecan.ma.sh $pft 15000 500
> and the directory to $HOME/pecantest/
> 
> I get the error pasted below.
> 
> the ./bash/pecan.ma.sh contains the following:
> #!/bin/bash
> env PFT=$1 ITER=$2 ENSN=$3 R --vanilla < ./rscripts/pecan.start.R
> R --vanilla < ./rscripts/pecan.MA.runscript.R
> 
> Thank you for your help.
> 
> -David
> 
> ptolemy.kernel.util.IllegalActionException: Executing command
> "./bash/pecan.ma.sh 'ebifarm.c4crop'  15000 500" returned a non-zero
> return value of 1.
> The last input was: null.
> The standard output was:
> R version 2.7.0 (2008-04-22)
> Copyright (C) 2008 The R Foundation for Statistical Computing
> ISBN 3-900051-07-0
> 
> R is free software and comes with ABSOLUTELY NO WARRANTY.
> You are welcome to redistribute it under certain conditions.
> Type 'license()' or 'licence()' for distribution details.
> 
>  Natural language support but running in an English locale
> 
> R is a collaborative project with many contributors.
> Type 'contributors()' for more information and
> 'citation()' on how to cite R or R packages in publications.
> 
> Type 'demo()' for some demos, 'help()' for on-line help, or
> 'help.start()' for an HTML browser interface to help.
> Type 'q()' to quit R.
> 
>> ##input variables
>> pft   <- system("echo $PFT", intern = TRUE)
>> ITER  <- as.numeric(system("echo $ITER", intern = TRUE))
>> M     <- as.numeric(system("echo $ENSN", intern = TRUE))
>> print(cat("PECAn run with ",pft,
> +           "\nmeta-analysis has",ITER,"iterations",
> +           "\nensemble has",M,"config files"))
> PECAn run with  'ebifarm.c4crop'
> meta-analysis has 15000 iterations
> ensemble has 500 config filesNULL
>> save.image(file='pecan.start.Rdata')
>> 
> 
> R version 2.7.0 (2008-04-22)
> Copyright (C) 2008 The R Foundation for Statistical Computing
> ISBN 3-900051-07-0
> 
> R is free software and comes with ABSOLUTELY NO WARRANTY.
> You are welcome to redistribute it under certain conditions.
> Type 'license()' or 'licence()' for distribution details.
> 
>  Natural language support but running in an English locale
> 
> R is a collaborative project with many contributors.
> Type 'contributors()' for more information and
> 'citation()' on how to cite R or R packages in publications.
> 
> Type 'demo()' for some demos, 'help()' for on-line help, or
> 'help.start()' for an HTML browser interface to help.
> Type 'q()' to quit R.
> 
>> library(PECAn, lib.loc = '~/lib/R')
> loading JAGS module
>   basemod
>   bugs
>> load('pecan.start.Rdata')
>> ## 1. get species list based on pft
>> spp <- query.bety.pft_species(pft)
> 
> The error output was: Loading required package: RMySQL
> Loading required package: DBI
> Loading required package: rjags
> Loading required package: coda
> Loading required package: lattice
> Loading Tcl/Tk interface ... done
> Loading required package: XML
> Loading required package: MASS
> Loading required package: ggplot2
> Loading required package: proto
> Loading required package: grid
> Loading required package: reshape
> Loading required package: plyr
> Loading required package: digest
> Loading required package: randtoolbox
> Loading required package: rngWELL
> Loading required package: hdf5
> Warning messages:
> 1: package 'DBI' was built under R version 2.8.1
> 2: package 'rjags' was built under R version 2.8.1
> 3: package 'XML' was built under R version 2.8.1
> Error in mysqlExecStatement(conn, statement, ...) :
>  RS-DBI driver: (could not run statement: 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 'ebifarm.c4crop''))' at line
> 1)
> Calls: query.bety.pft_species ... .valueClassTest -> is -> is ->
> mysqlExecStatement -> .Call
> Execution halted
> 
>  in .ITER.External Execution
> 	at ptolemy.actor.lib.Exec.fire(Exec.java:376)
> 	at ptolemy.actor.AtomicActor.iterate(AtomicActor.java:469)
> 	at ptolemy.actor.sched.StaticSchedulingDirector.fire(StaticSchedulingDirector.java:188)
> 	at ptolemy.actor.CompositeActor.fire(CompositeActor.java:458)
> 	at ptolemy.actor.Manager.iterate(Manager.java:749)
> 	at ptolemy.actor.Manager.execute(Manager.java:351)
> 	at ptolemy.actor.Manager.run(Manager.java:1162)
> 	at ptolemy.actor.Manager$3.run(Manager.java:1215)
> 
> -- 
> David LeBauer, PhD
> Energy Biosciences Institute
> University of Illinois Urbana-Champaign
> 1206 W. Gregory Drive
> Urbana, IL  61801, U.S.A.
> _______________________________________________
> Kepler-users mailing list
> Kepler-users at kepler-project.org
> http://lists.nceas.ucsb.edu/kepler/mailman/listinfo/kepler-users