ruby-on-railsrubydevisedevise-confirmable

How to fix "unknown column" error in query


I'm getting this error

Mysql2::Error: Unknown column 'users.' in 'where clause': UPDATE `users` SET `confirmed_at` = '2020-02-29 19:14:22', `updated_at` = '2020-02-29 19:14:22' WHERE `users`.`` IS NULL

after I click on the link sent by Devise to my email. I have no idea how to handle this since I have no power on the query. I'm wondering if I'm forgetting something.

I overwrote the confirmation controller, but I didn't write anything inside it yet.

The flow is:

This is the Users schema:

create_table "users", id: false, options: "ENGINE=InnoDB DEFAULT CHARSET=latin1", force: :cascade do |t|
    t.integer "usercode"
    t.boolean "admin", default: false
    t.string "drivlicense_nr"
    t.string "birth_nation", limit: 2
    t.string "tpoliceman_id"
    t.string "email"
    t.string "encrypted_password"
    t.string "reset_password_token"
    t.datetime "reset_password_sent_at"
    t.datetime "remember_created_at"
    t.integer "sign_in_count", default: 0, null: false
    t.datetime "current_sign_in_at"
    t.datetime "last_sign_in_at"
    t.string "current_sign_in_ip"
    t.string "last_sign_in_ip"
    t.string "confirmation_token"
    t.datetime "confirmed_at"
    t.datetime "confirmation_sent_at"
    t.string "unconfirmed_email"
    t.string "provider"
    t.string "uid"
    t.string "refresh_token"
    t.string "token"
    t.boolean "expires"
    t.integer "expires_at"
    t.integer "failed_attempts", default: 0, null: false
    t.string "unlock_token"
    t.datetime "locked_at"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
    t.index ["confirmation_token"], name: "index_users_on_confirmation_token", unique: true
    t.index ["email"], name: "index_users_on_email", unique: true
    t.index ["reset_password_token"], name: "index_users_on_reset_password_token", unique: true
    t.index ["unlock_token"], name: "index_users_on_unlock_token", unique: true
    t.index ["usercode"], name: "index_users_on_usercode", unique: true
  end

This is the migration:

class DeviseCreateUsers < ActiveRecord::Migration[5.0]
  def change
      create_table :users, primary_key: :email, id: false do |t|
          t.integer   :usercode
          t.boolean   :admin, default: false
          t.string    :drivlicense_nr
          t.string    :birth_nation, limit: 2
          t.string    :tpoliceman_id

          ## Database authenticatable
          t.string    :email
          t.string    :encrypted_password

          ## Recoverable
          t.string    :reset_password_token
          t.datetime  :reset_password_sent_at

          ## Rememberable
          t.datetime  :remember_created_at

          ## Trackable
          t.integer   :sign_in_count, null: false, default: 0
          t.datetime  :current_sign_in_at
          t.datetime  :last_sign_in_at
          t.string    :current_sign_in_ip
          t.string    :last_sign_in_ip

          ## Confirmable
          t.string    :confirmation_token
          t.datetime  :confirmed_at
          t.datetime  :confirmation_sent_at
          t.string    :unconfirmed_email # Only if using reconfirmable
          #t.datetime  :updated_at

          ## Omniauthable
          t.string    :provider
          t.string    :uid
          t.string    :refresh_token
          t.string    :token
          t.boolean   :expires
          t.integer   :expires_at

          ## Lockable
          t.integer   :failed_attempts,                      null: false, default: 0 # Only if lock strategy is :failed_attempts
          t.string    :unlock_token # Only if unlock strategy is :email or :both
          t.datetime  :locked_at

          t.timestamps null: false
      end

      add_index :users, :usercode,             unique: true
      add_index :users, :email,                unique: true
      add_index :users, :reset_password_token, unique: true
      add_index :users, :confirmation_token,   unique: true
      add_index :users, :unlock_token,         unique: true
      #add_index :users, %i[drivlicense_nr birth_nation], name: 'index_users_on_person'#, unique: true
      # validates :drivlicense_nr, uniqueness: { scope: :birth_nation }
  end
end

This is the whole error printed in the console:

Started GET "/users/confirmation?confirmation_token=vS2uK-SfvxW9XhrTMULA" for ::1 at 2020-02-29 19:24:17 +0100
Processing by Users::ConfirmationsController#show as HTML
  Parameters: {"confirmation_token"=>"vS2uK-SfvxW9XhrTMULA"}
  User Load (0.5ms)  SELECT  `users`.* FROM `users` WHERE `users`.`confirmation_token` = 'vS2uK-SfvxW9XhrTMULA' LIMIT 1
  ↳ /Users/mac/.rvm/gems/ruby-2.4.1/gems/activerecord-5.2.4.1/lib/active_record/log_subscriber.rb:98
   (0.7ms)  BEGIN
  ↳ /Users/mac/.rvm/gems/ruby-2.4.1/gems/activerecord-5.2.4.1/lib/active_record/log_subscriber.rb:98
  User Update (2.4ms)  UPDATE `users` SET `confirmed_at` = '2020-02-29 19:24:17', `updated_at` = '2020-02-29 19:24:17' WHERE `users`.`` IS NULL
  ↳ /Users/mac/.rvm/gems/ruby-2.4.1/gems/activerecord-5.2.4.1/lib/active_record/log_subscriber.rb:98
   (0.6ms)  ROLLBACK
  ↳ /Users/mac/.rvm/gems/ruby-2.4.1/gems/activerecord-5.2.4.1/lib/active_record/log_subscriber.rb:98
Completed 500 Internal Server Error in 14ms (ActiveRecord: 4.2ms)



ActiveRecord::StatementInvalid (Mysql2::Error: Unknown column 'users.' in 'where clause': UPDATE `users` SET `confirmed_at` = '2020-02-29 19:24:17', `updated_at` = '2020-02-29 19:24:17' WHERE `users`.`` IS NULL):

mysql2 (0.5.3) lib/mysql2/client.rb:131:in `_query'
mysql2 (0.5.3) lib/mysql2/client.rb:131:in `block in query'
mysql2 (0.5.3) lib/mysql2/client.rb:130:in `handle_interrupt'
mysql2 (0.5.3) lib/mysql2/client.rb:130:in `query'
activerecord (5.2.4.1) lib/active_record/connection_adapters/abstract_mysql_adapter.rb:187:in `block (2 levels) in execute'
activesupport (5.2.4.1) lib/active_support/dependencies/interlock.rb:48:in `block in permit_concurrent_loads'
activesupport (5.2.4.1) lib/active_support/concurrency/share_lock.rb:187:in `yield_shares'
activesupport (5.2.4.1) lib/active_support/dependencies/interlock.rb:47:in `permit_concurrent_loads'
activerecord (5.2.4.1) lib/active_record/connection_adapters/abstract_mysql_adapter.rb:186:in `block in execute'
activerecord (5.2.4.1) lib/active_record/connection_adapters/abstract_adapter.rb:581:in `block (2 levels) in log'
/Users/mac/.rvm/rubies/ruby-2.4.1/lib/ruby/2.4.0/monitor.rb:214:in `mon_synchronize'
activerecord (5.2.4.1) lib/active_record/connection_adapters/abstract_adapter.rb:580:in `block in log'
activesupport (5.2.4.1) lib/active_support/notifications/instrumenter.rb:23:in `instrument'
activerecord (5.2.4.1) lib/active_record/connection_adapters/abstract_adapter.rb:571:in `log'
activerecord (5.2.4.1) lib/active_record/connection_adapters/abstract_mysql_adapter.rb:185:in `execute'
activerecord (5.2.4.1) lib/active_record/connection_adapters/mysql/database_statements.rb:28:in `execute'
activerecord (5.2.4.1) lib/active_record/connection_adapters/abstract_mysql_adapter.rb:196:in `execute_and_free'
activerecord (5.2.4.1) lib/active_record/connection_adapters/mysql/database_statements.rb:45:in `exec_delete'
activerecord (5.2.4.1) lib/active_record/connection_adapters/abstract/database_statements.rb:170:in `update'
activerecord (5.2.4.1) lib/active_record/connection_adapters/abstract/query_cache.rb:21:in `update'
activerecord (5.2.4.1) lib/active_record/persistence.rb:197:in `_update_record'
activerecord (5.2.4.1) lib/active_record/persistence.rb:696:in `_update_row'
activerecord (5.2.4.1) lib/active_record/locking/optimistic.rb:80:in `_update_row'
activerecord (5.2.4.1) lib/active_record/persistence.rb:719:in `_update_record'
activerecord (5.2.4.1) lib/active_record/attribute_methods/dirty.rb:134:in `_update_record'
activerecord (5.2.4.1) lib/active_record/callbacks.rb:350:in `block in _update_record'
activesupport (5.2.4.1) lib/active_support/callbacks.rb:132:in `run_callbacks'
activesupport (5.2.4.1) lib/active_support/callbacks.rb:816:in `_run_update_callbacks'
activerecord (5.2.4.1) lib/active_record/callbacks.rb:350:in `_update_record'
activerecord (5.2.4.1) lib/active_record/timestamp.rb:114:in `_update_record'
activerecord (5.2.4.1) lib/active_record/persistence.rb:705:in `create_or_update'
activerecord (5.2.4.1) lib/active_record/callbacks.rb:342:in `block in create_or_update'
activesupport (5.2.4.1) lib/active_support/callbacks.rb:98:in `run_callbacks'
activesupport (5.2.4.1) lib/active_support/callbacks.rb:816:in `_run_save_callbacks'
activerecord (5.2.4.1) lib/active_record/callbacks.rb:342:in `create_or_update'
activerecord (5.2.4.1) lib/active_record/persistence.rb:275:in `save'
activerecord (5.2.4.1) lib/active_record/validations.rb:46:in `save'
activerecord (5.2.4.1) lib/active_record/transactions.rb:310:in `block (2 levels) in save'
activerecord (5.2.4.1) lib/active_record/transactions.rb:387:in `block in with_transaction_returning_status'
activerecord (5.2.4.1) lib/active_record/connection_adapters/abstract/database_statements.rb:267:in `block in transaction'
activerecord (5.2.4.1) lib/active_record/connection_adapters/abstract/transaction.rb:239:in `block in within_new_transaction'
/Users/mac/.rvm/rubies/ruby-2.4.1/lib/ruby/2.4.0/monitor.rb:214:in `mon_synchronize'
activerecord (5.2.4.1) lib/active_record/connection_adapters/abstract/transaction.rb:236:in `within_new_transaction'
activerecord (5.2.4.1) lib/active_record/connection_adapters/abstract/database_statements.rb:267:in `transaction'
activerecord (5.2.4.1) lib/active_record/transactions.rb:212:in `transaction'
activerecord (5.2.4.1) lib/active_record/transactions.rb:385:in `with_transaction_returning_status'
activerecord (5.2.4.1) lib/active_record/transactions.rb:310:in `block in save'
activerecord (5.2.4.1) lib/active_record/transactions.rb:325:in `rollback_active_record_state!'
activerecord (5.2.4.1) lib/active_record/transactions.rb:309:in `save'
activerecord (5.2.4.1) lib/active_record/suppressor.rb:44:in `save'
devise (4.7.1) lib/devise/models/confirmable.rb:97:in `block in confirm'
devise (4.7.1) lib/devise/models/confirmable.rb:239:in `pending_any_confirmation'
devise (4.7.1) lib/devise/models/confirmable.rb:80:in `confirm'
devise (4.7.1) lib/devise/models/confirmable.rb:373:in `confirm_by_token'
devise (4.7.1) app/controllers/devise/confirmations_controller.rb:23:in `show'
actionpack (5.2.4.1) lib/action_controller/metal/basic_implicit_render.rb:6:in `send_action'
actionpack (5.2.4.1) lib/abstract_controller/base.rb:194:in `process_action'
actionpack (5.2.4.1) lib/action_controller/metal/rendering.rb:30:in `process_action'
actionpack (5.2.4.1) lib/abstract_controller/callbacks.rb:42:in `block in process_action'
activesupport (5.2.4.1) lib/active_support/callbacks.rb:132:in `run_callbacks'
actionpack (5.2.4.1) lib/abstract_controller/callbacks.rb:41:in `process_action'
actionpack (5.2.4.1) lib/action_controller/metal/rescue.rb:22:in `process_action'
actionpack (5.2.4.1) lib/action_controller/metal/instrumentation.rb:34:in `block in process_action'
activesupport (5.2.4.1) lib/active_support/notifications.rb:168:in `block in instrument'
activesupport (5.2.4.1) lib/active_support/notifications/instrumenter.rb:23:in `instrument'
activesupport (5.2.4.1) lib/active_support/notifications.rb:168:in `instrument'
actionpack (5.2.4.1) lib/action_controller/metal/instrumentation.rb:32:in `process_action'
actionpack (5.2.4.1) lib/action_controller/metal/params_wrapper.rb:256:in `process_action'
activerecord (5.2.4.1) lib/active_record/railties/controller_runtime.rb:24:in `process_action'
actionpack (5.2.4.1) lib/abstract_controller/base.rb:134:in `process'
actionview (5.2.4.1) lib/action_view/rendering.rb:32:in `process'
actionpack (5.2.4.1) lib/action_controller/metal.rb:191:in `dispatch'
actionpack (5.2.4.1) lib/action_controller/metal.rb:252:in `dispatch'
actionpack (5.2.4.1) lib/action_dispatch/routing/route_set.rb:52:in `dispatch'
actionpack (5.2.4.1) lib/action_dispatch/routing/route_set.rb:34:in `serve'
actionpack (5.2.4.1) lib/action_dispatch/routing/mapper.rb:18:in `block in <class:Constraints>'
actionpack (5.2.4.1) lib/action_dispatch/routing/mapper.rb:48:in `serve'
actionpack (5.2.4.1) lib/action_dispatch/journey/router.rb:52:in `block in serve'
actionpack (5.2.4.1) lib/action_dispatch/journey/router.rb:35:in `each'
actionpack (5.2.4.1) lib/action_dispatch/journey/router.rb:35:in `serve'
actionpack (5.2.4.1) lib/action_dispatch/routing/route_set.rb:840:in `call'
omniauth (1.9.0) lib/omniauth/strategy.rb:192:in `call!'
omniauth (1.9.0) lib/omniauth/strategy.rb:169:in `call'
omniauth (1.9.0) lib/omniauth/strategy.rb:192:in `call!'
omniauth (1.9.0) lib/omniauth/strategy.rb:169:in `call'
omniauth (1.9.0) lib/omniauth/strategy.rb:192:in `call!'
omniauth (1.9.0) lib/omniauth/strategy.rb:169:in `call'
warden (1.2.8) lib/warden/manager.rb:36:in `block in call'
warden (1.2.8) lib/warden/manager.rb:34:in `catch'
warden (1.2.8) lib/warden/manager.rb:34:in `call'
rack (2.2.2) lib/rack/tempfile_reaper.rb:15:in `call'
rack (2.2.2) lib/rack/etag.rb:27:in `call'
rack (2.2.2) lib/rack/conditional_get.rb:27:in `call'
rack (2.2.2) lib/rack/head.rb:12:in `call'
actionpack (5.2.4.1) lib/action_dispatch/http/content_security_policy.rb:18:in `call'
rack (2.2.2) lib/rack/session/abstract/id.rb:266:in `context'
rack (2.2.2) lib/rack/session/abstract/id.rb:260:in `call'
actionpack (5.2.4.1) lib/action_dispatch/middleware/cookies.rb:670:in `call'
activerecord (5.2.4.1) lib/active_record/migration.rb:559:in `call'
actionpack (5.2.4.1) lib/action_dispatch/middleware/callbacks.rb:28:in `block in call'
activesupport (5.2.4.1) lib/active_support/callbacks.rb:98:in `run_callbacks'
actionpack (5.2.4.1) lib/action_dispatch/middleware/callbacks.rb:26:in `call'
actionpack (5.2.4.1) lib/action_dispatch/middleware/executor.rb:14:in `call'
actionpack (5.2.4.1) lib/action_dispatch/middleware/debug_exceptions.rb:61:in `call'
web-console (3.7.0) lib/web_console/middleware.rb:135:in `call_app'
web-console (3.7.0) lib/web_console/middleware.rb:30:in `block in call'
web-console (3.7.0) lib/web_console/middleware.rb:20:in `catch'
web-console (3.7.0) lib/web_console/middleware.rb:20:in `call'
actionpack (5.2.4.1) lib/action_dispatch/middleware/show_exceptions.rb:33:in `call'
railties (5.2.4.1) lib/rails/rack/logger.rb:38:in `call_app'
railties (5.2.4.1) lib/rails/rack/logger.rb:26:in `block in call'
activesupport (5.2.4.1) lib/active_support/tagged_logging.rb:71:in `block in tagged'
activesupport (5.2.4.1) lib/active_support/tagged_logging.rb:28:in `tagged'
activesupport (5.2.4.1) lib/active_support/tagged_logging.rb:71:in `tagged'
railties (5.2.4.1) lib/rails/rack/logger.rb:26:in `call'
sprockets-rails (3.2.1) lib/sprockets/rails/quiet_assets.rb:13:in `call'
actionpack (5.2.4.1) lib/action_dispatch/middleware/remote_ip.rb:81:in `call'
actionpack (5.2.4.1) lib/action_dispatch/middleware/request_id.rb:27:in `call'
rack (2.2.2) lib/rack/method_override.rb:24:in `call'
rack (2.2.2) lib/rack/runtime.rb:22:in `call'
activesupport (5.2.4.1) lib/active_support/cache/strategy/local_cache_middleware.rb:29:in `call'
actionpack (5.2.4.1) lib/action_dispatch/middleware/executor.rb:14:in `call'
actionpack (5.2.4.1) lib/action_dispatch/middleware/static.rb:127:in `call'
rack (2.2.2) lib/rack/sendfile.rb:110:in `call'
railties (5.2.4.1) lib/rails/engine.rb:524:in `call'
puma (3.12.3) lib/puma/configuration.rb:227:in `call'
puma (3.12.3) lib/puma/server.rb:674:in `handle_request'
puma (3.12.3) lib/puma/server.rb:476:in `process_client'
puma (3.12.3) lib/puma/server.rb:334:in `block in run'
puma (3.12.3) lib/puma/thread_pool.rb:135:in `block in spawn_thread'

Solution

  • So basically I recreated from scratch the basic flow for devise mailer. I checked what the query was looking for and I noticed it wanted users.id. By looking inside my migration I found that I put id:false because I was overwriting a primary_key.