Recovering information from a previous installation

[meierwm]

I am helping a friend with an installation of Simple Machines because their previous installation was about nine years old and was starting to report database problems that did not seem to be able to be resolved. I wasn't very familiar with the software, but I have worked in IT for a couple of decades.

We did a fresh installation of 2.0.17 and it is running on a completely new database. We have two backups of the old database by different methods, and both seem to have intact boards tables when I look at them through phpMyAdmin. I was trying to figure out if I could use a SQL insert to bring the old postings over, understanding that they would not have old attachments or images which I am sure are stored in other tables. There are years worth of content and conversations we would like to preserve without necessarily needing to try to repair the entire old database.

I looked through the Wiki and forums for a similar topic, but I did not find one. If someone can point me to a white paper or a discussion on a similar topic that may serve as a guide to what we're trying to do I would appreciate it. TIA.

[Arantor]

You need to copy at least the messages, topics and boards tables, everything else you can kind of work around, but those three tables you absolutely need to keep the content.

[meierwm]

Should I be able to do something along the lines of:

  insert into database1.table  select * from database2.table where id not in(select id from database1.table);

as long as I get the contents of the three tables you mention? I'm not a DBA, but I have a bit of SQL experience. Is there a particular order they would need to be inserted to keep the internal mechanisms of the board happy? Would it be necessary to put things in maintenance mode before performing an insert, if that is an acceptable method? Thanks for the quick reply.

[Arantor]

As long as they're 2.0 backups you're installing from, that will be fine - there's no constraints in the schema since SMF 2.0 is largely from the era when MySQL didn't support proper foreign keys.

If they're not 2.0 backups but SMF 1.1 backups, that's a lot more awkward as column names have changed in the interim.

Also, existing addons may have changed the schema, but give it a go and eee what happens.

[meierwm]

They're not 2.0 backups. The old forums hadn't been upgraded in nine years, so the old databases are whatever 1.x version was running since the installation in 2011. I'll take a look at the column names and see if I can figure out how they should be mapped, unless there is a utility for handling the conversion.

[Doug Heffernan]

They're not 2.0 backups. The old forums hadn't been upgraded in nine years, so the old databases are whatever 1.x version was running since the installation in 2011. I'll take a look at the column names and see if I can figure out how they should be mapped, unless there is a utility for handling the conversion.

In that case, the best way imo, woul dbe to restore the backup to the db of your 2.0.17 forum and then run the upgrader to upgrade the db.

Or, you can install the 1.x whatever version of your forum, (you can check the precise version at the smf_settings table of your backup, restore the backup and then upgrade to 2.0.17

You need to copy at least the messages, topics and boards tables, everything else you can kind of work around, but those three tables you absolutely need to keep the content.

What about the members table? That is one of the most important table.

[meierwm]

When attempting to repair the old database we were getting errors for an unexpected null value in a spam field. We were also seeing other inconsistent errors, which unfortunately I did not capture. The main reason we did a clean install and are trying to migrate data in was to avoid bringing forward corruption in the DB. If there's a better path I am happy to try it though. Are those kind of errors likely to cause an upgrade to fail? Is the upgrade utility in the AdminCP?

On a strange (to me) side note, I checked for the version number after writing the above response, and smfVersion is 2.0 on the old database.

[Kindred]

that's entirely possible as SMF 2.0 branch is 10 years old.

[Sir Osis of Liver]

It has been my experience that smfVersion in _settings is not always correct.  Branch is usually right, but release sometimes isn't, or is not shown.  If you have a 2.0 database, it should run in any 2.0.x.  If it's 1.1, there were db changes along the way, so it could be problematic.  If the db is intact, try to get it running in it's original version, then upgrade to 2.0.17. 

[Arantor]

that's entirely possible as SMF 2.0 branch is 10 years old.

Older than that, 2.0 RC1 is late 2008/early 2009 (Curve came in with RC2 in Nov 2009) and betas run back to 2007.