I can't seem to figure out what have went wrong here.
I am trying to run a sql query in R and I got the following error:
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 'DROP INDEX `PRIMARY` ON temporary_table' at line 2
The following is my code:
library(RMySQL)
con <- dbConnect(MySQL(),
user="XXX", password="XXX",
dbname="test", host="localhost")
query <- "CREATE TEMPORARY TABLE temporary_table LIKE test.helloworld;
DROP INDEX `PRIMARY` ON temporary_table;"
rs <- dbSendQuery(con, query) #Where the error occurs
I have run the exact same query in mySQL and it is totally working.
Can anyone please point out what I'm doing wrong?
Many thanks!
Execute the query in 2 parts. first part create table and second part drop the index. for ex-
query <- "CREATE TEMPORARY TABLE temporary_table LIKE test.helloworld;"
rs <- dbSendQuery(con, query) #Where the error occurs
query <- "DROP INDEX `PRIMARY` ON temporary_table;"
rs <- dbSendQuery(con, query) #Where the error occurs
I have tested in in php. This have same problem in php. Its running fine.
<?php
$mysqli = new mysqli("localhost", "root", "root", "test");
$result = $mysqli->query("CREATE TEMPORARY TABLE `temporary_table` LIKE t;");
printf("Errormessage: %s\n", $mysqli->error);
$result = $mysqli->query(" DROP INDEX `PRIMARY` ON temporary_table;");
#$result = $mysqli->query("DROP table temporary_table;");
printf("Errormessage: %s\n", $mysqli->error);
?>
root@smart:/var/www# php test.php
Errormessage:
Errormessage:
When i run both the query in one then is say error as you said.
<?php
// mysqli
$mysqli = new mysqli("localhost", "root", "root", "test");
$result = $mysqli->query("CREATE TEMPORARY TABLE `temporary_table` LIKE t;DROP INDEX `PRIMARY` ON temporary_table;");
printf("Errormessage: %s\n", $mysqli->error);
?>
root@smart:/var/www# php test.php
Errormessage: 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 'DROP INDEX `PRIMARY` ON temporary_table' at line 1