Imports with default primary key breaking

#13

Unless I explicitly set the primary key to be a value other than id, I have not been able to get this to work. I have below 2 examples below, one without primary key of id, and one with primary key of id. Note, if I do turn off validations, the errors below go away.

I think I may have uncovered somethings that may require patches, although i am not sure how to implement them. I couldn't find a forum about this gem, so if there is one, I would like to know for future questions or discoveries.

I have installed this gem to do mass inserts. I'll start by listing what does work. I have followed somethings form this post: http://www.igvita.com/2007/07/11...

The only way I have gotten the import statement to update on insert is to do the following:

My model


User(facebook_id[primary Key], created_at, updated_at)

this is for a facebook app with rails.


config/environment.rb (bottom of file)
...
...
end

require 'ar-extensions/adapters/mysql'
require 'ar-extensions/import/mysql'

    create_table :users, :primary_key => :facebook_id do |t|
      t.timestamps
    end

class User > ActiveRecord::Base
  self.primary_key = "facebook_id"
  validates_uniqueness_of :facebook_id
end

When setting the above code, doing the following works as it seems one should expect at the console:


>>columns=[:facebook_id]
>>values=[[1234],[2345],[3456],[4567]]
>>User.import columns, values, :on_duplicate_key_update => [:updated_at]
=>#<OpenStruct failed_instances=[], num_inserts=1>
>>User.import columns, values, :on_duplicate_key_update => [:updated_at]
=>#<OpenStruct failed_instances=[], num_inserts=1>

but on the other hand, here is my log file:


WARNING: Can't mass-assign these protected attributes: facebook_id
  [4;35;1mSQL (0.040990) [0m   [0mSELECT `facebook_id` FROM `users` WHERE (`users`.facebook_id IS NULL) [0m
WARNING: Can't mass-assign these protected attributes: facebook_id
  [4;36;1mSQL (0.000091) [0m   [0;1mSELECT `facebook_id` FROM `users` WHERE (`users`.facebook_id IS NULL) [0m
WARNING: Can't mass-assign these protected attributes: facebook_id
  [4;35;1mSQL (0.000086) [0m   [0mSELECT `facebook_id` FROM `users` WHERE (`users`.facebook_id IS NULL) [0m
WARNING: Can't mass-assign these protected attributes: facebook_id
  [4;36;1mSQL (0.000056) [0m   [0;1mSELECT `facebook_id` FROM `users` WHERE (`users`.facebook_id IS NULL) [0m
WARNING: Can't mass-assign these protected attributes: facebook_id
  [4;35;1mSQL (0.000054) [0m   [0mSELECT `facebook_id` FROM `users` WHERE (`users`.facebook_id IS NULL) [0m
  [4;36;1mSQL (0.000252) [0m   [0;1mSHOW VARIABLES like 'max_allowed_packet'; [0m
  [4;35;1mClass Create Many Without Validations Or Callbacks (0.017332) [0m   [0mINSERT INTO `users` (`facebook_id`,`created_at`,`updated_at`) VALUES (1153003682,'2008-11-12 01:51:23','2008-11-12 01:51:23'),(1159273529,'2008-11-12 01:51:23','2008-11-12 01:51:23'),(1209008725,'2008-11-12 01:51:23','2008-11-12 01:51:23'),(1212122770,'2008-11-12 01:51:23','2008-11-12 01:51:23'),(1231862724,'2008-11-12 01:51:23','2008-11-12 01:51:23') ON DUPLICATE KEY UPDATE `users`.`updated_at`=VALUES(`updated_at`),`users`.`updated_at`=VALUES(`updated_at`) [0m

I see warnings, and 'create many without validations or callbacks'. Even if I explcitly call :validate => true and pass that in as option, I still receive the create without validations or callbacks. Not sure why.

Here is what does not work. I can't get the above code to work when an auto increment id is the primary key of User.

My model


User(id, facebook_id, created_at, updated_at)

Here is my schema:


    create_table :users do |t|
      t.column  :facebook_id, :null => false
      t.timestamps
    end
    add_index "users", :facebook_id, :unique=>true

class Users > ActiveRecord::Base
  validates_uniqueness_of :facebook_id
end

Here is what I am trying to do at the console:


>>columns=[:facebook_id]
>>values=[[1234],[2345],[3456],[4567]]
>>User.import columns, values, :on_duplicate_key_update => [:updated_at]
=>#<OpenStruct failed_instances=[], num_inserts=1>
>>User.import columns, values, :on_duplicate_key_update => [:updated_at]
=> #<OpenStruct failed_instances=[#<User id: nil, facebook_id: 1153003682, created_at: "2008-11-12 02:05:05", updated_at: "2008-11-12 02:05:05">, #<User id: nil, facebook_id: 1159273529, created_at: "2008-11-12 02:05:05", updated_at: "2008-11-12 02:05:05">, #<User id: nil, facebook_id: 1209008725, created_at: "2008-11-12 02:05:05", updated_at: "2008-11-12 02:05:05">, #<User id: nil, facebook_id: 1212122770, created_at: "2008-11-12 02:05:05", updated_at: "2008-11-12 02:05:05">, #<User id: nil, facebook_id: 1231862724, created_at: "2008-11-12 02:05:05", updated_at: "2008-11-12 02:05:05">], num_inserts=0>

My thinking is that this would update all the updated_at columns everytime a duplicate entry is being inserted. For some reason, I get failed attempts everytime this happens.

Here is my development.log output after a first insert:


[4;35;1mUser Columns (0.001432) [0m   [0mSHOW FIELDS FROM `users` [0m
  [4;36;1mSQL (0.000524) [0m   [0;1mSELECT `facebook_id` FROM `users` WHERE (`users`.facebook_id = 1153003682) [0m
  [4;35;1mSQL (0.000318) [0m   [0mSELECT `facebook_id` FROM `users` WHERE (`users`.facebook_id = 1159273529) [0m
  [4;36;1mSQL (0.000286) [0m   [0;1mSELECT `facebook_id` FROM `users` WHERE (`users`.facebook_id = 1209008725) [0m
  [4;35;1mSQL (0.000367) [0m   [0mSELECT `facebook_id` FROM `users` WHERE (`users`.facebook_id = 1212122770) [0m
  [4;36;1mSQL (0.000451) [0m   [0;1mSELECT `facebook_id` FROM `users` WHERE (`users`.facebook_id = 1231862724) [0m
  [4;35;1mSQL (0.000482) [0m   [0mSHOW VARIABLES like 'max_allowed_packet'; [0m
  [4;36;1mClass Create Many Without Validations Or Callbacks (0.030564) [0m   [0;1mINSERT INTO `users` (`facebook_id`,`created_at`,`updated_at`) VALUES (1153003682,'2008-11-12 01:20:50','2008-11-12 01:20:50'),(1159273529,'2008-11-12 01:20:50','2008-11-12 01:20:50'),(1209008725,'2008-11-12 01:20:50','2008-11-12 01:20:50'),(1212122770,'2008-11-12 01:20:50','2008-11-12 01:20:50'),(1231862724,'2008-11-12 01:20:50','2008-11-12 01:20:50') ON DUPLICATE KEY UPDATE `users`.`updated_at`=VALUES(`updated_at`),`users`.`updated_at`=VALUES(`updated_at`) [0m

The above says 'create many without validations or callbacks', not sure why, as the docs say that validations are on by default. And here is my development.log file after a second import with the same exact data:


[4;36;1mSQL (0.000840) [0m   [0;1mSELECT `facebook_id` FROM `users` WHERE (`users`.facebook_id = 1153003682) [0m
  [4;35;1mSQL (0.000197) [0m   [0mSELECT `facebook_id` FROM `users` WHERE (`users`.facebook_id = 1159273529) [0m
  [4;36;1mSQL (0.000144) [0m   [0;1mSELECT `facebook_id` FROM `users` WHERE (`users`.facebook_id = 1209008725) [0m
  [4;35;1mSQL (0.000140) [0m   [0mSELECT `facebook_id` FROM `users` WHERE (`users`.facebook_id = 1212122770) [0m
  [4;36;1mSQL (0.000137) [0m   [0;1mSELECT `facebook_id` FROM `users` WHERE (`users`.facebook_id = 1231862724) [0m

As you can see, nothing is happening, no inserts or updates are made. I have no idea why this is happening. I am on ar-extensions-0.8.0 and Rails 2.1.2.

What am I missing? I have spent about 2 days on this can can't find anything online. I would like this to work without getting warnings, and using an id as the primary key.

Any help or guidance on how to get this working would be greatly appreciated.

Reported by jdurden · November 27th, 2008 @ 02:59 PM

Activity

wladjmir
same problem, with jruby , jdbc-sqlite3 i get

WARNING: Can't mass-assign these protected attributes ActiveRecord::StatementInvalid - ActiveRecord::ActiveRecordError: not implemented by SQLite JDBC driver: INSERT INTO contacts (id,name) VALUES(1,'bla bla')

any suggestions?

February 8th, 2009 @ 12:23 PM
Mike Woodhouse
I have something similar with Oracle, where it attempts to insert NULL into my id column.

In import.rb/import_without_validations_or_callbacks we get nice net-in-sequence value generation when supports_import? is false, but nothing when it's true.

I'm thinking there's something missing in the value string generation when multi-insert is possible. I think the value string generation may really want to be independent of support_import? completely. I'll see if I can figure it out...

February 9th, 2009 @ 07:40 AM
Zach Dennis
wladjmir - Can you post the entire SQL generated, as well as the code you are using to execute import?

Mike - if you figure something out I'd gladly accept the patch. I won't have time to dig into this for a little while. Feel free to ping me on IRC, nick is zdennis.

February 9th, 2009 @ 07:37 PM
Mike Woodhouse
I've forked the code on Git (which gives me another learning curve to negotiate!).

I think I've isolated the problem - I'll put it up as soon as I've figured out running the rest of the test suite. Well, all but maybe Postgres, which I may not have time to install (the others are all present).

I'm slowly making headway on Oracle imports as well, which are really really nasty and likely to be constrained at best - the number of rows you can import may depend on the size of the concatenated INSERTs - I'm seeing 100 small rows go through OK, but a crash at 200. Triangulating now. I hope there's an answer: the "next-best" alternative (extract/transform via fastercsv, load via SQL*Loader and give up on AR migrations completely) is not pleasant.

February 11th, 2009 @ 10:29 AM

wladjmir

Zach,

the code:

require 'java'
require 'models'

require 'ar-extensions'
require 'active_record/connection_adapters/sqlite_adapter'
require 'ar-extensions/adapters/sqlite'
require 'ar-extensions/import/sqlite'
begin
  java::lang::Class.forName('com.ddtek.jdbc.sequelink.SequeLinkDriver', false, JRuby.runtime.jruby_class_loader).newInstance
  conn = java::sql::DriverManager.getConnection("jdbc:sequelink://localhost:2399;user=ruby;password=ruby;serverDataSource=Contatti");

  stmt = conn.createStatement
  cnx = ActiveRecord::Base.connection

  rs = stmt.executeQuery("select * from table")

  columns = [:id, :name]
  values = []
  while (rs.next) do
    values.push [rs.getString("ID").to_i, (rs.getString("Name") ? rs.getString("Name") : "")]
  end
  Contact.import columns, values

  rs.close
  stmt.close
  conn.close
rescue java::lang::ClassNotFoundException
  puts "ClassNotFoundException"
rescue java::sql::SQLException => err
  puts "SQLException" + err
end



SQL 

WARNING: Can't mass-assign these protected attributes: i
....
...
WARNING: Can't mass-assign these protected attributes: id
  SQL (0.0ms)   ActiveRecord::ActiveRecordError: not implemented by SQLite JDBC driver: INSERT INTO contacts (id,name) VALUES(1,'PROVA')

February 22nd, 2009 @ 08:43 AM

Zach Dennis
wladjmir,

Is it failing to import completely and raising the error at the bottom of your post?
```
ActiveRecord::ActiveRecordError: not implemented by SQLite JDBC driver: INSERT INTO contacts (id,name) VALUES(1,'PROVA')
```
February 22nd, 2009 @ 10:45 AM
wladjmir
yes, does not import at all

February 28th, 2009 @ 02:59 AM

Please Sign in or create a free account to add a new ticket.

With your very own profile, you can contribute to projects, track your activity, watch tickets, receive and update tickets through your email and much more.