Problems with ActiveRecord after certain amount of columns in table

rubyonrails-talk
1

I first had this problem like a week ago and being unable to solve it I went "meanwhile to look other aspects of my application.Today I'm back to the problem and regardless the fact I've isolated it still don't know its nature

I have a migration that goes like:

class CreateUsers < ActiveRecord::Migration   def self.up     create_table :users do |t|         t.references "faculty"         t.references "department"         t.references "role", :null => false         t.string "name", :null => false, :limit => 20         t.string "surname", :null => false, :limit => 20         t.string "username",:null => false, :limit => 25         t.string "study_group",:limit => 6         t.string "study_course",:limit => 50         t.string "card_code",:limit => 12         t.boolean "During_Day",:default => false         t.string "email", :limit => 100         t.string "hashed_password", :limit => 40         t.string "salt", :limit => 40     end   end

  def self.down     drop_table :users   end end

If I start to create users from the rails console and save them when trying to recover the data through

  User.all

some fields' info would be corrupted.I started to remove fields and it works when I leave the first 8 columns (including the ID which is created by default).Creating a ninth column or even more would damage everything and afterwards I'm getting all kind of not correct information at my views. I must say that when from the rails console I save something and then check it from the mysql command line then the info is fine....so what's ActiveRecord missing?

2

Daniel Garcia wrote in post #972292:

I first had this problem like a week ago and being unable to solve it I went "meanwhile to look other aspects of my application.Today I'm back to the problem and regardless the fact I've isolated it still don't know its nature

I have a migration that goes like:

class CreateUsers < ActiveRecord::Migration   def self.up     create_table :users do |t|         t.references "faculty"         t.references "department"         t.references "role", :null => false         t.string "name", :null => false, :limit => 20         t.string "surname", :null => false, :limit => 20         t.string "username",:null => false, :limit => 25         t.string "study_group",:limit => 6         t.string "study_course",:limit => 50         t.string "card_code",:limit => 12         t.boolean "During_Day",:default => false         t.string "email", :limit => 100         t.string "hashed_password", :limit => 40         t.string "salt", :limit => 40     end   end

  def self.down     drop_table :users   end end

If I start to create users from the rails console and save them when trying to recover the data through

  User.all

some fields' info would be corrupted.

How does the generated SQL look? And the data in the DB?

I started to remove fields and it works when I leave the first 8 columns (including the ID which is created by default).Creating a ninth column or even more would damage everything and afterwards I'm getting all kind of not correct information at my views. I must say that when from the rails console I save something and then check it from the mysql command line then the info is fine....so what's ActiveRecord missing?

It's hard to tell. Let's see the SQL.

Best,

3

First I copied what I did from the rails console.The you can the file I attached.

Loading development environment (Rails 3.0.3) irb(main):001:0> me = User.new => #<User id: nil, faculty_id: nil, department_id: nil, role_id: nil, name: "", surname: "", username: "", study_group: nil, study_cour se: nil, card_code: nil, day_time: false, email: nil, hashed_password: nil, salt: nil> irb(main):002:0> me.name = 'Daniel' => "Daniel" irb(main):003:0> me.surname = 'Garcia' => "Garcia" irb(main):004:0> me.username = 'vinagrito' => "vinagrito" irb(main):005:0> me.role_id = 1 => 1 irb(main):006:0> me.save => false irb(main):007:0> me.errors => {:password=>["is too short (minimum is 8 characters)"], :email=>["is invalid", "can't be blank"]} irb(main):008:0> me.password = '12345678' => "12345678" irb(main):009:0> me.email = 'mail@mail.com' => "mail@mail.com" irb(main):010:0> me.save => true irb(main):011:0> me => #<User id: 2, faculty_id: nil, department_id: nil, role_id: 1, name: "Daniel", surname: "Garcia", username: "vinagrito", study_group : nil, study_course: nil, card_code: nil, day_time: false, email: "mail@mail.com", hashed_password: "6305ee7016b263c0ec41a81439a378837a 318035", salt: "5c33e8fed10b87c9f4b7841f0faeb7b10424289a"> irb(main):012:0> user = User.where(:id => 1) => irb(main):013:0> user = User.where(:id => 2) => [#<User id: 2, faculty_id: nil, department_id: nil, role_id: 1, name: "Daniel", surname: "Garcia", username: "vinagrito", study_grou p: nil, study_course: nil, card_code: nil, day_time: false, email: "mail@mail.com", hashed_password: 6305.0, salt: "5c33e8fed10b87c9f4b 7841f0faeb7b10424289a">] irb(main):014:0> user.to_sql => "SELECT `users`.* FROM `users` WHERE (`users`.`id` = 2)" irb(main):015:0>

And in my DB everything's OK.But of course,as soon as I make a query hashed_password column in the rails console after I query for the record!)

Attachments: http://www.ruby-forum.com/attachment/5654/mysql_cmd_line.JPG

4

Please quote when replying. This makes the discussion easier to follow, and makes it harder to forget to reply to part of a message.

Specifically, I asked to see generated SQL queries so that I could see what data was getting sent from Rail to the DB. You provided none, except for a SELECT query that contained no data.

Daniel Garcia wrote in post #972309:

[...]

=> #<User id: 2, faculty_id: nil, department_id: nil, role_id: 1, name: "Daniel", surname: "Garcia", username: "vinagrito", study_group : nil, study_course: nil, card_code: nil, day_time: false, email: "mail@mail.com", hashed_password: "6305ee7016b263c0ec41a81439a378837a 318035", salt: "5c33e8fed10b87c9f4b7841f0faeb7b10424289a"> irb(main):012:0> user = User.where(:id => 1) => irb(main):013:0> user = User.where(:id => 2) => [#<User id: 2, faculty_id: nil, department_id: nil, role_id: 1, name: "Daniel", surname: "Garcia", username: "vinagrito", study_grou p: nil, study_course: nil, card_code: nil, day_time: false, email: "mail@mail.com", hashed_password: 6305.0, salt: "5c33e8fed10b87c9f4b 7841f0faeb7b10424289a">] irb(main):014:0> user.to_sql => "SELECT `users`.* FROM `users` WHERE (`users`.`id` = 2)" irb(main):015:0>

And in my DB everything's OK.But of course,as soon as I make a query from my controller then is going to bring "trash" (check at the hashed_password column in the rails console after I query for the record!)

Seems like Rails thinks your hashed_password is numeric. What does this table's entry in db/schema.rb look like?

Best,

5

Please quote when replying. This makes the discussion easier to follow, and makes it harder to forget to reply to part of a message.

My bad.Pretty new at forums as you can see

Specifically, I asked to see generated SQL queries so that I could see what data was getting sent from Rail to the DB. You provided none, except for a SELECT query that contained no data.

I guess I didn't understand.What do you mean by "generated SQL",where should I look at?

Seems like Rails thinks your hashed_password is numeric.

Yeah, I saw that...just can't see why!.

What does this table's entry in db/schema.rb look like?

the only thing you can see there is:

ActiveRecord::Schema.define(:version => 20101226144505) do

# Could not dump table "authors" because of following Mysql2::Error # Invalid date: BTREE

# Could not dump table "book_loans" because of following Mysql2::Error # Invalid date: BTREE

# Could not dump table "books" because of following Mysql2::Error # Invalid date: BTREE

# Could not dump table "departments" because of following Mysql2::Error # Invalid date: BTREE

# Could not dump table "faculties" because of following Mysql2::Error # Invalid date: BTREE

# Could not dump table "roles" because of following Mysql2::Error # Invalid date: BTREE

# Could not dump table "subjects" because of following Mysql2::Error # Invalid date: BTREE

# Could not dump table "users" because of following Mysql2::Error # Invalid date: BTREE

end

6

Daniel Garcia wrote in post #972318:

Please quote when replying. This makes the discussion easier to follow, and makes it harder to forget to reply to part of a message.

My bad.Pretty new at forums as you can see

Specifically, I asked to see generated SQL queries so that I could see what data was getting sent from Rail to the DB. You provided none, except for a SELECT query that contained no data.

I guess I didn't understand.What do you mean by "generated SQL",

I mean the SQL that is generated by ActiveRecord operations and sent to the database.

where should I look at?

In the log.

Seems like Rails thinks your hashed_password is numeric.

Yeah, I saw that...just can't see why!.

What does this table's entry in db/schema.rb look like?

the only thing you can see there is:

ActiveRecord::Schema.define(:version => 20101226144505) do

# Could not dump table "authors" because of following Mysql2::Error # Invalid date: BTREE

# Could not dump table "book_loans" because of following Mysql2::Error # Invalid date: BTREE

# Could not dump table "books" because of following Mysql2::Error # Invalid date: BTREE

# Could not dump table "departments" because of following Mysql2::Error # Invalid date: BTREE

# Could not dump table "faculties" because of following Mysql2::Error # Invalid date: BTREE

# Could not dump table "roles" because of following Mysql2::Error # Invalid date: BTREE

# Could not dump table "subjects" because of following Mysql2::Error # Invalid date: BTREE

# Could not dump table "users" because of following Mysql2::Error # Invalid date: BTREE

end

Oh, man, do you ever have problems. Fix your Mysql2 gem installation.

Best,

7

In the log.

This is what I get after migrating.Should I get a log when saving data from the rails console?.

  [1m[36mSQL (1.0ms)[0m [1mSHOW TABLES[0m   [1m[35mSQL (2.0ms)[0m SHOW TABLES   [1m[36mSQL (1.0ms)[0m [1mSELECT `schema_migrations`.`version` FROM `schema_migrations`[0m   [1m[35mSQL (31.0ms)[0m CREATE TABLE `users` (`id` int(11) DEFAULT NULL auto_increment PRIMARY KEY, `faculty_id` int(11), `department_id` int(11), `role_id` int(11) NOT NULL, `name` varchar(20) NOT NULL, `surname` varchar(20) NOT NULL, `username` varchar(25) NOT NULL, `study_group` varchar(6), `study_course` varchar(50), `card_code` varchar(15), `day_time` tinyint(1) DEFAULT 0, `email` varchar(100), `hashed_password` varchar(40), `salt` varchar(40)) ENGINE=InnoDB   [1m[36mSQL (68.0ms)[0m [1mCREATE INDEX faculty ON users(faculty_id)[0m   [1m[35mSQL (65.0ms)[0m CREATE INDEX department ON users(department_id)   [1m[36mSQL (78.0ms)[0m [1mCREATE INDEX role ON users(role_id)[0m   [1m[35mSQL (80.8ms)[0m CREATE INDEX username ON users(username)   [1m[36mSQL (30.0ms)[0m [1mINSERT INTO `schema_migrations` (`version`) VALUES ('20101226144503')[0m   [1m[35mSQL (7.0ms)[0m SHOW TABLES   [1m[36mSQL (3.0ms)[0m [1mSELECT `schema_migrations`.`version` FROM `schema_migrations`[0m   [1m[35mSQL (2.0ms)[0m SHOW TABLES   [1m[36mSQL (7.0ms)[0m [1mdescribe `authors`[0m   [1m[35mSQL (2.0ms)[0m SHOW KEYS FROM `authors`   [1m[36mSQL (7.0ms)[0m [1mdescribe `book_loans`[0m   [1m[35mSQL (3.0ms)[0m SHOW KEYS FROM `book_loans`   [1m[36mSQL (7.0ms)[0m [1mdescribe `books`[0m   [1m[35mSQL (2.0ms)[0m SHOW KEYS FROM `books`   [1m[36mSQL (7.0ms)[0m [1mdescribe `departments`[0m   [1m[35mSQL (3.0ms)[0m SHOW KEYS FROM `departments`   [1m[36mSQL (8.0ms)[0m [1mdescribe `faculties`[0m   [1m[35mSQL (3.0ms)[0m SHOW KEYS FROM `faculties`   [1m[36mSQL (7.0ms)[0m [1mdescribe `roles`[0m   [1m[35mSQL (2.0ms)[0m SHOW KEYS FROM `roles`   [1m[36mSQL (7.0ms)[0m [1mdescribe `subjects`[0m   [1m[35mSQL (2.0ms)[0m SHOW KEYS FROM `subjects`   [1m[36mSQL (7.0ms)[0m [1mdescribe `users`[0m   [1m[35mSQL (1.0ms)[0m SHOW KEYS FROM `users`   [1m[36mSQL (0.0ms)[0m [1mSHOW TABLES[0m   [1m[35mSQL (0.0ms)[0m BEGIN   [1m[36mSQL (0.0ms)[0m [1mdescribe `users`[0m   [1m[35mAREL (0.0ms)[0m INSERT INTO `users` (`faculty_id`, `department_id`, `role_id`, `name`, `surname`, `username`, `study_group`, `study_course`, `card_code`, `day_time`, `email`, `hashed_password`, `salt`) VALUES (NULL, NULL, 1, 'Daniel', 'Garcia', 'viangrito', NULL, NULL, NULL, 0, 'mail@mail.com', 'e1382ac8675daee167a262ee7ef3bd038f997c4a', 'a3518e0a6b273590073bd733e2ee692fbca28c2d')   [1m[36mSQL (15.6ms)[0m [1mCOMMIT[0m   [1m[35mUser Load (0.0ms)[0m SELECT `users`.* FROM `users`

8

Daniel Garcia wrote in post #972328:

In the log.

This is what I get after migrating.

That's of no particular use here, since you already posted what MySQL thinks your schema looks like.

Should I get a log when saving data from the rails console?.

Assuming your logger level is set to show SQL (which, in the development environment, it usually is), then yes. That's what we're interested in.

Think about it: what we're looking for is the SQL that Rails is sending to the DB for its save operations, so we can find out whether the data is getting corrupted before or after it gets to the DB. That's all we need. You've so far failed to provide it.

Do you understand the logic flow on an AR save operation? If you do, then these concepts should be easy to understand.

I'm explaining at length because I think you need some training in how to troubleshoot stuff effectively...I hope this is helpful.

Best,

9

Should I get a log when saving data from the rails console?.

When I pasted the log I thought it was it.Then I exited the rails console and the log updated and gave the last lines I also pasted at the end

  [1m[36mSQL (0.0ms)[0m [1mSHOW TABLES[0m   [1m[35mSQL (0.0ms)[0m BEGIN   [1m[36mSQL (15.6ms)[0m [1mdescribe `users`[0m   [1m[35mAREL (0.0ms)[0m INSERT INTO `users` (`faculty_id`, `department_id`, `role_id`, `name`, `surname`, `username`, `study_group`, `study_course`, `card_code`, `day_time`, `email`, `hashed_password`, `salt`) VALUES (NULL, NULL, 1, 'Daniel', 'Garcia', 'vinagrito', NULL, NULL, NULL, 0, 'mail@mail.com', 'f0f95c43624ea4de5aa51a29cd4390cf942ad52f', 'e25f64c2540d31d9a81de02d1abee39303cf0b4b')   [1m[36mSQL (31.2ms)[0m [1mCOMMIT[0m

I'm explaining at length because I think you need some training in how to troubleshoot stuff effectively

I think that's clear.I'm pretty new at following indications like this so I suppose I get lost at certain points.Thank you for being so patient.

So,isn't the data being well saved according to the log? and if, as I already said, through the mysql cmd line I get real data shouldn't this be a retrieving matter?

10

the only thing you can see there is:

ActiveRecord::Schema.define(:version => 20101226144505) do

# Could not dump table "authors" because of following Mysql2::Error # Invalid date: BTREE

If rails can't dump the table schema then it could conceivably by typecasting attributes incorrectly. This could be confirmed by comparing blah.attributes with blah.attributes_before_type_cast. People at Issues · brianmario/mysql2 · GitHub seem to indicate that this could be caused by a libmysql.dll that doesn't match the version of mysql that is installed

Fred

11

@Fred

People at Issues · brianmario/mysql2 · GitHub seem to indicate that this could be caused by a libmysql.dll that doesn't match the version of mysql that is installed

Yeah Fred....coincidence it is I was just there and saw a post about it. As soon as I change it I'll post back

12

@Fred Problem solved.Switched to mysql 5.1 and now I get things as I want them.

@Marnen Laibow-Koser Again thank you for the patience.Your inquiring about my on screen results helped others to relate the issue.