Help! "phpbb_posts" table crashed and ejected all data! All posts in forum gone! What can be done?

Get help with installation and running phpBB 3.2.x here. Please do not post bug reports, feature requests, or extension related questions here.
WWu777
Registered User
Posts: 600
Joined: Tue Aug 14, 2007 12:40 pm
Contact:

Re: Help! "phpbb_posts" table crashed and ejected all data! All posts in forum gone! What can be done?

Post by WWu777 » Mon Oct 07, 2019 5:17 pm

stevemaury wrote:
Mon Oct 07, 2019 3:23 pm
The query will work, but the results will not be very useful. There will be no context, links won't be hot, etc. Plus, I would not do this for a member. What if all your members wanted this? Are you going to do this after every time someone posts a new post?

They are free to search for their own posts and cut and paste, or they could compose their posts in a text editor and paste them, maintaining the document for reference.
That's ok. He just wants his posts in plain text at least. Better than nothing. He doesn't want his years of valuable content to be lost. I can understand that and would want the same for myself.

Well this person is a close friend of mine in real life, not a stranger. So I don't mind. I'm a nice guy, to an extent.

No other users have requested this yet, so I'm not worried. But if too many people start requesting this, I can start charging them a small fee for it. ;)

Yes I will let them know that they can just copy and paste too from their posts. Or they can just go to every page their post is on and use Control-S to save each webpage offline. However, this would take many hours for thousands of posts so we would probably wanna hire a freelance contractor to do this, like from India or Philippines, which for us is easy since we live part time in the Philippines. :)

Btw. One more question for David63. You said:
david63 wrote:
Mon Oct 07, 2019 6:11 am
WWu777 wrote:
Mon Oct 07, 2019 3:55 am
But what about stopping the MYSQL server? Do I need to do that too?
If you stop your MySql server then you will not be able to perform a restore, or for that matter anything else.
So when I perform any operations on my database, such as backup or restore or export or import, all I should do is disable my board and then run the operation, right?

I notice that in instructions for repairing databases from SSH, that the instructions usually tell you to stop the MySQL server first before using the repair commands. Why? Is there something about using SSH that requires MySQL server to be stopped first?

I also heard somewhere here that when you do a backup of your database, you should disable the board too, so that new posts don't get submitted into the database while it's being backed up, which could result in an error. Is that true?

If so, then what about your auto backup extension? Does it automatically disable the board while it performs the backup? If not, wouldn't that risk some type of error or crash occurring if someone were to submit a post while the backup is being made?

Thanks again.

User avatar
janus_zonstraal
Registered User
Posts: 3624
Joined: Sat Aug 30, 2014 1:30 pm

Re: Help! "phpbb_posts" table crashed and ejected all data! All posts in forum gone! What can be done?

Post by janus_zonstraal » Mon Oct 07, 2019 5:42 pm

However the folder itself is called "autodbbackup-3.2" so should it be: root/ext/david63/autodbbackup-3.2? Does it matter? Will either work?
No that will not work, rename it in root/ext/david63/autodbbackup
Sorry! My English is bat ;) !!!

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

Re: Help! "phpbb_posts" table crashed and ejected all data! All posts in forum gone! What can be done?

Post by david63 » Mon Oct 07, 2019 8:43 pm

WWu777 wrote:
Mon Oct 07, 2019 5:04 pm
I'm sure if you exported his posts as tables, like phpbb_posts or phpbb_topics, in MySQL format, I'm sure they could be used for restore purposes right?
Possibly but I would not rely on it working. You would need to write a script to do that - both backup and restore.
WWu777 wrote:
Mon Oct 07, 2019 5:04 pm
So I was wondering why your extension broke precedent.
Not broken any precedent - that is the way that extensions are packaged on Github. Any extension that is in the CDB does have to be packaged correctly.
WWu777 wrote:
Mon Oct 07, 2019 5:17 pm
what about your auto backup extension? Does it automatically disable the board while it performs the backup? If not, wouldn't that risk some type of error or crash occurring if someone were to submit a post while the backup is being made?
No need because databases will lock tables automatically when they are being accessed. I have been running this extension for years on several boards and never had a problem with corrupt data - and if I did I would just try another backup (I backup my live board every hour)
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

WWu777
Registered User
Posts: 600
Joined: Tue Aug 14, 2007 12:40 pm
Contact:

Re: Help! "phpbb_posts" table crashed and ejected all data! All posts in forum gone! What can be done?

Post by WWu777 » Mon Oct 07, 2019 10:10 pm

Dear David63,
Ok thanks. I've correctly installed your extension. In my ACP though it says your extension is outdated and that a newer version exists. See the screenshots below. Why does it say that? Is it a bug? Or does that mean there is a newer version? If so, where? Is it in the phpbb extensions database? I can't seem to find it.

Also, why isn't your et in the phpbb ext library here: https://www.phpbb.com/customise/db/extensions-36

Btw do the backups get replaced by new backups?


Untitled.jpg
Untitled2.jpg
Last edited by WWu777 on Tue Oct 08, 2019 10:56 pm, edited 2 times in total.

WWu777
Registered User
Posts: 600
Joined: Tue Aug 14, 2007 12:40 pm
Contact:

Re: Help! "phpbb_posts" table crashed and ejected all data! All posts in forum gone! What can be done?

Post by WWu777 » Mon Oct 07, 2019 10:18 pm

One more important question everyone.

I'm about to restore my database backup from Sept 13 that Bluehost retrieved for me. However, I want to try to merge or add the posts made after the crash on Sept 18 too. What if I exported the phpbb_posts and phpbb_topics tables from the database after the crash, and then imported them into the restored database from Sept 13, using phpmyadmin? Would that work? What would happen? Would any errors result?

Btw I did try to merge the two databases using the script from the link above, but I can't get the script to work. It won't access the program when I point to the directory. See below:

viewtopic.php?p=15337996#p15337996
Dear Support,
I tried uploading this program into my root folder root/forum/phpbb-merge-script/. But when I point my browser to the directory, per the instructions, it says this:

http://happierabroad.com/forum/phpbb-merge-script/

[an error occurred while processing this directive]

Does anyone know why?

User avatar
stevemaury
Support Team Member
Support Team Member
Posts: 50812
Joined: Thu Nov 02, 2006 12:21 am
Location: The U.P.
Name: Steve
Contact:

Re: Help! "phpbb_posts" table crashed and ejected all data! All posts in forum gone! What can be done?

Post by stevemaury » Tue Oct 08, 2019 12:15 am

What you want to do is not practically possible. That is, merging posts into an existing database. I am not saying it can't be done, but not easily or cheaply.
For REALLY good and VERY inexpensive hosting CLICK HERE

I can stop all your spam. I can upgrade or update your Board. PM or email me. (Paid support)

WWu777
Registered User
Posts: 600
Joined: Tue Aug 14, 2007 12:40 pm
Contact:

Re: Help! "phpbb_posts" table crashed and ejected all data! All posts in forum gone! What can be done?

Post by WWu777 » Tue Oct 08, 2019 6:20 am

stevemaury wrote:
Tue Oct 08, 2019 12:15 am
What you want to do is not practically possible. That is, merging posts into an existing database. I am not saying it can't be done, but not easily or cheaply.
You're right. I tried to export the phpbb_posts table and import it into the restored database. I got these error messages on phpmyadmin:
SQL query:

--
-- Database: `happier2_HappierAbroad`
--

-- --------------------------------------------------------

--
-- Table structure for table `phpbb_posts`
--

CREATE TABLE `phpbb_posts` (
`post_id` int(10) UNSIGNED NOT NULL,
`topic_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
`forum_id` mediumint(8) UNSIGNED NOT NULL DEFAULT '0',
`poster_id` int(10) UNSIGNED NOT NULL DEFAULT '0',
`icon_id` mediumint(8) UNSIGNED NOT NULL DEFAULT '0',
`poster_ip` varchar(40) COLLATE utf8_bin NOT NULL DEFAULT '',
`post_time` int(11) UNSIGNED NOT NULL DEFAULT '0',
`post_reported` tinyint(1) UNSIGNED NOT NULL DEFAULT '0',
`enable_bbcode` tinyint(1) UNSIGNED NOT NULL DEFAULT '1',
`enable_smilies` tinyint(1) UNSIGNED NOT NULL DEFAULT '1',
`enable_magic_url` tinyint(1) UNSIGNED NOT NULL DEFAULT '1',
`enable_sig` tinyint(1) UNSIGNED NOT NULL DEFAULT '1',
`post_username` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
`post_subject` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_c[...]
MySQL said: Documentation

#1050 - Table 'phpbb_posts' already exists
Is there a script in the support forums here or knowledge base that I can just copy and paste into my SQL query box to do this kind of thing?

Also, how do I get the merge scripts to work? I pointed to it in my browser but it show an error message. See my previous post. Any idea how to get it to work?

WWu777
Registered User
Posts: 600
Joined: Tue Aug 14, 2007 12:40 pm
Contact:

Re: Help! "phpbb_posts" table crashed and ejected all data! All posts in forum gone! What can be done?

Post by WWu777 » Tue Oct 08, 2019 6:28 am

Steve Maury,
Will this work to merge my database tables and posts?

https://www.digitalberg.com/WiKi/how-to ... -database/
CPANEL

1. From cPanel, you need to click the phpMyAdmin icon.
2. Click the SQL tab at the top. You will see where it says, ‘Run SQL query/queries on server “localhost”:’
3. In the text box below that, insert the following code, but replace DB1 and DB2 with the database names. Also, replace TABLE1 with the table name you are trying to merge.
INSERT INTO DB1.TABLE1 SELECT * FROM DB2.TABLE1
4. Click the Go button.
5. Repeat for any other tables you want to merge.

User avatar
stevemaury
Support Team Member
Support Team Member
Posts: 50812
Joined: Thu Nov 02, 2006 12:21 am
Location: The U.P.
Name: Steve
Contact:

Re: Help! "phpbb_posts" table crashed and ejected all data! All posts in forum gone! What can be done?

Post by stevemaury » Tue Oct 08, 2019 3:04 pm

It will probably work in the sense that data from one database will be inserted into another. However, it will not work in the sense of actually creating a working version of a phpBB database. There are many more tables involved other than the posts table and, just for example, there will almost certainly be a clash of post_ids.

If Prime Halo says that script worked for him, then it probably works. However we are not in a position to provide support for it here. Hopefully Ken will help you in that topic.
For REALLY good and VERY inexpensive hosting CLICK HERE

I can stop all your spam. I can upgrade or update your Board. PM or email me. (Paid support)

WWu777
Registered User
Posts: 600
Joined: Tue Aug 14, 2007 12:40 pm
Contact:

Re: Help! "phpbb_posts" table crashed and ejected all data! All posts in forum gone! What can be done?

Post by WWu777 » Tue Oct 08, 2019 10:45 pm

stevemaury wrote:
Tue Oct 08, 2019 3:04 pm
It will probably work in the sense that data from one database will be inserted into another. However, it will not work in the sense of actually creating a working version of a phpBB database. There are many more tables involved other than the posts table and, just for example, there will almost certainly be a clash of post_ids.

If Prime Halo says that script worked for him, then it probably works. However we are not in a position to provide support for it here. Hopefully Ken will help you in that topic.
Hi Steve, I don't understand something. Why would post_ids conflict? isn't everything in the forum numbered sequentially? Even after a crash? I mean after the crash I noticed that new posts and topics were still numbered sequentially from the previous post number before the crash. So why would any numbers conflict?

And what about keys? Why would the keys conflict? I just tried the script above in SQL and it said that there was a duplicate key error.

What if I used this script that ignores duplicate keys?

https://stackoverflow.com/questions/220 ... -structure
INSERT INTO s2.table_name
SELECT * FROM s1.table_name
ON DUPLICATE KEY IGNORE;
Would that work better, by ignoring duplicate keys?

Who is Ken? What's his username? I'll try to PM him.

Thanks again.

User avatar
stevemaury
Support Team Member
Support Team Member
Posts: 50812
Joined: Thu Nov 02, 2006 12:21 am
Location: The U.P.
Name: Steve
Contact:

Re: Help! "phpbb_posts" table crashed and ejected all data! All posts in forum gone! What can be done?

Post by stevemaury » Tue Oct 08, 2019 10:56 pm

PrimeHalo or Prime Halo.

I have a board. The last post id is 100. But, Like you, I lose some posts. Let's say I lose 90 through 100. Them, like you, I restore what I have (last post id 89). Users make new posts, lets say 11 of them. So now, the last post id is again 100. Now you insert the lost posts, whose is is 90-100. That is the same as the ids of the new posts made.
For REALLY good and VERY inexpensive hosting CLICK HERE

I can stop all your spam. I can upgrade or update your Board. PM or email me. (Paid support)

WWu777
Registered User
Posts: 600
Joined: Tue Aug 14, 2007 12:40 pm
Contact:

Re: Help! "phpbb_posts" table crashed and ejected all data! All posts in forum gone! What can be done?

Post by WWu777 » Tue Oct 08, 2019 11:02 pm

stevemaury wrote:
Tue Oct 08, 2019 10:56 pm
PrimeHalo or Prime Halo.

I have a board. The last post id is 100. But, Like you, I lose some posts. Let's say I lose 90 through 100. Them, like you, I restore what I have (last post id 89). Users make new posts, lets say 11 of them. So now, the last post id is again 100. Now you insert the lost posts, whose is is 90-100. That is the same as the ids of the new posts made.
That's not what I'm trying to do though. The whole forum is restored, so the posts after the crash should be numbered sequentially after the crash. There are no lost posts in between to recover. I'm just putting the new posts in only, like in your case, after 100.

Nevertheless, I keep getting weird errors when I try to do it. The syntax is not even being recognized. Sigh.
Error

Static analysis:

2 errors were found during analysis.

Unrecognized keyword. (near "KEY" at position 110)
Unrecognized keyword. (near "IGNORE" at position 114)
SQL query: Documentation

INSERT INTO happier2_phpbbbackup.phpbb_posts SELECT * FROM happier2_HappierAbroad.phpbb_posts ON DUPLICATE KEY IGNORE

MySQL said: Documentation

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IGNORE' at line 3

WWu777
Registered User
Posts: 600
Joined: Tue Aug 14, 2007 12:40 pm
Contact:

Re: Help! "phpbb_posts" table crashed and ejected all data! All posts in forum gone! What can be done?

Post by WWu777 » Thu Oct 10, 2019 1:26 am

A few quick questions:

1. Does importing tables into a database cause any overwrites? Does it add data or replace the whole table and overwrite it? I tried this but it doesn't seem to do anything but result in error messages. No new posts are added.

2. I noticed that when I delete the search indexes, it frees up almost 1 GB in the database size. Wow. Wtf? How can a search index take up that much data? Is it just text or something more? A 1 GB file can hold a whole movie in high quality video format. How can a mere search index take up the same space? Any idea?

Also is the search index necessary? What is it for? Just for searching topics? Can the forum run without it? It seems to take a lot of space.

User avatar
JoshyPHP
Code Contributor
Posts: 1027
Joined: Mon Jul 11, 2011 12:28 am

Re: Help! "phpbb_posts" table crashed and ejected all data! All posts in forum gone! What can be done?

Post by JoshyPHP » Thu Oct 10, 2019 1:58 am

Search indexes are big because they're lossless and they provide instant access to any record. Video codecs are lossy and can only seek to certain frames.

FWIW, the MySQL FULLTEXT back end takes less space because it uses specialized data structures.
I wrote the thing that does BBCodes in 3.2.

WWu777
Registered User
Posts: 600
Joined: Tue Aug 14, 2007 12:40 pm
Contact:

Re: Help! "phpbb_posts" table crashed and ejected all data! All posts in forum gone! What can be done?

Post by WWu777 » Thu Oct 10, 2019 3:12 am

JoshyPHP wrote:
Thu Oct 10, 2019 1:58 am
Search indexes are big because they're lossless and they provide instant access to any record. Video codecs are lossy and can only seek to certain frames.

FWIW, the MySQL FULLTEXT back end takes less space because it uses specialized data structures.
So the search index is basically a duplicate of all the words in the forum? Thats why it nearly doubles the size of the database?

Im in my search index ACP now. I see four sections which looks like four types of search indexes i can create. Do i only need to create one index? Not all four right? Which one is best?

So the one called MYSQL FULLTEXT is best? Because it uses less space? But its just as efficient as the rest right?

This is not for google search right? Just for users to search for topics and posts in the forum right?

Post Reply

Return to “[3.2.x] Support Forum”