Trying to restore my forum from a MySQL backup. Need help!

[White_Wolf]

Long story short, my old host's server is ruined and I switched to a new host, and signed up with a Virtual Dedicated Server.
I want to put my SMF forum back up the way it was. So what I did was download everything from the old server in the folder "/forum", and then uploaded into my new server.

Now I need to install the old database. I have a backup of the old MySQL database from just last week, and I am trying to restore it, but I am running into some problems.

The server environment the database file was saved from looks like this:
Linux    (Kernel version 2.6.9-42.ELsmp)
MySQL                       4.1.22-standard
Apache                    version  1.3.39 (Unix)
PERL                        version  5.8.8
PHP                          version  5.2.5

The new server environment I am trying to install the database to looks like this:
Linux                                 Fedora (I believe fedora5)
Mysql                                5.0.27-1.fc5 
Perl-Apache-ASP               2.59-fc5.build82070706.15
Php                                   5.1.6-1.2

On getting a virtual dedicated server, I realize I may have gotten in over my head. The interface is a control panel called Plesk.
With Plesk I have created a database name, created a user account for it, and when I try to import the database file residing on my hard disk, I get some errors.

The first error I get is:

Error
SQL query:

CREATE TABLE `smf_arcade_favorite` (

ID_FAVORITE int( 10 ) unsigned NOT NULL AUTO_INCREMENT ,
ID_MEMBER int( 10 ) unsigned NOT NULL default '',
ID_GAME int( 10 ) unsigned NOT NULL default '',
PRIMARY KEY ( ID_FAVORITE )
) TYPE = MYISAM ;



MySQL said: 

#1067 - Invalid default value for 'ID_MEMBER'

I looked in the SQL file and the first mention of ID_MEMBER is this

# ==========================================================
#
# Database dump of tables in `XXXXXX_forum`
# January 11, 2008, 10:59:18 PM
#
# ==========================================================


#
# Table structure for table `smf_arcade_categories`
#

DROP TABLE IF EXISTS `smf_arcade_categories`;

CREATE TABLE `smf_arcade_categories` (
  ID_CAT int(10) unsigned NOT NULL auto_increment,
  catName varchar(20) NOT NULL default '',
  numGames int(10) unsigned NOT NULL default 0,
  numPlays int(10) unsigned NOT NULL default 0,
  numRates int(10) unsigned NOT NULL default 0,
  gameRate int(10) unsigned NOT NULL default 0,
  catOrder int(10) unsigned NOT NULL default 0,
  special int(10) unsigned NOT NULL default 0,
  memberGroups varchar(255) NOT NULL default '',
  PRIMARY KEY (ID_CAT)
) TYPE=MyISAM;

#
# Dumping data in `smf_arcade_categories`
#

INSERT INTO `smf_arcade_categories`
   (`ID_CAT`, `catName`, `numGames`, `numPlays`, `numRates`, `gameRate`, `catOrder`, `special`, `memberGroups`)
VALUES (1, 'IBP', 0, 0, 0, 0, 0, 1, '0,2,9,4,5,6,7,8'),
   (2, 'phpBB', 0, 0, 0, 0, 0, 0, '0,2,9,4,5,6,7,8'),
   (3, 'default', 0, 0, 0, 0, 0, 0, '0,2,9,4,5,6,7,8'),
   (4, 'Flash247', 0, 0, 0, 0, 0, 0, '0,2,9,4,5,6,7,8');
# --------------------------------------------------------

#
# Table structure for table `smf_arcade_favorite`
#

DROP TABLE IF EXISTS `smf_arcade_favorite`;

CREATE TABLE `smf_arcade_favorite` (
  ID_FAVORITE int(10) unsigned NOT NULL auto_increment,
  ID_MEMBER int(10) unsigned NOT NULL default '',
  ID_GAME int(10) unsigned NOT NULL default '',
  PRIMARY KEY (ID_FAVORITE)
) TYPE=MyISAM;

Not knowing what any of that stuff means, I decided to just manually edit the SQL file, and remove everything in the begining of the file pertaining to the "SMF_Arcade". I then saved it as a seperate SQL file and tried to import it. When I done that I get this error:

Error
SQL query:

# ==========================================================
#
# Database dump of tables in `xxxxxx_forum`
# January 11, 2008, 10:59:18 PM
#
# ==========================================================
#
# Table structure for table `smf_attachments`
#
DROP TABLE IF EXISTS `smf_attachments` ;



MySQL said: 

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '

DROP TABLE IF EXISTS `smf_attachments`' at line 1

So again, the very first peice of text it comes across it finds an error with it.
I really don't know what to do. Any help would be nice.

[青山 素子]

What tool did you edit the file with? It inserted a UTF-8 BOM in the file and MySQL doesn't like that. (Most UNIX/Linux systems don't use the BOM.)

Some things changed between MySQL 4.1 and 5.0 because of Strict Mode. You'll probably need to make some further modifications of the file to clean it up.

Specifically, default values for int columns must be an int. Simply remove the " default '' " part for these. For dates, the default should be changed from 0000-00-00 to 0001-01-01.

That is all I can think of at the moment.

[White_Wolf]

thank you Motoko-chan, for the reply. I'm sorry I am so late acknowledging your reply. I have had the flu really bad.
Anyhow, I had a friend of the website read what you said, and he modified the SQL text, and was able to finally import it, and somehow synchronize the static files with the database (a process I do not understand).

So the database is up, we got our forum back, and I have had to mess with the themes, avatar and attachments to point them to our new directory.

Everything is working now except one little problem: The forum is moving super slow. I'm talking like 10 seconds to render a page with 20 something queries.

I would assume that our new virtual dedicated server is just super slow, but the homepage HTML files move really fast. The links from one html page to the next in our homepage changes as fast as you can click a link. Very fast. But the forum is moving sooo slow. I just know it has something to do with the database. But I know nothing about databases.

Anyhow,I just wanted to thank you for your reply. You guys have always given good prompt advice to us noobs.

[青山 素子]

It might be something as simple as hostname lookups. Try turning those off.

Also, turn on generation time to see how long it is actually taking to build the page itself. Path is "Features and Options -> Layout and Options -> Display time taken to create every page".

[White_Wolf]

I already have host name lookup disabled.
And I already have the "Display time taken to create every page" feature enabled.

Here is a sample of the times it has recorded.

Page created in 4.483 seconds with 25 queries.
Page created in 6.952 seconds with 28 queries.
Page created in 5.257 seconds with 31 queries.
Page created in 5.382 seconds with 22 queries.
Page created in 7.805 seconds with 28 queries.
Page created in 5.885 seconds with 31 queries.
Page created in 5.246 seconds with 23 queries.
Page created in 3.94 seconds with 25 queries.
Page created in 5.093 seconds with 29 queries.
Page created in 4.832 seconds with 31 queries.

Another problem I am having is trying to uninstall old mods.
Anytime I attempt to uninstall, list files, or delete a mod my browser attempts to download a .php file like when I try to uninstall my flashchat integration mod, my downloader points to this address "http://www.xxxx.org/forum/index.php?action=packages;sa=uninstall;package=FlashChatSMF_1-0.zip"

If I select "open" instead of download, it opens a new browser window and takes me to the install/uninstall page with an error warning

Installing this package will perform the following actions:  Type Action Description
1. Delete File ./Sources/Chat.php 
2. Delete File ./Themes/default//Chat.template.php 
3. Delete File ./dologout.php 
4. Execute Modification install.xml Modification parse error
5. Execute Modification - Modification parse error

And some of my mods, like arcade, won't uninstall because it says there is no uninstaller file.

I went to forum maintenance and clicked the "fix forum errors" button and it said

To avoid overloading your server, the process has been temporarily paused. It should automatically continue in a few seconds. If it doesn't, please click continue below.

It then took over a half hour, and finally finished, telling me I had no errors.

[Deaks]

do you still require help?

[White_Wolf]

do you still require help?

Yes I still require help.

I am mainly frustrated over the inability to un-install my mods.

[Rumbaar]

Due to the nature of the move and the system you have in place you may have to manually un-install your mod, the same way some have to manually install mods.

These links should help and you just do the opposite to clean out all the code:
SMF Package Parser
Manual Installation of Mods
How do I modify files?