Database configuration howto

---

Another question

So with the default configuration, pimatic checks every 2h the database for expired entries and deletes them (in memory) and each second time (every 4h) the changes are synced to disk (deleted and new entries).

I have the impression that nothing is written in the DB anymore, for testing purpose i have reduce deleteExpiredInterval and diskSyncInterval to 1 minute and 3 minutes

"database": {
  "client": "sqlite3",
  "connection": {
    "filename": "pimatic-database.sqlite"
  },
  "deviceAttributeLogging": [
    {
      "deviceId": "*",
      "attributeName": "*",
      "expire": "7d",
      "type": "*"
    },
    {
      "deviceId": "pin-switch",
      "attributeName": "*",
      "expire": "0",
      "type": "*"
    },
    {
      "attributeName": "contact",
      "expire": "31d",
      "type": "*"
    },
    {
      "deviceId": "rasptime",
      "attributeName": "uptime",
      "expire": "0",
      "type": "*"
    },
    {
      "deviceId": "syssensor",
      "attributeName": "*",
      "interval": "5m",
      "expire": "7d",
      "type": "*"
    },
    {
      "deviceId": "*",
      "attributeName": "temperature",
      "interval": "5m",
      "expire": "1y",
      "type": "*"
    },
    {
      "deviceId": "*",
      "attributeName": "humidity",
      "interval": "5m",
      "expire": "1y",
      "type": "*"
    },
    {
      "deviceId": "gas-sensor",
      "attributeName": "*",
      "interval": "10m",
      "expire": "31d",
      "type": "*"
    },
    {
      "deviceId": "conso-gaz-24h",
      "attributeName": "*",
      "interval": "1h",
      "expire": "1y",
      "type": "*"
    },
    {
      "deviceId": "elec-sensor",
      "attributeName": "*",
      "interval": "10m",
      "expire": "31d",
      "type": "*"
    },
    {
      "deviceId": "conso-elec-24h",
      "attributeName": "*",
      "interval": "1h",
      "expire": "1y",
      "type": "*"
    }
  ],
  "messageLogging": [
    {
      "level": "*",
      "tags": [],
      "expire": "7d"
    },
    {
      "level": "debug",
      "tags": [],
      "expire": "0"
    }
  ],
  "deleteExpiredInterval": "1m",
  "diskSyncInterval": "3m",
  "debug": true
},

I can’t see any message in pimatic log about the DB sync and nothing on the timestamp of the DB : so my impression is that nothing will go to disk (pimatic-database.sqlite-wal remains empty too)

@Yves911 said:

Another question

So with the default configuration, pimatic checks every 2h the database for expired entries and deletes them (in memory) and each second time (every 4h) the changes are synced to disk (deleted and new entries).

I have the impression that nothing is written in the DB anymore, for testing purpose i have reduce deleteExpiredInterval and diskSyncInterval to 1 minute and 3 minutes

Thanks for reporting. The min deleteExpriedInterval was 5min (you should have a warning about that in your log), If it is less than that it is automatically set to 5min.

I can’t see any message in pimatic log about the DB sync and nothing on the timestamp of the DB : so my impression is that nothing will go to disk (pimatic-database.sqlite-wal remains empty too)

In addition here was an bug, so that the disk sync wasn’t triggered if diskSyncInterval was not a multiple of deleteExpriedInterval (what was true in your case, because the auto set to 5min).

I lowered the lower bound to 1min and fixed the bug. Could you retest with 0.8.39?

EDIT: One more fix in 0.8.40!

With 0.8;39 i can see

20:39:52.483 [pimatic] debug: deleteing expired logged values

5 minutes after pimatic startup (=deleteExpriedInterval) => So all good for that.

Now i can’t tell you about diskSyncInterval because of that

20:39:32.624 [pimatic-homeduino] debug: generic:  { id: 1111, type: 2, positive: true, value: 774152 }
20:39:44.211 [pimatic-homeduino] debug: data: "RF receive 595 1923 3829 7648 0 0 0 0 0101010202020201020101010201020102020101010101020201010202020101020201020101010101020303"
20:39:44.224 [pimatic-homeduino] debug: received: [ 595, 1923, 3829, 7648 ] 0101010202020201020101010201020102020101010101020201010202020101020201020101010101020303
20:39:44.237 [pimatic-homeduino] debug: weather3:  { id: 122, channel: 3, temperature: 18.2, humidity: 55 }
20:39:49.500 [pimatic-homeduino] debug: data: "RF receive 689 4263 1970 10072 0 0 0 0 010201020102020101020102010202010102020101020201020102010102010202010102020101020102010201020102010201020102010201020201010202010201020102010102020101020102010201020102010201020102020102010102010203"
20:39:49.511 [pimatic-homeduino] debug: received: [ 689, 1970, 4263, 10072 ] 020102010201010202010201020101020201010202010102010201020201020101020201010202010201020102010201020102010201020102010102020101020102010201020201010202010201020102010201020102010201010201020201020103
20:39:49.524 [pimatic-homeduino] debug: generic:  { id: 1111, type: 2, positive: true, value: 774156 }
20:39:52.483 [pimatic] debug: deleteing expired logged values
20:40:09.589 [pimatic-homeduino] debug: Couldn't connect (operation timed out after 5000 ms), retrying...

When pimatic clean expired logged values CPU jump to 100% for a while (~1 min) and then pimatic-homeduino crash…

 4008 root      20   0  151m 110m   9m S  97,7 29,8   4:00.59 /usr/local/bin/node /root/pimatic-git/node_modules/pimatic/pimatic.js start 

I don’t understand what’s happen here?

@sweetpi said:

“filename”: “pimatic-database.sqlite”

If i want to put the database on a external usb drive, can i just change it to something like “filename”:"/media/usbstick/pimatic-database.sqlite" ?

@sweebee said:

@sweetpi said:

“filename”: “pimatic-database.sqlite”

If i want to put the database on a external usb drive, can i just change it to something like “filename”:"/media/usbstick/pimatic-database.sqlite" ?

yes

@Yves911 said:

With 0.8;39 i can see

20:39:52.483 [pimatic] debug: deleteing expired logged values

5 minutes after pimatic startup (=deleteExpriedInterval) => So all good for that.

Now i can’t tell you about diskSyncInterval because of that

20:39:32.624 [pimatic-homeduino] debug: generic:  { id: 1111, type: 2, positive: true, value: 774152 }
20:39:44.211 [pimatic-homeduino] debug: data: "RF receive 595 1923 3829 7648 0 0 0 0 0101010202020201020101010201020102020101010101020201010202020101020201020101010101020303"
20:39:44.224 [pimatic-homeduino] debug: received: [ 595, 1923, 3829, 7648 ] 0101010202020201020101010201020102020101010101020201010202020101020201020101010101020303
20:39:44.237 [pimatic-homeduino] debug: weather3:  { id: 122, channel: 3, temperature: 18.2, humidity: 55 }
20:39:49.500 [pimatic-homeduino] debug: data: "RF receive 689 4263 1970 10072 0 0 0 0 010201020102020101020102010202010102020101020201020102010102010202010102020101020102010201020102010201020102010201020201010202010201020102010102020101020102010201020102010201020102020102010102010203"
20:39:49.511 [pimatic-homeduino] debug: received: [ 689, 1970, 4263, 10072 ] 020102010201010202010201020101020201010202010102010201020201020101020201010202010201020102010201020102010201020102010102020101020102010201020201010202010201020102010201020102010201010201020201020103
20:39:49.524 [pimatic-homeduino] debug: generic:  { id: 1111, type: 2, positive: true, value: 774156 }
20:39:52.483 [pimatic] debug: deleteing expired logged values
20:40:09.589 [pimatic-homeduino] debug: Couldn't connect (operation timed out after 5000 ms), retrying...

When pimatic clean expired logged values CPU jump to 100% for a while (~1 min) and then pimatic-homeduino crash…

 4008 root      20   0  151m 110m   9m S  97,7 29,8   4:00.59 /usr/local/bin/node /root/pimatic-git/node_modules/pimatic/pimatic.js start 

I don’t understand what’s happen here?

That:s really strange. How big is your database? Can you test with an empty one? What does crash mean?

@sweetpi my DB is about 60 MB, i just tried with a new empty DB there is no problem

Here is the error message

12:44:59.488 [pimatic] deleteing expired logged values
12:45:35.436 [pimatic-homeduino] Couldn't connect (operation timed out after 5000 ms), retrying...
TypeError: Cannot read property 'length' of undefined
    at Function.Buffer.concat (buffer.js:491:20)
    at afterRead (/root/pimatic-git/node_modules/pimatic-homeduino/node_modules/homeduino/node_modules/serialport/serialport.js:323:36)
    at /root/pimatic-git/node_modules/pimatic-homeduino/node_modules/homeduino/node_modules/serialport/serialport.js:342:9
    at Object.wrapper [as oncomplete] (fs.js:454:17)

EDIT, i think (due to the error message) this is the continuation of the story here:
https://github.com/pimatic/pimatic/issues/443
except that now:
-pimatic is NOT stable when running (crash after some minutes or very few hours)
-pimatic crash *even if the raspberry has been freshly rebooted…

I Moved the database to a USB stick but after 8 hours:

Sd writes: 110000
USB writes: 420

Whats going wrong here? What makes al the writes On the sd card?

Hello,

my problem is that the database is only saved once.
No interval is following.

“database”: {
“client”: “sqlite3”,
“connection”: {
“filename”: “pimatic-database.sqlite”
},
“deviceAttributeLogging”: [
{
“deviceId”: “",
“attributeName”: "”,
“type”: “",
“interval”: “0”,
“expire”: “7d”
},
{
“deviceId”: "”,
“attributeName”: “",
“type”: “continuous”,
“interval”: “5min”,
“expire”: “7d”
},
{
“deviceId”: "”,
“attributeName”: “temperature”,
“type”: “number”,
“expire”: “1y”
},
{
“deviceId”: “",
“attributeName”: “humidity”,
“type”: “number”,
“expire”: “1y”
}
],
“messageLogging”: [
{
“level”: "”,
“tags”: [],
“expire”: “7d”
},
{
“level”: “debug”,
“tags”: [],
“expire”: “0”
}
],
“deleteExpiredInterval”: “1m”,
“diskSyncInterval”: “2m”,
“debug”: true
},

@Andre-Bensch could you try with

      "deleteExpiredInterval": "10m",
      "diskSyncInterval": "20m",

Thanks for your reply.
I did as you said, but still the saving just happens once.
Nothing happens later on.

@Andre-Bensch Thanks for reporting. This should be fixed in pimatic v0.8.50.

From this thread I learned that for intervals the units are

• s for seconds
• m for minutes
• h for hours
• y for years

Hope this is right. Is there also an unit letter for month?

Would be nice if this thread can ne moved to the how-to section or even better to extend the pimatic doocumentation.

Is it possible to disable some attributes completely from being logged in the database? I would like to exclude the battery attribute of my temperature sensor.

@michbeck100 said in Database configuration howto:

Is it possible to disable some attributes completely from being logged in the database? I would like to exclude the battery attribute of my temperature sensor.

@sweetpi said

All values with expire set to “0” are not written to the database at all. This also applies to the deviceAttributeLogging config.

i want to delete some old entries like “No device with the ID “XX” found.” and “Device “Battery” has no attribute with the name “battery_10” found.”. When i use the database-page i get an timeout … is there a trick do do it?

Can I use the wildcards in attributeName? temperature.* does not work. I have several devices that have several temperatures.
For example:

    {
      "attributes": [
        {
          "name": "temperature.fishtank",
          "topic": "mh/gf/lab/wemosd1r2/fishtank/temperature",
          "type": "number",
          "unit": "°C",
          "acronym": "fishtank",
          "messageMap": {}
        },
        {
          "name": "temperature.freshtank",
          "topic": "mh/gf/lab/wemosd1r2/freshwater/temperature",
          "type": "number",
          "unit": "°C",
          "acronym": "freshwater",
          "messageMap": {}
        },
        {
          "name": "temperature.room",
          "topic": "mh/gf/lab/wd1mini-9/si7021/temperature",
          "type": "number",
          "unit": " °C",
          "acronym": "room",
          "messageMap": {}
        }
      ],
      "xAttributeOptions": [
        {
          "name": "temperature.room",
          "displaySparkline": false
        }
      ],
      "id": "fishtank-temp",
      "name": "Temperature",
      "class": "MqttSensor"
    }

you could try it with this one for the fishtank-temp device to log all data with this id.
with the below example, you need to create a database entry for each device.
if a wildcard is possible i cannot tell, but maybe @sweetpi can ?

    {
       "deviceId": "fishtank-temp", 
       "attributeName": "*", 
       "type": "number",
       "interval": "5min", 
       "expire": "1y"
     }

Is it possible to read out the pimatic database via Grafana as it offers MySQL and now Microsoft SQL Server datasource features? Sorry for the noob question as i am still new to databases but i guess the answer to my question would be “no” as pimatic isnt offering a database server ? I am just trying to find a way to get the pimatic database file into grafana as i don´t want to feed all the pimatic data in my influx db and having twice the data…

@orki You could opt for using MySQL as DB platform (Installed either on the Pimatic server or remote), which is more flexible for remote access. Also see here. Hope this gets you underway!

Example section for config.json:

"database": {
      "client": "mysql",
      "connection": {
        "host": "<ip or dns name of DB server>",
        "user": "<user_with_db_admin",
        "password": "<db_admin_password>",
        "database": "<db-name>"
      },
      ...............

All you would need to do is install MySQL Db server, and configure an empty database, and a user with suffient privileges, and allowed to access from the localhost or local subnet.