### Author:  [@merefield](https://meta.discourse.org/u/merefield)
#### Post date: [August 28, 2020, 6:38pm UTC](https://meta.discourse.org/t/duplicate-data-explorer-key-blocking-rebuild/162375/1 "2020-08-28T18:38:51Z")
Hey all,
Rare I ask for help, but I’m puzzled about how to resolve this.
Just rebuilt my site and getting this:
```plaintext
discourse-data-explorer is already at latest compatible version
I, [2020-08-28T17:53:57.578920 #1] INFO -- : > cd /var/www/discourse && su discourse -c 'bundle exec rake db:migrate'
2020-08-28 17:54:07.380 UTC [3060] discourse@discourse ERROR: duplicate key value violates unique constraint "data_explorer_queries_pkey"
2020-08-28 17:54:07.380 UTC [3060] discourse@discourse DETAIL: Key (id)=(-7) already exists.
2020-08-28 17:54:07.380 UTC [3060] discourse@discourse STATEMENT: INSERT INTO data_explorer_queries(id, name, description, sql, user_id, last_run_at, hidden, created_at, updated_at)
SELECT
(replace(key, 'q:',''))::integer,
value::json->>'name',
value::json->>'description',
value::json->>'sql',
CASE WHEN (value::json->'created_by')::text = 'null' THEN
null
WHEN (value::json->'created_by')::text = '""' THEN
null
WHEN (value::jsonb ? 'created_by') THEN
(value::json->>'created_by')::integer
ELSE
null
END,
CASE WHEN (value::json->'last_run_at')::text = 'null' THEN
null
WHEN (value::json->'last_run_at')::text = '""' THEN
null
ELSE
(value::json->'last_run_at')::text::timestamptz
END,
CASE WHEN (value::json->'hidden')::text = 'null' THEN
false
WHEN (value::jsonb ? 'hidden') THEN
(value::json->'hidden')::text::boolean
ELSE
false
END,
'2020-08-28 17:54:07.368132',
'2020-08-28 17:54:07.368132'
FROM plugin_store_rows
WHERE plugin_name = 'discourse-data-explorer' AND type_name = 'JSON' AND (replace(key, 'q:',''))::integer < 0
rake aborted!
StandardError: An error has occurred, this and all later migrations canceled:
ERROR: duplicate key value violates unique constraint "data_explorer_queries_pkey"
```
The important part being `ERROR: duplicate key value violates unique constraint "data_explorer_queries_pkey"`
I’ve found the query with the id -7 on the rails console, but it seems like one of the built in queries.
```plaintext
[5] pry(main)> DataExplorer::Query.find(-7)
=> #= CURRENT_DATE - INTERVAL '6 month'\nAND NOT u.admin\nAND u.active\nGROUP BY user_id,\n u.views\nHAVING count(p.id) > 50\nORDER BY sum(p.score) / count(p) DESC\nLIMIT 50">
```
I’ve also got into the plsql prompt but not sure - should I be removing a built in query to allow it to insert a new one?
---
### Author:  [@Falco](https://meta.discourse.org/u/Falco)
#### Post date: [August 28, 2020, 6:41pm UTC](https://meta.discourse.org/t/duplicate-data-explorer-key-blocking-rebuild/162375/2 "2020-08-28T18:41:19Z")
Drop into psql, and run:
```plaintext
SELECT
(replace(key, 'q:',''))::integer,
value::json->>'name',
value::json->>'description',
value::json->>'sql',
CASE WHEN (value::json->'created_by')::text = 'null' THEN
null
WHEN (value::json->'created_by')::text = '""' THEN
null
WHEN (value::jsonb ? 'created_by') THEN
(value::json->>'created_by')::integer
ELSE
null
END,
CASE WHEN (value::json->'last_run_at')::text = 'null' THEN
null
WHEN (value::json->'last_run_at')::text = '""' THEN
null
ELSE
(value::json->'last_run_at')::text::timestamptz
END,
CASE WHEN (value::json->'hidden')::text = 'null' THEN
false
WHEN (value::jsonb ? 'hidden') THEN
(value::json->'hidden')::text::boolean
ELSE
false
END,
'2020-08-28 17:54:07.368132',
'2020-08-28 17:54:07.368132'
FROM plugin_store_rows
WHERE plugin_name = 'discourse-data-explorer' AND type_name = 'JSON' AND (replace(key, 'q:',''))::integer < 0;
```
---
### Author:  [@merefield](https://meta.discourse.org/u/merefield)
#### Post date: [August 28, 2020, 6:45pm UTC](https://meta.discourse.org/t/duplicate-data-explorer-key-blocking-rebuild/162375/3 "2020-08-28T18:45:38Z")
That brings back 2 rows which look identical. “Top 50 Quality Users”
Weirdly if I do:
```plaintext
[3] pry(main)> PluginStoreRow.where(plugin_name: 'discourse-data-explorer', key: "q:-7")
=> [#]
```
It looks like I have just one row with id -7?
---
### Author:  [@merefield](https://meta.discourse.org/u/merefield)
#### Post date: [August 28, 2020, 7:04pm UTC](https://meta.discourse.org/t/duplicate-data-explorer-key-blocking-rebuild/162375/4 "2020-08-28T19:04:52Z")
OK if I simplify this to:
`select * from plugin_store_rows WHERE plugin_name = 'discourse-data-explorer' AND type_name = 'JSON' AND (replace(key, 'q:',''))::integer < 0;`
That brings back 2 identical rows (bar ‘id’).
I guess I should be brave and delete one!
PS Done, deleted the newer record - rebuilding
(Used `DELETE from plugin_store_rows WHERE id = $$$the duplicate records id$$$;`)
MIGRATION NOW WORKED!
Thanks for push @Falco
---
### Author:  [@Falco](https://meta.discourse.org/u/Falco)
#### Post date: [August 28, 2020, 7:13pm UTC](https://meta.discourse.org/t/duplicate-data-explorer-key-blocking-rebuild/162375/5 "2020-08-28T19:13:50Z")
That is the whole reason we are moving to dedicated tables, preventing future cases of this. Too bad you got it before 😜
---
### Author:  [@j127](https://meta.discourse.org/u/j127)
#### Post date: [September 5, 2020, 2:38am UTC](https://meta.discourse.org/t/duplicate-data-explorer-key-blocking-rebuild/162375/6 "2020-09-05T02:38:43Z")
Thanks for the detailed fix. I ran into the same problem (two duplicate `id`s), and that worked.
---
### Author:  [@system](https://meta.discourse.org/u/system)
#### Post date: [October 5, 2020, 2:39am UTC](https://meta.discourse.org/t/duplicate-data-explorer-key-blocking-rebuild/162375/7 "2020-10-05T02:39:01Z")
This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.