lundi 27 avril 2015

Timestamps from SQLite get skipped

this is my first post here so I hope my way of my problem description is fine.

I experienced a problem with the Highcharts implementation of the realtime data.

In the Background a C++ Programm is running and generates all the data and puts them into a SQLite3 database which is stored locally. After about 100 entries the db gets emptied.

The php file opens a DB connection, grab this data from the local database, make some pre-processing (time to unix-timestamp,etc.) and returns it in Json format. With JavaScript this data gets rendered with Highcharts.

Now to the main Problem: The data gets correctly inserted in the DB but after some time (completely random) one timestamp (x-value) gets skipped, even though it is correctly displayed in the database. So it cannot be returned correctly to Highcharts. (x-values-->timestamps, y-values--> difference between two measured values)

Btw also in the live example of Highcharts (http://ift.tt/1EwrK4B) I saw that after some time one random timestamp (1second) gets skipped.

First I thought that it could be because of some timeouts but I changed and modified timeouts, without any positive effect. I also thought about that there is a problem with the milliseconds but the time only gets saved in the DB as "YYYY-MM-DD HH:mm:ss".

Error Log

[Mon Apr 27 15:16:16 2015] [error] [client 127.0.0.1] 1430140486000
[Mon Apr 27 15:16:18 2015] [error] [client 127.0.0.1] 1430140487000
[Mon Apr 27 15:16:19 2015] [error] [client 127.0.0.1] 1430140488000
[Mon Apr 27 15:16:20 2015] [error] [client 127.0.0.1] 1430140489000
[Mon Apr 27 15:16:21 2015] [error] [client 127.0.0.1] 1430140490000
[Mon Apr 27 15:16:22 2015] [error] [client 127.0.0.1] 1430140491000
[Mon Apr 27 15:16:23 2015] [error] [client 127.0.0.1] 1430140493000
[Mon Apr 27 15:16:24 2015] [error] [client 127.0.0.1] 1430140494000

The right entries in the error log are UNIX timestamps. As you can see there is a short timeout with the System time and shortly afterwards a timestamp from the DB gets skipped. I don't have any clue why this is actually happening.

Here are my two main files I work with:

getRTChartValues.js

var chartRT;

function requestData() {
$.ajax({
    url: 'server-data.php',
    success: function (point) {
        // shift if the series is longer than 30
        var series = chartRT.series[0],
            shift = series.data.length > 60;

        // add the point
        chartRT.series[0].addPoint(eval(point), true, shift);

        // call it again after one second
        setTimeout(requestData, 1000);
    },
    error: function () {
        console.log("some weird error in the php file")
    },
    cache: false
});
}

$(document).ready(

function () {
chartRT = new Highcharts.Chart({
    chart: {
        renderTo: 'diagram_realtime',
        type: 'area',
        animation: Highcharts.svg,
        events: {
            load: requestData
        },
        shadow: false,
        zoomType: 'x'
    },
    xAxis: {
        type: 'datetime',
        tickPixelInterval: 150,
        maxZoom: 60 * 1000
    },
    yAxis: {
        minPadding: 0.2,
        maxPadding: 0.2,
        allowDecimals: false,
        title: {
            text: 'Watt',
            margin: 80
        }
    },
    series: [{
        name: 'Data values',
        data: [],

    }],
    plotOptions: {
        series: {
            //threshold: 100,
            fillOpacity: 0.4,
            lineWidth: 2,
            marker: {
                enabled: false
            }
        }
    }
 });

Highcharts.setOptions({
    global: {
        useUTC: false
        // Sets the timestamps to the correct
        // timezone
    }
});

});

server-data_realtime.php

 <? php

 header("Content-type: text/json");

 $filename_database = "database.db";


 $mighty_database = new PDO("sqlite:".$filename_database, null, null, array(PDO::ATTR_PERSISTENT = > true));
 $mighty_database - > setAttribute(SQLITE3_OPEN_READONLY, true);


 $statement = $mighty_database - > query("SELECT timestamp,value1,value2 FROM table1 ORDER BY ID DESC LIMIT 1");
 $mighty_database - > setAttribute(PDO::ATTR_TIMEOUT, 1);

 $row = $statement - > fetch(PDO::FETCH_BOTH);

 $x = strtotime($row['timestamp']) * 1000;
 $y = ($row['value1'] - $row['value2']);
 $ret = array($x,$y);
 error_log(strval($ret[0]));
 echo json_encode($ret, JSON_NUMERIC_CHECK);
 ?>

I made a lot of research and debugging until now but I couldn't figure it out and don't know what to do next. So I would be pleased for every kind of help.

Thanks, Alex

Aucun commentaire:

Enregistrer un commentaire