Bug #51236 mysqldump cannot dump tables larger than max_join_size
Submitted: 17 Feb 2010 10:53 Modified: 5 Mar 2015 15:12
Reporter: Jarrod Makin Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: mysqldump Command-line Client Severity:S3 (Non-critical)
Version:5.6.23 OS:Any
Assigned to: CPU Architecture:Any
Tags: Max_join_size, mysqldump, sql_big_selects
Triage: Triaged: D5 (Feature request)

[17 Feb 2010 10:53] Jarrod Makin
Description:
If max_join_size is set in [mysqld] section of /etc/my.cnf attempting to dump a large table can never complete, and gives unhelpful message.

mysqldump: Couldn't execute 'SELECT /*!40001 SQL_NO_CACHE */ * FROM `table_name`': The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay (1104)

Setting a where clause is no help, as I don't want to restrict the data set, I want to backup the whole table.

Setting sql_big_selects=1 or sql_max_join_size=1 is not an option either, as mysqldump is not an interactive mysql session, so I can't run arbitrary commands

How to repeat:
Add a line like
set-variable = max_join_size=1000
to the [mysqld] section of global mysql server configuration
Restart mysql server
Create a large table
Attempt to dump the large table with mysqldump

Suggested fix:
An optional override such as --sql_big_selects=1 on the command line invocation of mysqldump.

An optional override such as
[mysqldump]
set-variable = sql_big_selects=1
in a personal .my.cnf file

perhaps options like these should only be available to the root mysql user
[22 Mar 2010 19:10] Sveta Smirnova
Thank you for the reasonable feature request.
[20 Sep 2010 10:01] Jarrod Makin
Has there been any further progress on this?
[28 Oct 2010 1:56] Evren Yurtesen
I am having the exact same problem and not able to use mysqldump to dump several databases.

I believe this is a bug that mysqldump does not recognize the command line option of sql_big_selects
[24 Jul 2013 19:47] Jason Welter
Has this been resolved?  I'm really bumping into this limitation of mysqldump.
[25 Jul 2013 8:42] Jarrod Makin
I have updated the list of affected versions to current.
[5 Mar 2015 15:12] Jarrod Makin
Version affected updated to reflect that the bug is still out there