[BUG] MariaDB does not seem to respect `MYSQL_ROOT_PASSWORD` on initial launch

[Paraphraser]

Is there an existing issue for this?

• I have searched the existing issues

Current Behavior

MariaDB does not seem to respect MYSQL_ROOT_PASSWORD on initial launch

When MariaDB is launched from a clean slate (no persistent store), the value passed into the container via MYSQL_ROOT_PASSWORD does not seem to be applied. It appears no root password is in effect.

Associated environment variables (eg MYSQL_DATABASE, MYSQL_USER and MYSQL_PASSWORD are applied so this problem seems confined to the root password.

Service definition:

  mariadb_test:
    container_name: mariadb_test
    image: ghcr.io/linuxserver/mariadb
    restart: unless-stopped
    environment:
      - TZ=${TZ:-Etc/UTC}
      - PUID=1000
      - PGID=1000
      - MYSQL_ROOT_PASSWORD=${MARIADB_ROOT_PASSWORD}
      - MYSQL_DATABASE=${MARIADB_DB_NAME}
      - MYSQL_USER=${MARIADB_DB_USER}
      - MYSQL_PASSWORD=${MARIADB_DB_PASSWORD}
    volumes:
      - ./volumes/mariadb_test/db:/config

Environment variables:

$ grep "^MARIADB" .env
MARIADB_ROOT_PASSWORD=rootPassword
MARIADB_DB_NAME=db2025
MARIADB_DB_USER=user2025
MARIADB_DB_PASSWORD=userPassword

Clean slate (no persistent store):

$ stat ./volumes/mariadb_test
stat: cannot statx './volumes/mariadb_test': No such file or directory

Start container:

[+] Running 1/1
 ✔ Container mariadb_test  Started                                                                                                                         0.5s 

Show persistent store initialised:

$ tree -L 2 ./volumes/mariadb_test
./volumes/mariadb_test
└── db
    ├── custom.cnf
    ├── databases
    └── log

Open shell into container:

$ docker exec -it mariadb_test bash
root@a4d6ae8d7064:/# 

Show expected variables have made it into the container:

root@a4d6ae8d7064:/# echo -e "$MYSQL_ROOT_PASSWORD\n$MYSQL_DATABASE\n$MYSQL_USER\n$MYSQL_PASSWORD"
rootPassword
db2025
user2025
userPassword

Enter CLI without a password:

root@a4d6ae8d7064:/# mariadb
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 7
Server version: 11.4.5-MariaDB-log Alpine Linux

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> SHOW databases;
+--------------------+
| Database           |
+--------------------+
| db2025             |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.002 sec)

MariaDB [(none)]> SELECT User FROM mysql.user;
+-------------+
| User        |
+-------------+
| root        |
| user2025    |
| root        |
| root        |
| root        |
| mariadb.sys |
| root        |
+-------------+
7 rows in set (0.003 sec)

MariaDB [(none)]> exit
Bye
root@a4d6ae8d7064:/# 

Note that database db2025 and user user2025 have been created via environment variables.

Change the root password to the expected value:

root@a4d6ae8d7064:/# mariadb-admin -u root password $MYSQL_ROOT_PASSWORD
root@a4d6ae8d7064:/# 

The ability to set a new root password without providing the old root password would appear to confirm that no root password is in effect when the container is initialised.

Attempt to enter the CLI without a password:

root@a4d6ae8d7064:/# mariadb
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

Enter the CLI using the root password:

root@a4d6ae8d7064:/# mariadb -p$MYSQL_ROOT_PASSWORD
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 12
Server version: 11.4.5-MariaDB-log Alpine Linux

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> exit
Bye

Attempt to enter the CLI using the unprivileged username:

root@a4d6ae8d7064:/# mariadb -u $MYSQL_USER
ERROR 1045 (28000): Access denied for user 'user2025'@'localhost' (using password: NO)

Repeat with the relevant password:

root@a4d6ae8d7064:/# mariadb -u $MYSQL_USER -p$MYSQL_PASSWORD
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 16
Server version: 11.4.5-MariaDB-log Alpine Linux

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> exit
Bye
root@a4d6ae8d7064:/# exit
exit
$ 

Expected Behavior

That the database structures should be initialised with the root password specified in MYSQL_ROOT_PASSWORD.

Steps To Reproduce

Steps shown in "current behaviour".

Environment

- OS: Raspberry Pi 4 OS 11.11 (bullseye) full 64-bit OS
- Docker "convenience script" ([https://get.docker.com](https://get.docker.com))

CPU architecture

arm64

Docker creation

Service definition included "current behaviour"

Container logs

$ docker logs mariadb_test 
[migrations] started
[migrations] no migrations found
───────────────────────────────────────

      ██╗     ███████╗██╗ ██████╗
      ██║     ██╔════╝██║██╔═══██╗
      ██║     ███████╗██║██║   ██║
      ██║     ╚════██║██║██║   ██║
      ███████╗███████║██║╚██████╔╝
      ╚══════╝╚══════╝╚═╝ ╚═════╝

   Brought to you by linuxserver.io
───────────────────────────────────────

To support LSIO projects visit:
https://www.linuxserver.io/donate/

───────────────────────────────────────
GID/UID
───────────────────────────────────────

User UID:    1000
User GID:    1000
───────────────────────────────────────
Linuxserver.io version: 11.4.5-r0-ls182
Build-date: 2025-04-22T07:46:02+00:00
───────────────────────────────────────
    
Setting Up Initial Databases
Installing MariaDB/MySQL system tables in '/config/databases' ...
OK

To start mariadbd at boot time you have to copy
support-files/mariadb.service to the right place for your system


PLEASE REMEMBER TO SET A PASSWORD FOR THE MariaDB root USER !
To do so, start the server, then issue the following command:

'/usr/bin/mariadb-secure-installation'

which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.

See the MariaDB Knowledgebase at https://mariadb.com/kb

You can start the MariaDB daemon with:
cd '/usr' ; /usr/bin/mariadbd-safe --datadir='/config/databases'

You can test the MariaDB daemon with mariadb-test-run.pl
cd '/usr/mariadb-test' ; perl mariadb-test-run.pl

Please report any problems at https://mariadb.org/jira

The latest information about MariaDB is available at https://mariadb.org/.

Consider joining MariaDB's strong and vibrant community:
https://mariadb.org/get-involved/

2025-04-23 15:50:39 0 [Note] Starting MariaDB 11.4.5-MariaDB source revision 0771110266ff5c04216af4bf1243c65f8c67ccf4 server_uid QhM0gbXKjfGz2mXxr2nbWnf4axo= as process 176
2025-04-23 15:50:39 0 [Note] InnoDB: Compressed tables use zlib 1.3.1
2025-04-23 15:50:39 0 [Note] InnoDB: Number of transaction pools: 1
2025-04-23 15:50:39 0 [Note] InnoDB: Using ARMv8 crc32 instructions
2025-04-23 15:50:39 0 [Note] mariadbd: O_TMPFILE is not supported on /var/tmp (disabling future attempts)
2025-04-23 15:50:39 0 [Note] InnoDB: Using Linux native AIO
2025-04-23 15:50:39 0 [Note] InnoDB: Initializing buffer pool, total size = 128.000MiB, chunk size = 2.000MiB
2025-04-23 15:50:39 0 [Note] InnoDB: Completed initialization of buffer pool
2025-04-23 15:50:39 0 [Note] InnoDB: File system buffers for log disabled (block size=512 bytes)
2025-04-23 15:50:39 0 [Note] InnoDB: End of log at LSN=47763
2025-04-23 15:50:39 0 [Note] InnoDB: Opened 3 undo tablespaces
2025-04-23 15:50:39 0 [Note] InnoDB: 128 rollback segments in 3 undo tablespaces are active.
2025-04-23 15:50:39 0 [Note] InnoDB: Setting file './ibtmp1' size to 12.000MiB. Physically writing the file full; Please wait ...
2025-04-23 15:50:39 0 [Note] InnoDB: File './ibtmp1' size is now 12.000MiB.
2025-04-23 15:50:39 0 [Note] InnoDB: log sequence number 47763; transaction id 14
2025-04-23 15:50:39 0 [Note] InnoDB: Loading buffer pool(s) from /config/databases/ib_buffer_pool
2025-04-23 15:50:39 0 [Note] Plugin 'FEEDBACK' is disabled.
2025-04-23 15:50:39 0 [Note] Plugin 'wsrep-provider' is disabled.
2025-04-23 15:50:39 0 [Note] InnoDB: Buffer pool(s) load completed at 250423 15:50:39
2025-04-23 15:50:44 0 [Note] mariadbd: Event Scheduler: Loaded 0 events
2025-04-23 15:50:44 0 [Note] mariadbd: ready for connections.
Version: '11.4.5-MariaDB'  socket: '/run/mysqld/mysqld.sock'  port: 0  Alpine Linux
2025-04-23 15:50:45 0 [Note] mariadbd (initiated by: root[root] @ localhost []): Normal shutdown
2025-04-23 15:50:46 0 [Note] InnoDB: FTS optimize thread exiting.
2025-04-23 15:50:46 0 [Note] InnoDB: Starting shutdown...
2025-04-23 15:50:46 0 [Note] InnoDB: Dumping buffer pool(s) to /config/databases/ib_buffer_pool
2025-04-23 15:50:46 0 [Note] InnoDB: Buffer pool(s) dump completed at 250423 15:50:46
2025-04-23 15:50:46 0 [Note] InnoDB: Removed temporary tablespace data file: "./ibtmp1"
2025-04-23 15:50:46 0 [Note] InnoDB: Shutdown completed; log sequence number 47763; transaction id 16
2025-04-23 15:50:46 0 [Note] mariadbd: Shutdown complete
Database Setup Completed
[custom-init] No custom files found, skipping...
250423 15:50:47 mysqld_safe Logging to '/config/log/mysql/mariadb-error.log'.
250423 15:50:47 mysqld_safe Starting mariadbd daemon with databases from /config/databases
Connection to localhost (::1) 3306 port [tcp/mysql] succeeded!
Logrotate is enabled
[ls.io-init] done.

[github-actions]

Thanks for opening your first issue here! Be sure to follow the relevant issue templates, or risk having this issue marked as invalid.

[LinuxServer-CI]

This issue has been automatically marked as stale because it has not had recent activity. This might be due to missing feedback from OP. It will be closed if no further activity occurs. Thank you for your contributions.

[Paraphraser]

This bug is still present in

mariadb from 11.4.5-MariaDB, client 15.2 for Linux (x86_64) using readline 5.1

[profplump]

I tracked this down. The root password is set for user='root' host='%'

But the DB already has user='root' host='localhost' (among other local host names) with no password, so when you connect from inside the container the more-specific no-password user matches first

The fix is to run connect as root and run:
DELETE FROM mysql.user WHERE user='root' AND host <> '%' ; FLUSH PRIVILEGES ;
Which will delete the root users for everything except the wildcard host (the one with the password set). For scripted solutions you could use the remote_sql option or a script in /config/initdb.d to run this command at first init

I suspect this should happen in /etc/s6-overlay/s6-rc.d/init-mariadb-initdb/run as part of the other initial user management

It appears older versions of the mariadb container did not automatically add these other root users; DBs I initialized under older versions of the container don't have the extra localhost entries for root

[Paraphraser]

Hi Zach,

Your explanation makes perfect sense but perhaps you can give me some further guidance because I'm not sure what your explanation means in terms of fixing the bug in the Docker container.

Two possibilities occur to me:

1. You understand the LinuxServer maintenance procedures so you know that what you wrote is likely to trigger a process which will lead to a revised Docker image appearing in due course.
2. You are providing this information in the expectation and belief that I understand the LinuxServer maintenance procedures and will be able to take it further.

If it's number 1, great. Thank you!

If it's number 2 then I apologise for my ignorance but I'm at a loss to know what to do next.

[profplump]

You can see the problem if you list root users from the DB. That would look like this:
echo "SELECT user,host from mysql.user WHERE user='root'; \q" | docker exec -it <container> mariadb
which shows something like:

+------+--------------+
| User | Host         |
+------+--------------+
| root | %            |
| root | 127.0.0.1    |
| root | ::1          |
| root | bb78d6938ce9 |
| root | localhost    |
+------+--------------+

For a one-time fix you just need to run the two SQL statements provided above. That would look like this:
echo "DELETE FROM mysql.user WHERE user='root' AND host <> '%' ; FLUSH PRIVILEGES ; \q" | docker exec -it <container> mariadb

After that no-password connections will fail and this should work:
echo "SELECT user,host from mysql.user WHERE user='root'; \q" | docker exec -it <container> mariadb -u root -p

After being prompted for your password you should get a table looks like this, with only the wildcard root entry:

+------+------+
| User | Host |
+------+------+
| root | %    |
+------+------+

--

If you are doing this more than once, before the image is fixed, consider adding those two SQL statements to your REMOTE_SQL or other initialization script, so they run automatically at first init

As for fixing the image, I would add the same SQL to /etc/s6-overlay/s6-rc.d/init-mariadb-initdb/run. On a future day I might offer a PR fix for this. If future me or someone else is actually fixing the image I think it's:

--- orig
+++ new
@@ -1,4 +1,5 @@
     # set basic sql command
     cat >"${tempSqlFile}" <<-EOSQL
 DELETE FROM mysql.user WHERE user <> 'mariadb.sys' AND user <> 'root';
+DELETE FROM mysql.user WHERE user='root' AND host <> '%';
 EOSQL
    # set basic sql command
    cat >"${tempSqlFile}" <<-EOSQL
DELETE FROM mysql.user WHERE user <> 'mariadb.sys' AND user <> 'root';
EOSQL

The FLUSH PRIVILEGES statement isn't needed in the startup script, as the whole DB will restart after this statement runs