Bug #30606 Non-super user may increase sort_buffer_size
Submitted: 23 Aug 2007 23:05 Modified: 26 Aug 2007 16:17
Reporter: Grigory Rubtsov Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Parser Severity:S3 (Non-critical)
Version:5.0.22 OS:Linux
Assigned to: CPU Architecture:Any

[23 Aug 2007 23:05] Grigory Rubtsov
Description:
Setting the session variables of sort_buffer_size and join_buffer_size, read-only access user may use a lot more of the server memory than needed.

How to repeat:
mysql> grant select on test.* to a@localhost identified by b;
mysql> exit
Bye

$ mysql -u a -pb test
mysql> select * from cities;
+----+----------+
| id | name     |
+----+----------+
| 32 | New York | 
| 33 | Tokyo    | 
| 34 | Paris    | 
| 35 | Roma     | 
| 36 | Milano   | 
| 37 | Brussels | 
| 38 | Denver   | 
+----+----------+
7 rows in set (0.00 sec)

mysql> set @@session.join_buffer_size=1000000000;
mysql> set @@session.sort_buffer_size=1000000000;

Then, some join will eat all the memory:
mysql> select * from (select c1.id, c2.id id2, c3.id id3, c4.id id4, c5.id id5 from cities c1, cities c2, cities c3, cities c4, cities c5, cities c6, cities c7) f limit 2;

Suggested fix:
Limit the sort_buffer_size to be not larger than a global value.
[24 Aug 2007 5:50] Grigory Rubtsov
It's easier to see the memory usage difference of a bit more complex query:

mysql> select * from (select c1.id, c2.id id2, c3.id id3, c4.id id4, c5.id id5, c6.id id6, c7.id id7, c8.id id8 from cities c1, cities c2, cities c3, cities c4, cities c5, cities c6, cities c7, cities c8) f order by id*id5*id7+id3-id2, id6 desc limit 200000,2;

Line in top for default sort and join buffers, while the query is running:
 5892 mysql     21   0  151m  36m 4592 S 81.6  3.2   4:04.22 mysqld             

compared to line in top for 1G sort and join buffers set in session variables by user in two consecutive time moment:
5892 mysql     21   0 2057m  36m 4592 S 37.6  3.2   4:31.74 mysqld             
5892 mysql     21   0  399m 284m 4592 S 82.9 25.1   4:50.64 mysqld
[24 Aug 2007 10:09] MySQL Verification Team
looks like bug #29340
[26 Aug 2007 16:17] MySQL Verification Team
Thank you for the bug report. Duplicate of bug: http://bugs.mysql.com/bug.php?id=29340