Error while loading dashboard, dashboard data is invalid

[badsmoke]

Hello folks,

I am trying to switch from default database to postgres.

the script seems to work fine, in the search the dashboards are also listed. as soon as i want to look at one of the dashboards, there is unfortunately the error seen above.

INFO[2022-10-25T09:58:13Z] 📁 SQLlite file: /grafana.db                  
INFO[2022-10-25T09:58:13Z] 📁 Dump directory: /tmp                       
INFO[2022-10-25T09:58:13Z] ✅ sqlite3 command exists                     
INFO[2022-10-25T09:58:13Z] ✅ sqlite3 database dumped to /tmp/grafana.sql 
INFO[2022-10-25T09:58:14Z] ✅ CREATE statements removed from dump file   
INFO[2022-10-25T09:58:18Z] ✅ sqlite3 dump sanitized                     
INFO[2022-10-25T09:58:19Z] ✅ migration_log statements removed           
INFO[2022-10-25T09:58:20Z] ✅ char keyword transformed                   
INFO[2022-10-25T09:58:20Z] ✅ hex-encoded data decoded                   
INFO[2022-10-25T10:05:57Z] ✅ Imported dump file to Postgres             
INFO[2022-10-25T10:05:57Z] 🎉 All done!

Grafana version: 9.2.1 migrater version: 2.2.2

any ideas?

[wbh1]

I recently changed how hex-encoded data values get decoded to fix a different bug, so this may be related to that. Could you try with an older version like 2.2.1 or earlier?

[badsmoke]

thanks for the reply

version 2.2.1 gives me an error:

INFO[2022-10-27T15:29:46Z] 📁 SQLlite file: /grafana.db
INFO[2022-10-27T15:29:46Z] 📁 Dump directory: /tmp
INFO[2022-10-27T15:29:46Z] ✅ sqlite3 command exists
INFO[2022-10-27T15:29:47Z] ✅ sqlite3 database dumped to /tmp/grafana.sql
INFO[2022-10-27T15:29:48Z] ✅ CREATE statements removed from dump file
INFO[2022-10-27T15:29:51Z] ✅ sqlite3 dump sanitized
INFO[2022-10-27T15:29:53Z] ✅ migration_log statements removed
INFO[2022-10-27T15:29:53Z] ✅ char keyword transformed
INFO[2022-10-27T15:29:54Z] ✅ hex-encoded data decoded
FATAL[2022-10-27T15:29:55Z] ❌ pq: invalid byte sequence for encoding "UTF8": 0xab INSERT INTO "dashboard_snapshot" VALUES(3,'special-alerts-dashboard','Xwy4dG17D18mVvavI9NE6JIOAq6oU1BN','izrCuQVd4pNElL5xl6L105LgaHDF5RjW',1,1,0,'','{}','2071-11-19 09:14:57','2021-12-01 09:14:57','2021-12-01 09:14:57','','KMJ0YBee�����&�r㤃�ÎOe/xii�v톤�ׯf�3 

and so on 

[wbh1]

Heh, traded one error for another. I'll take a stab at reproducing on my end.

[wbh1]

So far I haven't been able to replicate it but I found the error you're getting in the Grafana codebase: https://github.com/grafana/grafana/blob/73462803169284645781f2f799f0a3a27c19df33/pkg/api/dashboard.go#L113-L125

It seems like somehow the data column in the dashboard table isn't valid JSON. Could you try to pull the value in there from Postgres? My command looked like this (change the title to the title of your dashboard):

psql -U postgres grafana -c "select data from dashboard where title = 'Prometheus / Overview';" -t

[wbh1]

Also, just to confirm: the SQLite file you're pulling from is from a Grafana instance running the same version of Grafana that you used to connect to Postgres and create the tables/schemas, right?

[badsmoke]

ok strange.

if I run 2.2.1 and afterwards the 2.2.2 then everything works, all dashboards are valid.

I have also re-checked it, if I run only 2.2.2 over it comes reproducible the above error.

so something seems to perform version 2.2.1 until the "invalid byte sequence..." error, so that 2.2.2 runs.

and yes, the grafana versions are the same

edit: if you need any more logs let me know

[wbh1]

Hacked away at this some more this morning but still haven't been able to replicate it ☹️

If you can send your grafana.db sqlite file to the email will at wbhegedus.me, I could poke more at it. Otherwise, this issue can probably be closed out as a workaround seems to exist (albeit a crummy one).

[gatalyak]

Hi, I have the same issue on v.2.2.2 and v.2.2.1:

1. For v.2.2.2 - no errors on migration, but grafana cannot open the migrated dashboards. In the dashboard table the column data has data: \x7b22616e6e6f746174696f6e73223a7b226c697374223a5b7b226275696c74496e223a312c2264617461736f75726365223a222d2d2047726166616e61202d2d222c22656e61626c65223a747275652c2268696465223a747275652c2269636f6e436f6c6f72223a2272676261283... and it is why the grafana cannot open dashboard. Grafana expect json text, not hex data. The reason could be of function decode('data',hex') when importing sql into postgresql.
2. For v.2.2.1 - the dashboards converted fine, but stucks on the dashboard_snapshot table - the same error as @badsmoke has. To replicate it some dashboards snapshots should be present in grafana.

[dvarrazzo]

I am finding the same problem, and I have verified that the dashboard.data is encoded. Note that it's not just encoded, but encoded in bytea hex format. I don't know what the go driver for postgres does, but it might be a data type error (escaping a bytes string to bytea syntax)

I have fixed my dashboards using a bit of Python and Psycopg 3:

import os, psycopg

conn = psycopg.connect(os.environ["URL"])

cur = conn.execute("select id, data from dashboard order by 1")
dbmap = dict(cur)
for id, data in dbmap.items():
    print(id, data[:10])

# 1 {"__requir
# 2 {"__elemen
# 3 \x7b22616e
# 4 {"schemaVe
# 5 \x7b22616e
# 6 \x7b22616e
# ...

esc = psycopg.pq.Escaping(conn.pgconn)

for id, data in dbmap.items():
    if data.startswith("{"):
        continue
    data = bytes(esc.unescape_bytea(data.encode())).decode()
    conn.execute("update dashboard set data = %s where id = %s", (data, id))

cur = conn.execute("select id, data from dashboard order by 1")
dbmap = dict(cur)
for id, data in dbmap.items():
    print(id, data[:10])

# 1 {"__requir
# 2 {"__elemen
# 3 {"annotati
# 4 {"schemaVe
# 5 {"annotati
# 6 {"annotati

conn.commit()

[wbh1]

Thanks @dvarrazzo and @gatalyak ! This gives me a lot more to go off of, and I'm hoping to dig more into it this week.

[wbh1]

I was able to replicate this after making changes to a dashboard several times so that its JSON got bigger and sqlite's .dump started outputting it as a hex-encoded string.

I made some changes to handle decoding these into a text column using convert_from in https://github.com/wbh1/grafana-sqlite-to-postgres/commit/b63ecbcf03661a3c7a3de10c143e490f5b268451, and still handle keeping things hex-encoded for bytea types.

Closing as resolved 🎉

[wbh1]

New release available here: https://github.com/wbh1/grafana-sqlite-to-postgres/releases/tag/v2.2.3