Restoring a Database - help needed

[Nixxi]

Hi all,

We recently lost our forum admin and board owner in sudden and tragic circumstances. Since then have been muddling through but unfortunately, our admin was the only person with knowledge of SMF and we are now having problems.

I'd also like to say that myself and one other person have been trying to fix things for many hours over a week - so, we have really tried - but lack the knowledge.

A mod was in the admin section when his computer crashed, and since then the forum has not been accessible. We managed to get a back up (from a time when the forum worked) from the hosts, but initially had no idea how to install it. The back up is in the webspace but we were unable to install it from PhpMyAdmin as it wouldn't let me browse to a file on the site. We don't have the ftp password.

I managed to install MySqlDumper which lets me get to the backup file, but can't install the backup as it keeps giving a fatal error.

I've also tried the Repair tool from this site. Two files have been dropped from the database, which seemed a good idea at the time, but subsequent reading indicates it wasn't.

So, I'm going around in circles. I know 200% more than last week, but not enough. I would be very grateful if someone could point me in the right direction.

Thanks in advance!

Nicola

[Sir Osis of Liver]

phpmyadmin will allow you to import a database backup from your local computer, not your website.  MySqlDumper will not help if your production db is damaged, you must import the host backup.  Have you tried repairing the db with phpmyadmin?

[Nixxi]

Apologies, forgot to say, we are using SMF 1.1.19

The uls is www.totherplace.co.uk [nofollow]

The Log in screen is available but after entering the login info the following error displays:

Database Error: Table 'totherplace_co_.smf_ban_groups' doesn't exist
File: /customers/9/4/5/totherplace.co.uk/httpd.www/Sources/Security.php
Line: 342

This refers to the dropped table but originally complained about the attachments folder.

[Sir Osis of Liver]

Why were the tables dropped?  Do you have the host db backup anywhere that you can d/l it to your computer?  Should be accessible via cpanel.

[Nixxi]

phpmyadmin will allow you to import a database backup from your local computer, not your website.  MySqlDumper will not help if your production db is damaged, you must import the host backup.  Have you tried repairing the db with phpmyadmin?

Hi and thanks. 

I don't have the ftp password.

In the Structure tab, I selected all the tables and chose "repair table".

Is there anything else that I could try?

[Nixxi]

Why were the tables dropped?  Do you have the host db backup anywhere that you can d/l it to your computer?  Should be accessible via cpanel.

The tables being dropped were a mistake.

Yes the host db backup is on the website. I managed to move it into the backup folder of SQLDumper, but couldn't use it for the reasons you explained.

[Sir Osis of Liver]

Do you know which tables were dropped?
Is your original db still in place?
Would appear you have access to your host cpanel.  If so, you should be able to reset the FTP password.
Where did your host put the db backup?  Can you get to it with cpanel file manager?

[Nixxi]

The original database is in place, but even before tables were dropped there were problems. As these occurred when a computer crashed, I wonder if it became corrupted.

I can't reset the FTP password as I don't have access to the email address - that went when the admin suddenly passed away. We know we are going to need to move, but just want to get the forum back until we can facilitate that.

Yes, I can get to the back up with file manager. Its in the "root"? - where settings.php is.

THank you very much for trying to help

[Sir Osis of Liver]

Use file manager to d/l the db backup to your local computer, then you should be able to import it from there with phpmyadmin.

[Nixxi]

Thanks, but the compressed file is too large - it's 200mb, which is over the limit.

[Sir Osis of Liver]

Does your cpanel allow you to restore a db backup?  It would be in the MySQL section.  You can also ask your host to restore the backup, most will do so.

[Nixxi]

Apologies if I'm not understanding you correctly - the only way I know to do restore a db backup is by importing it using the import function of phpmyadmin. This is what I tried at the very beginning, but was unable to do so due to the restrictions on the file size. I don't know of another way.

I will ask the Hosts. Thanks I didn't realise they would do this.

[Kindred]

you could try zipping it...

[Sir Osis of Liver]

Thanks, but the compressed file is too large - it's 200mb, which is over the limit.

[Kindred]

whoops...  missed that. 

[Illori]

which version of SMF are you using?

[Horatio Alger]

which version of SMF are you using?

It's currently running 1.1.19.

[Kindred]

lol... now you missed it Illori...

Apologies, forgot to say, we are using SMF 1.1.19

The uls is www.totherplace.co.uk

[Nixxi]

There's bad news and .... a slight improvement.

Our hosts said that they could not install the back up for us and pointed us to a guide explaining how to do it via phpmyadmin. However, on checking we found that the compressed file size is actually 112mb - sorry, I don't know where I got 200mb from.

So I attempted to upload the backup and got the following error:

Error

SQL query:

CREATE TABLE `smf_attachments` (
`ID_ATTACH` int( 10 ) unsigned NOT NULL AUTO_INCREMENT ,
`ID_THUMB` int( 10 ) unsigned NOT NULL DEFAULT '0',
`ID_MSG` int( 10 ) unsigned NOT NULL DEFAULT '0',
`ID_MEMBER` mediumint( 8 ) unsigned NOT NULL DEFAULT '0',
`attachmentType` tinyint( 3 ) unsigned NOT NULL DEFAULT '0',
`filename` tinytext COLLATE latin1_general_ci NOT NULL ,
`size` int( 10 ) unsigned NOT NULL DEFAULT '0',
`downloads` mediumint( 8 ) unsigned NOT NULL DEFAULT '0',
`width` mediumint( 8 ) unsigned NOT NULL DEFAULT '0',
`height` mediumint( 8 ) unsigned NOT NULL DEFAULT '0',
`file_hash` varchar( 40 ) COLLATE latin1_general_ci NOT NULL DEFAULT '',
PRIMARY KEY ( `ID_ATTACH` ) ,
UNIQUE KEY `ID_MEMBER` ( `ID_MEMBER` , `ID_ATTACH` ) ,
KEY `ID_MSG` ( `ID_MSG` )
) ENGINE = MYISAM AUTO_INCREMENT =4707 DEFAULT CHARSET = latin1 COLLATE = latin1_general_ci;

MySQL said: Documentation
#1046 - No database selected

The error message from the site is now:

8192: preg_replace(): The /e modifier is deprecated, use preg_replace_callback instead
File: /customers/9/4/5/totherplace.co.uk/httpd.www/Sources/Load.php(198) : runtime-created function
Line: 3

Is it possible to help with this, please? It's meaningless to me

[Nixxi]

So, stabbing around in the dark, I know. I used File Manager to upload a new attachments folder and contents (from the back up) and tried to upload the backup again. This time the error message was slightly different:

Error

SQL query:

CREATE TABLE `smf_attachments` (
`ID_ATTACH` int( 10 ) unsigned NOT NULL AUTO_INCREMENT ,
`ID_THUMB` int( 10 ) unsigned NOT NULL DEFAULT '0',
`ID_MSG` int( 10 ) unsigned NOT NULL DEFAULT '0',
`ID_MEMBER` mediumint( 8 ) unsigned NOT NULL DEFAULT '0',
`attachmentType` tinyint( 3 ) unsigned NOT NULL DEFAULT '0',
`filename` tinytext COLLATE latin1_general_ci NOT NULL ,
`size` int( 10 ) unsigned NOT NULL DEFAULT '0',
`downloads` mediumint( 8 ) unsigned NOT NULL DEFAULT '0',
`width` mediumint( 8 ) unsigned NOT NULL DEFAULT '0',
`height` mediumint( 8 ) unsigned NOT NULL DEFAULT '0',
`file_hash` varchar( 40 ) COLLATE latin1_general_ci NOT NULL DEFAULT '',
PRIMARY KEY ( `ID_ATTACH` ) ,
UNIQUE KEY `ID_MEMBER` ( `ID_MEMBER` , `ID_ATTACH` ) ,
KEY `ID_MSG` ( `ID_MSG` )
) ENGINE = MYISAM AUTO_INCREMENT =4707 DEFAULT CHARSET = latin1 COLLATE = latin1_general_ci;

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

[Sir Osis of Liver]

If you can pm cpanel access (url, username, password), will have a look when I'm on tonight.

[Nixxi]

Many thanks. I have done this 

[Sir Osis of Liver]

Just got on, will have a look.

[Sir Osis of Liver]

Wow. 

- Ok, first thing is, you don't have FTP access, and that can only be accomplished by having access to the owner's email account, which you don't have.  Should be able to work around that using file manager.

- Your host is running php 5.6.16, which will cause problems with SMF 1.x.  You can revert to php 5.5, but I don't believe that will help.  You'll need to upgrade to SMF 2.0.11.

- Your database is 6.7gb, 6.6gb of which is smf_log_errors (contains 16,777,215 errors).  I've emptied the table, now the db is 94.6mb, which is a manageable size.  I don't see a db backup in your forum root.  I've exported the db as sql and gz, looks like one or both may be useable. 

Will see if I can get your database running on a test install.

[Sir Osis of Liver]

I have it running here.  Replaced smf_ban_groups with a clean table, upgrade from 1.1.21 to 2.0.11 was successful.  Everything works, but several years of posts are missing.  If you click on last post link in any active topic on board index, you'll get an error.  Look in the topic and you'll see no recent posts.  Where is the db backup your host provided?

n/m found it.  Will see if I can do anything with it.

[Sir Osis of Liver]

<sigh>

Downloaded what appears to be your host's backup - totherplace_co_.sql.gz dated 1-3-16.  It's 107.16mb, unzips to 5.56gb.  Don't have anything that will open the file, so can't edit it manually, and it's well over limit to import.  Will ask my host to import it to a scratch database, and try upgrading it.  They should get to it by the time I get on tomorrow.  If you have a different db backup, let me know where it is.

[Horatio Alger]

OMG, thank you so much for your help,  Sir Osis. Things are looking much better after your input. Nixxi and I couldn't have done that in a month of Sundays. Thanks again, really appreciate your support.

[Sir Osis of Liver]

Host has not imported db as yet, they may not get to it until Mon.  Can't do anything further until they do.  Will not be on again until late Sun.  Meanwhile, would be helpful if you could kick around the test install and see if there are any other problems or missing content.


Update:  My host has refused to import the db backup because it is so huge (they will import 1gb max).  Have asked them if they can empty or drop smf_log_errors, then import it, but would be surprised if they do.  You must ask your host if there is any way to restore the backup from your account.

[Nixxi]

Will do.

Once again, thank you for your help. This appears to have been a good deal of work, and we greatly appreciate it.

[Sir Osis of Liver]

Re your pm, I see the forum is up but cannot register, get database error.  What did you do to get it running? 

No, you do not want to upgrade to 2.1, it's only at beta 2, and not recommended for production forums.  You'll need to upgrade to 2.0.11, your host is running php 5.6, as per this.

Can you give me admin access to the forum?

[Sir Osis of Liver]

Nixxi, if you get back to this, was able to get my host to import the 6.6gb database backup your host provided.  Cleaned it up, connected it to clean 1.1.21 install, upgraded to 2.0.11.  Everything works, not throwing any errors, last post is 4 Jan.  Your 1.1.19 install is a mess, generating a lot of errors, registration doesn't work, needs to be upgraded to 2.0.11.  Not a good idea to upgrade the damaged database.  If you want to do anything with this, let me know.

[Nixxi]

Many apologies for not getting back to you last night. I have been away for a short holiday and have been managing to post here while away. Anyway, I was travelling home, hence the drop out.

To take recent questions in order:

1. I didn't do anything other than described in this topic! I assumed that it was something you had done

2. Although there is a database error, it's possible to log in, and members have been posting since Saturday when the forum reappeared. The only problems are with Chrome OS and iOS where the page has to be refreshed after logging in to reach the forum. Otherwise, everything seems fine, and nothing is missing.

3. Yes, please. We need to move forward. Many thanks.

[Sir Osis of Liver]

Will try to upgrade to 2.0.11.  I've hacked Amanda's account to get admin access, will reset it when I'm done.  Unable to d/l backup of forum files, server is too slow, so I've copied everything into /1.1.19 backup.  Database will not upgrade as is, but should be able to fix it.  You're in maintenance mode, this may take a while.

[Sir Osis of Liver]

No go.  Am unable to upload folders with your cpanel file manager, just files, and it's very slow.  Need ftp access, and doesn't appear to be any way to do that without access to owner's email account.  Fixed the database, restored Amanda's password, and left admin account for Sir Osis.  You're back to 1.1.19.  Going off now, will have another try tomorrow.  What you really need to do is create a new host account so you have complete access, do a clean install, and import the database.  Meanwhile you should disable error logging (Admin -> Features and Options -> Enable error logging), or your database will bloat up again.

[Nixxi]

A new domain and hosting account was always our intention once we had regained access to the forum, so we are going to start dealing with this straight away. We weren't sure if we would be able to use the old database, but your previous post indicates that we can, so that's great news.

In the meantime, there doesn't seem much point in flogging a dead horse (trying to upgrade again) when we can do a clean install from new hosting, so please don't waste your time Sir. O. We sincerely thank you for what you have achieved already.

We hope you won't mind, but we will probably (almost certainly) need further help setting up/moving, although we will obviously try support topics and the manual first.  On that note, I won't mark this topic "solved" just yet, and I can revisit later.

[Kindred]

please note... you can NOT do a clean install of 2.0
You would need to install 1.1.19 on the NEW server -- import the existign 1.1.19 database and then run reapir_settings.php

THEN you can do the upgrade on the NEW host...

[Nixxi]

Kindred, you are a mind-reader! I just came back here to ask that very question (which version to install).

Thanks

[Sir Osis of Liver]

I added the missing table (smf_ban_groups) from the upgraded 2.0.11 database to your 1.1.19 db, shouldn't cause any problems.  upgrade.php will not run if the table is not there.