Issue with pq: unterminated quoted string at or near ...

[jgould22]

When attempting to import a Grafana sqlite3 db to Postgres 12 I have twice encountered the same bug

INFO[2020-01-22T15:04:24-05:00] 📁 SQLlite file: grafana.db                   
INFO[2020-01-22T15:04:24-05:00] 📁 Dump directory: /tmp                       
INFO[2020-01-22T15:04:24-05:00] ✅ sqlite3 command exists                     
INFO[2020-01-22T15:04:25-05:00] ✅ sqlite3 database dumped to /tmp/grafana.sql 
INFO[2020-01-22T15:04:28-05:00] ✅ CREATE statements removed from dump file   
INFO[2020-01-22T15:04:38-05:00] ✅ sqlite3 dump sanitized                     
INFO[2020-01-22T15:04:42-05:00] ✅ migration_log statements removed           
INFO[2020-01-22T15:04:43-05:00] ✅ hex-encoded data decoded                   
FATAL[2020-01-22T15:04:47-05:00] ❌ pq: unterminated quoted string at or near "'<Redacted Alert Message text> -" INSERT INTO "alert" VALUES(5,0,77,2,1,'','<Redacted Alert Message text> - failed to import dump file to Postgres. 

There were only a cuple alerts so I removed them but the same error seems to occur on what appears to be annotations now

./grafana-migrate_linux_amd64-9d3f51c grafana.db "postgres://<user>:<pass>@postgresdb:5000/grafana?sslmode=disable"
INFO[2020-01-22T15:30:35-05:00] 📁 SQLlite file: grafana5.db                  
INFO[2020-01-22T15:30:35-05:00] 📁 Dump directory: /tmp                       
INFO[2020-01-22T15:30:35-05:00] ✅ sqlite3 command exists                     
INFO[2020-01-22T15:30:36-05:00] ✅ sqlite3 database dumped to /tmp/grafana.sql 
INFO[2020-01-22T15:30:38-05:00] ✅ CREATE statements removed from dump file   
INFO[2020-01-22T<Redacted Text>15:30:46-05:00] ✅ sqlite3 dump sanitized                     
INFO[2020-01-22T15:30:50-05:00] ✅ migration_log statements removed           
INFO[2020-01-22T15:30:50-05:00] ✅ hex-encoded data decoded                   
FATAL[2020-01-22T15:30:52-05:00] ❌ pq: unterminated quoted string at or near "'<Redacted Text> " INSERT INTO "annotation" VALUES(48,1,0,3,15,6,NULL,'','','<Redacted Text>  - failed to import dump file to Postgres. 

Unfortunately there are far more annotations and it isnt feasible for me to recreate them

Any help is greatly appreciated

[wbh1]

Hey Jordan, I will look into this. I hadn't tested with a Grafana that is used for alerting, personally.

[wbh1]

@jgould22 I was able to import alerts & annotations without issue, just now. Could you send me one or two of the failing alerts/annotations from the /tmp/grafana.sql dump?

[jgould22]

Hey Will, So I had a look at the grafana.sql and the failing annotations seems to be failing when encountering a \n

Here is an example of a failing annotation as it appears in /tmp/grafana.sql

INSERT INTO "annotation" VALUES(49,1,0,3,15,6,NULL,'','','IPs 
127.0.0.1/32
127.0.0.1/32
127.0.0.1/32
127.0.0.1/32
127.0.0.1/32
127.0.0.1/32
127.0.0.1/32
127.0.0.1/32
127.0.0.1/32
127.0.0.1/32
127.0.0.1/32
127.0.0.1/32
127.0.0.1/32
127.0.0.1/32
127.0.0.1/32
127.0.0.1/32
127.0.0.1/32
127.0.0.1/32
127.0.0.1/32
127.0.0.1/32
127.0.0.1/32',NULL,'','','',1548796008492,0,'["tag","global"]',1548798001194,1548860046794,1548796008492);

[wbh1]

Ah, yeah... That'll do it. Currently I separate the SQL statements to be run by splitting on new lines (ref).

I'll see if there's a better way to do this with the database drivers I'm using, or if I need to sanitize the input differently.

[wbh1]

I have updated this and cut a new release. Can you test it and confirm it works now?

[jgould22]

Hey Will, I tried the new release and everything went well without issue.

Thanks for the help and the migration tool!

[wbh1]

@jgould22 glad to hear it! Thanks for helping me pinpoint the issue.