<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
<p>Hi Micha, <br>
</p>
<p>ich hab leider keine Ahnung, wie die Datenbank aussieht (ich
nutze selbst eine andere), aber der Fehler sagt aus, dass eine
Division durch 0 vorliegt. <br>
</p>
<p>Die einzige Zeile in der SQL Query, die mir da ins Auge sticht
ist diese hier:</p>
<p>> COALESCE( SUM(agg.val_by_time) / (MAX(agg.timestamp) -
MIN(agg.prev_timestamp)), <br>
</p>
<p>Das Maximum des Timestamps minus dem Minimum des vorherigen
Timestamps aus der (on the fly) erzeugten "agg" Tabelle sind
zusammen 0. Daher klappt die Division und damit die SQL Query
nicht. Die Daten stammen (soweit ich das sehen kann) aus der
"data" Tabelle. Vielleicht fällt dir ja da etwas auf in den Daten
bei den Timestamps. Den Rest überlasse ich den Experten, die das
Tool so im Einsatz haben ;)<br>
</p>
<p>Viel Erfolg,<br>
Christian <br>
</p>
<p><br>
</p>
<p><br>
</p>
<div class="moz-cite-prefix">Am 20.03.2023 um 10:37 schrieb Michael
Hartmann:<br>
</div>
<blockquote type="cite"
cite="mid:003601d95b0f$aa159b00$fe40d100$@web.de">
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<meta name="Generator" content="Microsoft Word 14 (filtered
medium)">
<style>@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-serif";
mso-fareast-language:EN-US;}a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}p.MsoPlainText, li.MsoPlainText, div.MsoPlainText
{mso-style-priority:99;
mso-style-link:"Nur Text Zchn";
margin:0cm;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-serif";
mso-fareast-language:EN-US;}span.E-MailFormatvorlage17
{mso-style-type:personal-compose;
font-family:"Calibri","sans-serif";
color:windowtext;}span.NurTextZchn
{mso-style-name:"Nur Text Zchn";
mso-style-priority:99;
mso-style-link:"Nur Text";
font-family:"Calibri","sans-serif";}.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri","sans-serif";
mso-fareast-language:EN-US;}div.WordSection1
{page:WordSection1;}</style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
<div class="WordSection1">
<p class="MsoNormal">Hallo,<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">ich hole das hier noch einmal vor, da es
ziemliche nervt.<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<div
style="mso-element:para-border-div;border:none;border-bottom:solid
windowtext 1.0pt;padding:0cm 0cm 1.0pt 0cm">
<p class="MsoNormal" style="border:none;padding:0cm">Via
cronjob lasse ich alle 10min eine Aggregation auf die Minute
laufen. Bereits vor einigen Wochen ist diese dann plötzlich
mit der folgenden Fehlermeldung ausgestiegen:<o:p></o:p></p>
<p class="MsoNormal" style="border:none;padding:0cm"><o:p> </o:p></p>
</div>
<p class="MsoPlainText"><span lang="EN-US">In
AbstractMySQLDriver.php line 128:<o:p></o:p></span></p>
<p class="MsoPlainText"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoPlainText"><span lang="EN-US"> An exception
occurred while executing 'REPLACE INTO aggregate
(channel_id,<o:p></o:p></span></p>
<p class="MsoPlainText"><span lang="EN-US"> type, timestamp,
value, count) SELECT channel_id, ? AS type, MAX(agg.timest<o:p></o:p></span></p>
<p class="MsoPlainText"><span lang="EN-US"> amp) AS timestamp,
COALESCE( SUM(agg.val_by_time) / (MAX(agg.timestamp) - M<o:p></o:p></span></p>
<p class="MsoPlainText"><span lang="EN-US">
IN(agg.prev_timestamp)), AVG(agg.value)) AS value,
COUNT(agg.value) AS coun<o:p></o:p></span></p>
<p class="MsoPlainText"><span lang="EN-US"> t FROM ( SELECT
channel_id, timestamp, value, value * (timestamp - @prev_ti<o:p></o:p></span></p>
<p class="MsoPlainText"><span lang="EN-US"> mestamp) AS
val_by_time, COALESCE(@prev_timestamp, 0) AS prev_timestamp,
@p<o:p></o:p></span></p>
<p class="MsoPlainText"><span lang="EN-US"> rev_timestamp :=
timestamp FROM data CROSS JOIN (SELECT @prev_timestamp :=<o:p></o:p></span></p>
<p class="MsoPlainText"><span lang="EN-US">
UNIX_TIMESTAMP(DATE_ADD(FROM_UNIXTIME(MAX(timestamp) / 1000,
"%Y-%m-%d %H:%<o:p></o:p></span></p>
<p class="MsoPlainText"><span lang="EN-US"> i:00"), INTERVAL 1
minute)) * 1000 FROM aggregate WHERE type = ? AND aggreg<o:p></o:p></span></p>
<p class="MsoPlainText"><span lang="EN-US"> ate.channel_id = ?)
AS vars WHERE channel_id = ? AND timestamp >= IFNULL((S<o:p></o:p></span></p>
<p class="MsoPlainText"><span lang="EN-US"> ELECT
UNIX_TIMESTAMP(DATE_ADD(FROM_UNIXTIME(MAX(timestamp) / 1000,
"%Y-%m-%<o:p></o:p></span></p>
<p class="MsoPlainText"><span lang="EN-US"> d %H:%i:00"),
INTERVAL 1 minute)) * 1000 FROM aggregate WHERE type = ? AND<o:p></o:p></span></p>
<p class="MsoPlainText"><span lang="EN-US">
aggregate.channel_id = ? ), 0) AND timestamp <
UNIX_TIMESTAMP(DATE_FORMAT(N<o:p></o:p></span></p>
<p class="MsoPlainText"><span lang="EN-US"> OW(), "%Y-%m-%d
%H:%i:00")) * 1000 ) AS agg GROUP BY channel_id, YEAR(FROM_<o:p></o:p></span></p>
<p class="MsoPlainText"><span lang="EN-US">
UNIXTIME(timestamp/1000)),
DAYOFYEAR(FROM_UNIXTIME(timestamp/1000)), HOUR(F<o:p></o:p></span></p>
<p class="MsoPlainText"><span lang="EN-US">
ROM_UNIXTIME(timestamp/1000)),
MINUTE(FROM_UNIXTIME(timestamp/1000))' with<o:p></o:p></span></p>
<p class="MsoPlainText"><span lang="EN-US"> params [1, 1, "3",
"3", 1, "3"]:<o:p></o:p></span></p>
<p class="MsoPlainText"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoPlainText"><span lang="EN-US"> SQLSTATE[22012]:
Division by zero: 1365 Division by 0<o:p></o:p></span></p>
<p class="MsoPlainText"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoPlainText"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoPlainText"><span lang="EN-US">In Exception.php line
18:<o:p></o:p></span></p>
<p class="MsoPlainText"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoPlainText"><span lang="EN-US"> SQLSTATE[22012]:
Division by zero: 1365 Division by 0<o:p></o:p></span></p>
<p class="MsoPlainText"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoPlainText"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoPlainText"><span lang="EN-US">In PDOStatement.php
line 117:<o:p></o:p></span></p>
<p class="MsoPlainText"><span lang="EN-US"><o:p> </o:p></span></p>
<div
style="mso-element:para-border-div;border:none;border-bottom:solid
windowtext 1.0pt;padding:0cm 0cm 1.0pt 0cm">
<p class="MsoPlainText" style="border:none;padding:0cm"><span
lang="EN-US"> SQLSTATE[22012]: Division by zero: 1365
Division by 0<o:p></o:p></span></p>
</div>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal">Die Aggregation auf Stunde und Tag bereitet
(bisher) keine Probleme.<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Beim letzten Mal hatte ich die
Aggregationstabelle gelöscht und neu aufgebaut. Nach einigen
Wochen kommt der Fehler nun wieder.<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Kann mir jemand erklären wo das Problem
liegt? Die Fehlermeldung kann ich nicht interpretieren.<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Viele Grüße<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Micha<o:p></o:p></p>
</div>
</blockquote>
</body>
</html>