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

[krfg]

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: "kr...@example.com", 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?

[Colin Law]

On 26 December 2015 at 15:52, 'krfg' via Ruby on Rails: Talk

<rubyonra...@googlegroups.com> 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

[krfg]

On Saturday, December 26, 2015 at 10:44:44 PM UTC+1, Colin Law 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'?

[Hassan Schroeder]

On Sun, Dec 27, 2015 at 1:29 AM, 'krfg' via Ruby on Rails: Talk
<rubyonra...@googlegroups.com> 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 Schroeder ------------------------ hassan.s...@gmail.com
http://about.me/hassanschroeder
twitter: @hassan
Consulting Availability : Silicon Valley or remote

[krfg]

On Sunday, December 27, 2015 at 7:17:17 PM UTC+1, Hassan Schroeder 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!

[Tamara Temple]

'krfg' via Ruby on Rails: Talk <rubyonra...@googlegroups.com> writes:

> On Sunday, December 27, 2015 at 7:17:17 PM UTC+1, Hassan Schroeder 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!

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 Temple
tam...@gmail.com
http://www.tamouse.org

[krfg]

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

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.

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"

[Tamara Temple]

'krfg' via Ruby on Rails: Talk <rubyonra...@googlegroups.com> writes:

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

>> 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.
>>
>>

> 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"

What happened when you tried creating a new test application?

[Frederick Cheung]

On Sunday, December 27, 2015 at 6:17:17 PM UTC, Hassan Schroeder wrote:
> On Sun, Dec 27, 2015 at 1:29 AM, 'krfg' via Ruby on Rails: Talk
> <rubyonra...@googlegroups.com> 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

[krfg]

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.

[Tamara Temple]

'krfg' via Ruby on Rails: Talk <rubyonra...@googlegroups.com> writes:

> 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.

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.

[krfg]

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 Law 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?

[jel...@gmail.com]

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

--
You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email to rubyonrails-ta...@googlegroups.com.
To post to this group, send email to rubyonra...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/3246c189-392c-4bef-a12a-b8b51174032a%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Frederick Cheung]

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 ( http://www.postgresql.org/docs/current/static/functions-sequence.html). Creating a record in the sandbox would create a gap in the id sequence.

Fred

> But your quirk is interesting.  I've tried duplicating it and cannot. 
>
> Sent from my iPhone
>
> On Dec 30, 2015, at 4:26 PM, 'krfg' via Ruby on Rails: Talk <rubyonra...@googlegroups.com> wrote:
>
>
>
>
> 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

[Tamara Temple]

'krfg' via Ruby on Rails: Talk <rubyonra...@googlegroups.com> writes:

> 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.

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 Law 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?

[krfg]

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.

[Colin Law]

On 31 Dec 2015 14:10, "'krfg' via Ruby on Rails: Talk" <rubyonra...@googlegroups.com> 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 Groups "Ruby on Rails: Talk" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to rubyonrails-ta...@googlegroups.com.
> To post to this group, send email to rubyonra...@googlegroups.com.

> To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/a3d59189-ad1f-4e64-a5d9-7145532695fb%40googlegroups.com.

[Tamara Temple]

Colin Law <cla...@gmail.com> writes:

> On 31 Dec 2015 14:10, "'krfg' via Ruby on Rails: Talk" <
> rubyonra...@googlegroups.com> 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

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.