Support

Akeeba Backup for Joomla!

#8436 Cron backup and mysql wait_timeout bug

Posted in ‘Akeeba Backup for Joomla!’
This is a public ticket

Everybody will be able to see its contents. Do not include usernames, passwords or any other sensitive information.

Environment Information

Joomla! version
n/a
PHP version
n/a
Akeeba Backup version
n/a

user10710
Hi,

I want to share something which in my opinion is a bug. Recently I upgraded to pro and tested backup.php cron script.
It goes OK (mean creating files and starting post processing phase) but it fails to update ak_stats table . Soon after starting it executes inserts query on that table with status='run'. After script is finished with packaging it starts set_or_update_statistics function which is trying to run update query on mentioned table with status='completed' and backupend set - that query is not running properly and there is not error handling after $db->query(). In my case $db object has error set saying "mysql gone away" - this is because of wait_timeout was set to 300s but whole operation was of course longer. So in my opinion it should be error handling there and also AEFactory::getDatabase should handle connection timeouts somehow.

dlb
Oh boy, is this one for Nicholas! :D


Dale L. Brackin
Support Specialist


us.gifEnglish: native


Please keep in mind my timezone and cultural differences when reading my replies. Thank you!


????
My time zone is EST (UTC -5) (click here to see my current time in Philadelphia, PA)

nicholas
Akeeba Staff
Manager
Technically, at this point the backup is done. Adding error handling will do what? If the MySQL server is dead I can't mark the backup as failed, can I? Moreover, the post-processing steps run after the call to set_or_update_statistics(). Let's compare what happens now and what would happen with the - intentionally left out - error handling:

NOW: The main backup process finishes. The backup is valid. MySQL is dead. I don't care. Post-processing runs and the archive is stored to, say, Amazon S3. User is happy, backup is safe.

WITH ERROR HANDLING: The main backup process finishes. The backup is valid. MySQL is dead. I pick it up and call it a failed backup. I delete the backup archive. MySQL still shows it as running ( the connection is dead, remember? ). Post-processing never runs. User visits the back-end and sees a failed backup (pending backups are force-failed when visiting the Control Panel) and no file in S3. User gets angry and claims that the advertised cloud backup feature doesn't work. And I do, what? Punch his host in the face because he kills the MySQL connection within a second or so? Well, I probably should, but...

Both of the solutions are suboptimal. What I am intending to do is run the table update on a new step, so that there is a fresh db connection and the query doesn't fail ;)

Nicholas K. Dionysopoulos

Lead Developer and Director

🇬🇷Greek: native 🇬🇧English: excellent 🇫🇷French: basic • 🕐 My time zone is Europe / Athens
Please keep in mind my timezone and cultural differences when reading my replies. Thank you!

user10710
I agree - if mysql is death we can not mark backup as failed - we can not do any query cause mysql is death - but in my case my sql was not death - it was only the connection time out.
Also there was problem like this:
Backup was done successfully but in mysql it has status="run". I am using "Enable count quota". Next backup task checks mysql table and sets "fail" for all "run". I had count quota set to 1 - but all the files from previous backup (this with run status replaced by fail) was not deleted - this was not expected and may lead to disk capacity problem - please check this also.
Thanks.

nicholas
Akeeba Staff
Manager
Dead MySQL = MySQL timeout. We're talking about the same thing.

If a backup is set to "run" when the Control Panel is launched, the status is set to "failed" and all files are removed. I checked it, it's working here. Same goes when rerunning the backup file. The same cleanup method is called both times, removing old and - presumably failed - archives.

Nicholas K. Dionysopoulos

Lead Developer and Director

🇬🇷Greek: native 🇬🇧English: excellent 🇫🇷French: basic • 🕐 My time zone is Europe / Athens
Please keep in mind my timezone and cultural differences when reading my replies. Thank you!

user10710
>> Dead MySQL = MySQL timeout. We're talking about the same thing.
Can not agree. As I mentioned before I had wait_timeout = 300 set in my.cnf - so mysql was not dead but connection time out occurred (a connection object had been set on the beginning of backup job and after 300 seconds mysql has terminated the connection). In function set_or_update_statistics there is an object $db =& AEFactory::getDatabase on which I have done var_export() and saw it holds an error - "server gone away" but mysql was not death - please see mysql documentation here:
http://dev.mysql.com/doc/refman/5.0/en/gone-away.html
This was exactly the case as written in mysql docs. When I set wait_timeout higher (longer than whole backup time) (eg. 3600 s) everything goes well.

nicholas
Akeeba Staff
Manager
Let me rephrase it in the exact terms I meant:

If the MySQL connection is dead (due to a client or server timeout, the mysqld process experiencing an error, the network, shared memory or connection pipe being suspended/broken/terminated/otherwise blocked or hampered or not properly functional, or any other event which might prevent the PHP thread or process from accessing the MySQL server resources) then... (rest of my message).

I thought that adding this long list of things which would go wrong is as nonsensical as the worst form of legalese, therefore I just used the term "dead MySQL" which meant to be perceived in this context.

I take your point about the connection timing out. I will close and reopen the db connection when running in CRON mode to ensure that this error doesn't happen with long-running backup jobs.

Nicholas K. Dionysopoulos

Lead Developer and Director

🇬🇷Greek: native 🇬🇧English: excellent 🇫🇷French: basic • 🕐 My time zone is Europe / Athens
Please keep in mind my timezone and cultural differences when reading my replies. Thank you!

user10710
OK. Think, this is enough.

nicholas
Akeeba Staff
Manager
I will post the fixed release later today. I am now testing this (only for the native CRON mode): as soon as the database backup is over, the database connection is closed and the database object freed. Once the file backup ends and the engine hits the finalization stage, the database object will be automatically spawned (and reconnected to the MySQL server) just before the backup stats record is updated. This should be enough to avoid the connection timeout, as the bulk of the database-idle backup process (file packing) will be performed without an active MySQL connection.

Nicholas K. Dionysopoulos

Lead Developer and Director

🇬🇷Greek: native 🇬🇧English: excellent 🇫🇷French: basic • 🕐 My time zone is Europe / Athens
Please keep in mind my timezone and cultural differences when reading my replies. Thank you!

Support Information

Working hours: Typically we work Monday to Friday, 9am to 7pm Cyprus timezone (EEST). Support is provided by the same developers writing the software, all of which live in Europe. You can still file tickets, but we cannot respond to them, outside of our working hours.

Support policy: We would like to kindly inform you that when using our support you have already agreed to the Support Policy which is part of our Terms of Service. Thank you for your understanding and for helping us help you!

Summer vacations: Our support will be closed for replies and new tickets from August 6th to August 21st, 2022 due to summer vacations.