RMySQL dbDisconnect is not closing the DB connection

Question

dbDisconnect function of RMySQL library is not closing the DB connection properly. Here is the code I have tried out in my local to test it

library(RMySQL)

execute.query <- function(query){
  mydb_con <- dbConnect(MySQL(), user='username', password='pass', dbname='student', host='db_ip')
  returned_data <- dbGetQuery(mydb_con, query)
  dbDisconnect(mydb_con)
  return(returned_data)
}

for(i in c(1:100)){
  print(paste0("i = ", i));
  execute.query(paste("select now()")); 
}

which executes 100 SQL queries sequentially that should ideally create and close new DB connection for each query but after executing the above code, I could see that using this command watch netstat -nat | grep {db_ip} |wc -l the number of DB connections goes from 0 to 20 until first 20 queries, maintains it at 18-24 connections until 100th query and after 10 seconds the program finished executing, drains to 0 connections.

so my question is why dbDisconnect is not closing the DB connection? is there any nuance that I am missing to understand here?

because of this issue, I am getting Failed to connect to database: Error: Can't create TCP/IP socket (24) error in my production R code (Huge Multithreaded legacy code base) when the connection reaches 1000+

Answer 1

Ideally in a multi threaded environment, you should keep the dbConnect in a static block and the query execution in separate method, then dbdisconnect atlast say a equivalent of finally block in java. When you get connection exceptions, you should try to reconnect. If you open a new connection for every query in a multi threaded environment, then definitely you will have many open files simultaneously and you will get this error.

Answer 2

I would prefer the dbSendQuery() function, ideally from the odbc package and as the previous answer stated, just leave the connection open and then just send subsequent queries. If you want to make sure the connection is valid, then you could use dbIsValid and if it's not valid reconnect again. That should make the query execution pretty fail-safe. Caveat, code might not run directly.

library(odbc)
mydb_con <- dbConnect(MySQL(), user='username', password='pass', dbname='student', host='db_ip')

execute.query <- function(query){
  result <- odbc::dbSendQuery(mydb_con, query)
  returned_data <- odbc::dbFetch(result)
  odbc::dbClearResult(result)
  return(returned_data)
}