SQL Server Identity Issue

[SimonMac]

Hi,

We are trying to setup to use rodauth-rails with SQL Server, but are getting multiple issues with identity insert

TinyTds::Error: Cannot insert explicit value for identity column in table 'account_verification_keys' when IDENTITY_INSERT is set to OFF.

Has anyone successfully used rodauth with SQL Server (using activerecord-sqlserver-adapter)?

Thanks
Simon

[janko]

The issue is likely originating from sequel-activerecord_connection, whose support for SQL server is only attempted (it hasn't been confirmed to work). I would like to take a stab at a proper support, I just haven't used SQL server before, and don't know how to install it locally on my Mac, so I would appreciate any pointers on that.

[janko]

To be clear, Sequel itself supports SQL server, but is probably not compatible with activerecord-sql-adapter out-of-the-box. Looking on the internet, it seems like the way to go for installing it is through Docker.

[SimonMac]

We just have an old windows box that we've installed SQL Server on and connecting to from Linux/Mac development machines.
Our other models etc appear to be working ok, however, the rodauth inserts are triggering the error I mentioned.

I did have it in a windows VM, but that's only usable if you have enough resources not to bring the machine to a crawl

• Docker would probably work ok as well

Happy to help in debugging etc

[janko]

I was able to install it via Docker, but it appears that sqlcmd is specifically missing for M1 Macbooks 🤷🏻‍♂️. From the Azure SQL Edge image docs:

You can connect to Azure SQL Edge instance using the sqlcmd tool inside of the container by using the following command on the host:

[!NOTE] sqlcmd tool is not available inside the ARM64 version of SQL Edge containers.

docker exec -it <container_id|container_name> /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P <your_password>

Without sqlcmd, I cannot run Rodauth specs, because it's used for DB setup. And I would prefer first getting Rodauth specs running, before continuing onto sequel-activerecord_connection.

[janko]

I was able to get SQL Server running on Mac M1, and managed to run Rodauth specs. This is what I did:

# allegedly the only image that supports M1
$ docker pull mcr.microsoft.com/azure-sql-edge

# we set $SQLCMDUSER and $SQLCMDPASSWORD extra for sqlcmd-go (see below)
$ docker run --cap-add SYS_PTRACE -e 'ACCEPT_EULA=1' -e 'MSSQL_SA_PASSWORD=Strong.Pwd-123' -e 'SQLCMDUSER=sa' -e 'SQLCMDPASSWORD=Strong.Pwd-123' -p 1433:1433 --name sqledge -d mcr.microsoft.com/azure-sql-edge

Since there is no sqlcmd, I installed the Go version (from SO answer):

$ docker exec -it sqledge bash
> cd /opt/mssql/bin
> wget https://github.com/microsoft/go-sqlcmd/releases/download/v0.8.0/sqlcmd-v0.8.0-linux-arm64.tar.bz2 && bzip2 -d sqlcmd-v0.8.0-linux-arm64.tar.bz2 && tar -xvf sqlcmd-v0.8.0-linux-arm64.tar && chmod 755 sqlcmd

Next, I installed the FreeTDS via Homebrew:

$ brew install freetds

And the TinyTDS gem:

$ gem install tiny_tds -- --with-freetds-dir=/opt/homebrew/Cellar/freetds/1.3.12

I then copied setup & teardown SQL files into the docker image:

$ docker cp spec/sql/mssql_setup.sql sqledge:/home/mssql/setup.sql
$ docker cp spec/sql/mssql_teardown.sql sqledge:/home/mssql/teardown.sql

I modified the first line of db_setup_mssql and db_teardown_mssql rake tasks:

task :db_setup_mssql do
  sh 'docker exec -it sqledge /opt/mssql/bin/sqlcmd -b -r1 -i /home/mssql/setup.sql'
  # ...
end

task :db_teardown_mssql do
  sh 'docker exec -it sqledge /opt/mssql/bin/sqlcmd -b -r1 -i /home/mssql/teardown.sql'
end

I also had to add ansi=true to the database URL in db_setup_mssql rake task, as well as for RODAUTH_SPEC_DB when running rake spec.

$ RODAUTH_SPEC_DB="tinytds://localhost/rodauth_test?user=rodauth_test_password&password=Rodauth1.&ansi=true" rake spec

There are about 25 spec failures, so I will have to investigate that.

[janko]

False alarm, when I set the database timezone to UTC, all Rodauth specs pass 🤘🏻. Now I have a good starting point for adding integration to sequel-activerecord_connection.

[janko]

So, it seems that when Sequel is used with AR's SQL Server adapter, it's failing when trying to manually set value for primary key on insert. The "identity" in the error message appears to refer to the primary key. I will work on a fix.

[SimonMac]

That's great - we figured it was something like that, but couldn't work out where the difference was between our models and the rodauth models.

[janko]

Hmm, I actually wasn't able to reproduce this error when running the Rodauth test suite with an Active Record connection. After running db_setup_mssql, I made the following changes to spec_helper.rb, before running rake spec:

diff --git a/spec/spec_helper.rb b/spec/spec_helper.rb
index 6c39623..5447c3f 100644
--- a/spec/spec_helper.rb
+++ b/spec/spec_helper.rb
@@ -66,15 +66,18 @@ require 'mail'
 require 'logger'
 require 'tilt/string'

-unless db_url = ENV['RODAUTH_SPEC_DB']
-  db_url = if RUBY_ENGINE == 'jruby'
-    'jdbc:postgresql:///rodauth_test?user=rodauth_test&password=rodauth_test'
-  else
-    'postgres:///?user=rodauth_test&password=rodauth_test'
-  end
-end
-DB = Sequel.connect(db_url, :identifier_mangling=>false)
+require "active_support/all"
+require "active_record"
+ActiveRecord::Base.establish_connection(
+  adapter: "sqlserver",
+  database: "rodauth_test",
+  username: "rodauth_test",
+  password: "Rodauth1.",
+  host: "localhost",
+)
+DB = Sequel.tinytds(extensions: :activerecord_connection)
 DB.extension :freeze_datasets, :date_arithmetic
+DB.timezone = :utc
 puts "using #{DB.database_type}"

 DB.loggers << Logger.new($stdout) if ENV['LOG_SQL']

All specs passed, which indicated that sequel-activerecord_connection gem is backwards compatible with Active Record adapter in terms of Rodauth itself.

Could you give me the stack trace of the failure? I'm wondering if this error is something specific to rodauth-rails, though I'm not sure what it could be 🤔

[janko]

The issue appears to be that the default Active Record migrations that rodauth-rails generates don't produce the same SQL as Sequel migrations in the Rodauth README, specifically when it comes to the id primary key that's also a foreign key for accounts_* tables.

The official Sequel migration generates bigint NOT NULL PRIMARY KEY type, while the current Active Record migration generates bigint NOT NULL IDENTITY(1,1) PRIMARY KEY. The difference is the extra IDENTITY(1,1), which is precisely what SQL Server requires special massaging for (i.e. temporarily disabling IDENTITY_INSERT setting). Changing

create_table :account_verification_keys do |t|
  t.foreign_key :accounts, column: :id
  # ...
end

to

create_table :account_verification_keys, id: false do |t|
  t.column :id, :bigint, primary_key: true
  t.foreign_key :accounts, column: :id
  # ...
end

appears to work, the latter doesn't have the IDENTITY(1,1) anymore. Could you try whether it works when you update your migrations to this syntax for all account_* tables?

The Active Record migrations should match Sequel migrations as closely as possible; it's not possible to make the SQL identical, due to differences in Active Record and Sequel adapters. It appears that on built-in adapters (postgresql, mysql2, and sqlite3) this doesn't change the generated SQL, so it should be safe to update in rodauth-rails. Once you confirm this fixes the issue, I'll work on updating this.

[janko]

I just pushed this change for migrations, which should make Active Record's SQL Server adapter work out-of-the-box with rodauth-rails – bbbb94c.