Possible Bug in the adapter?

[Mark]

I don't know if this is the correct place to discuss this, but I think
I've found a bug in the SQL server adapter.

It seems that on auto-increment integer columns the SQL that is
attempted is trying to insert
a NULL value for the primary key. I'm not sure if this is specifically
on legacy systems i.e. non-Rails convention table names and primary
keys or not.

I have a table called P (I know). And it has an auto-increment primary
key called P_ID. It also has a column called ID.
It's a legacy system and I'm working on migrating it from SQL Server
to MySQL and thought ActiveRecord would be the least painful method of
moving it across. (Just by way of explanation).

I had a quick look at the points in the code in the stacktrace, but
I'm not familiar enough with it to know where the problem may be
originating. The code I'm calling it from is just setting some values
on a new 'P' object (without setting the primary key P_ID value).
There are some other columns in the query there that I've redacted for
privacy, and readability). Apologies in advance if this is the wrong
place to discuss/report this.

Here's the error and the stack trace.

DBI::DatabaseError: 37000 (339) [Microsoft][ODBC SQL Server Driver]
[SQL Server]DEFAULT or NULL are not allowed as explicit identity
values.: INSERT INTO [P] ([P_ID], [ID], [P_ClaimNo]) VALUES(NULL,
N'xv23254752102', N'23254752102')


C:/ruby191/lib/ruby/gems/1.9.1/gems/activerecord-2.3.3/lib/
active_record/connection_adapters/abstract_adapter.rb:212:in `rescue
in log'
C:/ruby191/lib/ruby/gems/1.9.1/gems/activerecord-2.3.3/lib/
active_record/connection_adapters/abstract_adapter.rb:195:in `log'
C:/ruby191/lib/ruby/gems/1.9.1/gems/activerecord-sqlserver-
adapter-2.2.22/lib/active_record/connection_adapters/
sqlserver_adapter.rb:840:in `raw_execute'
C:/ruby191/lib/ruby/gems/1.9.1/gems/activerecord-sqlserver-
adapter-2.2.22/lib/active_record/connection_adapters/
sqlserver_adapter.rb:405:in `block in execute'
C:/ruby191/lib/ruby/gems/1.9.1/gems/activerecord-sqlserver-
adapter-2.2.22/lib/active_record/connection_adapters/
sqlserver_adapter.rb:933:in `with_identity_inser
t_enabled'
C:/ruby191/lib/ruby/gems/1.9.1/gems/activerecord-sqlserver-
adapter-2.2.22/lib/active_record/connection_adapters/
sqlserver_adapter.rb:405:in `execute'
C:/ruby191/lib/ruby/gems/1.9.1/gems/activerecord-2.3.3/lib/
active_record/connection_adapters/abstract/database_statements.rb:
259:in `insert_sql'
C:/ruby191/lib/ruby/gems/1.9.1/gems/activerecord-sqlserver-
adapter-2.2.22/lib/active_record/connection_adapters/
sqlserver_adapter.rb:827:in `insert_sql'
C:/ruby191/lib/ruby/gems/1.9.1/gems/activerecord-2.3.3/lib/
active_record/connection_adapters/abstract/database_statements.rb:
44:in `insert'
C:/ruby191/lib/ruby/gems/1.9.1/gems/activerecord-2.3.3/lib/
active_record/connection_adapters/abstract/query_cache.rb:25:in
`insert_with_query_dirty'
C:/ruby191/lib/ruby/gems/1.9.1/gems/activerecord-2.3.3/lib/
active_record/base.rb:2897:in `create'
C:/ruby191/lib/ruby/gems/1.9.1/gems/activerecord-2.3.3/lib/
active_record/timestamp.rb:53:in `create_with_timestamps'
C:/ruby191/lib/ruby/gems/1.9.1/gems/activerecord-2.3.3/lib/
active_record/callbacks.rb:266:in `create_with_callbacks'
C:/ruby191/lib/ruby/gems/1.9.1/gems/activerecord-2.3.3/lib/
active_record/base.rb:2863:in `create_or_update'
C:/ruby191/lib/ruby/gems/1.9.1/gems/activerecord-2.3.3/lib/
active_record/callbacks.rb:250:in `create_or_update_with_callbacks'
C:/ruby191/lib/ruby/gems/1.9.1/gems/activerecord-2.3.3/lib/
active_record/base.rb:2534:in `save'
C:/ruby191/lib/ruby/gems/1.9.1/gems/activerecord-2.3.3/lib/
active_record/validations.rb:1009:in `save_with_validation'
C:/ruby191/lib/ruby/gems/1.9.1/gems/activerecord-2.3.3/lib/
active_record/dirty.rb:79:in `save_with_dirty'
C:/ruby191/lib/ruby/gems/1.9.1/gems/activerecord-2.3.3/lib/
active_record/transactions.rb:229:in `block in
with_transaction_returning_status'
C:/ruby191/lib/ruby/gems/1.9.1/gems/activerecord-2.3.3/lib/
active_record/connection_adapters/abstract/database_statements.rb:
136:in `transaction'
C:/ruby191/lib/ruby/gems/1.9.1/gems/activerecord-2.3.3/lib/
active_record/transactions.rb:182:in `transaction'
C:/ruby191/lib/ruby/gems/1.9.1/gems/activerecord-2.3.3/lib/
active_record/transactions.rb:228:in
`with_transaction_returning_status'
C:/ruby191/lib/ruby/gems/1.9.1/gems/activerecord-2.3.3/lib/
active_record/transactions.rb:196:in `block in save_with_transactions'
C:/ruby191/lib/ruby/gems/1.9.1/gems/activerecord-2.3.3/lib/
active_record/transactions.rb:208:in `rollback_active_record_state!'
C:/ruby191/lib/ruby/gems/1.9.1/gems/activerecord-2.3.3/lib/
active_record/transactions.rb:196:in `save_with_transactions'

[Ken Collins]

This sounds DB related. I work in a big legacy DB with PKs, no PKs,
Compound PKs, you name it. ActiveRecord does what it should as you
tell it. In this case, it looks like your DB is not doing what it
should? Curious what does your model look like? Something like this?

class P < ActiveRecord::Base
set_table_name :P
set_primary_key :P_ID
end

[Mark Burns]

Yes the model is pretty much like that.

class OldClaimPart < ActiveRecord::Base

  set_table_name :P
  set_primary_key :P_ID
end

I'm just wondering if it is something to do with there being a column
called ID as well as a primary key P_ID?
What seems strange to me is that the generated SQL that is causing the trouble is
trying to insert a NULL value to the auto-generated P_ID column.
Presumably, based on the error message this isn't possible for SQL server.

I tried the following statements in a SQL Server query window and the first one failed,
the latter one inserted fine.

INSERT INTO [P] ([P_ID], [ID], [P_ClaimNo]) VALUES(NULL,N'xv23254752102', N'23254752102')

INSERT INTO [P] ( [ID], [P_ClaimNo]) VALUES(N'xv23254752102', N'23254752102')

I'm wondering what you mean when you say the DB is not doing what it should?
If you're saying SQL Server is not behaving as you may expect it to, then shouldn't
that mean that something in the adapter should be altered so that it communicates
in an expected manner?
Unless you were thinking I'd ommited the set_table_name and set_primary_key?

It seems to me that ActiveRecord or sqlserver-adapter (or at whatever level it occurs in the callstack)
should be using the second type of query when there is an auto-incremented primary key.

I could well be wrong though, so if you have any pointers to how to workaround/fix this, I'd be
grateful.

2009/10/28 Ken Collins <k...@metaskills.net>

[Ken Collins]

Hey Mark,

Yes the model is pretty much like that.

class OldClaimPart < ActiveRecord::Base
  set_table_name :P
  set_primary_key :P_ID
end

OK, good, it's important.

I'm just wondering if it is something to do with there being a column
called ID as well as a primary key P_ID?

Perhaps. Here is the thing the way ActiveRecord and specifically with the adapter works in general with PKs. Let's say I have a normal table [foos] with an [id] auto increment column and a model like so.

class Foo < ActiveRecord::Base ; end

So these two lines would not include the primary key column in the VALUES and it would use the SCOPE_IDENTITY() after the insert statement to populate the ruby object with the new PK [id] column value.

f = Foo.new

f.save

Now if you did something like this. It would include the [id] column in the VALUES. In other DBs this is just fine but in the adapter we have to SET IDENTITY_INSERT and the adapter does this by examining the insert statement and scanning to see if it has an identity column present from the reflection on the models column objects.

f = Foo.new

f.id = 420

f.save

This is all low level and just basic stuff to pass the core ActiveRecord tests, the boiler plate. I mention it because if for some reason your did not set_primary_key(:P_ID) then I would suspect you would see the error your seeing. Perhaps something is manually setting that object's id to null in a call back. For instance changing the code below.

f = Foo.new

f.id = nil

f.save

Would result in an error like your describing since ActiveRecord (not the adapter) monitors that setter for PK and setups up the inserted attributes to include the VALUES. Again this is just boiler plate AR.

What seems strange to me is that the generated SQL that is causing the trouble is
trying to insert a NULL value to the auto-generated P_ID column.
Presumably, based on the error message this isn't possible for SQL server.

I tried the following statements in a SQL Server query window and the first one failed,
the latter one inserted fine.

INSERT INTO [P] ([P_ID], [ID], [P_ClaimNo]) VALUES(NULL,N'xv23254752102', N'23254752102')

INSERT INTO [P] ( [ID], [P_ClaimNo]) VALUES(N'xv23254752102', N'23254752102')

Nah... none of that seems strange to me. Especially since I do not know the nitty gritty details of your model object at hand. It could be perfectly normal AR depending on what is happening.

I'm wondering what you mean when you say the DB is not doing what it should?
If you're saying SQL Server is not behaving as you may expect it to, then shouldn't
that mean that something in the adapter should be altered so that it communicates
in an expected manner?

I dunno. We'll see. I've just seen a lot of what appear to be errors on the adapter and end up being some user configuration issue that most people have never heard of in the adapter.

Unless you were thinking I'd ommited the set_table_name and set_primary_key?

It seems to me that ActiveRecord or sqlserver-adapter (or at whatever level it occurs in the callstack)
should be using the second type of query when there is an auto-incremented primary key.

I agree and it does for me and most (if not all) others. This is a battle tested code and the use case your describing seems real common. Curious, have your run the test suite yet? I saw that you were using Windows ODBC driver. As a side interest, I would love to know if you had to do anything special with either the adapter or the DBI/ODBC-DBD layer. I can not recall if anyone has run the tests on Windows yet. 

I could well be wrong though, so if you have any pointers to how to workaround/fix this, I'd be
grateful.

Sure... let me know if anything above sparks some thought. I can write a test case for you just to run your situation thru the gauntlet of tests we already have. It might help find the point of failure.

 - Ken

[Ken Collins]

OK, here is a DIFF of some tests I added to a topic branch.

http://gist.github.com/221060

Everything is passing, as you can see, the only thing that I see odd in this case is that the the hanging [id] column which is not the identity column is only accessible via the lower level ActiveRecord attribute accessors since #id is now the custom PK, which is what you would expect. Thoughts?

 - Ken

[Mark Burns]

OK as may be expected, I think I was being stupid, but thankfully posting here allowed me to get to the bottom of it.

It seems my code had

class LegacyData < ActiveRecord::Base
 
  self.abstract_class = true
  establish_connection $databases["old"]
 
end


class OldClaimPart < LegacyData

  set_table_name "P"
  primary_key  = "P_ID"
 
end

rather than

class OldClaimPart < LegacyData

  set_table_name "P"
  set_primary_key  "P_ID"
 
end

It seems to be working now.
If

primary_key = "P_ID"

had thrown an error I would have spotted it.
Presumably this does something unlike what I was expecting.

By the way the reason for the discrepancy is because last night I was at home and not in front
of the code at work. In my mind primary_key= and set_primary_key were obviously equivalent.

I did have to do a _lot_ of hacking to get Windows and Ruby 1.9.1 playing nicely with Sql Server and the
various other gems I wanted to use (most difficult being Cucumber).

I will try and get back to you with more details of problems I encountered. I'm not 100% clear at this
point what things worked and didn't work as I spent around a week battling with the installation
process, but I know I defintely had some problems with ODBC/DBI/SQL Server. But don't understand
enough to know what the root cause of the various problems were.

Most of my searches were either no results or one that didn't help, so I imagine I may be
one of the lucky few to get it working and may have useful knowledge. Unfortunately(?), I don't have
windows at home and so can't do a lot in my spare time.

Thanks for your help,

Mark

[Ken Collins]

Hey Mark,

> had thrown an error I would have spotted it.
> Presumably this does something unlike what I was expecting.

Yea... I've seen this trip people up before (including me). What you
did there was just create a local variable to the class. It is common
that if you want to call the whatever= method in scope at the time vs
creating a local variable you will need to either prefix with self. or
use the long winded setter method. For example, setting bar would not
have hit the accessor unless I used self.

class Foo
attr_accessor :bar
def validate
self.bar = 'batz'
end
end

f = Foo.new # => #<Foo:0x100174688>
f.validate
f # => #<Foo:0x100174688 @bar="batz">

> By the way the reason for the discrepancy is because last night I
> was at home and not in front
> of the code at work. In my mind primary_key= and set_primary_key
> were obviously equivalent.

No worries. I had a feeling.

> I did have to do a _lot_ of hacking to get Windows and Ruby 1.9.1
> playing nicely with Sql Server and the
> various other gems I wanted to use (most difficult being Cucumber).
>
> I will try and get back to you with more details of problems I
> encountered. I'm not 100% clear at this
> point what things worked and didn't work as I spent around a week
> battling with the installation
> process, but I know I defintely had some problems with ODBC/DBI/SQL
> Server. But don't understand
> enough to know what the root cause of the various problems were.
>
> Most of my searches were either no results or one that didn't help,
> so I imagine I may be
> one of the lucky few to get it working and may have useful
> knowledge. Unfortunately(?), I don't have
> windows at home and so can't do a lot in my spare time.

Well if you do find some time, please help us out and put something up
on the wiki. I added an article there this week that covered getting
Ruby Enterprise Edition (REE) up and running with this stack. Would
love to know more about Windows setup. I have a goal in my head that I
want to try out IronRuby for the hell of it and see if I can do some
stuff to either show up or help for the next upcoming RubyConf. As a
side note, if anyone is on the list from the IronRuby team, I kept
hearing from ya'll with native connection additions to the adapter and
no one gets in touch with me afterward. What up ya'll????

- Cheers,
Ken

[Mark Burns]

Thanks Ken,
Sorry for the delay in replying. I haven't really done any method calling or anything like that outside of
actual methods before, with the exception of the rails stuff. So I'd not paid much mind as to what was going
on attributing it to some Rails magic.

class Foo

 def hello
   puts "hello"
 end

 hello

end

Although the above in IRB gives me

undefined local variable or method `hello' for Foo:Class


I will need to set up the whole system on our windows production server at some point
soon, so having been through the headache twice on two development machines
it should take a lot less time, and I'll make sure to document the process I went through.
And this time not taking too many dead-end wrong turns

2009/10/29 Ken Collins <k...@metaskills.net>

[Ken Collins]

Here you go... your code negates the fact that there is either an inheritable attribute or class attribute accessor already setup by the time you are creating a subclass of ActiveRecord::Base. This should demonstrate:

require 'rubygems'

require 'activesupport'

class Foo

  # Mimic Magic

  cattr_accessor :bar

  class << self

    def set_bar(value)

      @@bar = value

    end

  end

  # User Code

  bar = :tricked_local_var

end

Foo.bar # => nil

class Foo

  set_bar :batz

end

Foo.bar # => :batz

class Foo

  self.bar = :real_class_attr

end

Foo.bar # => :real_class_attr