Connecting to the database

[grazioli]

I have succesfully run the provided examples and I am now trying to understand how to connect to the database. I have added another R application to the ones already available. It is a very simple one containing only the code to connect to the database used by "Crop water demand" application and to list its tables. The code is the following:

library(DBI)
library(RMySQL)

con = DBI::dbConnect(MySQL(), user='root', password='example', dbname='edss_db', host='127.0.0.1')
dbListTables(con)
DBI::dbDisconnect(con)

DBI::dbDisconnect(con)

ui <- fluidPage(
  'Hello, world!'
)

server <- function(input, output, session) { } 

However when I run it I get a Container not responsive error. I thought the problem may be the host set to 127.0.0.1 since in Docker it represents the current container and I tried to replace it with db as the MySQL database is named in run_examples.yml file, but I get the same error. What may the issue be?

[cocomice]

hi @grazioli, thank you for using this tool and providing the feedback. The host name should be replaced with db (as you did) to stay consistent with containers' running environment. However, is the code snippet shown here complete? Can you try to paste the following codes in an app.R file, build a docker image for it and then try it again?

library(DBI)
library(RMySQL)
library(shiny)

con = DBI::dbConnect(MySQL(), user='root', password='example', dbname='edss_db', host='db')
dbListTables(con)
DBI::dbDisconnect(con)

ui <- fluidPage(
  'Hello, world!'
)

server <- function(input, output, session) { } 

shinyApp(ui = ui, server = server)

Let me know if it works. Regards!

[grazioli]

Hi @cocomice, it works perfectly now. Thank you very much. Regards!

[grazioli]

Hi @cocomice, I am tying to configure another machine but I still have the "Container unresponsive" error. The machine uses Ubuntu 20.04 and the config files are as follows.

docker.compose.yml:

version:            "3.7"

services:
  sp_app:
    image: cocomcie/shinyproxy:1.0
    restart: always
    ports:
      - "80:8080"
    volumes:
      - type: bind
        source: /var/run/docker.sock
        target: /var/run/docker.sock
      - type: bind
        source: ./log/server
        target: /log
      - type: bind
        source: ./log/container
        target: /container-logs
      - type: bind
        source: ./Shiny/application.yml
        target: /opt/shinyproxy/application.yml
        read_only: true
    environment:
      - VIRTUAL_HOST=test
      - VIRTUAL_PORT=8080
    networks:
      - test-shiny-db

  db:
    image: mysql:8.0
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: "db_pwd"
      MYSQL_DATABASE: db_name
    volumes:
      - type: bind
        source: ./data/db
        target: /var/lib/mysql
    networks:
      - test-shiny-db
    ports:
      - "6603:3306"

  adminer:
    image: adminer
    restart: always
    ports:
      - "8080:8080"
    networks:
      - test-shiny-db

networks:
  test-shiny-db:
    name: test-shiny-db

application.yml

proxy:
  title: Title
  landing-page: /
  heartbeat-rate: 60000
  heartbeat-timeout: 1800000
  port: 8080
  template-path: ./html
  authentication: simple
  admin-groups: [group1]
  container-wait-time: 20000
  users:
  - name: user1
    password: pwd_user1
    groups: group1
  docker:
    internal-networking: true
    bind-address:        127.0.0.1
    container-network:   "test-shiny-db"
  specs:
  - id: App_1
    display-name: App 1
    description: App 1
    container-cmd: ["R", "-e", "shiny::runApp('/root/App1', host='0.0.0.0', port=3838)"]
    container-image: dnaphone/shiny-app-db
    container-network: "${proxy.docker.container-network}"
    access-groups: [group1]

spring:
  servlet:
    multipart:
      max-file-size: 300MB
      max-request-size: 300MB

The R code to connect to the db (reporting also libraries used by the script) is:

library(DBI)
library(RMySQL)
library(shiny)
library(shinydashboard)
library(shinymanager)
library(tidyverse)
library(plotly)
library(vistime) 
library(pool)
library(lubridate)
library(readxl)
library(writexl)

stuffDB <- dbPool(
    RMySQL::MySQL(),
    user = "root",
    password = "db_pwd",
    dbname = "db_name",
    host = "db"
)

The machine also has installed MySQL listening on port 3306. I have set both the "native" and the dockerized MySQL to use TCP/IP instead of socket by adding [client] protocol=tcp to both their /etc/mysql/my.cnf files. If I start the db container I can access both the "native" MySQL and the dockerized one by specifying the port (mysql -P XXXX -u root -p). I also tried connecting using a Python script and there is no problem. Only the Shiny app cannot connect. Do you have any suggestion?

[cocomice]

Hi @grazioli, sorry for the late reply. Most likely your Dockerized shiny app has some issues. Are all r libraries installed together when you are building your app (i.e., by specifying all libraries in dpp.R file) ? you can try to "bash" into the container (e.g.,docker exec -it <container name> /bin/bash) that installs your shiny app and see if the app runs properly.

[grazioli]

Thanks for the answer. I used the same configuration on another machine and it works perfectly. It must be something related with the image as you said.