Dubbele data explorer sleutel blokkeert rebuild

AI-generated summary

In a Support thread titled “Duplicate data explorer key blocking rebuild,” merefield reported a migration error caused by a duplicate key violation (id = -7) in the data_explorer_queries table during a Discourse rebuild. The error stemmed from two identical plugin store rows for the built-in query “Top 50 Quality Users” with negative IDs. Falco advised inspecting the plugin_store_rows table, which merefield confirmed contained two nearly identical rows differing only by their internal id. After merefield bravely deleted the newer duplicate record using DELETE from plugin_store_rows WHERE id = $$$the duplicate records id$$$;, the migration succeeded. Falco noted this issue highlights why Discourse is moving toward dedicated tables to avoid such conflicts in the future. j127 confirmed the fix worked for them too, validating the solution.

Hey all,

Rare I ask for help, but I’m puzzled about how to resolve this.

Just rebuilt my site and getting this:

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.

[5] pry(main)> DataExplorer::Query.find(-7)
=> #<DataExplorer::Query:0x000055e44bbb4038
 @created_by="-1",
 @description="based on post score calculated using reply count, likes, incoming links, bookmarks, time spent and read count.",
 @group_ids=[],
 @hidden=nil,
 @id=-7,
 @last_run_at="2020-01-03T18:06:31.966+00:00",
 @name="Top 50 Quality Users",
 @sql=
  "SELECT sum(p.score) / count(p) AS \"average score per post\",\n    count(p.id) AS post_count,\n    p.user_id\nFROM posts p\nJOIN users u ON u.id = p.user_id\nWHERE p.created_at >= 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?

Drop into psql, and run:

     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;

That brings back 2 rows which look identical. “Top 50 Quality Users”

Weirdly if I do:

[3] pry(main)> PluginStoreRow.where(plugin_name: 'discourse-data-explorer', key: "q:-7")
=> [#<PluginStoreRow:0x000055e280408bf8
  id: 1286,
  plugin_name: "discourse-data-explorer",
  key: "q:-7",
  type_name: "JSON",
  value:
   "{\"id\":-7,\"name\":\"Top 50 Quality Users\",\"description\":\"based on post score calculated using reply count, likes, incoming links, bookmarks, time spent and read count.\",\"sql\":\"SELECT sum(p.score) / count(p) AS \\\"average score per post\\\",\\n    count(p.id) AS post_count,\\n    p.user_id\\nFROM posts p\\nJOIN users u ON u.id = p.user_id\\nWHERE p.created_at \\u003e= CURRENT_DATE - INTERVAL '6 month'\\nAND NOT u.admin\\nAND u.active\\nGROUP BY user_id,\\n    u.views\\nHAVING count(p.id)
 \\u003e 50\\nORDER BY sum(p.score) / count(p) DESC\\nLIMIT 50\\n\",\"created_by\":\"-1\",\"created_at\":null,\"group_ids\":[],\"last_run_at\":\"2020-01-03T18:06:31.966+00:00\"}">]

It looks like I have just one row with id -7?

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

That is the whole reason we are moving to dedicated tables, preventing future cases of this. Too bad you got it before :winking_face_with_tongue:

Thanks for the detailed fix. I ran into the same problem (two duplicate ids), and that worked.