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

Supplying user & password in variables does not work #123

Open
blazejp83 opened this issue Aug 6, 2023 · 12 comments
Open

Supplying user & password in variables does not work #123

blazejp83 opened this issue Aug 6, 2023 · 12 comments
Labels

Comments

@blazejp83
Copy link

I have a separate container with MariaDB. I have linked webtrees containter with it. I have specified DB_HOST parameter and also MARIADB_USER, MARIADB_PASSWORD and MARIADB_DATABASE. I am getting Connection refused. When I've checked MariaDB logs, I'm getting attempts to connect as a root user without any password: Access denied for user 'root'@'xyz' (using password: NO). I have tried using MYSQL_ version of variables, but I am getting the same error. I have specified DB_TYPE as 'mysql'.

@NathanVaughn
Copy link
Owner

NathanVaughn commented Aug 10, 2023

Interesting.... I use webtrees myself with a completely separate MariaDB database as well with no issues. Are you sure there are no networking issues at play? The container comes with the mysql command line client so you could try attaching to the console and troubleshooting with that.

image

I would also recommend removing the environment variables entirely and try using the builtin setup wizard.

Lastly, I'll add that one root access denied message is not unexpected. Output log from MariaDB for me running the docker-compose.yml file in the repo:

2023-08-09 19:17:26 2023-08-10 00:17:26+00:00 [Note] [Entrypoint]: Entrypoint script for MariaDB Server 1:10.11.3+maria~ubu2204 started.
2023-08-09 19:17:27 2023-08-10 00:17:27+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
2023-08-09 19:17:27 2023-08-10 00:17:27+00:00 [Note] [Entrypoint]: Entrypoint script for MariaDB Server 1:10.11.3+maria~ubu2204 started.
2023-08-09 19:17:27 2023-08-10 00:17:27+00:00 [Note] [Entrypoint]: Initializing database files
2023-08-09 19:17:30
2023-08-09 19:17:30
2023-08-09 19:17:30 PLEASE REMEMBER TO SET A PASSWORD FOR THE MariaDB root USER !
2023-08-09 19:17:30 To do so, start the server, then issue the following command:
2023-08-09 19:17:30
2023-08-09 19:17:30 '/usr/bin/mariadb-secure-installation'
2023-08-09 19:17:30
2023-08-09 19:17:30 which will also give you the option of removing the test
2023-08-09 19:17:30 databases and anonymous user created by default.  This is
2023-08-09 19:17:30 strongly recommended for production servers.
2023-08-09 19:17:30
2023-08-09 19:17:30 See the MariaDB Knowledgebase at https://mariadb.com/kb
2023-08-09 19:17:30
2023-08-09 19:17:30 Please report any problems at https://mariadb.org/jira
2023-08-09 19:17:30
2023-08-09 19:17:30 The latest information about MariaDB is available at https://mariadb.org/.
2023-08-09 19:17:30
2023-08-09 19:17:30 Consider joining MariaDB's strong and vibrant community:
2023-08-09 19:17:30 https://mariadb.org/get-involved/
2023-08-09 19:17:30
2023-08-09 19:17:30 2023-08-10 00:17:30+00:00 [Note] [Entrypoint]: Database files initialized
2023-08-09 19:17:30 2023-08-10 00:17:30+00:00 [Note] [Entrypoint]: Starting temporary server
2023-08-09 19:17:30 2023-08-10 00:17:30+00:00 [Note] [Entrypoint]: Waiting for server startup
2023-08-09 19:17:30 2023-08-10  0:17:30 0 [Note] Starting MariaDB 10.11.3-MariaDB-1:10.11.3+maria~ubu2204 source revision 0bb31039f54bd6a0dc8f0fc7d40e6b58a51998b0 as process 119
2023-08-09 19:17:30 2023-08-10  0:17:30 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2023-08-09 19:17:30 2023-08-10  0:17:30 0 [Note] InnoDB: Number of transaction pools: 1
2023-08-09 19:17:30 2023-08-10  0:17:30 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
2023-08-09 19:17:30 2023-08-10  0:17:30 0 [Note] mariadbd: O_TMPFILE is not supported on /tmp (disabling future attempts)
2023-08-09 19:17:30 2023-08-10  0:17:30 0 [Note] InnoDB: Using liburing
2023-08-09 19:17:30 2023-08-10  0:17:30 0 [Note] InnoDB: Initializing buffer pool, total size = 128.000MiB, chunk size = 2.000MiB
2023-08-09 19:17:30 2023-08-10  0:17:30 0 [Note] InnoDB: Completed initialization of buffer pool
2023-08-09 19:17:30 2023-08-10  0:17:30 0 [Note] InnoDB: File system buffers for log disabled (block size=4096 bytes)
2023-08-09 19:17:30 2023-08-10  0:17:30 0 [Note] InnoDB: 128 rollback segments are active.
2023-08-09 19:17:30 2023-08-10  0:17:30 0 [Note] InnoDB: Setting file './ibtmp1' size to 12.000MiB. Physically writing the file full; Please wait ...
2023-08-09 19:17:30 2023-08-10  0:17:30 0 [Note] InnoDB: File './ibtmp1' size is now 12.000MiB.
2023-08-09 19:17:30 2023-08-10  0:17:30 0 [Note] InnoDB: log sequence number 46606; transaction id 14
2023-08-09 19:17:30 2023-08-10  0:17:30 0 [Note] Plugin 'FEEDBACK' is disabled.
2023-08-09 19:17:30 2023-08-10  0:17:30 0 [Warning] 'user' entry 'root@cb151d5ddcdc' ignored in --skip-name-resolve mode.
2023-08-09 19:17:30 2023-08-10  0:17:30 0 [Warning] 'proxies_priv' entry '@% root@cb151d5ddcdc' ignored in --skip-name-resolve mode.
2023-08-09 19:17:30 2023-08-10  0:17:30 0 [Note] mariadbd: ready for connections.
2023-08-09 19:17:30 Version: '10.11.3-MariaDB-1:10.11.3+maria~ubu2204'  socket: '/run/mysqld/mysqld.sock'  port: 0  mariadb.org binary distribution
2023-08-09 19:17:32 2023-08-10  0:17:32 0 [Note] mariadbd (initiated by: unknown): Normal shutdown
2023-08-09 19:17:32 2023-08-10  0:17:32 0 [Note] InnoDB: FTS optimize thread exiting.
2023-08-09 19:17:32 2023-08-10  0:17:32 0 [Note] InnoDB: Starting shutdown...
2023-08-09 19:17:32 2023-08-10  0:17:32 0 [Note] InnoDB: Dumping buffer pool(s) to /var/lib/mysql/ib_buffer_pool
2023-08-09 19:17:32 2023-08-10  0:17:32 0 [Note] InnoDB: Buffer pool(s) dump completed at 230810  0:17:32
2023-08-09 19:17:33 2023-08-10  0:17:33 0 [Note] InnoDB: Removed temporary tablespace data file: "./ibtmp1"
2023-08-09 19:17:33 2023-08-10  0:17:33 0 [Note] InnoDB: Shutdown completed; log sequence number 46606; transaction id 15
2023-08-09 19:17:33 2023-08-10  0:17:33 0 [Note] mariadbd: Shutdown complete
2023-08-09 19:17:33
2023-08-09 19:17:33 2023-08-10  0:17:33 0 [Note] Starting MariaDB 10.11.3-MariaDB-1:10.11.3+maria~ubu2204 source revision 0bb31039f54bd6a0dc8f0fc7d40e6b58a51998b0 as process 1
2023-08-09 19:17:33 2023-08-10  0:17:33 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2023-08-09 19:17:33 2023-08-10  0:17:33 0 [Note] InnoDB: Number of transaction pools: 1
2023-08-09 19:17:33 2023-08-10  0:17:33 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
2023-08-09 19:17:33 2023-08-10  0:17:33 0 [Note] mariadbd: O_TMPFILE is not supported on /tmp (disabling future attempts)
2023-08-09 19:17:33 2023-08-10  0:17:33 0 [Note] InnoDB: Using liburing
2023-08-09 19:17:33 2023-08-10  0:17:33 0 [Note] InnoDB: Initializing buffer pool, total size = 128.000MiB, chunk size = 2.000MiB
2023-08-09 19:17:33 2023-08-10  0:17:33 0 [Note] InnoDB: Completed initialization of buffer pool
2023-08-09 19:17:33 2023-08-10  0:17:33 0 [Note] InnoDB: File system buffers for log disabled (block size=4096 bytes)
2023-08-09 19:17:33 2023-08-10  0:17:33 0 [Note] InnoDB: 128 rollback segments are active.
2023-08-09 19:17:33 2023-08-10  0:17:33 0 [Note] InnoDB: Setting file './ibtmp1' size to 12.000MiB. Physically writing the file full; Please wait ...
2023-08-09 19:17:33 2023-08-10  0:17:33 0 [Note] InnoDB: File './ibtmp1' size is now 12.000MiB.
2023-08-09 19:17:33 2023-08-10  0:17:33 0 [Note] InnoDB: log sequence number 46606; transaction id 14
2023-08-09 19:17:33 2023-08-10  0:17:33 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2023-08-09 19:17:33 2023-08-10  0:17:33 0 [Note] Plugin 'FEEDBACK' is disabled.
2023-08-09 19:17:33 2023-08-10  0:17:33 0 [Warning] You need to use --log-bin to make --expire-logs-days or --binlog-expire-logs-seconds work.
2023-08-09 19:17:33 2023-08-10  0:17:33 0 [Note] InnoDB: Buffer pool(s) load completed at 230810  0:17:33
2023-08-09 19:17:33 2023-08-10  0:17:33 0 [Note] Server socket created on IP: '0.0.0.0'.
2023-08-09 19:17:33 2023-08-10  0:17:33 0 [Note] Server socket created on IP: '::'.
2023-08-09 19:17:33 2023-08-10  0:17:33 0 [Note] mariadbd: ready for connections.
2023-08-09 19:17:33 Version: '10.11.3-MariaDB-1:10.11.3+maria~ubu2204'  socket: '/run/mysqld/mysqld.sock'  port: 3306  mariadb.org binary distribution
2023-08-09 19:17:33 2023-08-10  0:17:33 3 [Warning] Access denied for user 'root'@'172.20.0.3' (using password: NO)
2023-08-09 19:17:31 2023-08-10 00:17:31+00:00 [Note] [Entrypoint]: Temporary server started.
2023-08-09 19:17:32 2023-08-10 00:17:32+00:00 [Note] [Entrypoint]: Creating database webtrees
2023-08-09 19:17:32 2023-08-10 00:17:32+00:00 [Note] [Entrypoint]: Creating user webtrees
2023-08-09 19:17:32 2023-08-10 00:17:32+00:00 [Note] [Entrypoint]: Giving user webtrees access to schema webtrees
2023-08-09 19:17:32 2023-08-10 00:17:32+00:00 [Note] [Entrypoint]: Securing system users (equivalent to running mysql_secure_installation)
2023-08-09 19:17:32
2023-08-09 19:17:32 2023-08-10 00:17:32+00:00 [Note] [Entrypoint]: Stopping temporary server
2023-08-09 19:17:33 2023-08-10 00:17:33+00:00 [Note] [Entrypoint]: Temporary server stopped
2023-08-09 19:17:33
2023-08-09 19:17:33 2023-08-10 00:17:33+00:00 [Note] [Entrypoint]: MariaDB init process done. Ready for start up.
2023-08-09 19:17:33

@albertopuerto
Copy link

albertopuerto commented Oct 5, 2023

Hi there @NathanVaughn , i have pretty similar issue

Trying to set up the wizard, the error response is
SQLSTATE[HY000] [1045] Access denied for user 'username'@'172.28.0.1' (using password: YES) (SQL: CREATE DATABASE IF NOT EXISTS MARIADB_DATABASEvar COLLATE utf8_unicode_ci)

I´ve tried to run your command in the webtree app terminal, and with the service.consul comand i´m not able to connect (perhaps the name is wrong by my side)
however, if i run the command with -h webtree_db_ip, the connection is working

mysql -h mariadb.service.consul -u username -p
Unknonw server host

mysql -h MARIADB_DATABASEvar.service.consul -u username -p
Unknown server host again

mysql -h IPADDRESS -u username -p
MariaDB connection is OK

so it seems that the issue is not related in this case with the credentials provided in the docker-compose, isn´t?
Perhaps @blazejp83 has the same issue...
what could be the error?
is it necessary for me to open a separate thread or can we continue to iterate on the problem in this one?

thanks and greetings

@albertopuerto
Copy link

something that i found here #115

A second issue i noticed is:
if u use local mapped folders instead of docker volumes for persistant data, there are permission and ownership problems creating or starting the db container the first time, so it will not create the db files properly

this can be the issue, that issue is still opened, any solution? Thanks!

@NathanVaughn
Copy link
Owner

First, the mariadb.service.consul is the hostname of my database server. Substitute with whatever IP address or hostname your database server is located on.

I believe I fixed the issue in 9f1361d with the alternate environment variables not being picked up. Try the DB_ variables as well.

Additionally, I use mapped folders on my installation for persistent storage with no issues. It could be permissions problems. A temporary thing to try to see if that is it, is giving all permissions of chmod -R 777. You should definitely change the permissions afterwards

@albertopuerto
Copy link

Hello @NathanVaughn

Yep, it seems there is an issue recovering the MARIADB var, at least the passwords

image

anyway i´ve tried to change to DB vars
image

but i still can not access

SQLSTATE[HY000] [1045] Access denied for user 'username'@'172.28.0.1' (using password: YES) (SQL: CREATE DATABASE IF NOT EXISTS tablename COLLATE utf8_unicode_ci)

@NathanVaughn
Copy link
Owner

What container image and tag are pulling? ghcr.io/nathanvaughn/webtrees:latest? I want to try and replicate.

@albertopuerto
Copy link

albertopuerto commented Oct 8, 2023

Hi there @NathanVaughn

yep, the latest one, this is the docker-compose.yml

version: "3"

services:
  webtrees_app:
    depends_on:
      - webtrees_db
    environment:
      PRETTY_URLS: "1"
      HTTPS: "0"
      HTTPS_REDIRECT: "0"
      LANG: "es-ES"
      BASE_URL: "http://localipserveraddress"
      DB_TYPE: "mysql"
      DB_HOST: "localhost"
      DB_PORT: "3306"
      DB_USER: "DBUSERhere"
      DB_PASS: "DBPASShere"
      DB_NAME: "DBNAMEhere"
      # MARIADB_USER: "DBUSERhere"
      # MARIADB_PASS: "DBPASShere"
      # MARIADB_DATABASE: "DBNAMEhere"
      DB_PREFIX: "wt_"
      WT_USER: "WTUSERhere"
      WT_NAME: "WTnamehere"
      WT_PASS: "WTpasshere"
      WT_EMAIL: "[email protected]"
    image: ghcr.io/nathanvaughn/webtrees:latest
    ports:
      - 8084:80
      # - 80443:443
    restart: unless-stopped
    volumes:
      # - ~/certs:/certs/
      - ./app_data:/var/www/webtrees/data/
      - ./app_media:/var/www/webtrees/media/
      - ./app_themes:/var/www/webtrees/modules_v4/

  webtrees_db:
    environment:
      DB_NAME: "DBNAMEhere"
      DB_USER: "DBUSERhere"
      DB_PASS: "DBPASShere"
      # MARIADB_DATABASE: "DBNAMEhere"
      # MARIADB_USER: "DBUSERhere"
      # MARIADB_PASSWORD: "DBPASShere"
    image: docker.io/library/mariadb:latest
    restart: unless-stopped
    volumes:
      - ./db_data:/var/lib/mysql


volumes:
  db_data:
    driver: local
  app_data:
    driver: local
  app_media:
    driver: local
  app_themes:
    driver: local

Thanks in advance!

@NathanVaughn
Copy link
Owner

  • The environment variable it is looking for is MARIADB_PASSWORD, not MARIADB_PASS.
  • You're also missing MARIADB_ROOT_PASSWORD.
  • Port 3306 is not being exposed by the database container so it can't connect on localhost.

I got everything to launch with this compose file:

version: "3"

services:
  webtrees_app:
    depends_on:
      - webtrees_db
    environment:
      PRETTY_URLS: "1"
      HTTPS: "0"
      HTTPS_REDIRECT: "0"
      LANG: "es-ES"
      BASE_URL: "http://localhost:8084"
      DB_TYPE: "mysql"
      DB_HOST: "webtrees_db"
      DB_PORT: "3306"
      MARIADB_USER: "DBUSERhere"
      MARIADB_PASSWORD: "DBPASShere"
      MARIADB_DATABASE: "DBNAMEhere"
      DB_PREFIX: "wt_"
      WT_USER: "WTUSERhere"
      WT_NAME: "WTnamehere"
      WT_PASS: "WTpasshere"
      WT_EMAIL: "[email protected]"
    image: ghcr.io/nathanvaughn/webtrees:latest
    ports:
      - 8084:80
      # - 80443:443
    restart: unless-stopped
    volumes:
      # - ~/certs:/certs/
      - ./app_data:/var/www/webtrees/data/
      - ./app_media:/var/www/webtrees/media/
      - ./app_themes:/var/www/webtrees/modules_v4/

  webtrees_db:
    environment:
      MARIADB_DATABASE: "DBNAMEhere"
      MARIADB_USER: "DBUSERhere"
      MARIADB_PASSWORD: "DBPASShere"
      MARIADB_ROOT_PASSWORD: "DBROOTPASShere"
    image: docker.io/library/mariadb:latest
    restart: unless-stopped
    volumes:
      - ./db_data:/var/lib/mysql


volumes:
  db_data:
    driver: local
  app_data:
    driver: local
  app_media:
    driver: local
  app_themes:
    driver: local

I still had some issues with the MariaDB container, but I think that is because of WSL. See https://stackoverflow.com/a/70784438/9944427

@albertopuerto
Copy link

Hello!

Ok, i had to change the localhost var to the local ip of my server to be able to connect, but everything else in the yml works correctly, thanks!

Regarding the var MARIADB_ROOT_PASSWORD I deleted it because it was not found in the logs:
image

Anyway, after entering the username and password for the first time, I see this error:

image

Cannot assign null to property Fisharebest\Webtrees\I18N::$language of type Fisharebest\Webtrees\Module\ModuleLanguageInterface …/app/I18N.php:306
#0 …/app/Http/RequestHandlers/LoginAction.php(141): Fisharebest\Webtrees\I18N::init('es-ES')
(blablabla..)

it seems that the error was caused by the selected language, but after setting it right in the user options, then I was able to create the first family tree table and everything fine.

Thank you!

@ArtemSmaznov
Copy link

Gotta admit I haven't read the whole thread but I noticed that DB_PASS doesn't work with the password and DB_PASS_FILE doesn't work with the password file. Getting same Access denied for user 'root'@'xyz' (using password: NO)

But for some reason DB_PASS_FILE works with password and DB_PASS_FILE_FILE works with password file.

So I have it working locally with the following env (just remove one of the _FILE and use the password directly if you don't setup secrets:

      DB_TYPE: mysql
      DB_HOST: webtrees-db
      DB_PORT: 3306
      DB_NAME: ${WEBTREES_DB_NAME}
      DB_USER: ${WEBTREES_DB_USER}
      DB_PASS_FILE_FILE: /run/secrets/webtrees_db_password
      DB_PREFIX: wt_

That being said I am still figuring out how to make it work behind my nginx reverse proxy

@DuvelCorp
Copy link

image

Cannot assign null to property Fisharebest\Webtrees\I18N::$language of type Fisharebest\Webtrees\Module\ModuleLanguageInterface …/app/I18N.php:306
#0 …/app/Http/RequestHandlers/LoginAction.php(141): Fisharebest\Webtrees\I18N::init('es-ES')
(blablabla..)

it seems that the error was caused by the selected language, but after setting it right in the user options, then I was able to create the first family tree table and everything fine.

Thank you!

Note that I just installed, with this:

    environment:
      PRETTY_URLS: "1"
      HTTPS: "0"
      HTTPS_REDIRECT: "0"
      LANG: "fr-FR"

And I had the exact same error. While the app language was properly set to french.
Re-selecting french (that was selected) in the top right menu fixed the issue.

Copy link
Contributor

This issue is stale because it has been open 60 days with no activity. Remove stale label or comment or this will be closed in 7 days.

@github-actions github-actions bot added the Stale label Dec 17, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
Projects
None yet
Development

No branches or pull requests

5 participants