Setup for MariaDB

[f-zehner]

Thought you might be interested in this marginally adapted yml-file for the use with MariaDB.

# needed R packages for DB connection. Use comma separation for multiple dependencies like [package1, package2]
r_packages: [RMariaDB]

# connection using DBI Interface
# Possible to use !expr Sys.getenv(`NAME_ENV_VAR`)
# all args to be passed to DBI::dbConnect
connect:
  drv: !expr RMariaDB::MariaDB()
  host: "localhost"
  dbname: "dbname"
  port: 3306
  user: "username"
  password: "************"

tables:
  credentials:
    tablename: credentials                    # if you want to change tablename
    # user, password, start, expire, admin = mandatory with this name
    # after you can add optionnal custom columns next, getting back in server.R and admin interface
    init: CREATE TABLE {`tablename`} (
            `user`  varchar(100) PRIMARY KEY, 
            `password`  varchar(256),         
            `start`  date,                   
            `expire` date,                   
            `admin` boolean                  

        )
    # Keep same {glue_name*}, update request only if needed.   
    select: SELECT * FROM {`tablename`} WHERE `user` IN ({user*})
    update: UPDATE {`tablename`} SET {`name`} = {value} WHERE `user` IN ({udpate_users*})
    delete: DELETE FROM {`tablename`} WHERE `user` IN ({del_users*})
  pwd_mngt:
    tablename: pwd_mngt                       # if you want to change tablename
    # user, must_change, have_changed, date_change, n_wrong_pwd = mandatory with this name
    #  No additionnal columns here
    init: CREATE TABLE {`tablename`} (
            `user`  varchar(100) PRIMARY KEY, 
            `must_change`  boolean,           
            `have_changed`  boolean,        
            `date_change` date, 
            `n_wrong_pwd` smallint
          )
    # Keep same {glue_name*}, update request only if needed.  
    select: SELECT * FROM {`tablename`} WHERE `user` IN ({user*})
    update: UPDATE {`tablename`} SET {`name`} = {value} WHERE `user` IN ({udpate_users*})
    delete: DELETE FROM {`tablename`} WHERE `user` IN ({del_users*})
  logs:
    tablename: logs                           # if you want to change tablename
    #  all = mandatory with this name
    #  No additionnal columns here
    init: CREATE TABLE {`tablename`} (
            `id` SERIAL PRIMARY KEY,
            `user`  varchar(100),             
            `server_connected` timestamp, 
            `token`  varchar(100),          
            `logout` timestamp,          
            `status`  varchar(100),          
            `app` varchar(100)               
          )
    # Keep same {glue_name*}, update request only if needed. 
    check_token : SELECT * FROM {`tablename`} WHERE `token` IN ({token*})
    select: SELECT * FROM {`tablename`} WHERE `user` IN ({user*}) AND `server_connected` >= {`date_h_begin`}  AND `server_connected` <= {`date_h_end`}
    update: UPDATE {`tablename`} SET {`name`} = {value} WHERE `token` IN ({token*})

Works, although the whole app has become super-slow since I included MariaDB via shinymanager. Will need to see whether I can improve this.

EDIT: It seems that shinymanager does not manage the DB connections properly when using MariaDB. I just copied a MWE from my local dev environment to the server and while the app loaded quickly the first time, when I reloaded the app, it also was really slow (>1min loading time), so it seems it generates connection zombies.

[sciordia]

Hi @f-zehner,

I have the same performance problems as you with an R shiny application that integrates shinymanager + MariaDB, have you been able to solve the problem?

Thank you very much. Sergio

[f-zehner]

Hi @sciordia,

Interesting. Unfortunately, I haven't. Just using a workaround by falling back to a non-DB variant (setting up a dataframe loaded via an RDS that is managed via a different interface based on MariaDB). If you do not need the admin panel and user log information shinymanager provides this is a quite well working variant because it's slim. Did you use my YAML posted above or one you created yourself? (just trying to make sure I did not introduce the performance problems with my YAML)

Best, Fabian

[bthieurmel]

Hi. Some performance improvment on last version 1.0.510.

• For SQL Database, we used now by default one unique connection per session rather connect/disconnect each request (can be configure adding connect_every_request: true in SQL config yml
• We don't import unused database (like admin user, logs, ...) when not needed

[f-zehner]

Hi @bthieurmel Thanks so much for your work! Indeed, the performance issues with MariaDB are resolved 💪 However, unfortunately, I have two subsequent issues. Let me know if you want me to open new issues for those instead of adding them here. They seem, however, related to your recent release, so it might be reasonable to report them in here:

• marginal one: somewhere you seem to have left dev code as "[1] "con" is printed to the console once the database connection is established.
• severe: Can't add a new user in the admin panel. Once I do this, the admin panel reports an error Failed to update user and the console contains the following error: Error : Incorrect integer value: 'FALSE' for columnreco_shinyusers.credentials.adminat row 1 [1366]. For me, this doesn't look like a mistake on my end, but I might be wrong.

Thanks again for your work

EDIT: The admin panel is able, however, to update the credentials-table (e.g., if I ask for password changes), so I'm pretty confident the above issue is a bug in the add user-feature.

[bthieurmel]

Hi. Due to MariaDB boolean gestion as integer and DBI support. We needed to explicitly convert first value to 0 / 1.

Fix on github version.

[sciordia]

Hi @f-zehner and @bthieurmel ,

I'm glad you were able to solve the performance issues with shinymanager, could you update the code you are currently using after the improvements made by @bthieurmel , please?. @bthieurmel, is the version that fixes these problems already available in cran or do you have to use the latest version from github?.

Would it be possible to use a MariaDB database that already has the user table implemented. I would just want shinymanager to check the credentials and if they are correct to allow access.

Thanks in advance to both of you.

Sergio