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

[David LeBauer]

Hi Ben,

Both of your points were helpful; by both 1) using double instead of
single quotes and
2) installing the previous version of RMySQL which was compatible with R 2.7,

My workflow is working.

Thanks!

David

On Tue, Oct 12, 2010 at 11:55 PM, ben leinfelder
<leinfelder at nceas.ucsb.edu> wrote:
> 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
>



-- 
David LeBauer, PhD
Energy Biosciences Institute
University of Illinois Urbana-Champaign
1206 W. Gregory Drive
Urbana, IL  61801, U.S.A.