Mysql "no" has no default settings to limit SELECT queries.
You’re probably using it Mysql Workbench which automatically puts LIMIT 0.1000 in all your Selects (Default settings).
To disregard this setting just use one LIMIT larger than 1000 in your query so Workbench will read your query and see that you already have a limit defined and will not put any limit in your query.
The other option is to remove the Workbench setting ("SQL queries > Limit Rows").
Mysql has a configuration called sql_select_limit used to limit the output of Selects even without using LIMIT, the default value of this setting is (2 32)-1 or (2 64)-1, for sure no one will display a report with a value greater than these. :)
Mysql Workbench does not change this setting, the program literally concatenates LIMIT 0.1000 in the Selects if LIMIT is not set.
Spinning here...
– Igoto
Yeah, that’s what I meant to say.
– Igoto
@Igob, are you using any UI? Mysql does not limit the query, who puts are some access interfaces to it. This can usually be solved with confusion. Your limit test worked simply by going over the original limit.
– Bacco
To make it clear, Igob: Are you using Mysql Workbench, and if so, does this limit problem occur only in Mysql Workbench? What other tools you used and in which of them the problem occurred?
– user7835
I used Mysql Workbench. I only had to set the value on it.
– Igoto