MySQL Forums :: General :: "SHOW VARIABLES" and "mysqladmin variables" show different settings


Advanced Search

"SHOW VARIABLES" and "mysqladmin variables" show different settings
Posted by: rihad rihad ()
Date: February 12, 2011 12:26PM

Hi, we have MySQL 5.5.9 running on FreeBSD 8.2-RC3. I've noticed that some heavy queries refuse to run under mysql client with the error:

Quote

ERROR 1104 (42000): 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

As per the docs SQL_BIG_SELECTS should be On by default. So I looked in /etc/my.cnf but found no setting that would set it to Off. And indeed it is "On" by default in MySQL 5.1, but not in 5.5.8. Then I ran mysql -e"SHOW VARIABLES" and "mysqladmin variables" with the same normal user, and those settings were different:

Quote

"mysqladmin"
| sql_big_selects | ON |
| sql_max_join_size | 18446744073709551615 |
Quote

"mysql client"
| sql_big_selects | OFF |
| sql_max_join_size | 1000000 |

Can someone tell me why two different client apps return different results?


Also, selects that tend to return big results limit their results to 1000 rows unless a specific larger LIMIT is set. Can anything be done except for hunting down such queries and adding a "big enough" LIMIT to them that would cover all rows?

Thanks.

Options: ReplyQuote


Subject Written By Posted
"SHOW VARIABLES" and "mysqladmin variables" show different settings rihad rihad 02/12/2011 12:26PM
Re: "SHOW VARIABLES" and "mysqladmin variables" show different settings Peter Brawley 02/12/2011 02:12PM
Re: "SHOW VARIABLES" and "mysqladmin variables" show different settings Rick James 02/12/2011 04:20PM
Re: "SHOW VARIABLES" and "mysqladmin variables" show different settings rihad rihad 02/13/2011 12:13AM
Re: "SHOW VARIABLES" and "mysqladmin variables" show different settings Peter Brawley 02/13/2011 12:11PM
Re: "SHOW VARIABLES" and "mysqladmin variables" show different settings rihad rihad 02/13/2011 12:56PM
Re: "SHOW VARIABLES" and "mysqladmin variables" show different settings Rick James 02/13/2011 06:43PM
Re: "SHOW VARIABLES" and "mysqladmin variables" show different settings rihad rihad 02/13/2011 11:32PM
Re: "SHOW VARIABLES" and "mysqladmin variables" show different settings rihad rihad 02/15/2011 10:58PM
Re: "SHOW VARIABLES" and "mysqladmin variables" show different settings Peter Brawley 02/16/2011 12:13AM
Re: "SHOW VARIABLES" and "mysqladmin variables" show different settings rihad rihad 02/18/2011 12:50AM
Re: "SHOW VARIABLES" and "mysqladmin variables" show different settings Peter Brawley 02/18/2011 01:23AM
Re: "SHOW VARIABLES" and "mysqladmin variables" show different settings rihad rihad 02/18/2011 09:25AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.