Inserting data fails

[strokovnjaka]

What happened:

when using Business Table to insert a row, I get an error from the clickhouse datasource:

Request error

Details
add Error: {
    "error":{"refId":"","message":"PGX connection lifecycle error: driver: bad connection","status":500},
    "message":"PGX connection lifecycle error: driver: bad connection\nRequest: {\n \"editorType\": \"sql\",\n \"format\": 1,\n \"meta\": {\n \"builderOptions\": {\n \"columns\": [],\n \"database\": \"\",\n \"limit\": 1000,\n \"mode\": \"list\",\n \"queryType\": \"table\",\n \"table\": \"\"\n }\n },\n \"pluginVersion\": \"4.10.2\",\n \"queryType\": \"table\",\n \"rawSql\": \"INSERT INTO testing.test (name) VALUES ('sdf')\",\n \"refId\": \"\"\n}"
}

The thing is, three rows are inserted, and the above error is reported.

What you expected to happen:

A single row to be inserted and no error reported.

How to reproduce it (as minimally and precisely as possible):

1. create a clickhouse datasource (say you have a db called 'testing' with a table 'test', single column 'name' String), user has insert permissions
2. create a new dashboard for the datasource
3. add a panel of type Business Table:
   • in "Queries" put SELECT name FROM testing.test
   • in "Layout" add table test and its column name
   • in "Add data"
     • enable test and its column name
     • in "Add Request" choose datasource and put in the query INSERT INTO testing.test (name) VALUES (${payload.name:sqlstring})
4. click on the + sign in the top right corner of the panel
5. enter something in "name", click on the save icon on the right
6. observe the above error
7. refresh the panel, observer three rows inserted

Note that the same procedure works just fine with an "Microsoft SQL Server" datasource

Environment:

• Grafana version: v11.3.0 (d9455ff7db)
• Plugin version: 4.10.2
• OS Grafana is installed on: ubuntu 22.04
• User OS & Browser: any
• Others:

[SpencerTorres]

I would 100% recommend not ever, ever trying to insert rows through the Grafana plugin. It shouldn't be allowed, we should block it really. We recommend creating read-only users to prevent INSERTs.

It's possible in the sense that the plugin will execute the INSERT, but it really shouldn't be possible since the plugin is meant to query data. I see you're trying to insert test data, but perhaps this can be done through a different tool? Maybe you can explain your use case further, but I don't expect this is something we will fix since it is not at all recommended or officially supported.

What makes this even more confusing is seeing "PGX" in the error. Are you using Postgres at all? Either through ClickHouse or Grafana? It's possible the connection pool logic is re-used from PGX, but I'm confused with why it's appearing in relation to the ClickHouse plugin.

The thing is, three rows are inserted, and the above error is reported.

The query may be executed multiple times, depending on whether it's on the dashboard page or explore page. Grafana has a lot of logic related to refreshing queries and I would not at all expect predictable behavior especially in relation to INSERTs.

in "Add Request" choose datasource and put in the query

You might have better luck using ClickHouse's HTTP interface for the insert. I don't think Grafana should be inserting data like this, but if you're going to do it then do it as a separate request over HTTP instead of through the plugin. Maybe I'm not understanding the use case though. Perhaps you could attach a screenshot?

[strokovnjaka]

@SpencerTorres thanks for the explanation! Seems like a bad idea to have non read-only user.

The use case was, enter some access tokens for devices in a table, for an import script to use them for importing measurements from the devices. This way all (user-wise) would be done in grafana; administration of devices via Business Table, and browsing the measurements. I have found an easy none grafana way to administer, so going the read-only-user way.

PGX: yp, this is weird, as not using postgres at all, no postgres datasources even present.

As per three rows being inserted, I suppose the query is executed, but the client executing it does not interpret the result as a success and thus tries two more times before reporting the error. If I put select 1 in the Add Request query, only one call is done on the clickhouse server, and no error is reported.