"SHOW VARIABLES" and "mysqladmin variables" show different settings

[rihad rihad]

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.