Limit of 50 at SQL

Hello there,
I’m trying to visualize some temperature value in Grafana as part of HomeAssistant.
Surprisingly it was working on the first attempt :slight_smile: …almost…
Unfortunately the SQL query (which I created with the “Query Bilder”) is limited to 50.
Could someone help me to understand where the limit is comming from and how to change it?
Bildschirmfoto vom 2022-12-01 22-58-36

Best Regards
Michael

PS: please find the code of the graph below.

Key Value
Panel timeseries @ 9.2.4 (64017e8ca6)
Grafana 9.2.4 (64017e8ca6) // Open Source
Panel debug snapshot dashboard
{
  "panels": [
    {
      "id": 2,
      "gridPos": {
        "h": 13,
        "w": 15,
        "x": 0,
        "y": 0
      },
      "type": "timeseries",
      "title": "Reproduced with embedded data",
      "datasource": {
        "type": "grafana",
        "uid": "grafana"
      },
      "description": "Vor- und Rücklauftemperaturen des Heitzkreis1",
      "fieldConfig": {
        "defaults": {
          "custom": {
            "drawStyle": "line",
            "lineInterpolation": "smooth",
            "barAlignment": 0,
            "lineWidth": 1,
            "fillOpacity": 0,
            "gradientMode": "none",
            "spanNulls": false,
            "showPoints": "auto",
            "pointSize": 5,
            "stacking": {
              "mode": "none",
              "group": "A"
            },
            "axisPlacement": "auto",
            "axisLabel": "",
            "axisColorMode": "text",
            "scaleDistribution": {
              "type": "linear"
            },
            "axisCenteredZero": false,
            "hideFrom": {
              "tooltip": false,
              "viz": false,
              "legend": false
            },
            "thresholdsStyle": {
              "mode": "off"
            }
          },
          "color": {
            "mode": "palette-classic"
          },
          "mappings": [],
          "thresholds": {
            "mode": "absolute",
            "steps": [
              {
                "color": "green",
                "value": null
              },
              {
                "color": "red",
                "value": 80
              }
            ]
          }
        },
        "overrides": [
          {
            "matcher": {
              "id": "byName",
              "options": "ruecklaufTemp"
            },
            "properties": [
              {
                "id": "color",
                "value": {
                  "fixedColor": "semi-dark-blue",
                  "mode": "fixed"
                }
              }
            ]
          },
          {
            "matcher": {
              "id": "byName",
              "options": "vorlaufTemp"
            },
            "properties": [
              {
                "id": "color",
                "value": {
                  "fixedColor": "semi-dark-red",
                  "mode": "fixed"
                }
              }
            ]
          }
        ]
      },
      "options": {
        "tooltip": {
          "mode": "single",
          "sort": "none"
        },
        "legend": {
          "showLegend": true,
          "displayMode": "list",
          "placement": "bottom",
          "calcs": []
        }
      },
      "targets": [
        {
          "refId": "A",
          "datasource": {
            "type": "grafana",
            "uid": "grafana"
          },
          "queryType": "snapshot",
          "snapshot": [
            {
              "schema": {
                "refId": "A",
                "meta": {
                  "executedQueryString": "SELECT vorlaufTemp, ruecklaufTemp, Timestamp FROM MyHomeDataTest.LWZ8data LIMIT 50 "
                },
                "fields": [
                  {
                    "name": "vorlaufTemp",
                    "type": "number",
                    "typeInfo": {
                      "frame": "float64",
                      "nullable": true
                    },
                    "config": {}
                  },
                  {
                    "name": "ruecklaufTemp",
                    "type": "number",
                    "typeInfo": {
                      "frame": "float64",
                      "nullable": true
                    },
                    "config": {}
                  },
                  {
                    "name": "Timestamp",
                    "type": "time",
                    "typeInfo": {
                      "frame": "time.Time",
                      "nullable": true
                    },
                    "config": {}
                  }
                ]
              },
              "data": {
                "values": [
                  [
                    31.4,
                    32.3,
                    32.3,
                    33,
                    33.1,
                    33.2,
                    33.2,
                    33.5,
                    33.3,
                    32.5,
                    32.5,
                    33.1,
                    33.3,
                    30.7,
                    30.9,
                    31.1,
                    31.1,
                    31.1,
                    30.1,
                    29.5,
                    29.7,
                    29.8,
                    29.8,
                    29.6,
                    28.9,
                    29.1,
                    28.9,
                    28.2,
                    28.3,
                    28.3,
                    28.3,
                    27.9,
                    27.7,
                    27.7,
                    27.8,
                    27.2,
                    26.8,
                    27.6,
                    27.1,
                    27.7,
                    27.9,
                    28,
                    28.8,
                    29.8,
                    29.8,
                    29.9,
                    30.7,
                    31.3,
                    32.4,
                    32.1
                  ],
                  [
                    29.5,
                    30.4,
                    30.4,
                    30.4,
                    30.8,
                    30.7,
                    30.7,
                    31.5,
                    31.3,
                    30.6,
                    30.6,
                    30.9,
                    31.4,
                    31.4,
                    31.6,
                    31.7,
                    31.7,
                    31.3,
                    30.1,
                    30.3,
                    30.5,
                    30.3,
                    30.3,
                    29.9,
                    29.5,
                    29.6,
                    29.1,
                    28.7,
                    28.8,
                    28.8,
                    28.9,
                    28.4,
                    28.3,
                    28.3,
                    28.2,
                    27.3,
                    25.5,
                    24.8,
                    24.5,
                    24.7,
                    25,
                    24.9,
                    25.2,
                    25.2,
                    26,
                    26.5,
                    27.4,
                    27.6,
                    27.8,
                    27.8
                  ],
                  [
                    1669924340000,
                    1669924369000,
                    1669924397000,
                    1669924457000,
                    1669924517000,
                    1669924577000,
                    1669924637000,
                    1669924741000,
                    1669924801000,
                    1669924861000,
                    1669924921000,
                    1669924981000,
                    1669925041000,
                    1669925148000,
                    1669925233000,
                    1669925293000,
                    1669925353000,
                    1669925413000,
                    1669925473000,
                    1669925533000,
                    1669925593000,
                    1669925653000,
                    1669925713000,
                    1669925773000,
                    1669925864000,
                    1669925924000,
                    1669926040000,
                    1669926100000,
                    1669926160000,
                    1669926220000,
                    1669926280000,
                    1669926388000,
                    1669926448000,
                    1669926508000,
                    1669926568000,
                    1669926628000,
                    1669926688000,
                    1669926760000,
                    1669926933000,
                    1669926993000,
                    1669927053000,
                    1669927113000,
                    1669927173000,
                    1669927233000,
                    1669927293000,
                    1669927353000,
                    1669927413000,
                    1669927473000,
                    1669927533000,
                    1669927593000
                  ]
                ]
              }
            }
          ]
        }
      ]
    },
    {
      "gridPos": {
        "h": 7,
        "w": 9,
        "x": 15,
        "y": 0
      },
      "id": 5,
      "options": {
        "content": "<table width=\"100%\">\n    <tr>\n      <th width=\"2%\">Panel</th>\n      <td >timeseries @ 9.2.4 (64017e8ca6)</td>\n    </tr>\n    <tr>\n      <th>Queries</th>\n      <td>A[mysql]</td>\n    </tr>\n    \n    <tr><th>Data</th><td> 1 frames, 3 fields, 50 rows </td></tr>\n    \n    <tr>\n      <th>Grafana</th>\n      <td>9.2.4 (64017e8ca6) // Open Source</td>\n    </tr>\n  </table>",
        "mode": "html"
      },
      "title": "Debug info",
      "type": "text"
    },
    {
      "id": 6,
      "title": "Original Panel JSON",
      "type": "text",
      "gridPos": {
        "h": 13,
        "w": 9,
        "x": 15,
        "y": 7
      },
      "options": {
        "content": "{\n  \"id\": 6,\n  \"gridPos\": {\n    \"h\": 8,\n    \"w\": 12,\n    \"x\": 0,\n    \"y\": 0\n  },\n  \"type\": \"timeseries\",\n  \"title\": \"Vor- / Rücklauf\",\n  \"datasource\": {\n    \"type\": \"mysql\",\n    \"uid\": \"-ZrW0OF4z\"\n  },\n  \"description\": \"Vor- und Rücklauftemperaturen des Heitzkreis1\",\n  \"fieldConfig\": {\n    \"defaults\": {\n      \"custom\": {\n        \"drawStyle\": \"line\",\n        \"lineInterpolation\": \"smooth\",\n        \"barAlignment\": 0,\n        \"lineWidth\": 1,\n        \"fillOpacity\": 0,\n        \"gradientMode\": \"none\",\n        \"spanNulls\": false,\n        \"showPoints\": \"auto\",\n        \"pointSize\": 5,\n        \"stacking\": {\n          \"mode\": \"none\",\n          \"group\": \"A\"\n        },\n        \"axisPlacement\": \"auto\",\n        \"axisLabel\": \"\",\n        \"axisColorMode\": \"text\",\n        \"scaleDistribution\": {\n          \"type\": \"linear\"\n        },\n        \"axisCenteredZero\": false,\n        \"hideFrom\": {\n          \"tooltip\": false,\n          \"viz\": false,\n          \"legend\": false\n        },\n        \"thresholdsStyle\": {\n          \"mode\": \"off\"\n        }\n      },\n      \"color\": {\n        \"mode\": \"palette-classic\"\n      },\n      \"mappings\": [],\n      \"thresholds\": {\n        \"mode\": \"absolute\",\n        \"steps\": [\n          {\n            \"color\": \"green\",\n            \"value\": null\n          },\n          {\n            \"color\": \"red\",\n            \"value\": 80\n          }\n        ]\n      }\n    },\n    \"overrides\": [\n      {\n        \"matcher\": {\n          \"id\": \"byName\",\n          \"options\": \"ruecklaufTemp\"\n        },\n        \"properties\": [\n          {\n            \"id\": \"color\",\n            \"value\": {\n              \"fixedColor\": \"semi-dark-blue\",\n              \"mode\": \"fixed\"\n            }\n          }\n        ]\n      },\n      {\n        \"matcher\": {\n          \"id\": \"byName\",\n          \"options\": \"vorlaufTemp\"\n        },\n        \"properties\": [\n          {\n            \"id\": \"color\",\n            \"value\": {\n              \"fixedColor\": \"semi-dark-red\",\n              \"mode\": \"fixed\"\n            }\n          }\n        ]\n      }\n    ]\n  },\n  \"options\": {\n    \"tooltip\": {\n      \"mode\": \"single\",\n      \"sort\": \"none\"\n    },\n    \"legend\": {\n      \"showLegend\": true,\n      \"displayMode\": \"list\",\n      \"placement\": \"bottom\",\n      \"calcs\": []\n    }\n  },\n  \"targets\": [\n    {\n      \"dataset\": \"MyHomeDataTest\",\n      \"datasource\": {\n        \"type\": \"mysql\",\n        \"uid\": \"-ZrW0OF4z\"\n      },\n      \"editorMode\": \"builder\",\n      \"format\": \"table\",\n      \"rawSql\": \"SELECT vorlaufTemp, ruecklaufTemp, Timestamp FROM MyHomeDataTest.LWZ8data LIMIT 50 \",\n      \"refId\": \"A\",\n      \"sql\": {\n        \"columns\": [\n          {\n            \"parameters\": [\n              {\n                \"name\": \"vorlaufTemp\",\n                \"type\": \"functionParameter\"\n              }\n            ],\n            \"type\": \"function\"\n          },\n          {\n            \"parameters\": [\n              {\n                \"name\": \"ruecklaufTemp\",\n                \"type\": \"functionParameter\"\n              }\n            ],\n            \"type\": \"function\"\n          },\n          {\n            \"parameters\": [\n              {\n                \"name\": \"Timestamp\",\n                \"type\": \"functionParameter\"\n              }\n            ],\n            \"type\": \"function\"\n          }\n        ],\n        \"groupBy\": [\n          {\n            \"property\": {\n              \"type\": \"string\"\n            },\n            \"type\": \"groupBy\"\n          }\n        ],\n        \"limit\": 50\n      },\n      \"table\": \"LWZ8data\"\n    }\n  ]\n}",
        "mode": "code",
        "code": {
          "language": "json",
          "showLineNumbers": true,
          "showMiniMap": true
        }
      }
    },
    {
      "id": 3,
      "title": "Data from panel above",
      "type": "table",
      "datasource": {
        "type": "datasource",
        "uid": "-- Dashboard --"
      },
      "gridPos": {
        "h": 7,
        "w": 15,
        "x": 0,
        "y": 13
      },
      "options": {
        "showTypeIcons": true
      },
      "targets": [
        {
          "datasource": {
            "type": "datasource",
            "uid": "-- Dashboard --"
          },
          "panelId": 2,
          "withTransforms": true,
          "refId": "A"
        }
      ]
    }
  ],
  "schemaVersion": 37,
  "title": "Debug: Vor- / Rücklauf // 2022-12-01 22:51:00",
  "tags": [
    "debug",
    "debug-timeseries"
  ],
  "time": {
    "from": "2022-12-01T12:15:33.295Z",
    "to": "2022-12-02T00:15:33.295Z"
  }
}

Welcome

Can you go from query builder to actual code? Then remove there?

1 Like

Hi yosiasz,

yes, I just tried and I could change it in the code. The graph seems then correct.
But as soon as I switch to another page and move back again then it’s in “query builder” again and the limit of 50 is set again.

what database is it? Delete that query, and create a new one without the use of query builder

It’s MariaDB.

I’ll give it a try creating the query manually. Though it would be pretty comfortable using the query builder if it would work as expected.

I have the same problem and it really freaks me out why they implemented this fix limit with no (at least no easy) possibility to change it. This came with the actual version of Grafana, with the Query Builder of the previous versions this was no problem at all. By the way, I really liked the old query bilder lots more than the new version which is not intuitive and obscure for me. So what about this limit 50? Why it’s there and where can I cange it?

1 Like

I would highly encourage the use of stored procedures or views instead of using query builder. Yes query builder is easy but one should also be more comfortable using pure query crafting.

I am new to Grafana as well, and the very same 50-limit is messing with me.

Click “Order” checkbox
image
Erase “Limit” textbox
image
This worked for me.

5 Likes

Thank you.
I discovered that clicking “code” instead of “builder” lets me just delete the limit.
Nevertheless, this is a bad first-experience.

1 Like