GZIP in backup

[Jason H]

Ok.. That was my bright idea about defaulting the GZIP option in SQL Backup to "No"...

I now realize I was wrong.. I didn't go far enough. Can we just remove that functionality totally?

It just doesn't work except for a miniscule number of people who either have tiny forums that don't really NEED to GZIP the content anyway, or the people who know how to configure PHP to not timeout.. And they aren't doing backups from IPB anyway.. They're using phpMyAdmin or SSH.

Let's just kill it. It was a fine idea that has outlived its usefulness. It's just as easy to download the full DB, know it's a good backup, and then the user can ZIP the content after they have downloaded it.

[bfarber]

How about we just look into a way to support the gzipped backup better? :P I'm sure there is a way we can gzip the buffer sending it to the browser instead of reading it all into a string and then gzipping it.

[rct2·com]

I'd rather you spent time in this area implementing a RESTORE to go with the backup.

I attended a seminar many years ago at Novell HQ in the UK. There was a guy presenting on backup solutions [for NetWare]. His main message was that buyers should go out and look for good RESTORE products. Backups were less than 20% of the problem, 80% focus should be on how to restore the backup.

PHPMyAdmin has got that badly wrong in my opinion [easy to backup, hard to restore], so hase IP.Board.

[Nils]

How about we just look into a way to support the gzipped backup better? :P I'm sure there is a way we can gzip the buffer sending it to the browser instead of reading it all into a string and then gzipping it.

Why don't you use gzfile() to save the backup to a file first and then redirect the browser to it? Wouldn't help with the PHP timeouts, though.

PHPMyAdmin has got that badly wrong in my opinion [easy to backup, hard to restore], so hase IP.Board.

What the hell? I don't know about IPB, but in phpMyAdmin you select the file, click the submit button and you're done. How much easier can you make it?

[bfarber]

Or use the load data infile, but yeah, it's about as easy in phpmyadmin as it can possibly be. :unsure:

The problem with implementing any type of restore option in IP.Board is simply timeouts. We run into the problem of someone with a 1 GB backup file trying to upload or import it via the ACP. :-\ Realistically, we just send out a .sql file - this can be restored via phpmyadmin, or commandline, already. Do we really need to add a restore method into IPB too?

[Keith J. Kacin]

The restore feature would be pointless in 90% of the cases where a restore is needed, in my opinion.

[Nils]

Well, I guess for people who don't know anything about MySQL and phpMyAdmin a restore option might be useful. But then again they wouldn't usually run their own forums...

[Jason H]

The restore function.. I look at it as a bad idea from a security standpoint.

The main problem? Someone throws up a .SQL file with nothing but drop table commands in it and <poof> your board is gone.

If you don't allow the drop table command to be used, then you can't do a full backup, because the backup wouldn't restore.

I just want to see the backup be right. Probably one of the most critical areas.. Too many people do a backup and just assume it's good. We need to find a way that in 99% of cases, it is.

Disabling the GZIP by default has moved it up to a much higher success rate, but people see that "Make smaller", click it, and then think when they get a 3k file that's their database.

It's also a problem that when a table is crashed, the backup appears to have succeeded when it dies at the crashed table. But.. A very minor problem. Rarely see this.

[Luke]

Personally I would rather see the backup function go away completely and replace it with good instructions on how to backup and restore your database. If the database is too big, either way you're better off doing it through shell anyway.

As far as the SQL Management area goes, all you really need is what's there at the moment. The ability to easily repair a table if you need to, etc...

The biggest thing I would like to see is a favorite query thing where you can add/remove queries to a list with a description and hit a "Run" button. Not all my admins are familiar with SQL code and a lot of missing tools in IPB could easily be filled by a customizable query list. Example: Admins/Moderators can't view IP addresses for comments made, let alone find what comments someone made to other profiles.... Custom query with a simple input box or two (for ID, IP, etc..) would be really cool.

[bfarber]

I like the favorites query idea. Could input it as

select * from ibf_sometable where somecolumn='%s'

Then have an input box (as many as are needed for all %s entries) and do a standard sprintf on the result with the query as a base. Will hit some roadbumps, but the idea is a pretty good one (in the right hands...).

[Josh04]

What the hell? I don't know about IPB, but in phpMyAdmin you select the file, click the submit button and you're done. How much easier can you make it?

I remember in older versions of phpMyAdmin that wasn't there, there was only an export button. I might just have been more hidden though.

[Michael]

I like the favorites query idea. Could input it as

select * from ibf_sometable where somecolumn='%s'

Then have an input box (as many as are needed for all %s entries) and do a standard sprintf on the result with the query as a base. Will hit some roadbumps, but the idea is a pretty good one (in the right hands...).

Dean made a mod a few versions back for me when I asked about this same feature. If you're going to add this, you could do like you said where you could add inputs, but his mod also just let you build full queries and save them. I used it to do things like show who has the highest posts-per-day average, which skins were used the most, etc., stuff you can query for if you know the query but that you can't find from IPB as it is by default.

[bfarber]

Well, yes, if there were no replacement tags (whatever I'd do about them) then there would be no input field and clicking the query would immediately just execute it.

[Jason H]

I remember in older versions of phpMyAdmin that wasn't there, there was only an export button. I might just have been more hidden though.

There's the other problem with the possibility of restore in IPB.. The file would have to be uploaded via PHP, which would limit it to 8M on most installations. Once your DB hits 50M or so.. Restoration is basically limited to shell. And, I stand by restore being a bad idea from a security standpoint. Someone has access to your site, and can import a file of SQL queries? Pretty easy to destroy a forum that way.

Secondary problem is disk quotas. Most hosts don't count your DB size against your disk quota, but add in the space to upload the backup.. though that is rather minor, you'd just have to remember to delete it afterwards.

I got fooled last night.. Transferring a forum and I did the SQL backup because they hadn't given me phpMyAdmin access.. Turned out that it bombed about 190k posts through a 230k post table. I didn't notice it until the restore and subsequent attempted upgrade, but pretty easy to figure out, and I knew what had happened. Used a few tools to do a better backup and all was well.. But.. The backup appeared to be about the right size.. So I didn't think anything about it.

Problem is.. If i had been Joe Q User, and I assumed the backup was good.. That could have been trouble. The topics table didn't get backed up, so the whole dump was useless. Interesting thing was that this was a non-GZIP backup and it still failed. Just a massive posts table caused it to bomb.

[Luke]

That's why I suggest removing the whole backup thing and put in good instructions on how to backup and restore using shell....

[Nils]

And, I stand by restore being a bad idea from a security standpoint. Someone has access to your site, and can import a file of SQL queries? Pretty easy to destroy a forum that way.

If someone has access to your ACP they won't need any SQL files to destroy your forums...

[Nils]

That's why I suggest removing the whole backup thing and put in good instructions on how to backup and restore using shell....

Not everybody has shell access, though. For smaller forums on shared servers using phpMyAdmin/ACP is often the only option.


edit: So... why is post merging disabled here? ;)

[Luke]

If they have no access to shell and it's a small forum, they can use phpMyadmin then. But if it's a forum over 50 MB, which is quite small to begin with, they should have some sort of shell access, even if it's limited. I know that on many shared hosts that use cPanel you have limited shell access, which should give you enough to export/import a database. Heck you can download/restore a backup in cPanel.

That's why I think it's completely useless to have the feature, especially when other web based options work better anyway. Replacing it with detailed instructions would be very helpful to the end user. It could detect how big the database is going to be and recommend shell because of the size.

[Nils]

If they have no access to shell and it's a small forum, they can use phpMyadmin then.

Not everybody has phpMyAdmin and knowns how to make backups with it. Why force your users to learn how to use a 3rd-party software when you can just include the functionality in your own software?

But if it's a forum over 50 MB, which is quite small to begin with, they should have some sort of shell access, even if it's limited. I know that on many shared hosts that use cPanel you have limited shell access, which should give you enough to export/import a database. Heck you can download/restore a backup in cPanel.

I've had several shared hosting packages over the last few years, and I can't remember any of them having shell access (or cPanel, for that matter). The first time I got shell access was when I got my own server.

[Jason H]

If someone has access to your ACP they won't need any SQL files to destroy your forums...

Partially true. If they have access through a non-root admin account, the SQL toolbox is off limits. Can damage still be done through other methods? Of course, but it's generally a bit more difficult for them to delete all members from the forum via the ACP than it is to go to the SQL toolbox.. And they cannot drop tables from the SQL Toolbox, and the truncate command is disabled as well. While there are ways around that.. Hopefully when they see truncate is disabled...

It takes time to do things through the ACP.. Restoring a nasty SQL dump could be done in seconds.

[bfarber]

@Nils, maybe so. Maybe this setup and that setup could benefit from it.

But you have no idea the number of tickets we get from people that have 3GB databases and try to back them up from the IPB ACP. :blink: Then they state "If the feature is there it should work - You should fix it" not understanding the reasons why it doesn't work.

If we add restore, people will not understand why they can't upload 500MB gz files through the ACP to restore them (or why it times out when they try to import the files even if they are already on disk). It's trouble waiting to happen, and would be a tremendous headache for our support staff.

I think the current implementation is useful in many cases. I just don't see much of a need to add restore options.