Database Restore

[zan3y]

My question has to do with database restore. I've recently upgraded my vBulletin to the newest 3.6.8 version from 3.6.4. Instead of doing an upgrade, I wanted to do a fresh re-install of vBulletin as well as vBAdvanced on a new database that would be filled by my backup. I backed-up my database using the admin panel from vBulletin, which I have done several times before. The main problem is that my SQL file is too large for phpMyAdmin and I don't have shell access to my server so that too is out of the question. I was wondering if there was a way in which to manually put the data from my old database into my new one using phpMyAdmin. I've noticed that there are several functions in which to "copy", "move" and "import/export" tables into databases but every time I try to do it, I get an error through myPhpAdmin either saying that the "Column count doesn't match value count at row 1." Or that the table cannot be created because it already has been. Thanks for the help in advanced!

Here is the actual error I get when trying to "move" the table 'user' to my new database.

Error

SQL query: Edit

CREATE TABLE `shores_sog`.`user` (
`userid` int( 10 ) unsigned NOT NULL AUTO_INCREMENT ,
`usergroupid` smallint( 5 ) unsigned NOT NULL default '0',
`membergroupids` varchar( 250 ) NOT NULL default '',
`displaygroupid` smallint( 5 ) unsigned NOT NULL default '0',
`username` varchar( 100 ) NOT NULL default '',
`password` varchar( 32 ) NOT NULL default '',
`passworddate` date NOT NULL default '0000-00-00',
`email` varchar( 100 ) NOT NULL default '',
`styleid` smallint( 5 ) unsigned NOT NULL default '0',
`parentemail` varchar( 50 ) NOT NULL default '',
`homepage` varchar( 100 ) NOT NULL default '',
`icq` varchar( 20 ) NOT NULL default '',
`aim` varchar( 20 ) NOT NULL default '',
`yahoo` varchar( 32 ) NOT NULL default '',
`msn` varchar( 100 ) NOT NULL default '',
`skype` varchar( 32 ) NOT NULL default '',
`showvbcode` smallint( 5 ) unsigned NOT NULL default '0',
`showbirthday` smallint( 5 ) unsigned NOT NULL default '2',
`usertitle` varchar( 250 ) NOT NULL default '',
`customtitle` smallint( 6 ) NOT NULL default '0',
`joindate` int( 10 ) unsigned NOT NULL default '0',
`daysprune` smallint( 6 ) NOT NULL default '0',
`lastvisit` int( 10 ) unsigned NOT NULL default '0',
`lastactivity` int( 10 ) unsigned NOT NULL default '0',
`lastpost` int( 10 ) unsigned NOT NULL default '0',
`posts` int( 10 ) unsigned NOT NULL default '0',
`reputation` int( 11 ) NOT NULL default '10',
`reputationlevelid` int( 10 ) unsigned NOT NULL default '1',
`timezoneoffset` varchar( 4 ) NOT NULL default '',
`pmpopup` smallint( 6 ) NOT NULL default '0',
`avatarid` smallint( 6 ) NOT NULL default '0',
`avatarrevision` int( 10 ) unsigned NOT NULL default '0',
`profilepicrevision` int( 10 ) unsigned NOT NULL default '0',
`options` int( 10 ) unsigned NOT NULL default '15',
`birthday` varchar( 10 ) NOT NULL default '',
`birthday_search` date NOT NULL default '0000-00-00',
`maxposts` smallint( 6 ) NOT NULL default '-1',
`startofweek` smallint( 6 ) NOT NULL default '1',
`ipaddress` varchar( 15 ) NOT NULL default '',
`referrerid` int( 10 ) unsigned NOT NULL default '0',
`languageid` smallint( 5 ) unsigned NOT NULL default '0',
`emailstamp` int( 10 ) unsigned NOT NULL default '0',
`threadedmode` smallint( 5 ) unsigned NOT NULL default '0',
`autosubscribe` smallint( 6 ) NOT NULL default '-1',
`pmtotal` smallint( 5 ) unsigned NOT NULL default '0',
`pmunread` smallint( 5 ) unsigned NOT NULL default '0',
`salt` char( 3 ) NOT NULL default '',
`adminoptions` int( 10 ) unsigned NOT NULL default '0',
`lastpostid` int( 10 ) unsigned NOT NULL default '0',
`sigpicrevision` int( 10 ) unsigned NOT NULL default '0',
`ipoints` int( 10 ) unsigned NOT NULL default '0',
`infractions` int( 10 ) unsigned NOT NULL default '0',
`warnings` int( 10 ) unsigned NOT NULL default '0',
`infractiongroupids` varchar( 255 ) NOT NULL default '',
`infractiongroupid` smallint( 5 ) unsigned NOT NULL default '0',
`arcadeoptions` int( 10 ) unsigned NOT NULL default '0',
`challengecache` int( 10 ) unsigned NOT NULL default '0',
`favcache` text NOT NULL ,
PRIMARY KEY ( `userid` ) ,
KEY `usergroupid` ( `usergroupid` ) ,
KEY `username` ( `username` ) ,
KEY `birthday` ( `birthday` , `showbirthday` ) ,
KEY `birthday_search` ( `birthday_search` ) ,
KEY `posts` ( `posts` )
) TYPE = MYISAM

MySQL said: Documentation
#1050 - Table 'user' already exists

Here is the error I get which trying to "copy" the 'user' table into my new database. I also only checked the box that says "Data only."

Error

SQL query: DocumentationEdit

INSERT INTO `shores_sog`.`user`
SELECT *
FROM `shores_msgboard`.`user`

MySQL said: Documentation
#1136 - Column count doesn't match value count at row 1

I apologize if I've posted this issue in the wrong forum.

UPDATE:
I've figured out how to get the tables in manually. I am having to delete each table in my new database install and replacing it with my old database. If I keep doing this hopefully everything will be restored. It is just going to be a pain to manually do each table. Oh well, at least I got it working.

UPDATE:
I've made some progress on importing everything by hand but I was wondering if there was a list of table structure for which the tables use each other. This will greatly help me in moving all the tables with their data to my new database. Thanks again in advanced!

[Dismounted]

Many people have reported success (even with large databases) with MySQLDumper. It can be found here.

[zan3y]

Originally Posted by Dismounted

Many people have reported success (even with large databases) with MySQLDumper. It can be found here.

Thanks! I've checked this out but my main problem is that I don't want to take all the data from my old database because I've changed the file structure and if I do import specific tables it will mess up my vBadvanced install. I just want to be able to pull the tables from my old database that use all the thread, users, statistics, and anything else that just dealt with my actual forums.

[Dismounted]

In that case, using ImpEx may be a good idea.

[zan3y]

I've used ImpEx and it seems to work pretty good. However, there is one problem. When I imported my user names they imported fine but now each user name says, "import_username." Is there anyway to fix this? I've tried to go to "Maintenance" and fix/rebuild the user options but to no avail. Thanks!

UPDATE:
It seems that I got most of my users to work fine, however, 2 of my users say, "imported_username." Does anyone know how to fix this issue or at least point me in the right direction. It only seems to be doing this for administrator users. I noticed that it said that the administrator users can't be fixed at this stage. Does this mean that there is a fix for this issue? Or that ImpEx hasn't been able to implement this yet? Thanks!

UPDATE:
After messing with ImpEx for a little while I was finally able to get almost all of my data restored. I got all my user names + changed over the "imported_username" for my administrators. I am still having trouble getting the "Top Poster" stat to work correctly. For some reason all the other stats are working, such as follows:

Members: 647
Threads: 303
Posts: 2,781
Top Poster: (0)

For some reason the "Top Poster" stat doesn't seem to be picking up. I've tried rebuilding the statistics but it didn't work.

[Dismounted]

Which modification is that? You should contact it's author.

[zan3y]

It's not a modification, it's the default stats for vBulletin. I've got it to work now. It apparently caches every 24 hours. I just bumped that time down to 12 hours and it worked. My only problem now is with smilies. When I tried to import my tables using ImpEx it imported the "code" to use the smilies but not the actual images. I was wondering if there was a way to get around this. I like how these smilies are below the post instead of to the side. Is this one just a mod? Thanks!

[Dismounted]

Just move your smilies over into the new folder.