mysql DB support problems with 2.7.3

Post Reply
vesku79
OpenVpn Newbie
Posts: 4
Joined: Wed Mar 27, 2019 9:20 am

mysql DB support problems with 2.7.3

Post by vesku79 » Mon May 06, 2019 10:54 am

We have been using OpenVPN AS with Mysql successfully quite some time. Changes in 2.7.3 breaks the support as new DB config_db_local has been introduced.
Below is a snippet from as.conf after running the "DB Convert" tool. It looks like "config_db_local" remains as a local SQLite DB. However this is a problem as some essential configuration is only stored in the "config_db_local". For example all changes under "Configuration => VPN Settings" seems to be stored in the config_db_local. I even tried using dbcvt tool to convert config_db_local to mysql but it gives errors after trying to start the OpenVPN process.
We are using AWS and regularly recreate servers so full Mysql support is handy. Do you have any suggestions or do we need to start using persistent config_db_local file? It would be great if OpenVPN AS would use a single DB where all configuration etc. is stored.

# certificates database
certs_db=mysql://<USERNAME>:<PASSWORD>@<MYSQL-HOST>:3306/as_certs

# user properties DB
user_prop_db=mysql://<USERNAME>:<PASSWORD>@<MYSQL-HOST>:3306/as_userprop

# configuration DB
config_db=mysql://<USERNAME>:<PASSWORD>@<MYSQL-HOST>:3306/as_config

# configuration DB Local
config_db_local=sqlite:///~/db/config_local.db

# cluster DB
cluster_db=mysql://<USERNAME>:<PASSWORD>@<MYSQL-HOST>:3306/as_cluster

# notification DB
notification_db=mysql://<USERNAME>:<PASSWORD>@<MYSQL-HOST>:3306/as_notification

# log DB
log_db=sqlite:///~/db/log.db

novaflash
I should be on the dev team.
Posts: 1017
Joined: Fri Apr 13, 2012 8:43 pm

Re: mysql DB support problems with 2.7.3

Post by novaflash » Mon May 06, 2019 11:00 am

Well, this was actually done on purpose, for clustering mode. That local DB should remain local. The rest can be pulled from MySQL but there is a need for a local DB file. I am pretty sure you can't run the local DB in MySQL at the moment. It's something we could look into but it kind of defeats the whole purpose.

vesku79
OpenVpn Newbie
Posts: 4
Joined: Wed Mar 27, 2019 9:20 am

Re: mysql DB support problems with 2.7.3

Post by vesku79 » Tue May 07, 2019 7:52 am

In AWS it's common to use concept called Auto Scaling Group where virtual servers can be killed at any time and identical server is launched from an image. Image does not hold any configuration like as.conf or secrets like DB passwords. These are injected during server launch using tools like Ansible.
So essentially servers can be recreated from scratch and automation is used to configure eg. as.conf. This requires that the DB/configuration need to be persisted separately. So far MySQL has worked nicely so could you reconsider the clustering approach? I mean it doesn't sound that great that part of the data is Mysql and partly in Sqlite.

novaflash
I should be on the dev team.
Posts: 1017
Joined: Fri Apr 13, 2012 8:43 pm

Re: mysql DB support problems with 2.7.3

Post by novaflash » Tue May 07, 2019 7:58 am

The entire idea of the local database is that local settings like which network interface to listen to are stored locally on the server, and are not shared between other nodes.

Changing this design now while we need this design to be this way is not something that we will easily reconsider.

I suggest you work towards getting the necessary data in the local db using the command line so you can automate it. All other information can still be stored in MySQL, but some data simply must be locally stored.

Post Reply