By Liliplanet on

Hi,

While building my new site, I imported users, deleted them and then re-imported the database. The problem is that in PHPmyAdmin after user 1 the next user is uid 8890.

Is there perhaps a way to reset the users to start from 2? Does this affect my database load, should I leave it like this?

Look so forward to any reply and thank you.
Lilian

⋅ Categories: Drupal 5.x

Comments

Liliplanet’s picture

Liliplanet commented

Just to mention, I have the Database administration dba.module installed for easy export. How to reset the user and usernode id's? The users also have content type 'profile' attached to their uid.

Looking so forward to any reply.
Lilian

mantyla’s picture

mantyla commented

First, a warning: messing around in the Drupal database is recommended only for experts who know what they're doing. Many fields in tables are used for joining them to other tables, and uid is a prime example: it is used to join users with node, node_revisions, watchdog and others. So be careful.

Now with the warning out of the way, I wish to be clear: you have a situation where you've wiped all users from your system and you want to start the numbering of new users from 2. Is this correct?

If yes, what you need to do is this:

UPDATE sequences SET id = 1 WHERE name = 'users_uid';

users_uid contains the uid of the last new user, meaning the next user will be numbered 2. But note that if you have any users left with uids higher than 2, you're bound to have problems by the time you reach that number.

Liliplanet’s picture

Liliplanet commented

Thank you so much for your reply mantyala. No actually I did not delete the users. I'm just worried that after uid1 the next is uid 8886. Does it matter a lot .. does it affect my database load?

Also exporting the users and importing into another site, just want it nice and clean before I do that? Should I perhaps just leave as is?

Look forward to your reply.
Lilian

mantyla’s picture

mantyla commented

You don't need to worry about the database load. MySQL stores user records in whatever order it considers best, just like all records of any type. There are no requirements that a particular table should be sorted in any way, or that the values of a particular field should be consecutive. If they are, a good SQL engine can use that information to increase efficiency, but in Drupal they're not.

The only thing to worry about when importing users is what I said about table joins. Suppose you have a site with a node created by a particular user, say 173. You delete the user table and import users from another site. That other site may or may not have a user 173, and if it does it is probably a different user, who becomes the author of the node, despite having nothing to do with it.

So, you can import users from other sites, but unless you want to spend a lot of time fixing all the cross-table references, the only time you can do this safely is on a new site before posting any content.

Liliplanet’s picture

Liliplanet commented

Excellent, thank you so much! Wishing you a fabulous day ..

WoozyDuck’s picture

WoozyDuck commented

How this can be done for Drupal 7?

NewZeal’s picture

NewZeal commented

In Drupal 7, the controlling table appears to be sequences. Change the value of the entry as well as the auto increment value. Make sure of the following:

  1. that the value in the table is one less than the auto increment value of the table
  2. clear the cache before you do any processing or add new users
  3. the auto increment value is greater than the highest uid in the users table
fwgrange’s picture

fwgrange commented

I was having issues with the sequence table resetting on me. The best solution I found http://dropbucket.org/node/1387

There you will see this snippet. 

UPDATE sequences SET value = 2;
NEXT
ALTER TABLE sequences AUTO_INCREMENT=2;

Another bit of advice, leveraging the delete_all module is great for deleting users.

salah2020’s picture

salah2020 commented

it works for drupal 7 too (^_^)