3

Background: I am developing a rscript that pulls data from a mysql database, performs a logistic regression and then inserts the predictions back into the database. I want the entire system to be self contained in the script in case of database failure. This includes all mysql stored procedures that the script depends on to aggregate the data on the backend since these would be deleted in such a database failure.

Question: I'm having trouble creating a stored procedure from an R script. I am running the following:

mySQLDriver <- dbDriver("MySQL")
connect <- dbConnect(mySQLDriver, group = connection)
query <- 
"
DROP PROCEDURE IF EXISTS Test.Tester;
DELIMITER //
CREATE PROCEDURE Test.Tester()
BEGIN
/***DO DATA AGGREGATION***/
END // 
DELIMITER ;
"
sendQuery <- dbSendQuery(connect, query)
dbClearResult(dbListResults(connect)[[1]])
dbDisconnect(connect)

I however get the following error that seems to involve the DELIMITER change.

Error in .local(conn, statement, ...) : 
  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 'DELIMITER //
CREATE PROCEDURE Test.Tester()
BEGIN
/***DO DATA AGGREGATION***/
EN' at line 2

What I've Done: I have spent quite a bit of time searching for the answer, but have come up with nothing. What am I missing?

zx8754
  • 52,746
  • 12
  • 114
  • 209
Andrew Redd
  • 31
  • 4
  • Why not read in data into R, then use R to do aggregation, stats, etc, then write back to database? – zx8754 Mar 14 '16 at 19:12
  • 1
    Maybe you have to escape the backslashes in your SQL query. – cory Mar 14 '16 at 19:13
  • I did this initially but the amount of data quickly filled up memory. While I am familiar with tools that circumvent this issue, my team is interested in doing the data heavy lifting with MySQL for reproducibility and redundancy. (Few use R at the company) – Andrew Redd Mar 14 '16 at 20:39
  • Does your query work when executed in the MySQL client? – hadley Mar 14 '16 at 23:29
  • @hadley It does run in MySQL Workbench and Mac terminal. I believe the MySQL syntax error may come as a result of MySQL not recognizing the DELIMITER statement. – Andrew Redd Mar 15 '16 at 05:03
  • Your second sentence doesn't follow the first. If MySQL doesn't recognise `DELIMITER`, why does it work in the terminal? – hadley Mar 15 '16 at 15:21
  • @ hadley Sorry for the miscommunication. This query does work in both Mac Terminal and MySQL Workbench with no problems with the DELIMITER. I was referring to the mechanism running the MySQL code in R. Does it run line by line in the terminal (or equivalent in windows)? I have tried to change the delimiter to ";;" in response to the concern raised by @cory about canceling the slashes with no change. – Andrew Redd Mar 15 '16 at 16:07

1 Answers1

0

Just wanted to follow up on this string of comments. Thank you for your thoughts on this issue. I have a couple Python scripts that need to have this functionality and I began researching the same topic for Python. I found this question that indicates the answer. The question states:

"The DELIMITER command is a MySQL shell client builtin, and it's recognized only by that program (and MySQL Query Browser). It's not necessary to use DELIMITER if you execute SQL statements directly through an API.

The purpose of DELIMITER is to help you avoid ambiguity about the termination of the CREATE FUNCTION statement, when the statement itself can contain semicolon characters. This is important in the shell client, where by default a semicolon terminates an SQL statement. You need to set the statement terminator to some other character in order to submit the body of a function (or trigger or procedure)."

Hence the following code will run in R:

mySQLDriver <- dbDriver("MySQL")
connect <- dbConnect(mySQLDriver, group = connection)
query <- 
"
CREATE PROCEDURE Test.Tester()
BEGIN
/***DO DATA AGGREGATION***/
END
"
sendQuery <- dbSendQuery(connect, query)
dbClearResult(dbListResults(connect)[[1]])
dbDisconnect(connect)
Community
  • 1
  • 1
Andrew Redd
  • 31
  • 4
  • @`Andrew Redd`▬ I would like some thoughts from you regardin using mysql and R together. I am getting set to learning to integrating R with databases for out-of-memory analytics and have mysql server on my laptop. Could you share some thoughts on benefits of writing a stored procedure and what do you mean by "While I am familiar with tools that circumvent this issue, my team is interested in doing the data heavy lifting with MySQL"; what tools? Looking ahead I'm having a hard time getting over the fact that if I have to query data into R there remains that challenge of RAM limitations Thanks – Bhail Aug 15 '17 at 03:21