[vz-users] Datenbank-Umzug auf neuen Raspi schlägt fehl

John Doe Tue, 17 Aug 2021 05:16:02 -0700

Hallo zusammen,

nach einigen Kartencrashs möchte ich mein System auf etwas stabilere Füße in Form eines RPi 4, der von SSD bootet, stellen.

Was ich bisher getan habe:

1. Aktuelles Image installiert (der Fehler beim Booten lag tatsächlich an einem zu schwachen Netzteil, mit 18W läuft alles).

2. Ein sqlite.db3 (Backup) ins home-Verzeichnis des RPi4 kopiert.

3. dbcopy.yaml und vzlogger.conf kopiert:

dbcopy.yaml:

# DATABASE DEFINITION
source:
driver: pdo_mysql
host: localhost
user: root
password: demo
dbname: volkszaehler

target:
driver: pdo_sqlite
host: localhost
user: root
password: raspberry
dbname: volkszaehler_backup
path: /home/pi/sqlite.db3 # path is only used if driver = pdo_sqlite

# influxdb target database connection
influx:
dsn: influxdb://localhost:8086
dbname: volkszaehler
measurement: data

# TABLE DEFINITION
# ----------------
# tables will be processed in the order they are mentioned:
#       - foreign keys on target will be dropped
#       - if a table is not listed here, it will not be touched
# transfer mode
#       skip:       table will not be copied
#       copy:       entire table will be truncated on target and copied from source
#       pk:           selective copy by primary key. only data not present on target
#                    will be copied from source.
tables:
entities: copy
properties: copy
entities_in_aggregator: copy
data: pk
aggregate: skip

vzlogger.conf:

{
"retry": 0,
"daemon": true,
"verbosity": 0,
"log": "/var/log/vzlogger.log",
"local": {
    "enabled": false,
    "port": 8080,
    "index": false,
    "timeout": 0,
    "buffer": 0
},
"meters": [
    {
      "enabled": true,
      "allowskip": false,
      "interval": -1,
      "aggtime": -1,
      "aggfixedinterval": false,
      "channels": [
        {
          "uuid": "36743eb0-8518-11e9-a32c-2f8e238be491",
          "identifier": "1-0:1.8.1*255",
          "api": "volkszaehler",
          "middleware": "http://localhost/middleware.php",
          "secretKey": "",
          "type": "device",
          "scaler": 1,
          "aggmode": "none",
          "duplicates": 0
        }
      ],
      "protocol": "d0",
      "device": "/dev/ttyUSB0",
      "baudrate": 9600,
      "parity": "7e1",
    },
    {
      "enabled": true,
      "allowskip": false,
      "interval": -1,
      "aggtime": -1,
      "aggfixedinterval": false,
      "channels": [
        {
          "uuid": "d28a7a20-8518-11e9-8699-198afa78ae1b",
          "identifier": "1-0:1.8.1*255",
          "api": "volkszaehler",
          "middleware": "http://localhost/middleware.php",
          "secretKey": "",
          "type": "device",
          "scaler": 1,
          "aggmode": "none",
          "duplicates": 0
        }
      ],
      "protocol": "d0",
      "device": "/dev/ttyUSB1",
      "baudrate": 9600,
      "parity": "7e1",
       }
]
}

4. Auf dem neuen RPi 4:

pi@raspberrypi:~ $ sudo /var/www/volkszaehler.org/vendor/bin/dbcopy create -c /etc/dbcopy.yaml

In AbstractMySQLDriver.php line 112:

An exception occurred in driver: SQLSTATE[HY000] [1049] Unknown database 'volkszaehler'

In Exception.php line 18:

SQLSTATE[HY000] [1049] Unknown database 'volkszaehler'

In PDOConnection.php line 38:

SQLSTATE[HY000] [1049] Unknown database 'volkszaehler'

create [-c|--config CONFIG]

pi@raspberrypi:~ $ sudo mysql --user=root -praspberry
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 316
Server version: 10.3.29-MariaDB-0+deb10u1 Raspbian 10

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> CREATE DATABASE volkszaehler;
Query OK, 1 row affected (0.001 sec)

MariaDB [(none)]> exit
Bye

pi@raspberrypi:~ $ sudo /var/www/volkszaehler.org/vendor/bin/dbcopy create -c /etc/dbcopy.yaml
Creating target schema
Creating tables
Updating schema assets for target platform compatibility: sqlite

7. In der dbcopy.yaml source und target getauscht, dann

pi@raspberrypi:~ $ sudo /var/www/volkszaehler.org/vendor/bin/dbcopy copy -c /etc/dbcopy.yaml

In CopyCommand.php line 49:

Table entities doesn't exist.To create the schema run

   doctrine.php orm:schema-tool:create --dump-sql

copy [-c|--config CONFIG] [-b|--batch BATCH] [-k|--keep-constraints] [--] [<tables>...]

pi@raspberrypi:~ $ sudo volkszaehler.org/bin/doctrine orm:schema-tool:create --dump-sql

The following SQL statements will be executed:

     CREATE TABLE entities (id INT AUTO_INCREMENT NOT NULL, uuid VARCHAR(36) NOT NULL, type VARCHAR(255) NOT NULL, class VARCHAR(255) NOT NULL, UNIQUE INDEX UNIQ_50EC64E5D17F50A6 (uuid), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE `utf8_unicode_ci` ENGINE = InnoDB;
     CREATE TABLE entities_in_aggregator (parent_id INT NOT NULL, child_id INT NOT NULL, INDEX IDX_2BD88468727ACA70 (parent_id), INDEX IDX_2BD88468DD62C21B (child_id), PRIMARY KEY(parent_id, child_id)) DEFAULT CHARACTER SET utf8 COLLATE `utf8_unicode_ci` ENGINE = InnoDB;
     CREATE TABLE properties (id INT AUTO_INCREMENT NOT NULL, entity_id INT DEFAULT NULL, pkey VARCHAR(255) NOT NULL, value LONGTEXT NOT NULL, INDEX IDX_87C331C781257D5D (entity_id), UNIQUE INDEX property_unique (entity_id, pkey), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE `utf8_unicode_ci` ENGINE = InnoDB;
     CREATE TABLE data (id INT AUTO_INCREMENT NOT NULL, channel_id INT DEFAULT NULL, timestamp BIGINT NOT NULL, value DOUBLE PRECISION NOT NULL, INDEX IDX_ADF3F36372F5A1AA (channel_id), UNIQUE INDEX data_unique (channel_id, timestamp), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE `utf8_unicode_ci` ENGINE = InnoDB;
     CREATE TABLE aggregate (id INT AUTO_INCREMENT NOT NULL, channel_id INT DEFAULT NULL, type SMALLINT NOT NULL, timestamp BIGINT NOT NULL, value DOUBLE PRECISION NOT NULL, count INT NOT NULL, INDEX IDX_B77949FF72F5A1AA (channel_id), UNIQUE INDEX aggregate_unique (channel_id, type, timestamp), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE `utf8_unicode_ci` ENGINE = InnoDB;
     ALTER TABLE entities_in_aggregator ADD CONSTRAINT FK_2BD88468727ACA70 FOREIGN KEY (parent_id) REFERENCES entities (id);
     ALTER TABLE entities_in_aggregator ADD CONSTRAINT FK_2BD88468DD62C21B FOREIGN KEY (child_id) REFERENCES entities (id);
     ALTER TABLE properties ADD CONSTRAINT FK_87C331C781257D5D FOREIGN KEY (entity_id) REFERENCES entities (id);
     ALTER TABLE data ADD CONSTRAINT FK_ADF3F36372F5A1AA FOREIGN KEY (channel_id) REFERENCES entities (id);
     ALTER TABLE aggregate ADD CONSTRAINT FK_B77949FF72F5A1AA FOREIGN KEY (channel_id) REFERENCES entities (id);

pi@raspberrypi:~ $ sudo volkszaehler.org/bin/doctrine orm:schema-tool:update --force

Updating database schema...

10 queries were executed

[OK] Database schema updated successfully!

10. Nun das Problem:

pi@raspberrypi:~ $ sudo /var/www/volkszaehler.org/vendor/bin/dbcopy copy -c /etc/dbcopy.yaml
Dropping FK FK_87C331C781257D5D on properties
Dropping FK FK_2BD88468727ACA70 on entities_in_aggregator
Dropping FK FK_2BD88468DD62C21B on entities_in_aggregator
Dropping FK FK_ADF3F36372F5A1AA on data
Dropping FK FK_B77949FF72F5A1AA on aggregate
entities: copying 0 rows (overwrite)
0 [->--------------------------] < 1 sec 6.0 MiB

properties: copying 0 rows (overwrite)
0 [->--------------------------] < 1 sec 6.0 MiB

entities_in_aggregator: copying 0 rows (overwrite)
0 [->--------------------------] < 1 sec 6.0 MiB

data: copying 0 rows (partial copy)
0 [->--------------------------] < 1 sec 6.0 MiB

aggregate: skipping
Creating FK FK_B77949FF72F5A1AA on aggregate

Auf meinem laufenden RPi3 dauert ein DB-Restore von ca. 3.2 GB ca. fünf Stunden. Hier scheint etwas nicht zu klappen.

Kann mir jemand evtl sagen, was ich falsch mache ?

Grüße

JD.

[vz-users] Datenbank-Umzug auf neuen Raspi schlägt fehl

Antwort per Email an