Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Better support for hosted database services using maintenance mode #95

Closed
robotdan opened this issue Mar 24, 2019 · 17 comments
Closed

Better support for hosted database services using maintenance mode #95

robotdan opened this issue Mar 24, 2019 · 17 comments
Assignees
Labels
enhancement New feature or request
Milestone

Comments

@robotdan
Copy link
Member

robotdan commented Mar 24, 2019

Enhance Maintenance Mode to configure 3rd party database services

Problem

FusionAuth has a feature called maintenance mode. During startup this allows FusionAuth possible configure the database silently, or through an interactive setup mode.

Some services such as PostgreSQL in Azure, do not offer a root user configuration. Instead, a user is created that has authority to create a schema.

The FusionAuth maintenance mode and silent configuration mode request a super user credentials, and this is not always available.

Solution

Enhance Maintenance Mode to account for this possibility and allow silent configuration or interactive db configurations using maintenance mode on 3rd party db services.

Ensure db configuration can handle:

  1. https://www.digitalocean.com/products/managed-databases/
  2. https://azure.microsoft.com/en-us/services/postgresql/
  3. https://azure.microsoft.com/en-us/services/mysql/
  4. Stolon https://github.com/sorintlab/stolon
  5. Others?

Local and AWS RDS flavors of PostgreSQL and MySQL work today.

Alternatives/workarounds

If you run into issues with this today, you have a few options:

  1. Try to complete maintenance mode by entering the user credentials offered to you by your database service provider in both the super user and the user sections of the db maintenance mode configuration. This may work.

  2. If that doesn't work, you may manually create the db schema using the advanced install instructions.
    https://fusionauth.io/docs/v1/tech/installation-guide/fusionauth-app#advanced-installation

Additional context

Similar issues reported via the following:

fusionauth-containers Issue #7
fusionauth-issues Issue #94
fusionauth-issues Issuer #115
fusionauth-issues Issue #681

Thanks to @davidmw, @MatthewLymer, @hengel2810 and @plunkettscott for reporting their issues.

How to vote

Please give us a thumbs up or thumbs down as a reaction to help us prioritize this feature. Feel free to comment if you have a particular need or comment on how this feature should work.

@robotdan robotdan added the enhancement New feature or request label Mar 24, 2019
@robotdan robotdan added this to Backlog in FusionAuth Issues via automation May 8, 2019
@tyduptyler13
Copy link

See also (internal issue):
inversoft/inversoft-maintenance-mode#1

@robotdan robotdan mentioned this issue Nov 8, 2019
@sanderkooger
Copy link

Hey @robotdan

Im trying to get fusionauth running using azure database for mysql, but the installer fails.
https://azure.microsoft.com/en-us/services/mysql/

As far as i know Azure expects a user like rootnamething@instancename

For Superuser credentials i used: rootname@tif-mysql-prod

for the superuser it works, database is being created, but then fusionauth can not use it after because it gets stuck on:

The database and schema exist, but for some reason the ordinary user you specified cannot connect to it. Perhaps the user already existed and you provided the wrong password. The error from the database was [The connection string may not be right. Please visit portal for references.].

When i change the fusionauth username to fusionauth@tif-mysql-prod it throws a different error

Unable to create the ordinary user using the username and password you provided. Perhaps the username is invalid or uses a reserved word. The error from the database was [You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-mysql-prod IDENTIFIED BY '[Lets remove this for a moment]'' at line 1].

How to resolve?
Workarrounds are no working
Manually creating DB and user is not working

Im kinda stuck

@mooreds
Copy link
Collaborator

mooreds commented May 5, 2020

@sanderkooger this issue is closed so won't get much attention. I'd suggest creating a new github issue or posting in the forums: https://fusionauth.io/community/forum/

Edit: whoops, this issue is open. Thanks for giving us additional feedback.

@sanderkooger
Copy link

@mooreds That just made me laugh on this amazingly "confined" day!!
Would it work if i copy the database from another clean install over to mysql for azure?

@sanderkooger
Copy link

@mooreds Do you have an idea on how to connect to an azure mysql Database, I have set up an environment to troubleshoot this one.

@sanderkooger
Copy link

sanderkooger commented Jun 15, 2020

I took a deepdive;
on localhost DB works (mysql 8.0)
Using mysqlfor azure does not work ( version 8.0) with above problems

I migrated the local working DB to mysqlforazure. including a user with full rights and the same password.

I changed the connectionstring in the fusionauth.properties file.

#------------------------------------------------------------------------------
# Database
# - Specify the URL and credentials for your database here
# - Required on each node running FusionAuth App webservice
#------------------------------------------------------------------------------
database.url=jdbc:mysql://localhost:3306/fusionauth?serverTimezone=UTC&useSSL=true&allowPublicKeyRetrieval=true
database.username=fusionauth
database.password=xxxxxxxxxxx 
#------------------------------------------------------------------------------
# Database
# - Specify the URL and credentials for your database here
# - Required on each node running FusionAuth App webservice
#------------------------------------------------------------------------------
database.url=jdbc:mysql://thisisfashion-mysql-db.mysql.database.azure.com:3306/fusionauth?serverTimezone=UTC&useSSL=true&allowPublicKeyRetrieval=true
database.username=fusionauth
database.password=xxxx

As soon as i restart the service it leads me to the installation wizzard. When i change back to local host it works.

I ruled out connection issues between server and DB, I am able to connect using phpMyAdmin using the fusionauth database credentials.

@robotdan and @mooreds Un peu d'aide SVP ;)

@mooreds
Copy link
Collaborator

mooreds commented Jun 15, 2020

@sanderkooger unfortunately I think that this aspect of FA needs a bit of work, as outlined in this issue. My only suggestion would be to run the SQL manually using the advanced installation: https://fusionauth.io/docs/v1/tech/installation-guide/fusionauth-app#advanced-installation

which should hopefully sidestep the installation wizard.

@sanderkooger
Copy link

@sanderkooger unfortunately I think that this aspect of FA needs a bit of work, as outlined in this issue. My only suggestion would be to run the SQL manually using the advanced installation: https://fusionauth.io/docs/v1/tech/installation-guide/fusionauth-app#advanced-installation

which should hopefully sidestep the installation wizard.

I already did. I even went as far as to copy over a working database from a local mysql server, adding the same user with the same password and changing the config file. Even then it does not want to work.

You are more than welcome to troubleshoot with me.

@robotdan
Copy link
Member Author

@sanderkooger If you have a URL we can test with that may help, otherwise we'll have to stand up a similar environment in Azure and do some testing to either identify a work around or a fix on our end.

@sanderkooger
Copy link

sanderkooger commented Jun 15, 2020

@robotdan the Database is not in use yet, so I could just give you the root credentials in private. I would be glad to be of service.
If needed I am also willing do temporary deploy a Postgres server for you guys so you could help #681 out too.

Feel free to shoot me a mail on sander@thisisfashion.tv

@robotdan
Copy link
Member Author

Re: MySQL in Azure, see comments in #681 (comment)

@saul-data
Copy link

Managed Mysql on Digitalocean cant load all the tables from mysql.sql - each table requires a primary key, something to do with performance on replication.

@saul-data
Copy link

saul-data commented Jul 8, 2020

I am now getting this error for Mysql and I cant figure out what causes it - this is hosted on Scalegrid which deploys into Digitalocean (to be close to our kubernetes cluster) because I couldn't get Managed Digitalocean Mysql to work.

I tried both Mysql 8 and Mysql 5.7 - both gave the same error. I originally thought it might have been about native password but doesnt seem to be the case.

jdbc:mysql://xxx.mongodirector.com:3306/fusionauth?useSSL=true&requireSSL=true

WARNING: An illegal reflective access operation has occurred
WARNING: Illegal reflective access by com.inversoft.lang.ClassLoaderTools (file:/usr/local/fusionauth/fusionauth-app/web/WEB-INF/lib/inversoft-database-0.4.12.jar) to method java.net.URLClassLoader.addURL(java.net.URL)
WARNING: Please consider reporting this to the maintainers of com.inversoft.lang.ClassLoaderTools
WARNING: Use --illegal-access=warn to enable warnings of further illegal reflective access operations
WARNING: All illegal access operations will be denied in a future release

@voidmain
Copy link
Member

voidmain commented Jul 8, 2020

Just to leave a little more information on this issue.

FusionAuth is not designed to work on constrained or non-standard systems. It assumes that the JVM is a standard JVM, the server behaves like a standard Linux/Windows installation, and the database behaves like a standard installation of PostgreSQL or MySQL.

We can look into changing this in the future, but as it stands, some hosting environments won't support the current version of FusionAuth because they these types of constraints.

@robotdan
Copy link
Member Author

robotdan commented Jul 9, 2020

Re: the warning that @saul-gush is seeing - that is simply a warning as far as I can tell. If the system is not starting up or not connecting to the database, I don't believe that is the root cause. That is just JDK 14 complaining.

@mrspartak
Copy link

Can't figure out how to set up with DigitalOcean managed Postgres.
I have Docker service running. Set up a database with schema from the website. I made a configuration file
Also, DigitalOcean asks to provide ?sslmode=require and I got error parsing JDBC when I provide it.

//this config is located to /usr/local/fusionauth/config/fusionauth.properties
database.url=jdbc:postgresql://do-postgres.db.ondigitalocean.com:25060/fusionauth
database.username=[login]
database.password=[password]

ENV configuration

FUSIONAUTH_SEARCH_ENGINE_TYPE=database
FUSIONAUTH_URL=[my domain]
FUSIONAUTH_MEMORY=512M
FUSIONAUTH_RUNTIME_MODE=development

And this is my log from service with an error
org.apache.catalina.LifecycleException: The connector cannot start since the specified port value of [-1] is invalid

NOTE: Picked up JDK_JAVA_OPTIONS:  --add-opens=java.base/java.lang=ALL-UNNAMED --add-opens=java.base/java.io=ALL-UNNAMED --add-opens=java.rmi/sun.rmi.transport=ALL-UNNAMED
NOTE: Picked up JDK_JAVA_OPTIONS:  --add-opens=java.base/java.lang=ALL-UNNAMED --add-opens=java.base/java.io=ALL-UNNAMED --add-opens=java.rmi/sun.rmi.transport=ALL-UNNAMED
22-Jul-2020 18:02:36.060 WARNING [main] org.apache.tomcat.util.digester.SetPropertiesRule.begin [SetPropertiesRule]{Server} Setting property 'port' to '${fusionauth.management.port}' did not find a matching property.
22-Jul-2020 18:02:36.146 WARNING [main] org.apache.catalina.startup.SetAllPropertiesRule.begin [SetAllPropertiesRule]{Server/Service/Connector} Setting property 'port' to '${fusionauth.http.port}' did not find a matching property.
22-Jul-2020 18:02:36.146 WARNING [main] org.apache.catalina.startup.SetAllPropertiesRule.begin [SetAllPropertiesRule]{Server/Service/Connector} Setting property 'redirectPort' to '${fusionauth.https.port}' did not find a matching property.
22-Jul-2020 18:02:36.161 WARNING [main] org.apache.catalina.startup.SetAllPropertiesRule.begin [SetAllPropertiesRule]{Server/Service/Connector} Setting property 'port' to '${fusionauth.https.port}' did not find a matching property.
22-Jul-2020 18:02:36.185 WARNING [main] org.apache.catalina.startup.SetAllPropertiesRule.begin [SetAllPropertiesRule]{Server/Service/Connector} Setting property 'port' to '${fusionauth.ajp.port}' did not find a matching property.
22-Jul-2020 18:02:36.271 INFO [main] org.apache.catalina.core.AprLifecycleListener.lifecycleEvent The APR based Apache Tomcat Native library which allows optimal performance in production environments was not found on the java.library.path: [/opt/openjdk/lib/server:/opt/openjdk/lib:/opt/openjdk/../lib:/usr/java/packages/lib:/usr/lib64:/lib64:/lib:/usr/lib]
22-Jul-2020 18:02:36.586 INFO [main] org.apache.coyote.AbstractProtocol.init Initializing ProtocolHandler ["http-nio-auto-1"]
22-Jul-2020 18:02:36.608 INFO [main] org.apache.tomcat.util.net.NioSelectorPool.getSharedSelector Using a shared selector for servlet write/read
22-Jul-2020 18:02:36.630 INFO [main] org.apache.coyote.AbstractProtocol.init Initializing ProtocolHandler ["https-jsse-nio-auto-2"]
22-Jul-2020 18:02:36.868 INFO [main] org.apache.tomcat.util.net.NioSelectorPool.getSharedSelector Using a shared selector for servlet write/read
22-Jul-2020 18:02:36.871 INFO [main] org.apache.coyote.AbstractProtocol.init Initializing ProtocolHandler ["ajp-nio-127.0.0.1-auto-3"]
22-Jul-2020 18:02:36.873 INFO [main] org.apache.tomcat.util.net.NioSelectorPool.getSharedSelector Using a shared selector for servlet write/read
22-Jul-2020 18:02:36.874 INFO [main] org.apache.catalina.startup.Catalina.load Initialization processed in 944 ms
22-Jul-2020 18:02:36.881 INFO [main] org.apache.catalina.core.StandardService.startInternal Starting service [Catalina]
22-Jul-2020 18:02:36.881 INFO [main] org.apache.catalina.core.StandardEngine.startInternal Starting Servlet Engine: Apache Tomcat/8.5.53
Jul 22, 2020 6:02:39 PM org.apache.jasper.servlet.TldScanner scanJars
INFO: At least one JAR was scanned for TLDs yet contained no TLDs. Enable debug logging for this logger for a complete list of JARs that were scanned but no TLDs were found in them. Skipping unneeded JARs during scanning can improve startup time and JSP compilation time.
Jul 22, 2020 6:02:39.523 PM INFO  io.fusionauth.api.configuration.PropertiesFileFusionAuthConfiguration - Loading FusionAuth configuration file [/usr/local/fusionauth/config/fusionauth.properties]
Jul 22, 2020 6:02:39.527 PM INFO  io.fusionauth.api.configuration.PropertiesFileFusionAuthConfiguration - Override property [fusionauth-app.url], set to [my domain] from ENV.FUSIONAUTH_URL
Jul 22, 2020 6:02:39.527 PM INFO  io.fusionauth.api.configuration.PropertiesFileFusionAuthConfiguration - Override property [fusionauth-app.search-engine-type], set to [database] from ENV.FUSIONAUTH_SEARCH_ENGINE_TYPE
Jul 22, 2020 6:02:39.528 PM INFO  io.fusionauth.api.configuration.PropertiesFileFusionAuthConfiguration - Override property [fusionauth.runtime-mode], set to [Development] from ENV.FUSIONAUTH_RUNTIME_MODE.
Jul 22, 2020 6:02:39.528 PM INFO  io.fusionauth.api.configuration.PropertiesFileFusionAuthConfiguration - Set Max HTTP header size to [10240].
Jul 22, 2020 6:02:40.272 PM INFO  io.fusionauth.api.configuration.PropertiesFileFusionAuthConfiguration - Loading FusionAuth configuration file [/usr/local/fusionauth/config/fusionauth.properties]
Jul 22, 2020 6:02:40.273 PM INFO  io.fusionauth.api.configuration.PropertiesFileFusionAuthConfiguration - Override property [fusionauth-app.url], set to [my domain] from ENV.FUSIONAUTH_URL
Jul 22, 2020 6:02:40.273 PM INFO  io.fusionauth.api.configuration.PropertiesFileFusionAuthConfiguration - Override property [fusionauth-app.search-engine-type], set to [database] from ENV.FUSIONAUTH_SEARCH_ENGINE_TYPE
Jul 22, 2020 6:02:40.273 PM INFO  io.fusionauth.api.configuration.PropertiesFileFusionAuthConfiguration - Override property [fusionauth.runtime-mode], set to [Development] from ENV.FUSIONAUTH_RUNTIME_MODE.
Jul 22, 2020 6:02:40.273 PM INFO  io.fusionauth.api.configuration.PropertiesFileFusionAuthConfiguration - Set Max HTTP header size to [10240].
Jul 22, 2020 6:02:40.289 PM INFO  io.fusionauth.api.configuration.PropertiesFileFusionAuthConfiguration - Loading FusionAuth configuration file [/usr/local/fusionauth/config/fusionauth.properties]
Jul 22, 2020 6:02:40.289 PM INFO  io.fusionauth.api.configuration.PropertiesFileFusionAuthConfiguration - Override property [fusionauth-app.url], set to [my domain] from ENV.FUSIONAUTH_URL
Jul 22, 2020 6:02:40.289 PM INFO  io.fusionauth.api.configuration.PropertiesFileFusionAuthConfiguration - Override property [fusionauth-app.search-engine-type], set to [database] from ENV.FUSIONAUTH_SEARCH_ENGINE_TYPE
Jul 22, 2020 6:02:40.289 PM INFO  io.fusionauth.api.configuration.PropertiesFileFusionAuthConfiguration - Override property [fusionauth.runtime-mode], set to [Development] from ENV.FUSIONAUTH_RUNTIME_MODE.
Jul 22, 2020 6:02:40.289 PM INFO  io.fusionauth.api.configuration.PropertiesFileFusionAuthConfiguration - Set Max HTTP header size to [10240].
Jul 22, 2020 6:02:40.581 PM INFO  io.fusionauth.app.maintenance.FusionAuthMaintenanceModeWorkflow - Determine database status : NO_SCHEMA
Jul 22, 2020 6:02:40.582 PM INFO  org.primeframework.mvc.servlet.PrimeServletContextListener - Initializing Prime
Jul 22, 2020 6:02:40.583 PM INFO  io.fusionauth.app.maintenance.guice.FusionAuthMaintenanceModeModule -

---------------------------------------------------------------------------------------------------------
--------------------------------------- Entering Maintenance Mode ---------------------------------------
---------------------------------------------------------------------------------------------------------

Jul 22, 2020 6:02:40.653 PM INFO  io.fusionauth.api.configuration.PropertiesFileFusionAuthConfiguration - Loading FusionAuth configuration file [/usr/local/fusionauth/config/fusionauth.properties]
Jul 22, 2020 6:02:40.653 PM INFO  io.fusionauth.api.configuration.PropertiesFileFusionAuthConfiguration - Override property [fusionauth-app.url], set to [my domain] from ENV.FUSIONAUTH_URL
Jul 22, 2020 6:02:40.653 PM INFO  io.fusionauth.api.configuration.PropertiesFileFusionAuthConfiguration - Override property [fusionauth-app.search-engine-type], set to [database] from ENV.FUSIONAUTH_SEARCH_ENGINE_TYPE
Jul 22, 2020 6:02:40.653 PM INFO  io.fusionauth.api.configuration.PropertiesFileFusionAuthConfiguration - Override property [fusionauth.runtime-mode], set to [Development] from ENV.FUSIONAUTH_RUNTIME_MODE.
Jul 22, 2020 6:02:40.653 PM INFO  io.fusionauth.api.configuration.PropertiesFileFusionAuthConfiguration - Set Max HTTP header size to [10240].
Jul 22, 2020 6:02:40.654 PM INFO  com.inversoft.maintenance.MaintenanceModePoller - Poller started to Wait for configuration to be completed.
22-Jul-2020 18:02:40.695 SEVERE [main] org.apache.catalina.core.StandardService.startInternal Failed to start connector [Connector[HTTP/1.1-auto-1]]
	org.apache.catalina.LifecycleException: The connector cannot start since the specified port value of [-1] is invalid
		at org.apache.catalina.connector.Connector.startInternal(Connector.java:1048)
		at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183)
		at org.apache.catalina.core.StandardService.startInternal(StandardService.java:440)
		at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183)
		at org.apache.catalina.core.StandardServer.startInternal(StandardServer.java:766)
		at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183)
		at org.apache.catalina.startup.Catalina.start(Catalina.java:688)
		at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
		at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
		at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
		at java.base/java.lang.reflect.Method.invoke(Method.java:564)
		at org.apache.catalina.startup.Bootstrap.start(Bootstrap.java:343)
		at org.apache.catalina.startup.Bootstrap.main(Bootstrap.java:474)
22-Jul-2020 18:02:40.696 SEVERE [main] org.apache.catalina.core.StandardService.startInternal Failed to start connector [Connector[HTTP/1.1-auto-2]]
	org.apache.catalina.LifecycleException: The connector cannot start since the specified port value of [-1] is invalid
		at org.apache.catalina.connector.Connector.startInternal(Connector.java:1048)
		at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183)
		at org.apache.catalina.core.StandardService.startInternal(StandardService.java:440)
		at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183)
		at org.apache.catalina.core.StandardServer.startInternal(StandardServer.java:766)
		at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183)
		at org.apache.catalina.startup.Catalina.start(Catalina.java:688)
		at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
		at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
		at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
		at java.base/java.lang.reflect.Method.invoke(Method.java:564)
		at org.apache.catalina.startup.Bootstrap.start(Bootstrap.java:343)
		at org.apache.catalina.startup.Bootstrap.main(Bootstrap.java:474)
22-Jul-2020 18:02:40.697 SEVERE [main] org.apache.catalina.core.StandardService.startInternal Failed to start connector [Connector[AJP/1.3-auto-3]]
	org.apache.catalina.LifecycleException: The connector cannot start since the specified port value of [-1] is invalid
		at org.apache.catalina.connector.Connector.startInternal(Connector.java:1048)
		at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183)
		at org.apache.catalina.core.StandardService.startInternal(StandardService.java:440)
		at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183)
		at org.apache.catalina.core.StandardServer.startInternal(StandardServer.java:766)
		at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183)
		at org.apache.catalina.startup.Catalina.start(Catalina.java:688)
		at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
		at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
		at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
		at java.base/java.lang.reflect.Method.invoke(Method.java:564)
		at org.apache.catalina.startup.Bootstrap.start(Bootstrap.java:343)
		at org.apache.catalina.startup.Bootstrap.main(Bootstrap.java:474)
22-Jul-2020 18:02:40.698 INFO [main] org.apache.catalina.startup.Catalina.start Server startup in 3823 ms

@voidmain
Copy link
Member

voidmain commented Sep 4, 2020

This is fixed in version 1.19.0. The new maintenance mode and silent mode will allow FusionAuth to connect to database servers that already have a database for FusionAuth created and do not have super user credentials available.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
enhancement New feature or request
Projects
FusionAuth Issues
  
Delivered
Development

No branches or pull requests

7 participants