Mysql – thesqldump without SQL_BIG_SELECTS

MySQL

Trying to dump all databases for replication creation using

mysqldump –all-databases –master-data –single-transaction >
all_databases.sql

Results in the following error

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

Is there a way to ensure mysqldump works without having to restart the server and updating my.cnf in it? Definitely we wouldn't like to always enable big selects on the production server.

Using MySQL 5.6

Best Answer

Before launching the mysqldump you can send this query to check if big selects are available:

SELECT @@global.SQL_BIG_SELECTS;

and then you can enable them using

SET @@global.SQL_BIG_SELECTS = 1;

and disable again (after the dump) using

SET @@global.SQL_BIG_SELECTS = 0;

Related Topic