Hi Suzanne,

I installed XAMPP. Apache and MySQL are up and running. MySQL seems to work fine because I can create a connection using a test database. But I fail to connect to the metrop database, so I suspect a configuration issue.

Do you have idea suggestion about what else I try?

Shall I discuss with Anudeep? At the last database workshop I think he managed to connect RStudio with the database.

Setup

library(dbplyr)
library(dplyr)
library(DBI)

Failing to connect to the metrop database

# Alternative host I have tried
# host = "http://localhost/"
# host = "160.111.248.55"
# host = "ctfs.si.edu"
# host = "localhost.com"

con <- DBI::dbConnect(
  RMySQL::MySQL(),
  user = "mysqlGuest",
  password = "TEMPpwd$347",
  dbname = "metrop",
  host = "localhost"
)
## Error in .local(drv, ...): Failed to connect to database: Error: Access denied for user 'mysqlGuest'@'localhost' (using password: YES)

Anudeep suggests to add port = 3306, but unfortunately this still fails.

con <- DBI::dbConnect(
  RMySQL::MySQL(),
  user = "mysqlGuest",
  password = "TEMPpwd$347",
  dbname = "metrop",
  # host = "localhost",
  host = "160.111.248.55",
  port = 3306
 )
## Error in .local(drv, ...): Failed to connect to database: Error: Can't connect to MySQL server on '160.111.248.55' (0)

Successfully connecting to local copy of metrop: metrop_new

I created a local copy of the metrop database (I called it metrop_new) – by importing the file metrop.sql that Suzanne sent me – and I successfully connected it.

con <- DBI::dbConnect(
  RMySQL::MySQL(),
  user = "root",
  password = "",
  dbname = "metrop_new"
)
dbListTables(con)
##  [1] "Census"                "CensusQuadrat"        
##  [3] "Coordinates"           "Country"              
##  [5] "CurrentObsolete"       "DBH"                  
##  [7] "DBHAttributes"         "DataCollection"       
##  [9] "Family"                "FeatureTypes"         
## [11] "Features"              "Genus"                
## [13] "Log"                   "Measurement"          
## [15] "MeasurementAttributes" "MeasurementType"      
## [17] "Personnel"             "PersonnelRole"        
## [19] "Quadrat"               "Reference"            
## [21] "RemeasAttribs"         "Remeasurement"        
## [23] "RoleReference"         "Site"                 
## [25] "Species"               "SpeciesInventory"     
## [27] "Specimen"              "Stem"                 
## [29] "SubSpecies"            "TAX2temp"             
## [31] "TAX3temp"              "TSMAttributes"        
## [33] "Tree"                  "TreeAttributes"       
## [35] "TreeTaxChange"         "ViewFullTable"        
## [37] "ViewTaxonomy"

Succeeding to connect to a test MySQL-database

Connecting to the test database

MySQL configuration file

con <- DBI::dbConnect(
  RMySQL::MySQL(),
  user = "root",
  password = "",
  dbname = "test"
)

dbListTables(con)
## [1] "flights" "mtcars"
dbWriteTable(con, "mtcars", mtcars, overwrite = TRUE)
## [1] TRUE
dbListTables(con)
## [1] "flights" "mtcars"
dbListFields(con, "mtcars")
##  [1] "row_names" "mpg"       "cyl"       "disp"      "hp"       
##  [6] "drat"      "wt"        "qsec"      "vs"        "am"       
## [11] "gear"      "carb"
dbReadTable(con, "mtcars")
##                      mpg cyl  disp  hp drat    wt  qsec vs am gear carb
## Mazda RX4           21.0   6 160.0 110 3.90 2.620 16.46  0  1    4    4
## Mazda RX4 Wag       21.0   6 160.0 110 3.90 2.875 17.02  0  1    4    4
## Datsun 710          22.8   4 108.0  93 3.85 2.320 18.61  1  1    4    1
## Hornet 4 Drive      21.4   6 258.0 110 3.08 3.215 19.44  1  0    3    1
## Hornet Sportabout   18.7   8 360.0 175 3.15 3.440 17.02  0  0    3    2
## Valiant             18.1   6 225.0 105 2.76 3.460 20.22  1  0    3    1
## Duster 360          14.3   8 360.0 245 3.21 3.570 15.84  0  0    3    4
## Merc 240D           24.4   4 146.7  62 3.69 3.190 20.00  1  0    4    2
## Merc 230            22.8   4 140.8  95 3.92 3.150 22.90  1  0    4    2
## Merc 280            19.2   6 167.6 123 3.92 3.440 18.30  1  0    4    4
## Merc 280C           17.8   6 167.6 123 3.92 3.440 18.90  1  0    4    4
## Merc 450SE          16.4   8 275.8 180 3.07 4.070 17.40  0  0    3    3
## Merc 450SL          17.3   8 275.8 180 3.07 3.730 17.60  0  0    3    3
## Merc 450SLC         15.2   8 275.8 180 3.07 3.780 18.00  0  0    3    3
## Cadillac Fleetwood  10.4   8 472.0 205 2.93 5.250 17.98  0  0    3    4
## Lincoln Continental 10.4   8 460.0 215 3.00 5.424 17.82  0  0    3    4
## Chrysler Imperial   14.7   8 440.0 230 3.23 5.345 17.42  0  0    3    4
## Fiat 128            32.4   4  78.7  66 4.08 2.200 19.47  1  1    4    1
## Honda Civic         30.4   4  75.7  52 4.93 1.615 18.52  1  1    4    2
## Toyota Corolla      33.9   4  71.1  65 4.22 1.835 19.90  1  1    4    1
## Toyota Corona       21.5   4 120.1  97 3.70 2.465 20.01  1  0    3    1
## Dodge Challenger    15.5   8 318.0 150 2.76 3.520 16.87  0  0    3    2
## AMC Javelin         15.2   8 304.0 150 3.15 3.435 17.30  0  0    3    2
## Camaro Z28          13.3   8 350.0 245 3.73 3.840 15.41  0  0    3    4
## Pontiac Firebird    19.2   8 400.0 175 3.08 3.845 17.05  0  0    3    2
## Fiat X1-9           27.3   4  79.0  66 4.08 1.935 18.90  1  1    4    1
## Porsche 914-2       26.0   4 120.3  91 4.43 2.140 16.70  0  1    5    2
## Lotus Europa        30.4   4  95.1 113 3.77 1.513 16.90  1  1    5    2
## Ford Pantera L      15.8   8 351.0 264 4.22 3.170 14.50  0  1    5    4
## Ferrari Dino        19.7   6 145.0 175 3.62 2.770 15.50  0  1    5    6
## Maserati Bora       15.0   8 301.0 335 3.54 3.570 14.60  0  1    5    8
## Volvo 142E          21.4   4 121.0 109 4.11 2.780 18.60  1  1    4    2
# You can fetch all results:
res <- dbSendQuery(con, "SELECT * FROM mtcars WHERE cyl = 4")
dbFetch(res)
##         row_names  mpg cyl  disp  hp drat    wt  qsec vs am gear carb
## 1      Datsun 710 22.8   4 108.0  93 3.85 2.320 18.61  1  1    4    1
## 2       Merc 240D 24.4   4 146.7  62 3.69 3.190 20.00  1  0    4    2
## 3        Merc 230 22.8   4 140.8  95 3.92 3.150 22.90  1  0    4    2
## 4        Fiat 128 32.4   4  78.7  66 4.08 2.200 19.47  1  1    4    1
## 5     Honda Civic 30.4   4  75.7  52 4.93 1.615 18.52  1  1    4    2
## 6  Toyota Corolla 33.9   4  71.1  65 4.22 1.835 19.90  1  1    4    1
## 7   Toyota Corona 21.5   4 120.1  97 3.70 2.465 20.01  1  0    3    1
## 8       Fiat X1-9 27.3   4  79.0  66 4.08 1.935 18.90  1  1    4    1
## 9   Porsche 914-2 26.0   4 120.3  91 4.43 2.140 16.70  0  1    5    2
## 10   Lotus Europa 30.4   4  95.1 113 3.77 1.513 16.90  1  1    5    2
## 11     Volvo 142E 21.4   4 121.0 109 4.11 2.780 18.60  1  1    4    2
dbClearResult(res)
## [1] TRUE
# Or a chunk at a time
res <- dbSendQuery(con, "SELECT * FROM mtcars WHERE cyl = 4")
while(!dbHasCompleted(res)) {
  chunk <- dbFetch(res, n = 5)
  print(nrow(chunk))
}
## [1] 5
## [1] 5
## [1] 1
# Clear the result
dbClearResult(res)
## [1] TRUE
# Disconnect from the database
dbDisconnect(con)
## [1] TRUE

Adding the table flights to the test database

con <- DBI::dbConnect(
  RMySQL::MySQL(),
  user = "root",
  password = "",
  dbname = "test"
)

copy_to(con, nycflights13::flights, "flights",
  temporary = FALSE, 
  indexes = list(
    c("year", "month", "day"), 
    "carrier", 
    "tailnum",
    "dest"
  ),
  overwrite = TRUE
)

dbListTables(con)
## [1] "flights" "mtcars"

Now that we’ve copied the data, we can use tbl() to take a reference to it:

flights_db <- tbl(con, "flights")
flights_db
## # Source:   table<flights> [?? x 19]
## # Database: mysql 10.1.30-MariaDB [root@localhost:/test]
##     year month   day dep_time sched_dep_time dep_delay arr_time
##    <int> <int> <int>    <int>          <int>     <dbl>    <int>
##  1  2013     1     1      517            515        2.      830
##  2  2013     1     1      533            529        4.      850
##  3  2013     1     1      542            540        2.      923
##  4  2013     1     1      544            545       -1.     1004
##  5  2013     1     1      554            600       -6.      812
##  6  2013     1     1      554            558       -4.      740
##  7  2013     1     1      555            600       -5.      913
##  8  2013     1     1      557            600       -3.      709
##  9  2013     1     1      557            600       -3.      838
## 10  2013     1     1      558            600       -2.      753
## # ... with more rows, and 12 more variables: sched_arr_time <int>,
## #   arr_delay <dbl>, carrier <chr>, flight <int>, tailnum <chr>,
## #   origin <chr>, dest <chr>, air_time <dbl>, distance <dbl>, hour <dbl>,
## #   minute <dbl>, time_hour <chr>