Why is ActiveRecord/PostgreSQL not finding my data in the database?

Question

I'm sure this is something simple I'm overlooking but since I've been dealing with this strange issue for a few days now I'm asking for help.

Here is my apps setup and the issue:

A Rails 3.2.13 app with the pg gem and the following db scheme:

create_table "clients", :force => true do |t|
  t.string   "uuid"
  t.string   "private_key"
  t.datetime "created_at",  :null => false
  t.datetime "updated_at",  :null => false
end

add_index "clients", ["private_key"], :name => "index_clients_on_private_key"
add_index "clients", ["uuid"], :name => "index_clients_on_uuid"

I currently have two rows in my "clients" database. They pictured below:

enter image description here

The issue

When I perform a simple "SELECT * FROM clients;" using the RubyMine database console I get the rows pictured in the screen shot above. When I perform a "SELECT * FROM clients WHERE id = 11;" I get that row, all is well there. However, the issue is that when I try to perform a "SELECT * FROM clients WHERE private_key = "MkRBNUZBQzUtMzVDRi00NzQ3LThFNjEtNjI4OThERUQzQkRF";" it fails and shows the following in the console output...

sql> SELECT * FROM clients WHERE private_key = "MkRBNUZBQzUtMzVDRi00NzQ3LThFNjEtNjI4OThERUQzQkRF"
[2013-04-17 20:09:51] [42703] ERROR: column "MkRBNUZBQzUtMzVDRi00NzQ3LThFNjEtNjI4OThERUQzQkRF" does not exist
  Position: 43

I've also tried pulling this row out of the db by utilizing the active record helper methods...

Client.exists?(:private_key => token)
Client.find_by_private_key(token)

However, nothing works, it never finds the record that does exist in the db. Does anyone have any idea what is going on here? All I need to be able to do is utilize the active record helpers, specifically the "Client.exists?(:private_key => token)" to be able to check and see if the supplied token exists in the db.

Thanks for your help!

Update 4/18/13 @ 9:30am

I just tried Client.find(12) just to see if it would find the record by id, and this works. This doesn't help me understand why I still can't use Client.exists?(:private_key => token) or Client.find_by_private_key(token).

Update 4/19/13 @ 9:15am

The answer to this problem was that the code generating my private_key value was adding a return/white space to the end of the value. So when trying to query the db for row based on a private_key it was always failing to find anything.

To fix the problem I added a gsub to the end of the code that generates the private_key, like this...

private_key = SecurityHelper.encrypt_private_key(client_uuid).gsub(/\s+/, "")

This strips all white space from the generated private key and solved the problem.

@garbagecollection, I just tried Client.all and it did find both rows in the table. However, nothing else seems to work. Any idea why I can't simply Client.exists?(:private_key => token) or Client.find_by_private_key(token)? — Jeremy Fox
– Jeremy Fox, Commented Apr 18, 2013 at 13:19
Can you use Ruby debugger and check if token value actually is the value that one of the clients has? — Jason Kim
– Jason Kim, Commented Apr 18, 2013 at 16:11
@garbagecollection, I have been using the Ruby debugger to check this and the value of "token" matches the exact value of one of the rows' private_key. — Jeremy Fox
– Jeremy Fox, Commented Apr 18, 2013 at 16:40
Can you try this? Client.all.select {|c| c.token == token } Worst comes to worst, if that works, you can use that. If this works, and Client.exists?(:private_key => token) doesn't, I think that means that pg adapter is using wrong postgresql call for Ruby method. — Jason Kim
– Jason Kim, Commented Apr 18, 2013 at 17:33

Craig Ringer · Accepted Answer · 2013-04-18 01:58:38Z

1

You're using the wrong quoting style. ANSI SQL quoting as used in PostgreSQL is:

 "SELECT * FROM clients WHERE private_key = 'MkRBNUZBQzUtMzVDRi00NzQ3LThFNjEtNjI4OThERUQzQkRF';"

Note the single quotes.

It isn't clear how the first statement even got parsed by Ruby, since the inner double quotes would've ended the quoted string, leading to:

"SELECT * FROM clients WHERE private_key = "MkRBNUZBQzUtMzVDRi00NzQ3LThFNjEtNjI4OThERUQzQkRF";"
                                           ^^^^
                                           should be Ruby syntax error here

I don't really do Rails so I can't speak for the correct Rails syntax to do this check via ActiveRecord, though.

edited Apr 18, 2013 at 1:58

answered Apr 18, 2013 at 0:48

Craig Ringer

329k84 gold badges742 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jeremy Fox Over a year ago

I tried pulling a row through the RubyMine database console again using the single quote as you suggested. This time instead of it saying, "[2013-04-17 20:09:51] [42703] ERROR: column "MkRBNUZBQzUtMzVDRi00NzQ3LThFNjEtNjI4OThERUQzQkRF" does not exist" It said, "[2013-04-18 08:56:11] 0 rows retrieved in 61/79 ms". So it is still not finding the data which IS in the database.

Craig Ringer Over a year ago

@JeremyFox Please show the exact SQL that Ruby generated for that query.

Jeremy Fox Over a year ago

I don't know exactly what the query is that Rails generates when using an active record helper (in my case Client.find_by_private_key(token)). But I suspect it is something like a standard SELECT * FROM clients WHERE private_key = 'MkRBNUZBQzUtMzVDRi00NzQ3LThFNjEtNjI4OThERUQzQkRF';

Fred · Accepted Answer · 2013-04-18 00:50:33Z

0

Have you tried

Client.exists?(:private_key => "MkRBNUZBQzUtMzVDRi00NzQ3LThFNjEtNjI4OThERUQzQkRF")

answered Apr 18, 2013 at 0:50

Fred

8,6021 gold badge24 silver badges28 bronze badges

5 Comments

Fred Over a year ago

Can you successfully do Client.find(11)? If so, what is the string length of the private key? Perhaps you have a nonprinting character in the string.

Jeremy Fox Over a year ago

See my update in the original post. I am able to pull rows out of the database utilizing Client.find(11). I checked the length of the private_key and it is 48 characters long. It is 48 characters in the database and token.length returns 48.

Fred Over a year ago

Puzzling. Does Client.exists?(:uuid => uuid_token) work (where uuid_token has the correct value for record 11)?

Jeremy Fox Over a year ago

Fred, your last comment helped me to uncover the problem! I was able to successful query the db for the row containing a certain uuid. SO that got me thinking, there has to be something wrong with my private_key values. So I just inserted a new row manually and it now works. So I've narrowed it down the the code that is generating my private_key's as being the cause. I'm back in business, thanks!

Jeremy Fox Over a year ago

I'm marking yours as the accepted answer. It isn't exactly the "answer" but it will help anyone in the same situation narrow down what might have been the cause.

Collectives™ on Stack Overflow

Why is ActiveRecord/PostgreSQL not finding my data in the database?

Here is my apps setup and the issue:

I currently have two rows in my "clients" database. They pictured below:

The issue

Update 4/18/13 @ 9:30am

Update 4/19/13 @ 9:15am

2 Answers 2

3 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Here is my apps setup and the issue:

I currently have two rows in my "clients" database. They pictured below:

The issue

Update 4/18/13 @ 9:30am

Update 4/19/13 @ 9:15am

2 Answers 2

3 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related