How do I import all users from one VB to another?

[buro9]

I need to import all users from one VB to another.
Both are the same version (2.2.1), and the second one is currently empty, being a clean install on my home desktop (once I've emulated this process locally I'll do it on my live site).

I want users only (no posts, threads, polls, templates, etc).

At the moment I guess all I have to do is to move the user and userfields tables.

But then comes the questions, of what do I have to do make this data clean now? i.e. user.posts will still be populated with the post count on the old board... and user.styleid may refer to styles that don't exist.

Which columns in user would have to be 'defaulted' in order to enable the new version of the board recognise all of the users and for this data to be clean?

Please help, I've waited for ages for a vB to vB imported just to do this task, but since one hasn't come along I need to do it manually, and for that I need advice on the db tables and data integrity.

I am basically nuking a board (because of data corruption in posts, threads and sessions) and starting afresh, but want to carry over the barest info possible, being user details.

Cheers

David K

[Martz]

Well, to answer the easy part first...

The post counts can be recalculated if you run "Update counters" in the admin cp. This will go through each user and correct the post count for the number of posts they have (zero).

Technically, all you should need to do is to "Backup Database" in the admin cp, and just select the tables you want (i.e. user and userfields). This will give you an SQL file. If you cant do this, you will need to find out the correct mysqldump command to dump a the required tables only.

You can then insert this into your new forum (via telnet/SSH using the mySQL client: ./mysql -u USERNAME -p DATBASENAME < mybackupfile.sql) and your users will be imported.

Haven't you considered that instead of starting again it may be easier to repair the database?

[buro9]

It isn't possible to repair the database, whilst the tables are now fine, in the time that they were damaged the data integrity between tables has become shot and rows exist in one place without corresponding rows in other places.

So, to salvage I want to just move my users.

I have already run update counters for the post counts, but this didn't affect all records... some 500 users are left with post counts > 0. Which shows that it isn't working properly even on a new database with no threads or posts!!.

I can manually run an update query to just nuke them all to 'zero' and is in fact what I will probably end up doing. But this is where my line of questioning comes in... what should be done (which columns nuked and to what value) to be able to move users en masse from a damaged installation of vb to be able to have a clean and safe installation of vb?

It's an often asked question, but one that is never fully resolved. I still need this resolving, and I would guess that others do too.

It shouldn't be that hard to move users like this, and there should be an import script to do it... but lacking either... I still would gratefully have advice from anyone who can offer it.

The last thing I want is more data integrity problems, so I can't have any columns in user point to anything else, without being a default value (avatars spring to mind here!).

Cheers

David K

[buro9]

for those of you who also need to do this in future, here is what I did...

1) Backup your current database by dumping your mysql.
2) Wipe everything (drop all tables and delete the php files).
3) Install vBulletin from scratch, make sure this is the version that was in use before you dumped everything so that you are sure that the user tables is of the same coumns, etc.

Then....

IMPORT USER
IMPORT USERFIELD

UPDATE user SET posts=0 WHERE NOT(posts=0)
UPDATE user SET styleid=1 WHERE NOT(styleid=1)
UPDATE user SET lastpost=0 WHERE NOT(lastpost=0)
UPDATE user SET avatarid=0 WHERE NOT(avatarid=0)
UPDATE user SET maxposts=-1 WHERE NOT(maxposts=-1)
UPDATE user SET inforum=0 WHERE NOT(inforum=0)
UPDATE user SET nosessionhash=0 WHERE NOT(nosessionhash=0)

for your users to be recognised, you need to have a row in both USER and USERFIELD... these pieces of SQL find stray users (with rows in one but not the other).

\\ Find stray users
select user.* from user
LEFT JOIN userfield ON user.userid=userfield.userid
where userfield.userid is NULL;

select userfield.* from userfield
LEFT JOIN user ON userfield.userid=user.userid
where user.userid is NULL;

Then when you go to your forum, you will have a new forum with all of your users still intact.

Note that this doesn't bring over their avatars... but hey, this is better than nothing. One day vBulletin might release a proper script to do this that merges user databases, keeps avatars and checks data integrity for you... until then, this will see those of us whose db is corrupt through.

Cheers

David K

[Thunderpuck]

Unbelievable !!!

I simply cannot believe that vBulletin does not have an import script to support their own product! Import scripts for every other forum out there - but not their own? This alone is almost enough to make me go back to UBB! Un-Friggin-Believable!

[Lats]

Thunderpuck, vB call it moving servers - it's the same thing.


Lats...

[Jake Bunce]

just move the entire database.

http://www.vbulletin.com/manual/movingservers.html

[JoshFink]

Re: Unbelievable !!!

Originally posted by Thunderpuck
I simply cannot believe that vBulletin does not have an import script to support their own product! Import scripts for every other forum out there - but not their own? This alone is almost enough to make me go back to UBB! Un-Friggin-Believable!

Is it really necessary to be so dramatic?

[Floris]

Re: Unbelievable !!!

Originally posted by Thunderpuck
I simply cannot believe that vBulletin does not have an import script to support their own product! Import scripts for every other forum out there - but not their own? This alone is almost enough to make me go back to UBB! Un-Friggin-Believable!

Why would they make an import script for their own product .. hehe And why one that lets you import from version A to version A ?? Just sounds so illogical.

You can use an MySQL query to get the user table from the database and import it into another database. See mysql.org for help on mysql query's

[Thunderpuck]

Thanks smachol !

Thanks for the reply and explanation smachol!

CLICK