how to set password as environment variable to connect to mysql

Questions / Help
1

I want to deploy a spring boot backend with a mysql database.
I deployed the mysql database as described in this guide: Use a MySQL Database · Fly Docs.
However I’m puzzled about this sentence: “Any app that needs to access the database should set the hostname and username as environment variables, and create a secret for the database password.”

As described in the guide I set this secrets: “fly secrets set MYSQL_PASSWORD=password MYSQL_ROOT_PASSWORD=password”

In my application properties I defined these variables:
spring.datasource.url=jdbc:mysql://recipe-mysql.internal:3306/recipedb
spring.datasource.username=root
spring.datasource.password=${MYSQL_ROOT_PASSWORD}

In the logging I see this error:
java.sql.SQLException: Access denied for user ‘root’@‘fdaa:3:3513:a7b:16a:88da:6b43:2’ (using password: YES)

What am I doing wrong?

2

Hi… The second half of that sentence is referring exactly to the above fly secrets set invocation, and really it’s just pointing out that you’ll need to do that step at least twice (since the settings aren’t shared): once for the MySQL machine itself and then again (!) for any application that wants to connect to it. (By running that command in the directory that contains the Java application’s fly.toml file, typically, or with the -a knob.) That establishes environment variables MYSQL_PASSWORD (for the non-root user) and MYSQL_ROOT_PASSWORD.

You can check with fly secrets list -a java-app-name.

The other half of the quote is a convention, which your Java properties are an adequate substitute for—if that’s how you prefer to handle such things, :coffee:.

The database is most likely configured so that you can log in as root only when you’re on localhost. (That’s how the MySQL Docker entrypoints that I’ve seen initialize it by default, anyway.)

Try MYSQL_USER—assuming you had MYSQL_DATABASE, MYSQL_USER, and the secrets all set correctly on first deploy of the MySQL machine.

(The first two are in the sample fly.toml.)

A bit of extra context:

https://community.fly.io/t/mysql-not-connecting-even-after-setting-up-wire-guard-and-peering/16532

Hope this helps!

3

I use this procedure now to deploy my spring backend:

  1. ‘fly launch --no-deploy’
  2. “fly secrets set MYSQL_PASSWORD=password MYSQL_ROOT_PASSWORD=password”
  3. ‘flyctl deploy’

The environment variables in my application.properties I’ve set to :
spring.datasource.url=jdbc:mysql://recipe-mysql.internal:3306/recipedb
spring.datasource.username=MYSQL_USER

Since the guide says to set hostname and username as environment variables, I figured that there’s no need to set 'spring.datasource.password. Is that correct?

I’ve tried with username ‘MYSQL_USER’, ‘$MYSQL_USER’, ‘root’ and also ‘non-root-user’ (since in the fly.toml of the database MYSQL_USER seems to be given value ‘non-root-user’ (as shown beneath).

[env]
MYSQL_DATABASE = “some_db”
MYSQL_USER = “non_root_user”

But I stil get this exception: java.sql.SQLException: Access denied for user ‘’@‘fdaa:3:3513:a7b:169:77f4:c763:2’ (using password: NO)

Any suggestions on what I can do better/different?

4

Thanks for the additional details…

Generally, their instructions were tacitly expecting you to use your own values in place of "some_db", "non_root_user", password, and the like—instead of copying them verbatim. E.g.,

[env]
  MYSQL_DATABASE = "recipedb"
  MYSQL_USER = "recipe"

So, the most orthodox recommendation would be to re-create the MySQL server, with all of those changed to recipe-app related strings (+ unique, strong passwords).[1]

Although I don’t use Spring myself, it seems fairly inevitable that you would need either that or a more complicated intervention elsewhere; search results suggest that something along the following lines might be the path of least resistance…

spring.datasource.url=jdbc:mysql://recipe-mysql.internal/recipedb
spring.datasource.username=recipe
spring.datasource.password=${MYSQL_PASSWORD}

(Be wary of hyphens versus underscores in identifiers, incidentally, :dragon:.)

  1. One caveat is that you probably must destroy both the existing MySQL machine and—separately and subsequently—its volume, to be confident that the magic database auto-configuration inside there will go through again.
    This part of the process tends to not be as smart about change detection as other parts are. ↩︎

5

Thanks again for your suggestions and thinking along. Much appreciated.
Of course I did replace the default user and database name, but for reference it is easier to use the default ones :wink:
I will retry again soon with your suggestions and will let you know the result

6

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.