Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

[BUG] adding --rows to command triggers unknown column in where clause warning #379

Closed
roni-estein opened this issue Jul 15, 2021 · 3 comments · Fixed by #375
Closed

[BUG] adding --rows to command triggers unknown column in where clause warning #379

roni-estein opened this issue Jul 15, 2021 · 3 comments · Fixed by #375
Labels
duplicate
Milestone
Release 0.10.7-2

Comments

@roni-estein
Copy link

Basic run works fine, but I read in an article that running with different --row sizes works better with more threads when you have a few extremely large tables.

When I add -r 500 or --rows=500 for example certain tables trigger this:

** (mydumper:14630): WARNING **: 10:20:43.037: Unable to get estimates for export.xf_cron_entry: Unknown column 'cleanUpDaily' in 'where clause'
[1] 14630 segmentation fault mydumper -u root -B export -o ~/backups/mydumper/export -t 8 -r 500

It appears if a table does not have a primary key or a numeric key the rows parameter can't back it up. This is unfortunate, lots of tables are basically hash tables that may not have a primary key. Though this is trivial to add in many cases we don't control the source database.

Some micro optimizations might be if the table has less rows than the limit, like most hash tables, just skip the where altogether and pull the table. An alternative in tables like this might also just be to use the offset/limit instead of the where. It may not be as good, but it won't seg-fault.

DESCRIBE
Field	Type	Null	Key	Default	Extra
entry_id	varbinary(25)	NO	PRI	NULL	
cron_class	varchar(100)	NO		NULL	
cron_method	varchar(75)	NO		NULL	
run_rules	mediumblob	NO		NULL	
active	tinyint unsigned	NO	MUL	NULL	
next_run	int unsigned	NO		NULL	
addon_id	varbinary(50)	NO		NULL	


ACTUAL
entry_id	cron_class	cron_method	run_rules	active	next_run	addon_id
cleanUpDaily	XF\\Cron\\CleanUp	runDailyCleanUp	{"day_type":"dom","dom":[-1],"hours":[3],"minutes":[0]}	1	1599102018	XF
cleanUpHourly	XF\\Cron\\CleanUp	runHourlyCleanUp	{"day_type":"dom","dom":[-1],"hours":[-1],"minutes":[10]}	1	1599073820	XF
dailyStats	XF\\Cron\\Counters	recordDailyStats	{"day_type":"dom","dom":[-1],"hours":[0],"minutes":[30]}	1	1599093038	XF
deleteExpiredBans	XF\\Cron\\Ban	deleteExpiredBans	{"day_type":"dom","dom":[-1],"hours":[-1],"minutes":[45]}	1	1599072325	XF
downgradeExpired	XF\\Cron\\CleanUp	runUserDowngrade	{"day_type":"dom","dom":[-1],"hours":[-1],"minutes":[50]}	1	1599072653	XF
emailBounce	XF\\Cron\\EmailBounce	process	{"day_type":"dom","dom":[-1],"hours":[-1],"minutes":[23,53]}	1	1599072834	XF
emailUnsubscribe	XF\\Cron\\EmailUnsubscribe	process	{"day_type":"dom","dom":[-1],"hours":[-1],"minutes":[13,43]}	1	1599072200	XF
expireTempUserChanges	XF\\Cron\\CleanUp	expireTempUserChanges	{"day_type":"dom","dom":[-1],"hours":[-1],"minutes":[42]}	1	1599072137	XF
feeder	XF\\Cron\\Feeder	importFeeds	{"day_type":"dom","dom":[-1],"hours":[-1],"minutes":[2,12,22,32,42,52]}	1	1599072176	XF
fileCheck	XF\\Cron\\FileCheck	checkFiles	{"day_type":"dom","dom":[-1],"hours":[0],"minutes":[10]}	1	1599091804	XF
forumStatistics	XF\\Cron\\Counters	rebuildForumStatistics	{"day_type":"dom","dom":[-1],"hours":[-1],"minutes":[3,13,23,33,43,53]}	1	1599072235	XF
memberStatsCache	XF\\Cron\\MemberStats	rebuildMemberStatsCache	{"day_type":"dom","dom":[-1],"hours":[-1],"minutes":[0,10,20,30,40,50]}	1	1599072055	XF
sitemap	XF\\Cron\\Sitemap	triggerSitemapRebuild	{"day_type":"dow","dow":[1,3,5],"hours":[5],"minutes":[37]}	1	1599197838	XF
trophy	XF\\Cron\\Trophy	runTrophyCheck	{"day_type":"dom","dom":[-1],"hours":[-1],"minutes":[40]}	1	1599072043	XF
userGroupPromotions	XF\\Cron\\UserGroupPromotion	runPromotions	{"day_type":"dom","dom":[-1],"hours":[-1],"minutes":[20]}	1	1599074455	XF
views	XF\\Cron\\Views	runViewUpdate	{"day_type":"dom","dom":[-1],"hours":[-1],"minutes":[30]}	1	1599075006	XF
warningExpiry	XF\\Cron\\Warnings	expireWarnings	{"day_type":"dom","dom":[-1],"hours":[-1],"minutes":[55]}	1	1599072920	XF
xfesReindex	XFES\\Cron\\Reindex	reindex	{"day_type":"dom","dom":[-1],"hours":[-1],"minutes":[5,15,25,35,45,55]}	1	1599072322	XFES
 mydumper -u root -e -B export -o ~/backups/mydumper/export -t 8 -r 500 -v 3 -L dump.log

Log
2021-07-15 10:57:37 [INFO] - Server version reported as: 8.0.25
2021-07-15 10:57:37 [INFO] - Connected to a MySQL server
2021-07-15 10:57:37 [INFO] - Started dump at: 2021-07-15 10:57:37
2021-07-15 10:57:37 [INFO] - Written master status
2021-07-15 10:57:37 [INFO] - Thread 1 connected using MySQL connection ID 90
2021-07-15 10:57:37 [INFO] - Thread 2 connected using MySQL connection ID 91
2021-07-15 10:57:37 [INFO] - Thread 3 connected using MySQL connection ID 92
2021-07-15 10:57:37 [INFO] - Thread 4 connected using MySQL connection ID 93
2021-07-15 10:57:37 [INFO] - Thread 5 connected using MySQL connection ID 94
2021-07-15 10:57:37 [INFO] - Thread 6 connected using MySQL connection ID 95
2021-07-15 10:57:37 [INFO] - Thread 7 connected using MySQL connection ID 96
2021-07-15 10:57:37 [INFO] - Thread 8 connected using MySQL connection ID 97
2021-07-15 10:57:37 [INFO] - Thread 1 dumping db information for export
2021-07-15 10:57:37 [INFO] - Thread 2 dumping schema create for export
2021-07-15 10:57:37 [INFO] - Thread 3 dumping data for export.xf_attachment_view
2021-07-15 10:57:37 [INFO] - Empty table export.xf_attachment_view
2021-07-15 10:57:37 [INFO] - Thread 5 dumping data for export.xf_attachment WHERE (attachment_id >= 1300 AND attachment_id < 2599)
2021-07-15 10:57:37 [INFO] - Thread 7 dumping data for export.xf_attachment WHERE (attachment_id >= 3898 AND attachment_id < 5197)
2021-07-15 10:57:37 [INFO] - Thread 8 dumping data for export.xf_attachment WHERE (attachment_id >= 5197 AND attachment_id < 6496)
2021-07-15 10:57:37 [INFO] - Thread 4 dumping data for export.xf_attachment WHERE attachment_id IS NULL OR (attachment_id >= 1 AND attachment_id < 1300)
2021-07-15 10:57:37 [INFO] - Thread 1 dumping data for export.xf_attachment WHERE (attachment_id >= 6496 AND attachment_id < 7795)
2021-07-15 10:57:37 [INFO] - Thread 6 dumping data for export.xf_attachment WHERE (attachment_id >= 2599 AND attachment_id < 3898)
2021-07-15 10:57:37 [INFO] - Thread 3 dumping data for export.xf_attachment WHERE (attachment_id >= 7795 AND attachment_id < 9094)
2021-07-15 10:57:37 [INFO] - Thread 2 dumping data for export.xf_attachment WHERE (attachment_id >= 9094 AND attachment_id < 10393)
2021-07-15 10:57:37 [INFO] - Thread 5 dumping data for export.xf_attachment WHERE (attachment_id >= 10393 AND attachment_id < 11692)
2021-07-15 10:57:37 [INFO] - Thread 4 dumping data for export.xf_attachment WHERE (attachment_id >= 11692 AND attachment_id < 12991)
2021-07-15 10:57:37 [INFO] - Thread 6 dumping data for export.xf_attachment WHERE (attachment_id >= 12991 AND attachment_id < 14290)
2021-07-15 10:57:37 [INFO] - Thread 7 dumping data for export.xf_attachment WHERE (attachment_id >= 14290 AND attachment_id < 15589)
2021-07-15 10:57:37 [INFO] - Thread 8 dumping data for export.xf_attachment WHERE (attachment_id >= 15589 AND attachment_id < 16888)
2021-07-15 10:57:37 [INFO] - Thread 1 dumping data for export.xf_attachment WHERE (attachment_id >= 16888 AND attachment_id < 18187)
2021-07-15 10:57:37 [INFO] - Thread 3 dumping data for export.xf_attachment WHERE (attachment_id >= 18187 AND attachment_id < 19486)
2021-07-15 10:57:37 [INFO] - Thread 2 dumping data for export.xf_attachment WHERE (attachment_id >= dump.log
2021-07-15 10:57:37 [INFO] - Server version reported as: 8.0.25
2021-07-15 10:57:37 [INFO] - Connected to a MySQL server
2021-07-15 10:57:37 [INFO] - Started dump at: 2021-07-15 10:57:37
2021-07-15 10:57:37 [INFO] - Written master status
2021-07-15 10:57:37 [INFO] - Thread 1 connected using MySQL connection ID 90
2021-07-15 10:57:37 [INFO] - Thread 2 connected using MySQL connection ID 91
2021-07-15 10:57:37 [INFO] - Thread 3 connected using MySQL connection ID 92
2021-07-15 10:57:37 [INFO] - Thread 4 connected using MySQL connection ID 93
2021-07-15 10:57:37 [INFO] - Thread 5 connected using MySQL connection ID 94
2021-07-15 10:57:37 [INFO] - Thread 6 connected using MySQL connection ID 95
2021-07-15 10:57:37 [INFO] - Thread 7 connected using MySQL connection ID 96
2021-07-15 10:57:37 [INFO] - Thread 8 connected using MySQL connection ID 97
2021-07-15 10:57:37 [INFO] - Thread 1 dumping db information for export
2021-07-15 10:57:37 [INFO] - Thread 2 dumping schema create for export
2021-07-15 10:57:37 [INFO] - Thread 3 dumping data for export.xf_attachment_view
2021-07-15 10:57:37 [INFO] - Empty table export.xf_attachment_view
2021-07-15 10:57:37 [INFO] - Thread 5 dumping data for export.xf_attachment WHERE (attachment_id >= 1300 AND attachment_id < 2599)
2021-07-15 10:57:37 [INFO] - Thread 7 dumping data for export.xf_attachment WHERE (attachment_id >= 3898 AND attachment_id < 5197)
2021-07-15 10:57:37 [INFO] - Thread 8 dumping data for export.xf_attachment WHERE (attachment_id >= 5197 AND attachment_id < 6496)
2021-07-15 10:57:37 [INFO] - Thread 4 dumping data for export.xf_attachment WHERE attachment_id IS NULL OR (attachment_id >= 1 AND attachment_id < 1300)
2021-07-15 10:57:37 [INFO] - Thread 1 dumping data for export.xf_attachment WHERE (attachment_id >= 6496 AND attachment_id < 7795)
2021-07-15 10:57:37 [INFO] - Thread 6 dumping data for export.xf_attachment WHERE (attachment_id >= 2599 AND attachment_id < 3898)
2021-07-15 10:57:37 [INFO] - Thread 3 dumping data for export.xf_attachment WHERE (attachment_id >= 7795 AND attachment_id < 9094)
2021-07-15 10:57:37 [INFO] - Thread 2 dumping data for export.xf_attachment WHERE (attachment_id >= 9094 AND attachment_id < 10393)
2021-07-15 10:57:37 [INFO] - Thread 5 dumping data for export.xf_attachment WHERE (attachment_id >= 10393 AND attachment_id < 11692)
2021-07-15 10:57:37 [INFO] - Thread 4 dumping data for export.xf_attachment WHERE (attachment_id >= 11692 AND attachment_id < 12991)
2021-07-15 10:57:37 [INFO] - Thread 6 dumping data for export.xf_attachment WHERE (attachment_id >= 12991 AND attachment_id < 14290)
2021-07-15 10:57:37 [INFO] - Thread 7 dumping data for export.xf_attachment WHERE (attachment_id >= 14290 AND attachment_id < 15589)
2021-07-15 10:57:37 [INFO] - Thread 8 dumping data for export.xf_attachment WHERE (attachment_id >= 15589 AND attachment_id < 16888)
2021-07-15 10:57:37 [INFO] - Thread 1 dumping data for export.xf_attachment WHERE (attachment_id >= 16888 AND attachment_id < 18187)
2021-07-15 10:57:37 [INFO] - Thread 3 dumping data for export.xf_attachment WHERE (attachment_id >= 18187 AND attachment_id < 19486)
2021-07-15 10:57:37 [INFO] - Thread 2 dumping data for export.xf_attachment WHERE (attachment_id >= 1:
2021-07-15 10:57:37 [INFO] - Server version reported as: 8.0.25
2021-07-15 10:57:37 [INFO] - Connected to a MySQL server
2021-07-15 10:57:37 [INFO] - Started dump at: 2021-07-15 10:57:37
2021-07-15 10:57:37 [INFO] - Written master status
2021-07-15 10:57:37 [INFO] - Thread 1 connected using MySQL connection ID 90
2021-07-15 10:57:37 [INFO] - Thread 2 connected using MySQL connection ID 91
2021-07-15 10:57:37 [INFO] - Thread 3 connected using MySQL connection ID 92
2021-07-15 10:57:37 [INFO] - Thread 4 connected using MySQL connection ID 93
2021-07-15 10:57:37 [INFO] - Thread 5 connected using MySQL connection ID 94
2021-07-15 10:57:37 [INFO] - Thread 6 connected using MySQL connection ID 95
2021-07-15 10:57:37 [INFO] - Thread 7 connected using MySQL connection ID 96
2021-07-15 10:57:37 [INFO] - Thread 8 connected using MySQL connection ID 97
2021-07-15 10:57:37 [INFO] - Thread 1 dumping db information for export
2021-07-15 10:57:37 [INFO] - Thread 2 dumping schema create for export
2021-07-15 10:57:37 [INFO] - Thread 3 dumping data for export.xf_attachment_view
2021-07-15 10:57:37 [INFO] - Empty table export.xf_attachment_view
2021-07-15 10:57:37 [INFO] - Thread 5 dumping data for export.xf_attachment WHERE (attachment_id >= 1300 AND attachment_id < 2599)
2021-07-15 10:57:37 [INFO] - Thread 7 dumping data for export.xf_attachment WHERE (attachment_id >= 3898 AND attachment_id < 5197)
2021-07-15 10:57:37 [INFO] - Thread 8 dumping data for export.xf_attachment WHERE (attachment_id >= 5197 AND attachment_id < 6496)
2021-07-15 10:57:37 [INFO] - Thread 4 dumping data for export.xf_attachment WHERE attachment_id IS NULL OR (attachment_id >= 1 AND attachment_id < 1300)
2021-07-15 10:57:37 [INFO] - Thread 1 dumping data for export.xf_attachment WHERE (attachment_id >= 6496 AND attachment_id < 7795)
2021-07-15 10:57:37 [INFO] - Thread 6 dumping data for export.xf_attachment WHERE (attachment_id >= 2599 AND attachment_id < 3898)
2021-07-15 10:57:37 [INFO] - Thread 3 dumping data for export.xf_attachment WHERE (attachment_id >= 7795 AND attachment_id < 9094)
2021-07-15 10:57:37 [INFO] - Thread 2 dumping data for export.xf_attachment WHERE (attachment_id >= 9094 AND attachment_id < 10393)
2021-07-15 10:57:37 [INFO] - Thread 5 dumping data for export.xf_attachment WHERE (attachment_id >= 10393 AND attachment_id < 11692)
2021-07-15 10:57:37 [INFO] - Thread 4 dumping data for export.xf_attachment WHERE (attachment_id >= 11692 AND attachment_id < 12991)
2021-07-15 10:57:37 [INFO] - Thread 6 dumping data for export.xf_attachment WHERE (attachment_id >= 12991 AND attachment_id < 14290)
2021-07-15 10:57:37 [INFO] - Thread 7 dumping data for export.xf_attachment WHERE (attachment_id >= 14290 AND attachment_id < 15589)
2021-07-15 10:57:37 [INFO] - Thread 8 dumping data for export.xf_attachment WHERE (attachment_id >= 15589 AND attachment_id < 16888)
2021-07-15 10:57:37 [INFO] - Thread 1 dumping data for export.xf_attachment WHERE (attachment_id >= 16888 AND attachment_id < 18187)
2021-07-15 10:57:37 [INFO] - Thread 3 dumping data for export.xf_attachment WHERE (attachment_id >= 18187 AND attachment_id < 19486)
2021-07-15 10:57:37 [INFO] - Thread 2 dumping data for export.xf_attachment WHERE (attachment_id >= 19486 AND attachment_id < 20785)
2021-07-15 10:57:37 [INFO] - Thread 1 dumping data for export.xf_attachment WHERE (attachment_id >= 20785 AND attachment_id < 22084)
2021-07-15 10:57:37 [INFO] - Thread 5 dumping data for export.xf_attachment WHERE (attachment_id >= 22084 AND attachment_id < 23383)
2021-07-15 10:57:37 [INFO] - Thread 6 dumping data for export.xf_attachment WHERE (attachment_id >= 23383 AND attachment_id < 24682)
2021-07-15 10:57:37 [INFO] - Thread 4 dumping data for export.xf_attachment WHERE (attachment_id >= 24682 AND attachment_id < 25981)
2021-07-15 10:57:37 [INFO] - Thread 8 dumping data for export.xf_attachment WHERE (attachment_id >= 25981 AND attachment_id < 27280)
2021-07-15 10:57:37 [INFO] - Thread 2 dumping data for export.xf_attachment WHERE (attachment_id >= 27280 AND attachment_id < 28579)
2021-07-15 10:57:37 [INFO] - Thread 7 dumping data for export.xf_attachment WHERE (attachment_id >= 28579 AND attachment_id < 29878)
2021-07-15 10:57:37 [INFO] - Thread 3 dumping data for export.xf_attachment WHERE (attachment_id >= 29878 AND attachment_id < 31177)
2021-07-15 10:57:37 [INFO] - Thread 4 dumping data for export.xf_attachment WHERE (attachment_id >= 31177 AND attachment_id < 32476)
2021-07-15 10:57:37 [INFO] - Thread 1 dumping data for export.xf_attachment WHERE (attachment_id >= 32476 AND attachment_id < 33775)
2021-07-15 10:57:37 [INFO] - Thread 8 dumping data for export.xf_attachment WHERE (attachment_id >= 33775 AND attachment_id < 35074)
2021-07-15 10:57:37 [INFO] - Thread 6 dumping data for export.xf_attachment WHERE (attachment_id >= 35074 AND attachment_id < 36373)
2021-07-15 10:57:37 [INFO] - Thread 2 dumping data for export.xf_attachment WHERE (attachment_id >= 36373 AND attachment_id < 37672)
2021-07-15 10:57:37 [INFO] - Thread 5 dumping data for export.xf_attachment WHERE (attachment_id >= 37672 AND attachment_id < 38971)
:
2021-07-15 10:57:37 [INFO] - Server version reported as: 8.0.25
2021-07-15 10:57:37 [INFO] - Connected to a MySQL server
2021-07-15 10:57:37 [INFO] - Started dump at: 2021-07-15 10:57:37
2021-07-15 10:57:37 [INFO] - Written master status
2021-07-15 10:57:37 [INFO] - Thread 1 connected using MySQL connection ID 90
2021-07-15 10:57:37 [INFO] - Thread 2 connected using MySQL connection ID 91
2021-07-15 10:57:37 [INFO] - Thread 3 connected using MySQL connection ID 92
2021-07-15 10:57:37 [INFO] - Thread 4 connected using MySQL connection ID 93
2021-07-15 10:57:37 [INFO] - Thread 5 connected using MySQL connection ID 94
2021-07-15 10:57:37 [INFO] - Thread 6 connected using MySQL connection ID 95
2021-07-15 10:57:37 [INFO] - Thread 7 connected using MySQL connection ID 96
2021-07-15 10:57:37 [INFO] - Thread 8 connected using MySQL connection ID 97
2021-07-15 10:57:37 [INFO] - Thread 1 dumping db information for export
2021-07-15 10:57:37 [INFO] - Thread 2 dumping schema create for export
2021-07-15 10:57:37 [INFO] - Thread 3 dumping data for export.xf_attachment_view
2021-07-15 10:57:37 [INFO] - Empty table export.xf_attachment_view
2021-07-15 10:57:37 [INFO] - Thread 5 dumping data for export.xf_attachment WHERE (attachment_id >= 1300 AND attachment_id < 2599)
2021-07-15 10:57:37 [INFO] - Thread 7 dumping data for export.xf_attachment WHERE (attachment_id >= 3898 AND attachment_id < 5197)
2021-07-15 10:57:37 [INFO] - Thread 8 dumping data for export.xf_attachment WHERE (attachment_id >= 5197 AND attachment_id < 6496)
2021-07-15 10:57:37 [INFO] - Thread 4 dumping data for export.xf_attachment WHERE attachment_id IS NULL OR (attachment_id >= 1 AND attachment_id < 1300)
2021-07-15 10:57:37 [INFO] - Thread 1 dumping data for export.xf_attachment WHERE (attachment_id >= 6496 AND attachment_id < 7795)

...

2021-07-15 10:57:37 [INFO] - Thread 3 dumping data for export.xf_attachment WHERE (attachment_id >= 266296 AND attachment_id < 267595)
2021-07-15 10:57:37 [INFO] - Thread 1 dumping data for export.xf_attachment WHERE (attachment_id >= 267595 AND attachment_id < 268894)
2021-07-15 10:57:37 [WARNING] - Unable to get estimates for export.xf_cron_entry: Unknown column 'cleanUpDaily' in 'where clause'

VERSIONS

  • Mac OS 11.4 (M1) / Oddly MyDumper 10.6 though the tags are actually 10.5 and 10.7 ?? But homebrew insists it's the 10.7 repo
@roni-estein
Copy link
Author

Using the file size option does work perfectly, as a work around in my case, however this probably still should be looked at

@davidducos davidducos linked a pull request Jul 15, 2021 that will close this issue
[Fix] estimate_count should not be called if it is not an integer field #375
Merged
@davidducos davidducos added this to the Release 0.10.9 milestone Jul 15, 2021
@davidducos
Copy link
Member

@roni-estein this is already fix on Master.

@roni-estein
Copy link
Author

Thanks!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
duplicate
Projects
None yet
Milestone
Release 0.10.7-2
Development

Successfully merging a pull request may close this issue.

[Fix] estimate_count should not be called if it is not an integer field mydumper/mydumper
2 participants
@davidducos @roni-estein