List:General Discussion« Previous MessageNext Message »
From:Arjen Lentz Date:January 21 2005 6:31am
Subject:Re: MySQL quota problem
View as plain text  
Hi Iavor,

On Fri, 2005-01-21 at 02:33, Iavor Stoev wrote:
> I have trouble with setting mysql database quota per user at my
> server.
> 
> I`m using MySQL 4.0.22 on Linux with kernel 2.4.29. 
> 
> I`m using group quota on my server and all user`s files, mails and
> databases are with group $user (user`s mysql database directory is
> with setgid bit enabled to apply the group id to the new crated tables
> also).
> 
> The problem is that when some user exceeds his quota the MySQL server
> "dies" for all users with:
> "Disk is full writing. Waiting for someone to free space... Retry in
> 60 secs"  
> 
> I suppose that this behaivior is caused, because the thread is waiting
> for free space, and when someone tries to write again to the same
> database, a very common example is some buletin board; a new thread is
> started that waits again, etc until the max threads are full and the
> MySQL server does nothing until it is restarted or the quota for the
> problem database is increased.
> 
> Can anyone tell me is there a working solution for MySQL quota per
> database or a patch to return disk exceed error and not wait ? 
> Because in my case the MySQL can`t write only to one database, all
> other hundreds of databases are operational, but server dies for all
> ...

Coding this into the RDBMS is theoretically possible, and at least for
InnoDB and other transactional engines (cluster, etc) you could trigger
a rollback on quote full. However, such luxiory does not exist with
MyISAM.

While your setup is pretty nifty, I would suggest a different approach.
Simply run a cron job that checks whatever you want to check, and
adjusts user privileges accoringly (i.e. grant/revoke INSERT privs and
any other privileges you want controlled).

Depending on the cron job frequency that's slightly rougher in terms of
disk space use, but the handling could be much cleaner.


Regards,
Arjen. 
-- 
Arjen Lentz, Community Relations Manager
MySQL AB, www.mysql.com

MySQL User Conference (Santa Clara CA, 18-21 April 2005)
Early registration now open: www.mysqluc.com


Thread
MySQL and NULL entiresMike Machado12 Mar
  • Re: MySQL and NULL entiresSasha Pachev12 Mar
  • Re: MySQL and NULL entiresThimble Smith12 Mar
  • Re: MySQL and NULL entiresChris12 Mar
  • Re: MySQL and NULL entiresChristian Mack12 Mar
  • Re: MySQL and NULL entiresKarl Pielorz12 Mar
  • Re: MySQL and NULL entiresEd Carp12 Mar
  • Searching for CEOs against software patents"Jonas Norrman6 Oct
  • Re: MySQL quota problemArjen Lentz21 Jan
Re: MySQL quota problemHeikki Tuuri21 Jan