No database selected with RMySQL

Question

I'm trying to connect to remote publicly-accessible MySQL server EnsEMBL public server using RMySQL, but when I try to list the tables, an error occurs:

library(RMySQL)

mydb = dbConnect(MySQL(), 
                 user = 'anonymous',
                 port = 5306,
                 host = 'asiadb.ensembl.org')

dbListTables(mydb)

Error in .local(conn, statement, ...) : 
  could not run statement: No database selected

Is there a a way to find out the name? Or Am I making a completely different mistake altogether?

score 13 · Accepted Answer · answered Jan 09 '16 at 20:24

13

You have to specify the name of the db in the dbConnect call. e.g. :

mydb = dbConnect(MySQL(), 
                 user = 'anonymous',
                 port = 5306,
                 host = 'asiadb.ensembl.org',
                 db = 'homo_sapiens_core_83_38')

dbListTables(mydb)

answered Jan 09 '16 at 20:24

milos.ai

3,882
7
31
33

Thanks! Is there a way to find out the names? Do I have to find this out somewhere on their web, or can I get that information by a query from R? – jakub Jan 09 '16 at 20:58
names of folders on this page http://ftp.ensembl.org/pub/release-83/mysql/ seems to be also names of various db – milos.ai Jan 09 '16 at 21:00
Ok, so I have to know the db name beforehand. Thanks for this! – jakub Jan 09 '16 at 21:02

score 2 · Answer 2 · answered Jan 03 '21 at 13:31

It is weird that database = 'testdb' executed with dbExecute in R

db <- dbConnect(RMySQL::MySQL(), 
        user = 'root',
        password = 'pwd123',
        host = 'localhost',
        database = 'testdb'
      )
dbExecute(db, MySQLStatement) # Executed Without Error

But when used dbListTables(db) showing no databases selected.

Changed database into db worked as expected

db <- dbConnect(RMySQL::MySQL(), 
        user = 'root',
        password = 'pwd123',
        host = 'localhost',
        db = 'testdb'
      )

No database selected with RMySQL

2 Answers2