« Number of 'online users'
Database Backup, Restore and Repair
Wedge and International Domain Names (IDNs) »

markham

  • Finally finished the Slideshow... phew!
  • Posts: 138
Database Backup, Restore and Repair
« on April 25th, 2012, 06:24 AM »Last edited on April 25th, 2012, 06:36 AM
Yesterday we learned that SMF's database backup "feature" is somewhat of a mixed-blessing - and I'm being polite here. Apparently the backup files may not necessarily be complete - contain all the records for all the tables. As it stands it serves little more than provide a false sense of security.

As I understand it, that feature was added to help those on free hosting where there may be a limited host Control Panel that doesn't include MyphpAdmin which would otherwise be used to backup and restore databases. But since SMF doesn't include a "restore" counterpart, it's all a bit half-arsed.

Now I do, I think, understand why the backup could fail and if I understand correctly, it's all about maximum execution times. But that surely is down to bad design in the first place?

Here's an idea for you. Would it not be better to have a backup system that runs in background via a scheduled task and rather than emitting a SQL "dump", it outputs table structures and data in XML format. It could do this table by table either as a complete backup or as a partial - all records added from a given date - thus allowing for incremental backups (but I do recognise that may entail time-stamping all records which may not be desirable). A complementary "restore" system would then simply prompt for a (compressed) backup file which is then decoded and updates the database. As both backup and restore would process manageable "chunks" of data, obtained via queries, the maximum execution time limit should not be exceeded.

Or am I being naive and overly simplistic? Forgive me if I am, please.

I mentioned "Repair" in the title for a reason. Would it be possible for Wedge to inspect its various tables and remove records that are incomplete or appear logically to be extraneous? My reason for asking this is simple: I appear to have a number of extra rows in the Aeva Permissions area - ie sets of permissions for membergoups that don't exist and have no membergroup name assigned and I don't know if I can safely remove them (and the only way I can see of doing that would be to edit the SQL to remove them, put the site into maintenance mode, delete that table and recreate it at the server level).[1]

I do appreciate that an analysis/repair function is a bit of a tall order and not really necessary as a core function - although backup/restore arguable should be - and best dealt with by a plug-in.

Again, my apologies if I'm being a prat!
 1. I am not asking for support for Aeva here, it's up to me to sort this out best I can, but merely mention this as a live example of why an analysis/repair module would be a desirable feature.

📎 Aeva Permissions.jpg - 73.35 kB, 728x744, viewed 408 times.

Nao

  • Dadman with a boy
  • Posts: 16,082
Re: Database Backup, Restore and Repair
« Reply #1, on April 25th, 2012, 07:48 AM »
We removed that feature last year. Our thinking is that you can't trust your data to a minor admin feature. There are third party pho scripts that do a much better job at that.

live627

  • Should five per cent appear too small / Be thankful I don't take it all / 'Cause I'm the taxman, yeah I'm the taxman
  • Posts: 1,670
Re: Database Backup, Restore and Repair
« Reply #2, on April 25th, 2012, 07:55 AM »
First up, Wedge doesn't have that feature. It's problems seemingly outweighed its benefits. That said, someone could create a plugin for this if they really wanted to. I have no idea who would, though...
Quote
Now I do, I think, understand why the backup could fail and if I understand correctly, it's all about maximum execution times. But that surely is down to bad design in the first place?
SMF's db dumper attempts to elongate the timeout. It also didn't handle special characters..
Quote
thus allowing for incremental backups
The messages table has timestamps which could be used for this purpose. Also, other tables related to messages could join to get relevant times.

Other tables would have to be absolute.

A confident man keeps quiet.whereas a frightened man keeps talking, hiding his fear.

Arantor

  • As powerful as possible, as complex as necessary.
  • Posts: 14,278
Re: Database Backup, Restore and Repair
« Reply #3, on April 25th, 2012, 05:26 PM »
SMF's dumper had two main problems, and a few subsidiary ones.

Firstly, yes, it attempted to staunch the timeout, but it never reserved enough memory and especially when gzipping, it would frequently hit memory limits and truncate the backup. (That's the biggest problem.)

Improper handling of special characters is another, and one that we have fortunately simplified in Wedge's case by only having UTF-8, but even that is not really a suitable approach.

I find it interesting to note that even WordPress does not itself offer a DB backup facility, though it does allow for an export facility of sorts.

If I were interested in providing a DB backup facility, I'd bundle functionality and data together, I'd put members+permissions+groups together since odds are that would not be too huge a set of data. Then I'd bundle boards+topics+messages but that's very likely to be the hugest part of data anyway, by a large majority on most sites.

Incremental backups would be tricky to perform since while the messages table has timestamps, you have to contend with both new and modified timestamps, and the fact it is implicitly tied to the members table which would also need to be handled incrementally to make that work.

You're ultimately better off contending with a more dedicated tool - like phpMyAdmin.
When we unite against a common enemy that attacks our ethos, it nurtures group solidarity. Trolls are sensational, yes, but we keep everyone honest. | Game Memorial

Norodo

  • Oh you Baidu, so randumb. (60 sites being indexed at once? Jeez)
  • Posts: 469
Re: Database Backup, Restore and Repair
« Reply #4, on April 25th, 2012, 07:40 PM »
phpMyAdmin has its own problems, at least when dealing with larger databases. (I've had so much stuff dissapear. :\) There's also the post_max_size and upload_max_filesize limits on most servers.

The only tool I've never had a problem with is mysqldump and "mysql < database.sql". Unfortunately, you can't really expect less experienced webmasters to use them.

In the end I think the export tool of Wordpress, MediaWiki and the like might be the "lest shitty" option to do this without fiddling with mysqldump. That said, I don't really have the need for such facilities.

Arantor

  • As powerful as possible, as complex as necessary.
  • Posts: 14,278
Re: Database Backup, Restore and Repair
« Reply #5, on April 25th, 2012, 07:45 PM »
Whatever you do, you're going to get into issues when trying to fudge around those limits. They're not really changeable.

Have you ever seen the output of WP's export? It really isn't actually that useful, IMO, such that I ended up eschewing it entirely, heh, just as I have with SMF's export.

Norodo

  • Oh you Baidu, so randumb. (60 sites being indexed at once? Jeez)
  • Posts: 469

Arantor

  • As powerful as possible, as complex as necessary.
  • Posts: 14,278
Re: Database Backup, Restore and Repair
« Reply #7, on April 25th, 2012, 08:49 PM »
Ah, well, the WP export basically pushes out an XML file that contains the content of posts. It's not a full DB export, doesn't necessarily hold all meta data for posts, doesn't necessarily hold all the related information like post owners on multi-author blogs, which to me doesn't seem that useful.

markham

  • Finally finished the Slideshow... phew!
  • Posts: 138

Arantor

  • As powerful as possible, as complex as necessary.
  • Posts: 14,278

Pandos

  • Living on the edge of Wedge
  • Posts: 635
# dpkg-reconfigure brain
error: brain is not installed or configured

Arantor

  • As powerful as possible, as complex as necessary.
  • Posts: 14,278
Re: Database Backup, Restore and Repair
« Reply #11, on April 27th, 2012, 02:09 PM »
Yes, but you're not the sort of user for who any of this is a problem. Folks like us who can administer with heavyweight tools do not have a problem because those heavyweight tools do the work. For the purpose of discussing incremental backups, we'd be talking about implementing it at the application level, nor the server level where XtraBackup works.

Folks who can use the command line have no such problems with timeouts or incomplete backups or corrupted backups, it's the poor sods who are on shared hosting who get shafted, every time.

Nao

  • Dadman with a boy
  • Posts: 16,082
« Number of 'online users'
Wedge and International Domain Names (IDNs) »