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 » Thu Oct 10, 2019 3:18 am

I got another question about the table "phpbb_posts" which contain all the forum posts.

Every time i optimize it in phpmyadmin it causes it to crash for some reason. Then i have to repair it. But even after i repair it, it will still crash if i try to optimize it for some reason.

Is this due to some deeper corruption? If so how do i repair it? The repair function in phpmyadmin isnt enough right? I also tried "REPAIR table phpbb_posts USE_FRM", but thats not enough either.

What if i try to repair the table from SSH? I heard SSH is more stable and better able to handle larger files. Does that means its repair tools are better too?

Also in my situation, when i use david63s auto backup extension, should i uncheck the box to optimize tables before the backup, so that it wont crash the table?

Thanks.

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

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

Post by warmweer » Sat Oct 12, 2019 12:09 pm

WWu777 wrote:
Thu Oct 10, 2019 3:18 am
I got another question about the table "phpbb_posts" which contain all the forum posts.

Every time i optimize it in phpmyadmin it causes it to crash for some reason. Then i have to repair it. But even after i repair it, it will still crash if i try to optimize it for some reason.
If you're running short on writing space (depends on your hosting account), optimising a large table will cause a crash. And you can consider yourself lucky that it can still be repaired.

Another way of optimising a table is to export it, and then reimport it (use drop table before reimporting it).
My board's not broken, it just went peculiar

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 » Sat Oct 12, 2019 11:20 pm

To David63,

I got your auto backup extension to work now. It seems to work fine and is making the backups as scheduled. Thanks a lot. I will be glad to send you a donation for this helpful extension you designed. :)

I have a question though. If I set the extension to backup every 3 days (or 72 hours), and the maximum stored backups to 3 databases, does that mean that every time a backup is made, the oldest stored backup will be replaced and overwritten? Is that assumption correct?

Does your backup use phpmyadmin or a more stable way to backup the forum?

Also, why don't you make your extension available in the phpbb extension database, so that more forum owners can find it and use it too? I mean the database here: https://www.phpbb.com/customise/db/extensions-36

Thanks.

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 » Sat Oct 12, 2019 11:53 pm

warmweer wrote:
Sat Oct 12, 2019 12:09 pm
WWu777 wrote:
Thu Oct 10, 2019 3:18 am
I got another question about the table "phpbb_posts" which contain all the forum posts.

Every time i optimize it in phpmyadmin it causes it to crash for some reason. Then i have to repair it. But even after i repair it, it will still crash if i try to optimize it for some reason.
If you're running short on writing space (depends on your hosting account), optimising a large table will cause a crash. And you can consider yourself lucky that it can still be repaired.

Another way of optimising a table is to export it, and then reimport it (use drop table before reimporting it).
You mean simply exporting and importing a database from a backup will automatically optimize the tables? Wow. I didn't know that. Will it clear out any deep internal corruptions too? How do you use drop table? Do you drop all the tables or just the one that has a problem?

Anyway, I just deleted some unnecessary files on my server, so now my hosting space has about 3 GB of free space left on it. Is that enough to run a forum properly?

I am also using David63's backup extension. It seems to work great so far. When it backs up my database, the backup in gzip format is only about 150 MB, which is a pretty small size, enough for me to store several backups on my server.

I also discovered another trick here. When I deleted the native text search index backend, it freed up about one gigabyte from the forum database. Wow. I never knew such a simple thing would reduce the database by one whole gigabyte. So I followed the advice given here in other threads, and used the SQL search index backend instead, which takes up far less space. By doing that, I've reduced my database size significantly. This means the backups will take less space as well. A win win situation. :) Too bad I didn't know about all this before.

User avatar
david63
Registered User
Posts: 16537
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 » Sun Oct 13, 2019 7:52 am

WWu777 wrote:
Sat Oct 12, 2019 11:20 pm
If I set the extension to backup every 3 days (or 72 hours), and the maximum stored backups to 3 databases, does that mean that every time a backup is made, the oldest stored backup will be replaced and overwritten? Is that assumption correct?
More or less. From memory every time a backup is performed that backup is added into the store folder and then the oldest one(s) are removed, so in effect at some point you would have four copies (but only for a few seconds!)

Obviously it is your decision but I would question the rationale of only performing a backup every three days, especially if you have a busy board - I backup mine 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

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

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

Post by warmweer » Sun Oct 13, 2019 9:08 am

WWu777 wrote:
Sat Oct 12, 2019 11:53 pm
You mean simply exporting and importing a database from a backup will automatically optimize the tables? Wow. I didn't know that. Will it clear out any deep internal corruptions too? How do you use drop table? Do you drop all the tables or just the one that has a problem?
Optimising a database is similar to defragmenting a harddisk. Internal corruption could be many things, but NOT fragmentation and will not be solved by defragmentation which only tries to ensure that a table is stored as 1 (physically) continuous set of data.
I suggest you read up on the basic SQL commands e.g. drop, truncate, create table.
WWu777 wrote:
Sat Oct 12, 2019 11:53 pm
Anyway, I just deleted some unnecessary files on my server, so now my hosting space has about 3 GB of free space left on it. Is that enough to run a forum properly?
That 3GB of free space is probably your free disk space which has nothing to do with the database space available. So as to the board files - you have lots of space left, but you may want to check your allotted database space
WWu777 wrote:
Sat Oct 12, 2019 11:53 pm
I am also using David63's backup extension. It seems to work great so far. When it backs up my database, the backup in gzip format is only about 150 MB, which is a pretty small size, enough for me to store several backups on my server.
A compressed database backup size of 150 MB, probably including the search tables, is actually a more or less small/medium size board. you may start start having problems backing up or transferring due to host limitations. (see next remark)
WWu777 wrote:
Sat Oct 12, 2019 11:53 pm
I also discovered another trick here. When I deleted the native text search index backend, it freed up about one gigabyte from the forum database. Wow. I never knew such a simple thing would reduce the database by one whole gigabyte. So I followed the advice given here in other threads, and used the SQL search index backend instead, which takes up far less space. By doing that, I've reduced my database size significantly. This means the backups will take less space as well. A win win situation. :) Too bad I didn't know about all this before.
I don't even keep the search tables in all backups (maybe once every 3 months or so). That would probaly cut your backup size in half.
My board's not broken, it just went peculiar

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 14, 2019 3:48 am

david63 wrote:
Sun Oct 13, 2019 7:52 am
WWu777 wrote:
Sat Oct 12, 2019 11:20 pm
If I set the extension to backup every 3 days (or 72 hours), and the maximum stored backups to 3 databases, does that mean that every time a backup is made, the oldest stored backup will be replaced and overwritten? Is that assumption correct?
More or less. From memory every time a backup is performed that backup is added into the store folder and then the oldest one(s) are removed, so in effect at some point you would have four copies (but only for a few seconds!)

Obviously it is your decision but I would question the rationale of only performing a backup every three days, especially if you have a busy board - I backup mine every hour.
You backup your forum every hour? Doesn't that put a lot of stress and strain on your server? Do you have like 50 or 100 backups stored on your server? Must take up a lot of space?

Ok maybe I should set the backup to every day or every other day then. The reason I selected every 3 days is because if I'm traveling and the forum crashes and loses data, and I don't find out about it until 2 or 3 days later, then the recent backup will be useless because it will be a backup of the crashed forum. You see, I don't check my forum everyday. Sometimes I don't check it for several days. So I need a backup that goes back at least several days or a week. The only way to do that without making too many backups, is every three days. What would you suggest? Every other day would be an option too I guess.

Thanks for your extension btw. You saved me a lot of trouble. I just sent you a donation.

User avatar
janus_zonstraal
Registered User
Posts: 3621
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 14, 2019 5:10 am

Maybe better to move to a other host, web space is almost free nowadays, and crashing tables are not normal..
Sorry! My English is bat ;) !!!

User avatar
dingus33
Registered User
Posts: 112
Joined: Fri Sep 29, 2017 11:11 am

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

Post by dingus33 » Mon Oct 14, 2019 6:28 am

just food for thought, if you want to go another direction with backups...

if you use postgres, i can recommend this backup solution: https://pgbackrest.org/
it's relatively mature and feature rich, including options like incremental backup and point-in-time recovery (basically down to the db transaction). unless you have a small db, making every backup a complete backup is wasteful in terms of storage. also, many people probably have more db stuff to back up than phpBB, so might as well get it all working in a single solution.

pgbackrest supports S3 out of the box. if you want a free offsite storage solution, you can back up to google drive, but support isn't built in. for that, you can couple this with something like duplicity (optionally using duply as a wrapper).

bottom line is to get offsite backups working as part of your backup solution and, very important, have a way to conveniently test your backups every now and then. have a dev/staging environment (even just a local vm) you can restore them to. backups are worthless if you're not testing them by doing restores. how do you know they work? you don't.

i've been thinking of writing a tutorial for this (maybe as part of a production-ready sysadmin guide for a VPS with phpBB), but no clue if anyone would be interested. kind of an awkward combination of:
  • slightly out of scope for this community
  • experienced sysadmins don't need such a guide
  • noobs might not be able to follow such a guide
might be too niche of an audience.
Last edited by dingus33 on Tue Oct 15, 2019 1:45 am, edited 1 time in total.

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

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

Post by warmweer » Mon Oct 14, 2019 6:51 am

dingus33 wrote:
Mon Oct 14, 2019 6:28 am
bottom line is to get offsite backups working as part of your backup solution and, very important, have a way to conveniently test your backups every now and then. have a dev/staging environment (even just a local vm) you can restore them to. backups are worthless if you're not testing them by doing restores. how do you know they work? you don't.
:thumbsup:
Seconded, thirded and fourthed
My board's not broken, it just went peculiar

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 » Mon Oct 14, 2019 7:27 pm

WWu777 wrote:
Mon Oct 14, 2019 3:48 am


You backup your forum every hour? Doesn't that put a lot of stress and strain on your server? Do you have like 50 or 100 backups stored on your server? Must take up a lot of space?
Just because one backs up every hour does not mean one has to store more than two or three backups. That is one of the beauties of the Auto Backup extension.
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)

User avatar
dingus33
Registered User
Posts: 112
Joined: Fri Sep 29, 2017 11:11 am

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

Post by dingus33 » Tue Oct 15, 2019 1:02 am

stevemaury wrote:
Mon Oct 14, 2019 7:27 pm
Just because one backs up every hour does not mean one has to store more than two or three backups.
is that an actual backup policy you recommend? it sounds like a pretty bad one to me. what if you need to recover from an event that happened more than a few hours ago?

User avatar
Lumpy Burgertushie
Registered User
Posts: 66728
Joined: Mon May 02, 2005 3:11 am
Contact:

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

Post by Lumpy Burgertushie » Tue Oct 15, 2019 1:27 am

each person has to decide what frequency of backups they are comfortable with.
even if you made a backup every minute you could still lose a post or two.
the point was that you get to decide how many backups are stored before one drops off.
you can backup every hour but only keep 3 or 10 or 20 on the server before the oldest gets dropped etc.


robert
I'm baaaaaccckkkk. still doing work on donation basis. PM your needs.

Premium phpBB 3.2 Styles by PlanetStyles.net

If a tree falls in the forest and nobody is there, does it make a sound?

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 15, 2019 2:01 am

dingus33 wrote:
Tue Oct 15, 2019 1:02 am
stevemaury wrote:
Mon Oct 14, 2019 7:27 pm
Just because one backs up every hour does not mean one has to store more than two or three backups.
is that an actual backup policy you recommend?
No. But I don't not recommend it either.
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)

User avatar
david63
Registered User
Posts: 16537
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 » Tue Oct 15, 2019 5:59 am

Just for the record my backup policy is to use the auto backup extension to do a backup every hour and retain a rolling 25 backups. On top of that I use my Dropbox extension to upload a backup every six hours to my Dropbox account where there is at least one week's backups. This means that at any one time I will have at least 50 backups to fall back on and I do occasionally do a restore with one of them to prove the integrity of the backups.
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

Post Reply

Return to “[3.2.x] Support Forum”