<div dir="ltr"><div dir="ltr"><br></div><div>Hallo Michael,</div><div><br></div><div>Um die Datensätze, die den Fehler verursachen, einzugrenzen öffne bitte mysql als root und gebe folgende Befehle ein: </div><div><br></div><div>$ sudo mysql</div><div>> use volkszaehler; </div><div><br></div><div>Dann kopiere dieses Statement hier ein:</div><div><br></div><div>--- schnipp ---</div><div><div style="color:rgb(212,212,212);background-color:rgb(30,30,30);font-family:Consolas,"Courier New",monospace;font-size:14px;line-height:19px;white-space:pre"><div> <span style="color:rgb(86,156,214)">SELECT</span> channel_id</div><div> , <span style="color:rgb(181,206,168)">1</span> <span style="color:rgb(86,156,214)">AS</span> <span style="color:rgb(86,156,214)">type</span></div><div> , <span style="color:rgb(220,220,170)">MAX</span>(agg.timestamp) <span style="color:rgb(86,156,214)">AS</span> <span style="color:rgb(86,156,214)">timestamp</span></div><div> , <span style="color:rgb(220,220,170)">SUM</span>(agg.val_by_time) <span style="color:rgb(86,156,214)">as</span> s </div><div> , <span style="color:rgb(220,220,170)">MAX</span>(agg.timestamp) <span style="color:rgb(86,156,214)">as</span> t_max </div><div> , <span style="color:rgb(220,220,170)">MIN</span>(agg.prev_timestamp) <span style="color:rgb(86,156,214)">as</span> t_min</div><div> , <span style="color:rgb(220,220,170)">MAX</span>(agg.timestamp) - <span style="color:rgb(220,220,170)">MIN</span>(agg.prev_timestamp) <span style="color:rgb(86,156,214)">as</span> delta</div><div> , <span style="color:rgb(220,220,170)">COUNT</span>(agg.value) <span style="color:rgb(86,156,214)">AS</span> count </div><div> <span style="color:rgb(86,156,214)">FROM</span> ( <span style="color:rgb(86,156,214)">SELECT</span> channel_id</div><div> , <span style="color:rgb(86,156,214)">timestamp</span></div><div> , <span style="color:rgb(86,156,214)">value</span></div><div> , <span style="color:rgb(86,156,214)">value</span> * (<span style="color:rgb(86,156,214)">timestamp</span> - @prev_timestamp) <span style="color:rgb(86,156,214)">AS</span> val_by_time</div><div> , <span style="color:rgb(220,220,170)">COALESCE</span>(@prev_timestamp, <span style="color:rgb(181,206,168)">0</span>) <span style="color:rgb(86,156,214)">AS</span> prev_timestamp</div><div> , @prev_timestamp := <span style="color:rgb(86,156,214)">timestamp</span> </div><div> <span style="color:rgb(86,156,214)">FROM</span> <span style="color:rgb(86,156,214)">data</span> <span style="color:rgb(86,156,214)">CROSS JOIN</span> (<span style="color:rgb(86,156,214)">SELECT</span> @prev_timestamp := UNIX_TIMESTAMP(DATE_ADD(FROM_UNIXTIME(<span style="color:rgb(220,220,170)">MAX</span>(<span style="color:rgb(86,156,214)">timestamp</span>) / <span style="color:rgb(181,206,168)">1000</span>, <span style="color:rgb(206,145,120)">"%Y-%m-%d %H:%i:00"</span>), INTERVAL <span style="color:rgb(181,206,168)">1</span> <span style="color:rgb(86,156,214)">minute</span>)) * <span style="color:rgb(181,206,168)">1000</span> </div><div> <span style="color:rgb(86,156,214)">FROM</span> <span style="color:rgb(86,156,214)">aggregate</span> </div><div> <span style="color:rgb(86,156,214)">WHERE</span> <span style="color:rgb(86,156,214)">type</span> = <span style="color:rgb(181,206,168)">1</span> </div><div> <span style="color:rgb(86,156,214)">AND</span> aggregate.channel_id = <span style="color:rgb(206,145,120)">"3"</span>) <span style="color:rgb(86,156,214)">AS</span> vars </div><div> <span style="color:rgb(86,156,214)">WHERE</span> channel_id = <span style="color:rgb(206,145,120)">"3"</span></div><div> <span style="color:rgb(86,156,214)">AND</span> <span style="color:rgb(86,156,214)">timestamp</span> >= <span style="color:rgb(86,156,214)">IFNULL</span>((<span style="color:rgb(86,156,214)">SELECT</span> UNIX_TIMESTAMP(DATE_ADD(FROM_UNIXTIME(<span style="color:rgb(220,220,170)">MAX</span>(<span style="color:rgb(86,156,214)">timestamp</span>) / <span style="color:rgb(181,206,168)">1000</span>, <span style="color:rgb(206,145,120)">"%Y-%m-%d %H:%i:00"</span>), INTERVAL <span style="color:rgb(181,206,168)">1</span> <span style="color:rgb(86,156,214)">minute</span>)) * <span style="color:rgb(181,206,168)">1000</span> </div><div> <span style="color:rgb(86,156,214)">FROM</span> <span style="color:rgb(86,156,214)">aggregate</span> </div><div> <span style="color:rgb(86,156,214)">WHERE</span> <span style="color:rgb(86,156,214)">type</span> = <span style="color:rgb(181,206,168)">1</span> </div><div> <span style="color:rgb(86,156,214)">AND</span> aggregate.channel_id = <span style="color:rgb(206,145,120)">"3"</span> ), <span style="color:rgb(181,206,168)">0</span>) </div><div> <span style="color:rgb(86,156,214)">AND</span> <span style="color:rgb(86,156,214)">timestamp</span> < UNIX_TIMESTAMP(<span style="color:rgb(86,156,214)">DATE_FORMAT</span>(<span style="color:rgb(86,156,214)">NOW</span>(), <span style="color:rgb(206,145,120)">"%Y-%m-%d %H:%i:00"</span>)) * <span style="color:rgb(181,206,168)">1000</span> ) <span style="color:rgb(86,156,214)">AS</span> agg </div><div> <span style="color:rgb(86,156,214)">GROUP BY</span> channel_id</div><div> , <span style="color:rgb(220,220,170)">YEAR</span>(FROM_UNIXTIME(<span style="color:rgb(86,156,214)">timestamp</span>/<span style="color:rgb(181,206,168)">1000</span>))</div><div> , DAYOFYEAR(FROM_UNIXTIME(<span style="color:rgb(86,156,214)">timestamp</span>/<span style="color:rgb(181,206,168)">1000</span>))</div><div> , <span style="color:rgb(86,156,214)">HOUR</span>(FROM_UNIXTIME(<span style="color:rgb(86,156,214)">timestamp</span>/<span style="color:rgb(181,206,168)">1000</span>))</div><div> , <span style="color:rgb(86,156,214)">MINUTE</span>(FROM_UNIXTIME(<span style="color:rgb(86,156,214)">timestamp</span>/<span style="color:rgb(181,206,168)">1000</span>))</div><div> ORDER BY 1,2,3,4,5</div><div> <span style="color:rgb(86,156,214)">INTO</span> OUTFILE <span style="color:rgb(206,145,120)">'/var/lib/mysql/offending.csv'</span></div><div> FIELDS TERMINATED <span style="color:rgb(86,156,214)">BY</span> <span style="color:rgb(206,145,120)">','</span></div><div> ENCLOSED <span style="color:rgb(86,156,214)">BY</span> <span style="color:rgb(206,145,120)">'"'</span></div><div> LINES TERMINATED <span style="color:rgb(86,156,214)">BY</span> <span style="color:rgb(206,145,120)">'\n'</span> ;</div><div></div></div></div><div>--- schnapp ---</div>Wenn Du uns die Werte nicht verraten möchtest, kannst Du "value" am Anfang durch "0" ersetzen.<div><br><div>Zeige uns bitte die Tabelle aus /var/lib/mysql/offending.csv! </div><div><br></div><div>Dort musst Du dann die Zeile finden, die in der vorletzten Spalte eine "0" hat. </div><div><br></div><div>In den Zeilen davor und danach findest Du die timestamps, die Du im folgenden Statement für ****MIN**** und ****MAX**** einsetzen musst:</div><div>--- schnipp ---<br></div><div style="color:rgb(212,212,212);background-color:rgb(30,30,30);font-family:Consolas,"Courier New",monospace;font-size:14px;line-height:19px;white-space:pre">
<div style="line-height:19px"><div><span style="color:rgb(86,156,214)">SELECT</span> channel_id</div><div> , <span style="color:rgb(86,156,214)">timestamp</span></div><div> , <span style="color:rgb(86,156,214)">value</span></div><div> , <span style="color:rgb(86,156,214)">value</span> * (<span style="color:rgb(86,156,214)">timestamp</span> - @prev_timestamp) <span style="color:rgb(86,156,214)">AS</span> val_by_time</div><div> , <span style="color:rgb(220,220,170)">COALESCE</span>(@prev_timestamp, <span style="color:rgb(181,206,168)">0</span>) <span style="color:rgb(86,156,214)">AS</span> prev_timestamp</div><div> , @prev_timestamp := <span style="color:rgb(86,156,214)">timestamp</span> </div><div> <span style="color:rgb(86,156,214)">FROM</span> <span style="color:rgb(86,156,214)">data</span> <span style="color:rgb(86,156,214)">CROSS JOIN</span> (<span style="color:rgb(86,156,214)">SELECT</span> @prev_timestamp := UNIX_TIMESTAMP(DATE_ADD(FROM_UNIXTIME(<span style="color:rgb(220,220,170)">MAX</span>(<span style="color:rgb(86,156,214)">timestamp</span>) / <span style="color:rgb(181,206,168)">1000</span>, <span style="color:rgb(206,145,120)">"%Y-%m-%d %H:%i:00"</span>), INTERVAL <span style="color:rgb(181,206,168)">1</span> <span style="color:rgb(86,156,214)">minute</span>)) * <span style="color:rgb(181,206,168)">1000</span> </div><div> <span style="color:rgb(86,156,214)">FROM</span> <span style="color:rgb(86,156,214)">aggregate</span> </div><div> <span style="color:rgb(86,156,214)">WHERE</span> <span style="color:rgb(86,156,214)">type</span> = <span style="color:rgb(181,206,168)">1</span> </div><div> <span style="color:rgb(86,156,214)">AND</span> aggregate.channel_id = <span style="color:rgb(206,145,120)">"3"</span>) <span style="color:rgb(86,156,214)">AS</span> vars </div><div><span style="color:rgb(86,156,214)">WHERE</span> channel_id = <span style="color:rgb(206,145,120)">"3"</span></div><div><span style="color:rgb(86,156,214)">AND</span> <span style="color:rgb(86,156,214)">timestamp</span> >= ****MIN****</div><div><span style="color:rgb(86,156,214)">AND</span> <span style="color:rgb(86,156,214)">timestamp</span> < ****MAX****</div><div> <span style="color:rgb(86,156,214)">INTO</span> OUTFILE <span style="color:rgb(206,145,120)">'/var/lib/mysql/rawdata.csv'</span></div><div> FIELDS TERMINATED <span style="color:rgb(86,156,214)">BY</span> <span style="color:rgb(206,145,120)">','</span></div><div> ENCLOSED <span style="color:rgb(86,156,214)">BY</span> <span style="color:rgb(206,145,120)">'"'</span></div><div> LINES TERMINATED <span style="color:rgb(86,156,214)">BY</span> <span style="color:rgb(206,145,120)">'\n'</span> ;</div></div><div><span style="background-color:rgb(255,255,255);font-family:Arial,Helvetica,sans-serif;font-size:small;color:rgb(34,34,34)">--- schnapp --- </span><br></div><div>
<span style="color:rgb(34,34,34);font-family:Arial,Helvetica,sans-serif;font-size:small;white-space:normal;background-color:rgb(255,255,255)">Wenn Du uns die Werte nicht verraten möchtest, kannst Du die zwei "value" am Anfang durch "0" ersetzen.</span><br class="gmail-Apple-interchange-newline">
</div><div><span style="background-color:rgb(255,255,255);font-family:Arial,Helvetica,sans-serif;font-size:small;color:rgb(34,34,34)">Auch diese Datei solltest Du anhängen.</span></div><div><span style="background-color:rgb(255,255,255);font-family:Arial,Helvetica,sans-serif;font-size:small;color:rgb(34,34,34)"><br></span></div></div></div></div>