You could try running the docker version, this has an MQTT built in as standard, at least it will allow you to quickly test to determine where the issue is:
MQTT explorer is very useful to test an MQTT server: http://mqtt-explorer.com/
Not sure I would be able to run the Docker container within Proxmox, will look into that. I did see a previous post where someone else had the same error showing. They said they reset the MQTT credentials following a procedure in the knowledge base. I’ve wanted to try that, but for the life of my can not find that procedure on how to reset those.
That said, I have noticed that during the installation near the end there seems to be a Fatal Error. I think I will rebuild it and look to capture that error to see if maybe that might be the cause as well. I tend to rerun the install script and it finishes fine the 2nd time.
Continuing on. Here is the error that I am seeing when I install EmonCMS.
PHP Fatal error: Uncaught mysqli_sql_exception: Table 'graph' already exists in /var/www/emoncms/Lib/dbschemasetup.php:275
Stack trace:
#0 /var/www/emoncms/Lib/dbschemasetup.php(275): mysqli->query()
#1 /opt/openenergymonitor/EmonScripts/common/emoncmsdbupdate.php(20): db_schema_setup()
#2 {main}
thrown in /var/www/emoncms/Lib/dbschemasetup.php on line 275
Fatal error: Uncaught mysqli_sql_exception: Table 'graph' already exists in /var/www/emoncms/Lib/dbschemasetup.php:275
Stack trace:
#0 /var/www/emoncms/Lib/dbschemasetup.php(275): mysqli->query()
#1 /opt/openenergymonitor/EmonScripts/common/emoncmsdbupdate.php(20): db_schema_setup()
#2 {main}
thrown in /var/www/emoncms/Lib/dbschemasetup.php on line 275
Also, I’m using MQTT Explorer and this is all I seem to see.
It would appear that it’s not even able to connect. Or is and disconnecting immediately. Like something on the EmonCMS side is not keeping the connection.
Edit:
So in continuing to troubleshoot. I went in an edited the settings.ini and rebooted. Finally got a change, though not what I was hoping for, but a change. Still getting the same result on MQTT Explorer as see above, but the EmonCMS Log shows something.
<details><summary>LAST ENTRIES ON THE LOG FILE</summary><br />
2023-07-14 17:46:31.018|ERROR|emoncms_mqtt.php|unexpected connection problem mqtt server:Connection Refused: not authorised.<br />
2023-07-14 17:46:37.003|WARN|emoncms_mqtt.php|Not connected, retrying connection<br />
2023-07-14 17:46:37.014|WARN|emoncms_mqtt.php|Connecting to MQTT server: Connection Refused: not authorised.: code: 5<br />
2023-07-14 17:46:37.014|ERROR|emoncms_mqtt.php|unexpected connection problem mqtt server:Connection Refused: not authorised.<br />
2023-07-14 17:46:43.002|WARN|emoncms_mqtt.php|Not connected, retrying connection<br />
2023-07-14 17:46:43.013|WARN|emoncms_mqtt.php|Connecting to MQTT server: Connection Refused: not authorised.: code: 5<br />
2023-07-14 17:46:43.013|ERROR|emoncms_mqtt.php|unexpected connection problem mqtt server:Connection Refused: not authorised.<br />
2023-07-14 17:46:49.003|WARN|emoncms_mqtt.php|Not connected, retrying connection<br />
2023-07-14 17:46:49.014|WARN|emoncms_mqtt.php|Connecting to MQTT server: Connection Refused: not authorised.: code: 5<br />
2023-07-14 17:46:49.014|ERROR|emoncms_mqtt.php|unexpected connection problem mqtt server:Connection Refused: not authorised.<br />
2023-07-14 17:46:55.002|WARN|emoncms_mqtt.php|Not connected, retrying connection<br />
2023-07-14 17:46:55.013|WARN|emoncms_mqtt.php|Connecting to MQTT server: Connection Refused: not authorised.: code: 5<br />
2023-07-14 17:46:55.013|ERROR|emoncms_mqtt.php|unexpected connection problem mqtt server:Connection Refused: not authorised.<br />
2023-07-14 17:47:01.007|WARN|emoncms_mqtt.php|Not connected, retrying connection<br />
2023-07-14 17:47:01.018|WARN|emoncms_mqtt.php|Connecting to MQTT server: Connection Refused: not authorised.: code: 5<br />
2023-07-14 17:47:01.018|ERROR|emoncms_mqtt.php|unexpected connection problem mqtt server:Connection Refused: not authorised.<br />
2023-07-14 17:47:07.004|WARN|emoncms_mqtt.php|Not connected, retrying connection<br />
2023-07-14 17:47:07.014|WARN|emoncms_mqtt.php|Connecting to MQTT server: Connection Refused: not authorised.: code: 5<br />
2023-07-14 17:47:07.015|ERROR|emoncms_mqtt.php|unexpected connection problem mqtt server:Connection Refused: not authorised.<br />
2023-07-14 17:47:13.007|WARN|emoncms_mqtt.php|Not connected, retrying connection<br />
2023-07-14 17:47:13.017|WARN|emoncms_mqtt.php|Connecting to MQTT server: Connection Refused: not authorised.: code: 5<br />
2023-07-14 17:47:13.017|ERROR|emoncms_mqtt.php|unexpected connection problem mqtt server:Connection Refused: not authorised.<br />
2023-07-14 17:47:19.007|WARN|emoncms_mqtt.php|Not connected, retrying connection<br />
2023-07-14 17:47:19.018|WARN|emoncms_mqtt.php|Connecting to MQTT server: Connection Refused: not authorised.: code: 5<br />
2023-07-14 17:47:19.018|ERROR|emoncms_mqtt.php|unexpected connection problem mqtt server:Connection Refused: not authorised.</details><br />
Sorry for the repeated entry. I might have been slightly frustrated. But I was trying both MQTT Explorer and OpenEVSE. This is a change from the one I was seeing before. So, looks like it sees the connection coming in at least.
Stepping back a bit. Change my log level to Error to see what else might be going on. So, this is the MQTT information the log shows.
2023-07-15 13:13:33.-80|WARN|emoncms_mqtt.php|Not connected, retrying connection
2023-07-15 13:13:33.-69|WARN|emoncms_mqtt.php|Connecting to MQTT server: Connection Accepted.: code: 0
2023-07-15 13:13:33.-69|INFO|emoncms_mqtt.php|Subscribed to: emon/# ID - 1
2023-07-15 13:13:33.-59|INFO|emoncms_mqtt.php|Callback subscribed to topic:
So, from what I can tell. It looks like it does establish a connection, but there doesn’t seem to be any topic that is being sent back. Am I reading this right?
So I pulled up the debug option in OpenEVSE to see if maybe I could see what the EVSE was seeing when it tried to connect via MQTT.
"mqtt_connected":0}
{"amp":0,"voltage":240,"power":0,"pilot":40,"temp":387,"temp_max":387,"temp1":387,"temp2":false,"temp3":false,"temp4":false,"state":254,"status":"disabled","flags":513,"vehicle":0,"colour":5,"manual_override":0,"freeram":174564,"divertmode":1,"srssi":-60,"time":"2023-07-15T23:44:59Z","local_time":"2023-07-15T17:44:59-0600","offset":"-0600","session_elapsed":0,"session_energy":0,"total_energy":157.2954046,"total_day":0.223048336,"total_week":23.13938604,"total_month":43.64040468,"total_year":43.64040468,"total_switches":35,"elapsed":0,"wattsec":0,"watthour":157295.4046}
{"session_elapsed":0,"session_energy":0,"total_energy":157.2954046,"total_day":0.223048336,"total_week":23.13938604,"total_month":43.64040468,"total_year":43.64040468,"total_switches":35}
{"emoncms_connected":0,"emoncms_message":"Failed to connect"}
So, from what I can see. It looks like it EmonCMS is reporting it failed to connect (or OpenEVSE is). Though, my understanding is the “0” is a response that it connects. Anyone have any incite here?
@borpin I know you used Proxmox with a container and EmonCMS. Not sure if you used OpenEVSE and connected it via MQTT or not. I’ve tried this with both a container and VM and it’s the same either way which is odd. It’s like something has changed under the hood in EmonCMS here because I would expect through a VM and Ubuntu Server 22.04 to be as it would on a stand alone system.
Continuing with my troubleshooting. So I did the MQTT test where I created a test topic and published to via a 2nd instance. That worked, so I at least know that MQTT is up and available to connect and publish. I’m starting to think the issue isn’t with MQTT but instead with EmonCMS. My question here is, once a connection is made to MQTT through Mosquitto. What occurs? Does the client then say it wants to connect to EmonCMS and publish into a database? Does it call up a topic "emon/#? I have edited the settings.ini to include a base topic thinking maybe that was it, but that didn’t seem to fix it.
Is mosquitto MQTT server running? What’s the output of
sudo systemctl status mosquitto
and the log
sudo tail /var/log/mosquitto/mosquitto.log
Also what’s the contents of the mosquitto config,
cat /etc/mosquitto/mosquitto.conf
It should look like:
# Place your local configuration in /etc/mosquitto/conf.d/
#
# A full description of the configuration file is at
# /usr/share/doc/mosquitto/examples/mosquitto.conf.example
listener 1883 0.0.0.0
pid_file /run/mosquitto/mosquitto.pid
persistence false
persistence_location /var/lib/mosquitto/
log_dest file /var/log/mosquitto/mosquitto.log
include_dir /etc/mosquitto/conf.d
allow_anonymous false
password_file /etc/mosquitto/passwd
log_type error
Mosquitto does appear to be running per systemctl output.
Output of the mosquitto log is this.
pi@EmonCMS-CT:~$ sudo tail /var/log/mosquitto/mosquitto.log
1689370632: Opening ipv6 listen socket on port 1883.
1689370632: mosquitto version 2.0.11 running
1689370721: New connection from ::1:55062 on port 1883.
1689370721: New client connected from ::1:55062 as emoncms (p2, c1, k5, u'emonpi').
1689370729: New connection from ::1:45384 on port 1883.
1689370729: New client connected from ::1:45384 as auto-C0523A4B-CDB2-C09C-7F0F-BEF55D74B950 (p2, c1, k5, u'emonpi').
1689371649: Client auto-C0523A4B-CDB2-C09C-7F0F-BEF55D74B950 closed its connection.
1689371649: Client emoncms closed its connection.
1689371649: mosquitto version 2.0.11 terminating
1689371649: Saving in-memory database to /var/lib/mosquitto//mosquitto.db.
Looks like the client, emoncms, is closing the connection, but one was made.
Mosquitto configuration is this.
pi@EmonCMS-CT:~$ cat /etc/mosquitto/mosquitto.conf
# Place your local configuration in /etc/mosquitto/conf.d/
#
# A full description of the configuration file is at
# /usr/share/doc/mosquitto/examples/mosquitto.conf.example
pid_file /run/mosquitto/mosquitto.pid
persistence false
persistence_location /var/lib/mosquitto/
log_dest file /var/log/mosquitto/mosquitto.log
include_dir /etc/mosquitto/conf.d
allow_anonymous false
password_file /etc/mosquitto/passwd
log_type error
Try disabling the ‘Validate certificate’ toggle, does MQTT Explorer connect successfully now? If MQTT Explorer does not maintain a connection either, then the issue must be with Mosquitto MQTT server rather than Emoncms.
I’ve tried it both ways and get the same result. Probably should reinstall MQTT?
To expand. Is the Mosquitto MQTT server apart of EmonCMS or separate? Also, since this is a fresh install I’m beginning to wonder if there might have been an update to Mosquitto MQTT server that might have broken something. Not sure if you guys have updated your own and ran into this. That said, I could reinstall Mosquitto MQTT server just to see if maybe something in the script caused a bad install. Does the script just install Mosquitto or does it do anything else?
Totally separate. You should focus on getting MQTT Explorer to connect reliably to Mosquitto server before thinking about Emoncms. The script just installs Mosquitto and Mosquitto PHP plugin:
Try adding the following to mosquitto.conf
listener 1883
Then restart mosquitto and try and connect with MQTT explorer.
Well, adding that made a difference.
So, now that I’m connected. Time to see if OpenEVSE is getting anywhere. But, it’s a nice change. I think I added that before but got no love. Though, I might have forgot to restart MQTT.
Excellent, what version of mosquitto are you running? I think the requirement for listener 1883 was added in a newer version that we currently use.
I can see the openevse is connecting successfully, you should be good to go now.
I’ll add listener 1883 to our install script.
I’ll get the version when I get home. I was starting to wonder if maybe I just happened to have gotten a newer version and a requirement changed, but it’s good to see I got the catch the change so we can nib this in the bud now. Both OpenEVSE are connection via MQTT and uploading to EmonCMS. Now I just need to flip my CTs for IotaWatt as it looks like my guess was 180 degrees off. Then build some Feeds.
1 Like
Look like it’s version 2.0.11
||/ Name Version Architecture Description
+++-==============-===============-============-====================================================
ii mosquitto 2.0.11-1ubuntu1 amd64 MQTT version 5.0/3.1.1/3.1 compatible message broker
Interesting, the version on our image is also 2.0.11, however looking at mosquitto.conf it looks like listener 1883 is present, it must have been added in later but not included in the installation script.
Do you recall this @TrystanLea?
Sorry for the hassle, thanks for helping us to fix this.
No worries. Next up for me is that it appears that EmonCMS is locking up after running for an extended time. I’m still troubleshooting that to confirm it’s not a fluke as I also appear to have an IotaWatt that is out of commission now, possible corrupted SD Card maybe or something else. lol. Home reno and tech is a fun mix.
Ah right, thanks.
1 Like