Database configuration howto

---

@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.