andig / dbcopy Goto Github PK

Hi Everyone,

I retrieve the following error, when running dbcopy from volkszaehler docker container:

`/vz/vendor/bin # php ./dbcopy create -c /vz/etc/dbcopy.nas.yaml
Creating target schema
Creating tables
/vz/vendor/bin # php ./dbcopy copy -c /vz/etc/dbcopy.nas.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

Fatal error: Uncaught TypeError: join(): Argument #2 ($array) must be of type ?array, string given in /vz/vendor/andig/dbcopy/src/Command/CopyCommand.php:90
Stack trace:
#0 /vz/vendor/andig/dbcopy/src/Command/CopyCommand.php(90): join(Array, ',')
#1 /vz/vendor/andig/dbcopy/src/Command/CopyCommand.php(223): DatabaseCopy\Command\CopyCommand->copyTable(Object(Doctrine\DBAL\Schema\Table), false)
#2 /vz/vendor/symfony/console/Command/Command.php(298): DatabaseCopy\Command\CopyCommand->execute(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#3 /vz/vendor/symfony/console/Application.php(1024): Symfony\Component\Console\Command\Command->run(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#4 /vz/vendor/symfony/console/Application.php(299): Symfony\Component\Console\Application->doRunCommand(Object(DatabaseCopy\Command\CopyCommand), Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#5 /vz/vendor/symfony/console/Application.php(171): Symfony\Component\Console\Application->doRun(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#6 /vz/vendor/andig/dbcopy/dbcopy(36): Symfony\Component\Console\Application->run()
#7 /vz/vendor/bin/dbcopy(120): include('/vz/vendor/andi...')
#8 {main}
thrown in /vz/vendor/andig/dbcopy/src/Command/CopyCommand.php on line 90
`

As far as I understood, the syntax from join changed from

join(Array, ',')

to

join(',', Array).

Can anyone confirm?

BTW.: My docker containers uses PHP 8.1.9
My previous - working - pi installation PHP 7.3.31

Thanks in advance / Danke im Voraus!

Not sure if it is something i did but when i run dbcopy copy using an entry for table date like "data: copy" (not pk or anything else) and option batch, -b, is 1000 or so in order to copy the entire data table to another mariadb server instance, i only get like 1000 rows copied out of the currently 3.2 million rows. Even if i leave out the -b option i only get 1000 rows copied into the new database on a remote machine.

Looking at the code of andig/dbcopy/src/Command/CopyCommand.php (the most up to date one delievered with volkszaehler.org) it tells me that for MODE_COPY copyTable is called with false for keyColumn and thus the condition in while is always false and the loop is left after iterating only 1 time. I changed it locally to:

while ( ($this->batch * $loopOffsetIndex) < $totalRows);

and it is at least slowly iterating over the 3 million rows... For sure i should have used a different method of copying the database contents but i wanted to try it this way.

The DB structure of VZ has been changed in omitting the auto generated, consecutive ID in favour of a combination of timestamp and channel_ID.

This is very welcome in terms of data efficiency.

Unfortunately dbcopy is not supporting this and instead looking for the old ID as the single primary key. So "pk option" fails on DB backups. Further on "copy option" only executes the first 1000 data sets.

dbcopy needs adaption to the new VZ DB structure.

[Doctrine\DBAL\Exception\DriverException]
An exception occurred while executing 'CREATE TABLE evento (idevento SERIAL NOT NULL, funcionario_codigo VARCHAR(20) DEFAULT '' NOT NULL COLLATE "utf8_general_ci", fecha T
IMESTAMP(0) WITHOUT TIME ZONE DEFAULT '0000-00-00 00:00:00', evento VARCHAR(255) DEFAULT 'ADICIONAR' NOT NULL COLLATE "utf8_general_ci", tabla_e VARCHAR(30) NOT NULL COLLA
TE "utf8_general_ci", estado CHAR(1) DEFAULT NULL COLLATE "utf8_general_ci", registro_id INT DEFAULT 0 NOT NULL, detalle TEXT NOT NULL COLLATE "utf8_general_ci", codigo_sq
l TEXT NOT NULL COLLATE "utf8_general_ci", PRIMARY KEY(idevento))':
SQLSTATE[42704]: Undefined object: 7 ERROR: no existe el ordenamiento (collation) «utf8_general_ci» para la codificación «UTF8»
LINE 1: ...uncionario_codigo VARCHAR(20) DEFAULT '' NOT NULL COLLATE "u...

Das create einer VZ-Datenbank von MySQL auf SQLite schlägt fehlt.
Siehe auch: http://www.photovoltaikforum.com/volkszaehler-org-f131/vz-parallel-auf-raspi-und-webhoster--t110985.html#p1353934

php /var/www/volkszaehler.org/vendor/andig/dbcopy/dbcopy.php create -c /etc/dbcopy.json
Creating target schema
Renaming assets for target platform: sqlite
table: aggregate
table: data
table: entities
table: entities_in_aggregator
table: properties
Creating tables
CREATE TABLE aggregate (id INTEGER NOT NULL, channel_id INTEGER NOT NULL, type BOOLEAN NOT NULL, timestamp BIGINT NOT NULL, value DOUBLE PRECISION NOT NULL, count INTEGER NOT NULL, PRIMARY KEY(id))
CREATE UNIQUE INDEX ts_uniq ON aggregate (channel_id, type, timestamp)
CREATE TABLE data (id INTEGER NOT NULL, channel_id INTEGER DEFAULT NULL, timestamp BIGINT NOT NULL, value DOUBLE PRECISION NOT NULL, PRIMARY KEY(id), CONSTRAINT FK_ADF3F36372F5A1AA FOREIGN KEY (channel_id) REFERENCES entities (id) NOT DEFERRABLE INITIALLY IMMEDIATE)
CREATE UNIQUE INDEX data_unique ON data (channel_id, timestamp)
CREATE INDEX IDX_ADF3F36372F5A1AA ON data (channel_id)
CREATE TABLE entities (id INTEGER NOT NULL, uuid VARCHAR(36) NOT NULL COLLATE utf8_unicode_ci, type VARCHAR(255) NOT NULL COLLATE utf8_unicode_ci, class VARCHAR(255) NOT NULL COLLATE utf8_unicode_ci, PRIMARY KEY(id))
CREATE UNIQUE INDEX UNIQ_50EC64E5D17F50A6 ON entities (uuid)
CREATE TABLE entities_in_aggregator (parent_id INTEGER NOT NULL, child_id INTEGER NOT NULL, PRIMARY KEY(parent_id, child_id), CONSTRAINT FK_2BD88468727ACA70 FOREIGN KEY (parent_id) REFERENCES entities (id) NOT DEFERRABLE INITIALLY IMMEDIATE, CONSTRAINT FK_2BD88468DD62C21B FOREIGN KEY (child_id) REFERENCES entities (id) NOT DEFERRABLE INITIALLY IMMEDIATE)
CREATE INDEX IDX_2BD88468727ACA70 ON entities_in_aggregator (parent_id)
CREATE INDEX IDX_2BD88468DD62C21B ON entities_in_aggregator (child_id)
CREATE TABLE properties (id INTEGER NOT NULL, entity_id INTEGER DEFAULT NULL, pkey VARCHAR(255) NOT NULL COLLATE utf8_unicode_ci, value CLOB NOT NULL COLLATE utf8_unicode_ci, PRIMARY KEY(id), CONSTRAINT FK_87C331C781257D5D FOREIGN KEY (entity_id) REFERENCES entities (id) NOT DEFERRABLE INITIALLY IMMEDIATE)
CREATE UNIQUE INDEX property_unique ON properties (entity_id, pkey)
CREATE INDEX IDX_87C331C781257D5D ON properties (entity_id)

[Doctrine\DBAL\Exception\DriverException]
An exception occurred while executing 'CREATE TABLE entities (id INTEGER NOT NULL, uuid VARCHAR(36) NOT NULL COLLATE utf8_unicode_ci, type VARCHAR(255
) NOT NULL COLLATE utf8_unicode_ci, class VARCHAR(255) NOT NULL COLLATE utf8_unicode_ci, PRIMARY KEY(id))':
SQLSTATE[HY000]: General error: 1 no such collation sequence: utf8_unicode_ci

[Doctrine\DBAL\Driver\PDOException]
SQLSTATE[HY000]: General error: 1 no such collation sequence: utf8_unicode_ci

[PDOException]
SQLSTATE[HY000]: General error: 1 no such collation sequence: utf8_unicode_ci