Shiny App Connection to MySQL Database

Question

I'm having some trouble publishing this Shiny app. For my question, I don't think ui.R is relevant. Here is server.R:

library(shiny)
library(RMySQL)
library(tidyverse)
library(colorspace)
library(ggplot2)

###     CONNECT AND QUERY THE DATABASE

### uncomment the next line to reconnect to server & re-query the database

source("http://www.mvabl.com/Dashrock/MySQL_connect_query.R")

con <- dbConnect(MySQL(),
    user = 'shiny_apps',
    password = '####',
    host = 'mysql.mvabl.com',
    dbname='sandbox191')

qmain <- dbSendQuery(con, "SELECT * FROM sizes;")

sizes <- as.data.frame(dbFetch(qmain,n=-1),na.rm=TRUE)

###     GENERATE GGPLOT

colors17 <- c("#9D8FAC","#8E96B0","#7F9BB0","#71A0AD","#67A5A7","#63A89E","#64AB94","#6BAC88","#77AD7B","#85AD70","#94AC66","#A4AA5F","#B5A75B","#C4A35B","#D39F5F","#DF9C67","#E99872")

colors6 <- c("#9D8FAC","#6FA1AC","#66AB8F","#8EAC6A","#C1A45A","#E99872")

sizes$Size_Cat <- factor(sizes$Size_Cat,
                         levels = c("n1_4","n5_9","n10_19",
                                    "n20_49","n50_99","n100_249",
                                    "n250_499","n500_999","n1000",
                                    "n1000_1","n1000_2","n1000_3",
                                    "n1000_4"))
sizes$market <- factor(sizes$market,
                         levels = c("NYC","LA","CHI","DC","SF","BOS"))
sizes <- sizes %>%
  filter(market %in% c("NYC","SF"),
         Size_Cat %in% c("n50_99","n100_249","n250_499"))

shinyServer(function(input, output) {

  output$plot <- renderPlot({

    g <- ggplot(sizes)
    g + geom_bar(stat = "identity",
                 position = "dodge",
                 aes_string(x=input$x, y=input$y))
    if (input$color != 'None')
    g <- g + aes_string(color=input$color)

    facets <- paste(input$facet_row, '~', input$facet_col)
    if (facets != '. ~ .')
      g <- g + facet_grid(facets)

      g <- g + scale_fill_manual (values=colors17)
      g <- g + facet_wrap(~ input$facet_row)
      g <- g + labs( y = "Number of Companies", title = "Market Structure")
      g <- g + theme(strip.text.x = element_text(size = 8),
            axis.text.x  = element_text(angle=90, size=6)) 

    if (input$jitter)
      g <- g + geom_jitter()
    if (input$smooth)
      g <- g + geom_smooth()

    print(g)

  }, height=700)

})    
dbHasCompleted(qmain)
dbClearResult(qmain)
dbDisconnect(con)

The result I get is "Object 'sizes' not found"

The thing is, when I run the exact same code locally, but without the Shiny part, I get the expected result. Here's that code:

library(RNeo4j)
library(tidyverse)
library(stringr)
library(MASS)
library(RColorBrewer)
library(colorspace)

### uncomment the next 2 lines to reconnect to server & re-query the database
 setwd("~/Desktop/Dashrock/")
 source("http://www.mvabl.com/Dashrock/MySQL_connect_query.R")

colors17 <- c("#9D8FAC","#8E96B0","#7F9BB0","#71A0AD","#67A5A7","#63A89E","#64AB94","#6BAC88","#77AD7B","#85AD70","#94AC66","#A4AA5F","#B5A75B","#C4A35B","#D39F5F","#DF9C67","#E99872")

colors6 <- c("#9D8FAC","#6FA1AC","#66AB8F","#8EAC6A","#C1A45A","#E99872")

sizes$Size_Cat <- factor(sizes$Size_Cat,
                         levels = c("n1_4","n5_9","n10_19",
                                    "n20_49","n50_99","n100_249",
                                    "n250_499","n500_999","n1000",
                                    "n1000_1","n1000_2","n1000_3",
                                    "n1000_4"))
sizes$market <- factor(sizes$market,
                         levels = c("NYC","LA","CHI","DC","SF","BOS"))
sizes <- sizes %>%
  filter(market %in% c("NYC","SF"),
         Size_Cat %in% c("n50_99","n100_249","n250_499"))

g <- ggplot(sizes)
      g + geom_bar(stat = "identity",
                   position = "dodge",
             aes(x = market, 
                 y = firms,
                 fill = industry),
             color = "grey") +
        scale_fill_manual (values=colors17) +
        facet_wrap(~ Size_Cat) +
        labs( y = "Number of Companies", title = "Market Structure") + 
        theme(strip.text.x = element_text(size = 8),
              axis.text.x  = element_text(angle=90, size=6))

The "connection" source file is publicly viewable on the web at http://www.mvabl.com/Dashrock/MySQL_connect_query.R (exactly the URL from the code blocks above), but I did hash out the PW, so this is not executable. (I don't know how to mask a password for code access to my db.)

Finally, in both of my 3rd-party MySQL clients (Sequel Pro & MySQL Workbench), the sizes table shows up without problem.

Can anyone spot the problem? Or, even better, tell me how I can diagnose it myself?

Thx

Answer 1

I've had something similar before. Try replacing

g <- ggplot(sizes)

with

g <- ggplot(sizes())