Timeout using DBI to connect to azure database in shinyio

[annie...@gmail.com]

Hello, 

I have developed a shiny app that connects to an Azure SQL Server, pulls data into the app, runs some analysis interactively using widgets on the app and then sends data back to the database.

I am connecting to the database using the following connection string:

dbconnection <- DBI::dbConnect(RSQLServer::SQLServer(),

                        server="servername.database.windows.net",

                        database="databasename", properties=list(useNTLMv2="true", user="username",

                                                                    password="password"))

and pulling in the data using the following syntax:

dat_df <- dplyr::tbl(dbconnection, "tablename") %>% collect()

the application plots `dat_df` and offers some interactive analysis on the data.

The database is then updated by pressing a button and executing something like:

DBI::dbExecute(dbconnection, "UPDATE [dbo].[tablename] SET column1 = 1 WHERE column2 = 2")

This works both on my own instance of the app and when i publish to shinyio, however after several (usually 4) clicks of the `DBI::dbExecute()` where I am trying to update the database. I get the "Disconnected from the Server. Reload" error in shinyio. No error on my own instance.

Here is a log file:

2018-01-17T16:38:20.007669+00:00 shinyapps[257578]: Warning: Translator is missing window functions:
2018-01-17T16:38:20.007670+00:00 shinyapps[257578]: n_distinct, sd, sdp, varp
2018-01-17T16:38:21.741636+00:00 shinyapps[257578]: Warning in if (config_file() == "") return() :
2018-01-17T16:38:21.741638+00:00 shinyapps[257578]:   the condition has length > 1 and only the first element will be used
2018-01-17T16:38:21.747924+00:00 shinyapps[257578]: Warning in if (config_file() == "") return() :
2018-01-17T16:38:21.747926+00:00 shinyapps[257578]:   the condition has length > 1 and only the first element will be used
2018-01-17T16:38:24.011991+00:00 shinyapps[257578]: Scale for 'colour' is already present. Adding another scale for 'colour',
2018-01-17T16:38:24.011995+00:00 shinyapps[257578]: which will replace the existing scale.
2018-01-17T16:38:25.425157+00:00 shinyapps[257578]: Warning in asMethod(object) : NAs introduced by coercion
2018-01-17T16:38:26.275465+00:00 shinyapps[257578]: Scale for 'colour' is already present. Adding another scale for 'colour',
2018-01-17T16:38:26.275468+00:00 shinyapps[257578]: which will replace the existing scale.
2018-01-17T16:38:53.238693+00:00 shinyapps[257578]: Scale for 'colour' is already present. Adding another scale for 'colour',
2018-01-17T16:38:53.238696+00:00 shinyapps[257578]: which will replace the existing scale.
2018-01-17T16:39:05.084098+00:00 shinyapps[257578]: Warning in asMethod(object) : NAs introduced by coercion
2018-01-17T16:39:05.084379+00:00 shinyapps[257578]: Warning in asMethod(object) : NAs introduced by coercion
2018-01-17T16:39:05.084672+00:00 shinyapps[257578]: Warning in asMethod(object) : NAs introduced by coercion
2018-01-17T16:39:05.084994+00:00 shinyapps[257578]: Warning in asMethod(object) : NAs introduced by coercion
2018-01-17T16:39:06.168973+00:00 shinyapps[257578]: Scale for 'colour' is already present. Adding another scale for 'colour',
2018-01-17T16:39:06.168977+00:00 shinyapps[257578]: which will replace the existing scale.
2018-01-17T16:39:31.680408+00:00 shinyapps[257578]: Scale for 'colour' is already present. Adding another scale for 'colour',
2018-01-17T16:39:31.680411+00:00 shinyapps[257578]: which will replace the existing scale.
2018-01-17T16:39:51.735422+00:00 shinyapps[257578]: Warning: Translator is missing window functions:
2018-01-17T16:39:51.735426+00:00 shinyapps[257578]: n_distinct, sd, sdp, varp
2018-01-17T16:39:51.736669+00:00 shinyapps[257578]: Warning: Translator is missing window functions:
2018-01-17T16:39:51.736672+00:00 shinyapps[257578]: n_distinct, sd, sdp, varp
2018-01-17T16:39:51.741409+00:00 shinyapps[257578]: n_distinct, sd, sdp, varp
2018-01-17T16:39:51.741407+00:00 shinyapps[257578]: Warning: Translator is missing window functions:
2018-01-17T16:39:51.742619+00:00 shinyapps[257578]: Warning: Translator is missing window functions:
2018-01-17T16:39:51.742621+00:00 shinyapps[257578]: n_distinct, sd, sdp, varp
2018-01-17T16:39:51.748508+00:00 shinyapps[257578]: Warning: Translator is missing window functions:
2018-01-17T16:39:51.748510+00:00 shinyapps[257578]: n_distinct, sd, sdp, varp
2018-01-17T16:39:51.972733+00:00 shinyapps[257578]: Warning: Translator is missing window functions:
2018-01-17T16:39:51.972737+00:00 shinyapps[257578]: n_distinct, sd, sdp, varp
2018-01-17T16:39:51.973793+00:00 shinyapps[257578]: Warning: Translator is missing window functions:
2018-01-17T16:39:51.973794+00:00 shinyapps[257578]: n_distinct, sd, sdp, varp
2018-01-17T16:39:51.747143+00:00 shinyapps[257578]: Warning: Translator is missing window functions:
2018-01-17T16:39:51.747145+00:00 shinyapps[257578]: n_distinct, sd, sdp, varp
2018-01-17T16:39:54.101775+00:00 shinyapps[257578]: Warning in if (config_file() == "") return() :
2018-01-17T16:39:54.101778+00:00 shinyapps[257578]:   the condition has length > 1 and only the first element will be used
2018-01-17T16:39:54.107715+00:00 shinyapps[257578]: Warning in if (config_file() == "") return() :
2018-01-17T16:39:54.107722+00:00 shinyapps[257578]:   the condition has length > 1 and only the first element will be used
2018-01-17T16:39:54.113516+00:00 shinyapps[257578]: Warning in if (config_file() == "") return() :
2018-01-17T16:39:54.113518+00:00 shinyapps[257578]:   the condition has length > 1 and only the first element will be used
2018-01-17T16:39:54.263757+00:00 shinyapps[257578]: Scale for 'colour' is already present. Adding another scale for 'colour',
2018-01-17T16:39:54.263761+00:00 shinyapps[257578]: which will replace the existing scale.
2018-01-17T16:39:59.558730+00:00 shinyapps[257578]: Warning: Translator is missing window functions:
2018-01-17T16:39:59.558732+00:00 shinyapps[257578]: n_distinct, sd, sdp, varp
2018-01-17T16:39:59.559961+00:00 shinyapps[257578]: Warning: Translator is missing window functions:
2018-01-17T16:39:59.559963+00:00 shinyapps[257578]: n_distinct, sd, sdp, varp
2018-01-17T16:40:01.661822+00:00 shinyapps[257578]: Scale for 'colour' is already present. Adding another scale for 'colour',
2018-01-17T16:40:01.661825+00:00 shinyapps[257578]: which will replace the existing scale.

As you can see there is no error message, the same warnings are also displayed when I run the app locally.

I've upgraded by app to XXXL (8GB) so I don't think it's overloading. 

Do you have any idea why this is happening?

Many thanks

[Joshua Spiewak]

Try using rsconnect::showLogs to obtain more logs that what is shown by default on the applications dashboard.

I see this:

2018-01-17T16:53:56.853167+00:00 shinyapps[257578]: Warning in odbcDriverConnect("Driver={ODBC Driver 13 for SQL Server};Server=tcp:xxxxx.database.windows.net,1433;Database=xxxxx;Uid=xxxxxx;Pwd=xxxxxxx;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;") :

2018-01-17T16:53:56.853177+00:00 shinyapps[257578]:   [RODBC] ERROR: state IM002, code 0, message [unixODBC][Driver Manager]Data source name not found, and no default driver specified

2018-01-17T16:53:56.853579+00:00 shinyapps[257578]: Warning in odbcDriverConnect("Driver={ODBC Driver 13 for SQL Server};Server=tcp:xxxxx.database.windows.net,1433;Database=xxxxx;Uid=xxxxx;Pwd=xxxxx;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;") :

2018-01-17T16:53:56.853581+00:00 shinyapps[257578]:   ODBC connection failed