Syntax error while importing hex-encoded data

[ficofer]

@wbh1 Will, thanks a lot for this amazing tool is really useful.

I am working in a project where I need to migrate from sqlite to Postgres running on RDS, before going to the actual AWS system I am testing this locally (a couple of containers running on my machine)

All seems to go as expected, overcame already some issues with manually (outside of grafana created tables) needed in the new psql to actually insert the info into them... but this one I am not able to workaround.

sqlite.db --> ~ 170mb

The error:

FicoMBP.local ➜ ./grafana-migrate_darwin_amd64-v1.1.2 ../../grafana.db "postgres://postgres:postgres@localhost:5432/grafana?sslmode=disable"
INFO[2020-05-02T20:06:08+01:00] 📁 SQLlite file: ../../grafana.db
INFO[2020-05-02T20:06:08+01:00] 📁 Dump directory: /tmp
INFO[2020-05-02T20:06:08+01:00] ✅ sqlite3 command exists
INFO[2020-05-02T20:06:14+01:00] ✅ sqlite3 database dumped to /tmp/grafana.sql
INFO[2020-05-02T20:06:19+01:00] ✅ CREATE statements removed from dump file
INFO[2020-05-02T20:06:42+01:00] ✅ sqlite3 dump sanitized
INFO[2020-05-02T20:06:52+01:00] ✅ migration_log statements removed
INFO[2020-05-02T20:06:56+01:00] ✅ hex-encoded data decoded
FATAL[2020-05-02T20:06:59+01:00] ❌ pq: syntax error at or near ")" INSERT INTO "alert" VALUES(28,0,106,26,1,'My Alert',replace('Hello,\n\nThis was triggered and exited over 200 times in 5 minutes on a server.','\n',char(10)),'pending','{"conditions":[{"evaluator":{"params":[200],"type":"gt"},"operator":{"type":"and"},"query":{"datasourceId":10,"model":{"hide":true,"refId":"A","target":"aliasByNode(summarize(statsd.$server.counters.runit.live_broker.exit.count, ''$timegroup'', ''sum'', ''false''), 1)"},"params":["A","5m","now"]},"reducer":{"params":[],"type":"avg"},"type":"query"}],"executionErrorState":"keep_state","frequency":"60s","handler":1,"message":"Hello,\n\nThis was triggered and exited over 200 times in 5 minutes on a server.","name":"My alert","noDataState":"keep_state","notifications":[{"id":13}]}',60,1,'',0,'',NULL,NULL,'2018-11-01 18:25:30',0,'2018-11-01 18:25:30','2020-04-28 18:33:31',0) 
- failed to import dump file to Postgres.

The /tmp/grafana.sql

INSERT INTO "alert" VALUES(28,0,106,26,1,'My Alert',replace('Hello,\n\nThis was triggered and exited over 200 times in 5 minutes on a server.','\n',char(10)),'pending','{"conditions":[{"evaluator":{"params":[200],"type":"gt"},"operator":{"type":"and"},"query":{"datasourceId":10,"model":{"hide":true,"refId":"A","target":"aliasByNode(summarize(statsd.$server.counters.runit.live_broker.exit.count, ''$timegroup'', ''sum'', ''false''), 1)"},"params":["A","5m","now"]},"reducer":{"params":[],"type":"avg"},"type":"query"}],"executionErrorState":"keep_state","frequency":"60s","handler":1,"message":"Hello,\n\nThis was triggered and exited over 200 times in 5 minutes on a server.","name":"My alert","noDataState":"keep_state","notifications":[{"id":13}]}',60,1,'',0,'',NULL,NULL,'2018-11-01 18:25:30',0,'2018-11-01 18:25:30','2020-04-28 18:33:31',0);

[wbh1]

Hey Fernando, I think you're running into an issue that #9 should resolve. Could you try running your migration with @nbuchwitz's branch? You'll have to manually build it from https://github.com/nbuchwitz/grafana-sqlite-to-postgres

If it works for you, then it should be OK to merge in :)

[ficofer]

@wbh1 Will, thanks for the prompt reply using @nbuchwitz definitely help or at least let me move on to the following error, I am trying to understand it as it seems to be a syntax error in some conversion ?

What could be the approach to help out fixing this edge cases maybe to make it error prune ?

FATAL[2020-05-03T18:42:28+01:00] ❌ pq: invalid input syntax for type integer: "" INSERT INTO "dashboard" VALUES(1,8,'new-user','New user','{"annotations":{"list":[]},"editable":true,"hideControls":false,"id":1,"links":[],"originalTitle":"New user","rows":[{"collapse":false,"editable":true,"height":"250px","panels":[{"aliasColors":{},"bars":true,"datasource":null,"editable":true,"error":false,"fill":1,"grid":{"leftLogBase":1,"leftMax":null,"leftMin":null,"rightLogBase":1,"rightMax":null,"rightMin":null,"threshold1":null,"threshold1Color":"rgba(216, 200, 27, 0.27)","threshold2":null,"threshold2Color":"rgba(234, 112, 112, 0.22)"},"id":3,"isNew":true,"legend":{"alignAsTable":true,"avg":false,"current":false,"max":false,"min":false,"rightSide":true,"show":true,"total":true,"values":true},"lines":false,"linewidth":2,"links":[],"nullPointMode":"connected","percentage":false,"pointradius":5,"points":false,"renderer":"flot","seriesOverrides":[{"alias":"index","bars":false,"fill":0,"lines":true,"linewidth":1}],"span":12,"stack":true,"steppedLine":false,"targets":[{"refId":"A","target":"aliasByNode(summarize(master.*.stats.claim.number.*, ''1d'', ''sum'', false), 5)"},{"hide":true,"refId":"B","target":"aliasByNode(summarize(master.*.stats.claim.index, ''1d'', ''sum'', false), 4)"}],"timeFrom":null,"timeShift":null,"title":"Claim Origin","tooltip":{"shared":true,"value_type":"individual"},"type":"graph","x-axis":true,"y-axis":true,"y_formats":["short","short"]}],"title":"Row"},{"collapse":false,"editable":true,"height":"250px","panels":[{"aliasColors":{},"bars":true,"datasource":null,"editable":true,"error":false,"fill":1,"grid":{"leftLogBase":1,"leftMax":null,"leftMin":null,"rightLogBase":1,"rightMax":null,"rightMin":null,"threshold1":null,"threshold1Color":"rgba(216, 200, 27, 0.27)","threshold2":null,"threshold2Color":"rgba(234, 112, 112, 0.22)"},"id":2,"isNew":true,"legend":{"alignAsTable":true,"avg":false,"current":false,"max":false,"min":false,"rightSide":true,"show":true,"total":true,"values":true},"lines":false,"linewidth":2,"links":[],"nullPointMode":"connected","percentage":false,"pointradius":5,"points":false,"renderer":"flot","seriesOverrides":[],"span":12,"stack":true,"steppedLine":false,"targets":[{"refId":"A","target":"aliasByNode(summarize(master.*.stats.new_account.*, ''1d'', ''sum'', false), 4)"}],"timeFrom":null,"timeShift":null,"title":"New Account","tooltip":{"shared":true,"value_type":"individual"},"type":"graph","x-axis":true,"y-axis":true,"y_formats":["short","short"]}],"title":"New row"}],"schemaVersion":8,"sharedCrosshair":false,"style":"dark","tags":[],"templating":{"list":[]},"time":{"from":"now-60d","to":"now"},"timepicker":{"now":true,"refresh_intervals":["5s","10s","30s","1m","5m","15m","30m","1h","2h","1d"],"time_options":["5m","15m","1h","6h","12h","24h","2d","7d","30d"]},"timezone":"browser","title":"New user","version":8}',1,'2016-01-26 00:00:46','2016-01-26 00:26:07','',0,0,'',0,0,0,'000000001') - failed to import dump file to Postgres.

INSERT INTO "dashboard" VALUES(1,8,'new-user','New user','{"annotations":{"list":[]},"editable":true,"hideControls":false,"id":1,"links":[],"originalTitle":"New user","rows":[{"collapse":false,"editable":true,"height":"250px","panels":[{"aliasColors":{},"bars":true,"datasource":null,"editable":true,"error":false,"fill":1,"grid":{"leftLogBase":1,"leftMax":null,"leftMin":null,"rightLogBase":1,"rightMax":null,"rightMin":null,"threshold1":null,"threshold1Color":"rgba(216, 200, 27, 0.27)","threshold2":null,"threshold2Color":"rgba(234, 112, 112, 0.22)"},"id":3,"isNew":true,"legend":{"alignAsTable":true,"avg":false,"current":false,"max":false,"min":false,"rightSide":true,"show":true,"total":true,"values":true},"lines":false,"linewidth":2,"links":[],"nullPointMode":"connected","percentage":false,"pointradius":5,"points":false,"renderer":"flot","seriesOverrides":[{"alias":"index","bars":false,"fill":0,"lines":true,"linewidth":1}],"span":12,"stack":true,"steppedLine":false,"targets":[{"refId":"A","target":"aliasByNode(summarize(master.*.stats.claim.number.*, ''1d'', ''sum'', false), 5)"},{"hide":true,"refId":"B","target":"aliasByNode(summarize(master.*.stats.claim.index, ''1d'', ''sum'', false), 4)"}],"timeFrom":null,"timeShift":null,"title":"Claim Origin","tooltip":{"shared":true,"value_type":"individual"},"type":"graph","x-axis":true,"y-axis":true,"y_formats":["short","short"]}],"title":"Row"},{"collapse":false,"editable":true,"height":"250px","panels":[{"aliasColors":{},"bars":true,"datasource":null,"editable":true,"error":false,"fill":1,"grid":{"leftLogBase":1,"leftMax":null,"leftMin":null,"rightLogBase":1,"rightMax":null,"rightMin":null,"threshold1":null,"threshold1Color":"rgba(216, 200, 27, 0.27)","threshold2":null,"threshold2Color":"rgba(234, 112, 112, 0.22)"},"id":2,"isNew":true,"legend":{"alignAsTable":true,"avg":false,"current":false,"max":false,"min":false,"rightSide":true,"show":true,"total":true,"values":true},"lines":false,"linewidth":2,"links":[],"nullPointMode":"connected","percentage":false,"pointradius":5,"points":false,"renderer":"flot","seriesOverrides":[],"span":12,"stack":true,"steppedLine":false,"targets":[{"refId":"A","target":"aliasByNode(summarize(master.*.stats.new_account.*, ''1d'', ''sum'', false), 4)"}],"timeFrom":null,"timeShift":null,"title":"New Account","tooltip":{"shared":true,"value_type":"individual"},"type":"graph","x-axis":true,"y-axis":true,"y_formats":["short","short"]}],"title":"New row"}],"schemaVersion":8,"sharedCrosshair":false,"style":"dark","tags":[],"templating":{"list":[]},"time":{"from":"now-60d","to":"now"},"timepicker":{"now":true,"refresh_intervals":["5s","10s","30s","1m","5m","15m","30m","1h","2h","1d"],"time_options":["5m","15m","1h","6h","12h","24h","2d","7d","30d"]},"timezone":"browser","title":"New user","version":8}',1,'2016-01-26 00:00:46','2016-01-26 00:26:07','',0,0,'',0,0,0,'000000001');

May be its the double quoting ?

--Edit--

As if it were not grabbing/replacing the empty spaces correctly?

LINE 1: ...8}',1,'2016-01-26 00:00:46','2016-01-26 00:26:07','',0,0,'',...
                                                             ^

[wbh1]

I believe the issue that you're hitting is that the value that is being attempted to be inserted into the updated_by column of the dashboard table is an empty string rather than NULL (I believe SQLite treats them basically the same but PostgreSQL does not).

I'm not sure of a good way to programmatically map empty strings in integer-typed columns to NULL. In the interim, I would suggest updating this dashboard in SQLite using:

update dashboard set updated_by = 1 where updated_by is NULL;

[ficofer]

@wbh1 that did the job, instead of doing it agains NULL I did it against EMPTY (not sure the diff but against NULL was still failing.

update dashboard set updated_by = 1 where updated_by is '';