Problems with ActiveRecord after certain amount of columns in table

Rails
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 G. 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,

Marnen Laibow-Koser
http://www.marnen.org
[email protected]

3

First I copied what I did from the rails console.The file I attached
contains a screen-shot of the mysql cmd line.

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 = ‘[email protected]’
=> “[email protected]”
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:
“[email protected]”, 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:
“[email protected]”, 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!)

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 G. 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:
“[email protected]”, 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:
“[email protected]”, 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,

Marnen Laibow-Koser
http://www.marnen.org
[email protected]

5

Daniel G. 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,

Marnen Laibow-Koser
http://www.marnen.org
[email protected]

6

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

7

Daniel G. 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,

Marnen Laibow-Koser
http://www.marnen.org
[email protected]

8

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, ‘[email protected]’,
‘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?

9

In the log.
This is what I get after migrating.

[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, ‘[email protected]’,
‘e1382ac8675daee167a262ee7ef3bd038f997c4a’,
‘a3518e0a6b273590073bd733e2ee692fbca28c2d’)
[1m[36mSQL (15.6ms)[0m [1mCOMMIT[0m
[1m[35mUser Load (0.0ms)[0m SELECT users.* FROM users

10

@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

11

On Jan 4, 9:20pm, Daniel G. [email protected] wrote:

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

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.