trying to reset id from console

40 views
Skip to first unread message

Robert Phillips

unread,
Dec 23, 2017, 11:02:05 AM12/23/17
to Ruby on Rails: Talk
I've got the program in chapter 2.

is there anything i can type in the console to reset the id? I can do e.g. User.delete_all  and delete all users, but if a new user is then created it doesn't have an id of 1 'cos the id doesn't reset.

this User.reset_primary_key   doesn't work either.

Notice how I removed all users I reset the primary key, I added a user, and they got quite a high id.
irb(main):064:0> User.first
  User Load (1.0ms)  SELECT  "users".* FROM "users" ORDER BY "user
=> nil
irb(main):065:0> User.primary_key
=> "id"
irb(main):066:0> User.reset_primary_key
=> "id"
irb(main):067:0> User.first
  User Load (0.0ms)  SELECT  "users".* FROM "users" ORDER BY "user
=> #<User id: 13, name: "f", email: "f", created_at: "2017-12-23 0
irb(main):068:0>


I get this error

C:\rubyarud\hartl\ch2\toy_app>rails db:drop
Permission denied @ unlink_internal - C:/rubyarud/h
Couldn't drop database 'db/development.sqlite3'
rails aborted!
Errno::EACCES: Permission denied @ unlink_internal
bin/rails:4:in `require'
bin/rails:4:in `<main>'
Tasks: TOP => db:drop:_unsafe
(See full trace by running task with --trace)


I notice the error mentions sqlite. I'm using the correct gem file, I think. https://pastebin.com/E2R77amU   I know the gemfile in the book that we are to use moves sqlite into the development and test groups. I have that and am using the gemfile that the book gives.

Is there anything I can do from the console to reset it?

Thanks

Walter Lee Davis

unread,
Dec 23, 2017, 11:27:35 AM12/23/17
to rubyonra...@googlegroups.com
The id is coming from an auto-increment column in your database. There are two options if you want to reset to start at 1:

* You can roll back your migrations until you drop the table,
* or your can use SQL: truncate table [your_table_name] in a SQL console.

Really, though, the numerical ID is utterly meaningless, so it's not clear why you would care that the ID start back at 1 or not. It matters to the database, because it's the primary key, so there's a real need for it to never repeat ever. But unless you're worried about a few digits in a 64-bit numberspace... You have larger issues to worry about if you ever hit the end of that possible id value.

Walter
> --
> 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/128e2eaa-2168-4ae9-b1fc-bcabcbb7a39a%40googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.

Hassan Schroeder

unread,
Dec 23, 2017, 11:41:57 AM12/23/17
to rubyonrails-talk
On Sat, Dec 23, 2017 at 6:02 AM, Robert Phillips
<robert.p...@gmail.com> wrote:

As Walter already pointed out, you shouldn't care about the content
of the id field, but ...

> I get this error
>
> C:\rubyarud\hartl\ch2\toy_app>rails db:drop
> Permission denied @ unlink_internal - C:/rubyarud/h
> Couldn't drop database 'db/development.sqlite3'
> rails aborted!
> Errno::EACCES: Permission denied @ unlink_internal

I would be concerned about not being able to drop and recreate a
database -- sometimes when you're experimenting you *will* get to
a place where you want a clean slate.

That said, I have no idea about Windows permissions; my guess
would be you have the DB open in some other process, but beyond
that -- no idea.

Good luck!
--
Hassan Schroeder ------------------------ hassan.s...@gmail.com
twitter: @hassan
Consulting Availability : Silicon Valley or remote

Colin Law

unread,
Dec 24, 2017, 4:25:22 AM12/24/17
to Ruby on Rails: Talk
On 23 December 2017 at 14:02, Robert Phillips
<robert.p...@gmail.com> wrote:
> I've got the program in chapter 2.
>
> is there anything i can type in the console to reset the id? I can do e.g.
> User.delete_all and delete all users, but if a new user is then created it
> doesn't have an id of 1 'cos the id doesn't reset.

To add to what others have said, if you want an id you can reference
that you have full control over then add another field for that, the
standard id field is not even guaranteed to start at 1 and increment
uniformly, different databases may generate ids in different ways.

Colin

Robert Phillips

unread,
Feb 11, 2018, 8:55:45 AM2/11/18
to Ruby on Rails: Talk


On Saturday, 23 December 2017 16:41:57 UTC, Hassan Schroeder wrote:
On Sat, Dec 23, 2017 at 6:02 AM, Robert Phillips
<robert.p...@gmail.com> wrote:

As Walter already pointed out, you shouldn't care about the content
of the id field, but ...

> I get this error
>
> C:\rubyarud\hartl\ch2\toy_app>rails db:drop
> Permission denied @ unlink_internal - C:/rubyarud/h
> Couldn't drop database 'db/development.sqlite3'
> rails aborted!
> Errno::EACCES: Permission denied @ unlink_internal

I would be concerned about not being able to drop and recreate a
database -- sometimes when you're experimenting you *will* get to
a place where you want a clean slate.


Regarding if I wanted to drop a table.

I think one thing I can do is use DB Browser for SQLite, and delete the table e.g. a table called blahabcs through that.

Then I could do rails destroy model blahabc     

Then could do rails db:migrate

Would that be equivalent or the same as dropping the table?

Walter Lee Davis

unread,
Feb 11, 2018, 10:35:05 AM2/11/18
to rubyonra...@googlegroups.com
Ideally, you would do all of this with migrations. You can run the migration that created the table down and back up again (look at the Rails Guide for Migrations for the exact syntax, but it's something like rake db:migrate:down VERSION=123456, where 123456 is the numerical part of the migration's filename). Then you can simply run rake db:migrate, and since that migration is down, it will be brought back up. The table, regardless which database engine is hosting it, will be dropped entirely and re-created.

That's an over-simplification of what you would do, because you may have made changes to the table after its initial creation in other migrations. So what you would need to do is run all migrations related to this table down in reverse order (same directions as above), going from newest (highest number) to the oldest (the creation of the table). Then run rake db:migrate and you'll be back to an empty table.

Note that if you had foreign keys to this table in other tables, they will all be wrong (orphan) at this point.

If you are doing this in SQLite, then I also imagine you are working in development, so there really is no point to this surgical removal and re-creation of one table. Just delete the entire database (rm db/development.sqlite3), and run rake db:migrate. You'll be back to a new and empty database.

Walter

Hassan Schroeder

unread,
Feb 11, 2018, 11:24:27 AM2/11/18
to rubyonrails-talk
On Sun, Feb 11, 2018 at 5:55 AM, Robert Phillips
<robert.p...@gmail.com> wrote:

>> > I get this error
>> >
>> > C:\rubyarud\hartl\ch2\toy_app>rails db:drop
>> > Permission denied @ unlink_internal - C:/rubyarud/h
>> > Couldn't drop database 'db/development.sqlite3'
>> > rails aborted!
>> > Errno::EACCES: Permission denied @ unlink_internal

> I think one thing I can do is

You can't successfully run a bog-standard Rails command in your
app, and you want to "work around" that?

Honestly, that's a terrible idea that has "future regret" stamped all
over it...
Reply all
Reply to author
Forward
0 new messages