rails-sqlserver/activerecord-sqlserver-adapter

TinyTds::Error: There is already an object named 'guest' in the database.

jvon1904 opened this issue · 0 comments

jvon1904 commented

Issue

After release 7.1.5 which included #1201, you will receive an error when loading the schema because of the create_schema directive automatically added to db/schema.rb:

create_schema 'guest'

The "guest" schema is automatically created by MSSQL. It's one of the four schemas that can't be dropped.

As such, it should be excluded along with db_%, sys and INFORMATION_SCHEMA from the SQL statement under #schema_names in lib/active_record/connection_adapters/sqlserver/schema_statements.rb:403.

# Returns an array of schema names.
        def schema_names
          sql = <<~SQL.squish
           SELECT name
            FROM sys.schemas
            WHERE
            name NOT LIKE 'db_%' AND
-          name NOT IN ('INFORMATION_SCHEMA', 'sys')
+          name NOT IN ('INFORMATION_SCHEMA', 'sys', 'guest')
          SQL

          query_values(sql, "SCHEMA")
        end

How to reproduce

First migrate the database to update the schema.rb file.

$ bin/rails db:migrate

Next, load the schema in the Test environment, or run tests if ActiveRecord::Migration.maintain_test_schema! is set.

$ RAILS_ENV=test bin/rails db:test:load_schema --trace                                                                          19ms
** Invoke db:test:load_schema (first_time)
** Invoke db:test:purge (first_time)
** Invoke db:load_config (first_time)
** Invoke environment (first_time)
** Execute environment
** Execute db:load_config
** Invoke db:check_protected_environments (first_time)
** Invoke db:load_config
** Execute db:check_protected_environments
** Execute db:test:purge
** Execute db:test:load_schema
bin/rails aborted!
ActiveRecord::StatementInvalid: TinyTds::Error: There is already an object named 'guest' in the database. (ActiveRecord::StatementInvalid)
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/activerecord-sqlserver-adapter-7.1.5/lib/active_record/connection_adapters/sqlserver/database_statements.rb:445:in `do'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/activerecord-sqlserver-adapter-7.1.5/lib/active_record/connection_adapters/sqlserver/database_statements.rb:445:in `internal_raw_execute'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/activerecord-sqlserver-adapter-7.1.5/lib/active_record/connection_adapters/sqlserver/database_statements.rb:24:in `block (2 levels) in raw_execute'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/activerecord-7.1.3.4/lib/active_record/connection_adapters/abstract_adapter.rb:1028:in `block in with_raw_connection'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/activesupport-7.1.3.4/lib/active_support/concurrency/null_lock.rb:9:in `synchronize'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/activerecord-7.1.3.4/lib/active_record/connection_adapters/abstract_adapter.rb:1000:in `with_raw_connection'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/activerecord-sqlserver-adapter-7.1.5/lib/active_record/connection_adapters/sqlserver/database_statements.rb:20:in `block in raw_execute'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/activesupport-7.1.3.4/lib/active_support/notifications/instrumenter.rb:58:in `instrument'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/activerecord-7.1.3.4/lib/active_record/connection_adapters/abstract_adapter.rb:1143:in `log'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/activerecord-sqlserver-adapter-7.1.5/lib/active_record/connection_adapters/sqlserver/database_statements.rb:19:in `raw_execute'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/activerecord-7.1.3.4/lib/active_record/connection_adapters/abstract/database_statements.rb:521:in `internal_execute'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/activerecord-7.1.3.4/lib/active_record/connection_adapters/abstract/database_statements.rb:131:in `execute'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/activerecord-7.1.3.4/lib/active_record/connection_adapters/abstract/query_cache.rb:25:in `execute'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/activerecord-sqlserver-adapter-7.1.5/lib/active_record/connection_adapters/sqlserver/schema_statements.rb:385:in `create_schema'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/activerecord-7.1.3.4/lib/active_record/migration/default_strategy.rb:10:in `method_missing'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/activerecord-7.1.3.4/lib/active_record/migration.rb:1047:in `block in method_missing'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/activerecord-7.1.3.4/lib/active_record/migration.rb:1017:in `block in say_with_time'
/Users/jvonbergen/.rvm/rubies/ruby-3.3.1/lib/ruby/3.3.0/benchmark.rb:298:in `measure'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/activerecord-7.1.3.4/lib/active_record/migration.rb:1017:in `say_with_time'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/activerecord-7.1.3.4/lib/active_record/migration.rb:1036:in `method_missing'
/Users/jvonbergen/Projects/apdprototype/db/schema.rb:14:in `block in <main>'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/activerecord-7.1.3.4/lib/active_record/schema.rb:55:in `instance_eval'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/activerecord-7.1.3.4/lib/active_record/schema.rb:55:in `define'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/activerecord-7.1.3.4/lib/active_record/schema.rb:50:in `define'
/Users/jvonbergen/Projects/apdprototype/db/schema.rb:13:in `<main>'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/activerecord-7.1.3.4/lib/active_record/tasks/database_tasks.rb:358:in `load'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/activerecord-7.1.3.4/lib/active_record/tasks/database_tasks.rb:358:in `load_schema'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/activerecord-7.1.3.4/lib/active_record/railties/databases.rake:554:in `block (4 levels) in <main>'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/activerecord-7.1.3.4/lib/active_record/tasks/database_tasks.rb:501:in `block in with_temporary_connection'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/activerecord-7.1.3.4/lib/active_record/tasks/database_tasks.rb:518:in `with_temporary_pool'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/activerecord-7.1.3.4/lib/active_record/tasks/database_tasks.rb:500:in `with_temporary_connection'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/activerecord-7.1.3.4/lib/active_record/tasks/database_tasks.rb:494:in `block in with_temporary_connection_for_each'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/activerecord-7.1.3.4/lib/active_record/tasks/database_tasks.rb:493:in `each'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/activerecord-7.1.3.4/lib/active_record/tasks/database_tasks.rb:493:in `with_temporary_connection_for_each'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/activerecord-7.1.3.4/lib/active_record/railties/databases.rake:550:in `block (3 levels) in <main>'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/rake-13.2.1/lib/rake/task.rb:281:in `block in execute'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/rake-13.2.1/lib/rake/task.rb:281:in `each'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/rake-13.2.1/lib/rake/task.rb:281:in `execute'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/rake-13.2.1/lib/rake/task.rb:219:in `block in invoke_with_call_chain'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/rake-13.2.1/lib/rake/task.rb:199:in `synchronize'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/rake-13.2.1/lib/rake/task.rb:199:in `invoke_with_call_chain'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/rake-13.2.1/lib/rake/task.rb:188:in `invoke'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/rake-13.2.1/lib/rake/application.rb:188:in `invoke_task'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/rake-13.2.1/lib/rake/application.rb:138:in `block (2 levels) in top_level'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/rake-13.2.1/lib/rake/application.rb:138:in `each'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/rake-13.2.1/lib/rake/application.rb:138:in `block in top_level'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/rake-13.2.1/lib/rake/application.rb:147:in `run_with_threads'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/rake-13.2.1/lib/rake/application.rb:132:in `top_level'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/railties-7.1.3.4/lib/rails/commands/rake/rake_command.rb:27:in `block (2 levels) in perform'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/rake-13.2.1/lib/rake/application.rb:214:in `standard_exception_handling'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/railties-7.1.3.4/lib/rails/commands/rake/rake_command.rb:27:in `block in perform'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/railties-7.1.3.4/lib/rails/commands/rake/rake_command.rb:44:in `block in with_rake'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/rake-13.2.1/lib/rake/rake_module.rb:59:in `with_application'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/railties-7.1.3.4/lib/rails/commands/rake/rake_command.rb:41:in `with_rake'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/railties-7.1.3.4/lib/rails/commands/rake/rake_command.rb:20:in `perform'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/railties-7.1.3.4/lib/rails/command.rb:156:in `invoke_rake'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/railties-7.1.3.4/lib/rails/command.rb:73:in `block in invoke'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/railties-7.1.3.4/lib/rails/command.rb:149:in `with_argv'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/railties-7.1.3.4/lib/rails/command.rb:69:in `invoke'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/railties-7.1.3.4/lib/rails/commands.rb:18:in `<main>'
/Users/jvonbergen/.rvm/rubies/ruby-3.3.1/lib/ruby/3.3.0/bundled_gems.rb:74:in `require'
/Users/jvonbergen/.rvm/rubies/ruby-3.3.1/lib/ruby/3.3.0/bundled_gems.rb:74:in `block (2 levels) in replace_require'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/bootsnap-1.18.3/lib/bootsnap/load_path_cache/core_ext/kernel_require.rb:30:in `require'
bin/rails:4:in `<main>'

Caused by:
TinyTds::Error: There is already an object named 'guest' in the database. (TinyTds::Error)
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/activerecord-sqlserver-adapter-7.1.5/lib/active_record/connection_adapters/sqlserver/database_statements.rb:445:in `do'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/activerecord-sqlserver-adapter-7.1.5/lib/active_record/connection_adapters/sqlserver/database_statements.rb:445:in `internal_raw_execute'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/activerecord-sqlserver-adapter-7.1.5/lib/active_record/connection_adapters/sqlserver/database_statements.rb:24:in `block (2 levels) in raw_execute'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/activerecord-7.1.3.4/lib/active_record/connection_adapters/abstract_adapter.rb:1028:in `block in with_raw_connection'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/activesupport-7.1.3.4/lib/active_support/concurrency/null_lock.rb:9:in `synchronize'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/activerecord-7.1.3.4/lib/active_record/connection_adapters/abstract_adapter.rb:1000:in `with_raw_connection'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/activerecord-sqlserver-adapter-7.1.5/lib/active_record/connection_adapters/sqlserver/database_statements.rb:20:in `block in raw_execute'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/activesupport-7.1.3.4/lib/active_support/notifications/instrumenter.rb:58:in `instrument'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/activerecord-7.1.3.4/lib/active_record/connection_adapters/abstract_adapter.rb:1143:in `log'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/activerecord-sqlserver-adapter-7.1.5/lib/active_record/connection_adapters/sqlserver/database_statements.rb:19:in `raw_execute'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/activerecord-7.1.3.4/lib/active_record/connection_adapters/abstract/database_statements.rb:521:in `internal_execute'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/activerecord-7.1.3.4/lib/active_record/connection_adapters/abstract/database_statements.rb:131:in `execute'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/activerecord-7.1.3.4/lib/active_record/connection_adapters/abstract/query_cache.rb:25:in `execute'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/activerecord-sqlserver-adapter-7.1.5/lib/active_record/connection_adapters/sqlserver/schema_statements.rb:385:in `create_schema'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/activerecord-7.1.3.4/lib/active_record/migration/default_strategy.rb:10:in `method_missing'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/activerecord-7.1.3.4/lib/active_record/migration.rb:1047:in `block in method_missing'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/activerecord-7.1.3.4/lib/active_record/migration.rb:1017:in `block in say_with_time'
/Users/jvonbergen/.rvm/rubies/ruby-3.3.1/lib/ruby/3.3.0/benchmark.rb:298:in `measure'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/activerecord-7.1.3.4/lib/active_record/migration.rb:1017:in `say_with_time'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/activerecord-7.1.3.4/lib/active_record/migration.rb:1036:in `method_missing'
/Users/jvonbergen/Projects/apdprototype/db/schema.rb:14:in `block in <main>'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/activerecord-7.1.3.4/lib/active_record/schema.rb:55:in `instance_eval'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/activerecord-7.1.3.4/lib/active_record/schema.rb:55:in `define'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/activerecord-7.1.3.4/lib/active_record/schema.rb:50:in `define'
/Users/jvonbergen/Projects/apdprototype/db/schema.rb:13:in `<main>'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/activerecord-7.1.3.4/lib/active_record/tasks/database_tasks.rb:358:in `load'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/activerecord-7.1.3.4/lib/active_record/tasks/database_tasks.rb:358:in `load_schema'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/activerecord-7.1.3.4/lib/active_record/railties/databases.rake:554:in `block (4 levels) in <main>'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/activerecord-7.1.3.4/lib/active_record/tasks/database_tasks.rb:501:in `block in with_temporary_connection'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/activerecord-7.1.3.4/lib/active_record/tasks/database_tasks.rb:518:in `with_temporary_pool'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/activerecord-7.1.3.4/lib/active_record/tasks/database_tasks.rb:500:in `with_temporary_connection'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/activerecord-7.1.3.4/lib/active_record/tasks/database_tasks.rb:494:in `block in with_temporary_connection_for_each'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/activerecord-7.1.3.4/lib/active_record/tasks/database_tasks.rb:493:in `each'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/activerecord-7.1.3.4/lib/active_record/tasks/database_tasks.rb:493:in `with_temporary_connection_for_each'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/activerecord-7.1.3.4/lib/active_record/railties/databases.rake:550:in `block (3 levels) in <main>'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/rake-13.2.1/lib/rake/task.rb:281:in `block in execute'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/rake-13.2.1/lib/rake/task.rb:281:in `each'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/rake-13.2.1/lib/rake/task.rb:281:in `execute'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/rake-13.2.1/lib/rake/task.rb:219:in `block in invoke_with_call_chain'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/rake-13.2.1/lib/rake/task.rb:199:in `synchronize'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/rake-13.2.1/lib/rake/task.rb:199:in `invoke_with_call_chain'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/rake-13.2.1/lib/rake/task.rb:188:in `invoke'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/rake-13.2.1/lib/rake/application.rb:188:in `invoke_task'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/rake-13.2.1/lib/rake/application.rb:138:in `block (2 levels) in top_level'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/rake-13.2.1/lib/rake/application.rb:138:in `each'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/rake-13.2.1/lib/rake/application.rb:138:in `block in top_level'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/rake-13.2.1/lib/rake/application.rb:147:in `run_with_threads'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/rake-13.2.1/lib/rake/application.rb:132:in `top_level'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/railties-7.1.3.4/lib/rails/commands/rake/rake_command.rb:27:in `block (2 levels) in perform'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/rake-13.2.1/lib/rake/application.rb:214:in `standard_exception_handling'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/railties-7.1.3.4/lib/rails/commands/rake/rake_command.rb:27:in `block in perform'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/railties-7.1.3.4/lib/rails/commands/rake/rake_command.rb:44:in `block in with_rake'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/rake-13.2.1/lib/rake/rake_module.rb:59:in `with_application'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/railties-7.1.3.4/lib/rails/commands/rake/rake_command.rb:41:in `with_rake'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/railties-7.1.3.4/lib/rails/commands/rake/rake_command.rb:20:in `perform'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/railties-7.1.3.4/lib/rails/command.rb:156:in `invoke_rake'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/railties-7.1.3.4/lib/rails/command.rb:73:in `block in invoke'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/railties-7.1.3.4/lib/rails/command.rb:149:in `with_argv'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/railties-7.1.3.4/lib/rails/command.rb:69:in `invoke'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/railties-7.1.3.4/lib/rails/commands.rb:18:in `<main>'
/Users/jvonbergen/.rvm/rubies/ruby-3.3.1/lib/ruby/3.3.0/bundled_gems.rb:74:in `require'
/Users/jvonbergen/.rvm/rubies/ruby-3.3.1/lib/ruby/3.3.0/bundled_gems.rb:74:in `block (2 levels) in replace_require'
/Users/jvonbergen/.rvm/gems/ruby-3.3.1/gems/bootsnap-1.18.3/lib/bootsnap/load_path_cache/core_ext/kernel_require.rb:30:in `require'
bin/rails:4:in `<main>'
Tasks: TOP => db:test:load_schema

Details

  • Rails version: 7.1.3.4
  • SQL Server adapter version: 7.1.5
  • TinyTDS version: 2.1.7
  • FreeTDS details: 1.4.21
$ tsql -C
Compile-time settings (established with the "configure" script)
                            Version: freetds v1.4.21
             freetds.conf directory: /opt/homebrew/etc
     MS db-lib source compatibility: no
        Sybase binary compatibility: yes
                      Thread safety: yes
                      iconv library: yes
                        TDS version: 7.3
                              iODBC: no
                           unixodbc: yes
              SSPI "trusted" logins: no
                           Kerberos: yes
                            OpenSSL: yes
                             GnuTLS: no
                               MARS: yes