I have this query :
SELECT count(DISTINCT trim(level1)) cnn
WHERE f1 != -1
ORDER BY trim(level1);`
ORDER BY trim Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with
no GROUP columns is illegal if there is no GROUP BY clause
ORDER BY trim(level1)
MySQL allow you to run non standard SQL queries for which the SELECT list, HAVING condition, or ORDER BY list refer to nonaggregated columns.
In your query you are ordering by a nonaggregated column:
SELECT count(DISTINCT trim(level1)) cnn FROM table1 WHERE f1 != -1 ORDER BY trim(level1)
since you have no group by, in standard SQL you can only ORDER BY
count(DISTINCT trim(level1)), but you are ordering by
trim(level1) which is not aggregated (what's the purpose of it anyway?).
The reason why on one server it works and one other is not working is that one server has the ONLY_FULL_GROUP_BY disabled (so the query will run) and the other has it enabled.
For a full explanation about standard SQL92, standard SQL99, and how MySql handles it please see this manual page: MySQL Handling of GROUP BY