MySQL Forums
Forum List  »  General

"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:


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:


| sql_big_selects | ON |
| sql_max_join_size | 18446744073709551615 |

"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?


Options: ReplyQuote

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.