93_DbRep.pm: Umstellung von SQLite auf MySQL/MariaDB mit Export / Import

[markus1407]

Hallo,

ich möchte meine Datenbank von SQLite auf MySQL/MariaDB umstellen und die Daten "mitnehmen".

Folgender Blog Beitrag hat mir geholfen:
https://mayach.de/fhem-umzug-sqlite-mysql-mariadb/


Dabei habe ich zwei Probleme in DbRep entdeckt:
Version: 93_DbRep.pm              25344 2021-12-14 15:06:47Z DS_Starter

======================================================================================
1) set DbRepMySQL index recreate_Report_Idx

DbRep DbRepMySQL - ################################################################
DbRep DbRepMySQL - ###                    New Index operation                   ###
DbRep DbRepMySQL - ################################################################
DbRep DbRepMySQL - execute command before index: 'set DbLogMySQL reopen 3600'
DbLog DbLogMySQL: Connection closed until 23:29:56 (3600 seconds).
DbRep DbRepMySQL - Command: index recreate_Report_Idx
DbRep DbRepMySQL - user "fhempiUser" doesn't have rights "INDEX" and "ALTER" as needed - try use adminCredentials automatically !
DbRep DbRepMySQL - ERROR - adminCredentials not set. Use "set DbRepMySQL adminCredentials" first.
DbRep DbRepMySQL - ERROR - admin credentials are needed for database operation, but are not set or can't read it
DbLog DbLogMySQL: Reopen requested.


Wenn ich mich mit phpMyAdmin als "fhempiUser" einlogge dann erhalte ich:

SHOW GRANTS;

GRANT FILE ON *.* TO `fhempiUser`@`%` IDENTIFIED BY PASSWORD '*xxxxxx'
GRANT SELECT, INSERT, UPDATE, DELETE, INDEX, ALTER, CREATE TEMPORARY TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `fhempiDB`.* TO `fhempiUser`@`%`


Das sollten alle erforderlichen Rechte sein.
Den Index kann ich "von Hand" erstellen mit:

ALTER TABLE `history` DROP INDEX `Report_Idx`;
ALTER TABLE `history` ADD INDEX `Report_Idx` (TIMESTAMP, READING) USING BTREE;


Das erstellen des Index in phpMyAdmin war für mich der Workarround.
Was die Ursache für das Problem ist konnte ich noch nicht heraus finden.


======================================================================================
2) Daten Import mit

"set DbRepMySQL importFromFile"
"attr expimpfile /opt/fhem/db_export.csv"


Erzeugt folgenden Fehler im Log:

DbRep DbRepMySQL - get initial structure information of database "fhempiDB", remaining attempts: 3
DbRep DbRepMySQL - Connectiontest to database mysql:database=fhempiDB;host=192.168.1.11;port=3336 with user fhempiUser
PERL WARNING: Use of uninitialized value $mints in concatenation (.) or string at ./FHEM/93_DbRep.pm line 1725.
PERL WARNING: Use of uninitialized value $mints in pattern match (m//) at ./FHEM/93_DbRep.pm line 1727.
DbRep DbRepMySQL - Index Report_Idx exists. Check ok
PERL WARNING: Use of uninitialized value in subroutine entry at ./FHEM/93_DbRep.pm line 1826.
DbRep DbRepMySQL - Initial data information retrieved - total time used: 0.0593 seconds


Der Grund ist vermutlich hier:

  # ältesten Datensatz der DB ermitteln
  ######################################
  my $mints     = qq{undefined - $mintsdef is used instead};
  eval { $mints = $dbh->selectrow_array("SELECT min(TIMESTAMP) FROM history;") };
 
  Log3 ($name, 4, "DbRep $name - Oldest timestamp determined: $mints");
 
  $mints = $mints =~ /undefined/x ? $mintsdef : $mints;



Bei mir ist die Tabelle history noch komplett leer.
-> das macht hier vermutlich Problme.
eval { $mints = $dbh->selectrow_array("SELECT min(TIMESTAMP) FROM history;") };

Als Bugfix könnte man zunächst prüfen, ob die Tabelle leer ist und erst dann den TIMESTAMP holen.

Mein Workarround, war jetzt die Zeile zu entfernen und damit immer $mintsdef zu verwenden.


Folgender Fehler erscheint immer noch im Log:
PERL WARNING: Use of uninitialized value $rsf in substitution (s///) at ./FHEM/93_DbRep.pm line 6384.
eval: {DbRep_getInitDataDone('DbRepMySQL|MTk3MC0wMS0wMSAwMTowMDowMA==|0.006638,0.011828|0|importFromFile|/opt/fhem/db_export.csv|DbRep_Main|SW5kZXggUmVwb3J0X0lkeCBleGlzdHM=|Q1JFQVRFIFRFTVBPUkFSWSBUQUJMRVMsQUxURVIsVVBEQVRFLFNFTEVDVCxFVkVOVCxFWEVDVVRFLENSRUFURSBST1VUSU5FLFNIT1cgVklFVyxBTFRFUiBST1VUSU5FLERFTEVURSxJTlNFUlQsVFJJR0dFUixJTkRFWCxGSUxFLENSRUFURSBWSUVX|VVRGOE1CMw==')}

Hat das evtl. auch was mit der leeren Datenbank zu tun ?


======================================================================================

Erst nach dem Import habe ich das Logging in die neue MySQL Datenbank aktiviert.


Schöne Grüße.

[DS_Starter]

Hallo Markus,

zunächst freut es mich, dass das Modul hilfreich für deinen use case war und danke für deine Hinweise.

Ich bin den Sachen mal nachgegangen.

1) set DbRepMySQL index recreate_Report_Idx

Die Index Befehle funktionieren auf meiner DB einwandfrei. Log:

Code Auswählen Erweitern

2021.12.26 09:07:42.756 3: DbRep Rep.LogMariaVM.Test - ################################################################
2021.12.26 09:07:42.757 3: DbRep Rep.LogMariaVM.Test - ###                    New Index operation                   ###
2021.12.26 09:07:42.758 3: DbRep Rep.LogMariaVM.Test - ################################################################
2021.12.26 09:07:42.758 3: DbRep Rep.LogMariaVM.Test - Command: index recreate_Report_Idx
2021.12.26 09:07:42.808 5: DbRep Rep.LogMariaVM.Test -> Start DbRep_Index
2021.12.26 09:07:42.810 4: DbRep Rep.LogMariaVM.Test - Database connect - user: fhemtest, UTF-8 option set: yes
2021.12.26 09:07:42.812 4: DbRep Rep.LogMariaVM.Test - List all indexes: SELECT TABLE_NAME,INDEX_NAME,COLUMN_NAME FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = 'fhemtest';
2021.12.26 09:07:42.813 3: DbRep Rep.LogMariaVM.Test - Index found in database:
Table: current, Idx: PRIMARY, Col: DEVICE, READING
Table: history, Idx: Report_Idx, Col: TIMESTAMP, READING
2021.12.26 09:07:42.814 4: DbRep Rep.LogMariaVM.Test - SQL execute: SHOW INDEX FROM history where Key_name='Report_Idx'; ALTER TABLE `history` DROP INDEX `Report_Idx`; ALTER TABLE `history` ADD INDEX `Report_Idx` (TIMESTAMP, READING) USING BTREE;
2021.12.26 09:07:42.819 3: DbRep Rep.LogMariaVM.Test - dropping index Report_Idx ...
2021.12.26 09:07:42.829 3: DbRep Rep.LogMariaVM.Test - Index Report_Idx dropped
2021.12.26 09:07:42.830 3: DbRep Rep.LogMariaVM.Test - creating index Report_Idx ...
2021.12.26 09:07:42.850 3: DbRep Rep.LogMariaVM.Test - Index Report_Idx created
2021.12.26 09:07:42.851 5: DbRep Rep.LogMariaVM.Test -> DbRep_Index finished

Die Rechte werden beim ersten Start eines DbRep-Devices ermittelt und gespeichert.
Möglicherweise hast du nach dem ersten Lauf die fehlenden Rechte bemerkt und den grant ausgeführt, danach aber nicht
FHEM restartet bzw. ein "get ... initData" um die Rechte neu einzulesen. Konntest du ja auch nicht wissen.

Ein "get ... initData" holt die Rechte:

Code Auswählen Erweitern

   READINGS:
     2021-12-26 09:08:56   background_processing_time 0.0125
     2021-12-26 09:08:56   dbEncoding      UTF8
     2021-12-26 09:08:56   indexState      Index Report_Idx exists
     2021-12-26 09:08:56   sql_processing_time 0.0070
     2021-12-26 09:08:56   state           done
     2021-12-26 09:08:56   timestamp_oldest_dataset 2021-12-26 08:00:00
     2021-12-26 09:08:56   userRights      ALTER,SELECT,DELETE,SHOW VIEW,FILE,PROCESS,INDEX,UPDATE,INSERT


Ich schaue mal ob ich die Hilfe erweitere oder noch besser vor den Index-Operationen die Rechte immer neu einlese was
mein Favorit ist.

Bei mir ist die Tabelle history noch komplett leer.
-> das macht hier vermutlich Problme.
eval { $mints = $dbh->selectrow_array("SELECT min(TIMESTAMP) FROM history;") };

Als Bugfix könnte man zunächst prüfen, ob die Tabelle leer ist und erst dann den TIMESTAMP holen.

Da hast du recht.

Ich schaue mir die Punkte und auch die Warnungen mal in Ruhe an und melde mich wieder.

Grüße,
Heiko

[DS_Starter]

Ich habe die angesprochenen Punkte abgearbeitet und die neue DbRep Version eingecheckt.
Sie ist morgen früh im Update enthalten.

Unter anderem ist es mit dem Attribut useAdminCredentials = 1 möglich die sqlCmd-Kommandos mit einem
privilegierten User auszuführen. Dadurch kann man z.B. grant-Änderungen des normalen DB-Users mit sqlCmd ausführen.