Hallo Michael,
Um die Datensätze, die den Fehler verursachen, einzugrenzen öffne bitte
mysql als root und gebe folgende Befehle ein:
$ sudo mysql
> use volkszaehler;
Dann kopiere dieses Statement hier ein:
--- schnipp ---
SELECT channel_id
, 1 AS type
, MAX(agg.timestamp) AS timestamp
, SUM(agg.val_by_time) as s
, MAX(agg.timestamp) as t_max
, MIN(agg.prev_timestamp) as t_min
, MAX(agg.timestamp) - MIN(agg.prev_timestamp) as delta
, COUNT(agg.value) AS count
FROM ( SELECT channel_id
, timestamp
, value
, value * (timestamp - @prev_timestamp) AS val_by_time
, COALESCE(@prev_timestamp, 0) AS prev_timestamp
, @prev_timestamp := timestamp
FROM data CROSS JOIN (SELECT @prev_timestamp :=
UNIX_TIMESTAMP(DATE_ADD(FROM_UNIXTIME(MAX(timestamp) / 1000, "%Y-%m-%d
%H:%i:00"), INTERVAL 1 minute)) * 1000
FROM aggregate
WHERE type = 1
AND aggregate.channel_id = "3") AS
vars
WHERE channel_id = "3"
AND timestamp >= IFNULL((SELECT
UNIX_TIMESTAMP(DATE_ADD(FROM_UNIXTIME(MAX(timestamp) / 1000, "%Y-%m-%d
%H:%i:00"), INTERVAL 1 minute)) * 1000
FROM aggregate
WHERE type = 1
AND aggregate.channel_id = "3" ), 0)
AND timestamp < UNIX_TIMESTAMP(DATE_FORMAT(NOW(), "%Y-%m-%d
%H:%i:00")) * 1000 ) AS agg
GROUP BY channel_id
, YEAR(FROM_UNIXTIME(timestamp/1000))
, DAYOFYEAR(FROM_UNIXTIME(timestamp/1000))
, HOUR(FROM_UNIXTIME(timestamp/1000))
, MINUTE(FROM_UNIXTIME(timestamp/1000))
ORDER BY 1,2,3,4,5
INTO OUTFILE '/var/lib/mysql/offending.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n' ;
--- schnapp ---
Wenn Du uns die Werte nicht verraten möchtest, kannst Du "value" am Anfang
durch "0" ersetzen.
Zeige uns bitte die Tabelle aus /var/lib/mysql/offending.csv!
Dort musst Du dann die Zeile finden, die in der vorletzten Spalte eine "0"
hat.
In den Zeilen davor und danach findest Du die timestamps, die Du im
folgenden Statement für ****MIN**** und ****MAX**** einsetzen musst:
--- schnipp ---
SELECT channel_id
, timestamp
, value
, value * (timestamp - @prev_timestamp) AS val_by_time
, COALESCE(@prev_timestamp, 0) AS prev_timestamp
, @prev_timestamp := timestamp
FROM data CROSS JOIN (SELECT @prev_timestamp :=
UNIX_TIMESTAMP(DATE_ADD(FROM_UNIXTIME(MAX(timestamp) / 1000, "%Y-%m-%d
%H:%i:00"), INTERVAL 1 minute)) * 1000
FROM aggregate
WHERE type = 1
AND aggregate.channel_id = "3") AS vars
WHERE channel_id = "3"
AND timestamp >= ****MIN****
AND timestamp < ****MAX****
INTO OUTFILE '/var/lib/mysql/rawdata.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n' ;
--- schnapp ---
Wenn Du uns die Werte nicht verraten möchtest, kannst Du die zwei "value"
am Anfang durch "0" ersetzen.
Auch diese Datei solltest Du anhängen.
