Supplying user & password in variables does not work

blazejp83 commented 1 year ago

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 MARIADBDATABASE. 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 commented 1 year ago

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.

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 commented 1 year ago

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 commented 1 year ago

something that i found here https://github.com/NathanVaughn/webtrees-docker/issues/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 commented 1 year ago

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 9f1361dd18dbe85e3574f2fe5a723aa01df1ec8b 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 commented 1 year ago

Hello @NathanVaughn

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

anyway i´ve tried to change to DB vars

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 commented 1 year ago

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

albertopuerto commented 1 year ago

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: "emailhere@email.com"
    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 commented 1 year ago

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: "emailhere@email.com"
    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 commented 1 year ago

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:

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

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 commented 1 year ago

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 commented 1 month ago

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.

NathanVaughn / webtrees-docker

Supplying user & password in variables does not work #123