<html><head></head><body><div style="font-family: Verdana;font-size: 12.0px;"><div>Hallo zusammen,</div>
<div> </div>
<div>nach einigen Kartencrashs möchte ich mein System auf etwas stabilere Füße in Form eines RPi 4, der von SSD bootet, stellen.</div>
<div>Was ich bisher getan habe:</div>
<div> </div>
<div>1. Aktuelles Image installiert (der Fehler beim Booten lag tatsächlich an einem zu schwachen Netzteil, mit 18W läuft alles).</div>
<div> </div>
<div>2. Ein sqlite.db3 (Backup) ins home-Verzeichnis des RPi4 kopiert.</div>
<div> </div>
<div>3. dbcopy.yaml und vzlogger.conf kopiert:</div>
<div> </div>
<div>dbcopy.yaml:</div>
<div> </div>
<div>
<div># DATABASE DEFINITION<br/>
source:<br/>
driver: pdo_mysql<br/>
host: localhost<br/>
user: root<br/>
password: demo<br/>
dbname: volkszaehler</div>
<div>target:<br/>
driver: pdo_sqlite<br/>
host: localhost<br/>
user: root<br/>
password: raspberry<br/>
dbname: volkszaehler_backup<br/>
path: /home/pi/sqlite.db3 # path is only used if driver = pdo_sqlite</div>
<div># influxdb target database connection<br/>
influx:<br/>
dsn: influxdb://localhost:8086<br/>
dbname: volkszaehler<br/>
measurement: data</div>
<div># TABLE DEFINITION<br/>
# ----------------<br/>
# tables will be processed in the order they are mentioned:<br/>
# - foreign keys on target will be dropped<br/>
# - if a table is not listed here, it will not be touched<br/>
# transfer mode<br/>
# skip: table will not be copied<br/>
# copy: entire table will be truncated on target and copied from source<br/>
# pk: selective copy by primary key. only data not present on target<br/>
# will be copied from source.<br/>
tables:<br/>
entities: copy<br/>
properties: copy<br/>
entities_in_aggregator: copy<br/>
data: pk<br/>
aggregate: skip</div>
<div> </div>
<div> </div>
<div>vzlogger.conf:</div>
<div> </div>
<div>
<div>{<br/>
"retry": 0,<br/>
"daemon": true,<br/>
"verbosity": 0,<br/>
"log": "/var/log/vzlogger.log",<br/>
"local": {<br/>
"enabled": false,<br/>
"port": 8080,<br/>
"index": false,<br/>
"timeout": 0,<br/>
"buffer": 0<br/>
},<br/>
"meters": [<br/>
{<br/>
"enabled": true,<br/>
"allowskip": false,<br/>
"interval": -1,<br/>
"aggtime": -1,<br/>
"aggfixedinterval": false,<br/>
"channels": [<br/>
{<br/>
"uuid": "36743eb0-8518-11e9-a32c-2f8e238be491",<br/>
"identifier": "1-0:1.8.1*255",<br/>
"api": "volkszaehler",<br/>
"middleware": "http://localhost/middleware.php",<br/>
"secretKey": "",<br/>
"type": "device",<br/>
"scaler": 1,<br/>
"aggmode": "none",<br/>
"duplicates": 0<br/>
}<br/>
],<br/>
"protocol": "d0",<br/>
"device": "/dev/ttyUSB0",<br/>
"baudrate": 9600,<br/>
"parity": "7e1",<br/>
},<br/>
{<br/>
"enabled": true,<br/>
"allowskip": false,<br/>
"interval": -1,<br/>
"aggtime": -1,<br/>
"aggfixedinterval": false,<br/>
"channels": [<br/>
{<br/>
"uuid": "d28a7a20-8518-11e9-8699-198afa78ae1b",<br/>
"identifier": "1-0:1.8.1*255",<br/>
"api": "volkszaehler",<br/>
"middleware": "http://localhost/middleware.php",<br/>
"secretKey": "",<br/>
"type": "device",<br/>
"scaler": 1,<br/>
"aggmode": "none",<br/>
"duplicates": 0<br/>
}<br/>
],<br/>
"protocol": "d0",<br/>
"device": "/dev/ttyUSB1",<br/>
"baudrate": 9600,<br/>
"parity": "7e1",<br/>
}<br/>
]<br/>
}</div>
<div> </div>
<div>4. Auf dem neuen RPi 4:</div>
<div> </div>
<div> </div>
<div>
<div>pi@raspberrypi:~ $ sudo /var/www/volkszaehler.org/vendor/bin/dbcopy create -c /etc/dbcopy.yaml</div>
<div>In AbstractMySQLDriver.php line 112:<br/>
<br/>
An exception occurred in driver: SQLSTATE[HY000] [1049] Unknown database 'volkszaehler' <br/>
</div>
<div>In Exception.php line 18:<br/>
<br/>
SQLSTATE[HY000] [1049] Unknown database 'volkszaehler' <br/>
</div>
<div>In PDOConnection.php line 38:<br/>
<br/>
SQLSTATE[HY000] [1049] Unknown database 'volkszaehler' <br/>
</div>
<div>create [-c|--config CONFIG]</div>
<div> </div>
<div>5.</div>
<div> </div>
<div>
<div>pi@raspberrypi:~ $ sudo mysql --user=root -praspberry<br/>
Welcome to the MariaDB monitor. Commands end with ; or \g.<br/>
Your MariaDB connection id is 316<br/>
Server version: 10.3.29-MariaDB-0+deb10u1 Raspbian 10</div>
<div>Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.</div>
<div>Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.</div>
<div>MariaDB [(none)]> SHOW DATABASES;<br/>
+--------------------+<br/>
| Database |<br/>
+--------------------+<br/>
| information_schema |<br/>
| mysql |<br/>
| performance_schema |<br/>
+--------------------+<br/>
3 rows in set (0.001 sec)</div>
<div>MariaDB [(none)]> CREATE DATABASE volkszaehler;<br/>
Query OK, 1 row affected (0.001 sec)</div>
<div>MariaDB [(none)]> SHOW DATABASES;<br/>
+--------------------+<br/>
| Database |<br/>
+--------------------+<br/>
| information_schema |<br/>
| mysql |<br/>
| performance_schema |<br/>
| volkszaehler |<br/>
+--------------------+<br/>
4 rows in set (0.001 sec)</div>
<div>MariaDB [(none)]> exit<br/>
Bye</div>
<div> </div>
</div>
<div> </div>
</div>
<div>6.</div>
<div> </div>
<div>
<div>pi@raspberrypi:~ $ sudo /var/www/volkszaehler.org/vendor/bin/dbcopy create -c /etc/dbcopy.yaml<br/>
Creating target schema<br/>
Creating tables<br/>
Updating schema assets for target platform compatibility: sqlite</div>
<div> </div>
<div> </div>
</div>
<div>7. In der dbcopy.yaml source und target getauscht, dann</div>
<div> </div>
<div>
<div>pi@raspberrypi:~ $ sudo /var/www/volkszaehler.org/vendor/bin/dbcopy copy -c /etc/dbcopy.yaml</div>
<div>In CopyCommand.php line 49:<br/>
<br/>
Table entities doesn't exist.To create the schema run <br/>
<br/>
doctrine.php orm:schema-tool:create --dump-sql <br/>
</div>
<div>copy [-c|--config CONFIG] [-b|--batch BATCH] [-k|--keep-constraints] [--] [<tables>...]</div>
<div> </div>
<div> </div>
<div>8.</div>
<div> </div>
<div>
<div>pi@raspberrypi:~ $ sudo volkszaehler.org/bin/doctrine orm:schema-tool:create --dump-sql</div>
<div> The following SQL statements will be executed:</div>
<div> 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;<br/>
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;<br/>
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;<br/>
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;<br/>
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;<br/>
ALTER TABLE entities_in_aggregator ADD CONSTRAINT FK_2BD88468727ACA70 FOREIGN KEY (parent_id) REFERENCES entities (id);<br/>
ALTER TABLE entities_in_aggregator ADD CONSTRAINT FK_2BD88468DD62C21B FOREIGN KEY (child_id) REFERENCES entities (id);<br/>
ALTER TABLE properties ADD CONSTRAINT FK_87C331C781257D5D FOREIGN KEY (entity_id) REFERENCES entities (id);<br/>
ALTER TABLE data ADD CONSTRAINT FK_ADF3F36372F5A1AA FOREIGN KEY (channel_id) REFERENCES entities (id);<br/>
ALTER TABLE aggregate ADD CONSTRAINT FK_B77949FF72F5A1AA FOREIGN KEY (channel_id) REFERENCES entities (id);</div>
<div> </div>
<div>9.</div>
<div> </div>
<div>
<div>pi@raspberrypi:~ $ sudo volkszaehler.org/bin/doctrine orm:schema-tool:update --force</div>
<div> Updating database schema...</div>
<div> 10 queries were executed</div>
<div> <br/>
[OK] Database schema updated successfully! </div>
</div>
</div>
</div>
<div> </div>
<div> </div>
</div>
<div>10. Nun das Problem:</div>
<div> </div>
<div>
<div>pi@raspberrypi:~ $ sudo /var/www/volkszaehler.org/vendor/bin/dbcopy copy -c /etc/dbcopy.yaml<br/>
Dropping FK FK_87C331C781257D5D on properties<br/>
Dropping FK FK_2BD88468727ACA70 on entities_in_aggregator<br/>
Dropping FK FK_2BD88468DD62C21B on entities_in_aggregator<br/>
Dropping FK FK_ADF3F36372F5A1AA on data<br/>
Dropping FK FK_B77949FF72F5A1AA on aggregate<br/>
entities: copying 0 rows (overwrite)<br/>
0 [->--------------------------] < 1 sec 6.0 MiB</div>
<div>properties: copying 0 rows (overwrite)<br/>
0 [->--------------------------] < 1 sec 6.0 MiB</div>
<div>entities_in_aggregator: copying 0 rows (overwrite)<br/>
0 [->--------------------------] < 1 sec 6.0 MiB</div>
<div>data: copying 0 rows (partial copy)<br/>
0 [->--------------------------] < 1 sec 6.0 MiB</div>
<div>aggregate: skipping<br/>
Creating FK FK_B77949FF72F5A1AA on aggregate</div>
<div> </div>
<div>Auf meinem laufenden RPi3 dauert ein DB-Restore von ca. 3.2 GB ca. fünf Stunden. Hier scheint etwas nicht zu klappen.</div>
<div>Kann mir jemand evtl sagen, was ich falsch mache ?</div>
<div>Grüße</div>
<div> </div>
<div>JD.</div>
</div>
</div></div></body></html>