RMySQL CREATE TABLE error

2 messages · Jeffrey Horner, Anthony Dick

Sat, Jan 10, 2009 8:49 AM #

Not sure why you need to use temporary tables, but if you do you may 
want to add a "COMMIT" statement immediately after your "CREATE 
TEMPORARY TABLE" statement. Using your code, something like this may work:

dbSendQuery(con,"COMMIT")

Scope out this page in the MySQL 5.0 manual:

http://dev.mysql.com/doc/refman/5.0/en/implicit-commit.html

Three notes:

1) It's best to provide small reproducible examples WITHOUT personal 
information such as usernames and passwords (You may want to inform the 
administrators of db.ci.uchicago.edu that you just posted your 
credentials to a publicly available mailing list).

2) It's best to provide software versions, and in particular which 
storage engine you are using with MySQL. For instance innodb tables are 
transaction-safe whereas MyISAM tables are not.

3) Database related questions are best posted to the R-sig-db mailing 
list which you can find here:

http://www.r-project.org/mail.html

Best,

Jeff

Anthony Dick wrote:

Hi all-

I am stumped. The code in A. returns errors at lines 14 and 15 and fails 
to load series1 and series2. However, in B., if temp1 and temp2 are 
called again (which returns a "Table exists" error; see lines 14-17 in 
B.) series1 and series2 load correctly. Any ideas? Also-I am open to any 
suggestions to improve the code as I am a horrific programmer. Thanks

A.

1 > library(RMySQL)
2 > library(psych)
3 > drv=dbDriver("MySQL")
4 > con = dbConnect(drv, user="tsgcst", password = "sOy9Nays", 
dbname="tsgcst", host="db.ci.uchicago.edu")
5 > dbListTables(con)
[1] "TS_descript_spec"        "participant_TS_descript" 
"snr_adultsTS1"           "snr_adultsTS2"           
"time_series_small"       "tvalFDR_small"         6 > temp1 <- 
paste("CREATE TEMPORARY TABLE MyTemporaryTable1 SELECT * FROM 
time_series_small WHERE participant = '005_avg' AND roi = '1033' AND 
timeseries_run = '1';")
7 > temp2 <- paste("CREATE TEMPORARY TABLE MyTemporaryTable2 SELECT * 
FROM time_series_small WHERE participant = '005_avg' AND roi = '1033' 
AND timeseries_run = '2';")
8 > temp3 <- paste("CREATE TEMPORARY TABLE MyTemporaryTable3 SELECT * 
FROM tvalFDR_small WHERE participant = '005_avg' AND roi = '1033' AND 
nogestureFDR > 1.95;")
9 > dbSendQuery(con, temp1)
<MySQLResult:(436,20,1)>
10 > dbSendQuery(con, temp2)
<MySQLResult:(436,20,2)>
11 > dbSendQuery(con, temp3)
<MySQLResult:(436,20,3)>
12 > query.string1 <- paste("SELECT t.timeseries_id, t.timeseries_signal 
FROM MyTemporaryTable1 t, MyTemporaryTable3 v WHERE t.participant = 
v.participant AND t.roi = v.roi AND t.voxel_id = v.voxel_id;",sep="")
13 > query.string2 <- paste("SELECT t.timeseries_id, t.timeseries_signal 
FROM MyTemporaryTable2 t, MyTemporaryTable3 v WHERE t.participant = 
v.participant AND t.roi = v.roi AND t.voxel_id = v.voxel_id;",sep="")
14 > series1 <- dbGetQuery(con, query.string1)
Error in mysqlExecStatement(conn, statement, ...) :
 RS-DBI driver: (could not run statement: Table 
'tsgcst.MyTemporaryTable1' doesn't exist)
15 > series2 <- dbGetQuery(con, query.string2)
Error in mysqlExecStatement(conn, statement, ...) :
 RS-DBI driver: (could not run statement: Table 
'tsgcst.MyTemporaryTable2' doesn't exist)

B.

1 > library(RMySQL)
2 > library(psych)
3 > drv=dbDriver("MySQL")
4 > con = dbConnect(drv, user="tsgcst", password = "sOy9Nays", 
dbname="tsgcst", host="db.ci.uchicago.edu")
5 > dbListTables(con)
[1] "TS_descript_spec"        "participant_TS_descript" 
"snr_adultsTS1"           "snr_adultsTS2"           
"time_series_small"       "tvalFDR_small"         6 > temp1 <- 
paste("CREATE TEMPORARY TABLE MyTemporaryTable1 SELECT * FROM 
time_series_small WHERE participant = '005_avg' AND roi = '1033' AND 
timeseries_run = '1';")
7 > temp2 <- paste("CREATE TEMPORARY TABLE MyTemporaryTable2 SELECT * 
FROM time_series_small WHERE participant = '005_avg' AND roi = '1033' 
AND timeseries_run = '2';")
8 > temp3 <- paste("CREATE TEMPORARY TABLE MyTemporaryTable3 SELECT * 
FROM tvalFDR_small WHERE participant = '005_avg' AND roi = '1033' AND 
nogestureFDR > 1.95;")
9 > dbSendQuery(con, temp1)
<MySQLResult:(436,19,1)>
10 > dbSendQuery(con, temp2)
<MySQLResult:(436,19,2)>
11 > dbSendQuery(con, temp3)
<MySQLResult:(436,19,3)>
12 > query.string1 <- paste("SELECT t.timeseries_id, t.timeseries_signal 
FROM MyTemporaryTable1 t, MyTemporaryTable3 v WHERE t.participant = 
v.participant AND t.roi = v.roi AND t.voxel_id = v.voxel_id;",sep="")
13 > query.string2 <- paste("SELECT t.timeseries_id, t.timeseries_signal 
FROM MyTemporaryTable2 t, MyTemporaryTable3 v WHERE t.participant = 
v.participant AND t.roi = v.roi AND t.voxel_id = v.voxel_id;",sep="")
14 > dbSendQuery(con, temp1)
Error in mysqlExecStatement(conn, statement, ...) :
 RS-DBI driver: (could not run statement: Table 'MyTemporaryTable1' 
already exists)
15 > series1 <- dbGetQuery(con, query.string1)
16 > dbSendQuery(con, temp2)
Error in mysqlExecStatement(conn, statement, ...) :
 RS-DBI driver: (could not run statement: Table 'MyTemporaryTable2' 
already exists)
17 > series2 <- dbGetQuery(con, query.string2)

http://biostat.mc.vanderbilt.edu/JeffreyHorner

Anthony Dick

Sat, Jan 10, 2009 9:08 AM #

Hi Jeff-

Thanks for the tips. Posting my password information is just stupid, and 
I have contacted my administrator.

I am using temporary tables because I am dealing with very large tables 
and using JOINs, and for joining smaller temporary tables is much faster.

Thanks again.

Anthony

Jeffrey Horner wrote:

Not sure why you need to use temporary tables, but if you do you may 
want to add a "COMMIT" statement immediately after your "CREATE 
TEMPORARY TABLE" statement. Using your code, something like this may 
work:

dbSendQuery(con,"COMMIT")

Scope out this page in the MySQL 5.0 manual:

http://dev.mysql.com/doc/refman/5.0/en/implicit-commit.html

Three notes:

1) It's best to provide small reproducible examples WITHOUT personal 
information such as usernames and passwords (You may want to inform 
the administrators of db.ci.uchicago.edu that you just posted your 
credentials to a publicly available mailing list).

2) It's best to provide software versions, and in particular which 
storage engine you are using with MySQL. For instance innodb tables 
are transaction-safe whereas MyISAM tables are not.

3) Database related questions are best posted to the R-sig-db mailing 
list which you can find here:

http://www.r-project.org/mail.html

Best,

Jeff

Anthony Dick wrote:

Hi all-

I am stumped. The code in A. returns errors at lines 14 and 15 and 
fails to load series1 and series2. However, in B., if temp1 and temp2 
are called again (which returns a "Table exists" error; see lines 
14-17 in B.) series1 and series2 load correctly. Any ideas? Also-I am 
open to any suggestions to improve the code as I am a horrific 
programmer. Thanks

A.

1 > library(RMySQL)
2 > library(psych)
3 > drv=dbDriver("MySQL")
4 > con = dbConnect(drv, user="tsgcst", password = "sOy9Nays", 
dbname="tsgcst", host="db.ci.uchicago.edu")
5 > dbListTables(con)
[1] "TS_descript_spec"        "participant_TS_descript" 
"snr_adultsTS1"           "snr_adultsTS2"           
"time_series_small"       "tvalFDR_small"         6 > temp1 <- 
paste("CREATE TEMPORARY TABLE MyTemporaryTable1 SELECT * FROM 
time_series_small WHERE participant = '005_avg' AND roi = '1033' AND 
timeseries_run = '1';")
7 > temp2 <- paste("CREATE TEMPORARY TABLE MyTemporaryTable2 SELECT * 
FROM time_series_small WHERE participant = '005_avg' AND roi = '1033' 
AND timeseries_run = '2';")
8 > temp3 <- paste("CREATE TEMPORARY TABLE MyTemporaryTable3 SELECT * 
FROM tvalFDR_small WHERE participant = '005_avg' AND roi = '1033' AND 
nogestureFDR > 1.95;")
9 > dbSendQuery(con, temp1)
<MySQLResult:(436,20,1)>
10 > dbSendQuery(con, temp2)
<MySQLResult:(436,20,2)>
11 > dbSendQuery(con, temp3)
<MySQLResult:(436,20,3)>
12 > query.string1 <- paste("SELECT t.timeseries_id, 
t.timeseries_signal FROM MyTemporaryTable1 t, MyTemporaryTable3 v 
WHERE t.participant = v.participant AND t.roi = v.roi AND t.voxel_id 
= v.voxel_id;",sep="")
13 > query.string2 <- paste("SELECT t.timeseries_id, 
t.timeseries_signal FROM MyTemporaryTable2 t, MyTemporaryTable3 v 
WHERE t.participant = v.participant AND t.roi = v.roi AND t.voxel_id 
= v.voxel_id;",sep="")
14 > series1 <- dbGetQuery(con, query.string1)
Error in mysqlExecStatement(conn, statement, ...) :
 RS-DBI driver: (could not run statement: Table 
'tsgcst.MyTemporaryTable1' doesn't exist)
15 > series2 <- dbGetQuery(con, query.string2)
Error in mysqlExecStatement(conn, statement, ...) :
 RS-DBI driver: (could not run statement: Table 
'tsgcst.MyTemporaryTable2' doesn't exist)

B.

1 > library(RMySQL)
2 > library(psych)
3 > drv=dbDriver("MySQL")
4 > con = dbConnect(drv, user="tsgcst", password = "sOy9Nays", 
dbname="tsgcst", host="db.ci.uchicago.edu")
5 > dbListTables(con)
[1] "TS_descript_spec"        "participant_TS_descript" 
"snr_adultsTS1"           "snr_adultsTS2"           
"time_series_small"       "tvalFDR_small"         6 > temp1 <- 
paste("CREATE TEMPORARY TABLE MyTemporaryTable1 SELECT * FROM 
time_series_small WHERE participant = '005_avg' AND roi = '1033' AND 
timeseries_run = '1';")
7 > temp2 <- paste("CREATE TEMPORARY TABLE MyTemporaryTable2 SELECT * 
FROM time_series_small WHERE participant = '005_avg' AND roi = '1033' 
AND timeseries_run = '2';")
8 > temp3 <- paste("CREATE TEMPORARY TABLE MyTemporaryTable3 SELECT * 
FROM tvalFDR_small WHERE participant = '005_avg' AND roi = '1033' AND 
nogestureFDR > 1.95;")
9 > dbSendQuery(con, temp1)
<MySQLResult:(436,19,1)>
10 > dbSendQuery(con, temp2)
<MySQLResult:(436,19,2)>
11 > dbSendQuery(con, temp3)
<MySQLResult:(436,19,3)>
12 > query.string1 <- paste("SELECT t.timeseries_id, 
t.timeseries_signal FROM MyTemporaryTable1 t, MyTemporaryTable3 v 
WHERE t.participant = v.participant AND t.roi = v.roi AND t.voxel_id 
= v.voxel_id;",sep="")
13 > query.string2 <- paste("SELECT t.timeseries_id, 
t.timeseries_signal FROM MyTemporaryTable2 t, MyTemporaryTable3 v 
WHERE t.participant = v.participant AND t.roi = v.roi AND t.voxel_id 
= v.voxel_id;",sep="")
14 > dbSendQuery(con, temp1)
Error in mysqlExecStatement(conn, statement, ...) :
 RS-DBI driver: (could not run statement: Table 'MyTemporaryTable1' 
already exists)
15 > series1 <- dbGetQuery(con, query.string1)
16 > dbSendQuery(con, temp2)
Error in mysqlExecStatement(conn, statement, ...) :
 RS-DBI driver: (could not run statement: Table 'MyTemporaryTable2' 
already exists)
17 > series2 <- dbGetQuery(con, query.string2)

Anthony Steven Dick, Ph.D.
Post-Doctoral Fellow
Human Neuroscience Laboratory
Department of Neurology
The University of Chicago
5841 S. Maryland Ave. MC-2030
Chicago, IL 60637
Phone: (773)-834-7770
Email: adick at uchicago.edu
Web: http://home.uchicago.edu/~adick/