Database

Domotiga uses a database to save almost all it's configuration and all sensor data.

You very likely installed MySQL according to the instructions on DomotiGa Installation and the first time Domotiga was started it created the database using the Database Install Wizard.

Accessing the database

If you did not change the default database in the MySQL Database Settings the default Database is called domotiga, the default username domouser and the default password kung-fu. Sample log in session:

$ mysql --user=domouser --password=kung-fu
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 642
Server version: 5.5.38-0+wheezy1 (Debian)

mysql> use domotiga;

Database changed
mysql> show tables;
+---------------------------+
| Tables_in_domotiga        |
+---------------------------+
| actions                   |
| calendar                  |
...
| conditions                |
| contacts                  |
| device_values             |
| device_values_log         |
| deviceblacklist           |
| devices                   |
...
| events                    |
| events_actions            |
...
| globalvars                |
| graph_data                |
| graphs                    |
...
| settings_*                |
...
| triggers                  |
...
+---------------------------+

Settings

  • The server is localhost (or 127.0.0.1) for stand-alone setup and the IP address or hostname or the server for a Client-Server setup
  • Database gives the name of the database Domotiga will use
  • The Username and Password give the creditals to access the database
  • By enabling Debug the commands that are used to access the database are logged
     

Backup and restore

A backup can be made with the following command:

$ mysqldump -u root -p domotiga >~/domotiga.<date>/domotiga-backup.sql

Restoring:

$ mysql -u root -p domotiga <~/domotiga.<date>/domotiga-backup.sql

Database Upgrade

See Database Upgrade.

The database upgrade is done using the upgrade/*.sql files, so upgrading from Domotiga 1.0.015 to 1.0.016 is done using the upgrade/10015to10016.sql file.

DomotiGa will detect itself that the database needs to be updated and if so, it does do so using:

mysql --host=<SQLHost> --user=<SQLUser> --password=<SQLPass> --database=<SQLDatabase> --execute='SOURCE upgrade/<xxxxxtoyyyyy.sql>'

Manual updating of database with SQL changes

If you are on the beta branch the upgrade file can change over time. Once you did the upgrade you have to update the database manually every time the upgrade file is change.

For example for this change:

You can do:

$ mysql --user=domouser --password=kung-fu
mysql> use domotiga;
mysql> INSERT INTO `devicetypes` VALUES (380,'TFA Dostmann Temp Sensor','Temp Sensor','TFA','temp10 0x121','','','',-1,-1,0,0,0,0,0);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `devicetypes` VALUES (381,'Imagintronix Soil Sensor','Soil Sensor','Imagintrx','th12 0x1','','','',-1,-1,0,0,0,0,0);                                                                      
Query OK, 1 row affected (0.01 sec)

mysql> UPDATE plugins SET protocols = 'X10 X10Security Oregon KAKU RFXCom AC HEUK ATI Digimax Mertik Ninja Flamingo Waveman HEEU ARC HE105 Koppla RTS10 Harrison Anslut Impuls AB400 EMW200 LightwaveRF TFA LaCrosse UPM Cresta Viking Rubicson RisingSun PhilipsSBC EMW100 BBSB Blyss RollerTrol HastaNew HastaOld A-OKRF01 A-OKAC114 Meiantech ByronSX SA30 X10SecDW X10SecMotion X10SecRemote PowerCodeDW PowerCodeMotion PowerCodeAux CodeSecure Energenie Livolo RSL TRC02 MDRemote SF01 RFY RFYEXT Imagin' WHERE id = 35;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> exit;

Reducing memory usage

MySQL can be quite a memory hog, especially for systems with the server running on less powerful hardware like the Raspberry (512/256 MByte) or Cubieboard2 (1 GByte).

To see how much virtual memory mysql is using, try (replacing 21488 by the pid of your running myslqd):

$ ps ax | grep mysqld
21136 ?        S      0:00 /bin/sh /usr/bin/mysqld_safe
21488 ?        Sl   241:09 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql ...
21489 ?        S      0:00 logger -t mysqld -p daemon.error
28781 pts/0    S+     0:00 grep mysqld

$ ps p 21488 o pid,rss,vsz,comm
  PID   RSS    VSZ COMMAND
  979 37644 317928 mysqld

RSS is the resident memory size (37 MByte), VSZ is the virtual memory size (318 MByte) for a Cubieboard2/Raspberry Pi. Doing the same for DomotiGaServer3.gambas 1.0.018 gives about about 8 MByte of resident memory and 52 MByte virtual memory so MySQL is using relatively a lot memory.

Depending on the Unix/Linux version the following works too. The header line is not shown but for explanation purposes added to the example.

$ ps aux | grep mysqld | grep -v grep
USER       PID %CPU %MEM    VSZ   RSS TTY      STAT START   TIME COMMAND
mysql     1163  0.4  1.9 324432 41192 ?        Ssl  Dec21  57:33 /usr/sbin/mysqld

Disable InnoDB

Check if there are any tables using InnoDB:

SELECT table_schema, table_name FROM INFORMATION_SCHEMA.TABLES WHERE engine = 'innodb';

+--------------+---------------------+
| table_schema | table_name          |
+--------------+---------------------+
| domotiga     | calendar            |
| domotiga     | temperature_profile |
| domotiga     | thermostat          |
| domotiga     | macros              |
+--------------+---------------------+

DomotiGa 1.0.018 does not use InnoDB tables anymore (see Domotiga 1.0.013 regulary hangs, sql errors and source:upgrade/10013to10014.sql) but for some reason I saw that the macros, calendar, temperature_profile, thermostat tables were still using the InnoDB engine. To change it login to mysql:

use domotiga;
ALTER TABLE table_name ENGINE = MyISAM;

If there are no tables left that are using InnoDB as engine, you can disable InnoDB.

$ sudo vi /etc/mysql/my.cnf

Add:
# Disable InnoDB
skip-innodb
default-storage-engine=MyISAM

And restart mysqld (just a reminder, STOP your applications first!):

sudo /etc/init.d/mysql restart

After this ps p reports 13 MByte (was 37) of residential memory and about 60 MByte (was 318) of virtual memory.

Retrieving properties

# All kind of properties of a table
DESCRIBE device_values;
SHOW index FROM device_values;
SHOW CREATE TABLE device_values;

SHOW variables LIKE '%character_set%';

Logging

You can enable logging in the /etc/mysql/my.cnf configuration file but for debugging it is handier to enable logging at runtime.
Log in as root and issue:

$ mysql --user=root --password=YourRootPassword
mysql> SET GLOBAL log_output="FILE";
mysql> SET GLOBAL general_log_file="/var/log/mysql/mysql.log";
mysql> SET GLOBAL general_log='ON';

To disable:

mysql> SET GLOBAL general_log='OFF';

Alternative databases

  • sqlite

    Updated by: domoldi, Updated over 2 years ago
    Access count: 46289 since 2014-08-30

    Attached Files

    Database_Settings.png (22.7 KB) danfos, 08/30/2014 11:02 AM

    DatabaseUpgrade.png (59.9 KB) danfos, 09/08/2014 11:25 PM

    Also available in: PDF HTML TXT