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
• Status: Verified
• Category: MySQL Server: mysqldump Command-line Client
• Severity: S3 (Non-critical)
• Version: 5.6.23
• OS: Any
• CPU Architecture: Any
• Tags: Max_join_size, mysqldump, sql_big_selects

[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