Fails trying to add existing row to database

[Craige McWhirter]

The grafana charm fails trying to insert a row that already exists:

2018-02-24 00:34:03 INFO juju-log Invoking reactive handler: reactive/grafana.py:297:configure_sources
2018-02-24 00:34:04 INFO juju-log Found datasource: {'type': 'prometheus',
'description': 'Juju generated source', 'service_name': 'prometheus',
'url': 'http://host.maas:9090'}
2018-02-24 00:34:04 INFO juju-log Adding new datasource: prometheus - Juju generated source
2018-02-24 00:34:04 INFO juju-log Statement is:

         INSERT INTO DATA_SOURCE (org_id, version, type, name, access, url, is_default, created, updated, basic_auth)
         VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

2018-02-24 00:34:04 INFO juju-log [1, 0, 'prometheus', 'prometheus - Juju generated source', 'proxy',
    'http://host.maas:9090', 0, '2018-02-24 00:34:04', '2018-02-24 00:34:04', 0]
2018-02-24 00:34:04 INFO config-changed Traceback (most recent call last):
2018-02-24 00:34:04 INFO config-changed File "/var/lib/juju/agents/unit-grafana-0/charm/hooks/config-changed", line 19, in <module>
2018-02-24 00:34:04 INFO config-changed main()
2018-02-24 00:34:04 INFO config-changed File "/usr/local/lib/python3.4/dist-packages/charms/reactive/__init__.py", line 113, in main
2018-02-24 00:34:04 INFO config-changed bus.dispatch(restricted=restricted_mode)
2018-02-24 00:34:04 INFO config-changed File "/usr/local/lib/python3.4/dist-packages/charms/reactive/bus.py", line 364, in dispatch
2018-02-24 00:34:04 INFO config-changed _invoke(other_handlers)
2018-02-24 00:34:04 INFO config-changed File "/usr/local/lib/python3.4/dist-packages/charms/reactive/bus.py", line 340, in _invoke
2018-02-24 00:34:04 INFO config-changed handler.invoke()
2018-02-24 00:34:04 INFO config-changed File "/usr/local/lib/python3.4/dist-packages/charms/reactive/bus.py", line 162, in invoke
2018-02-24 00:34:04 INFO config-changed self._action(*args)
2018-02-24 00:34:04 INFO config-changed File "/var/lib/juju/agents/unit-grafana-0/charm/reactive/grafana.py", line 304, in configure_sources
2018-02-24 00:34:04 INFO config-changed check_datasource(ds)
2018-02-24 00:34:04 INFO config-changed File "/var/lib/juju/agents/unit-grafana-0/charm/reactive/grafana.py", line 403, in check_datasource
2018-02-24 00:34:04 INFO config-changed cur.execute(stmt, values)
2018-02-24 00:34:04 INFO config-changed sqlite3.IntegrityError: UNIQUE constraint failed: data_source.org_id, data_source.name
2018-02-24 00:34:04 ERROR juju.worker.uniter.operation runhook.go:107 hook "config-changed" failed: exit status 1

[Xav Paice (xavpaice)]

There is indeed a duplicate row - what I found is that the address for prometheus had changed from IP address to name, and because the entire row doesn't match the charm tries to add a fresh row with the same id.

cur.execute('DELETE from DATA_SOURCE where url="http://n.n.n.n:9090"')

[Junien F (axino)]

Got hit by this bug today, except the data in the DB was an FQDN, and the charm wanted to insert an IP address. Worked around by updating the rows manually with the IP address.

[Peter Jose De Sousa (pjds)]

Adding notes on how to access the grafana DB:
cd /var/lib/grafana/
sudo apt install sqlite3 -y
sqlite3 grafana.db
SQLite version 3.22.0 2018-01-22 18:45:57
Enter ".help" for usage hints.
sqlite> .tables
alert dashboard_tag server_lock
alert_notification dashboard_version session
alert_notification_state data_source short_url
alert_rule_tag login_attempt star
annotation migration_log tag
annotation_tag org team
api_key org_user team_member
cache_data playlist temp_user
dashboard playlist_item test_data
dashboard_acl plugin_setting user
dashboard_provisioning preferences user_auth
dashboard_snapshot quota user_auth_token
sqlite> select * from data_source;
.... table output

Run SQL commands to remove the duplicated row.

Resolve the juju unit using juju prometheus/unit_num

Cheers,
Peter