Transferred forum and now stuck with archive problem

[LemonGrenade]

So I've spent all day transferring over to a VPS using Plesk and can only have one database per site.

I had a seperate archive on the last host and trying to merge it into the normal database now, I obviously hit this error?

Quote

Error at the line 84: ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Query: --
--
CREATE TABLE `ibf_forums_archive_posts` (
`archive_id` int(10) NOT NULL DEFAULT 0,
`archive_author_id` int(10) NOT NULL DEFAULT 0,
`archive_author_name` varchar(255) NOT NULL DEFAULT '0',
`archive_ip_address` varchar(46) NOT NULL DEFAULT '',
`archive_content_date` int(10) NOT NULL DEFAULT 0,
`archive_content` mediumtext DEFAULT NULL,
`archive_queued` int(1) NOT NULL DEFAULT 1,
`archive_topic_id` int(10) NOT NULL DEFAULT 0,
`archive_is_first` int(1) NOT NULL DEFAULT 0,
`archive_bwoptions` int(10) UNSIGNED NOT NULL DEFAULT 0,
`archive_attach_key` char(32) NOT NULL DEFAULT '',
`archive_html_mode` int(1) NOT NULL DEFAULT 0,
`archive_show_signature` int(1) NOT NULL DEFAULT 0,
`archive_show_emoticons` int(1) NOT NULL DEFAULT 0,
`archive_show_edited_by` int(1) NOT NULL DEFAULT 0,
`archive_edit_time` int(10) NOT NULL DEFAULT 0,
`archive_edit_name` varchar(255) NOT NULL DEFAULT '',
`archive_edit_reason` varchar(255) NOT NULL DEFAULT '',
`archive_added` int(10) NOT NULL DEFAULT 0,
`archive_restored` int(1) NOT NULL DEFAULT 0,
`archive_forum_id` int(10) NOT NULL DEFAULT 0,
`archive_field_int` int(10) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1

MySQL: Table 'ibf_forums_archive_posts' already exists

As the archive is only 2 tables, is there a change I could make easily and try and dump it back in again?

[LemonGrenade]

Any ideas because there is about a million posts missing. I removed the create tables and just left the 'insert' to add to current, but there is more columns in sql file than the`ibf_forums_archive_posts` has. 'signature' for one.

Quote

MySQL: Unknown column '' in 'field list'

 

[Randy Calvert]

Manually merging databases is not officially supported. If you have a VPS, can you create a fake domain (foo.com) and create a database for it? 

[LemonGrenade]

Yes I can, but I think I'll be in the same boat with the errors.

[Randy Calvert]

10 minutes ago, LemonGrenade said:

Yes I can, but I think I'll be in the same boat with the errors.

It should be fine.  Import the database in the dummy account. In IPB, choose it’s a remote database. You can leave the host as “localhost” since it’s on the same physical machine. Just provide the correct database username/pass, and table name. 

You’re not merging databases. You’re simply keeping what is already setup. 

[LemonGrenade]

Sounds like it could work, but you do have to assign the database to a domain?

[Randy Calvert]

PHP can talk to ALL databases. It just means if you ever have to use phpMyAdmin or something similar you have to manually switch to that domain. It’s an extra step to manage, but it should absolutely be possible. 

By the way… the worse you risk is it not working and you lost a little time. 🙂

[Marc]

You would simply restore the archive database to the same database as your live database, then you would need to ensure your archive database setting point to your main database

[LemonGrenade]

5 hours ago, Marc Stridgen said:

You would simply restore the archive database to the same database as your live database, then you would need to ensure your archive database setting point to your main database

That was the issue though Marc. The Archive had extra fields in there per the error in the opening post and so couldn't be added to the main database tables.

So if I turn off the archive within ACP, it just won't sent topics to archive any longer and only just call from that db if someone clicks into an archived topic? It won't re-populate if I change the archived db to the usual one?

It basically all comes down to the Plesk setting of having only one db per website. @Randy Calvert that was a great idea but with a few other issues on the transfer, I've set it back to the original host.

[Marc]

Unfortunately you are overthinking things there. You should not be trying to add anything to the main tables. You should be restoring the archive tables themselves to the new database. You would not try to merge the 2 sets of tables together

[Randy Calvert]

17 minutes ago, LemonGrenade said:

That was the issue though Marc. The Archive had extra fields in there per the error in the opening post and so couldn't be added to the main database tables.

So if I turn off the archive within ACP, it just won't sent topics to archive any longer and only just call from that db if someone clicks into an archived topic? It won't re-populate if I change the archived db to the usual one?

It basically all comes down to the Plesk setting of having only one db per website. @Randy Calvert that was a great idea but with a few other issues on the transfer, I've set it back to the original host.

If you've gone back to the original host, turn off archiving and bring it back all into a single database.  Once done, you can move it back to the new location and setup archiving again, but just telling it to put it into a different table.  

[LemonGrenade]

Thanks Randy, but how would I bring the archive back into the main? Is it possible via ACP? or would I have to fix those extra fields in the sql and manually import it?

[Randy Calvert]

Just turn off archiving.  It will bring it back for you.

When you turn it off, you'll see:

Choose to restore topics that don't match the new archive settings.  

Remember...  this does not happen real-time, so it may take a few days to do.  It's done in small batches in the background.  

Edited October 18, 2022 by Randy Calvert

[LemonGrenade]

Thanks so much Randy, thats perfect!

[Gary]

I'm glad this issue has been resolved. Thanks Randy for assisting.