Why the first user created would get id 2 instead of 1?

Rails
1

Have any of you any idea why at Heroku the first user created would get
id
2 instead of 1, and ActiveRecord cannot find user with id 1?

After deploying my application I created a new user and noticed that the
address bar was reporting number 2 at the end of the url:
http://…herokuapp.com/users/2.

So I checked with rails console in production environment first and then
at
Heroku. There seemed to be nothing irregular in my production
environment
(running sqlite3),

With heroku run console I got the following results:

irb(main):001:0> User.count
(4.9ms) SELECT COUNT() FROM “users”
(4.9ms) SELECT COUNT() FROM “users”=> 1

irb(main):002:0> User.first
User Load (2.5ms) SELECT “users”.* FROM “users” ORDER BY
“users”.“id” ASC LIMIT 1
User Load (2.5ms) SELECT “users”.* FROM “users” ORDER BY
“users”.“id” ASC LIMIT 1=> #<User id: 2, name: “krfg”, email:
“[email protected]”, created_at: “2015-12-20 13:56:04”, updated_at:
“2015-12-20 13:56:04”, password_digest: “…”>

irb(main):001:0> User.find(1)
User Load (1.1ms) SELECT “users”.* FROM “users” WHERE “users”.“id” =
$1 LIMIT 1 [[“id”, 1]]
User Load (1.1ms) SELECT “users”.* FROM “users” WHERE “users”.“id” =
$1 LIMIT 1 [[“id”, 1]]ActiveRecord::RecordNotFound: Couldn’t find User
with ‘id’=1

I am not an experienced user, I am just a beginner, but to me it is
strange
that User Load is repeated twice.
What do you think is wrong?

2

On 26 December 2015 at 15:52, ‘krfg’ via Ruby on Rails: Talk
[email protected] wrote:

Have any of you any idea why at Heroku the first user created would get id 2
instead of 1, and ActiveRecord cannot find user with id 1?

After deploying my application I created a new user and noticed that the
address bar was reporting number 2 at the end of the url:
http://…herokuapp.com/users/2.

Did you create a user and then delete it again? If so then that would
explain it. id numbers are not reused. Whatever the reason do not
worry about it, you should never assume anything about the actual
value of an id, just consider it to be a random value.

Colin

3

On Sun, Dec 27, 2015 at 1:29 AM, ‘krfg’ via Ruby on Rails: Talk
[email protected] wrote:

I did not delete any user, at very least not after creating the user that
received id 2.

Creating and deleting a first user would “use up” sequence # 1 in most
DBs, so the next user would be - surprise! - 2.

But as Colin said: it’s only important that each user has a unique id,
not what it is.

Anyway, what most worries me is the repetition of ‘User Load’ when I query
the database. Would it be possible that the database holds two tables with
same name ‘users’?

I can’t imagine that being a possibility, but what’s stopping you from
looking for yourself?

And if you’re concerned that an action is causing duplicate queries
to be run, why not look at the DB logs?

–
Hassan S. ------------------------ [email protected]

twitter: @hassan
Consulting Availability : Silicon Valley or remote

4

On Sunday, December 27, 2015 at 7:17:17 PM UTC+1, Hassan S.
wrote:

I can’t imagine that being a possibility, but what’s stopping you from
looking for yourself?

And if you’re concerned that an action is causing duplicate queries
to be run, why not look at the DB logs?

I am a beginner, have a only two months study experience, so I would not
know how to do it, honestly. Sorry for that!
Basically, I would like to check whether my suspicion is correct or not,
and in case it is correct find a solution: I would appreciate any hint
on
how to do it.
In case everything is fine, I would feel surely relieved, but I would
like
to know anyway the reason of this repetition, unless someone of you
reassured me that it is a normal behaviour due to some reason, for
instance
because PostgreSQL queries produces double responses like those or
else…
This happens at Heroku, while in my production environment, that uses
sqlite3, same queries do not load User and select users twice. So there
everything looks fine.

My suspicion is due to the fact that in a topic branch (that I reset and
cleaned) in my production env I once did duplicate by mistake the User
model and migrated the migration which then was aborted, because the
users
table already existed. It may be silly but I am afraid that I did
something
wrong and caused this issue.
I am aware that all this sounds messy!

5

On Saturday, December 26, 2015 at 10:44:44 PM UTC+1, Colin L. wrote:

Did you create a user and then delete it again? If so then that would
explain it. id numbers are not reused. Whatever the reason do not
worry about it, you should never assume anything about the actual
value of an id, just consider it to be a random value.

Colin

I did not delete any user, at very least not after creating the user
that
received id 2.
I also added another user that was given id 3.
Anyway, what most worries me is the repetition of ‘User Load’ when I
query
the database. Would it be possible that the database holds two tables
with
same name ‘users’?

6

‘krfg’ via Ruby on Rails: Talk [email protected]
writes:

I am a beginner, have a only two months study experience, so I would not
know how to do it, honestly. Sorry for that!

The way you do it is to create a very small test rails application with
a single model, create an heroku app for it, and see what happens to the
when you create the first model record. Isolating everything out of the
mix that may have been happining with your other app, previous attempts
to use the data base, etc. If you can recreate that problem in a new
app, (which I cannot; my tests show it working as expected), then
carefully writing down exactly what happened and posting the code so we
can try it ourselves, we might have something.

Basically, I would like to check whether my suspicion is correct or not,
and in case it is correct find a solution: I would appreciate any hint on
how to do it.

Personally, I have the suspicion that you’ve used the same database on
that Heroku project for a previous try with this app and it’s an
artifact of having previously created a user in that table in the
database. To be perfectly sure, you can drop and recreate the database
on heroku with this command:

heroku run rake db:drop db:create db:migrate

and when that’s complete, create your first user again and see what
happens.

If that actually shows that the id’s start from one (which my tests do
show), then you have your answer. If not, then try the test application
route, documenting each step fully.

In case everything is fine, I would feel surely relieved, but I would like
to know anyway the reason of this repetition, unless someone of you
reassured me that it is a normal behaviour due to some reason, for instance
because PostgreSQL queries produces double responses like those or else…

This actually is normal, though confusing, but the reason is much
simpler: when you’re running the console at heroku, both irb and the
heroku logger are writing to output, so you get two messages, one from
irb and one from the logger. There is only one actual database operation
happening.

–
Tamara T.
[email protected]
http://www.tamouse.org

7

On Monday, December 28, 2015 at 4:54:42 AM UTC+1, tamouse wrote:

and when that’s complete, create your first user again and see what
happens.

If that actually shows that the id’s start from one (which my tests do
show), then you have your answer.

Hi tamouse,

thanks for you reply.
I tried as you suggested with ‘heroku run rake db:drop db:create
db:migrate’.
This is what happened:

Running rake db:drop db:create db:migrate on evening-tor-7558… up,
run.1645
FATAL: permission denied for database “postgres”
DETAIL: User does not have CONNECT privilege.

Couldn’t drop d7joq5egek6ik
FATAL: permission denied for database “postgres”
DETAIL: User does not have CONNECT privilege.

Couldn’t create database for {“adapter”=>“postgresql”, “pool”=>5,
“timeout”=>5000, “database”=>“d7joq5egek6ik”,
“username”=>“ehufnjwspxqoqf”,
“password”=>“jq-7RPmjk0ipNkvh-3PaSgyyhv”, “port”=>5432,
“host”=>“ec2-54-204-30-115.compute-1.amazonaws.com”}
ActiveRecord::SchemaMigration Load (4.3ms) SELECT
“schema_migrations”.*
FROM “schema_migrations”

8

On Monday, December 28, 2015 at 4:54:42 AM UTC+1, tamouse wrote:

and when that’s complete, create your first user again and see what
happens.

If that actually shows that the id’s start from one (which my tests do
show), then you have your answer.

Hi tamouse,

thanks for you reply.
I tried as you suggested with ‘heroku run rake db:drop db:create
db:migrate’.
This is what happened:

Running rake db:drop db:create db:migrate on evening-tor-XXXX… up,
run.1645
FATAL: permission denied for database “postgres”
DETAIL: User does not have CONNECT privilege.

Couldn’t drop d7joqXXXXk6ik
FATAL: permission denied for database “postgres”
DETAIL: User does not have CONNECT privilege.

Couldn’t create database for {“adapter”=>“postgresql”, “pool”=>5,
“timeout”=>5000, “database”=>“d7joqXXXXk6ik”,
“username”=>“ehuXXXXspxqoqf”,
“password”=>“jq-7RXXXX0ipNkvh-
3PaSgyyhv”, “port”=>5432,
“host”=>"ec2-54-204-30-115.compute-1.amazonaws.com
"}
ActiveRecord::SchemaMigration Load (4.3ms) SELECT
“schema_migrations”.*
FROM “schema_migrations”

9

‘krfg’ via Ruby on Rails: Talk [email protected]
writes:

I tried as you suggested with 'heroku run rake db:drop db:create
DETAIL: User does not have CONNECT privilege.

Couldn’t create database for {“adapter”=>“postgresql”, “pool”=>5,
“timeout”=>5000, “database”=>“d7joq5egek6ik”, “username”=>“ehufnjwspxqoqf”,
“password”=>“jq-7RPmjk0ipNkvh-3PaSgyyhv”, “port”=>5432,
“host”=>“ec2-54-204-30-115.compute-1.amazonaws.com”}
ActiveRecord::SchemaMigration Load (4.3ms) SELECT “schema_migrations”.*
FROM “schema_migrations”

What happened when you tried creating a new test application?

–
Tamara T.
[email protected]
http://www.tamouse.org

10

On Sunday, December 27, 2015 at 6:17:17 PM UTC, Hassan S. wrote:

On Sun, Dec 27, 2015 at 1:29 AM, ‘krfg’ via Ruby on Rails: Talk
[email protected] wrote:

I did not delete any user, at very least not after creating the user that
received id 2.

Creating and deleting a first user would “use up” sequence # 1 in most
DBs, so the next user would be - surprise! - 2.

Note that on most dbs (eg postgres, mysql) if you roll back a
transaction then that does not rollback the sequence, so you can get
gaps in the ids of rows without ever having deleted any rows.

Fred

11

On Tuesday, December 29, 2015 at 4:38:47 AM UTC+1, tamouse wrote:

What happened when you tried creating a new test application?

I am sorry but if you do not mind I need some instruction on how to
recreate same conditions in a test app. I feel and am a bit awkward at
this
stage.
As far as I understood you want me to create a test app with sign up
page,
see if it works and then recreate the issue that the original app is
having.

I am using Cloud9 as a development environment, and I am working on a
private workspace.
Cloud9 provides free accounts with only 1 private workspace and
unlimited
public workspaces.
I may choose to create a new public workspace for my test app or using
the
same workspace I have been using so far and which is hosting 3 different
apps overall.
What solution best suits me?
I also have to consider that free accounts at Heroku allow only 5 apps,
but
I suppose I can eventually destroy my test app both in my workspace and
at
Heroku.

12

‘krfg’ via Ruby on Rails: Talk [email protected]
writes:

As far as I understood you want me to create a test app with sign up page,
see if it works and then recreate the issue that the original app is having.

The problem as you’ve stated it is that your first user gets id=2. The
point of the test is to find the minimum set of conditions that
reproduce that behaviour so you can see why it’s happening. My thought
was (and this is just what I did) to in a new directory, simply run the
rails new command to create a new rails app, generate a scaffold with a
model with one field, and check what happens locally to make sure it
works. Then prepare the app for heroku, create a new app at heroku for
this test app, and run it on heroku and see what happens. Then
progressively add to the test app until or if you see the same behaviour
of
the first user id starting at 2 instead of 1. However, as you’ve shown,
regular heroku users don’t have the permission to drop the database and
recreate it, which makes it a bit more complex. As an alternative to
that, you can instead destroy the heroku app and create another.

I am using Cloud9 as a development environment, and I am working on a
private workspace.
Cloud9 provides free accounts with only 1 private workspace and unlimited
public workspaces.
I may choose to create a new public workspace for my test app or using the
same workspace I have been using so far and which is hosting 3 different
apps overall.
What solution best suits me?

I have no idea what best suits you in this scenario; it’s really up to
you. I think, though, you may just be overthinking this. It’s just a
test app, it’s not going to be around long, does it really matter?

The only possible consideration I would have, is that since you are
publicy sharing with us this problem, if you put it on a public
workspace, there’s just the possibility that someone else can pair with
you, look at it, and possibly spot the issue.

I also have to consider that free accounts at Heroku allow only 5 apps, but
I suppose I can eventually destroy my test app both in my workspace and at
Heroku.

You can create and destroy heroku apps many times a day, really.

Here’s the thing about this: in every test I’ve personally run to try to
recreate your problem, I’ve never been able to reproduce it. It’s
definitely not an issue strictly with heroku, rails, or postgres, that I
can find. At this point, since you are not deleting your data base, I’m
quite unsure how you are even getting them to consistently start at 2
instead of 1, or really by this time, depending on how many times you’ve
tested this, even higher numbers.

–
Tamara T.
[email protected]
http://www.tamouse.org

13

On Tuesday, December 29, 2015 at 7:31:36 PM UTC+1, tamouse wrote:

What happened when you tried creating a new test application?

The problem as you’ve stated it is that your first user gets id=2. The
point of the test is to find the minimum set of conditions that
reproduce that behaviour so you can see why it’s happening…

… Then prepare the app for heroku, create a new app at heroku for

this test app, and run it on heroku and see what happens

I just finished to create a new test app as you suggested.
In the test app too first user created got id 2 instead of 1, and there
is
no user with id 1.
I am really puzzled…

I recreated the same conditions of the former application, following the
same steps.
However I did not repeated the error of duplicating the User model and
of
migrating the migration in any topic branch. This means that what is
creating the issue is something else.

I used a new, totally separated public workspace at Colud9.

I tried to figure out what can causing the issue and found just 2
possible
explanations:

  1. I added a new user in my development environment at Cloud9 and
    deployed
    at Heroku without resetting the database at Cloud9
  2. I added a new user at Heroku with the console in sandbox mode.

Point 2 could explain what Colin L. previously suggested, that is that
id
numbers are not reused: is it possible that this rule is effective even
when the user is created with the console in a sandbox mode?

14

Sandbox won’t persist data. It’ll do a transactional rollback. It never
really persists data.

But your quirk is interesting. I’ve tried duplicating it and cannot.

Sent from my iPhone

15

On Wednesday, December 30, 2015 at 10:30:40 PM UTC, James Jelinek wrote:

Sandbox won’t persist data. It’ll do a transactional rollback. It never really
persists data.

As I said earlier, rolling back transactions doesn’t reset sequences on
Postgres (
PostgreSQL: Documentation: 15: 9.17. Sequence Manipulation Functions).
Creating a record in the sandbox would create a gap in the id sequence.

Fred

16

Well, so thank you for helping me and supporting me up to the solution.
I am already looking for and reading documentation on transactions,
sequences and truncations in Postgres, which is new stuff for me. My
next
goal is finding how to reset the id sequence in Postgres.

I seize the opportunity to wish you all a happy new year.

17

On 31 Dec 2015 14:10, “‘krfg’ via Ruby on Rails: Talk” <
[email protected]> wrote:

Well, so thank you for helping me and supporting me up to the solution.
I am already looking for and reading documentation on transactions,
sequences and truncations in Postgres, which is new stuff for me. My
next
goal is finding how to reset the id sequence in Postgres.

Why? As I said before you should not use the id as a numeric value.

Colin

I seize the opportunity to wish you all a happy new year.

–
You received this message because you are subscribed to the Google G.
“Ruby on Rails: Talk” group.
To unsubscribe from this group and stop receiving emails from it, send an
email to [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/rubyonrails-talk/a3d59189-ad1f-4e64-a5d9-7145532695fb%40googlegroups.com
.

18

‘krfg’ via Ruby on Rails: Talk [email protected]
writes:

… Then prepare the app for heroku, create a new app at heroku for
However I did not repeated the error of duplicating the User model and of
2. I added a new user at Heroku with the console in sandbox mode.
This is it, exactly. Sandbox mode inserts the record, but since it’s
running in a transaction, the table is then truncated. Table truncation
in Postgres does not reset the id sequence without a specific directive
to do so.

Point 2 could explain what Colin L. previously suggested, that is that id
numbers are not reused: is it possible that this rule is effective even
when the user is created with the console in a sandbox mode?

–
Tamara T.
[email protected]
http://www.tamouse.org

19

Colin L. [email protected] writes:

Colin
Even further, there are reasons to completely and absolutely ignore
and never even expose the id column for a record, and prefer a UUID
instead. Some of these include:

  • you can’t count on it being the same across databases if you even need
    to migrate your data

  • exposing it to the user can reveal more information about your app
    than you want (“oh, there’s only 550 people signed up, this must be
    useless”)

  • exposing it to the user can open avenues of attack you don’t want to
    open

  • id 1 must be the admin, so we’ll concentrate our attacks there

The emphasis you seem to be showing is causing me to scratch my
head. Why is starting at so absolutely important to you? It is a proper
error to count on a particular record always have an absolute record id
value.

–
Tamara T.
[email protected]
http://www.tamouse.org