Creating new User error after upgrade PG::UniqueViolation Duplicate Key

sso
1

I am running from my site from my local dev sever - connecting to a live staging server that discourse is hosted on.

I am using discourse_api.

I can run the client.sync_sso command from my mac terminal and it will create a user on the forum without any error. But it throws an error if the command comes from my dev server - even though the exact same info is being passed through which is confusing me.

The api should create the user if it doesn’t exist, but the forums are throwing a duplicate key error

Failed to create or lookup user: PG::UniqueViolation: ERROR:  duplicate key value violates unique constraint "user_emails_pkey"
DETAIL:  Key (id)=(14) already exists.
.    add_groups: 
admin: 
moderator: 
avatar_force_update: true
avatar_url: /images/missing_profile.png
bio: 
card_background_url: 
confirmed_2fa: 
email: 231447@test.com
external_id: 350942
groups: 
locale: 
locale_force_update: 
location: 
logout: 
name: 
no_2fa_methods: 
nonce: xxxxxxxxxxxx
profile_background_url: 
remove_groups: 
require_2fa: 
require_activation: 
return_sso_url: https://xxxx.com/session/sso_login
suppress_welcome_message: 
title: 
username: 
website:     /var/www/discourse/vendor/bundle/ruby/3.1.0/gems/rack-mini-profiler-3.0.0/lib/patches/db/pg.rb:69:in `exec_params'
/var/www/discourse/vendor/bundle/ruby/3.1.0/gems/rack-mini-profiler-3.0.0/lib/patches/db/pg.rb:69:in `exec_params'
/var/www/discourse/vendor/bundle/ruby/3.1.0/gems/activerecord-7.0.4/lib/active_record/connection_adapters/postgresql_adapter.rb:768:in `block (2 levels) in exec_no_cache'
/var/www/discourse/vendor/bundle/ruby/3.1.0/gems/activesupport-7.0.4/lib/active_support/concurrency/share_lock.rb:187:in `yield_shares'
/var/www/discourse/vendor/bundle/ruby/3.1.0/gems/activesupport-7.0.4/lib/active_support/dependencies/interlock.rb:41:in `permit_concurrent_loads'
/var/www/discourse/vendor/bundle/ruby/3.1.0/gems/activerecord-7.0.4/lib/active_record/connection_adapters/postgresql_adapter.rb:767:in `block in exec_no_cache'
/var/www/discourse/vendor/bundle/ruby/3.1.0/gems/activesupport-7.0.4/lib/active_support/concurrency/load_interlock_aware_monitor.rb:25:in `handle_interrupt'
/var/www/discourse/vendor/bundle/ruby/3.1.0/gems/activesupport-7.0.4/lib/active_support/concurrency/load_interlock_aware_monitor.rb:25:in `block in synchronize'
/var/www/discourse/vendor/bundle/ruby/3.1.0/gems/activesupport-7.0.4/lib/active_support/concurrency/load_interlock_aware_monitor.rb:21:in `handle_interr...

Backtrace

/var/www/discourse/app/controllers/session_controller.rb:268:in `rescue in sso_login'
/var/www/discourse/app/controllers/session_controller.rb:160:in `sso_login'
actionpack-7.0.4/lib/action_controller/metal/basic_implicit_render.rb:6:in `send_action'
actionpack-7.0.4/lib/abstract_controller/base.rb:215:in `process_action'
actionpack-7.0.4/lib/action_controller/metal/rendering.rb:53:in `process_action'
actionpack-7.0.4/lib/abstract_controller/callbacks.rb:234:in `block in process_action'
activesupport-7.0.4/lib/active_support/callbacks.rb:118:in `block in run_callbacks'
/var/www/discourse/app/controllers/application_controller.rb:414:in `block in with_resolved_locale'
i18n-1.12.0/lib/i18n.rb:322:in `with_locale'
/var/www/discourse/app/controllers/application_controller.rb:414:in `with_resolved_locale'
activesupport-7.0.4/lib/active_support/callbacks.rb:127:in `block in run_callbacks'
activesupport-7.0.4/lib/active_support/callbacks.rb:138:in `run_callbacks'
actionpack-7.0.4/lib/abstract_controller/callbacks.rb:233:in `process_action'
actionpack-7.0.4/lib/action_controller/metal/rescue.rb:22:in `process_action'
actionpack-7.0.4/lib/action_controller/metal/instrumentation.rb:67:in `block in process_action'
activesupport-7.0.4/lib/active_support/notifications.rb:206:in `block in instrument'
activesupport-7.0.4/lib/active_support/notifications/instrumenter.rb:24:in `instrument'
activesupport-7.0.4/lib/active_support/notifications.rb:206:in `instrument'
actionpack-7.0.4/lib/action_controller/metal/instrumentation.rb:66:in `process_action'
actionpack-7.0.4/lib/action_controller/metal/params_wrapper.rb:259:in `process_action'
activerecord-7.0.4/lib/active_record/railties/controller_runtime.rb:27:in `process_action'
actionpack-7.0.4/lib/abstract_controller/base.rb:151:in `process'
actionview-7.0.4/lib/action_view/rendering.rb:39:in `process'
rack-mini-profiler-3.0.0/lib/mini_profiler/profiling_methods.rb:85:in `block in profile_method'
actionpack-7.0.4/lib/action_controller/metal.rb:188:in `dispatch'
actionpack-7.0.4/lib/action_controller/metal.rb:251:in `dispatch'
actionpack-7.0.4/lib/action_dispatch/routing/route_set.rb:49:in `dispatch'
actionpack-7.0.4/lib/action_dispatch/routing/route_set.rb:32:in `serve'
actionpack-7.0.4/lib/action_dispatch/journey/router.rb:50:in `block in serve'
actionpack-7.0.4/lib/action_dispatch/journey/router.rb:32:in `each'
actionpack-7.0.4/lib/action_dispatch/journey/router.rb:32:in `serve'
actionpack-7.0.4/lib/action_dispatch/routing/route_set.rb:852:in `call'
/var/www/discourse/lib/middleware/omniauth_bypass_middleware.rb:74:in `call'
rack-2.2.6.2/lib/rack/tempfile_reaper.rb:15:in `call'
rack-2.2.6.2/lib/rack/conditional_get.rb:27:in `call'
rack-2.2.6.2/lib/rack/head.rb:12:in `call'
actionpack-7.0.4/lib/action_dispatch/http/permissions_policy.rb:38:in `call'
/var/www/discourse/lib/content_security_policy/middleware.rb:12:in `call'
/var/www/discourse/lib/middleware/anonymous_cache.rb:364:in `call'
rack-2.2.6.2/lib/rack/session/abstract/id.rb:266:in `context'
rack-2.2.6.2/lib/rack/session/abstract/id.rb:260:in `call'
actionpack-7.0.4/lib/action_dispatch/middleware/cookies.rb:696:in `call'
actionpack-7.0.4/lib/action_dispatch/middleware/callbacks.rb:27:in `block in call'
activesupport-7.0.4/lib/active_support/callbacks.rb:99:in `run_callbacks'
actionpack-7.0.4/lib/action_dispatch/middleware/callbacks.rb:26:in `call'
actionpack-7.0.4/lib/action_dispatch/middleware/debug_exceptions.rb:28:in `call'
actionpack-7.0.4/lib/action_dispatch/middleware/show_exceptions.rb:26:in `call'
logster-2.11.3/lib/logster/middleware/reporter.rb:43:in `call'
railties-7.0.4/lib/rails/rack/logger.rb:40:in `call_app'
railties-7.0.4/lib/rails/rack/logger.rb:27:in `call'
/var/www/discourse/config/initializers/100-quiet_logger.rb:20:in `call'
/var/www/discourse/config/initializers/100-silence_logger.rb:29:in `call'
actionpack-7.0.4/lib/action_dispatch/middleware/remote_ip.rb:93:in `call'
actionpack-7.0.4/lib/action_dispatch/middleware/request_id.rb:26:in `call'
/var/www/discourse/lib/middleware/enforce_hostname.rb:24:in `call'
rack-2.2.6.2/lib/rack/method_override.rb:24:in `call'
actionpack-7.0.4/lib/action_dispatch/middleware/executor.rb:14:in `call'
rack-2.2.6.2/lib/rack/sendfile.rb:110:in `call'
actionpack-7.0.4/lib/action_dispatch/middleware/host_authorization.rb:131:in `call'
rack-mini-profiler-3.0.0/lib/mini_profiler/profiler.rb:249:in `call'
message_bus-4.3.2/lib/message_bus/rack/middleware.rb:60:in `call'
/var/www/discourse/lib/middleware/request_tracker.rb:228:in `call'
railties-7.0.4/lib/rails/engine.rb:530:in `call'
railties-7.0.4/lib/rails/railtie.rb:226:in `public_send'
railties-7.0.4/lib/rails/railtie.rb:226:in `method_missing'
rack-2.2.6.2/lib/rack/urlmap.rb:74:in `block in call'
rack-2.2.6.2/lib/rack/urlmap.rb:58:in `each'
rack-2.2.6.2/lib/rack/urlmap.rb:58:in `call'
unicorn-6.1.0/lib/unicorn/http_server.rb:634:in `process_client'
unicorn-6.1.0/lib/unicorn/http_server.rb:739:in `worker_loop'
unicorn-6.1.0/lib/unicorn/http_server.rb:547:in `spawn_missing_workers'
unicorn-6.1.0/lib/unicorn/http_server.rb:143:in `start'
unicorn-6.1.0/bin/unicorn:128:in `<top (required)>'
/var/www/discourse/vendor/bundle/ruby/3.1.0/bin/unicorn:25:in `load'
/var/www/discourse/vendor/bundle/ruby/3.1.0/bin/unicorn:25:in `<main>'
2

Posting the solution here for anyone else that comes up against a similar problem

I upgraded our forums from v1.6 to 3.1 using a backup and restore on a new server
Since updating new user sign ups stopped working via using the discourse_api and SSO

the error was a
PG::UniqueViolation: ERROR: duplicate key value violates unique constraint “user_emails_pkey”
DETAIL: Key (id)=(xx) already exists.

after some digging i logged into the droplet and accessed postgres - specifically the “user_emails” table

                                          Table "public.user_emails"
      Column      |            Type             | Collation | Nullable |                 Default                 
------------------+-----------------------------+-----------+----------+-----------------------------------------
 id               | integer                     |           | not null | nextval('user_emails_id_seq'::regclass)
 user_id          | integer                     |           | not null | 
 email            | character varying(513)      |           | not null | 
 primary          | boolean                     |           | not null | false
 created_at       | timestamp without time zone |           | not null | 
 updated_at       | timestamp without time zone |           | not null | 
 normalized_email | character varying           |           |          | 
Indexes:
    "user_emails_pkey" PRIMARY KEY, btree (id)
    "index_user_emails_on_email" UNIQUE, btree (lower(email::text))
    "index_user_emails_on_normalized_email" btree (lower(normalized_email::text))
    "index_user_emails_on_user_id" btree (user_id)
    "index_user_emails_on_user_id_and_primary" UNIQUE, btree (user_id, "primary") WHERE "primary"

The id column has a “user_emails_id_seq” for selecting the next available ID to use when inserting a new user

i ran SELECT MAX(id) FROM user_emails; which returned 237023
and then SELECT nextval(‘user_emails_id_seq’); which returned 23

so the table was out of sequence.

to fix
SELECT setval(‘user_emails_id_seq’, (SELECT MAX(id) FROM user_emails)+1);

1 Like
3

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.