mysql access denied for user after update

[DamonLane]

This is an installation that has been working for months. I haven't changed anything and after a container update, my user can no longer access the database and the Emoncms app therefore is failing.

There's another issue about updating the database after an update to the software/container. I think that may apply here, but since my interface won't load, I don't have that simple button to try.

Oct 23 07:23:06 opi emoncms[692736]: Fatal error: Uncaught mysqli_sql_exception: Access denied for user 'damon'@'localhost' (using password: YES) in /var/www/emoncms/Lib/lo>
Oct 23 07:23:06 opi emoncms[692736]: Stack trace:
Oct 23 07:23:06 opi emoncms[692736]: #0 /var/www/emoncms/Lib/load_emoncms.php(21): mysqli->__construct()
Oct 23 07:23:06 opi emoncms[692736]: #1 /opt/emoncms/modules/sync/sync_upload.php(13): require_once('...')
Oct 23 07:23:06 opi emoncms[692736]: #2 {main}
Oct 23 07:23:06 opi emoncms[692736]:   thrown in /var/www/emoncms/Lib/load_emoncms.php on line 21
Oct 23 07:23:06 opi emoncms[692736]: 2025-10-23 13:23:06 224018 [Warning] Access denied for user 'damon'@'localhost' (using password: YES)
Oct 23 07:23:06 opi emoncms[692736]: PHP Fatal error:  Uncaught mysqli_sql_exception: Access denied for user 'damon'@'localhost' (using password: YES) in /var/www/emoncms/s>
Oct 23 07:23:06 opi emoncms[692736]: Stack trace:
Oct 23 07:23:06 opi emoncms[692736]: #0 /var/www/emoncms/scripts/feedwriter.php(22): mysqli->__construct()
Oct 23 07:23:06 opi emoncms[692736]: #1 {main}
Oct 23 07:23:06 opi emoncms[692736]:   thrown in /var/www/emoncms/scripts/feedwriter.php on line 22
Oct 23 07:23:06 opi emoncms[692736]: 
Oct 23 07:23:06 opi emoncms[692736]: Fatal error: Uncaught mysqli_sql_exception: Access denied for user 'damon'@'localhost' (using password: YES) in /var/www/emoncms/script>
Oct 23 07:23:06 opi emoncms[692736]: Stack trace:
Oct 23 07:23:06 opi emoncms[692736]: #0 /var/www/emoncms/scripts/feedwriter.php(22): mysqli->__construct()
Oct 23 07:23:06 opi emoncms[692736]: #1 {main}
Oct 23 07:23:06 opi emoncms[692736]:   thrown in /var/www/emoncms/scripts/feedwriter.php on line 22`
```

[alexandrecuer]

do you use the homeassistant addon or just a docker "raw" container ?

[alexandrecuer]

you can update the database manually. log into the container, then cd /opt/openenergymonitor and run php emoncmsdbupdate.php

[DamonLane]

Hi, I'm only using the docker container, without any HA interaction.

Thanks for the prompt reply! Here's how I implemented your suggestion. It gives the same error:

damon@opi:~$ podman exec emoncms php /opt/openenergymonitor/emoncmsdbupdate.php

Fatal error: Uncaught mysqli_sql_exception: Access denied for user 'damon'@'localhost' (using password: YES) in /opt/openenergymonitor/emoncmsdbupdate.php:7
Stack trace:
#0 /opt/openenergymonitor/emoncmsdbupdate.php(7): mysqli->__construct()
#1 {main}
  thrown in /opt/openenergymonitor/emoncmsdbupdate.php on line 7
PHP Fatal error:  Uncaught mysqli_sql_exception: Access denied for user 'damon'@'localhost' (using password: YES) in /opt/openenergymonitor/emoncmsdbupdate.php:7
Stack trace:
#0 /opt/openenergymonitor/emoncmsdbupdate.php(7): mysqli->__construct()
#1 {main}
  thrown in /opt/openenergymonitor/emoncmsdbupdate.php on line 7

I'm using rootless podman, but the error doesn't seem related to that and I can't see how this error could come up after it had been working. The only thing I can think of that changed is that podman auto-updated the container.

[alexandrecuer]

@DamonLane : when connected in cli to the emoncms container (i dont use podman but it should be podman run -it emoncms sh), can you connect to the database ? Just type mysql you should have a prompt then do use emoncms; and select * from users;

Could you give the version you upgraded from ? I suppose yu upgraded to alpine3.20_emoncms11.9.11

[alexandrecuer]

since april 2024, the container runs mysql in root mode, cause we add problems with home assistant backups see #15

[DamonLane]

That showed a new database being created:

damon@opi:~$ podman run -it emoncms sh
s6-rc: info: service s6rc-oneshot-runner: starting
s6-rc: info: service s6rc-oneshot-runner successfully started
s6-rc: info: service fix-attrs: starting
s6-rc: info: service fix-attrs successfully started
s6-rc: info: service legacy-cont-init: starting
s6-rc: info: service legacy-cont-init successfully started
s6-rc: info: service emoncms_pre: starting
Creating timeseries folders
initializing a security.conf file in /config
Creating a new mariadb
....

So then there's no users and of course no existing user:

MariaDB [emoncms]> select * from users;
Empty set (0.001 sec)

Setting up proper database backups is in my todo list.

I don't know what version I had before. But since I've been using podman auto-update, I'd say that this update was to the newest version, from the previous newest version at docker.io/alexjunk/emoncms

[alexandrecuer]

Do you have a data folder binding ? Or a volume mounted ? It is necessary to persist the datas. If it (re)creates the mysql database, it means it does not find the existing data folder and think it is a new install...

See here for detailed instructions

https://emoncms-docker.github.io/setup/

Maybe i mistake the instruction, with run it tries to create a new container but normaly you have to give the full path of the image, i mean alexjunk/emoncms:alpine3.20_emoncms11.9.11, and not only emoncms

But creating a new container is not what we want to debug. you rather have to connect to the existing container which i presume is called emoncms.
in plain docker, you connect to a running container through : docker exec -it your_running_container sh

So i guess with podman you have to replace docker by podman...

[DamonLane]

Yes, I have a volume that mounts 'data' inside my emoncms folder. Inside that data folder, the container added another emoncms folder with the following contents:

damon@opi:~/emoncms/data/emoncms$ ls -l
total 20
drwxr-xr-x 3 165635 damon 4096 Jun  6 15:37 backup
drwx------ 2 165635 damon 4096 Jun 18 08:05 mysql
drwxr-xr-x 2 165635 damon 4096 Jun  6 15:38 phpfina
drwxr-xr-x 2 165635 damon 4096 Jun  6 15:37 phpfiwa
drwxr-xr-x 2 165635 damon 4096 Jun  6 15:38 phptimeseries

June modified dates? That doesn't look good. It's possible I copied those there manually and hoped the container would update them, but it never did. I.e., it's possible I set up this container in June.

Although this is rootless podman, inside the container is running a root user. Podman/systemd apparently make that UID 165635 in this case (since it's not actually root on the host system). Sometimes I try to have the container use my UID inside to facilitate file sync and backup, but it often doesn't work since containers often expect to have a root user/process, which sounds like the case with this one since that HA backup issue.

Yes podman exec works just like the docker command. I think podman commands are all the same. Yes this is the right path. I can get into my working container and with those mysql commands can see one user.

Some time ago, I think before I used the containerized version, I had a user 'damon' on my local Emoncms. Maybe I lost the password for that, and later would login to Emoncms with 'DamonLane'. Is the Emoncms user the same as the mysql user?

My container setup includes:
Environment=MYSQL_DATABASE=emoncms
Environment=MYSQL_USER=damon
Environment=MYSQL_PASSWORD=_________________________________

[alexandrecuer]

no the username/password between mysql and emoncms are not the same, but if you can log to the database when you are connected in CLI to the container, you should be able to recreate the user password...

it is something like that :

ALTER USER damon@localhost IDENTIFIED BY damon_password;
GRANT ALL ON emoncms.* TO damon@localhost; flush privileges;

damon_password has to be the same with your container setup

[DamonLane]

Hi Alex, sorry for disappearing. Thanks for your help before. I had tried to get it working, maybe messed up my data because of a database syntax mistake, got distracted with running the container as my user vs as root, went down several wrong paths suggested by ChatGPT, then went on a trip.

Now, I'm trying again. I can log into mysql using the user in the .container setup file:

561265eb844c:/opt/openenergymonitor# mysql -u damon -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 398
Server version: 10.11.14-MariaDB 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)]> use emoncms;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [emoncms]> select * from users;
Empty set (0.002 sec)

Despite being able to login with this user, the app doesn't run. ChatGPT was focused on users in mysql.global_priv. Maybe I can log into mysql because that user is set up there, but not in the emoncms user table?

The web interface shows this error:

Fatal error: Uncaught mysqli_sql_exception: Access denied for user 'damon'@'localhost' (using password: YES) in /var/www/emoncms/index.php:74 Stack trace: #0 /var/www/emoncms/index.php(74): mysqli->__construct() #1 {main} thrown in /var/www/emoncms/index.php on line 74

and the journal shows problems for this user and root:

Nov 25 19:33:37 opi emoncms[825401]: 2025-11-26  1:33:37 1398 [Warning] Access denied for user 'root'@'localhost' (using password: NO)
Nov 25 19:33:38 opi emoncms[825401]: 2025-11-26  1:33:38 1399 [Warning] Access denied for user 'damon'@'localhost' (using password: YES)