Please help - restoring an SMF instance without much help

[cbotkins]

Hello, first of all thank you for any assistance you can offer. We are getting truly stuck and don't have many options at this point.

The long story of this. Our company has an old SMF site that was hosted on AWS. The IT person that was in charge of not only AWS but this forum is no longer with the company. And he didn't leave many notes or documentation to work with. Our current IT staff noticed that the instance on AWS was not secure (http instead of https) so they attempted to fix it. In doing so they brought the site down. Somehow the database corrupted and the site became completely unusable late last week. We have been directed to bring the site back up and get it running again.

The plan as of last week was to migrate the SMF site to a new host that we had control of and get everything back up and running the best we could. We managed to get an SSH connection to the AWS instance and were able to SQL Dump the database. I then started a clean install of SMF on our web server and created a new DB. I have spent all day trying to get the site restored from the SQL Dump files and while I'm close I feel I'm stuck. I got all the topics showing, and the messages there. However when clicking on a topic/message we now get this error: Wrong value type sent to the database. Integer expected. (id_msg)

I've looked at the database and the id_msg field is set to: int(10) unsigned type and SQL won't let me change/update that to anything else.

I have uninstalled any and all mods that were associated with the SMF instance and I've uploaded all of the SQL files from the SQL Dump. I really don't know how to proceed. Any new post or topic can be posted and read just fine. And any "Salvaged Topics" posts work just fine. It's just the other topics/messages that are throwing the error. I have also installed the repair_settings.php file and that offered no help.

Link to the forum on the new web server: hxxp:wbynation.weatherby.com/ [nonactive]

Please let me know any thoughts anyone has.

[Kindred]

1- what version was the old site?
2- what version is the new site?

3- if 1 != 2, did you run the upgrade script?  Because 1.0.x < 1.1.x < 2.0.x < 2.1.x  and databases for one will not work on the next, without conversion

4- What version of php is running on the new server?

5- what mods are installed?

[cbotkins]

Both sites are 2.0.19. I made sure of that when I did the clean install.

On the new site there are no mods installed. On the old site there were a few mods (YT video, PrettyUrL, Gallery Pro, and a couple others I can't recall right now). I tried installing PretyyURL and I would get 404 errors when clicking on a topic/post

PHP version is 7.4

[Sir Osis of Liver]

I've uploaded all of the SQL files from the SQL Dump.

What does this mean?  An SQL dump is just one file.  How did you dump the database?  How did you restore it to current install?

Everything I can access as guest works normally, except that all old posts are marked as 'Awaiting Approval', and those topics return 'This topic doesn't exist on this board' error.

[cbotkins]

Because of how AWS works (at least how we could get it to work) is we had to down load every sql table as a .sql file. So it was broken up into pieces (all different tables within the whole database). Again, we are coming at this without any cpanel or phpmyadmin. We couldn't find anything in the limited documentation we have on hand.

And as you see all the topics are "locked" but if I unlock them as an admin they still throw the same error:

Wrong value type sent to the database. Integer expected. (id_msg)

It looks like when you aren't logged in the topics throw the error you are seeing:

This topic doesn't exist on this board.

[Kindred]

How did you dump the sql?

It seems that the database may not have the right settings for the tables/columns

[Sir Osis of Liver]

Haven't dealt with AWS, but on their website they have support links (https://aws.amazon.com/websites/).  You should be able to contact them and determine if they can provide a clean database dump, maybe from server backups. 

How did you migrate to new host?  Did you copy all forum files from old to new?  If original forum was running Pretty Urls mod there would be an .htaccess file containing multiple redirects.  If you did a clean install on new host and copied over the .htaccess without reinstalling the mod, it can cause all kinds of problems.

[cbotkins]

Our IT guy was able to do a sql dump via MySQL Workbench

I have seen an htaccess file in and amongst everything. I didn't upload that though since I couldn't find the correct version of pretty URLs that was used on the original site. I haven't looked in there. That might help too.

I don't think we have support on AWS

The steps I took to migrate to a new host are this:

- Carved out a subdomain on cpanel on our web server.
- created new sql database and assigned user with all privileges
- downloaded a clean version of smf ver. 2.0.19
- ran through the smf installer and got the framework of the site setup
- found an old (2020) back up of the old website for the theme files
- uploaded old theme files
- finally got the various sql files from my IT counterpart and went to work on uploading them
- uploaded every table from old site and did SQL commands to copy contents of each table into their respective tables into new db. And that's what got us to this point.

Had to get support on the host to increase my sql upload size because the table "messages" table was 188mb. When I got that uploaded all the categories acted like they had messages/posts in them but nothing displayed (would display the number of messages in that topic eg: 1 of 172 but each "page" was empty). When I installed the "topics" table that's when I got to this point.

[Sir Osis of Liver]

- uploaded every table from old site and did SQL commands to copy contents of each table into their respective tables into new db.

Something may have gone sideways when you did this, it's not really necessary.  You can do a clean 2.0.19 install with new database using the full install package.  When it's up and running, drop all database tables and import the tables from the production database.  Make sure you have all of them, there are 63 tables in a clean install, mods may add additional tables.  Then you'll have to run repair_settings to fix paths.

[cbotkins]

Wouldn't I have to change the name of the database tables? The old database prefix doesn't match the new one.

Or would that not matter if I'm dropping them all? Or should I try and stick as close to the original prefix as much as possible?

[Sir Osis of Liver]

The tables will import with old prefix, you can change it in phpmyadmin, but it's easier to change $db_prefix in Settings.php, or you can do it with repair_settings.

[cbotkins]

Ok! Thank you! We'll give it a go in the morning. Worse case scenario is we end up right back where we are.

Thank you for your help!!!!! I'll update this post after.

[Sir Osis of Liver]

Good luck.

[Kindred]

You will have to make changes to Settings.php for db_prefix...

And then you will have to run repair_settings.php to update the paths and URLs which are stored in the database sib that they match your new configuration

[cbotkins]

Good luck.

Well... I got it working. It was a slog though. I basically had to do a combination of what you suggested with dropping all the tables and installing a fresh instance.

Uploading the tables and renaming the prefix did not fix it, in fact that made it worse. Both through repair_settings and through the database itself.

What finally did the trick was doing a clean install of version 2.0.19. Then only uploading the same 63 tables from the backup that matched the clean install. I then dropped the clean install tables and renamed the prefix of the newly uploaded/backup tables. That was the only way I got it to work.

It appears one (or all) of the add ons is what caused things to go haywire. I'm honestly not sure which at this point, but I'm leaning towards either the gallery or the pretty url add on. Can anyone tell me how often those add-ons are updated? Do they typically cause issues with SMF sites?

I appreciate your help and support. You at least got us on the right path to fixing this and eventually moving it to a new host today. I feel better about where it's at. Hopefully I can get it back to full operation over the next few days.

[Doug Heffernan]

It appears one (or all) of the add ons is what caused things to go haywire. I'm honestly not sure which at this point, but I'm leaning towards either the gallery or the pretty url add on. Can anyone tell me how often those add-ons are updated? Do they typically cause issues with SMF sites?

How the mods are updated depends on their developers. You can also check their support topics and see if there are any issues with them being reported by other members.

That being said, when you uninstall a mod that has added stuff in the database, be that new tables(s) and/or columns in the smf tables, and you want to remove all their stuff from the database, you must check the remove all data associated with this mod, otherwise their data will remain in the database and might cause issues down the road.

[Kindred]

other than the "field needs a default value" -- it is unlikely that old mod data will cause issues...   and I ALWAYS leave the old data, in case I ever re-install the mod and want the data that it had captured before

[Doug Heffernan]

other than the "field needs a default value" -- it is unlikely that old mod data will cause issues... 

I remember seeing quite a few topics here where members had problems caused by third party mods database tables when upgrading their forums.

[Kindred]

I upgraded with the data from 150 mods in the database...   

[Doug Heffernan]

I upgraded with the data from 150 mods in the database...   

Wow, that's quite a lot of mods

[Sir Osis of Liver]

If you copied your theme files from old install to new clean install, they may contain mod code from mods that were previously installed on old install.  You should reinstall your theme from original package before reinstalling mods on new server.  Tables and settings that were added to database by mods would be ignored in clean install.  The default value error is easily fixed by setting the field to NULL if you want to keep the mod table.

[Steve]

OP believes the problem solved so without further input, I'm marking it so.