How to reset userid

[redtailboas]

I ran a test import from yabbse. All worked successfully. However when I use the import tool to cleanup the database and remove all imported data, the next test import starts the userid where the old import left off. I have clicked all the remove session id and options on the import screen.

All user data was removed but if I were to manually add a user, it would now start with 1724 (there were 1722 users imported)

I want to have the vbulletin database start with userid 2 for my live import.

Any ideas

[Jerry]

If ImpEx didn't reset the userid counter (and it tries) then you will have to re-install the target database to get MySQL to reset the counter.

[redtailboas]

Reinstall the vbulletin database ?

[Jerry]

Yes, unless you know how to truncate a table and re-install the admin user that is the best thing to do. It's the internal counter of MySQL not ImpEx or vBulletin.

[denby]

For MyISAM tables TRUNCATE performs a DROP command on the table (removes it from the db) and then recreates it with the original structure, and resets the auto_increment counter. For InnoDB TRUNCATE translates to DELETE instead of DROP and this does not reset the counter. (Most dbs use MyISAM by default but check this.)

For InnoDB tables I think you will have to use pairs of TRUNCATE (or DELETE FROM) and ALTER TABLE queries (see below).

See http://dev.mysql.com/doc/refman/4.1/en/truncate.html &
http://dev.mysql.com/doc/refman/4.1/en/example-auto-increment.html (http://dev.mysql.com/doc/refman/5.0/en/truncate.html)
for more discussion about TRUNCATE and resetting auto_increment fields.

Please note that you must preserve your primary admin user in the user table (usually userid=1, but check yours), otherwise you will lose access to your Admin Control Panel. To reset the auto_increment counter at 2 for the primary key field userid (in my vb3_user table), first we have to delete all the rows with auto_incremented userid values greater than 1 (my admin user's id).

You may not wish to reset the primary key auto_increment fields for all these tables, because you are manually entering some data or some data is not applicable (e.g. you have no attachments to import), so you can leave out the queries that don't apply.

Be sure to amend the code to give your tables the correct prefix (all mine are "vb3_" .) Backup frequently and backup often. I hope this is useful. ;)

For a db with MyISAM tables:
TRUNCATE vb3_usergroup;
DELETE FROM vb3_user WHERE userid > 1;
ALTER TABLE vb3_user AUTO_INCREMENT=2;
TRUNCATE vb3_customavatar;
TRUNCATE vb3_forum;
TRUNCATE vb3_thread;
TRUNCATE vb3_smilie;
TRUNCATE vb3_post;
TRUNCATE vb3_poll;
TRUNCATE vb3_pm;
TRUNCATE vb3_ranks;
TRUNCATE vb3_attachment; For a db with InnoDB tables:
TRUNCATE vb3_usergroup;
ALTER TABLE vb3_usergroup AUTO_INCREMENT=1;
DELETE FROM vb3_user WHERE userid > 1;
ALTER TABLE vb3_user AUTO_INCREMENT=2;
TRUNCATE vb3_customavatar;
ALTER TABLE vb3_customavatar AUTO_INCREMENT=1;
TRUNCATE vb3_forum;
ALTER TABLE vb3_forum AUTO_INCREMENT=1;
TRUNCATE vb3_thread;
ALTER TABLE vb3_thread AUTO_INCREMENT=1;
TRUNCATE vb3_smilie;
ALTER TABLE vb3_smilie AUTO_INCREMENT=1;
TRUNCATE vb3_post;
ALTER TABLE vb3_post AUTO_INCREMENT=1;
TRUNCATE vb3_poll;
ALTER TABLE vb3_poll AUTO_INCREMENT=1;
TRUNCATE vb3_pm;
ALTER TABLE vb3_pm AUTO_INCREMENT=1;
TRUNCATE vb3_ranks;
ALTER TABLE vb3_ranksAUTO_INCREMENT=1;
TRUNCATE vb3_attachment;
ALTER TABLE vb3_attachment AUTO_INCREMENT=1;
I don't think I've missed any of the critical tables. Please LMK. :D

[Jerry]

ImpEx has always attempted to RUN that SQL, it has been in ImpEx since day one.

[Jerry]

ImpEx has always attempted to RUN that SQL, it has been in ImpEx since day one.

Also using truncate is a very bad idea, and shouldn't be done as you remove vBulletin data as well as imported data.

[denby]

I thought you were saying that redtailboas could truncate the user table (if they knew how). Were you using the common term but meaning the use of DELETE FROM instead of TRUNCATE in the SQL?

I found that Impex was not successful at resetting the auto_increment counters (using Delete Session and all imported data under Database cleanup & restart) and this is why I went looking for the SQL. I don't know why it didn't work. My tables are MyISAM.

I have now tried using DELETE FROM followed by the ALTER TABLE auto_increment reset (instead of TRUNCATE) and it works just as well. If you feel it's safer then I guess I can go with that. :)

[Jerry]

I thought you were saying that redtailboas could truncate the user table (if they knew how).

Truncating was the easy bit, it was truncating and reinstalling the admin user afterwards to vBulletin would still work.