Problem with PK ID field returned with ActiveRecord - Sqlserver - TinyTDS

[ejlevin1]

Hello,
I'm running into some issues with the incorrect PK being returned
to active record upon a model object being created. I've debugged
things quite a bit, and the SQL is getting into TinyTDS, the wrong ID
gets returned? For some reason the ID always gets returned as 19...
Its maddening :) Any help or thoughts on what is going on, would be
much appreciated! Thanks!

OK, so here are some of the details:

Debugged ActiveRecord::Persistence.create
and saw that the value of new_id was being returned as 19.

------------- SNIPPET -------------
new_id = if attributes_values.empty?
self.class.unscoped.insert
connection.empty_insert_statement_value
else
self.class.unscoped.insert attributes_values
end

self.id ||= new_id
-------------

Here is all I'm doing:

user = User.new :email => 'em...@test.com'
user.authentications.build( :provider => 'provider_name', :uid =>
self.employee_id )
user.first_name = 'Eric'
user.save!

The problem is that the new user gets assigned the value of 19 as the
PK ID (column name is ID), then the authentications child gets the
wrong FK assigned during its creation.

tSQL Config:

titanium:gems ejlevin1$ tsql -C
Compile-time settings (established with the "configure" script)
Version: freetds v0.82
freetds.conf directory: /opt/local/etc/freetds
MS db-lib source compatibility: no
Sybase binary compatibility: no
Thread safety: yes
iconv library: yes
TDS version: 5.0
iODBC: no
unixodbc: yes

freetds.conf Config section:
[myQA]
host = HOSTNAME
port = 1433
tds version = 8.0

[Ken Collins]

This is a know mis-configuration problem documented on the wiki here:
https://github.com/rails-sqlserver/activerecord-sqlserver-adapter/wiki/Using-TinyTds

Check the "Using freetds.conf section". Hopefully that conf file is not needed under the latest FreeTDS 0.91.dev and hopefully as more people upgrade, surprised like this will go away.

- Ken

[ejlevin1]

Ken,
Thanks for the direction. I am a bit confused still though... I
looked at the wiki and it says "Set “tds version” to 8.0 in your
freetds.conf. If you do not do this, it is very likely that all your
primary key will be wrong! In most case the id column will be assigned
a value of 19 for every insert." As you will notice with my
freetds.conf file, it is stating version 8.0 in the server section. I
tried removing that and only having "tds version = 8.0" under just the
[global] section, but the same result was seen... Thoughts?

Thanks again!

-Eric

[Ken Collins]

Eric,

This issue was something I could never reproduce. If I were you I would also debug it at a lower level. Some pseudo code like this would be more appropriate.

client = TinyTds::Client.new :dataserver => 'myQA', :username => '...', :password => '...'

# This is short hand for execute and return scope_identity().
client.execute("INSERT INTO [users] ([email]) VALUES (N'f...@bar.com')").insert # => ??? (should be identity)

# This is a longer manual version.
client.execute("INSERT INTO [users] ([email]) VALUES (N'f...@bar.com')").do
client.execute("SELECT CAST(SCOPE_IDENTITY() AS bigint) AS Ident").each.first['Ident'] # => ??? (should be identity)

Lemme know what you find out.

- Ken

[ejlevin1]

Ken,
Thanks so much for your help! I think I may have something that
will be interesting for you to look at below. I've copied my logs
from both Rails level and TinyTds::Client level so you can see how
things are reacting. The interesting thing is that the .insert
command is returning the wrong ID; however, the longer manual version
is returning the correct ID? Do you still think this is
configuration, or a bug? Let me know anything I can do to get you
more info that would be helpful...


##########################################################
Below is output from my Rails-level command prompt...
##########################################################

ruby-1.8.7-p334 :028 > u = User.new :email => 'ejle...@test.com'
ruby-1.8.7-p334 :029 > u.save!
EXECUTE (43.5ms) BEGIN TRANSACTION
User Load (41.1ms) SELECT TOP (1) [evt_users].[id] FROM [evt_users]
WHERE (LOWER([evt_users].[email]) = LOWER(N'ejle...@test.com'))
AREL (81.5ms) INSERT INTO [evt_users] ([current_sign_in_ip],
[sign_in_count], [encrypted_password], [email], [last_name],
[last_sign_in_ip], [created_at], [longitude], [reset_password_token],
[birthday], [remember_created_at], [first_name], [roles_mask],
[club_id], [latitude], [current_sign_in_at], [updated_at],
[last_sign_in_at]) VALUES (NULL, 0, N'', N'ejle...@test.com', NULL,
NULL, '2011-04-28 02:49:11.340', NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, '2011-04-28 02:49:11.340', NULL)
EXECUTE (37.2ms) COMMIT TRANSACTION
=> true
ruby-1.8.7-p334 :030 > u.delete
AREL (36.3ms) DELETE FROM [evt_users] WHERE [evt_users].[id] = 19
=> #<User id: 19, email: "ejle...@test.com", encrypted_password: "",
reset_password_token: nil, remember_created_at: nil, sign_in_count: 0,
current_sign_in_at: nil, last_sign_in_at: nil, current_sign_in_ip:
nil, last_sign_in_ip: nil, first_name: nil, last_name: nil, birthday:
nil, created_at: "2011-04-28 02:49:11", updated_at: "2011-04-28
02:49:11", latitude: nil, longitude: nil, club_id: nil, roles_mask:
nil>
ruby-1.8.7-p334 :031 > User.last
User Load (42.1ms) SELECT TOP (1) [evt_users].* FROM [evt_users]
ORDER BY evt_users.id DESC
=> #<User id: 964, email: "ejle...@test.com", encrypted_password:
"", reset_password_token: nil, remember_created_at: nil,
sign_in_count: 0, current_sign_in_at: nil, last_sign_in_at: nil,
current_sign_in_ip: nil, last_sign_in_ip: nil, first_name: nil,
last_name: nil, birthday: nil, created_at: "2011-04-28 02:49:11",
updated_at: "2011-04-28 02:49:11", latitude: nil, longitude: nil,
club_id: nil, roles_mask: nil>
ruby-1.8.7-p334 :033 > client.execute(" INSERT INTO [evt_users]
([current_sign_in_ip], [sign_in_count], [encrypted_password], [email],
[last_name], [last_sign_in_ip], [created_at], [longitude],
[reset_password_token], [birthday], [remember_created_at],
[first_name], [roles_mask], [club_id], [latitude],
[current_sign_in_at], [updated_at], [last_sign_in_at]) VALUES (NULL,
0, N'', N'ejle...@test.com', NULL, NULL, '2011-04-28 02:49:11.340',
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '2011-04-28
02:49:11.340', NULL)").insert
=> 19

##########################################################
Using .insert returns 19 as the identity!
##########################################################

ruby-1.8.7-p334 :042 > client.execute(" INSERT INTO [evt_users]
([current_sign_in_ip], [sign_in_count], [encrypted_password], [email],
[last_name], [last_sign_in_ip], [created_at], [longitude],
[reset_password_token], [birthday], [remember_created_at],
[first_name], [roles_mask], [club_id], [latitude],
[current_sign_in_at], [updated_at], [last_sign_in_at]) VALUES (NULL,
0, N'', N'ejle...@test.com', NULL, NULL, '2011-04-28 02:49:11.340',
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '2011-04-28
02:49:11.340', NULL)").insert
=> 19

##########################################################
Using the long manual version returns the correct ID
##########################################################

ruby-1.8.7-p334 :038 > client.execute(" INSERT INTO [evt_users]
([current_sign_in_ip], [sign_in_count], [encrypted_password], [email],
[last_name], [last_sign_in_ip], [created_at], [longitude],
[reset_password_token], [birthday], [remember_created_at],
[first_name], [roles_mask], [club_id], [latitude],
[current_sign_in_at], [updated_at], [last_sign_in_at]) VALUES (NULL,
0, N'', N'ejle...@test.com', NULL, NULL, '2011-04-28 02:49:11.340',
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '2011-04-28
02:49:11.340', NULL)").do
=> 1
ruby-1.8.7-p334 :039 > client.execute("SELECT CAST(SCOPE_IDENTITY() AS
bigint) AS Ident").each.first['Ident'].to_i => 966

On Apr 27, 9:30 pm, Ken Collins <k...@metaskills.net> wrote:
> Eric,
>
> This issue was something I could never reproduce. If I were you I would also debug it at a lower level. Some pseudo code like this would be more appropriate.
>
> client = TinyTds::Client.new :dataserver => 'myQA', :username => '...', :password => '...'
>
> # This is short hand for execute and return scope_identity().

> client.execute("INSERT INTO [users] ([email]) VALUES (N'...@bar.com')").insert  # => ??? (should be identity)

>
> # This is a longer manual version.

> client.execute("INSERT INTO [users] ([email]) VALUES (N'...@bar.com')").do

[Ken Collins]

Question, are you using the adapter versions I released yesterday?

- Ken

> --
> You received this message because you are subscribed to the Google Groups "Rails SQLServer Adapter" group.
> To post to this group, send email to rails-sqlse...@googlegroups.com.
> To unsubscribe from this group, send email to rails-sqlserver-a...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/rails-sqlserver-adapter?hl=en.
>

[Ken Collins]

Eric,

Nevermind what I said about the adapter versions. Although the TinyTds::Result#insert method will not be used in the 3.1 branch, it should technically work. The bug is something with TinyTds but I would love to know what. Could you please open an issue in the TinyTDS project <https://github.com/rails-sqlserver/tiny_tds/issues> and we can debug there?

- Thanks!

On Apr 27, 2011, at 11:01 PM, ejlevin1 wrote:

[ejlevin1]

Ken,
I put this within the issues list at:

https://github.com/rails-sqlserver/tiny_tds/issues/19

Thanks again!

-Eric

On Apr 28, 5:30 am, Ken Collins <k...@metaskills.net> wrote:
> Eric,
>

> Nevermind what I said about the adapter versions. Although the TinyTds::Result#insert method will not be used in the 3.1 branch, it should technically work. The bug is something with TinyTds but I would love to know what. Could you please open an issue in the TinyTDS project <https://github.com/rails-sqlserver/tiny_tds/issues> and we can debug there?
>
>  - Thanks!
>
> On Apr 27, 2011, at 11:01 PM, ejlevin1 wrote:
>
>
>
>
>
>
>
> > Ken,
> >     Thanks so much for your help!  I think I may have something that
> > will be interesting for you to look at below.  I've copied my logs
> > from both Rails level and TinyTds::Client level so you can see how
> > things are reacting.  The interesting thing is that the .insert
> > command is returning the wrong ID; however, the longer manual version
> > is returning the correct ID?  Do you still think this is
> > configuration, or a bug?  Let me know anything I can do to get you
> > more info that would be helpful...
>
> > ##########################################################
> > Below is output from my Rails-level command prompt...
> > ##########################################################
>

> > ruby-1.8.7-p334 :028 > u = User.new :email => 'ejlev...@test.com'

> > ruby-1.8.7-p334 :029 > u.save!
> >  EXECUTE (43.5ms)  BEGIN TRANSACTION
> >  User Load (41.1ms)  SELECT TOP (1) [evt_users].[id] FROM [evt_users]

> > WHERE (LOWER([evt_users].[email]) = LOWER(N'ejlev...@test.com'))

> >  AREL (81.5ms)  INSERT INTO [evt_users] ([current_sign_in_ip],
> > [sign_in_count], [encrypted_password], [email], [last_name],
> > [last_sign_in_ip], [created_at], [longitude], [reset_password_token],
> > [birthday], [remember_created_at], [first_name], [roles_mask],
> > [club_id], [latitude], [current_sign_in_at], [updated_at],

> > [last_sign_in_at]) VALUES (NULL, 0, N'', N'ejlev...@test.com', NULL,

> > NULL, '2011-04-28 02:49:11.340', NULL, NULL, NULL, NULL, NULL, NULL,
> > NULL, NULL, NULL, '2011-04-28 02:49:11.340', NULL)
> >  EXECUTE (37.2ms)  COMMIT TRANSACTION
> > => true
> > ruby-1.8.7-p334 :030 > u.delete
> >  AREL (36.3ms)  DELETE FROM [evt_users] WHERE [evt_users].[id] = 19

> > => #<User id: 19, email: "ejlev...@test.com", encrypted_password: "",

> > reset_password_token: nil, remember_created_at: nil, sign_in_count: 0,
> > current_sign_in_at: nil, last_sign_in_at: nil, current_sign_in_ip:
> > nil, last_sign_in_ip: nil, first_name: nil, last_name: nil, birthday:
> > nil, created_at: "2011-04-28 02:49:11", updated_at: "2011-04-28
> > 02:49:11", latitude: nil, longitude: nil, club_id: nil, roles_mask:
> > nil>
> > ruby-1.8.7-p334 :031 > User.last
> >  User Load (42.1ms)  SELECT TOP (1) [evt_users].* FROM [evt_users]
> > ORDER BY evt_users.id DESC

> > => #<User id: 964, email: "ejlev...@test.com", encrypted_password:

> > "", reset_password_token: nil, remember_created_at: nil,
> > sign_in_count: 0, current_sign_in_at: nil, last_sign_in_at: nil,
> > current_sign_in_ip: nil, last_sign_in_ip: nil, first_name: nil,
> > last_name: nil, birthday: nil, created_at: "2011-04-28 02:49:11",
> > updated_at: "2011-04-28 02:49:11", latitude: nil, longitude: nil,
> > club_id: nil, roles_mask: nil>
> > ruby-1.8.7-p334 :033 > client.execute(" INSERT INTO [evt_users]
> > ([current_sign_in_ip], [sign_in_count], [encrypted_password], [email],
> > [last_name], [last_sign_in_ip], [created_at], [longitude],
> > [reset_password_token], [birthday], [remember_created_at],
> > [first_name], [roles_mask], [club_id], [latitude],
> > [current_sign_in_at], [updated_at], [last_sign_in_at]) VALUES (NULL,

> > 0, N'', N'ejlev...@test.com', NULL, NULL, '2011-04-28 02:49:11.340',

> > NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '2011-04-28
> > 02:49:11.340', NULL)").insert
> > => 19
>
> > ##########################################################
> > Using .insert returns 19 as the identity!
> > ##########################################################
>
> > ruby-1.8.7-p334 :042 > client.execute(" INSERT INTO [evt_users]
> > ([current_sign_in_ip], [sign_in_count], [encrypted_password], [email],
> > [last_name], [last_sign_in_ip], [created_at], [longitude],
> > [reset_password_token], [birthday], [remember_created_at],
> > [first_name], [roles_mask], [club_id], [latitude],
> > [current_sign_in_at], [updated_at], [last_sign_in_at]) VALUES (NULL,

> > 0, N'', N'ejlev...@test.com', NULL, NULL, '2011-04-28 02:49:11.340',

> > NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '2011-04-28
> > 02:49:11.340', NULL)").insert
> > => 19
>
> > ##########################################################
> > Using the long manual version returns the correct ID
> > ##########################################################
>
> > ruby-1.8.7-p334 :038 > client.execute(" INSERT INTO [evt_users]
> > ([current_sign_in_ip], [sign_in_count], [encrypted_password], [email],
> > [last_name], [last_sign_in_ip], [created_at], [longitude],
> > [reset_password_token], [birthday], [remember_created_at],
> > [first_name], [roles_mask], [club_id], [latitude],
> > [current_sign_in_at], [updated_at], [last_sign_in_at]) VALUES (NULL,

> > 0, N'', N'ejlev...@test.com', NULL, NULL, '2011-04-28 02:49:11.340',

[ejlevin1]

Just to follow up on this...

I think I found a temp monkey fix in the SqlServerAdapter to overcome
the obstacle. Thank you Ken for pointing me in the right direction.
I will really be looking forward to getting an updated gem with the
fix for this.

I put the following in my application.rb file to re-open the
SqlServerAdapter class and override where the insert method is
called. The following seems to do the trick!

class ActiveRecord::ConnectionAdapters::SQLServerAdapter
def raw_connection_do(sql)
case @connection_options[:mode]
when :dblib
if(@insert_sql)
@connection.execute("#{sql}; SELECT CAST(SCOPE_IDENTITY() AS

bigint) AS Ident").each.first['Ident'].to_i

else
@connection.execute(sql).do
end
when :odbc
@connection.do(sql)
else :adonet
@connection.create_command.tap{ |cmd| cmd.command_text =
sql }.execute_non_query
end
ensure
@insert_sql = false
@update_sql = false
end
end

If anyone sees anything as they see as a major issue for this
temporary fix, please advise. If not, hope that helps anyone else
that runs into this issue!

-Eric

[Ken Collins]

Eric,

I posted a new version 0.4.5.rc3. Can you see if that fixes your problem?

- Ken