Fails on dashboard_snapshot

[andness]

Thank you so much for this tool, it saved me a bunch of time! 👍

I ran into this issue when running the migration against our DB. It was easy to fix as we don't really use dashboard snapshots so I could just clear the dashboard_snapshot table and move on, but figured I'd leave you a note.

FATAL[2022-07-14T10:44:42Z] ❌ pq: invalid byte sequence for encoding "UTF8": 0xc4 0x73 INSERT INTO "dashboard_snapshot" VALUES(6,'Usage','Dr9novg15a3GRVONa4fzXWbxaj0yIeFo','xdOr2pI0mjj6Fb3igOj5P6pyuDTFZGF8',1,2,0,'','{}','2072-01-27 17:23:33','2022-02-08 17:23:33','2022-02-08 17:23:33','','L4uu0Y806g�s�a>�j�

[wbh1]

Thank you for letting me know! I think this is related to https://github.com/wbh1/grafana-sqlite-to-postgres/issues/19, too. Which in turn I think is related to the usage of sqlite3 [1] based on this stackoverflow.

[weakcamel]

I'm seeing the same issue with Grafana 9.1.1. As a workaround, I removed the content of dashboard_snapshot table and retried the import, however now it's failing on data_keys table:

root@f44ed107e3a0:~# ./grafana-migrate /grafana.db "postgres://grafana:<rest of connection string>"

INFO[2022-08-26T12:58:09Z] 📁 SQLlite file: /grafana.db
INFO[2022-08-26T12:58:09Z] 📁 Dump directory: /tmp
INFO[2022-08-26T12:58:09Z] ✅ sqlite3 command exists
INFO[2022-08-26T12:58:09Z] ✅ sqlite3 database dumped to /tmp/grafana.sql
INFO[2022-08-26T12:58:10Z] ✅ CREATE statements removed from dump file
INFO[2022-08-26T12:58:14Z] ✅ sqlite3 dump sanitized
INFO[2022-08-26T12:58:15Z] ✅ migration_log statements removed
INFO[2022-08-26T12:58:15Z] ✅ char keyword transformed
INFO[2022-08-26T12:58:16Z] ✅ hex-encoded data decoded
FATAL[2022-08-26T12:58:23Z] ❌ pq: invalid byte sequence for encoding "UTF8": 0xe7 0x18 0x9b INSERT INTO "data_keys" VALUES('<redacted>',1,'root','secretKey.v1',<REDACTED>,'2022-08-25 15:37:01','2022-08-25 15:37:01','2022-08-25/root@secretKey.v1') - failed to import dump file to Postgres.

I'm not sure how important content of that table is - seems to be used for encryption of secrets: https://github.com/grafana/grafana/blob/main/pkg/services/secrets/database/database.go

Do you have any suggestions?

[weakcamel]

A bit more progress: I compiled the latest sqlite3 from source code within the image and this let me go a bit further... not all the way though.

Now it's failing on:

INFO[2022-08-26T13:26:42Z] ✅ hex-encoded data decoded
FATAL[2022-08-26T13:26:43Z] ❌ pq: null value in column "is_public" of relation "dashboard" violates not-null constraint INSERT INTO "dashboard" VALUES(1,13,'vmware-vsphere-vms','VMware vSphere  - VMs','{"__inputs": ....

[weakcamel]

Unfortunately, the UTF-8 problems have re-occurred for me while re-testing import from scratch and I'm still facing them:

FATAL[2022-08-26T12:58:23Z] ❌ pq: invalid byte sequence for encoding "UTF8": 0xe7 0x18 0x9b INSERT INTO "data_keys" VALUES('<redacted>',1,'root','secretKey.v1',<REDACTED>,'2022-08-25 15:37:01','2022-08-25 15:37:01','2022-08-25/root@secretKey.v1') - failed to import dump file to Postgres.

( just like https://github.com/wbh1/grafana-sqlite-to-postgres/issues/28#issuecomment-1228477984 )

I ended up using pgloader which seems to emit warnings about type conversions but works:

https://polyglot.jamie.ly/programming/2019/07/01/grafana-sqlite-to-postgres.html

[wbh1]

Closing this issue and tracking in https://github.com/wbh1/grafana-sqlite-to-postgres/issues/34