By mike_san on

On my new site, I have completed the configuration and now I need to set the reset the NID counter to zero so that my new content begins with node/1

Despite my efforts my new content begins with node/24, is there any way I can reset this?

⋅ Categories: Drupal 7.x

Comments

stefan lehmann’s picture

stefan lehmann
Primary language German
Location Hobart
commented

If it's MySQL you'll have to connect directly to the database eg. via the shell program (mysql) and execute the following query.

ALTER TABLE node AUTO_INCREMENT = 1;

mike_san’s picture

mike_san commented

I logged into PHPMyAdmin and ran the query but after adding new content it was node/26!

stefan lehmann’s picture

stefan lehmann
Primary language German
Location Hobart
commented

There is also the sequences table. I think you have to reset the number in the entry there to 1 as well.

I hope you know what you're doing. There might be already traces of these deleted nodes in other tables, so these normally also have to be truncated and reset as well.

BrijeshParmar’s picture

BrijeshParmar commented

Hi mike_san

Before to run sql " ALTER TABLE `node` AUTO_INCREMENT=1 " in database you have to delete all nodes.
So there is no secure way to do this. it really depends on your site, you'll have to truncate and adjust all tables that have an nid including the sequences table. Remember, it is highly probable that your site become unusable, so don't start without a backup first.

ashwinsh’s picture

ashwinsh
He/Him
Primary language English
Location Pune
commented 
ALTER TABLE `node` AUTO_INCREMENT=1;
ALTER TABLE `node_revision` AUTO_INCREMENT=1;
ALTER TABLE `node_field_data` AUTO_INCREMENT=1;
ALTER TABLE `node_field_revision` AUTO_INCREMENT=1;
klonos’s picture

klonos
he/him
Primary language English
Location 90% Melbourne, Australia - 10% Larissa, Greece
commented

https://www.drupal.org/project/delete_all (also has drush support)

Use cases where that module can be used:

  • This is a test site that the client was using for a period of time, and they must clean it up before starting with real data.
  • You are testing something that creates a lot of nodes (e.g. aggregator), and want to do it over and over again.
  • You created a site in the past and want to replicate it again, but with new content.
jaydarnell’s picture

jaydarnell
he/him
Primary language English
Location Guelph, Ontario
commented

I cannot for the life of me figure out how to use delete_all in D8 to reset the node counter. It deletes the nodes just fine but the node counter doesn't reset.

Jay Darnell - CivicActions
Web Designer / Developer / Illustrator

stefan lehmann’s picture

stefan lehmann
Primary language German
Location Hobart
commented

You won't be able to do that directly from the CMS for good reasons. You'll have to connect to your database with admin privileges and update the database tables as described above.

jaydarnell’s picture

jaydarnell
he/him
Primary language English
Location Guelph, Ontario
commented

So what is the good reason? Unless I am misreading documentation this functionality existed in the module in Drupal 7. Has it simply not been ported to Drupal 8 or are there specific reasons this functionality can’t be added into the Drupal 8 version?

Im working on my first D8 site now and I have access to the database but changing database tables directly makes me a little nervous.

Jay Darnell - CivicActions
Web Designer / Developer / Illustrator

sherlock4727’s picture

sherlock4727 commented

Yes You are Right Jay Darnell. It also makes me nervous.

stefan lehmann’s picture

stefan lehmann
Primary language German
Location Hobart
commented

In a nutshell: Because you simply don't reset that counter as it might damage the data integrity in whichever table a field still references an obsolete node id. Which should not happen, but could potentially happen. That's why you normally just keep incrementing to avoid collisions as it doesn't cost anything really. But I have been in your place where the client or whoever insists that the first node should start with 1. As unappealing it is esthetically, it has its technical reasons.

PS: and I'm pretty sure even in D7 you had to "hack" the database.

sherlock4727’s picture

sherlock4727 commented

Before to run sql " ALTER TABLE `node` AUTO_INCREMENT=1 " in database you have to delete all nodes.
So there is no secure way to do this. it really depends on your site, you'll have to truncate and adjust all tables that have an nid including the sequences table. Remember, it is highly probable that your site become unusable, so don't start without a backup first.

drugan’s picture

drugan commented

Here is how I fixed this issue for the profile entity:
 

  1. Delete all entities of a type using any tools provided by Drupal.
  2. Navigate through the site to check for possible errors.
  3. Go to phpMyAdmin and search for tables starting with you entity type name.
  4. Select all the tables and then choose the Empty action to execute.
  5. Enjoy)

Reset IDs