Binary mysqldump: error (code: 5)

[Riku TurunenParticipant]

Hi there!

I’m trying to troubleshoot / speedup my backups, and the logs tells me, Updraft is not propably using the mysqldump binary.

This is what the log says when initiating a database backup:

0000.039 (0) Testing potential mysqldump binary: /usr/bin/mysqldump
0000.047 (0) Output: /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;\n/*!40103 SET TIME_ZONE='+00:00' */;\n/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;\n/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;\n/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;\n/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
0000.052 (0) Binary mysqldump: error (code: 5)

However, I was not able to find too much information with the error code, so I’m trying my luck asking here. What does the “code: 5” mean?

I have set my max packet size for mysqldump to 64M and for mysqld the max allowed packet is 256M.

[Riku TurunenParticipant]

This reply has been marked as moderator-only.

[Riku TurunenParticipant]

Ok, I found the cause. In the latest mysql version, you must use option “–no-tablespace” when running mysqldump if the database user does not have global privileges for PROCESS command. In my case I granted this privilege to my wordpress user, but I’d rather not do that on ALL other mysql users on all my sites that are being backed up with updraftplus.

Therefore: I suggest you add the parameter “–no-tablespace” to the mysql dump command on the future update of Updraftplus.

[Bryle CroduaModerator]

Hi Riku,

Thanks for the info. I will forward this to our developers for review.

Thanks,
Bryle

[Riku TurunenParticipant]

Another issue with Mysql 8 & UpdraftPlus.

In the class-updraftplus.php there’s check being made for valid mysqldump binary. You have this in the test SQL clause:
--where=option_name=\'siteurl\'

This doesn’t work. It should be:
--where="option_name='siteurl'"

Server is running on Ubuntu 20.04 LTS. Right now I’ve manually fixed the code on all 40+ sites running on the server, but it’s something I’d rather not re-do once next Updraftplus update arrives…

[Riku TurunenParticipant]

Update just arrived and had to re-fix manually all 40+ sites. Can you please implement the fix to the plugin itself. Thanks.

[udadminKeymaster]

Hi Riku,

The changes in MySQL 8 regarding the tablespace parameter are something we’re already tracking. Please watch out for a future release. Note that with recent improvements in UD’s MySQL algorithm, using mysqldump no longer gives you a very large speed-up; the PHP algorithm has closed most of the distance.

David

[Riku TurunenParticipant]

Speed difference between mysqldump and php is huge. For example eCommerce site that has 2 million rows in the postmeta table, it takes several minutes to complete with PHP. With mysqldump binary it’s matter of seconds.

[udadminKeymaster]

Hi Riku,

Can you post a log with an example of that, using PHP? It used to be the case on old versions that you could see that sort of performance, but since last year UD has been using indexes to achieve comparable speed to mysqldump.

David.

[Riku TurunenParticipant]

Check log here: https://pastebin.com/Y1Q2hhxh

[udadminKeymaster]

Hi,

Thank you. Please try our next release when it comes out – it has an improvement for finding a working mysqldump binary that I believe will allow it to be used on your setup.

David

[Author]

Posts