"Decode" a post from .sql file

Get help with installation and running phpBB 3.2.x here. Please do not post bug reports, feature requests, or extension related questions here.
Post Reply
new.new
Registered User
Posts: 57
Joined: Sun Aug 12, 2018 9:53 am

"Decode" a post from .sql file

Post by new.new » Wed Jan 09, 2019 8:11 pm

I accidentally deleted a few posts from my forum.
I have a backup so i'm trying to recover them from the .sql database backup file.
I opened the .sql file with a text editor and I found the posts I lost.
The only problem it's that they are encoded in a particular format, for example there are strings like \n ,<br/>,&lt; etc...It's there a simple way to convert it to simple text?

User avatar
david63
Registered User
Posts: 16692
Joined: Thu Dec 19, 2002 8:08 am
Location: Lancashire, UK
Name: David Wood
Contact:

Re: "Decode" a post from .sql file

Post by david63 » Wed Jan 09, 2019 9:19 pm

Why not just restore the backup?
David
Remember: You only know what you know and - you don't know what you don't know!
My CDB Contributions | How to install an extension
I will not be accepting translations for any of my extensions in Github - please post any translations in the appropriate topic.
No support requests via PM or email as they will be ignored

User avatar
warmweer
Registered User
Posts: 2999
Joined: Fri Jul 04, 2003 6:34 am
Location: Van Allen Belt ... well actually Belgium

Re: "Decode" a post from .sql file

Post by warmweer » Wed Jan 09, 2019 9:21 pm

new.new wrote:
Wed Jan 09, 2019 8:11 pm
I accidentally deleted a few posts from my forum.
I have a backup so i'm trying to recover them from the .sql database backup file.
I opened the .sql file with a text editor and I found the posts I lost.
The only problem it's that they are encoded in a particular format, for example there are strings like \n ,<br/>,&lt; etc...It's there a simple way to convert it to simple text?
There are 2 easy ways to put those posts back (at least if there aren't many) and neither method needs any converting.
  1. The very easy way is to use that backup as database for a "backup" installation (on localhost for example). Once that backup forum is up and running you can read the deleted posts and copy them to your live forum to the correct topics. For each re-inserted post: edit the post_author and post _time (extension by javiexin). The only thing which won't have the original value wil be the post_id and the post_time (which will be off by maximum 1 second)
  2. Another easy way is to copy the records from the database backup directly into the live database (with phpMyAdmin) - you do not need to "convert" any post_text. Should a post be the first post of a topic then you'll also have to copy the topic record from the backup to the live database.
    Strictly speaking you also need to correct the post count, user_postcount, topic_ and forum_lastpost, etc... and that can be a bit tricky if there are many records that have to be inserted. Luckily these actions can be done form within the board itself:
    For each topic in which a lastpost was inserted, add (on the board) a new "fake" post and immediately delete it and the topic last_post will be corrected. As to postcounts, etc, >ACP > General > resynchronise statistics and resynchronise postcounts.
Use the second method ONLY if you really know and understand what you are doing.
Last edited by warmweer on Wed Jan 09, 2019 9:28 pm, edited 1 time in total.
The year is 2192. The British Prime Minister visits Brussels to ask for an extension of the Brexit deadline. No one remembers where this tradition originated, but every year it attracts many tourists from all over the world.

User avatar
warmweer
Registered User
Posts: 2999
Joined: Fri Jul 04, 2003 6:34 am
Location: Van Allen Belt ... well actually Belgium

Re: "Decode" a post from .sql file

Post by warmweer » Wed Jan 09, 2019 9:27 pm

david63 wrote:
Wed Jan 09, 2019 9:19 pm
Why not just restore the backup?
Probably because there have been new posts made since the deletion.
But it's a very good question because if the number of deleted posts is higher than the number of new posts, it would be better to backup the live board, restore the backup from before the deletion, then copy the new posts into the restored backup board instead of what I suggested (the deleted posts into the live board).
The year is 2192. The British Prime Minister visits Brussels to ask for an extension of the Brexit deadline. No one remembers where this tradition originated, but every year it attracts many tourists from all over the world.

new.new
Registered User
Posts: 57
Joined: Sun Aug 12, 2018 9:53 am

Re: "Decode" a post from .sql file

Post by new.new » Wed Jan 09, 2019 10:23 pm

I also tried to run the database on localhost, but it's an endless nightmare with errors everywhere.
Also, i don't want to try to manually edit the database, i have no experience in these thinks and i don't want to break something.

It's not important to replace exactly every post with the same date etc, i only want to recover the text in these posts.

new.new
Registered User
Posts: 57
Joined: Sun Aug 12, 2018 9:53 am

Re: "Decode" a post from .sql file

Post by new.new » Wed Jan 09, 2019 10:29 pm

warmweer wrote:
Wed Jan 09, 2019 9:27 pm
Probably because there have been new posts made since the deletion.
But it's a very good question because if the number of deleted posts is higher than the number of new posts, it would be better to backup the live board, restore the backup from before the deletion, then copy the new posts into the restored backup board instead of what I suggested (the deleted posts into the live board).
The deleted post are very few, like 7-8 posts. The newer posts are much more.

User avatar
warmweer
Registered User
Posts: 2999
Joined: Fri Jul 04, 2003 6:34 am
Location: Van Allen Belt ... well actually Belgium

Re: "Decode" a post from .sql file

Post by warmweer » Wed Jan 09, 2019 11:03 pm

new.new wrote:
Wed Jan 09, 2019 10:23 pm
I also tried to run the database on localhost, but it's an endless nightmare with errors everywhere.
That means that if your board is broken for one reason or another (e.g. update) you won't be able to restore any backup (because the restore feature needs a working phpBB board).

On localhost:
Create a new board of exactly the same version of your online board.
Once that is done, you have a database and a config.php, and a working (new) board on localhost.
If your backup was made using the phpBB Backup feature, copy that backup into /store.
Log in and to go ACP and restore the backup. Once restored, don't touch the board: Close the browser and delete cookies and cache (so that you will be logged out).

Delete all the files in your phpBB root on localhost but NOT config.php (copy that somewhere just to make sure)
Copy all files from your online phpBB root, to your localhost phpBB root
in /cache, delete everything except for .htaccess and index.htm
In your phpBB root (localhost) overwrite the config.php (which comes from online) with the first one you made.
This config.php points to the database on localhost so that is OK.
The last step is to edit the config_table with phpMyAdmin - edit cookie_domain (probably just /, script_path (probably the same as online ex. /phpBB, servername > probably localhost

Now you have a the backup copy of your online board on your PC

And now, since there are only 7 -8 posts to insert you can use the first method I proposed to copy the deleted post (which are in the backupforum on localhost) to your online board.
The year is 2192. The British Prime Minister visits Brussels to ask for an extension of the Brexit deadline. No one remembers where this tradition originated, but every year it attracts many tourists from all over the world.

Post Reply

Return to “[3.2.x] Support Forum”