[ABD] Auto db Backup

[david63]

The only way you could get it to run at 3am would be to reset it each day and even then there is no guarantee that it would run at that time.

As you point out it is governed by the way phpBB cron works, which is not ideal, and is only instigated when there is a visit to the site. This is even more compounded by the fact that it will also be dependant on any other cron jobs that are in the queue before this one.

The time for the next backup will be the current time plus the time interval.

As for copying the backup to Google Drive it would be possible for someone to create an extension to do that in the same way that my Dropbox Upload extension copies the backup to Dropbox.

[javiexin]

The time for the next backup will be the current time plus the time interval.

Is there any way to make the 3am stick there and not update/change? I realise that if the cron doesn't start till 9am, and then running it again at 3am wouldn't be 24 hours after.... but that's fine with me.

If I understood Steve right, what he is asking for is that the time for next backup is NOT current time plus interval, but originally scheduled time plus interval. For what he is saying, I think he understands the way phpbb cron tasks work...

I don't think this is too difficult to change, basically, in the cron task, replace:
$this->config->set('auto_db_backup_last_gc', time(), true); by

Code: Select all

$this->config->set('auto_db_backup_last_gc', $this->config->get('auto_db_backup_last_gc') + $this->config->get('auto_db_backup_gc'), true);

Or you may give an ACP config option, preserve interval or preserve scheduled time.
Regards,
-javiexin

PS: For what is worth, if I had the choice, I would go for a "preserve schedule" option.

[david63]

@javiexin

Obviously it would be possible but it is not quite that simple.

Take for example having the interval set at one hour. If there was no activity on the board for two hours then using the "preserved interval" method would result in the next backup being scheduled to be one hour ago, which would effectively mean that the next backup would be ready to run now. If the same thing was to happen (no activity for two hours) then the next backup would be two hours ago, etc.

Also adding into the equation the effect of timezones, DST and the vagaries of phpBB cron then the whole thing would become a mess.

[javiexin]

Ok, then something like:

Code: Select all

// Just for clarity
$last_bkp = $this->config->get('auto_db_backup_last_gc');
$this_bkp = time();
$bkp_interval = $this->config->get('auto_db_backup_gc');
$one_hour = ext::seconds;

// Schedule next backup for last schedule + interval, but at least in one hour
$next_bkp = max($last_bkp + $bkp_interval, $this_bkp + $one_hour);

// Now fake current backup time in the config table to get the right scheduled backup time
$this->config->set('auto_db_backup_last_gc', $next_bkp - $bkp_interval, true);

People that set backup frequency so low as one hour should not use "preserve schedule" but keep using the current "preserve interval" schema. Anyhow, the above code does something like that. And it makes sure that there are no backups in less than one hour (the extension minimum).

And timezones and other oddities should be taken care of when setting the date for the next backup, in the admin controller. Here all times are UTC (or at least should be), and you are calculating time intervals: a number of hours is the same regardless of the timezone or dst... Only if you say "4am each day in XXX city" you would have a difference with DST time changes (a "clock day" has 23 or 25 hours these days). But that is not being taken care of (and I don't think it matters). But I am digressing.

The code change is simple enough, I think... I have not tested it, of course, and I might be missing something, but you know the extension and I don't (only looked at it briefly), so you should be able to figure it out better...

Regards,
-javiexin

[steveprentice]

Thanks guys! Some fab chat on that, really very much appreciated.

As a temp workaround, I had taken to injecting the following SQL at the end of the google drive upload task:

UPDATE `phpbb_config`
SET config_value='1489028400'
WHERE config_name='auto_db_backup_last_gc';

That seems to do the trick from what I can see.... but the code you've come up with there looks like a much better option, thanks!

Cheers,

Steve

[steveprentice]

SET config_value='1489028400'

I'm an idiot. That should be (in my case anyway):

SET config_value=UNIX_TIMESTAMP(DATE_ADD(CURDATE(),INTERVAL '1 3' DAY_HOUR))

Also, anyone looking to automate the backup of the resulting sql file to somewhere (like google drive in my example)... then I'm sure there's more than one way, but what's working well for me is this: https://github.com/prasmussen/gdrive - i.e. gdrive.exe in Windows, which I schedule and use PowerShell script to tell it what files I want to be uploaded to where.

Cheers,

Steve

[david63]

nyone looking to automate the backup of the resulting sql file to somewhere (like google drive in my example)... then I'm sure there's more than one way,

There is. There is an event in the Auto Backup that will allow another extension to access the backup file when it is created and do whatever you want with it - such as my Dropbox Upload extensions that uploads the autobackup file to Dropbox.

If you wanted to upload automatically to Google Drive then it is only a matter of creating an extension to use the Google Drive API.

[wads24]

I do not see the settings on the extensions tab. Where can I find the settings in admin panel?

Update. Found it at
ACP -> Maintenance

[wads24]

What should I select for option that says, "Optimize the database before performing the backup:"?

[david63]

What should I select for option that says, "Optimize the database before performing the backup:"?

Yes or No

[wads24]

What should I select for option that says, "Optimize the database before performing the backup:"?

Yes or No

You're funny

[digitaltoast]

Based on experience, I would select "NO", because it doesn't backup with "YES"!

That said, you should be using InnoDB anyway, which apparently doesn't allow for optimisation like that (someone correct me if wrong)

[wads24]

Based on experience, I would select "NO", because it doesn't backup with "YES"!

That said, you should be using InnoDB anyway, which apparently doesn't allow for optimisation like that (someone correct me if wrong)

Thank you! You were more helpful than the actual developer of the extension!

[KhurramMunawar]

Dear David

Is the backup time issue has been resolved or not yet.

[david63]

Dear David

Is the backup time issue has been resolved or not yet.

What issue? As far as I am aware the time is working as intended