Deploying issue in shiny apps.io with mysql database

[Manohar11]

...

Hi I am getting this when I am deployind shiny app through shiny apps.io
I am accessing mysql data base for this app, locally shiny app is working but when deploying throwing this type of error.
Please help me ,its urgent
Thanks in Advance,
Manohar

[andresrcs]

How are you connecting to your MySQL instance? Could you share the connection code?

[Manohar11]

function_extract=function(db_user,db_password,db_name,db_host,db_port){

Acessing MysqlDatabase and queries

mydb <- dbConnect(dbDriver("MySQL"), user = db_user, password = db_password,dbname = db_name, host = db_host, port = db_port)

Campaigns Published

rs = dbSendQuery(mydb, "select * from mau9j_campaigns where is_published=1 ")
data = fetch(rs, n=-1)
Total_Campaigns=subset(data,select=c("name","id"))

Quering for Email Performance

email_stats=dbSendQuery(mydb,"select * from mau9j_emails where is_published=1" )
email_data_m=fetch(email_stats,n=-1)
cid=dbSendQuery(mydb,"select * FROM mau9j_campaign_events where channel ='email'")
Channel_id_data=fetch(cid,n=-1)
channel_id_data_upd=Channel_id_data %>% distinct(channel_id, .keep_all = TRUE)
email_data_m=merge(email_data_m,channel_id_data_upd,by.x="id",by.y="channel_id")
email_data_m=subset(email_data_m,select=c("id","name.x","sent_count","read_count","campaign_id","name.y","id.y","channel"))
email_data_m=email_data_m %>% filter(sent_count >0)
email_data_m=merge(email_data_m,data,by.x="campaign_id",by.y="id")
email_data_m=subset(email_data_m,select=c("name","name.x","sent_count","read_count"))
Email_UpdatedP=email_data_m
colnames(Email_UpdatedP)[colnames(Email_UpdatedP)=="name"]="Campaign"
colnames(Email_UpdatedP)[colnames(Email_UpdatedP)=="name.x"]="Stage of the Campaign"
Email_UpdatedP$SC="SentCount"
Email_UpdatedP$RC="ReadCount"
Email_UpdatedP$SC=paste(Email_UpdatedP$SC,"-",Email_UpdatedP$sent_count)
Email_UpdatedP$RC=paste(Email_UpdatedP$RC,"-",Email_UpdatedP$read_count)
Email_UpdatedP$C1="Channel"
Email_UpdatedP$Channel="Channel-Email"
Email_UpdatedP$Count=paste(Email_UpdatedP$SC,"<<||>>",Email_UpdatedP$RC)
Email_UpdatedP=Email_UpdatedP[c(1,7,8,2,9)]

Audience Size

ld=dbSendQuery(mydb,"select leadlist_id from mau9j_campaign_leadlist_xref inner join mau9j_campaigns where campaign_id = id and is_published=1")
LL_ID=fetch(ld,n=-1)
lln=dbSendQuery(mydb,"select * from mau9j_lead_lists")
LL_Names=fetch(lln,n=-1)
LL_CC=dbSendQuery(mydb,"select * from mau9j_campaign_leadlist_xref")
Leads_campaigns=fetch(LL_CC,n=-1)
Leads_campaigns=merge(Leads_campaigns,data,by.x="campaign_id",by.y="id")
leadcount=dbSendQuery(mydb,"select * from mau9j_lead_lists_leads")
Lead_count_list=fetch(leadcount,n=-1)
Lead_updated=merge(Lead_count_list,Leads_campaigns,by.x="leadlist_id",by.y="leadlist_id")
Lead_updated=subset(Lead_updated,select=c("name","campaign_id","leadlist_id"))
Lead_updated$AudienceSize=NA
AudienceSize=table(Lead_updated$name)
AudienceSize=as.data.frame(AudienceSize)
colnames(AudienceSize)[colnames(AudienceSize)=="Var1"] <- "Campaign"
colnames(AudienceSize)[colnames(AudienceSize)=="Freq"]="AudienceSize"
S_L_N_nn=merge(Leads_campaigns,LL_ID,by.x = "leadlist_id", by.y = "leadlist_id")
S_L_N_nu=merge(LL_Names,S_L_N_nn,by.x="id",by.y="leadlist_id")
TotalSegments=subset(S_L_N_nu,select=c("name.y","name.x"))
colnames(TotalSegments)[colnames(TotalSegments)=="name.y"] <- "Campaign"
colnames(TotalSegments)[colnames(TotalSegments)=="name.x"] <- "Target Segment"
AudienceSize_n=merge(AudienceSize,TotalSegments,by.x="Campaign",by.y="Campaign")
AudienceSize_n=subset(AudienceSize_n,select=c("Campaign","Target Segment","AudienceSize"))

Target Segment with Audience Size

dashboard_Segment=merge(Total_Campaigns,AudienceSize_n,by.x="name",by.y="Campaign")
dashboard_Segment=dashboard_Segment[c(1,3,4)]
dashboard_Segment$TargetSegment="TargetSegment"
dashboard_Segment=dashboard_Segment[c(1,4,2,3)]
dashboard_Segment$SizeofAudience="AudienceSize"
dashboard_Segment=dashboard_Segment[c(1,2,3,5,4)]
colnames(dashboard_Segment)[colnames(dashboard_Segment)=="name"]="Campaign"

Hits and Titles

pandc=dbSendQuery(mydb,"select * from mau9j_campaign_events inner join mau9j_campaigns where mau9j_campaigns.id=mau9j_campaign_events.campaign_id and is_published=1 and mau9j_campaign_events.channel='page'")
pages_c_uid=fetch(pandc,n=-1)
names(pages_c_uid)[30]<-paste("Campaign")
pages_c_uid=subset(pages_c_uid,select=c("Campaign","channel","campaign_id","channel_id"))
pages_c_uid=pages_c_uid %>% filter(channel_id>0)
hp=dbSendQuery(mydb,"select * from mau9j_page_hits")
hits=fetch(hp,n=-1)
page_count_N= merge(hits,pages_c_uid,by.x="page_id",by.y="channel_id")
page_count_M=subset(page_count_N,select=c("Campaign","channel","page_id"))
pg=dbSendQuery(mydb,"select id,title,hits from mau9j_pages ")
pages=fetch(pg,n=-1)
lapply(dbListConnections(MySQL()), dbDisconnect)
Hits_Downloads=merge(page_count_M,pages,by.x="page_id",by.y="id")
Hits_Downloadz=subset(Hits_Downloads,select=c("Campaign","title","channel"))
table_hits=as.data.frame(table(Hits_Downloadz))
colnames(table_hits)[colnames(table_hits)=="Freq"] <- "HitCount"
colnames(table_hits)[colnames(table_hits)=="channel"] <- "Channel"
table_hits$Channel="Channel-Page"
table_hits$C1="Channel"
table_hits=table_hits[c(1,5,2,3,4)]

Smart binding of Email_UpdatedP,table_hits,dashboard_Segment

Smart_Datum=smartbind(Email_UpdatedP,table_hits,dashboard_Segment)

Splitting the smartbind dataframe into multiple dataframes based on campaign wise

Campaigns_split=split(Smart_Datum,f=Smart_Datum$Campaign)
NAMES <-names(Campaigns_split)
lapply(seq_along(Campaigns_split),
function(x) {
assign(NAMES, Campaigns_split[],envir=.GlobalEnv)
}
)

}
Finally using this function to query database

[andresrcs]

@Manohar11

mydb <- dbConnect(dbDriver("MySQL"), user = db_user, password = db_password,dbname = db_name, host = db_host, port = db_port)

Your db_host is a public IP or is a locall IP?, Is your db_host configured to accept remote connections?

[Manohar11]

local IP only
Its working fine able to query from mysql database when running shiny app on rstudio server
but in deploying its throwing this error

[andresrcs]

Shinyapps.io servers are outside your local network, they don't have access to your local IPs, you have to use your network public ip instead and configure port forwarding in your router to redirect the connection to the local ip of your Mysql server.

[Manohar11]

how to do that configuring
and also one more doubt how to pass environment variables to shiny app , if I split a dataframe into multiple dataframes and naming each dataframe
how to save them as r objects and acces them in the shny app

[andresrcs]

@Manohar11

how to do that configuring

This is going to depend largely in your particular network infrastructure, it would be better for you to contact your IT support, or some one with networking knowledge that can give you in person help.

@Manohar11

and also one more doubt how to pass environment variables to shiny app

This is a very different question, please ask it on a new topic and provide a reproducible example about your issue. Please have a look at this guide, to see how to create one for a shiny app

Shiny debugging and reprex guide

Shiny issues can be challenging to resolve relative to other problems with your code or statistical methods. Shiny apps are often large, complex projects with interacting files. When seeking help from others it is considered polite to: First, do your best to work through RStudio's debugging tools to diagnose your issue on your own. Often those shiny logs and tracebacks are useful to others trying to help out. Second, strive to minimize the effort required to replicate your issue. You can do this with a reproducible example ("reprex"). Shiny Debugging Errors in Shiny code can be difficult to track down. If you don't know where your problem is coming from, you can track it down with some o…

[Manohar11]

library(RMySQL)
library(DBI)
library(dplyr)
library(gtools)
library(shinydashboard)
library(data.tree)
library(htmltools)
library(collapsibleTree)
library(devtools)
library(gtools)
library(RJSONIO)
library(knitr)
library(rmarkdown)
options(shiny.host = '0.0.0.0')
options(shiny.port = 5676)
function_extract=function(db_user,db_password,db_name,db_host,db_port){
  # Acessing  MysqlDatabase and queries
  mydb <-  dbConnect(dbDriver("MySQL"), user = db_user, password = db_password,dbname = db_name, host = db_host, port = db_port)
  # Campaigns Published 
  rs = dbSendQuery(mydb, "select  * from mau9j_campaigns where is_published=1 ")
  data = fetch(rs, n=-1)
  Total_Campaigns=subset(data,select=c("name","id"))
  # Quering for Email Performance
  email_stats=dbSendQuery(mydb,"select * from mau9j_emails where is_published=1" )
  email_data_m=fetch(email_stats,n=-1)
  cid=dbSendQuery(mydb,"select * FROM mau9j_campaign_events where  channel ='email'")
  Channel_id_data=fetch(cid,n=-1)
  channel_id_data_upd=Channel_id_data %>% distinct(channel_id, .keep_all = TRUE)
  email_data_m=merge(email_data_m,channel_id_data_upd,by.x="id",by.y="channel_id")
  email_data_m=subset(email_data_m,select=c("id","name.x","sent_count","read_count","campaign_id","name.y","id.y","channel"))
  email_data_m=email_data_m %>% filter(sent_count >0)
  email_data_m=merge(email_data_m,data,by.x="campaign_id",by.y="id")
  email_data_m=subset(email_data_m,select=c("name","name.x","sent_count","read_count"))
  Email_UpdatedP=email_data_m
  colnames(Email_UpdatedP)[colnames(Email_UpdatedP)=="name"]="Campaign"
  colnames(Email_UpdatedP)[colnames(Email_UpdatedP)=="name.x"]="Stage of the Campaign"
  Email_UpdatedP$SC="SentCount"
  Email_UpdatedP$RC="ReadCount"
  Email_UpdatedP$SC=paste(Email_UpdatedP$SC,"-",Email_UpdatedP$sent_count)
  Email_UpdatedP$RC=paste(Email_UpdatedP$RC,"-",Email_UpdatedP$read_count)
  Email_UpdatedP$C1="Channel"
  Email_UpdatedP$Channel="Channel-Email"
  Email_UpdatedP$Count=paste(Email_UpdatedP$SC,"<<||>>",Email_UpdatedP$RC)
  Email_UpdatedP=Email_UpdatedP[c(1,7,8,2,9)]
  # Audience Size 
  ld=dbSendQuery(mydb,"select leadlist_id  from mau9j_campaign_leadlist_xref inner join mau9j_campaigns where  campaign_id = id and is_published=1")
  LL_ID=fetch(ld,n=-1)
  lln=dbSendQuery(mydb,"select * from mau9j_lead_lists")
  LL_Names=fetch(lln,n=-1)
  LL_CC=dbSendQuery(mydb,"select * from mau9j_campaign_leadlist_xref")
  Leads_campaigns=fetch(LL_CC,n=-1)
  Leads_campaigns=merge(Leads_campaigns,data,by.x="campaign_id",by.y="id")
  leadcount=dbSendQuery(mydb,"select * from mau9j_lead_lists_leads")
  Lead_count_list=fetch(leadcount,n=-1)
  Lead_updated=merge(Lead_count_list,Leads_campaigns,by.x="leadlist_id",by.y="leadlist_id")
  Lead_updated=subset(Lead_updated,select=c("name","campaign_id","leadlist_id"))
  Lead_updated$AudienceSize=NA
  AudienceSize=table(Lead_updated$name)
  AudienceSize=as.data.frame(AudienceSize)
  colnames(AudienceSize)[colnames(AudienceSize)=="Var1"] <- "Campaign"
  colnames(AudienceSize)[colnames(AudienceSize)=="Freq"]="AudienceSize"
  S_L_N_nn=merge(Leads_campaigns,LL_ID,by.x = "leadlist_id", by.y = "leadlist_id")
  S_L_N_nu=merge(LL_Names,S_L_N_nn,by.x="id",by.y="leadlist_id")
  TotalSegments=subset(S_L_N_nu,select=c("name.y","name.x"))
  colnames(TotalSegments)[colnames(TotalSegments)=="name.y"] <- "Campaign"
  colnames(TotalSegments)[colnames(TotalSegments)=="name.x"] <- "Target Segment"
  AudienceSize_n=merge(AudienceSize,TotalSegments,by.x="Campaign",by.y="Campaign")
  AudienceSize_n=subset(AudienceSize_n,select=c("Campaign","Target Segment","AudienceSize"))
  # Target Segment with Audience Size
  dashboard_Segment=merge(Total_Campaigns,AudienceSize_n,by.x="name",by.y="Campaign")
  dashboard_Segment=dashboard_Segment[c(1,3,4)]
  dashboard_Segment$TargetSegment="TargetSegment"
  dashboard_Segment=dashboard_Segment[c(1,4,2,3)]
  dashboard_Segment$SizeofAudience="AudienceSize"
  dashboard_Segment=dashboard_Segment[c(1,2,3,5,4)]
  colnames(dashboard_Segment)[colnames(dashboard_Segment)=="name"]="Campaign"
  # Hits and Titles
  pandc=dbSendQuery(mydb,"select * from mau9j_campaign_events inner join mau9j_campaigns where mau9j_campaigns.id=mau9j_campaign_events.campaign_id and is_published=1 and mau9j_campaign_events.channel='page'")
  pages_c_uid=fetch(pandc,n=-1)
  names(pages_c_uid)[30]<-paste("Campaign")
  pages_c_uid=subset(pages_c_uid,select=c("Campaign","channel","campaign_id","channel_id"))
  pages_c_uid=pages_c_uid %>% filter(channel_id>0)
  hp=dbSendQuery(mydb,"select * from mau9j_page_hits")
  hits=fetch(hp,n=-1)
  page_count_N= merge(hits,pages_c_uid,by.x="page_id",by.y="channel_id")
  page_count_M=subset(page_count_N,select=c("Campaign","channel","page_id"))
  pg=dbSendQuery(mydb,"select id,title,hits from mau9j_pages ")
  pages=fetch(pg,n=-1)
  lapply(dbListConnections(MySQL()), dbDisconnect)
  Hits_Downloads=merge(page_count_M,pages,by.x="page_id",by.y="id")
  Hits_Downloadz=subset(Hits_Downloads,select=c("Campaign","title","channel"))
  table_hits=as.data.frame(table(Hits_Downloadz))
  colnames(table_hits)[colnames(table_hits)=="Freq"] <- "HitCount"
  colnames(table_hits)[colnames(table_hits)=="channel"] <- "Channel"
  table_hits$Channel="Channel-Page"
  table_hits$C1="Channel"
  table_hits=table_hits[c(1,5,2,3,4)]
  # Smart binding of Email_UpdatedP,table_hits,dashboard_Segment
  Smart_Datum=smartbind(Email_UpdatedP,table_hits,dashboard_Segment)
  # Splitting the smartbind dataframe into multiple dataframes based on campaign wise
  Campaigns_split=split(Smart_Datum,f=Smart_Datum$Campaign)
  NAMES <-names(Campaigns_split)
  lapply(seq_along(Campaigns_split), 
         function(x) {
           assign(NAMES[x], Campaigns_split[[x]],envir=.GlobalEnv)
         }
  )
  
}
Stark=function_extract(db_user <-'',db_password <-'',db_name <-'',db_host='',db_port <-)

``

[Manohar11]

Thanks Andreas for your patience in explaining things

[andresrcs]

Sorry but I don't undestand your last post, what are you trying to show with that code?

[Manohar11]

no that is moderator initially didnt approved now he approved thats it

[josh]

@Manohar11

how to do that configuring

The documentation would be a good place to start to understand how.