Bug #31347 Increase in memory usage after many DROP USER statements
Submitted: 2 Oct 2007 15:55 Modified: 9 Nov 2007 0:08
Reporter: Piotr Gasidlo Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.45, 5.0.48 OS:Any
Assigned to: Kristofer Pettersson CPU Architecture:Any
Tags: memory leak

[2 Oct 2007 15:55] Piotr Gasidlo
Description:
Droping huge number (10k+) of users using DROP USER statement causes huge increase in memory usage.

How to repeat:
Create huge number of users using standard GRANT statement and then delete them using DROP USER them in loop.

Suggested fix:
After executing FLUSH PRIVILEGES command memory usage returns to normal.
[16 Oct 2007 10:09] Sveta Smirnova
Thank you for the report.

Version 5.0.32 is quite old. Additionally I can not repeat described behaviour with current development sources. So I close the report as "Can't repeat".

Please upgrade and if you still have same problem feel free to provide your configuration file and reopen the report.
[18 Oct 2007 9:01] Piotr Gasidlo
I've done future investigation. I've done test on 5.0.45 and 5.0.48. Correct command sequence:

# Create sequentially 10k users and 10k databases. Map privileges for each user to separate database, important: grant ALL PRIVILEGES, not just USAGE, etc.

for i in `seq 1 ${USERS}`; do
echo "CREATE DATABASE IF NOT EXISTS \`db${i}\`;"
echo "GRANT ALL PRIVILEGES ON \`db${i}\`.* TO 'user${i}'@'localhost';"
done | mysql -u root 

# Drop sequentially 10k users and 10k databases. Use trick described in http://bugs.mysql.com/bug.php?id=19166 to emulate DROP USER [IF EXISTS].

for i in `seq 1 ${USERS}`; do
echo "GRANT USAGE ON \`db${i}\`.* TO 'user${i}'@'localhost' IDENTIFIED BY 'randompassword';"
echo "DROP USER 'user${i}'@'localhost';"
echo "DROP DATABASE IF EXISTS \`db${i}\`;"
done | mysql -u root

Result huge memory usage, till FLUSH PRIVILEGES (which should be optional acording to CREATE DATABASE/GRANT/DROP DATABASE/DROP USER manual pages).

Future testing. We tried also this combinations:

for i in `seq 1 ${USERS}`; do
echo "DROP DATABASE IF EXISTS \`db${i}\`;"
echo "GRANT USAGE ON \`db${i}\`.* TO 'user${i}'@'localhost' IDENTIFIED BY 'randompassword';"
echo "DROP USER 'user${i}'@'localhost';"
done | mysql -u root

for i in `seq 1 ${USERS}`; do
echo "DROP DATABASE \`db${i}\`;"
echo "GRANT USAGE ON \`db${i}\`.* TO 'user${i}'@'localhost' IDENTIFIED BY 'randompassword';"
echo "DROP USER 'user${i}'@'localhost';"
done | mysql -u root

for i in `seq 1 ${USERS}`; do
echo "GRANT USAGE ON \`db${i}\`.* TO 'user${i}'@'localhost' IDENTIFIED BY 'randompassword';"
echo "DROP USER 'user${i}'@'localhost';"
echo "DROP DATABASE \`db${i}\`;"
done | mysql -u root

for i in `seq 1 ${USERS}`; do
echo "DROP USER 'user${i}'@'localhost';"
echo "DROP DATABASE IF EXISTS \`db${i}\`;"
done | mysql -u root

for i in `seq 1 ${USERS}`; do
echo "DROP DATABASE IF EXISTS \`db${i}\`;"
echo "DROP USER 'user${i}'@'localhost';"
done | mysql -u root

for i in `seq 1 ${USERS}`; do
echo "DROP USER 'user${i}'@'localhost';"
echo "DROP DATABASE \`db${i}\`;"
done | mysql -u root

for i in `seq 1 ${USERS}`; do
echo "DROP DATABASE \`db${i}\`;"
echo "DROP USER 'user${i}'@'localhost';"
done | mysql -u root
[18 Oct 2007 9:04] Piotr Gasidlo
I've used medium.my.cnf without any changes (excluding paths changes).
[19 Oct 2007 22:28] Sveta Smirnova
Thank you for the feedback.

Verified as described.
[20 Oct 2007 5:29] Sveta Smirnova
Error log file is filled by:

mysqld(20140,0x4923c00) malloc: *** vm_allocate(size=1327104) failed (error code=3)
mysqld(20140,0x4923c00) malloc: *** error: can't allocate region
mysqld(20140,0x4923c00) malloc: *** set a breakpoint in szone_error to debug
071019 23:23:00 [ERROR] /Users/apple/Applications/mysql-5.0/libexec/mysqld: Out of memory at line 201, 'my_alloc.c'
071019 23:23:00 [ERROR] /Users/apple/Applications/mysql-5.0/libexec/mysqld: needed 1323256 byte (1293k), memory in use: -658354845 bytes (3551380k)
071019 23:23:00 [ERROR] Out of memory; check if mysqld or some other process uses all available memory; if not, you may have to use 'ulimit' to allow mysql        d to use more memory or you can add more swap space
[25 Oct 2007 12:14] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/36340

ChangeSet@1.2544, 2007-10-25 14:16:24+02:00, thek@adventure.(none) +1 -0
  Bug#31347 Increase in memory usage after many DROP USER statements
  
  Dropping users causes huge increase in memory usage because field values were
  allocated on the server memory root for temporary usage but never deallocated.
  
  This patch changes the target memory root to be that of the thread handler
  instead since this root is cleared between each statement.
[31 Oct 2007 10:25] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/36740

ChangeSet@1.2544, 2007-10-31 11:27:34+01:00, thek@adventure.(none) +1 -0
  Bug#31347 Increase in memory usage after many DROP USER statements
  
  Dropping users causes huge increase in memory usage because field values were
  allocated on the server memory root for temporary usage but never deallocated.
  
  This patch changes the target memory root to be that of the thread handler
  instead since this root is cleared between each statement.
[31 Oct 2007 11:23] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/36742

ChangeSet@1.2544, 2007-10-31 12:25:18+01:00, thek@adventure.(none) +1 -0
  Bug#31347 Increase in memory usage after many DROP USER statements
  
  Dropping users causes huge increase in memory usage because field values were
  allocated on the server memory root for temporary usage but never deallocated.
  
  This patch changes the target memory root to be that of the thread handler
  instead since this root is cleared between each statement.
[7 Nov 2007 21:59] Bugs System
Pushed into 6.0.4-alpha
[7 Nov 2007 22:00] Bugs System
Pushed into 5.1.23-rc
[7 Nov 2007 22:02] Bugs System
Pushed into 5.0.52
[9 Nov 2007 0:08] Paul DuBois
Noted in 5.0.52, 5.1.23, 6.0.4 changelogs.