Database requires an upgrade

[pete.thomas1962]

Hi, I'm new to the forum. On the advice of our web host I recently upgraded smf to version 2.0.11. Since the upgrade when I click on a link I get the following error:

Unknown column 'b.id_profile' in 'field list'

When I click on messages, there is a more detailed error report:

Note: It appears that your database may require an upgrade. Your forum's files are currently at version SMF 2.0.11, while your database is at version 2.0 a. The above error might possibly go away if you execute the latest version of upgrade.php.

We're a voluntary organisation, with very little IT knowledge. I've searched the forum and found a post suggesting the download of the 2.0.11 large upgrade zip file, however the instructions say to upload it using FTP to the root of the smf. There is an ftp function in our web host control panel, which I assume I need to use, but I don't know where to save the upgrade file or how to run it.

I'm hoping that someone might be able to help me by giving me simple instructions on how to make the upgrade (for a novice user).

Any help would be greatly appreciated.

Thanks in advance
Pete

[Kindred]

1- how did you upgrade?  Did you use the PATCH file in the package manager (like any other mod) or did you do a full upgrade, using the "large upgrade" archive?

2- If you used the patches, what did you upgrade from originally? (in other words, at what version were you at when you applied the first patch and the subsequent patches?)

It sounds like you upgraded using the patches...

regarding the upgrade....
http://wiki.simplemachines.org/smf/Upgrading

What mods do you have installed?

[pete.thomas1962]

Hi Kindred, thanks for your response. I used a program called softilicious, which is part of our web hosts control panel. Hope this makes sense?

I'm not sure what mods have been installed because of the way the upgrade was handled by softilicious. Sorry I can't be more specific.

Cheers
Pete

[Kindred]

Yup, that is actually the problem.

The auto upgrader didn't take into account mods, etc and replaced files willy nilly...

Your root to recovery now is to indeed do the full upgrade manually, and, in the future, don't use the auto upgrader...  Use the patch files that we release.

[pete.thomas1962]

Thanks Kindred, we live and learn

[Kindred]

 

[pete.thomas1962]

I could still do with some help, if possible . Until a week ago I hadn't heard of SMF or MySQL and am on a steep learning curve. It was only when the forum stopped working (over three weeks ago now) that I started looking into how to fix it. Previously all admin had been through the web host's control panel (lunarpages). The person who originally set up the forum is no longer with us and we don't receive any update emails (such as the patches Kindred referred to). Could someone please advise:

1) Are there any user friendly instructions for the manual update?
2) Do I need to reverse the changes made by the auto upgrader or just run the upgrade install?
3) How do I ftp the upgrade files into the control panel

Any advice would be greatly appreciated.

Thanks in advance
Pete

P.S. the forum is dedicated to remembering the Manchester Regiment's contribution to the First and Second World Wars. Stats for the forum are 55,496 Posts in 7,859 Topics by 3,076 Members. All the moderators are volunteers who either served in the Regiment or had family connections.

[Illori]

take a look at http://wiki.simplemachines.org/smf/How_to_upload_a_fresh_set_of_files

there are other FAQ's on the wiki that may be useful to you and help to answer some of your other questions.

[pete.thomas1962]

Thanks, that's really helpful 

[pete.thomas1962]

Hi, we're at step 3 of the install but have got a critical error:

The installer has detected another version of SMF already installed with the specified information. If you are trying to upgrade you should use the upgrader not the installer.

Otherwise you may wish to use different information, or create a backup and then delete the data currently in the database.

Could anyone please advise us where to go from here? We don't want to lose the database.

Best regards
Pete

[Illori]

then you downloaded the install package rather then the large upgrade package. please read the instructions again and hope you have a backup you can restore...

[pete.thomas1962]

Hi, we managed to complete the install. So now we have a fresh install and we pointed it to the correct database but the original forums is now showing as a new forum (i.e. not drawing the old data from the database)(please see attached link). However when we go to the web host control panel, the MySQL database is still the same size as it was before the install which suggests it is intact. Do you know how to link the two?

http://themanchesters.org/forum/index.php [nofollow]

(we have a full backup of the database)

Cheers
Pete

[Sir Osis of Liver]

You must have created a new database and run the install instead of upgrade.  You have to put the correct database credentials to your production db in Settings.php.  You can edit manually, or use repair_settings.php.

[pete.thomas1962]

Thanks Sir Osis, I'll try tomorrow and let you know how we get on.

Cheers
Pete

[pete.thomas1962]

Sorry I'm painfully slow at this.

Problem 1: I've tried to reset the admin password (the person who set up the forum is no longer with us) but couldn't. I think I will need this at some point in the process.

Problem 2: Next I tried to use the repair settings tool. My settings are in the following root: public/admin/ so I downloaded the file here. After a bit of experimenting with the site url I managed to open SMF 2.0 Settings Repair Tool (screenshot attached) but it has not found any of the settings so the URL is probably incorrect. There is a note saying: We were unable to detect your Sources folder. This is crucial for this tool to work. Please be sure it exists. I've checked and it exists but in public/forum.

Problem 3: When I tried to fill in the information required by the settings repair tool I can't find the following information:

• Table prefix
• Cached Directory
• Sources Directory

Could someone please advise. As always, any help would be greatly appreciated.

Cheers
Pete

[Shambles]

Put the repairsettings tool in the same directory as your Sources folder, and run it from there. No point trying to complete any of the fields where you currently have it.

[Kindred]

Pete....  I think you have gone about this all wrong.

(oh, and Shambles... no. repair_settings.php will NOT run from the Sources directory)



1- you appear to have tried to use a fresh INSTALL instead of the upgrade archive.
2- you set up a fresh install with a clean database when you should not have touched the database or database information at all.

You have made this process several hundred time more difficult than it should be.

At this point...  I *HOPE* that your original database is still untouched.

put repair_settings.php in the ROOT FOLDER of your *FORUM*.
That would be in the same directory where you see index.php, Settings.php and the /Sources/ and /Themes/ directory (amongst others)
you would access it by going to

Code Select Expand


themanchesters.org/forum/repair_settings.php

DO NOT LEAVE  THE FILE THERE. If you don't get this handled right away, then DELETE THE FILE and put it back when you are ready to try again.


If the file is in the correct location...
then you will need to enter the CORRECT database information to access your original database.
enter it and press Save.
don't worry about the paths and URLs just yet

now that the database information is correct, you can deal with the paths and URLs... and then press save again

[Illori]

Put the repairsettings tool in the same directory as your Sources folder, and run it from there. No point trying to complete any of the fields where you currently have it.

he did not say to run it from the Sources folder, but from the same directory that HAS the sources folder in it.

[Kindred]

you are right... that is one way to read it... but that's not how I read it originally -- and given the OP's confusion, I suspect that is not how he would read it.

[Shambles]

(oh, and Shambles... no. repair_settings.php will NOT run from the Sources directory)

Which is a good thing I didn't say it then 

[pete.thomas1962]

Hi all, apologies for slow responses - different time zones (UK).

thanks for your advice. I put the file in the correct folder, ran the link, checked and saved the settings and then deleted the repair_settings.php file from the directory. Unfortunately it showed that everything was pointing to the correct place but when I click on the forum's URL, it still isn't picking up the MySQL database.

I think the database is ok because it is still the same size as before the changes (5GB) and I also ran MySQL "Check a database" which came back with "OK". I have a full backup in case the database is unusable.

Kindred, with regard to the upgrade/install mixup, I'm not sure how but I misread your first post and took "full upgrade" to mean install (I'm feeling very stupid now )

Am I missing something? Any help greatly appreciated.

Cheers
Pete

[shred]

Hi All,

I am trying to help this group out but would like some advice prior to carrying out any changes.

We have a full back up (made prior to the new SMF install) of the old forum database which is now in the group's website directory. We have asked Lunerpages to create a new MySQL database using the backup and to name the database ****48_smfnew. The SMF currently is looking at ****48_smf1. When Lunerpages let us know that the old database is available I am presuming that pointing the SMF to the ****48_smfnew database using the repair_settings will bring the forum back up.

Or is there more to this? For example would following the instructions given on another part of the forum help? http://www.simplemachines.org/community/index.php?topic=400356.0

Thank you all for your time and patience.

[Sir Osis of Liver]

The forum was, at an earlier point, connected to a scratch database.  All you need to do is import the db backup into the scratch db, and your content should be restored.  Can't tell where it's at presently, as you're in maintenance mode.

[shred]

Thank you for the response Sir Osis of Liver(enough Bud will do that to you, but what a way to go)

We have taken the forum out of maintenance.

Current settings:


MySQL database on the server:

[Sir Osis of Liver]

Well, the good news is, your forum is up and running with a scratch database.  The bad news is, it's still not connected to your production database.  If you can pm access to your Lunarpages account, I'll have a look.

[shred]

Sir Osis,

You are a gentleman and a scholar.

PM on its way to you.

[Sir Osis of Liver]

   Uh, oh.  Don't suppose you have a good database backup from before you started changing things?  The db your forum is connected to, thema48_smf1, contains two sets of tables, both of which contain data.  The smf1 tables appear to be your current db, contains approx 800 members (mostly bots), the messages and topics tables are empty (didn't check the rest).  That's what you're currently connected to.  The other set of tables, smf_ , contains just over 3000 members, a populated messages table, almost 17 million logged errors (the db is mostly errors), will not run when connected to forum.  Settings table shows it at 2.0, but it errors like 1.1.x. 

Since I don't know what you and/or your host have done up this point, it's difficult to guess what went wrong, but your db is not useable.  Looked around your account and don't see anything that looks like a db backup.  As per your pm, host told you this -

Also, the backup provided backup-2.13.2016_03-01-04_thema48.tar.gz contains a full copy of your hosting account. You need to inform us the exact files/databases which need to be restored from the backup.

The cpanel backup feature creates a zip that's downloaded to your local computer, but apparently does not save a copy online.  If it's a complete account backup, it should include backups of all your databases.  You'll have to ask Lunarpages if they have a backup of thema48_smf1, and if they can restore it. 

[shred]

Doesn't sound good.

I thought that the backup shown here under would contain the database


I downloaded a backup of the actual SQL database on the 7th March prior to any changes. I am uploading the file now.

I cannot thank you enough for giving your time to help, but for now Thank You!

[shred]

Sir Osis,

Upload complete. File can be found under "Database Backup".

[Sir Osis of Liver]

Will have a look tonight.  How did you make the backup?

[shred]

Good Evening Sir Osis,

CPanel>backups and click on the database. Downloaded as a .gz file.

Cheers.

[Sir Osis of Liver]

You'll have to ask your host to import the backup.  The cpanel backup feature won't do it, it's too big for phpmyadmin, even tried BigDump, but it chokes on the 4.9gb _log_errors table.  Ask them to import it into thema48_smf20.  It's a new, clean db, and forum is connected to it.

I removed several sets of install and upgrade files from various SMF installs.  It's a major security risk to leave them on your server.  If this backup is good and import works, we'll have a little chat about forum maintenance and security.

[Kindred]

Sir osis...I can't do it, because I don't have access to my pc for a few days... But you could do what Margarett usually does...   Import the database to a local install and then truncate the error log table. That should make it importable to the server

[shred]

Guys,

Many thanks for continued support with this. We have raised a ticket asking Lunarpages to import the backup to thema48_smf20.

[Sir Osis of Liver]

Import the database to a local install and then truncate the error log table.

Yeah, did that with the last one, but my host usually doesn't allow 5gb databases, and it's a crapshoot whether I can talk them into doing it again.  If Lunarpages can't do the import, I'll give it a shot.

[Kindred]

oh, I was talking real local (in WAMPP)

[Sir Osis of Liver]

Crap, it's borked.  They did the import and it's erroring out.  Will have a look soon as I finish my rounds.

[Sir Osis of Liver]

That's a 1.1.4 database, last message was posted Tue, 01 Mar 2016 16:16:42 GMT, last error was logged Tue, 01 Mar 2016 18:56:27 GMT.  Is it reasonable to assume that this was your production database in a 1.1.4 install, backed up before any attempt was made to upgrade the forum?  It's nothing at all like the thema48_smf1 database that was connected to your 2.0 install.

[shred]

Sir Osis,

I am unsure as to what version the original install was done under. The backup was done prior to any attempts to install new version.

I have checked with one of the forum admins and the date of the last post is correct, also number of members tallies with your approx. 3000 as mentioned in one of your previous posts.

Sorry for short response, only have my phone to hand at the moment.

[Sir Osis of Liver]

Ok, got it running here.  Emptied the error log and backed up the database.  Need to know if the content is all there - members, posts, etc., up to the point where the wheels came off.  The database appears to be intact and current up to 1 March 2016.  Kick it around, look at everything.  If you're satisfied that this is what you started out with, I will upgrade it to 1.1.21, then 2.0.11.

[shred]

Sir Osis,

My gob is truly smacked. You are amazing.

Yes all looks good.

[shred]

Sir Osis,

I am out and about at the moment, so I will PM you later to thank you properly.

Cheers

[Sir Osis of Liver]

You're at 2.0.11.  Check everything.  Running out of beer, be back on tomorrow.

[shred]

PM sent.

Thank you once again for all your time and work. It is greatly appreciated by all the forum team and members.

Enjoy the amber nectar! I'd buy you a crate if I could.

[pete.thomas1962]

Sir Osis, you are a genius. Thank you so much for all your help. We thought we had lost all our data, the photos and the records that we had amassed. Today our members are waking up to a bright and shiny new version of the forum, with all the data intact. It's hard to find words to explain how grateful we all are.

Thank you from all the forum admin, our members and me, personally. You truly are a knight in shining armour

Cheers
Pete

[Steve]

Marking solved then.