Can't get by Maintenance Mode

dtokarz1

After some playing around and restarting this and that, I tried again and received this error, "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 [Access denied for user 'fusionauth'@'localhost' (using password: YES)]. "

I updated the password to what was in the config file and tried again and it went back to the superuser error of "Unable to connect to your database using the superuser username and password, verify your connection information. If it is correct, make sure the database is running before continuing."

dan

It is possible that the database url is incorrect. https://fusionauth.io/docs/v1/tech/reference/configuration has a way you can specify your database port. I find it hard to believe, but best to double check.

Can you share the relevant section of your fusionauth.properties that have the database url in them? And make sure they point to the MySQL server.

Also, are there any logs in the mysql directory that have are relevant?

dtokarz1

database.url=jdbc:mysql://localhost:3306/fa?serverTimezone=UTC&useSSL=false&allowPublicKeyRetrieval=true

This is the URL fusionauth built. I've tried the Plesk port and it built another fa database and then gave the same error. I've looked for MySQL logs but didn't see anything. To be honest I am not sure where to look for them. I've included the properties code but removed the fusionauth password.

#
# Copyright (c) 2018-2020, FusionAuth, All Rights Reserved
#

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

#------------------------------------------------------------------------------
# Additional MySQL database settings.
# - When enabled, FusionAuth will ensure the necessary 'utf8mb4' configuration parameters to
#   properly handle 4 byte unicode characters. FusionAuth will fail to start if not configured properly.
# - This value is ignored if using PostgreSQL
# - Only set to false if you understand the limitations not using 'utf8mb4' character sets.
#------------------------------------------------------------------------------
database.mysql.enforce-utf8mb4=true
database.mysql.connector-version=8.0.18

#------------------------------------------------------------------------------
# fusionauth-search
# - Required on each node running FusionAuth Search
#------------------------------------------------------------------------------
fusionauth-search.transport-port=9020
fusionauth-search.http-port=9021
fusionauth-search.memory=512M
fusionauth-search.additional-java-args=

# Special values include: _local_, _site_. Values can be combined in a comma separated list.
fusionauth-search.hosts=_local_

# Specify the location of your FusionAuth Search Engine servers. Multiple values may be specified using a comma separator.
# This configuration is used by ElasticSearch to configure clustering. The specified port should be the configured value
# for 'fusionauth-search.transport-port'.
#
# Single host example: localhost:9020
# Multiple hosts example: localhost:9020,192.168.1.42:9020
fusionauth-search.servers=localhost:9020

# Specify the full path to the ElasticSearch data directory. This defaults to /usr/local/fusionauth/data/search/esv6
# on Linux/Unix and %FUSIONAUTH_HOME%\data\search\esv6 on Windows if not specified
fusionauth-search.data-directory=

#------------------------------------------------------------------------------
# fusionauth-app
# - Required on each node running FusionAuth App webservice
#------------------------------------------------------------------------------

# Optionally specify an addressable URL or IP address to access this node from other FusionAuth nodes.
# - In most cases this should be left empty and it will be determined at runtime.
# - Ideally this is a site local IP address that is not publicly routable but accessible from other FusionAuth nodes.
fusionauth-app.url=

# Specify the search engine type, accepted values are `database` and `elasticsearch`.
# When configuring the search engine type to `elasticsearch`, you must configure the `fusionauth-app.search-servers` property as well.
fusionauth-app.search-engine-type=database

# Specify the location of your FusionAuth Search servers. Multiple values may be specified using a comma separator.
# This configuration is used by FusionAuth App to connect to the ElasticSearch nodes. The specified port should be the
# configured value for 'fusionauth-search.http-port'.
#
# Single host example: http://localhost:9021
# Multiple hosts example: http://localhost:9021,http://192.168.1.42:9021
#fusionauth-app.search-servers=http://localhost:9021

# Specify the management and HTTP ports for Tomcat
fusionauth-app.management-port=9010
fusionauth-app.http-port=9011
fusionauth-app.https-port=9013
fusionauth-app.ajp-port=9019

# Specify the memory size here. You can use M for megabytes and G for gigabytes
fusionauth-app.memory=512M

# Specify any additional JVM arguments here
fusionauth-app.additional-java-args=

# Cookie Same Site Policy
# - Possible values [Strict, None, Lax]
#
# During development you may want to set this value to None if you're on http and a server is returning you cross domain cookies over https.
fusionauth-app.cookie-same-site-policy=Lax

# Runtime Mode. During development use, 'development' this will allow you to utilize maintenance mode.
# - Once you move into production, use 'production', this will disable maintenance mode.
# - Possible values [production, development]
fusionauth.runtime-mode=development

dan

Hmmm.

So just want to recap:

You're running mysql 5.7, but there are two instances running.
you're on a windows server 2016 server.
The database is created, but FA cannot connect as the user is created.
You can manually connect using those same user credentials.
You can also manually connect to the database using the root credentials.

Which port is the plesk mysql database running on? Which port is the standard mysql process? I think you're on to something when you say:

Is FusionAuth somehow trying to authenticate with superuser to the Plesk instance?

dtokarz1

Whatever happens after the database is created and the schema is set and ordinary user is created is when it stops, goes back into Maintenance Mode and asks for credentials where it can't connect.

I can connect to the MySQL instance (port 3306) with super user. I cannot connect to Plesk instance with super user (port 8306). If I set maintenance mode to use port 8306 and .properties file to 8306 port it will create the database in that directory and then stop the process the same way.

MySQL my.ini

[MySQLD]
port=3306
basedir=C:\\Program Files\\MySQL\\MySQL Server 5.7\\
datadir=C:\\Program Files (x86)\\Plesk\\Databases\\MySQL\\Data
character-set-server=latin1
default-storage-engine=INNODB
read_buffer_size=1M
read_rnd_buffer_size=256K
sort_buffer_size=256K
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=1M
innodb_buffer_pool_size=16M
innodb_log_file_size=10M
innodb_thread_concurrency=8
max_connections=151
old_passwords=0
max_user_connections=30
thread_cache_size=4
thread_stack=196608
binlog_cache_size=32768
net_read_timeout=30
net_retry_count=10
net_write_timeout=30
open_files_limit=4161
table_definition_cache=400
[client]
port=3306

Plesk my.ini

# Copyright 1999-2019. Plesk International GmbH. All rights reserved.
# Note that any changes made to this file will be overwritten when Plesk is updated.
[client]
port=8306
[PleskSQLServer]
port=8306
character-set-server=latin1
default-storage-engine=INNODB
query_cache_size=8M
table_open_cache=32
tmp_table_size=7M
thread_cache_size=32
myisam_max_sort_file_size=100G
myisam_sort_buffer_size=2M
key_buffer_size=2M
read_buffer_size=1M
read_rnd_buffer_size=256K
sort_buffer_size=256K
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=1M
innodb_buffer_pool_size=16M
innodb_log_file_size=10M
innodb_thread_concurrency=8
max_connections=300
max_allowed_packet=1M
net_buffer_length=4K
old_passwords=0
connect_timeout=20
basedir=C:\\Program Files (x86)\\Plesk\\MySQL
datadir=C:\\Program Files (x86)\\Plesk\\MySQL\\Data
tmpdir=C:\\Program Files (x86)\\Plesk\\MySQL\\Data

Thanks for sticking around and giving this an attempt to work. Any reason why you think a log file isn't being created?

dan

Okay, I actually started fusionauth on windows and discovered why there is no logfile. If you start it up via startup.bat, the logfile is captured in the .bat file output.

Mine looks something like this:

Jun 09, 2020 11:45:03.497 AM INFO  io.fusionauth.app.maintenance.FusionAuthMaintenanceModeWorkflow - Determine database status : ORDINARY_USER_CANNOT_CONNECT [Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.]
Jun 09, 2020 11:45:03.501 AM INFO  org.primeframework.mvc.servlet.PrimeServletContextListener - Initializing Prime
Jun 09, 2020 11:45:03.502 AM INFO  io.fusionauth.app.maintenance.guice.FusionAuthMaintenanceModeModule -

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

Jun 09, 2020 11:45:03.554 AM INFO  io.fusionauth.api.configuration.PropertiesFileFusionAuthConfiguration - Loading FusionAuth configuration file [C:\fa\fusionauth\config\fusionauth.properties]

Can you cut and paste the relevant sections of your startup script output?

Thanks,
Dan

dan

I also added a bug for the log file not being in the logs directory: https://github.com/FusionAuth/fusionauth-issues/issues/673

dtokarz1

I did a complete uninstall and clean install for this. Same problems. The database name is fusionauth and I let FusionAuth create it, not me. The first attempt is when it first stops, no errors but like I ran Maintenance Mode for the first time. Admin credentials field and password are blank and ordinary user of fusionauth has a new password showing. Second attempt is trying to login again with admin credentials.

Output too long to put in here so I created a pastbin. Let me know if you can't see it. Sorry for the long log file I didn't know which output is relevant.

FusionAuth Log File

dan

Awesome, that pastebin is super helpful.

Around line 70, I see this error:

Enforcing utf8mb4 character encoding. Review the following messages:
 
 Errors that caused startup to fail:
  Expected [character_set_server] to start with or be equal to [utf8mb4] but found [latin1]
  Expected [collation_server] to start with or be equal to [utf8mb4] but found [latin1_swedish_ci]

This means that we're expecting a certain character set in mysql, but we aren't seeing it. So we're failing to continue.

You can turn off the expectation in your fusionauth.properties. Set database.mysql.enforce-utf8mb4=false and restart your app server. That should resolve this issue.

More on the ramifications of turning this off here: https://github.com/FusionAuth/fusionauth-issues/issues/234#issuecomment-632757441

Alternately, you can set up your database to have the expected charsets. That's beyond the scope of the assistance I can offer, but some googling turns up https://dev.mysql.com/doc/refman/8.0/en/charset-database.html

dtokarz1

Success! While I would have thought letting FusionAuth create the database would have set the character set in the database, the actual problem was setting the character set for the MySQL server and then restarting the server.

I appreciate your time and patience!

dan

Awesome!

chestnutae

This post is deleted!

ian 0

I'm encountering the same error using PostgreSQL and MySQL.

not at all sure what is happening. I didn't see any mismatch with character encoding.

I also joined your slack channel with a new subchannel https://fusionauth.slack.com/archives/C05R6G25472

with a list of steps and files I used to run this. I can't figure out what FusionAuth is expecting or I'd set it.

-Ian

dan

@ian-0 hate to ask you to try this, but have you created the database manually, following these steps?

https://fusionauth.io/docs/v1/tech/installation-guide/fusionauth-app#advanced-installation

In this case, you run the db creation scripts manually, and then when FusionAuth connects, it doesn't have to do anything DDL-wise.

Can you try that and let us know if it works?

ian 0

@ian-0

did get slightly further. New error:

The user for the username you provided was either created or already existed but could not be granted access to the database. The error from the database was [Access denied for user 'fusionauth'@'10.222.3.159' (using password: NO)].

dan

That indicates the fusionauth user doesn't have a password provided. https://fusionauth.io/docs/v1/tech/reference/configuration has the configuration values.

If you are using environment variables, you want to make sure DATABASE_PASSWORD is set.

dan

Also, @ian-0 , which version of which database are you using? We've had issues with postgresql 15 in the past, but I believe they were squashed in this bug.

ian 0

@dan said in Can't get by Maintenance Mode:

That indicates the fusionauth user doesn't have a password provided. https://fusionauth.io/docs/v1/tech/reference/configuration has the configuration values.

If you are using environment variables, you want to make sure DATABASE_PASSWORD is set.

Yup have this variable set... stranger still is I can manually connect to the database without issue using the same username and password that I have set in the ENV variables.

ian 0

@dan said in Can't get by Maintenance Mode:

That indicates the fusionauth user doesn't have a password provided. https://fusionauth.io/docs/v1/tech/reference/configuration has the configuration values.

If you are using environment variables, you want to make sure DATABASE_PASSWORD is set.

Using Engine version
14.6
really bizzare.

ian 0

I rolled back and installed an elastisearch container. Added the SEARCH_TYPE in the fusionauth deployment and mapped a service to the same RDS DB and it stood up without an issue. Something screwy in the way Fusionauth is connecting to either Postgre or MySQL.

I'm going to have to talk to sales as I want to enable the features temporarily to use it as a POC. Also so I can justify the cost per month for actual support beyond forums.

Thank you for your time.