curzon01 / mqtt2sql Goto Github PK

First: THANK YOU! This allowed me to start collecting data in minutes 👍

After collecting data for a few days, I started noticing that there was something odd about the data: it was all in local time. Which means that when there's a DST change (winter time just started here in Belgium on October 29), the records would be very confusing:

• …
• 02:59:58
• 02:59:59
• 02:00:00
• …

i.e. 3 AM becomes 2 AM on October 29.

The even bigger problem: no timezone information is stored! 😨 In fact, the schema claims to store UTC timestamps, but the reality is the opposite:

CREATE TABLE IF NOT EXISTS `mqtt` (
	`id` INTEGER PRIMARY KEY AUTOINCREMENT,
	`ts` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

👆 This means the default value for ts is the current UTC timestamp.

        timestamp = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
…
                    sql1 = "INSERT OR IGNORE INTO `{0}` \
                            (ts,topic,value,qos,retain) \
                            VALUES('{1}','{2}',x'{3}','{4}','{5}')"\
                        .format(
                            self.args_.sql_table,
                            timestamp,

☝️ This means that a local timestamp formatted as a UTC timestamp is inserted! This contradicts what the schema claims.

The proper way is:

1. store in UTC, always — this also prevents DST issues (SQLite: datetime('now') for UTC, datetime('now', 'localtime') for local time)
2. present in local time, using SELECT datetime(ts, 'localtime') FROM mqtt_history WHERE …;

Would you be willing to accept a PR to change this?

Change python to python3 in the instructions

Здравствуйте!
не получается добавить в автозагрузку скрипт работает 2 секунды и вылетает, с теми же параметрами запускаю из терминала все работает

Before execution of python3 mysql module. 2 Lines for 22.04 libmysql requirement:

 sudo apt-get install python3-dev default-libmysqlclient-dev build-essential

 sudo apt install pkg-config

see: https://stackoverflow.com/a/76588599/4953146

I've run the mysql database schema creation command but I'm receiving:
ERROR 1064 (42000) at line 67: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF NOT EXISTS mqtt_before_insert BEFORE INSERT ON mqtt FOR EACH ROW BEGIN
' at line 1

The system runs and shows the latest value in "mqtt" table but there's nothing in mqtt_history.

Server type: MySQL
Server version: 5.7.33

I haven't found anything in the documentation that covers it yet, can you help me sus it out?

CREATE VIEW IF NOT EXISTS mqtt_history_view AS
XXXXXXXXXXXXXX

VIEW IF NOT EXISTS is unsupported per https://stackoverflow.com/a/78208913/4953146

Deleted VIEW IF NOT EXISTS => script ran without errors

I've been trying this script since yesterday and it's about 3MB data / day.
The published value for many topics stay the same for quite some time however.
Thus would be very cool to have an opt-in option/flag that only runs the insert/update statement when the value is different to the existing one for a given topic (ignore timestamp in this case). This results in no automated trigger for copy to history.

Example: Some service publishes data every minute via mqtt , however data rarely change. I'm not interested in the history whether or not it was 30 times the same a row, only the change is relevant for history.

Note: I know it's possible to set specific topics, but I want all topics to history (#)

I found this great project and it's perfect for me to get the mqtt data from my home automation system.

The next step for me is to take this historic data and plot it so I can get graphs for my data. And I thought that perhaps someone have already one working solution to share in GitHub 😀

I'm going to try write a graphing solution using react and perhaps .net as backend part.

Hi, I can't stop the program...

Hello

Is it possible to use wildcards in the "topic" parameter?

That is, what would I have to put in order for me to get all messages from a server regardless of topic?

Thansk,
Very good code.

Hello friend! when you run the command m@my-ubuntu:~$ /home/m/mqtt2sql/mqtt2sql.py --qt-mnt --host localhost-username ******* --mts-password ******* \

--mqtt-topic '/#'
--> --sql-type sqlite --sql-db tt.db-v
2020-12-20 01:54:20: mqtt2sql.py[20040] v2.4.0041 start
2020-12-20 01:54:20: MQT server: localhost:1883 keepalive 60
2020-12-20 01:54:20: user: ********
2020-12-20 01:54:20: topics: ['/#']
2020-12-20 01:54:20: SQL-type: SQLite
2020-12-20 01:54:20: server: localhost:3306 [max 50 connections]
2020-12-20 01:54:20: db: mqtt.db
2020-12-20 01:54:20: table: mqtt
2020-12-20 01:54:20: user: None
2020-12-20 01:54:20: verbose level: 1 works properly but when autorun the configuration file in the application does not work i think that the problem is in the sql user
mqtt2sql-conf.txt

Start string:
./mqtt2sql.py --mqtt mqtt://user:pass@host:1883/topic/# --mqtt-insecure --sql-type mysql --sql-host host --sql-username mqtt --sql-password 'pass' --sql-db mqtt -v
Output:
2024-05-27 23:43:26: mqtt2sql.py[1917929] v3.0.2 start
2024-05-27 23:43:26: MQTT server: 192.168.31.3:1883 (suppress TLS verification) keepalive 60
2024-05-27 23:43:26: user: user
2024-05-27 23:43:26: topics: topic/#
2024-05-27 23:43:26: exclude: None
2024-05-27 23:43:26: SQL type: MySQL
2024-05-27 23:43:26: server: 192.168.31.3:3306 [max 50 connections]
2024-05-27 23:43:26: db: mqtt
2024-05-27 23:43:26: table: mqtt
2024-05-27 23:43:26: user: mqtt
2024-05-27 23:43:26: timezone: UTC
2024-05-27 23:43:26: Verbose level: 1
2024-05-27 23:44:11: end
2024-05-27 23:44:11: mqtt2sql.py[1917929] v3.0.2 end
2024-05-27 23:44:11: signal SIGTERM#15
2024-05-27 23:44:11: mqtt2sql.py[1917929] v3.0.2 end
2024-05-27 23:44:11: end
2024-05-27 23:44:11: mqtt2sql.py[1917929] v3.0.2 end
2024-05-27 23:44:11: signal SIGTERM#15
2024-05-27 23:44:11: mqtt2sql.py[1917929] v3.0.2 end
Exception ignored in: <module 'threading' from '/usr/lib/python3.10/threading.py'>
Traceback (most recent call last):
File "/usr/lib/python3.10/threading.py", line 1567, in shutdown
lock.acquire()
File "/opt/mqtt2sql/./mqtt2sql.py", line 934, in exitus
self.exitus(signal, 'signal {}#{}'.format(self.signalname(signal), signal))
File "/opt/mqtt2sql/./mqtt2sql.py", line 955, in exitus
sys.exit(status)
SystemExit: 0

Why I can't choose MySQL?

actually, I use MariaDB, so which type I should choose？

Hi

I followed your steps and when I execute the instruction it gives me an error

TypeError: on_connect() take exactly 3 arguments (4 given)

Can you help me?

I have noticed that my mqtt2sql service has started generating errors.

First I got these errors:

Jan 09 02:17:17 staropramen systemd[1]: Started mqtt2sql.service.
Jan 09 02:17:17 staropramen mqtt2sql.py[12599]: No module named 'configargparse'. Try 'python -m pip install 'configargparse'' to install
Jan 09 02:17:17 staropramen systemd[1]: mqtt2sql.service: Main process exited, code=exited, status=9/n/a
Jan 09 02:17:17 staropramen systemd[1]: mqtt2sql.service: Failed with result 'exit-code'.
Jan 09 02:17:27 staropramen systemd[1]: mqtt2sql.service: Scheduled restart job, restart counter is at 107.
Jan 09 02:17:27 staropramen systemd[1]: Stopped mqtt2sql.service.

i tried running the install as suggested but still same error, but I ran it with sudo I'm getting these errors instead:

Jan 09 02:17:27 staropramen systemd[1]: Started mqtt2sql.service.
Jan 09 02:18:19 staropramen mqtt2sql.py[12692]: 2022-01-09 02:17:28: mqtt2sql.py[12692] v2.4.0041 start
Jan 09 02:18:19 staropramen mqtt2sql.py[12692]: 2022-01-09 02:18:19: mqtt2sql.py[12692] v2.4.0041 end
Jan 09 02:18:19 staropramen mqtt2sql.py[12692]: 2022-01-09 02:18:19: mqtt2sql.py[12692] v2.4.0041 end
Jan 09 02:18:19 staropramen mqtt2sql.py[12692]: Exception ignored in: <module 'threading' from '/usr/lib/python3.10/threading.py'>
Jan 09 02:18:19 staropramen mqtt2sql.py[12692]: Traceback (most recent call last):
Jan 09 02:18:19 staropramen mqtt2sql.py[12692]:   File "/usr/lib/python3.10/threading.py", line 1560, in _shutdown
Jan 09 02:18:19 staropramen mqtt2sql.py[12692]:     lock.acquire()
Jan 09 02:18:19 staropramen mqtt2sql.py[12692]:   File "/home/markus/bin/mqtt2sql.py", line 851, in _exitus
Jan 09 02:18:19 staropramen mqtt2sql.py[12692]:     self.exitus(signal_, 'signal {}#{}'.format(self._signalname(signal_), signal_))
Jan 09 02:18:19 staropramen mqtt2sql.py[12692]:   File "/home/markus/bin/mqtt2sql.py", line 872, in exitus
Jan 09 02:18:19 staropramen mqtt2sql.py[12692]:     sys.exit(status)
Jan 09 02:18:19 staropramen mqtt2sql.py[12692]: SystemExit: 0
Jan 09 02:18:19 staropramen mqtt2sql.py[12692]: 2022-01-09 02:18:19: mqtt2sql.py[12692] v2.4.0041 end
Jan 09 02:18:19 staropramen mqtt2sql.py[12692]: 2022-01-09 02:18:19: mqtt2sql.py[12692] v2.4.0041 end
Jan 09 02:18:19 staropramen systemd[1]: mqtt2sql.service: Deactivated successfully.
Jan 09 02:18:29 staropramen systemd[1]: mqtt2sql.service: Scheduled restart job, restart counter is at 108.
Jan 09 02:18:29 staropramen systemd[1]: Stopped mqtt2sql.service.

In the home page's instructions: update the python command to python3

I'd love to have this as a self contained docker container to keep the program more isolated so it's easier to update. Now with latest update I needed to fiddle and install some python libraries.