Hi,
I am just checking this for you.
Hi Linus,
UpdraftPlus outputs some table backups itself, using its own code, but for some tables (particularly, larger ones), it will use the “mysqldump” program on your webserver, and this outputs in its own format. (This is done because mysqldump is faster, because it’s written in compiled C code, which is always faster than interpreted PHP – and on large tables, that makes a difference). Is the difference you’re seeing between tables that are and aren’t output with mysqldump?
(This has been so for around 10 years, by the way, but perhaps the relevant table you’re looking at has got larger in that time?).
Or if it’s not that, you’ll have to give an example of exactly what you’re seeing that’s changed, since the relevant code hasn’t changed in a good while that I remember.
David
Hi David,
Thanks for looking into this.
I have a table from WP Data Table, consisting of approximately 7.2k rows, which should be modest(?).
In the backup file I see the following, where the row count seems to be wrong (if I do a count(*) on the table, I do get the 7.2k).
Data contents of table wp_wpdatatable_1 Approximate rows expected in table: 5054
The first six insert statements correctly contain 1,000 entries, but the last one contains 1,200+ entries which breaks the SQL code.
Here is the table structure and the preceding code:
DROP TABLE IF EXISTS wp_wpdatatable_1; Table structure of table wp_wpdatatable_1
CREATE TABLE wp_wpdatatable_1 (
wdt_ID int(11) NOT NULL AUTO_INCREMENT,
manager varchar(255) DEFAULT NULL,
strategy varchar(255) DEFAULT NULL,
date date DEFAULT NULL,
ror decimal(16,4) DEFAULT NULL,
aum int(11) DEFAULT NULL,
userid int(11) DEFAULT NULL,
estimated varchar(2000) DEFAULT NULL,
UNIQUE KEY wdt_ID (wdt_ID)
) ENGINE=InnoDB AUTO_INCREMENT=186330736 DEFAULT CHARSET=utf8 ;
Hi,
The code in UpdraftPlus that decides how many rows to fetch at once is dynamic, and responds to server conditions and performance. For some tables it defaults to 1000, some to 4000, and even 100,000, but then it can adjust as the backup goes along depending on performance. That’s been the case for some years, so, if you’re mostly seeing 1000, that’s not been a fixed constant but just how things have ended up on your server.
Approximate rows expected in table: 5054
MySQL has a fast function for returning an *approximate* number of rows, and that’s where that comes from (since it’s only a log message). To get the precise number you have to do COUNT(*), but on a large table, that can be very slow – see: https://newrelic.com/blog/how-to-relic/fast-counting-in-postgresql-and-mysql
David
Hi David
Gotcha, so I was probably lucky before. The split at 1,000 rows did align nicely with the restrictions of SQL Server, into which I imported the data for further analysis. I don’t think this restriction exists in MariaDB/MySQL.
I assume that there is no way to force 1,000 per import statement?
Linus
No, unfortunately there isn’t
Thanks, I’ve marked this thread as resolved, thanks again for the technical details
I’ve added a WordPress filter updraftplus_number_of_rows_to_fetch that you’ll be able to use in future versions (i.e. any releases after today), like so – here’s how you’d set it to never fetch more than 1000 at once (put this code snippet in your preferred tool for adding code snippets):
add_filter('updraftplus_number_of_rows_to_fetch', function($rows) { return min($rows, 1000); });
That’s an awesome fix! I believe I might be using Updraft in a pretty non-standardized manner but thanks for releasing the option 🙂
Happy Thanksgiving!
