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:
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):
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
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."
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
Danke, werd ich testen.
Und nur keine Eile mit dem anderen Thema. :-)
EDIT: Läuft! Danke für den Tipp!