[gelöst] - dbRep - out of memory bei exporttofile

[Frank_Huber]

EDIT: Gelöst mit Attribut "aggregation"

Hello again,

Beim versuch alles was älter als 2 Jahre ist zu exportieren gibt es einen Error, 2.5 Jahre funktioniert.

Fehler im Log:

Code Auswählen Erweitern

2019.10.22 15:21:48 3: DbRep logdb_clean_2years - execute command before export: 'set logdb reopen 3600'
Out of memory (Needed 1730768 bytes)
2019.10.22 15:22:29 2: DbRep logdb_clean_2years - DBD::mysql::st execute failed: MySQL client ran out of memory at ./FHEM/93_DbRep.pm line 5520.

2019.10.22 15:22:29 2: DbRep logdb_clean_2years - command message after export: "Reopen executed."

dbRep Definition (list):

Code Auswählen Erweitern

Internals:
   .FhemMetaInternals 1
   CFGFN     
   DATABASE   fhem
   DEF        logdb
   FUUID      5daefcec-f33f-c13c-ced9-72835eda6cdcf688
   FVERSION   93_DbRep.pm:v8.28.2-s20379/2019-10-18
   LASTCMD    exportToFile
   MODEL      Client
   NAME       logdb_clean_2years
   NOTIFYDEV  global,logdb_clean_2years
   NR         56771
   NTFY_ORDER 50-logdb_clean_2years
   ROLE       Client
   STATE      error
   TYPE       DbRep
   UTF8       1
   .attraggr:
   .attrminint:
   HELPER:
     DBLOGDEVICE logdb
     IDRETRIES  3
     MINTS      2016-11-16 13:14:45
     PACKAGE    main
     SQLHIST   
     VERSION    8.28.2
     CV:
       aggregation no
       aggsec     1
       destr      2017-10-22
       dsstr      2016-11-16
       epoch_seconds_end 1508678507.6391
       mestr      10
       msstr      11
       testr      15:21:47
       tsstr      13:14:45
       wdadd      432000
       yestr      2017
       ysstr      2016
     DBREPCOL:
       COLSET     1
       DEVICE     64
       EVENT      512
       READING    64
       TYPE       64
       UNIT       32
       VALUE      128
   Helper:
     DBLOG:
       state:
         logdb:
           TIME       1571749100.59887
           VALUE      initialized
   OLDREADINGS:
   READINGS:
     2019-10-22 15:22:29   afterexport_message Reopen executed.
     2019-10-22 15:22:29   errortext       DBD::mysql::st execute failed: MySQL client ran out of memory at ./FHEM/93_DbRep.pm line 5520.

     2019-10-22 15:22:29   state           error
Attributes:
   DbLogExclude .*
   allowDeletion 1
   comment    alles älter als 2 Jahre
exportieren --> set exporttofile
löschen --> set delEntries
   device     EXCLUDE=Verbrauch,Energiekosten
   executeAfterProc set logdb reopen
   executeBeforeProc set logdb reopen 3600
   expimpfile /Q/backup/rpi/MySQL/older2y_%Y-%m-%d.csv
   group      DB cleanup
   room       SYSTEM
   timeOlderThan y:2

Hab ich hier einen Fehler?
FHEM läuft in diesem Fall auf einen PI3, die mySQL extern auf einem Windows Server 2012

Muss ich hier erstmal Schrittweise an die 2 Yahre dran weil das zuviel wird? also mit 3y anfangen und dann 2y 10m, 2y 8m usw?


EDIT: Verbose 5 Log

Code Auswählen Erweitern

2019.10.22 15:29:39 4: DbRep logdb_clean_2years - -------- New selection ---------
2019.10.22 15:29:39 4: DbRep logdb_clean_2years - Command: exportToFile /Q/backup/rpi/MySQL/older2y_%Y-%m-%d.csv
2019.10.22 15:29:39 4: DbRep logdb_clean_2years - timeOlderThan - year: 2, day: , hour: , min: , sec: 
2019.10.22 15:29:39 4: DbRep logdb_clean_2years - startMonth: 10 endMonth: 9 lastleapyear:  baseYear: 2017 diffdaylight:1 isdaylight:1
2019.10.22 15:29:39 5: DbRep logdb_clean_2years - Timestamp begin epocheseconds: 1479298485
2019.10.22 15:29:39 4: DbRep logdb_clean_2years - Timestamp begin human readable: 2016-11-16 13:14:45
2019.10.22 15:29:39 4: DbRep logdb_clean_2years - Time difference to current time for calculating Timestamp end: 63072001 sec
2019.10.22 15:29:39 5: DbRep logdb_clean_2years - Timestamp end epocheseconds: 1508678978.2155
2019.10.22 15:29:39 4: DbRep logdb_clean_2years - Timestamp end human readable: 2017-10-22 15:29:38
2019.10.22 15:29:39 5: DbRep logdb_clean_2years - weekday of start for selection: Mi  ->  wdadd: 432000
2019.10.22 15:29:39 4: DbRep logdb_clean_2years - Aggregation: no
2019.10.22 15:29:39 3: DbRep logdb_clean_2years - execute command before export: 'set logdb reopen 3600'
2019.10.22 15:29:39 4: DbRep logdb_clean_2years - Export data to file: /Q/backup/rpi/MySQL/older2y_2019-10-22.csv
2019.10.22 15:29:39 5: DbRep logdb_clean_2years - IsTimeSet: 1, IsAggrSet: 0
2019.10.22 15:29:39 5: DbRep logdb_clean_2years - Timestamp-Array:
no_aggregation#2016-11-16 13:14:45#2017-10-22 15:29:38
2019.10.22 15:29:39 5: DbRep logdb_clean_2years - Devices for operation -
included (1): %
included with wildcard: 
excluded (2): Verbrauch,Energiekosten
excluded with wildcard:
2019.10.22 15:29:39 5: DbRep logdb_clean_2years - Readings for operation -
included (1): %
included with wildcard: 
excluded (0): 
excluded with wildcard:
2019.10.22 15:29:39 4: DbRep logdb_clean_2years - SQL execute: SELECT TIMESTAMP,DEVICE,TYPE,EVENT,READING,VALUE,UNIT FROM history where DEVICE NOT IN ('Verbrauch','Energiekosten') AND TIMESTAMP >= '2016-11-16 13:14:45' AND TIMESTAMP <= '2017-10-22 15:29:38' ORDER BY TIMESTAMP;
Out of memory (Needed 1730768 bytes)
2019.10.22 15:30:19 2: DbRep logdb_clean_2years - DBD::mysql::st execute failed: MySQL client ran out of memory at ./FHEM/93_DbRep.pm line 5520.

2019.10.22 15:30:19 4: DbRep logdb_clean_2years - execute command after export: 'set logdb reopen'
2019.10.22 15:30:19 2: DbRep logdb_clean_2years - command message after export: "Reopen executed."

[DS_Starter]

Hi Frank,

aggregation = month setzen.

Dadurch werden die Daten kleiner paketiert. Hat keine Auswirkung auf das Ergebnis.
Die  andere Frage beantworte ich heute Abend. Das dauert länger.

Grüße,
Heiko

[Frank_Huber]

Danke, werd ich testen.
Und nur keine Eile mit dem anderen Thema.

EDIT: Läuft! Danke für den Tipp!